-- ============================================================
-- Migration: fn_generate_session_dropoff_report
--
-- Single-session drop-off report — one row per registrant who left the Zoom
-- session at least once, with overall (not portal-vs-Zoom-app-split) join/leave
-- counts and every raw leave_reason they triggered, newest last. Unlike the rest
-- of the session-attendance report family (vw_program_session_attendance_report,
-- fed by the pre-reconciled zoom_analytics_attendee_summary table), this reads
-- zoom_analytics_live_event directly — that's the only place raw per-event
-- leave_reason text lives; the summary table only carries a coarse
-- dropoff_count/last_dropoff_at (see migration 5's header, point 4).
--
-- Registrant identification: every registrant-tagged Zoom join uses a
-- plus-addressed email of the form "<local>+reg<registrationId>@<domain>"
-- (ZoomRegistrantEmailUtil.buildZoomRegistrantEmail) — general/unregistered
-- attendees use a different "+gen..." tag and are excluded by the
-- `email ILIKE '%+reg%'` filter, same as the reference query this migration
-- is based on. Scoped to registered seekers only (seat_allocated, not deleted),
-- same universe as vw_program_session_attendance_report.
--
-- sessionId is required by the caller (ReportsService.generateSessionDropoffsReport
-- enforces this — RPT_BR_003 if missing); 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.
--
-- "dropoffCount" (placed after "joinCount") is zoom_analytics_attendee_summary's
-- device-aware full-drop-off-transition count for this registrant × session — the
-- same column migration 5 added to vw_program_session_attendance_report. The raw
-- LEFT-event tally (leave_count) is NOT exposed as its own output column — it's still
-- computed internally (below) to drive the mid-session-drop-off HAVING filter and the
-- ORDER BY, but dropoffCount is the number actually surfaced.
--
-- Every TEXT-declared output column is explicitly cast to ::TEXT in the SELECT below.
-- A RETURNS TABLE function (unlike a plain view, which just infers its column types)
-- enforces an EXACT type match on RETURN QUERY — CITEXT, VARCHAR(n), and the VARCHAR
-- STRING_AGG(varchar_column) returns are all real-world types that hit this in practice
-- (reg.full_name is CITEXT per database/date-wise-scripts.sql; reg.mobile_number/
-- reg.email_address/users.org_usr_name/zoom_analytics_live_event.email are all
-- VARCHAR(n); leave_reason is VARCHAR(512), so STRING_AGG over it returns VARCHAR, not
-- TEXT) — every one throws "Returned type ... does not match expected type text in
-- column N" without the cast. Cast everything up front rather than discovering each
-- mismatch one deploy at a time.
--
-- LEFT events are only counted as a drop-off when they happened BEFORE the session's
-- own scheduled end (program_session.ends_at) — mirrors midSessionDropoffs()'s cutoff in
-- zoom-live-event-analytics-provider.base.ts: everyone gets a LEFT event when the host
-- ends the meeting or it simply concludes, and that's the session ending normally, not a
-- per-seeker drop-off. A registrant whose only LEFT event(s) land at/after ends_at is
-- excluded entirely (same HAVING condition), same as if they'd never left at all. Applies
-- to leave_count/leave_reasons only — join_count is left unfiltered by time.
--
-- DROP FUNCTION first, then CREATE (not a plain CREATE OR REPLACE): Postgres refuses to
-- CREATE OR REPLACE across ANY change to the OUT/RETURNS TABLE column list — with "cannot
-- change return type of existing function" (SQLSTATE 42P13) — unless the new signature is
-- character-for-character identical to whatever is currently in the database. This only
-- bites if the live database already has a function under this name from outside this
-- migration history (e.g. an earlier manually-applied version) — dropping first makes this
-- migration succeed regardless of that starting shape.
-- ============================================================

DROP FUNCTION IF EXISTS fn_generate_session_dropoff_report(JSONB);

CREATE OR REPLACE FUNCTION fn_generate_session_dropoff_report(p_filters JSONB DEFAULT '{}')
RETURNS TABLE(
  "registrationId"  BIGINT,
  "fullName"        TEXT,
  "phone"           TEXT,
  "email"           TEXT,
  "rmName"          TEXT,
  "zoomTaggedEmail" TEXT,
  "joinCount"       BIGINT,
  "dropoffCount"    INT,
  "leaveReasons"    TEXT
)
LANGUAGE plpgsql AS $$
DECLARE
  p_program_id INT := (p_filters->>'programId')::INT;
  p_session_id INT := (p_filters->>'sessionId')::INT;
BEGIN
  RETURN QUERY
  WITH dropoffs AS (
    SELECT
      e.email,
      COUNT(*) FILTER (WHERE e.event_type = 'JOINED') AS join_count,
      COUNT(*) FILTER (
        WHERE e.event_type = 'LEFT' AND (ps.ends_at IS NULL OR e.occurred_at < ps.ends_at)
      ) AS leave_count,
      STRING_AGG(e.leave_reason, '; ' ORDER BY e.occurred_at) FILTER (
        WHERE e.event_type = 'LEFT' AND (ps.ends_at IS NULL OR e.occurred_at < ps.ends_at)
      ) AS leave_reasons
    FROM zoom_analytics_live_event e
    JOIN program_session ps ON ps.id = e.session_id
    WHERE e.program_id = p_program_id
      AND (p_session_id IS NULL OR e.session_id = p_session_id)
      AND e.email ILIKE '%+reg%'
    GROUP BY e.email
    HAVING COUNT(*) FILTER (
      WHERE e.event_type = 'LEFT' AND (ps.ends_at IS NULL OR e.occurred_at < ps.ends_at)
    ) > 0
  )
  SELECT
    (regexp_match(d.email, '\+reg(\d+)@'))[1]::BIGINT AS "registrationId",
    reg.full_name::TEXT                                AS "fullName",
    reg.mobile_number::TEXT                            AS "phone",
    reg.email_address::TEXT                            AS "email",
    u.org_usr_name::TEXT                               AS "rmName",
    d.email::TEXT                                      AS "zoomTaggedEmail",
    d.join_count                                       AS "joinCount",
    zas.dropoff_count                                  AS "dropoffCount",
    d.leave_reasons::TEXT                              AS "leaveReasons"
  FROM dropoffs d
  JOIN hdb_program_registration reg
    ON reg.id = (regexp_match(d.email, '\+reg(\d+)@'))[1]::BIGINT
   AND reg.deleted_at IS NULL AND reg.seat_allocated = true
  LEFT JOIN users u
    ON u.id = reg.rm_contact
  LEFT JOIN zoom_analytics_attendee_summary zas
    ON zas.registration_id = reg.id AND zas.session_id = p_session_id
  ORDER BY d.leave_count DESC, reg.full_name;
END;
$$;
