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


-- WhatsApp + automações
CREATE TABLE IF NOT EXISTS whatsapp_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    provider_name VARCHAR(100) NOT NULL DEFAULT 'Webhook genérico',
    webhook_url VARCHAR(255) NULL,
    auth_token VARCHAR(255) NULL,
    webhook_token VARCHAR(255) NULL,
    telefone_remetente VARCHAR(30) NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 0,
    auto_notify_new TINYINT(1) NOT NULL DEFAULT 1,
    auto_notify_status TINYINT(1) NOT NULL DEFAULT 1,
    auto_notify_sla TINYINT(1) NOT NULL DEFAULT 1,
    auto_create_from_webhook TINYINT(1) NOT NULL DEFAULT 1,
    template_nova_ocorrencia TEXT NULL,
    template_status TEXT NULL,
    template_sla TEXT NULL,
    updated_em DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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;

INSERT INTO whatsapp_config (
    provider_name, webhook_url, auth_token, webhook_token, telefone_remetente, ativo,
    auto_notify_new, auto_notify_status, auto_notify_sla, auto_create_from_webhook,
    template_nova_ocorrencia, template_status, template_sla, updated_em
)
SELECT
    'Webhook genérico', '', '', '', '', 0,
    1, 1, 1, 1,
    'Olá {nome}, sua solicitação foi registrada com o protocolo {protocolo}. Serviço: {servico}. Acompanhe em {link_consulta}',
    'Olá {nome}, o protocolo {protocolo} foi atualizado para {status}. Serviço: {servico}.',
    'Alerta: o protocolo {protocolo} está {sla_label}. Serviço: {servico}.',
    NOW()
WHERE NOT EXISTS (SELECT 1 FROM whatsapp_config);
