<?php
namespace App;

use PDO;

class Product {
    /** @var int */
    public $id;
    /** @var string */
    public $title;
    /** @var string */
    public $slug;
    /** @var string */
    public $description;
    /** @var int */
    public $price_cents;
    /** @var string */
    public $template_file;
    /** @var string */
    public $image_url;

    /** @var array<int, array> */
    private static $byIdCache = [];
    /** @var array<int, array>|null */
    private static $allCache = null;

    public static function all(): array {
        if (self::$allCache !== null) {
            return self::$allCache;
        }
        $db = get_db();
        $stmt = $db->query('SELECT * FROM products ORDER BY id DESC');
        self::$allCache = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach (self::$allCache as $row) {
            $pid = (int)($row['id'] ?? 0);
            if ($pid > 0) {
                self::$byIdCache[$pid] = $row;
            }
        }
        return self::$allCache;
    }

    public static function find(int $id): ?array {
        if ($id <= 0) {
            return null;
        }
        if (isset(self::$byIdCache[$id])) {
            return self::$byIdCache[$id];
        }
        $db = get_db();
        $stmt = $db->prepare('SELECT * FROM products WHERE id = :id');
        $stmt->execute([':id'=>$id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row) {
            self::$byIdCache[$id] = $row;
            return $row;
        }
        return null;
    }

    /**
     * Fetch products by ids in a single query.
     * @param array<int, int|string> $ids
     * @return array<int, array>
     */
    public static function findMany(array $ids): array {
        $normalized = [];
        foreach ($ids as $id) {
            $pid = (int)$id;
            if ($pid > 0) {
                $normalized[$pid] = $pid;
            }
        }
        if (empty($normalized)) {
            return [];
        }

        $result = [];
        $missing = [];
        foreach ($normalized as $pid) {
            if (isset(self::$byIdCache[$pid])) {
                $result[$pid] = self::$byIdCache[$pid];
            } else {
                $missing[] = $pid;
            }
        }

        if (!empty($missing)) {
            $placeholders = implode(',', array_fill(0, count($missing), '?'));
            $db = get_db();
            $stmt = $db->prepare("SELECT * FROM products WHERE id IN ({$placeholders})");
            $stmt->execute(array_values($missing));
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
            foreach ($rows as $row) {
                $pid = (int)($row['id'] ?? 0);
                if ($pid > 0) {
                    self::$byIdCache[$pid] = $row;
                    $result[$pid] = $row;
                }
            }
        }

        return $result;
    }

    public static function create(array $data): int {
        $db = get_db();
        $stmt = $db->prepare('INSERT INTO products (title, slug, description, price_cents, template_file, image_url) VALUES (:title,:slug,:description,:price_cents,:template_file,:image_url)');
        $stmt->execute([
            ':title'=>$data['title'],
            ':slug'=>$data['slug'],
            ':description'=>$data['description'],
            ':price_cents'=>$data['price_cents'],
            ':template_file'=>$data['template_file'] ?? '',
            ':image_url'=>$data['image_url'] ?? null
        ]);
        self::$allCache = null;
        return (int)$db->lastInsertId();
    }

    public static function inferEventTypeCode(array $product): ?string {
        $source = strtolower(trim((string)($product['slug'] ?? '') . ' ' . (string)($product['title'] ?? '')));
        if ($source === '') {
            return null;
        }

        if (strpos($source, 'quince') !== false || strpos($source, 'xv') !== false) {
            return 'quince';
        }
        if (strpos($source, 'boda') !== false || strpos($source, 'wedding') !== false) {
            return 'boda';
        }
        if (strpos($source, 'bautizo') !== false) {
            return 'bautizo';
        }
        if (strpos($source, 'comunion') !== false) {
            return 'comunion';
        }
        if (strpos($source, 'gradua') !== false) {
            return 'graduacion';
        }
        if (strpos($source, 'cumple') !== false) {
            return 'cumpleanos';
        }
        if (strpos($source, 'corpora') !== false || strpos($source, 'empresa') !== false || strpos($source, 'business') !== false) {
            return 'corporativo';
        }

        return null;
    }
}
