DatabaseCreation: Customer addon invoice detail.sql

create view public.v_customer_addon_invoice_detail
            (customer_id, customer_name, email, account_number, subscription_number, plan_name, addon_name,
             addon_allowance, addon_allowance_unit, addon_activated_on, addon_deactivated_on, addon_price,
             invoice_number, billing_period_start, billing_period_end, invoice_line_description, quantity, unit_price,
             line_amount, payment_date, payment_amount, payment_status)
as
SELECT c.customer_id,
       COALESCE((c.first_name || ' '::text) || c.last_name, c.company_name) AS customer_name,
       c.email,
       a.account_number,
       s.subscription_number,
       p.plan_name,
       ad.addon_name,
       ad.allowance_value                                                   AS addon_allowance,
       ad.allowance_unit                                                    AS addon_allowance_unit,
       sa.activation_date                                                   AS addon_activated_on,
       sa.deactivation_date                                                 AS addon_deactivated_on,
       sa.price_at_activation                                               AS addon_price,
       i.invoice_number,
       i.billing_period_start,
       i.billing_period_end,
       ii.description                                                       AS invoice_line_description,
       ii.quantity,
       ii.unit_price,
       ii.line_amount,
       pay.payment_date,
       pay.amount                                                           AS payment_amount,
       pay.status                                                           AS payment_status
FROM customers c
         JOIN accounts a ON a.customer_id = c.customer_id
         JOIN subscriptions s ON s.account_id = a.account_id
         JOIN plans p ON p.plan_id = s.plan_id
         LEFT JOIN subscription_addons sa ON sa.subscription_id = s.subscription_id
         LEFT JOIN addons ad ON ad.addon_id = sa.addon_id
         JOIN invoices i ON i.account_id = a.account_id
         JOIN invoice_items ii ON ii.invoice_id = i.invoice_id AND ii.subscription_id = s.subscription_id
         JOIN payments pay ON pay.invoice_id = i.invoice_id AND pay.account_id = a.account_id;

alter table public.v_customer_addon_invoice_detail
    owner to postgres;