-- ============================================================
--  SiGeM — Sistema de Gestão Municipal
--  Município de Moatize — Província de Tete
--  Base de Dados Completa — PostgreSQL 15
--  Versão 1.0 — Julho 2025
-- ============================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

CREATE SCHEMA IF NOT EXISTS auth;
CREATE SCHEMA IF NOT EXISTS tributacao;
CREATE SCHEMA IF NOT EXISTS tesouraria;
CREATE SCHEMA IF NOT EXISTS contabilidade;
CREATE SCHEMA IF NOT EXISTS ugea;
CREATE SCHEMA IF NOT EXISTS aprovacoes;
CREATE SCHEMA IF NOT EXISTS cobradores;
CREATE SCHEMA IF NOT EXISTS rh;
CREATE SCHEMA IF NOT EXISTS patrimonio;
CREATE SCHEMA IF NOT EXISTS licencas;
CREATE SCHEMA IF NOT EXISTS obras;
CREATE SCHEMA IF NOT EXISTS orcamento;
CREATE SCHEMA IF NOT EXISTS reclamacoes;
CREATE SCHEMA IF NOT EXISTS notificacoes;
CREATE SCHEMA IF NOT EXISTS auditoria;

-- ============================================================
-- SCHEMA: auth
-- ============================================================
CREATE TABLE auth.perfis (
    id            SERIAL PRIMARY KEY,
    codigo        VARCHAR(30) UNIQUE NOT NULL,
    nome          VARCHAR(100) NOT NULL,
    nivel         INTEGER NOT NULL CHECK (nivel BETWEEN 1 AND 8),
    descricao     TEXT,
    ativo         BOOLEAN DEFAULT TRUE,
    criado_em     TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE auth.utilizadores (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    numero        VARCHAR(20) UNIQUE,
    nome          VARCHAR(150) NOT NULL,
    email         VARCHAR(150) UNIQUE NOT NULL,
    telefone      VARCHAR(20),
    senha_hash    TEXT NOT NULL,
    perfil_id     INTEGER REFERENCES auth.perfis(id),
    departamento  VARCHAR(100),
    ativo         BOOLEAN DEFAULT TRUE,
    ultimo_login  TIMESTAMPTZ,
    tentativas    INTEGER DEFAULT 0,
    bloqueado_ate TIMESTAMPTZ,
    dois_fatores  BOOLEAN DEFAULT FALSE,
    foto_url      TEXT,
    criado_em     TIMESTAMPTZ DEFAULT NOW(),
    atualizado_em TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE auth.sessoes (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    utilizador_id UUID REFERENCES auth.utilizadores(id) ON DELETE CASCADE,
    token_hash    TEXT NOT NULL,
    refresh_hash  TEXT,
    ip            INET,
    dispositivo   TEXT,
    user_agent    TEXT,
    expira_em     TIMESTAMPTZ NOT NULL,
    criado_em     TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE auth.permissoes (
    id            SERIAL PRIMARY KEY,
    perfil_id     INTEGER REFERENCES auth.perfis(id) ON DELETE CASCADE,
    modulo        VARCHAR(50) NOT NULL,
    pode_ler      BOOLEAN DEFAULT FALSE,
    pode_escrever BOOLEAN DEFAULT FALSE,
    pode_aprovar  BOOLEAN DEFAULT FALSE,
    pode_exportar BOOLEAN DEFAULT FALSE,
    pode_apagar   BOOLEAN DEFAULT FALSE,
    UNIQUE(perfil_id, modulo)
);

CREATE TABLE auth.tokens_reset (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    utilizador_id UUID REFERENCES auth.utilizadores(id) ON DELETE CASCADE,
    token_hash    TEXT NOT NULL,
    usado         BOOLEAN DEFAULT FALSE,
    expira_em     TIMESTAMPTZ NOT NULL,
    criado_em     TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- SCHEMA: auditoria
-- ============================================================
CREATE TABLE auditoria.log (
    id            BIGSERIAL PRIMARY KEY,
    utilizador_id UUID REFERENCES auth.utilizadores(id),
    acao          VARCHAR(100) NOT NULL,
    modulo        VARCHAR(50),
    tabela        VARCHAR(100),
    registo_id    TEXT,
    dados_antes   JSONB,
    dados_depois  JSONB,
    ip            INET,
    user_agent    TEXT,
    resultado     VARCHAR(20) DEFAULT 'sucesso',
    erro          TEXT,
    criado_em     TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE auditoria.alertas_seguranca (
    id            BIGSERIAL PRIMARY KEY,
    tipo          VARCHAR(50) NOT NULL,
    descricao     TEXT,
    utilizador_id UUID REFERENCES auth.utilizadores(id),
    ip            INET,
    resolvido     BOOLEAN DEFAULT FALSE,
    criado_em     TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- SCHEMA: tributacao
-- ============================================================
CREATE TABLE tributacao.contribuintes (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    nuit          VARCHAR(20) UNIQUE,
    nome          VARCHAR(200) NOT NULL,
    tipo          VARCHAR(30) CHECK (tipo IN ('singular','colectivo','ambulante','isento')),
    morada        TEXT,
    bairro        VARCHAR(100),
    telefone      VARCHAR(20),
    email         VARCHAR(150),
    nib           VARCHAR(30),
    banco         VARCHAR(100),
    ativo         BOOLEAN DEFAULT TRUE,
    observacoes   TEXT,
    criado_em     TIMESTAMPTZ DEFAULT NOW(),
    atualizado_em TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tributacao.tipos_imposto (
    id              SERIAL PRIMARY KEY,
    codigo          VARCHAR(20) UNIQUE NOT NULL,
    nome            VARCHAR(100) NOT NULL,
    periodicidade   VARCHAR(20) CHECK (periodicidade IN ('diaria','mensal','trimestral','anual','unica')),
    taxa_percentual NUMERIC(5,2),
    valor_fixo      NUMERIC(15,2),
    tem_iva         BOOLEAN DEFAULT FALSE,
    taxa_iva        NUMERIC(5,2) DEFAULT 0,
    rubrica_orcam   VARCHAR(20),
    conta_contab    VARCHAR(20),
    descricao       TEXT,
    ativo           BOOLEAN DEFAULT TRUE
);

CREATE TABLE tributacao.lancamentos (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    numero          VARCHAR(30) UNIQUE NOT NULL,
    contribuinte_id UUID REFERENCES tributacao.contribuintes(id),
    tipo_imposto_id INTEGER REFERENCES tributacao.tipos_imposto(id),
    periodo_inicio  DATE NOT NULL,
    periodo_fim     DATE NOT NULL,
    valor_base      NUMERIC(15,2) NOT NULL,
    taxa_aplicada   NUMERIC(5,2),
    valor_imposto   NUMERIC(15,2) NOT NULL,
    valor_iva       NUMERIC(15,2) DEFAULT 0,
    valor_multa     NUMERIC(15,2) DEFAULT 0,
    valor_juros     NUMERIC(15,2) DEFAULT 0,
    valor_total     NUMERIC(15,2) NOT NULL,
    estado          VARCHAR(20) DEFAULT 'pendente' CHECK (estado IN ('pendente','pago','em_falta','cancelado','isento','parcial')),
    data_vencimento DATE NOT NULL,
    data_pagamento  TIMESTAMPTZ,
    canal_pagamento VARCHAR(30),
    referencia_pag  VARCHAR(100),
    utilizador_id   UUID REFERENCES auth.utilizadores(id),
    observacoes     TEXT,
    criado_em       TIMESTAMPTZ DEFAULT NOW(),
    atualizado_em   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tributacao.recibos (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    numero          VARCHAR(30) UNIQUE NOT NULL,
    lancamento_id   UUID REFERENCES tributacao.lancamentos(id),
    valor_pago      NUMERIC(15,2) NOT NULL,
    cobrador_id     UUID REFERENCES auth.utilizadores(id),
    lat             NUMERIC(10,7),
    lng             NUMERIC(10,7),
    localizacao_txt VARCHAR(200),
    zona_id         INTEGER,
    hash_verificacao VARCHAR(64) UNIQUE,
    qr_code         TEXT,
    sincronizado    BOOLEAN DEFAULT FALSE,
    emitido_offline BOOLEAN DEFAULT FALSE,
    emitido_em      TIMESTAMPTZ DEFAULT NOW(),
    sincronizado_em TIMESTAMPTZ
);

CREATE TABLE tributacao.notificacoes_divida (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    lancamento_id   UUID REFERENCES tributacao.lancamentos(id),
    numero_notif    INTEGER DEFAULT 1,
    tipo            VARCHAR(30),
    canal           VARCHAR(20) CHECK (canal IN ('sms','email','sistema','carta')),
    estado          VARCHAR(20) DEFAULT 'pendente',
    conteudo        TEXT,
    enviado_em      TIMESTAMPTZ,
    criado_em       TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tributacao.isencoes (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    contribuinte_id UUID REFERENCES tributacao.contribuintes(id),
    tipo_imposto_id INTEGER REFERENCES tributacao.tipos_imposto(id),
    motivo          TEXT NOT NULL,
    data_inicio     DATE NOT NULL,
    data_fim        DATE,
    aprovado_por    UUID REFERENCES auth.utilizadores(id),
    aprovado_em     TIMESTAMPTZ,
    estado          VARCHAR(20) DEFAULT 'pendente',
    criado_em       TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- SCHEMA: tesouraria
-- ============================================================
CREATE TABLE tesouraria.contas_bancarias (
    id            SERIAL PRIMARY KEY,
    banco         VARCHAR(100) NOT NULL,
    nib           VARCHAR(30) UNIQUE NOT NULL,
    titular       VARCHAR(200),
    tipo          VARCHAR(30) CHECK (tipo IN ('conta_corrente','conta_poupanca','conta_prazo')),
    moeda         VARCHAR(5) DEFAULT 'MZN',
    saldo_atual   NUMERIC(15,2) DEFAULT 0,
    saldo_data    TIMESTAMPTZ DEFAULT NOW(),
    ativa         BOOLEAN DEFAULT TRUE,
    principal     BOOLEAN DEFAULT FALSE,
    criado_em     TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tesouraria.pagamentos (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    numero          VARCHAR(30) UNIQUE NOT NULL,
    tipo            VARCHAR(40) DEFAULT 'prestacao_servicos',
    beneficiario    VARCHAR(200) NOT NULL,
    nuit_benef      VARCHAR(20),
    nib_destino     VARCHAR(30),
    banco_destino   VARCHAR(100),
    valor_base      NUMERIC(15,2) NOT NULL,
    tem_iva         BOOLEAN DEFAULT FALSE,
    taxa_iva        NUMERIC(5,2) DEFAULT 0,
    valor_iva       NUMERIC(15,2) DEFAULT 0,
    valor_total     NUMERIC(15,2) NOT NULL,
    referencia      VARCHAR(200),
    num_factura     VARCHAR(50),
    justificacao    TEXT NOT NULL,
    rubrica_orcam   VARCHAR(20),
    conta_id        INTEGER REFERENCES tesouraria.contas_bancarias(id),
    estado          VARCHAR(30) DEFAULT 'rascunho' CHECK (estado IN ('rascunho','submetido','verificado','em_aprovacao','aprovado','rejeitado','executado','cancelado')),
    prioridade      VARCHAR(20) DEFAULT 'normal' CHECK (prioridade IN ('baixa','normal','urgente')),
    submetido_por   UUID REFERENCES auth.utilizadores(id),
    submetido_em    TIMESTAMPTZ,
    verificado_por  UUID REFERENCES auth.utilizadores(id),
    verificado_em   TIMESTAMPTZ,
    aprovado_por    UUID REFERENCES auth.utilizadores(id),
    aprovado_em     TIMESTAMPTZ,
    executado_em    TIMESTAMPTZ,
    documentos      JSONB DEFAULT '[]',
    observacoes     TEXT,
    num_cheque      VARCHAR(20),
    criado_em       TIMESTAMPTZ DEFAULT NOW(),
    atualizado_em   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tesouraria.movimentos (
    id              BIGSERIAL PRIMARY KEY,
    conta_id        INTEGER REFERENCES tesouraria.contas_bancarias(id),
    pagamento_id    UUID REFERENCES tesouraria.pagamentos(id),
    lancamento_id   UUID REFERENCES tributacao.lancamentos(id),
    tipo            VARCHAR(10) CHECK (tipo IN ('entrada','saida','transferencia')),
    valor           NUMERIC(15,2) NOT NULL,
    saldo_apos      NUMERIC(15,2) NOT NULL,
    descricao       TEXT,
    referencia_ext  VARCHAR(100),
    data_movimento  TIMESTAMPTZ DEFAULT NOW(),
    reconciliado    BOOLEAN DEFAULT FALSE,
    reconciliado_em TIMESTAMPTZ
);

CREATE TABLE tesouraria.cheques (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    numero          VARCHAR(20) UNIQUE NOT NULL,
    pagamento_id    UUID REFERENCES tesouraria.pagamentos(id),
    conta_id        INTEGER REFERENCES tesouraria.contas_bancarias(id),
    beneficiario    VARCHAR(200) NOT NULL,
    valor           NUMERIC(15,2) NOT NULL,
    valor_extenso   TEXT,
    data_emissao    DATE NOT NULL,
    data_validade   DATE,
    estado          VARCHAR(20) DEFAULT 'emitido' CHECK (estado IN ('emitido','em_transito','compensado','cancelado','devolvido')),
    emitido_por     UUID REFERENCES auth.utilizadores(id),
    emitido_em      TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- SCHEMA: contabilidade
-- ============================================================
CREATE TABLE contabilidade.plano_contas (
    id                SERIAL PRIMARY KEY,
    codigo            VARCHAR(20) UNIQUE NOT NULL,
    nome              VARCHAR(200) NOT NULL,
    tipo              VARCHAR(20) CHECK (tipo IN ('ativo','passivo','capital','receita','despesa','ordem')),
    natureza          VARCHAR(10) CHECK (natureza IN ('devedora','credora')),
    conta_pai_id      INTEGER REFERENCES contabilidade.plano_contas(id),
    nivel             INTEGER DEFAULT 1,
    aceita_lancamento BOOLEAN DEFAULT TRUE,
    ativa             BOOLEAN DEFAULT TRUE
);

CREATE TABLE contabilidade.diarios (
    id            SERIAL PRIMARY KEY,
    codigo        VARCHAR(20) UNIQUE NOT NULL,
    nome          VARCHAR(100) NOT NULL,
    tipo          VARCHAR(30),
    sequencia     INTEGER DEFAULT 0,
    ativo         BOOLEAN DEFAULT TRUE
);

CREATE TABLE contabilidade.exercicios (
    id            SERIAL PRIMARY KEY,
    ano           INTEGER UNIQUE NOT NULL,
    estado        VARCHAR(20) DEFAULT 'aberto' CHECK (estado IN ('aberto','fechado','auditado')),
    fechado_em    TIMESTAMPTZ,
    fechado_por   UUID REFERENCES auth.utilizadores(id)
);

CREATE TABLE contabilidade.lancamentos (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    numero          VARCHAR(30) UNIQUE NOT NULL,
    diario_id       INTEGER REFERENCES contabilidade.diarios(id),
    exercicio_id    INTEGER REFERENCES contabilidade.exercicios(id),
    data_lancamento DATE NOT NULL,
    descricao       TEXT NOT NULL,
    estado          VARCHAR(20) DEFAULT 'rascunho' CHECK (estado IN ('rascunho','validado','contabilizado','anulado')),
    referencia_ext  VARCHAR(100),
    tipo_origem     VARCHAR(30),
    origem_id       UUID,
    utilizador_id   UUID REFERENCES auth.utilizadores(id),
    criado_em       TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE contabilidade.linhas_lancamento (
    id              BIGSERIAL PRIMARY KEY,
    lancamento_id   UUID REFERENCES contabilidade.lancamentos(id) ON DELETE CASCADE,
    conta_id        INTEGER REFERENCES contabilidade.plano_contas(id),
    debito          NUMERIC(15,2) DEFAULT 0 CHECK (debito >= 0),
    credito         NUMERIC(15,2) DEFAULT 0 CHECK (credito >= 0),
    descricao       TEXT,
    centro_custo    VARCHAR(50),
    CHECK (NOT (debito > 0 AND credito > 0))
);

-- ============================================================
-- SCHEMA: ugea
-- ============================================================
CREATE TABLE ugea.fornecedores (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    nuit            VARCHAR(20) UNIQUE,
    nome            VARCHAR(200) NOT NULL,
    tipo            VARCHAR(30) CHECK (tipo IN ('empresa','individual','ong','estado')),
    morada          TEXT,
    telefone        VARCHAR(20),
    email           VARCHAR(150),
    nib             VARCHAR(30),
    banco           VARCHAR(100),
    certificado_url TEXT,
    ativo           BOOLEAN DEFAULT TRUE,
    lista_negra     BOOLEAN DEFAULT FALSE,
    criado_em       TIMESTAMPTZ DEFAULT NOW(),
    atualizado_em   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE ugea.processos (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    numero          VARCHAR(30) UNIQUE NOT NULL,
    descricao       TEXT NOT NULL,
    modalidade      VARCHAR(40) CHECK (modalidade IN ('ajuste_direto','concurso_limitado','concurso_publico','concurso_internacional','consulta_precos')),
    unidade_req     VARCHAR(100),
    rubrica_orcam   VARCHAR(20),
    valor_base      NUMERIC(15,2),
    valor_adjudicado NUMERIC(15,2),
    poupanca_pct    NUMERIC(5,2),
    estado          VARCHAR(30) DEFAULT 'aberto' CHECK (estado IN ('aberto','em_avaliacao','adjudicado','cancelado','concluido','suspenso')),
    data_abertura   DATE,
    data_prazo      DATE,
    data_adjudicacao DATE,
    criado_por      UUID REFERENCES auth.utilizadores(id),
    criado_em       TIMESTAMPTZ DEFAULT NOW(),
    atualizado_em   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE ugea.propostas (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    processo_id     UUID REFERENCES ugea.processos(id),
    fornecedor_id   UUID REFERENCES ugea.fornecedores(id),
    valor           NUMERIC(15,2) NOT NULL,
    prazo_dias      INTEGER,
    pontuacao_tecn  NUMERIC(5,2),
    pontuacao_fin   NUMERIC(5,2),
    pontuacao_total NUMERIC(5,2),
    classificacao   INTEGER,
    estado          VARCHAR(20) DEFAULT 'recebida' CHECK (estado IN ('recebida','em_analise','aprovada','rejeitada','desqualificada')),
    documentos      JSONB DEFAULT '[]',
    observacoes     TEXT,
    recebida_em     TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE ugea.contratos (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    numero          VARCHAR(30) UNIQUE NOT NULL,
    processo_id     UUID REFERENCES ugea.processos(id),
    fornecedor_id   UUID REFERENCES ugea.fornecedores(id),
    valor_total     NUMERIC(15,2) NOT NULL,
    valor_pago      NUMERIC(15,2) DEFAULT 0,
    data_inicio     DATE,
    data_fim        DATE,
    data_fim_real   DATE,
    estado          VARCHAR(20) DEFAULT 'ativo' CHECK (estado IN ('ativo','suspenso','concluido','rescindido','em_garantia')),
    garantia_valor  NUMERIC(15,2),
    garantia_expira DATE,
    documentos      JSONB DEFAULT '[]',
    criado_em       TIMESTAMPTZ DEFAULT NOW(),
    atualizado_em   TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- SCHEMA: aprovacoes
-- ============================================================
CREATE TABLE aprovacoes.fluxos (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    numero          VARCHAR(30) UNIQUE NOT NULL,
    tipo            VARCHAR(50) NOT NULL,
    referencia_id   UUID NOT NULL,
    modulo_origem   VARCHAR(30) NOT NULL,
    valor           NUMERIC(15,2),
    descricao       TEXT,
    estado          VARCHAR(20) DEFAULT 'pendente' CHECK (estado IN ('pendente','em_analise','aprovado','rejeitado','cancelado','expirado')),
    prioridade      VARCHAR(20) DEFAULT 'normal' CHECK (prioridade IN ('baixa','normal','urgente','critico')),
    submetido_por   UUID REFERENCES auth.utilizadores(id),
    submetido_em    TIMESTAMPTZ DEFAULT NOW(),
    decidido_por    UUID REFERENCES auth.utilizadores(id),
    decidido_em     TIMESTAMPTZ,
    comentario      TEXT,
    expira_em       TIMESTAMPTZ,
    documentos      JSONB DEFAULT '[]'
);

CREATE TABLE aprovacoes.historico (
    id              BIGSERIAL PRIMARY KEY,
    fluxo_id        UUID REFERENCES aprovacoes.fluxos(id),
    utilizador_id   UUID REFERENCES auth.utilizadores(id),
    acao            VARCHAR(30) CHECK (acao IN ('submetido','visto','aprovado','rejeitado','pedido_esclarecimento','respondido','cancelado')),
    comentario      TEXT,
    dados_extras    JSONB,
    criado_em       TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- SCHEMA: cobradores
-- ============================================================
CREATE TABLE cobradores.zonas (
    id              SERIAL PRIMARY KEY,
    nome            VARCHAR(100) NOT NULL,
    descricao       TEXT,
    bairro          VARCHAR(100),
    ativa           BOOLEAN DEFAULT TRUE,
    criado_em       TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE cobradores.atribuicoes (
    id              SERIAL PRIMARY KEY,
    cobrador_id     UUID REFERENCES auth.utilizadores(id),
    zona_id         INTEGER REFERENCES cobradores.zonas(id),
    data_inicio     DATE NOT NULL,
    data_fim        DATE,
    ativa           BOOLEAN DEFAULT TRUE
);

CREATE TABLE cobradores.metas (
    id              SERIAL PRIMARY KEY,
    cobrador_id     UUID REFERENCES auth.utilizadores(id),
    zona_id         INTEGER REFERENCES cobradores.zonas(id),
    mes             INTEGER CHECK (mes BETWEEN 1 AND 12),
    ano             INTEGER,
    meta_cobracoes  INTEGER DEFAULT 0,
    meta_valor      NUMERIC(15,2) DEFAULT 0,
    UNIQUE(cobrador_id, mes, ano)
);

CREATE TABLE cobradores.sync_queue (
    id              BIGSERIAL PRIMARY KEY,
    cobrador_id     UUID REFERENCES auth.utilizadores(id),
    payload         JSONB NOT NULL,
    tipo            VARCHAR(30),
    sincronizado    BOOLEAN DEFAULT FALSE,
    tentativas      INTEGER DEFAULT 0,
    erro            TEXT,
    criado_em       TIMESTAMPTZ DEFAULT NOW(),
    sincronizado_em TIMESTAMPTZ
);

-- ============================================================
-- SCHEMA: rh
-- ============================================================
CREATE TABLE rh.cargos (
    id            SERIAL PRIMARY KEY,
    codigo        VARCHAR(20) UNIQUE NOT NULL,
    nome          VARCHAR(100) NOT NULL,
    nivel_salarial INTEGER,
    salario_base  NUMERIC(15,2),
    descricao     TEXT,
    ativo         BOOLEAN DEFAULT TRUE
);

CREATE TABLE rh.funcionarios (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    utilizador_id   UUID UNIQUE REFERENCES auth.utilizadores(id),
    numero          VARCHAR(20) UNIQUE NOT NULL,
    nome            VARCHAR(150) NOT NULL,
    cargo_id        INTEGER REFERENCES rh.cargos(id),
    departamento    VARCHAR(100),
    data_admissao   DATE NOT NULL,
    data_saida      DATE,
    salario_base    NUMERIC(15,2) NOT NULL,
    subsidio_funcao NUMERIC(15,2) DEFAULT 0,
    subsidio_transp NUMERIC(15,2) DEFAULT 0,
    nib             VARCHAR(30),
    banco           VARCHAR(100),
    nuit            VARCHAR(20),
    niss            VARCHAR(20),
    estado          VARCHAR(20) DEFAULT 'ativo' CHECK (estado IN ('ativo','ferias','licenca','suspenso','inativo')),
    criado_em       TIMESTAMPTZ DEFAULT NOW(),
    atualizado_em   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE rh.folhas_vencimento (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    mes             INTEGER NOT NULL CHECK (mes BETWEEN 1 AND 12),
    ano             INTEGER NOT NULL,
    estado          VARCHAR(20) DEFAULT 'rascunho' CHECK (estado IN ('rascunho','processada','aprovada','paga')),
    total_bruto     NUMERIC(15,2) DEFAULT 0,
    total_inss      NUMERIC(15,2) DEFAULT 0,
    total_irps      NUMERIC(15,2) DEFAULT 0,
    total_liquido   NUMERIC(15,2) DEFAULT 0,
    processada_por  UUID REFERENCES auth.utilizadores(id),
    processada_em   TIMESTAMPTZ,
    aprovada_por    UUID REFERENCES auth.utilizadores(id),
    aprovada_em     TIMESTAMPTZ,
    UNIQUE(mes, ano)
);

CREATE TABLE rh.recibos_vencimento (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    folha_id        UUID REFERENCES rh.folhas_vencimento(id),
    funcionario_id  UUID REFERENCES rh.funcionarios(id),
    salario_base    NUMERIC(15,2) NOT NULL,
    subsidio_funcao NUMERIC(15,2) DEFAULT 0,
    subsidio_transp NUMERIC(15,2) DEFAULT 0,
    outros_abonos   NUMERIC(15,2) DEFAULT 0,
    total_bruto     NUMERIC(15,2) NOT NULL,
    inss_func       NUMERIC(15,2) DEFAULT 0,
    inss_entidade   NUMERIC(15,2) DEFAULT 0,
    irps            NUMERIC(15,2) DEFAULT 0,
    outros_descontos NUMERIC(15,2) DEFAULT 0,
    total_descontos NUMERIC(15,2) NOT NULL,
    total_liquido   NUMERIC(15,2) NOT NULL,
    pago            BOOLEAN DEFAULT FALSE,
    pago_em         TIMESTAMPTZ
);

CREATE TABLE rh.ausencias (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    funcionario_id  UUID REFERENCES rh.funcionarios(id),
    tipo            VARCHAR(30) CHECK (tipo IN ('ferias','falta_justificada','falta_injustificada','licenca_medica','licenca_maternidade','outros')),
    data_inicio     DATE NOT NULL,
    data_fim        DATE NOT NULL,
    dias            INTEGER,
    aprovado_por    UUID REFERENCES auth.utilizadores(id),
    estado          VARCHAR(20) DEFAULT 'pendente' CHECK (estado IN ('pendente','aprovada','rejeitada','cancelada')),
    observacoes     TEXT,
    criado_em       TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- SCHEMA: patrimonio
-- ============================================================
CREATE TABLE patrimonio.categorias (
    id            SERIAL PRIMARY KEY,
    codigo        VARCHAR(20) UNIQUE NOT NULL,
    nome          VARCHAR(100) NOT NULL,
    vida_util_anos INTEGER,
    taxa_amortiz  NUMERIC(5,2),
    descricao     TEXT
);

CREATE TABLE patrimonio.bens (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    codigo          VARCHAR(30) UNIQUE NOT NULL,
    descricao       TEXT NOT NULL,
    categoria_id    INTEGER REFERENCES patrimonio.categorias(id),
    numero_serie    VARCHAR(100),
    marca           VARCHAR(100),
    modelo          VARCHAR(100),
    valor_aquisicao NUMERIC(15,2),
    valor_atual     NUMERIC(15,2),
    data_aquisicao  DATE,
    fornecedor      VARCHAR(200),
    departamento    VARCHAR(100),
    responsavel_id  UUID REFERENCES auth.utilizadores(id),
    localizacao     VARCHAR(200),
    estado          VARCHAR(20) DEFAULT 'bom' CHECK (estado IN ('novo','bom','razoavel','degradado','inoperacional','abatido')),
    matricula       VARCHAR(20),
    ativo           BOOLEAN DEFAULT TRUE,
    observacoes     TEXT,
    criado_em       TIMESTAMPTZ DEFAULT NOW(),
    atualizado_em   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE patrimonio.manutencoes (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    bem_id          UUID REFERENCES patrimonio.bens(id),
    tipo            VARCHAR(30) CHECK (tipo IN ('preventiva','corretiva','substituicao','inspecao')),
    descricao       TEXT,
    custo           NUMERIC(15,2),
    fornecedor      VARCHAR(200),
    data_prevista   DATE,
    data_realizada  DATE,
    estado          VARCHAR(20) DEFAULT 'pendente' CHECK (estado IN ('pendente','agendada','em_curso','concluida','cancelada')),
    criado_em       TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- SCHEMA: licencas
-- ============================================================
CREATE TABLE licencas.tipos_licenca (
    id            SERIAL PRIMARY KEY,
    codigo        VARCHAR(20) UNIQUE NOT NULL,
    nome          VARCHAR(100) NOT NULL,
    taxa          NUMERIC(15,2),
    prazo_dias    INTEGER DEFAULT 15,
    validade_meses INTEGER DEFAULT 12,
    documentos_req JSONB DEFAULT '[]',
    ativo         BOOLEAN DEFAULT TRUE
);

CREATE TABLE licencas.pedidos (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    numero          VARCHAR(30) UNIQUE NOT NULL,
    tipo_id         INTEGER REFERENCES licencas.tipos_licenca(id),
    requerente_nome VARCHAR(200) NOT NULL,
    requerente_nuit VARCHAR(20),
    requerente_tel  VARCHAR(20),
    requerente_email VARCHAR(150),
    atividade       VARCHAR(200),
    localizacao     TEXT,
    area_m2         NUMERIC(10,2),
    estado          VARCHAR(30) DEFAULT 'submetido' CHECK (estado IN ('submetido','em_analise','em_vistoria','aguarda_docs','aguarda_pagamento','aprovado','rejeitado','emitido','expirado','renovado')),
    taxa_lancada    BOOLEAN DEFAULT FALSE,
    taxa_paga       BOOLEAN DEFAULT FALSE,
    documentos      JSONB DEFAULT '[]',
    observacoes     TEXT,
    tecnico_id      UUID REFERENCES auth.utilizadores(id),
    data_vistoria   DATE,
    canal           VARCHAR(20) DEFAULT 'balcao' CHECK (canal IN ('balcao','portal','app')),
    criado_em       TIMESTAMPTZ DEFAULT NOW(),
    atualizado_em   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE licencas.alvaras (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    numero          VARCHAR(30) UNIQUE NOT NULL,
    pedido_id       UUID REFERENCES licencas.pedidos(id),
    titular         VARCHAR(200) NOT NULL,
    atividade       VARCHAR(200),
    localizacao     TEXT,
    data_emissao    DATE NOT NULL,
    data_validade   DATE NOT NULL,
    qr_code         TEXT,
    hash_verif      VARCHAR(64) UNIQUE,
    estado          VARCHAR(20) DEFAULT 'valido' CHECK (estado IN ('valido','suspenso','revogado','expirado')),
    emitido_por     UUID REFERENCES auth.utilizadores(id),
    emitido_em      TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- SCHEMA: obras
-- ============================================================
CREATE TABLE obras.projectos (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    numero          VARCHAR(30) UNIQUE NOT NULL,
    nome            TEXT NOT NULL,
    descricao       TEXT,
    contrato_id     UUID REFERENCES ugea.contratos(id),
    valor_total     NUMERIC(15,2),
    valor_pago      NUMERIC(15,2) DEFAULT 0,
    data_inicio     DATE,
    data_fim_prev   DATE,
    data_fim_real   DATE,
    exec_fisica_pct NUMERIC(5,2) DEFAULT 0,
    exec_fin_pct    NUMERIC(5,2) DEFAULT 0,
    estado          VARCHAR(30) DEFAULT 'planeado' CHECK (estado IN ('planeado','em_execucao','suspenso','concluido','cancelado','em_atraso')),
    fiscal_id       UUID REFERENCES auth.utilizadores(id),
    localizacao     TEXT,
    criado_em       TIMESTAMPTZ DEFAULT NOW(),
    atualizado_em   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE obras.atualizacoes (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    projecto_id     UUID REFERENCES obras.projectos(id),
    exec_fisica_pct NUMERIC(5,2),
    exec_fin_pct    NUMERIC(5,2),
    descricao       TEXT,
    problemas       TEXT,
    documentos      JSONB DEFAULT '[]',
    registado_por   UUID REFERENCES auth.utilizadores(id),
    criado_em       TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE obras.tranches (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    projecto_id     UUID REFERENCES obras.projectos(id),
    numero          INTEGER NOT NULL,
    valor           NUMERIC(15,2) NOT NULL,
    percentagem     NUMERIC(5,2),
    condicao        TEXT,
    estado          VARCHAR(20) DEFAULT 'pendente' CHECK (estado IN ('pendente','desbloqueada','paga','cancelada')),
    desbloqueada_em TIMESTAMPTZ,
    paga_em         TIMESTAMPTZ,
    pagamento_id    UUID REFERENCES tesouraria.pagamentos(id)
);

-- ============================================================
-- SCHEMA: orcamento
-- ============================================================
CREATE TABLE orcamento.orcamentos (
    id              SERIAL PRIMARY KEY,
    ano             INTEGER UNIQUE NOT NULL,
    total_receitas  NUMERIC(15,2) DEFAULT 0,
    total_despesas  NUMERIC(15,2) DEFAULT 0,
    estado          VARCHAR(20) DEFAULT 'proposta' CHECK (estado IN ('proposta','aprovado','em_execucao','fechado')),
    aprovado_em     TIMESTAMPTZ,
    aprovado_por    UUID REFERENCES auth.utilizadores(id)
);

CREATE TABLE orcamento.rubricas (
    id              SERIAL PRIMARY KEY,
    orcamento_id    INTEGER REFERENCES orcamento.orcamentos(id),
    codigo          VARCHAR(20) NOT NULL,
    descricao       VARCHAR(200) NOT NULL,
    tipo            VARCHAR(10) CHECK (tipo IN ('receita','despesa')),
    dotacao_inicial NUMERIC(15,2) DEFAULT 0,
    dotacao_atual   NUMERIC(15,2) DEFAULT 0,
    executado       NUMERIC(15,2) DEFAULT 0,
    conta_contab    VARCHAR(20),
    UNIQUE(orcamento_id, codigo)
);

CREATE TABLE orcamento.revisoes (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    rubrica_id      INTEGER REFERENCES orcamento.rubricas(id),
    valor_anterior  NUMERIC(15,2),
    valor_novo      NUMERIC(15,2),
    justificacao    TEXT NOT NULL,
    aprovado_por    UUID REFERENCES auth.utilizadores(id),
    aprovado_em     TIMESTAMPTZ,
    estado          VARCHAR(20) DEFAULT 'pendente',
    criado_em       TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- SCHEMA: reclamacoes
-- ============================================================
CREATE TABLE reclamacoes.reclamacoes (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    numero          VARCHAR(30) UNIQUE NOT NULL,
    tipo            VARCHAR(30) DEFAULT 'reclamacao' CHECK (tipo IN ('reclamacao','sugestao','pedido_info','elogio')),
    categoria       VARCHAR(50),
    bairro          VARCHAR(100),
    assunto         VARCHAR(300) NOT NULL,
    descricao       TEXT NOT NULL,
    urgencia        VARCHAR(20) DEFAULT 'normal' CHECK (urgencia IN ('baixa','normal','urgente','muito_urgente')),
    canal           VARCHAR(20) DEFAULT 'balcao' CHECK (canal IN ('portal','sms','telefone','balcao','email')),
    nome_cidadao    VARCHAR(200),
    telefone        VARCHAR(20),
    email           VARCHAR(150),
    estado          VARCHAR(30) DEFAULT 'aberto' CHECK (estado IN ('aberto','triagem','atribuido','em_resolucao','respondido','resolvido','fechado','reaberto')),
    departamento_id VARCHAR(50),
    atribuido_a     UUID REFERENCES auth.utilizadores(id),
    prazo_resolucao TIMESTAMPTZ,
    resolvido_em    TIMESTAMPTZ,
    horas_resolucao NUMERIC(8,2),
    satisfacao_nota INTEGER CHECK (satisfacao_nota BETWEEN 1 AND 5),
    satisfacao_comt TEXT,
    criado_em       TIMESTAMPTZ DEFAULT NOW(),
    atualizado_em   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE reclamacoes.mensagens (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    reclamacao_id   UUID REFERENCES reclamacoes.reclamacoes(id),
    remetente_tipo  VARCHAR(10) CHECK (remetente_tipo IN ('cidadao','municipio','sistema')),
    remetente_id    UUID REFERENCES auth.utilizadores(id),
    mensagem        TEXT NOT NULL,
    interna         BOOLEAN DEFAULT FALSE,
    criado_em       TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- SCHEMA: notificacoes
-- ============================================================
CREATE TABLE notificacoes.regras (
    id              SERIAL PRIMARY KEY,
    codigo          VARCHAR(50) UNIQUE NOT NULL,
    nome            VARCHAR(200) NOT NULL,
    descricao       TEXT,
    modulo          VARCHAR(50),
    canais          TEXT[] DEFAULT ARRAY['sistema'],
    template_sms    TEXT,
    template_email  TEXT,
    ativa           BOOLEAN DEFAULT TRUE
);

CREATE TABLE notificacoes.enviadas (
    id              BIGSERIAL PRIMARY KEY,
    regra_id        INTEGER REFERENCES notificacoes.regras(id),
    destinatario_id UUID REFERENCES auth.utilizadores(id),
    canal           VARCHAR(20) CHECK (canal IN ('sistema','sms','email','push')),
    assunto         TEXT,
    conteudo        TEXT,
    estado          VARCHAR(20) DEFAULT 'pendente' CHECK (estado IN ('pendente','enviado','entregue','falhado','lido')),
    referencia_id   UUID,
    modulo          VARCHAR(50),
    tentativas      INTEGER DEFAULT 0,
    enviado_em      TIMESTAMPTZ,
    lido_em         TIMESTAMPTZ,
    criado_em       TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- ÍNDICES DE PERFORMANCE
-- ============================================================
CREATE INDEX idx_utilizadores_email    ON auth.utilizadores(email);
CREATE INDEX idx_utilizadores_perfil   ON auth.utilizadores(perfil_id);
CREATE INDEX idx_sessoes_token         ON auth.sessoes(token_hash);
CREATE INDEX idx_log_utilizador        ON auditoria.log(utilizador_id);
CREATE INDEX idx_log_criado            ON auditoria.log(criado_em DESC);
CREATE INDEX idx_log_modulo            ON auditoria.log(modulo);
CREATE INDEX idx_contribuintes_nuit    ON tributacao.contribuintes(nuit);
CREATE INDEX idx_contribuintes_nome    ON tributacao.contribuintes USING gin(nome gin_trgm_ops);
CREATE INDEX idx_lancamentos_contrib   ON tributacao.lancamentos(contribuinte_id);
CREATE INDEX idx_lancamentos_estado    ON tributacao.lancamentos(estado);
CREATE INDEX idx_lancamentos_vencim    ON tributacao.lancamentos(data_vencimento);
CREATE INDEX idx_recibos_lancamento    ON tributacao.recibos(lancamento_id);
CREATE INDEX idx_recibos_cobrador      ON tributacao.recibos(cobrador_id);
CREATE INDEX idx_recibos_hash          ON tributacao.recibos(hash_verificacao);
CREATE INDEX idx_pagamentos_estado     ON tesouraria.pagamentos(estado);
CREATE INDEX idx_pagamentos_criado     ON tesouraria.pagamentos(criado_em DESC);
CREATE INDEX idx_movimentos_conta      ON tesouraria.movimentos(conta_id);
CREATE INDEX idx_movimentos_data       ON tesouraria.movimentos(data_movimento DESC);
CREATE INDEX idx_cont_lanc_data        ON contabilidade.lancamentos(data_lancamento);
CREATE INDEX idx_aprovacoes_estado     ON aprovacoes.fluxos(estado);
CREATE INDEX idx_sync_cobrador         ON cobradores.sync_queue(cobrador_id, sincronizado);
CREATE INDEX idx_pedidos_estado        ON licencas.pedidos(estado);
CREATE INDEX idx_alvaras_hash          ON licencas.alvaras(hash_verif);
CREATE INDEX idx_reclamacoes_estado    ON reclamacoes.reclamacoes(estado);
CREATE INDEX idx_notif_destinatario    ON notificacoes.enviadas(destinatario_id, estado);