-- ============================================================
-- Migration: vw_program_session_attendance_report — add "duration",
-- "lateBy", "dropoffCount", "lastDropoffAt"; fix rmAttendance/coordinatorAttendance
-- to distinguish "not marked" from "marked absent"
--
-- 1. "duration" — zoom_analytics_attendee_summary.duration_seconds (total
--    seconds present from the session's actual start onward, computed by
--    ZoomLiveEventAnalyticsProviderBase.aggregateLiveEvents from JOIN/LEFT
--    interval pairs; excludes pre-session waiting-room time, which is
--    tracked separately in pre_session_duration_seconds and NOT included
--    here) — added as a new attendance column in whole minutes
--    (ROUND(seconds / 60.0)), guarded by the same session-not-finished NULL
--    check as the other four attendance columns (see migration 3's header).
--
-- 2. rmAttendance/coordinatorAttendance previously used
--    `attendance_events @> '[{"source":"..."}]'` which only checks whether that
--    source has ANY entry — so an explicit "marked absent" event (which still
--    has a "source" key) read as Yes, and "never touched by this source" also
--    collapsed to No instead of being distinguishable from an explicit absent
--    mark. Both are wrong: the source's own recorded status (present/absent)
--    was being ignored entirely. Fixed to read that source's status field —
--    NULL when the source never marked this registrant (no entry at all,
--    including after an undo), true when its mark is present (or legacy,
--    status-less — read as PRESENT per AttendanceEvent.status docblock),
--    false when its mark is absent.
--
-- 3. "lateBy" — exact whole minutes between ps.starts_at and zas.joined_at
--    (ROUND(seconds / 60.0), same whole-minutes convention as "duration"). NULL if the
--    session hasn't finished, they never joined, or they joined at/before the scheduled
--    start (on time — 0 minutes late isn't shown as "late" at all). The late cutoff here
--    is the session's raw start (0s threshold), matching
--    ZOOM_ANALYTICS_DEFAULTS.LATE_JOIN_THRESHOLD_SECONDS — if that env override
--    (ZOOM_ANALYTICS_LATE_JOIN_THRESHOLD_SECONDS) is ever actually set in an environment,
--    this column will disagree with the live dashboard's Late Comers widget for that env
--    and would need updating too (same tradeoff this report already accepts for
--    session_is_done mirroring the app's "completed" formula — see migration 3's header).
--
-- 4. "dropoffCount"/"lastDropoffAt" surface zoom_analytics_attendee_summary's
--    dropoff_count/last_dropoff_at columns (already reachable via the zas join) — a
--    coarse per-registrant drop-off count/timestamp, no reasons. The per-event detail
--    with leave reasons lives in the separate session-dropoffs report/purpose
--    (fn_generate_session_dropoff_report), which reads zoom_analytics_live_event
--    directly — dropoff_count/last_dropoff_at don't carry reason text at all.
--
-- 5. "joinCount" — how many times this registrant became present, from
--    zoom_analytics_attendee_summary: rejoin_count (rejoins after a FULL drop-off,
--    already reachable via the zas join) + 1 for the initial join, or 0 if they never
--    joined at all. Deliberately NOT the raw zoom_analytics_live_event JOINED-event tally
--    fn_generate_session_dropoff_report's joinCount uses (that counts every device/tab
--    join event, including ones that don't represent a genuine re-presence) — this stays
--    consistent with the rest of this view sourcing only from the pre-reconciled zas table,
--    no live-event join added. Appended as this view's last column (Postgres only allows
--    CREATE OR REPLACE VIEW to add columns at the end, not in the middle) — its display
--    position in the "session-report" purpose (right after dropoffCount) is controlled by
--    field order in SESSION_ATTENDANCE_REPORT_PURPOSE_FIELDS, independent of this physical
--    column order.
--
-- 6. "durationSeconds"/"lateBySeconds" — raw-seconds companions to "duration"/"lateBy"
--    (both whole minutes, point 1/3 above): the same zas.duration_seconds value unrounded,
--    and the same joined_at-minus-starts_at gap in seconds instead of ROUND(.../60.0).
--    Same NULL guards as their minute counterparts.
-- ============================================================

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"

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;


-- ── Field registry: add "duration" ────────────────────────────────────────────

DROP FUNCTION IF EXISTS fn_get_session_attendance_report_field_definitions;

CREATE OR REPLACE FUNCTION fn_get_session_attendance_report_field_definitions()
RETURNS TABLE(
  key                TEXT,
  alias              TEXT,
  label              TEXT,
  "group"            TEXT,
  "order"            INT,
  format             TEXT,
  "dependsOn"        TEXT,
  "requiredFlags"    TEXT[],
  "conditionalFormat" JSONB
)
STABLE LANGUAGE sql AS $$
  SELECT
    t.key, t.alias, t.label, t.grp, t.ord, t.fmt, t.dep, t.flags, t.cond_fmt
  FROM (VALUES

    -- ── Session ───────────────────────────────────────────────────────────────
    ('sessionName',            'sessionName',            'Session Name',            'Session',  1, NULL,       NULL::TEXT, NULL::TEXT[], NULL::JSONB),
    ('sessionStartsAt',        'sessionStartsAt',        'Session Date',            'Session',  2, 'datetime', NULL,       NULL,         NULL),

    -- ── Attendee ──────────────────────────────────────────────────────────────
    ('registrationSeqNumber',  'registrationSeqNumber',  'Registration Seq No.',    'Attendee', 3, NULL,       NULL,       NULL,         NULL),
    ('fullName',               'fullName',               'Full Name',               'Attendee', 4, NULL,       NULL,       NULL,         NULL),
    ('email',                  'email',                  'Email',                   'Attendee', 5, NULL,       NULL,       NULL,         NULL),
    ('phone',                  'phone',                  'Mobile',                  'Attendee', 6, NULL,       NULL,       NULL,         NULL),
    ('activationStatus',       'activationStatus',       'Activation Status',       'Attendee', 7, 'title-case', NULL,     NULL,         NULL),

    -- ── Attendance ────────────────────────────────────────────────────────────
    -- attendanceStatus is the precedence-resolved final call (program_user_attendance.is_attended);
    -- systemAttendance/rmAttendance/coordinatorAttendance break it down by who/what marked it.
    ('attendanceStatus',       'attendanceStatus',       'Final Attendance',        'Attendance', 8,  NULL,     NULL, NULL, NULL),
    ('systemAttendance',       'systemAttendance',       'System Attendance',       'Attendance', 9,  'boolean',NULL, NULL, NULL),
    ('rmAttendance',           'rmAttendance',           'RM Attendance',           'Attendance', 10, 'boolean',NULL, NULL, NULL),
    ('coordinatorAttendance',  'coordinatorAttendance',  'Coordinator Attendance',  'Attendance', 11, 'boolean',NULL, NULL, NULL),
    ('joinedTime',             'joinedTime',             'Joined Time',             'Attendance', 12, 'datetime',NULL, NULL, NULL),
    ('noOfLogins',             'noOfLogins',             'No. of Logins',           'Attendance', 13, 'number', NULL, NULL, NULL),
    ('duration',               'duration',               'Duration (mins)',         'Attendance', 14, 'number', NULL, NULL, NULL),
    ('lateBy',                 'lateBy',                 'Late By (mins)',          'Attendance', 15, 'number', NULL, NULL, NULL),
    ('dropoffCount',           'dropoffCount',           'Drop-off Count',          'Attendance', 16, 'number', NULL, NULL, NULL),
    ('lastDropoffAt',          'lastDropoffAt',          'Last Drop-off At',        'Attendance', 17, 'datetime',NULL, NULL, NULL),
    ('joinCount',              'joinCount',              'Join Count',              'Attendance', 18, 'number', NULL, NULL, NULL),
    ('durationSeconds',        'durationSeconds',        'Duration (secs)',         'Attendance', 19, 'number', NULL, NULL, NULL),
    ('lateBySeconds',          'lateBySeconds',          'Late By (secs)',          'Attendance', 20, 'number', NULL, NULL, NULL)

  ) AS t(key, alias, label, grp, ord, fmt, dep, flags, cond_fmt)
  ORDER BY t.ord
$$;
