<?php
// SPDX-License-Identifier: GPL-3.0-or-later
declare(strict_types=1);

namespace Aiwend\Forms;

use Aiwend\Db\Connection;

/**
 * Stage E3 storage of visitor submissions (table form_submissions, one table per WordPress site through the table prefix, and every query
 * additionally filters on site_id). Every read is bounded (limit/offset or a capped count) and every write names its site. Visitor text lives
 * only in this table: it is never copied into the audit log, the usage ledger or error messages.
 */
final class SubmissionStore
{
    public const NONE = 'none';
    public const RUNNING = 'running';
    public const DONE = 'done';
    public const FAILED = 'failed';
    public const UNCERTAIN = 'uncertain';
    /** A claim older than this is shown as uncertain (the worker may have died after dispatching): longer than the 60 s provider timeout. */
    public const LEASE = 300;
    /** Identical submissions from the same address-independent content within this many seconds are one submission. */
    public const DEDUPE_WINDOW = 600;
    /** Stored, unexpired rows per site; beyond this new submissions are refused until some expire or are deleted. */
    public const SITE_CAP = 5000;
    public const MAX_PAGE = 50;

    private const LIST_COLUMNS = 'id, form_id, created_at, expires_at, read_at, topic, name, email, ack, analysis_state, analysis_at, category, message';

    public function __construct(private Connection $db, private string $secret)
    {
    }

    /** True when the site is already holding the maximum number of unexpired submissions. */
    public function full(int $site, int $now): bool
    {
        return $this->db->selectOne('SELECT id FROM {form_submissions} WHERE site_id = ? AND expires_at > ? ORDER BY expires_at LIMIT 1 OFFSET ' . (self::SITE_CAP - 1), [$site, $now]) !== null;
    }

    /**
     * @param array{name:string, email:string, topic:string, message:string, ack:bool} $v validated values
     * @return string 'stored' | 'duplicate'
     */
    public function add(int $site, int $formId, array $v, int $now, int $retentionDays): string
    {
        $v['email'] = mb_strtolower(trim($v['email'])); // stored and compared in lower case, whatever the caller passed
        $normalized = mb_strtolower((string) preg_replace('/\s+/u', ' ', $v['message']));
        $bucket = intdiv($now, self::DEDUPE_WINDOW);
        $hash = hash_hmac('sha256', $formId . "\0" . $v['email'] . "\0" . $normalized . "\0" . $bucket, $this->secret);
        $inserted = $this->db->insertIfAbsent('form_submissions', [
            'site_id' => $site, 'form_id' => $formId, 'created_at' => $now, 'expires_at' => $now + max(1, $retentionDays) * 86400,
            'topic' => $v['topic'], 'name' => $v['name'], 'email' => $v['email'], 'message' => $v['message'], 'ack' => $v['ack'] ? 1 : 0,
            'dedupe' => $hash, 'analysis_state' => self::NONE,
        ], ['site_id', 'form_id', 'dedupe']);
        return $inserted ? 'stored' : 'duplicate';
    }

    /** @return array<string, mixed>|null one unexpired submission of this site */
    public function find(int $site, int $id, int $now): ?array
    {
        return $this->db->selectOne('SELECT * FROM {form_submissions} WHERE site_id = ? AND id = ? AND expires_at > ?', [$site, $id, $now]);
    }

    /** @return list<array<string, mixed>> newest first; $formId 0 = every form of the site */
    public function recent(int $site, int $formId, int $now, int $limit = 20, int $offset = 0): array
    {
        $limit = max(1, min(self::MAX_PAGE, $limit));
        $offset = max(0, min(100000, $offset));
        $where = 'site_id = ? AND expires_at > ?' . ($formId > 0 ? ' AND form_id = ?' : '');
        $args = $formId > 0 ? [$site, $now, $formId] : [$site, $now];
        return $this->db->select('SELECT ' . self::LIST_COLUMNS . ' FROM {form_submissions} WHERE ' . $where . ' ORDER BY created_at DESC, id DESC LIMIT ' . $limit . ' OFFSET ' . $offset, $args);
    }

    /** Unread submissions per form, counted up to $cap rows in total (so the query cost is bounded). @return array<int, int> form_id => count */
    public function unreadByForm(int $site, int $now, int $cap = 500): array
    {
        $out = [];
        foreach ($this->db->select('SELECT form_id FROM {form_submissions} WHERE site_id = ? AND read_at IS NULL AND expires_at > ? LIMIT ' . max(1, min(1000, $cap)), [$site, $now]) as $r) {
            $out[(int) $r['form_id']] = ($out[(int) $r['form_id']] ?? 0) + 1;
        }
        return $out;
    }

    /** Number of unread, unexpired submissions, counted up to $cap (the caller shows "99+"). */
    public function unread(int $site, int $now, int $cap = 100): int
    {
        return count($this->db->select('SELECT id FROM {form_submissions} WHERE site_id = ? AND read_at IS NULL AND expires_at > ? LIMIT ' . max(1, min(1000, $cap)), [$site, $now]));
    }

    public function markRead(int $site, int $id, int $now): void
    {
        $this->db->execute('UPDATE {form_submissions} SET read_at = ? WHERE site_id = ? AND id = ? AND read_at IS NULL', [$now, $site, $id]);
    }

    public function delete(int $site, int $id): bool
    {
        return $this->db->execute('DELETE FROM {form_submissions} WHERE site_id = ? AND id = ?', [$site, $id]) === 1;
    }

    // ------------------------------------------------------------------ manual analysis

    /** The state to show: a claim older than LEASE is "uncertain" (the earlier attempt may have been dispatched and billed). */
    public static function effectiveState(array $row, int $now): string
    {
        $state = (string) ($row['analysis_state'] ?? self::NONE);
        if ($state === self::RUNNING && (int) ($row['analysis_at'] ?? 0) < $now - self::LEASE) {
            return self::UNCERTAIN;
        }
        return $state;
    }

    /**
     * Atomically claims the row for one analysis. Succeeds from "none" or "failed" (nothing was dispatched, or the dispatch is known to have
     * failed with no charge); from "done" or "uncertain" only when $again is true (an explicit repeat the administrator confirmed); never
     * while another analysis holds a fresh claim. Returns false when the row is gone, expired, already claimed or not claimable.
     */
    public function claim(int $site, int $id, string $requestId, int $now, bool $again): bool
    {
        $states = $again ? "('none','failed','done','uncertain')" : "('none','failed')";
        $n = $this->db->execute(
            "UPDATE {form_submissions} SET analysis_state = 'running', analysis_request = ?, analysis_at = ? WHERE site_id = ? AND id = ? AND expires_at > ? AND (analysis_state IN $states OR (analysis_state = 'running' AND analysis_at < ?" . ($again ? '' : ' AND 1 = 0') . '))',
            [$requestId, $now, $site, $id, $now, $now - self::LEASE]
        );
        return $n === 1;
    }

    /** Stores the result only while this request still owns the row (an erased or re-claimed row is left alone). */
    public function finish(int $site, int $id, string $requestId, string $summary, string $category, string $reply, string $model, int $now): bool
    {
        return $this->db->execute(
            "UPDATE {form_submissions} SET analysis_state = 'done', analysis_at = ?, summary = ?, category = ?, reply = ?, analysis_model = ? WHERE site_id = ? AND id = ? AND analysis_request = ? AND analysis_state = 'running'",
            [$now, $summary, $category, $reply, $model, $site, $id, $requestId]
        ) === 1;
    }

    /** @param string $state self::FAILED (nothing billable happened) or self::UNCERTAIN */
    public function fail(int $site, int $id, string $requestId, string $state, int $now): void
    {
        $state = $state === self::UNCERTAIN ? self::UNCERTAIN : self::FAILED;
        $this->db->execute("UPDATE {form_submissions} SET analysis_state = ?, analysis_at = ? WHERE site_id = ? AND id = ? AND analysis_request = ? AND analysis_state = 'running'", [$state, $now, $site, $id, $requestId]);
    }

    // ------------------------------------------------------------------ retention and privacy

    /** Deletes up to $limit expired rows, oldest expiry first (an unexpired row can never hide an expired one). Returns the number removed. */
    public function expire(int $site, int $now, int $limit = 200): int
    {
        $ids = array_map(static fn(array $r): int => (int) $r['id'], $this->db->select('SELECT id FROM {form_submissions} WHERE site_id = ? AND expires_at <= ? ORDER BY expires_at, id LIMIT ' . max(1, min(1000, $limit)), [$site, $now]));
        if ($ids === []) {
            return 0;
        }
        return $this->db->execute('DELETE FROM {form_submissions} WHERE site_id = ? AND expires_at <= ? AND id IN (' . implode(',', $ids) . ')', [$site, $now]);
    }

    /** Shortens (never lengthens) the expiry of stored rows to created_at + $days. Returns the number of rows changed. */
    public function shorten(int $site, int $days): int
    {
        $span = max(1, $days) * 86400;
        return $this->db->execute('UPDATE {form_submissions} SET expires_at = created_at + ? WHERE site_id = ? AND expires_at > created_at + ?', [$span, $site, $span]);
    }

    /** @return list<array<string, mixed>> rows for a verified email address on this site (privacy export), $limit per page */
    public function byEmail(int $site, string $email, int $offset, int $limit = 50): array
    {
        return $this->db->select('SELECT * FROM {form_submissions} WHERE site_id = ? AND email = ? ORDER BY id LIMIT ' . max(1, min(200, $limit)) . ' OFFSET ' . max(0, $offset), [$site, mb_strtolower(trim($email))]);
    }

    /** Deletes up to $limit rows for a verified email address on this site, AI summary and draft included. Returns the number removed. */
    public function eraseEmail(int $site, string $email, int $limit = 200): int
    {
        $ids = array_map(static fn(array $r): int => (int) $r['id'], $this->db->select('SELECT id FROM {form_submissions} WHERE site_id = ? AND email = ? ORDER BY id LIMIT ' . max(1, min(1000, $limit)), [$site, mb_strtolower(trim($email))]));
        if ($ids === []) {
            return 0;
        }
        return $this->db->execute('DELETE FROM {form_submissions} WHERE site_id = ? AND id IN (' . implode(',', $ids) . ')', [$site]);
    }

    public function countForEmail(int $site, string $email): int
    {
        return count($this->db->select('SELECT id FROM {form_submissions} WHERE site_id = ? AND email = ? LIMIT 1000', [$site, mb_strtolower(trim($email))]));
    }
}