-- ============================================================
-- Migration: vw_program_session_attendance_report + fn_generate_session_attendance_report
--
-- Session-wise attendance report: one row per (registration × program_session)
-- pair, for seat-allocated registrations, scoped to a program (and optionally
-- a single session). This is a genuinely different row granularity than
-- vw_hdb_registration_report (one row per registration), so it gets its own
-- sibling view + function pair rather than being force-fit into the existing
-- one — same architecture, separate objects.
--
-- Attendance source of truth: program_user_attendance.is_attended for the resolved
-- "attendanceStatus" call; systemAttendance/rmAttendance/coordinatorAttendance break
-- that down by who/what marked it — systemAttendance from
-- zoom_analytics_attendee_summary.is_system_attended (Zoom-webhook-derived, see that
-- entity's docblock), rm/coordinatorAttendance from program_user_attendance.attendance_events
-- (AttendanceSourceEnum.MANUAL_RM / MANUAL_COORDINATOR — the canonical manual-mark log).
--
-- session_is_done mirrors the exact "completed" formula the rest of the app already uses
-- (see OnlineSessionController's `completed: !!session.endsAt && endsAt < now()`). All 4
-- attendance columns are NULL while a session hasn't finished yet — there's nothing to
-- report on a session that hasn't happened, so "Not Attended" would be misleading there.
-- This report is meant to be run across every session of a program in one call (no
-- `sessionId` filter), which is exactly why the null-vs-resolved distinction matters: rows
-- for not-yet-run sessions sit alongside rows for completed ones in the same result set.
--
-- Creation order: view → filter function. Drop order: filter function → view.
-- ============================================================


-- ── DOWN ──────────────────────────────────────────────────────────────────────
DROP FUNCTION IF EXISTS fn_generate_session_attendance_report;
DROP VIEW      IF EXISTS vw_program_session_attendance_report;


-- ── VIEW (UP) ─────────────────────────────────────────────────────────────────

CREATE 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 COALESCE(pua.attendance_events @> '[{"source":"MANUAL_RM"}]'::jsonb, false) END AS "rmAttendance",
  CASE WHEN ps.ends_at IS NULL OR ps.ends_at >= NOW() THEN NULL
       ELSE COALESCE(pua.attendance_events @> '[{"source":"MANUAL_COORDINATOR"}]'::jsonb, false) END AS "coordinatorAttendance",
  zas.joined_at                    AS "joinedTime",
  zas.no_of_devices                AS "noOfLogins",
  pua.is_manually_checked_in       AS "isManuallyCheckedIn"

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;


-- ── FILTER FUNCTION (UP) ──────────────────────────────────────────────────────

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), '{}');
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))
  ORDER BY v.session_display_order, v."registrationSeqNumber";
END;
$$;
