30 lines
996 B
SQL
30 lines
996 B
SQL
-- 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)
|
|
);
|