<?php
declare(strict_types=1);

require_once __DIR__ . '/config.php';

function pos_bootstrap(): void
{
    static $booted = false;
    if ($booted) {
        return;
    }

    $pdo = db();
    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS pos_products (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(120) NOT NULL,
            category VARCHAR(80) NOT NULL,
            price DECIMAL(10,2) NOT NULL DEFAULT 0,
            stock INT NOT NULL DEFAULT 0,
            low_stock_threshold INT NOT NULL DEFAULT 5,
            unit_label VARCHAR(30) NOT NULL DEFAULT 'unidad',
            sort_order INT NOT NULL DEFAULT 0,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_pos_products_category (category),
            KEY idx_pos_products_active (is_active)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
    );

    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS pos_sales (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            receipt_number VARCHAR(40) NOT NULL UNIQUE,
            cashier_code VARCHAR(40) NOT NULL,
            cashier_name VARCHAR(120) NOT NULL,
            cashier_role VARCHAR(30) NOT NULL,
            item_count INT NOT NULL DEFAULT 0,
            subtotal DECIMAL(10,2) NOT NULL DEFAULT 0,
            total DECIMAL(10,2) NOT NULL DEFAULT 0,
            items_json LONGTEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            KEY idx_pos_sales_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
    );

    $count = (int)$pdo->query("SELECT COUNT(*) FROM pos_products")->fetchColumn();
    if ($count === 0) {
        $seedProducts = [
            ['Manzana roja', 'Frutas', 1.20, 18, 5, 'unidad', 10],
            ['Plátano', 'Frutas', 0.60, 22, 6, 'unidad', 20],
            ['Naranja dulce', 'Frutas', 0.90, 9, 4, 'unidad', 30],
            ['Zanahoria', 'Verduras', 0.70, 14, 5, 'unidad', 40],
            ['Lechuga romana', 'Verduras', 1.50, 6, 3, 'unidad', 50],
            ['Tomate saladet', 'Verduras', 1.10, 8, 4, 'unidad', 60],
            ['Leche entera 1L', 'Lácteos', 1.80, 12, 4, 'botella', 70],
            ['Yogur natural', 'Lácteos', 0.95, 4, 3, 'unidad', 80],
            ['Queso fresco', 'Lácteos', 2.60, 5, 3, 'pieza', 90],
            ['Pan integral', 'Despensa', 1.30, 10, 4, 'pieza', 100],
            ['Arroz 1kg', 'Despensa', 2.10, 7, 3, 'paquete', 110],
            ['Café molido', 'Despensa', 4.90, 3, 2, 'paquete', 120],
        ];

        $stmt = $pdo->prepare(
            "INSERT INTO pos_products (
                name,
                category,
                price,
                stock,
                low_stock_threshold,
                unit_label,
                sort_order
            ) VALUES (
                :name,
                :category,
                :price,
                :stock,
                :low_stock_threshold,
                :unit_label,
                :sort_order
            )"
        );

        foreach ($seedProducts as [$name, $category, $price, $stock, $threshold, $unitLabel, $sortOrder]) {
            $stmt->execute([
                'name' => $name,
                'category' => $category,
                'price' => $price,
                'stock' => $stock,
                'low_stock_threshold' => $threshold,
                'unit_label' => $unitLabel,
                'sort_order' => $sortOrder,
            ]);
        }
    }

    $booted = true;
}

function pos_all_products(): array
{
    $stmt = db()->query(
        "SELECT id, name, category, price, stock, low_stock_threshold, unit_label, sort_order
         FROM pos_products
         WHERE is_active = 1
         ORDER BY FIELD(category, 'Frutas', 'Verduras', 'Lácteos', 'Despensa'), sort_order, name"
    );

    return $stmt->fetchAll() ?: [];
}

function pos_product_by_id(int $productId): ?array
{
    $stmt = db()->prepare(
        "SELECT id, name, category, price, stock, low_stock_threshold, unit_label, sort_order
         FROM pos_products
         WHERE id = :id AND is_active = 1
         LIMIT 1"
    );
    $stmt->execute(['id' => $productId]);
    $product = $stmt->fetch();

    return $product ?: null;
}

function pos_products_by_ids(array $productIds): array
{
    $productIds = array_values(array_filter(array_map('intval', $productIds), static fn (int $id): bool => $id > 0));
    if ($productIds === []) {
        return [];
    }

    $placeholders = implode(',', array_fill(0, count($productIds), '?'));
    $stmt = db()->prepare(
        "SELECT id, name, category, price, stock, low_stock_threshold, unit_label, sort_order
         FROM pos_products
         WHERE is_active = 1 AND id IN ($placeholders)"
    );
    $stmt->execute($productIds);
    $rows = $stmt->fetchAll() ?: [];
    $map = [];

    foreach ($rows as $row) {
        $map[(int)$row['id']] = $row;
    }

    return $map;
}

function pos_recent_sales(int $limit = 8): array
{
    $limit = max(1, min(25, $limit));
    $stmt = db()->query(
        "SELECT id, receipt_number, cashier_name, cashier_role, item_count, subtotal, total, created_at
         FROM pos_sales
         ORDER BY created_at DESC
         LIMIT {$limit}"
    );

    return $stmt->fetchAll() ?: [];
}

function pos_sale_by_id(int $saleId): ?array
{
    $stmt = db()->prepare(
        "SELECT id, receipt_number, cashier_code, cashier_name, cashier_role, item_count, subtotal, total, items_json, created_at
         FROM pos_sales
         WHERE id = :id
         LIMIT 1"
    );
    $stmt->execute(['id' => $saleId]);
    $sale = $stmt->fetch();
    if (!$sale) {
        return null;
    }

    $items = json_decode((string)$sale['items_json'], true);
    $sale['items'] = is_array($items) ? $items : [];

    return $sale;
}

function pos_low_stock_count(): int
{
    $stmt = db()->query(
        "SELECT COUNT(*)
         FROM pos_products
         WHERE is_active = 1 AND stock <= low_stock_threshold"
    );

    return (int)$stmt->fetchColumn();
}

function pos_low_stock_products(int $limit = 6): array
{
    $limit = max(1, min(20, $limit));
    $stmt = db()->query(
        "SELECT id, name, category, price, stock, low_stock_threshold, unit_label, sort_order
         FROM pos_products
         WHERE is_active = 1 AND stock <= low_stock_threshold
         ORDER BY stock ASC, sort_order ASC, name ASC
         LIMIT {$limit}"
    );

    return $stmt->fetchAll() ?: [];
}

function pos_today_metrics(): array
{
    $stmt = db()->query(
        "SELECT COUNT(*) AS sales_count, COALESCE(SUM(total), 0) AS sales_total
         FROM pos_sales
         WHERE DATE(created_at) = CURDATE()"
    );
    $row = $stmt->fetch() ?: ['sales_count' => 0, 'sales_total' => 0];

    return [
        'sales_count' => (int)($row['sales_count'] ?? 0),
        'sales_total' => (float)($row['sales_total'] ?? 0),
    ];
}

function pos_adjust_stock(int $productId, int $delta): ?array
{
    $product = pos_product_by_id($productId);
    if (!$product) {
        return null;
    }

    $newStock = max(0, (int)$product['stock'] + $delta);
    $stmt = db()->prepare(
        "UPDATE pos_products
         SET stock = :stock
         WHERE id = :id
         LIMIT 1"
    );
    $stmt->execute([
        'stock' => $newStock,
        'id' => $productId,
    ]);

    return pos_product_by_id($productId);
}