BEGIN;

ALTER TABLE IF EXISTS hr_payroll.performance_kpis
    ALTER COLUMN employee_id DROP NOT NULL,
    ADD COLUMN IF NOT EXISTS assignment_scope varchar(40) NOT NULL DEFAULT 'employee',
    ADD COLUMN IF NOT EXISTS department_id bigint,
    ADD COLUMN IF NOT EXISTS designation_id bigint,
    ADD COLUMN IF NOT EXISTS team_label varchar(150),
    ADD COLUMN IF NOT EXISTS calibration_status varchar(40) NOT NULL DEFAULT 'not_calibrated',
    ADD COLUMN IF NOT EXISTS calibrated_score numeric(6,2),
    ADD COLUMN IF NOT EXISTS calibrated_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS calibrated_by uuid,
    ADD COLUMN IF NOT EXISTS audit_notes text;

ALTER TABLE IF EXISTS hr_payroll.performance_reviews
    ADD COLUMN IF NOT EXISTS calibration_status varchar(40) NOT NULL DEFAULT 'not_calibrated',
    ADD COLUMN IF NOT EXISTS calibrated_score numeric(6,2),
    ADD COLUMN IF NOT EXISTS calibrated_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS calibrated_by uuid,
    ADD COLUMN IF NOT EXISTS audit_notes text;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'chk_performance_kpis_assignment_scope'
          AND conrelid = 'hr_payroll.performance_kpis'::regclass
    ) THEN
        ALTER TABLE hr_payroll.performance_kpis
            ADD CONSTRAINT chk_performance_kpis_assignment_scope
            CHECK (
                assignment_scope IN ('employee', 'department', 'position', 'team')
                AND (
                    (assignment_scope = 'employee' AND employee_id IS NOT NULL)
                    OR (assignment_scope = 'department' AND department_id IS NOT NULL)
                    OR (assignment_scope = 'position' AND designation_id IS NOT NULL)
                    OR (assignment_scope = 'team' AND NULLIF(TRIM(team_label), '') IS NOT NULL)
                )
            );
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_performance_kpis_assignment_scope
    ON hr_payroll.performance_kpis (tenant_id, assignment_scope, department_id, designation_id, employee_id);

COMMIT;