CREATE OR REPLACE VIEW public.budget_actuals_monthly AS
SELECT b.association_id,
    date_trunc('month'::text, b.bill_date::timestamp with time zone)::date AS period_month,
    'expense'::text AS account_type,
    b.expense_account_id AS gl_account_id,
    coa.account_name AS category_name,
    sum(COALESCE(b.amount, 0::numeric)) AS amount
   FROM bills b
     LEFT JOIN chart_of_accounts coa ON coa.id = b.expense_account_id
  WHERE b.bill_date IS NOT NULL AND (COALESCE(lower(b.status), ''::text) <> ALL (ARRAY['void'::text, 'voided'::text, 'cancelled'::text, 'canceled'::text, 'rejected'::text, 'draft'::text]))
  GROUP BY b.association_id, (date_trunc('month'::text, b.bill_date::timestamp with time zone)), b.expense_account_id, coa.account_name
UNION ALL
 SELECT ap.association_id,
    date_trunc('month'::text, COALESCE(ap.payment_date, ap.created_at::date)::timestamp with time zone)::date AS period_month,
    'income'::text AS account_type,
    NULL::uuid AS gl_account_id,
    'Owner Payments'::text AS category_name,
    sum(COALESCE(ap.amount, 0::numeric)) AS amount
   FROM admin_payments ap
  WHERE COALESCE(lower(ap.status), ''::text) <> ALL (ARRAY['void'::text, 'voided'::text, 'cancelled'::text, 'canceled'::text, 'failed'::text, 'refunded'::text])
  GROUP BY ap.association_id, (date_trunc('month'::text, COALESCE(ap.payment_date, ap.created_at::date)::timestamp with time zone))
UNION ALL
 SELECT ci.association_id,
    date_trunc('month'::text, COALESCE(ci.issue_date, ci.paid_date)::timestamp with time zone)::date AS period_month,
    'income'::text AS account_type,
    NULL::uuid AS gl_account_id,
    'Invoiced Income'::text AS category_name,
    sum(COALESCE(ci.total, ci.amount, 0::numeric)) AS amount
   FROM client_invoices ci
  WHERE COALESCE(ci.issue_date, ci.paid_date) IS NOT NULL AND (COALESCE(lower(ci.status), ''::text) <> ALL (ARRAY['void'::text, 'voided'::text, 'cancelled'::text, 'canceled'::text, 'draft'::text]))
  GROUP BY ci.association_id, (date_trunc('month'::text, COALESCE(ci.issue_date, ci.paid_date)::timestamp with time zone));