-- Migration: Online Session — dedicated table
-- Description: Replaces the meeting_details / webinar_details / stream_details
--              JSONB columns on program_v1 and program_session with a single
--              normalized table, hdb_online_session (1:1 with each owner via a
--              nullable+unique program_id / program_session_id; exactly one set).
--              `type` discriminates meeting/webinar/live_stream; external_id and
--              status are indexed (webhook lookup + status filters). Existing
--              JSONB is backfilled, then the columns are dropped.
-- Idempotent — safe to re-run.

-- =====================================================================
-- 1. Table + indexes
-- =====================================================================
CREATE TABLE IF NOT EXISTS hdb_online_session (
  id                     BIGSERIAL    PRIMARY KEY,
  program_id             INT          NULL REFERENCES program_v1 (id),
  program_session_id     INT          NULL REFERENCES program_session (id),
  type                   VARCHAR(50)  NOT NULL DEFAULT 'NA',
  provider               VARCHAR(50)  NULL,
  external_id            VARCHAR(255) NULL,
  join_url               TEXT         NULL,
  password               VARCHAR(255) NULL,
  registration_url       TEXT         NULL,
  panelist_url           TEXT         NULL,
  host_email             VARCHAR(255) NULL,
  start_url              TEXT         NULL,
  status                 VARCHAR(50)  NULL,
  require_registration   BOOLEAN      NULL,
  actual_meeting_ends_at TIMESTAMPTZ  NULL,
  stream_url             TEXT         NULL,
  backup_stream_url      TEXT         NULL,
  chat_url               TEXT         NULL,
  details                JSONB        NULL,
  created_at             TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at             TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at             TIMESTAMPTZ  NULL,
  created_by             INT          NULL,
  updated_by             INT          NULL
);

COMMENT ON TABLE hdb_online_session IS 'Canonical online-session record for a program (template defaults) or program session (live provisioned resource). Replaces the meeting/webinar/stream JSONB columns.';

-- Exactly-one-owner, 1:1 per owner (partial unique allows the other to be NULL).
CREATE UNIQUE INDEX IF NOT EXISTS uq_online_session_program_id
  ON hdb_online_session (program_id) WHERE program_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uq_online_session_program_session_id
  ON hdb_online_session (program_session_id) WHERE program_session_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_online_session_external_id ON hdb_online_session (external_id);
CREATE INDEX IF NOT EXISTS idx_online_session_status ON hdb_online_session (status);

-- =====================================================================
-- 2. Backfill from program_session JSONB (one row per session, meeting
--    preferred, then webinar, then stream — guards keep program_session_id unique).
-- =====================================================================
INSERT INTO hdb_online_session (
  program_session_id, type, provider, external_id, join_url, password,
  registration_url, host_email, start_url, status, require_registration, actual_meeting_ends_at
)
SELECT
  ps.id, 'meeting',
  ps.meeting_details ->> 'provider',
  ps.meeting_details ->> 'meetingId',
  ps.meeting_details ->> 'meetingLink',
  ps.meeting_details ->> 'meetingPassword',
  ps.meeting_details ->> 'registrationLink',
  ps.meeting_details ->> 'hostEmail',
  ps.meeting_details ->> 'startUrl',
  ps.meeting_details ->> 'status',
  (ps.meeting_details ->> 'requireRegistration')::boolean,
  (ps.meeting_details ->> 'actualMeetingEndsAt')::timestamptz
FROM program_session ps
WHERE ps.meeting_details IS NOT NULL
  AND COALESCE(
        NULLIF(ps.meeting_details ->> 'provider', ''),
        NULLIF(ps.meeting_details ->> 'meetingId', ''),
        NULLIF(ps.meeting_details ->> 'meetingLink', ''),
        NULLIF(ps.meeting_details ->> 'meetingPassword', ''),
        NULLIF(ps.meeting_details ->> 'registrationLink', ''),
        NULLIF(ps.meeting_details ->> 'hostEmail', ''),
        NULLIF(ps.meeting_details ->> 'startUrl', ''),
        NULLIF(ps.meeting_details ->> 'status', ''),
        NULLIF(ps.meeting_details ->> 'requireRegistration', ''),
        NULLIF(ps.meeting_details ->> 'actualMeetingEndsAt', '')
      ) IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM hdb_online_session o WHERE o.program_session_id = ps.id);

INSERT INTO hdb_online_session (
  program_session_id, type, provider, external_id, join_url, password,
  registration_url, panelist_url, host_email, start_url, status, actual_meeting_ends_at, details
)
SELECT
  ps.id, 'webinar',
  ps.webinar_details ->> 'provider',
  ps.webinar_details ->> 'webinarId',
  ps.webinar_details ->> 'webinarLink',
  ps.webinar_details ->> 'webinarPassword',
  ps.webinar_details ->> 'registrationLink',
  ps.webinar_details ->> 'panelistLink',
  ps.webinar_details ->> 'hostEmail',
  ps.webinar_details ->> 'startUrl',
  ps.webinar_details ->> 'status',
  (ps.webinar_details ->> 'actualMeetingEndsAt')::timestamptz,
  (ps.webinar_details - 'provider' - 'webinarId' - 'webinarLink' - 'webinarPassword'
    - 'registrationLink' - 'panelistLink' - 'hostEmail' - 'startUrl' - 'status'
    - 'actualMeetingEndsAt')
FROM program_session ps
WHERE ps.webinar_details IS NOT NULL
  AND COALESCE(
        NULLIF(ps.webinar_details ->> 'provider', ''),
        NULLIF(ps.webinar_details ->> 'webinarId', ''),
        NULLIF(ps.webinar_details ->> 'webinarLink', ''),
        NULLIF(ps.webinar_details ->> 'webinarPassword', ''),
        NULLIF(ps.webinar_details ->> 'registrationLink', ''),
        NULLIF(ps.webinar_details ->> 'panelistLink', ''),
        NULLIF(ps.webinar_details ->> 'hostEmail', ''),
        NULLIF(ps.webinar_details ->> 'startUrl', ''),
        NULLIF(ps.webinar_details ->> 'status', ''),
        NULLIF(ps.webinar_details ->> 'actualMeetingEndsAt', '')
      ) IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM hdb_online_session o WHERE o.program_session_id = ps.id);

INSERT INTO hdb_online_session (
  program_session_id, type, stream_url, backup_stream_url, chat_url
)
SELECT
  ps.id, 'live_stream',
  ps.stream_details ->> 'streamUrl',
  ps.stream_details ->> 'backupStreamUrl',
  ps.stream_details ->> 'chatUrl'
FROM program_session ps
WHERE ps.stream_details IS NOT NULL
  AND COALESCE(
        NULLIF(ps.stream_details ->> 'streamUrl', ''),
        NULLIF(ps.stream_details ->> 'backupStreamUrl', ''),
        NULLIF(ps.stream_details ->> 'chatUrl', '')
      ) IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM hdb_online_session o WHERE o.program_session_id = ps.id);

-- =====================================================================
-- 3. Backfill from program_v1 JSONB (template defaults).
-- =====================================================================
INSERT INTO hdb_online_session (
  program_id, type, provider, external_id, join_url, password,
  registration_url, host_email, start_url, status, require_registration, actual_meeting_ends_at
)
SELECT
  p.id, 'meeting',
  p.meeting_details ->> 'provider',
  p.meeting_details ->> 'meetingId',
  p.meeting_details ->> 'meetingLink',
  p.meeting_details ->> 'meetingPassword',
  p.meeting_details ->> 'registrationLink',
  p.meeting_details ->> 'hostEmail',
  p.meeting_details ->> 'startUrl',
  p.meeting_details ->> 'status',
  (p.meeting_details ->> 'requireRegistration')::boolean,
  (p.meeting_details ->> 'actualMeetingEndsAt')::timestamptz
FROM program_v1 p
WHERE p.meeting_details IS NOT NULL
  AND COALESCE(
        NULLIF(p.meeting_details ->> 'provider', ''),
        NULLIF(p.meeting_details ->> 'meetingId', ''),
        NULLIF(p.meeting_details ->> 'meetingLink', ''),
        NULLIF(p.meeting_details ->> 'meetingPassword', ''),
        NULLIF(p.meeting_details ->> 'registrationLink', ''),
        NULLIF(p.meeting_details ->> 'hostEmail', ''),
        NULLIF(p.meeting_details ->> 'startUrl', ''),
        NULLIF(p.meeting_details ->> 'status', ''),
        NULLIF(p.meeting_details ->> 'requireRegistration', ''),
        NULLIF(p.meeting_details ->> 'actualMeetingEndsAt', '')
      ) IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM hdb_online_session o WHERE o.program_id = p.id);

INSERT INTO hdb_online_session (
  program_id, type, provider, external_id, join_url, password,
  registration_url, panelist_url, host_email, start_url, status, actual_meeting_ends_at, details
)
SELECT
  p.id, 'webinar',
  p.webinar_details ->> 'provider',
  p.webinar_details ->> 'webinarId',
  p.webinar_details ->> 'webinarLink',
  p.webinar_details ->> 'webinarPassword',
  p.webinar_details ->> 'registrationLink',
  p.webinar_details ->> 'panelistLink',
  p.webinar_details ->> 'hostEmail',
  p.webinar_details ->> 'startUrl',
  p.webinar_details ->> 'status',
  (p.webinar_details ->> 'actualMeetingEndsAt')::timestamptz,
  (p.webinar_details - 'provider' - 'webinarId' - 'webinarLink' - 'webinarPassword'
    - 'registrationLink' - 'panelistLink' - 'hostEmail' - 'startUrl' - 'status'
    - 'actualMeetingEndsAt')
FROM program_v1 p
WHERE p.webinar_details IS NOT NULL
  AND COALESCE(
        NULLIF(p.webinar_details ->> 'provider', ''),
        NULLIF(p.webinar_details ->> 'webinarId', ''),
        NULLIF(p.webinar_details ->> 'webinarLink', ''),
        NULLIF(p.webinar_details ->> 'webinarPassword', ''),
        NULLIF(p.webinar_details ->> 'registrationLink', ''),
        NULLIF(p.webinar_details ->> 'panelistLink', ''),
        NULLIF(p.webinar_details ->> 'hostEmail', ''),
        NULLIF(p.webinar_details ->> 'startUrl', ''),
        NULLIF(p.webinar_details ->> 'status', ''),
        NULLIF(p.webinar_details ->> 'actualMeetingEndsAt', '')
      ) IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM hdb_online_session o WHERE o.program_id = p.id);

INSERT INTO hdb_online_session (
  program_id, type, stream_url, backup_stream_url, chat_url
)
SELECT
  p.id, 'live_stream',
  p.stream_details ->> 'streamUrl',
  p.stream_details ->> 'backupStreamUrl',
  p.stream_details ->> 'chatUrl'
FROM program_v1 p
WHERE p.stream_details IS NOT NULL
  AND COALESCE(
        NULLIF(p.stream_details ->> 'streamUrl', ''),
        NULLIF(p.stream_details ->> 'backupStreamUrl', ''),
        NULLIF(p.stream_details ->> 'chatUrl', '')
      ) IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM hdb_online_session o WHERE o.program_id = p.id);

-- =====================================================================
-- 4. Drop the now-migrated JSONB columns.
-- =====================================================================
ALTER TABLE program_session DROP COLUMN IF EXISTS meeting_details;
ALTER TABLE program_session DROP COLUMN IF EXISTS webinar_details;
ALTER TABLE program_session DROP COLUMN IF EXISTS stream_details;

ALTER TABLE program_v1 DROP COLUMN IF EXISTS meeting_details;
ALTER TABLE program_v1 DROP COLUMN IF EXISTS webinar_details;
ALTER TABLE program_v1 DROP COLUMN IF EXISTS stream_details;
