<?php

namespace Formatrica\Submission;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

use DateTimeImmutable;
use DateTimeZone;
use wpdb;

final class Submission_Repository
{
    private string $table;
    private wpdb $wpdb;

    public function __construct(?wpdb $db = null)
    {
        global $wpdb;

        $this->wpdb = $db ?? $wpdb;
        $this->table = $this->wpdb->prefix . 'formatrica_submissions';
    }

    /**
     * Persist a submission.
     *
     * @param array<string, mixed> $payload
     */
    public function create(int $form_id, string $uuid, array $payload, array $meta = []): int
    {
        $data = [
            'form_id' => $form_id,
            'submission_uuid' => $uuid,
            'data' => wp_json_encode($payload, JSON_UNESCAPED_UNICODE),
            'status' => $meta['status'] ?? 'new',
            'ip_address' => $meta['ip_address'] ?? null,
            'user_agent' => $meta['user_agent'] ?? null,
        ];

        $this->wpdb->insert(
            $this->table,
            $data,
            [
                '%d',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
            ]
        );

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

    /**
     * Count submissions in the specified window for throttling.
     */
    public function count_recent(int $form_id, string $ip_address, int $seconds): int
    {
        $now = new DateTimeImmutable('now', new DateTimeZone('UTC'));
        $threshold = $now->modify(sprintf('-%d seconds', $seconds))->format('Y-m-d H:i:s');

        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i identifier placeholder is valid since WP 6.2
        return (int) $this->wpdb->get_var(
            $this->wpdb->prepare(
                'SELECT COUNT(*) FROM %i WHERE form_id = %d AND ip_address = %s AND created_at >= %s',
                $this->table,
                $form_id,
                $ip_address,
                $threshold
            )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
    }

    public function count_for_form(int $form_id): int
    {
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i identifier placeholder is valid since WP 6.2
        return (int) $this->wpdb->get_var(
            $this->wpdb->prepare(
                'SELECT COUNT(*) FROM %i WHERE form_id = %d',
                $this->table,
                $form_id
            )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function all_for_form(int $form_id): array
    {
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i identifier placeholder is valid since WP 6.2
        $rows = $this->wpdb->get_results(
            $this->wpdb->prepare(
                'SELECT id, submission_uuid, data, status, ip_address, user_agent, created_at FROM %i WHERE form_id = %d ORDER BY created_at DESC',
                $this->table,
                $form_id
            ),
            ARRAY_A
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared

        if (empty($rows)) {
            return [];
        }

        return array_map(static function (array $row): array {
            $payload = json_decode($row['data'] ?? '', true);

            return [
                'id' => (int) $row['id'],
                'uuid' => $row['submission_uuid'],
                'status' => $row['status'],
                'ip_address' => $row['ip_address'],
                'user_agent' => $row['user_agent'],
                'created_at' => $row['created_at'],
                'data' => is_array($payload) ? $payload : [],
            ];
        }, $rows);
    }

    /**
     * Paginated, filterable admin listing (Free submissions surface). Joins the
     * form title so the list can show + filter by form.
     *
     * @param array{form_id?:int,search?:string,status?:string,orderby?:string,order?:string,per_page?:int,paged?:int} $args
     * @return array{rows: list<array<string,mixed>>, total: int}
     */
    public function paginate(array $args): array
    {
        $form_id = (int) ($args['form_id'] ?? 0);
        $search = trim((string) ($args['search'] ?? ''));
        $status = (string) ($args['status'] ?? '');
        $per_page = max(1, min(200, (int) ($args['per_page'] ?? 20)));
        $paged = max(1, (int) ($args['paged'] ?? 1));

        $orderby_map = [
            'id' => 's.id',
            'uuid' => 's.submission_uuid',
            'form' => 'f.title',
            'status' => 's.status',
            'created_at' => 's.created_at',
        ];
        $orderby = $orderby_map[$args['orderby'] ?? ''] ?? 's.created_at';
        $order = strtoupper((string) ($args['order'] ?? 'DESC')) === 'ASC' ? 'ASC' : 'DESC';

        $forms_table = $this->wpdb->prefix . 'formatrica_forms';
        $where = ['1=1'];
        $params = [];

        if ($form_id > 0) {
            $where[] = 's.form_id = %d';
            $params[] = $form_id;
        }
        if ($status !== '' && in_array($status, ['new', 'read'], true)) {
            $where[] = 's.status = %s';
            $params[] = $status;
        }
        if ($search !== '') {
            $like = '%' . $this->wpdb->esc_like($search) . '%';
            $where[] = '(s.submission_uuid LIKE %s OR s.data LIKE %s)';
            $params[] = $like;
            $params[] = $like;
        }

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

        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table names + validated ORDER BY tokens; value placeholders live inside the interpolated WHERE
        $total = (int) $this->wpdb->get_var(
            $params
                ? $this->wpdb->prepare("SELECT COUNT(*) FROM {$this->table} s LEFT JOIN {$forms_table} f ON f.id = s.form_id WHERE {$where_sql}", ...$params)
                : "SELECT COUNT(*) FROM {$this->table} s LEFT JOIN {$forms_table} f ON f.id = s.form_id WHERE {$where_sql}"
        );

        $offset = ($paged - 1) * $per_page;
        $list_sql = "SELECT s.id, s.form_id, s.submission_uuid, s.status, s.created_at, f.title AS form_title"
            . " FROM {$this->table} s LEFT JOIN {$forms_table} f ON f.id = s.form_id"
            . " WHERE {$where_sql} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
        $rows = $this->wpdb->get_results(
            $this->wpdb->prepare($list_sql, ...array_merge($params, [$per_page, $offset])),
            ARRAY_A
        );
        // phpcs:enable

        return [
            'rows' => array_map(static fn (array $r) => [
                'id' => (int) $r['id'],
                'form_id' => (int) $r['form_id'],
                'uuid' => (string) $r['submission_uuid'],
                'status' => (string) $r['status'],
                'created_at' => (string) $r['created_at'],
                'form_title' => (string) ($r['form_title'] ?? ''),
            ], (array) $rows),
            'total' => $total,
        ];
    }

    /**
     * Full detail for one submission (admin preview / export): decoded data +
     * the stored form's field definitions so values can be labelled.
     *
     * @return array<string, mixed>|null
     */
    public function get_detail(int $id): ?array
    {
        $forms_table = $this->wpdb->prefix . 'formatrica_forms';
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- placeholders are interpolated pre-prepare; table names from $wpdb->prefix
        $row = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT s.*, f.title AS form_title, f.fields AS form_fields FROM {$this->table} s LEFT JOIN {$forms_table} f ON f.id = s.form_id WHERE s.id = %d",
                $id
            ),
            ARRAY_A
        );
        // phpcs:enable

        if (!$row) {
            return null;
        }

        $data = json_decode((string) ($row['data'] ?? ''), true);
        $fields = json_decode((string) ($row['form_fields'] ?? ''), true);

        return [
            'id' => (int) $row['id'],
            'form_id' => (int) $row['form_id'],
            'uuid' => (string) $row['submission_uuid'],
            'status' => (string) $row['status'],
            'ip_address' => (string) ($row['ip_address'] ?? ''),
            'user_agent' => (string) ($row['user_agent'] ?? ''),
            'created_at' => (string) $row['created_at'],
            'data' => is_array($data) ? $data : [],
            'form_title' => (string) ($row['form_title'] ?? ''),
            'form_fields' => is_array($fields) ? $fields : [],
        ];
    }

    /**
     * Mark a submission read (idempotent; used by the Pro preview open).
     */
    public function mark_read(int $id): void
    {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $this->wpdb->update($this->table, ['status' => 'read'], ['id' => $id, 'status' => 'new'], ['%s'], ['%d', '%s']);
    }

    /**
     * Set the read/new status on a set of submissions. Returns the rows changed.
     *
     * @param list<int> $ids
     */
    public function mark_status(array $ids, string $status): int
    {
        $status = $status === 'read' ? 'read' : 'new';
        $ids = array_values(array_filter(array_map('intval', $ids), static fn ($i) => $i > 0));
        if ($ids === []) {
            return 0;
        }
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- placeholders are interpolated pre-prepare; table names from $wpdb->prefix
        $this->wpdb->query(
            $this->wpdb->prepare("UPDATE {$this->table} SET status = %s WHERE id IN ({$placeholders})", $status, ...$ids)
        );
        // phpcs:enable
        return (int) $this->wpdb->rows_affected;
    }

    /**
     * Delete a set of submissions by id. Returns the number deleted.
     *
     * @param list<int> $ids
     */
    public function delete_by_ids(array $ids): int
    {
        $ids = array_values(array_filter(array_map('intval', $ids), static fn ($i) => $i > 0));
        if ($ids === []) {
            return 0;
        }
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- placeholders are interpolated pre-prepare; table names from $wpdb->prefix
        $this->wpdb->query(
            $this->wpdb->prepare("DELETE FROM {$this->table} WHERE id IN ({$placeholders})", ...$ids)
        );
        // phpcs:enable
        return (int) $this->wpdb->rows_affected;
    }

    /**
     * Forms that have at least one submission (for the list filter dropdown).
     *
     * @return list<array{id:int,title:string}>
     */
    public function forms_with_submissions(): array
    {
        $forms_table = $this->wpdb->prefix . 'formatrica_forms';
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- placeholders are interpolated pre-prepare; table names from $wpdb->prefix
        $rows = $this->wpdb->get_results(
            "SELECT DISTINCT f.id, f.title FROM {$this->table} s INNER JOIN {$forms_table} f ON f.id = s.form_id ORDER BY f.title ASC",
            ARRAY_A
        );
        // phpcs:enable
        return array_map(static fn (array $r) => ['id' => (int) $r['id'], 'title' => (string) $r['title']], (array) $rows);
    }

    public function delete_by_form_id(int $form_id): int
    {
        $result = $this->wpdb->delete(
            $this->table,
            ['form_id' => $form_id],
            ['%d']
        );

        return $result !== false ? (int) $result : 0;
    }

    /**
     * Delete up to $limit submissions for a form older than $cutoff.
     * Batched (LIMIT) so a huge backlog is retired in bounded chunks by the
     * retention sweep rather than one unbounded DELETE.
     *
     * @return int Number of rows deleted this call.
     */
    public function delete_older_than(int $form_id, string $cutoff, int $limit): int
    {
        if ($form_id <= 0 || $limit <= 0) {
            return 0;
        }

        $orders = $this->wpdb->prefix . 'formatrica_payment_orders';

        // Never retire a submission whose payment is still in flight (P9): the
        // webhook needs the row to flip. Orders expire in 30 min, so this only
        // ever protects genuinely-pending rows.
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table names from $wpdb->prefix; values are placeholders
        $result = $this->wpdb->query(
            $this->wpdb->prepare(
                "DELETE FROM {$this->table} WHERE form_id = %d AND created_at < %s"
                    . " AND submission_uuid NOT IN (SELECT submission_uuid FROM {$orders} WHERE status IN ('pending', 'processing_payment') AND submission_uuid <> '')"
                    . ' LIMIT %d',
                $form_id,
                $cutoff,
                $limit
            )
        );
        // phpcs:enable

        return is_numeric($result) ? (int) $result : 0;
    }

    /**
     * Orphan sweep: delete up to $limit submissions whose parent form no longer
     * exists (hard-deleted) and are older than $cutoff (a grace window). Prevents
     * deleted-form PII from lingering indefinitely.
     *
     * @return int Number of rows deleted this call.
     */
    public function delete_orphans_older_than(string $cutoff, int $limit): int
    {
        if ($limit <= 0) {
            return 0;
        }

        $forms_table = $this->wpdb->prefix . 'formatrica_forms';
        $orders      = $this->wpdb->prefix . 'formatrica_payment_orders';

        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $result = $this->wpdb->query(
            $this->wpdb->prepare(
                "DELETE FROM {$this->table} WHERE created_at < %s AND form_id NOT IN (SELECT id FROM {$forms_table})"
                    . " AND submission_uuid NOT IN (SELECT submission_uuid FROM {$orders} WHERE status IN ('pending', 'processing_payment') AND submission_uuid <> '')"
                    . ' LIMIT %d',
                $cutoff,
                $limit
            )
        );
        // phpcs:enable

        return is_numeric($result) ? (int) $result : 0;
    }
}
