-- ============================================================
-- Migration: RM scoping for vw_program_session_attendance_report /
-- fn_generate_session_attendance_report / fn_generate_session_dropoff_report
--
-- The registrant-based session-attendance report family (single-session-attendance,
-- all-sessions-attendance, session-report, late-comer-report, eligible-registrations-
-- attendance, session-dropoffs) had no RM scoping at all — an RM downloading any of
-- these got every registrant/session row for the whole program, not just their own
-- seekers. This is the SQL half of that fix (ReportsService now resolves the caller's
-- rmContactId — ROLE_RELATIONAL_MANAGER only, never client-supplied — and passes it as
-- filters.rmContactId); this migration makes both underlying functions actually filter
-- by it. Mirrors the scoping the live attendee-list screens already do (see
-- ZoomAnalyticsAttendeeSummaryRepository's `rm_contact` filter) and the registration-
-- report family's own `rm_contact_id`/p_rm_contact_ids pattern (migration
-- 1create-registration-report-view-and-function.sql in the 2026-06-15 folder).
--
-- The general-attendee-family purposes (general-attendees/general-session-report/
-- general-late-comer-report/general-session-dropoffs) are untouched here — they read
-- rows with registration_id IS NULL (shared/common-Zoom-link attendees), which have no
-- rm_contact to scope by at all.
--
-- 1. vw_program_session_attendance_report — add rm_contact_id (reg.rm_contact),
--    appended as the view's last column (Postgres only allows CREATE OR REPLACE VIEW to
--    add columns at the end, same constraint migration 5's own header documents).
--    fn_generate_session_attendance_report's RETURNS SETOF vw_program_session_attendance_report
--    tracks the view's row type automatically, so the function needs no RETURNS change —
--    only the new p_rm_contact_id filter in its body.
--
-- 2. fn_generate_session_dropoff_report — reg.rm_contact is already in scope (joined for
--    "rmName") but was never used as a filter; add p_rm_contact_id and a WHERE clause on
--    the final SELECT. DROP FUNCTION first (not a plain CREATE OR REPLACE), matching the
--    defensive convention migration 10 established for this exact function.
-- ============================================================


-- ── vw_program_session_attendance_report — add rm_contact_id ─────────────────────

CREATE OR REPLACE VIEW vw_program_session_attendance_report AS
SELECT
  reg.id                           AS reg_id,
  reg.program_id,
  ps.id                            AS session_id,
  ps.display_order                 AS session_display_order,

  reg.registration_seq_number      AS "registrationSeqNumber",
  reg.full_name                    AS "fullName",
  reg.email_address                AS "email",
  reg.mobile_number                AS "phone",
  reg.activation_status            AS "activationStatus",
  reg.registration_status::TEXT    AS "registrationStatus",

  ps.name                          AS "sessionName",
  ps.starts_at                     AS "sessionStartsAt",
  ps.ends_at                       AS "sessionEndsAt",

  COALESCE(pua.is_attended, false) AS attended,
  CASE
    WHEN ps.ends_at IS NULL OR ps.ends_at >= NOW() THEN NULL
    WHEN pua.is_attended THEN 'Attended'
    ELSE 'Not Attended'
  END AS "attendanceStatus",
  CASE WHEN ps.ends_at IS NULL OR ps.ends_at >= NOW() THEN NULL
       ELSE COALESCE(zas.is_system_attended, false) END AS "systemAttendance",
  CASE WHEN ps.ends_at IS NULL OR ps.ends_at >= NOW() THEN NULL
       ELSE (
         SELECT CASE WHEN evt->>'status' = 'absent' THEN false ELSE true END
         FROM jsonb_array_elements(COALESCE(pua.attendance_events, '[]'::jsonb)) evt
         WHERE evt->>'source' = 'MANUAL_RM'
         LIMIT 1
       )
  END AS "rmAttendance",
  CASE WHEN ps.ends_at IS NULL OR ps.ends_at >= NOW() THEN NULL
       ELSE (
         SELECT CASE WHEN evt->>'status' = 'absent' THEN false ELSE true END
         FROM jsonb_array_elements(COALESCE(pua.attendance_events, '[]'::jsonb)) evt
         WHERE evt->>'source' = 'MANUAL_COORDINATOR'
         LIMIT 1
       )
  END AS "coordinatorAttendance",
  zas.joined_at                    AS "joinedTime",
  zas.no_of_devices                AS "noOfLogins",
  pua.is_manually_checked_in       AS "isManuallyCheckedIn",
  CASE WHEN ps.ends_at IS NULL OR ps.ends_at >= NOW() THEN NULL
       ELSE ROUND(zas.duration_seconds / 60.0)::INT END AS "duration",
  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",
  zas.dropoff_count                AS "dropoffCount",
  zas.last_dropoff_at              AS "lastDropoffAt",
  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",
  CASE WHEN ps.ends_at IS NULL OR ps.ends_at >= NOW() THEN NULL
       ELSE zas.duration_seconds END 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)))::INT
  END AS "lateBySeconds",
  reg.rm_contact                   AS rm_contact_id

FROM hdb_program_registration reg

JOIN program_session ps
  ON ps.program_id = reg.program_id AND ps.deleted_at IS NULL

LEFT JOIN program_user_attendance pua
  ON pua.registration_id = reg.id AND pua.session_id = ps.id

LEFT JOIN zoom_analytics_attendee_summary zas
  ON zas.registration_id = reg.id AND zas.session_id = ps.id

WHERE reg.deleted_at IS NULL AND reg.seat_allocated = true;


-- ── fn_generate_session_attendance_report — filter by rm_contact_id ──────────────

CREATE OR REPLACE FUNCTION fn_generate_session_attendance_report(p_filters JSONB DEFAULT '{}')
RETURNS SETOF vw_program_session_attendance_report
LANGUAGE plpgsql AS $$
DECLARE
  p_program_id           INT     := (p_filters->>'programId')::INT;
  p_session_id           INT     := (p_filters->>'sessionId')::INT;
  p_attended             BOOLEAN := (p_filters->>'attended')::BOOLEAN;
  p_registration_status  TEXT[]  := NULLIF(ARRAY(SELECT v FROM jsonb_array_elements_text(p_filters->'registrationStatus') v), '{}');
  p_rm_contact_id        INT     := (p_filters->>'rmContactId')::INT;
BEGIN
  RETURN QUERY
  SELECT * FROM vw_program_session_attendance_report v
  WHERE
        (p_program_id  IS NULL OR v.program_id = p_program_id)
    AND (p_session_id  IS NULL OR v.session_id = p_session_id)
    AND (p_attended    IS NULL OR v.attended   = p_attended)
    AND (p_registration_status IS NULL OR v."registrationStatus" = ANY(p_registration_status))
    AND (p_rm_contact_id IS NULL OR v.rm_contact_id = p_rm_contact_id)
  ORDER BY v.session_display_order, v."registrationSeqNumber";
END;
$$;


-- ── fn_generate_session_dropoff_report — filter by rm_contact_id ─────────────────

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;
  p_rm_contact_id INT := (p_filters->>'rmContactId')::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
  WHERE (p_rm_contact_id IS NULL OR reg.rm_contact = p_rm_contact_id)
  ORDER BY d.leave_count DESC, reg.full_name;
END;
$$;
