DatabaseCreation: 08 Customer payment history.sql

DROP VIEW IF EXISTS public.v_customer_payment_history;

CREATE VIEW public.v_customer_payment_history AS
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,
    a.account_number,
    pay.payment_id,
    pay.payment_date,
    pay.amount AS paid_amount,
    pay.status AS payment_status,
    pm.method_name AS payment_method,
    pm.provider_name,
    i.invoice_number,
    i.issue_date,
    i.due_date,
    i.total_amount AS invoice_total,
    i.status AS invoice_status,
    SUM(pay.amount) FILTER (WHERE pay.status = 'completed') OVER (
        PARTITION BY pay.invoice_id ORDER BY pay.payment_date, pay.payment_id
    ) AS cumulative_completed_payment,
    GREATEST(
        i.total_amount - COALESCE(SUM(pay.amount) FILTER (WHERE pay.status = 'completed') OVER (
            PARTITION BY pay.invoice_id ORDER BY pay.payment_date, pay.payment_id
        ), 0),
        0
    ) AS remaining_after_payment
FROM public.customers c
JOIN public.accounts a ON a.customer_id = c.customer_id
JOIN public.payments pay ON pay.account_id = a.account_id
LEFT JOIN public.invoices i ON i.invoice_id = pay.invoice_id
LEFT JOIN public.payment_methods pm ON pm.payment_method_id = pay.payment_method_id;