-- ============================================
-- SISTEMA DE CERTIFICADOS FBUSINESS
-- Atualização da Tabela treinamento_progresso
-- ============================================
-- 
-- Sistema: SaaS-Control
-- Empresa: FBusiness - Sistemas Operacionais e Aplicativos
-- Data: Janeiro 2026
-- Versão: 1.0.0
--
-- Descrição:
-- Este script adiciona os campos necessários para o sistema
-- de certificados automáticos do treinamento FBusiness
--
-- ============================================

-- Verificar se a tabela existe antes de alterar
-- Se não existir, criar tabela completa

-- ============================================
-- 1. CRIAR TABELA (se não existir)
-- ============================================

CREATE TABLE IF NOT EXISTS `treinamento_progresso` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `usuario_id` INT NOT NULL,
    `videos_assistidos` INT DEFAULT 0,
    `tempo_estudo` INT DEFAULT 0 COMMENT 'Tempo em minutos',
    `tempo_total_estudo` INT DEFAULT 0 COMMENT 'Tempo total em minutos',
    `pontuacao_quiz` DECIMAL(5,2) DEFAULT 0,
    `taxa_conclusao` DECIMAL(5,2) DEFAULT 0 COMMENT 'Porcentagem de conclusão',
    `progresso_geral` DECIMAL(5,2) DEFAULT 0 COMMENT 'Progresso geral em %',
    `modulo_atual` VARCHAR(50) DEFAULT 'basics',
    `ultima_atividade` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `data_inicio` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    -- NOVOS CAMPOS PARA CERTIFICADOS
    `certificado_gerado` TINYINT(1) DEFAULT 0 COMMENT '0=Não gerado, 1=Gerado',
    `codigo_certificado` VARCHAR(20) UNIQUE COMMENT 'Código único de verificação',
    `data_conclusao` DATETIME COMMENT 'Data e hora da conclusão do curso',
    
    -- Índices
    INDEX `idx_usuario` (`usuario_id`),
    INDEX `idx_certificado` (`codigo_certificado`),
    INDEX `idx_conclusao` (`data_conclusao`),
    
    -- Chave estrangeira (ajustar conforme sua estrutura)
    -- Referencia a tabela 'acessos' que contém os usuários do sistema
    FOREIGN KEY (`usuario_id`) REFERENCES `acessos`(`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Tabela de progresso do treinamento com sistema de certificados';

-- ============================================
-- 2. ADICIONAR CAMPOS (se tabela já existir)
-- ============================================

-- Verificar se os campos já existem antes de adicionar
-- Ignora erro se campo já existe

-- Campo: certificado_gerado
SET @query = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'treinamento_progresso' 
     AND COLUMN_NAME = 'certificado_gerado') = 0,
    'ALTER TABLE `treinamento_progresso` ADD COLUMN `certificado_gerado` TINYINT(1) DEFAULT 0 COMMENT ''0=Não gerado, 1=Gerado''',
    'SELECT ''Campo certificado_gerado já existe'' AS msg'
);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Campo: codigo_certificado
SET @query = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'treinamento_progresso' 
     AND COLUMN_NAME = 'codigo_certificado') = 0,
    'ALTER TABLE `treinamento_progresso` ADD COLUMN `codigo_certificado` VARCHAR(20) UNIQUE COMMENT ''Código único de verificação''',
    'SELECT ''Campo codigo_certificado já existe'' AS msg'
);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Campo: data_conclusao
SET @query = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'treinamento_progresso' 
     AND COLUMN_NAME = 'data_conclusao') = 0,
    'ALTER TABLE `treinamento_progresso` ADD COLUMN `data_conclusao` DATETIME COMMENT ''Data e hora da conclusão do curso''',
    'SELECT ''Campo data_conclusao já existe'' AS msg'
);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- 3. CRIAR ÍNDICES (se não existirem)
-- ============================================

-- Índice para codigo_certificado (para buscas rápidas na verificação)
SET @query = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'treinamento_progresso' 
     AND INDEX_NAME = 'idx_certificado') = 0,
    'CREATE INDEX `idx_certificado` ON `treinamento_progresso` (`codigo_certificado`)',
    'SELECT ''Índice idx_certificado já existe'' AS msg'
);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Índice para data_conclusao (para relatórios)
SET @query = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'treinamento_progresso' 
     AND INDEX_NAME = 'idx_conclusao') = 0,
    'CREATE INDEX `idx_conclusao` ON `treinamento_progresso` (`data_conclusao`)',
    'SELECT ''Índice idx_conclusao já existe'' AS msg'
);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- 4. DADOS DE EXEMPLO (OPCIONAL)
-- ============================================

-- Descomente as linhas abaixo para inserir dados de teste
/*
INSERT INTO `treinamento_progresso` 
(usuario_id, videos_assistidos, tempo_total_estudo, pontuacao_quiz, taxa_conclusao, progresso_geral, certificado_gerado, codigo_certificado, data_conclusao)
VALUES 
(1, 80, 1200, 95.5, 100, 95, 1, 'ABC123DEF456GHI7', NOW()),
(2, 60, 900, 85.0, 75, 80, 0, NULL, NULL);
*/

-- ============================================
-- 5. VERIFICAÇÃO DA ESTRUTURA
-- ============================================

-- Exibir estrutura da tabela atualizada
DESCRIBE `treinamento_progresso`;

-- Verificar índices criados
SHOW INDEX FROM `treinamento_progresso`;

-- ============================================
-- 6. QUERY DE TESTE
-- ============================================

-- Verificar usuários elegíveis para certificado
-- (progresso >= 80% mas ainda não tem certificado)
SELECT 
    u.id,
    u.nome,
    u.email,
    tp.progresso_geral,
    tp.videos_assistidos,
    tp.pontuacao_quiz,
    tp.certificado_gerado,
    tp.codigo_certificado,
    CASE 
        WHEN tp.progresso_geral >= 80 AND tp.certificado_gerado = 0 THEN 'Elegível para certificado'
        WHEN tp.progresso_geral >= 80 AND tp.certificado_gerado = 1 THEN 'Certificado já emitido'
        ELSE 'Ainda não completou o curso'
    END AS status_certificado
FROM usuarios_condominio u
LEFT JOIN treinamento_progresso tp ON u.id = tp.usuario_id
WHERE tp.progresso_geral >= 80
ORDER BY tp.progresso_geral DESC;

-- ============================================
-- 7. ESTATÍSTICAS
-- ============================================

-- Estatísticas de certificados emitidos
SELECT 
    COUNT(*) AS total_usuarios,
    COUNT(CASE WHEN progresso_geral >= 80 THEN 1 END) AS usuarios_completos,
    COUNT(CASE WHEN certificado_gerado = 1 THEN 1 END) AS certificados_emitidos,
    AVG(pontuacao_quiz) AS nota_media,
    AVG(tempo_total_estudo / 60) AS tempo_medio_horas,
    MIN(data_conclusao) AS primeira_conclusao,
    MAX(data_conclusao) AS ultima_conclusao
FROM treinamento_progresso;

-- ============================================
-- 8. LIMPEZA (USAR COM CUIDADO!)
-- ============================================

-- ATENÇÃO: Descomente apenas se precisar remover os campos
-- Isso irá DELETAR todos os dados de certificados!

/*
ALTER TABLE `treinamento_progresso` 
DROP COLUMN `certificado_gerado`,
DROP COLUMN `codigo_certificado`,
DROP COLUMN `data_conclusao`;

DROP INDEX `idx_certificado` ON `treinamento_progresso`;
DROP INDEX `idx_conclusao` ON `treinamento_progresso`;
*/

-- ============================================
-- 9. BACKUP RECOMENDADO
-- ============================================

-- Antes de executar este script, recomenda-se fazer backup:
-- mysqldump -u usuario -p nome_banco treinamento_progresso > backup_treinamento.sql

-- ============================================
-- FIM DO SCRIPT
-- ============================================

-- Mensagem de sucesso
SELECT 
    '✅ Script executado com sucesso!' AS status,
    'Sistema de Certificados FBusiness instalado' AS mensagem,
    NOW() AS data_instalacao;

-- ============================================
-- INFORMAÇÕES ADICIONAIS
-- ============================================

-- Documentação: README_CERTIFICADOS_FBUSINESS.md
-- Documentação Completa: CERTIFICADOS_FBUSINESS_DOCUMENTACAO.md
-- Empresa: FBusiness - Sistemas Operacionais e Aplicativos
-- Sistema: SaaS-Control
-- Versão: 1.0.0
-- Data: Janeiro 2026