<?php
// app/Models/Item.php - Item Model

class Item {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Create item
     */
    public function create($data) {
        $sql = "INSERT INTO items (vendor_id, category_id, title, slug, description, price_per_day, 
                security_deposit, quantity, available_quantity, condition_status, location, city, state, 
                features, terms_and_conditions, main_image, status, approval_status, created_at) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', NOW())";
        
        $result = $this->db->execute($sql, [
            $data['vendor_id'],
            $data['category_id'],
            $data['title'],
            $data['slug'],
            $data['description'],
            $data['price_per_day'],
            $data['security_deposit'] ?? 0,
            $data['quantity'] ?? 1,
            $data['quantity'] ?? 1,
            $data['condition_status'] ?? 'good',
            $data['location'] ?? null,
            $data['city'],
            $data['state'],
            $data['features'] ?? null,
            $data['terms_and_conditions'] ?? null,
            $data['main_image'],
            $data['status'] ?? 'draft'
        ]);
        
        return $result ? $this->db->lastInsertId() : false;
    }


    /**
     * Approve an item
     */
    public function approve($item_id, $admin_id, $notes = null)
    {
        try {
            $sql = "UPDATE items SET 
                    approval_status = 'approved',
                    status = 'approved',
                    approved_by = ?,
                    approval_date = NOW(),
                    updated_at = NOW()
                    WHERE id = ?";
            
            return $this->db->execute($sql, [$admin_id, $item_id]);
        } catch (Exception $e) {
            throw new Exception("Failed to approve item: " . $e->getMessage());
        }
    }
    
    /**
     * Find item by ID
     */
    public function find($id) {
        $sql = "SELECT i.*, 
                    v.business_name as vendor_name, 
                    v.phone as vendor_phone,
                    v.address as vendor_address,
                    v.city as vendor_city,
                    v.state as vendor_state,
                    v.business_type as vendor_business_type,
                    v.created_at as vendor_created_at,
                    v.logo as vendor_logo,
                    c.name as category_name,
                    u.id as vendor_user_id,
                    u.email as vendor_email
                FROM items i
                JOIN vendors v ON i.vendor_id = v.id
                JOIN users u ON v.user_id = u.id
                JOIN categories c ON i.category_id = c.id
                WHERE i.id = ? LIMIT 1";
        
        $item = $this->db->fetch($sql, [$id]);
        
        if ($item) {
            // Get additional images
            $images_sql = "SELECT * FROM item_images WHERE item_id = ? ORDER BY display_order ASC";
            $item['images'] = $this->db->fetchAll($images_sql, [$id]);
        }
        
        return $item;
    }
    
    /**
     * Find item by slug
     */
    public function findBySlug($slug) {
        $sql = "SELECT i.*, 
                    v.business_name as vendor_name, 
                    v.phone as vendor_phone,
                    v.address as vendor_address,
                    v.city as vendor_city,
                    v.state as vendor_state,
                    v.business_type as vendor_business_type,
                    v.created_at as vendor_created_at,
                    v.logo as vendor_logo,
                    c.name as category_name,
                    c.slug as category_slug,  -- ADD THIS LINE
                    u.id as vendor_user_id,
                    u.email as vendor_email
                FROM items i
                JOIN vendors v ON i.vendor_id = v.id
                JOIN users u ON v.user_id = u.id
                JOIN categories c ON i.category_id = c.id
                WHERE i.slug = ? LIMIT 1";
        
        $item = $this->db->fetch($sql, [$slug]);
        
        if ($item) {
            // Get additional images
            $images_sql = "SELECT image_url FROM item_images WHERE item_id = ? ORDER BY display_order ASC";
            $additional_images = $this->db->fetchAll($images_sql, [$item['id']]);
            
            // Build images array
            $images = [];
            foreach ($additional_images as $img) {
                $images[] = $img['image_url'];
            }
            
            // If no additional images, use main_image
            if (empty($images) && !empty($item['main_image'])) {
                $images[] = $item['main_image'];
            }
            
            // If still no images, use default
            if (empty($images)) {
                $images[] = 'assets/images/default-item.jpg';
            }
            
            // IMPORTANT: Convert to JSON string for frontend compatibility
            $item['images'] = json_encode($images);
            
            // Add computed fields
            $item['daily_rate'] = $item['price_per_day'];
            $item['featured'] = $item['is_featured'] ?? 0;
            $item['availability_status'] = ($item['available_quantity'] > 0) ? 'available' : 'unavailable';
            
            // Increment views
            $this->incrementViews($item['id']);
        }
        
        return $item;
    }
    
    /**
     * Get all items with pagination
     */
    public function getAll($limit = 20, $offset = 0, $filters = []) {
        $sql = "SELECT i.*, v.business_name as vendor_name, c.name as category_name,
                (SELECT COUNT(*) FROM orders WHERE item_id = i.id) as total_bookings
                FROM items i
                JOIN vendors v ON i.vendor_id = v.id
                JOIN categories c ON i.category_id = c.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND i.vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        if (!empty($filters['category_id'])) {
            $sql .= " AND i.category_id = ?";
            $params[] = $filters['category_id'];
        }
        
        if (!empty($filters['status'])) {
            $sql .= " AND i.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['approval_status'])) {
            $sql .= " AND i.approval_status = ?";
            $params[] = $filters['approval_status'];
        }
        
        if (!empty($filters['city'])) {
            $sql .= " AND i.city = ?";
            $params[] = $filters['city'];
        }
        
        if (!empty($filters['state'])) {
            $sql .= " AND i.state = ?";
            $params[] = $filters['state'];
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (i.title LIKE ? OR i.description LIKE ?)";
            $search = '%' . $filters['search'] . '%';
            $params[] = $search;
            $params[] = $search;
        }
        
        if (!empty($filters['min_price'])) {
            $sql .= " AND i.price_per_day >= ?";
            $params[] = $filters['min_price'];
        }
        
        if (!empty($filters['max_price'])) {
            $sql .= " AND i.price_per_day <= ?";
            $params[] = $filters['max_price'];
        }
        
        // Sorting
        $order_by = " ORDER BY ";
        if (!empty($filters['sort'])) {
            switch ($filters['sort']) {
                case 'price_low':
                    $order_by .= "i.price_per_day ASC";
                    break;
                case 'price_high':
                    $order_by .= "i.price_per_day DESC";
                    break;
                case 'popular':
                    $order_by .= "i.views_count DESC";
                    break;
                case 'rating':
                    $order_by .= "i.rating_average DESC";
                    break;
                default:
                    $order_by .= "i.created_at DESC";
            }
        } else {
            $order_by .= "i.created_at DESC";
        }
        
        $sql .= $order_by . " LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Count items
     */
    public function count($filters = []) {
        $sql = "SELECT COUNT(*) as count FROM items i WHERE 1=1";
        $params = [];
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND i.vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        if (!empty($filters['category_id'])) {
            $sql .= " AND i.category_id = ?";
            $params[] = $filters['category_id'];
        }
        
        if (!empty($filters['status'])) {
            $sql .= " AND i.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['approval_status'])) {
            $sql .= " AND i.approval_status = ?";
            $params[] = $filters['approval_status'];
        }
        
        $result = $this->db->fetch($sql, $params);
        return $result['count'] ?? 0;
    }
    
    /**
     * Update item
     */
    public function update($id, $data) {
        $fields = [];
        $params = [];
        
        foreach ($data as $key => $value) {
            $fields[] = "{$key} = ?";
            $params[] = $value;
        }
        
        $params[] = $id;
        
        $sql = "UPDATE items SET " . implode(', ', $fields) . ", updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, $params);
    }
    
    /**
     * Update approval status
     */
    public function updateApprovalStatus($id, $status, $approved_by, $reason = null) {
        $sql = "UPDATE items SET 
                approval_status = ?, 
                approved_by = ?, 
                approval_date = NOW(),
                rejection_reason = ?,
                updated_at = NOW() 
                WHERE id = ?";
        
        return $this->db->execute($sql, [$status, $approved_by, $reason, $id]);
    }
    
    /**
     * Update available quantity
     */
    public function updateAvailableQuantity($id, $quantity) {
        $sql = "UPDATE items SET available_quantity = ?, updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, [$quantity, $id]);
    }
    
    /**
     * Increment views
     */
    public function incrementViews($id) {
        $sql = "UPDATE items SET views_count = views_count + 1 WHERE id = ?";
        return $this->db->execute($sql, [$id]);
    }
    
    /**
     * Update rating
     */
    public function updateRating($id) {
        $sql = "UPDATE items i SET 
                rating_average = (SELECT COALESCE(AVG(rating), 0) FROM reviews WHERE item_id = i.id),
                rating_count = (SELECT COUNT(*) FROM reviews WHERE item_id = i.id)
                WHERE i.id = ?";
        
        return $this->db->execute($sql, [$id]);
    }
    
    /**
     * Delete item
     */
    /**
     * Delete item and related data (except transactions)
     */
    public function delete($id) {
        $db = Database::getInstance();
        
        // Start transaction
        $db->beginTransaction();
        
        try {
            // Get item details for file deletion
            $item = $this->find($id);
            if (!$item) {
                throw new Exception('Item not found');
            }
            
            // Get all item images for physical file deletion
            $images_sql = "SELECT * FROM item_images WHERE item_id = ?";
            $images = $db->fetchAll($images_sql, [$id]);
            
            // Delete physical image files
            if (!empty($item['main_image'])) {
                $this->deleteFile('items/' . $item['main_image']);
            }
            
            foreach ($images as $image) {
                $this->deleteFile('items/' . $image['image_path']);
            }
            
            // Delete item images records
            $db->execute("DELETE FROM item_images WHERE item_id = ?", [$id]);
            
            // Delete reviews
            $db->execute("DELETE FROM reviews WHERE item_id = ?", [$id]);
            
            // Delete item updates
            $db->execute("DELETE FROM item_updates WHERE item_id = ?", [$id]);
            
            // Delete the item itself (transactions remain via item_id reference)
            $db->execute("DELETE FROM items WHERE id = ?", [$id]);
            
            $db->commit();
            return true;
        } catch (Exception $e) {
            $db->rollBack();
            throw new Exception("Failed to delete item: " . $e->getMessage());
        }
    }
    
    /**
     * Get pending approval items
     */
    public function getPendingApproval($limit = 10) {
        $sql = "SELECT i.*, v.business_name as vendor_name, c.name as category_name
                FROM items i
                JOIN vendors v ON i.vendor_id = v.id
                JOIN categories c ON i.category_id = c.id
                WHERE i.approval_status = 'pending'
                ORDER BY i.created_at ASC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Get featured items
     */
    // public function getFeatured($limit = 10) {
    //     $sql = "SELECT i.*, v.business_name as vendor_name, c.name as category_name
    //             FROM items i
    //             JOIN vendors v ON i.vendor_id = v.id
    //             JOIN categories c ON i.category_id = c.id
    //             WHERE i.approval_status = 'approved' AND i.status = 'approved'
    //             ORDER BY i.rating_average DESC, i.views_count DESC
    //             LIMIT ?";
        
    //     return $this->db->fetchAll($sql, [$limit]);
    // }
    
    /**
     * Get popular items
     */
    // public function getPopular($limit = 10) {
    //     $sql = "SELECT i.*, v.business_name as vendor_name, c.name as category_name,
    //             (SELECT COUNT(*) FROM orders WHERE item_id = i.id) as booking_count
    //             FROM items i
    //             JOIN vendors v ON i.vendor_id = v.id
    //             JOIN categories c ON i.category_id = c.id
    //             WHERE i.approval_status = 'approved' AND i.status = 'approved'
    //             ORDER BY i.views_count DESC, booking_count DESC
    //             LIMIT ?";
        
    //     return $this->db->fetchAll($sql, [$limit]);
    // }
    
    /**
     * Get related items
     */
    public function getRelated($item_id, $category_id, $limit = 6) {
        $sql = "SELECT i.*, v.business_name as vendor_name, c.name as category_name
                FROM items i
                JOIN vendors v ON i.vendor_id = v.id
                JOIN categories c ON i.category_id = c.id
                WHERE i.category_id = ? AND i.id != ? 
                AND i.approval_status = 'approved' AND i.status = 'approved'
                ORDER BY RAND()
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$category_id, $item_id, $limit]);
    }
    
    /**
     * Search items
     */
    public function search($keyword, $limit = 20) {
        $sql = "SELECT i.*, v.business_name as vendor_name, c.name as category_name
                FROM items i
                JOIN vendors v ON i.vendor_id = v.id
                JOIN categories c ON i.category_id = c.id
                WHERE i.approval_status = 'approved' AND i.status = 'approved'
                AND (i.title LIKE ? OR i.description LIKE ? OR c.name LIKE ?)
                ORDER BY i.rating_average DESC, i.views_count DESC
                LIMIT ?";
        
        $search = '%' . $keyword . '%';
        return $this->db->fetchAll($sql, [$search, $search, $search, $limit]);
    }
    
    /**
     * Check if slug exists
     */
    public function slugExists($slug, $exclude_id = null) {
        if ($exclude_id) {
            $sql = "SELECT id FROM items WHERE slug = ? AND id != ? LIMIT 1";
            $result = $this->db->fetch($sql, [$slug, $exclude_id]);
        } else {
            $sql = "SELECT id FROM items WHERE slug = ? LIMIT 1";
            $result = $this->db->fetch($sql, [$slug]);
        }
        
        return $result !== null;
    }
    
    /**
     * Get item statistics
     */
    public function getStats() {
        $sql = "SELECT 
                COUNT(*) as total_items,
                SUM(CASE WHEN approval_status = 'approved' THEN 1 ELSE 0 END) as approved_items,
                SUM(CASE WHEN approval_status = 'pending' THEN 1 ELSE 0 END) as pending_items,
                SUM(CASE WHEN approval_status = 'rejected' THEN 1 ELSE 0 END) as rejected_items,
                SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as active_items,
                SUM(CASE WHEN DATE(created_at) >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) as new_this_week,
                SUM(CASE WHEN DATE(created_at) >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) as new_this_month
                FROM items";
        
        return $this->db->fetch($sql);
    }

    /**
     * Count all items
     */
    // public function count() {
    //     $sql = "SELECT COUNT(*) as total FROM items";
    //     $result = $this->db->fetch($sql);
    //     return $result['total'] ?? 0;
    // }

    /**
     * Count items by status
     */
    public function countByStatus($status) {
        $sql = "SELECT COUNT(*) as total FROM items WHERE approval_status = ?";
        $result = $this->db->fetch($sql, [$status]);
        return $result['total'] ?? 0;
    }

    /**
     * Count items by vendor
     */
    public function countByVendor($vendor_id) {
        $sql = "SELECT COUNT(*) as total FROM items WHERE vendor_id = ?";
        $result = $this->db->fetch($sql, [$vendor_id]);
        return $result['total'] ?? 0;
    }

    /**
     * Count items by vendor and status
     */
    public function countByVendorAndStatus($vendor_id, $status) {
        $sql = "SELECT COUNT(*) as total FROM items WHERE vendor_id = ? AND approval_status = ?";
        $result = $this->db->fetch($sql, [$vendor_id, $status]);
        return $result['total'] ?? 0;
    }


    /**
 * Reject an item with reason
 */
    public function reject($item_id, $admin_id, $reason)
    {
        try {
            $sql = "UPDATE items SET 
                    approval_status = 'rejected',
                    status = 'rejected',
                    rejection_reason = ?,
                    rejected_by = ?,
                    rejected_at = NOW(),
                    updated_at = NOW()
                    WHERE id = ?";
            
            // Use execute() method instead of query()
            return $this->db->execute($sql, [$reason, $admin_id, $item_id]);
        } catch (Exception $e) {
            throw new Exception("Failed to reject item: " . $e->getMessage());
        }
    }

    /**
     * Track item updates for admin review
     */
    public function trackUpdate($item_id, $vendor_id, $changes, $update_reason = '') {
        $sql = "INSERT INTO item_updates (item_id, vendor_id, changes, update_reason, status, created_at) 
                VALUES (?, ?, ?, ?, 'pending', NOW())";
        
        $result = $this->db->execute($sql, [
            $item_id,
            $vendor_id,
            json_encode($changes),
            $update_reason
        ]);
        
        // Update approval_status in items table
        if ($result) {
            $update_sql = "UPDATE items SET approval_status = 'pending' WHERE id = ?";
            $this->db->execute($update_sql, [$item_id]);
        }
        
        return $result;
    }

    /**
     * Get pending updates count
     */
    public function getPendingUpdatesCount() {
        $sql = "SELECT COUNT(*) as count FROM item_updates WHERE status = 'pending'";
        $result = $this->db->fetch($sql);
        return $result['count'] ?? 0;
    }

    /**
     * Get item updates for admin review
     */
    public function getItemUpdates($filters = [], $limit = 20, $offset = 0) {
        $sql = "SELECT iu.*,
                i.title as item_title,
                i.slug as item_slug,
                i.main_image as item_image,
                i.approval_status as original_approval_status,
                v.business_name as vendor_name,
                u.email as vendor_email,
                reviewer.email as reviewer_email
                FROM item_updates iu
                LEFT JOIN items i ON iu.item_id = i.id
                LEFT JOIN vendors v ON iu.vendor_id = v.id
                LEFT JOIN users u ON v.user_id = u.id
                LEFT JOIN users reviewer ON iu.reviewed_by = reviewer.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['status'])) {
            $sql .= " AND iu.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (i.title LIKE ? OR v.business_name LIKE ? OR iu.update_reason LIKE ?)";
            $search_term = "%{$filters['search']}%";
            $params[] = $search_term;
            $params[] = $search_term;
            $params[] = $search_term;
        }
        
        $sql .= " ORDER BY iu.created_at ASC LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        
        return $this->db->fetchAll($sql, $params);
    }

    /**
     * Count item updates
     */
    public function countItemUpdates($filters = []) {
        $sql = "SELECT COUNT(*) as count FROM item_updates iu
                LEFT JOIN items i ON iu.item_id = i.id
                LEFT JOIN vendors v ON iu.vendor_id = v.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['status'])) {
            $sql .= " AND iu.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['item_id'])) {
            $sql .= " AND iu.item_id = ?";
            $params[] = $filters['item_id'];
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (i.title LIKE ? OR v.business_name LIKE ? OR iu.update_reason LIKE ?)";
            $search_term = "%{$filters['search']}%";
            $params[] = $search_term;
            $params[] = $search_term;
            $params[] = $search_term;
        }
        
        $result = $this->db->fetch($sql, $params);
        return $result['count'] ?? 0;
    }

    /**
     * Get update by ID
     */
    public function getUpdate($update_id) {
        $sql = "SELECT iu.id,
                iu.item_id,
                iu.vendor_id,
                iu.changes,
                iu.update_reason,
                COALESCE(iu.status, 'pending') as status,
                COALESCE(iu.status, 'pending') as approval_status,
                iu.created_at,
                iu.reviewed_by,
                iu.reviewed_at,
                i.title,
                i.title as item_title,
                i.main_image as item_image,
                i.description,
                i.price_per_day,
                i.security_deposit,
                i.quantity,
                i.condition_status,
                i.location,
                i.city,
                i.state,
                i.features,
                i.terms_and_conditions,
                i.approval_status as item_approval_status,
                v.business_name as vendor_name,
                u.email as vendor_email,
                reviewer.email as reviewer_email
                FROM item_updates iu
                LEFT JOIN items i ON iu.item_id = i.id
                LEFT JOIN vendors v ON iu.vendor_id = v.id
                LEFT JOIN users u ON v.user_id = u.id
                LEFT JOIN users reviewer ON iu.reviewed_by = reviewer.id
                WHERE iu.id = ?";
        
        return $this->db->fetch($sql, [$update_id]);
    }

    /**
     * Approve item update - SIMPLIFIED VERSION
     */
    public function approveUpdate($update_id, $admin_id) {
        $db = Database::getInstance();
        
        try {
            // Get the update
            $update = $db->fetch("SELECT * FROM item_updates WHERE id = ?", [$update_id]);
            
            if (!$update) {
                return false;
            }
            
            // Apply the changes
            $changes = json_decode($update['changes'], true);
            if ($changes) {
                foreach ($changes as $field => $value) {
                    $db->execute("UPDATE items SET {$field} = ?, updated_at = NOW() WHERE id = ?", 
                                [$value, $update['item_id']]);
                }
            }
            
            // Mark update as approved
            $db->execute("UPDATE item_updates SET status = 'approved', reviewed_by = ?, reviewed_at = NOW() WHERE id = ?", 
                        [$admin_id, $update_id]);
            
            // Update item approval status
            $db->execute("UPDATE items SET approval_status = 'approved', approved_by = ?, approval_date = NOW() WHERE id = ?", 
                        [$admin_id, $update['item_id']]);
            
            // Check for more pending updates
            $pending = $db->fetch("SELECT COUNT(*) as cnt FROM item_updates WHERE item_id = ? AND status = 'pending'", 
                                 [$update['item_id']]);
            
            // If more pending exist, set back to pending
            if ($pending['cnt'] > 0) {
                $db->execute("UPDATE items SET approval_status = 'pending' WHERE id = ?", [$update['item_id']]);
            }
            
            return true;
            
        } catch (Exception $e) {
            error_log("Approve error: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Reject item update - SIMPLIFIED VERSION
     */
    public function rejectUpdate($update_id, $admin_id, $review_notes = '') {
        $db = Database::getInstance();
        
        try {
            // Get the update
            $update = $db->fetch("SELECT * FROM item_updates WHERE id = ?", [$update_id]);
            
            if (!$update) {
                return false;
            }
            
            // Mark update as rejected
            $db->execute("UPDATE item_updates SET status = 'rejected', reviewed_by = ?, reviewed_at = NOW(), review_notes = ? WHERE id = ?", 
                        [$admin_id, $review_notes, $update_id]);
            
            // Check for more pending updates
            $pending = $db->fetch("SELECT COUNT(*) as cnt FROM item_updates WHERE item_id = ? AND status = 'pending'", 
                                 [$update['item_id']]);
            
            // If no more pending, set item to approved
            if ($pending['cnt'] == 0) {
                $db->execute("UPDATE items SET approval_status = 'approved' WHERE id = ?", [$update['item_id']]);
            }
            
            return true;
            
        } catch (Exception $e) {
            error_log("Reject error: " . $e->getMessage());
            return false;
        }
    }


    /**
     * Delete a file from the uploads directory
     */
    public function deleteFile($file_path) {
        $full_path = UPLOAD_PATH . '/' . $file_path;
        
        if (file_exists($full_path) && is_file($full_path)) {
            return unlink($full_path);
        }
        
        return false;
    }

    /**
     * Get featured items
     */
    public function getFeatured(? int $limit = 12) {
        $sql = "SELECT i.*, 
                c.name as category_name, c.icon as category_icon,
                v.business_name as vendor_name,
                COALESCE(AVG(r.rating), 0) as average_rating,
                COUNT(DISTINCT r.id) as total_reviews
                FROM items i
                LEFT JOIN categories c ON i.category_id = c.id
                LEFT JOIN vendors v ON i.vendor_id = v.id
                LEFT JOIN reviews r ON i.id = r.item_id AND r.status = 'active'
                WHERE i.featured = 1 
                AND i.status = 'approved' 
                AND i.approval_status = 'approved'
                GROUP BY i.id
                ORDER BY i.created_at DESC
                LIMIT ?";
        
        $items = $this->db->fetchAll($sql, [$limit]);
        
        // Post-process to add images
        foreach ($items as &$item) {
            $images = [];
            
            $img_sql = "SELECT image_url FROM item_images WHERE item_id = ? ORDER BY display_order ASC";
            $additional_images = $this->db->fetchAll($img_sql, [$item['id']]);
            
            if (!empty($additional_images)) {
                foreach ($additional_images as $img) {
                    $images[] = $img['image_url'];
                }
            }
            
            if (empty($images) && !empty($item['main_image'])) {
                $images[] = $item['main_image'];
            }
            
            if (empty($images)) {
                $images[] = 'assets/images/default-item.jpg';
            }
            
            $item['images'] = json_encode($images);
            $item['daily_rate'] = $item['price_per_day'];
            $item['featured'] = $item['is_featured'] ?? 0;
        }
        
        return $items;
    }

    /**
     * Get popular items (most booked)
     */
    public function getPopular($limit = 8) {
        $sql = "SELECT i.*, 
                c.name as category_name,
                v.business_name as vendor_name,
                COUNT(DISTINCT o.id) as total_bookings,
                COALESCE(AVG(r.rating), 0) as average_rating,
                COUNT(DISTINCT r.id) as total_reviews
                FROM items i
                LEFT JOIN categories c ON i.category_id = c.id
                LEFT JOIN vendors v ON i.vendor_id = v.id
                LEFT JOIN orders o ON i.id = o.item_id
                LEFT JOIN reviews r ON i.id = r.item_id AND r.status = 'active'
                WHERE i.status = 'approved' 
                AND i.approval_status = 'approved'
                GROUP BY i.id
                ORDER BY total_bookings DESC, average_rating DESC
                LIMIT ?";
        
        $items = $this->db->fetchAll($sql, [$limit]);
        
        // Post-process to add images
        foreach ($items as &$item) {
            $images = [];
            
            $img_sql = "SELECT image_url FROM item_images WHERE item_id = ? ORDER BY display_order ASC";
            $additional_images = $this->db->fetchAll($img_sql, [$item['id']]);
            
            if (!empty($additional_images)) {
                foreach ($additional_images as $img) {
                    $images[] = $img['image_url'];
                }
            }
            
            if (empty($images) && !empty($item['main_image'])) {
                $images[] = $item['main_image'];
            }
            
            if (empty($images)) {
                $images[] = 'assets/images/default-item.jpg';
            }
            
            $item['images'] = json_encode($images);
            $item['daily_rate'] = $item['price_per_day'];
            $item['featured'] = $item['is_featured'] ?? 0;
        }
        
        return $items;
    }

    /**
     * Get count of items by category
     */
    public function getCountByCategory($category_id) {
        $sql = "SELECT COUNT(*) as count 
                FROM items 
                WHERE category_id = ? 
                AND status = 'active' 
                AND approval_status = 'approved'";
        
        $result = $this->db->fetch($sql, [$category_id]);
        return $result['count'] ?? 0;
    }

    /**
     * Get total count of active items
     */
    public function getTotalCount() {
        $sql = "SELECT COUNT(*) as count 
                FROM items 
                WHERE status = 'active' 
                AND approval_status = 'approved'";
        
        $result = $this->db->fetch($sql);
        return $result['count'] ?? 0;
    }

    /**
     * Get items with filters
     */
    public function getWithFilters($filters = []) {
        // Build WHERE conditions
        $whereConditions = "i.status = 'approved' AND i.approval_status = 'approved'";
        $params = [];
        
        // Search filter
        if (!empty($filters['search'])) {
            $whereConditions .= " AND (i.title LIKE ? OR i.description LIKE ? OR c.name LIKE ?)";
            $searchTerm = '%' . $filters['search'] . '%';
            $params[] = $searchTerm;
            $params[] = $searchTerm;
            $params[] = $searchTerm;
        }
        
        // Category filter
        if (!empty($filters['category'])) {
            $whereConditions .= " AND c.slug = ?";
            $params[] = $filters['category'];
        }
        
        // Location filter
        if (!empty($filters['location'])) {
            $whereConditions .= " AND (i.city LIKE ? OR i.state LIKE ?)";
            $params[] = '%' . $filters['location'] . '%';
            $params[] = '%' . $filters['location'] . '%';
        }
        
        // Price range filter
        if (isset($filters['min_price']) && $filters['min_price'] > 0) {
            $whereConditions .= " AND i.price_per_day >= ?";
            $params[] = $filters['min_price'];
        }
        
        if (isset($filters['max_price']) && $filters['max_price'] > 0) {
            $whereConditions .= " AND i.price_per_day <= ?";
            $params[] = $filters['max_price'];
        }
        
        // Count total items BEFORE pagination
        $countSql = "SELECT COUNT(DISTINCT i.id) as total 
                    FROM items i
                    LEFT JOIN categories c ON i.category_id = c.id
                    WHERE " . $whereConditions;
        
        $totalResult = $this->db->fetch($countSql, $params);
        $total = $totalResult['total'] ?? 0;
        
        // Main query with all fields - USE SAME PARAMS
        $sql = "SELECT i.*, 
                c.name as category_name,
                c.slug as category_slug,
                v.business_name as vendor_name,
                v.id as vendor_id,
                COALESCE(AVG(r.rating), 0) as average_rating,
                COUNT(DISTINCT r.id) as total_reviews
                FROM items i
                LEFT JOIN categories c ON i.category_id = c.id
                LEFT JOIN vendors v ON i.vendor_id = v.id
                LEFT JOIN reviews r ON i.id = r.item_id AND r.status = 'active'
                WHERE " . $whereConditions;
        
        $sql .= " GROUP BY i.id";
        
        // Sort order
        $sort = $filters['sort'] ?? 'newest';
        switch ($sort) {
            case 'oldest':
                $sql .= " ORDER BY i.created_at ASC";
                break;
            case 'price_low':
                $sql .= " ORDER BY i.price_per_day ASC";
                break;
            case 'price_high':
                $sql .= " ORDER BY i.price_per_day DESC";
                break;
            case 'popular':
                $sql .= " ORDER BY total_reviews DESC";
                break;
            case 'rating':
                $sql .= " ORDER BY average_rating DESC";
                break;
            case 'newest':
            default:
                $sql .= " ORDER BY i.created_at DESC";
                break;
        }
        
        // Pagination - ADD to existing params array
        $page = $filters['page'] ?? 1;
        $per_page = $filters['per_page'] ?? 12;
        $offset = ($page - 1) * $per_page;
        
        $sql .= " LIMIT ? OFFSET ?";
        
        // Create new array with pagination params
        $queryParams = array_merge($params, [$per_page, $offset]);
        
        $items = $this->db->fetchAll($sql, $queryParams);
        
        // Post-process items to add computed fields
        foreach ($items as &$item) {
            // Add images as JSON array
            $images = [];
            
            // Get additional images from item_images table
            $img_sql = "SELECT image_url FROM item_images WHERE item_id = ? ORDER BY display_order ASC";
            $additional_images = $this->db->fetchAll($img_sql, [$item['id']]);
            
            if (!empty($additional_images)) {
                foreach ($additional_images as $img) {
                    $images[] = $img['image_url'];
                }
            }
            
            // If no additional images, use main_image
            if (empty($images) && !empty($item['main_image'])) {
                $images[] = $item['main_image'];
            }
            
            // If still no images, use default
            if (empty($images)) {
                $images[] = 'assets/images/default-item.jpg';
            }
            
            $item['images'] = json_encode($images);
            
            // Add alias fields for frontend compatibility
            $item['daily_rate'] = $item['price_per_day'];
            $item['featured'] = $item['is_featured'] ?? 0;
            $item['availability_status'] = ($item['available_quantity'] > 0) ? 'available' : 'unavailable';
        }
        
        return [
            'items' => $items,
            'total' => $total
        ];
    }


    /**
     * Get similar items by category (different name to avoid conflict)
     */
    public function getRelatedByCategory($item_id, $category_id, $limit = 4) {
        $sql = "SELECT i.*, 
                c.name as category_name,
                v.business_name as vendor_name,
                COALESCE(AVG(r.rating), 0) as average_rating,
                COUNT(DISTINCT r.id) as total_reviews
                FROM items i
                LEFT JOIN categories c ON i.category_id = c.id
                LEFT JOIN vendors v ON i.vendor_id = v.id
                LEFT JOIN reviews r ON i.id = r.item_id AND r.status = 'approved'
                WHERE i.status = 'active' 
                AND i.approval_status = 'approved'
                AND i.category_id = ?
                AND i.id != ?
                GROUP BY i.id
                ORDER BY RAND()
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$category_id, $item_id, $limit]);
    }
    

    /**
     * Find item by ID with full details (for frontend display)
     * This is specifically for item-details.php page
     */
    public function findById($id) {
        $sql = "SELECT i.*, 
                c.name as category_name,
                c.slug as category_slug,
                v.business_name as vendor_name,
                v.id as vendor_id,
                v.city as vendor_city,
                v.state as vendor_state,
                v.phone as vendor_phone,
                v.logo as vendor_logo,
                COALESCE(AVG(r.rating), 0) as average_rating,
                COUNT(DISTINCT r.id) as total_reviews
                FROM items i
                LEFT JOIN categories c ON i.category_id = c.id
                LEFT JOIN vendors v ON i.vendor_id = v.id
                LEFT JOIN reviews r ON i.id = r.item_id AND r.status = 'active'
                WHERE i.id = ?
                GROUP BY i.id
                LIMIT 1";
        
        $item = $this->db->fetch($sql, [$id]);
        
        if ($item) {
            // Get additional images
            $images_sql = "SELECT image_url FROM item_images WHERE item_id = ? ORDER BY display_order ASC";
            $images = $this->db->fetchAll($images_sql, [$id]);
            
            // Create images array for JSON encoding
            $image_array = [];
            if (!empty($images)) {
                foreach ($images as $img) {
                    $image_array[] = $img['image_url'];
                }
            }
            // Add main image as first image if no additional images
            if (empty($image_array) && !empty($item['main_image'])) {
                $image_array[] = $item['main_image'];
            }
            
            $item['images'] = json_encode($image_array);
            
            // Map field names for frontend compatibility
            $item['daily_rate'] = $item['price_per_day'];
            $item['condition'] = $item['condition_status'];
            $item['location'] = $item['city'] . ', ' . $item['state'];
            $item['availability_status'] = ($item['status'] === 'approved' && $item['available_quantity'] > 0) ? 'available' : 'unavailable';
            $item['featured'] = 0; // Default, update if you have a featured field
        }
        
        return $item;
    }

        /**
     * Get recent items - ADD THIS METHOD ONLY
     */
    public function getRecent($limit = 10) {
        $sql = "SELECT i.*, 
                c.name as category_name, c.icon as category_icon,
                v.business_name as vendor_name,
                COALESCE(AVG(r.rating), 0) as average_rating,
                COUNT(DISTINCT r.id) as total_reviews
                FROM items i
                LEFT JOIN categories c ON i.category_id = c.id
                LEFT JOIN vendors v ON i.vendor_id = v.id
                LEFT JOIN reviews r ON i.id = r.item_id AND r.status = 'active'
                WHERE i.status = 'approved' 
                AND i.approval_status = 'approved'
                GROUP BY i.id
                ORDER BY i.created_at DESC
                LIMIT ?";
        
        $items = $this->db->fetchAll($sql, [$limit]);
        
        // Post-process to add images (same as your existing functions)
        foreach ($items as &$item) {
            $images = [];
            
            $img_sql = "SELECT image_url FROM item_images WHERE item_id = ? ORDER BY display_order ASC";
            $additional_images = $this->db->fetchAll($img_sql, [$item['id']]);
            
            if (!empty($additional_images)) {
                foreach ($additional_images as $img) {
                    $images[] = $img['image_url'];
                }
            }
            
            if (empty($images) && !empty($item['main_image'])) {
                $images[] = $item['main_image'];
            }
            
            if (empty($images)) {
                $images[] = 'assets/images/default-item.jpg';
            }
            
            $item['images'] = json_encode($images);
            $item['daily_rate'] = $item['price_per_day'];
            $item['featured'] = $item['is_featured'] ?? 0;
        }
        
        return $items;
    }


    public function count_state_total($state) {
        $sql = "SELECT COUNT(*) as total_items FROM items WHERE state = ? AND approval_status = 'approved'";
        $result = $this->db->fetch($sql, [$state]);
        return $result['total_items'] ?? 0;
    }


}