<?php
class Product
{
    private $conn;
    private $table_name = "products";

    public $id;
    public $name;
    public $brand;
    public $description;
    public $base_price;
    public $sku;
    public $stock;
    public $category_id;
    public $is_active;
    public $created_at;
    public $updated_at;

    public function __construct($db)
    {
        $this->conn = $db;
    }

    // Get all products with pagination and filters
    public function getProducts($page = 1, $limit = 12, $category = null, $brand = null, $search = null, $minPrice = null, $maxPrice = null, $sortBy = 'created_at', $sortOrder = 'DESC')
    {
        $offset = ($page - 1) * $limit;

        $query = "SELECT 
                    p.id, p.name, p.brand, p.description, p.base_price, p.sku, p.stock,
                    p.discount_percentage, p.discounted_price, p.has_deal,
                    c.name as category_name, c.slug as category_slug,
                    (SELECT pi.url FROM product_images pi WHERE pi.product_id = p.id ORDER BY pi.position ASC LIMIT 1) as image_url
                  FROM " . $this->table_name . " p
                  LEFT JOIN categories c ON p.category_id = c.id
                  WHERE p.is_active = 1";

        $params = [];
        $countParams = [];

        if ($category) {
            $query .= " AND c.slug = :category";
            $params[':category'] = $category;
            $countParams[':category'] = $category;
        }

        if ($brand) {
            $brandWildcard = '%' . $brand . '%';
            $query .= " AND p.brand LIKE :brand";
            $params[':brand'] = $brandWildcard;
            $countParams[':brand'] = $brandWildcard;
        }

        if ($search) {
            $searchWildcard = '%' . $search . '%';
            $query .= " AND (p.name LIKE :search OR p.brand LIKE :search2 OR p.description LIKE :search3)";
            $params[':search'] = $searchWildcard;
            $params[':search2'] = $searchWildcard;
            $params[':search3'] = $searchWildcard;
            $countParams[':search'] = $searchWildcard;
            $countParams[':search2'] = $searchWildcard;
            $countParams[':search3'] = $searchWildcard;
        }

        if ($minPrice !== null) {
            $query .= " AND p.base_price >= :minPrice";
            $params[':minPrice'] = $minPrice;
            $countParams[':minPrice'] = $minPrice;
        }

        if ($maxPrice !== null) {
            $query .= " AND p.base_price <= :maxPrice";
            $params[':maxPrice'] = $maxPrice;
            $countParams[':maxPrice'] = $maxPrice;
        }

        // --- TOTAL COUNT QUERY ---
        $countQuery = "SELECT COUNT(*) FROM " . $this->table_name . " p LEFT JOIN categories c ON p.category_id = c.id WHERE p.is_active = 1";
        if ($category) $countQuery .= " AND c.slug = :category";
        if ($brand) $countQuery .= " AND p.brand LIKE :brand";
        if ($search) $countQuery .= " AND (p.name LIKE :search OR p.brand LIKE :search2 OR p.description LIKE :search3)";
        if ($minPrice !== null) $countQuery .= " AND p.base_price >= :minPrice";
        if ($maxPrice !== null) $countQuery .= " AND p.base_price <= :maxPrice";

        $countStmt = $this->conn->prepare($countQuery);

        // ### THIS WAS THE BUG ###
        // The execute call was empty, it should use $countParams
        $countStmt->execute($countParams);
        // ### END OF BUG FIX ###

        $totalItems = $countStmt->fetchColumn();


        // --- MAIN PRODUCT QUERY ---
        // Add sorting (whitelisting columns to prevent SQL injection)
        $allowedSortBy = ['created_at', 'base_price', 'name'];
        $sortColumn = in_array($sortBy, $allowedSortBy) ? 'p.' . $sortBy : 'p.created_at';
        $sortDirection = strtoupper($sortOrder) === 'ASC' ? 'ASC' : 'DESC';
        $query .= " ORDER BY $sortColumn $sortDirection";

        // Add pagination
        $query .= " LIMIT :limit OFFSET :offset";
        $params[':limit'] = $limit;
        $params[':offset'] = $offset;

        $stmt = $this->conn->prepare($query);

        // Bind parameters
        foreach ($params as $key => &$value) {
            if ($key === ':limit' || $key === ':offset') {
                $stmt->bindParam($key, $value, PDO::PARAM_INT);
            } else {
                $stmt->bindParam($key, $value);
            }
        }

        $stmt->execute();
        $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Add variants for each product
        foreach ($products as &$product) {
            $variantQuery = "SELECT id, variant_name, price_diff, stock, sku FROM product_variants WHERE product_id = :id";
            $variantStmt = $this->conn->prepare($variantQuery);
            $variantStmt->bindParam(':id', $product['id']);
            $variantStmt->execute();
            $product['variants'] = $variantStmt->fetchAll(PDO::FETCH_ASSOC);
        }

        return [
            'products' => $products,
            'pagination' => [
                'currentPage' => (int)$page,
                'totalPages' => ceil($totalItems / $limit),
                'totalItems' => (int)$totalItems,
                'itemsPerPage' => (int)$limit
            ]
        ];
    }

    // ... rest of the file is the same
    // Get single product by ID
    public function getProductById($id)
    {
        $query = "SELECT 
                    p.id, p.name, p.brand, p.description, p.base_price, p.sku, p.stock,
                    p.discount_percentage, p.discounted_price, p.has_deal,
                    c.name as category_name, c.slug as category_slug
                  FROM " . $this->table_name . " p
                  LEFT JOIN categories c ON p.category_id = c.id
                  WHERE p.id = :id AND p.is_active = 1";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

        $product = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($product) {
            // Get product images
            $imageQuery = "SELECT url, alt_text, position FROM product_images WHERE product_id = :id ORDER BY position";
            $imageStmt = $this->conn->prepare($imageQuery);
            $imageStmt->bindParam(':id', $id);
            $imageStmt->execute();
            $product['images'] = $imageStmt->fetchAll(PDO::FETCH_ASSOC);

            // Get product variants
            $variantQuery = "SELECT id, variant_name, price_diff, stock, sku FROM product_variants WHERE product_id = :id";
            $variantStmt = $this->conn->prepare($variantQuery);
            $variantStmt->bindParam(':id', $id);
            $variantStmt->execute();
            $product['variants'] = $variantStmt->fetchAll(PDO::FETCH_ASSOC);
        }

        return $product;
    }

    // Create new product (Admin only)
    public function create()
    {
        $query = "INSERT INTO " . $this->table_name . "
                  SET name = :name, brand = :brand, description = :description,
                      base_price = :base_price, sku = :sku, stock = :stock,
                      category_id = :category_id, created_at = NOW(), updated_at = NOW()";

        $stmt = $this->conn->prepare($query);

        // Sanitize
        $this->name = htmlspecialchars(strip_tags($this->name));
        $this->brand = htmlspecialchars(strip_tags($this->brand));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->base_price = htmlspecialchars(strip_tags($this->base_price));
        $this->sku = htmlspecialchars(strip_tags($this->sku));
        $this->stock = htmlspecialchars(strip_tags($this->stock));
        $this->category_id = htmlspecialchars(strip_tags($this->category_id));

        // Bind values
        $stmt->bindParam(':name', $this->name);
        $stmt->bindParam(':brand', $this->brand);
        $stmt->bindParam(':description', $this->description);
        $stmt->bindParam(':base_price', $this->base_price);
        $stmt->bindParam(':sku', $this->sku);
        $stmt->bindParam(':stock', $this->stock);
        $stmt->bindParam(':category_id', $this->category_id);

        if ($stmt->execute()) {
            return $this->conn->lastInsertId();
        }

        return false;
    }

    // Update product
    public function update()
    {
        $query = "UPDATE " . $this->table_name . "
                  SET name = :name, brand = :brand, description = :description,
                      base_price = :base_price, stock = :stock,
                      category_id = :category_id, updated_at = NOW()
                  WHERE id = :id";

        $stmt = $this->conn->prepare($query);

        // Sanitize
        $this->name = htmlspecialchars(strip_tags($this->name));
        $this->brand = htmlspecialchars(strip_tags($this->brand));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->base_price = htmlspecialchars(strip_tags($this->base_price));
        $this->stock = htmlspecialchars(strip_tags($this->stock));
        $this->category_id = htmlspecialchars(strip_tags($this->category_id));
        $this->id = htmlspecialchars(strip_tags($this->id));

        // Bind values
        $stmt->bindParam(':name', $this->name);
        $stmt->bindParam(':brand', $this->brand);
        $stmt->bindParam(':description', $this->description);
        $stmt->bindParam(':base_price', $this->base_price);
        $stmt->bindParam(':stock', $this->stock);
        $stmt->bindParam(':category_id', $this->category_id);
        $stmt->bindParam(':id', $this->id);

        return $stmt->execute();
    }

    // Delete product (soft delete)
    public function delete()
    {
        $query = "UPDATE " . $this->table_name . " SET is_active = 0 WHERE id = :id";
        $stmt = $this->conn->prepare($query);
        $this->id = htmlspecialchars(strip_tags($this->id));
        $stmt->bindParam(':id', $this->id);
        return $stmt->execute();
    }

    // Get related products based on category and brand
    public function getRelatedProducts($productId, $limit = 8)
    {
        // First get the current product's category and brand
        $currentProductQuery = "SELECT category_id, brand FROM " . $this->table_name . " WHERE id = :id";
        $currentStmt = $this->conn->prepare($currentProductQuery);
        $currentStmt->bindParam(':id', $productId);
        $currentStmt->execute();
        $currentProduct = $currentStmt->fetch(PDO::FETCH_ASSOC);

        if (!$currentProduct) {
            return [];
        }

        // Get related products from same category or brand, excluding current product
        $query = "SELECT 
                    p.id, p.name, p.brand, p.description, p.base_price, p.sku, p.stock,
                    c.name as category_name, c.slug as category_slug,
                    (SELECT pi.url FROM product_images pi WHERE pi.product_id = p.id ORDER BY pi.position ASC LIMIT 1) as image_url,
                    CASE 
                        WHEN p.category_id = :category_id AND p.brand = :brand THEN 3
                        WHEN p.category_id = :category_id2 THEN 2
                        WHEN p.brand = :brand2 THEN 1
                        ELSE 0
                    END as relevance_score
                  FROM " . $this->table_name . " p
                  LEFT JOIN categories c ON p.category_id = c.id
                  WHERE p.is_active = 1 
                    AND p.id != :product_id
                    AND (p.category_id = :category_id3 OR p.brand = :brand3)
                  ORDER BY relevance_score DESC, RAND()
                  LIMIT :limit";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':category_id', $currentProduct['category_id']);
        $stmt->bindParam(':category_id2', $currentProduct['category_id']);
        $stmt->bindParam(':category_id3', $currentProduct['category_id']);
        $stmt->bindParam(':brand', $currentProduct['brand']);
        $stmt->bindParam(':brand2', $currentProduct['brand']);
        $stmt->bindParam(':brand3', $currentProduct['brand']);
        $stmt->bindParam(':product_id', $productId);
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);

        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}
