DatabaseCreation: Customer subscription status timeline.sql

create view public.v_customer_subscription_status_timeline
            (customer_id, customer_name, email, account_number, subscription_number, plan_name, status_history_id,
             old_status, new_status, changed_at, reason, changed_by_employee)
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,
       ssh.status_history_id,
       ssh.old_status,
       ssh.new_status,
       ssh.changed_at,
       ssh.reason,
       (e.first_name || ' '::text) || e.last_name                           AS changed_by_employee
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
         JOIN subscription_status_history ssh ON ssh.subscription_id = s.subscription_id
         LEFT JOIN employees e ON e.employee_id = ssh.changed_by_employee_id;

alter table public.v_customer_subscription_status_timeline
    owner to postgres;