-- Criar os CSVs
DROP TABLE IF EXISTS motoristas;
DROP TABLE IF EXISTS viagens;
DROP TABLE IF EXISTS abastecimentos;

CREATE TABLE motoristas (
    id_motorista INTEGER PRIMARY KEY,
    nome TEXT,
    categoria_cnh TEXT,
    turno TEXT
);

CREATE TABLE viagens (
    id_viagem INTEGER PRIMARY KEY,
    id_motorista INTEGER,
    rota TEXT,
    data TEXT,
    km_inicial INTEGER,
    km_final INTEGER,
    FOREIGN KEY (id_motorista) REFERENCES motoristas(id_motorista)
);

CREATE TABLE abastecimentos (
    id_abastecimento INTEGER PRIMARY KEY,
    id_viagem INTEGER,
    litros REAL,
    valor_litro REAL,
    FOREIGN KEY (id_viagem) REFERENCES viagens(id_viagem)
);

-- Transformar os CSVs em INSERTS
INSERT INTO motoristas VALUES
(1,'João Victor','D','E'),
(2,'Ricardo','D','N'),
(3,'Antonio','C','E'),
(4,'Marcos Vinícius','E','D'),
(5,'Richard','C','N');

INSERT INTO viagens VALUES
(1001,1,'Usina → CD','2025-01-10',12000,12280),
(1002,1,'CD → Porto','2025-01-11',12280,12650),
(1003,2,'Usina → CD','2025-01-10',54000,54220),
(1004,3,'CD → Usina','2025-01-12',33000,33320),
(1005,4,'Usina → Porto','2025-01-15',75500,76000),
(1006,5,'Porto → Usina','2025-01-16',28000,28350);

INSERT INTO abastecimentos VALUES
(1,1001,90,4.80),
(2,1002,110,4.75),
(3,1002,30,4.90),
(4,1004,80,4.85),
(5,1005,150,4.70),
(6,1006,120,4.95);

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

CREATE TABLE fato_viagens AS
SELECT
    v.id_viagem,
    v.data,
    v.rota,
    m.nome AS motorista,
    m.turno,
    m.categoria_cnh,
    (v.km_final - v.km_inicial) AS km_percorrido,

    /* Total de litros abastecidos */
    (SELECT SUM(litros) FROM abastecimentos a 
     WHERE a.id_viagem = v.id_viagem) AS litros_usados,

    /* Custo total */
    (SELECT SUM(litros * valor_litro) FROM abastecimentos a 
     WHERE a.id_viagem = v.id_viagem) AS custo_total,

    /* Consumo km/l */
    ROUND((v.km_final - v.km_inicial) / 
        (SELECT SUM(litros) FROM abastecimentos a WHERE a.id_viagem = v.id_viagem), 2) 
        AS consumo_km_l,

    /* Custo por km */
    ROUND(
        (SELECT SUM(litros * valor_litro) FROM abastecimentos a WHERE a.id_viagem = v.id_viagem)
         / (v.km_final - v.km_inicial), 
    2) AS custo_por_km

FROM viagens v
JOIN motoristas m ON v.id_motorista = m.id_motorista;

-- KPIs operacionais
-- 🛢 Consumo médio (km/l)
SELECT ROUND(AVG(consumo_km_l), 2) AS consumo_medio
FROM fato_viagens;

-- 💰 Custo total das operações
SELECT SUM(custo_total) AS custo_geral
FROM fato_viagens;

-- 📌 Ranking de motoristas por eficiência (km/l)
SELECT motorista, ROUND(AVG(consumo_km_l),2) AS eficiencia
FROM fato_viagens
GROUP BY motorista
ORDER BY eficiencia DESC;

-- 🛣 Quilometragem total por rota
SELECT rota, SUM(km_percorrido)
FROM fato_viagens
GROUP BY rota;

-- 🚨 Viagens com inconsistência (km ≤ 0)
SELECT *
FROM fato_viagens
WHERE km_percorrido <= 0;