DatabaseCreation: 12 Customer support ticket timeline.sql

DROP VIEW IF EXISTS public.v_customer_support_ticket_timeline;

CREATE VIEW public.v_customer_support_ticket_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,
    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,
    ROUND(EXTRACT(EPOCH FROM (t.closed_at - t.created_at))::numeric / 3600.0, 2) AS resolution_hours,
    owner.first_name || ' ' || owner.last_name AS assigned_employee,
    ci.interaction_id,
    ci.interaction_type,
    ci.channel,
    ci.interaction_time,
    ci.notes,
    ci.old_status,
    ci.new_status,
    actor.first_name || ' ' || actor.last_name AS interaction_employee,
    ROW_NUMBER() OVER (PARTITION BY t.ticket_id ORDER BY ci.interaction_time NULLS LAST, ci.interaction_id) AS interaction_sequence
FROM public.customers c
JOIN public.crm_tickets t ON t.customer_id = c.customer_id
LEFT JOIN public.accounts a ON a.account_id = t.account_id
LEFT JOIN public.subscriptions s ON s.subscription_id = t.subscription_id
LEFT JOIN public.plans p ON p.plan_id = s.plan_id
LEFT JOIN public.employees owner ON owner.employee_id = t.assigned_employee_id
LEFT JOIN public.crm_interactions ci ON ci.ticket_id = t.ticket_id
LEFT JOIN public.employees actor ON actor.employee_id = ci.employee_id;