buff2.0/backend-admin/migrations/20260107_add_admin_id_api_logs.sql

-- Migration: add admin_id column to api_logs and migrate historical admin-written entries
-- 1) Add column admin_id (nullable)
ALTER TABLE `api_logs` ADD COLUMN `admin_id` INT NULL COMMENT '管理员ID（管理端操作记录）';

-- 2) For historical rows where user_id refers to an admin (i.e., not present in users), move to admin_id
UPDATE `api_logs` al
LEFT JOIN `users` u ON al.user_id = u.id
SET al.admin_id = al.user_id
WHERE al.user_id IS NOT NULL AND u.id IS NULL;

-- 3) Clear user_id for those migrated rows
UPDATE `api_logs`
SET user_id = NULL
WHERE admin_id IS NOT NULL;

-- 4) Optional: create index on admin_id
ALTER TABLE `api_logs` ADD INDEX `idx_admin_id` (`admin_id`);