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

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

class AF_Stats_Database {

    /**
     * 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. 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', 1, 'no');
    }

    /**
     * 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',
        );
        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));
        }
    }
}
