<?php
/**
 * VmpFence Database Upgrade for SIEM Sync
 *
 * Adds `synced` and `synced_at` columns to 6 existing tables so the
 * SIEM sync modules can track which rows have been sent to the backend.
 * Also creates 4 new tables for data types not yet tracked locally.
 *
 * All ALTER operations are idempotent (safe to re-run).
 *
 * @package    VMPFence
 * @subpackage Includes
 * @since      2.2.4
 */

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

class VmpFence_DB_Upgrade {

    /**
     * Target SIEM database version
     * @var string
     */
    const DB_VERSION = '2.3.0';

    /**
     * WordPress option key for SIEM DB version tracking
     * @var string
     */
    const VERSION_OPTION = 'vmpfence_siem_db_version';

    /**
     * Run the upgrade if needed.
     * Checks the stored SIEM DB version and only runs migrations once.
     *
     * @since 2.2.4
     * @return void
     */
    public static function upgrade() {
        $current = get_option(self::VERSION_OPTION, '0');

        if (version_compare($current, self::DB_VERSION, '>=')) {
            // Sanity check: verify columns actually exist even if version looks current.
            // Protects against stale option left behind by incomplete uninstall (ISSUE-002).
            if (!self::verify_migration_state()) {
                // Stale version option — columns missing, force re-run
                delete_option(self::VERSION_OPTION);
                $current = '0';
            } else {
                return;
            }
        }

        self::add_synced_columns();
        self::create_new_siem_tables();

        update_option(self::VERSION_OPTION, self::DB_VERSION);

        if (class_exists('VMPFence_Secure_Logger')) {
            VMPFence_Secure_Logger::log('info', 'SIEM database upgrade completed to v' . self::DB_VERSION);
        }
    }

    /**
     * Verify that SIEM migration actually applied (columns exist on at least one table).
     *
     * @since 2.2.4
     * @return bool True if migration state is valid, false if columns are missing.
     */
    private static function verify_migration_state() {
        global $wpdb;
        $table = $wpdb->prefix . 'vmpfence_audit_log';

        // Check if the table exists first
        $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table));
        if (!$table_exists) {
            return false;
        }

        // Check if synced column exists
        $col = $wpdb->get_results("SHOW COLUMNS FROM `{$table}` LIKE 'synced'");
        return !empty($col);
    }

    /**
     * Add `synced` and `synced_at` columns to 6 existing tables.
     *
     * Tables: audit_log, file_changes, waf_failures, country_block_logs, crawlers, custom_patterns
     *
     * @since 2.2.4
     * @return void
     */
    private static function add_synced_columns() {
        global $wpdb;

        $tables = array(
            'vmpfence_audit_log',
            'vmpfence_file_changes',
            'vmpfence_waf_failures',
            'vmpfence_country_block_logs',
            'vmpfence_crawlers',
            'vmpfence_custom_patterns',
        );

        foreach ($tables as $suffix) {
            $table = $wpdb->prefix . $suffix;

            // Check if table exists
            $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table));
            if (!$table_exists) {
                if (class_exists('VMPFence_Secure_Logger')) {
                    VMPFence_Secure_Logger::log('warning', "SIEM upgrade: Table {$table} does not exist, skipping");
                }
                continue;
            }

            // Check if column already exists (idempotent)
            $col = $wpdb->get_results("SHOW COLUMNS FROM `{$table}` LIKE 'synced'");
            if (!empty($col)) {
                continue; // Already migrated
            }

            // Add synced column
            $wpdb->query("ALTER TABLE `{$table}` ADD COLUMN `synced` tinyint(1) NOT NULL DEFAULT 0");

            // Add synced_at column
            $wpdb->query("ALTER TABLE `{$table}` ADD COLUMN `synced_at` datetime DEFAULT NULL");

            // Add index on synced for fast lookups
            $wpdb->query("ALTER TABLE `{$table}` ADD INDEX `idx_synced` (`synced`)");

            if (class_exists('VMPFence_Secure_Logger')) {
                VMPFence_Secure_Logger::log('info', "SIEM upgrade: Added synced columns to {$table}");
            }
        }
    }

    /**
     * Create 4 new tables for data types not currently tracked locally.
     * These are OPTIONAL for DB v2.3.0 MVP — the 6 existing tables already have data to sync.
     *
     * @since 2.2.4
     * @return void
     */
    private static function create_new_siem_tables() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        // Table 1: Lockout State (brute-force protection tracking)
        $lockout_table = $wpdb->prefix . 'vmpfence_lockout_state';
        if (!$wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $lockout_table))) {
            $wpdb->query("CREATE TABLE `{$lockout_table}` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `ip_address` varchar(45) NOT NULL,
                `lockout_type` varchar(50) NOT NULL DEFAULT 'login',
                `fail_count` int(11) NOT NULL DEFAULT 0,
                `lockout_start` datetime DEFAULT NULL,
                `lockout_end` datetime DEFAULT NULL,
                `reason` text,
                `synced` tinyint(1) NOT NULL DEFAULT 0,
                `synced_at` datetime DEFAULT NULL,
                `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                INDEX `idx_ip_address` (`ip_address`),
                INDEX `idx_synced` (`synced`)
            ) {$charset_collate}");
        }

        // Table 2: Temporary Blocks (rate-limit temporary blocks)
        $temp_blocks_table = $wpdb->prefix . 'vmpfence_temporary_blocks';
        if (!$wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $temp_blocks_table))) {
            $wpdb->query("CREATE TABLE `{$temp_blocks_table}` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `ip_address` varchar(45) NOT NULL,
                `block_type` varchar(50) NOT NULL DEFAULT 'rate_limit',
                `reason` text,
                `blocked_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `expires_at` datetime DEFAULT NULL,
                `synced` tinyint(1) NOT NULL DEFAULT 0,
                `synced_at` datetime DEFAULT NULL,
                PRIMARY KEY (`id`),
                INDEX `idx_ip_address` (`ip_address`),
                INDEX `idx_synced` (`synced`)
            ) {$charset_collate}");
        }

        // Table 3: WHOIS Enhanced (WHOIS data for IPs)
        $whois_table = $wpdb->prefix . 'vmpfence_whois_enhanced';
        if (!$wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $whois_table))) {
            $wpdb->query("CREATE TABLE `{$whois_table}` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `ip_address` varchar(45) NOT NULL,
                `query` varchar(255) NOT NULL DEFAULT '',
                `body` longtext,
                `lookup_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `synced` tinyint(1) NOT NULL DEFAULT 0,
                `synced_at` datetime DEFAULT NULL,
                PRIMARY KEY (`id`),
                INDEX `idx_ip_address` (`ip_address`),
                INDEX `idx_synced` (`synced`)
            ) {$charset_collate}");
        }

        // Table 4: File Modifications (detailed file analysis records)
        $file_mods_table = $wpdb->prefix . 'vmpfence_file_modifications';
        if (!$wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $file_mods_table))) {
            $wpdb->query("CREATE TABLE `{$file_mods_table}` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `file_path` varchar(500) NOT NULL,
                `modification_type` varchar(50) NOT NULL DEFAULT 'modified',
                `old_hash` varchar(64) DEFAULT NULL,
                `new_hash` varchar(64) DEFAULT NULL,
                `old_size` bigint(20) DEFAULT NULL,
                `new_size` bigint(20) DEFAULT NULL,
                `detected_by` varchar(50) NOT NULL DEFAULT 'scanner',
                `severity` varchar(20) NOT NULL DEFAULT 'info',
                `detected_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `synced` tinyint(1) NOT NULL DEFAULT 0,
                `synced_at` datetime DEFAULT NULL,
                PRIMARY KEY (`id`),
                INDEX `idx_file_path` (`file_path`(255)),
                INDEX `idx_synced` (`synced`)
            ) {$charset_collate}");
        }
    }

    /**
     * Check if the SIEM upgrade has been applied.
     *
     * @since 2.2.4
     * @return bool
     */
    public static function is_upgraded() {
        $current = get_option(self::VERSION_OPTION, '0');
        return version_compare($current, self::DB_VERSION, '>=');
    }

    /**
     * Get the current SIEM DB version.
     *
     * @since 2.2.4
     * @return string
     */
    public static function get_version() {
        return get_option(self::VERSION_OPTION, '0');
    }
}
