<?php
/**
 * Statistics archiving.
 *
 * Event-stream rows older than a threshold (default 90 days) are MOVED out of
 * the hot tables into parallel `*_archive` tables. Nothing is deleted. The
 * dashboard reads the hot table alone for any range that cannot reach an
 * archived row — which covers the default view and the overwhelming majority of
 * use — and transparently unions in the archive only when the operator asks for
 * a range that reaches further back.
 *
 * The threshold defaults to 90 rather than 30 specifically so it clears the
 * dashboard's longest common preset; see days() for why 30 would have put the
 * default screen on the union path on every single load.
 *
 * Why move rather than partition: partitioning would need the partition key in
 * every unique index, which cannot be done to af_stats_sessions without
 * weakening the session_hash uniqueness that get_or_create_session() depends
 * on. Separate tables also let the archive carry far fewer indexes than the hot
 * table, which is where most of the storage saving comes from.
 *
 * This class REPLACES the destructive branch of AF_Data_Purge for these four
 * tables. Before it existed, a hardcoded 30-day DELETE silently overrode the
 * configured af_stats_retention_days (365 on most sites) and had already
 * destroyed more history than it kept.
 *
 * @since 3.7.3.0
 */

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

class AF_Stats_Archive {

    const HOOK        = 'af_stats_archive_run';
    const OPT_ENABLED = 'af_stats_archive_enabled';
    const OPT_DAYS    = 'af_stats_archive_days';
    const OPT_LAST    = 'af_stats_archive_last_run';
    const SUFFIX      = '_archive';

    /** Rows moved per transaction. Small enough that the row locks are short. */
    const BATCH = 2000;

    /** Wall-clock budget for one cron tick, so a backlog can never hold a worker. */
    const MAX_SECONDS = 20;

    /** Never archive more aggressively than this, whatever the option says. */
    const MIN_DAYS = 7;

    /** @var array<string,array> per-request memo */
    private static $cache = array();

    /**
     * Archived tables => the column that decides a row's age.
     *
     * af_stats_visitors, af_stats_errors and the af_user_* tables are
     * deliberately absent: they are dimension/aggregate tables, not event
     * streams, and the dashboard joins them for rows of any age.
     */
    public static function tables() {
        return array(
            'af_stats_pageviews' => 'created_at',
            'af_stats_sessions'  => 'started_at',
            'af_stats_searches'  => 'created_at',
            'af_stats_outbound'  => 'created_at',
        );
    }

    public static function register() {
        add_action(self::HOOK, array(__CLASS__, 'run'));
        if (!wp_next_scheduled(self::HOOK)) {
            // Offset from the other nightly jobs so they don't contend.
            wp_schedule_event(time() + 1800, 'daily', self::HOOK);
        }
    }

    public static function is_enabled() {
        return get_option(self::OPT_ENABLED, 'on') === 'on';
    }

    /**
     * Default is 90, NOT 30, and the gap from the dashboard's default range is
     * deliberate. get_date_range() (class-stats-ajax.php:194) pins the default
     * "Last 30 days" view to MIDNIGHT of day 30, while boundary() cuts at the
     * cron's time-of-day on day 30. A 30-day threshold therefore puts the
     * default view a few hours over the line on any site with overnight
     * traffic, so the flagship screen would take the union path on every single
     * load — measured 0.43s -> 8.0s on a 49k-row site. Keeping the threshold
     * well clear of the longest common preset keeps the default view hot-only.
     */
    public static function days() {
        $d = (int) get_option(self::OPT_DAYS, 90);
        return $d >= self::MIN_DAYS ? $d : 90;
    }

    /**
     * Rows strictly older than this belong in the archive.
     *
     * Uses current_time('mysql') because that is what writes created_at
     * (class-stats-tracker.php:368) and started_at — mixing in gmdate() here
     * would drift the boundary by the site's UTC offset.
     */
    public static function boundary() {
        return date('Y-m-d H:i:s', strtotime(current_time('mysql')) - self::days() * DAY_IN_SECONDS);
    }

    /* ------------------------------------------------------------------ */
    /* Schema                                                              */
    /* ------------------------------------------------------------------ */

    public static function archive_name($logical) {
        global $wpdb;
        return $wpdb->prefix . $logical . self::SUFFIX;
    }

    public static function table_exists($name) {
        global $wpdb;
        if (!isset(self::$cache['exists'][$name])) {
            self::$cache['exists'][$name] =
                ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $name)) === $name);
        }
        return self::$cache['exists'][$name];
    }

    private static function columns($name) {
        global $wpdb;
        if (!isset(self::$cache['cols'][$name])) {
            $cols = $wpdb->get_col($wpdb->prepare(
                "SELECT column_name FROM information_schema.columns
                  WHERE table_schema = %s AND table_name = %s
                  ORDER BY ordinal_position",
                DB_NAME, $name
            ));
            self::$cache['cols'][$name] = is_array($cols) ? $cols : array();
        }
        return self::$cache['cols'][$name];
    }

    /**
     * Create each archive table as a clone of its hot table, then strip the
     * indexes it does not need.
     *
     * Cloning with CREATE TABLE LIKE rather than a hand-written schema keeps the
     * two definitions in lockstep automatically — INSERT..SELECT between them
     * would break the moment someone added a column to only one of them.
     *
     * The archive keeps its PRIMARY KEY (needed for the batched move and for
     * INSERT IGNORE to be idempotent) and its date index (needed for range
     * selection). Everything else is dropped: archive reads are rare and scan
     * wide ranges anyway, so secondary indexes would cost storage and slow the
     * move for no read benefit.
     */
    public static function ensure_tables() {
        global $wpdb;

        foreach (self::tables() as $logical => $date_col) {
            $hot  = $wpdb->prefix . $logical;
            $arch = self::archive_name($logical);

            if (!self::table_exists($hot)) continue;

            if (!self::table_exists($arch)) {
                $wpdb->query("CREATE TABLE IF NOT EXISTS $arch LIKE $hot");
                unset(self::$cache['exists'][$arch], self::$cache['cols'][$arch]);
                if (!self::table_exists($arch)) continue;

                // Drop every index except PRIMARY and the date index.
                $keep = 'idx_' . $date_col;
                $idx = $wpdb->get_results("SHOW INDEX FROM $arch", ARRAY_A);
                $seen = array();
                foreach ((array) $idx as $row) {
                    $name = $row['Key_name'];
                    if ($name === 'PRIMARY' || $name === $keep || isset($seen[$name])) continue;
                    $seen[$name] = true;
                    $wpdb->query("ALTER TABLE $arch DROP INDEX `" . esc_sql($name) . "`");
                }
                // If the hot table's date index was named something else, make
                // sure the archive still has one on the ageing column.
                $has_date_idx = $wpdb->get_var($wpdb->prepare(
                    "SELECT COUNT(*) FROM information_schema.statistics
                      WHERE table_schema = %s AND table_name = %s AND column_name = %s",
                    DB_NAME, $arch, $date_col
                ));
                if (!$has_date_idx) {
                    $wpdb->query("ALTER TABLE $arch ADD INDEX `$keep` (`$date_col`)");
                }
            }

            self::sync_columns($hot, $arch);
        }
    }

    /**
     * Add to the archive any column the hot table has gained since the archive
     * was created. Without this a later schema change would silently start
     * dropping that column's data on every move.
     */
    private static function sync_columns($hot, $arch) {
        global $wpdb;

        $hot_cols  = self::columns($hot);
        $arch_cols = self::columns($arch);
        if (!$hot_cols || !$arch_cols) return;

        $missing = array_diff($hot_cols, $arch_cols);
        if (!$missing) return;

        foreach ($missing as $col) {
            $def = $wpdb->get_row($wpdb->prepare(
                "SELECT column_type, is_nullable, column_default
                   FROM information_schema.columns
                  WHERE table_schema = %s AND table_name = %s AND column_name = %s",
                DB_NAME, $hot, $col
            ), ARRAY_A);
            if (!$def) continue;

            $null    = ($def['is_nullable'] === 'YES') ? 'NULL' : 'NOT NULL';
            $default = '';
            if ($def['column_default'] !== null) {
                $d = $def['column_default'];
                // MariaDB reports expression defaults unquoted; pass those through.
                $default = (strtoupper($d) === 'CURRENT_TIMESTAMP' || $d === 'NULL')
                    ? " DEFAULT $d"
                    : $wpdb->prepare(' DEFAULT %s', trim($d, "'"));
            } elseif ($def['is_nullable'] === 'YES') {
                $default = ' DEFAULT NULL';
            }

            $wpdb->query("ALTER TABLE $arch ADD COLUMN `" . esc_sql($col) . "` "
                . $def['column_type'] . " $null" . $default);
        }
        unset(self::$cache['cols'][$arch]);
    }

    /* ------------------------------------------------------------------ */
    /* The move                                                            */
    /* ------------------------------------------------------------------ */

    /**
     * Move rows the other way — archive -> hot — for anything that is no longer
     * old enough to be archived.
     *
     * Without this, RAISING the threshold is a one-way door: rows archived under
     * the old, shorter setting stay archived forever, so every report inside the
     * new window keeps paying for the union even though the operator just asked
     * for a bigger live window. Reads stay correct either way (routing follows
     * archive contents, not the threshold), but the performance the setting
     * promises never arrives. Same batching, same idempotency, mirrored.
     */
    private static function restore_recent($logical, $date_col, $boundary, $started) {
        global $wpdb;

        $hot  = $wpdb->prefix . $logical;
        $arch = self::archive_name($logical);
        $cols = array_values(array_intersect(self::columns($hot), self::columns($arch)));
        if (!$cols) return 0;
        $list = '`' . implode('`,`', array_map('esc_sql', $cols)) . '`';

        $moved = 0;
        while ((microtime(true) - $started) < self::MAX_SECONDS) {
            $ids = $wpdb->get_col($wpdb->prepare(
                "SELECT id FROM $arch WHERE $date_col >= %s ORDER BY id ASC LIMIT %d",
                $boundary, self::BATCH
            ));
            if (!$ids) break;

            $min = (int) min($ids);
            $max = (int) max($ids);

            $wpdb->query('START TRANSACTION');
            $ins = $wpdb->query($wpdb->prepare(
                "INSERT IGNORE INTO $hot ($list)
                 SELECT $list FROM $arch WHERE id BETWEEN %d AND %d AND $date_col >= %s",
                $min, $max, $boundary
            ));
            if ($ins === false) {
                $wpdb->query('ROLLBACK');
                error_log('[AuctionForge] stats archive: restore INSERT failed on ' . $hot . ' — ' . $wpdb->last_error);
                break;
            }
            $del = $wpdb->query($wpdb->prepare(
                "DELETE FROM $arch WHERE id BETWEEN %d AND %d AND $date_col >= %s",
                $min, $max, $boundary
            ));
            if ($del === false) {
                $wpdb->query('ROLLBACK');
                error_log('[AuctionForge] stats archive: restore DELETE failed on ' . $arch . ' — ' . $wpdb->last_error);
                break;
            }
            $wpdb->query('COMMIT');
            $moved += (int) $del;
            usleep(50000);
        }
        return $moved;
    }

    /**
     * Move aged rows hot -> archive, in bounded transactional batches.
     *
     * Idempotent: INSERT IGNORE keys on the copied PRIMARY KEY, so a tick that
     * dies between the insert and the delete re-copies the same rows harmlessly
     * on the next run rather than duplicating or losing them.
     */
    public static function run() {
        global $wpdb;

        if (!self::is_enabled()) return;

        self::ensure_tables();

        $started  = microtime(true);
        $boundary = self::boundary();
        $totals   = array();

        foreach (self::tables() as $logical => $date_col) {
            $hot  = $wpdb->prefix . $logical;
            $arch = self::archive_name($logical);
            if (!self::table_exists($hot) || !self::table_exists($arch)) continue;

            // Only columns both tables have, named explicitly — never SELECT *,
            // which would break on any ordinal mismatch.
            $cols = array_values(array_intersect(self::columns($hot), self::columns($arch)));
            if (!$cols) continue;
            $list = '`' . implode('`,`', array_map('esc_sql', $cols)) . '`';

            // Pull back anything the threshold no longer considers old, before
            // pushing out anything it now does.
            $restored = self::restore_recent($logical, $date_col, $boundary, $started);
            if ($restored) $totals[$logical . ':restored'] = $restored;

            $moved = 0;
            while ((microtime(true) - $started) < self::MAX_SECONDS) {
                // Bound the batch by id so both statements drive off the PK.
                $ids = $wpdb->get_col($wpdb->prepare(
                    "SELECT id FROM $hot WHERE $date_col < %s ORDER BY id ASC LIMIT %d",
                    $boundary, self::BATCH
                ));
                if (!$ids) break;

                $min = (int) min($ids);
                $max = (int) max($ids);

                $wpdb->query('START TRANSACTION');

                $ins = $wpdb->query($wpdb->prepare(
                    "INSERT IGNORE INTO $arch ($list)
                     SELECT $list FROM $hot
                      WHERE id BETWEEN %d AND %d AND $date_col < %s",
                    $min, $max, $boundary
                ));

                if ($ins === false) {
                    $wpdb->query('ROLLBACK');
                    error_log('[AuctionForge] stats archive: INSERT failed on ' . $hot . ' — ' . $wpdb->last_error);
                    break;
                }

                $del = $wpdb->query($wpdb->prepare(
                    "DELETE FROM $hot WHERE id BETWEEN %d AND %d AND $date_col < %s",
                    $min, $max, $boundary
                ));

                if ($del === false) {
                    $wpdb->query('ROLLBACK');
                    error_log('[AuctionForge] stats archive: DELETE failed on ' . $hot . ' — ' . $wpdb->last_error);
                    break;
                }

                $wpdb->query('COMMIT');
                $moved += (int) $del;

                // Let other writers in between batches.
                usleep(50000);
            }

            if ($moved) $totals[$logical] = $moved;
        }

        // Every memo in here — archive_max above all — describes the state
        // BEFORE this run. WP-Cron fires inside an ordinary page request, so
        // the same request can go on to render a dashboard; leaving the stale
        // MAX in place would route that render on facts that are no longer
        // true (observed: archive drained to 0 rows, yet table_expr still
        // emitted a UNION against it).
        self::$cache = array();

        update_option(self::OPT_LAST, current_time('mysql'), 'no');
        update_option('af_stats_archive_last_totals', wp_json_encode($totals), 'no');
    }

    /* ------------------------------------------------------------------ */
    /* Read routing                                                        */
    /* ------------------------------------------------------------------ */

    private static function valid_dt($s) {
        return is_string($s) && preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $s) === 1;
    }

    /**
     * Every caller drops this expression inside its own $wpdb->prepare(), where
     * a bare % would be read as a placeholder and corrupt the argument mapping.
     * Identifiers realistically never contain one, but the cost of being sure
     * is a single str_replace and the failure mode is a mangled query.
     */
    private static function for_prepare($sql) {
        return str_replace('%', '%%', $sql);
    }

    /**
     * Newest row actually sitting in an archive table, or '' if it is empty.
     *
     * Routing keys off this rather than off boundary(). The boundary only says
     * where the NEXT move will cut; it says nothing about where past moves cut.
     * Raising af_stats_archive_days (say 30 -> 90) leaves already-archived rows
     * exactly where they are, so a boundary-based test would call a 90-day
     * range "hot-only" and silently undercount every archived row inside it.
     * Comparing against real archive contents cannot drift that way, and it is
     * a single indexed MAX() — memoised per request.
     */
    private static function archive_max($logical) {
        global $wpdb;
        if (!isset(self::$cache['max'][$logical])) {
            $arch = self::archive_name($logical);
            $tables = self::tables();
            $val = '';
            if (isset($tables[$logical]) && self::table_exists($arch)) {
                $col = $tables[$logical];
                $val = (string) $wpdb->get_var("SELECT MAX(`$col`) FROM $arch");
            }
            self::$cache['max'][$logical] = $val;
        }
        return self::$cache['max'][$logical];
    }

    /**
     * Does this range need the archive at all?
     * Used by the dashboard to tell the operator why a query is slow.
     */
    /**
     * Deliberately NOT gated on is_enabled(). That flag controls whether the
     * nightly move RUNS; it must never control whether already-archived rows
     * are READ. Gating reads on it would mean unticking "Archive Old Data"
     * silently drops every archived row from every report — on this site that
     * is 13.6% of the data — while the Archive Status panel on the same screen
     * still counts those rows as retained. A site that has never archived has
     * no archive table, and the check below returns false anyway, so its SQL
     * stays byte-identical to the pre-archive behaviour.
     */
    public static function range_spans_archive($from, $logical = 'af_stats_pageviews') {
        if (!self::valid_dt($from)) return false;
        $max = self::archive_max($logical);
        return $max !== '' && $from <= $max;
    }

    /**
     * Resolve a logical stats table to a SQL table expression, always aliased.
     *
     * Returns a plain `table AS alias` — producing SQL identical to the
     * pre-archive behaviour — unless the requested range reaches back past the
     * archive boundary, in which case it returns an aliased derived table
     * unioning hot and archive with the caller's own bounds pushed into BOTH
     * branches so each side can still use its date index.
     *
     * $from/$to are embedded as literals, not placeholders, so that the caller's
     * own $wpdb->prepare() argument count is unaffected. They are strictly
     * validated as 'Y-m-d H:i:s' first and anything else falls back to hot-only.
     *
     * @param string      $logical e.g. 'af_stats_pageviews'
     * @param string      $alias   alias the caller's SQL refers to
     * @param string|null $from    inclusive lower bound, 'Y-m-d H:i:s'
     * @param string|null $to      inclusive upper bound, 'Y-m-d H:i:s'
     */
    public static function table_expr($logical, $alias, $from = null, $to = null) {
        global $wpdb;

        $hot    = $wpdb->prefix . $logical;
        $tables = self::tables();
        $alias  = preg_replace('/[^A-Za-z0-9_]/', '', (string) $alias);
        if ($alias === '') $alias = 'af_t';

        // Not gated on is_enabled() — see range_spans_archive(). Reads must
        // follow the data, not the toggle.
        if (!isset($tables[$logical])) {
            return self::for_prepare("$hot AS $alias");
        }

        $arch = self::archive_name($logical);
        if (!self::table_exists($arch)) {
            return self::for_prepare("$hot AS $alias");
        }

        // Hot-only whenever the range cannot reach anything the archive holds.
        // This is the default dashboard view, and it keeps that SQL exactly as
        // it was before archiving existed. Note this tests real archive
        // contents, not boundary() — see archive_max() for why.
        if (!self::valid_dt($from) || !self::range_spans_archive($from, $logical)) {
            return self::for_prepare("$hot AS $alias");
        }

        $date_col = $tables[$logical];
        $cols     = array_values(array_intersect(self::columns($hot), self::columns($arch)));
        if (!$cols) {
            return self::for_prepare("$hot AS $alias");
        }
        $list = '`' . implode('`,`', array_map('esc_sql', $cols)) . '`';

        $where = " WHERE `$date_col` >= '" . esc_sql($from) . "'";
        if (self::valid_dt($to)) {
            $where .= " AND `$date_col` <= '" . esc_sql($to) . "'";
        }

        return self::for_prepare(
            "(SELECT $list FROM $hot$where"
            . " UNION ALL SELECT $list FROM $arch$where) AS $alias"
        );
    }

    /**
     * Row counts either side of the boundary, for the admin UI.
     */
    public static function stats() {
        global $wpdb;
        $out = array();
        foreach (self::tables() as $logical => $date_col) {
            $arch = self::archive_name($logical);
            $out[$logical] = array(
                'hot'     => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}{$logical}"),
                'archived' => self::table_exists($arch)
                    ? (int) $wpdb->get_var("SELECT COUNT(*) FROM $arch")
                    : 0,
            );
        }
        return $out;
    }
}
