-- Gabinete Digital Verci - Schema MySQL
-- PHP 8.4 | Cadastro Único orientado a demandas

CREATE DATABASE IF NOT EXISTS verci
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE verci;

-- ---------------------------------------------------------------------------
-- Usuários do sistema (responsáveis pelas demandas)
-- ---------------------------------------------------------------------------
CREATE TABLE usuarios (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome            VARCHAR(150) NOT NULL,
    email           VARCHAR(150) NOT NULL UNIQUE,
    senha_hash      VARCHAR(255) NOT NULL,
    ativo           TINYINT(1) NOT NULL DEFAULT 1,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Origens de indicação (lista configurável)
-- ---------------------------------------------------------------------------
CREATE TABLE origens_indicacao (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome            VARCHAR(100) NOT NULL UNIQUE,
    exige_indicador TINYINT(1) NOT NULL DEFAULT 0,
    ativo           TINYINT(1) NOT NULL DEFAULT 1,
    ordem           SMALLINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;

INSERT INTO origens_indicacao (nome, exige_indicador, ordem) VALUES
    ('Atendimento Presencial', 0, 1),
    ('WhatsApp', 0, 2),
    ('Telefone', 0, 3),
    ('Evento', 0, 4),
    ('Rede Social', 0, 5),
    ('Site', 0, 6),
    ('Visita', 0, 7),
    ('Indicação de Apoiador', 1, 8),
    ('Outra', 0, 9);

-- ---------------------------------------------------------------------------
-- Cadastro Único do Cidadão
-- ---------------------------------------------------------------------------
CREATE TABLE cidadaos (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    -- Dados pessoais
    nome_completo           VARCHAR(200) NOT NULL,
    cpf                     CHAR(11) NULL UNIQUE,
    rg                      VARCHAR(20) NULL,
    data_nascimento         DATE NULL,
    sexo                    ENUM('M', 'F', 'O', 'N') NULL COMMENT 'M=Masculino, F=Feminino, O=Outro, N=Não informado',

    -- Contato
    telefone                VARCHAR(20) NULL,
    whatsapp                VARCHAR(20) NULL,
    email                   VARCHAR(150) NULL,

    -- Endereço
    cep                     CHAR(8) NULL,
    logradouro              VARCHAR(200) NULL,
    numero                  VARCHAR(20) NULL,
    complemento             VARCHAR(100) NULL,
    bairro                  VARCHAR(100) NULL,
    cidade                  VARCHAR(100) NULL,
    estado                  CHAR(2) NULL,
    endereco_completo       VARCHAR(400) NULL,

    -- Georreferenciamento
    latitude                DECIMAL(10, 8) NULL,
    longitude               DECIMAL(11, 8) NULL,
    geocod_status           ENUM('pendente', 'sucesso', 'falha', 'parcial') NOT NULL DEFAULT 'pendente',
    geocodificado_em        DATETIME NULL,

    -- Dados eleitorais
    zona_eleitoral          VARCHAR(10) NULL,
    secao_eleitoral         VARCHAR(10) NULL,

    -- Redes sociais
    instagram               VARCHAR(100) NULL,
    facebook                VARCHAR(150) NULL,
    tiktok                  VARCHAR(100) NULL,

    -- Relacionamento
    origem_indicacao_id     INT UNSIGNED NULL,
    indicado_por_cidadao_id INT UNSIGNED NULL,
    eh_apoiador             TINYINT(1) NOT NULL DEFAULT 0,

    created_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_cidadao_origem
        FOREIGN KEY (origem_indicacao_id) REFERENCES origens_indicacao(id),
    CONSTRAINT fk_cidadao_indicador
        FOREIGN KEY (indicado_por_cidadao_id) REFERENCES cidadaos(id),

    INDEX idx_cidadao_nome (nome_completo),
    INDEX idx_cidadao_telefone (telefone),
    INDEX idx_cidadao_whatsapp (whatsapp),
    INDEX idx_cidadao_bairro (bairro),
    INDEX idx_cidadao_zona (zona_eleitoral),
    INDEX idx_cidadao_apoiador (eh_apoiador),
    INDEX idx_cidadao_geo (latitude, longitude)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Categorias e subcategorias de demanda
-- ---------------------------------------------------------------------------
CREATE TABLE categorias_demanda (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome        VARCHAR(100) NOT NULL UNIQUE,
    ativo       TINYINT(1) NOT NULL DEFAULT 1,
    ordem       SMALLINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE subcategorias_demanda (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    categoria_id    INT UNSIGNED NOT NULL,
    nome            VARCHAR(100) NOT NULL,
    ativo           TINYINT(1) NOT NULL DEFAULT 1,
    ordem           SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    CONSTRAINT fk_subcat_categoria
        FOREIGN KEY (categoria_id) REFERENCES categorias_demanda(id),
    UNIQUE KEY uk_subcat (categoria_id, nome)
) ENGINE=InnoDB;

INSERT INTO categorias_demanda (nome, ordem) VALUES
    ('Infraestrutura', 1),
    ('Saúde', 2),
    ('Educação', 3),
    ('Assistência Social', 4),
    ('Outros', 99);

INSERT INTO subcategorias_demanda (categoria_id, nome, ordem)
SELECT id, 'Geral', 1 FROM categorias_demanda;

-- ---------------------------------------------------------------------------
-- Demandas
-- ---------------------------------------------------------------------------
CREATE TABLE demandas (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cidadao_id          INT UNSIGNED NOT NULL,
    categoria_id        INT UNSIGNED NOT NULL,
    subcategoria_id     INT UNSIGNED NULL,
    assunto             VARCHAR(200) NOT NULL,
    descricao           TEXT NOT NULL,
    prioridade          ENUM('baixa', 'media', 'alta', 'urgente') NOT NULL DEFAULT 'media',
    responsavel_id      INT UNSIGNED NULL,
    prazo               DATE NULL,
    status              ENUM('pendente', 'em_andamento', 'concluida') NOT NULL DEFAULT 'pendente',
    observacoes         TEXT NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_demanda_cidadao
        FOREIGN KEY (cidadao_id) REFERENCES cidadaos(id),
    CONSTRAINT fk_demanda_categoria
        FOREIGN KEY (categoria_id) REFERENCES categorias_demanda(id),
    CONSTRAINT fk_demanda_subcategoria
        FOREIGN KEY (subcategoria_id) REFERENCES subcategorias_demanda(id),
    CONSTRAINT fk_demanda_responsavel
        FOREIGN KEY (responsavel_id) REFERENCES usuarios(id),

    INDEX idx_demanda_status (status),
    INDEX idx_demanda_cidadao (cidadao_id),
    INDEX idx_demanda_prazo (prazo)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Anexos de demanda
-- ---------------------------------------------------------------------------
CREATE TABLE demanda_anexos (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    demanda_id      INT UNSIGNED NOT NULL,
    nome_original   VARCHAR(255) NOT NULL,
    caminho         VARCHAR(500) NOT NULL,
    mime_type       VARCHAR(100) NULL,
    tamanho_bytes   INT UNSIGNED NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_anexo_demanda
        FOREIGN KEY (demanda_id) REFERENCES demandas(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Histórico de movimentações da demanda
-- ---------------------------------------------------------------------------
CREATE TABLE demanda_historico (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    demanda_id      INT UNSIGNED NOT NULL,
    usuario_id      INT UNSIGNED NULL,
    status_anterior VARCHAR(30) NULL,
    status_novo     VARCHAR(30) NOT NULL,
    descricao       TEXT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_hist_demanda
        FOREIGN KEY (demanda_id) REFERENCES demandas(id) ON DELETE CASCADE,
    CONSTRAINT fk_hist_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Linha do tempo do cidadão
-- ---------------------------------------------------------------------------
CREATE TABLE cidadao_timeline (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cidadao_id      INT UNSIGNED NOT NULL,
    tipo            ENUM(
                        'cadastro',
                        'demanda',
                        'alteracao_cadastral',
                        'atendimento',
                        'visita',
                        'comentario',
                        'arquivo',
                        'encaminhamento',
                        'conclusao'
                    ) NOT NULL,
    titulo          VARCHAR(200) NOT NULL,
    descricao       TEXT NULL,
    referencia_tipo VARCHAR(50) NULL COMMENT 'demanda, anexo, etc.',
    referencia_id   INT UNSIGNED NULL,
    usuario_id      INT UNSIGNED NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_timeline_cidadao
        FOREIGN KEY (cidadao_id) REFERENCES cidadaos(id) ON DELETE CASCADE,
    CONSTRAINT fk_timeline_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id),
    INDEX idx_timeline_cidadao (cidadao_id, created_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Auditoria de alterações cadastrais
-- ---------------------------------------------------------------------------
CREATE TABLE cidadao_auditoria (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cidadao_id      INT UNSIGNED NOT NULL,
    usuario_id      INT UNSIGNED NULL,
    campo           VARCHAR(100) NOT NULL,
    valor_anterior  TEXT NULL,
    valor_novo      TEXT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_audit_cidadao
        FOREIGN KEY (cidadao_id) REFERENCES cidadaos(id) ON DELETE CASCADE,
    CONSTRAINT fk_audit_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id),
    INDEX idx_audit_cidadao (cidadao_id, created_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Colaboradores (equipe de campo / visitas)
-- ---------------------------------------------------------------------------
CREATE TABLE colaboradores (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome            VARCHAR(150) NOT NULL,
    cpf             CHAR(11) NULL,
    telefone        VARCHAR(20) NULL,
    email           VARCHAR(150) NOT NULL UNIQUE,
    senha_hash      VARCHAR(255) NOT NULL,
    funcao          VARCHAR(100) NULL,
    ativo           TINYINT(1) NOT NULL DEFAULT 1,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Rotas de visita
-- ---------------------------------------------------------------------------
CREATE TABLE rotas (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    titulo          VARCHAR(200) NOT NULL,
    descricao       TEXT NULL,
    responsavel_id  INT UNSIGNED NULL,
    colaborador_id  INT UNSIGNED NULL,
    data_planejada  DATE NULL,
    status          ENUM('rascunho', 'planejada', 'em_andamento', 'concluida', 'cancelada') NOT NULL DEFAULT 'rascunho',
    created_by      INT UNSIGNED NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_rota_responsavel FOREIGN KEY (responsavel_id) REFERENCES usuarios(id),
    CONSTRAINT fk_rota_colaborador FOREIGN KEY (colaborador_id) REFERENCES colaboradores(id),
    CONSTRAINT fk_rota_criador FOREIGN KEY (created_by) REFERENCES usuarios(id)
) ENGINE=InnoDB;

CREATE TABLE rota_paradas (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rota_id         INT UNSIGNED NOT NULL,
    cidadao_id      INT UNSIGNED NOT NULL,
    ordem           SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    observacao      TEXT NULL,
    status          ENUM('pendente', 'visitado', 'nao_localizado', 'cancelado') NOT NULL DEFAULT 'pendente',
    visitado_em     DATETIME NULL,
    CONSTRAINT fk_parada_rota FOREIGN KEY (rota_id) REFERENCES rotas(id) ON DELETE CASCADE,
    CONSTRAINT fk_parada_cidadao FOREIGN KEY (cidadao_id) REFERENCES cidadaos(id),
    UNIQUE KEY uk_rota_cidadao (rota_id, cidadao_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Usuário admin padrão (senha: admin123 — alterar em produção)
-- ---------------------------------------------------------------------------
INSERT INTO usuarios (nome, email, senha_hash) VALUES
    ('Administrador', 'admin@verci.local', '$2y$12$SvXTRhCr.bjYAuGVNHriv.yUUwg4S9isXh9Px1DKi3O1vWrl.NaBK');
