<?php

namespace Aumframe\Forms;

/**
 * The enquiry inbox.
 *
 * A submission is stored first; the email is only a notification.
 *
 * A form that only sends email loses orders: PHP mail on shared hosting is regularly marked as spam or rejected
 * outright, and the client never learns which enquiries they missed — they simply feel that "nobody has been
 * asking lately". The mail may fail; the data must not be lost.
 *
 * @package Aumframe
 */

defined('ABSPATH') || exit;

class Inbox
{
    const DB_VERSION = 1;

    public static function table(): string
    {
        global $wpdb;
        return $wpdb->prefix . 'aumframe_form_entries';
    }

    public static function maybe_install(): void
    {
        if ((int) get_option('aumframe_form_db_version') === self::DB_VERSION) {
            return;
        }
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $table   = self::table();
        $collate = $wpdb->get_charset_collate();

        dbDelta("CREATE TABLE {$table} (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            form_id     BIGINT UNSIGNED NOT NULL,
            data        LONGTEXT        NULL,
            source_id   BIGINT UNSIGNED NOT NULL DEFAULT 0,
            source_url  VARCHAR(255)    NOT NULL DEFAULT '',
            ip          VARCHAR(45)     NOT NULL DEFAULT '',
            user_agent  VARCHAR(255)    NOT NULL DEFAULT '',
            status      VARCHAR(20)     NOT NULL DEFAULT 'new',
            created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY form_created (form_id, created_at),
            KEY status (status)
        ) {$collate};");

        update_option('aumframe_form_db_version', self::DB_VERSION);
    }

    public static function insert(int $form_id, array $data, array $ctx): int
    {
        global $wpdb;
        self::maybe_install();

        $wpdb->insert(self::table(), [
            'form_id'    => $form_id,
            'data'       => wp_json_encode($data, JSON_UNESCAPED_UNICODE),
            'source_id'  => (int) ($ctx['source_id'] ?? 0),
            'source_url' => substr((string) ($ctx['source_url'] ?? ''), 0, 255),
            'ip'         => substr((string) ($ctx['ip'] ?? ''), 0, 45),
            'user_agent' => substr((string) ($ctx['user_agent'] ?? ''), 0, 255),
        ]);
        return (int) $wpdb->insert_id;
    }

    /**
     * How many times one IP has submitted in a short window.
     * The honeypot and the time trap stop unsophisticated bots; rate limiting stops repeat submissions and
     * small-scale flooding.
 */
    public static function recent_count(string $ip, int $seconds = 600): int
    {
        global $wpdb;
        if ($ip === '') {
            return 0;
        }
        $table = self::table();
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE ip = %s AND created_at > DATE_SUB(NOW(), INTERVAL %d SECOND)",
            $ip,
            $seconds
        ));
    }

    public static function query(int $form_id = 0, int $limit = 50, int $offset = 0): array
    {
        global $wpdb;
        self::maybe_install();
        $table = self::table();
        $sql   = $form_id
            ? $wpdb->prepare("SELECT * FROM {$table} WHERE form_id = %d ORDER BY created_at DESC LIMIT %d OFFSET %d", $form_id, $limit, $offset)
            : $wpdb->prepare("SELECT * FROM {$table} ORDER BY created_at DESC LIMIT %d OFFSET %d", $limit, $offset);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Both branches of the ternary assigning $sql are return values of $wpdb->prepare(); the sniff does not follow a ternary assignment. If either branch is ever changed to concatenation, this annotation becomes false.
        return $wpdb->get_results($sql, ARRAY_A) ?: [];
    }

    public static function count(int $form_id = 0): int
    {
        global $wpdb;
        self::maybe_install();
        $table = self::table();
        return (int) ($form_id
            ? $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$table} WHERE form_id = %d", $form_id))
            : $wpdb->get_var("SELECT COUNT(*) FROM {$table}"));
    }
}