-- Remove total hours fields from candidate_transcript
ALTER TABLE app.candidate_transcript
DROP COLUMN IF EXISTS total_theory_hours,
DROP COLUMN IF EXISTS total_practical_hours,
DROP COLUMN IF EXISTS ai_total_theory_hours,
DROP COLUMN IF EXISTS ai_total_practical_hours;

-- Add boolean fields to candidate_transcript
ALTER TABLE app.candidate_transcript
ADD COLUMN has_theory BOOLEAN,
ADD COLUMN has_practice BOOLEAN,
ADD COLUMN duration_hours INTEGER,
ADD COLUMN ai_has_theory BOOLEAN,
ADD COLUMN ai_has_practice BOOLEAN,
ADD COLUMN ai_duration_hours INTEGER;

-- Update the constraint to reflect new fields
ALTER TABLE app.candidate_transcript
DROP CONSTRAINT IF EXISTS ai_data_requires_document;

ALTER TABLE app.candidate_transcript
ADD CONSTRAINT ai_data_requires_document CHECK (
    (source_document_id IS NOT NULL) OR
    (ai_has_theory IS NULL AND
     ai_has_practice IS NULL AND
     ai_duration_hours IS NULL AND
     ai_extracted_by IS NULL AND
     ai_extraction_date IS NULL)
);