#!/usr/bin/perl -Ilibs

use strict;
use warnings;
package API::VehicleAdmin;

our $Config;
BEGIN{
	use API::Config;
	$Config = $API::Config::Config;
}
use lib ($Config->{Website_libs});
use JSON ();

use API::Controller;

use File::Basename;
use MIME::Base64;
use MIME::Entity;

use ECS::CDN::S3;
use ECS::Vehicle;
use ECS::Site;
use Moose;
use Data::Dumper;

use ECS::Endeca::Dimension;

use vars qw(@ISA);
@ISA = qw(SOAP::Server::Parameters API::Controller);

my @VEHICLE_CONFIG_STEPS = qw(
    make
    year
    model
    subModel
    engine
    bodyType
    bodyCode
);

my $TMS_VEHICLE_GROUP_URL_PREFIX = "BMW-";

my $logTrace = $ENV{'VEHICLE_ADMIN_TRACE'} || 0;

__PACKAGE__->meta->make_immutable;

###############################################################################

sub vehicleIndex {
    my $self = (shift)->new({'input' => shift || {}});

    my %selectorFieldLabels;
    for my $dim ( keys %ECS::Endeca::Dimension::DISPLAY_NAMES ) {
        next unless $dim =~ /^Selector/;
        $selectorFieldLabels{ lcfirst($dim) } =
            $ECS::Endeca::Dimension::DISPLAY_NAMES{ $dim };
    }

    my %makeFields;
    my %makeIDs;
    for my $makeID ( keys %ECS::Vehicle::MAKES ) {
        my $makeConfig = $ECS::Vehicle::MAKES{ $makeID };
        $makeIDs{ $makeConfig->{'makeName'} } = $makeID;
        next if $makeConfig->{'type'} ne "vehicleSelector";
        $makeFields{ $makeConfig->{'makeName'} } = $makeConfig->{'steps'};
    }

    if ( my $errorMsg = $self->input->{'errorMsg'} ) {
        $self->data->{'errorMsg'} = $errorMsg;
    }

    $self->data->{'makeIDs'} = \%makeIDs;
    $self->data->{'selectors'} = {
        'makeFields' => \%makeFields,
        'fieldLabels' => \%selectorFieldLabels,
    };

    my $tmsSite = ECS::Site::fromName("TMS");
    $self->data->{'tmsSiteDomain'} = $tmsSite->domain;
    $self->data->{'tmsVehicleGroupUrlPrefix'} = $TMS_VEHICLE_GROUP_URL_PREFIX;
	$self->data->{'tmsVehicleGroupParents'} = $tmsSite->site_dbhr->selectcol_arrayref(
        "SELECT label
        FROM
            vehicleGroup
        WHERE
            parentID IS NULL
            AND vehicleConfigID IS NULL
        ORDER BY
            label ASC"
    );

    $self->data->{'templates'} = [ "index.html" ];

    return $self->result;
}

###############################################################################

sub getNextVehicleConfig {
    my $self = (shift)->new({'input' => shift || {}});

    $self->data->{'dataType'} = "json";

    my $categories = JSON::decode_json($self->input->{'categories'});
    my $nextCategory = $self->input->{'nextCategory'};
    $nextCategory =~ s/\W//g;

    my $query = "SELECT
            DISTINCT " . $nextCategory . "Name AS optionName
        FROM
            vehicleConfig
            LEFT JOIN make
                USING (makeID)
            LEFT JOIN year
                USING (yearID)
            LEFT JOIN model
                USING (modelID)
            LEFT JOIN subModel
                USING (subModelID)
            LEFT JOIN engine
                USING (engineID)
            LEFT JOIN bodyType
                USING (bodyTypeID)
            LEFT JOIN bodyCode
                USING (bodyCodeID)
        WHERE
            ";

    my @where = ();
    my @argv = ();
    for my $category (keys %{$categories}) {
        my $value = $categories->{$category};
        $category =~ s/\W//g;

        push(@where, $category . "Name = ?");
        push(@argv, $value);
    }

    push(@where, $nextCategory . "ID <> 0");
    push(@where, $nextCategory . "Name IS NOT NULL");
    $query .= join("\nAND ", @where);

    my $sth = $self->dbhr->prepare($query);
    $sth->execute(@argv);

    my @options = ();
    while(my $option = $sth->fetchrow_hashref()) {
        push(@options, $option->{'optionName'});
    }

    $self->data->{'data'} = \@options;
	
    return $self->result;
}

###############################################################################

sub validateCreateVehicle {
    my $self = (shift)->new({'input' => shift || {}});

    my $errorMsg;

    if ( $self->input->{'addToTMS'} ) {
        $errorMsg = $self->_validateTmsVehicleGroupCreate();
        return $self->_doValidateReturn( $errorMsg ) if $errorMsg;
    }

    return $self->_doValidateReturn( $errorMsg );
}

###############################################################################

sub _validateTmsVehicleGroupCreate {
    my $self = shift;

    my $label = $self->_scrubTextInput( $self->input->{'label'} );
    if ( length($label || '') == 0 ) {
        return "TMS Vehicle Group label is required";
    }
    if ( my $labelExistsID = $self->_getTmsVehicleGroupByLabel( $label ) ) {
        return "TMS Vehicle Navigation label already exists: $label";
    }

    my $shortURLSuffix = $self->_scrubTextInput( $self->input->{'tmsVehicleGroupUrl'} );
    if ( length($shortURLSuffix) == 0 ) {
        return "TMS Vehicle Navigation URL is required";
    }

    my $shortURL = (
        $TMS_VEHICLE_GROUP_URL_PREFIX
        .$shortURLSuffix
    );
    if ( my $urlExistsID = $self->_getTmsVehicleGroupByURL( $shortURL ) ) {
        return "TMS Vehicle Navigation URL already exists: $shortURL";
    }

    return;
}

###############################################################################

sub _doValidateReturn {
    my $self = shift;
    my ($errorMsg) = @_;
    $self->data->{'dataType'} = "json";
    $self->data->{'data'} = {
        'valid' => (defined $errorMsg ? 0 : 1),
        'errorMsg' => $errorMsg,
    };
    return $self->result;
}

###############################################################################

sub createVehicle {
    my $self = (shift)->new({'input' => shift || {}});
    my $envelope = pop;

    my $makeID = $self->input->{'makeID'}
        or die "makeID required";
    my $makeConfig = $ECS::Vehicle::MAKES{ $makeID }
        or die "makeConfig does not exist for makeID $makeID";

    warn 'input: '.Data::Dumper::Dumper($self->input) if $logTrace;

    my $vehicleConfigID = $self->_createVehicleConfig({
        'steps' => \@VEHICLE_CONFIG_STEPS,
        'detailVehicle' => 1,
    });

    warn '_createVehicleConfig vehicleConfigID: '.$vehicleConfigID if $logTrace;

    if ( not $vehicleConfigID ) {
        $self->data->{'redirect'} = "./?errorMsg=vehicleConfig+create+failed";
        return $self->result;
    }

    if (
        # these are the Makes that have vehicleNav records
           $self->input->{'make'} eq "Audi"
        or $self->input->{'make'} eq "BMW"
        or $self->input->{'make'} eq "Volkswagen"
    ) {
        $self->_createVehicleNav({
            'vehicleConfigID' => $vehicleConfigID,
        });
    }

    if ( $makeConfig->{'type'} eq "vehicleSelector" ) {

        my $makeVehicleConfigID = $self->_getVehicleConfigID({
            'makeID' => $makeID,
        });

        if (not $makeVehicleConfigID) {
            die "could not determine makeVehicleConfigID value";
        }
        warn "makeVehicleConfigID: $makeVehicleConfigID" if $logTrace;

        my $vehicleSelectorID = $self->_createVehicleSelector({
            'steps' => $makeConfig->{'steps'},
            'vehicleConfigID' => $vehicleConfigID,
            'makeVehicleConfigID' => $makeVehicleConfigID,
        });
        warn '_createVehicleSelector vehicleSelectorID: '.$vehicleSelectorID if $logTrace;
    }

    if ( $self->input->{'addToTMS'} ) {
        my $tmsVehicleGroupID = $self->_addToTMS({
            'envelope' => $envelope,
        });
        warn "_addToTMS created tms vehicleGroupID $tmsVehicleGroupID"  if $logTrace;
    }

    $self->data->{'redirect'} = "./";
    return $self->result;
}

###############################################################################

sub _createVehicleConfig {
    my $self = shift;
    my ($p) = @_;

    my $steps = $p->{'steps'}
        or die "steps required";
    my $detailVehicle = $p->{'detailVehicle'};
        die "detailVehicle must be specified" unless defined $detailVehicle;

    my %vehicleConfig = ();
    for my $key ( @{ $p->{'steps'} } ) {
        if(defined $self->input->{$key}) {
            my $value = $self->_scrubTextInput( $self->input->{ $key } );

            my $categoryID = $self->_getCategoryID($key, $value);

            $vehicleConfig{$key . "ID"} = $categoryID;
            $vehicleConfig{$key} = $value;
        }
    }

    my @values = ();
    my @argv = ();
    for my $key (keys %vehicleConfig) {
        next unless $key =~ m/ID$/;

        my $value = $vehicleConfig{$key};

        push(@values, "$key = ?");
        push(@argv, $value);
    }

    push(@values, "detailVehicle = ?");
    push(@argv, $detailVehicle);

    my $vehicleLabel = ECS::Vehicle::buildVehicleLabel({
        'vehicleConfig' => \%vehicleConfig,
    }) if $detailVehicle; # we want a value of NULL here for non-detail-vehicles
    push(@values, "vehicleLabel = ?");
    push(@argv, $vehicleLabel);

    my $vehicleURL = ECS::Vehicle::buildVehicleURL({
        'vehicleConfig' => \%vehicleConfig,
    }) if $detailVehicle; # we want a value of NULL here for non-detail-vehicles
    push(@values, "vehicleURL = ?");
    push(@argv, $vehicleURL);

    my $query = "INSERT IGNORE INTO
            vehicleConfig
        SET
            " . join(",\n", @values);

    my $sth = $self->dbh->prepare($query);
    $sth->execute(@argv);

    return $self->_getVehicleConfigID(\%vehicleConfig);
}

###############################################################################

sub _createVehicleNav {
    my $self = shift;
    my ($p) = @_;

    my $vehicleConfigID = $p->{'vehicleConfigID'}
        or die "vehicleConfigID required";

    my $query = "INSERT IGNORE INTO
            vehicleNav
        SET
            L1 = ?,
            cleanL1 = ?,
            L2 = ?,
            cleanL2 = ?,
            L3 = ?,
            cleanL3 = ?,
            L4 = ?,
            cleanL4 = ?,
            L5 = ?,
            cleanL5 = ?,
            L6 = ?,
            cleanL6 = ?,
            vehicleConfigID = ?";

    my $sth = $self->dbh->prepare($query);
    $sth->execute(
        ( $self->input->{'L1'} || "" ) x 2,
        ( $self->input->{'L2'} || "" ) x 2,
        ( $self->input->{'L3'} || "" ) x 2,
        ( $self->input->{'L4'} || "" ) x 2,
        ( $self->input->{'L5'} || "" ) x 2,
        ( $self->input->{'L6'} || "" ) x 2,
        $vehicleConfigID,
        );

    return;
}

###############################################################################

sub _createVehicleSelector {
    my $self = shift;
    my ($p) = @_;

    my $pSteps = $p->{'steps'}
        or die "steps required";
    my @steps = @{ $pSteps };
    my $vehicleConfigID = $p->{'vehicleConfigID'}
        or die "vehicleConfigID required";
    my $makeVehicleConfigID = $p->{'makeVehicleConfigID'};
        die "makeVehicleConfigID must be specified" unless defined $makeVehicleConfigID;

    my $makeID = $self->input->{'makeID'};
    my $makeConfig = $ECS::Vehicle::MAKES{ $makeID };
    my %selector;
    my @urlParts = ( $makeConfig->{'makeName'} );
    for my $step ( @steps ) {
        my $field = $step."ID";

        # If the selector field ID was specified, use it
        if ( my $selectedID = $self->input->{ $field } ) {
            $selector{ $field } = $selectedID;
            my $option = $self->_getVehicleSelectorOptionById({
                'tableName' => $step,
                'optionID' => $selectedID,
            });
            push(@urlParts, $option->{'optionLabel'});
            next;
        }

        # die if we have no information at all for this selector field
        if (not defined $self->input->{ $step }) {
            die "_createVehicleSelector missing step input for $step";
        }

        my $optionLabel = $self->_scrubTextInput( $self->input->{ $step } );

        # retrieve the existing option by label just in case the user didn't select the auto-suggest option
        my $option = $self->_getVehicleSelectorOptionByLabel({
            'tableName' => $step,
            'optionLabel' => $optionLabel,
        });

        # if retrieving the option by label returned no option, create the option
        if (not $option) {
            warn "_createVehicleSelector $step: $optionLabel" if $logTrace;
            $option = $self->_createVehicleSelectorOption({
                'tableName' => $step,
                'optionLabel' => $optionLabel,
            });
        }

        $selector{ $field } = $option->{'optionID'};
        push(@urlParts, $option->{'optionLabel'});

    }

    my $selectorVehicleURL = $self->_buildSelectorVehicleURL({
        'urlParts' => \@urlParts,
    });
    warn "_createVehicleSelector selectorVehicleURL: $selectorVehicleURL" if $logTrace;

    my $existsVehicleSelectorID =
        $self->_getVehicleSelectorByURL( $selectorVehicleURL );

    if ($existsVehicleSelectorID) {
        warn "vehicleSelector record already exists: $existsVehicleSelectorID";
        return $existsVehicleSelectorID;
    }

    my $sth = $self->dbh->prepare(
        "INSERT INTO vehicleSelector (
            makeVehicleConfigID,
            vehicleConfigID,
            selectorVehicleURL,
            ".join(',', map { $_."ID" } @steps)
        .") VALUES (?, ?, ?, ". join(',', ('?') x @steps).")"
    );

    $sth->execute(
        $makeVehicleConfigID,
        $vehicleConfigID,
        $selectorVehicleURL,
        map { $selector{ $_."ID" } } @steps,
    );

    return $sth->{'mysql_insertid'};
}

###############################################################################

sub _buildSelectorVehicleURL {
    my $self = shift;
    my ($p) = @_;

    my $urlParts = $p->{'urlParts'}
        or die "urlParts required";

    my @cleanParts;
    for my $part ( @{ $urlParts } ) {
        $part =~ s/( |-|\/)/_/g;
        $part =~ s/\+/_present/g;
        $part =~ s/(\(|\)|')//g;
        push(@cleanParts, $part);
    }

    return join('-', @cleanParts);
}

###############################################################################


sub _getVehicleSelectorByURL {
    my $self = shift;
    my ($url) = @_;

    die "url required" unless $url;

    my $sth = $self->dbhr->prepare(
        "SELECT
            vehicleSelectorID
        FROM
            vehicleSelector
        WHERE
            selectorVehicleURL = ?"
    );
    $sth->execute( $url );
    my ($vehicleSelectorID) = $sth->fetchrow_array;

    return $vehicleSelectorID;
}

###############################################################################

sub _getVehicleSelectorOptionById {
    my $self = shift;
    my ($p) = @_;

    my $tableName = $p->{'tableName'}
        or die "tableName required";
    my $optionID = $p->{'optionID'}
        or die "optionID required";

    my $sth = $self->dbhr->prepare(
        "SELECT
            ${tableName}ID AS optionID,
            ${tableName}Name AS optionLabel
        FROM
            ${tableName}
        WHERE
            ${tableName}ID = ?"
    );
    $sth->execute( $optionID );
    my ($option) = $sth->fetchrow_hashref;

    return $option;
}

###############################################################################

sub _getVehicleSelectorOptionByLabel {
    my $self = shift;
    my ($p) = @_;

    my $tableName = $p->{'tableName'}
        or die "tableName required";
    my $optionLabel = $p->{'optionLabel'};
    die "optionLabel must be specified"
        unless defined $optionLabel;

    my $sth = $self->dbhr->prepare(
        "SELECT
            ${tableName}ID AS optionID,
            ${tableName}Name AS optionLabel
        FROM
            ${tableName}
        WHERE
            ${tableName}Name = ?"
    );
    $sth->execute( $optionLabel );
    my ($option) = $sth->fetchrow_hashref;

    return $option;
}

###############################################################################

sub _createVehicleSelectorOption {
    my $self = shift;
    my ($p) = @_;

    my $tableName = $p->{'tableName'}
        or die "tableName required";
    my $optionLabel = $p->{'optionLabel'};
    die "optionLabel must be specified"
        unless defined $optionLabel;

    my $sth = $self->dbh->prepare(
        "INSERT INTO $tableName (${tableName}Name)
        VALUES (?)"
    );

    $sth->execute(
        $optionLabel,
    );

    return {
        'optionID' => $sth->{'mysql_insertid'},
        'optionLabel' => $optionLabel,
    };
}

###############################################################################

sub _addToTMS {
    my $self = shift;
    my ($p) = @_;

    my $envelope = $p->{'envelope'};

    my $site = ECS::Site::fromName("TMS");
    my $makeID = $self->input->{'makeID'};
    my $makeConfig = $ECS::Vehicle::MAKES{ $makeID };

    my $parentLabel = $self->_scrubTextInput( $self->input->{'plabel'} );
    my $parentID = $self->_getTmsVehicleGroupByLabel( $parentLabel );

    if ( not $parentID ) {
        die "_addToTMS failed to fetch vehicleGroup parentID by label: $parentLabel";
    }
    warn '_addToTMS parentID: '.$parentID if $logTrace;

    my $label = $self->_scrubTextInput( $self->input->{'label'} );
    my $labelExistsID = $self->_getTmsVehicleGroupByLabel( $label );

    if ( $labelExistsID ) {
        die "_addToTMS failed - label already exists: $label";
    }
    warn "_addToTMS label: $label" if $logTrace;

    my @vehicleConfigStepValues;
    my @configSelectedSteps;
    for my $step ( @VEHICLE_CONFIG_STEPS ) {
        my $checkField = "vehiclegroup-vehicleconfig-$step";
        $checkField = "make" if $step eq "make";

        # skip over non-selected steps
        next unless $self->input->{ $checkField };

        my $stepValue = $self->_scrubTextInput( $self->input->{ $step } );
        warn "_addToTMS vehicleConfig $step: $stepValue" if $logTrace;
        push(@vehicleConfigStepValues, $stepValue);
        push(@configSelectedSteps, $step);
    }

    if (not @configSelectedSteps) {
        die "_addToTMS failed due to no vehicleConfig steps selected";
    }

    my $vehicleConfigID = $self->_createVehicleConfig({
        'steps' => \@configSelectedSteps,
        'detailVehicle' => 0,
    });
    warn "_addToTMS vehicleConfigID: $vehicleConfigID" if $logTrace;

    if ( $makeConfig->{'type'} eq "vehicleSelector" ) {

        my @selectorSelectedSteps;
        for my $step ( @{ $makeConfig->{'steps'} } ) {
            my $checkField = "vehiclegroup-vehicleselector-$step";
            $checkField = "make" if $step eq "make";

            # skip over non-selected steps
            next unless $self->input->{ $checkField };

            my $stepValue = $self->_scrubTextInput( $self->input->{ $step } );
            warn "_addToTMS vehicleSelector $step: $stepValue" if $logTrace;
            push(@selectorSelectedSteps, $step);
        }
        warn '_addToTMS selectorSelectedSteps: '.join(',', @selectorSelectedSteps) if $logTrace;

        my $vehicleSelectorID = $self->_createVehicleSelector({
            'steps' => \@selectorSelectedSteps,
            'vehicleConfigID' => $vehicleConfigID,
            'makeVehicleConfigID' => 0,
        });
        warn "_addToTMS vehicleSelectorID: $vehicleSelectorID" if $logTrace;
    }

    my $shortURL = (
        $TMS_VEHICLE_GROUP_URL_PREFIX
        .$self->_scrubTextInput( $self->input->{'tmsVehicleGroupUrl'} )
    );
    warn "_addToTMS shortURL: $shortURL" if $logTrace;

    my $iconName;
    if ( $self->input->{'iconFilename'} ) {
        $iconName = "icon_".uc($shortURL);
        $iconName =~ s/\-/_/g;
        $iconName =~ s/_$//;
        my $file = $self->input->{'iconFilename'};
        my (undef,undef,$ext) = fileparse($file,qr{\..*});
        $iconName = $iconName . $ext;
        $self->input->{'iconName'} = $iconName;
        $self->uploadIcon({ 
            'envelope' => $envelope,
            'fileName' => $iconName,
        });
    }

    my $sortOrder = $self->_getTmsVehicleGroupNextSortOrder( $parentID );
    warn "_addToTMS sortOrder: $sortOrder" if $logTrace;

    my $query = "INSERT INTO
            vehicleGroup
        SET
            parentID = ?,
            sortOrder = ?,
            label = ?,
            shortURL = ?,
            iconFilename = ?,
            vehicleConfigID = ?";

    my $sth = $site->site_dbh->prepare($query);
    $sth->execute(
        $parentID,
        $sortOrder,
        $label,
        $shortURL,
        $iconName,
        $vehicleConfigID,
    );

    return $sth->{'mysql_insertid'};
}

###############################################################################

sub uploadIcon {
    my $self = shift;
    my ($p) = @_;

    my $envelope = $p->{'envelope'}
        or die "envelope required";
    my $fileName = $p->{'fileName'}
        or die "fileName required";

    my $result = {
        'success'	=> 1,
        'update_DB'	=> $Config->{update_DB},
        'update_cache'	=> $Config->{update_cache},
        'write_cloud'	=> $Config->{write_cloud},
        'error'		=> undef,
        'response'	=> {
            'html'		=> $self->input->{'html'},
            'error'		=> $self->input->{'error'},
        }
    };

    my $tmpDir = "/tmp";
    my $tmpFilePath = $tmpDir."/tms_vehicle_icon_".$$."_".$fileName;

    open(my $of, ">$tmpFilePath") || die "Cannot open file for writing ($tmpFilePath)";
    my @parts = $envelope->parts();
    my $part = $parts[0]->[0];
    print $of $part->bodyhandle->as_string;
    close($of);


    my $s3 = ECS::CDN::S3->new();
    my $container = 'static_tms';
    if($Config->{write_cloud}){
        my $c_object_name = $s3->build_path( $container => "images/tms_subcat_icons/$fileName" );
        $s3->put_file( $c_object_name, $tmpFilePath );

    }

    return $result;
}

###############################################################################

sub getNavOptions {
    my $self = (shift)->new({'input' => shift || {}});

    my @where = ();
    my @argv = ();
    for(1 .. 6) {
        my $field = "L$_";
        if($self->input->{$field}) {
            push(@where, "$field = ?");
            push(@argv, $self->input->{$field});
        }
    }

    my $navField = $self->input->{'curNav'};
    $navField =~ s/\W//g;

    my $query = "SELECT
            DISTINCT $navField
        FROM
            vehicleNav
        WHERE
            $navField <> ''
            AND " . join("\nAND ", @where);

    my $sth = $self->dbhr->prepare($query);
    $sth->execute(@argv);

    my @options = ();
    while(my $option = $sth->fetchrow_array()) {
        push(@options, $option);
    }

    $self->data->{'dataType'} = "json";
    $self->data->{'data'} = \@options;

    return $self->result;
}

###############################################################################

sub getSelectorOptions {
    my $self = (shift)->new({'input' => shift || {}});

    my $fieldName = $self->input->{'fieldName'};
    my $makeID = $self->input->{'makeID'};
    my $makeConfig = $ECS::Vehicle::MAKES{ $makeID };

    my @where = ( 'vehicleConfig.makeID' );
    my @argv = ( $makeID );
    for my $step ( @ECS::Vehicle::SHORT_STEPS ) {
        next if $step eq "makeID";
        push(@where, "vehicleConfig.$step");
        push(@argv, 0);
    }

    for my $step (@{ $makeConfig->{'steps'} }) {
        my $field = $step."ID";
        if ( my $val = $self->input->{ $field } ) {
            push(@where, "vehicleSelector.$field");
            push(@argv, $val);
        }
    }

    my $sth = $self->dbhr->prepare(
        "SELECT
            ${fieldName}ID AS value,
            ${fieldName}Name AS label
        FROM vehicleSelector
            JOIN $fieldName USING ( ${fieldName}ID )
            JOIN vehicleConfig ON (
                vehicleSelector.makeVehicleConfigID = vehicleConfig.vehicleConfigID
            )
        WHERE ".join(" AND ", map { "$_ = ?" } @where)."
        GROUP BY ${fieldName}ID
        ORDER BY ${fieldName}Name"
    );
    $sth->execute( @argv );
    my $options = $sth->fetchall_arrayref( {} );

    $self->data->{'dataType'} = "json";
    $self->data->{'data'} = $options;

    return $self->result;
}

###############################################################################

sub _getCategoryID {
    my $self = shift;
    my $category = shift;
    my $value = shift;

    $category =~ s/\W//g;

    my $query = "SELECT
            " . $category . "ID
        FROM
            $category
        WHERE
            " . $category . "Name = ?";

    my $sth = $self->dbhr->prepare($query);
    $sth->execute($value);

    if($sth->rows) {
        my $categoryID = $sth->fetchrow_array();
        return $categoryID;
    }

    $query = "INSERT INTO
            $category
        SET
            " . $category . "Name  = ?";

    my @argv = ($value);

    if($category eq "year") {
        $query .= ", yearID = ?";
        push(@argv, $value);
    }

    $sth = $self->dbh->prepare($query);
    $sth->execute(@argv);

    return $self->dbh->{'mysql_insertid'} || $value;
}

###############################################################################

sub _getVehicleConfigID {
    my $self = shift;
    my $categories = shift;

    my @where = ();
    my @argv = ();
    for my $step ( @VEHICLE_CONFIG_STEPS ) {
        my $field = $step."ID";
        if($categories->{$field}) {
            push(@where, "$field = ?");
            push(@argv, $categories->{$field});
        }
        else {
            push(@where, "$field = 0");
        }
    }

    my $query = "SELECT
            vehicleConfigID
        FROM
            vehicleConfig
        WHERE
            " . join("\nAND ", @where);

    my $sth = $self->dbh->prepare($query);
    $sth->execute(@argv);

    my $vehicleConfigID = $sth->fetchrow_array();

    return $vehicleConfigID;
}

###############################################################################

sub _getTmsVehicleGroupByLabel {
    my $self = shift;
    my ($label) = @_;

    die "label must be specified"
        unless defined $label;

    my $site = ECS::Site::fromName("TMS");
    my $sth = $site->site_dbhr->prepare(
        "SELECT
            vehicleGroupID
        FROM
            vehicleGroup
        WHERE
            label = ?"
    );
    $sth->execute( $label );
    my ($vehicleGroupID) = $sth->fetchrow_array;

    return $vehicleGroupID;
}

###############################################################################

sub _getTmsVehicleGroupByURL {
    my $self = shift;
    my ($shortURL) = @_;

    die "shortURL must be specified"
        unless defined $shortURL;

    my $site = ECS::Site::fromName("TMS");
    my $sth = $site->site_dbhr->prepare(
        "SELECT
            vehicleGroupID
        FROM
            vehicleGroup
        WHERE
            shortURL = ?"
    );
    $sth->execute( $shortURL );
    my ($vehicleGroupID) = $sth->fetchrow_array;

    return $vehicleGroupID;
}

###############################################################################

sub _getTmsVehicleGroupNextSortOrder {
    my $self = shift;
    my ( $parentID ) = @_;

    die "parentID required" unless $parentID;

    my $site = ECS::Site::fromName("TMS");
    my $sth = $site->site_dbhr->prepare(
        "SELECT max(sortOrder)
        FROM vehicleGroup
        WHERE parentID = ?"
    );
    $sth->execute( $parentID );
    my $sortOrder = $sth->fetchrow_array();

    return 0 unless defined $sortOrder;
    return $sortOrder + 10;
}

###############################################################################

sub _scrubTextInput {
    my $self = shift;
    my ( $input ) = @_;
    $input =~ s/^\s+|\s+$//g; # trim leading and trailing whitespace
    return $input;
}

###############################################################################

1;
