<?php
/**
 * Database Management Class
 *
 * @package VMPFence
 * @since 1.0.0
 * @since 2.0.0 - Added missing WAF Rules Table, Migration Mechanism for version 2.0.0
 * @since 2.2.0 - Added Country Blocking, Custom Pattern Blocking, Country Blocking Logs table and migration mechanism
 * @since 2.2.4 - Added SIEM Sync tables and migration mechanism
 * @since 2.2.5 - Added enhanced observer-based audit logging
 */

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

/**
 * VMPFence Database Class
 *
 * @since 1.0.0
 */
class VMPFence_Database {

    /**
     * Create all database tables
     *
     * @since 1.0.0
     * @since 2.0.0 -- Decrepited `vmpfence_failed_logins` table replaced with `vmpfence_security_attempts`
     * @since 2.2.0 -- Added Country Blocking, Custom Pattern Blocking, Country Blocking Logs tables
     * @return void
     */
    public static function create_tables() {
        global $wpdb;

        // Include WordPress upgrade functions for dbDelta()
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

        $charset_collate = $wpdb->get_charset_collate();

        // Configuration & Settings Tables
        self::create_config_table($charset_collate);
        self::create_ls_settings_table($charset_collate);

        // Security Events & Monitoring Tables
        self::create_hits_table($charset_collate);
        self::create_logins_table($charset_collate);
        self::create_locked_out_table($charset_collate);
        self::create_blocks_table($charset_collate);
        self::create_blocked_ips_table($charset_collate);
        self::create_security_attempts_table($charset_collate);
        self::create_waf_log_table($charset_collate);
        self::create_events_table($charset_collate);
        self::create_audit_log_table($charset_collate);

        // Scanner & File Monitoring Tables
        self::create_scan_results_table($charset_collate);
        self::create_scan_issues_table($charset_collate);
        self::create_file_mods_table($charset_collate);
        self::create_known_files_table($charset_collate);
        self::create_file_changes_table($charset_collate);
        // Note: Old vmpfence_issues table creation removed - migrated to vmpfence_scan_issues

        // Traffic Analysis Tables
        self::create_live_traffic_human_table($charset_collate);
        self::create_crawlers_table($charset_collate);
        self::create_reverse_cache_table($charset_collate);
        self::create_status_table($charset_collate);
        self::create_traffic_rates_table($charset_collate);

        // Security Features Tables
        self::create_2fa_table($charset_collate);
        self::create_ls_2fa_secrets_table($charset_collate);
        self::create_ls_role_counts_table($charset_collate);
        self::create_notifications_table($charset_collate);
        self::create_pending_issues_table($charset_collate);

        // Central Management Tables
        self::create_snip_cache_table($charset_collate);

        // Advanced Features Tables
        self::create_whois_table($charset_collate);
        self::create_waf_failures_table($charset_collate);
        
        // WAF Rules Table
        self::create_waf_rules_table($charset_collate);
        
        // File Download/View Token Table
        self::create_download_tokens_table();
        
        // Country Blocking Table (NEW - Phase 1)
        self::create_blocked_countries_table($charset_collate);
        
        // Country Block Logs Table (Phase 1)
        self::create_country_block_logs_table($charset_collate);
        
        // Custom Pattern Blocking Table (Day 55)
        self::create_custom_patterns_table($charset_collate);
    }

    /**
     * Create configuration table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_config_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_CONFIG);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            name varchar(100) NOT NULL,
            val longblob,
            autoload enum('no','yes') NOT NULL DEFAULT 'yes',
            PRIMARY KEY (name)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create login security settings table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_ls_settings_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_LS_SETTINGS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            name varchar(100) NOT NULL,
            value longblob,
            PRIMARY KEY (name)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create hits (live traffic) table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_hits_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_HITS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            attackLogTime double(17,6) unsigned NOT NULL,
            ctime double(17,6) unsigned NOT NULL,
            IP varbinary(16) DEFAULT NULL,
            jsRun tinyint DEFAULT '0',
            statusCode int DEFAULT '200',
            isGoogle tinyint DEFAULT '0',
            userID int unsigned DEFAULT NULL,
            newVisit tinyint unsigned DEFAULT '0',
            URL text,
            referer text,
            UA text,
            action varchar(64) NOT NULL DEFAULT '',
            actionDescription text,
            actionData text,
            PRIMARY KEY (id),
            KEY attackLogTime (attackLogTime),
            KEY ctime (ctime),
            KEY IP (IP),
            KEY userID (userID)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create logins table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_logins_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_LOGINS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            hitID int unsigned DEFAULT NULL,
            ctime double(17,6) unsigned NOT NULL,
            fail tinyint unsigned NOT NULL,
            action varchar(40) NOT NULL DEFAULT '',
            username varchar(255) NOT NULL DEFAULT '',
            userID int unsigned NOT NULL DEFAULT '0',
            IP varbinary(16) DEFAULT NULL,
            UA text,
            reason varchar(255) DEFAULT NULL,
            PRIMARY KEY (id),
            KEY k1 (IP,fail,ctime),
            KEY k2 (userID,fail,ctime),
            KEY hitID (hitID),
            KEY ctime (ctime)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create locked out table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_locked_out_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_LOCKED_OUT);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            IP varbinary(16) NOT NULL DEFAULT '',
            blockedTime bigint unsigned NOT NULL,
            reason varchar(255) NOT NULL DEFAULT '',
            lastAttempt int unsigned DEFAULT '0',
            blockedHits int unsigned DEFAULT '0',
            PRIMARY KEY (id),
            UNIQUE KEY IP (IP),
            KEY blockedTime (blockedTime),
            KEY lastAttempt (lastAttempt)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create blocks table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_blocks_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_BLOCKS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            type tinyint unsigned NOT NULL DEFAULT '0',
            IP varbinary(16) DEFAULT NULL,
            blockedTime bigint unsigned NOT NULL,
            reason varchar(255) NOT NULL DEFAULT '',
            lastAttempt int unsigned DEFAULT '0',
            blockedHits int unsigned DEFAULT '0',
            expiration bigint unsigned NOT NULL DEFAULT '0',
            parameters text,
            PRIMARY KEY (id),
            UNIQUE KEY type_IP (type,IP),
            KEY blockedTime (blockedTime),
            KEY expiration (expiration),
            KEY lastAttempt (lastAttempt)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create blocked IPs table
     *
     * @since 1.0.0
     * @since 2.0.0 -- Constraint Changed of `ip_address`
     * @since 2.0.0 -- Added is_active and source columns for API sync functionality, changed blocked_at/blocked_until to int (Unix timestamp)
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_blocked_ips_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_BLOCKED_IPS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            ip_address varchar(45) NOT NULL,
            reason varchar(255) NOT NULL DEFAULT '',
            blocked_at int unsigned NOT NULL,
            blocked_until int unsigned NOT NULL DEFAULT '0',
            block_count int unsigned NOT NULL DEFAULT '1',
            last_attempt datetime DEFAULT NULL,
            is_active tinyint(1) NOT NULL DEFAULT '1',
            source varchar(10) NOT NULL DEFAULT 'local',
            rule_id varchar(50) DEFAULT NULL,
            PRIMARY KEY (id),
            KEY ip_address (ip_address),
            KEY blocked_until (blocked_until),
            KEY blocked_at (blocked_at),
            KEY ip_blocked_until (ip_address, blocked_until),
            KEY is_active (is_active),
            KEY source (source)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create security attempts table
     *
     * @since 1.0.0
     * @since 2.0.0 -- Decrepited `vmpfence_failed_logins` table renamed with `vmpfence_security_attempts`
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_security_attempts_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_SECURITY_ATTEMPTS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            ip_address varchar(45) NOT NULL,
            username varchar(60) DEFAULT NULL,
            event_type varchar(32) NOT NULL DEFAULT 'failed_login',
            user_agent text,
            attempt_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            blocked_until datetime DEFAULT NULL,
            attempt_count int unsigned NOT NULL DEFAULT '1',
            geo_location varchar(100) DEFAULT NULL,
            PRIMARY KEY (id),
            KEY ip_address (ip_address),
            KEY username (username),
            KEY event_type (event_type),
            KEY attempt_time (attempt_time),
            KEY blocked_until (blocked_until),
            KEY event_type_ip_time (event_type, ip_address, attempt_time)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create WAF log table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_waf_log_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_WAF_LOG);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            attackLogTime double(17,6) unsigned NOT NULL,
            IP varbinary(16) DEFAULT NULL,
            countryCode char(2) DEFAULT NULL,
            severity tinyint unsigned NOT NULL DEFAULT '0',
            action varchar(16) NOT NULL DEFAULT '',
            actionDescription varchar(255) DEFAULT NULL,
            actionData text,
            logMsg text,
            request_uri text,
            http_referer text,
            http_user_agent text,
            PRIMARY KEY (id),
            KEY attackLogTime (attackLogTime),
            KEY IP (IP),
            KEY countryCode (countryCode)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create events table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_events_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_EVENTS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            time int unsigned NOT NULL,
            event_type varchar(64) NOT NULL,
            severity tinyint unsigned NOT NULL DEFAULT '0',
            source_ip varbinary(16) DEFAULT NULL,
            user_id int unsigned DEFAULT NULL,
            username varchar(255) DEFAULT NULL,
            message text,
            data text,
            PRIMARY KEY (id),
            KEY time (time),
            KEY event_type (event_type),
            KEY source_ip (source_ip),
            KEY user_id (user_id)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create audit log table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_audit_log_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_AUDIT_LOG);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            time double(17,6) unsigned NOT NULL,
            event_type varchar(64) NOT NULL,
            user_id int unsigned DEFAULT NULL,
            username varchar(255) DEFAULT NULL,
            user_role varchar(64) DEFAULT NULL,
            source_ip varbinary(16) DEFAULT NULL,
            user_agent text,
            object_type varchar(64) DEFAULT NULL,
            object_id bigint unsigned DEFAULT NULL,
            object_name varchar(255) DEFAULT NULL,
            action varchar(64) NOT NULL,
            message text,
            old_value longtext,
            new_value longtext,
            additional_info longtext,
            PRIMARY KEY (id),
            KEY time (time),
            KEY event_type (event_type),
            KEY user_id (user_id),
            KEY source_ip (source_ip),
            KEY action (action)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create scan results table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_scan_results_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_SCAN_RESULTS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            scan_type varchar(32) NOT NULL DEFAULT 'file_changes',
            scan_time datetime NOT NULL,
            total_files int unsigned DEFAULT '0',
            issues_found int unsigned DEFAULT '0',
            scan_status varchar(32) NOT NULL DEFAULT 'completed',
            scan_data longtext,
            created_at timestamp DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY scan_time (scan_time),
            KEY scan_type (scan_type),
            KEY scan_status (scan_status)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create scan issues table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_scan_issues_table($charset_collate) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'vmpfence_scan_issues';
        $scan_results_table = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_SCAN_RESULTS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            scan_id int unsigned NOT NULL,
            file_path varchar(255) NOT NULL,
            issue_type varchar(50) NOT NULL,
            type varchar(50) NOT NULL DEFAULT '',
            severity varchar(20) NOT NULL DEFAULT 'medium',
            description text,
            file_hash varchar(64),
            file_size bigint unsigned,
            detected_time datetime NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'new',
            resolved_time datetime NULL,
            is_repairable tinyint(1) DEFAULT 0,
            repair_status varchar(20) DEFAULT 'not_checked',
            repair_attempted datetime NULL,
            backup_path varchar(500) DEFAULT '',
            PRIMARY KEY (id),
            KEY scan_id (scan_id),
            KEY issue_type (issue_type),
            KEY type (type),
            KEY severity (severity),
            KEY status (status),
            KEY detected_time (detected_time),
            KEY is_repairable (is_repairable),
            KEY repair_status (repair_status)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create file modifications table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_file_mods_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_FILE_MODS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            filename varchar(1000) NOT NULL,
            knownFile tinyint NOT NULL,
            oldMD5 varchar(32) NOT NULL DEFAULT '',
            newMD5 varchar(32) NOT NULL DEFAULT '',
            SHAC varchar(64) DEFAULT NULL,
            stoppedOnSignature varchar(255) DEFAULT NULL,
            stoppedOnPosition int unsigned DEFAULT NULL,
            isSafeFile varchar(1) DEFAULT NULL,
            PRIMARY KEY (id),
            KEY filename (filename(191)),
            KEY oldMD5 (oldMD5),
            KEY newMD5 (newMD5)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create known files table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_known_files_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_KNOWN_FILES);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            file_path varchar(500) NOT NULL,
            file_type varchar(20) NOT NULL DEFAULT 'unknown',
            shac binary(32) NOT NULL,
            file_size bigint(20) unsigned NOT NULL DEFAULT 0,
            last_updated datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            component varchar(100) DEFAULT NULL,
            version varchar(50) DEFAULT NULL,
            is_queued tinyint(1) NOT NULL DEFAULT 0,
            repair_source varchar(50) DEFAULT 'wordpress.org',
            PRIMARY KEY (id),
            KEY file_path (file_path(191)),
            KEY file_type (file_type),
            KEY shac (shac),
            KEY component (component),
            KEY is_queued (is_queued)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create file changes table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_file_changes_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_FILE_CHANGES);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            file varchar(1000) NOT NULL,
            md5 varchar(32) NOT NULL,
            timestamp double(17,6) unsigned NOT NULL,
            action varchar(16) NOT NULL DEFAULT '',
            PRIMARY KEY (id),
            KEY file (file(191)),
            KEY timestamp (timestamp),
            KEY action (action)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create issues table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_issues_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_ISSUES);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            time int unsigned NOT NULL,
            last_updated int unsigned NOT NULL,
            status varchar(10) NOT NULL,
            type varchar(20) NOT NULL,
            severity tinyint unsigned NOT NULL DEFAULT '0',
            ignoreP char(1) NOT NULL DEFAULT '',
            ignoreC char(1) NOT NULL DEFAULT '',
            shortMsg varchar(255) NOT NULL DEFAULT '',
            longMsg text,
            data longtext,
            PRIMARY KEY (id),
            KEY type (type),
            KEY time (time),
            KEY status (status),
            KEY ignoreP (ignoreP),
            KEY ignoreC (ignoreC)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create live traffic human table
     *
     * @since 1.0.0
     * @since 2.0.0 -- Modified PRIMARY KEY to (IP,identifier) from IP only
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_live_traffic_human_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_LIVE_TRAFFIC_HUMAN);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            IP varbinary(16) NOT NULL,
            identifier varchar(32) NOT NULL,
            expiration int unsigned NOT NULL,
            PRIMARY KEY (IP,identifier),
            KEY expiration (expiration),
            KEY identifier (identifier)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create crawlers table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_crawlers_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_CRAWLERS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            IP varbinary(16) NOT NULL,
            patternSig varbinary(16) NOT NULL,
            status char(8) NOT NULL DEFAULT '',
            lastUpdate int unsigned NOT NULL,
            PTR varchar(255) DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY IP (IP),
            KEY patternSig (patternSig),
            KEY lastUpdate (lastUpdate),
            KEY status (status)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create reverse cache table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_reverse_cache_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_REVERSE_CACHE);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            IP varbinary(16) NOT NULL,
            host varchar(255) NOT NULL DEFAULT '',
            lastUpdate int unsigned NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY IP (IP),
            KEY lastUpdate (lastUpdate)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create status table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_status_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_STATUS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id tinyint unsigned NOT NULL AUTO_INCREMENT,
            ctime double(17,6) unsigned NOT NULL,
            level tinyint unsigned NOT NULL,
            type char(5) NOT NULL,
            msg varchar(1000) NOT NULL,
            PRIMARY KEY (id),
            KEY k1 (ctime),
            KEY k2 (type)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create traffic rates table
     *
     * @since 1.0.0
     * @since 2.0.0 -- Modified PRIMARY KEY to (window_start,IP,hitType) from (IP,window_start)
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_traffic_rates_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_TRAFFIC_RATES);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            IP varbinary(16) NOT NULL,
            hitCount int unsigned NOT NULL DEFAULT '0',
            hitType enum('hit','404') NOT NULL DEFAULT 'hit',
            window_start int unsigned NOT NULL,
            PRIMARY KEY (window_start,IP,hitType),
            KEY IP (IP),
            KEY window_start (window_start)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create 2FA table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_2fa_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_2FA);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint unsigned NOT NULL,
            phone varchar(15) NOT NULL DEFAULT '',
            country_code varchar(2) NOT NULL DEFAULT '',
            enabled tinyint NOT NULL DEFAULT '0',
            secret varchar(32) DEFAULT NULL,
            recovery varchar(64) DEFAULT NULL,
            ctime int unsigned NOT NULL,
            twofa_last_login int unsigned NOT NULL DEFAULT '0',
            PRIMARY KEY (id),
            UNIQUE KEY user_id (user_id),
            KEY phone (phone),
            KEY enabled (enabled)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create login security 2FA secrets table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_ls_2fa_secrets_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_LS_2FA_SECRETS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint unsigned NOT NULL,
            secret varchar(255) NOT NULL,
            recovery_codes text,
            activated int unsigned NOT NULL DEFAULT '0',
            mode varchar(16) NOT NULL DEFAULT 'authenticator',
            PRIMARY KEY (id),
            UNIQUE KEY user_id (user_id),
            KEY activated (activated)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create login security role counts table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_ls_role_counts_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_LS_ROLE_COUNTS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            role varchar(50) NOT NULL,
            count_users int unsigned NOT NULL DEFAULT '0',
            count_with_2fa int unsigned NOT NULL DEFAULT '0',
            updated_time int unsigned NOT NULL,
            PRIMARY KEY (role),
            KEY updated_time (updated_time)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create notifications table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_notifications_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_NOTIFICATIONS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            category varchar(32) NOT NULL,
            priority tinyint unsigned NOT NULL DEFAULT '0',
            ctime int unsigned NOT NULL,
            html text NOT NULL,
            links text,
            PRIMARY KEY (id),
            KEY category (category),
            KEY priority (priority),
            KEY ctime (ctime)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create pending issues table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_pending_issues_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_PENDING_ISSUES);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            ctime int unsigned NOT NULL,
            issue_type varchar(50) NOT NULL,
            severity tinyint unsigned NOT NULL DEFAULT '0',
            status varchar(20) NOT NULL DEFAULT 'new',
            data longtext,
            PRIMARY KEY (id),
            KEY ctime (ctime),
            KEY issue_type (issue_type),
            KEY status (status)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create SNIP cache table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_snip_cache_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_SNIP_CACHE);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            IP varbinary(16) NOT NULL,
            expiration int unsigned NOT NULL,
            body varchar(255) NOT NULL DEFAULT '',
            count tinyint unsigned NOT NULL DEFAULT '0',
            PRIMARY KEY (id),
            UNIQUE KEY IP (IP),
            KEY expiration (expiration)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create WHOIS table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_whois_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_WHOIS);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            IP varbinary(16) NOT NULL,
            ctime int unsigned NOT NULL,
            whois_data longtext,
            PRIMARY KEY (id),
            UNIQUE KEY IP (IP),
            KEY ctime (ctime)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create WAF failures table
     *
     * @since 1.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_waf_failures_table($charset_collate) {
        global $wpdb;

        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_WAF_FAILURES);

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            ctime double(17,6) unsigned NOT NULL,
            IP varbinary(16) DEFAULT NULL,
            fail_count int unsigned NOT NULL DEFAULT '1',
            blocked_until int unsigned NOT NULL DEFAULT '0',
            reason varchar(255) DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY IP (IP),
            KEY ctime (ctime),
            KEY blocked_until (blocked_until)
        ) $charset_collate;";

        dbDelta($sql);
    }

    /**
     * Create download tokens table for secure file viewing/downloading
     *
     * @since 1.0.0
     * @return void
     */
    private static function create_download_tokens_table() {
        global $wpdb;

        $table_name = $wpdb->prefix . 'vmpfence_download_tokens';
        
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
            id int(11) NOT NULL AUTO_INCREMENT,
            token varchar(255) NOT NULL,
            issue_id int(11) NOT NULL,
            file_path text NOT NULL,
            filename varchar(255) NOT NULL,
            expiry int(11) NOT NULL,
            created_at int(11) NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY token (token),
            KEY expiry (expiry)
        ) {$charset_collate};";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }

    /**
     * Drop all database tables
     *
     * @since 1.0.0
     * @since 2.0.0 -- Decrepited `vmpfence_failed_logins` table replaced with `vmpfence_security_attempts`
     * @since 2.2.2 -- Added drop for additional tables created by other systems
     * @return void
     */
    public static function drop_tables() {
        global $wpdb;

        $tables = array(
            VMPFence_Constants::TABLE_CONFIG,
            VMPFence_Constants::TABLE_LS_SETTINGS,
            VMPFence_Constants::TABLE_HITS,
            VMPFence_Constants::TABLE_LOGINS,
            VMPFence_Constants::TABLE_LOCKED_OUT,
            VMPFence_Constants::TABLE_BLOCKS,
            VMPFence_Constants::TABLE_BLOCKED_IPS,
            VMPFence_Constants::TABLE_BLOCKED_COUNTRIES,
            VMPFence_Constants::TABLE_SECURITY_ATTEMPTS,
            VMPFence_Constants::TABLE_WAF_LOG,
            VMPFence_Constants::TABLE_EVENTS,
            VMPFence_Constants::TABLE_AUDIT_LOG,
            VMPFence_Constants::TABLE_SCAN_RESULTS,
            VMPFence_Constants::TABLE_FILE_MODS,
            VMPFence_Constants::TABLE_KNOWN_FILES,
            VMPFence_Constants::TABLE_FILE_CHANGES,
            VMPFence_Constants::TABLE_ISSUES,
            VMPFence_Constants::TABLE_LIVE_TRAFFIC_HUMAN,
            VMPFence_Constants::TABLE_CRAWLERS,
            VMPFence_Constants::TABLE_REVERSE_CACHE,
            VMPFence_Constants::TABLE_STATUS,
            VMPFence_Constants::TABLE_TRAFFIC_RATES,
            VMPFence_Constants::TABLE_2FA,
            VMPFence_Constants::TABLE_LS_2FA_SECRETS,
            VMPFence_Constants::TABLE_LS_ROLE_COUNTS,
            VMPFence_Constants::TABLE_NOTIFICATIONS,
            VMPFence_Constants::TABLE_PENDING_ISSUES,
            VMPFence_Constants::TABLE_SNIP_CACHE,
            VMPFence_Constants::TABLE_WHOIS,
            VMPFence_Constants::TABLE_WAF_FAILURES,
            VMPFence_Constants::TABLE_DOWNLOAD_TOKENS,
            VMPFence_Constants::TABLE_WAF_RULES,
            VMPFence_Constants::TABLE_COUNTRY_BLOCK_LOGS,
            VMPFence_Constants::TABLE_CUSTOM_PATTERNS
        );

        foreach ($tables as $table) {
            $table_name = VMPFence_Constants::get_table_name($table);
            $result = $wpdb->query("DROP TABLE IF EXISTS $table_name");  // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            // if ($result === false) {
            //     error_log('VMPFence_Database::drop_tables() - ERROR dropping table ' . $table_name . ': ' . $wpdb->last_error);
            // } else {
            //     error_log('VMPFence_Database::drop_tables() - Successfully dropped table: ' . $table_name);
            // }
        }
        
        // Drop trusted hashes table using its own drop method
        if (class_exists('VMPFence_Trusted_Hashes_DB')) {
            VMPFence_Trusted_Hashes_DB::drop_table();
        }
        
        // Drop additional tables not in constants (created by separate systems)
        $additional_tables = array(
            'analytics',           // Created by VMPFence_Logging_Database
            'api_logs',           // Created by VMPFence_Logging_Database  
            'geoip'              // Created by VMPFence_GeoIP_Lookup
        );
        
        
        foreach ($additional_tables as $table_suffix) {
            $table_name = $wpdb->prefix . 'vmpfence_' . $table_suffix;
            $result = $wpdb->query("DROP TABLE IF EXISTS `{$table_name}`");  // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            // if ($result === false) {
            //     error_log('VMPFence_Database::drop_tables() - ERROR dropping table ' . $table_name . ': ' . $wpdb->last_error);
            // } else {
            //     error_log('VMPFence_Database::drop_tables() - Successfully dropped table: ' . $table_name);
            // }
        }
        
        // error_log('VMPFence_Database::drop_tables() - Completed table deletion');
    }

    /**
     * Check if database needs update
     *
     * @since 1.0.0
     * @return bool
     */
    public function needs_update() {
        $current_version = get_option('vmpfence_db_version', '0.0.0');
        return version_compare($current_version, VMPFENCE_DB_VERSION, '<');
    }

    /**
     * Update database structure
     *
     * @since 1.0.0
     * @param string $from_version Previous version
     * @return void
     */
    public function update_database($from_version) {
        // Perform database migrations based on version
        if (version_compare($from_version, '1.0.0', '<')) {
            self::create_tables();
        }

        // Fire hook for observer-based audit logging
        do_action('vmpfence_database_updated', $from_version, VMPFENCE_DB_VERSION);
    }
    /**
     * Get table schema
     *
     * @since 1.0.0
     * @param string $table_name Table name
     * @return array
     */
    public function get_table_schema($table_name) {
        global $wpdb;
        
        $full_table_name = VMPFence_Constants::get_table_name($table_name);
        return $wpdb->get_results("DESCRIBE $full_table_name", ARRAY_A);  // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    }

    /**
     * Cleanup old records
     *
     * @since 1.0.0
     * @since 2.1.0 -- Enhanced cleanup logic for hits table and added cleanup for WAF log, login attempts, and expired blocks
     * @return void
     * 
     */
    public function cleanup_old_records() {
        global $wpdb;

        // Get retention settings from Live Traffic options
        $retention_days = absint(get_option('vmpfence_max_traffic_days', 30));
        $max_rows = absint(get_option('vmpfence_max_traffic_rows', 2000));

        // Cleanup hits table (Live Traffic)
        $hits_table = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_HITS);
        // First, cleanup by days
        $cutoff_time = time() - ($retention_days * 86400);
        $deleted_by_days = $wpdb->query($wpdb->prepare("DELETE FROM $hits_table WHERE attackLogTime < %f", $cutoff_time));  // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // Then, cleanup by row count (keep only the latest max_rows entries)
        $current_count = $wpdb->get_var("SELECT COUNT(*) FROM $hits_table");  // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        if ($current_count > $max_rows) {
            $rows_to_delete = $current_count - $max_rows;
            // Delete oldest entries beyond the limit
            $wpdb->query($wpdb->prepare("DELETE FROM $hits_table ORDER BY attackLogTime ASC LIMIT %d", $rows_to_delete));  // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        }

        // Cleanup WAF log table
        $waf_log_table = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_WAF_LOG);
        $wpdb->query($wpdb->prepare("DELETE FROM $waf_log_table WHERE attackLogTime < %f", $cutoff_time));  // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        // Cleanup login attempts table
        $logins_table = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_LOGINS);
        $wpdb->query($wpdb->prepare("DELETE FROM $logins_table WHERE ctime < %f", $cutoff_time));  // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        // Cleanup expired blocks
        $blocks_table = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_BLOCKS);
        $current_time = time();
        $wpdb->query($wpdb->prepare("DELETE FROM $blocks_table WHERE expiration > 0 AND expiration < %d", $current_time));  // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        // Fire hook for observer-based audit logging
        do_action('vmpfence_database_cleanup', $retention_days, $max_rows, $deleted_by_days);
    }

    /**
     * Get database statistics
     *
     * @since 1.0.0
     * @return array
     */
    public function get_statistics() {
        global $wpdb;

        $stats = array();

        // Get table sizes
        $tables = array(
            'hits' => VMPFence_Constants::TABLE_HITS,
            'logins' => VMPFence_Constants::TABLE_LOGINS,
            'blocks' => VMPFence_Constants::TABLE_BLOCKS,
            'waf_log' => VMPFence_Constants::TABLE_WAF_LOG,
            'scan_results' => VMPFence_Constants::TABLE_SCAN_RESULTS,
            'audit_log' => VMPFence_Constants::TABLE_AUDIT_LOG
        );

        foreach ($tables as $key => $table) {
            $table_name = VMPFence_Constants::get_table_name($table);
            $count = $wpdb->get_var("SELECT COUNT(*) FROM $table_name");  // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $stats[$key . '_count'] = intval($count);
        }

        return $stats;
    }

    /**
     * Run database migrations based on version
     *
     * @since 2.0.0
     * @since 2.1.0 -- Added migration to 2.1.0 to enhance blocked_ips table
     * @since 2.2.0 -- Added migration to 2.2.0 for country blocking and custom patterns and logs tables
     * @since 2.2.4 -- Added SIEM sync columns
     * @param string $from_version Previous database version
     * @return void
     */
    public static function run_migrations($from_version) {
        global $wpdb;
        
        // Migration from 1.0.0 to 2.0.0
        if (version_compare($from_version, '2.0.0', '<')) {
            self::migrate_to_2_0_0();
        }
        
        // Migration to 2.1.0 - Add source and is_active columns to blocked_ips
        if (version_compare($from_version, '2.1.0', '<')) {
            self::migrate_to_2_1_0();
        }
        
        // Migration to 2.2.0 - Add country blocking and custom patterns tables
        if (version_compare($from_version, '2.2.0', '<')) {
            self::migrate_to_2_2_0();
        }

        // Migration to 2.3.0 - Add SIEM sync columns and tables
        if (version_compare($from_version, '2.3.0', '<')) {
            if (class_exists('VmpFence_DB_Upgrade')) {
                VmpFence_DB_Upgrade::upgrade();
            }
        }
    }
    
    /**
     * Migrate database from 1.0.0 to 2.0.0
     * - Rename vmpfence_failed_logins to vmpfence_security_attempts
     * - Add event_type column and indexes
     * - Modify vmpfence_blocked_ips indexes
     * - Modify vmpfence_live_traffic_human primary key
     * - Modify vmpfence_traffic_rates structure
     * 
     * Note: New tables (vmpfence_waf_rules) will be created automatically 
     * by create_tables() since they don't exist
     *
     * @since 2.0.0
     * @return void
     */
    private static function migrate_to_2_0_0() {
        global $wpdb;
        
        // 1. Rename vmpfence_failed_logins to vmpfence_security_attempts
        $old_table = $wpdb->prefix . 'vmpfence_failed_logins';
        $new_table = $wpdb->prefix . 'vmpfence_security_attempts';
        
        $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $old_table));
        if ($table_exists) {
            $wpdb->query("RENAME TABLE `{$old_table}` TO `{$new_table}`");
            
            // Add event_type column
            $wpdb->query(
                "ALTER TABLE `{$new_table}` 
                ADD COLUMN `event_type` varchar(32) NOT NULL DEFAULT 'failed_login' 
                AFTER `username`"
            );
            
            // Add new indexes
            $wpdb->query("ALTER TABLE `{$new_table}` ADD INDEX `event_type` (`event_type`)");
            $wpdb->query(
                "ALTER TABLE `{$new_table}` 
                ADD INDEX `event_type_ip_time` (`event_type`, `ip_address`, `attempt_time`)"
            );
        }
        
        // 2. Modify vmpfence_blocked_ips - Change UNIQUE to regular index
        $blocked_ips_table = $wpdb->prefix . 'vmpfence_blocked_ips';
        
        $unique_exists = $wpdb->get_results(
            "SHOW INDEX FROM `{$blocked_ips_table}` 
            WHERE Key_name = 'ip_address' AND Non_unique = 0"
        );
        
        if (!empty($unique_exists)) {
            $wpdb->query("ALTER TABLE `{$blocked_ips_table}` DROP INDEX `ip_address`");
            $wpdb->query("ALTER TABLE `{$blocked_ips_table}` ADD INDEX `ip_address` (`ip_address`)");
        }
        
        // Add composite index if not exists
        $composite_exists = $wpdb->get_results(
            "SHOW INDEX FROM `{$blocked_ips_table}` WHERE Key_name = 'ip_blocked_until'"
        );
        
        if (empty($composite_exists)) {
            $wpdb->query(
                "ALTER TABLE `{$blocked_ips_table}` 
                ADD INDEX `ip_blocked_until` (`ip_address`, `blocked_until`)"
            );
        }
        
        // 3. Modify vmpfence_live_traffic_human - Change primary key
        $traffic_human_table = $wpdb->prefix . 'vmpfence_live_traffic_human';
        
        // Check if old structure (with id column) exists
        $has_id = $wpdb->get_results(
            "SHOW COLUMNS FROM `{$traffic_human_table}` WHERE Field = 'id'"
        );
        
        if (!empty($has_id)) {
            // Clean NULL values first
            $wpdb->query(
                "DELETE FROM `{$traffic_human_table}` 
                WHERE IP IS NULL OR identifier IS NULL"
            );
            
            // Drop id column first (must drop AUTO_INCREMENT before dropping PRIMARY KEY)
            $wpdb->query("ALTER TABLE `{$traffic_human_table}` DROP COLUMN `id`");
            
            // Make columns NOT NULL
            $wpdb->query(
                "ALTER TABLE `{$traffic_human_table}` 
                MODIFY `IP` varbinary(16) NOT NULL"
            );
            $wpdb->query(
                "ALTER TABLE `{$traffic_human_table}` 
                MODIFY `identifier` varchar(32) NOT NULL"
            );
            
            // Add new composite primary key
            $wpdb->query(
                "ALTER TABLE `{$traffic_human_table}` 
                ADD PRIMARY KEY (`IP`, `identifier`)"
            );
        }
        
        // 4. Modify vmpfence_traffic_rates - Add hitType and change primary key
        $traffic_rates_table = $wpdb->prefix . 'vmpfence_traffic_rates';
        
        // Check if old structure exists
        $has_id = $wpdb->get_results(
            "SHOW COLUMNS FROM `{$traffic_rates_table}` WHERE Field = 'id'"
        );
        
        if (!empty($has_id)) {
            // Drop old UNIQUE constraint first if exists
            $unique_exists = $wpdb->get_results(
                "SHOW INDEX FROM `{$traffic_rates_table}` WHERE Key_name = 'IP_window'"
            );
            if (!empty($unique_exists)) {
                $wpdb->query("ALTER TABLE `{$traffic_rates_table}` DROP INDEX `IP_window`");
            }
            
            // Drop id column (must drop AUTO_INCREMENT before dropping PRIMARY KEY)
            $wpdb->query("ALTER TABLE `{$traffic_rates_table}` DROP COLUMN `id`");
            
            // Drop blocked column if exists
            $has_blocked = $wpdb->get_results(
                "SHOW COLUMNS FROM `{$traffic_rates_table}` WHERE Field = 'blocked'"
            );
            if (!empty($has_blocked)) {
                $wpdb->query("ALTER TABLE `{$traffic_rates_table}` DROP COLUMN `blocked`");
            }
            
            // Add hitType column
            $wpdb->query(
                "ALTER TABLE `{$traffic_rates_table}` 
                ADD COLUMN `hitType` enum('hit','404') NOT NULL DEFAULT 'hit' 
                AFTER `hitCount`"
            );
            
            // Add new composite primary key
            $wpdb->query(
                "ALTER TABLE `{$traffic_rates_table}` 
                ADD PRIMARY KEY (`window_start`, `IP`, `hitType`)"
            );
        }
    }

    /**
     * Migrate database to version 2.1.0
     * - Add is_active and source columns to blocked_ips table
     * - Convert datetime columns to int (Unix timestamp)
     * - Add rule_id column
     *
     * @since 2.1.0
     * @return void
     */
    private static function migrate_to_2_1_0() {
        // Run the blocked IPs table migration
        self::migrate_blocked_ips_table();
    }

    /**
     * Migrate database to version 2.2.0
     * - Create blocked countries table
     * - Create country block logs table
     * - Create custom patterns table
     *
     * @since 2.2.0
     * @return void
     */
    private static function migrate_to_2_2_0() {
        global $wpdb;
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        $charset_collate = $wpdb->get_charset_collate();
        
        // Create blocked countries table if it doesn't exist
        $blocked_countries_table = $wpdb->prefix . 'vmpfence_blocked_countries';
        if ($wpdb->get_var("SHOW TABLES LIKE '{$blocked_countries_table}'") !== $blocked_countries_table) {
            self::create_blocked_countries_table($charset_collate);
            
            if (class_exists('VMPFence_Secure_Logger')) {
                VMPFence_Secure_Logger::info('Migration 2.2.0: Created blocked_countries table');
            }
        }
        
        // Create country block logs table if it doesn't exist
        $country_logs_table = $wpdb->prefix . 'vmpfence_country_block_logs';
        if ($wpdb->get_var("SHOW TABLES LIKE '{$country_logs_table}'") !== $country_logs_table) {
            self::create_country_block_logs_table($charset_collate);
            
            if (class_exists('VMPFence_Secure_Logger')) {
                VMPFence_Secure_Logger::info('Migration 2.2.0: Created country_block_logs table');
            }
        }
        
        // Create custom patterns table if it doesn't exist
        $custom_patterns_table = $wpdb->prefix . 'vmpfence_custom_patterns';
        if ($wpdb->get_var("SHOW TABLES LIKE '{$custom_patterns_table}'") !== $custom_patterns_table) {
            self::create_custom_patterns_table($charset_collate);
            
            if (class_exists('VMPFence_Secure_Logger')) {
                VMPFence_Secure_Logger::info('Migration 2.2.0: Created custom_patterns table');
            }
        }
    }

    /**
     * Create WAF rules table
     *
     * @since 2.0.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_waf_rules_table($charset_collate) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'vmpfence_waf_rules';

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id int unsigned NOT NULL AUTO_INCREMENT,
            rule_id varchar(100) NOT NULL,
            enabled tinyint(1) NOT NULL DEFAULT '1',
            category varchar(50) NOT NULL DEFAULT '',
            severity tinyint unsigned NOT NULL DEFAULT '0',
            pattern text NOT NULL,
            description varchar(255) NOT NULL DEFAULT '',
            version varchar(20) NOT NULL DEFAULT '1.0',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            score int unsigned NOT NULL DEFAULT '50',
            action varchar(16) NOT NULL DEFAULT 'block',
            whitelist tinyint(1) NOT NULL DEFAULT '0',
            PRIMARY KEY (id),
            UNIQUE KEY rule_id (rule_id),
            KEY enabled (enabled),
            KEY category (category),
            KEY whitelist (whitelist)
        ) $charset_collate;";

        dbDelta($sql);
    }
    
    /**
     * Check if the standalone events table exists
     *
     * @since 2.0.0
     * @return bool True if table exists, false otherwise
     */
    public static function events_table_exists() {
        global $wpdb;
        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_EVENTS);
        $result = $wpdb->get_var("SHOW TABLES LIKE '{$table_name}'");
        return $result === $table_name;
    }
    
    /**
     * Ensure the standalone events table exists (create if missing)
     *
     * @since 2.0.0
     * @return void
     */
    public static function ensure_events_table_exists() {
        if (!self::events_table_exists()) {
            global $wpdb;
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            $charset_collate = $wpdb->get_charset_collate();
            self::create_events_table($charset_collate);
        }
    }

    /**
     * Migrate blocked IPs table to add is_active and source columns
     *
     * @since 2.1.0
     * @return void
     */
    public static function migrate_blocked_ips_table() {
        global $wpdb;
        
        $table_name = VMPFence_Constants::get_table_name(VMPFence_Constants::TABLE_BLOCKED_IPS);
        
        // Check current column types
        $columns = $wpdb->get_results("SHOW COLUMNS FROM `{$table_name}`", ARRAY_A);
        $column_map = array();
        foreach ($columns as $column) {
            $column_map[$column['Field']] = $column;
        }
        
        // Migrate blocked_at from datetime to int if needed
        if (isset($column_map['blocked_at']) && strpos($column_map['blocked_at']['Type'], 'datetime') !== false) {
            // First, convert existing datetime values to Unix timestamps
            $wpdb->query("
                UPDATE `{$table_name}` 
                SET blocked_at = UNIX_TIMESTAMP(blocked_at)
                WHERE blocked_at IS NOT NULL
            ");
            
            // Then change column type to int
            $wpdb->query("
                ALTER TABLE `{$table_name}` 
                MODIFY COLUMN `blocked_at` int unsigned NOT NULL
            ");
        }
        
        // Migrate blocked_until from datetime to int if needed
        if (isset($column_map['blocked_until']) && strpos($column_map['blocked_until']['Type'], 'datetime') !== false) {
            // Convert existing datetime values to Unix timestamps (0 for NULL or past dates = permanent)
            $wpdb->query("
                UPDATE `{$table_name}` 
                SET blocked_until = CASE 
                    WHEN blocked_until IS NULL OR blocked_until < NOW() THEN 0
                    ELSE UNIX_TIMESTAMP(blocked_until)
                END
            ");
            
            // Then change column type to int
            $wpdb->query("
                ALTER TABLE `{$table_name}` 
                MODIFY COLUMN `blocked_until` int unsigned NOT NULL DEFAULT '0'
            ");
        }
        
        // Check if is_active column exists
        $has_is_active = isset($column_map['is_active']);
        
        // Add is_active column if missing
        if (!$has_is_active) {
            $wpdb->query(
                "ALTER TABLE `{$table_name}` 
                ADD COLUMN `is_active` tinyint(1) NOT NULL DEFAULT '1' AFTER `last_attempt`,
                ADD INDEX `is_active` (`is_active`)"
            );
        }
        
        // Check if source column exists
        $has_source = isset($column_map['source']);
        
        // Add source column if missing
        if (!$has_source) {
            $wpdb->query(
                "ALTER TABLE `{$table_name}` 
                ADD COLUMN `source` varchar(10) NOT NULL DEFAULT 'local' AFTER `is_active`,
                ADD INDEX `source` (`source`)"
            );
        }
        
        // Check if rule_id column exists
        $has_rule_id = isset($column_map['rule_id']);
        
        // Add rule_id column if missing
        if (!$has_rule_id) {
            $wpdb->query(
                "ALTER TABLE `{$table_name}` 
                ADD COLUMN `rule_id` varchar(50) DEFAULT NULL AFTER `source`"
            );
        }
    }

    /**
     * Check if a specific table exists
     *
     * @since 2.0.0
     * @param string $table_constant Table constant name (e.g., VMPFence_Constants::TABLE_BLOCKED_IPS)
     * @return bool True if table exists, false otherwise
     */
    public static function table_exists($table_constant) {
        global $wpdb;
        $table_name = VMPFence_Constants::get_table_name($table_constant);
        $result = $wpdb->get_var("SHOW TABLES LIKE '{$table_name}'");
        return $result === $table_name;
    }

    /**
     * Verify all core tables exist and create missing ones
     *
     * @since 2.0.0
     * @return array List of tables that were created
     */
    public static function verify_and_create_tables() {
        global $wpdb;
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        $charset_collate = $wpdb->get_charset_collate();
        
        $created_tables = array();
        
        // Define core tables with their creation methods
        $core_tables = array(
            VMPFence_Constants::TABLE_CONFIG => 'create_config_table',
            VMPFence_Constants::TABLE_LS_SETTINGS => 'create_ls_settings_table',
            VMPFence_Constants::TABLE_HITS => 'create_hits_table',
            VMPFence_Constants::TABLE_LOGINS => 'create_logins_table',
            VMPFence_Constants::TABLE_LOCKED_OUT => 'create_locked_out_table',
            VMPFence_Constants::TABLE_BLOCKS => 'create_blocks_table',
            VMPFence_Constants::TABLE_BLOCKED_IPS => 'create_blocked_ips_table',
            VMPFence_Constants::TABLE_SECURITY_ATTEMPTS => 'create_security_attempts_table',
            VMPFence_Constants::TABLE_WAF_LOG => 'create_waf_log_table',
            VMPFence_Constants::TABLE_EVENTS => 'create_events_table',
            VMPFence_Constants::TABLE_AUDIT_LOG => 'create_audit_log_table',
            VMPFence_Constants::TABLE_SCAN_RESULTS => 'create_scan_results_table',
            VMPFence_Constants::TABLE_SCAN_ISSUES => 'create_scan_issues_table',
            VMPFence_Constants::TABLE_FILE_MODS => 'create_file_mods_table',
            VMPFence_Constants::TABLE_KNOWN_FILES => 'create_known_files_table',
            VMPFence_Constants::TABLE_FILE_CHANGES => 'create_file_changes_table',
            VMPFence_Constants::TABLE_LIVE_TRAFFIC_HUMAN => 'create_live_traffic_human_table',
            VMPFence_Constants::TABLE_CRAWLERS => 'create_crawlers_table',
            VMPFence_Constants::TABLE_REVERSE_CACHE => 'create_reverse_cache_table',
            VMPFence_Constants::TABLE_STATUS => 'create_status_table',
            VMPFence_Constants::TABLE_TRAFFIC_RATES => 'create_traffic_rates_table',
            VMPFence_Constants::TABLE_2FA => 'create_2fa_table',
            VMPFence_Constants::TABLE_LS_2FA_SECRETS => 'create_ls_2fa_secrets_table',
            VMPFence_Constants::TABLE_LS_ROLE_COUNTS => 'create_ls_role_counts_table',
            VMPFence_Constants::TABLE_NOTIFICATIONS => 'create_notifications_table',
            VMPFence_Constants::TABLE_PENDING_ISSUES => 'create_pending_issues_table',
            // VMPFence_Constants::TABLE_CENTRAL => 'create_central_table',
            VMPFence_Constants::TABLE_SNIP_CACHE => 'create_snip_cache_table',
            VMPFence_Constants::TABLE_WHOIS => 'create_whois_table',
            VMPFence_Constants::TABLE_WAF_FAILURES => 'create_waf_failures_table',
        );
        
        // Check each table and create if missing
        foreach ($core_tables as $table_constant => $creation_method) {
            if (!self::table_exists($table_constant)) {
                if (method_exists(__CLASS__, $creation_method)) {
                    self::$creation_method($charset_collate);
                    $created_tables[] = VMPFence_Constants::get_table_name($table_constant);
                }
            }
        }
        
        // Check WAF rules table (doesn't use constant)
        $waf_rules_table = $wpdb->prefix . 'vmpfence_waf_rules';
        if ($wpdb->get_var("SHOW TABLES LIKE '{$waf_rules_table}'") !== $waf_rules_table) {
            self::create_waf_rules_table($charset_collate);
            $created_tables[] = $waf_rules_table;
        }
        
        // Check download tokens table
        $download_tokens_table = $wpdb->prefix . 'vmpfence_download_tokens';
        if ($wpdb->get_var("SHOW TABLES LIKE '{$download_tokens_table}'") !== $download_tokens_table) {
            self::create_download_tokens_table();
            $created_tables[] = $download_tokens_table;
        }
        
        // Check blocked countries table
        $blocked_countries_table = $wpdb->prefix . 'vmpfence_blocked_countries';
        if ($wpdb->get_var("SHOW TABLES LIKE '{$blocked_countries_table}'") !== $blocked_countries_table) {
            self::create_blocked_countries_table($charset_collate);
            $created_tables[] = $blocked_countries_table;
        }
        
        // Check custom patterns table
        $custom_patterns_table = $wpdb->prefix . 'vmpfence_custom_patterns';
        if ($wpdb->get_var("SHOW TABLES LIKE '{$custom_patterns_table}'") !== $custom_patterns_table) {
            self::create_custom_patterns_table($charset_collate);
            $created_tables[] = $custom_patterns_table;
        }
        
        // Run blocked IPs migration to ensure all columns exist
        self::migrate_blocked_ips_table();
        
        return $created_tables;
    }

    /**
     * Ensure all database tables exist (called on admin_init)
     *
     * @since 2.0.0
     * @return void
     */
    public static function ensure_all_tables_exist() {
        $created_tables = self::verify_and_create_tables();
        
        // Log if any tables were created
        if (!empty($created_tables) && class_exists('VMPFence_Secure_Logger')) {
            VMPFence_Secure_Logger::log('info', 'Database tables auto-created', array(
                'tables' => $created_tables,
                'count' => count($created_tables)
            ));
        }
    }

    /**
     * Create custom patterns table
     *
     * @since 2.2.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_custom_patterns_table($charset_collate) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'vmpfence_custom_patterns';

        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            pattern_type VARCHAR(20) NOT NULL COMMENT 'Type: hostname, user_agent, referrer, ip_range',
            pattern_value TEXT NOT NULL COMMENT 'The actual pattern (wildcard or regex)',
            is_regex TINYINT(1) DEFAULT 0 COMMENT '0=wildcard, 1=regex',
            reason VARCHAR(255) DEFAULT NULL COMMENT 'Admin explanation for block',
            created_at INT UNSIGNED NOT NULL COMMENT 'Unix timestamp',
            created_by INT UNSIGNED NOT NULL COMMENT 'WP User ID',
            is_active TINYINT(1) DEFAULT 1 COMMENT '1=active, 0=disabled',
            source VARCHAR(10) DEFAULT 'local' COMMENT 'local or api',
            last_matched INT UNSIGNED DEFAULT NULL COMMENT 'Last match timestamp',
            match_count INT UNSIGNED DEFAULT 0 COMMENT 'Total number of blocks',
            group_id VARCHAR(50) DEFAULT NULL COMMENT 'Links patterns created together',
            INDEX idx_pattern_type (pattern_type),
            INDEX idx_active (is_active),
            INDEX idx_source (source),
            INDEX idx_created_at (created_at),
            INDEX idx_group_id (group_id)
        ) ENGINE=InnoDB $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        // Log table creation
        if (class_exists('VMPFence_Secure_Logger')) {
            VMPFence_Secure_Logger::info('Custom patterns table created');
        }
    }

    /**
     * Create blocked countries table
     *
     * @since 2.2.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_blocked_countries_table($charset_collate) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'vmpfence_blocked_countries';

        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            country_code VARCHAR(2) NOT NULL COMMENT 'ISO 3166-1 alpha-2',
            country_name VARCHAR(100) NOT NULL,
            block_login TINYINT(1) DEFAULT 1 COMMENT 'Block wp-login.php',
            block_site TINYINT(1) DEFAULT 0 COMMENT 'Block entire site',
            reason TEXT,
            blocked_at INT(10) UNSIGNED NOT NULL,
            blocked_until INT(10) DEFAULT 0 COMMENT '0=permanent',
            is_active TINYINT(1) DEFAULT 1,
            source VARCHAR(20) DEFAULT 'local',
            block_count INT DEFAULT 0,
            last_attempt DATETIME,
            UNIQUE KEY country_code (country_code),
            KEY is_active (is_active),
            KEY block_login (block_login),
            KEY block_site (block_site),
            KEY blocked_until (blocked_until)
        ) ENGINE=InnoDB $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        // Log table creation
        if (class_exists('VMPFence_Secure_Logger')) {
            VMPFence_Secure_Logger::info('Country blocking table created or verified');
        }
    }

    /**
     * Create country block logs table
     *
     * @since 2.2.0
     * @param string $charset_collate Database charset collation
     * @return void
     */
    private static function create_country_block_logs_table($charset_collate) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'vmpfence_country_block_logs';

        $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            ip VARCHAR(45) NOT NULL,
            country_code CHAR(2) NOT NULL,
            country_name VARCHAR(100) DEFAULT NULL,
            block_type VARCHAR(10) NOT NULL COMMENT 'site or login',
            request_uri VARCHAR(500) DEFAULT NULL,
            user_agent VARCHAR(500) DEFAULT NULL,
            blocked_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            KEY ip (ip),
            KEY country_code (country_code),
            KEY blocked_at (blocked_at),
            KEY block_type (block_type),
            KEY country_date (country_code, blocked_at)
        ) {$charset_collate};";

        dbDelta($sql);
    }
}
