License/backend/migrations/009_domain_super_admin_id.sql

-- 为域名表添加 super_admin_id 关联字段
ALTER TABLE domain_monitor.monitored_domains
ADD COLUMN IF NOT EXISTS super_admin_id INTEGER;

-- 根据 username 反查，将已有的 super_admin_id 回填到域名表
-- 注意：这只在 username 与域名相同时生效
UPDATE domain_monitor.monitored_domains md
SET super_admin_id = sa.id
FROM domain_monitor.super_admin sa
WHERE sa.username = md.domain
  AND md.super_admin_id IS NULL;

-- 将域名备注同步到超管备注（基于 domain -> super_admin_id 映射）
UPDATE domain_monitor.super_admin sa
SET remark = md.remark
FROM domain_monitor.monitored_domains md
WHERE md.super_admin_id = sa.id
  AND md.remark IS NOT NULL
  AND md.remark != ''
  AND (sa.remark IS NULL OR sa.remark = '');