<?php

declare(strict_types=1);

$basePath = dirname(__DIR__);
$workbookPath = $argv[1] ?? ($basePath . '/data/BMC_items_import_template_invent.xlsx');
$divisionName = $argv[2] ?? 'BMC DUBAI';
$importReference = $argv[3] ?? pathinfo($workbookPath, PATHINFO_FILENAME);

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

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

$pdo = new PDO(
    'mysql:host=127.0.0.1;port=8889;dbname=kcg-inventory;charset=utf8mb4',
    'root',
    'root',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]
);

function columnIndex(string $letters): int
{
    $index = 0;
    foreach (str_split($letters) as $letter) {
        $index = ($index * 26) + (ord($letter) - 64);
    }

    return $index;
}

function cellValue(SimpleXMLElement $cell, array $sharedStrings): string
{
    $type = (string) $cell['t'];
    $value = isset($cell->v) ? (string) $cell->v : '';

    if ($type === 's') {
        return trim($sharedStrings[(int) $value] ?? '');
    }

    if ($type === 'inlineStr') {
        return trim((string) $cell->is->t);
    }

    return trim($value);
}

function readWorkbookRows(string $path): array
{
    $zip = new ZipArchive();
    if ($zip->open($path) !== true) {
        throw new RuntimeException("Unable to open workbook: {$path}");
    }

    $sharedStrings = [];
    $sharedXml = $zip->getFromName('xl/sharedStrings.xml');
    if ($sharedXml !== false) {
        $shared = simplexml_load_string($sharedXml);
        foreach ($shared->si as $si) {
            $parts = [];
            if (isset($si->t)) {
                $parts[] = (string) $si->t;
            }
            if (isset($si->r)) {
                foreach ($si->r as $run) {
                    $parts[] = (string) $run->t;
                }
            }
            $sharedStrings[] = implode('', $parts);
        }
    }

    $sheetXml = $zip->getFromName('xl/worksheets/sheet1.xml');
    if ($sheetXml === false) {
        throw new RuntimeException('Data sheet XML not found.');
    }

    $sheet = simplexml_load_string($sheetXml);
    $rows = [];

    foreach ($sheet->sheetData->row as $row) {
        $values = [];

        foreach ($row->c as $cell) {
            $reference = (string) $cell['r'];
            preg_match('/^[A-Z]+/', $reference, $matches);
            $values[columnIndex($matches[0])] = cellValue($cell, $sharedStrings);
        }

        $rows[(int) $row['r']] = $values;
    }

    $zip->close();

    return $rows;
}

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

    if ($value === '') {
        return '0';
    }

    return (string) (float) str_replace(',', '', $value);
}

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

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

function fetchId(PDO $pdo, string $sql, array $params): ?int
{
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $id = $stmt->fetchColumn();

    return $id === false ? null : (int) $id;
}

function upsertCategory(PDO $pdo, string $code, string $name, string $type): int
{
    $id = fetchId($pdo, 'SELECT id FROM item_categories WHERE category_code = ?', [$code]);

    if ($id) {
        $stmt = $pdo->prepare(
            'UPDATE item_categories
             SET category_name = ?, type_of_goods = ?, description = ?, status = "active", updated_at = NOW()
             WHERE id = ?'
        );
        $stmt->execute([$name, $type, $name, $id]);

        return $id;
    }

    $stmt = $pdo->prepare(
        'INSERT INTO item_categories (category_code, category_name, type_of_goods, description, status, created_at, updated_at)
         VALUES (?, ?, ?, ?, "active", NOW(), NOW())'
    );
    $stmt->execute([$code, $name, $type, $name]);

    return (int) $pdo->lastInsertId();
}

function directGoodsTypeId(PDO $pdo): int
{
    $typeName = 'Direct Good (Raw Material)';
    $id = fetchId($pdo, 'SELECT id FROM item_types WHERE type_name = ?', [$typeName]);

    if ($id) {
        return $id;
    }

    $stmt = $pdo->prepare(
        'INSERT INTO item_types (type_name, type_code, description, status, created_at, updated_at)
         VALUES (?, "DIR", "Auto-created for direct goods import", "active", NOW(), NOW())'
    );
    $stmt->execute([$typeName]);

    return (int) $pdo->lastInsertId();
}

function unitId(PDO $pdo, string $unit): ?int
{
    return fetchId(
        $pdo,
        'SELECT id FROM units
         WHERE LOWER(unit_name) = LOWER(?)
            OR LOWER(unit_code) = LOWER(?)
            OR LOWER(unit_symbol) = LOWER(?)
         LIMIT 1',
        [$unit, $unit, $unit]
    );
}

$rows = readWorkbookRows($workbookPath);
$divisionId = fetchId($pdo, 'SELECT id FROM project_divisions WHERE division_name = ?', [$divisionName]);
$userId = fetchId($pdo, 'SELECT id FROM users ORDER BY id LIMIT 1', []);

if (! $divisionId) {
    throw new RuntimeException("{$divisionName} division was not found.");
}

if (! $userId) {
    throw new RuntimeException('No user found for stock movement audit rows.');
}

$headers = $rows[1] ?? [];
$expected = [
    1 => 'item_code',
    2 => 'item_description',
    3 => 'type_of_goods',
    4 => 'category_code',
    5 => 'category_description',
    6 => 'unit_of_measurement',
    7 => 'Current Stock',
    8 => 'min_stock_level',
    9 => 'max_stock_level',
    10 => 'reorder_point',
    11 => 'status',
    12 => 'notes',
];

foreach ($expected as $index => $label) {
    if (($headers[$index] ?? '') !== $label) {
        throw new RuntimeException("Unexpected workbook header at column {$index}: expected {$label}, found " . ($headers[$index] ?? ''));
    }
}

$pdo->beginTransaction();

try {
    $typeId = directGoodsTypeId($pdo);
    $imported = 0;
    $updated = 0;
    $inventoryRows = 0;
    $stockMovements = 0;
    $categoryIds = [];

    foreach ($rows as $rowNumber => $row) {
        if ($rowNumber === 1 || trim($row[1] ?? '') === '') {
            continue;
        }

        $itemCode = scalarValue($row[1] ?? null);
        $description = scalarValue($row[2] ?? null);
        $typeOfGoods = scalarValue($row[3] ?? null);
        $categoryCode = scalarValue($row[4] ?? null);
        $categoryName = scalarValue($row[5] ?? null);
        $unit = scalarValue($row[6] ?? null);
        $openingStock = decimalValue($row[7] ?? null);
        $minStock = decimalValue($row[8] ?? null);
        $maxStock = decimalValue($row[9] ?? null);
        $reorderPoint = decimalValue($row[10] ?? null);
        $status = strtolower(scalarValue($row[11] ?? 'active') ?? 'active');
        $notes = scalarValue($row[12] ?? null);

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

        if ($typeOfGoods !== 'Direct Good (Raw Material)') {
            throw new RuntimeException("Row {$rowNumber} is not marked as Direct Good (Raw Material).");
        }

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

        $categoryIds[$categoryCode] ??= upsertCategory($pdo, $categoryCode, $categoryName, $typeOfGoods);
        $unitId = unitId($pdo, $unit);

        if (! $unitId) {
            throw new RuntimeException("Row {$rowNumber} uses unknown unit: {$unit}");
        }

        $existingItemId = fetchId($pdo, 'SELECT id FROM items WHERE item_code = ?', [$itemCode]);

        if ($existingItemId) {
            $stmt = $pdo->prepare(
                'UPDATE items
                 SET item_description = ?, category_id = ?, item_type_id = ?, unit_id = ?, unit_of_measure = ?,
                     min_stock_level = ?, max_stock_level = ?, reorder_point = ?, status = ?, notes = ?, updated_at = NOW()
                 WHERE id = ?'
            );
            $stmt->execute([
                $description,
                $categoryIds[$categoryCode],
                $typeId,
                $unitId,
                $unit,
                $minStock,
                $maxStock,
                $reorderPoint,
                $status,
                $notes,
                $existingItemId,
            ]);
            $itemId = $existingItemId;
            $updated++;
        } else {
            $stmt = $pdo->prepare(
                'INSERT INTO items
                    (item_code, item_description, category_id, item_type_id, unit_id, unit_of_measure,
                     min_stock_level, max_stock_level, reorder_point, status, notes, created_at, updated_at)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())'
            );
            $stmt->execute([
                $itemCode,
                $description,
                $categoryIds[$categoryCode],
                $typeId,
                $unitId,
                $unit,
                $minStock,
                $maxStock,
                $reorderPoint,
                $status,
                $notes,
            ]);
            $itemId = (int) $pdo->lastInsertId();
            $imported++;
        }

        $pdo->prepare('DELETE FROM inventory WHERE item_id = ? AND division_id = ?')->execute([$itemId, $divisionId]);

        $inventoryStatus = ((float) $openingStock) <= 0 ? 'out_of_stock' : 'in_stock';
        $stmt = $pdo->prepare(
            'INSERT INTO inventory
                (item_id, division_id, quantity_available, unit_price, purchase_price, vat_amount, currency,
                 total_value, location, status, quality_status, compliance_verified, delivery_status,
                 delivery_type, delivery_reference, delivered_at, last_updated, created_at, updated_at)
             VALUES (?, ?, ?, 0, 0, 0, "AED", 0, ?, ?, "approved", 1, "delivered",
                     "warehouse_transfer", ?, NOW(), NOW(), NOW(), NOW())'
        );
        $stmt->execute([$itemId, $divisionId, $openingStock, $divisionName, $inventoryStatus, "{$divisionName} opening stock import"]);
        $inventoryRows++;

        $pdo->prepare(
            'DELETE FROM stock_movements WHERE item_id = ? AND division_id = ? AND reference_type = ? AND reference_id = 0'
        )->execute([$itemId, $divisionId, 'opening_stock_import']);

        if ((float) $openingStock != 0.0) {
            $stmt = $pdo->prepare(
                'INSERT INTO stock_movements
                    (item_id, movement_type, reference_type, reference_id, quantity_before, quantity_moved,
                     quantity_after, division_id, user_id, notes, created_at, updated_at)
                 VALUES (?, "in", "opening_stock_import", 0, 0, ?, ?, ?, ?, ?, NOW(), NOW())'
            );
            $stmt->execute([
                $itemId,
                $openingStock,
                $openingStock,
                $divisionId,
                $userId,
                "Opening stock imported from {$importReference}",
            ]);
            $stockMovements++;
        }
    }

    $pdo->commit();

    echo "Imported new items: {$imported}\n";
    echo "Updated existing items: {$updated}\n";
    echo "Division: {$divisionName}\n";
    echo "Workbook: {$workbookPath}\n";
    echo "Inventory rows for {$divisionName}: {$inventoryRows}\n";
    echo "Opening stock movements: {$stockMovements}\n";
    echo "Categories touched: " . count($categoryIds) . "\n";
} catch (Throwable $e) {
    $pdo->rollBack();
    fwrite(STDERR, "Import failed: {$e->getMessage()}\n");
    exit(1);
}
