-- Migration: Session Communication Status — Schema
-- Description: Creates hdb_session_communication_status, an append-only history of session
--              bulk-communication sends. One row per send, keyed by (program, session,
--              purpose); both channels (email + whatsapp) are aggregated into the counts on
--              the row. session_id is NULL for program-level purposes (Welcome, Program
--              completion), which are sent for the whole program, not a session.
-- Idempotent — safe to re-run.

CREATE TABLE IF NOT EXISTS hdb_session_communication_status (
  id                       BIGSERIAL     PRIMARY KEY,
  program_id               BIGINT        NOT NULL,
  session_id               BIGINT        NULL,
  purpose                  VARCHAR(40)   NOT NULL,
  occurrence               VARCHAR(20)   NULL,
  status                   VARCHAR(30)   NOT NULL,
  requested_count          INT           NOT NULL,
  email_enqueued_count     INT           NOT NULL,
  whatsapp_enqueued_count  INT           NOT NULL,
  skipped_count            INT           NOT NULL,
  created_at               TIMESTAMPTZ   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at               TIMESTAMPTZ   NULL     DEFAULT CURRENT_TIMESTAMP,
  created_by               BIGINT        NULL,
  updated_by               BIGINT        NULL
);

COMMENT ON TABLE hdb_session_communication_status IS
  'Append-only history of session bulk-communication sends, keyed by (program, session, purpose); both channels aggregated per row. session_id NULL for program-level Welcome/Program-completion.';
COMMENT ON COLUMN hdb_session_communication_status.session_id IS
  'Target program_session; NULL for program-level purposes (Welcome, Program completion).';
COMMENT ON COLUMN hdb_session_communication_status.purpose IS
  'WELCOME | INVITE | ABSENT | VALUE_CARD | PROGRAM_COMPLETION.';
COMMENT ON COLUMN hdb_session_communication_status.occurrence IS
  'REGULAR | FINAL for occurrence-dependent purposes (Invite/Absent); NULL otherwise.';
COMMENT ON COLUMN hdb_session_communication_status.status IS
  'TRIGGERED = at least one recipient dispatched on some channel; SKIPPED = nothing went out.';

-- Supporting indexes: "which comms have gone out for this session?" and "... for this
-- program + purpose?" (the two read paths).
CREATE INDEX IF NOT EXISTS idx_session_comm_status_program_session
  ON hdb_session_communication_status (program_id, session_id);

CREATE INDEX IF NOT EXISTS idx_session_comm_status_program_purpose
  ON hdb_session_communication_status (program_id, purpose);
