-- Criar as tabelas
DROP TABLE IF EXISTS equipamentos;
DROP TABLE IF EXISTS manutencoes;
DROP TABLE IF EXISTS custos;

CREATE TABLE equipamentos (
    id_equipamento INTEGER PRIMARY KEY,
    nome TEXT,
    setor TEXT,
    data_instalacao TEXT
);

CREATE TABLE manutencoes (
    id_manutencao INTEGER PRIMARY KEY,
    id_equipamento INTEGER,
    tipo TEXT,
    inicio TEXT,
    fim TEXT,
    FOREIGN KEY (id_equipamento) REFERENCES equipamentos(id_equipamento)
);

CREATE TABLE custos (
    id_custo INTEGER PRIMARY KEY,
    id_manutencao INTEGER,
    pecas REAL,
    mao_obra REAL,
    FOREIGN KEY (id_manutencao) REFERENCES manutencoes(id_manutencao)
);

-- INSERTs prontos dos CSVs
INSERT INTO equipamentos VALUES
(1,'Bomba A','Moagem','2020-05-10'),
(2,'Bomba B','Moagem','2020-09-14'),
(3,'Correia 01','Transporte','2019-11-20'),
(4,'Misturador A','Pré-Mistura','2021-03-02'),
(5,'Forno Secagem','Secagem','2018-08-11');

INSERT INTO manutencoes VALUES
(101,1,'Corretiva','2025-01-03 08:00','2025-01-03 12:00'),
(102,1,'Preventiva','2025-01-10 07:00','2025-01-10 09:00'),
(103,2,'Corretiva','2025-01-05 14:00','2025-01-05 18:00'),
(104,3,'Preventiva','2025-01-07 09:00','2025-01-07 10:30'),
(105,3,'Corretiva','2025-01-15 11:00','2025-01-15 15:00'),
(106,4,'Preventiva','2025-01-18 08:00','2025-01-18 10:00'),
(107,5,'Corretiva','2025-01-20 06:00','2025-01-20 13:00');

INSERT INTO custos VALUES
(1,101,500,300),
(2,102,200,150),
(3,103,400,280),
(4,104,100,120),
(5,105,600,350),
(6,106,150,160),
(7,107,800,500);

-- Criar a tabela FATO com cálculos
DROP TABLE IF EXISTS fato_manutencao;

CREATE TABLE fato_manutencao AS
SELECT
    m.id_manutencao,
    m.id_equipamento,
    e.nome AS equipamento,
    e.setor,
    m.tipo,
    m.inicio,
    m.fim,

    /* Tempo de manutenção em horas */
    ROUND((strftime('%s', m.fim) - strftime('%s', m.inicio)) / 3600.0, 2) AS horas_parado,

    /* Custo total */
    (c.pecas + c.mao_obra) AS custo_total

FROM manutencoes m
JOIN equipamentos e ON m.id_equipamento = e.id_equipamento
JOIN custos c ON m.id_manutencao = c.id_manutencao;

-- 📊 5️⃣ KPIs Industriais
-- ✔ Total gasto em manutenção
SELECT SUM(custo_total) FROM fato_manutencao;

-- ✔ % preventivas
SELECT 
    ROUND(100.0 * SUM(tipo='Preventiva') / COUNT(*), 1) AS percentual_preventivas
FROM fato_manutencao;

-- ✔ Custo por equipamento
SELECT equipamento, SUM(custo_total)
FROM fato_manutencao
GROUP BY equipamento
ORDER BY 2 DESC;

-- ✔ Tempo total de máquina parada
SELECT equipamento, SUM(horas_parado)
FROM fato_manutencao
GROUP BY equipamento;

-- ✔ MTTR (Tempo Médio de Reparo)
SELECT ROUND(AVG(horas_parado), 2) AS MTTR
FROM fato_manutencao
WHERE tipo = 'Corretiva';