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

class DSProofwp_Widget_Repository
{
    private const SUPPORTED_WIDGET_TYPES = ['visual', 'testimonial'];

    private wpdb $wpdb;
    private string $table;

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

    public function list(array $filters = []): array
    {
        $where_clauses = ['1=1', 'w.widget_type IN (%s, %s)'];
        $params        = self::SUPPORTED_WIDGET_TYPES;

        if (! empty($filters['status'])) {
            $where_clauses[] = 'w.status = %s';
            $params[]        = sanitize_text_field((string) $filters['status']);
        }

        if (! empty($filters['search'])) {
            $search = trim(sanitize_text_field((string) $filters['search']));

            if ($search !== '') {
                $search_like     = '%' . $this->wpdb->esc_like($search) . '%';
                $search_clauses  = ['w.title LIKE %s', 'w.widget_type LIKE %s'];
                $search_params   = [$search_like, $search_like];

                if (is_numeric($search)) {
                    $search_clauses[] = 'w.id = %d';
                    $search_params[]  = (int) $search;
                }

                $where_clauses[] = '(' . implode(' OR ', $search_clauses) . ')';
                $params          = array_merge($params, $search_params);
            }
        }

        $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;

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

        // Separate count query instead of SQL_CALC_FOUND_ROWS
        $count_query = "SELECT COUNT(DISTINCT w.id) FROM {$this->table} AS w WHERE {$where_sql}";
        $total_count = (int) $this->wpdb->get_var($this->wpdb->prepare($count_query, $params));

        $query = "SELECT w.id, w.title, w.widget_type, w.settings, w.review_selection, w.status, w.created_at, w.updated_at
            FROM {$this->table} AS w
            WHERE {$where_sql}
            GROUP BY w.id
            ORDER BY w.id DESC
            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 find(int $widget_id): ?array
    {
        $row = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT id, title, widget_type, settings, review_selection, status, created_at, updated_at
                 FROM {$this->table}
                 WHERE id = %d
                   AND widget_type IN (%s, %s)
                 LIMIT 1",
                $widget_id,
                ...self::SUPPORTED_WIDGET_TYPES
            ),
            ARRAY_A
        );
        return $row ? $this->map_row($row) : null;
    }

    public function create(array $data): ?array
    {
        $inserted = $this->wpdb->insert(
            $this->table,
            [
                'title'            => sanitize_text_field((string) ($data['title'] ?? '')),
                'widget_type'      => sanitize_key((string) ($data['widget_type'] ?? '')),
                'settings'         => $this->encode_settings($data['settings'] ?? []),
                'review_selection' => sanitize_text_field((string) ($data['review_selection'] ?? 'custom')),
                'status'           => sanitize_text_field((string) ($data['status'] ?? 'active')),
            ],
            ['%s', '%s', '%s', '%s', '%s']
        );

        if (! $inserted) {
            return null;
        }

        return $this->find((int) $this->wpdb->insert_id);
    }

    public function get_next_untitled_title(): string
    {
        $rows = $this->wpdb->get_col(
            $this->wpdb->prepare(
                "SELECT title
                 FROM {$this->table}
                 WHERE LOWER(title) = %s
                    OR LOWER(title) REGEXP %s",
                'untitled',
                '^untitled [0-9]+$'
            )
        );

        if (! is_array($rows) || $rows === []) {
            return 'untitled';
        }

        $highest_suffix = 0;

        foreach ($rows as $title) {
            $normalized_title = strtolower(trim((string) $title));

            if ($normalized_title === 'untitled') {
                $highest_suffix = max($highest_suffix, 1);
                continue;
            }

            if (preg_match('/^untitled (\d+)$/', $normalized_title, $matches) === 1) {
                $highest_suffix = max($highest_suffix, (int) $matches[1]);
            }
        }

        if ($highest_suffix <= 0) {
            return 'untitled';
        }

        return $highest_suffix === 1
            ? 'untitled 2'
            : 'untitled ' . ($highest_suffix + 1);
    }

    public function update(int $widget_id, array $data): ?array
    {
        $update = [];
        $format = [];

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

        if (array_key_exists('widget_type', $data)) {
            $update['widget_type'] = sanitize_key((string) $data['widget_type']);
            $format[]              = '%s';
        }

        if (array_key_exists('settings', $data)) {
            $update['settings'] = $this->encode_settings($data['settings']);
            $format[]           = '%s';
        }

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

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

        if ($update !== []) {
            $this->wpdb->update(
                $this->table,
                $update,
                ['id' => $widget_id],
                $format,
                ['%d']
            );
        }

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

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

    private function encode_settings($settings): ?string
    {
        if ($settings === null) {
            return null;
        }

        if (is_string($settings)) {
            return $settings;
        }

        return wp_json_encode($settings);
    }

    private function map_row(array $row): array
    {
        $settings = json_decode((string) $row['settings'], true);

        return [
            'id'               => (int) $row['id'],
            'title'            => (string) $row['title'],
            'widget_type'      => (string) $row['widget_type'],
            'settings'         => is_array($settings) ? $settings : $row['settings'],
            'review_selection' => (string) ($row['review_selection'] ?? 'custom'),
            'status'           => (string) $row['status'],
            'created_at'       => (string) $row['created_at'],
            'updated_at'       => (string) $row['updated_at'],
        ];
    }
}
