​

📮 Customer Service Module (cs_*)

​

1. Module Purpose

• Menyimpan ticket issue/pertanyaan dari customer.
• Menyimpan riwayat percakapan antar agent dan customer.

​

2. Tables & Structure

​

3. Key Fields & Relationships

​

3.1 cs_ticket

• Fields: company_id, customer_ref_type, customer_ref_id, subject, status, priority, created_by, assigned_to.
• created_by, assigned_to → core_user.id.

​

3.2 cs_ticket_msg

• Fields: ticket_id, sender_user_id, message, created_at.
• FK: ticket_id → cs_ticket.id, sender_user_id → core_user.id.

​

4. Business Flows

​

4.1 Ticket Lifecycle

1. Ticket dibuat (cs_ticket) oleh agent atau channel publik.
2. Agent balas lewat cs_ticket_msg.
3. Status ticket berubah (open → in_progress → resolved/closed).

​

5. Example Reports (SQL)

​

5.1 Ticket Open per Agent

SELECT
  u.email AS agent,
  COUNT(*) AS open_tickets
FROM cs_ticket t
JOIN core_user u ON u.id = t.assigned_to
WHERE t.company_id = :company_id
  AND t.status IN ('open', 'in_progress')
GROUP BY u.id;

​

5.2 SLA Sederhana (Age in Days)

SELECT
  t.id,
  t.subject,
  t.status,
  DATEDIFF(CURDATE(), t.created_at) AS age_days
FROM cs_ticket t
WHERE t.company_id = :company_id
  AND t.status IN ('open', 'in_progress');

​

5.3 Volume Ticket per Hari

SELECT
  DATE(t.created_at) AS ticket_date,
  COUNT(*)           AS ticket_count
FROM cs_ticket t
WHERE t.company_id = :company_id
  AND t.created_at BETWEEN :start_date AND :end_date
GROUP BY DATE(t.created_at)
ORDER BY ticket_date;