<?php
/**
 * Product Management Helper Functions
 * XChange 2025 Registration System
 */

class ProductManager {
    private $conn;
    
    public function __construct($database_connection) {
        $this->conn = $database_connection;
    }
    
    /**
     * Get all products with category and stock information
     */
    public function getAllProducts($filters = []) {
        $sql = "SELECT p.*, pc.name as category_name,
                       CASE 
                           WHEN p.total_stock IS NULL THEN 'Unlimited'
                           WHEN p.available_stock > (p.total_stock * 0.2) THEN 'High'
                           WHEN p.available_stock > 0 THEN 'Low'
                           ELSE 'Out of Stock'
                       END as stock_level
                FROM products p
                LEFT JOIN product_categories pc ON p.category_id = pc.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['category_id'])) {
            $sql .= " AND p.category_id = ?";
            $params[] = $filters['category_id'];
        }
        
        if (!empty($filters['stock_level'])) {
            switch ($filters['stock_level']) {
                case 'high':
                    $sql .= " AND (p.total_stock IS NULL OR p.available_stock > (p.total_stock * 0.2))";
                    break;
                case 'low':
                    $sql .= " AND p.total_stock IS NOT NULL AND p.available_stock > 0 AND p.available_stock <= (p.total_stock * 0.2)";
                    break;
                case 'out':
                    $sql .= " AND p.total_stock IS NOT NULL AND p.available_stock = 0";
                    break;
            }
        }
        
        // Add frontend visibility filter if specified
        if (isset($filters['frontend_visible_only']) && $filters['frontend_visible_only']) {
            $sql .= " AND p.show_in_frontend = 1";
        }
        
        $sql .= " ORDER BY pc.display_order, p.display_order";
        
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get product purchases with registration details
     */
    public function getProductPurchases($filters = [], $limit = 50, $offset = 0) {
        $sql = "SELECT rp.*, p.name as product_name, p.product_code, 
                       pc.name as category_name,
                       r.registration_number, r.contact_full_name as contact_name, r.contact_email,
                       r.payment_status, r.created_at as registration_date
                FROM registration_products rp
                JOIN products p ON rp.product_id = p.id
                LEFT JOIN product_categories pc ON p.category_id = pc.id
                JOIN registrations r ON rp.registration_id = r.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['product_id'])) {
            $sql .= " AND rp.product_id = ?";
            $params[] = $filters['product_id'];
        }
        
        if (!empty($filters['category_id'])) {
            $sql .= " AND p.category_id = ?";
            $params[] = $filters['category_id'];
        }
        
        if (!empty($filters['stock_status'])) {
            $sql .= " AND rp.stock_status = ?";
            $params[] = $filters['stock_status'];
        }
        
        if (!empty($filters['payment_status'])) {
            $sql .= " AND r.payment_status = ?";
            $params[] = $filters['payment_status'];
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (r.registration_number LIKE ? OR r.contact_full_name LIKE ? OR r.contact_email LIKE ? OR p.name LIKE ?)";
            $searchTerm = '%' . $filters['search'] . '%';
            $params[] = $searchTerm;
            $params[] = $searchTerm;
            $params[] = $searchTerm;
            $params[] = $searchTerm;
        }
        
        $sql .= " ORDER BY rp.created_at DESC LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get total count for pagination
     */
    public function getProductPurchasesCount($filters = []) {
        $sql = "SELECT COUNT(*) as total
                FROM registration_products rp
                JOIN products p ON rp.product_id = p.id
                JOIN registrations r ON rp.registration_id = r.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['product_id'])) {
            $sql .= " AND rp.product_id = ?";
            $params[] = $filters['product_id'];
        }
        
        if (!empty($filters['category_id'])) {
            $sql .= " AND p.category_id = ?";
            $params[] = $filters['category_id'];
        }
        
        if (!empty($filters['stock_status'])) {
            $sql .= " AND rp.stock_status = ?";
            $params[] = $filters['stock_status'];
        }
        
        if (!empty($filters['payment_status'])) {
            $sql .= " AND r.payment_status = ?";
            $params[] = $filters['payment_status'];
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (r.registration_number LIKE ? OR r.contact_full_name LIKE ? OR r.contact_email LIKE ? OR p.name LIKE ?)";
            $searchTerm = '%' . $filters['search'] . '%';
            $params[] = $searchTerm;
            $params[] = $searchTerm;
            $params[] = $searchTerm;
            $params[] = $searchTerm;
        }
        
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetch(PDO::FETCH_ASSOC)['total'];
    }
    
    /**
     * Get all products (alias for getAllProducts for compatibility)
     */
    public function getProducts($filters = []) {
        return $this->getAllProducts($filters);
    }
    
    /**
     * Get products visible in frontend only
     */
    public function getFrontendVisibleProducts($filters = []) {
        $filters['frontend_visible_only'] = true;
        return $this->getAllProducts($filters);
    }
    
    /**
     * Get single product by ID
     */
    public function getProduct($id) {
        $sql = "SELECT p.*, pc.name as category_name
                FROM products p
                LEFT JOIN product_categories pc ON p.category_id = pc.id
                WHERE p.id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get product categories
     */
    public function getProductCategories($includeInactive = false) {
        $sql = "SELECT pc.*, 
                       COUNT(p.id) as product_count
                FROM product_categories pc
                LEFT JOIN products p ON pc.id = p.category_id AND p.is_active = 1";
        if (!$includeInactive) {
            $sql .= " WHERE pc.is_active = 1";
        }
        $sql .= " GROUP BY pc.id, pc.name, pc.description, pc.display_order, pc.is_active, pc.created_at, pc.updated_at
                  ORDER BY pc.display_order, pc.name";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Create new product category
     */
    public function createCategory($data) {
        $sql = "INSERT INTO product_categories (name, description, display_order, is_active, created_at) 
                VALUES (?, ?, ?, 1, NOW())";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([
            $data['name'],
            $data['description'] ?? '',
            $data['display_order'] ?? 0
        ]);
        return $this->conn->lastInsertId();
    }
    
    /**
     * Update product category
     */
    public function updateCategory($id, $data) {
        $sql = "UPDATE product_categories 
                SET name = ?, description = ?, display_order = ?, is_active = ?, updated_at = NOW() 
                WHERE id = ?";
        $stmt = $this->conn->prepare($sql);
        return $stmt->execute([
            $data['name'],
            $data['description'] ?? '',
            $data['display_order'] ?? 0,
            $data['is_active'] ?? 1,
            $id
        ]);
    }
    
    /**
     * Delete product category (only if no products assigned)
     */
    public function deleteCategory($id) {
        // Check if category has products
        $checkSql = "SELECT COUNT(*) FROM products WHERE category_id = ?";
        $checkStmt = $this->conn->prepare($checkSql);
        $checkStmt->execute([$id]);
        $productCount = $checkStmt->fetchColumn();
        
        if ($productCount > 0) {
            throw new Exception("Cannot delete category with {$productCount} assigned products. Move products to another category first.");
        }
        
        $sql = "DELETE FROM product_categories WHERE id = ?";
        $stmt = $this->conn->prepare($sql);
        return $stmt->execute([$id]);
    }
    
    /**
     * Get category by ID
     */
    public function getCategoryById($id) {
        $sql = "SELECT * FROM product_categories WHERE id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get product purchase summary statistics
     */
    public function getProductSummary() {
        $sql = "SELECT 
                    COUNT(DISTINCT rp.id) as total_purchases,
                    SUM(CASE WHEN r.payment_status = 'paid' THEN rp.total_price ELSE 0 END) as total_revenue,
                    SUM(CASE WHEN r.payment_status != 'paid' THEN rp.total_price ELSE 0 END) as unpaid_amount,
                    COUNT(CASE WHEN rp.stock_status = 'confirmed' THEN 1 END) as confirmed_purchases,
                    COUNT(CASE WHEN rp.stock_status = 'reserved' THEN 1 END) as pending_purchases,
                    COUNT(CASE WHEN rp.stock_status = 'cancelled' THEN 1 END) as cancelled_purchases,
                    COUNT(DISTINCT rp.registration_id) as unique_registrations
                FROM registration_products rp
                JOIN registrations r ON rp.registration_id = r.id";
        
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get product-wise sales summary
     */
    public function getProductSales() {
        $sql = "SELECT p.name, p.product_code,
                       COUNT(rp.id) as total_orders,
                       SUM(rp.quantity) as total_quantity,
                       SUM(rp.total_price) as total_revenue,
                       COUNT(CASE WHEN rp.stock_status = 'confirmed' THEN 1 END) as confirmed_orders,
                       p.total_stock, p.available_stock, p.sold_stock
                FROM products p
                LEFT JOIN registration_products rp ON p.id = rp.product_id
                WHERE p.is_active = 1
                GROUP BY p.id
                ORDER BY total_revenue DESC";
        
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Update stock status for a registration product
     */
    public function updateStockStatus($registration_product_id, $new_status, $admin_user_id = null, $reason = '') {
        try {
            $this->conn->beginTransaction();
            
            // Get current registration product details
            $sql = "SELECT rp.*, p.name as product_name 
                    FROM registration_products rp 
                    JOIN products p ON rp.product_id = p.id 
                    WHERE rp.id = ?";
            $stmt = $this->conn->prepare($sql);
            $stmt->execute([$registration_product_id]);
            $rp = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if (!$rp) {
                throw new Exception("Registration product not found");
            }
            
            $old_status = $rp['stock_status'];
            
            // Update the registration product status
            $sql = "UPDATE registration_products SET 
                        stock_status = ?,
                        confirmed_at = CASE WHEN ? = 'confirmed' THEN NOW() ELSE confirmed_at END,
                        updated_at = NOW()
                    WHERE id = ?";
            $stmt = $this->conn->prepare($sql);
            $stmt->execute([$new_status, $new_status, $registration_product_id]);
            
            // Update product stock counts
            $this->updateProductStockCounts($rp['product_id']);
            
            // Log the stock movement
            $movement_type = $this->getMovementType($old_status, $new_status);
            if ($movement_type) {
                $this->logStockMovement($rp['product_id'], $rp['registration_id'], $movement_type, 
                                      $rp['quantity'], $admin_user_id, $reason);
            }
            
            $this->conn->commit();
            return true;
            
        } catch (Exception $e) {
            $this->conn->rollBack();
            throw $e;
        }
    }
    
    /**
     * Update product stock counts based on registration products
     */
    private function updateProductStockCounts($product_id) {
        $sql = "UPDATE products SET 
                    sold_stock = (
                        SELECT COALESCE(SUM(quantity), 0) 
                        FROM registration_products 
                        WHERE product_id = ? AND stock_status = 'confirmed'
                    ),
                    reserved_stock = (
                        SELECT COALESCE(SUM(quantity), 0) 
                        FROM registration_products 
                        WHERE product_id = ? AND stock_status = 'reserved'
                    )
                WHERE id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([$product_id, $product_id, $product_id]);
    }
    
    /**
     * Log stock movement for audit trail
     */
    private function logStockMovement($product_id, $registration_id, $movement_type, $quantity, $admin_user_id = null, $reason = '') {
        // Get current stock levels
        $sql = "SELECT available_stock FROM products WHERE id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([$product_id]);
        $product = $stmt->fetch(PDO::FETCH_ASSOC);
        
        $quantity_change = 0;
        $new_available = $product['available_stock'];
        $previous_available = $new_available;
        
        switch ($movement_type) {
            case 'reserve':
                $quantity_change = -$quantity;
                $previous_available = $new_available + $quantity;
                break;
            case 'release':
                $quantity_change = $quantity;
                $previous_available = $new_available - $quantity;
                break;
            case 'sell':
                // No change to available stock (already reserved)
                $quantity_change = 0;
                break;
        }
        
        $sql = "INSERT INTO product_stock_movements 
                (product_id, registration_id, movement_type, quantity_change, 
                 previous_available, new_available, reason, admin_user_id)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([$product_id, $registration_id, $movement_type, $quantity_change,
                       $previous_available, $new_available, $reason, $admin_user_id]);
    }
    
    /**
     * Determine movement type based on status change
     */
    private function getMovementType($old_status, $new_status) {
        if ($old_status === 'reserved' && $new_status === 'confirmed') {
            return 'sell';
        } elseif ($old_status === 'reserved' && $new_status === 'cancelled') {
            return 'release';
        } elseif ($old_status === 'confirmed' && $new_status === 'cancelled') {
            return 'refund';
        } elseif ($old_status === 'confirmed' && $new_status === 'refunded') {
            return 'refund';
        }
        return null;
    }
    
    /**
     * Check if product is available for purchase
     */
    public function isProductAvailable($product_id, $quantity = 1, $check_frontend_visibility = false) {
        $sql = "SELECT p.*, 
                       CASE 
                           WHEN p.total_stock IS NULL THEN 999999
                           ELSE p.available_stock
                       END as available_qty
                FROM products p 
                WHERE p.id = ? AND p.is_active = 1";
        
        // Add frontend visibility check if requested
        if ($check_frontend_visibility) {
            $sql .= " AND p.show_in_frontend = 1";
        }
        
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([$product_id]);
        $product = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$product) {
            return false;
        }
        
        // Check sale period
        $now = date('Y-m-d H:i:s');
        if ($product['sale_start_date'] && $now < $product['sale_start_date']) {
            return false;
        }
        if ($product['sale_end_date'] && $now > $product['sale_end_date']) {
            return false;
        }
        
        // Check stock availability
        if ($product['available_qty'] < $quantity) {
            return false;
        }
        
        return true;
    }
    
    /**
     * Get current pricing for a product
     */
    public function getCurrentPrice($product_id) {
        $now = date('Y-m-d H:i:s');
        
        $sql = "SELECT pp.price, pp.pricing_tier
                FROM product_pricing pp
                WHERE pp.product_id = ? 
                AND pp.is_active = 1
                AND (pp.valid_from IS NULL OR pp.valid_from <= ?)
                AND (pp.valid_until IS NULL OR pp.valid_until >= ?)
                ORDER BY pp.valid_from DESC
                LIMIT 1";
        
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([$product_id, $now, $now]);
        $pricing = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($pricing) {
            return $pricing;
        }
        
        // Fallback to base price
        $sql = "SELECT base_price as price, 'standard' as pricing_tier FROM products WHERE id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([$product_id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
}
?>
