DatabaseCreation: 14 Customer financial summary.sql

DROP VIEW IF EXISTS public.v_customer_financial_summary;

CREATE VIEW public.v_customer_financial_summary AS
WITH invoice_balances AS (
    SELECT i.account_id,
           i.invoice_id,
           i.issue_date,
           i.status,
           i.total_amount,
           COALESCE(SUM(p.amount) FILTER (WHERE p.status = 'completed'), 0) AS paid_amount,
           GREATEST(
               i.total_amount - COALESCE(SUM(p.amount) FILTER (WHERE p.status = 'completed'), 0),
               0
           ) AS remaining_amount,
           MAX(p.payment_date) FILTER (WHERE p.status = 'completed') AS latest_payment_at
    FROM public.invoices i
    LEFT JOIN public.payments p ON p.invoice_id = i.invoice_id
    WHERE i.status <> 'cancelled'
    GROUP BY i.account_id, i.invoice_id, i.issue_date, i.status, i.total_amount
),
invoice_summary AS (
    SELECT account_id,
           COUNT(*) AS invoice_count,
           SUM(total_amount) AS total_billed,
           SUM(paid_amount) AS total_paid,
           SUM(remaining_amount) AS outstanding_balance,
           SUM(remaining_amount) FILTER (WHERE status = 'overdue') AS overdue_invoice_value,
           COUNT(*) FILTER (WHERE status = 'overdue' AND remaining_amount > 0) AS overdue_invoice_count,
           MAX(issue_date) AS latest_invoice_date,
           MAX(latest_payment_at) AS latest_payment_at
    FROM invoice_balances
    GROUP BY account_id
)
SELECT
    c.customer_id,
    CASE WHEN c.customer_type = 'business' THEN c.company_name ELSE c.first_name || ' ' || c.last_name END AS customer_name,
    c.customer_type,
    a.account_id,
    a.account_number,
    a.account_status,
    a.credit_limit,
    a.current_balance,
    COALESCE(i.invoice_count, 0) AS invoice_count,
    COALESCE(i.total_billed, 0) AS total_billed,
    COALESCE(i.total_paid, 0) AS total_paid,
    COALESCE(i.outstanding_balance, 0) AS calculated_outstanding_balance,
    COALESCE(i.overdue_invoice_count, 0) AS overdue_invoice_count,
    COALESCE(i.overdue_invoice_value, 0) AS overdue_invoice_value,
    i.latest_invoice_date,
    i.latest_payment_at
FROM public.customers c
JOIN public.accounts a ON a.customer_id = c.customer_id
LEFT JOIN invoice_summary i ON i.account_id = a.account_id;