-- Sistema Corporativo de Migração de E-mails IMAP
-- Schema MySQL 8.0+
-- Charset: utf8mb4_unicode_ci


-- Tabela: login_logs (Logs de autenticação)
CREATE TABLE IF NOT EXISTS login_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED DEFAULT NULL,
    username VARCHAR(50) DEFAULT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    status ENUM('success','failed','blocked','logout') NOT NULL,
    two_factor_used TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_ip_address (ip_address),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: projects (Projetos de migração)
CREATE TABLE IF NOT EXISTS projects (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    status ENUM('draft','active','paused','completed','archived','error') DEFAULT 'draft',
    source_provider VARCHAR(50) DEFAULT NULL,
    destination_provider VARCHAR(50) DEFAULT NULL,
    started_at TIMESTAMP NULL DEFAULT NULL,
    completed_at TIMESTAMP NULL DEFAULT NULL,
    created_by INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_status (status),
    INDEX idx_created_by (created_by),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: account_folders (Pastas das contas)
CREATE TABLE IF NOT EXISTS account_folders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id INT UNSIGNED NOT NULL,
    source_name VARCHAR(255) NOT NULL,
    destination_name VARCHAR(255) NOT NULL,
    folder_type ENUM('inbox','sent','drafts','trash','junk','archive','custom') DEFAULT 'custom',
    total_messages INT UNSIGNED DEFAULT 0,
    migrated_messages INT UNSIGNED DEFAULT 0,
    failed_messages INT UNSIGNED DEFAULT 0,
    skipped_messages INT UNSIGNED DEFAULT 0,
    total_size BIGINT UNSIGNED DEFAULT 0,
    status ENUM('pending','migrating','completed','error','skipped') DEFAULT 'pending',
    sync_date TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    INDEX idx_account_id (account_id),
    INDEX idx_status (status),
    INDEX idx_folder_type (folder_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: migration_jobs (Tarefas de migração)
CREATE TABLE IF NOT EXISTS migration_jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id INT UNSIGNED NOT NULL,
    folder_id INT UNSIGNED NOT NULL,
    message_uid VARCHAR(255) NOT NULL,
    message_id_hash VARCHAR(64) NOT NULL,
    sha256_hash VARCHAR(64) NOT NULL,
    status ENUM('pending','processing','completed','failed','skipped') DEFAULT 'pending',
    retry_count TINYINT UNSIGNED DEFAULT 0,
    error_message TEXT DEFAULT NULL,
    processed_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (folder_id) REFERENCES account_folders(id) ON DELETE CASCADE,
    UNIQUE KEY unique_message (account_id, folder_id, message_uid),
    INDEX idx_status (status),
    INDEX idx_message_id_hash (message_id_hash),
    INDEX idx_sha256_hash (sha256_hash),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: migration_batches (Lotes de processamento)
CREATE TABLE IF NOT EXISTS migration_batches (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id INT UNSIGNED NOT NULL,
    batch_number INT UNSIGNED NOT NULL,
    status ENUM('running','paused','completed','failed') DEFAULT 'running',
    config JSON,
    started_at TIMESTAMP NULL DEFAULT NULL,
    completed_at TIMESTAMP NULL DEFAULT NULL,
    total_jobs INT UNSIGNED DEFAULT 0,
    processed_jobs INT UNSIGNED DEFAULT 0,
    failed_jobs INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    INDEX idx_account_id (account_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: migration_logs (Logs operacionais)
CREATE TABLE IF NOT EXISTS migration_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED DEFAULT NULL,
    account_id INT UNSIGNED DEFAULT NULL,
    folder_id INT UNSIGNED DEFAULT NULL,
    job_id BIGINT UNSIGNED DEFAULT NULL,
    level ENUM('debug','info','warning','error','critical') DEFAULT 'info',
    category VARCHAR(50) DEFAULT 'general',
    message TEXT NOT NULL,
    context JSON DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_id (project_id),
    INDEX idx_account_id (account_id),
    INDEX idx_level (level),
    INDEX idx_category (category),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: activity_logs (Registro de atividades dos usuários)
CREATE TABLE IF NOT EXISTS activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED DEFAULT NULL,
    project_id INT UNSIGNED DEFAULT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) DEFAULT NULL,
    entity_id INT UNSIGNED DEFAULT NULL,
    description TEXT,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_project_id (project_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: system_metrics (Métricas do sistema)
CREATE TABLE IF NOT EXISTS system_metrics (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    metric_type ENUM('cpu','memory','disk','load','database') NOT NULL,
    metric_value DECIMAL(10,2) NOT NULL,
    metric_unit VARCHAR(10) DEFAULT '%',
    details JSON DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_metric_type (metric_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: notifications (Notificações)
CREATE TABLE IF NOT EXISTS notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED DEFAULT NULL,
    account_id INT UNSIGNED DEFAULT NULL,
    type ENUM('email','telegram','discord','webhook') NOT NULL,
    status ENUM('pending','sent','failed') DEFAULT 'pending',
    recipient TEXT NOT NULL,
    subject VARCHAR(255) DEFAULT NULL,
    content TEXT,
    error_message TEXT DEFAULT NULL,
    sent_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_id (project_id),
    INDEX idx_type (type),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

