DatabaseCreation: Customer payment history.sql

create view public.v_customer_payment_history
            (customer_id, customer_name, email, account_number, billing_cycle, invoice_number, billing_period_start,
             billing_period_end, invoice_total, invoice_status, payment_id, payment_date, paid_amount, payment_status,
             payment_method, subscription_number, item_type, billed_item_description, billed_item_amount)
as
SELECT c.customer_id,
       COALESCE((c.first_name || ' '::text) || c.last_name, c.company_name) AS customer_name,
       c.email,
       a.account_number,
       bc.cycle_name                                                        AS billing_cycle,
       i.invoice_number,
       i.billing_period_start,
       i.billing_period_end,
       i.total_amount                                                       AS invoice_total,
       i.status                                                             AS invoice_status,
       pay.payment_id,
       pay.payment_date,
       pay.amount                                                           AS paid_amount,
       pay.status                                                           AS payment_status,
       pm.method_name                                                       AS payment_method,
       s.subscription_number,
       ii.item_type,
       ii.description                                                       AS billed_item_description,
       ii.line_amount                                                       AS billed_item_amount
FROM customers c
         JOIN accounts a ON a.customer_id = c.customer_id
         LEFT JOIN billing_cycles bc ON bc.billing_cycle_id = a.billing_cycle_id
         JOIN payments pay ON pay.account_id = a.account_id
         LEFT JOIN invoices i ON i.invoice_id = pay.invoice_id
         LEFT JOIN invoice_items ii ON ii.invoice_id = i.invoice_id
         LEFT JOIN subscriptions s ON s.subscription_id = ii.subscription_id
         LEFT JOIN payment_methods pm ON pm.payment_method_id = pay.payment_method_id;

alter table public.v_customer_payment_history
    owner to postgres;