-- Views de apoio para Dashboard de Orçamento x Realizado
-- Ajuste se necessário conforme estrutura real das tabelas.

-- =====================================================================
-- 1) VIEW: v_realizado_conta_mes
--    - Agrega valores de back.realizado por conta contábil (analítica
--      e sintética), subindo na hierarquia do plano de contas.
--    - Resultado: um valor por empresa/ano/mês/conta (já incluindo
--      somatório dos filhos para contas sintéticas).
-- =====================================================================

CREATE OR REPLACE VIEW back.v_realizado_conta_mes AS
WITH RECURSIVE real_base AS (
    SELECT
        r.cdempresa,
        r.anoref,
        r.mesref,
        pc.cdcontacontabil AS cdconta_filho,
        SUM(CASE WHEN r.tpmovimento = 'C' THEN r.valor ELSE -r.valor END) AS valor_mes
    FROM back.realizado r
    JOIN back.planocontabil pc
      ON pc.cdcontacontabil = r.cdplanocontabil
    WHERE r.flcancelado = 0
    GROUP BY r.cdempresa, r.anoref, r.mesref, pc.cdcontacontabil
),
hier AS (
    -- leaf = conta analítica; node = cada ancestral (inclui a própria)
    SELECT
        pc.cdcontacontabil AS leaf,
        pc.cdcontacontabil AS node,
        pc.cdcontapai
    FROM back.planocontabil pc

    UNION ALL

    SELECT
        h.leaf,
        p.cdcontacontabil AS node,
        p.cdcontapai
    FROM hier h
    JOIN back.planocontabil p
      ON p.cdconta = h.cdcontapai
)
SELECT
    rb.cdempresa,
    rb.anoref,
    rb.mesref,
    h.node AS cdcontacontabil,
      SUM(CASE WHEN r.tpmovimento = 'C' THEN r.valor ELSE -r.valor END) AS valor_mes
FROM real_base rb
JOIN hier h
  ON h.leaf = rb.cdconta_filho
GROUP BY rb.cdempresa, rb.anoref, rb.mesref, h.node;


-- =====================================================================
-- 2) VIEW: v_orcado_conta_mes
--    - Agrega valores de back.orcamentodet por conta analítica e
--      sobe na hierarquia do plano de contas.
--    - Considera apenas contas analíticas (fltipo = 'A') como folhas
--      para evitar dupla contagem de contas sintéticas já calculadas.
--    - Resultado: valor orçado por empresa/ano/mês/conta.
-- =====================================================================

CREATE OR REPLACE VIEW back.v_orcado_conta_mes AS
WITH RECURSIVE orc_base AS (
    SELECT
        o.cdempresa,
        od.anoref,
        od.cdcontacontabil AS cdconta_filho,
        1  AS mesref,
        od.jan AS valor_mes
    FROM back.orcamentodet od
    JOIN back.orcamento o
      ON o.cdorcamento = od.cdorcamento
    JOIN back.planocontabil pc
      ON pc.cdcontacontabil = od.cdcontacontabil
    WHERE o.flexcluido = 0
      AND NOT EXISTS (
          SELECT 1
            FROM back.planocontabil p2
           WHERE p2.cdcontapai = pc.cdconta
      )

    UNION ALL

    SELECT
        o.cdempresa,
        od.anoref,
        od.cdcontacontabil AS cdconta_filho,
        2  AS mesref,
        od.fev AS valor_mes
    FROM back.orcamentodet od
    JOIN back.orcamento o
      ON o.cdorcamento = od.cdorcamento
    JOIN back.planocontabil pc
      ON pc.cdcontacontabil = od.cdcontacontabil
    WHERE o.flexcluido = 0
      AND NOT EXISTS (
          SELECT 1
            FROM back.planocontabil p2
           WHERE p2.cdcontapai = pc.cdconta
      )

    UNION ALL

    SELECT
        o.cdempresa,
        od.anoref,
        od.cdcontacontabil AS cdconta_filho,
        3  AS mesref,
        od.mar AS valor_mes
    FROM back.orcamentodet od
    JOIN back.orcamento o
      ON o.cdorcamento = od.cdorcamento
    JOIN back.planocontabil pc
      ON pc.cdcontacontabil = od.cdcontacontabil
    WHERE o.flexcluido = 0
      AND NOT EXISTS (
          SELECT 1
            FROM back.planocontabil p2
           WHERE p2.cdcontapai = pc.cdconta
      )
    UNION ALL
    SELECT
        o.cdempresa,
        od.anoref,
        od.cdcontacontabil AS cdconta_filho,
        4  AS mesref,
        od.abr AS valor_mes
    FROM back.orcamentodet od
    JOIN back.orcamento o
      ON o.cdorcamento = od.cdorcamento
    JOIN back.planocontabil pc
      ON pc.cdcontacontabil = od.cdcontacontabil
    WHERE o.flexcluido = 0
      AND NOT EXISTS (
          SELECT 1
            FROM back.planocontabil p2
           WHERE p2.cdcontapai = pc.cdconta
      )
    UNION ALL
    SELECT
        o.cdempresa,
        od.anoref,
        od.cdcontacontabil AS cdconta_filho,
        5  AS mesref,
        od.mai AS valor_mes
    FROM back.orcamentodet od
    JOIN back.orcamento o
      ON o.cdorcamento = od.cdorcamento
    JOIN back.planocontabil pc
      ON pc.cdcontacontabil = od.cdcontacontabil
    WHERE o.flexcluido = 0
      AND NOT EXISTS (
          SELECT 1
            FROM back.planocontabil p2
           WHERE p2.cdcontapai = pc.cdconta
      )
    UNION ALL
    SELECT
        o.cdempresa,
        od.anoref,
        od.cdcontacontabil AS cdconta_filho,
        6  AS mesref,
        od.jun AS valor_mes
    FROM back.orcamentodet od
    JOIN back.orcamento o
      ON o.cdorcamento = od.cdorcamento
    JOIN back.planocontabil pc
      ON pc.cdcontacontabil = od.cdcontacontabil
    WHERE o.flexcluido = 0
      AND NOT EXISTS (
          SELECT 1
            FROM back.planocontabil p2
           WHERE p2.cdcontapai = pc.cdconta
      )
    UNION ALL
    SELECT
        o.cdempresa,
        od.anoref,
        od.cdcontacontabil AS cdconta_filho,
        7  AS mesref,
        od.jul AS valor_mes
    FROM back.orcamentodet od
    JOIN back.orcamento o
      ON o.cdorcamento = od.cdorcamento
    JOIN back.planocontabil pc
      ON pc.cdcontacontabil = od.cdcontacontabil
    WHERE o.flexcluido = 0
      AND NOT EXISTS (
          SELECT 1
            FROM back.planocontabil p2
           WHERE p2.cdcontapai = pc.cdconta
      )
    UNION ALL
    SELECT
        o.cdempresa,
        od.anoref,
        od.cdcontacontabil AS cdconta_filho,
        8  AS mesref,
        od.ago AS valor_mes
    FROM back.orcamentodet od
    JOIN back.orcamento o
      ON o.cdorcamento = od.cdorcamento
    JOIN back.planocontabil pc
      ON pc.cdcontacontabil = od.cdcontacontabil
    WHERE o.flexcluido = 0
      AND NOT EXISTS (
          SELECT 1
            FROM back.planocontabil p2
           WHERE p2.cdcontapai = pc.cdconta
      )
    UNION ALL
    SELECT
        o.cdempresa,
        od.anoref,
        od.cdcontacontabil AS cdconta_filho,
        9  AS mesref,
        od.set AS valor_mes
    FROM back.orcamentodet od
    JOIN back.orcamento o
      ON o.cdorcamento = od.cdorcamento
    JOIN back.planocontabil pc
      ON pc.cdcontacontabil = od.cdcontacontabil
    WHERE o.flexcluido = 0
      AND NOT EXISTS (
          SELECT 1
            FROM back.planocontabil p2
           WHERE p2.cdcontapai = pc.cdconta
      )
    UNION ALL
    SELECT
        o.cdempresa,
        od.anoref,
        od.cdcontacontabil AS cdconta_filho,
        10 AS mesref,
        od."out" AS valor_mes
    FROM back.orcamentodet od
    JOIN back.orcamento o
      ON o.cdorcamento = od.cdorcamento
    JOIN back.planocontabil pc
      ON pc.cdcontacontabil = od.cdcontacontabil
    WHERE o.flexcluido = 0
      AND NOT EXISTS (
          SELECT 1
            FROM back.planocontabil p2
           WHERE p2.cdcontapai = pc.cdconta
      )
    UNION ALL
    SELECT
        o.cdempresa,
        od.anoref,
        od.cdcontacontabil AS cdconta_filho,
        11 AS mesref,
        od.nov AS valor_mes
    FROM back.orcamentodet od
    JOIN back.orcamento o
      ON o.cdorcamento = od.cdorcamento
    JOIN back.planocontabil pc
      ON pc.cdcontacontabil = od.cdcontacontabil
    WHERE o.flexcluido = 0
      AND NOT EXISTS (
          SELECT 1
            FROM back.planocontabil p2
           WHERE p2.cdcontapai = pc.cdconta
      )
    UNION ALL
    SELECT
        o.cdempresa,
        od.anoref,
        od.cdcontacontabil AS cdconta_filho,
        12 AS mesref,
        od.dez AS valor_mes
    FROM back.orcamentodet od
    JOIN back.orcamento o
      ON o.cdorcamento = od.cdorcamento
    JOIN back.planocontabil pc
      ON pc.cdcontacontabil = od.cdcontacontabil
    WHERE o.flexcluido = 0
      AND NOT EXISTS (
          SELECT 1
            FROM back.planocontabil p2
           WHERE p2.cdcontapai = pc.cdconta
      )
),
hier AS (
    SELECT
        pc.cdcontacontabil AS leaf,
        pc.cdcontacontabil AS node,
        pc.cdcontapai
    FROM back.planocontabil pc

    UNION ALL

    SELECT
        h.leaf,
        p.cdcontacontabil AS node,
        p.cdcontapai
    FROM hier h
    JOIN back.planocontabil p
      ON p.cdconta = h.cdcontapai
)
SELECT
    ob.cdempresa,
    ob.anoref,
    ob.mesref,
    h.node AS cdcontacontabil,
    SUM(ob.valor_mes) AS valor_mes
FROM orc_base ob
JOIN hier h
  ON h.leaf = ob.cdconta_filho
GROUP BY ob.cdempresa, ob.anoref, ob.mesref, h.node;
