<?php

namespace Modules\M3\Console;

use Config;
use DB;
use Illuminate\Console\Command;
use Log;
use Modules\M3\Services\SpreadsheetExport;
use Storage;

class ClosedOrderSaveXls extends Command
{
    private const XLSX_EXTENSION = '.xlsx';

    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'command:ClosedOrderSaveXls {user?} {co?} {whs?} {pl?}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Save Orders Closed Lines to Excel';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $this->configureRuntime();

        try {
            Log::channel('dailyCron')->info('Cron...Start Save Orders Closed Lines to Excel');
            [$company, $warehouse, $plant] = $this->argumentsForReport();

            $this->generatePreviousMonthReport($company, $warehouse, $plant);
            $this->generateCurrentMonthReport($company, $warehouse, $plant);

            Log::channel('dailyCron')->info('Cron...End Save Orders Closed Lines to Excel');
            $this->info('Cron...End Save Orders Closed Lines to Excel');
        } catch (\Throwable $th) {
            Log::channel('dailyCron')->error($th);

            return self::FAILURE;
        }

        return self::SUCCESS;
    }

    private function configureRuntime(): void
    {
        ini_set('max_execution_time', Config::get('arrays.PHPTimeOutCL'));
        ini_set('max_input_time', Config::get('arrays.PHPTimeOutCL'));
        ini_set('default_socket_timeout', Config::get('arrays.PHPTimeOutCL'));
        ini_set('memory_limit', '1024M');
    }

    private function argumentsForReport(): array
    {
        return [
            trim((string) $this->argument('co')),
            trim((string) $this->argument('whs')),
            trim((string) $this->argument('pl')),
        ];
    }

    private function generatePreviousMonthReport(string $company, string $warehouse, string $plant): void
    {
        $date = new \DateTime('FIRST DAY OF THIS MONTH');
        $date->modify('-1 month');
        $from = $date->format('Y-m-d');
        $to = $date->modify('last day of this month')->format('Y-m-d');
        $file = $warehouse === '321'
            ? 'Closedorderlinesreport_' . $from . '-' . $to . self::XLSX_EXTENSION
            : $warehouse . '/Closedorderlinesreport_' . $from . '-' . $to . '-' . $warehouse . self::XLSX_EXTENSION;

        $this->info('Generate Last Month Report...');
        $this->generateReport($company, $warehouse, $plant, [
            'from' => str_replace('-', '', $from),
            'to' => str_replace('-', '', $to),
            'file' => $file,
            'shouldGenerate' => ! Storage::disk('publicCL')->exists($file),
            'label' => 'Last Month',
        ]);
    }

    private function generateCurrentMonthReport(string $company, string $warehouse, string $plant): void
    {
        $now = new \DateTime;
        $from = $now->format('Y-m-01');
        $to = $now->format('Y-m-d');
        $file = $warehouse === '321'
            ? 'first_of_month_to_current_date.xlsx'
            : $warehouse . '/first_of_month_to_current_date_' . $warehouse . self::XLSX_EXTENSION;

        $this->info('Generate Current Month Report...');
        $this->generateReport($company, $warehouse, $plant, [
            'from' => str_replace('-', '', $from),
            'to' => str_replace('-', '', $to),
            'file' => $file,
            'shouldGenerate' => true,
            'label' => 'Current Month',
        ]);
    }

    private function generateReport(
        string $company,
        string $warehouse,
        string $plant,
        array $report,
    ): void {
        $this->info('From Date: ' . $report['from']);
        $this->info('To Date: ' . $report['to']);

        if (! $report['shouldGenerate'] || ! $this->hasReportScope($company, $warehouse, $plant)) {
            return;
        }

        $rows = $this->closedOrderRows($company, $warehouse, $plant, $report['from'], $report['to']);
        if ($rows->isEmpty()) {
            Log::channel('dailyCron')->info($report['label'] . ' report, no records found: Nothing to save...');

            return;
        }

        $rows->prepend($this->headers());
        $this->logRecordLimit($rows->count());
        $this->info('Saving ' . $report['label'] . ' Excel Report to Disk');
        Log::channel('dailyCron')->info('Saving ' . $report['label'] . ' Excel Report to Disk');
        app(SpreadsheetExport::class)->store($rows, $report['file'], 'publicCL');
    }

    private function hasReportScope(string $company, string $warehouse, string $plant): bool
    {
        return $company !== '' && $warehouse !== '' && $plant !== '';
    }

    private function closedOrderRows(string $company, string $warehouse, string $plant, string $from, string $to)
    {
        $ltlFactor = (100.00 - Config::get('arrays.misc.discountCarrierRateForCustomerPriceLTL')) / 100;
        $parcelFactor = (100.00 - Config::get('arrays.misc.discountCarrierRateForCustomerPriceParcel')) / 100;

        $sql = <<<'SQL'
SELECT (SHDT || ' ' || SHTIME) AS DATECLOSED, A5MACPKR, A5ONO,
    A4OFSTRM, A5PMPWHT*A5PACKED AS WEIGHT, A4SCAC, A4SHIPTYPE,
    (SELECT A4REFORD FROM ICM688M1 WHERE A4MACPKR=A5MACPKR) AS A4REFORD,
    A5PART, A5RSVQTY, A5PACKED, A5RSVQTY - A5PACKED AS CALCULATED_COLUMN,
    A5SLUNPR * A5PACKED AS ANOTHER_CALCULATED_COLUMN,
    (SELECT A4SHSHNM FROM ICM688M1 WHERE A4MACPKR=A5MACPKR) AS A4SHSHNM,
    CASE WHEN SHDT IS NULL THEN
        (SELECT SHDATA FROM ICM699M WHERE SHMACPKR=A5MACPKR AND SHTYPE='M3-Res-out'
         AND SHSOURCE='CloseShipmentCoChargeAPI' ORDER BY SHDT DESC, SHTIME DESC FETCH FIRST 1 ROWS ONLY)
    ELSE '' END AS REASON,
    A1SHCURR,
    CASE WHEN (SELECT A4SHIPTYPE FROM ICM688M1 WHERE A4MACPKR=A5MACPKR) = 'LTL'
        THEN (SELECT A4RATETCP FROM ICM688M1 WHERE A4MACPKR=A5MACPKR) * ?
        ELSE (SELECT A4RATETCP FROM ICM688M1 WHERE A4MACPKR=A5MACPKR) * ?
    END AS FREIGHT
FROM ICM688M2
LEFT JOIN ICM699M ON SHMACPKR=A5MACPKR AND SHTYPE='M3-Res-out' AND SHDATA LIKE 'Success%'
    AND SHSOURCE='CloseShipmentCoChargeAPI'
LEFT JOIN ICM666M ON A1DELNO=A5MACPKR AND A1ONO=A5ONO AND A1PKLN=A5BRADLN
LEFT JOIN ICM688M1 ON A4MACPKR=A5MACPKR
WHERE A5MACPKR IN (
    SELECT DISTINCT AYMACPKR FROM ICM695M1
    WHERE AYSTATUS IN ('Z','z') AND AYAPR8N002 BETWEEN ? AND ?
)
AND A5CO=? AND A5WHS=? AND A5PLANT=?
SQL;

        return collect(DB::connection(config('m3.connection', 'pgsql'))->select($sql, [
            $ltlFactor, $parcelFactor, $from, $to, $company, $warehouse, $plant,
        ]));
    }

    private function headers(): array
    {
        return [
            'DATECLOSED' => 'Close Date', 'A5MACPKR' => 'Delivery #', 'A5ONO' => 'Order #',
            'A4OFSTRM' => 'Terms', 'WEIGHT' => 'Weight', 'A4SCAC' => 'Carrier',
            'A4SHIPTYPE' => 'Mode (Parcel, Air, TL or LTL)', 'A4REFORD' => 'Order Type',
            'A5PART' => 'Part #', 'A5RSVQTY' => 'Reserved', 'A5PACKED' => 'Packed',
            'CALCULATED_COLUMN' => 'Back Order Qty', 'ANOTHER_CALCULATED_COLUMN' => 'Value Packed $',
            'A4SHSHNM' => 'Customer name', 'REASON' => 'Failure Reason',
            'A1SHCURR' => 'Currency Code', 'FREIGHT' => 'Freight',
        ];
    }

    private function logRecordLimit(int $count): void
    {
        $maxRecords = (int) Config::get('arrays.maxRecCL');
        if ($count <= $maxRecords) {
            return;
        }

        Log::channel('dailyCron')->info(
            'Maximum allowed records for Excel export is: ' . number_format($maxRecords)
            . ' current records: ' . number_format($count),
        );
    }
}