#!/usr/bin/perl -Ilibs
use strict;
use warnings;

package API::Warehouse::User;

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

use ECS::SQL;
use ECS::Error;

use List::Util ();

sub getPickersPackers {
	my $self = shift;
	my $input = shift;
	
	my $DBH = ECS::SQL::connect('SLAVE');
	
	my $response = {
		'success' => 1,
		'error' => undef,
		'data' => {},
		'update_DB' => $Config->{'update_DB'}
	};
	
	my $sql = "
		SELECT
			scanner_user.name
		FROM scanner_user
			JOIN scanner_role ON (scanner_user.id = scanner_role.suID)
		WHERE
			scanner_user.active = 'Y'
			AND scanner_role.role = 'PickPack'
		ORDER BY name
	";
	
	my $rd = ECS::SQL::query({ 'DBH' => $DBH, 'sql' => $sql, useSlave => 1 });
	
	if (!$rd) {
		$response->{'success'} = 0;
		$response->{'error'} = 'Could not retrieve user list';
		return $response;
	}
	
	my @users;
	$response->{'data'}->{'users'} = \@users;
	
	foreach my $user (@{ $rd }) {
		push(@users, $user->{'name'});
	}
	
	$sql = "
		SELECT
			warehouseStationID, COALESCE(IF(tmpLabelPrinter = '',NULL,tmpLabelPrinter),labelPrinter) as labelPrinter, formsPrinter, packingStationID, tmpLabelPrinter, shippingLabelPrinter
		FROM warehouseStation
		ORDER BY warehouseStationID
	";

	$rd = ECS::SQL::query({ 'DBH' => $DBH, 'sql' => $sql, useSlave => 1 });
	
	if (!$rd) {
		$response->{'success'} = 0;
		$response->{'error'} = 'Could not retrieve warehouseStation list',
		return $response;
	}
	
    my @warehouseStations;
	$response->{'data'}->{'warehouseStations'} = \@warehouseStations;

	foreach my $warehouseStation (@{ $rd }) {
        push(@warehouseStations, $warehouseStation);
	}

	return $response;
}


sub getUserID {
	my $self = shift;
	my $input = shift;
	
	my $DBH = ECS::SQL::connect('SLAVE');
	
	my $response = {
		'success' => 1,
		'error' => undef,
		'data' => {},
		'update_DB' => $Config->{'update_DB'}
	};
	
	my $sql = "
		SELECT userID
		FROM scanner_user
		WHERE name = ?
		LIMIT 1
	";
	
	my $getUser = $DBH->prepare($sql);
	$getUser->execute($input->{'user'});
	
	my @row = $getUser->fetchrow_array;
	
	if(@row){
		$response->{'data'}->{'userID'} = $row[0];
	}
	
	return $response;
}


# API method
# returns the users and number of orders/items packed for the current date
sub getPackedCounts {
    my $self = shift;
    my $input = shift;

    my $DBH = ECS::SQL::connect('SLAVE');

	my $response = {
		'success' => 1,
		'error' => undef,
		'data' => {},
		'update_DB' => $Config->{'update_DB'}
	};
    
    $response->{'data'}->{'counts'} = _doPackCounts({
        'DBH' => $DBH,
    });

    return $response;
}

# API method
# returns the users and number of orders/items picked for the current date
sub getPickedCounts {
    my $self = shift;
    my $input = shift;

    my $DBH = ECS::SQL::connect('SLAVE');

	my $response = {
		'success' => 1,
		'error' => undef,
		'data' => {},
		'update_DB' => $Config->{'update_DB'}
	};
    
    $response->{'data'}->{'counts'} = doPickCounts({
        'DBH'    => $DBH,
    });

    $response->{'data'}->{'mispickCounts'} = doMispickCounts({
        'DBH' => $DBH,
    });

    return $response;
}

sub _doPackCounts {
    my ( $p ) = @_;

    my $dbh = ECS::SQL::toDBI( $p );

    my $sqlCreateTable = q{
        CREATE TEMPORARY TABLE dev_estore.tmp_countOrders (
            orderID INT UNSIGNED NOT NULL,
            PRIMARY KEY ( orderID )
        ) ENGINE = MEMORY
    };
    my $sqlDropTable = q{
        DROP TEMPORARY TABLE IF EXISTS dev_estore.tmp_countOrders
    };
    my $sqlPopulateOrders = q{
        INSERT IGNORE INTO dev_estore.tmp_countOrders (
            orderID
        )
        SELECT
            orderID
        FROM orderstatus
        WHERE
            status IN ( 'Shipped' )
            AND timestamp > CURDATE()
    };
    my $sqlGetPacks = q{
        SELECT
            orders.packer,
            HOUR( orderShipping.dtShipped ) AS hour
        FROM orders
        JOIN orderShipping
            USING ( orderID )
        JOIN dev_estore.tmp_countOrders
            USING ( orderID )
        JOIN cart
            USING ( orderID )
        JOIN product
            USING ( productID )
        LEFT JOIN productComponent
            ON product.productID = productComponent.productID
            AND product.type = 'Kit'
        WHERE
            orders.packer IS NOT NULL
    };
    my $sthGetPacks = $dbh->prepare( $sqlGetPacks );

    $dbh->do( $sqlDropTable );
    $dbh->do( $sqlCreateTable );
    $dbh->do( $sqlPopulateOrders );
    $sthGetPacks->execute;
    $dbh->do( $sqlDropTable );

    my %packers = ();
    my $minHour = 23;
    my $maxHour = 0;
    while ( my $pack = $sthGetPacks->fetchrow_hashref ) {
        my $packer = $pack->{ 'packer' };
        my $hour   = $pack->{ 'hour' };

        $packers{ $packer } ||= { 'name' => $packer };
        $packers{ $packer }->{ $hour } ++;

        $minHour = $hour if $hour < $minHour;
        $maxHour = $hour if $hour > $maxHour;
    }

    for my $packerName ( keys %packers ) {
        my $packer = $packers{ $packerName };
        my @hours = grep { $_ ne "name" } keys %{ $packer };
        my $total = List::Util::sum( @{ $packer }{ @hours } );
        $packer->{ 'total' } = $total;
    }

    return {
        'hourStart' => $minHour,
        'hourEnd'   => $maxHour,
        'packers'   => [ values %packers ],
    };
}

# Not an API method
sub doPickCounts {
    my $p = shift;

    # select as much data as is needed
    local $p->{'nolimit'} = 1;
    
    local $p->{'sql'} =
        "CREATE TEMPORARY TABLE dev_estore.tmp_countOrders (
            orderID int(10) unsigned NOT NULL,
            PRIMARY KEY (orderID)
        ) ENGINE = MEMORY";
    ECS::SQL::query($p);
    
    $p->{'sql'} =
        "INSERT IGNORE INTO dev_estore.tmp_countOrders (orderID)
        SELECT
            orderID
        FROM
            orderstatus
        WHERE
            status IN ('Picked', 'Picked OSOOB')
            AND timestamp > CURDATE()";
    ECS::SQL::query($p);

    local $p->{'userField'} = 'orders.picker';
    return doUserCountsSum($p);
}

# Not an API method
sub doMispickCounts {
    my $p = shift;

    # select as much data as is needed
    local $p->{'nolimit'} = 1;
    
    local $p->{'sql'} =
        "CREATE TEMPORARY TABLE dev_estore.tmp_countOrders (
            orderID int(10) unsigned NOT NULL,
            picker varchar(16) NOT NULL,
            INDEX `idxOrderID` (orderID)
        ) ENGINE = MEMORY";
    ECS::SQL::query($p);

    $p->{'sql'} =
        "INSERT INTO dev_estore.tmp_countOrders (orderID, picker)
        SELECT
            orderID,
            picker
        FROM
            orderMispick
        WHERE
            dtRepicking > CURDATE()";
    ECS::SQL::query($p);

    local $p->{'userField'} = 'tmp_countOrders.picker';
    return doUserCountsSum($p);
}

# Not an API method
sub doUserCountsSum {
    my $p = shift;

    local $p->{'sql'} = "SELECT
            ". $p->{'userField'} . " AS user,
            orders.orderID,
            SUM(cart.qty * COALESCE(productComponent.qtyRequired, 1)) AS numProducts
        FROM
            orders
            JOIN dev_estore.tmp_countOrders
                USING (orderID)
            JOIN cart
                USING (orderID)
            JOIN product
                USING (productID)
            LEFT JOIN productComponent
                ON product.productID = productComponent.productID
                AND product.type = 'Kit'
        GROUP BY
            cart.cartitemID,
            user";

    my $rs = ECS::SQL::query($p) || [];

    local $p->{'sql'} = 'DROP TEMPORARY TABLE dev_estore.tmp_countOrders';
    ECS::SQL::query($p);

    my %users = ();
    for my $order (@{ $rs }) {
        my $user = ($users{ $order->{'user'} } ||= {});
        $user->{'name'} = $order->{'user'};
        $user->{'orders'}->{ $order->{'orderID'} } = 1;
        $user->{'numProducts'} += $order->{'numProducts'};
    }

    my @users = ();
    for my $user (sort {
            keys %{ $users{$b}->{'orders'} } <=> keys %{ $users{$a}->{'orders'} }
        } keys %users
        ) {
        push @users, $users{$user};
    }

    return \@users;
}

# not an API method
# requires:
#   $p->{'dbh'}
#   $p->{'userID'}
sub getUser {
    my $p = shift;

    my $qUserID = ECS::SQL::quote({'DBH' => $p->{'DBH'}, 'value' => $p->{'userID'}});

    local $p->{'sql'} = "SELECT
            scanner_user.name,
            scanner_user.active,
            scanner_user.manager
        FROM
            scanner_user
        WHERE
            scanner_user.userID = $qUserID";

    my $rs = ECS::SQL::query($p)
        or return;
    return $rs->[0];
}

