DROP TABLE IF EXISTS Estado;

CREATE TABLE Estado (
    IDEstado INT AUTO_INCREMENT PRIMARY KEY,
    Nome VARCHAR(50) NOT NULL,
    UF VARCHAR(2) NOT NULL
);

INSERT INTO Estado (Nome, UF) VALUES
('São Paulo', 'SP'),        -- ID 1
('Rio de Janeiro', 'RJ'),   -- ID 2
('Espírito Santo', 'ES'),   -- ID 3
('Ceará', 'CE');            -- ID 4


DROP TABLE IF EXISTS Municipio;

DROP TABLE IF EXISTS Municipio;

CREATE TABLE Municipio (
    IDMunicipio INT AUTO_INCREMENT PRIMARY KEY,
    Estado_IDEstado INT NOT NULL,
    Nome VARCHAR(80) NOT NULL,
    CodIBGE INT NOT NULL,
    FOREIGN KEY (Estado_IDEstado) REFERENCES Estado(IDEstado)
);

INSERT INTO Municipio (Estado_IDEstado, Nome, CodIBGE) VALUES
(1, 'Guarulhos', 3518800),      -- SP
(1, 'São Paulo', 3550308),      -- SP
(3, 'Vila Velha', 3205200),     -- ES
(4, 'Fortaleza', 2304400);      -- CE

DROP TABLE IF EXISTS Cliente;

CREATE TABLE Cliente (
    IDCliente INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    Nome VARCHAR(80) NOT NULL,
    CPF CHAR(11) NOT NULL UNIQUE,
    Celular CHAR(11),
    EndLogradouro VARCHAR(100) NOT NULL,
    EndNumero VARCHAR(10) NOT NULL,
    EndMunicipio INT NOT NULL,
    EndCep VARCHAR(8),
    FOREIGN KEY (EndMunicipio) REFERENCES Municipio(IDMunicipio)
);



-- Inserir alguns dados

INSERT INTO Cliente (Nome, CPF, Celular, EndLogradouro, EndNumero, EndMunicipio, EndCep)
VALUES ('Sergio Cardoso', '12345678901', '85974997823', 'Rua Alagoas', '38', 4, '60000001');

INSERT INTO Cliente (Nome, CPF, Celular, EndLogradouro, EndNumero, EndMunicipio, EndCep)
VALUES ('Olinda Vasconcelos', '13234567890', '27975997823', 'Rua Rantes', '489', 3, '29199990');

INSERT INTO Cliente (Nome, CPF, Celular, EndLogradouro, EndNumero, EndMunicipio, EndCep)
VALUES ('Arminda Soares', '98776543178', '27987310011', 'Rua B', '25', 3, '29000100');

INSERT INTO Cliente (Nome, CPF, Celular, EndLogradouro, EndNumero, EndMunicipio, EndCep)
VALUES ('Rogério Fernandes', '65789034512', '11966474976', 'Rua Borba Gato', '6578', 1, '07878123');

INSERT INTO Cliente (Nome, CPF, Celular, EndLogradouro, EndNumero, EndMunicipio, EndCep)
VALUES ('Verônica Cruz Palmas', '25465412367', '21956784500', 'Rua Copacabana', '2', 2, '23799200');

DROP TABLE IF EXISTS ContasReceber;

CREATE TABLE ContasReceber (
    IDCReceber INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    Cliente_IDCliente INT NOT NULL,
    FaturaVenda INT NOT NULL,
    DataConta DATE NOT NULL,
    DataVencimento DATE NOT NULL,
    Valor DECIMAL(18,2) NOT NULL,
    Situacao ENUM('1','2','3') NOT NULL,
    FOREIGN KEY (Cliente_IDCliente) REFERENCES Cliente(IDCliente)
);

INSERT INTO ContasReceber 
(Cliente_IDCliente, FaturaVenda, DataConta, DataVencimento, Valor, Situacao)
VALUES 
(1, 123, '2026-04-12', '2026-05-13', 320.98, '1'),
(2, 143, '2026-03-10', '2026-04-10', 580.00, '3'),
(3, 220, '2026-03-05', '2026-04-06', 1980.35, '3'),
(4, 221, '2026-04-12', '2026-05-13', 320.98, '1'),
(5, 234, '2026-04-10', '2026-05-11', 2987.65, '1');

CREATE VIEW vw_Clientes AS
SELECT 
    IDCliente,
    Nome,
    CPF,
    Celular,
    EndLogradouro,
    EndNumero,
    EndMunicipio,
    EndCep
FROM Cliente;

CREATE VIEW vw_MunicipiosEstados AS
SELECT 
    m.IDMunicipio,
    m.Nome AS Municipio,
    e.Nome AS Estado,
    e.UF
FROM Municipio m
JOIN Estado e ON m.Estado_IDEstado = e.IDEstado;

CREATE VIEW vw_ContasReceber AS
SELECT 
    cr.IDCReceber,
    c.Nome AS Cliente,
    cr.FaturaVenda,
    cr.DataConta,
    cr.DataVencimento,
    cr.Valor,
    cr.Situacao
FROM ContasReceber cr
JOIN Cliente c ON cr.Cliente_IDCliente = c.IDCliente;

CREATE VIEW vw_ContasAbertas AS
SELECT 
    cr.IDCReceber,
    c.Nome AS Cliente,
    cr.FaturaVenda,
    cr.DataConta,
    cr.DataVencimento,
    cr.Valor,
    cr.Situacao
FROM ContasReceber cr
JOIN Cliente c ON cr.Cliente_IDCliente = c.IDCliente
WHERE cr.Situacao = '1';

CREATE VIEW vw_ContasCompleto AS
SELECT 
    c.Nome AS Cliente,
    m.Nome AS Municipio,
    e.Nome AS Estado,
    e.UF,
    cr.FaturaVenda,
    cr.DataConta,
    cr.DataVencimento,
    cr.Valor,
    cr.Situacao
FROM ContasReceber cr
JOIN Cliente c   ON cr.Cliente_IDCliente = c.IDCliente
JOIN Municipio m ON c.EndMunicipio = m.IDMunicipio
JOIN Estado e    ON m.Estado_IDEstado = e.IDEstado;

SELECT * FROM vw_Clientes;
SELECT * FROM vw_MunicipiosEstados;
SELECT * FROM vw_ContasReceber;
SELECT * FROM vw_ContasAbertas;
SELECT * FROM vw_ContasCompleto;