DROP VIEW IF EXISTS public.v_customer_addon_history;

CREATE VIEW public.v_customer_addon_history AS
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,
    s.subscription_number,
    p.plan_name,
    sa.subscription_addon_id,
    ad.addon_name,
    ad.addon_type,
    ad.allowance_value,
    ad.allowance_unit,
    ad.is_recurring,
    sa.price_at_activation,
    sa.activation_date,
    sa.deactivation_date,
    sa.status,
    CASE WHEN sa.deactivation_date IS NULL THEN CURRENT_DATE ELSE sa.deactivation_date END - sa.activation_date + 1 AS active_days
FROM public.customers c
JOIN public.accounts a ON a.customer_id = c.customer_id
JOIN public.subscriptions s ON s.account_id = a.account_id
JOIN public.plans p ON p.plan_id = s.plan_id
JOIN public.subscription_addons sa ON sa.subscription_id = s.subscription_id
JOIN public.addons ad ON ad.addon_id = sa.addon_id;