<?php
/**
 * Database Management Class
 *
 * Handles database schema creation, upgrades, and version management
 * for VMPFence plugin local storage.
 *
 * @package VMPFence
 * @since 2.1.0
 */

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

class VMPFence_Logging_Database {
    
    /**
     * Current database version
     */
    const DB_VERSION = '2.1.0';
    
    /**
     * Database version option name
     * Using separate option to avoid conflict with VMPFence_Database
     */
    const DB_VERSION_OPTION = 'vmpfence_logging_db_version';
    
    /**
     * Events table name
     *
     * @var string
     */
    private $events_table;
    
    /**
     * Analytics table name
     *
     * @var string
     */
    private $analytics_table;
    
    /**
     * Constructor
     */
    public function __construct() {
        global $wpdb;
        $this->events_table = $wpdb->prefix . 'vmpfence_api_logs';
        $this->analytics_table = $wpdb->prefix . 'vmpfence_analytics';
    }
    
    /**
     * Install database tables
     *
     * Called on plugin activation
     */
    public function install() {
        $current_version = get_option(self::DB_VERSION_OPTION, '0');
        
        if (version_compare($current_version, self::DB_VERSION, '<')) {
            $this->create_tables();
            update_option(self::DB_VERSION_OPTION, self::DB_VERSION);
        }
    }
    
    /**
     * Create database tables
     * 
     * Creates BOTH events and analytics tables for the logging system
     */
    private function create_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        // API Logs table SQL - NEW schema for API logging service
        // Separate from wp_vmpfence_events (standalone events with OLD schema)
        $events_sql = "CREATE TABLE IF NOT EXISTS {$this->events_table} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            event_type varchar(100) NOT NULL,
            severity varchar(20) NOT NULL,
            message text NOT NULL,
            ip_address varchar(45) DEFAULT NULL,
            user_agent text DEFAULT NULL,
            request_uri text DEFAULT NULL,
            metadata longtext DEFAULT NULL,
            synced tinyint(1) NOT NULL DEFAULT 0,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY (id),
            KEY idx_event_type (event_type),
            KEY idx_severity (severity),
            KEY idx_synced (synced),
            KEY idx_created_at (created_at),
            KEY idx_ip_address (ip_address)
        ) $charset_collate;";
        
        // Analytics table SQL
        // Schema matches what class-logging-service.php expects
        $analytics_sql = "CREATE TABLE IF NOT EXISTS {$this->analytics_table} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            date date NOT NULL,
            total_events int(11) NOT NULL DEFAULT 0,
            critical_events int(11) NOT NULL DEFAULT 0,
            high_events int(11) NOT NULL DEFAULT 0,
            medium_events int(11) NOT NULL DEFAULT 0,
            low_events int(11) NOT NULL DEFAULT 0,
            info_events int(11) NOT NULL DEFAULT 0,
            firewall_blocks int(11) NOT NULL DEFAULT 0,
            malware_detections int(11) NOT NULL DEFAULT 0,
            unique_ips int(11) NOT NULL DEFAULT 0,
            unique_attackers int(11) NOT NULL DEFAULT 0,
            event_types longtext DEFAULT NULL,
            top_ips longtext DEFAULT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY idx_date (date),
            KEY idx_created_at (created_at)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($events_sql);
        dbDelta($analytics_sql);
    }
    
    /**
     * Upgrade database schema
     *
     * Called when plugin version changes
     */
    public function upgrade() {
        $current_version = get_option(self::DB_VERSION_OPTION, '0');
        
        if (version_compare($current_version, self::DB_VERSION, '<')) {
            // Future upgrade logic here
            $this->create_tables(); // Re-run to add any new columns
            update_option(self::DB_VERSION_OPTION, self::DB_VERSION);
        }
    }
    
    /**
     * Drop all tables (called on uninstall)
     */
    public function drop_tables() {
        global $wpdb;
        
        $wpdb->query("DROP TABLE IF EXISTS {$this->events_table}");
        $wpdb->query("DROP TABLE IF EXISTS {$this->analytics_table}");
        
        delete_option(self::DB_VERSION_OPTION);
    }
    
    /**
     * Get events table name
     *
     * @return string
     */
    public function get_events_table() {
        return $this->events_table;
    }
    
    /**
     * Get analytics table name
     *
     * @return string
     */
    public function get_analytics_table() {
        return $this->analytics_table;
    }
    
    /**
     * Check if both API logs and analytics tables exist
     *
     * @return bool True if both tables exist, false otherwise
     */
    public static function tables_exist() {
        global $wpdb;
        
        $events_table = $wpdb->prefix . 'vmpfence_api_logs';
        $analytics_table = $wpdb->prefix . 'vmpfence_analytics';
        
        $events_exists = $wpdb->get_var("SHOW TABLES LIKE '{$events_table}'") === $events_table;
        $analytics_exists = $wpdb->get_var("SHOW TABLES LIKE '{$analytics_table}'") === $analytics_table;
        
        return $events_exists && $analytics_exists;
    }
    
    /**
     * Ensure tables exist (for admin_init auto-creation)
     * 
     * Forces table creation by temporarily resetting version if tables are missing
     *
     * @return void
     */
    public static function ensure_tables_exist() {
        // Check if tables actually exist
        if (!self::tables_exist()) {
            // Temporarily reset version to force table creation
            $current_version = get_option(self::DB_VERSION_OPTION, '0');
            delete_option(self::DB_VERSION_OPTION);
            
            // Create tables
            $db = new self();
            $db->install();
            
            // Restore version
            if ($current_version !== '0') {
                update_option(self::DB_VERSION_OPTION, $current_version);
            }
        }
    }
    
    /**
     * Migrate legacy data from wp_options
     *
     * Migrates old event queue data to new tables
     */
    public function migrate_legacy_data() {
        $legacy_queue = get_option('vmpfence_event_queue', array());
        
        if (!empty($legacy_queue) && is_array($legacy_queue)) {
            global $wpdb;
            
            $migrated = 0;
            foreach ($legacy_queue as $event) {
                if (!isset($event['event_type']) || !isset($event['severity'])) {
                    continue;
                }
                
                $wpdb->insert(
                    $this->events_table,
                    array(
                        'site_id' => get_option('vmpfence_api_key', ''),
                        'event_type' => $event['event_type'],
                        'severity' => $event['severity'],
                        'message' => isset($event['message']) ? $event['message'] : '',
                        'ip_address' => isset($event['ip_address']) ? $event['ip_address'] : null,
                        'user_agent' => isset($event['user_agent']) ? $event['user_agent'] : null,
                        'request_uri' => isset($event['request_uri']) ? $event['request_uri'] : null,
                        'metadata' => isset($event['metadata']) ? wp_json_encode($event['metadata']) : null,
                        'created_at' => isset($event['timestamp']) ? $event['timestamp'] : current_time('mysql'),
                        'synced_to_server' => 0
                    ),
                    array('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d')
                );
                
                if ($wpdb->insert_id) {
                    $migrated++;
                }
            }
            
            // Clear legacy queue after successful migration
            if ($migrated > 0) {
                delete_option('vmpfence_event_queue');
            }
            
            return $migrated;
        }
        
        return 0;
    }
    
    /**
     * Get database statistics
     *
     * @return array
     */
    public function get_stats() {
        global $wpdb;
        
        $stats = array(
            'total_events' => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->events_table}"),
            'unsynced_events' => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->events_table} WHERE synced = 0"),
            'total_analytics_days' => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->analytics_table}"),
            'database_size' => $this->get_table_sizes()
        );
        
        return $stats;
    }
    
    /**
     * Get table sizes in MB
     *
     * @return array
     */
    private function get_table_sizes() {
        global $wpdb;
        
        $sizes = array();
        
        $result = $wpdb->get_results("
            SELECT table_name, 
                   ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
            FROM information_schema.TABLES 
            WHERE table_schema = DATABASE()
            AND table_name IN ('{$this->events_table}', '{$this->analytics_table}')
        ");
        
        foreach ($result as $row) {
            $table_short_name = str_replace($wpdb->prefix, '', $row->table_name);
            $sizes[$table_short_name] = $row->size_mb;
        }
        
        return $sizes;
    }
    
    /**
     * Clean old events
     *
     * @param int $days Number of days to keep (0 = keep all)
     * @return int Number of deleted records
     */
    public function clean_old_events($days = 0) {
        if ($days <= 0) {
            return 0;
        }
        
        global $wpdb;
        
        $date_threshold = date('Y-m-d H:i:s', strtotime("-{$days} days"));
        
        $deleted = $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$this->events_table} WHERE created_at < %s AND synced_to_server = 1",
                $date_threshold
            )
        );
        
        return (int) $deleted;
    }
    
    /**
     * Delete all events
     *
     * @param bool $synced_only Only delete synced events
     * @return int Number of deleted records
     */
    public function delete_all_events($synced_only = false) {
        global $wpdb;
        
        if ($synced_only) {
            $deleted = $wpdb->query("DELETE FROM {$this->events_table} WHERE synced_to_server = 1");
        } else {
            $deleted = $wpdb->query("TRUNCATE TABLE {$this->events_table}");
        }
        
        return (int) $deleted;
    }
    
    /**
     * Clear analytics cache
     *
     * @return int Number of deleted records
     */
    public function clear_analytics_cache() {
        global $wpdb;
        
        $deleted = $wpdb->query("TRUNCATE TABLE {$this->analytics_table}");
        
        return (int) $deleted;
    }
}
