-- hdb_program_registration
SELECT setval(pg_get_serial_sequence('public.hdb_program_registration', 'id'),
              last_value + 4, true)
FROM public.hdb_program_registration_id_seq;

-- hdb_program_question
SELECT setval(pg_get_serial_sequence('public.hdb_program_question', 'id'),
              last_value + 4, true)
FROM public.hdb_program_question_id_seq;

-- hdb_registration_question_answer
SELECT setval(pg_get_serial_sequence('public.hdb_registration_question_answer', 'id'),
              last_value + 4, true)
FROM public.hdb_registration_question_answer_id_seq;

-- hdb_registration_travel_info
SELECT setval(pg_get_serial_sequence('public.hdb_registration_travel_info', 'id'),
              last_value + 4, true)
FROM public.hdb_registration_travel_info_id_seq;

-- hdb_registration_travel_plan
SELECT setval(pg_get_serial_sequence('public.hdb_registration_travel_plan', 'id'),
              last_value + 4, true)
FROM public.hdb_registration_travel_plan_id_seq;

-- hdb_registration_invoice_detail
SELECT setval(pg_get_serial_sequence('public.hdb_registration_invoice_detail', 'id'),
              last_value + 4, true)
FROM public.hdb_registration_invoice_detail_id_seq;

-- hdb_registration_payment_details_history
SELECT setval(pg_get_serial_sequence('public.hdb_registration_payment_details_history', 'id'),
              last_value + 4, true)
FROM public.hdb_registration_payment_details_history_id_seq;

-- hdb_registration_payment_detail
SELECT setval(pg_get_serial_sequence('public.hdb_registration_payment_detail', 'id'),
              last_value + 4, true)
FROM public.hdb_registration_payment_detail_id_seq;

-- hdb_registration_approval
SELECT setval(pg_get_serial_sequence('public.hdb_registration_approval', 'id'),
              last_value + 4, true)
FROM public.hdb_registration_approval_id_seq;

-- hdb_registration_approval_track
SELECT setval(pg_get_serial_sequence('public.hdb_registration_approval_track', 'id'),
              last_value + 4, true)
FROM public.hdb_registration_approval_track_id_seq;

-- program_v1
SELECT setval(pg_get_serial_sequence('public.program_v1', 'id'),
              last_value + 4, true)
FROM public.program_v1_id_seq;

-- program_session
SELECT setval(pg_get_serial_sequence('public.program_session', 'id'),
              last_value + 4, true)
FROM public.program_session_id_seq;


-- 2025-09-06: Add sub_program_type support
-- Create enum type for sub_program_type
CREATE TYPE sub_program_type_enum AS ENUM ('PST_HDB', 'PST_MSD');

-- Add sub_program_type column to program_v1 table (nullable)
ALTER TABLE program_v1 
ADD COLUMN IF NOT EXISTS sub_program_type sub_program_type_enum NULL;

-- Add index for better query performance
CREATE INDEX IF NOT EXISTS idx_program_v1_sub_program_type 
ON program_v1 (sub_program_type) WHERE sub_program_type IS NOT NULL;

-- Update existing programs with appropriate sub_program_type values, leave others as NULL
UPDATE program_v1 
SET sub_program_type = CASE 
    WHEN LOWER(name) LIKE '%hdb%' THEN 'PST_HDB'::sub_program_type_enum
    WHEN LOWER(name) LIKE '%msd%' THEN 'PST_MSD'::sub_program_type_enum
    ELSE NULL
END
WHERE sub_program_type IS NULL;

-- Add sub_program_type column to user_participation_summary table (nullable)
ALTER TABLE user_participation_summary 
ADD COLUMN IF NOT EXISTS sub_program_type sub_program_type_enum NULL;

-- Add index for better query performance on user_participation_summary
CREATE INDEX IF NOT EXISTS idx_user_participation_summary_sub_program_type 
ON user_participation_summary (sub_program_type, user_id) WHERE sub_program_type IS NOT NULL;

-- Update existing participation records with sub_program_type from associated programs
UPDATE user_participation_summary ups
SET sub_program_type = p.sub_program_type
FROM program_v1 p
WHERE ups.program_id = p.id 
  AND ups.sub_program_type IS NULL
  AND p.sub_program_type IS NOT NULL;

-- Add comments for documentation
COMMENT ON COLUMN program_v1.sub_program_type IS 'Sub program type classification (PST_HDB, PST_MSD) - NULL for other programs';
COMMENT ON COLUMN user_participation_summary.sub_program_type IS 'Sub program type classification for participation tracking - NULL for other programs';
