BEGIN;

ALTER TABLE hr_payroll.exit_interviews
    ADD COLUMN IF NOT EXISTS last_working_date DATE,
    ADD COLUMN IF NOT EXISTS interview_mode VARCHAR(30),
    ADD COLUMN IF NOT EXISTS interview_status VARCHAR(30) NOT NULL DEFAULT 'scheduled',
    ADD COLUMN IF NOT EXISTS experience_rating SMALLINT,
    ADD COLUMN IF NOT EXISTS would_recommend BOOLEAN,
    ADD COLUMN IF NOT EXISTS retention_classification VARCHAR(30) NOT NULL DEFAULT 'tbd',
    ADD COLUMN IF NOT EXISTS completed_at TIMESTAMPTZ;

ALTER TABLE hr_payroll.exit_interviews DROP CONSTRAINT IF EXISTS chk_exit_interview_rating;
ALTER TABLE hr_payroll.exit_interviews ADD CONSTRAINT chk_exit_interview_rating CHECK (experience_rating IS NULL OR experience_rating BETWEEN 1 AND 5);

CREATE INDEX IF NOT EXISTS idx_exit_interviews_tenant_interview_status
    ON hr_payroll.exit_interviews (tenant_id, interview_status, scheduled_at)
    WHERE is_deleted = FALSE;

COMMIT;