<?php

declare(strict_types=1);

namespace App\Repositories;

use PDO;

final class ItemRepository
{
    public function __construct(private PDO $pdo) {}

    public function hasSupplierColumn(): bool
    {
        return table_has_column($this->pdo, 'items', 'default_supplier_id') && table_exists($this->pdo, 'parties') && table_has_column($this->pdo, 'parties', 'is_supplier');
    }

    private function cleanSearchText(string $value): string
    {
        $value = preg_replace('/^\xEF\xBB\xBF/u', '', $value) ?? $value;
        $value = preg_replace('/[\x{200B}-\x{200D}\x{FEFF}]/u', '', $value) ?? $value;
        $value = preg_replace('/\s+/u', ' ', $value) ?? $value;
        return trim($value);
    }

    public function countSearch(string $q): int
    {
        $q = $this->cleanSearchText($q);
        $sql = "SELECT COUNT(*) FROM items i WHERE COALESCE(i.is_active,1)=1";
        $params = [];
        if ($q !== '') {
            $sql .= " AND (i.name LIKE ? OR COALESCE(i.barcode,'') LIKE ? OR COALESCE(i.brand,'') LIKE ?)";
            $like = "%{$q}%";
            $params = [$like, $like, $like];
        }
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        return (int)($st->fetchColumn() ?: 0);
    }

    public function searchPage(string $q, int $page = 1, int $perPage = 25): array
    {
        $q = $this->cleanSearchText($q);
        $page = max(1, $page);
        $perPage = max(1, min(100, $perPage));
        $offset = ($page - 1) * $perPage;
        $supplierJoin = $this->hasSupplierColumn() ? "LEFT JOIN parties s ON s.id=i.default_supplier_id AND COALESCE(s.is_supplier,0)=1" : "";
        $supplierSelect = $this->hasSupplierColumn() ? ", s.name supplier_name" : "";
        $sql = "SELECT i.*, COALESCE(sb.qty,0) qty, c.name category_name, u.name unit_name{$supplierSelect}
            FROM items i
            LEFT JOIN categories c ON c.id=i.category_id
            LEFT JOIN units u ON u.id=i.default_unit_id
            {$supplierJoin}
            LEFT JOIN stock_balances sb ON sb.item_id=i.id AND sb.warehouse_id=1
            WHERE COALESCE(i.is_active,1)=1";
        $params = [];
        if ($q !== '') {
            $sql .= " AND (i.name LIKE ? OR COALESCE(i.barcode,'') LIKE ? OR COALESCE(i.brand,'') LIKE ?)";
            $like = "%{$q}%";
            $params = [$like, $like, $like];
        }
        $sql .= " ORDER BY i.id DESC LIMIT {$perPage} OFFSET {$offset}";
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        return $st->fetchAll() ?: [];
    }

    public function search(string $q, int $limit = 200): array
    {
        return $this->searchPage($q, 1, $limit);
    }

    public function delete(int $id): void
    {
        $this->pdo->prepare('DELETE FROM items WHERE id=?')->execute([$id]);
    }

    public function usageSummary(int $id): array
    {
        $checks = [
            'sales_items' => ['table' => 'sales_items', 'column' => 'item_id'],
            'purchase_invoice_lines' => ['table' => 'purchase_invoice_lines', 'column' => 'item_id'],
            'purchase_return_lines' => ['table' => 'purchase_return_lines', 'column' => 'item_id'],
            'sales_return_items' => ['table' => 'sales_return_items', 'column' => 'item_id'],
            'wh_doc_lines' => ['table' => 'wh_doc_lines', 'column' => 'item_id'],
            'stock_moves' => ['table' => 'stock_moves', 'column' => 'item_id'],
            'stock_balances' => ['table' => 'stock_balances', 'column' => 'item_id'],
            'opening_doc_lines' => ['table' => 'opening_doc_lines', 'column' => 'item_id'],
            'quotation_items' => ['table' => 'quotation_items', 'column' => 'item_id'],
            'item_units' => ['table' => 'item_units', 'column' => 'item_id'],
        ];

        $summary = [];
        foreach ($checks as $key => $meta) {
            if (!table_exists($this->pdo, $meta['table']) || !table_has_column($this->pdo, $meta['table'], $meta['column'])) {
                continue;
            }
            $st = $this->pdo->prepare(sprintf('SELECT COUNT(*) FROM %s WHERE %s = ?', $meta['table'], $meta['column']));
            $st->execute([$id]);
            $count = (int)($st->fetchColumn() ?: 0);
            if ($count > 0) {
                if ($key === 'stock_balances') {
                    $st2 = $this->pdo->prepare('SELECT COUNT(*) FROM stock_balances WHERE item_id=? AND ABS(qty) > 0.000001');
                    $st2->execute([$id]);
                    $count = (int)($st2->fetchColumn() ?: 0);
                    if ($count === 0) {
                        continue;
                    }
                }
                $summary[$key] = $count;
            }
        }

        return $summary;
    }

    public function findById(int $id): array|false
    {
        if ($this->hasSupplierColumn()) {
            $st = $this->pdo->prepare("SELECT i.*, s.name supplier_name FROM items i LEFT JOIN parties s ON s.id=i.default_supplier_id AND COALESCE(s.is_supplier,0)=1 WHERE i.id=? LIMIT 1");
        } else {
            $st = $this->pdo->prepare("SELECT * FROM items WHERE id=? LIMIT 1");
        }
        $st->execute([$id]);
        return $st->fetch();
    }

    public function latestPackInfo(int $itemId): array|false
    {
        $st = $this->pdo->prepare("SELECT unit_id, factor_to_base FROM item_units WHERE item_id=? AND is_base=0 ORDER BY id DESC LIMIT 1");
        $st->execute([$itemId]);
        return $st->fetch();
    }

    public function create(array $data): int
    {
        if ($this->hasSupplierColumn()) {
            $st = $this->pdo->prepare("INSERT INTO items (name, brand, barcode, category_id, default_unit_id, default_supplier_id, cost, price, reorder_point, low_stock_level, notes) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
            $st->execute([$data['name'], $data['brand'], $data['barcode'], $data['category_id'], $data['default_unit_id'], $data['default_supplier_id'], $data['cost'], $data['price'], $data['reorder_point'], $data['low_stock_level'], $data['notes']]);
        } else {
            $st = $this->pdo->prepare("INSERT INTO items (name, brand, barcode, category_id, default_unit_id, cost, price, reorder_point, low_stock_level, notes) VALUES (?,?,?,?,?,?,?,?,?,?)");
            $st->execute([$data['name'], $data['brand'], $data['barcode'], $data['category_id'], $data['default_unit_id'], $data['cost'], $data['price'], $data['reorder_point'], $data['low_stock_level'], $data['notes']]);
        }
        return (int)$this->pdo->lastInsertId();
    }

    public function update(int $id, array $data): void
    {
        if ($this->hasSupplierColumn()) {
            $st = $this->pdo->prepare("UPDATE items SET name=?, brand=?, barcode=?, category_id=?, default_unit_id=?, default_supplier_id=?, cost=?, price=?, reorder_point=?, low_stock_level=?, notes=?, is_active=? WHERE id=?");
            $st->execute([$data['name'], $data['brand'], $data['barcode'], $data['category_id'], $data['default_unit_id'], $data['default_supplier_id'], $data['cost'], $data['price'], $data['reorder_point'], $data['low_stock_level'], $data['notes'], $data['is_active'], $id]);
        } else {
            $st = $this->pdo->prepare("UPDATE items SET name=?, brand=?, barcode=?, category_id=?, default_unit_id=?, cost=?, price=?, reorder_point=?, low_stock_level=?, notes=?, is_active=? WHERE id=?");
            $st->execute([$data['name'], $data['brand'], $data['barcode'], $data['category_id'], $data['default_unit_id'], $data['cost'], $data['price'], $data['reorder_point'], $data['low_stock_level'], $data['notes'], $data['is_active'], $id]);
        }
    }

    public function ensureMainStockBalance(int $itemId): void
    {
        $this->pdo->prepare("INSERT IGNORE INTO stock_balances (warehouse_id, item_id, qty) VALUES (1, ?, 0)")->execute([$itemId]);
    }

    public function clearBaseItemUnitsExcept(int $itemId, int $unitId): void
    {
        $this->pdo->prepare("DELETE FROM item_units WHERE item_id=? AND is_base=1 AND unit_id<>?")->execute([$itemId, $unitId]);
    }

    public function upsertItemUnit(int $itemId, int $unitId, float $factorToBase, int $isBase): void
    {
        $st = $this->pdo->prepare("INSERT INTO item_units (item_id, unit_id, factor_to_base, is_base) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE factor_to_base=VALUES(factor_to_base), is_base=VALUES(is_base)");
        $st->execute([$itemId, $unitId, $factorToBase, $isBase]);
    }

    public function clearItemUnits(int $itemId): void
    {
        $this->pdo->prepare("DELETE FROM item_units WHERE item_id=?")->execute([$itemId]);
    }

    public function clearNonBaseItemUnits(int $itemId): void
    {
        $this->pdo->prepare("DELETE FROM item_units WHERE item_id=? AND is_base=0")->execute([$itemId]);
    }
}
