-- Criando Tabelas

DROP TABLE IF EXISTS clientes;
DROP TABLE IF EXISTS pedidos;
DROP TABLE IF EXISTS itens_pedido;

CREATE TABLE clientes (
    id_cliente INTEGER PRIMARY KEY,
    nome TEXT,
    setor TEXT,
    estado TEXT
);

CREATE TABLE pedidos (
    id_pedido INTEGER PRIMARY KEY,
    id_cliente INTEGER,
    data_pedido TEXT,
    FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente)
);

CREATE TABLE itens_pedido (
    id_item INTEGER PRIMARY KEY,
    id_pedido INTEGER,
    produto TEXT,
    quantidade INTEGER,
    preco_unitario REAL,
    FOREIGN KEY (id_pedido) REFERENCES pedidos(id_pedido)
);

-- Importação dos CSVs → Transformar em INSERTS
INSERT INTO clientes VALUES
(1,'João Victor','Faturamento','SP'),
(2,'Heloísa','Financeiro','GO'),
(3,'Richard','Operações','SP'),
(4,'Gabriel','TI','RJ'),
(5,'Douglas','Segurança','MG');

INSERT INTO pedidos VALUES
(1001,1,'2025-01-10'),
(1002,1,'2025-01-15'),
(1003,2,'2025-01-12'),
(1004,3,'2025-01-20'),
(1005,4,'2025-01-21'),
(1006,5,'2025-01-22');

INSERT INTO itens_pedido VALUES
(1,1001,'Etanol',5000,2.50),
(2,1001,'Diesel',2000,3.80),
(3,1002,'Gasolina',3000,4.20),
(4,1003,'Óleo Lubrificante',500,15.00),
(5,1004,'Etanol',10000,2.40),
(6,1005,'Diesel',3000,3.70),
(7,1006,'Gasolina',2000,4.10);

-- Criar a FATO de Venda
DROP TABLE IF EXISTS fato_vendas;

CREATE TABLE fato_vendas AS
SELECT
    i.id_item,
    p.id_pedido,
    p.data_pedido,
    c.id_cliente,
    c.nome AS cliente,
    c.setor,
    c.estado,
    i.produto,
    i.quantidade,
    i.preco_unitario,
    i.quantidade * i.preco_unitario AS valor_total
FROM itens_pedido i
JOIN pedidos p ON i.id_pedido = p.id_pedido
JOIN clientes c ON p.id_cliente = c.id_cliente;

-- KPIs avançados
-- ✔ Total vendido
SELECT SUM(valor_total) AS faturamento_total
FROM fato_vendas;

-- ✔ Faturamento por cliente
SELECT cliente, SUM(valor_total) AS total
FROM fato_vendas
GROUP BY cliente
ORDER BY total DESC;

-- ✔ Faturamento por produto
SELECT produto, SUM(valor_total) AS total
FROM fato_vendas
GROUP BY produto
ORDER BY total DESC;

-- ✔ Pedidos sem itens (verificação de inconsistências)
SELECT p.id_pedido
FROM pedidos p
LEFT JOIN itens_pedido i ON p.id_pedido = i.id_pedido
WHERE i.id_item IS NULL;

-- ✔ Ranking de clientes
SELECT cliente, SUM(valor_total) AS total
FROM fato_vendas
GROUP BY cliente
ORDER BY total DESC;