-- ============================================================
-- Migration: fn_generate_general_attendees_report
--
-- Single-session "General Attendees" report — one row per attendee who joined
-- (or, for a known-but-not-yet-joined registrant, is expected to join) via the
-- shared/common Zoom link rather than a personal registration link, i.e. every
-- zoom_analytics_attendee_summary row with registration_id IS NULL for the
-- session. This is the downloadable-report counterpart to the admin "General
-- Attendees" screen (ZoomAnalyticsFacadeService.getGeneralAttendeesV1) — that
-- screen's own reports list is empty today (see ZOOM_ANALYTICS_SESSION_REPORTS'
-- docblock) precisely because none of the other session-attendance-family
-- reports/functions read general rows; this migration is what fills that gap.
--
-- Backs 3 of the 4 general-attendee report purposes (general-attendees,
-- general-session-report, general-late-comer-report — the same "one enriched
-- function, purpose picks a column subset" shape vw_program_session_attendance_report
-- gives the registered-seeker family), by returning every column any of those 3
-- purposes need in one call; ReportsService/the field-defs registry pick the subset
-- per purpose. The 4th (general-session-dropoffs, leave-reason detail from raw
-- live events) is a genuinely different data source, so it gets its own function
-- (migration 8), same split as session-report/late-comer-report vs session-dropoffs
-- for the registered family.
--
-- Identity resolution mirrors getAttendeeTable's own fallback chain exactly
-- (zoom-live-event-analytics-provider.base.ts), rather than re-deriving it from
-- the tagged email in SQL: every general/unregistered Zoom join is tagged
-- "<local>+gen<userId|sequenceNumber>p<programId>@<domain>"
-- (ZoomRegistrantEmailUtil.buildZoomUserRegistrantEmail /
-- buildZoomPlaceholderRegistrantEmail) — but a role-matched staff user's
-- userId and a placeholder batch slot's sequenceNumber share the exact same
-- "+gen<number>p<number>" shape, so the tagged number alone can't tell them
-- apart (and could coincidentally collide with an unrelated user's id). The
-- disambiguator already lives in zoom_generated_registrant_link — one row per
-- pre-generated recipient, created with its own explicit sourceType
-- ('ROLE'/'PLACEHOLDER') and the ROLE user's real sourceEmail/sourceMobile/
-- displayName captured at generation time — so this function joins that table
-- by (session, normalized email) instead of parsing the tag, same as the live
-- table does. A row with no match at all is a genuine unidentified walk-in;
-- its own zoom_display_name/email/mobile (Zoom's own report, never a real
-- email/phone) is all that's available for it.
--
-- Column-by-column fallback (matches getAttendeeTable's row-building exactly):
--   fullName — zas.full_name (only ever set for a known-but-not-joined row,
--     see notJoinedGeneralAttendeePayload) → link.display_name (a known
--     registrant who has joined) → zas.zoom_display_name (unidentified walk-in).
--   email/mobile — link.source_email/source_mobile (the ROLE user's real
--     contact info) → zas.email/mobile (Zoom's own tagged address / whatever
--     it collected, never a real phone for a walk-in).
--   sourceTag — link.role_key ('SYSTEM' for a PLACEHOLDER row per that
--     entity's own docblock, the actual role key for a ROLE row) when matched,
--     else 'Unidentified'. Flattened to one text column here (the live
--     screen's own sourceTag is a {sourceType, label} pair) since a downloadable
--     report has no nested-object column type.
--   attendance — zas.is_system_attended only; general rows have no registration
--     and therefore no rm/coordinator/final marks to blend in (unlike
--     vw_program_session_attendance_report's 4-way attendanceStatus).
--   lateBy/lateBySeconds/joinCount — same formulas/gating migration 5 added to
--     vw_program_session_attendance_report for the registered family (NULL until
--     the session finishes; lateBy/lateBySeconds also NULL if never joined or
--     joined at/before the scheduled start; joinCount is rejoin_count + 1, or 0
--     if never joined). Requires joining program_session for starts_at/ends_at,
--     which the un-enriched version of this function didn't need.
--   lastDropoffAt — zas.last_dropoff_at, straight passthrough, no gating (matches
--     migration 5's own ungated dropoffCount/lastDropoffAt columns).
--
-- Email match uses the same normalization findActiveMapBySession's own map key
-- applies (LOWER(TRIM(...))) so it lines up with that repository without
-- re-deriving a different rule here. status = 'registered' (lowercase — see
-- OnlineSessionRegistrationStatus.REGISTERED) and deleted_at IS NULL restrict
-- the join to this session's currently-active generated links, same scope
-- findActiveMapBySession itself enforces.
--
-- sessionId is required by the caller (ReportsService.generateGeneralAttendeesReport
-- and its 2 siblings enforce this — RPT_BR_003 if missing, same as session-dropoffs);
-- this function still tolerates a NULL p_session_id by falling back to program-wide,
-- since nothing here strictly depends on the single-session assumption other than
-- the caller's contract (same tolerance migration 6 gives fn_generate_session_dropoff_report).
--
-- Every TEXT-declared output column is explicitly cast to ::TEXT — the same
-- exact-type-match requirement migration 6 documents at length (CITEXT/
-- VARCHAR(n) columns and COALESCE()s over them return their own type, not text).
-- ============================================================

CREATE OR REPLACE FUNCTION fn_generate_general_attendees_report(p_filters JSONB DEFAULT '{}')
RETURNS TABLE(
  "fullName"        TEXT,
  "email"           TEXT,
  "mobile"          TEXT,
  "attendance"      TEXT,
  "joinedAt"        TIMESTAMPTZ,
  "noOfDevices"     INT,
  "dropoffCount"    INT,
  "durationSeconds" INT,
  "lateBy"          INT,
  "lateBySeconds"   INT,
  "joinCount"       INT,
  "lastDropoffAt"   TIMESTAMPTZ,
  "sourceTag"       TEXT
)
LANGUAGE plpgsql AS $$
DECLARE
  p_program_id INT := (p_filters->>'programId')::INT;
  p_session_id INT := (p_filters->>'sessionId')::INT;
BEGIN
  RETURN QUERY
  SELECT
    COALESCE(zas.full_name, link.display_name, zas.zoom_display_name)::TEXT AS "fullName",
    COALESCE(link.source_email, zas.email)::TEXT                           AS "email",
    COALESCE(link.source_mobile, zas.mobile)::TEXT                         AS "mobile",
    CASE WHEN zas.is_system_attended THEN 'Attended' ELSE 'Not Attended' END::TEXT AS "attendance",
    zas.joined_at                                                          AS "joinedAt",
    zas.no_of_devices                                                      AS "noOfDevices",
    zas.dropoff_count                                                      AS "dropoffCount",
    zas.duration_seconds                                                   AS "durationSeconds",
    CASE
      WHEN ps.ends_at IS NULL OR ps.ends_at >= NOW() THEN NULL
      WHEN zas.joined_at IS NULL OR zas.joined_at <= ps.starts_at THEN NULL
      ELSE ROUND(EXTRACT(EPOCH FROM (zas.joined_at - ps.starts_at)) / 60.0)::INT
    END                                                                    AS "lateBy",
    CASE
      WHEN ps.ends_at IS NULL OR ps.ends_at >= NOW() THEN NULL
      WHEN zas.joined_at IS NULL OR zas.joined_at <= ps.starts_at THEN NULL
      ELSE ROUND(EXTRACT(EPOCH FROM (zas.joined_at - ps.starts_at)))::INT
    END                                                                    AS "lateBySeconds",
    CASE
      WHEN ps.ends_at IS NULL OR ps.ends_at >= NOW() THEN NULL
      WHEN zas.joined_at IS NULL THEN 0
      ELSE COALESCE(zas.rejoin_count, 0) + 1
    END                                                                    AS "joinCount",
    zas.last_dropoff_at                                                    AS "lastDropoffAt",
    COALESCE(link.role_key, 'Unidentified')::TEXT                          AS "sourceTag"
  FROM zoom_analytics_attendee_summary zas
  JOIN program_session ps
    ON ps.id = zas.session_id
  LEFT JOIN zoom_generated_registrant_link link
    ON link.program_session_id = zas.session_id
   AND LOWER(TRIM(link.registrant_email)) = LOWER(TRIM(zas.email))
   AND link.status = 'registered'
   AND link.deleted_at IS NULL
  WHERE zas.registration_id IS NULL
    AND zas.program_id = p_program_id
    AND (p_session_id IS NULL OR zas.session_id = p_session_id)
  ORDER BY "fullName" NULLS LAST;
END;
$$;
