-- Crie o banco e selecione
CREATE DATABASE IF NOT EXISTS indicacoes CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE indicacoes;

-- Setores (ex.: Jurídico)
CREATE TABLE IF NOT EXISTS setores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  criado_em DATETIME NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS orgaos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(160) NOT NULL,
  responsavel VARCHAR(160) NULL,
  ativo TINYINT(1) NOT NULL DEFAULT 1,
  criado_em DATETIME NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tipos_solicitacao (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(160) NOT NULL,
  ativo TINYINT(1) NOT NULL DEFAULT 1,
  criado_em DATETIME NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS solicitacoes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tema VARCHAR(220) NOT NULL,
  bairro VARCHAR(160) NOT NULL,
  data_solicitacao DATE NOT NULL,
  autor_id INT NOT NULL,
  tipo_id INT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'aberta', -- aberta|pendente|aprovada|recusada|cancelada
  descricao MEDIUMTEXT NULL,
  criado_em DATETIME NULL,
  CONSTRAINT fk_sol_autor FOREIGN KEY (autor_id) REFERENCES usuarios(id) ON DELETE RESTRICT,
  CONSTRAINT fk_sol_tipo  FOREIGN KEY (tipo_id)  REFERENCES tipos_solicitacao(id) ON DELETE RESTRICT,
  FULLTEXT KEY ft_tema_desc (tema, descricao)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS solicitacao_orgaos (
  solicitacao_id INT NOT NULL,
  orgao_id INT NOT NULL,
  PRIMARY KEY (solicitacao_id, orgao_id),
  CONSTRAINT fk_so_sol FOREIGN KEY (solicitacao_id) REFERENCES solicitacoes(id) ON DELETE CASCADE,
  CONSTRAINT fk_so_org FOREIGN KEY (orgao_id) REFERENCES orgaos(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS solicitacao_subscritos (
  solicitacao_id INT NOT NULL,
  autor_id INT NOT NULL,
  PRIMARY KEY (solicitacao_id, autor_id),
  CONSTRAINT fk_ss_sol FOREIGN KEY (solicitacao_id) REFERENCES solicitacoes(id) ON DELETE CASCADE,
  CONSTRAINT fk_ss_aut FOREIGN KEY (autor_id) REFERENCES usuarios(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- Seeds
INSERT INTO setores (nome, criado_em) VALUES ('Jurídico', NOW()) ON DUPLICATE KEY UPDATE nome=nome;
