<?php
/**
 * Review Model
 * Maps to `reviews` table
 * Handles review eligibility verification, data retrieval, rating calculation, and moderation
 */

require_once __DIR__ . '/../Core/Model.php';
require_once __DIR__ . '/ReviewImage.php';

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

    public function __construct() {
        parent::__construct();
        $this->reviewImageModel = new ReviewImage();
    }

    /**
     * Check if a user is eligible to review an order item
     * Rule: Order must belong to user, order_status = 'completed', and item not yet reviewed.
     */
    public function checkEligibility(int $userId, int $orderItemId): array {
        $sql = "SELECT oi.*, o.order_no, o.customer_id, o.store_id, o.order_status, o.created_at as order_date,
                       p.name as product_name, p.slug as product_slug, p.main_image as product_main_image,
                       s.store_name, s.store_slug
                FROM `order_items` oi
                JOIN `orders` o ON oi.order_id = o.id
                JOIN `products` p ON oi.product_id = p.id
                JOIN `stores` s ON o.store_id = s.id
                WHERE oi.id = :order_item_id AND o.deleted_at IS NULL
                LIMIT 1";

        $item = $this->fetchOne($sql, [':order_item_id' => $orderItemId]);
        if (!$item) {
            return [
                'eligible' => false,
                'message' => 'ไม่พบรายการสินค้าที่ระบุ',
                'item' => null
            ];
        }

        if ((int)$item['customer_id'] !== $userId) {
            return [
                'eligible' => false,
                'message' => 'คุณไม่มีสิทธิ์รีวิวรายการคำสั่งซื้อของผู้อื่น',
                'item' => null
            ];
        }

        if ($item['order_status'] !== 'completed') {
            return [
                'eligible' => false,
                'message' => 'สามารถรีวิวได้เฉพาะสินค้าในคำสั่งซื้อที่จัดส่งสำเร็จแล้วเท่านั้น',
                'item' => $item
            ];
        }

        // Check if already reviewed
        $existing = $this->fetchOne("SELECT * FROM `reviews` WHERE `order_item_id` = :order_item_id LIMIT 1", [
            ':order_item_id' => $orderItemId
        ]);

        if ($existing) {
            return [
                'eligible' => false,
                'already_reviewed' => true,
                'message' => 'คุณได้ทำการรีวิวสินค้านี้ไปแล้ว',
                'item' => $item,
                'existing_review' => $existing
            ];
        }

        return [
            'eligible' => true,
            'message' => 'สามารถรีวิวสินค้านี้ได้',
            'item' => $item
        ];
    }

    /**
     * Find review by ID with images and item info
     */
    public function findDetailById(int $id): ?array {
        $sql = "SELECT r.*,
                       u.first_name, u.last_name, u.username, u.profile_image as user_avatar,
                       p.name as product_name, p.slug as product_slug, p.main_image as product_main_image,
                       s.store_name, s.store_slug,
                       oi.variation_summary, oi.unit_price, oi.quantity,
                       o.order_no
                FROM `reviews` r
                JOIN `users` u ON r.user_id = u.id
                JOIN `products` p ON r.product_id = p.id
                JOIN `stores` s ON r.store_id = s.id
                JOIN `order_items` oi ON r.order_item_id = oi.id
                JOIN `orders` o ON r.order_id = o.id
                WHERE r.id = :id LIMIT 1";

        $review = $this->fetchOne($sql, [':id' => $id]);
        if (!$review) {
            return null;
        }

        $review['images'] = $this->reviewImageModel->getImagesByReview((int)$review['id']);
        return $review;
    }

    /**
     * Create review with transactional safety and recalculate ratings
     */
    public function createReview(array $data, array $imagePaths = []): int {
        $this->db->beginTransaction();
        try {
            $reviewId = $this->insert([
                'order_id'      => $data['order_id'],
                'order_item_id' => $data['order_item_id'],
                'product_id'    => $data['product_id'],
                'store_id'      => $data['store_id'],
                'user_id'       => $data['user_id'],
                'rating'        => $data['rating'],
                'review_text'   => $data['review_text'] ?? null,
                'status'        => 'active',
                'is_anonymous'  => !empty($data['is_anonymous']) ? 1 : 0,
                'created_at'    => date('Y-m-d H:i:s'),
                'updated_at'    => date('Y-m-d H:i:s')
            ]);

            foreach ($imagePaths as $idx => $path) {
                $this->reviewImageModel->addImage($reviewId, $path, $idx);
            }

            // Commit review creation
            $this->db->commit();

            // Recalculate rating caches
            $this->recalculateProductRating((int)$data['product_id']);
            $this->recalculateStoreRating((int)$data['store_id']);

            return $reviewId;
        } catch (Exception $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    /**
     * Update existing review
     */
    public function updateReview(int $reviewId, int $userId, int $rating, ?string $text, bool $isAnonymous, array $newImagePaths = [], array $deleteImageIds = []): bool {
        $review = $this->findById($reviewId);
        if (!$review || (int)$review['user_id'] !== $userId) {
            return false;
        }

        $this->db->beginTransaction();
        try {
            $this->update($reviewId, [
                'rating'       => $rating,
                'review_text'  => $text,
                'is_anonymous' => $isAnonymous ? 1 : 0,
                'updated_at'   => date('Y-m-d H:i:s')
            ]);

            // Delete requested images
            foreach ($deleteImageIds as $imgId) {
                $this->reviewImageModel->deleteImageById((int)$imgId, $reviewId);
            }

            // Add new images
            $existingImages = $this->reviewImageModel->getImagesByReview($reviewId);
            $startOrder = count($existingImages);
            foreach ($newImagePaths as $idx => $path) {
                $this->reviewImageModel->addImage($reviewId, $path, $startOrder + $idx);
            }

            $this->db->commit();

            // Recalculate ratings
            $this->recalculateProductRating((int)$review['product_id']);
            $this->recalculateStoreRating((int)$review['store_id']);

            return true;
        } catch (Exception $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    /**
     * Delete review (soft delete by marking status = 'removed' or hard delete)
     */
    public function deleteReview(int $reviewId, int $userId): bool {
        $review = $this->findById($reviewId);
        if (!$review || (int)$review['user_id'] !== $userId) {
            return false;
        }

        $this->update($reviewId, [
            'status'     => 'removed',
            'updated_at' => date('Y-m-d H:i:s')
        ]);

        $this->recalculateProductRating((int)$review['product_id']);
        $this->recalculateStoreRating((int)$review['store_id']);

        return true;
    }

    /**
     * Update review status (Moderation: active, hidden, removed)
     */
    public function updateReviewStatus(int $reviewId, string $status, ?string $adminNotes = null): bool {
        $review = $this->findById($reviewId);
        if (!$review) {
            return false;
        }

        $data = [
            'status'     => $status,
            'updated_at' => date('Y-m-d H:i:s')
        ];
        if ($adminNotes !== null) {
            $data['admin_notes'] = $adminNotes;
        }

        $this->update($reviewId, $data);

        // Recalculate rating because hidden/removed reviews don't count toward active rating
        $this->recalculateProductRating((int)$review['product_id']);
        $this->recalculateStoreRating((int)$review['store_id']);

        return true;
    }

    /**
     * Get active reviews for a product with pagination and star rating filter
     */
    public function getProductReviews(int $productId, int $limit = 10, int $offset = 0, ?int $rating = null): array {
        $where = "r.product_id = :product_id AND r.status = 'active'";
        $params = [':product_id' => $productId];

        if ($rating !== null && $rating >= 1 && $rating <= 5) {
            $where .= " AND r.rating = :rating";
            $params[':rating'] = $rating;
        }

        $sql = "SELECT r.*,
                       u.first_name, u.last_name, u.username, u.profile_image as user_avatar,
                       oi.variation_summary
                FROM `reviews` r
                JOIN `users` u ON r.user_id = u.id
                JOIN `order_items` oi ON r.order_item_id = oi.id
                WHERE {$where}
                ORDER BY r.created_at DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        $reviews = $stmt->fetchAll();

        // Attach images
        foreach ($reviews as &$rev) {
            $rev['images'] = $this->reviewImageModel->getImagesByReview((int)$rev['id']);
            $rev['display_name'] = $this->formatReviewerName($rev);
        }

        return $reviews;
    }

    public function countProductReviews(int $productId, ?int $rating = null): int {
        $where = "product_id = :product_id AND status = 'active'";
        $params = [':product_id' => $productId];

        if ($rating !== null && $rating >= 1 && $rating <= 5) {
            $where .= " AND rating = :rating";
            $params[':rating'] = $rating;
        }

        $sql = "SELECT COUNT(*) FROM `reviews` WHERE {$where}";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    /**
     * Get Product Rating Breakdown (Average, total count, count per star 1-5)
     */
    public function getProductRatingBreakdown(int $productId): array {
        $sql = "SELECT 
                  COUNT(*) as total_reviews,
                  COALESCE(AVG(rating), 0) as avg_rating,
                  SUM(CASE WHEN rating = 5 THEN 1 ELSE 0 END) as star_5,
                  SUM(CASE WHEN rating = 4 THEN 1 ELSE 0 END) as star_4,
                  SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) as star_3,
                  SUM(CASE WHEN rating = 2 THEN 1 ELSE 0 END) as star_2,
                  SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) as star_1,
                  SUM(CASE WHEN (SELECT COUNT(*) FROM `review_images` ri WHERE ri.review_id = r.id) > 0 THEN 1 ELSE 0 END) as with_images_count
                FROM `reviews` r
                WHERE r.product_id = :product_id AND r.status = 'active'";

        $res = $this->fetchOne($sql, [':product_id' => $productId]);
        if (!$res || (int)$res['total_reviews'] === 0) {
            return [
                'total_reviews' => 0,
                'avg_rating'    => 0.00,
                'star_5'        => 0,
                'star_4'        => 0,
                'star_3'        => 0,
                'star_2'        => 0,
                'star_1'        => 0,
                'star_percentages' => [5 => 0, 4 => 0, 3 => 0, 2 => 0, 1 => 0],
                'with_images_count' => 0
            ];
        }

        $total = (int)$res['total_reviews'];
        $percentages = [];
        for ($s = 1; $s <= 5; $s++) {
            $percentages[$s] = $total > 0 ? round(((int)$res['star_' . $s] / $total) * 100) : 0;
        }

        return [
            'total_reviews'     => $total,
            'avg_rating'        => round((float)$res['avg_rating'], 1),
            'star_5'            => (int)$res['star_5'],
            'star_4'            => (int)$res['star_4'],
            'star_3'            => (int)$res['star_3'],
            'star_2'            => (int)$res['star_2'],
            'star_1'            => (int)$res['star_1'],
            'star_percentages'  => $percentages,
            'with_images_count' => (int)$res['with_images_count']
        ];
    }

    /**
     * Get Store Reviews for Seller Center
     */
    public function getStoreReviews(int $storeId, array $filters = [], int $limit = 20, int $offset = 0): array {
        [$whereSql, $params] = $this->buildStoreConditions($storeId, $filters);

        $sql = "SELECT r.*,
                       u.first_name, u.last_name, u.username, u.profile_image as user_avatar,
                       p.name as product_name, p.slug as product_slug, p.main_image as product_main_image,
                       oi.variation_summary,
                       o.order_no
                FROM `reviews` r
                JOIN `users` u ON r.user_id = u.id
                JOIN `products` p ON r.product_id = p.id
                JOIN `order_items` oi ON r.order_item_id = oi.id
                JOIN `orders` o ON r.order_id = o.id
                WHERE {$whereSql}
                ORDER BY r.created_at DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        $reviews = $stmt->fetchAll();

        foreach ($reviews as &$rev) {
            $rev['images'] = $this->reviewImageModel->getImagesByReview((int)$rev['id']);
            $rev['display_name'] = $this->formatReviewerName($rev);
        }

        return $reviews;
    }

    public function countStoreReviews(int $storeId, array $filters = []): int {
        [$whereSql, $params] = $this->buildStoreConditions($storeId, $filters);
        $sql = "SELECT COUNT(*) FROM `reviews` r
                JOIN `products` p ON r.product_id = p.id
                JOIN `users` u ON r.user_id = u.id
                WHERE {$whereSql}";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    private function buildStoreConditions(int $storeId, array $filters): array {
        $conditions = ["r.store_id = :store_id", "r.status != 'removed'"];
        $params = [':store_id' => $storeId];

        if (!empty($filters['rating']) && (int)$filters['rating'] > 0) {
            $conditions[] = "r.rating = :rating";
            $params[':rating'] = (int)$filters['rating'];
        }

        if (!empty($filters['product_id'])) {
            $conditions[] = "r.product_id = :product_id";
            $params[':product_id'] = (int)$filters['product_id'];
        }

        if (!empty($filters['q'])) {
            $conditions[] = "(p.name LIKE :kw OR r.review_text LIKE :kw)";
            $params[':kw'] = '%' . trim($filters['q']) . '%';
        }

        return [implode(' AND ', $conditions), $params];
    }

    public function getStoreRatingSummary(int $storeId): array {
        $sql = "SELECT 
                  COUNT(*) as total_reviews,
                  COALESCE(AVG(rating), 0) as avg_rating,
                  SUM(CASE WHEN rating = 5 THEN 1 ELSE 0 END) as star_5,
                  SUM(CASE WHEN rating = 4 THEN 1 ELSE 0 END) as star_4,
                  SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) as star_3,
                  SUM(CASE WHEN rating = 2 THEN 1 ELSE 0 END) as star_2,
                  SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) as star_1
                FROM `reviews`
                WHERE store_id = :store_id AND status = 'active'";

        $res = $this->fetchOne($sql, [':store_id' => $storeId]);
        return [
            'total_reviews' => (int)($res['total_reviews'] ?? 0),
            'avg_rating'    => round((float)($res['avg_rating'] ?? 0), 1),
            'star_5'        => (int)($res['star_5'] ?? 0),
            'star_4'        => (int)($res['star_4'] ?? 0),
            'star_3'        => (int)($res['star_3'] ?? 0),
            'star_2'        => (int)($res['star_2'] ?? 0),
            'star_1'        => (int)($res['star_1'] ?? 0)
        ];
    }

    /**
     * Get Customer's Submitted Reviews
     */
    public function getUserReviews(int $userId): array {
        $sql = "SELECT r.*,
                       p.name as product_name, p.slug as product_slug, p.main_image as product_main_image,
                       s.store_name, s.store_slug,
                       oi.variation_summary,
                       o.order_no
                FROM `reviews` r
                JOIN `products` p ON r.product_id = p.id
                JOIN `stores` s ON r.store_id = s.id
                JOIN `order_items` oi ON r.order_item_id = oi.id
                JOIN `orders` o ON r.order_id = o.id
                WHERE r.user_id = :user_id AND r.status != 'removed'
                ORDER BY r.created_at DESC";

        $reviews = $this->fetchAll($sql, [':user_id' => $userId]);
        foreach ($reviews as &$rev) {
            $rev['images'] = $this->reviewImageModel->getImagesByReview((int)$rev['id']);
        }
        return $reviews;
    }

    /**
     * Get Customer's Completed Order Items Pending Review
     */
    public function getPendingReviewItemsForUser(int $userId): array {
        $sql = "SELECT oi.id as order_item_id, oi.product_id, oi.product_name, oi.variation_summary,
                       oi.product_image, oi.unit_price, oi.quantity,
                       o.id as order_id, o.order_no, o.created_at as order_date,
                       p.slug as product_slug,
                       s.store_name, s.store_slug
                FROM `order_items` oi
                JOIN `orders` o ON oi.order_id = o.id
                JOIN `products` p ON oi.product_id = p.id
                JOIN `stores` s ON o.store_id = s.id
                LEFT JOIN `reviews` r ON r.order_item_id = oi.id
                WHERE o.customer_id = :user_id 
                  AND o.order_status = 'completed' 
                  AND o.deleted_at IS NULL
                  AND r.id IS NULL
                ORDER BY o.created_at DESC";

        return $this->fetchAll($sql, [':user_id' => $userId]);
    }

    /**
     * Admin Review Listing with Moderation Filters
     */
    public function getAdminReviews(array $filters = [], int $limit = 20, int $offset = 0): array {
        [$whereSql, $params] = $this->buildAdminConditions($filters);

        $sql = "SELECT r.*,
                       u.first_name, u.last_name, u.username, u.email as user_email,
                       p.name as product_name, p.slug as product_slug, p.main_image as product_main_image,
                       s.store_name, s.store_slug,
                       o.order_no
                FROM `reviews` r
                JOIN `users` u ON r.user_id = u.id
                JOIN `products` p ON r.product_id = p.id
                JOIN `stores` s ON r.store_id = s.id
                JOIN `orders` o ON r.order_id = o.id
                WHERE {$whereSql}
                ORDER BY r.created_at DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        $reviews = $stmt->fetchAll();

        foreach ($reviews as &$rev) {
            $rev['images'] = $this->reviewImageModel->getImagesByReview((int)$rev['id']);
            $rev['display_name'] = $this->formatReviewerName($rev, false);
        }

        return $reviews;
    }

    public function countAdminReviews(array $filters = []): int {
        [$whereSql, $params] = $this->buildAdminConditions($filters);
        $sql = "SELECT COUNT(*) FROM `reviews` r
                JOIN `users` u ON r.user_id = u.id
                JOIN `products` p ON r.product_id = p.id
                JOIN `stores` s ON r.store_id = s.id
                WHERE {$whereSql}";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    public function getAdminReviewStats(): array {
        $sql = "SELECT
                  COUNT(*) as total_reviews,
                  SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_count,
                  SUM(CASE WHEN status = 'hidden' THEN 1 ELSE 0 END) as hidden_count,
                  SUM(CASE WHEN status = 'removed' THEN 1 ELSE 0 END) as removed_count,
                  COALESCE(AVG(CASE WHEN status = 'active' THEN rating ELSE NULL END), 0) as platform_avg_rating
                FROM `reviews`";
        $res = $this->fetchOne($sql);
        return [
            'total_reviews'       => (int)($res['total_reviews'] ?? 0),
            'active_count'        => (int)($res['active_count'] ?? 0),
            'hidden_count'        => (int)($res['hidden_count'] ?? 0),
            'removed_count'       => (int)($res['removed_count'] ?? 0),
            'platform_avg_rating' => round((float)($res['platform_avg_rating'] ?? 0), 2)
        ];
    }

    private function buildAdminConditions(array $filters): array {
        $conditions = ['1=1'];
        $params = [];

        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $conditions[] = 'r.status = :status';
            $params[':status'] = $filters['status'];
        }

        if (!empty($filters['rating']) && (int)$filters['rating'] > 0) {
            $conditions[] = 'r.rating = :rating';
            $params[':rating'] = (int)$filters['rating'];
        }

        if (!empty($filters['q'])) {
            $conditions[] = '(p.name LIKE :kw OR s.store_name LIKE :kw OR u.username LIKE :kw OR u.first_name LIKE :kw OR r.review_text LIKE :kw OR o.order_no LIKE :kw)';
            $params[':kw'] = '%' . trim($filters['q']) . '%';
        }

        if (!empty($filters['date_from'])) {
            $conditions[] = 'r.created_at >= :date_from';
            $params[':date_from'] = $filters['date_from'] . ' 00:00:00';
        }

        if (!empty($filters['date_to'])) {
            $conditions[] = 'r.created_at <= :date_to';
            $params[':date_to'] = $filters['date_to'] . ' 23:59:59';
        }

        return [implode(' AND ', $conditions), $params];
    }

    /**
     * Recalculate cached rating values in `products` table
     */
    public function recalculateProductRating(int $productId): array {
        $sql = "SELECT COUNT(*) as cnt, COALESCE(AVG(rating), 0) as avg_score
                FROM `reviews`
                WHERE product_id = :product_id AND status = 'active'";

        $stat = $this->fetchOne($sql, [':product_id' => $productId]);
        $cnt = (int)($stat['cnt'] ?? 0);
        $avg = round((float)($stat['avg_score'] ?? 0), 2);

        $updateSql = "UPDATE `products` SET `rating_avg` = :avg, `rating_count` = :cnt WHERE `id` = :id";
        $this->query($updateSql, [
            ':avg' => $avg,
            ':cnt' => $cnt,
            ':id'  => $productId
        ]);

        return ['rating_avg' => $avg, 'rating_count' => $cnt];
    }

    /**
     * Recalculate cached rating values in `stores` table
     */
    public function recalculateStoreRating(int $storeId): array {
        $sql = "SELECT COUNT(*) as cnt, COALESCE(AVG(rating), 0) as avg_score
                FROM `reviews`
                WHERE store_id = :store_id AND status = 'active'";

        $stat = $this->fetchOne($sql, [':store_id' => $storeId]);
        $cnt = (int)($stat['cnt'] ?? 0);
        $avg = round((float)($stat['avg_score'] ?? 0), 2);

        $updateSql = "UPDATE `stores` SET `rating_avg` = :avg, `rating_count` = :cnt WHERE `id` = :id";
        $this->query($updateSql, [
            ':avg' => $avg,
            ':cnt' => $cnt,
            ':id'  => $storeId
        ]);

        return ['rating_avg' => $avg, 'rating_count' => $cnt];
    }

    /**
     * Anonymize reviewer name for public privacy protection
     */
    public function formatReviewerName(array $review, bool $anonymize = true): string {
        $isAnon = !empty($review['is_anonymous']);
        $rawName = trim(($review['first_name'] ?? '') . ' ' . mb_substr($review['last_name'] ?? '', 0, 1) . '.');
        if (empty($rawName) || $rawName === '.') {
            $rawName = $review['username'] ?? 'User';
        }

        if ($anonymize && $isAnon) {
            $len = mb_strlen($rawName);
            if ($len <= 2) {
                return mb_substr($rawName, 0, 1) . '***';
            }
            return mb_substr($rawName, 0, 1) . str_repeat('*', min(4, $len - 2)) . mb_substr($rawName, -1);
        }

        return $rawName;
    }
}