<?php

declare(strict_types=1);

use App\Models\User;
use App\Models\Warehouse\IndirectGoods\IndirectGood;
use App\Models\Warehouse\IndirectGoods\IndirectGoodsInventory;
use App\Models\Warehouse\IndirectGoods\IndirectGoodsStockMovement;
use App\Models\Warehouse\ItemCategory;
use App\Models\Warehouse\ProjectDivision;
use App\Models\Warehouse\Unit;
use Illuminate\Contracts\Console\Kernel;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\IOFactory;

$basePath = dirname(__DIR__);
$workbookPath = $argv[1] ?? '/Users/shafeekelayadath/Downloads/CRES_DCR_items_import_template_Consumables_INDIRECT GOODS_FOC_NO COST.xlsx';
$divisionName = $argv[2] ?? 'CRES DUBAI';
$importReference = $argv[3] ?? pathinfo($workbookPath, PATHINFO_FILENAME);
$batchNumber = $argv[4] ?? 'CRES-DCR-OPENING-STOCK';

$workbookPath = str_starts_with($workbookPath, '/') ? $workbookPath : $basePath . '/' . $workbookPath;

if (! file_exists($workbookPath)) {
    fwrite(STDERR, "Workbook not found: {$workbookPath}\n");
    exit(1);
}

require $basePath . '/vendor/autoload.php';
$app = require $basePath . '/bootstrap/app.php';
$app->make(Kernel::class)->bootstrap();

function textValue(mixed $value): ?string
{
    $value = trim((string) $value);

    return $value === '' ? null : $value;
}

function decimalValue(mixed $value): float
{
    $value = str_replace(',', '', trim((string) $value));

    return is_numeric($value) ? (float) $value : 0.0;
}

function unitForImport(string $unitName): Unit
{
    $unit = Unit::whereRaw('LOWER(unit_name) = LOWER(?)', [$unitName])
        ->orWhereRaw('LOWER(unit_symbol) = LOWER(?)', [$unitName])
        ->orWhereRaw('LOWER(unit_code) = LOWER(?)', [$unitName])
        ->first();

    if ($unit) {
        return $unit;
    }

    $unitCode = strtoupper(substr(preg_replace('/[^A-Za-z0-9]/', '', $unitName), 0, 3));
    $unitCode = $unitCode !== '' ? $unitCode : 'UNT';
    $baseCode = $unitCode;
    $counter = 1;

    while (Unit::where('unit_code', $unitCode)->exists()) {
        $unitCode = $baseCode . $counter;
        $counter++;
    }

    return Unit::create([
        'unit_code' => $unitCode,
        'unit_name' => $unitName,
        'unit_symbol' => $unitName,
        'unit_type' => 'general',
        'description' => 'Auto-created during CRES indirect goods import',
        'status' => 'active',
    ]);
}

$spreadsheet = IOFactory::load($workbookPath);
$sheet = $spreadsheet->getSheetByName('Data') ?? $spreadsheet->getActiveSheet();
$rows = $sheet->toArray(null, true, true, true);

$expectedHeaders = [
    'A' => 'item_code',
    'B' => 'item_description',
    'C' => 'type_of_goods',
    'D' => 'category_code',
    'E' => 'category_description',
    'F' => 'unit_of_measurement',
    'G' => 'Current Stock',
    'H' => 'min_stock_level',
    'I' => 'max_stock_level',
    'J' => 'reorder_point',
    'K' => 'status',
    'L' => 'notes',
];

foreach ($expectedHeaders as $column => $label) {
    if (($rows[1][$column] ?? null) !== $label) {
        fwrite(STDERR, "Unexpected workbook header at column {$column}: expected {$label}, found " . ($rows[1][$column] ?? '') . "\n");
        exit(1);
    }
}

$division = ProjectDivision::where('division_name', $divisionName)->first();
$user = User::orderBy('id')->first();

if (! $division) {
    fwrite(STDERR, "{$divisionName} division was not found.\n");
    exit(1);
}

if (! $user) {
    fwrite(STDERR, "No user found for stock movement audit rows.\n");
    exit(1);
}

$createdGoods = 0;
$updatedGoods = 0;
$inventoryRows = 0;
$stockMovements = 0;
$categoriesTouched = [];
$unitsCreated = 0;
$openingStockTotal = 0.0;

DB::beginTransaction();

try {
    foreach ($rows as $rowNumber => $row) {
        if ($rowNumber === 1 || ! textValue($row['B'] ?? null)) {
            continue;
        }

        $description = textValue($row['B'] ?? null);
        $typeOfGoods = textValue($row['C'] ?? null);
        $categoryCode = textValue($row['D'] ?? null);
        $categoryName = textValue($row['E'] ?? null);
        $unitName = textValue($row['F'] ?? null);
        $openingStock = decimalValue($row['G'] ?? null);
        $minStock = decimalValue($row['H'] ?? null);
        $maxStock = decimalValue($row['I'] ?? null);
        $reorderPoint = decimalValue($row['J'] ?? null);
        $status = strtolower(textValue($row['K'] ?? 'active') ?? 'active');
        $notes = textValue($row['L'] ?? null);

        if (! $description || ! $typeOfGoods || ! $categoryCode || ! $categoryName || ! $unitName) {
            throw new RuntimeException("Row {$rowNumber} is missing required item/category/unit data.");
        }

        if (! in_array($status, ['active', 'inactive'], true)) {
            throw new RuntimeException("Row {$rowNumber} has invalid status: {$status}");
        }

        $category = ItemCategory::firstOrNew(['category_code' => $categoryCode]);
        $category->fill([
            'category_name' => $categoryName,
            'type_of_goods' => $typeOfGoods,
            'description' => $categoryName,
            'status' => 'active',
        ]);
        $category->save();
        $categoriesTouched[$categoryCode] = true;

        $unitCountBefore = Unit::count();
        $unit = unitForImport($unitName);
        if (Unit::count() > $unitCountBefore) {
            $unitsCreated++;
        }

        $good = IndirectGood::where('category_id', $category->id)
            ->where('ig_description', $description)
            ->first();

        if ($good) {
            $good->fill([
                'unit_id' => $unit->id,
                'unit_of_measure' => $unitName,
                'min_stock_level' => $minStock,
                'max_stock_level' => $maxStock,
                'reorder_point' => $reorderPoint,
                'status' => $status,
                'notes' => $notes,
            ]);
            $good->save();
            $updatedGoods++;
        } else {
            $good = IndirectGood::create([
                'ig_description' => $description,
                'category_id' => $category->id,
                'unit_id' => $unit->id,
                'unit_of_measure' => $unitName,
                'min_stock_level' => $minStock,
                'max_stock_level' => $maxStock,
                'reorder_point' => $reorderPoint,
                'status' => $status,
                'notes' => $notes,
            ]);
            $createdGoods++;
        }

        IndirectGoodsInventory::where('indirect_good_id', $good->id)
            ->where('division_id', $division->id)
            ->where('batch_number', $batchNumber)
            ->delete();

        IndirectGoodsStockMovement::where('indirect_good_id', $good->id)
            ->where('division_id', $division->id)
            ->where('reference_type', 'opening_stock_import')
            ->where('reference_id', 0)
            ->where('invoice_number', $importReference)
            ->delete();

        IndirectGoodsInventory::create([
            'indirect_good_id' => $good->id,
            'division_id' => $division->id,
            'quantity_available' => $openingStock,
            'unit_price' => 0,
            'location' => $division->division_name,
            'batch_number' => $batchNumber,
            'invoice_number' => $importReference,
            'status' => $openingStock > 0 ? 'in_stock' : 'out_of_stock',
        ]);
        $inventoryRows++;
        $openingStockTotal += $openingStock;

        if ($openingStock !== 0.0) {
            IndirectGoodsStockMovement::create([
                'indirect_good_id' => $good->id,
                'movement_type' => 'in',
                'reference_type' => 'opening_stock_import',
                'reference_id' => 0,
                'quantity_before' => 0,
                'quantity_moved' => $openingStock,
                'quantity_after' => $openingStock,
                'invoice_number' => $importReference,
                'division_id' => $division->id,
                'user_id' => $user->id,
                'notes' => "Opening stock imported from {$importReference}",
            ]);
            $stockMovements++;
        }
    }

    DB::commit();

    echo "Created indirect goods: {$createdGoods}\n";
    echo "Updated indirect goods: {$updatedGoods}\n";
    echo "Division: {$division->division_name} ({$division->division_code})\n";
    echo "Workbook: {$workbookPath}\n";
    echo "Inventory rows: {$inventoryRows}\n";
    echo "Opening stock total: {$openingStockTotal}\n";
    echo "Opening stock movements: {$stockMovements}\n";
    echo "Categories touched: " . count($categoriesTouched) . "\n";
    echo "Units created: {$unitsCreated}\n";
} catch (Throwable $e) {
    DB::rollBack();
    fwrite(STDERR, "Import failed: {$e->getMessage()}\n");
    exit(1);
}
