-- Adds columns for the fuller Commslayer field set confirmed in
-- conversation (closed_tickets_percentage, avg_csat, messages_sent,
-- messages_received, ticket_handle_time) — none of these existed in
-- 0021's commslayer_reports, which only had the narrower field set
-- Code.js's legacy fetchCommslayerMetrics actually requested
-- (closed_tickets, tickets_replied, first_response_time, avg_response_time,
-- resolution_time, one_touch_tickets). This is a genuine schema gap the
-- fetch route needs closed before it can store the full response.
--
-- ticket_handle_time_secs follows the same raw-seconds convention as
-- first_response_secs/avg_response_secs/resolution_secs (0021) — assumed
-- to be another duration field from the API, stored unconverted.
-- closed_tickets_percentage and avg_csat are numeric (not integer) since
-- both are plausibly fractional. messages_sent/messages_received are
-- plain counts, same shape as closed_tickets/tickets_replied.

alter table public.commslayer_reports
  add column if not exists closed_tickets_percentage numeric,
  add column if not exists avg_csat                  numeric,
  add column if not exists messages_sent              integer not null default 0,
  add column if not exists messages_received          integer not null default 0,
  add column if not exists ticket_handle_time_secs     integer;