BEGIN;

ALTER TABLE hr_payroll.performance_scorecard_templates
    ADD COLUMN IF NOT EXISTS employee_id bigint NULL,
    ADD COLUMN IF NOT EXISTS team_label varchar(150) NULL;

ALTER TABLE hr_payroll.performance_scorecard_templates
    DROP CONSTRAINT IF EXISTS chk_performance_scorecard_templates_scope;

ALTER TABLE hr_payroll.performance_scorecard_templates
    ADD CONSTRAINT chk_performance_scorecard_templates_scope CHECK (
        applies_to IN ('employee', 'department', 'position', 'team')
        AND (
            (applies_to = 'employee' AND employee_id IS NOT NULL AND department_id IS NULL AND designation_id IS NULL AND NULLIF(TRIM(team_label), '') IS NULL)
            OR (applies_to = 'department' AND department_id IS NOT NULL AND employee_id IS NULL AND designation_id IS NULL AND NULLIF(TRIM(team_label), '') IS NULL)
            OR (applies_to = 'position' AND designation_id IS NOT NULL AND employee_id IS NULL AND department_id IS NULL AND NULLIF(TRIM(team_label), '') IS NULL)
            OR (applies_to = 'team' AND NULLIF(TRIM(team_label), '') IS NOT NULL AND employee_id IS NULL AND department_id IS NULL AND designation_id IS NULL)
        )
    ) NOT VALID;

CREATE INDEX IF NOT EXISTS idx_performance_scorecard_templates_employee
    ON hr_payroll.performance_scorecard_templates (tenant_id, employee_id)
    WHERE is_deleted = FALSE AND employee_id IS NOT NULL;

COMMIT;