BEGIN;

ALTER TABLE IF EXISTS hr_payroll.recruitment_candidates
    ADD COLUMN IF NOT EXISTS cv_documents JSONB NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS highest_qualification VARCHAR(120) NULL,
    ADD COLUMN IF NOT EXISTS education_history JSONB NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS work_experience JSONB NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS years_experience NUMERIC(8, 2) NULL,
    ADD COLUMN IF NOT EXISTS certifications JSONB NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS professional_associations JSONB NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS skills JSONB NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS social_links JSONB NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS cv_profile_source VARCHAR(40) NOT NULL DEFAULT 'manual',
    ADD COLUMN IF NOT EXISTS cv_profile_updated_at TIMESTAMP NULL;

CREATE INDEX IF NOT EXISTS idx_recruitment_candidates_qualification
    ON hr_payroll.recruitment_candidates (tenant_id, highest_qualification)
    WHERE is_deleted = FALSE;

COMMIT;