-- Migration: Add registration_mode to hdb_program_registration

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'registration_mode_enum') THEN
    CREATE TYPE registration_mode_enum AS ENUM ('self', 'other');
  END IF;
END $$;

ALTER TABLE hdb_program_registration
  ADD COLUMN IF NOT EXISTS registration_mode registration_mode_enum;

-- Backfill: existing self-registrations (created_by matches user_id)
UPDATE hdb_program_registration
SET registration_mode = 'self'
WHERE registration_mode IS NULL
  AND user_id IS NOT NULL
  AND created_by IS NOT NULL
  AND user_id = created_by;

-- Backfill: admin-assigned (owner_user_id differs from created_by)
UPDATE hdb_program_registration
SET registration_mode = 'other'
WHERE registration_mode IS NULL
  AND owner_user_id IS NOT NULL
  AND created_by IS NOT NULL
  AND owner_user_id != created_by;

-- Backfill: remaining non-self registrations as other
UPDATE hdb_program_registration
SET registration_mode = 'other'
WHERE registration_mode IS NULL;
