select * from program_v1 where deleted_at is null order by id desc;

-- =====================================================
-- SCRIPT: Add missing columns to program_type_v1
--         and populate data for all program types
-- Missing: allow_save_as_draft, currency
-- =====================================================

-- =====================================================
-- STEP 1: Add missing columns to program_type_v1
-- =====================================================

-- Create enum type if it doesn't already exist
DO $$ BEGIN
  CREATE TYPE sub_program_type_enum AS ENUM ('PST_HDB', 'PST_MSD', 'PST_TAT', 'PST_ENTRAINMENT');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

ALTER TABLE program_type_v1
  ADD COLUMN IF NOT EXISTS sub_program_type        sub_program_type_enum NULL,
  ADD COLUMN IF NOT EXISTS allow_save_as_draft     BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS currency                VARCHAR(3) NOT NULL DEFAULT 'INR',
  ADD COLUMN IF NOT EXISTS max_proxy_registrations INT NOT NULL DEFAULT 0;

ALTER TABLE program_v1
  ADD COLUMN IF NOT EXISTS max_proxy_registrations INT NOT NULL DEFAULT 0;

ALTER TABLE program_session
  ADD COLUMN IF NOT EXISTS max_proxy_registrations INT NOT NULL DEFAULT 0;

-- =====================================================
-- STEP 2: Populate / update all three program types
-- Fields that already exist in the table are updated
-- here to guarantee correct values are set.
-- =====================================================

-- ── HDB (PT_HDBMSD) ─────────────────────────────────
UPDATE program_type_v1
SET
  sub_program_type              = 'PST_HDB',
  -- Scheduling / session defaults
  mode_of_operation             = 'offline',
  online_type                   = 'NA',
  max_session_duration_days     = 8,
  has_multiple_sessions         = TRUE,
  frequency                     = 'yearly',
  default_start_time            = '07:00:00',
  default_end_time              = '21:00:00',
  default_duration              = '8 days',

  -- Feature flags
  has_goodies                   = TRUE,
  registration_level            = 'program',
  max_capacity                  = 0,
  seeker_can_share_experience   = TRUE,
  allow_save_as_draft           = TRUE,
  allows_proxy_registration    = FALSE,
  max_proxy_registrations       = 0,

  -- Financial / currency
  currency                      = 'INR'
WHERE key = 'PT_HDBMSD'
  AND deleted_at IS NULL;

-- ── TAT (PT_TAT) ─────────────────────────────────────
UPDATE program_type_v1
SET
  sub_program_type              = 'PST_TAT',
  mode_of_operation             = 'online',
  online_type                   = 'webinar',
  max_session_duration_days     = 1,
  has_multiple_sessions         = TRUE,
  frequency                     = 'yearly',
  default_start_time            = '09:00:00',
  default_end_time              = '05:00:00',
  default_duration              = '1 days',

  has_goodies                   = FALSE,
  registration_level            = 'program',
  max_capacity                  = 0,
  seeker_can_share_experience   = FALSE,
  allow_save_as_draft           = FALSE,
  allows_proxy_registration    = FALSE,
  max_proxy_registrations       = 0,

  currency                      = 'INR'
WHERE key = 'PT_TAT'
  AND deleted_at IS NULL;

-- ── ENTRAINMENT (PT_ENTRAINMENT) ─────────────────────
UPDATE program_type_v1
SET
  sub_program_type              = 'PST_ENTRAINMENT',
  mode_of_operation             = 'offline',
  online_type                   = 'NA',
  max_session_duration_days     = 1,
  has_multiple_sessions         = FALSE,
  frequency                     = 'yearly',
  default_start_time            = '07:00:00',
  default_end_time              = '21:00:00',
  default_duration              = '1 day',

  has_goodies                   = FALSE,
  registration_level            = 'program',
  max_capacity                  = 0,
  seeker_can_share_experience   = FALSE,
  allow_save_as_draft           = FALSE,
  allows_proxy_registration    = TRUE,
  max_proxy_registrations       = 5,

  currency                      = 'INR'
WHERE key = 'PT_ENTRAINMENT'
  AND deleted_at IS NULL;

-- =====================================================
-- STEP 3: Verify
-- =====================================================

SELECT
  key,
  sub_program_type,
  mode_of_operation,
  online_type,
  max_session_duration_days,
  has_multiple_sessions,
  frequency,
  default_start_time,
  default_end_time,
  default_duration,
  has_goodies,
  registration_level,
  max_capacity,
  seeker_can_share_experience,
  allow_save_as_draft,
  currency,
  max_proxy_registrations
FROM program_type_v1
WHERE key IN ('PT_HDBMSD', 'PT_TAT', 'PT_ENTRAINMENT')
  AND deleted_at IS NULL
ORDER BY key;
