-- =====================================================
-- Base de datos para el Sistema de Migración cPanel
-- @author MiniMax Agent
-- =====================================================

CREATE DATABASE IF NOT EXISTS cpanel_migration CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE cpanel_migration;

-- Tabla principal de migraciones
CREATE TABLE IF NOT EXISTS migrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    migration_id VARCHAR(50) NOT NULL UNIQUE,
    source_host TEXT NOT NULL,              -- Encriptado
    dest_host TEXT NOT NULL,                -- Encriptado
    source_user VARCHAR(100) NOT NULL,
    status ENUM('pending', 'in_progress', 'completed', 'failed', 'partial') DEFAULT 'pending',
    details JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at DATETIME NOT NULL,
    updated_at DATETIME,
    completed_at DATETIME,
    INDEX idx_migration_id (migration_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de pasos de migración (logs detallados)
CREATE TABLE IF NOT EXISTS migration_steps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    migration_id VARCHAR(50) NOT NULL,
    step_name VARCHAR(50) NOT NULL,         -- files, databases, emails, etc.
    status ENUM('pending', 'in_progress', 'completed', 'failed', 'partial') DEFAULT 'pending',
    message TEXT,
    details JSON,
    created_at DATETIME NOT NULL,
    INDEX idx_migration_id (migration_id),
    INDEX idx_step_name (step_name),
    FOREIGN KEY (migration_id) REFERENCES migrations(migration_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de intentos de conexión (seguridad)
CREATE TABLE IF NOT EXISTS connection_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,
    action VARCHAR(50) NOT NULL,
    success BOOLEAN DEFAULT FALSE,
    details TEXT,
    created_at DATETIME NOT NULL,
    INDEX idx_ip_address (ip_address),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de sesiones activas
CREATE TABLE IF NOT EXISTS active_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(128) NOT NULL UNIQUE,
    migration_id VARCHAR(50),
    ip_address VARCHAR(45),
    created_at DATETIME NOT NULL,
    last_activity DATETIME NOT NULL,
    INDEX idx_session_id (session_id),
    INDEX idx_last_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Limpiar sesiones antiguas automáticamente (evento programado)
DELIMITER //
CREATE EVENT IF NOT EXISTS cleanup_old_sessions
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    DELETE FROM active_sessions WHERE last_activity < DATE_SUB(NOW(), INTERVAL 24 HOUR);
    DELETE FROM connection_attempts WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
END//
DELIMITER ;

-- Habilitar el event scheduler
SET GLOBAL event_scheduler = ON;