-- ============================================================
-- Migration: fn_generate_session_dropoff_report / fn_generate_general_session_dropoff_report
--
-- Supersedes migration 9's device-aware "concurrent connections hit zero"
-- definition of "dropoffCount" — that undercounted for registrants whose
-- Zoom-tagged email is shared across many simultaneous devices (e.g. one
-- school/institutional inbox used by a whole classroom): almost every
-- individual device's LEFT event was masked by a sibling device staying
-- connected, collapsing hundreds of real leave events down to a tiny handful
-- of "every connection closed at once" moments.
--
-- "dropoffCount" is now simply every raw LEFT event before the session's own
-- scheduled end (program_session.ends_at) MINUS the ones whose leave_reason
-- indicates the host closed/ended the meeting — those are the session ending
-- for everyone at once, not a per-seeker drop, same exclusion migration 9 and
-- the app's normalizeDropOffReason()/HOST_ENDED case use, just applied per
-- raw event instead of per device-transition. A NULL leave_reason still
-- counts (only a confirmed host-ended reason is excluded), matching
-- midSessionDropoffs()'s own "reason === null || normalized !== HOST_ENDED"
-- rule. The host-ended phrase only needs a plain case-insensitive substring
-- match against the raw leave_reason text (no "extract the detail after
-- 'Reason :'" step needed) — the phrase itself is always literally present
-- somewhere in the raw string when it applies, and never appears incidentally
-- in any other observed reason text (network/disconnect/client-close/left
-- variants never contain "closed"/"ended" immediately before "the meeting").
--
-- No procedural per-registrant event replay needed any more — this is back
-- to a single declarative aggregate, same shape as migrations 6/8 originally
-- shipped, just with one more FILTER clause on the existing leave_count/
-- leave_reasons aggregation.
--
-- "dropoffCount" and "leaveReasons" share the EXACT SAME FILTER predicate
-- (event_type/cutoff/host-ended), so the number always matches the reasons
-- text one-for-one — leaveReasons is no longer every raw leave_reason, it's
-- every leave_reason that counted toward dropoffCount (host-ended ones are
-- dropped from both). leave_count (the plain unfiltered LEFT tally) keeps
-- its own, wider predicate — it's internal-only, driving the HAVING filter
-- and ORDER BY, never surfaced to the caller, so it doesn't need to match.
--
-- 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. Both
-- functions' signatures ARE identical across migrations 6/8/9/10 in this repo, so this only
-- bites if the live database's function predates or otherwise diverges from this migration
-- history (e.g. a 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,
      COUNT(*) FILTER (
        WHERE e.event_type = 'LEFT'
          AND (ps.ends_at IS NULL OR e.occurred_at < ps.ends_at)
          AND (e.leave_reason IS NULL OR NOT (lower(e.leave_reason) ~ 'host (closed|ended)|(closed|ended) the meeting'))
      ) AS dropoff_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)
          AND (e.leave_reason IS NULL OR NOT (lower(e.leave_reason) ~ 'host (closed|ended)|(closed|ended) the meeting'))
      ) 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",
    d.dropoff_count::INT                               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
  ORDER BY d.leave_count DESC, reg.full_name;
END;
$$;

DROP FUNCTION IF EXISTS fn_generate_general_session_dropoff_report(JSONB);

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,
      COUNT(*) FILTER (
        WHERE e.event_type = 'LEFT'
          AND (ps.ends_at IS NULL OR e.occurred_at < ps.ends_at)
          AND (e.leave_reason IS NULL OR NOT (lower(e.leave_reason) ~ 'host (closed|ended)|(closed|ended) the meeting'))
      ) AS dropoff_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)
          AND (e.leave_reason IS NULL OR NOT (lower(e.leave_reason) ~ 'host (closed|ended)|(closed|ended) the meeting'))
      ) 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",
    d.dropoff_count::INT                                       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;
$$;
