DatabaseCreation: 01 Customer subscription overview.sql

DROP VIEW IF EXISTS public.v_customer_subscription_overview;

CREATE VIEW public.v_customer_subscription_overview AS
SELECT
    c.customer_id,
    CASE
        WHEN c.customer_type = 'business' THEN c.company_name || ' (' || c.first_name || ' ' || c.last_name || ')'
        ELSE c.first_name || ' ' || c.last_name
    END AS customer_name,
    c.customer_type,
    c.email,
    a.account_id,
    a.account_number,
    a.account_status,
    a.current_balance,
    bc.cycle_name AS billing_cycle,
    s.subscription_id,
    s.subscription_number,
    s.status AS subscription_status,
    s.activation_date,
    s.end_date,
    p.plan_name,
    p.monthly_fee,
    con.contract_number,
    con.contract_type,
    con.status AS contract_status,
    current_sim.msisdn,
    current_sim.sim_type,
    current_device.manufacturer AS device_manufacturer,
    current_device.model AS device_model,
    current_device.device_type,
    COALESCE(active_addons.recurring_addon_charge, 0) AS recurring_addon_charge,
    p.monthly_fee + COALESCE(active_addons.recurring_addon_charge, 0) AS total_monthly_recurring_charge
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.subscriptions s ON s.account_id = a.account_id
JOIN public.plans p ON p.plan_id = s.plan_id
LEFT JOIN public.contracts con ON con.contract_id = s.contract_id
LEFT JOIN LATERAL (
    SELECT sc.msisdn, sc.sim_type
    FROM public.sim_card_subscription_history ssh
    JOIN public.sim_cards sc ON sc.sim_id = ssh.sim_id
    WHERE ssh.subscription_id = s.subscription_id
      AND ssh.end_date IS NULL
    ORDER BY ssh.start_date DESC
    LIMIT 1
) current_sim ON TRUE
LEFT JOIN LATERAL (
    SELECT d.manufacturer, d.model, d.device_type
    FROM public.device_assignments da
    JOIN public.devices d ON d.device_id = da.device_id
    WHERE da.subscription_id = s.subscription_id
      AND da.assigned_to IS NULL
    ORDER BY da.assigned_from DESC
    LIMIT 1
) current_device ON TRUE
LEFT JOIN LATERAL (
    SELECT SUM(sa.price_at_activation) AS recurring_addon_charge
    FROM public.subscription_addons sa
    JOIN public.addons ad ON ad.addon_id = sa.addon_id
    WHERE sa.subscription_id = s.subscription_id
      AND sa.status = 'active'
      AND sa.deactivation_date IS NULL
      AND ad.is_recurring
) active_addons ON TRUE;