-- ============================================================================
-- 1. CRIAÇÃO DO BANCO DE DADOS
-- ============================================================================
CREATE DATABASE IF NOT EXISTS eco_sbs;
USE eco_sbs;

-- ============================================================================
-- 2. CRIAÇÃO DAS TABELAS COM CORREÇÕES DE FKs E CHECKS (DDL)
-- ============================================================================

CREATE TABLE Bairro (
    id_bairro INT AUTO_INCREMENT PRIMARY KEY,
    nome_bairro VARCHAR(100) NOT NULL
);

CREATE TABLE Endereco (
    id_endereco INT AUTO_INCREMENT PRIMARY KEY,
    id_bairro INT NOT NULL, 
    rua VARCHAR(150) NOT NULL,
    numero VARCHAR(10),
    complemento VARCHAR(100),
    cep CHAR(8) NOT NULL,
    CONSTRAINT fk_endereco_bairro FOREIGN KEY (id_bairro) REFERENCES Bairro(id_bairro)
);

CREATE TABLE Solicitante (
    id_solicitante INT AUTO_INCREMENT PRIMARY KEY,
    id_endereco INT NOT NULL, 
    cpf_cnpj VARCHAR(14) UNIQUE NOT NULL,
    telefone VARCHAR(15) NOT NULL,
    email VARCHAR(100) NOT NULL,
    tipo_solicitante VARCHAR(20) NOT NULL, 
    senha VARCHAR(100) NOT NULL,
    nome VARCHAR(100) NOT NULL,
    CONSTRAINT fk_solicitante_endereco FOREIGN KEY (id_endereco) REFERENCES Endereco(id_endereco),
    
    -- CHECK 1: Garante que o solicitante seja apenas pessoa física ou jurídica
    CONSTRAINT chk_tipo_solicitante CHECK (tipo_solicitante IN ('Fisica', 'Juridica')) 
);

CREATE TABLE Equipe (
    id_equipe INT AUTO_INCREMENT PRIMARY KEY,
    nome_equipe VARCHAR(100) NOT NULL,
    turno VARCHAR(20) NOT NULL
);

CREATE TABLE Motorista (
    id_motorista INT AUTO_INCREMENT PRIMARY KEY,
    id_equipe INT NOT NULL, 
    nome VARCHAR(100) NOT NULL,
    cpf CHAR(11) UNIQUE NOT NULL,
    cnh VARCHAR(20) UNIQUE NOT NULL,
    validade_cnh DATE NOT NULL,
    CONSTRAINT fk_motorista_equipe FOREIGN KEY (id_equipe) REFERENCES Equipe(id_equipe)
);

CREATE TABLE Caminhao (
    id_caminhao INT AUTO_INCREMENT PRIMARY KEY,
    placa CHAR(7) UNIQUE NOT NULL,
    modelo VARCHAR(100) NOT NULL,
    ano INT NOT NULL,
    capacidade_carga DECIMAL(10,2) NOT NULL,
    
    -- CHECK 2: A capacidade de carga do caminhão não pode ser zero ou negativa
    CONSTRAINT chk_capacidade CHECK (capacidade_carga > 0)
);

CREATE TABLE Oficina (
    id_oficina INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    telefone VARCHAR(15),
    endereco VARCHAR(150)
);

CREATE TABLE Manutencao (
    id_manutencao INT AUTO_INCREMENT PRIMARY KEY,
    id_caminhao INT NOT NULL, -- Correção: O caminhão que recebe a manutenção
    id_oficina INT NOT NULL,  -- Correção: A oficina que realiza a manutenção
    data_manutencao DATE NOT NULL,
    tipo_manutencao VARCHAR(50) NOT NULL,
    custo DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_manutencao_caminhao FOREIGN KEY (id_caminhao) REFERENCES Caminhao(id_caminhao),
    CONSTRAINT fk_manutencao_oficina FOREIGN KEY (id_oficina) REFERENCES Oficina(id_oficina)
);

CREATE TABLE Rota (
    id_rota INT AUTO_INCREMENT PRIMARY KEY,
    id_equipe INT NOT NULL, 
    data_rota DATE NOT NULL,
    quilometragem DECIMAL(10,2),
    CONSTRAINT fk_rota_equipe FOREIGN KEY (id_equipe) REFERENCES Equipe(id_equipe)
);

CREATE TABLE Solicitacao (
    id_solicitacao INT AUTO_INCREMENT PRIMARY KEY,
    id_solicitante INT NOT NULL, -- Correção apontada pelo professor: lado N fica com a chave
    id_rota INT, 
    data_solicitacao DATE NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'Pendente',
    observacao VARCHAR(200),
    CONSTRAINT fk_solicitacao_solicitante FOREIGN KEY (id_solicitante) REFERENCES Solicitante(id_solicitante),
    CONSTRAINT fk_solicitacao_rota FOREIGN KEY (id_rota) REFERENCES Rota(id_rota),
    
    -- CHECK 3: Padronização do status da solicitação
    CONSTRAINT chk_status_solicitacao CHECK (status IN ('Pendente', 'Agendada', 'Em Andamento', 'Concluida', 'Cancelada'))
);

CREATE TABLE Categoria_residuo (
    id_categoria INT AUTO_INCREMENT PRIMARY KEY,
    nome_categoria VARCHAR(100) NOT NULL
);

CREATE TABLE Tipo_residuo (
    id_tipo_residuo INT AUTO_INCREMENT PRIMARY KEY,
    id_categoria INT NOT NULL, 
    nome_tipo VARCHAR(100) NOT NULL,
    grau_periculosidade VARCHAR(30) NOT NULL,
    CONSTRAINT fk_tipo_categoria FOREIGN KEY (id_categoria) REFERENCES Categoria_residuo(id_categoria),
    
    -- CHECK 4: Classificação fixa para o grau de periculosidade dos materiais
    CONSTRAINT chk_grau_perigo CHECK (grau_periculosidade IN ('Baixo', 'Medio', 'Alto'))
);

CREATE TABLE Item_residuo (
    id_item INT AUTO_INCREMENT PRIMARY KEY,
    id_solicitacao INT NOT NULL, 
    id_tipo_residuo INT NOT NULL, 
    descricao VARCHAR(200) NOT NULL,
    quantidade DECIMAL(10,2) NOT NULL DEFAULT 1,
    CONSTRAINT fk_item_solicitacao FOREIGN KEY (id_solicitacao) REFERENCES Solicitacao(id_solicitacao),
    CONSTRAINT fk_item_tipo FOREIGN KEY (id_tipo_residuo) REFERENCES Tipo_residuo(id_tipo_residuo)
);

CREATE TABLE Destino_final (
    id_destino INT AUTO_INCREMENT PRIMARY KEY,
    nome_local VARCHAR(100) NOT NULL,
    tipo_destino VARCHAR(50) NOT NULL
);

CREATE TABLE Coleta (
    id_coleta INT AUTO_INCREMENT PRIMARY KEY,
    id_solicitacao INT NOT NULL UNIQUE, 
    id_caminhao INT NOT NULL,
    id_destino INT NOT NULL,
    data_coleta DATE NOT NULL,
    hora_coleta TIME NOT NULL,
    status_coleta VARCHAR(30) NOT NULL,
    CONSTRAINT fk_coleta_solicitacao FOREIGN KEY (id_solicitacao) REFERENCES Solicitacao(id_solicitacao),
    CONSTRAINT fk_coleta_caminhao FOREIGN KEY (id_caminhao) REFERENCES Caminhao(id_caminhao),
    CONSTRAINT fk_coleta_destino FOREIGN KEY (id_destino) REFERENCES Destino_final(id_destino)
);

CREATE TABLE Rota_caminhao (
    id_rota_caminhao INT AUTO_INCREMENT PRIMARY KEY,
    id_rota INT NOT NULL,
    id_caminhao INT NOT NULL,
    CONSTRAINT fk_rc_rota FOREIGN KEY (id_rota) REFERENCES Rota(id_rota),
    CONSTRAINT fk_rc_caminhao FOREIGN KEY (id_caminhao) REFERENCES Caminhao(id_caminhao)
);

-- ============================================================================
-- 3. PERFIS DE ACESSO (DCL)
-- ============================================================================

-- Perfil Administrativo: Permissão total no banco eco_sbs
CREATE USER 'admin_ecosbs'@'localhost' IDENTIFIED BY 'Admin@SBS2024';
GRANT ALL PRIVILEGES ON eco_sbs.* TO 'admin_ecosbs'@'localhost';

-- Perfil Operacional: Apenas leitura e inserção de dados
CREATE USER 'operador_ecosbs'@'localhost' IDENTIFIED BY 'Operador@SBS2024';
GRANT SELECT, INSERT ON eco_sbs.* TO 'operador_ecosbs'@'localhost';
FLUSH PRIVILEGES;

-- ============================================================================
-- 4. CARGA DE DADOS (DML) - Mínimo de 6 registros coerentes por tabela
-- ============================================================================

-- Bairro
INSERT INTO Bairro (nome_bairro) VALUES 
('Centro'), ('Oxford'), ('Centenário'), ('Progresso'), ('Serra Alta'), ('Cruzeiro');

-- Endereco
INSERT INTO Endereco (id_bairro, rua, numero, complemento, cep) VALUES 
(1, 'Rua Barão do Rio Branco', '120', 'Apto 2', '89280000'),
(2, 'Rua Jorge Lacerda', '450', 'Casa', '89281000'),
(3, 'Av. Argolo', '88', 'Galpão 1', '89282000'),
(4, 'Rua Carlos Gomes', '900', 'Fundos', '89283000'),
(5, 'Estrada das Neves', '1500', 'Marcenaria', '89284000'),
(6, 'Rua Augusto Wunderwald', '32', 'Casa', '89285000');

-- Solicitante
INSERT INTO Solicitante (id_endereco, cpf_cnpj, telefone, email, tipo_solicitante, senha, nome) VALUES 
(1, '11122233344', '47999991111', 'joao@email.com', 'Fisica', 'senha123', 'João da Silva'),
(2, '55566677788', '47999992222', 'maria@email.com', 'Fisica', 'senha123', 'Maria Souza'),
(3, '12345678000199', '4736330000', 'contato@moveisx.com.br', 'Juridica', 'senha123', 'Móveis X Ltda'),
(4, '99988877766', '47999993333', 'carlos@email.com', 'Fisica', 'senha123', 'Carlos Pereira'),
(5, '98765432000188', '4736331111', 'log@marcenariaz.com', 'Juridica', 'senha123', 'Marcenaria Z'),
(6, '44455566677', '47999994444', 'ana@email.com', 'Fisica', 'senha123', 'Ana Costa');

-- Equipe
INSERT INTO Equipe (nome_equipe, turno) VALUES 
('Equipe Alfa', 'Manhã'), ('Equipe Beta', 'Manhã'), ('Equipe Gama', 'Tarde'),
('Equipe Delta', 'Tarde'), ('Equipe Eco', 'Integral'), ('Equipe Rápida', 'Integral');

-- Motorista
INSERT INTO Motorista (id_equipe, nome, cpf, cnh, validade_cnh) VALUES 
(1, 'Pedro Santos', '10020030040', '12345678901', '2026-05-10'),
(2, 'Roberto Alves', '20030040050', '23456789012', '2026-11-20'),
(3, 'Fernando Lima', '30040050060', '34567890123', '2027-02-15'),
(4, 'Marcos Dias', '40050060070', '45678901234', '2026-12-01'),
(5, 'Luiz Otávio', '50060070080', '56789012345', '2026-08-30'),
(6, 'Tiago Nogueira', '60070080090', '67890123456', '2027-04-10');

-- Caminhao
INSERT INTO Caminhao (placa, modelo, ano, capacidade_carga) VALUES 
('ABC1D23', 'Ford Cargo', 2015, 5000.00), ('XYZ9E87', 'VW Worker', 2018, 6000.00),
('QWE4R56', 'MB Accelo', 2020, 4500.00), ('RTY7U89', 'Volvo VM', 2019, 7000.00),
('IOP0A12', 'Iveco Tector', 2021, 8000.00), ('LJK3F45', 'VW Delivery', 2017, 3000.00);

-- Oficina
INSERT INTO Oficina (nome, telefone, endereco) VALUES
('Oficina Mecânica São Bento', '4736331111', 'Rua dos Imigrantes, 100'),
('Auto Center Diesel', '4736332222', 'Rodovia BR-280, Km 50'),
('Mecânica do Beto', '47988887777', 'Rua Felipe Schmidt, 300'),
('Oficina Oxford', '4736334444', 'Av. São Bento, 400'),
('Centro Automotivo SC', '47999996666', 'Estrada Velha, 500'),
('Auto Peças e Serviços', '4736335555', 'Rua Capitão Ernesto Nunes, 600');

-- Manutencao
INSERT INTO Manutencao (id_caminhao, id_oficina, data_manutencao, tipo_manutencao, custo) VALUES
(1, 1, '2026-06-01', 'Troca de Óleo', 500.00),
(2, 2, '2026-06-05', 'Revisão Sistema de Freios', 1200.00),
(3, 3, '2026-06-10', 'Troca de Pneus Traseiros', 3000.00),
(4, 4, '2026-06-15', 'Retífica do Motor', 5000.00),
(5, 5, '2026-06-20', 'Manutenção na Suspensão', 1500.00),
(6, 6, '2026-06-25', 'Reparo no Sistema Elétrico', 800.00);

-- Rota
INSERT INTO Rota (id_equipe, data_rota, quilometragem) VALUES 
(1, '2026-07-01', 45.5), (2, '2026-07-01', 30.0), (3, '2026-07-02', 50.2),
(4, '2026-07-02', 25.4), (5, '2026-07-03', 60.1), (6, '2026-07-03', 40.8);

-- Categoria_residuo
INSERT INTO Categoria_residuo (nome_categoria) VALUES 
('Madeira Processada'), ('Madeira Bruta'), ('Móveis Residenciais'),
('Móveis de Escritório'), ('Eletrodomésticos Grandes'), ('Sucata Metálica');

-- Tipo_residuo
INSERT INTO Tipo_residuo (id_categoria, nome_tipo, grau_periculosidade) VALUES 
(1, 'Sobras de MDF', 'Baixo'), (1, 'Serragem Pura', 'Baixo'),
(3, 'Sofá Velho', 'Baixo'), (3, 'Colchão', 'Baixo'),
(5, 'Geladeira Quebrada', 'Medio'), (1, 'Madeira com Verniz', 'Medio');

-- Solicitacao
INSERT INTO Solicitacao (id_solicitante, id_rota, data_solicitacao, status, observacao) VALUES 
(1, 1, '2026-06-28', 'Concluida', 'Sofá na calçada'),
(2, 2, '2026-06-29', 'Concluida', 'Geladeira velha'),
(3, 3, '2026-06-30', 'Agendada', 'Serragem da fábrica'),
(4, 4, '2026-06-30', 'Agendada', 'Guarda-roupa desmontado'),
(5, 5, '2026-07-01', 'Pendente', 'Restos de MDF cortado'),
(6, NULL, '2026-07-02', 'Pendente', 'Cama quebrada');

-- Item_residuo
INSERT INTO Item_residuo (id_solicitacao, id_tipo_residuo, descricao, quantidade) VALUES 
(1, 3, 'Sofá de 3 lugares rasgado', 1), (2, 5, 'Geladeira enferrujada', 1),
(3, 2, 'Sacos de 50kg de serragem', 10), (4, 1, 'Placas de MDF do guarda-roupa', 15),
(5, 1, 'Retalhos de MDF', 50), (6, 3, 'Cama de casal de madeira', 1);

-- Destino_final
INSERT INTO Destino_final (nome_local, tipo_destino) VALUES 
('Aterro Sanitário SBS', 'Aterro'), ('Cooperativa Recicla Tudo', 'Reciclagem'),
('Usina de Biomassa', 'Queima Controlada'), ('Ecoponto Centro', 'Ecoponto'),
('Ecoponto Oxford', 'Ecoponto'), ('Marcenaria Reaproveitamento', 'Reuso');

-- Coleta
INSERT INTO Coleta (id_solicitacao, id_caminhao, id_destino, data_coleta, hora_coleta, status_coleta) VALUES 
(1, 1, 4, '2026-07-01', '08:30:00', 'Finalizada'), (2, 2, 5, '2026-07-01', '10:00:00', 'Finalizada'),
(3, 3, 3, '2026-07-02', '14:00:00', 'Agendada'), (4, 4, 4, '2026-07-02', '15:30:00', 'Agendada'),
(5, 5, 2, '2026-07-03', '09:00:00', 'Programada'), (6, 6, 6, '2026-07-04', '11:00:00', 'Programada');

-- Rota_caminhao
INSERT INTO Rota_caminhao (id_rota, id_caminhao) VALUES 
(1, 1), 
(1, 2), -- A rota 1 vai utilizar dois caminhões (1 e 2)
(2, 3), 
(3, 4), 
(3, 5), -- A rota 3 também vai utilizar dois caminhões (4 e 5)
(4, 6);