<?php
declare(strict_types=1);

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../core/Cache.php';
require_once __DIR__ . '/../core/Tracking.php';

class Product
{
    private function forgetLists(): void
    {
        $c = new Cache();
        foreach ([8, 10, 50, 100, 200, 500] as $n) {
            $c->forget('products.all.' . $n);
        }
    }

    public function all(int $limit = 50, bool $includeDeleted = false): array
    {
        if (!$includeDeleted) {
            return (new Cache())->remember('products.all.' . $limit, 60, function () use ($limit): array {
                $stmt = db()->prepare(
                    'SELECT * FROM products WHERE deleted_at IS NULL ORDER BY id DESC LIMIT :lim'
                );
                $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
                $stmt->execute();
                return $stmt->fetchAll();
            });
        }
        $stmt = db()->prepare('SELECT * FROM products ORDER BY id DESC LIMIT :lim');
        $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function lowStock(int $threshold = 5, int $limit = 20): array
    {
        $stmt = db()->prepare(
            'SELECT * FROM products WHERE deleted_at IS NULL AND stock <= :t ORDER BY stock ASC LIMIT :lim'
        );
        $stmt->bindValue(':t', $threshold, PDO::PARAM_INT);
        $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function find(int $id): ?array
    {
        $stmt = db()->prepare('SELECT * FROM products WHERE id = :id AND deleted_at IS NULL LIMIT 1');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function findAny(int $id): ?array
    {
        $stmt = db()->prepare('SELECT * FROM products WHERE id = :id LIMIT 1');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function findBySlug(string $slug): ?array
    {
        $stmt = db()->prepare('SELECT * FROM products WHERE slug = :slug AND deleted_at IS NULL LIMIT 1');
        $stmt->execute(['slug' => $slug]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function search(string $q): array
    {
        $cacheKey = 'products.search.' . md5($q);
        return (new Cache())->remember($cacheKey, 45, function () use ($q): array {
            $stmt = db()->prepare(
                'SELECT * FROM products WHERE deleted_at IS NULL AND (title LIKE :q OR sku LIKE :q OR tags LIKE :q) ORDER BY id DESC LIMIT 20'
            );
            $stmt->execute(['q' => '%' . $q . '%']);
            return $stmt->fetchAll();
        });
    }

    /**
     * @return list<array<string, mixed>>
     */
    public function listPublic(?string $q, ?string $category, int $limit = 500): array
    {
        $parts = ['deleted_at IS NULL'];
        $params = [];
        $qTrim = $q !== null ? trim($q) : '';
        if ($qTrim !== '') {
            $parts[] = '(title LIKE :q OR sku LIKE :q OR tags LIKE :q)';
            $params['q'] = '%' . $qTrim . '%';
        }
        $catTrim = $category !== null ? trim($category) : '';
        if ($catTrim !== '') {
            $parts[] = 'category = :cat';
            $params['cat'] = $catTrim;
        }
        $sql = 'SELECT * FROM products WHERE ' . implode(' AND ', $parts) . ' ORDER BY id DESC LIMIT :lim';
        $stmt = db()->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue(':' . $key, $value, PDO::PARAM_STR);
        }
        $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
        $stmt->execute();
        /** @var list<array<string, mixed>> */
        return $stmt->fetchAll();
    }

    /**
     * @return list<string>
     */
    public function distinctCategories(int $limit = 40): array
    {
        $lim = max(1, min(200, $limit));
        $stmt = db()->prepare(
            'SELECT DISTINCT category FROM products WHERE deleted_at IS NULL AND TRIM(category) <> "" ORDER BY category ASC LIMIT ' . $lim
        );
        $stmt->execute();
        $rows = $stmt->fetchAll();
        $out = [];
        foreach ($rows as $row) {
            $c = trim((string)($row['category'] ?? ''));
            if ($c !== '') {
                $out[] = $c;
            }
        }
        return $out;
    }

    public function ensureUniqueTracking(): string
    {
        for ($i = 0; $i < 12; $i++) {
            $code = Tracking::productCode();
            $stmt = db()->prepare('SELECT id FROM products WHERE tracking_code = :c LIMIT 1');
            $stmt->execute(['c' => $code]);
            if (!$stmt->fetch()) {
                return $code;
            }
        }
        return Tracking::productCode();
    }

    public function slugify(string $title, ?int $ignoreId = null): string
    {
        $base = trim(preg_replace('/[^a-z0-9\x{0600}-\x{06FF}]+/iu', '-', $title) ?? '', '-');
        if ($base === '') {
            $base = 'product';
        }
        $slug = $base;
        $n = 1;
        while ($this->slugTaken($slug, $ignoreId)) {
            $slug = $base . '-' . $n;
            $n++;
        }
        return $slug;
    }

    private function slugTaken(string $slug, ?int $ignoreId): bool
    {
        if ($ignoreId !== null) {
            $stmt = db()->prepare('SELECT id FROM products WHERE slug = :s AND id != :id LIMIT 1');
            $stmt->execute(['s' => $slug, 'id' => $ignoreId]);
        } else {
            $stmt = db()->prepare('SELECT id FROM products WHERE slug = :s LIMIT 1');
            $stmt->execute(['s' => $slug]);
        }
        return (bool)$stmt->fetch();
    }

    /**
     * @param array<string, mixed> $data
     */
    public function create(array $data): bool
    {
        $tracking = (string)($data['tracking_code'] ?? '');
        if ($tracking === '') {
            $tracking = $this->ensureUniqueTracking();
        }
        $slug = (string)($data['slug'] ?? '');
        if ($slug === '') {
            $slug = $this->slugify((string)$data['title']);
        }
        $stmt = db()->prepare(
            'INSERT INTO products (tracking_code, sku, title, slug, category, brand, price, short_description, long_description, image_url, video_path, discount_percent, stock, weight_kg, dimensions, tags, status)
             VALUES (:tracking_code, :sku, :title, :slug, :category, :brand, :price, :short_description, :long_description, :image_url, :video_path, :discount_percent, :stock, :weight_kg, :dimensions, :tags, :status)'
        );
        $ok = $stmt->execute([
            'tracking_code' => $tracking,
            'sku' => $data['sku'],
            'title' => $data['title'],
            'slug' => $slug,
            'category' => $data['category'],
            'brand' => $data['brand'] ?: null,
            'price' => $data['price'],
            'short_description' => $data['short_description'] ?? '',
            'long_description' => $data['long_description'] ?? '',
            'image_url' => $data['image_url'] ?: null,
            'video_path' => $data['video_path'] ?: null,
            'discount_percent' => (int)($data['discount_percent'] ?? 0),
            'stock' => (int)$data['stock'],
            'weight_kg' => $data['weight_kg'] !== null && $data['weight_kg'] !== '' ? (float)$data['weight_kg'] : null,
            'dimensions' => $data['dimensions'] ?: null,
            'tags' => $data['tags'] ?: null,
            'status' => (string)($data['status'] ?? 'active'),
        ]);
        $this->forgetLists();
        return $ok;
    }

    /**
     * @param array<string, mixed> $data
     */
    public function update(int $id, array $data): bool
    {
        $slug = (string)($data['slug'] ?? '');
        if ($slug === '') {
            $slug = $this->slugify((string)$data['title'], $id);
        }
        $stmt = db()->prepare(
            'UPDATE products SET sku=:sku,title=:title,slug=:slug,category=:category,brand=:brand,price=:price,
             short_description=:short_description,long_description=:long_description,image_url=:image_url,video_path=:video_path,
             discount_percent=:discount_percent,stock=:stock,weight_kg=:weight_kg,dimensions=:dimensions,tags=:tags,status=:status,
             tracking_code=:tracking_code
             WHERE id=:id'
        );
        $ok = $stmt->execute([
            'id' => $id,
            'sku' => $data['sku'],
            'title' => $data['title'],
            'slug' => $slug,
            'category' => $data['category'],
            'brand' => $data['brand'] ?: null,
            'price' => $data['price'],
            'short_description' => $data['short_description'] ?? '',
            'long_description' => $data['long_description'] ?? '',
            'image_url' => $data['image_url'] ?: null,
            'video_path' => $data['video_path'] ?: null,
            'discount_percent' => (int)($data['discount_percent'] ?? 0),
            'stock' => (int)$data['stock'],
            'weight_kg' => $data['weight_kg'] !== null && $data['weight_kg'] !== '' ? (float)$data['weight_kg'] : null,
            'dimensions' => $data['dimensions'] ?: null,
            'tags' => $data['tags'] ?: null,
            'status' => (string)($data['status'] ?? 'active'),
            'tracking_code' => (string)$data['tracking_code'],
        ]);
        $this->forgetLists();
        return $ok;
    }

    public function softDelete(int $id): bool
    {
        $stmt = db()->prepare('UPDATE products SET deleted_at = NOW(), status = :s WHERE id = :id');
        $ok = $stmt->execute(['id' => $id, 's' => 'inactive']);
        $this->forgetLists();
        return $ok;
    }

    public function hardDelete(int $id): bool
    {
        $stmt = db()->prepare('DELETE FROM products WHERE id = :id');
        $ok = $stmt->execute(['id' => $id]);
        $this->forgetLists();
        return $ok;
    }

    public function decrementStock(int $id, int $qty): bool
    {
        $stmt = db()->prepare('UPDATE products SET stock = stock - :qty WHERE id = :id AND stock >= :qty');
        return $stmt->execute([
            'id' => $id,
            'qty' => max(1, $qty),
        ]);
    }
}
