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

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Tabela: settings (Configurações do sistema)
CREATE TABLE IF NOT EXISTS settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) NOT NULL UNIQUE,
    `value` TEXT,
    `group` VARCHAR(50) DEFAULT 'general',
    is_encrypted TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_group (`group`),
    INDEX idx_key (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: users (Usuários administrativos)
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','operator','viewer') DEFAULT 'operator',
    status ENUM('active','inactive','blocked') DEFAULT 'active',
    two_factor_secret VARCHAR(32) DEFAULT NULL,
    two_factor_enabled TINYINT(1) DEFAULT 0,
    failed_attempts TINYINT UNSIGNED DEFAULT 0,
    blocked_until TIMESTAMP NULL DEFAULT NULL,
    last_login_at TIMESTAMP NULL DEFAULT NULL,
    last_login_ip VARCHAR(45) DEFAULT NULL,
    remember_token VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: password_resets (Recuperação de senha)
CREATE TABLE IF NOT EXISTS password_resets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    used_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token_hash (token_hash),
    INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=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: accounts (Contas de e-mail)
CREATE TABLE IF NOT EXISTS accounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    source_host VARCHAR(255) NOT NULL,
    source_port INT UNSIGNED DEFAULT 993,
    source_encryption ENUM('ssl','tls','none') DEFAULT 'ssl',
    source_username VARCHAR(255) NOT NULL,
    source_password_encrypted TEXT NOT NULL,
    source_folders_hash VARCHAR(64) DEFAULT NULL,
    destination_host VARCHAR(255) NOT NULL,
    destination_port INT UNSIGNED DEFAULT 993,
    destination_encryption ENUM('ssl','tls','none') DEFAULT 'ssl',
    destination_username VARCHAR(255) NOT NULL,
    destination_password_encrypted TEXT NOT NULL,
    status ENUM('pending','connecting','analyzing','ready','migrating','paused','completed','error') DEFAULT 'pending',
    source_provider VARCHAR(50) DEFAULT NULL,
    destination_provider VARCHAR(50) DEFAULT NULL,
    oauth_source_token TEXT DEFAULT NULL,
    oauth_destination_token TEXT DEFAULT NULL,
    total_messages INT UNSIGNED DEFAULT 0,
    total_folders INT UNSIGNED DEFAULT 0,
    total_size BIGINT UNSIGNED DEFAULT 0,
    migrated_messages INT UNSIGNED DEFAULT 0,
    failed_messages INT UNSIGNED DEFAULT 0,
    skipped_messages INT UNSIGNED DEFAULT 0,
    started_at TIMESTAMP NULL DEFAULT NULL,
    completed_at TIMESTAMP NULL DEFAULT NULL,
    last_error TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_project_id (project_id),
    INDEX idx_status (status),
    INDEX idx_source_username (source_username)
) 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;

-- Tabela: api_tokens (Tokens da API REST)
CREATE TABLE IF NOT EXISTS api_tokens (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    refresh_token_hash VARCHAR(255) DEFAULT NULL,
    name VARCHAR(100) DEFAULT 'API Token',
    scopes JSON DEFAULT NULL,
    expires_at TIMESTAMP NOT NULL,
    last_used_at TIMESTAMP NULL DEFAULT NULL,
    revoked_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token_hash (token_hash),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: provider_settings (Configurações de provedores)
CREATE TABLE IF NOT EXISTS provider_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider_name VARCHAR(50) NOT NULL,
    provider_type ENUM('hosting','service') NOT NULL,
    imap_host VARCHAR(255) DEFAULT NULL,
    imap_port INT UNSIGNED DEFAULT 993,
    imap_encryption ENUM('ssl','tls','none') DEFAULT 'ssl',
    smtp_host VARCHAR(255) DEFAULT NULL,
    smtp_port INT UNSIGNED DEFAULT 587,
    smtp_encryption ENUM('ssl','tls','none') DEFAULT 'tls',
    autodiscover_url VARCHAR(255) DEFAULT NULL,
    oauth_enabled TINYINT(1) DEFAULT 0,
    oauth_client_id VARCHAR(255) DEFAULT NULL,
    oauth_client_secret_encrypted TEXT DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_provider (provider_name),
    INDEX idx_provider_type (provider_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Inserir provedores padrão
INSERT INTO provider_settings (provider_name, provider_type, imap_host, imap_port, imap_encryption) VALUES
('locaweb', 'hosting', 'imap.locaweb.com.br', 993, 'ssl'),
('cpanel', 'hosting', NULL, 993, 'ssl'),
('plesk', 'hosting', NULL, 993, 'ssl'),
('directadmin', 'hosting', NULL, 993, 'ssl'),
('hostinger', 'hosting', 'imap.hostinger.com', 993, 'ssl'),
('hostgator', 'hosting', 'imap.hostgator.com.br', 993, 'ssl'),
('kinghost', 'hosting', 'imap.kinghost.com.br', 993, 'ssl'),
('uolhost', 'hosting', 'imap.uolhost.com.br', 993, 'ssl'),
('godaddy', 'hosting', 'imap.secureserver.net', 993, 'ssl'),
('registrobr', 'hosting', 'imap.registro.br', 993, 'ssl'),
('microsoft365', 'service', 'outlook.office365.com', 993, 'ssl'),
('exchange_online', 'service', 'outlook.office365.com', 993, 'ssl'),
('google_workspace', 'service', 'imap.gmail.com', 993, 'ssl'),
('gmail', 'service', 'imap.gmail.com', 993, 'ssl'),
('zoho_mail', 'service', 'imap.zoho.com', 993, 'ssl'),
('zimbra', 'service', NULL, 993, 'ssl'),
('icewarp', 'service', NULL, 993, 'ssl'),
('kerio_connect', 'service', NULL, 993, 'ssl'),
('smartermail', 'service', NULL, 993, 'ssl')
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

SET FOREIGN_KEY_CHECKS = 1;