<?php
/**
 * ============================================================
 * PRODUCT SERVICE
 * ============================================================
 * Handles product CRUD operations and queries
 */

class ProductService {
    
    /**
     * Get products with filters and pagination
     * @param array $filters
     * @param int $limit
     * @param int $offset
     * @return array
     */
    public static function getAll(array $filters = [], int $limit = 12, int $offset = 0): array {
        $where = "p.is_active = 1";
        $params = [];
        
        if (!empty($filters['category_id'])) {
            $where .= " AND p.category_id = ?";
            $params[] = $filters['category_id'];
        }
        
        if (!empty($filters['category_slug'])) {
            $where .= " AND c.slug = ?";
            $params[] = $filters['category_slug'];
        }
        
        if (!empty($filters['featured'])) {
            $where .= " AND p.is_featured = 1";
        }
        
        if (!empty($filters['search'])) {
            $where .= " AND (p.name LIKE ? OR p.description LIKE ? OR p.sku LIKE ?)";
            $search = '%' . $filters['search'] . '%';
            $params[] = $search;
            $params[] = $search;
            $params[] = $search;
        }
        
        if (!empty($filters['min_price'])) {
            $where .= " AND COALESCE(p.sale_price, p.price) >= ?";
            $params[] = $filters['min_price'];
        }
        
        if (!empty($filters['max_price'])) {
            $where .= " AND COALESCE(p.sale_price, p.price) <= ?";
            $params[] = $filters['max_price'];
        }
        
        // Sort order
        $orderBy = "p.created_at DESC";
        if (!empty($filters['sort'])) {
            switch ($filters['sort']) {
                case 'price_low':
                    $orderBy = "COALESCE(p.sale_price, p.price) ASC";
                    break;
                case 'price_high':
                    $orderBy = "COALESCE(p.sale_price, p.price) DESC";
                    break;
                case 'popular':
                    $orderBy = "p.total_sold DESC";
                    break;
                case 'rating':
                    $orderBy = "p.avg_rating DESC";
                    break;
                case 'newest':
                default:
                    $orderBy = "p.created_at DESC";
            }
        }
        
        $params[] = $limit;
        $params[] = $offset;
        
        $products = Database::fetchAll(
            "SELECT p.*, c.name as category_name, c.slug as category_slug,
                    (SELECT file_path FROM product_images WHERE product_id = p.id AND is_primary = 1 LIMIT 1) as primary_image
             FROM products p
             JOIN categories c ON p.category_id = c.id
             WHERE {$where}
             ORDER BY {$orderBy}
             LIMIT ? OFFSET ?",
            $params
        );
        
        return $products;
    }
    
    /**
     * Get featured products
     * @param int $limit
     * @return array
     */
    public static function getFeatured(int $limit = 8): array {
        return self::getAll(['featured' => true], $limit);
    }
    
    /**
     * Get best sellers
     * @param int $limit
     * @return array
     */
    public static function getBestSellers(int $limit = 8): array {
        return self::getAll(['sort' => 'popular'], $limit);
    }
    
    /**
     * Get product by ID with all images
     * @param int $id
     * @return array|null
     */
    public static function getById(int $id): ?array {
        $product = Database::fetchOne(
            "SELECT p.*, c.name as category_name, c.slug as category_slug
             FROM products p
             JOIN categories c ON p.category_id = c.id
             WHERE p.id = ?",
            [$id]
        );
        
        if (!$product) return null;
        
        $product['images'] = Database::fetchAll(
            "SELECT * FROM product_images WHERE product_id = ? ORDER BY is_primary DESC, sort_order ASC",
            [$id]
        );
        
        return $product;
    }
    
    /**
     * Get product by slug
     * @param string $slug
     * @return array|null
     */
    public static function getBySlug(string $slug): ?array {
        $product = Database::fetchOne(
            "SELECT p.*, c.name as category_name, c.slug as category_slug
             FROM products p
             JOIN categories c ON p.category_id = c.id
             WHERE p.slug = ? AND p.is_active = 1",
            [$slug]
        );
        
        if (!$product) return null;
        
        $product['images'] = Database::fetchAll(
            "SELECT * FROM product_images WHERE product_id = ? ORDER BY is_primary DESC, sort_order ASC",
            [$product['id']]
        );
        
        return $product;
    }
    
    /**
     * Create product (admin)
     * @param array $data
     * @return int Product ID
     */
    public static function create(array $data): int {
        $slug = self::generateUniqueSlug($data['name']);
        
        return Database::insert(
            "INSERT INTO products (
                category_id, name, slug, sku, short_description, description,
                price, sale_price, stock_qty, is_active, is_featured,
                meta_title, meta_description
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            [
                $data['category_id'],
                $data['name'],
                $slug,
                $data['sku'] ?? null,
                $data['short_description'] ?? null,
                $data['description'] ?? null,
                $data['price'],
                $data['sale_price'] ?? null,
                $data['stock_qty'] ?? 0,
                $data['is_active'] ?? 1,
                $data['is_featured'] ?? 0,
                $data['meta_title'] ?? $data['name'],
                $data['meta_description'] ?? null
            ]
        );
    }
    
    /**
     * Update product (admin)
     * @param int $id
     * @param array $data
     * @return bool
     */
    public static function update(int $id, array $data): bool {
        $product = self::getById($id);
        if (!$product) return false;
        
        // Generate new slug if name changed
        $slug = $product['slug'];
        if ($data['name'] !== $product['name']) {
            $slug = self::generateUniqueSlug($data['name'], $id);
        }
        
        return Database::execute(
            "UPDATE products SET
                category_id = ?, name = ?, slug = ?, sku = ?, short_description = ?,
                description = ?, price = ?, sale_price = ?, stock_qty = ?,
                is_active = ?, is_featured = ?, meta_title = ?, meta_description = ?
             WHERE id = ?",
            [
                $data['category_id'],
                $data['name'],
                $slug,
                $data['sku'] ?? null,
                $data['short_description'] ?? null,
                $data['description'] ?? null,
                $data['price'],
                $data['sale_price'] ?? null,
                $data['stock_qty'] ?? 0,
                $data['is_active'] ?? 1,
                $data['is_featured'] ?? 0,
                $data['meta_title'] ?? $data['name'],
                $data['meta_description'] ?? null,
                $id
            ]
        ) >= 0;
    }
    
    /**
     * Delete product (admin)
     * @param int $id
     * @return bool
     */
    public static function delete(int $id): bool {
        // Get images to delete files
        $images = Database::fetchAll(
            "SELECT file_path FROM product_images WHERE product_id = ?",
            [$id]
        );
        
        // Delete product (cascade deletes images records)
        $deleted = Database::execute("DELETE FROM products WHERE id = ?", [$id]) > 0;
        
        if ($deleted) {
            // Delete image files
            foreach ($images as $img) {
                Upload::delete('products/' . $img['file_path']);
            }
        }
        
        return $deleted;
    }
    
    /**
     * Add product image
     * @param int $productId
     * @param string $filePath
     * @param bool $isPrimary
     * @return int Image ID
     */
    public static function addImage(int $productId, string $filePath, bool $isPrimary = false): int {
        // If setting as primary, unset others
        if ($isPrimary) {
            Database::execute(
                "UPDATE product_images SET is_primary = 0 WHERE product_id = ?",
                [$productId]
            );
        }
        
        // Get max sort order
        $maxSort = (int)Database::fetchColumn(
            "SELECT COALESCE(MAX(sort_order), 0) FROM product_images WHERE product_id = ?",
            [$productId]
        );
        
        return Database::insert(
            "INSERT INTO product_images (product_id, file_path, file_name, is_primary, sort_order)
             VALUES (?, ?, ?, ?, ?)",
            [$productId, $filePath, basename($filePath), $isPrimary ? 1 : 0, $maxSort + 1]
        );
    }
    
    /**
     * Delete product image
     * @param int $imageId
     * @return bool
     */
    public static function deleteImage(int $imageId): bool {
        $image = Database::fetchOne(
            "SELECT * FROM product_images WHERE id = ?",
            [$imageId]
        );
        
        if (!$image) return false;
        
        $deleted = Database::execute("DELETE FROM product_images WHERE id = ?", [$imageId]) > 0;
        
        if ($deleted) {
            Upload::delete('products/' . $image['file_path']);
        }
        
        return $deleted;
    }
    
    /**
     * Set primary image
     * @param int $productId
     * @param int $imageId
     * @return bool
     */
    public static function setPrimaryImage(int $productId, int $imageId): bool {
        Database::execute(
            "UPDATE product_images SET is_primary = 0 WHERE product_id = ?",
            [$productId]
        );
        
        return Database::execute(
            "UPDATE product_images SET is_primary = 1 WHERE id = ? AND product_id = ?",
            [$imageId, $productId]
        ) > 0;
    }
    
    /**
     * Generate unique slug
     * @param string $name
     * @param int|null $excludeId
     * @return string
     */
    private static function generateUniqueSlug(string $name, ?int $excludeId = null): string {
        $baseSlug = generateSlug($name);
        $slug = $baseSlug;
        $counter = 1;
        
        while (true) {
            $params = [$slug];
            $excludeClause = "";
            
            if ($excludeId) {
                $excludeClause = " AND id != ?";
                $params[] = $excludeId;
            }
            
            $exists = Database::fetchColumn(
                "SELECT COUNT(*) FROM products WHERE slug = ?{$excludeClause}",
                $params
            );
            
            if (!$exists) break;
            
            $slug = $baseSlug . '-' . $counter;
            $counter++;
        }
        
        return $slug;
    }
    
    /**
     * Get related products
     * @param int $productId
     * @param int $categoryId
     * @param int $limit
     * @return array
     */
    public static function getRelated(int $productId, int $categoryId, int $limit = 4): array {
        return Database::fetchAll(
            "SELECT p.*, 
                    (SELECT file_path FROM product_images WHERE product_id = p.id AND is_primary = 1 LIMIT 1) as primary_image
             FROM products p
             WHERE p.category_id = ? AND p.id != ? AND p.is_active = 1
             ORDER BY RAND()
             LIMIT ?",
            [$categoryId, $productId, $limit]
        );
    }
    
    /**
     * Get product count
     * @param array $filters
     * @return int
     */
    public static function getCount(array $filters = []): int {
        $where = "p.is_active = 1";
        $params = [];
        
        if (!empty($filters['category_id'])) {
            $where .= " AND p.category_id = ?";
            $params[] = $filters['category_id'];
        }
        
        if (!empty($filters['search'])) {
            $where .= " AND (p.name LIKE ? OR p.description LIKE ?)";
            $search = '%' . $filters['search'] . '%';
            $params[] = $search;
            $params[] = $search;
        }
        
        return (int)Database::fetchColumn(
            "SELECT COUNT(*) FROM products p WHERE {$where}",
            $params
        );
    }
    
    /**
     * Get all products for admin (including inactive)
     * @param int $limit
     * @param int $offset
     * @return array
     */
    public static function getAllAdmin(int $limit = 20, int $offset = 0): array {
        return Database::fetchAll(
            "SELECT p.*, c.name as category_name,
                    (SELECT file_path FROM product_images WHERE product_id = p.id AND is_primary = 1 LIMIT 1) as primary_image
             FROM products p
             JOIN categories c ON p.category_id = c.id
             ORDER BY p.created_at DESC
             LIMIT ? OFFSET ?",
            [$limit, $offset]
        );
    }
}
