27 lines
841 B
SQL
27 lines
841 B
SQL
-- Create document status enum
|
|
CREATE TYPE app.document_status AS ENUM (
|
|
'Missing',
|
|
'Invalid',
|
|
'Verified',
|
|
'Unverified'
|
|
);
|
|
|
|
-- Add document_status column to candidate_document table
|
|
ALTER TABLE app.candidate_document
|
|
ADD COLUMN document_status app.document_status DEFAULT 'Unverified';
|
|
|
|
-- Migrate existing data based on document_verified_at
|
|
UPDATE app.candidate_document
|
|
SET document_status = CASE
|
|
WHEN document_verified_at IS NOT NULL THEN 'Verified'::app.document_status
|
|
ELSE 'Unverified'::app.document_status
|
|
END;
|
|
|
|
-- Make document_status NOT NULL after migration
|
|
ALTER TABLE app.candidate_document
|
|
ALTER COLUMN document_status SET NOT NULL;
|
|
|
|
-- Drop the old columns as they're no longer needed
|
|
ALTER TABLE app.candidate_document
|
|
DROP COLUMN document_verified_at,
|
|
DROP COLUMN document_verified_by; |