-- ============================================================
--  SiGeM — Views, Funções e Triggers
--  Município de Moatize — Versão 1.0
-- ============================================================

-- ============================================================
-- VIEWS ÚTEIS
-- ============================================================

-- View: Resumo financeiro do mês actual
CREATE OR REPLACE VIEW v_resumo_financeiro_mes AS
SELECT
    DATE_TRUNC('month', NOW()) AS mes,
    COALESCE(SUM(CASE WHEN l.estado = 'pago' THEN l.valor_total ELSE 0 END), 0) AS receita_cobrada,
    COALESCE(SUM(CASE WHEN l.estado IN ('pendente','em_falta') THEN l.valor_total ELSE 0 END), 0) AS divida_total,
    COUNT(DISTINCT l.contribuinte_id) FILTER (WHERE l.estado = 'pago') AS contribuintes_pagaram,
    COUNT(DISTINCT l.contribuinte_id) FILTER (WHERE l.estado IN ('pendente','em_falta')) AS contribuintes_devem,
    COUNT(*) AS total_lancamentos
FROM tributacao.lancamentos l
WHERE DATE_TRUNC('month', l.criado_em) = DATE_TRUNC('month', NOW());

-- View: Saldo consolidado de tesouraria
CREATE OR REPLACE VIEW v_saldo_tesouraria AS
SELECT
    cb.id,
    cb.banco,
    cb.nib,
    cb.tipo,
    cb.saldo_atual,
    cb.principal,
    COALESCE((SELECT SUM(m.valor) FROM tesouraria.movimentos m WHERE m.conta_id = cb.id AND m.tipo = 'entrada' AND m.data_movimento >= DATE_TRUNC('month', NOW())), 0) AS entradas_mes,
    COALESCE((SELECT SUM(m.valor) FROM tesouraria.movimentos m WHERE m.conta_id = cb.id AND m.tipo = 'saida' AND m.data_movimento >= DATE_TRUNC('month', NOW())), 0) AS saidas_mes
FROM tesouraria.contas_bancarias cb WHERE cb.ativa = TRUE;

-- View: Aprovações pendentes com prioridade
CREATE OR REPLACE VIEW v_aprovacoes_pendentes AS
SELECT
    f.id,
    f.numero,
    f.tipo,
    f.modulo_origem,
    f.valor,
    f.descricao,
    f.prioridade,
    f.submetido_em,
    EXTRACT(EPOCH FROM (NOW() - f.submetido_em))/3600 AS horas_pendente,
    u.nome AS submetido_por_nome,
    u.departamento AS departamento_origem
FROM aprovacoes.fluxos f
JOIN auth.utilizadores u ON u.id = f.submetido_por
WHERE f.estado = 'pendente'
ORDER BY
    CASE f.prioridade WHEN 'critico' THEN 1 WHEN 'urgente' THEN 2 WHEN 'normal' THEN 3 ELSE 4 END,
    f.submetido_em ASC;

-- View: Desempenho dos cobradores no mês
CREATE OR REPLACE VIEW v_desempenho_cobradores AS
SELECT
    u.id AS cobrador_id,
    u.nome AS cobrador_nome,
    z.nome AS zona_nome,
    COUNT(r.id) AS total_cobracoes,
    COALESCE(SUM(r.valor_pago), 0) AS total_cobrado,
    COALESCE(m.meta_cobracoes, 0) AS meta_cobracoes,
    COALESCE(m.meta_valor, 0) AS meta_valor,
    CASE WHEN COALESCE(m.meta_valor, 0) > 0
         THEN ROUND((COALESCE(SUM(r.valor_pago), 0) / m.meta_valor) * 100, 1)
         ELSE 0 END AS pct_meta_valor
FROM auth.utilizadores u
JOIN auth.perfis p ON p.id = u.perfil_id AND p.codigo = 'COBRADOR'
LEFT JOIN cobradores.atribuicoes a ON a.cobrador_id = u.id AND a.ativa = TRUE
LEFT JOIN cobradores.zonas z ON z.id = a.zona_id
LEFT JOIN tributacao.recibos r ON r.cobrador_id = u.id
    AND DATE_TRUNC('month', r.emitido_em) = DATE_TRUNC('month', NOW())
LEFT JOIN cobradores.metas m ON m.cobrador_id = u.id
    AND m.mes = EXTRACT(MONTH FROM NOW())
    AND m.ano = EXTRACT(YEAR FROM NOW())
WHERE u.ativo = TRUE
GROUP BY u.id, u.nome, z.nome, m.meta_cobracoes, m.meta_valor;

-- View: Top devedores
CREATE OR REPLACE VIEW v_top_devedores AS
SELECT
    c.id,
    c.nuit,
    c.nome,
    c.bairro,
    c.telefone,
    COUNT(l.id) AS num_lancamentos_abertos,
    SUM(l.valor_total) AS divida_total,
    MIN(l.data_vencimento) AS vencimento_mais_antigo,
    CURRENT_DATE - MIN(l.data_vencimento) AS dias_atraso
FROM tributacao.contribuintes c
JOIN tributacao.lancamentos l ON l.contribuinte_id = c.id
WHERE l.estado IN ('pendente', 'em_falta')
GROUP BY c.id, c.nuit, c.nome, c.bairro, c.telefone
ORDER BY divida_total DESC;

-- View: Execução orçamental
CREATE OR REPLACE VIEW v_execucao_orcamental AS
SELECT
    r.codigo,
    r.descricao,
    r.tipo,
    r.dotacao_atual,
    r.executado,
    r.dotacao_atual - r.executado AS saldo,
    CASE WHEN r.dotacao_atual > 0
         THEN ROUND((r.executado / r.dotacao_atual) * 100, 1)
         ELSE 0 END AS pct_execucao,
    o.ano
FROM orcamento.rubricas r
JOIN orcamento.orcamentos o ON o.id = r.orcamento_id
WHERE o.estado = 'em_execucao';

-- View: Estado das obras
CREATE OR REPLACE VIEW v_estado_obras AS
SELECT
    p.id,
    p.numero,
    p.nome,
    p.valor_total,
    p.valor_pago,
    p.exec_fisica_pct,
    p.exec_fin_pct,
    p.estado,
    p.data_inicio,
    p.data_fim_prev,
    CASE WHEN p.data_fim_prev < CURRENT_DATE AND p.estado = 'em_execucao'
         THEN CURRENT_DATE - p.data_fim_prev
         ELSE 0 END AS dias_atraso,
    f.nome AS fornecedor_nome,
    u.nome AS fiscal_nome
FROM obras.projectos p
LEFT JOIN ugea.contratos c ON c.id = p.contrato_id
LEFT JOIN ugea.fornecedores f ON f.id = c.fornecedor_id
LEFT JOIN auth.utilizadores u ON u.id = p.fiscal_id
WHERE p.estado NOT IN ('cancelado');

-- ============================================================
-- FUNÇÕES ÚTEIS
-- ============================================================

-- Função: Gerar número de sequência
CREATE OR REPLACE FUNCTION gerar_numero(prefixo TEXT, tabela TEXT, coluna TEXT)
RETURNS TEXT AS $$
DECLARE
    seq INTEGER;
    num TEXT;
BEGIN
    EXECUTE format('SELECT COALESCE(MAX(CAST(RIGHT(%I, 5) AS INTEGER)), 0) + 1 FROM %s', coluna, tabela) INTO seq;
    num := prefixo || TO_CHAR(EXTRACT(YEAR FROM NOW()), 'FM0000') || '-' || LPAD(seq::TEXT, 5, '0');
    RETURN num;
END;
$$ LANGUAGE plpgsql;

-- Função: Calcular valor extenso (MZN)
CREATE OR REPLACE FUNCTION valor_extenso(valor NUMERIC)
RETURNS TEXT AS $$
DECLARE
    inteiro BIGINT;
    centavos INTEGER;
    resultado TEXT;
    unidades TEXT[] := ARRAY['', 'um', 'dois', 'três', 'quatro', 'cinco', 'seis', 'sete', 'oito', 'nove',
                              'dez', 'onze', 'doze', 'treze', 'catorze', 'quinze', 'dezasseis', 'dezassete', 'dezoito', 'dezanove'];
    dezenas TEXT[] := ARRAY['', '', 'vinte', 'trinta', 'quarenta', 'cinquenta', 'sessenta', 'setenta', 'oitenta', 'noventa'];
BEGIN
    inteiro := FLOOR(valor)::BIGINT;
    centavos := ROUND((valor - inteiro) * 100)::INTEGER;
    -- Simplificado para valores até 999.999
    IF inteiro = 0 THEN resultado := 'zero';
    ELSIF inteiro < 20 THEN resultado := unidades[inteiro + 1];
    ELSIF inteiro < 100 THEN
        resultado := dezenas[inteiro / 10 + 1];
        IF inteiro % 10 > 0 THEN resultado := resultado || ' e ' || unidades[inteiro % 10 + 1]; END IF;
    ELSIF inteiro < 1000 THEN
        resultado := unidades[inteiro / 100 + 1] || 'centos';
        IF inteiro % 100 > 0 THEN resultado := resultado || ' e ' || valor_extenso((inteiro % 100)::NUMERIC); END IF;
    ELSIF inteiro < 1000000 THEN
        resultado := valor_extenso((inteiro / 1000)::NUMERIC) || ' mil';
        IF inteiro % 1000 > 0 THEN resultado := resultado || ' e ' || valor_extenso((inteiro % 1000)::NUMERIC); END IF;
    ELSE
        resultado := valor_extenso((inteiro / 1000000)::NUMERIC) || ' milhões';
        IF inteiro % 1000000 > 0 THEN resultado := resultado || ' e ' || valor_extenso((inteiro % 1000000)::NUMERIC); END IF;
    END IF;
    resultado := resultado || ' meticais';
    IF centavos > 0 THEN resultado := resultado || ' e ' || centavos::TEXT || ' centavos'; END IF;
    RETURN resultado;
END;
$$ LANGUAGE plpgsql;

-- Função: Trigger para audit log automático
CREATE OR REPLACE FUNCTION fn_audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO auditoria.log (acao, tabela, registo_id, dados_antes, dados_depois)
    VALUES (
        TG_OP,
        TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME,
        CASE TG_OP WHEN 'DELETE' THEN OLD.id::TEXT ELSE NEW.id::TEXT END,
        CASE TG_OP WHEN 'INSERT' THEN NULL ELSE to_jsonb(OLD) END,
        CASE TG_OP WHEN 'DELETE' THEN NULL ELSE to_jsonb(NEW) END
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Função: Actualizar saldo após movimento
CREATE OR REPLACE FUNCTION fn_actualizar_saldo()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.tipo = 'entrada' THEN
        UPDATE tesouraria.contas_bancarias
        SET saldo_atual = saldo_atual + NEW.valor, saldo_data = NOW()
        WHERE id = NEW.conta_id;
    ELSIF NEW.tipo = 'saida' THEN
        UPDATE tesouraria.contas_bancarias
        SET saldo_atual = saldo_atual - NEW.valor, saldo_data = NOW()
        WHERE id = NEW.conta_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Função: Calcular IRPS (simplificado Moçambique)
CREATE OR REPLACE FUNCTION calcular_irps(salario_bruto NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    irps NUMERIC := 0;
BEGIN
    IF salario_bruto <= 20249 THEN irps := 0;
    ELSIF salario_bruto <= 30374 THEN irps := (salario_bruto - 20249) * 0.10;
    ELSIF salario_bruto <= 40499 THEN irps := 1012.50 + (salario_bruto - 30374) * 0.15;
    ELSIF salario_bruto <= 60749 THEN irps := 2531.25 + (salario_bruto - 40499) * 0.20;
    ELSIF salario_bruto <= 121499 THEN irps := 6581.25 + (salario_bruto - 60749) * 0.25;
    ELSE irps := 21768.75 + (salario_bruto - 121499) * 0.32;
    END IF;
    RETURN ROUND(irps, 2);
END;
$$ LANGUAGE plpgsql;

-- ============================================================
-- TRIGGERS
-- ============================================================

-- Trigger: Audit em tabelas críticas
CREATE TRIGGER trg_audit_lancamentos
AFTER INSERT OR UPDATE OR DELETE ON tributacao.lancamentos
FOR EACH ROW EXECUTE FUNCTION fn_audit_trigger();

CREATE TRIGGER trg_audit_pagamentos
AFTER INSERT OR UPDATE OR DELETE ON tesouraria.pagamentos
FOR EACH ROW EXECUTE FUNCTION fn_audit_trigger();

CREATE TRIGGER trg_audit_aprovacoes
AFTER INSERT OR UPDATE OR DELETE ON aprovacoes.fluxos
FOR EACH ROW EXECUTE FUNCTION fn_audit_trigger();

CREATE TRIGGER trg_audit_cheques
AFTER INSERT OR UPDATE OR DELETE ON tesouraria.cheques
FOR EACH ROW EXECUTE FUNCTION fn_audit_trigger();

-- Trigger: Actualizar saldo automaticamente
CREATE TRIGGER trg_saldo_movimento
AFTER INSERT ON tesouraria.movimentos
FOR EACH ROW EXECUTE FUNCTION fn_actualizar_saldo();

-- Trigger: Atualizar timestamp
CREATE OR REPLACE FUNCTION fn_update_timestamp()
RETURNS TRIGGER AS $$
BEGIN NEW.atualizado_em = NOW(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_ts_lancamentos BEFORE UPDATE ON tributacao.lancamentos FOR EACH ROW EXECUTE FUNCTION fn_update_timestamp();
CREATE TRIGGER trg_ts_pagamentos  BEFORE UPDATE ON tesouraria.pagamentos   FOR EACH ROW EXECUTE FUNCTION fn_update_timestamp();
CREATE TRIGGER trg_ts_contratos   BEFORE UPDATE ON ugea.contratos          FOR EACH ROW EXECUTE FUNCTION fn_update_timestamp();
CREATE TRIGGER trg_ts_obras       BEFORE UPDATE ON obras.projectos         FOR EACH ROW EXECUTE FUNCTION fn_update_timestamp();
CREATE TRIGGER trg_ts_pedidos     BEFORE UPDATE ON licencas.pedidos        FOR EACH ROW EXECUTE FUNCTION fn_update_timestamp();
CREATE TRIGGER trg_ts_reclamacoes BEFORE UPDATE ON reclamacoes.reclamacoes FOR EACH ROW EXECUTE FUNCTION fn_update_timestamp();