<?php

namespace App\Models;

use App\Core\Model;

class Product extends Model
{
    protected string $table = 'products';
    protected string $primaryKey = 'product_id';

    public function findWithCategory(int $id): ?array
    {
        $sql = "SELECT p.*, c.name as category_name, c.slug as category_slug,
                COALESCE(p.rating, 0) as rating,
                COALESCE(p.review_count, 0) as review_count
                FROM {$this->table} p
                LEFT JOIN categories c ON p.category_id = c.category_id
                WHERE p.product_id = ?";
        return $this->queryOne($sql, [$id]);
    }

    public function getAvailable(array $filters = [], int $limit = 50): array
    {
        $sql = "SELECT p.*, c.name as category_name,
                COALESCE(p.rating, 0) as rating,
                COALESCE(p.review_count, 0) as review_count
                FROM {$this->table} p
                LEFT JOIN categories c ON p.category_id = c.category_id
                WHERE p.is_available = TRUE";
        $params = [];

        if (!empty($filters['category_id'])) {
            $sql .= " AND p.category_id = ?";
            $params[] = $filters['category_id'];
        }

        if (!empty($filters['min_price'])) {
            $sql .= " AND p.price >= ?";
            $params[] = $filters['min_price'];
        }
        if (!empty($filters['max_price'])) {
            $sql .= " AND p.price <= ?";
            $params[] = $filters['max_price'];
        }

        if (!empty($filters['colors']) && is_array($filters['colors'])) {
            $placeholders = implode(',', array_fill(0, count($filters['colors']), '?'));
            $sql .= " AND p.color IN ({$placeholders})";
            $params = array_merge($params, $filters['colors']);
        }

        if (!empty($filters['materials']) && is_array($filters['materials'])) {
            $placeholders = implode(',', array_fill(0, count($filters['materials']), '?'));
            $sql .= " AND p.material IN ({$placeholders})";
            $params = array_merge($params, $filters['materials']);
        }

        if (!empty($filters['search'])) {
            $sql .= " AND (p.name ILIKE ? OR p.description ILIKE ?)";
            $search = '%' . $filters['search'] . '%';
            $params[] = $search;
            $params[] = $search;
        }

        $sql .= " ORDER BY p.product_id ASC LIMIT ?";
        $params[] = $limit;

        return $this->query($sql, $params);
    }

    public function getAllForAdmin(bool $showAll = true): array
    {
        $sql = "SELECT p.*, c.name as category_name,
                COALESCE(p.rating, 0) as rating,
                COALESCE(p.review_count, 0) as review_count
                FROM {$this->table} p
                LEFT JOIN categories c ON p.category_id = c.category_id";
        
        if (!$showAll) {
            $sql .= " WHERE p.is_available = TRUE";
        }
        
        $sql .= " ORDER BY p.created_at DESC";
        
        return $this->query($sql);
    }

    public function getSimilar(int $productId, int $categoryId, int $limit = 3): array
    {
        $sql = "SELECT *,
                COALESCE(rating, 0) as rating,
                COALESCE(review_count, 0) as review_count
                FROM {$this->table}
                WHERE category_id = ?
                AND product_id != ?
                AND is_available = TRUE
                ORDER BY RANDOM()
                LIMIT ?";
        return $this->query($sql, [$categoryId, $productId, $limit]);
    }

    public function getAvailableColors(): array
    {
        $sql = "SELECT DISTINCT color FROM {$this->table}
                WHERE color IS NOT NULL AND color != '' AND is_available = TRUE
                ORDER BY color";
        $result = $this->query($sql);
        return array_column($result, 'color');
    }

    public function getAvailableMaterials(): array
    {
        $sql = "SELECT DISTINCT material FROM {$this->table}
                WHERE material IS NOT NULL AND material != '' AND is_available = TRUE
                ORDER BY material";
        $result = $this->query($sql);
        return array_column($result, 'material');
    }

    public function createProduct(array $data): ?int
    {
        $slug = $this->generateSlug($data['name']);
        $sku = $data['sku'] ?? $this->generateSku($data['name']);
        
        return $this->create([
            'category_id' => $data['category_id'],
            'name' => $data['name'],
            'slug' => $slug,
            'description' => $data['description'] ?? null,
            'price' => $data['price'],
            'old_price' => $data['old_price'] ?? null,
            'sku' => $sku,
            'stock_quantity' => $data['stock_quantity'] ?? 0,
            'is_available' => $data['is_available'] ?? true,
            'is_featured' => $data['is_featured'] ?? false,
            'image_url' => $data['image_url'] ?? null,
            'color' => $data['color'] ?? null,
            'material' => $data['material'] ?? null,
            'card_size' => $data['card_size'] ?? 'small'
        ]);
    }

    public function updateProduct(int $id, array $data): bool
    {
        $updateData = [
            'name' => $data['name'],
            'category_id' => $data['category_id'],
            'description' => $data['description'] ?? null,
            'price' => $data['price'],
            'old_price' => $data['old_price'] ?? null,
            'stock_quantity' => $data['stock_quantity'] ?? 0,
            'is_available' => $data['is_available'] ?? true,
            'image_url' => $data['image_url'] ?? null,
            'color' => $data['color'] ?? null,
            'material' => $data['material'] ?? null,
            'updated_at' => date('Y-m-d H:i:s')
        ];
        
        return $this->update($id, $updateData);
    }

    public function decreaseStock(int $productId, int $quantity): bool
    {
        $sql = "UPDATE {$this->table}
                SET stock_quantity = stock_quantity - ?,
                    updated_at = CURRENT_TIMESTAMP
                WHERE product_id = ?";
        return $this->execute($sql, [$quantity, $productId]);
    }

    public function checkStock(int $productId, int $quantity): bool
    {
        $product = $this->find($productId);
        return $product && $product['is_available'] && $product['stock_quantity'] >= $quantity;
    }

    private function generateSlug(string $name): string
    {
        $slug = mb_strtolower($name);
        
        $transliteration = [
            'а' => 'a', 'б' => 'b', 'в' => 'v', 'г' => 'g', 'д' => 'd',
            'е' => 'e', 'ё' => 'yo', 'ж' => 'zh', 'з' => 'z', 'и' => 'i',
            'й' => 'y', 'к' => 'k', 'л' => 'l', 'м' => 'm', 'н' => 'n',
            'о' => 'o', 'п' => 'p', 'р' => 'r', 'с' => 's', 'т' => 't',
            'у' => 'u', 'ф' => 'f', 'х' => 'h', 'ц' => 'ts', 'ч' => 'ch',
            'ш' => 'sh', 'щ' => 'sch', 'ъ' => '', 'ы' => 'y', 'ь' => '',
            'э' => 'e', 'ю' => 'yu', 'я' => 'ya'
        ];
        
        $slug = strtr($slug, $transliteration);
        $slug = preg_replace('/[^a-z0-9]+/', '-', $slug);
        
        return trim($slug, '-');
    }

    private function generateSku(string $name): string
    {
        $prefix = strtoupper(substr(preg_replace('/[^a-zA-Z0-9]/', '', $name), 0, 6));
        return 'PROD-' . $prefix . '-' . rand(100, 999);
    }
}