-- AI_CHANGELOG
-- | 日期 | Prompt | 变更 |
-- |------|--------|------|
-- | 2026-03-23 | 角色体系隔离+店铺管理员 | 清理小程序 RBAC 中的 site_admin/tenant_admin；新增 head_coach/manager；tenant_admins 加 admin_type |

-- 迁移：角色体系隔离 + 店铺管理员支持
-- 原因：site_admin/tenant_admin 属于租户管理后台概念，不应出现在小程序 RBAC 体系中
-- 回滚：见文件末尾

BEGIN;

-- ═══════════════════════════════════════════════════════════
-- Part A: 清理小程序 RBAC 中的 site_admin / tenant_admin
-- ═══════════════════════════════════════════════════════════

-- A1. 安全检查：确认无用户绑定这两个角色（如有则中止）
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM auth.user_site_roles usr
    JOIN auth.roles r ON r.id = usr.role_id
    WHERE r.code IN ('site_admin', 'tenant_admin')
  ) THEN
    RAISE EXCEPTION '存在用户绑定了 site_admin/tenant_admin 角色，需先迁移数据';
  END IF;
END $$;

-- A2. 删除 role_permissions 关联
DELETE FROM auth.role_permissions
 WHERE role_id IN (SELECT id FROM auth.roles WHERE code IN ('site_admin', 'tenant_admin'));

-- A3. 删除角色记录
DELETE FROM auth.roles WHERE code IN ('site_admin', 'tenant_admin');

-- ═══════════════════════════════════════════════════════════
-- Part B: 新增 head_coach / manager 角色（幂等）
-- ═══════════════════════════════════════════════════════════

INSERT INTO auth.roles (code, name, description)
VALUES ('head_coach', '教练', '教练，可查看任务和看板')
ON CONFLICT (code) DO NOTHING;

INSERT INTO auth.roles (code, name, description)
VALUES ('manager', '管理员', '店铺管理员（小程序端），可查看所有板块')
ON CONFLICT (code) DO NOTHING;

-- B2. 为 head_coach 分配权限：view_tasks, view_board
INSERT INTO auth.role_permissions (role_id, permission_id)
SELECT r.id, p.id
  FROM auth.roles r, auth.permissions p
 WHERE r.code = 'head_coach' AND p.code IN ('view_tasks', 'view_board')
ON CONFLICT DO NOTHING;

-- B3. 为 manager 分配全部 5 个权限
INSERT INTO auth.role_permissions (role_id, permission_id)
SELECT r.id, p.id
  FROM auth.roles r, auth.permissions p
 WHERE r.code = 'manager'
ON CONFLICT DO NOTHING;

-- ═══════════════════════════════════════════════════════════
-- Part C: tenant_admins 加 admin_type 列
-- ═══════════════════════════════════════════════════════════

ALTER TABLE auth.tenant_admins
  ADD COLUMN IF NOT EXISTS admin_type VARCHAR(20) NOT NULL DEFAULT 'tenant_admin';

-- C2. CHECK 约束：只允许 tenant_admin / site_admin
ALTER TABLE auth.tenant_admins
  ADD CONSTRAINT chk_admin_type CHECK (admin_type IN ('tenant_admin', 'site_admin'));

COMMIT;

-- ═══════════════════════════════════════════════════════════
-- 回滚
-- ═══════════════════════════════════════════════════════════
-- BEGIN;
-- ALTER TABLE auth.tenant_admins DROP CONSTRAINT IF EXISTS chk_admin_type;
-- ALTER TABLE auth.tenant_admins DROP COLUMN IF EXISTS admin_type;
-- DELETE FROM auth.role_permissions WHERE role_id IN (SELECT id FROM auth.roles WHERE code IN ('head_coach', 'manager'));
-- DELETE FROM auth.roles WHERE code IN ('head_coach', 'manager');
-- INSERT INTO auth.roles (code, name, description) VALUES ('site_admin', '店铺管理员', '单店管理员，可查看所有看板和审核用户');
-- INSERT INTO auth.roles (code, name, description) VALUES ('tenant_admin', '租户管理员', '连锁管理员，可管理多店铺和所有功能');
-- -- 重新插入 role_permissions（site_admin/tenant_admin 各 5 条）
-- COMMIT;

-- ═══════════════════════════════════════════════════════════
-- 验证
-- ═══════════════════════════════════════════════════════════
-- 1. SELECT code, name FROM auth.roles ORDER BY id;
--    期望：coach, staff, head_coach, manager（无 site_admin/tenant_admin）
-- 2. SELECT r.code, array_agg(p.code ORDER BY p.code) FROM auth.role_permissions rp JOIN auth.roles r ON r.id = rp.role_id JOIN auth.permissions p ON p.id = rp.permission_id GROUP BY r.code;
--    期望：head_coach=[view_board, view_tasks], manager=[全部5个]
-- 3. SELECT column_name, data_type, column_default FROM information_schema.columns WHERE table_schema='auth' AND table_name='tenant_admins' AND column_name='admin_type';
--    期望：admin_type, varchar, 'tenant_admin'
