-- ================================================
-- Migration: PCF-001 — Parental Consent Form
-- Date: 2026-06-11
-- Safe to re-run (idempotent).
-- ================================================

-- ------------------------------------------------
-- PART 0: Drop the incorrectly created type from a previous failed run (if it exists)
-- ------------------------------------------------

DROP TYPE IF EXISTS hdb_program_registration_parental_form_status_enum;

-- ------------------------------------------------
-- PART 1: Extend parental_form_status_enum with new values (idempotent)
-- ------------------------------------------------

DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_enum e JOIN pg_type t ON t.oid = e.enumtypid
    WHERE t.typname = 'parental_form_status_enum' AND e.enumlabel = 'pending_upload'
  ) THEN
    ALTER TYPE parental_form_status_enum ADD VALUE 'pending_upload';
  END IF;
END $$;

DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_enum e JOIN pg_type t ON t.oid = e.enumtypid
    WHERE t.typname = 'parental_form_status_enum' AND e.enumlabel = 'submitted'
  ) THEN
    ALTER TYPE parental_form_status_enum ADD VALUE 'submitted';
  END IF;
END $$;

DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_enum e JOIN pg_type t ON t.oid = e.enumtypid
    WHERE t.typname = 'parental_form_status_enum' AND e.enumlabel = 'verified'
  ) THEN
    ALTER TYPE parental_form_status_enum ADD VALUE 'verified';
  END IF;
END $$;

-- ------------------------------------------------
-- PART 2: hdb_program_registration — all parental form columns
-- Existing columns included with IF NOT EXISTS so the full set is guaranteed.
-- ------------------------------------------------

ALTER TABLE hdb_program_registration
  ADD COLUMN IF NOT EXISTS parental_form_pdf_url      TEXT,
  ADD COLUMN IF NOT EXISTS parental_form_status       parental_form_status_enum,
  ADD COLUMN IF NOT EXISTS parental_form_uploaded_at  TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS parental_form_uploaded_by  BIGINT,
  ADD COLUMN IF NOT EXISTS parental_form_updated_at   TIMESTAMPTZ;

-- ------------------------------------------------
-- PART 3: program_type_v1 — new columns (source of truth; cloned down to program and session)
-- ------------------------------------------------

ALTER TABLE program_type_v1
  ADD COLUMN IF NOT EXISTS parental_consent_enabled            BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS parental_consent_form_as_attachment BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS parental_consent_upload_deadline    TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS parental_consent_form_content       TEXT;

-- ------------------------------------------------
-- PART 4: program_v1 — cloned from program_type on create; may be overridden per programme
-- ------------------------------------------------

ALTER TABLE program_v1
  ADD COLUMN IF NOT EXISTS parental_consent_enabled            BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS parental_consent_form_as_attachment BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS parental_consent_upload_deadline    TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS parental_consent_form_content       TEXT;

-- ------------------------------------------------
-- PART 5: program_session — may override per session (resolution: session → program → program_type)
-- ------------------------------------------------

ALTER TABLE program_session
  ADD COLUMN IF NOT EXISTS parental_consent_enabled            BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS parental_consent_form_as_attachment BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS parental_consent_upload_deadline    TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS parental_consent_form_content       TEXT;

-- ------------------------------------------------
-- Verification
-- Expected: 17 rows (5 on hdb_program_registration + 4 each on program_type_v1, program_v1, program_session)
-- ------------------------------------------------

SELECT
  table_name,
  column_name,
  data_type,
  is_nullable,
  column_default
FROM information_schema.columns
WHERE (table_name = 'hdb_program_registration'
       AND column_name IN (
         'parental_form_pdf_url',
         'parental_form_status',
         'parental_form_uploaded_at',
         'parental_form_uploaded_by',
         'parental_form_updated_at'
       ))
   OR (table_name IN ('program_type_v1', 'program_v1', 'program_session')
       AND column_name IN (
         'parental_consent_enabled',
         'parental_consent_form_as_attachment',
         'parental_consent_upload_deadline',
         'parental_consent_form_content'
       ))
ORDER BY table_name, column_name;

-- Verify new enum values
SELECT enumlabel
FROM pg_enum e
JOIN pg_type t ON t.oid = e.enumtypid
WHERE t.typname = 'parental_form_status_enum'
ORDER BY enumsortorder;
-- Expected values: completed, not_applicable, pending, pending_upload, submitted, verified
