-- ============================================================
-- Migration: fn_generate_session_dropoff_report / fn_generate_general_session_dropoff_report
--
-- Fixes "Drop-off Count" showing up BLANK for a real subset of rows in both
-- drop-off reports (confirmed in a live export: ~16% of rows had a populated
-- Join Count and Leave Reasons but an empty Drop-off Count).
--
-- Root cause: migrations 6/8 sourced "dropoffCount" from a LEFT JOIN to
-- zoom_analytics_attendee_summary — a table only upserted, per session, for
-- registrants returned by reconcile()'s fetchRoster() at whatever moment
-- reconcile() last ran (ZoomLiveEventAnalyticsProviderBase.reconcile(),
-- zoom-live-event-analytics-provider.base.ts). That roster is gated by the
-- registrant's session-level ProgramRegistrationOnlineSession row being
-- REGISTERED + ACTIVE + not deleted RIGHT NOW — a status that can change after
-- the fact (unregister cascades, session/meeting removal) even for a session
-- already attended. reconcile() is also safely re-runnable at any later time
-- (ended-webhook + a manual reconcile endpoint), so a later rerun after such a
-- change silently drops the registrant from attendeePayloads and never writes
-- their summary row — leaving the report's LEFT JOIN with a NULL to display,
-- for a registrant who plainly has leave events and belongs in the report.
--
-- Fix: compute "dropoffCount" directly from zoom_analytics_live_event inside
-- these two functions, replaying each registrant's own JOINED/LEFT events in
-- order and tracking concurrently-open connections exactly like
-- aggregateLiveEvents() does — a "drop-off" is a transition from >=1 open
-- connection to 0 (not a raw per-device LEFT tally, which overcounts
-- multi-device flicker), excluding transitions whose leave reason normalizes
-- to "host ended the meeting" (that's the session ending for everyone at
-- once, not a per-seeker drop) and transitions at/after the session's own
-- ends_at (ZOOM_ANALYTICS_DEFAULTS.DROPOFF_GRACE_SECONDS is 0, so the cutoff
-- is exactly ends_at — see midSessionDropoffs()). This removes the dependency
-- on zoom_analytics_attendee_summary entirely, so every registrant already
-- included by the report's own "left at least once" filter gets a real,
-- non-blank count — no separately-gated roster to fall out of.
--
-- The raw per-device LEFT tally (leave_count) is still computed and still
-- drives the HAVING filter/ORDER BY exactly as before — only the surfaced
-- "dropoffCount" column's source changes.
-- ============================================================

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;
  v_row RECORD;
  v_event RECORD;
  v_open_devices TEXT[];
  v_anonymous_open INT;
  v_was_present BOOLEAN;
  v_now_present BOOLEAN;
  v_dropoff_count INT;
  v_detail TEXT;
  v_normalized TEXT;
BEGIN
  FOR v_row IN
    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 registration_id,
      reg.full_name::TEXT                                AS full_name,
      reg.mobile_number::TEXT                            AS phone,
      reg.email_address::TEXT                            AS email,
      u.org_usr_name::TEXT                               AS rm_name,
      d.email::TEXT                                      AS zoom_tagged_email,
      d.join_count                                       AS join_count,
      d.leave_reasons::TEXT                              AS leave_reasons,
      d.leave_count                                      AS leave_count
    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
  LOOP
    v_open_devices := ARRAY[]::TEXT[];
    v_anonymous_open := 0;
    v_dropoff_count := 0;

    FOR v_event IN
      SELECT e.event_type, e.occurred_at, e.zoom_participant_id, e.leave_reason, ps.ends_at AS session_ends_at
      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 = v_row.zoom_tagged_email
        AND e.event_type IN ('JOINED', 'LEFT')
      ORDER BY e.occurred_at
    LOOP
      v_was_present := (COALESCE(array_length(v_open_devices, 1), 0) > 0) OR v_anonymous_open > 0;

      IF v_event.event_type = 'JOINED' THEN
        IF v_event.zoom_participant_id IS NOT NULL AND v_event.zoom_participant_id <> '' THEN
          IF NOT (v_event.zoom_participant_id = ANY(v_open_devices)) THEN
            v_open_devices := array_append(v_open_devices, v_event.zoom_participant_id);
          END IF;
        ELSE
          v_anonymous_open := v_anonymous_open + 1;
        END IF;
      ELSE
        IF v_event.zoom_participant_id IS NOT NULL AND v_event.zoom_participant_id <> ''
           AND v_event.zoom_participant_id = ANY(v_open_devices) THEN
          v_open_devices := array_remove(v_open_devices, v_event.zoom_participant_id);
        ELSIF v_anonymous_open > 0 THEN
          v_anonymous_open := v_anonymous_open - 1;
        ELSIF COALESCE(array_length(v_open_devices, 1), 0) > 0 THEN
          v_open_devices := v_open_devices[2:array_length(v_open_devices, 1)];
        END IF;
      END IF;

      v_now_present := (COALESCE(array_length(v_open_devices, 1), 0) > 0) OR v_anonymous_open > 0;

      IF v_was_present AND NOT v_now_present THEN
        v_detail := COALESCE(substring(v_event.leave_reason from '(?i)reason\s*:\s*(.+)$'), v_event.leave_reason);
        v_normalized := lower(regexp_replace(trim(both from v_detail), '\.+$', ''));

        IF (v_event.session_ends_at IS NULL OR v_event.occurred_at < v_event.session_ends_at)
           AND (v_event.leave_reason IS NULL
                OR NOT (v_normalized ~ 'host (closed|ended)' OR v_normalized ~ '(closed|ended) the meeting'))
        THEN
          v_dropoff_count := v_dropoff_count + 1;
        END IF;
      END IF;
    END LOOP;

    RETURN QUERY SELECT
      v_row.registration_id, v_row.full_name, v_row.phone, v_row.email, v_row.rm_name,
      v_row.zoom_tagged_email, v_row.join_count, v_dropoff_count, v_row.leave_reasons;
  END LOOP;
END;
$$;

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;
  v_row RECORD;
  v_event RECORD;
  v_open_devices TEXT[];
  v_anonymous_open INT;
  v_was_present BOOLEAN;
  v_now_present BOOLEAN;
  v_dropoff_count INT;
  v_detail TEXT;
  v_normalized TEXT;
BEGIN
  FOR v_row IN
    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 full_name,
      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 source_tag,
      d.email::TEXT                                              AS zoom_tagged_email,
      d.join_count                                               AS join_count,
      d.leave_reasons::TEXT                                      AS leave_reasons,
      d.leave_count                                              AS leave_count
    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, full_name
  LOOP
    v_open_devices := ARRAY[]::TEXT[];
    v_anonymous_open := 0;
    v_dropoff_count := 0;

    FOR v_event IN
      SELECT e.event_type, e.occurred_at, e.zoom_participant_id, e.leave_reason, ps.ends_at AS session_ends_at
      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 = v_row.zoom_tagged_email
        AND e.event_type IN ('JOINED', 'LEFT')
      ORDER BY e.occurred_at
    LOOP
      v_was_present := (COALESCE(array_length(v_open_devices, 1), 0) > 0) OR v_anonymous_open > 0;

      IF v_event.event_type = 'JOINED' THEN
        IF v_event.zoom_participant_id IS NOT NULL AND v_event.zoom_participant_id <> '' THEN
          IF NOT (v_event.zoom_participant_id = ANY(v_open_devices)) THEN
            v_open_devices := array_append(v_open_devices, v_event.zoom_participant_id);
          END IF;
        ELSE
          v_anonymous_open := v_anonymous_open + 1;
        END IF;
      ELSE
        IF v_event.zoom_participant_id IS NOT NULL AND v_event.zoom_participant_id <> ''
           AND v_event.zoom_participant_id = ANY(v_open_devices) THEN
          v_open_devices := array_remove(v_open_devices, v_event.zoom_participant_id);
        ELSIF v_anonymous_open > 0 THEN
          v_anonymous_open := v_anonymous_open - 1;
        ELSIF COALESCE(array_length(v_open_devices, 1), 0) > 0 THEN
          v_open_devices := v_open_devices[2:array_length(v_open_devices, 1)];
        END IF;
      END IF;

      v_now_present := (COALESCE(array_length(v_open_devices, 1), 0) > 0) OR v_anonymous_open > 0;

      IF v_was_present AND NOT v_now_present THEN
        v_detail := COALESCE(substring(v_event.leave_reason from '(?i)reason\s*:\s*(.+)$'), v_event.leave_reason);
        v_normalized := lower(regexp_replace(trim(both from v_detail), '\.+$', ''));

        IF (v_event.session_ends_at IS NULL OR v_event.occurred_at < v_event.session_ends_at)
           AND (v_event.leave_reason IS NULL
                OR NOT (v_normalized ~ 'host (closed|ended)' OR v_normalized ~ '(closed|ended) the meeting'))
        THEN
          v_dropoff_count := v_dropoff_count + 1;
        END IF;
      END IF;
    END LOOP;

    RETURN QUERY SELECT
      v_row.full_name, v_row.email, v_row.mobile, v_row.source_tag,
      v_row.zoom_tagged_email, v_row.join_count, v_dropoff_count, v_row.leave_reasons;
  END LOOP;
END;
$$;
