-- ============================================================
-- Migration: fn_generate_general_session_dropoff_report
--
-- The general-attendee counterpart to fn_generate_session_dropoff_report
-- (migration 6) — one row per shared/common-link attendee who left this
-- session at least once, with overall join/leave counts and every raw
-- leave_reason they triggered. Reads zoom_analytics_live_event directly, same
-- reason migration 6 does: that's the only place raw per-event leave_reason
-- text lives, the summary table only carries a coarse dropoff_count.
--
-- Registrant identification: migration 6 scopes to `email ILIKE '%+reg%'`
-- (registered seekers) and excludes `+gen...`-tagged rows outright — this
-- function is that exclusion's complement, scoping to `email ILIKE '%+gen%'`
-- instead. Once scoped, identity is resolved exactly like
-- fn_generate_general_attendees_report (migration 7): join
-- zoom_generated_registrant_link by (session, normalized email) for a known
-- registrant's real display name/email/mobile, falling back to
-- zoom_analytics_attendee_summary's own zoom_display_name/email/mobile (Zoom's
-- own report, never a real email/phone) for a genuinely unidentified walk-in —
-- not by parsing the tagged number, for the same ROLE-userId-vs-PLACEHOLDER-
-- sequenceNumber ambiguity migration 7's header explains at length.
--
-- p_session_id is required by the caller (ReportsService.generateGeneralSessionDropoffsReport
-- enforces this — RPT_BR_003 if missing, same as session-dropoffs) and used directly
-- (not COALESCEd) in the zas/link joins below, same convention migration 6 itself uses.
--
-- LEFT events only count as a drop-off when they happened BEFORE the session's own
-- scheduled end (program_session.ends_at) — identical cutoff/rationale to migration 6's
-- own header (mirrors midSessionDropoffs() in zoom-live-event-analytics-provider.base.ts).
--
-- Every TEXT-declared output column is explicitly cast to ::TEXT — same exact-type-match
-- requirement migration 6 documents (CITEXT/VARCHAR(n) columns and COALESCE()s over them
-- return their own type, not text).
-- ============================================================

CREATE OR REPLACE FUNCTION fn_generate_general_session_dropoff_report(p_filters JSONB DEFAULT '{}')
RETURNS TABLE(
  "fullName"        TEXT,
  "email"           TEXT,
  "mobile"          TEXT,
  "sourceTag"       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 '%+gen%'
    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
    COALESCE(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",
    COALESCE(link.role_key, 'Unidentified')::TEXT             AS "sourceTag",
    d.email::TEXT                                              AS "zoomTaggedEmail",
    d.join_count                                               AS "joinCount",
    zas.dropoff_count                                          AS "dropoffCount",
    d.leave_reasons::TEXT                                      AS "leaveReasons"
  FROM dropoffs d
  LEFT JOIN zoom_analytics_attendee_summary zas
    ON LOWER(TRIM(zas.email)) = LOWER(TRIM(d.email))
   AND zas.session_id = p_session_id
   AND zas.registration_id IS NULL
  LEFT JOIN zoom_generated_registrant_link link
    ON link.program_session_id = p_session_id
   AND LOWER(TRIM(link.registrant_email)) = LOWER(TRIM(d.email))
   AND link.status = 'registered'
   AND link.deleted_at IS NULL
  ORDER BY d.leave_count DESC, "fullName";
END;
$$;
