-- ============================================================================
-- Á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: Configurações padrão
-- ============================================================================
INSERT INTO orchestrator_configurations (`key`, `group`, `value`, `type`, description) VALUES
('system.app_name', 'system', 'ÁQUILA Control Center', 'string', 'Nome da aplicação'),
('system.timezone', 'system', 'America/Sao_Paulo', 'string', 'Timezone do sistema'),
('system.locale', 'system', 'pt_BR', 'string', 'Locale padrão'),
('system.debug', 'system', '0', 'boolean', 'Modo debug ativo'),
('streamcore.api_url', 'streamcore', '', 'string', 'URL da API do StreamCore'),
('streamcore.api_key', 'streamcore', '', 'string', 'Chave de API do StreamCore'),
('streamcore.timeout', 'streamcore', '30', 'integer', 'Timeout em segundos para requisições'),
('tmdb.api_key', 'tmdb', '', 'string', 'Chave de API do TMDB'),
('tmdb.language', 'tmdb', 'pt-BR', 'string', 'Idioma para resultados TMDB'),
('smtp.host', 'smtp', '', 'string', 'Host do servidor SMTP'),
('smtp.port', 'smtp', '587', 'integer', 'Porta do servidor SMTP'),
('smtp.username', 'smtp', '', 'string', 'Usuário SMTP'),
('smtp.password', 'smtp', '', 'string', 'Senha SMTP'),
('smtp.encryption', 'smtp', 'tls', 'string', 'Tipo de criptografia (tls/ssl)'),
('pix_gateway.provider', 'pix_gateway', '', 'string', 'Provedor do gateway PIX'),
('pix_gateway.api_url', 'pix_gateway', '', 'string', 'URL da API do gateway PIX'),
('pix_gateway.api_key', 'pix_gateway', '', 'string', 'Chave de API do gateway PIX'),
('pix_gateway.webhook_secret', 'pix_gateway', '', 'string', 'Secret para validação de webhooks'),
('whatsapp.provider', 'whatsapp', 'evolution_api', 'string', 'Provedor WhatsApp ativo'),
('whatsapp.api_url', 'whatsapp', '', 'string', 'URL da API do provedor WhatsApp'),
('whatsapp.api_key', 'whatsapp', '', 'string', 'Chave de API do provedor WhatsApp'),
('whatsapp.instance_name', 'whatsapp', '', 'string', 'Nome da instância WhatsApp'),
('telegram.bot_token', 'telegram', '', 'string', 'Token do bot Telegram'),
('telegram.chat_id', 'telegram', '', 'string', 'Chat ID padrão para notificações'),
('dns.primary', 'dns', '', 'string', 'DNS primário para aplicativos'),
('dns.secondary', 'dns', '', 'string', 'DNS secundário para aplicativos'),
('apps.android_version', 'apps', '1.0.0', 'string', 'Versão atual do app Android'),
('apps.android_tv_version', 'apps', '1.0.0', 'string', 'Versão atual do app Android TV'),
('apps.fire_tv_version', 'apps', '1.0.0', 'string', 'Versão atual do app Fire TV'),
('apps.lg_version', 'apps', '1.0.0', 'string', 'Versão atual do app LG'),
('apps.samsung_version', 'apps', '1.0.0', 'string', 'Versão atual do app Samsung'),
('apps.roku_version', 'apps', '1.0.0', 'string', 'Versão atual do app Roku')
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);

-- ============================================================================
-- 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);