<?php

namespace App\Models;

use App\Models\BaseModel;
use Exception;

/**
 * Product Model
 * Handles product data with variants, inventory tracking, and search functionality
 */
class Product extends BaseModel
{
    protected string $table = 'products';
    protected array $fillable = [
        'sku', 'name', 'slug', 'description', 'short_description',
        'category_id', 'brand', 'base_price', 'compare_price', 'cost_price',
        'weight', 'dimensions', 'tags', 'meta_title', 'meta_description',
        'status', 'featured', 'track_inventory', 'allow_backorder', 'min_stock_level'
    ];
    protected array $casts = [
        'base_price' => 'float',
        'compare_price' => 'float',
        'cost_price' => 'float',
        'weight' => 'float',
        'dimensions' => 'json',
        'tags' => 'json',
        'featured' => 'boolean',
        'track_inventory' => 'boolean',
        'allow_backorder' => 'boolean',
        'min_stock_level' => 'integer'
    ];

    /**
     * Get product with all related data
     */
    public function getWithDetails(int $id): ?array
    {
        $product = $this->find($id);
        if (!$product) return null;

        // Get variants
        $product['variants'] = $this->getVariants($id);

        // Get images
        $product['images'] = $this->getImages($id);

        // Get category
        $product['category'] = $this->getCategory($product['category_id']);

        // Calculate total stock
        $product['total_stock'] = $this->getTotalStock($id);

        // Check if in stock
        $product['in_stock'] = $this->isInStock($id);

        return $product;
    }

    /**
     * Get product variants
     */
    public function getVariants(int $productId): array
    {
        return $this->db->query(
            "SELECT * FROM product_variants
             WHERE product_id = ? AND is_active = 1
             ORDER BY position ASC, id ASC",
            [$productId]
        );
    }

    /**
     * Get single product variant
     */
    public function getVariant(int $variantId): ?array
    {
        return $this->db->queryOne(
            "SELECT * FROM product_variants
             WHERE id = ? AND is_active = 1",
            [$variantId]
        );
    }

    /**
     * Get product images
     */
    public function getImages(int $productId): array
    {
        return $this->db->query(
            "SELECT * FROM product_images
             WHERE product_id = ?
             ORDER BY is_primary DESC, position ASC, id ASC",
            [$productId]
        );
    }

    /**
     * Get category information
     */
    public function getCategory(int $categoryId): ?array
    {
        return $this->db->queryOne(
            "SELECT * FROM categories WHERE id = ?",
            [$categoryId]
        );
    }

    /**
     * Get total stock across all variants
     */
    public function getTotalStock(int $productId): int
    {
        $result = $this->db->queryOne(
            "SELECT SUM(inventory_quantity) as total_stock
             FROM product_variants
             WHERE product_id = ? AND is_active = 1",
            [$productId]
        );

        return (int)($result['total_stock'] ?? 0);
    }

    /**
     * Check if product is in stock
     */
    public function isInStock(int $productId): bool
    {
        $product = $this->find($productId);
        if (!$product) return false;

        if (!$product['track_inventory']) {
            return true; // Always in stock if not tracking inventory
        }

        $totalStock = $this->getTotalStock($productId);

        return $totalStock > 0 || $product['allow_backorder'];
    }

    /**
     * Check stock for specific variant
     */
    public function checkVariantStock(int $productId, int $variantId, int $quantity = 1): bool
    {
        $variant = $this->db->queryOne(
            "SELECT pv.inventory_quantity, p.track_inventory, p.allow_backorder
             FROM product_variants pv
             JOIN products p ON pv.product_id = p.id
             WHERE pv.id = ? AND pv.product_id = ? AND pv.is_active = 1",
            [$variantId, $productId]
        );

        if (!$variant) return false;

        if (!$variant['track_inventory']) {
            return true; // Always available if not tracking inventory
        }

        return $variant['inventory_quantity'] >= $quantity || $variant['allow_backorder'];
    }

    /**
     * Reserve stock for order
     */
    public function reserveStock(int $productId, int $variantId, int $quantity): bool
    {
        // Check if we can reserve the stock
        if (!$this->checkVariantStock($productId, $variantId, $quantity)) {
            return false;
        }

        // Reserve the stock
        $updated = $this->db->execute(
            "UPDATE product_variants
             SET inventory_quantity = inventory_quantity - ?
             WHERE id = ? AND product_id = ? AND inventory_quantity >= ?",
            [$quantity, $variantId, $productId, $quantity]
        );

        return $updated > 0;
    }

    /**
     * Release reserved stock (e.g., when order is cancelled)
     */
    public function releaseStock(int $productId, int $variantId, int $quantity): bool
    {
        $updated = $this->db->execute(
            "UPDATE product_variants
             SET inventory_quantity = inventory_quantity + ?
             WHERE id = ? AND product_id = ?",
            [$quantity, $variantId, $productId]
        );

        return $updated > 0;
    }

    /**
     * Search products with filters
     */
    public function search(array $filters = [], int $page = 1, int $limit = 20): array
    {
        $offset = ($page - 1) * $limit;
        $params = [];

        // Base query
        $sql = "SELECT p.*, c.name as category_name, c.slug as category_slug
                FROM products p
                LEFT JOIN categories c ON p.category_id = c.id
                WHERE p.status = 'active'";

        // Search query
        if (!empty($filters['q'])) {
            $sql .= " AND (MATCH(p.name, p.description, p.short_description) AGAINST(? IN NATURAL LANGUAGE MODE)
                      OR p.name LIKE ? OR p.description LIKE ?)";
            $searchTerm = $filters['q'];
            $likeTerm = '%' . $searchTerm . '%';
            $params[] = $searchTerm;
            $params[] = $likeTerm;
            $params[] = $likeTerm;
        }

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

        // Price range filter
        if (!empty($filters['min_price'])) {
            $sql .= " AND p.base_price >= ?";
            $params[] = $filters['min_price'];
        }

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

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

        // Featured filter
        if (!empty($filters['featured'])) {
            $sql .= " AND p.featured = 1";
        }

        // In stock filter
        if (!empty($filters['in_stock'])) {
            $sql .= " AND (
                p.track_inventory = 0 OR
                p.allow_backorder = 1 OR
                EXISTS (
                    SELECT 1 FROM product_variants pv
                    WHERE pv.product_id = p.id AND pv.inventory_quantity > 0 AND pv.is_active = 1
                )
            )";
        }

        // Sorting
        $sortBy = $filters['sort'] ?? 'relevance';
        switch ($sortBy) {
            case 'price_asc':
                $sql .= " ORDER BY p.base_price ASC";
                break;
            case 'price_desc':
                $sql .= " ORDER BY p.base_price DESC";
                break;
            case 'name':
                $sql .= " ORDER BY p.name ASC";
                break;
            case 'newest':
                $sql .= " ORDER BY p.created_at DESC";
                break;
            case 'featured':
                $sql .= " ORDER BY p.featured DESC, p.created_at DESC";
                break;
            default: // relevance
                if (!empty($filters['q'])) {
                    $sql .= " ORDER BY MATCH(p.name, p.description, p.short_description) AGAINST(? IN NATURAL LANGUAGE MODE) DESC";
                    $params[] = $filters['q'];
                } else {
                    $sql .= " ORDER BY p.featured DESC, p.created_at DESC";
                }
        }

        // Get total count for pagination (use separate params for count query)
        $countSql = preg_replace('/SELECT.*?FROM/', 'SELECT COUNT(*) as total FROM', $sql);
        $countSql = preg_replace('/ORDER BY.*$/', '', $countSql);

        // Create count params without the extra search parameter for ORDER BY
        $countParams = $params;
        if (!empty($filters['q']) && ($filters['sort'] ?? 'relevance') === 'relevance') {
            array_pop($countParams); // Remove the last search parameter used for ORDER BY
        }

        $totalResult = $this->db->queryOne($countSql, $countParams);
        $total = (int)($totalResult['total'] ?? 0);

        // Add pagination
        $sql .= " LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;

        // Execute query
        $products = $this->db->query($sql, $params);

        // Add stock information to each product
        foreach ($products as &$product) {
            $product = $this->castAttributes($product);
            $product['total_stock'] = $this->getTotalStock($product['id']);
            $product['in_stock'] = $this->isInStock($product['id']);
        }

        return [
            'data' => $products,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $limit,
                'total' => $total,
                'last_page' => ceil($total / $limit),
                'from' => $offset + 1,
                'to' => min($offset + $limit, $total)
            ]
        ];
    }

    /**
     * Get featured products
     */
    public function getFeatured(int $limit = 8): array
    {
        $products = $this->db->query(
            "SELECT p.*, c.name as category_name
             FROM products p
             LEFT JOIN categories c ON p.category_id = c.id
             WHERE p.status = 'active' AND p.featured = 1
             ORDER BY p.created_at DESC
             LIMIT ?",
            [$limit]
        );

        // Add stock information
        foreach ($products as &$product) {
            $product = $this->castAttributes($product);
            $product['total_stock'] = $this->getTotalStock($product['id']);
            $product['in_stock'] = $this->isInStock($product['id']);
        }

        return $products;
    }

    /**
     * Get related products
     */
    public function getRelated(int $productId, int $limit = 4): array
    {
        $product = $this->find($productId);
        if (!$product) return [];

        $products = $this->db->query(
            "SELECT p.*, c.name as category_name
             FROM products p
             LEFT JOIN categories c ON p.category_id = c.id
             WHERE p.status = 'active'
             AND p.id != ?
             AND (p.category_id = ? OR p.brand = ?)
             ORDER BY
                CASE WHEN p.category_id = ? THEN 1 ELSE 2 END,
                p.featured DESC,
                RAND()
             LIMIT ?",
            [$productId, $product['category_id'], $product['brand'], $product['category_id'], $limit]
        );

        // Add stock information
        foreach ($products as &$relatedProduct) {
            $relatedProduct = $this->castAttributes($relatedProduct);
            $relatedProduct['total_stock'] = $this->getTotalStock($relatedProduct['id']);
            $relatedProduct['in_stock'] = $this->isInStock($relatedProduct['id']);
        }

        return $products;
    }

    /**
     * Get low stock products (for admin)
     */
    public function getLowStock(): array
    {
        return $this->db->query(
            "SELECT p.id, p.name, p.sku, p.min_stock_level,
                    SUM(pv.inventory_quantity) as total_stock,
                    COUNT(pv.id) as variant_count
             FROM products p
             LEFT JOIN product_variants pv ON p.id = pv.product_id AND pv.is_active = 1
             WHERE p.status = 'active' AND p.track_inventory = 1
             GROUP BY p.id
             HAVING total_stock <= p.min_stock_level
             ORDER BY total_stock ASC"
        );
    }

    /**
     * Update product stock
     */
    public function updateStock(int $productId, int $variantId, int $newQuantity): bool
    {
        $updated = $this->db->execute(
            "UPDATE product_variants
             SET inventory_quantity = ?, updated_at = NOW()
             WHERE id = ? AND product_id = ?",
            [$newQuantity, $variantId, $productId]
        );

        return $updated > 0;
    }

    /**
     * Generate unique SKU
     */
    public function generateSku(string $prefix = 'PRD'): string
    {
        do {
            $sku = $prefix . str_pad(mt_rand(1, 999999), 6, '0', STR_PAD_LEFT);
            $exists = $this->db->queryOne("SELECT id FROM products WHERE sku = ?", [$sku]);
        } while ($exists);

        return $sku;
    }

    /**
     * Generate unique slug
     */
    public function generateSlug(string $name, ?int $excludeId = null): string
    {
        $slug = strtolower(trim(preg_replace('/[^A-Za-z0-9-]+/', '-', $name)));
        $originalSlug = $slug;
        $counter = 1;

        do {
            $query = "SELECT id FROM products WHERE slug = ?";
            $params = [$slug];

            if ($excludeId) {
                $query .= " AND id != ?";
                $params[] = $excludeId;
            }

            $exists = $this->db->queryOne($query, $params);

            if ($exists) {
                $slug = $originalSlug . '-' . $counter;
                $counter++;
            }
        } while ($exists);

        return $slug;
    }

    /**
     * Get products with advanced filtering (for ProductController)
     */
    public function getProducts(array $filters = [], int $limit = 20, int $offset = 0): array
    {
        $page = ($offset / $limit) + 1;
        $result = $this->search($filters, $page, $limit);

        return [
            'products' => $result['data'],
            'total' => $result['pagination']['total']
        ];
    }

    /**
     * Search products with suggestions (for ProductController)
     */
    public function searchProducts(string $query, array $filters = [], int $limit = 20, int $offset = 0): array
    {
        $filters['q'] = $query;
        $page = ($offset / $limit) + 1;
        $result = $this->search($filters, $page, $limit);

        // Get suggestions
        $suggestions = $this->getSuggestions($query, 5);

        return [
            'products' => $result['data'],
            'total' => $result['pagination']['total'],
            'suggestions' => $suggestions
        ];
    }

    /**
     * Get search suggestions
     */
    public function getSuggestions(string $query, int $limit = 10): array
    {
        $suggestions = $this->db->query(
            "SELECT DISTINCT name, slug
             FROM products
             WHERE status = 'active'
             AND (name LIKE ? OR description LIKE ?)
             ORDER BY
                CASE
                    WHEN name LIKE ? THEN 1
                    WHEN name LIKE ? THEN 2
                    ELSE 3
                END,
                name ASC
             LIMIT ?",
            ["%$query%", "%$query%", $query, "$query%", $limit]
        );

        return $suggestions;
    }

    /**
     * Get product with variants (alias for getWithDetails)
     */
    public function getWithVariants(int $id): ?array
    {
        return $this->getWithDetails($id);
    }

    /**
     * Get featured products (alias for getFeatured)
     */
    public function getFeaturedProducts(int $limit = 10): array
    {
        return $this->getFeatured($limit);
    }

    /**
     * Get related products (alias for getRelated)
     */
    public function getRelatedProducts(int $productId, int $limit = 8): array
    {
        return $this->getRelated($productId, $limit);
    }

    /**
     * Get stock information for a product/variant
     */
    public function getStockInfo(int $productId, ?int $variantId = null): ?array
    {
        if ($variantId) {
            return $this->db->queryOne(
                "SELECT inventory_quantity, sku
                 FROM product_variants
                 WHERE id = ? AND product_id = ? AND is_active = 1",
                [$variantId, $productId]
            );
        } else {
            return [
                'total_stock' => $this->getTotalStock($productId),
                'variant_count' => count($this->getVariants($productId))
            ];
        }
    }

    /**
     * Check if product has orders
     */
    public function hasOrders(int $productId): bool
    {
        $result = $this->db->queryOne(
            "SELECT COUNT(*) as order_count
             FROM order_items
             WHERE product_id = ?",
            [$productId]
        );

        return (int)($result['order_count'] ?? 0) > 0;
    }

    /**
     * Check stock (alias for checkVariantStock)
     */
    public function checkStock(int $productId, ?int $variantId = null, int $quantity = 1): bool
    {
        if ($variantId) {
            return $this->checkVariantStock($productId, $variantId, $quantity);
        } else {
            $totalStock = $this->getTotalStock($productId);
            $product = $this->find($productId);

            if (!$product || !$product['track_inventory']) {
                return true;
            }

            return $totalStock >= $quantity || $product['allow_backorder'];
        }
    }

    /**
     * Count products by category
     */
    public function countByCategory(int $categoryId): int
    {
        $result = $this->db->queryOne(
            "SELECT COUNT(*) as count FROM products WHERE category_id = ?",
            [$categoryId]
        );

        return (int)($result['count'] ?? 0);
    }

}