DatabaseCreation: Customer support ticket timeline.sql

create view public.v_customer_support_ticket_timeline
            (customer_id, customer_name, email, account_number, subscription_number, plan_name, ticket_id, ticket_type,
             subject, priority, ticket_status, ticket_created_at, ticket_closed_at, interaction_id, interaction_type,
             channel, interaction_time, notes, old_status, new_status, employee_name)
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,
       t.ticket_id,
       t.ticket_type,
       t.subject,
       t.priority,
       t.status                                                             AS ticket_status,
       t.created_at                                                         AS ticket_created_at,
       t.closed_at                                                          AS ticket_closed_at,
       ci.interaction_id,
       ci.interaction_type,
       ci.channel,
       ci.interaction_time,
       ci.notes,
       ci.old_status,
       ci.new_status,
       (e.first_name || ' '::text) || e.last_name                           AS employee_name
FROM customers c
         JOIN crm_tickets t ON t.customer_id = c.customer_id
         LEFT JOIN subscriptions s ON s.subscription_id = t.subscription_id
         LEFT JOIN accounts a ON a.account_id = COALESCE(t.account_id, s.account_id)
         LEFT JOIN plans p ON p.plan_id = s.plan_id
         LEFT JOIN crm_interactions ci ON ci.ticket_id = t.ticket_id
         LEFT JOIN employees e ON e.employee_id = ci.employee_id;

alter table public.v_customer_support_ticket_timeline
    owner to postgres;