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

class Review {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Create review
     */
    public function create($data) {
        $sql = "INSERT INTO reviews (item_id, order_id, customer_id, vendor_id, rating, review, status, created_at) 
                VALUES (?, ?, ?, ?, ?, ?, 'active', NOW())";
        
        $result = $this->db->execute($sql, [
            $data['item_id'],
            $data['order_id'],
            $data['customer_id'],
            $data['vendor_id'],
            $data['rating'],
            $data['review'] ?? null
        ]);
        
        if ($result) {
            // Update item rating
            $item = new Item();
            $item->updateRating($data['item_id']);
        }
        
        return $result ? $this->db->lastInsertId() : false;
    }
    
    /**
     * Find review by ID
     */
    public function find($id) {
        $sql = "SELECT r.*, 
                i.title as item_title, i.main_image as item_image,
                CONCAT(c.first_name, ' ', c.last_name) as customer_name, c.profile_picture,
                v.business_name as vendor_name,
                o.order_number
                FROM reviews r
                JOIN items i ON r.item_id = i.id
                JOIN customers c ON r.customer_id = c.id
                JOIN vendors v ON r.vendor_id = v.id
                JOIN orders o ON r.order_id = o.id
                WHERE r.id = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$id]);
    }
    
    /**
     * Find review by order ID
     */
    public function findByOrderId($order_id) {
        $sql = "SELECT r.*, 
                i.title as item_title,
                CONCAT(c.first_name, ' ', c.last_name) as customer_name
                FROM reviews r
                JOIN items i ON r.item_id = i.id
                JOIN customers c ON r.customer_id = c.id
                WHERE r.order_id = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$order_id]);
    }
    
    /**
     * Get reviews by item ID
     */
    public function getByItemId($item_id, $limit = 10, $offset = 0) {
        $sql = "SELECT r.*, 
                CONCAT(c.first_name, ' ', c.last_name) as customer_name, 
                c.profile_picture,
                o.order_number
                FROM reviews r
                JOIN customers c ON r.customer_id = c.id
                JOIN orders o ON r.order_id = o.id
                WHERE r.item_id = ? AND r.status = 'active'
                ORDER BY r.created_at DESC
                LIMIT ? OFFSET ?";
        
        return $this->db->fetchAll($sql, [$item_id, $limit, $offset]);
    }
    
    /**
     * Count reviews for item
     */
    public function countByItemId($item_id) {
        $sql = "SELECT COUNT(*) as count FROM reviews WHERE item_id = ? AND status = 'active'";
        $result = $this->db->fetch($sql, [$item_id]);
        return $result['count'] ?? 0;
    }
    
    /**
     * Get reviews by vendor ID
     */
    public function getByVendorId($vendor_id, $limit = 10, $offset = 0) {
        $sql = "SELECT r.*, 
                i.title as item_title, i.main_image as item_image,
                CONCAT(c.first_name, ' ', c.last_name) as customer_name,
                o.order_number
                FROM reviews r
                JOIN items i ON r.item_id = i.id
                JOIN customers c ON r.customer_id = c.id
                JOIN orders o ON r.order_id = o.id
                WHERE r.vendor_id = ? AND r.status = 'active'
                ORDER BY r.created_at DESC
                LIMIT ? OFFSET ?";
        
        return $this->db->fetchAll($sql, [$vendor_id, $limit, $offset]);
    }
    
    /**
     * Get reviews by customer ID
     */
    public function getByCustomerId($customer_id, $limit = 10, $offset = 0) {
        $sql = "SELECT r.*, 
                i.title as item_title, i.main_image as item_image,
                v.business_name as vendor_name,
                o.order_number
                FROM reviews r
                JOIN items i ON r.item_id = i.id
                JOIN vendors v ON r.vendor_id = v.id
                JOIN orders o ON r.order_id = o.id
                WHERE r.customer_id = ?
                ORDER BY r.created_at DESC
                LIMIT ? OFFSET ?";
        
        return $this->db->fetchAll($sql, [$customer_id, $limit, $offset]);
    }
    
    /**
     * Get all reviews with pagination
     */
    public function getAll($limit = 20, $offset = 0, $filters = []) {
        $sql = "SELECT r.*, 
                i.title as item_title,
                CONCAT(c.first_name, ' ', c.last_name) as customer_name,
                v.business_name as vendor_name,
                o.order_number
                FROM reviews r
                JOIN items i ON r.item_id = i.id
                JOIN customers c ON r.customer_id = c.id
                JOIN vendors v ON r.vendor_id = v.id
                JOIN orders o ON r.order_id = o.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['item_id'])) {
            $sql .= " AND r.item_id = ?";
            $params[] = $filters['item_id'];
        }
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND r.vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        if (!empty($filters['customer_id'])) {
            $sql .= " AND r.customer_id = ?";
            $params[] = $filters['customer_id'];
        }
        
        if (!empty($filters['status'])) {
            $sql .= " AND r.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['rating'])) {
            $sql .= " AND r.rating = ?";
            $params[] = $filters['rating'];
        }
        
        $sql .= " ORDER BY r.created_at DESC LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Count reviews
     */
    public function count($filters = []) {
        $sql = "SELECT COUNT(*) as count FROM reviews r WHERE 1=1";
        $params = [];
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND r.vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        if (!empty($filters['customer_id'])) {
            $sql .= " AND r.customer_id = ?";
            $params[] = $filters['customer_id'];
        }
        
        if (!empty($filters['status'])) {
            $sql .= " AND r.status = ?";
            $params[] = $filters['status'];
        }
        
        $result = $this->db->fetch($sql, $params);
        return $result['count'] ?? 0;
    }
    
    /**
     * Update review
     */
    public function update($id, $data) {
        $fields = [];
        $params = [];
        
        foreach ($data as $key => $value) {
            $fields[] = "{$key} = ?";
            $params[] = $value;
        }
        
        $params[] = $id;
        
        $sql = "UPDATE reviews SET " . implode(', ', $fields) . ", updated_at = NOW() WHERE id = ?";
        
        $result = $this->db->execute($sql, $params);
        
        if ($result) {
            // Get review to update item rating
            $review = $this->find($id);
            if ($review) {
                $item = new Item();
                $item->updateRating($review['item_id']);
            }
        }
        
        return $result;
    }
    
    /**
     * Add vendor reply
     */
    public function addVendorReply($id, $reply) {
        $sql = "UPDATE reviews SET 
                vendor_reply = ?, 
                vendor_replied_at = NOW(),
                updated_at = NOW() 
                WHERE id = ?";
        
        return $this->db->execute($sql, [$reply, $id]);
    }
    
    /**
     * Update status
     */
    public function updateStatus($id, $status) {
        $sql = "UPDATE reviews SET status = ?, updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, [$status, $id]);
    }
    
    /**
     * Delete review
     */
    public function delete($id) {
        $review = $this->find($id);
        $sql = "DELETE FROM reviews WHERE id = ?";
        $result = $this->db->execute($sql, [$id]);
        
        if ($result && $review) {
            // Update item rating
            $item = new Item();
            $item->updateRating($review['item_id']);
        }
        
        return $result;
    }
    
    /**
     * Check if customer can review
     */
    public function canReview($customer_id, $order_id) {
        // Check if order is completed
        $order_sql = "SELECT status FROM orders WHERE id = ? AND customer_id = ?";
        $order = $this->db->fetch($order_sql, [$order_id, $customer_id]);
        
        if (!$order || $order['status'] !== 'completed') {
            return false;
        }
        
        // Check if already reviewed
        $review_sql = "SELECT id FROM reviews WHERE order_id = ? AND customer_id = ?";
        $existing = $this->db->fetch($review_sql, [$order_id, $customer_id]);
        
        return $existing === null;
    }
    
    /**
     * Get item rating breakdown
     */
    public function getRatingBreakdown($item_id) {
        $sql = "SELECT 
                rating,
                COUNT(*) as count
                FROM reviews
                WHERE item_id = ? AND status = 'active'
                GROUP BY rating
                ORDER BY rating DESC";
        
        return $this->db->fetchAll($sql, [$item_id]);
    }
    
    /**
     * Get recent reviews
     */
    public function getRecent($limit = 10) {
        $sql = "SELECT r.*, 
                i.title as item_title, i.main_image as item_image,
                CONCAT(c.first_name, ' ', c.last_name) as customer_name,
                v.business_name as vendor_name
                FROM reviews r
                JOIN items i ON r.item_id = i.id
                JOIN customers c ON r.customer_id = c.id
                JOIN vendors v ON r.vendor_id = v.id
                WHERE r.status = 'active'
                ORDER BY r.created_at DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Get review statistics
     */
    public function getStats($filters = []) {
        $sql = "SELECT 
                COUNT(*) as total_reviews,
                AVG(rating) as average_rating,
                SUM(CASE WHEN rating = 5 THEN 1 ELSE 0 END) as five_star,
                SUM(CASE WHEN rating = 4 THEN 1 ELSE 0 END) as four_star,
                SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) as three_star,
                SUM(CASE WHEN rating = 2 THEN 1 ELSE 0 END) as two_star,
                SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) as one_star,
                SUM(CASE WHEN vendor_reply IS NOT NULL THEN 1 ELSE 0 END) as replied_count,
                SUM(CASE WHEN DATE(created_at) >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) as new_this_week
                FROM reviews
                WHERE status = 'active'";
        
        $params = [];
        
        if (!empty($filters['item_id'])) {
            $sql .= " AND item_id = ?";
            $params[] = $filters['item_id'];
        }
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        return $this->db->fetch($sql, $params);
    }

    /**
     * Get customer reviews (alias for getByCustomerId)
     */
    public function getCustomerReviews($customer_id, $limit = 10, $offset = 0) {
        return $this->getByCustomerId($customer_id, $limit, $offset);
    }

    /**
     * Get customer review statistics
     */
    public function getCustomerStats($customer_id) {
        $sql = "SELECT 
                COUNT(*) as total_reviews,
                AVG(rating) as average_rating,
                SUM(CASE WHEN rating = 5 THEN 1 ELSE 0 END) as five_star,
                SUM(CASE WHEN rating = 4 THEN 1 ELSE 0 END) as four_star,
                SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) as three_star,
                SUM(CASE WHEN rating = 2 THEN 1 ELSE 0 END) as two_star,
                SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) as one_star,
                SUM(CASE WHEN vendor_reply IS NOT NULL THEN 1 ELSE 0 END) as replied_count
                FROM reviews
                WHERE customer_id = ?";
        
        return $this->db->fetch($sql, [$customer_id]);
    }

    /**
     * Get unreviewed orders for a customer
     */
    public function getUnreviewedOrders($customer_id, $limit = 10) {
        $sql = "SELECT o.*, 
                i.title as item_title, 
                i.main_image as item_image,
                v.business_name as vendor_name
                FROM orders o
                JOIN items i ON o.item_id = i.id
                JOIN vendors v ON o.vendor_id = v.id
                LEFT JOIN reviews r ON o.id = r.order_id
                WHERE o.customer_id = ? 
                AND o.status = 'completed'
                AND r.id IS NULL
                ORDER BY o.completed_at DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$customer_id, $limit]);
    }


    /**
     * Get reviews for a specific item
     */
    public function getItemReviews($item_id, $limit = null) {
        $sql = "SELECT r.*, 
                CONCAT(c.first_name, ' ', c.last_name) as customer_name
                FROM reviews r
                JOIN customers c ON r.customer_id = c.id
                WHERE r.item_id = ? 
                AND r.status = 'approved'
                ORDER BY r.created_at DESC";
        
        if ($limit !== null) {
            $sql .= " LIMIT ?";
            return $this->db->fetchAll($sql, [$item_id, $limit]);
        }
        
        return $this->db->fetchAll($sql, [$item_id]);
    }

    /**
     * Get review statistics for an item
     */
    public function getItemReviewStats($item_id) {
        $sql = "SELECT 
                COUNT(*) as total_reviews,
                COALESCE(AVG(rating), 0) as average_rating,
                SUM(CASE WHEN rating = 5 THEN 1 ELSE 0 END) as five_star,
                SUM(CASE WHEN rating = 4 THEN 1 ELSE 0 END) as four_star,
                SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) as three_star,
                SUM(CASE WHEN rating = 2 THEN 1 ELSE 0 END) as two_star,
                SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) as one_star
                FROM reviews 
                WHERE item_id = ? 
                AND status = 'approved'";
        
        return $this->db->fetch($sql, [$item_id]);
    }
    

}