<?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 widget-review mappings.
defined('ABSPATH') || exit;

class DSProofwp_Widget_Review_Repository
{
    private wpdb $wpdb;
    private string $table;

    public function __construct(wpdb $wpdb)
    {
        $this->wpdb  = $wpdb;
        $this->table = $wpdb->prefix . 'dsproofwp_widget_reviews';
    }

    public function find_by_widget_ids(array $widget_ids): array
    {
        $widget_ids = array_values(array_filter(array_map('absint', $widget_ids)));

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

        $placeholders = implode(', ', array_fill(0, count($widget_ids), '%d'));
        $query        = $this->wpdb->prepare(
            "SELECT id, widget_id, review_id, sort_order, added_at
             FROM {$this->table}
             WHERE widget_id IN ({$placeholders})
             ORDER BY sort_order ASC, id ASC",
            $widget_ids
        );

        $rows    = $this->wpdb->get_results($query, ARRAY_A);
        $grouped = [];

        foreach ($rows as $row) {
            $widget_id = (int) $row['widget_id'];

            if (! isset($grouped[$widget_id])) {
                $grouped[$widget_id] = [];
            }

            $grouped[$widget_id][] = [
                'id'         => (int) $row['id'],
                'review_id'  => (int) $row['review_id'],
                'sort_order' => (int) $row['sort_order'],
                'added_at'   => (string) $row['added_at'],
            ];
        }

        return $grouped;
    }

    public function replace_for_widget(int $widget_id, array $review_ids): array
    {
        $review_ids = array_values(array_unique(array_filter(array_map('absint', $review_ids))));
        $current_assignments = $this->find_by_widget_ids([$widget_id]);
        $current_assignments = $current_assignments[$widget_id] ?? [];
        $current_review_ids  = array_map(
            static function (array $assignment): int {
                return (int) $assignment['review_id'];
            },
            $current_assignments
        );
        $current_positions = [];

        foreach ($current_assignments as $assignment) {
            $current_positions[(int) $assignment['review_id']] = (int) $assignment['sort_order'];
        }

        $reviews_to_remove = array_values(array_diff($current_review_ids, $review_ids));
        $reviews_to_add    = array_values(array_diff($review_ids, $current_review_ids));
        $sort_updates      = [];

        foreach ($review_ids as $index => $review_id) {
            if (isset($current_positions[$review_id]) && $current_positions[$review_id] !== $index) {
                $sort_updates[$review_id] = $index;
            }
        }

        if ($reviews_to_remove !== []) {
            $this->delete_reviews_for_widget($widget_id, $reviews_to_remove);
        }

        if ($sort_updates !== []) {
            $this->update_sort_orders($widget_id, $sort_updates);
        }

        if ($reviews_to_add !== []) {
            $insert_data = [];

            foreach ($review_ids as $index => $review_id) {
                if (! in_array($review_id, $reviews_to_add, true)) {
                    continue;
                }

                $insert_data[] = [
                    'widget_id'  => $widget_id,
                    'review_id'  => $review_id,
                    'sort_order' => $index,
                ];
            }

            $this->insert_reviews_for_widget($insert_data);
        }

        $grouped = $this->find_by_widget_ids([$widget_id]);

        return $grouped[$widget_id] ?? [];
    }

    public function delete_by_widget_id(int $widget_id): void
    {
        $this->wpdb->delete($this->table, ['widget_id' => $widget_id], ['%d']);
    }

    public function delete_by_review_id(int $review_id): void
    {
        $this->wpdb->delete($this->table, ['review_id' => $review_id], ['%d']);
    }

    private function delete_reviews_for_widget(int $widget_id, array $review_ids): void
    {
        $review_ids = array_values(array_filter(array_map('absint', $review_ids)));

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

        $placeholders = implode(', ', array_fill(0, count($review_ids), '%d'));
        $query        = $this->wpdb->prepare(
            "DELETE FROM {$this->table}
             WHERE widget_id = %d
             AND review_id IN ({$placeholders})",
            array_merge([$widget_id], $review_ids)
        );

        $this->wpdb->query($query);
    }

    /**
     * @param array<int, array{widget_id:int, review_id:int, sort_order:int}> $insert_data
     */
    private function insert_reviews_for_widget(array $insert_data): void
    {
        if ($insert_data === []) {
            return;
        }

        $placeholders = implode(', ', array_fill(0, count($insert_data), '(%d, %d, %d)'));
        $values       = [];

        foreach ($insert_data as $data) {
            $values[] = $data['widget_id'];
            $values[] = $data['review_id'];
            $values[] = $data['sort_order'];
        }

        $query = $this->wpdb->prepare(
            "INSERT INTO {$this->table} (widget_id, review_id, sort_order)
             VALUES {$placeholders}",
            $values
        );

        $this->wpdb->query($query);
    }

    /**
     * @param array<int, int> $sort_updates
     */
    private function update_sort_orders(int $widget_id, array $sort_updates): void
    {
        if ($sort_updates === []) {
            return;
        }

        $case_clauses  = [];
        $case_values   = [];
        $review_ids    = [];
        $in_values     = [];

        foreach ($sort_updates as $review_id => $sort_order) {
            $case_clauses[] = 'WHEN %d THEN %d';
            $case_values[]  = (int) $review_id;
            $case_values[]  = (int) $sort_order;
            $review_ids[]   = (int) $review_id;
            $in_values[]    = '%d';
        }

        $query = $this->wpdb->prepare(
            "UPDATE {$this->table}
             SET sort_order = CASE review_id " . implode(' ', $case_clauses) . " END
             WHERE widget_id = %d
             AND review_id IN (" . implode(', ', $in_values) . ')',
            array_merge($case_values, [$widget_id], $review_ids)
        );

        $this->wpdb->query($query);
    }
}
