-- =====================================================
-- LIMPEZA (caso já tenha executado antes)
-- =====================================================

DROP VIEW IF EXISTS vw_performance_logistica;
DROP TABLE IF EXISTS sla_transportadora;
DROP TABLE IF EXISTS cargas;

-- =====================================================
-- 1️⃣ CRIAR TABELA PRINCIPAL
-- =====================================================

CREATE TABLE cargas (
    id INT PRIMARY KEY,
    transportadora VARCHAR(100),
    motorista VARCHAR(100),
    peso_ton DECIMAL(5,2),
    data_entrada DATETIME,
    data_saida DATETIME,
    tempo_permanencia_min INT
);

-- =====================================================
-- 2️⃣ INSERIR DADOS
-- =====================================================

INSERT INTO cargas (id, transportadora, motorista, peso_ton, data_entrada, data_saida)
VALUES
(1,'TransLog','Carlos Silva',37.20,'2026-03-01 07:45:00','2026-03-01 08:50:00'),
(2,'Rápido Brasil','João Mendes',35.80,'2026-03-01 08:10:00','2026-03-01 09:25:00'),
(3,'Carga Forte','Ana Souza',36.50,'2026-03-01 09:00:00','2026-03-01 10:05:00'),
(4,'TransLog','Marcos Lima',38.10,'2026-03-01 09:40:00','2026-03-01 10:20:00'),
(5,'Via Norte','Pedro Alves',34.90,'2026-03-01 10:15:00','2026-03-01 11:30:00'),
(6,'Rápido Brasil','Lucas Santos',37.80,'2026-03-01 11:00:00','2026-03-01 12:35:00'),
(7,'Carga Forte','Fernanda Rocha',36.20,'2026-03-01 12:10:00','2026-03-01 13:05:00'),
(8,'TransLog','Carlos Silva',37.00,'2026-03-01 13:00:00','2026-03-01 14:15:00'),
(9,'Via Norte','Rafael Costa',35.60,'2026-03-01 14:20:00','2026-03-01 15:40:00'),
(10,'Rápido Brasil','Bruno Martins',38.50,'2026-03-01 15:00:00','2026-03-01 16:05:00');

-- =====================================================
-- 3️⃣ CALCULAR TEMPO DE PERMANÊNCIA
-- =====================================================

UPDATE cargas
SET tempo_permanencia_min =
    TIMESTAMPDIFF(MINUTE, data_entrada, data_saida);

-- =====================================================
-- 4️⃣ CRIAR TABELA DE SLA
-- =====================================================

CREATE TABLE sla_transportadora (
    transportadora VARCHAR(100) PRIMARY KEY,
    meta_minutos INT
);

INSERT INTO sla_transportadora VALUES
('TransLog', 90),
('Rápido Brasil', 85),
('Carga Forte', 95),
('Via Norte', 88);

-- =====================================================
-- 5️⃣ CONSULTAS ANALÍTICAS
-- =====================================================

-- Tempo médio geral
SELECT 
    ROUND(AVG(tempo_permanencia_min),2) AS tempo_medio_minutos
FROM cargas;

-- Ranking transportadoras
SELECT 
    transportadora,
    ROUND(AVG(tempo_permanencia_min),2) AS tempo_medio
FROM cargas
GROUP BY transportadora
ORDER BY tempo_medio ASC;

-- Ranking motoristas
SELECT 
    motorista,
    ROUND(AVG(tempo_permanencia_min),2) AS tempo_medio
FROM cargas
GROUP BY motorista
ORDER BY tempo_medio ASC;

-- Horário de pico
SELECT 
    HOUR(data_entrada) AS hora,
    COUNT(*) AS volume
FROM cargas
GROUP BY HOUR(data_entrada)
ORDER BY volume DESC;

-- Top 5 maiores tempos
SELECT 
    id,
    transportadora,
    motorista,
    tempo_permanencia_min
FROM cargas
ORDER BY tempo_permanencia_min DESC
LIMIT 5;

-- Percentual de cumprimento por transportadora
SELECT
    c.transportadora,
    ROUND(
        SUM(CASE 
            WHEN c.tempo_permanencia_min <= s.meta_minutos THEN 1 
            ELSE 0 
        END) * 100.0 / COUNT(*)
    ,2) AS percentual_dentro_meta
FROM cargas c
JOIN sla_transportadora s
    ON c.transportadora = s.transportadora
GROUP BY c.transportadora;

-- =====================================================
-- 6️⃣ CRIAR VIEW ANALÍTICA
-- =====================================================

CREATE VIEW vw_performance_logistica AS
SELECT
    c.id,
    c.transportadora,
    c.motorista,
    c.peso_ton,
    c.tempo_permanencia_min,
    s.meta_minutos,
    CASE
        WHEN c.tempo_permanencia_min <= s.meta_minutos 
        THEN 'Dentro da Meta'
        ELSE 'Fora da Meta'
    END AS status_meta
FROM cargas c
JOIN sla_transportadora s
    ON c.transportadora = s.transportadora;

-- Testar VIEW
SELECT * FROM vw_performance_logistica;