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

CREATE TABLE servicos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(150) NOT NULL,
    descricao TEXT NULL,
    taxa_inicial DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    exige_pagamento TINYINT(1) NOT NULL DEFAULT 1,
    max_correcoes INT NOT NULL DEFAULT 0,
    correcoes_para_nova_taxa INT NOT NULL DEFAULT 0,
    texto_protocolo TEXT NULL,
    texto_deferimento TEXT NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    criado_em DATETIME NOT NULL,
    atualizado_em DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE servico_documentos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    servico_id INT UNSIGNED NOT NULL,
    nome_documento VARCHAR(150) NOT NULL,
    obrigatorio TINYINT(1) NOT NULL DEFAULT 1,
    ordem INT NOT NULL DEFAULT 1,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    CONSTRAINT fk_servico_documentos_servico FOREIGN KEY (servico_id) REFERENCES servicos(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE requerimentos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    numero_protocolo VARCHAR(20) NOT NULL UNIQUE,
    usuario_id INT UNSIGNED NOT NULL,
    servico_id INT UNSIGNED NOT NULL,
    assunto VARCHAR(180) NOT NULL,
    descricao TEXT NULL,
    inscricao_imobiliaria VARCHAR(80) NULL,
    endereco_obra VARCHAR(180) NULL,
    status ENUM(
        'RASCUNHO',
        'PROTOCOLADO',
        'EM_ANALISE_TRIBUTOS',
        'AGUARDANDO_PAGAMENTO',
        'PAGAMENTO_EM_CONFERENCIA',
        'PAGO',
        'EM_ANALISE_ENGENHARIA',
        'EM_EXIGENCIA',
        'REENVIADO_PELO_REQUERENTE',
        'AGUARDANDO_NOVA_TAXA',
        'DEFERIDO',
        'INDEFERIDO',
        'ENCERRADO'
    ) NOT NULL DEFAULT 'PROTOCOLADO',
    quantidade_correcoes INT NOT NULL DEFAULT 0,
    taxa_regerada TINYINT(1) NOT NULL DEFAULT 0,
    criado_em DATETIME NOT NULL,
    atualizado_em DATETIME NOT NULL,
    CONSTRAINT fk_requerimentos_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id),
    CONSTRAINT fk_requerimentos_servico FOREIGN KEY (servico_id) REFERENCES servicos(id)
) ENGINE=InnoDB;

CREATE TABLE requerimento_anexos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    requerimento_id INT UNSIGNED NOT NULL,
    nome_original VARCHAR(255) NOT NULL,
    arquivo VARCHAR(255) NOT NULL,
    tipo_arquivo VARCHAR(100) NOT NULL,
    tamanho BIGINT NOT NULL DEFAULT 0,
    enviado_por INT UNSIGNED NOT NULL,
    criado_em DATETIME NOT NULL,
    CONSTRAINT fk_anexos_requerimento FOREIGN KEY (requerimento_id) REFERENCES requerimentos(id) ON DELETE CASCADE,
    CONSTRAINT fk_anexos_enviado_por FOREIGN KEY (enviado_por) REFERENCES usuarios(id)
) ENGINE=InnoDB;

CREATE TABLE movimentacoes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    requerimento_id INT UNSIGNED NOT NULL,
    usuario_id INT UNSIGNED NOT NULL,
    status_origem VARCHAR(60) NULL,
    status_destino VARCHAR(60) NOT NULL,
    observacao TEXT NULL,
    criado_em DATETIME NOT NULL,
    CONSTRAINT fk_mov_requerimento FOREIGN KEY (requerimento_id) REFERENCES requerimentos(id) ON DELETE CASCADE,
    CONSTRAINT fk_mov_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB;

CREATE TABLE exigencias (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    requerimento_id INT UNSIGNED NOT NULL,
    descricao TEXT NOT NULL,
    respondida TINYINT(1) NOT NULL DEFAULT 0,
    data_limite DATE NULL,
    criado_por INT UNSIGNED NOT NULL,
    criado_em DATETIME NOT NULL,
    respondido_em DATETIME NULL,
    CONSTRAINT fk_exigencias_requerimento FOREIGN KEY (requerimento_id) REFERENCES requerimentos(id) ON DELETE CASCADE,
    CONSTRAINT fk_exigencias_criado_por FOREIGN KEY (criado_por) REFERENCES usuarios(id)
) ENGINE=InnoDB;

CREATE TABLE pagamentos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    requerimento_id INT UNSIGNED NOT NULL,
    tipo_pagamento ENUM('GUIA', 'PIX') NOT NULL DEFAULT 'GUIA',
    numero_guia VARCHAR(60) NULL,
    codigo_pix TEXT NULL,
    valor DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    arquivo_guia VARCHAR(255) NULL,
    status_pagamento ENUM('GERADO', 'AGUARDANDO', 'PAGO', 'CONFIRMADO', 'CANCELADO') NOT NULL DEFAULT 'GERADO',
    gerado_por INT UNSIGNED NOT NULL,
    gerado_em DATETIME NOT NULL,
    pago_em DATETIME NULL,
    confirmado_por INT UNSIGNED NULL,
    confirmado_em DATETIME NULL,
    CONSTRAINT fk_pag_requerimento FOREIGN KEY (requerimento_id) REFERENCES requerimentos(id) ON DELETE CASCADE,
    CONSTRAINT fk_pag_gerado_por FOREIGN KEY (gerado_por) REFERENCES usuarios(id),
    CONSTRAINT fk_pag_confirmado_por FOREIGN KEY (confirmado_por) REFERENCES usuarios(id)
) ENGINE=InnoDB;

CREATE TABLE pareceres (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    requerimento_id INT UNSIGNED NOT NULL,
    usuario_id INT UNSIGNED NOT NULL,
    parecer TEXT NOT NULL,
    resultado ENUM('PENDENTE', 'DEFERIDO', 'INDEFERIDO', 'EXIGENCIA') NOT NULL DEFAULT 'PENDENTE',
    criado_em DATETIME NOT NULL,
    CONSTRAINT fk_parecer_requerimento FOREIGN KEY (requerimento_id) REFERENCES requerimentos(id) ON DELETE CASCADE,
    CONSTRAINT fk_parecer_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB;

INSERT INTO servicos (nome, descricao, taxa_inicial, exige_pagamento, max_correcoes, correcoes_para_nova_taxa, texto_protocolo, texto_deferimento, ativo, criado_em, atualizado_em)
VALUES
('Aprovação de Projeto', 'Análise e aprovação de projeto arquitetônico.', 150.00, 1, 2, 2, 'Protocolo nº {NUMERO_PROTOCOLO} referente ao serviço {SERVICO}, protocolado em {DATA_PROTOCOLO}, em nome de {NOME_REQUERENTE}.', 'Fica deferido o requerimento nº {NUMERO_PROTOCOLO}, referente ao serviço {SERVICO}, conforme análise técnica da Secretaria de Engenharia.', 1, NOW(), NOW()),
('Habite-se', 'Solicitação de vistoria e emissão de habite-se.', 120.00, 1, 1, 1, 'Protocolo nº {NUMERO_PROTOCOLO} para solicitação de {SERVICO}, em nome de {NOME_REQUERENTE}.', 'Fica deferido o requerimento nº {NUMERO_PROTOCOLO} para o serviço {SERVICO}.', 1, NOW(), NOW()),
('Alvará de Construção', 'Emissão de alvará para início de obra.', 200.00, 1, 2, 2, 'Protocolo nº {NUMERO_PROTOCOLO} para {SERVICO}, assunto {ASSUNTO}.', 'Requerimento nº {NUMERO_PROTOCOLO} deferido para o serviço {SERVICO}.', 1, NOW(), NOW());

-- Senha inicial dos usuários internos adicionais: admin123
