DatabaseCreation: Customer invoice details.sql

create view public.v_customer_invoice_detail
            (customer_id, customer_name, email, account_number, billing_cycle, invoice_id, invoice_number,
             billing_period_start, billing_period_end, issue_date, due_date, invoice_total, tax_amount, discount_amount,
             invoice_status, invoice_item_id, item_type, item_description, quantity, unit_price, line_amount,
             subscription_number, plan_name, payment_date, payment_amount, payment_status, payment_method)
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_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,
       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,
       pay.payment_date,
       pay.amount                                                           AS payment_amount,
       pay.status                                                           AS payment_status,
       pm.method_name                                                       AS payment_method
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 invoices i ON i.account_id = a.account_id
         JOIN invoice_items ii ON ii.invoice_id = i.invoice_id
         LEFT JOIN subscriptions s ON s.subscription_id = ii.subscription_id
         LEFT JOIN plans p ON p.plan_id = s.plan_id
         LEFT JOIN payments pay ON pay.invoice_id = i.invoice_id AND pay.account_id = a.account_id
         LEFT JOIN payment_methods pm ON pm.payment_method_id = pay.payment_method_id;

alter table public.v_customer_invoice_detail
    owner to postgres;