<?php

declare(strict_types=1);

namespace Numinix\SeekmodoWordPress\Events;

use Numinix\SeekmodoWordPress\Gateway\Client;

/**
 * Local events queue, drained on the EventsFlushCron tick.
 *
 * Why a custom table and not `wp_options` / `wp_postmeta`?
 *
 *   - High write throughput: a busy storefront produces dozens of
 *     click + impression events per second. Custom table = one indexed
 *     write per event vs. the LIKE-prone wp_options autoload bloat.
 *
 *   - Easy to truncate. Operator can `TRUNCATE wp_seekmodo_events`
 *     during incident response without disturbing any other plugin's
 *     state.
 *
 *   - Bounded retention. Rows older than 7 days are pruned on each
 *     flush -- if the gateway has been unreachable for that long, the
 *     events are stale and not worth resending.
 *
 * Schema (`{$wpdb->prefix}seekmodo_events`):
 *
 *   id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
     *   type        VARCHAR(32)  NOT NULL    -- 'click' | 'impression' | 'search' | 'tombstone' | 'add_to_cart' | 'purchase'
 *   object_id   BIGINT       NOT NULL    -- post / product / row id
 *   keyword     VARCHAR(255) NOT NULL    -- search keyword for click/impression
 *   position    SMALLINT     NOT NULL    -- 1-based; 0 for non-positional
 *   surface     VARCHAR(32)  NOT NULL    -- 'serp' | 'typeahead' | 'related' | 'block'
 *   bot_verdict VARCHAR(8)              -- 'human' | 'bot' | NULL
 *   payload     LONGTEXT                 -- JSON envelope (extra fields)
 *   created_at  INT UNSIGNED NOT NULL    -- unix ts
 *
 * Indexes:
 *   idx_created (created_at)
 *   idx_type_created (type, created_at)
 */
final class EventsQueue
{
    public const TABLE = 'seekmodo_events';
    public const SCHEMA_VERSION = 1;
    private const RETENTION_DAYS = 7;
    // Keep batches small: gateway last-touch attribution on conversion
    // rows made 200-event flushes time out (250ms client) and re-send
    // forever (POCO Marine Jul 2026 ATC storm).
    private const FLUSH_BATCH = 25;
    private const FLUSH_BATCH_CONVERSION = 10;

    public static function register(): void {}

    public static function table(): string
    {
        global $wpdb;
        if (!isset($wpdb) || !is_object($wpdb)) {
            return self::TABLE;
        }
        $prefix = property_exists($wpdb, 'prefix') ? (string) $wpdb->prefix : 'wp_';

        return $prefix . self::TABLE;
    }

    public static function installTable(): void
    {
        global $wpdb;
        if (!isset($wpdb) || !is_object($wpdb)) {
            return;
        }
        if (!function_exists('dbDelta')) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }
        if (!function_exists('dbDelta')) {
            return;
        }

        $table   = self::table();
        $charset = method_exists($wpdb, 'get_charset_collate') ? $wpdb->get_charset_collate() : '';
        $sql     = "CREATE TABLE {$table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            type VARCHAR(32) NOT NULL,
            object_id BIGINT NOT NULL DEFAULT 0,
            keyword VARCHAR(255) NOT NULL DEFAULT '',
            position SMALLINT NOT NULL DEFAULT 0,
            surface VARCHAR(32) NOT NULL DEFAULT 'serp',
            bot_verdict VARCHAR(8) DEFAULT NULL,
            payload LONGTEXT NULL,
            created_at INT UNSIGNED NOT NULL,
            PRIMARY KEY  (id),
            KEY idx_created (created_at),
            KEY idx_type_created (type, created_at)
        ) {$charset};";

        dbDelta($sql);
    }

    /**
     * @param array<string,mixed> $row
     */
    public static function push(array $row): void
    {
        global $wpdb;
        if (!isset($wpdb) || !is_object($wpdb) || !method_exists($wpdb, 'insert')) {
            return;
        }
        $now = isset($row['occurred_at']) ? (int) $row['occurred_at'] : time();
        $payload_extras = $row;
        foreach (['type', 'object_id', 'keyword', 'position', 'surface', 'bot_verdict', 'occurred_at'] as $k) {
            unset($payload_extras[$k]);
        }
        $payload_json = !empty($payload_extras) ? wp_json_encode($payload_extras) : null;

        $wpdb->insert(self::table(), [
            'type'        => isset($row['type']) ? substr((string) $row['type'], 0, 32) : 'click',
            'object_id'   => isset($row['object_id']) ? (int) $row['object_id'] : 0,
            'keyword'     => isset($row['keyword']) ? substr((string) $row['keyword'], 0, 255) : '',
            'position'    => isset($row['position']) ? (int) $row['position'] : 0,
            'surface'     => isset($row['surface']) ? substr((string) $row['surface'], 0, 32) : 'serp',
            'bot_verdict' => isset($row['bot_verdict']) ? substr((string) $row['bot_verdict'], 0, 8) : null,
            'payload'     => $payload_json,
            'created_at'  => $now,
        ]);
    }

    /**
     * Drain a batch from the queue, forward to the gateway, delete the
     * forwarded rows on success.
     */
    public static function flush(): int
    {
        global $wpdb;
        if (!isset($wpdb) || !is_object($wpdb)) {
            return 0;
        }
        $table = self::table();

        // Prune ancient rows opportunistically.
        $cutoff = time() - self::RETENTION_DAYS * 86400;
        if (method_exists($wpdb, 'query')) {
            $wpdb->query($wpdb->prepare("DELETE FROM {$table} WHERE created_at < %d", $cutoff)); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        }

        // Prefer clicks (LTR labels) over conversion spam. Conversion
        // rows are flushed in a smaller batch because each one triggers
        // a gateway-side attribution lookup.
        $rows = [];
        if (method_exists($wpdb, 'get_results')) {
            // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name from self::table()
            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM {$table} WHERE type = %s ORDER BY id ASC LIMIT %d",
                    'click',
                    (int) self::FLUSH_BATCH
                ),
                ARRAY_A
            );
            if (!is_array($rows) || $rows === []) {
                $rows = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT * FROM {$table} ORDER BY id ASC LIMIT %d",
                        (int) self::FLUSH_BATCH_CONVERSION
                    ),
                    ARRAY_A
                );
            }
            // phpcs:enable
        }
        if (!is_array($rows) || empty($rows)) {
            return 0;
        }

        $events = [];
        $ids    = [];
        foreach ($rows as $r) {
            $ids[]   = (int) $r['id'];
            $extras  = !empty($r['payload']) ? (json_decode((string) $r['payload'], true) ?: []) : [];
            $events[] = array_merge([
                'event_type'       => (string) $r['type'],
                'keyword'          => (string) $r['keyword'],
                'product_id'       => (int) $r['object_id'],
                'object_id'        => (int) $r['object_id'],
                'object_type'      => (string) ($extras['object_type'] ?? 'post'),
                'position'         => (int) $r['position'],
                'surface'          => (string) $r['surface'],
                'bot_verdict'      => $r['bot_verdict'] === null ? null : (string) $r['bot_verdict'],
                'occurred_at_unix' => (int) $r['created_at'],
            ], is_array($extras) ? $extras : []);
        }

        $sent = Client::sendEvents($events);
        if ($sent !== true) {
            // Gateway unreachable; keep rows for next tick.
            return 0;
        }

        // Delete the forwarded rows.
        if (!empty($ids) && method_exists($wpdb, 'query')) {
            $ids_csv = implode(',', array_map('intval', $ids));
            $wpdb->query("DELETE FROM {$table} WHERE id IN ({$ids_csv})"); // phpcs:ignore WordPress.DB.PreparedSQL
        }

        return count($events);
    }

    /**
     * @return array<int,array<string,mixed>>
     */
    public static function drain(int $batch_size = 200): array
    {
        // Legacy entry retained for the Sprint-0 contract; Sprint 4's
        // canonical drain is flush().
        return [];
    }

    public static function count(): int
    {
        global $wpdb;
        if (!isset($wpdb) || !is_object($wpdb) || !method_exists($wpdb, 'get_var')) {
            return 0;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name from self::table()
        return (int) $wpdb->get_var('SELECT COUNT(*) FROM ' . self::table());
    }

    /**
     * Recent click events for local Enhanced Native CTR bias.
     *
     * @return list<array{keyword: string, object_id: int}>
     */
    public static function recentClicks(int $days = 7): array
    {
        global $wpdb;
        if (!isset($wpdb) || !is_object($wpdb) || !method_exists($wpdb, 'get_results')) {
            return [];
        }
        $since = time() - ($days * 86400);
        $table = self::table();
        $rows  = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT keyword, object_id FROM {$table} WHERE type = %s AND created_at >= %d ORDER BY created_at DESC LIMIT 5000",
                'click',
                $since
            ),
            ARRAY_A
        );
        if (!is_array($rows)) {
            return [];
        }
        $out = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $out[] = [
                'keyword'   => (string) ($row['keyword'] ?? ''),
                'object_id' => (int) ($row['object_id'] ?? 0),
            ];
        }

        return $out;
    }
}
