<?php
namespace WPUltimateSecurity\UltimateSecurity\Infrastructure\Persistence\Schema;

use WPUltimateSecurity\UltimateSecurity\Support\Helpers\DateHelper;

/**
 * Database Schema Manager
 *
 * Centralizes all custom table schema definitions to ensure uniform baseline
 * across all tables in both free and pro versions.
 *
 * ============================================================================
 * HOW TO ADD A NEW TABLE
 * ============================================================================
 *
 * 1. Create a private method for the table schema:
 *    private static function get_my_feature_table_schema($charset_collate) { ... }
 *
 * 2. Register it in get_table_definitions():
 *    $tables = [
 *        'requestslog' => self::get_requestslog_table_schema($charset_collate),
 *        'my_feature' => self::get_my_feature_table_schema($charset_collate), // NEW
 *    ];
 *
 * 3. Bump ULTIMATE_SECURITY_DB_VERSION in ultimate-security.php
 *
 * That's it! The table will be automatically created on plugin update.
 * No migration files needed - dbDelta() handles table creation/modification.
 *
 * ============================================================================
 * BASELINE SCHEMA REQUIREMENTS
 * ============================================================================
 *
 * All tables MUST include these columns:
 * - id: bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
 * - created_at: datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
 * - updated_at: datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 * - ENGINE=InnoDB with charset collate
 *
 * This ensures:
 * - Consistent cleanup tooling (relies on created_at)
 * - Audit trail (track when records are created/modified)
 * - Same structure for all tables regardless of version
 *
 * ============================================================================
 * TABLE NAMING CONVENTION
 * ============================================================================
 *
 * Tables are prefixed automatically: {wp_prefix}ultimate_security_{table_key}
 * Example: 'activity_log' becomes 'wp_ultimate_security_activity_log'
 */
class DatabaseSchema
{
    /**
     * Get all table definitions for the plugin
     *
     * @return array Associative array of table_name => SQL definition
     */
    public static function get_table_definitions()
    {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        $tables = [
            // Removed: 'requestslog' - URL Guard removed (server logs do this better)

            // Dedicated tables for specific features
            'testmode_simulations' => self::get_testmode_simulations_table_schema($charset_collate),

            // Trusted devices for 2FA
            'trusted_devices' => self::get_trusted_devices_table_schema($charset_collate),

            // Simple audit logs for security events (free plugin)
            'audit_logs' => self::get_audit_logs_table_schema($charset_collate),

            // Pro/shared: queue for mirroring activity/security logs to external destinations
            // (Safe to create in free too; table will remain unused if feature is absent.)
            'log_mirror_queue' => self::get_log_mirror_queue_table_schema($charset_collate),

            // Vulnerability scan history
            'vulnerability_scan_history' => self::get_vulnerability_scan_history_table_schema($charset_collate),
        ];

        return $tables;
    }


    // Removed: get_requestslog_table_schema() - URL Guard removed (server logs do this better)

    /**
     * Drop removed tables
     *
     * Called during plugin updates to clean up tables from removed features.
     */
    public static function drop_removed_tables()
    {
        global $wpdb;

        // Drop URL Guard table - feature removed
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange
        $wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}ultimate_security_url_guard_requestslog");
    }

    /**
     * Get testmode_simulations table schema with optimized indexing
     *
     * Stores test mode simulation logs (replaces histories table for test_mode_simulation events)
     *
     * @param string $charset_collate WordPress charset collation
     * @return string SQL CREATE TABLE statement
     */
    private static function get_testmode_simulations_table_schema($charset_collate)
    {
        global $wpdb;
        $table_name = $wpdb->prefix . 'ultimate_security_testmode_simulations';
        return "CREATE TABLE {$table_name} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        module varchar(50) NOT NULL,
        action varchar(100) NOT NULL,
        user_id bigint(20) unsigned DEFAULT NULL,
        user_role varchar(50) DEFAULT NULL,
        ip_address varchar(45) DEFAULT NULL,
        request_uri varchar(500) DEFAULT NULL,
        request_method varchar(10) DEFAULT NULL,
        reason text DEFAULT NULL,
        would_have_blocked tinyint(1) DEFAULT 0,
        created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_module (module),
        KEY idx_action (action),
        KEY idx_user_id (user_id),
        KEY idx_ip_address (ip_address),
        KEY idx_created_at (created_at),
        KEY idx_module_created (module, created_at),
        KEY idx_user_created (user_id, created_at),
        KEY idx_would_have_blocked (would_have_blocked),
        KEY idx_request_method (request_method)
    ) ENGINE=InnoDB {$charset_collate};";
    }

    /**
     * Get trusted_devices table schema
     *
     * Stores trusted devices for 2FA bypass
     *
     * @param string $charset_collate WordPress charset collation
     * @return string SQL CREATE TABLE statement
     */
    private static function get_trusted_devices_table_schema($charset_collate)
    {
        global $wpdb;
        $table_name = $wpdb->prefix . 'ultimate_security_trusted_devices';
        return "CREATE TABLE {$table_name} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        user_id bigint(20) unsigned NOT NULL,
        device_fingerprint varchar(64) NOT NULL,
        device_token varchar(64) NOT NULL,
        device_name varchar(255) DEFAULT NULL,
        browser varchar(100) DEFAULT NULL,
        browser_version varchar(50) DEFAULT NULL,
        os varchar(100) DEFAULT NULL,
        os_version varchar(50) DEFAULT NULL,
        device_type varchar(50) DEFAULT 'desktop',
        ip_address varchar(45) DEFAULT NULL,
        location varchar(255) DEFAULT NULL,
        last_used_at datetime DEFAULT NULL,
        expires_at datetime NOT NULL,
        is_revoked tinyint(1) DEFAULT 0,
        created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY device_token (device_token),
        KEY idx_user_id (user_id),
        KEY idx_device_fingerprint (device_fingerprint),
        KEY idx_user_fingerprint (user_id, device_fingerprint),
        KEY idx_expires_at (expires_at),
        KEY idx_is_revoked (is_revoked),
        KEY idx_created_at (created_at),
        KEY idx_last_used (last_used_at)
    ) ENGINE=InnoDB {$charset_collate};";
    }

    /**
     * Get audit_logs table schema
     *
     * Stores simple audit logs for security events in the free plugin
     *
     * @param string $charset_collate WordPress charset collation
     * @return string SQL CREATE TABLE statement
     */
    private static function get_audit_logs_table_schema($charset_collate)
    {
        global $wpdb;
        $table_name = $wpdb->prefix . 'ultimate_security_audit_logs';
        return "CREATE TABLE {$table_name} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        event_type varchar(50) NOT NULL,
        severity varchar(20) NOT NULL DEFAULT 'low',
        user_id bigint(20) unsigned DEFAULT NULL,
        username varchar(100) DEFAULT NULL,
        ip_address varchar(45) DEFAULT NULL,
        user_agent varchar(500) DEFAULT NULL,
        message text DEFAULT NULL,
        context longtext DEFAULT NULL,
        created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_event_type (event_type),
        KEY idx_severity (severity),
        KEY idx_user_id (user_id),
        KEY idx_ip_address (ip_address),
        KEY idx_created_at (created_at),
        KEY idx_event_severity (event_type, severity),
        KEY idx_user_created (user_id, created_at)
    ) ENGINE=InnoDB {$charset_collate};";
    }

    /**
     * Get log_mirror_queue table schema
     *
     * Stores queued log mirror events for asynchronous processing.
     *
     * Expected usage (from queue processor):
     * - status IN ('pending','retry')
     * - next_retry IS NULL OR next_retry <= NOW()
     * - ORDER BY created_at ASC LIMIT N
     *
     * @param string $charset_collate WordPress charset collation
     * @return string SQL CREATE TABLE statement
     */
    private static function get_log_mirror_queue_table_schema($charset_collate)
    {
        global $wpdb;
        $table_name = $wpdb->prefix . 'ultimate_security_log_mirror_queue';
        return "CREATE TABLE {$table_name} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        status varchar(20) NOT NULL DEFAULT 'pending',
        attempts int(11) NOT NULL DEFAULT 0,
        next_retry datetime DEFAULT NULL,
        payload longtext NOT NULL,
        last_error text DEFAULT NULL,
        created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_status (status),
        KEY idx_next_retry (next_retry),
        KEY idx_created_at (created_at),
        KEY idx_status_next_retry (status, next_retry),
        KEY idx_status_created_at (status, created_at)
    ) ENGINE=InnoDB {$charset_collate};";
    }

    /**
     * Get vulnerability scan history table schema
     *
     * Stores vulnerability scan history for tracking and comparison
     *
     * @param string $charset_collate WordPress charset collation
     * @return string SQL CREATE TABLE statement
     */
    private static function get_vulnerability_scan_history_table_schema($charset_collate)
    {
        global $wpdb;
        $table_name = $wpdb->prefix . 'ultimate_security_vulnerability_scan_history';

        return "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            scan_id varchar(50) NOT NULL,
            scan_type varchar(20) NOT NULL DEFAULT 'manual',
            api_used varchar(20) DEFAULT 'wpscan',
            total_vulnerabilities int NOT NULL DEFAULT 0,
            critical_count int NOT NULL DEFAULT 0,
            high_count int NOT NULL DEFAULT 0,
            medium_count int NOT NULL DEFAULT 0,
            low_count int NOT NULL DEFAULT 0,
            abandoned_plugins int NOT NULL DEFAULT 0,
            abandoned_themes int NOT NULL DEFAULT 0,
            outdated_count int NOT NULL DEFAULT 0,
            plugins_scanned int NOT NULL DEFAULT 0,
            themes_scanned int NOT NULL DEFAULT 0,
            wp_version varchar(20) DEFAULT NULL,
            results longtext DEFAULT NULL,
            triggered_by bigint(20) unsigned DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY scan_id (scan_id),
            KEY scan_type (scan_type),
            KEY api_used (api_used),
            KEY total_vulnerabilities (total_vulnerabilities),
            KEY triggered_by (triggered_by),
            KEY created_at (created_at)
        ) ENGINE=InnoDB {$charset_collate};";
    }


    /**
     * Get all table names (without prefix)
     *
     * @return array List of table name keys
     */
    public static function get_table_names()
    {
        return array_keys(self::get_table_definitions());
    }

    /**
     * Get full table name with WordPress prefix
     *
     * @param string $table_key Table key (e.g., 'histories', 'historymeta')
     * @return string Full table name with prefix
     */
    public static function get_full_table_name($table_key)
    {
        global $wpdb;
        return $wpdb->prefix . 'ultimate_security_' . $table_key;
    }

    /**
     * Get all full table names with WordPress prefix
     *
     * @return array List of full table names
     */
    public static function get_all_full_table_names()
    {
        $table_names = self::get_table_names();
        return array_map([self::class, 'get_full_table_name'], $table_names);
    }

    /**
     * Validate that a table has the required baseline columns
     *
     * @param string $table_name Full table name
     * @return bool True if table has all required baseline columns
     */
    public static function validate_table_baseline($table_name)
    {
        global $wpdb;

        $required_columns = ['id', 'created_at', 'updated_at'];

        foreach ($required_columns as $column) {
            $column_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = %s
                AND TABLE_NAME = %s
                AND COLUMN_NAME = %s",
                DB_NAME,
                $table_name,
                $column
            ));

            if (!$column_exists) {
                return false;
            }
        }

        return true;
    }

    /**
     * Create or update all tables
     *
     * @return void
     */
    public static function create_tables()
    {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $table_definitions = self::get_table_definitions();

        foreach ($table_definitions as $definition) {
            dbDelta($definition);
        }
    }

    /**
     * Drop all plugin tables (used during uninstall)
     *
     * @return void
     */
    public static function drop_all_tables()
    {
        global $wpdb;

        $tables = self::get_all_full_table_names();

        foreach ($tables as $table) {
            $wpdb->query("DROP TABLE IF EXISTS `$table`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        }
    }

    /**
     * Run database migrations to update existing tables to match baseline schema
     *
     * This ensures that all existing tables have the required baseline columns
     * even if they were created before the baseline was established.
     *
     * @return array Results of migrations
     */
    public static function run_migrations()
    {
        global $wpdb;
        $results = [];

        // Get all plugin tables
        $table_names = self::get_all_full_table_names();

        foreach ($table_names as $table) {
            // Check if table exists
            $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table));

            if (!$table_exists) {
                continue; // Skip if table doesn't exist
            }

            // Check and add missing updated_at column
            $updated_at_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = %s
                AND TABLE_NAME = %s
                AND COLUMN_NAME = 'updated_at'",
                DB_NAME,
                $table
            ));

            if (!$updated_at_exists) {
                // Add updated_at column
                $wpdb->query(
                    "ALTER TABLE `{$table}`
                    ADD COLUMN updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at,
                    ADD KEY updated_at (updated_at)"
                ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

                $results[] = "Added updated_at column to {$table}";
            }

            // Check and add created_at index if missing
            $index_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
                WHERE TABLE_SCHEMA = %s
                AND TABLE_NAME = %s
                AND INDEX_NAME = 'created_at'",
                DB_NAME,
                $table
            ));

            if (!$index_exists) {
                // Add created_at index
                $created_at_column_exists = $wpdb->get_var($wpdb->prepare(
                    "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_SCHEMA = %s
                    AND TABLE_NAME = %s
                    AND COLUMN_NAME = 'created_at'",
                    DB_NAME,
                    $table
                ));

                if ($created_at_column_exists) {
                    $wpdb->query("ALTER TABLE `{$table}` ADD KEY created_at (created_at)"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    $results[] = "Added created_at index to {$table}";
                }
            }
        }

        // Store migration timestamp
        update_option('ultimate_security_schema_migration_version', DateHelper::now());

        return $results;
    }
}
