<?php
declare(strict_types=1);
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,PluginCheck.Security.DirectDB.UnescapedDBParameter -- This repository uses prepared queries and trusted SQL identifiers for WordPress comment lookups.
defined('ABSPATH') || exit;

class DSProofwp_Review_Repository
{
    private const RATING_META_KEY = 'rating';
    private const VERIFIED_META_KEY = 'verified';

    private wpdb $wpdb;
    private string $comments_table;
    private string $review_fields_table;

    public function __construct(wpdb $wpdb)
    {
        $this->wpdb           = $wpdb;
        $this->comments_table = $wpdb->comments;
        $this->review_fields_table = $wpdb->prefix . 'dsproofwp_review_fields';
    }

    public function list(array $filters = []): array
    {
        $comments_alias = 'c';
        $review_fields_alias = 'rf';
        $where_clauses  = ["{$comments_alias}.comment_type = 'review'"];
        $params         = [];

        if (! empty($filters['include'])) {
            $include = array_values(array_filter(array_map('absint', (array) $filters['include'])));

            if ($include !== []) {
                $placeholders    = implode(', ', array_fill(0, count($include), '%d'));
                $where_clauses[] = "{$comments_alias}.comment_ID IN ({$placeholders})";
                $params          = array_merge($params, $include);
            }
        }

        if (! empty($filters['product_id'])) {
            $where_clauses[] = "{$comments_alias}.comment_post_ID = %d";
            $params[]        = absint($filters['product_id']);
        }

        if (isset($filters['is_visual']) && $filters['is_visual'] !== '' && $filters['is_visual'] !== null) {
            $where_clauses[] = "{$review_fields_alias}.is_visual = %d";
            $params[]        = (int) rest_sanitize_boolean($filters['is_visual']);
        }

        if (! empty($filters['source']) && $filters['source'] !== 'all') {
            $source = sanitize_text_field((string) $filters['source']);

            if ($source === 'customer') {
                $where_clauses[] = "({$review_fields_alias}.source = %s OR {$review_fields_alias}.source IS NULL OR {$review_fields_alias}.source = '')";
            } else {
                $where_clauses[] = "{$review_fields_alias}.source = %s";
            }

            $params[] = $source;
        }

        if (isset($filters['status']) && $filters['status'] !== '') {
            $where_clauses[] = "{$comments_alias}.comment_approved = %s";
            $params[]        = sanitize_text_field((string) $filters['status']);
        }

        if (! empty($filters['rating'])) {
            $where_clauses[] = "rating_meta.meta_value = %d";
            $params[]        = (int) $filters['rating'];
        }

        if (! empty($filters['min_rating'])) {
            $where_clauses[] = "CAST(rating_meta.meta_value AS UNSIGNED) >= %d";
            $params[]        = (int) $filters['min_rating'];
        }

        if (! empty($filters['search'])) {
            $search          = '%' . $this->wpdb->esc_like((string) $filters['search']) . '%';
            $where_clauses[] = "({$comments_alias}.comment_author LIKE %s OR {$comments_alias}.comment_content LIKE %s)";
            $params[]        = $search;
            $params[]        = $search;
        }

        $per_page = max(1, min(1000, isset($filters['per_page']) ? (int) $filters['per_page'] : 20));
        $page     = max(1, isset($filters['page']) ? (int) $filters['page'] : 1);
        $offset   = ($page - 1) * $per_page;
        $order_by = $this->resolve_order_by($filters['sort'] ?? null, $comments_alias);

        $where_sql = implode(' AND ', $where_clauses);

        // Separate count query instead of SQL_CALC_FOUND_ROWS
        $count_query = "SELECT COUNT(DISTINCT {$comments_alias}.comment_ID) 
            FROM {$this->comments_table} AS {$comments_alias}
            LEFT JOIN {$this->review_fields_table} AS {$review_fields_alias} ON {$review_fields_alias}.comment_id = {$comments_alias}.comment_ID
            LEFT JOIN {$this->wpdb->commentmeta} AS rating_meta ON rating_meta.comment_id = {$comments_alias}.comment_ID AND rating_meta.meta_key = '" . self::RATING_META_KEY . "'
            WHERE {$where_sql}";
        $total_count = (int) $this->wpdb->get_var($this->wpdb->prepare($count_query, $params));

        $query     = "SELECT {$comments_alias}.comment_ID, {$comments_alias}.comment_post_ID, {$comments_alias}.comment_author,
            {$comments_alias}.comment_author_email, {$comments_alias}.comment_content, {$comments_alias}.comment_date, {$comments_alias}.comment_approved,
            {$review_fields_alias}.source, {$review_fields_alias}.is_visual, rating_meta.meta_value AS rating, verified_meta.meta_value AS verified
        FROM {$this->comments_table} AS {$comments_alias}
        LEFT JOIN {$this->review_fields_table} AS {$review_fields_alias}
                ON {$review_fields_alias}.comment_id = {$comments_alias}.comment_ID
        LEFT JOIN {$this->wpdb->commentmeta} AS rating_meta
            ON rating_meta.comment_id = {$comments_alias}.comment_ID
            AND rating_meta.meta_key = '" . self::RATING_META_KEY . "'
        LEFT JOIN {$this->wpdb->commentmeta} AS verified_meta
            ON verified_meta.comment_id = {$comments_alias}.comment_ID
            AND verified_meta.meta_key = '" . self::VERIFIED_META_KEY . "'
        WHERE {$where_sql}
        ORDER BY {$order_by}
        LIMIT %d OFFSET %d";

        $params[] = $per_page;
        $params[] = $offset;

        $rows = $this->wpdb->get_results($this->wpdb->prepare($query, $params), ARRAY_A);

        return [
            'items'    => array_map([$this, 'map_row'], $rows),
            'total'    => $total_count,
            'page'     => $page,
            'per_page' => $per_page,
        ];
    }

    public function get_summary(int $product_id): array
    {
        $query = $this->wpdb->prepare(
            "SELECT rating_meta.meta_value AS rating, COUNT(*) AS count
             FROM {$this->comments_table} AS c
             INNER JOIN {$this->wpdb->commentmeta} AS rating_meta ON rating_meta.comment_id = c.comment_ID AND rating_meta.meta_key = %s
             WHERE c.comment_post_ID = %d AND c.comment_type = 'review' AND c.comment_approved = '1'
             GROUP BY rating_meta.meta_value",
            self::RATING_META_KEY,
            $product_id
        );

        $results = $this->wpdb->get_results($query, ARRAY_A);

        $star_counts = [
            1 => 0,
            2 => 0,
            3 => 0,
            4 => 0,
            5 => 0,
        ];

        $total_reviews = 0;
        $total_rating  = 0;

        foreach ($results as $row) {
            $rating = (int) $row['rating'];
            $count  = (int) $row['count'];

            if (isset($star_counts[$rating])) {
                $star_counts[$rating] = $count;
                $total_reviews += $count;
                $total_rating  += $rating * $count;
            }
        }

        return [
            'star_counts'    => $star_counts,
            'total_reviews'  => $total_reviews,
            'average_rating' => $total_reviews > 0 ? (float) ($total_rating / $total_reviews) : 0.0,
        ];
    }

    public function find(int $review_id): ?array
    {
        $comments_alias = 'c';
        $review_fields_alias = 'rf';


        $row = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT {$comments_alias}.comment_ID, {$comments_alias}.comment_post_ID, {$comments_alias}.comment_author, {$comments_alias}.comment_author_email,
                        {$comments_alias}.comment_content, {$comments_alias}.comment_date, {$comments_alias}.comment_approved, {$review_fields_alias}.source, {$review_fields_alias}.is_visual,
                        rating_meta.meta_value AS rating, verified_meta.meta_value AS verified
                 FROM {$this->comments_table} AS {$comments_alias}
                 LEFT JOIN {$this->review_fields_table} AS {$review_fields_alias}
                    ON {$review_fields_alias}.comment_id = {$comments_alias}.comment_ID
                 LEFT JOIN {$this->wpdb->commentmeta} AS rating_meta
                    ON rating_meta.comment_id = {$comments_alias}.comment_ID
                    AND rating_meta.meta_key = %s
                 LEFT JOIN {$this->wpdb->commentmeta} AS verified_meta
                    ON verified_meta.comment_id = {$comments_alias}.comment_ID
                    AND verified_meta.meta_key = %s
                 WHERE {$comments_alias}.comment_ID = %d AND {$comments_alias}.comment_type = 'review'
                 LIMIT 1",
                self::RATING_META_KEY,
                self::VERIFIED_META_KEY,
                $review_id
            ),
            ARRAY_A
        );

        return $row ? $this->map_row($row) : null;
    }

    public function create(array $data): ?array
    {
        $comment_data = [
            'comment_post_ID'      => absint($data['product_id'] ?? 0),
            'comment_author'       => sanitize_text_field((string) ($data['author_name'] ?? '')),
            'comment_author_email' => sanitize_email((string) ($data['author_email'] ?? '')),
            'comment_content'      => wp_kses_post((string) ($data['content'] ?? '')),
            'comment_approved'     => $this->sanitize_status($data['status'] ?? '1'),
            'comment_type'         => 'review',
            'user_id'              => isset($data['user_id']) ? absint($data['user_id']) : 0,
        ];

        if (! empty($data['date'])) {
            $date_gmt = $this->normalize_comment_date((string) $data['date']);

            if ($date_gmt !== null) {
                $comment_data['comment_date_gmt'] = $date_gmt;
                $comment_data['comment_date']     = get_date_from_gmt($date_gmt, 'Y-m-d H:i:s');
            }
        }

        $review_id = wp_insert_comment($comment_data);

        if (! $review_id) {
            return null;
        }

        $this->update_review_fields((int) $review_id, [
            'source'    => $data['source'] ?? '',
            'is_visual' => $data['is_visual'] ?? 0,
        ]);
        $this->update_rating_meta((int) $review_id, $data['rating'] ?? null);
        $this->update_verified_meta((int) $review_id, $data['verified'] ?? false);

        return $this->find((int) $review_id);
    }

    /**
     * Check if a user (by user ID or email) has already reviewed a given product.
     *
     * @param int $product_id
     * @param int $user_id
     * @param string $email
     * @return bool
     */
    public function has_user_reviewed(int $product_id, int $user_id = 0, string $email = ''): bool
    {
        if ($product_id <= 0) {
            return false;
        }

        $user_id = absint($user_id);
        $email   = sanitize_email(strtolower(trim($email)));

        if ($user_id <= 0 && empty($email)) {
            return false;
        }

        if ($user_id > 0 && ! empty($email)) {
            $query = $this->wpdb->prepare(
                "SELECT comment_ID 
                 FROM {$this->comments_table} 
                 WHERE comment_post_ID = %d 
                   AND comment_type = 'review' 
                   AND comment_approved != 'trash'
                   AND (user_id = %d OR (comment_author_email != '' AND LOWER(comment_author_email) = %s))
                 LIMIT 1",
                $product_id,
                $user_id,
                $email
            );
        } elseif ($user_id > 0) {
            $query = $this->wpdb->prepare(
                "SELECT comment_ID 
                 FROM {$this->comments_table} 
                 WHERE comment_post_ID = %d 
                   AND comment_type = 'review' 
                   AND comment_approved != 'trash'
                   AND user_id = %d
                 LIMIT 1",
                $product_id,
                $user_id
            );
        } else {
            $query = $this->wpdb->prepare(
                "SELECT comment_ID 
                 FROM {$this->comments_table} 
                 WHERE comment_post_ID = %d 
                   AND comment_type = 'review' 
                   AND comment_approved != 'trash'
                   AND comment_author_email != '' 
                   AND LOWER(comment_author_email) = %s
                 LIMIT 1",
                $product_id,
                $email
            );
        }

        return (bool) $this->wpdb->get_var($query);
    }

    public function update(int $review_id, array $data): ?array
    {
        $payload = ['comment_ID' => $review_id];

        if (array_key_exists('product_id', $data)) {
            $payload['comment_post_ID'] = absint($data['product_id']);
        }

        if (array_key_exists('author_name', $data)) {
            $payload['comment_author'] = sanitize_text_field((string) $data['author_name']);
        }

        if (array_key_exists('author_email', $data)) {
            $payload['comment_author_email'] = sanitize_email((string) $data['author_email']);
        }

        if (array_key_exists('content', $data)) {
            $payload['comment_content'] = wp_kses_post((string) $data['content']);
        }

        if (array_key_exists('status', $data)) {
            $payload['comment_approved'] = $this->sanitize_status($data['status']);
        }

        if (count($payload) > 1) {
            wp_update_comment($payload);
        }

        $custom_fields = [];

        if (array_key_exists('source', $data)) {
            $custom_fields['source'] = $data['source'];
        }

        if (array_key_exists('is_visual', $data)) {
            $custom_fields['is_visual'] = $data['is_visual'];
        }

        if ($custom_fields !== []) {
            $this->update_review_fields($review_id, $custom_fields);
        }

        if (array_key_exists('rating', $data)) {
            $this->update_rating_meta($review_id, $data['rating']);
        }

        if (array_key_exists('verified', $data)) {
            $this->update_verified_meta($review_id, $data['verified']);
        }

        return $this->find($review_id);
    }

    public function approve(int $review_id): bool
    {
        $review = $this->find($review_id);
        if (! $review) {
            return false;
        }

        $result = (bool) wp_set_comment_status($review_id, 'approve');

        if ($result && $review['product_id']) {
            $this->update_product_review_meta($review['product_id'], $review_id);
        }

        return $result;
    }

    public function delete(int $review_id): bool
    {
        $review = $this->find($review_id);
        $this->wpdb->delete($this->review_fields_table, ['comment_id' => $review_id], ['%d']);

        $result = (bool) wp_delete_comment($review_id, true);

        if ($result && $review && $review['product_id']) {
            $this->update_product_review_meta($review['product_id'], $review_id);
        }

        return $result;
    }

    private function update_product_review_meta(int $product_id, int $comment_id): void
    {
        if (function_exists('wc_update_product_review_count')) {
            wc_update_product_review_count($product_id);
        }

        if (function_exists('wc_delete_product_transients')) {
            wc_delete_product_transients($product_id);
        }

        if (class_exists('WC_Comments') && method_exists('WC_Comments', 'clear_transients')) {
            WC_Comments::clear_transients($comment_id);
        }
    }

    public function get_used_sources(): array
    {
        $sources = $this->wpdb->get_col(
            "SELECT DISTINCT source FROM {$this->review_fields_table} WHERE source IS NOT NULL AND source != ''"
        );

        $has_product_reviews = (bool) $this->wpdb->get_var(
            "SELECT 1 FROM {$this->comments_table} AS c
             LEFT JOIN {$this->review_fields_table} AS rf ON c.comment_ID = rf.comment_id
             WHERE c.comment_type = 'review' 
             AND (rf.source IS NULL OR rf.source = '' OR rf.source = 'product')
             LIMIT 1"
        );

        $results = array_filter($sources);
        if ($has_product_reviews) {
            $results[] = 'customer';
        }

        return array_values(array_unique($results));
    }

    public function get_cusrev_stats(): array
    {
        // Count comments that have any CusRev-specific metadata (ivole_*) but are NOT in dsproofwp_review_fields AT ALL
        $query = "SELECT COUNT(DISTINCT c.comment_ID) 
                  FROM {$this->comments_table} AS c
                  INNER JOIN {$this->wpdb->commentmeta} AS cm ON c.comment_ID = cm.comment_id 
                  LEFT JOIN {$this->review_fields_table} AS rf ON c.comment_ID = rf.comment_id
                  WHERE c.comment_type = 'review' 
                  AND rf.comment_id IS NULL
                  AND cm.meta_key LIKE 'ivole_%'";

        return [
            'count' => (int) $this->wpdb->get_var($query),
        ];
    }

    public function get_unmigrated_cusrev_ids(int $limit = 100): array
    {
        $query = $this->wpdb->prepare(
            "SELECT DISTINCT c.comment_ID 
             FROM {$this->comments_table} AS c
             INNER JOIN {$this->wpdb->commentmeta} AS cm ON c.comment_ID = cm.comment_id
             LEFT JOIN {$this->review_fields_table} AS rf ON c.comment_ID = rf.comment_id
             WHERE c.comment_type = 'review' 
             AND rf.comment_id IS NULL
             AND cm.meta_key LIKE 'ivole_%%'
             LIMIT %d",
            $limit
        );

        return array_map('intval', $this->wpdb->get_col($query));
    }

    public function get_reviewx_stats(): array
    {
        // Count comments that have ReviewX meta (rvx_* or reviewx_*) but are NOT in dsproofwp_review_fields AT ALL
        $query = "SELECT COUNT(DISTINCT c.comment_ID) 
                  FROM {$this->comments_table} AS c
                  INNER JOIN {$this->wpdb->commentmeta} AS cm ON c.comment_ID = cm.comment_id 
                  LEFT JOIN {$this->review_fields_table} AS rf ON c.comment_ID = rf.comment_id
                  WHERE c.comment_type = 'review' 
                  AND rf.comment_id IS NULL
                  AND (cm.meta_key LIKE 'rvx_%' OR cm.meta_key LIKE 'reviewx_%')";

        return [
            'count' => (int) $this->wpdb->get_var($query),
        ];
    }

    public function get_unmigrated_reviewx_ids(int $limit = 100): array
    {
        $query = $this->wpdb->prepare(
            "SELECT DISTINCT c.comment_ID 
             FROM {$this->comments_table} AS c
             INNER JOIN {$this->wpdb->commentmeta} AS cm ON c.comment_ID = cm.comment_id
             LEFT JOIN {$this->review_fields_table} AS rf ON c.comment_ID = rf.comment_id
             WHERE c.comment_type = 'review' 
             AND rf.comment_id IS NULL
             AND (cm.meta_key LIKE 'rvx_%%' OR cm.meta_key LIKE 'reviewx_%%')
             LIMIT %d",
            $limit
        );

        return array_map('intval', $this->wpdb->get_col($query));
    }

    public function register_as_proofwp_review(int $comment_id, string $source, bool $is_visual = false): void
    {
        $this->update_review_fields($comment_id, [
            'source'    => $source,
            'is_visual' => $is_visual,
        ]);
    }

    private function update_review_fields(int $review_id, array $fields): void
    {
        $data   = [];
        $format = [];

        if (array_key_exists('source', $fields)) {
            $data['source'] = sanitize_text_field((string) $fields['source']);
            $format[]       = '%s';
        }

        if (array_key_exists('is_visual', $fields)) {
            $data['is_visual'] = (int) rest_sanitize_boolean($fields['is_visual']);
            $format[]          = '%d';
        }

        if ($data === []) {
            return;
        }

        $data['comment_id'] = $review_id;
        $format[]           = '%d';

        $columns      = implode(', ', array_keys($data));
        $placeholders = implode(', ', $format);
        $updates      = implode(', ', array_map(
            static fn($col) => "{$col} = VALUES({$col})",
            array_keys($data)
        ));

        $this->wpdb->query(
            $this->wpdb->prepare(
                "INSERT INTO {$this->review_fields_table} ({$columns}) VALUES ({$placeholders})
                 ON DUPLICATE KEY UPDATE {$updates}",
                array_values($data)
            )
        );
    }

    private function sanitize_status($status): string
    {
        $status = (string) $status;

        if (in_array($status, ['0', '1', 'spam', 'trash', 'hold'], true)) {
            return $status;
        }

        return '1';
    }

    private function resolve_order_by($sort, string $comments_alias): string
    {
        $sort = sanitize_text_field((string) $sort);

        if ($sort === 'rating_desc') {
            return "CAST(rating_meta.meta_value AS UNSIGNED) DESC, {$comments_alias}.comment_date DESC";
        }

        if ($sort === 'rating_asc') {
            return "CAST(rating_meta.meta_value AS UNSIGNED) ASC, {$comments_alias}.comment_date DESC";
        }

        if ($sort === 'oldest') {
            return "{$comments_alias}.comment_date ASC";
        }

        if ($sort === 'newest') {
            return "{$comments_alias}.comment_date DESC";
        }

        return "{$comments_alias}.comment_date DESC";
    }

    private function update_rating_meta(int $review_id, $rating): void
    {
        $rating = $this->sanitize_rating($rating);

        if ($rating === null) {
            delete_comment_meta($review_id, self::RATING_META_KEY);

            return;
        }

        update_comment_meta($review_id, self::RATING_META_KEY, $rating);
    }

    private function sanitize_rating($rating): ?int
    {
        if ($rating === null || $rating === '') {
            return null;
        }

        $rating = (int) $rating;

        if ($rating < 1) {
            return 1;
        }

        if ($rating > 5) {
            return 5;
        }

        return $rating;
    }

    private function update_verified_meta(int $review_id, $verified): void
    {
        update_comment_meta($review_id, self::VERIFIED_META_KEY, (int) rest_sanitize_boolean($verified));
    }

    private function normalize_comment_date(string $date): ?string
    {
        $date = trim($date);

        if ($date === '') {
            return null;
        }

        $timestamp = strtotime($date);

        if ($timestamp === false) {
            return null;
        }

        return gmdate('Y-m-d H:i:s', $timestamp);
    }

    private function map_row(array $row): array
    {
        return [
            'id'           => (int) $row['comment_ID'],
            'product_id'   => (int) $row['comment_post_ID'],
            'author_name'  => (string) $row['comment_author'],
            'content'      => (string) $row['comment_content'],
            'date'         => (string) $row['comment_date'],
            'status'       => (string) $row['comment_approved'],
            'source'       => (string) ($row['source'] ?? ''),
            'rating'       => isset($row['rating']) && $row['rating'] !== null ? (int) $row['rating'] : null,
            'verified'     => ! empty($row['verified']),
            'is_visual'    => (bool) ($row['is_visual'] ?? false),
        ];
    }
}
