-- Criar a base RAW (com problemas para limpar)
DROP TABLE IF EXISTS treinamento_raw;

CREATE TABLE treinamento_raw (
    id INTERGER PRIMARY KEY,
    nome_colaborador TEXT,
    treinamento TEXT,
    data_realizada TEXT,
    validade_meses TEXT
);

INSERT INTO treinamento_raw VALUES
(1, 'Joao Victor ', 'NR-11 - Empilhadeira', '10/01/2024', '12'),
(2, 'joao  victor', 'NR 11 Empilhadeira', '2024-01-10', '12'),
(3, 'Heloisa', 'NR-20 Inflamáveis', '05-02-2024', '24'),
(4, 'heloisa ', 'NR20 inflamaveis', '2024/02/05', '24'),
(5, 'Richard', 'NR-35 Trabalho Altura', '12/03/2023', '24'),
(6, 'Richard ', 'NR 35 - Altura', '2023-03-12', '24'),
(7, 'Ana Paula', 'Integração', '01/12/2024', '12'),
(8, 'Ana  paula', 'Integracao', '2024/12/01', '12');
/* Essa tabela bruta contém problemas reais:

Nomes inconsistentes
Treinamentos escritos de formas diferentes
Datas em 4 formatos diferentes
Repetições/duplicações
Espaços extras */

-- Criar a tabela TRATADA
DROP TABLE IF EXISTS treinamento_tratado;

CREATE TABLE treinamento_tratado AS
SELECT
    id,
    TRIM(LOWER(nome_colaborador)) AS nomecolaborador_limpo,
    
    CASE
        WHEN LOWER(treinamento) LIKE '%empilhadeira%' THEN 'NR-11 - Empilhadeira'
        WHEN LOWER(treinamento) LIKE '%inflam%' THEN 'NR-20 - Inflamáveis'
        WHEN LOWER(treinamento) LIKE '%altura%' THEN 'NR-35 - Trabalho em Altura'
        WHEN LOWER(treinamento) LIKE '%integra%' THEN 'Integração'
        ELSE treinamento
    END AS treinamento_padrao,

    /* Converter datas bagunçadas em formato AAAA-MM-DD */
    CASE
        WHEN data_realizada LIKE '%/%/%' THEN 
            substr(data_realizada,7,4)||'-'||substr(data_realizada,4,2)||'-'||substr(data_realizada,1,2)
        WHEN data_realizada LIKE '%-%-%' THEN 
            substr(data_realizada,7,4)||'-'||substr(data_realizada,4,2)||'-'||substr(data_realizada,1,2)
        ELSE data_realizada
    END AS data_realizacao_iso,

    CAST(validade_meses AS INTEGER) AS validade_meses

FROM treinamento_raw;

-- Criar a tabela FATO com cálculo de vencimento
DROP TABLE IF EXISTS fato_treinamento;

CREATE TABLE fato_treinamento AS
SELECT
    *,
    DATE(data_realizacao_iso, '+' || validade_meses || ' months') AS data_vencimento,
    
    CASE
        WHEN DATE(data_realizacao_iso, '+' || validade_meses || ' months') < DATE('now')
        THEN 'Vencido'
        WHEN DATE(data_realizacao_iso, '+' || validade_meses || ' months') 
             BETWEEN DATE('now') AND DATE('now', '+30 days')
        THEN 'A vencer (30 dias)'
        ELSE 'Em dia'
    END AS status
FROM treinamento_tratado;

-- Total de treinamentos
SELECT COUNT(*) FROM fato_treinamento;

-- Quantos estão vencidos
SELECT COUNT(*) FROM fato_treinamento WHERE status = 'Vencido';

-- Quantos vencem em 30 dias
SELECT COUNT(*) FROM fato_treinamento WHERE status = 'A vencer (30 dias)';

-- Treinamentos por colaborador
SELECT nomecolaborador_limpo, COUNT(*)
FROM fato_treinamento
GROUP BY nomecolaborador_limpo;