DatabaseCreation: 07 Customer invoice detail.sql

DROP VIEW IF EXISTS public.v_customer_invoice_detail;

CREATE VIEW public.v_customer_invoice_detail AS
WITH payment_summary AS (
    SELECT invoice_id,
           SUM(amount) FILTER (WHERE status = 'completed') AS completed_payment_amount,
           COUNT(*) FILTER (WHERE status = 'completed') AS completed_payment_count,
           MAX(payment_date) FILTER (WHERE status = 'completed') AS last_payment_at
    FROM public.payments
    WHERE invoice_id IS NOT NULL
    GROUP BY invoice_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,
    a.account_number,
    bc.cycle_name AS billing_cycle,
    i.invoice_id,
    i.invoice_number,
    i.billing_period_start,
    i.billing_period_end,
    i.issue_date,
    i.due_date,
    i.total_amount AS invoice_total,
    i.tax_amount,
    i.discount_amount,
    i.status AS invoice_status,
    COALESCE(ps.completed_payment_amount, 0) AS invoice_paid_amount,
    GREATEST(i.total_amount - COALESCE(ps.completed_payment_amount, 0), 0) AS invoice_remaining_amount,
    ps.completed_payment_count,
    ps.last_payment_at,
    ii.invoice_item_id,
    ii.item_type,
    ii.description AS item_description,
    ii.quantity,
    ii.unit_price,
    ii.line_amount,
    s.subscription_number,
    p.plan_name
FROM public.customers c
JOIN public.accounts a ON a.customer_id = c.customer_id
LEFT JOIN public.billing_cycles bc ON bc.billing_cycle_id = a.billing_cycle_id
JOIN public.invoices i ON i.account_id = a.account_id
JOIN public.invoice_items ii ON ii.invoice_id = i.invoice_id
LEFT JOIN public.subscriptions s ON s.subscription_id = ii.subscription_id
LEFT JOIN public.plans p ON p.plan_id = s.plan_id
LEFT JOIN payment_summary ps ON ps.invoice_id = i.invoice_id;


COMMENT ON VIEW public.v_customer_invoice_detail IS
'One row per invoice item. Invoice-level payment totals repeat by design and must not be summed across item rows.';