#!/usr/bin/perl

use strict;
use warnings;

package API::PurchasingImportsAdmin;

use Archive::Zip qw(AZ_OK);
use File::Slurp ();
use File::Temp;
use List::MoreUtils qw/uniq firstidx/;
use MIME::Base64;
use MIME::Entity;
use MIME::Lite;
use SOAP::Lite ();
use base 'SOAP::Server::Parameters';
use Text::CSV;

use ECS::Config;
use ECS::Site;
use ECS::SQL;

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

my $website_config = \%ECS::Config::c;

return 1;


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

sub tariffNOLCImport {
    my $self = shift;
    my $input = shift;
    my $envelope = pop;

    my $result = {
        'success' => 1,
        'error' => undef,
        'data' => {},
        'update_DB' => $Config->{'update_DB'}
    };

    my @parts = $envelope->parts();
    my $file;

    # grab attachment from request
    if (
        defined($parts[0])
        and defined($parts[0]->[0])
    ) {
        $file = $parts[0]->[0];
    } else {
        return $self->_markError($result, 'No file provided');
    }

    # open mime attachment bodyhandle and read into zip file
    my $fh = $file->bodyhandle->open('r');
    my $zip = Archive::Zip->new();
    unless($zip->read($fh) == 0) {
        return $self->_markError($result, 'Failed to open file');
    }

    # identify relevant csv file in zip file
    my $member = $zip->memberNamed('file.csv');
    my $tmp = File::Temp->new( SUFFIX => '.csv' );

    # extract csv from zip to file handle for reading
    unless($member->extractToFileHandle($tmp) == 0) {
        return $self->_markError($result, 'Failed to open file');
    }

    seek($tmp, 0, 0);

    # format into Text::CSV for processing
    my $csv = Text::CSV->new({
        'binary' => 1,
    });
    my $header_line = <$tmp>;
    $csv->parse($header_line);
    my @headers = $csv->fields();

    return $self->_markError($result, 'File is missing header row') if !@headers;

    my @expectedHeaders = ('productID', 'mm_VendorID', 'nextOrderLandedCost', 'tariffRate');
    for my $header (@headers) {
        return $self->_markError($result, 'File contains erroneous header field \'' . $header . '\'') if !(grep(/^$header$/, @expectedHeaders));
    }
    
    for my $expectedHeader (@expectedHeaders) {
        return $self->_markError($result, 'File is missing required header field \'' . $expectedHeader . '\'') if !(grep(/^$expectedHeader$/, @headers));
    }

    return $self->_markError($result, 'Too many header fields on file') if (scalar @headers != scalar @expectedHeaders);

    my @rows = ();
    while (<$tmp>) {
        my $rowInfo;
        my $parse = $csv->parse($_);
        my @row = $csv->fields();
        # for each field, grab the index of the row that matches the index of the corresponding header (allows headers to be in any order)
        $rowInfo->{'productID'} = @row[ firstidx { $_ eq 'productID' } @headers ];
        $rowInfo->{'vendorID'} = @row[ firstidx { $_ eq 'mm_VendorID' } @headers ];
        $rowInfo->{'nextOrderLandedCost'} = @row[ firstidx { $_ eq 'nextOrderLandedCost' } @headers ];
        $rowInfo->{'tariffRate'} = @row[ firstidx { $_ eq 'tariffRate' } @headers ];

        push(@rows, $rowInfo);
    }

    # implement a row limitation here?

    for my $row (@rows) {
        # no info in the row, don't try to update
        next if ($row->{'tariffRate'} eq '' && $row->{'nextOrderLandedCost'} eq '');

        my $sqlSetString = '';
        my @executeParams;
        if ($row->{'tariffRate'} ne '' && $row->{'nextOrderLandedCost'} ne '') {
            $sqlSetString = 'productVendor.tariffRate = ?, productVendor.nextOrderLandedCostOverride = ?';
            push(@executeParams, $row->{'tariffRate'});
            push(@executeParams, $row->{'nextOrderLandedCost'});
        } elsif ($row->{'tariffRate'} ne '') {
            $sqlSetString = 'productVendor.tariffRate = ?';
            push(@executeParams, $row->{'tariffRate'});
        } elsif ($row->{'nextOrderLandedCost'} ne '') {
            $sqlSetString = 'productVendor.nextOrderLandedCostOverride = ?';
            push(@executeParams, $row->{'nextOrderLandedCost'});
        }

        push(@executeParams, $row->{'productID'});
        push(@executeParams, $row->{'vendorID'});
        # perform DB updates
        my $sql = "
            UPDATE productVendor
            JOIN vendor
            USING (vendorID)
            SET " . $sqlSetString .
            " WHERE productVendor.productID = ? AND vendor.mm_VendorID = ? 
        ";
        my $dbh = ECS::Site::fromName(ECS)->estore_dbh;
        my $sth = $dbh->prepare($sql);
        $sth->execute(@executeParams);
    }

    return $result;
}

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

sub freightRateShipTypeImport {
    my $self = shift;
    my $input = shift;
    my $envelope = pop;

    my $result = {
        'success' => 1,
        'error' => undef,
        'data' => {},
        'update_DB' => $Config->{'update_DB'}
    };

    my @parts = $envelope->parts();
    my $file;

    # grab attachment from request
    if (
        defined($parts[0])
        and defined($parts[0]->[0])
    ) {
        $file = $parts[0]->[0];
    } else {
        return $self->_markError($result, 'No file provided');
    }

    # open mime attachment bodyhandle and read into zip file
    my $fh = $file->bodyhandle->open('r');
    my $zip = Archive::Zip->new();
    unless($zip->read($fh) == 0) {
        return $self->_markError($result, 'Failed to open file');
    }

    # identify relevant csv file in zip file
    my $member = $zip->memberNamed('file.csv');
    my $tmp = File::Temp->new( SUFFIX => '.csv' );

    # extract csv from zip to file handle for reading
    unless($member->extractToFileHandle($tmp) == 0) {
        return $self->_markError($result, 'Failed to open file');
    }

    seek($tmp, 0, 0);

    # format into Text::CSV for processing
    my $csv = Text::CSV->new({
        'binary' => 1,
    });
    my $header_line = <$tmp>;
    $csv->parse($header_line);
    my @headers = $csv->fields();

    return $self->_markError($result, 'File is missing header row') if !@headers;

    my @expectedHeaders = ('mm_VendorID', 'inboundFreightRate', 'shippingType');
    for my $header (@headers) {
        return $self->_markError($result, 'File contains erroneous header field \'' . $header . '\'') if !(grep(/^$header$/, @expectedHeaders));
    }
    
    for my $expectedHeader (@expectedHeaders) {
        return $self->_markError($result, 'File is missing required header field \'' . $expectedHeader . '\'') if !(grep(/^$expectedHeader$/, @headers));
    }

    return $self->_markError($result, 'Too many header fields on file') if (scalar @headers != scalar @expectedHeaders);

    my @rows = ();
    while (<$tmp>) {
        my $rowInfo;
        my $parse = $csv->parse($_);
        my @row = $csv->fields();
        # for each field, grab the index of the row that matches the index of the corresponding header (allows headers to be in any order)
        $rowInfo->{'vendorID'} = @row[ firstidx { $_ eq 'mm_VendorID' } @headers ];
        $rowInfo->{'inboundFreightRate'} = @row[ firstidx { $_ eq 'inboundFreightRate' } @headers ];
        $rowInfo->{'shippingType'} = @row[ firstidx { $_ eq 'shippingType' } @headers ];

        push(@rows, $rowInfo);
    }

    # implement a row limitation here?

    # pull vendorShippingTypeIDs
    my $dbh = ECS::Site::fromName(ECS)->estore_dbh;
    my $sql = "SELECT * FROM vendorShippingType";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $shippingTypes = $sth->fetchall_arrayref({});

    # validate shippingTypes
    my $rowNum = 2;
    for my $row (@rows) {
        my $shippingTypeID;
        for my $shippingType (@{ $shippingTypes }) {
            if ($row->{'shippingType'} eq $shippingType->{'name'}) {
                $shippingTypeID = $shippingType->{'vendorShippingTypeID'};
            }
        }

        return $self->_markError($result, 'Invalid shippingType "' . $row->{'shippingType'} . '" provided on line ' . $rowNum) if !$shippingTypeID && $row->{'shippingType'} != '';

        $rowNum++;

        $row->{'shippingType'} = $shippingTypeID;
    }

    for my $row (@rows) {
        # no info in the row, don't try to update
        next if ($row->{'inboundFreightRate'} eq '' && $row->{'shippingType'} eq '');

        my $sqlSetString = '';
        my @executeParams;
        if ($row->{'inboundFreightRate'} ne '' && $row->{'shippingType'} ne '') {
            $sqlSetString = 'inboundFreightRate = ?, vendorShippingTypeID = ?';
            push(@executeParams, $row->{'inboundFreightRate'});
            push(@executeParams, $row->{'shippingType'});
        } elsif ($row->{'inboundFreightRate'} ne '') {
            $sqlSetString = 'inboundFreightRate = ?';
            push(@executeParams, $row->{'inboundFreightRate'});
        } elsif ($row->{'shippingType'} ne '') {
            $sqlSetString = 'vendorShippingTypeID = ?';
            push(@executeParams, $row->{'shippingType'});
        }

        push(@executeParams, $row->{'vendorID'});
        # perform DB updates
        $sql = "
            UPDATE vendor
            SET " . $sqlSetString .
            " WHERE mm_VendorID = ? 
        ";
        $sth = $dbh->prepare($sql);
        $sth->execute(@executeParams);
    }

    return $result;
}

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

sub shipTypeImport {
    my $self = shift;
    my $input = shift;
    my $envelope = pop;

    my $result = {
        'success' => 1,
        'error' => undef,
        'data' => {},
        'update_DB' => $Config->{'update_DB'}
    };

    my @parts = $envelope->parts();
    my $file;

    # grab attachment from request
    if (
        defined($parts[0])
        and defined($parts[0]->[0])
    ) {
        $file = $parts[0]->[0];
    } else {
        return $self->_markError($result, 'No file provided');
    }

    # open mime attachment bodyhandle and read into zip file
    my $fh = $file->bodyhandle->open('r');
    my $zip = Archive::Zip->new();
    unless($zip->read($fh) == 0) {
        warn('failed to read');
        return $self->_markError($result, 'Failed to open file');
    }

    # identify relevant csv file in zip file
    my $member = $zip->memberNamed('file.csv');
    my $tmp = File::Temp->new( SUFFIX => '.csv' );

    # extract csv from zip to file handle for reading
    unless($member->extractToFileHandle($tmp) == 0) {
        warn('failed to extract');
        return $self->_markError($result, 'Failed to open file');
    }

    seek($tmp, 0, 0);

    # format into Text::CSV for processing
    my $csv = Text::CSV->new({
        'binary' => 1,
    });
    my $header_line = <$tmp>;
    $csv->parse($header_line);
    my @headers = $csv->fields();

    return $self->_markError($result, 'File is missing header row') if !@headers;

    my @expectedHeaders = ('productID', 'mm_VendorID', 'shippingType');
    for my $header (@headers) {
        return $self->_markError($result, 'File contains erroneous header field \'' . $header . '\'') if !(grep(/^$header$/, @expectedHeaders));
    }
    
    for my $expectedHeader (@expectedHeaders) {
        return $self->_markError($result, 'File is missing required header field \'' . $expectedHeader . '\'') if !(grep(/^$expectedHeader$/, @headers));
    }

    return $self->_markError($result, 'Too many header fields on file') if (scalar @headers != scalar @expectedHeaders);

    my @rows = ();
    while (<$tmp>) {
        my $rowInfo;
        my $parse = $csv->parse($_);
        my @row = $csv->fields();
        # for each field, grab the index of the row that matches the index of the corresponding header (allows headers to be in any order)
        $rowInfo->{'productID'} = @row[ firstidx { $_ eq 'productID' } @headers ];
        $rowInfo->{'vendorID'} = @row[ firstidx { $_ eq 'mm_VendorID' } @headers ];
        $rowInfo->{'shippingType'} = @row[ firstidx { $_ eq 'shippingType' } @headers ];

        push(@rows, $rowInfo);
    }

    # implement a row limitation here?

    # pull vendorShippingTypeIDs
    my $dbh = ECS::Site::fromName(ECS)->estore_dbh;
    my $sql = "SELECT * FROM vendorShippingType";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $shippingTypes = $sth->fetchall_arrayref({});

    # validate shippingTypes
    my $rowNum = 2;
    for my $row (@rows) {
        my $shippingTypeID;
        for my $shippingType (@{ $shippingTypes }) {
            if ($row->{'shippingType'} eq $shippingType->{'name'}) {
                $shippingTypeID = $shippingType->{'vendorShippingTypeID'};
            }
        }


        return $self->_markError($result, 'Invalid shippingType "' . $row->{'shippingType'} . '" provided on line ' . $rowNum) if !$shippingTypeID && $row->{'shippingType'} ne '';
        return $self->_markError($result, 'Missing shippingType on line ' . $rowNum) if $row->{'shippingType'} eq '';

        $rowNum++;

        $row->{'shippingType'} = $shippingTypeID;
    }

    for my $row (@rows) {
        # no info in the row, don't try to update
        next if ($row->{'shippingType'} eq '');

        # perform DB updates
        $sql = "
            UPDATE productVendor
            JOIN vendor
            USING (vendorID)
            SET productVendor.vendorShippingTypeIDOverride = ?
            WHERE productVendor.productID = ? AND vendor.mm_VendorID = ? 
        ";
        $sth = $dbh->prepare($sql);
        $sth->execute($row->{'shippingType'}, $row->{'productID'}, $row->{'vendorID'});
    }

    return $result;
}

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

# helper function for marking a result as errored, makes error logging cleaner in functions
sub _markError {
    my $self = shift;
    my $result = shift;
    my $errorMessage = shift;

    $result->{'success'} = 0;
    $result->{'error'} = $errorMessage;

    return $result;
}

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

sub _getVendorShippingTypes {
    my $self = shift;

    my $dbh = ECS::Site::fromName(ECS)->estore_dbh;
    my $sql = "SELECT * FROM vendorShippingType";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $shippingTypes = $sth->fetchall_arrayref({});

    return $shippingTypes;
}

1;