BEGIN;

UPDATE hr_payroll.performance_review_categories
SET score = CASE
    WHEN score > 10 AND score <= 100 THEN ROUND(score / 10, 2)
    WHEN score > 100 THEN 10
    WHEN score < 1 THEN NULL
    ELSE score
END
WHERE score IS NOT NULL AND (score < 1 OR score > 10);

UPDATE hr_payroll.performance_reviews
SET overall_score = CASE
        WHEN overall_score > 10 AND overall_score <= 100 THEN ROUND(overall_score / 10, 2)
        WHEN overall_score > 100 THEN 10
        WHEN overall_score < 1 THEN NULL
        ELSE overall_score
    END,
    calibrated_score = CASE
        WHEN calibrated_score > 10 AND calibrated_score <= 100 THEN ROUND(calibrated_score / 10, 2)
        WHEN calibrated_score > 100 THEN 10
        WHEN calibrated_score < 1 THEN NULL
        ELSE calibrated_score
    END
WHERE (overall_score IS NOT NULL AND (overall_score < 1 OR overall_score > 10))
   OR (calibrated_score IS NOT NULL AND (calibrated_score < 1 OR calibrated_score > 10));

ALTER TABLE hr_payroll.performance_review_categories
    DROP CONSTRAINT IF EXISTS chk_performance_review_category_score_scale,
    ADD CONSTRAINT chk_performance_review_category_score_scale CHECK (score IS NULL OR score BETWEEN 1 AND 10);

ALTER TABLE hr_payroll.performance_reviews
    DROP CONSTRAINT IF EXISTS chk_performance_review_overall_score_scale,
    ADD CONSTRAINT chk_performance_review_overall_score_scale CHECK (overall_score IS NULL OR overall_score BETWEEN 1 AND 10),
    DROP CONSTRAINT IF EXISTS chk_performance_review_calibrated_score_scale,
    ADD CONSTRAINT chk_performance_review_calibrated_score_scale CHECK (calibrated_score IS NULL OR calibrated_score BETWEEN 1 AND 10);

COMMIT;