-- =============================================================================
-- MIGRATION 005: Change student_profiles PK to composite (partner_id, course_id)
-- PURPOSE: Allows a partner to be enrolled in multiple courses simultaneously.
-- NOTE:    registrations_true.student_id FK must be dropped first (depends on
--          student_profiles_pkey), then re-created pointing to partners(partner_id)
--          since the audit log records partner identity, not enrollment records.
-- IMPORTANT: Run AFTER migration 001 (courses table must exist).
-- =============================================================================

-- Step 1: Drop dependent FK on registrations_true
ALTER TABLE public.registrations_true
    DROP CONSTRAINT IF EXISTS registrations_true_student_id_fkey;

-- Step 2: Add course_id column to student_profiles
ALTER TABLE public.student_profiles
    ADD COLUMN IF NOT EXISTS course_id INT REFERENCES public.courses(course_id);

-- Step 3: Assign all existing profiles to the default course
UPDATE public.student_profiles SET course_id = 1;

-- Step 4: Enforce NOT NULL
ALTER TABLE public.student_profiles ALTER COLUMN course_id SET NOT NULL;

-- Step 5: Drop old single-column PK
ALTER TABLE public.student_profiles DROP CONSTRAINT student_profiles_pkey;

-- Step 6: Add composite PK
ALTER TABLE public.student_profiles ADD PRIMARY KEY (partner_id, course_id);

-- Step 7: Re-add FK on registrations_true → partners(partner_id)
-- References partners instead of student_profiles: the audit log records
-- the partner's identity, and a partner can now exist in multiple courses.
ALTER TABLE public.registrations_true
    ADD CONSTRAINT registrations_true_student_id_fkey
    FOREIGN KEY (student_id) REFERENCES public.partners(partner_id);