DROP VIEW IF EXISTS public.v_customer_subscription_status_timeline;

CREATE VIEW public.v_customer_subscription_status_timeline 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,
    ssh.status_history_id,
    ssh.old_status,
    ssh.new_status,
    ssh.changed_at,
    ssh.reason,
    e.first_name || ' ' || e.last_name AS changed_by_employee,
    ROW_NUMBER() OVER (PARTITION BY s.subscription_id ORDER BY ssh.changed_at, ssh.status_history_id) AS status_sequence
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_status_history ssh ON ssh.subscription_id = s.subscription_id
LEFT JOIN public.employees e ON e.employee_id = ssh.changed_by_employee_id;