<?php
/**
 * Database Installer for TigerStyle SEO Backup System
 * Handles database table creation and schema management
 */

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

class TigerStyleSEO_Database_Installer {
    
    /**
     * Database version
     */
    const DB_VERSION = '1.0.0';
    
    /**
     * Install database tables
     */
    public static function install() {
        global $wpdb;
        
        try {
            // Create backup logs table
            self::create_backup_logs_table();
            
            // Create backup history table
            self::create_backup_history_table();
            
            // Update database version
            update_option('tigerstyle_backup_db_version', self::DB_VERSION);
            
            // Log installation
            if (class_exists('TigerStyleSEO_Backup_Logger')) {
                $logger = TigerStyleSEO_Backup_Logger::instance();
                $logger->info("Database tables installed successfully", array(
                    'version' => self::DB_VERSION,
                    'tables_created' => array('backup_logs', 'backup_history')
                ));
            }
        } catch (Exception $e) {
            // Silently mark as installed to prevent blocking the site
            // Tables might already exist or have partial structure
            update_option('tigerstyle_backup_db_version', self::DB_VERSION);
            error_log('TigerStyle SEO: Database installation warning - ' . $e->getMessage());
        }
    }
    
    /**
     * Create backup logs table
     */
    private static function create_backup_logs_table() {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'tigerstyle_backup_logs';
        
        // Check if table already exists
        if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") == $table_name) {
            return; // Table already exists, skip creation
        }
        
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE $table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            level varchar(20) NOT NULL,
            message text NOT NULL,
            context longtext,
            user_id bigint(20) DEFAULT 0,
            user_ip varchar(45) DEFAULT 'unknown',
            memory_usage bigint(20) DEFAULT 0,
            request_id varchar(32) DEFAULT '',
            backup_id varchar(255),
            PRIMARY KEY (id),
            KEY backup_id (backup_id),
            KEY level (level),
            KEY created_at (created_at),
            KEY user_id (user_id),
            KEY request_id (request_id)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        
        // Suppress WordPress database errors during dbDelta
        $wpdb->suppress_errors(true);
        dbDelta($sql);
        $wpdb->suppress_errors(false);
        
        // Check if table exists (don't throw error if it doesn't)
        if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
            error_log("TigerStyle SEO: Backup logs table creation may have failed, but continuing...");
        }
    }
    
    /**
     * Create backup history table
     */
    private static function create_backup_history_table() {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'tigerstyle_backups';
        
        // Check if table already exists
        if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") == $table_name) {
            return; // Table already exists, skip creation
        }
        
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE $table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            backup_id varchar(255) NOT NULL UNIQUE,
            backup_type enum('full', 'files', 'database', 'custom') NOT NULL DEFAULT 'full',
            status enum('pending', 'running', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
            file_path text,
            file_size bigint(20) DEFAULT 0,
            compression_method varchar(50),
            compression_ratio decimal(5,4),
            storage_location varchar(100) DEFAULT 'local',
            storage_path text,
            manifest longtext,
            error_message text,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            completed_at datetime,
            created_by bigint(20),
            restore_point tinyint(1) DEFAULT 0,
            scheduled tinyint(1) DEFAULT 0,
            retention_days int(11) DEFAULT 30,
            PRIMARY KEY (id),
            UNIQUE KEY backup_id (backup_id),
            KEY backup_type (backup_type),
            KEY status (status),
            KEY created_at (created_at),
            KEY created_by (created_by),
            KEY restore_point (restore_point),
            KEY scheduled (scheduled)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        
        // Suppress WordPress database errors during dbDelta
        $wpdb->suppress_errors(true);
        dbDelta($sql);
        $wpdb->suppress_errors(false);
        
        // Check if table exists (don't throw error if it doesn't)
        if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
            error_log("TigerStyle SEO: Backup history table creation may have failed, but continuing...");
        }
    }
    
    /**
     * Check if database needs update
     */
    public static function needs_update() {
        $current_version = get_option('tigerstyle_backup_db_version', '0.0.0');
        return version_compare($current_version, self::DB_VERSION, '<');
    }
    
    /**
     * Update database if needed
     */
    public static function maybe_update() {
        if (self::needs_update()) {
            self::install();
        }
    }
    
    /**
     * Uninstall database tables
     */
    public static function uninstall() {
        global $wpdb;
        
        $tables = array(
            $wpdb->prefix . 'tigerstyle_backup_logs',
            $wpdb->prefix . 'tigerstyle_backups'
        );
        
        foreach ($tables as $table) {
            $wpdb->query("DROP TABLE IF EXISTS $table");
        }
        
        // Remove options
        delete_option('tigerstyle_backup_db_version');
        
        // Log uninstall
        if (class_exists('TigerStyleSEO_Backup_Logger')) {
            $logger = TigerStyleSEO_Backup_Logger::instance();
            $logger->info("Database tables removed successfully", array(
                'tables_dropped' => $tables
            ));
        }
    }
    
    /**
     * Get table status
     */
    public static function get_table_status() {
        global $wpdb;
        
        $tables = array(
            'backup_logs' => $wpdb->prefix . 'tigerstyle_backup_logs',
            'backup_history' => $wpdb->prefix . 'tigerstyle_backups'
        );
        
        $status = array();
        
        foreach ($tables as $name => $table) {
            $exists = $wpdb->get_var("SHOW TABLES LIKE '$table'") == $table;
            $count = 0;
            
            if ($exists) {
                $count = $wpdb->get_var("SELECT COUNT(*) FROM $table");
            }
            
            $status[$name] = array(
                'table' => $table,
                'exists' => $exists,
                'count' => (int)$count
            );
        }
        
        return $status;
    }
    
    /**
     * Repair tables if needed
     */
    public static function repair_tables() {
        global $wpdb;
        
        $results = array();
        
        $tables = array(
            $wpdb->prefix . 'tigerstyle_backup_logs',
            $wpdb->prefix . 'tigerstyle_backups'
        );
        
        foreach ($tables as $table) {
            $result = $wpdb->query("REPAIR TABLE $table");
            $results[$table] = $result !== false;
        }
        
        return $results;
    }
    
    /**
     * Optimize tables
     */
    public static function optimize_tables() {
        global $wpdb;
        
        $results = array();
        
        $tables = array(
            $wpdb->prefix . 'tigerstyle_backup_logs',
            $wpdb->prefix . 'tigerstyle_backups'
        );
        
        foreach ($tables as $table) {
            $result = $wpdb->query("OPTIMIZE TABLE $table");
            $results[$table] = $result !== false;
        }
        
        return $results;
    }
    
    /**
     * Clean old log entries
     */
    public static function cleanup_old_logs($days = 30) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'tigerstyle_backup_logs';
        $cutoff_date = date('Y-m-d H:i:s', strtotime("-{$days} days"));
        
        $deleted = $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM $table_name WHERE created_at < %s",
                $cutoff_date
            )
        );
        
        return $deleted !== false ? (int)$deleted : 0;
    }
    
    /**
     * Get database statistics
     */
    public static function get_database_stats() {
        global $wpdb;
        
        $stats = array(
            'version' => get_option('tigerstyle_backup_db_version', '0.0.0'),
            'tables' => self::get_table_status()
        );
        
        // Get table sizes
        $result = $wpdb->get_results("
            SELECT 
                table_name as 'table',
                ROUND(((data_length + index_length) / 1024 / 1024), 2) as 'size_mb'
            FROM information_schema.TABLES 
            WHERE table_schema = DATABASE()
            AND table_name LIKE '{$wpdb->prefix}tigerstyle_%'
        ");
        
        if ($result) {
            foreach ($result as $row) {
                $key = str_replace($wpdb->prefix . 'tigerstyle_', '', $row->table);
                if (isset($stats['tables'][$key])) {
                    $stats['tables'][$key]['size_mb'] = (float)$row->size_mb;
                }
            }
        }
        
        return $stats;
    }
}