-- ============================================================================
-- ÁQUILA ORCHESTRATOR - Migration
-- Data: 2026-07-10
-- Descrição: Cria todas as tabelas do módulo Orchestrator
-- ============================================================================

-- ─── Queue Jobs ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS orchestrator_queue_jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    queue VARCHAR(100) NOT NULL DEFAULT 'default',
    payload JSON NOT NULL,
    priority TINYINT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending',
    max_attempts TINYINT UNSIGNED NOT NULL DEFAULT 3,
    attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    result JSON NULL,
    error_message TEXT NULL,
    available_at DATETIME NOT NULL,
    started_at DATETIME NULL,
    completed_at DATETIME NULL,
    failed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_queue_status_available (queue, status, available_at),
    INDEX idx_queue_priority (queue, priority DESC, created_at ASC),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Scheduled Tasks ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS orchestrator_scheduled_tasks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    task_type VARCHAR(100) NOT NULL,
    cron_expression VARCHAR(50) NOT NULL DEFAULT '0 0 * * *',
    payload JSON NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    status ENUM('idle', 'running') NOT NULL DEFAULT 'idle',
    max_retries TINYINT UNSIGNED NOT NULL DEFAULT 3,
    run_count INT UNSIGNED NOT NULL DEFAULT 0,
    fail_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_run_at DATETIME NULL,
    last_result JSON NULL,
    last_error TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_active_status (is_active, status),
    INDEX idx_task_type (task_type),
    UNIQUE INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Audit Logs ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS orchestrator_audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(200) NOT NULL,
    data JSON NULL,
    user_id INT UNSIGNED NULL,
    user_type VARCHAR(50) NOT NULL DEFAULT 'system',
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(500) NULL,
    device VARCHAR(200) NULL,
    origin VARCHAR(100) NOT NULL DEFAULT 'orchestrator',
    result VARCHAR(50) NOT NULL DEFAULT 'success',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_action (action),
    INDEX idx_user (user_id, user_type),
    INDEX idx_ip (ip_address),
    INDEX idx_origin (origin),
    INDEX idx_result (result),
    INDEX idx_created_at (created_at),
    INDEX idx_action_created (action, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Configurations ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS orchestrator_configurations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(200) NOT NULL,
    `group` VARCHAR(100) NOT NULL DEFAULT 'system',
    `value` TEXT NULL,
    `type` VARCHAR(20) NOT NULL DEFAULT 'string',
    description VARCHAR(500) NULL,
    is_sensitive BOOLEAN NOT NULL DEFAULT FALSE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE INDEX idx_key (`key`),
    INDEX idx_group (`group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Feature Flags ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS orchestrator_feature_flags (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    flag VARCHAR(100) NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT NULL,
    is_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    rollout_percentage TINYINT UNSIGNED NOT NULL DEFAULT 100,
    allowed_users JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE INDEX idx_flag (flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Health Checks ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS orchestrator_health_checks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_name VARCHAR(100) NOT NULL,
    status ENUM('healthy', 'degraded', 'down', 'unknown') NOT NULL DEFAULT 'unknown',
    message VARCHAR(500) NULL,
    response_time_ms DECIMAL(10, 2) NULL,
    checked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_service_checked (service_name, checked_at DESC),
    INDEX idx_status (status),
    INDEX idx_checked_at (checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Event Log (persistência opcional do Event Bus) ──────────────────────────
CREATE TABLE IF NOT EXISTS orchestrator_event_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id VARCHAR(100) NOT NULL,
    event_name VARCHAR(100) NOT NULL,
    payload JSON NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_event_name (event_name),
    INDEX idx_event_id (event_id),
    INDEX idx_created_at (created_at),
    INDEX idx_name_created (event_name, created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Notifications Log ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS orchestrator_notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    channel VARCHAR(50) NOT NULL,
    recipient VARCHAR(200) NOT NULL,
    title VARCHAR(300) NULL,
    message TEXT NOT NULL,
    status ENUM('queued', 'sent', 'delivered', 'failed') NOT NULL DEFAULT 'queued',
    error_message TEXT NULL,
    sent_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_channel_status (channel, status),
    INDEX idx_recipient (recipient),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- DADOS INICIAIS: Feature Flags padrão
-- ============================================================================
INSERT INTO orchestrator_feature_flags (flag, name, description, is_enabled, rollout_percentage) VALUES
('ai_core', 'AI Core', 'Módulo de inteligência artificial e automação de conversas', TRUE, 100),
('tmdb', 'TMDB Integration', 'Integração com The Movie Database para metadados', FALSE, 100),
('sports', 'Sports Module', 'Módulo de esportes ao vivo', FALSE, 100),
('pix_gateway', 'Gateway PIX', 'Gateway de pagamentos via PIX', TRUE, 100),
('lg_app', 'LG App', 'Aplicativo para Smart TVs LG', TRUE, 100),
('samsung_app', 'Samsung App', 'Aplicativo para Smart TVs Samsung', TRUE, 100),
('roku_app', 'Roku App', 'Aplicativo para dispositivos Roku', FALSE, 100),
('telegram_bot', 'Telegram Bot', 'Bot de atendimento via Telegram', FALSE, 100),
('auto_renewal', 'Auto Renewal', 'Renovação automática após confirmação de pagamento', TRUE, 100),
('auto_test', 'Auto Test', 'Criação automática de linhas de teste', TRUE, 100),
('knowledge_base', 'Knowledge Base', 'Base de conhecimento para respostas automáticas', TRUE, 100),
('human_handoff', 'Human Handoff', 'Transferência automática para operador humano', TRUE, 100)
ON DUPLICATE KEY UPDATE name = VALUES(name);



-- ============================================================================
-- DADOS INICIAIS: Tarefas agendadas padrão
-- ============================================================================
INSERT INTO orchestrator_scheduled_tasks (name, task_type, cron_expression, payload, is_active) VALUES
('Verificar clientes vencendo', 'check_expiring_clients', '0 8 * * *', '{"days_ahead": 3}', TRUE),
('Verificar clientes vencidos', 'check_expired_clients', '0 9 * * *', '{"notify": true}', TRUE),
('Limpar filas antigas', 'purge_completed_jobs', '0 3 * * *', '{"older_than_days": 7}', TRUE),
('Limpar logs de auditoria', 'purge_audit_logs', '0 4 1 * *', '{"older_than_days": 90}', TRUE),
('Health check geral', 'health_check_all', '*/5 * * * *', '{}', TRUE),
('Sincronizar cache TMDB', 'sync_tmdb_cache', '0 2 * * *', '{}', FALSE),
('Backup configurações', 'backup_configurations', '0 1 * * 0', '{}', TRUE),
('Limpar health checks antigos', 'purge_health_checks', '0 5 1 * *', '{"older_than_days": 30}', TRUE),
('Resetar jobs travados', 'reset_stuck_jobs', '*/15 * * * *', '{"stuck_minutes": 30}', TRUE)
ON DUPLICATE KEY UPDATE task_type = VALUES(task_type);
