CREATE DATABASE IF NOT EXISTS portal_transparencia DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE portal_transparencia;

CREATE TABLE usuarios (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  senha_hash VARCHAR(255) NOT NULL,
  perfil ENUM('admin','editor','leitor') DEFAULT 'admin',
  status TINYINT(1) DEFAULT 1,
  criado_em DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cat_receita (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  status TINYINT(1) DEFAULT 1
);

CREATE TABLE cat_despesa (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  status TINYINT(1) DEFAULT 1
);

CREATE TABLE receitas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data DATE NOT NULL,
  categoria_id INT,
  subcategoria VARCHAR(120),
  descricao TEXT,
  fonte VARCHAR(120),
  valor DECIMAL(14,2) NOT NULL,
  documento VARCHAR(255),
  criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX(data),
  CONSTRAINT fk_receita_cat FOREIGN KEY (categoria_id) REFERENCES cat_receita(id)
);

CREATE TABLE despesas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data DATE NOT NULL,
  orgao VARCHAR(160),
  categoria_id INT,
  descricao TEXT,
  favorecido VARCHAR(160),
  cnpj VARCHAR(20),
  valor_empenho DECIMAL(14,2) DEFAULT 0,
  valor_liquidado DECIMAL(14,2) DEFAULT 0,
  valor_pago DECIMAL(14,2) DEFAULT 0,
  documento VARCHAR(255),
  criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX(data), INDEX(cnpj),
  CONSTRAINT fk_despesa_cat FOREIGN KEY (categoria_id) REFERENCES cat_despesa(id)
);

CREATE TABLE licitacoes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  modalidade VARCHAR(60) NOT NULL,
  numero VARCHAR(60) NOT NULL,
  objeto TEXT NOT NULL,
  data_abertura DATETIME NOT NULL,
  situacao ENUM('aberta','andamento','homologada','fracassada','revogada','suspensa','cancelada') DEFAULT 'andamento',
  edital_pdf VARCHAR(255),
  outros_anexos TEXT,
  link_sessao_publica VARCHAR(255),
  criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_mod_num (modalidade, numero)
);

CREATE TABLE contratos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  numero VARCHAR(60) NOT NULL,
  processo VARCHAR(60),
  fornecedor VARCHAR(160) NOT NULL,
  cnpj VARCHAR(20),
  objeto TEXT NOT NULL,
  inicio DATE,
  fim DATE,
  valor_total DECIMAL(14,2) NOT NULL,
  pdf VARCHAR(255),
  criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_numero (numero),
  INDEX(cnpj)
);

CREATE TABLE servidores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(160) NOT NULL,
  cargo VARCHAR(120),
  lotacao VARCHAR(120),
  mes_referencia CHAR(7) NOT NULL COMMENT 'YYYY-MM',
  remuneracao_bruta DECIMAL(14,2) DEFAULT 0,
  descontos DECIMAL(14,2) DEFAULT 0,
  remuneracao_liquida DECIMAL(14,2) AS (remuneracao_bruta - descontos) STORED,
  folha_pdf VARCHAR(255),
  criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX(mes_referencia), INDEX(cargo)
);

CREATE TABLE diarias (
  id INT AUTO_INCREMENT PRIMARY KEY,
  beneficiario VARCHAR(160) NOT NULL,
  cargo VARCHAR(120),
  destino VARCHAR(160),
  motivo TEXT,
  data_inicio DATE,
  data_fim DATE,
  valor_total DECIMAL(14,2) NOT NULL,
  portaria_pdf VARCHAR(255),
  criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX(data_inicio), INDEX(data_fim)
);

CREATE TABLE auditoria (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  usuario_id INT,
  acao VARCHAR(40),
  tabela VARCHAR(60),
  registro_id VARCHAR(60),
  ip VARCHAR(45),
  criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX(usuario_id)
);
