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 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)
);

CREATE TABLE IF NOT EXISTS whatsapp_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    direction ENUM('IN','OUT') NOT NULL DEFAULT 'OUT',
    evento VARCHAR(50) NOT NULL,
    telefone VARCHAR(30) NULL,
    protocolo VARCHAR(30) NULL,
    payload_json LONGTEXT NULL,
    response_json LONGTEXT NULL,
    status_code INT NOT NULL DEFAULT 0,
    sucesso TINYINT(1) NOT NULL DEFAULT 0,
    mensagem VARCHAR(255) NULL,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_whatsapp_logs_evento (evento),
    INDEX idx_whatsapp_logs_protocolo (protocolo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS whatsapp_dispatches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ocorrencia_id INT NOT NULL,
    gatilho VARCHAR(50) NOT NULL,
    referencia VARCHAR(120) NOT NULL,
    telefone VARCHAR(30) NULL,
    mensagem TEXT NULL,
    payload_json LONGTEXT NULL,
    response_json LONGTEXT NULL,
    status_code INT NOT NULL DEFAULT 0,
    sucesso TINYINT(1) NOT NULL DEFAULT 0,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_whatsapp_dispatch (ocorrencia_id, gatilho, referencia),
    CONSTRAINT fk_whatsapp_dispatch_ocorrencia FOREIGN KEY (ocorrencia_id) REFERENCES ocorrencias(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
