<?php
/**
 * AuctionForge Site Statistics - Database Schema
 * Creates and manages all statistics tables.
 */

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

class AF_Stats_Database {

    /**
     * Schema version for the af_stats_* subsystem.
     *
     * Bumping this triggers AF_Stats_Init::maybe_create_tables() to re-run
     * create_tables() on the next admin page load after auto-update — adding
     * any new tables introduced since the previous release. dbDelta is
     * idempotent on existing tables, so re-runs are safe.
     *
     * History:
     *   1 — initial af_stats_* tables (visitors, sessions, pageviews, searches,
     *       outbound, errors).
     *   2 — added af_stats_visitor_emails (identity stitching: visitor_hash → email).
     *   3 — added af_stats_lot_keywords (per-lot keyword cache),
     *       af_user_interests (per-user-per-dimension running tally),
     *       af_user_recent_activity (capped per-user event log),
     *       and synced_at columns on event-stream tables for the durable
     *       sync-and-purge lifecycle.
     */
    const DB_VERSION = 3;

    /**
     * Create all statistics tables
     */
    public static function create_tables() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

        // 1. Visitors - unique visitor profiles
        $table = $wpdb->prefix . 'af_stats_visitors';
        $sql = "CREATE TABLE IF NOT EXISTS $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            visitor_hash varchar(64) NOT NULL,
            ip_address varchar(45) NOT NULL DEFAULT '',
            country varchar(2) NOT NULL DEFAULT '',
            city varchar(100) NOT NULL DEFAULT '',
            region varchar(100) NOT NULL DEFAULT '',
            browser varchar(50) NOT NULL DEFAULT '',
            browser_version varchar(20) NOT NULL DEFAULT '',
            os varchar(50) NOT NULL DEFAULT '',
            os_version varchar(20) NOT NULL DEFAULT '',
            device_type varchar(10) NOT NULL DEFAULT 'desktop',
            screen_width smallint unsigned NOT NULL DEFAULT 0,
            screen_height smallint unsigned NOT NULL DEFAULT 0,
            first_seen datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_seen datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            total_visits int unsigned NOT NULL DEFAULT 0,
            total_pageviews int unsigned NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            UNIQUE KEY idx_visitor_hash (visitor_hash),
            KEY idx_country (country),
            KEY idx_device_type (device_type),
            KEY idx_browser (browser),
            KEY idx_first_seen (first_seen),
            KEY idx_last_seen (last_seen)
        ) $charset_collate;";
        dbDelta($sql);

        // 2. Sessions - grouped visits
        $table = $wpdb->prefix . 'af_stats_sessions';
        $sql = "CREATE TABLE IF NOT EXISTS $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            session_hash varchar(64) NOT NULL,
            visitor_id bigint(20) unsigned NOT NULL DEFAULT 0,
            visitor_hash varchar(64) NOT NULL DEFAULT '',
            ip_address varchar(45) NOT NULL DEFAULT '',
            entry_url varchar(2048) NOT NULL DEFAULT '',
            exit_url varchar(2048) NOT NULL DEFAULT '',
            referrer_url varchar(2048) NOT NULL DEFAULT '',
            referrer_domain varchar(255) NOT NULL DEFAULT '',
            utm_source varchar(255) NOT NULL DEFAULT '',
            utm_medium varchar(255) NOT NULL DEFAULT '',
            utm_campaign varchar(255) NOT NULL DEFAULT '',
            utm_term varchar(255) NOT NULL DEFAULT '',
            utm_content varchar(255) NOT NULL DEFAULT '',
            traffic_channel varchar(30) NOT NULL DEFAULT 'direct',
            country varchar(2) NOT NULL DEFAULT '',
            device_type varchar(10) NOT NULL DEFAULT 'desktop',
            browser varchar(50) NOT NULL DEFAULT '',
            pageview_count smallint unsigned NOT NULL DEFAULT 0,
            duration int unsigned NOT NULL DEFAULT 0,
            is_bounce tinyint(1) NOT NULL DEFAULT 1,
            started_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            ended_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_session_hash (session_hash),
            KEY idx_visitor_id (visitor_id),
            KEY idx_started_at (started_at),
            KEY idx_traffic_channel (traffic_channel),
            KEY idx_referrer_domain (referrer_domain),
            KEY idx_country (country),
            KEY idx_device_type (device_type),
            KEY idx_is_bounce (is_bounce)
        ) $charset_collate;";
        dbDelta($sql);

        // 3. Pageviews - every page load
        $table = $wpdb->prefix . 'af_stats_pageviews';
        $sql = "CREATE TABLE IF NOT EXISTS $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            session_id bigint(20) unsigned NOT NULL DEFAULT 0,
            visitor_id bigint(20) unsigned NOT NULL DEFAULT 0,
            visitor_hash varchar(64) NOT NULL DEFAULT '',
            session_hash varchar(64) NOT NULL DEFAULT '',
            url varchar(2048) NOT NULL DEFAULT '',
            url_path varchar(500) NOT NULL DEFAULT '',
            page_title varchar(500) NOT NULL DEFAULT '',
            referrer_url varchar(2048) NOT NULL DEFAULT '',
            time_on_page int unsigned NOT NULL DEFAULT 0,
            load_time int unsigned NOT NULL DEFAULT 0,
            country varchar(2) NOT NULL DEFAULT '',
            device_type varchar(10) NOT NULL DEFAULT 'desktop',
            is_entry tinyint(1) NOT NULL DEFAULT 0,
            is_exit tinyint(1) NOT NULL DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_session_id (session_id),
            KEY idx_visitor_id (visitor_id),
            KEY idx_url_path (url_path(191)),
            KEY idx_created_at (created_at),
            KEY idx_country (country),
            KEY idx_device_type (device_type)
        ) $charset_collate;";
        dbDelta($sql);

        // 4. Searches - internal search queries
        $table = $wpdb->prefix . 'af_stats_searches';
        $sql = "CREATE TABLE IF NOT EXISTS $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            session_id bigint(20) unsigned NOT NULL DEFAULT 0,
            visitor_hash varchar(64) NOT NULL DEFAULT '',
            search_query varchar(500) NOT NULL DEFAULT '',
            search_type varchar(30) NOT NULL DEFAULT 'wordpress',
            results_count int NOT NULL DEFAULT -1,
            url varchar(2048) NOT NULL DEFAULT '',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_search_query (search_query(191)),
            KEY idx_search_type (search_type),
            KEY idx_created_at (created_at)
        ) $charset_collate;";
        dbDelta($sql);

        // 5. Outbound clicks - external link clicks
        $table = $wpdb->prefix . 'af_stats_outbound';
        $sql = "CREATE TABLE IF NOT EXISTS $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            session_id bigint(20) unsigned NOT NULL DEFAULT 0,
            visitor_hash varchar(64) NOT NULL DEFAULT '',
            from_url varchar(2048) NOT NULL DEFAULT '',
            to_url varchar(2048) NOT NULL DEFAULT '',
            to_domain varchar(255) NOT NULL DEFAULT '',
            link_text varchar(500) NOT NULL DEFAULT '',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_to_domain (to_domain),
            KEY idx_created_at (created_at)
        ) $charset_collate;";
        dbDelta($sql);

        // 6. Errors - 404 pages
        $table = $wpdb->prefix . 'af_stats_errors';
        $sql = "CREATE TABLE IF NOT EXISTS $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            url varchar(2048) NOT NULL DEFAULT '',
            url_path varchar(500) NOT NULL DEFAULT '',
            referrer_url varchar(2048) NOT NULL DEFAULT '',
            visitor_hash varchar(64) NOT NULL DEFAULT '',
            ip_address varchar(45) NOT NULL DEFAULT '',
            user_agent varchar(500) NOT NULL DEFAULT '',
            hit_count int unsigned NOT NULL DEFAULT 1,
            first_seen datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_seen datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_url_referrer (url_path(191), referrer_url(191)),
            KEY idx_url_path (url_path(191)),
            KEY idx_last_seen (last_seen),
            KEY idx_hit_count (hit_count)
        ) $charset_collate;";
        dbDelta($sql);

        // 7. Visitor → email map (identity stitching)
        $table = $wpdb->prefix . 'af_stats_visitor_emails';
        $sql = "CREATE TABLE IF NOT EXISTS $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            visitor_hash varchar(64) NOT NULL,
            user_email varchar(190) NOT NULL,
            ip_address varchar(45) NOT NULL DEFAULT '',
            first_seen datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_seen datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_visitor_email (visitor_hash, user_email),
            KEY idx_user_email (user_email),
            KEY idx_last_seen (last_seen)
        ) $charset_collate;";
        dbDelta($sql);

        // 8. Lot keyword cache (extracted once at save_post_lot)
        $table = $wpdb->prefix . 'af_stats_lot_keywords';
        $sql = "CREATE TABLE IF NOT EXISTS $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            post_id bigint(20) unsigned NOT NULL,
            keyword varchar(64) NOT NULL,
            source varchar(16) NOT NULL DEFAULT 'title',
            weight tinyint unsigned NOT NULL DEFAULT 1,
            PRIMARY KEY (id),
            UNIQUE KEY idx_post_keyword (post_id, keyword),
            KEY idx_keyword (keyword)
        ) $charset_collate;";
        dbDelta($sql);

        // 9. User interests aggregator (running tallies per dimension)
        $table = $wpdb->prefix . 'af_user_interests';
        $sql = "CREATE TABLE IF NOT EXISTS $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            user_email varchar(190) NOT NULL,
            dimension varchar(20) NOT NULL,
            dimension_value varchar(190) NOT NULL,
            dimension_label varchar(255) NOT NULL DEFAULT '',
            view_count int unsigned NOT NULL DEFAULT 0,
            fav_count int unsigned NOT NULL DEFAULT 0,
            bid_count int unsigned NOT NULL DEFAULT 0,
            win_count int unsigned NOT NULL DEFAULT 0,
            first_engaged datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_engaged datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_email_dim (user_email, dimension, dimension_value),
            KEY idx_email (user_email),
            KEY idx_dim_value (dimension, dimension_value),
            KEY idx_last_engaged (last_engaged)
        ) $charset_collate;";
        dbDelta($sql);

        // 10. Capped per-user recent activity log
        $table = $wpdb->prefix . 'af_user_recent_activity';
        $sql = "CREATE TABLE IF NOT EXISTS $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            user_email varchar(190) NOT NULL,
            activity_type varchar(20) NOT NULL,
            lot_post_id bigint(20) unsigned NOT NULL DEFAULT 0,
            term_id bigint(20) unsigned NOT NULL DEFAULT 0,
            value varchar(255) DEFAULT NULL,
            occurred_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            synced_at datetime NULL DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY idx_email_when (user_email, occurred_at, activity_type, lot_post_id),
            KEY idx_email (user_email),
            KEY idx_synced (synced_at),
            KEY idx_occurred (occurred_at)
        ) $charset_collate;";
        dbDelta($sql);

        // Add synced_at columns to existing event-stream tables (idempotent
        // via dbDelta — it only ADDs columns not already present).
        self::add_synced_at_columns();

        // 11. Settings
        $defaults = array(
            'af_stats_enabled' => 'on',
            'af_stats_exclude_ips' => '',
            'af_stats_exclude_roles' => 'administrator',
            'af_stats_anonymize_ip' => 'off',
            'af_stats_retention_days' => 0, // 0 = keep forever
            'af_stats_track_logged_in' => 'on',
        );
        foreach ($defaults as $key => $value) {
            if (get_option($key) === false) {
                add_option($key, $value, '', 'no');
            }
        }

        update_option('af_stats_db_version', self::DB_VERSION, 'no');
    }

    /**
     * Add `synced_at` to event-stream tables (idempotent).
     * dbDelta on plain ADD COLUMN can be unreliable across MariaDB versions,
     * so we check information_schema and run ALTER directly.
     */
    private static function add_synced_at_columns() {
        global $wpdb;
        $tables = array(
            'af_stats_pageviews', 'af_stats_sessions',
            'af_stats_searches', 'af_stats_outbound',
        );
        $db = DB_NAME;
        foreach ($tables as $t) {
            $full = $wpdb->prefix . $t;
            $exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.columns
                 WHERE table_schema = %s AND table_name = %s AND column_name = 'synced_at'",
                $db, $full
            ));
            if (!$exists) {
                $wpdb->query("ALTER TABLE $full ADD COLUMN synced_at DATETIME NULL DEFAULT NULL, ADD INDEX idx_synced_at (synced_at)");
            }
        }
    }

    /**
     * Drop all statistics tables
     */
    public static function drop_tables() {
        global $wpdb;
        $tables = array(
            'af_stats_pageviews',
            'af_stats_sessions',
            'af_stats_visitors',
            'af_stats_searches',
            'af_stats_outbound',
            'af_stats_errors',
            'af_stats_visitor_emails',
            'af_stats_lot_keywords',
            'af_user_interests',
            'af_user_recent_activity',
        );
        foreach ($tables as $table) {
            $wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}{$table}");
        }
    }

    /**
     * Purge old data based on retention setting
     */
    public static function purge_old_data() {
        $days = intval(get_option('af_stats_retention_days', 0));
        if ($days <= 0) return; // Keep forever

        global $wpdb;
        $cutoff = date('Y-m-d H:i:s', strtotime("-{$days} days"));

        $tables_date_col = array(
            'af_stats_pageviews' => 'created_at',
            'af_stats_sessions' => 'started_at',
            'af_stats_searches' => 'created_at',
            'af_stats_outbound' => 'created_at',
            'af_stats_errors' => 'last_seen',
        );

        foreach ($tables_date_col as $table => $col) {
            $full = $wpdb->prefix . $table;
            $wpdb->query($wpdb->prepare("DELETE FROM $full WHERE $col < %s", $cutoff));
        }
    }
}
