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

CREATE TABLE setores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    descricao VARCHAR(255) NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categorias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    icone VARCHAR(100) NULL,
    cor VARCHAR(20) NULL,
    sla_dias INT NOT NULL DEFAULT 3,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    senha VARCHAR(255) NOT NULL,
    perfil ENUM('ADMIN','TRIAGEM','SETOR','GESTOR') NOT NULL,
    setor_id INT NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    pode_visualizar_ocorrencias TINYINT(1) NOT NULL DEFAULT 1,
    visualiza_todas_ocorrencias TINYINT(1) NOT NULL DEFAULT 1,
    pode_editar_ocorrencias TINYINT(1) NOT NULL DEFAULT 1,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME NULL,
    CONSTRAINT fk_usuarios_setor FOREIGN KEY (setor_id) REFERENCES setores(id)
);

CREATE TABLE ocorrencias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    protocolo VARCHAR(30) NOT NULL UNIQUE,
    categoria_id INT NOT NULL,
    descricao TEXT NOT NULL,
    latitude DECIMAL(10,8) NOT NULL,
    longitude DECIMAL(11,8) NOT NULL,
    endereco_referencia VARCHAR(255) NULL,
    cidadao_nome VARCHAR(150) NULL,
    cidadao_telefone VARCHAR(30) NULL,
    cidadao_email VARCHAR(150) NULL,
    anonima TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('ABERTA','RECEBIDA','EM_ANALISE','ENCAMINHADA','EM_ATENDIMENTO','CONCLUIDA','NAO_PROCEDE','DUPLICADA') NOT NULL DEFAULT 'ABERTA',
    setor_id INT NULL,
    usuario_responsavel_id INT NULL,
    imagem VARCHAR(255) NULL,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME NULL,
    concluido_em DATETIME NULL,
    CONSTRAINT fk_ocorrencias_categoria FOREIGN KEY (categoria_id) REFERENCES categorias(id),
    CONSTRAINT fk_ocorrencias_setor FOREIGN KEY (setor_id) REFERENCES setores(id),
    CONSTRAINT fk_ocorrencias_usuario FOREIGN KEY (usuario_responsavel_id) REFERENCES usuarios(id)
);

CREATE TABLE usuario_servicos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT NOT NULL,
    categoria_id INT NOT NULL,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_usuario_servico (usuario_id, categoria_id),
    CONSTRAINT fk_usuario_servicos_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    CONSTRAINT fk_usuario_servicos_categoria FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE CASCADE
);

CREATE TABLE ocorrencia_historico (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ocorrencia_id INT NOT NULL,
    usuario_id INT NULL,
    status_anterior VARCHAR(50) NULL,
    status_novo VARCHAR(50) NOT NULL,
    observacao TEXT NULL,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_hist_ocorrencia FOREIGN KEY (ocorrencia_id) REFERENCES ocorrencias(id) ON DELETE CASCADE,
    CONSTRAINT fk_hist_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
);
