-- 迁移：user_site_roles 和 user_assistant_binding 软删除支持
-- 日期：2026-03-24
-- 原因：租户后台"移除用户"改为软删除，保留历史记录可追溯
-- 影响：后端所有查询需加 is_removed = false 过滤

BEGIN;

-- 1. user_site_roles 加软删除字段
ALTER TABLE auth.user_site_roles
    ADD COLUMN IF NOT EXISTS is_removed boolean NOT NULL DEFAULT false,
    ADD COLUMN IF NOT EXISTS removed_at timestamptz;

-- 2. user_assistant_binding 加软删除字段
ALTER TABLE auth.user_assistant_binding
    ADD COLUMN IF NOT EXISTS is_removed boolean NOT NULL DEFAULT false,
    ADD COLUMN IF NOT EXISTS removed_at timestamptz;

-- 3. 部分索引：加速常规查询（只查未移除的记录）
CREATE INDEX IF NOT EXISTS idx_user_site_roles_active
    ON auth.user_site_roles (user_id, site_id)
    WHERE is_removed = false;

CREATE INDEX IF NOT EXISTS idx_user_assistant_binding_active
    ON auth.user_assistant_binding (user_id, site_id)
    WHERE is_removed = false;

COMMIT;

-- ── 回滚 ──
-- BEGIN;
-- DROP INDEX IF EXISTS auth.idx_user_site_roles_active;
-- DROP INDEX IF EXISTS auth.idx_user_assistant_binding_active;
-- ALTER TABLE auth.user_site_roles DROP COLUMN IF EXISTS removed_at, DROP COLUMN IF EXISTS is_removed;
-- ALTER TABLE auth.user_assistant_binding DROP COLUMN IF EXISTS removed_at, DROP COLUMN IF EXISTS is_removed;
-- COMMIT;
