CREATE DATABASE IF NOT EXISTS viap_camara CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE viap_camara;

CREATE TABLE entidade (
 id INT AUTO_INCREMENT PRIMARY KEY, nome VARCHAR(180) NOT NULL, nome_curto VARCHAR(100), cnpj VARCHAR(20), endereco VARCHAR(180), numero VARCHAR(20), bairro VARCHAR(100), cidade VARCHAR(100), uf CHAR(2), cep VARCHAR(10), telefone VARCHAR(30), whatsapp VARCHAR(30), email VARCHAR(150), site VARCHAR(180), instagram VARCHAR(180), facebook VARCHAR(180), youtube VARCHAR(180), twitter VARCHAR(180), presidente_nome VARCHAR(180), presidente_cargo VARCHAR(120) DEFAULT 'Vereador - Presidente', lei_viap VARCHAR(180), logo VARCHAR(255), brasao VARCHAR(255), cor_primaria VARCHAR(10) DEFAULT '#0b3b70', cor_secundaria VARCHAR(10) DEFAULT '#d4a017', criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE vereadores (id INT AUTO_INCREMENT PRIMARY KEY,nome VARCHAR(150) NOT NULL,cpf VARCHAR(20),cargo VARCHAR(100) DEFAULT 'Vereador(a)',email VARCHAR(150),telefone VARCHAR(30),ativo TINYINT(1) DEFAULT 1,criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE credores (id INT AUTO_INCREMENT PRIMARY KEY,razao_social VARCHAR(180) NOT NULL,nome_fantasia VARCHAR(180),tipo ENUM('PF','PJ') DEFAULT 'PJ',cpf_cnpj VARCHAR(20) NOT NULL,endereco VARCHAR(180),cidade VARCHAR(100),uf CHAR(2),telefone VARCHAR(30),email VARCHAR(150),observacoes TEXT,ativo TINYINT(1) DEFAULT 1,criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_credor_doc(cpf_cnpj)) ENGINE=InnoDB;
CREATE TABLE categorias_despesa (id INT AUTO_INCREMENT PRIMARY KEY,codigo VARCHAR(20),nome VARCHAR(150) NOT NULL,descricao TEXT,limite_mensal DECIMAL(12,2) NULL,exige_nota TINYINT(1) DEFAULT 1,exige_anexo TINYINT(1) DEFAULT 1,ativo TINYINT(1) DEFAULT 1) ENGINE=InnoDB;
CREATE TABLE competencias (id INT AUTO_INCREMENT PRIMARY KEY,mes TINYINT NOT NULL,ano SMALLINT NOT NULL,limite_padrao DECIMAL(12,2) DEFAULT 0,status ENUM('aberta','fechada') DEFAULT 'aberta', UNIQUE KEY uq_comp(mes,ano)) ENGINE=InnoDB;
CREATE TABLE prestacoes (id INT AUTO_INCREMENT PRIMARY KEY,vereador_id INT NOT NULL,competencia_id INT NOT NULL,protocolo VARCHAR(60),limite DECIMAL(12,2) DEFAULT 0,status ENUM('aberta','fechada') DEFAULT 'aberta',fechada_em DATETIME NULL,criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_prestacao(vereador_id,competencia_id),FOREIGN KEY(vereador_id) REFERENCES vereadores(id),FOREIGN KEY(competencia_id) REFERENCES competencias(id)) ENGINE=InnoDB;
CREATE TABLE despesas (id INT AUTO_INCREMENT PRIMARY KEY,prestacao_id INT NOT NULL,credor_id INT NOT NULL,categoria_id INT NOT NULL,tipo_documento VARCHAR(60) DEFAULT 'Nota Fiscal',numero_documento VARCHAR(80) NOT NULL,numero_normalizado VARCHAR(80) NOT NULL,data_documento DATE NOT NULL,descricao TEXT,valor DECIMAL(12,2) NOT NULL,arquivo VARCHAR(255),status ENUM('pendente','aprovada','glosada') DEFAULT 'pendente',criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(prestacao_id) REFERENCES prestacoes(id) ON DELETE CASCADE,FOREIGN KEY(credor_id) REFERENCES credores(id),FOREIGN KEY(categoria_id) REFERENCES categorias_despesa(id),INDEX idx_duplicidade(credor_id,numero_normalizado,valor)) ENGINE=InnoDB;
CREATE TABLE auditoria (id BIGINT AUTO_INCREMENT PRIMARY KEY,usuario_id INT NULL,acao VARCHAR(100) NOT NULL,tabela_nome VARCHAR(80),registro_id INT,detalhes TEXT,ip VARCHAR(45),criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
INSERT INTO categorias_despesa(codigo,nome) VALUES ('01','Combustíveis e lubrificantes'),('02','Alimentação'),('03','Hospedagem'),('04','Passagens'),('05','Locação de veículos'),('06','Serviços gráficos'),('07','Material de expediente'),('08','Comunicação'),('09','Telefonia e internet'),('10','Consultoria / assessoria'),('11','Divulgação da atividade parlamentar'),('12','Manutenção de escritório'),('13','Outras despesas permitidas');
