<?php

declare(strict_types=1);

require __DIR__ . '/db.php';
require __DIR__ . '/helpers.php';

function ensure_catalog_table(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS catalog_products (
            id VARCHAR(64) PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            price DECIMAL(10,2) NOT NULL,
            category VARCHAR(100) NOT NULL,
            emoji VARCHAR(40) NOT NULL,
            image_url VARCHAR(255) NULL,
            taxable TINYINT(1) NOT NULL DEFAULT 0,
            tax_rate DECIMAL(6,2) NULL,
            report_category VARCHAR(100) NOT NULL,
            sort_order INT NOT NULL DEFAULT 0,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )'
    );

    $stmt = $pdo->query("SHOW COLUMNS FROM catalog_products LIKE 'image_url'");
    $column = $stmt ? $stmt->fetch() : false;
    if (!$column) {
        $pdo->exec('ALTER TABLE catalog_products ADD COLUMN image_url VARCHAR(255) NULL AFTER emoji');
    }
}

if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    $pdo = db();
    ensure_catalog_table($pdo);
    $stmt = $pdo->query(
        'SELECT id, name, price, category, emoji, image_url, taxable, tax_rate, report_category
         FROM catalog_products
         ORDER BY sort_order ASC, name ASC'
    );
    $rows = $stmt->fetchAll();

    $products = array_map(static function (array $row): array {
        return [
            'id' => $row['id'],
            'name' => $row['name'],
            'price' => (float) $row['price'],
            'category' => $row['category'],
            'emoji' => $row['emoji'],
            'imageUrl' => $row['image_url'],
            'taxable' => (bool) $row['taxable'],
            'taxRate' => $row['tax_rate'] !== null ? (float) $row['tax_rate'] : null,
            'reportCategory' => $row['report_category'],
        ];
    }, $rows);

    json_response(['products' => $products]);
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    json_response(['error' => 'Method not allowed'], 405);
}

$payload = read_json_body();
if (!isset($payload['products']) || !is_array($payload['products'])) {
    json_response(['error' => 'Missing products payload'], 400);
}

$pdo = db();
ensure_catalog_table($pdo);
$pdo->beginTransaction();

try {
    $pdo->exec('DELETE FROM catalog_products');

    $stmt = $pdo->prepare(
        'INSERT INTO catalog_products (
            id, name, price, category, emoji, image_url, taxable, tax_rate, report_category, sort_order
         ) VALUES (
            :id, :name, :price, :category, :emoji, :image_url, :taxable, :tax_rate, :report_category, :sort_order
         )'
    );

    foreach ($payload['products'] as $index => $product) {
        $stmt->execute([
            'id' => $product['id'] ?? ('product-' . $index),
            'name' => $product['name'] ?? 'Item',
            'price' => $product['price'] ?? 0,
            'category' => $product['category'] ?? 'General',
            'emoji' => $product['emoji'] ?? 'ITEM',
            'image_url' => $product['imageUrl'] ?? null,
            'taxable' => !empty($product['taxable']) ? 1 : 0,
            'tax_rate' => $product['taxRate'] ?? null,
            'report_category' => $product['reportCategory'] ?? 'Other',
            'sort_order' => $index,
        ]);
    }

    $pdo->commit();
    json_response(['ok' => true]);
} catch (Throwable $error) {
    $pdo->rollBack();
    json_response(['error' => $error->getMessage()], 500);
}