-- Migration: Zoom Generated Registrant Links — Schema
-- Description: Creates zoom_generated_registrant_link, one row per pre-generated Zoom
--              registrant that has no hdb_program_registration row of its own — either a
--              real staff user matched by role (source_type = ROLE) or a placeholder slot
--              from a caller-named batch (source_type = PLACEHOLDER, e.g. "Staff" x20).
--              Distinct from hdb_program_registration_online_session (real seeker
--              registrations) and from zoom_analytics_attendee_summary (read-only
--              per-session attendance analytics).
-- Idempotent — safe to re-run.

CREATE TABLE IF NOT EXISTS zoom_generated_registrant_link (
  id                       BIGSERIAL     PRIMARY KEY,
  program_id               BIGINT        NOT NULL,
  program_session_id       BIGINT        NOT NULL,
  source_type              VARCHAR(20)   NOT NULL,
  user_id                  BIGINT        NULL,
  role_key                 VARCHAR(50)   NULL,
  batch_name               VARCHAR(100)  NULL,
  sequence_number          INT           NULL,
  display_name             VARCHAR(255)  NOT NULL,
  registrant_email         VARCHAR(255)  NOT NULL,
  source_email             VARCHAR(255)  NULL,
  join_url                 TEXT          NULL,
  external_registrant_id   VARCHAR(255)  NULL,
  status                   VARCHAR(20)   NOT NULL DEFAULT 'registered',
  failure_reason           VARCHAR(500)  NULL,
  created_at               TIMESTAMPTZ   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at               TIMESTAMPTZ   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at               TIMESTAMPTZ   NULL,
  created_by               BIGINT        NULL,
  updated_by               BIGINT        NULL
);

COMMENT ON TABLE zoom_generated_registrant_link IS
  'One row per pre-generated Zoom registrant with no program registration of its own — a role-matched staff user (source_type=ROLE) or a placeholder batch slot (source_type=PLACEHOLDER).';
COMMENT ON COLUMN zoom_generated_registrant_link.source_type IS 'ROLE | PLACEHOLDER.';
COMMENT ON COLUMN zoom_generated_registrant_link.user_id IS 'Real user id — ROLE rows only.';
COMMENT ON COLUMN zoom_generated_registrant_link.role_key IS 'Matched role key that produced this row — ROLE rows only.';
COMMENT ON COLUMN zoom_generated_registrant_link.batch_name IS 'Caller-given batch name (e.g. "Staff") — PLACEHOLDER rows only.';
COMMENT ON COLUMN zoom_generated_registrant_link.sequence_number IS '1..count within the batch — PLACEHOLDER rows only.';
COMMENT ON COLUMN zoom_generated_registrant_link.registrant_email IS 'The tagged/generated email actually sent to Zoom (uniqueness key, not necessarily deliverable).';
COMMENT ON COLUMN zoom_generated_registrant_link.source_email IS 'The real user''s own email — ROLE rows only; kept for a future communication step.';
COMMENT ON COLUMN zoom_generated_registrant_link.status IS 'registered | failed — mirrors hdb_program_registration_online_session.status.';

-- One active row per (session, user) for ROLE rows — re-running the same request skips
-- an already-registered user instead of re-hitting Zoom / duplicating the row.
CREATE UNIQUE INDEX IF NOT EXISTS uq_zoom_generated_link_session_user
  ON zoom_generated_registrant_link (program_session_id, user_id)
  WHERE source_type = 'ROLE' AND deleted_at IS NULL;

-- One active row per (session, batch, sequence) for PLACEHOLDER rows — same idempotency
-- guarantee for re-running a placeholder-batch request.
CREATE UNIQUE INDEX IF NOT EXISTS uq_zoom_generated_link_session_batch_seq
  ON zoom_generated_registrant_link (program_session_id, batch_name, sequence_number)
  WHERE source_type = 'PLACEHOLDER' AND deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_zoom_generated_link_program_session
  ON zoom_generated_registrant_link (program_id, program_session_id);
