<?php

declare(strict_types=1);

namespace ApiModel\Infrastructure\Persistence\Repositories;

use ApiModel\Domain\Repository\InventoryRepositoryInterface;
use ApiModel\Infrastructure\Persistence\Database;
use PDO;

final class PdoInventoryRepository implements InventoryRepositoryInterface
{
    public function __construct(private readonly PDO $pdo) {}

    // --- Categories ---
    public function findCategoryById(string $id): ?array
    {
        $st = $this->pdo->prepare('SELECT * FROM inventory_categories WHERE id = ?');
        $st->execute([$id]);
        $row = $st->fetch();
        return $row !== false ? $row : null;
    }

    public function listCategories(string $companyId): array
    {
        $st = $this->pdo->prepare('SELECT * FROM inventory_categories WHERE company_id = ? ORDER BY name ASC');
        $st->execute([$companyId]);
        return $st->fetchAll();
    }

    public function insertCategory(string $id, string $companyId, string $name, ?string $description): void
    {
        $st = $this->pdo->prepare(
            'INSERT INTO inventory_categories (id, company_id, name, description, created_at, updated_at) 
             VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)'
        );
        $st->execute([$id, $companyId, $name, $description]);
    }

    public function updateCategory(string $id, string $name, ?string $description): void
    {
        $st = $this->pdo->prepare(
            'UPDATE inventory_categories SET name = ?, description = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?'
        );
        $st->execute([$name, $description, $id]);
    }

    public function deleteCategory(string $id): void
    {
        $st = $this->pdo->prepare('DELETE FROM inventory_categories WHERE id = ?');
        $st->execute([$id]);
    }

    public function categoryNameExists(string $companyId, string $name, ?string $excludeId = null): bool
    {
        if ($excludeId !== null) {
            $st = $this->pdo->prepare(
                'SELECT COUNT(*) FROM inventory_categories WHERE company_id = ? AND LOWER(name) = LOWER(?) AND id <> ?'
            );
            $st->execute([$companyId, $name, $excludeId]);
        } else {
            $st = $this->pdo->prepare(
                'SELECT COUNT(*) FROM inventory_categories WHERE company_id = ? AND LOWER(name) = LOWER(?)'
            );
            $st->execute([$companyId, $name]);
        }
        return ((int) $st->fetchColumn()) > 0;
    }

    // --- Items ---
    public function findItemById(string $id): ?array
    {
        $st = $this->pdo->prepare(
            'SELECT i.*, c.name as category_name, t.name as tax_name, t.rate as tax_rate
             FROM inventory_items i
             LEFT JOIN inventory_categories c ON i.category_id = c.id
             LEFT JOIN tax_rates t ON i.tax_rate_id = t.id
             WHERE i.id = ?'
        );
        $st->execute([$id]);
        $row = $st->fetch();
        return $row !== false ? $row : null;
    }

    public function findItemBySku(string $companyId, string $sku): ?array
    {
        $st = $this->pdo->prepare(
            'SELECT i.*, c.name as category_name, t.name as tax_name, t.rate as tax_rate
             FROM inventory_items i
             LEFT JOIN inventory_categories c ON i.category_id = c.id
             LEFT JOIN tax_rates t ON i.tax_rate_id = t.id
             WHERE i.company_id = ? AND LOWER(i.sku) = LOWER(?)'
        );
        $st->execute([$companyId, $sku]);
        $row = $st->fetch();
        return $row !== false ? $row : null;
    }

    public function listItems(string $companyId): array
    {
        $st = $this->pdo->prepare(
            'SELECT i.*, c.name as category_name, t.name as tax_name, t.rate as tax_rate
             FROM inventory_items i
             LEFT JOIN inventory_categories c ON i.category_id = c.id
             LEFT JOIN tax_rates t ON i.tax_rate_id = t.id
             WHERE i.company_id = ?
             ORDER BY i.name ASC'
        );
        $st->execute([$companyId]);
        return $st->fetchAll();
    }

    public function insertItem(array $data): void
    {
        $st = $this->pdo->prepare(
            'INSERT INTO inventory_items (
                id, company_id, category_id, sku, name, description, item_type,
                sale_price, purchase_price, tax_rate_id, income_account_id,
                expense_account_id, inventory_account_id, is_active, created_at, updated_at
             ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)'
        );
        $st->execute([
            $data['id'],
            $data['company_id'],
            $data['category_id'] ?: null,
            $data['sku'],
            $data['name'],
            $data['description'] ?: null,
            $data['item_type'] ?? 'product',
            $data['sale_price'] ?? 0.0,
            $data['purchase_price'] ?? 0.0,
            $data['tax_rate_id'] ?: null,
            $data['income_account_id'] ?: null,
            $data['expense_account_id'] ?: null,
            $data['inventory_account_id'] ?: null,
            Database::bool((bool) ($data['is_active'] ?? true)),
        ]);
    }

    public function updateItem(string $id, array $data): void
    {
        $st = $this->pdo->prepare(
            'UPDATE inventory_items 
             SET category_id = ?, sku = ?, name = ?, description = ?, item_type = ?,
                 sale_price = ?, purchase_price = ?, tax_rate_id = ?, income_account_id = ?,
                 expense_account_id = ?, inventory_account_id = ?, is_active = ?, updated_at = CURRENT_TIMESTAMP
             WHERE id = ?'
        );
        $st->execute([
            $data['category_id'] ?: null,
            $data['sku'],
            $data['name'],
            $data['description'] ?: null,
            $data['item_type'] ?? 'product',
            $data['sale_price'] ?? 0.0,
            $data['purchase_price'] ?? 0.0,
            $data['tax_rate_id'] ?: null,
            $data['income_account_id'] ?: null,
            $data['expense_account_id'] ?: null,
            $data['inventory_account_id'] ?: null,
            Database::bool((bool) ($data['is_active'] ?? true)),
            $id
        ]);
    }

    public function deleteItem(string $id): void
    {
        $st = $this->pdo->prepare('DELETE FROM inventory_items WHERE id = ?');
        $st->execute([$id]);
    }

    public function itemSkuExists(string $companyId, string $sku, ?string $excludeId = null): bool
    {
        if ($excludeId !== null) {
            $st = $this->pdo->prepare(
                'SELECT COUNT(*) FROM inventory_items WHERE company_id = ? AND LOWER(sku) = LOWER(?) AND id <> ?'
            );
            $st->execute([$companyId, $sku, $excludeId]);
        } else {
            $st = $this->pdo->prepare(
                'SELECT COUNT(*) FROM inventory_items WHERE company_id = ? AND LOWER(sku) = LOWER(?)'
            );
            $st->execute([$companyId, $sku]);
        }
        return ((int) $st->fetchColumn()) > 0;
    }

    // --- Warehouses ---
    public function findWarehouseById(string $id): ?array
    {
        $st = $this->pdo->prepare('SELECT * FROM warehouses WHERE id = ?');
        $st->execute([$id]);
        $row = $st->fetch();
        return $row !== false ? $row : null;
    }

    public function findWarehouseByCode(string $companyId, string $code): ?array
    {
        $st = $this->pdo->prepare('SELECT * FROM warehouses WHERE company_id = ? AND LOWER(code) = LOWER(?)');
        $st->execute([$companyId, $code]);
        $row = $st->fetch();
        return $row !== false ? $row : null;
    }

    public function listWarehouses(string $companyId): array
    {
        $st = $this->pdo->prepare('SELECT * FROM warehouses WHERE company_id = ? ORDER BY name ASC');
        $st->execute([$companyId]);
        return $st->fetchAll();
    }

    public function insertWarehouse(string $id, string $companyId, string $name, string $code, ?string $address): void
    {
        $st = $this->pdo->prepare(
            'INSERT INTO warehouses (id, company_id, name, code, address, is_active, created_at, updated_at) 
             VALUES (?, ?, ?, ?, ?, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)'
        );
        $st->execute([$id, $companyId, $name, $code, $address]);
    }

    public function updateWarehouse(string $id, string $name, string $code, ?string $address, bool $isActive): void
    {
        $st = $this->pdo->prepare(
            'UPDATE warehouses 
             SET name = ?, code = ?, address = ?, is_active = ?, updated_at = CURRENT_TIMESTAMP 
             WHERE id = ?'
        );
        $st->execute([$name, $code, $address, Database::bool($isActive), $id]);
    }

    public function deleteWarehouse(string $id): void
    {
        $st = $this->pdo->prepare('DELETE FROM warehouses WHERE id = ?');
        $st->execute([$id]);
    }

    public function warehouseCodeExists(string $companyId, string $code, ?string $excludeId = null): bool
    {
        if ($excludeId !== null) {
            $st = $this->pdo->prepare(
                'SELECT COUNT(*) FROM warehouses WHERE company_id = ? AND LOWER(code) = LOWER(?) AND id <> ?'
            );
            $st->execute([$companyId, $code, $excludeId]);
        } else {
            $st = $this->pdo->prepare(
                'SELECT COUNT(*) FROM warehouses WHERE company_id = ? AND LOWER(code) = LOWER(?)'
            );
            $st->execute([$companyId, $code]);
        }
        return ((int) $st->fetchColumn()) > 0;
    }

    // --- Stock ---
    public function getStock(string $itemId, string $warehouseId): float
    {
        $st = $this->pdo->prepare('SELECT quantity FROM inventory_stock WHERE item_id = ? AND warehouse_id = ?');
        $st->execute([$itemId, $warehouseId]);
        $qty = $st->fetchColumn();
        return $qty !== false ? (float) $qty : 0.0;
    }

    public function updateStock(string $itemId, string $warehouseId, float $quantity): void
    {
        // Upsert style
        $st = $this->pdo->prepare('SELECT COUNT(*) FROM inventory_stock WHERE item_id = ? AND warehouse_id = ?');
        $st->execute([$itemId, $warehouseId]);
        $exists = ((int) $st->fetchColumn()) > 0;

        if ($exists) {
            $st = $this->pdo->prepare('UPDATE inventory_stock SET quantity = ? WHERE item_id = ? AND warehouse_id = ?');
            $st->execute([$quantity, $itemId, $warehouseId]);
        } else {
            $st = $this->pdo->prepare('INSERT INTO inventory_stock (item_id, warehouse_id, quantity) VALUES (?, ?, ?)');
            $st->execute([$itemId, $warehouseId, $quantity]);
        }
    }

    public function listStockByItem(string $itemId): array
    {
        $st = $this->pdo->prepare(
            'SELECT s.*, w.name as warehouse_name, w.code as warehouse_code
             FROM inventory_stock s
             JOIN warehouses w ON s.warehouse_id = w.id
             WHERE s.item_id = ?'
        );
        $st->execute([$itemId]);
        return $st->fetchAll();
    }

    public function listStockByWarehouse(string $warehouseId): array
    {
        $st = $this->pdo->prepare(
            'SELECT s.*, i.name as item_name, i.sku as item_sku, i.item_type
             FROM inventory_stock s
             JOIN inventory_items i ON s.item_id = i.id
             WHERE s.warehouse_id = ?'
        );
        $st->execute([$warehouseId]);
        return $st->fetchAll();
    }
}
