ALTER TABLE app.candidate_education DROP CONSTRAINT IF EXISTS ai_data_requires_document;
ALTER TABLE app.candidate_license DROP CONSTRAINT IF EXISTS ai_data_requires_document;
ALTER TABLE app.candidate_language DROP CONSTRAINT IF EXISTS ai_data_requires_document;
ALTER TABLE app.candidate_experience DROP CONSTRAINT IF EXISTS ai_data_requires_document;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM app.candidate_education
        GROUP BY candidate_id
        HAVING COUNT(*) > 1
    ) THEN
        RAISE EXCEPTION 'Manual migration required: candidate_education still has multiple rows per candidate.';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM app.candidate_license
        GROUP BY candidate_id
        HAVING COUNT(*) > 1
    ) THEN
        RAISE EXCEPTION 'Manual migration required: candidate_license still has multiple rows per candidate.';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM app.candidate_language
        GROUP BY candidate_id
        HAVING COUNT(*) > 1
    ) THEN
        RAISE EXCEPTION 'Manual migration required: candidate_language still has multiple rows per candidate.';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM app.candidate_experience
        GROUP BY candidate_id
        HAVING COUNT(*) > 1
    ) THEN
        RAISE EXCEPTION 'Manual migration required: candidate_experience still has multiple rows per candidate.';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM app.candidate_document
        WHERE category = 'transcript'::app.candidate_document_type
    ) THEN
        RAISE EXCEPTION 'Manual migration required: transcript documents must be re-categorized before applying schema contract.';
    END IF;
END;
$$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'candidate_education_candidate_id_key'
    ) THEN
        ALTER TABLE app.candidate_education
            ADD CONSTRAINT candidate_education_candidate_id_key UNIQUE (candidate_id);
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'candidate_license_candidate_id_key'
    ) THEN
        ALTER TABLE app.candidate_license
            ADD CONSTRAINT candidate_license_candidate_id_key UNIQUE (candidate_id);
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'candidate_language_candidate_id_key'
    ) THEN
        ALTER TABLE app.candidate_language
            ADD CONSTRAINT candidate_language_candidate_id_key UNIQUE (candidate_id);
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'candidate_experience_candidate_id_key'
    ) THEN
        ALTER TABLE app.candidate_experience
            ADD CONSTRAINT candidate_experience_candidate_id_key UNIQUE (candidate_id);
    END IF;
END;
$$;

ALTER TABLE app.candidate_education
    DROP CONSTRAINT IF EXISTS candidate_education_source_document_id_fkey,
    DROP COLUMN IF EXISTS source_document_id;

ALTER TABLE app.candidate_license
    DROP CONSTRAINT IF EXISTS candidate_license_source_document_id_fkey,
    DROP COLUMN IF EXISTS source_document_id;

ALTER TABLE app.candidate_language
    DROP CONSTRAINT IF EXISTS candidate_language_source_document_id_fkey,
    DROP COLUMN IF EXISTS source_document_id;

ALTER TABLE app.candidate_experience
    DROP CONSTRAINT IF EXISTS candidate_experience_source_document_id_fkey,
    DROP COLUMN IF EXISTS source_document_id;

DROP TABLE IF EXISTS app.candidate_transcript CASCADE;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM pg_type
        WHERE typnamespace = 'app'::regnamespace
          AND typname = 'candidate_document_type_v2'
    ) THEN
        DROP TYPE app.candidate_document_type_v2;
    END IF;
END;
$$;

CREATE TYPE app.candidate_document_type_v2 AS ENUM (
    'education',
    'license',
    'language',
    'work_experience'
);

ALTER TABLE app.candidate_document
    ALTER COLUMN category TYPE app.candidate_document_type_v2
    USING category::text::app.candidate_document_type_v2;

DROP TYPE app.candidate_document_type;
ALTER TYPE app.candidate_document_type_v2 RENAME TO candidate_document_type;