-- Actuals sliced by vendor, aggregated by period.
-- Used by:
--   • carta-budget-actuals Layout F (vendor-view tab) — <period_trunc> from Gate 3a
--   • vendor-actuals parallel fetch (Layouts A–D) — <period_trunc> = MONTH
--
-- Substitutions (never hardcode):
--   <journal_entries_table>  — resolved via dwh:list:tables at Gate 0
--   <entity_name>            — exact FUND_NAME value from Gate 3
--   <period_trunc>           — YEAR | QUARTER | MONTH per Gate 3a aggregation choice
--   <period_start>           — first day of the selected period (YYYY-MM-DD)
--   <period_end>             — last day of the selected period (YYYY-MM-DD)
--
-- Hard rules (same as actuals-by-account-month.sql):
--   - FUND_NAME = exact match, never FIRM_NAME ILIKE
--   - EFFECTIVE_DATE (books date), not POSTED_DATE
--   - Revenue (4xxx) sign-flipped via CASE; expenses kept as-is
--   - ACCOUNT_TYPE >= '4000' restricts to P&L; balance sheet excluded
--   - Reversals preserved as negative postings
--   - COALESCE(VENDOR_NAME, 'Untagged') — NULL vendors roll into the
--     Untagged section; do NOT run a separate query for untagged rows

SELECT
    COALESCE(VENDOR_NAME, 'Untagged')                               AS vendor_name,
    ACCOUNT_TYPE                                                    AS gl_code,
    ACCOUNT_NAME                                                    AS account_name,
    DATE_TRUNC('<period_trunc>', EFFECTIVE_DATE)                    AS period,
    SUM(CASE WHEN LEFT(ACCOUNT_TYPE, 1) = '4' THEN -AMOUNT
             ELSE AMOUNT END)                                       AS signed_amount
FROM <journal_entries_table>
WHERE FUND_NAME = '<entity_name>'
  AND ACCOUNT_TYPE >= '4000'
  AND EFFECTIVE_DATE BETWEEN '<period_start>' AND '<period_end>'
GROUP BY 1, 2, 3, 4
ORDER BY
    -- Named vendors first (alphabetically), Untagged last
    CASE WHEN VENDOR_NAME IS NULL THEN 1 ELSE 0 END,
    vendor_name,
    gl_code,
    period;
