BEGIN;

ALTER TABLE hr_payroll.candidate_ai_scoring_settings
    ADD COLUMN IF NOT EXISTS hold_minimum_score NUMERIC(8, 2) NOT NULL DEFAULT 58.00,
    ADD COLUMN IF NOT EXISTS hire_minimum_score NUMERIC(8, 2) NOT NULL DEFAULT 74.00,
    ADD COLUMN IF NOT EXISTS strong_hire_minimum_score NUMERIC(8, 2) NOT NULL DEFAULT 88.00;

ALTER TABLE hr_payroll.candidate_ai_scoring_settings
    DROP CONSTRAINT IF EXISTS chk_candidate_ai_recommendation_bands;

ALTER TABLE hr_payroll.candidate_ai_scoring_settings
    ADD CONSTRAINT chk_candidate_ai_recommendation_bands CHECK (
        hold_minimum_score >= 0
        AND hold_minimum_score <= 100
        AND hire_minimum_score > hold_minimum_score
        AND hire_minimum_score <= 100
        AND strong_hire_minimum_score > hire_minimum_score
        AND strong_hire_minimum_score <= 100
    );

COMMIT;