<?php

declare(strict_types=1);

namespace App\Shared\Support;

use App\Core\SchemaInspector;
use PDO;
use Throwable;

final class InventoryLookup
{
    public static function mainWarehouseId(PDO $pdo): int
    {
        $id = (int) $pdo->query("SELECT id FROM warehouses WHERE is_active=1 AND is_main=1 ORDER BY id LIMIT 1")->fetchColumn();
        if ($id > 0) {
            return $id;
        }

        $id = (int) $pdo->query("SELECT id FROM warehouses WHERE is_active=1 ORDER BY id LIMIT 1")->fetchColumn();
        return $id > 0 ? $id : 1;
    }

    public static function getWarehouses(PDO $pdo): array
    {
        return $pdo->query("SELECT id, COALESCE(code, CONCAT('WH',id)) code, name, is_main, is_active
                            FROM warehouses ORDER BY is_main DESC, name")->fetchAll();
    }

    public static function warehouseAllowsNegative(PDO $pdo, int $warehouseId): bool
    {
        if ($warehouseId <= 0 || !self::tableHasColumn($pdo, 'warehouses', 'allow_negative')) {
            return false;
        }

        $st = $pdo->prepare("SELECT allow_negative FROM warehouses WHERE id=? LIMIT 1");
        $st->execute([$warehouseId]);
        return (int) ($st->fetchColumn() ?: 0) === 1;
    }

    public static function stockBalance(PDO $pdo, int $warehouseId, int $itemId): float
    {
        $st = $pdo->prepare("SELECT COALESCE(qty,0) FROM stock_balances WHERE warehouse_id=? AND item_id=? LIMIT 1");
        $st->execute([$warehouseId, $itemId]);
        return (float) ($st->fetchColumn() ?: 0);
    }

    public static function getCategories(PDO $pdo): array
    {
        return $pdo->query("SELECT id, name, parent_id, is_active FROM categories ORDER BY name")->fetchAll();
    }

    public static function getUnits(PDO $pdo): array
    {
        return $pdo->query("SELECT u.id, u.name, u.symbol, u.base_unit_id, u.factor_to_base, b.name base_name FROM units u LEFT JOIN units b ON b.id=u.base_unit_id ORDER BY u.name")->fetchAll();
    }

    public static function getSuppliers(PDO $pdo): array
    {
        if (!self::tableExists($pdo, 'suppliers')) {
            return [];
        }

        return $pdo->query("SELECT id, COALESCE(code, CONCAT('S',LPAD(id,5,'0'))) code, name, phone, is_active
                            FROM suppliers
                            ORDER BY name")->fetchAll();
    }

    public static function supplierLabel(array $supplier): string
    {
        $code = trim((string) ($supplier['code'] ?? ''));
        $name = trim((string) ($supplier['name'] ?? ''));
        $phone = trim((string) ($supplier['phone'] ?? ''));
        $parts = [];

        if ($name !== '') {
            $parts[] = $name;
        }
        if ($code !== '') {
            $parts[] = $code;
        }
        if ($phone !== '') {
            $parts[] = $phone;
        }

        return implode(' - ', $parts);
    }

    public static function applyStockBalance(PDO $pdo, int $warehouseId, int $itemId, float $delta): void
    {
        $pdo->prepare("INSERT INTO stock_balances (warehouse_id, item_id, qty)
                       VALUES (?,?,?)
                       ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty)")
            ->execute([$warehouseId, $itemId, $delta]);
    }

    public static function deductStockOrFail(PDO $pdo, int $warehouseId, int $itemId, float $qty): void
    {
        if ($qty <= 0) {
            return;
        }

        $pdo->prepare("INSERT INTO stock_balances (warehouse_id, item_id, qty)
                       VALUES (?, ?, 0)
                       ON DUPLICATE KEY UPDATE qty = qty")
            ->execute([$warehouseId, $itemId]);

        $st = $pdo->prepare("UPDATE stock_balances
                             SET qty = qty - ?
                             WHERE warehouse_id = ? AND item_id = ? AND qty >= ?");
        $st->execute([$qty, $warehouseId, $itemId, $qty]);

        if ($st->rowCount() !== 1) {
            throw new \RuntimeException('رصيد المخزون غير كافٍ أثناء الترحيل. أعد تحميل الصفحة ثم حاول مرة أخرى.');
        }
    }

    public static function itemUnitFactor(PDO $pdo, int $itemId, ?int $unitId): float
    {
        if (!$unitId) {
            return 1.0;
        }

        $st = $pdo->prepare("SELECT factor_to_base FROM item_units WHERE item_id=? AND unit_id=? LIMIT 1");
        $st->execute([$itemId, $unitId]);
        $factor = (float) ($st->fetchColumn() ?: 1);
        return $factor > 0 ? $factor : 1.0;
    }

    public static function lookupByBarcode(PDO $pdo, string $barcode): array
    {
        $barcode = trim($barcode);
        if ($barcode === '') {
            return [0, null, 1.0];
        }

        $st = $pdo->prepare("SELECT item_id, unit_id, factor_to_base FROM item_units WHERE barcode=? LIMIT 1");
        $st->execute([$barcode]);
        $row = $st->fetch();
        if ($row) {
            return [(int) $row['item_id'], (int) $row['unit_id'], (float) $row['factor_to_base']];
        }

        $st = $pdo->prepare("SELECT id FROM items WHERE is_active=1 AND barcode=? LIMIT 1");
        $st->execute([$barcode]);
        $id = (int) ($st->fetchColumn() ?: 0);
        return [$id, null, 1.0];
    }

    private static function tableExists(PDO $pdo, string $table): bool
    {
        try {
            return SchemaInspector::tableExists($pdo, $table);
        } catch (Throwable $e) {
            return false;
        }
    }

    private static function tableHasColumn(PDO $pdo, string $table, string $column): bool
    {
        try {
            return SchemaInspector::tableHasColumn($pdo, $table, $column);
        } catch (Throwable $e) {
            return false;
        }
    }
}
