ALTER TABLE app.candidate_experience
    DROP CONSTRAINT IF EXISTS ai_data_requires_document,
    DROP CONSTRAINT IF EXISTS candidate_experience_source_document_id_fkey,
    DROP CONSTRAINT IF EXISTS candidate_experience_candidate_id_key;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'app'
          AND table_name = 'candidate_experience'
          AND column_name = 'employer'
    ) AND EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'app'
          AND table_name = 'candidate_experience'
          AND column_name = 'employer_name'
    ) THEN
        UPDATE app.candidate_experience
        SET employer_name = employer
        WHERE employer_name IS NULL
          AND employer IS NOT NULL;

        ALTER TABLE app.candidate_experience DROP COLUMN employer;
    ELSIF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'app'
          AND table_name = 'candidate_experience'
          AND column_name = 'employer'
    ) AND NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'app'
          AND table_name = 'candidate_experience'
          AND column_name = 'employer_name'
    ) THEN
        ALTER TABLE app.candidate_experience RENAME COLUMN employer TO employer_name;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'app'
          AND table_name = 'candidate_experience'
          AND column_name = 'position'
    ) AND EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'app'
          AND table_name = 'candidate_experience'
          AND column_name = 'role_title'
    ) THEN
        UPDATE app.candidate_experience
        SET role_title = "position"
        WHERE role_title IS NULL
          AND "position" IS NOT NULL;

        ALTER TABLE app.candidate_experience DROP COLUMN "position";
    ELSIF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'app'
          AND table_name = 'candidate_experience'
          AND column_name = 'position'
    ) AND NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'app'
          AND table_name = 'candidate_experience'
          AND column_name = 'role_title'
    ) THEN
        ALTER TABLE app.candidate_experience RENAME COLUMN "position" TO role_title;
    END IF;
END;
$$;

ALTER TABLE app.candidate_experience
    ADD COLUMN IF NOT EXISTS country VARCHAR,
    ADD COLUMN IF NOT EXISTS city VARCHAR,
    ADD COLUMN IF NOT EXISTS facility_type VARCHAR;

ALTER TABLE app.candidate_experience
    DROP COLUMN IF EXISTS source_document_id,
    DROP COLUMN IF EXISTS duration_months,
    DROP COLUMN IF EXISTS is_full_time,
    DROP COLUMN IF EXISTS duties,
    DROP COLUMN IF EXISTS ai_employer,
    DROP COLUMN IF EXISTS ai_position,
    DROP COLUMN IF EXISTS ai_department,
    DROP COLUMN IF EXISTS ai_start_date,
    DROP COLUMN IF EXISTS ai_end_date,
    DROP COLUMN IF EXISTS ai_duration_months,
    DROP COLUMN IF EXISTS ai_is_full_time,
    DROP COLUMN IF EXISTS ai_duties,
    DROP COLUMN IF EXISTS ai_extracted_by,
    DROP COLUMN IF EXISTS ai_extraction_date;