<?php

class Review extends Model {
    protected string $table = 'reviews';

    /**
     * Get review by order item ID
     */
    public function getByOrderItemId(int $orderItemId): ?array {
        $sql = "SELECT r.*, GROUP_CONCAT(ri.image_path) as images_list
                FROM reviews r
                LEFT JOIN review_images ri ON r.id = ri.review_id
                WHERE r.order_item_id = :item_id
                GROUP BY r.id
                LIMIT 1";
        $stmt = $this->db->prepare($sql);
        $stmt->execute(['item_id' => $orderItemId]);
        $res = $stmt->fetch();
        if ($res && !empty($res['images_list'])) {
            $res['images'] = explode(',', $res['images_list']);
        } else if ($res) {
            $res['images'] = [];
        }
        return $res ?: null;
    }

    /**
     * Get approved public reviews for a product with author details & images
     */
    public function getApprovedProductReviews(int $productId): array {
        $sql = "SELECT r.*, u.full_name, u.avatar, GROUP_CONCAT(ri.image_path) as images_list
                FROM reviews r
                JOIN users u ON r.user_id = u.id
                LEFT JOIN review_images ri ON r.id = ri.review_id
                WHERE r.product_id = :pid AND r.status = 'approved'
                GROUP BY r.id
                ORDER BY r.created_at DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->execute(['pid' => $productId]);
        $rows = $stmt->fetchAll();
        foreach ($rows as &$row) {
            $row['images'] = !empty($row['images_list']) ? explode(',', $row['images_list']) : [];
        }
        return $rows;
    }

    /**
     * Get rating metrics (avg rating, total count, rating breakdown) for a product
     */
    public function getProductRatingMetrics(int $productId): array {
        $sql = "SELECT COUNT(*) as total_reviews, COALESCE(AVG(rating), 0) as avg_rating
                FROM reviews
                WHERE product_id = :pid AND status = 'approved'";
        $stmt = $this->db->prepare($sql);
        $stmt->execute(['pid' => $productId]);
        $summary = $stmt->fetch() ?: ['total_reviews' => 0, 'avg_rating' => 0];

        $breakdown = [5 => 0, 4 => 0, 3 => 0, 2 => 0, 1 => 0];
        $bSql = "SELECT rating, COUNT(*) as cnt 
                 FROM reviews 
                 WHERE product_id = :pid AND status = 'approved' 
                 GROUP BY rating";
        $bStmt = $this->db->prepare($bSql);
        $bStmt->execute(['pid' => $productId]);
        foreach ($bStmt->fetchAll() as $bRow) {
            $breakdown[(int)$bRow['rating']] = (int)$bRow['cnt'];
        }

        return [
            'total_reviews' => (int)$summary['total_reviews'],
            'avg_rating' => (float)$summary['avg_rating'],
            'breakdown' => $breakdown
        ];
    }

    /**
     * Add review images
     */
    public function addReviewImages(int $reviewId, array $imagePaths): void {
        $stmt = $this->db->prepare("INSERT INTO review_images (review_id, image_path) VALUES (:rid, :img)");
        foreach ($imagePaths as $path) {
            $stmt->execute(['rid' => $reviewId, 'img' => $path]);
        }
    }
}