-- =========================================================
-- 升鲜宝 SaaS Admin：SYS & SAAS 常用 CRUD SQL 示例（用于对账/排查/脚本）
-- 数据库：saas_master_db
--
-- 说明：
-- 1) 生产建议仍走 API + Service（SQL 仅示例）
-- 2) “禁用”使用 enabled=0；“软删除”使用 del_flag=1
-- 3) create_date / update_date 统一毫秒时间戳
-- =========================================================
SET NAMES utf8mb4;
SET @now_ms := ROUND(UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)) * 1000);

-- =========================================================
-- 1) 用户 sys_user
-- =========================================================

-- 1.1 用户列表（分页 + 关键字）
-- :keyword 为空则不过滤；:offset=: (page-1)*size；:size
SELECT
  u.id,
  u.user_code,
  u.user_name,
  u.status,
  u.enabled,
  u.del_flag,
  u.create_date,
  u.update_date
FROM sys_user u
WHERE u.del_flag = 0
  AND (
    :keyword IS NULL OR :keyword = ''
    OR u.user_code LIKE CONCAT('%', :keyword, '%')
    OR u.user_name LIKE CONCAT('%', :keyword, '%')
  )
ORDER BY u.id DESC
LIMIT :offset, :size;

-- 1.2 用户详情
SELECT
  id, user_code, user_name, password_hash, status, enabled, del_flag, create_date, update_date
FROM sys_user
WHERE id = :userId
LIMIT 1;

-- 1.3 新增用户（password_hash 建议用 BCrypt）
-- 注意：user_code 需唯一
INSERT INTO sys_user (
  id, user_code, user_name, password_hash,
  status, enabled, del_flag,
  create_date, update_date
)
VALUES (
  :id, :userCode, :userName, :passwordHash,
  1, 1, 0,
  @now_ms, @now_ms
);

-- 1.4 修改用户（不改密码）
UPDATE sys_user
SET
  user_name = :userName,
  status = :status,
  enabled = :enabled,
  update_date = @now_ms
WHERE id = :userId
  AND del_flag = 0;

-- 1.5 修改密码（重置）
UPDATE sys_user
SET
  password_hash = :passwordHash,
  update_date = @now_ms
WHERE id = :userId
  AND del_flag = 0;

-- 1.6 禁用 / 启用
UPDATE sys_user
SET enabled = 0, update_date = @now_ms
WHERE id = :userId AND del_flag = 0;

UPDATE sys_user
SET enabled = 1, update_date = @now_ms
WHERE id = :userId AND del_flag = 0;

-- 1.7 软删除
UPDATE sys_user
SET del_flag = 1, update_date = @now_ms
WHERE id = :userId;

-- 1.8 用户角色列表
SELECT
  ur.user_id,
  ur.role_id,
  r.role_code,
  r.role_name
FROM sys_user_role ur
JOIN sys_role r ON r.id = ur.role_id AND r.del_flag = 0
WHERE ur.user_id = :userId
ORDER BY r.id;

-- 1.9 给用户分配角色（先删后插，或用事务）
DELETE FROM sys_user_role WHERE user_id = :userId;
INSERT INTO sys_user_role (id, user_id, role_id, create_date)
VALUES
  (:id1, :userId, :roleId1, @now_ms),
  (:id2, :userId, :roleId2, @now_ms);


-- =========================================================
-- 2) 角色 sys_role
-- =========================================================

-- 2.1 角色列表（分页 + 关键字）
SELECT
  r.id, r.role_code, r.role_name, r.enabled, r.del_flag, r.create_date, r.update_date
FROM sys_role r
WHERE r.del_flag = 0
  AND (
    :keyword IS NULL OR :keyword = ''
    OR r.role_code LIKE CONCAT('%', :keyword, '%')
    OR r.role_name LIKE CONCAT('%', :keyword, '%')
  )
ORDER BY r.id DESC
LIMIT :offset, :size;

-- 2.2 新增角色
INSERT INTO sys_role (
  id, role_code, role_name,
  enabled, del_flag,
  create_date, update_date
)
VALUES (
  :id, :roleCode, :roleName,
  1, 0,
  @now_ms, @now_ms
);

-- 2.3 修改角色
UPDATE sys_role
SET
  role_name = :roleName,
  enabled = :enabled,
  update_date = @now_ms
WHERE id = :roleId
  AND del_flag = 0;

-- 2.4 禁用 / 启用
UPDATE sys_role SET enabled = 0, update_date = @now_ms WHERE id = :roleId AND del_flag = 0;
UPDATE sys_role SET enabled = 1, update_date = @now_ms WHERE id = :roleId AND del_flag = 0;

-- 2.5 软删除
UPDATE sys_role SET del_flag = 1, update_date = @now_ms WHERE id = :roleId;


-- =========================================================
-- 3) 菜单 sys_menu
-- =========================================================

-- 3.1 菜单全量（用于构树）
SELECT
  id, parent_id, menu_type, menu_code, menu_name,
  route_path, component_path, perms_code, icon,
  sort_code, visible, enabled, del_flag
FROM sys_menu
WHERE del_flag = 0 AND enabled = 1
ORDER BY sort_code ASC, id ASC;

-- 3.2 新增菜单
INSERT INTO sys_menu (
  id, parent_id, menu_type,
  menu_code, menu_name,
  route_path, component_path,
  perms_code, icon,
  sort_code, visible, enabled, del_flag,
  create_date, update_date
)
VALUES (
  :id, :parentId, :menuType,
  :menuCode, :menuName,
  :routePath, :componentPath,
  :permsCode, :icon,
  :sortCode, :visible, :enabled, 0,
  @now_ms, @now_ms
);

-- 3.3 修改菜单
UPDATE sys_menu
SET
  parent_id = :parentId,
  menu_type = :menuType,
  menu_name = :menuName,
  route_path = :routePath,
  component_path = :componentPath,
  perms_code = :permsCode,
  icon = :icon,
  sort_code = :sortCode,
  visible = :visible,
  enabled = :enabled,
  update_date = @now_ms
WHERE id = :menuId AND del_flag = 0;

-- 3.4 软删除菜单
UPDATE sys_menu SET del_flag = 1, update_date = @now_ms WHERE id = :menuId;


-- =========================================================
-- 4) 权限点 sys_permission / 授权 sys_role_permission
-- =========================================================

-- 4.1 权限点列表（分页 + 关键字）
SELECT
  p.id, p.perm_type, p.perm_code, p.perm_name, p.perm_value,
  p.menu_id, p.enabled, p.del_flag, p.create_date, p.update_date
FROM sys_permission p
WHERE p.del_flag = 0
  AND (
    :keyword IS NULL OR :keyword = ''
    OR p.perm_code LIKE CONCAT('%', :keyword, '%')
    OR p.perm_name LIKE CONCAT('%', :keyword, '%')
    OR p.perm_value LIKE CONCAT('%', :keyword, '%')
  )
ORDER BY p.id DESC
LIMIT :offset, :size;

-- 4.2 新增权限点
INSERT INTO sys_permission (
  id, perm_type, perm_code, perm_name, perm_value,
  menu_id, enabled, del_flag,
  create_date, update_date
)
VALUES (
  :id, :permType, :permCode, :permName, :permValue,
  :menuId, 1, 0,
  @now_ms, @now_ms
);

-- 4.3 修改权限点
UPDATE sys_permission
SET
  perm_type = :permType,
  perm_name = :permName,
  perm_value = :permValue,
  menu_id = :menuId,
  enabled = :enabled,
  update_date = @now_ms
WHERE id = :permId AND del_flag = 0;

-- 4.4 禁用 / 启用权限点
UPDATE sys_permission SET enabled=0, update_date=@now_ms WHERE id=:permId AND del_flag=0;
UPDATE sys_permission SET enabled=1, update_date=@now_ms WHERE id=:permId AND del_flag=0;

-- 4.5 软删除权限点
UPDATE sys_permission SET del_flag=1, update_date=@now_ms WHERE id=:permId;

-- 4.6 查询角色拥有的权限点
SELECT
  rp.role_id,
  rp.permission_id,
  p.perm_code,
  p.perm_name,
  p.perm_value
FROM sys_role_permission rp
JOIN sys_permission p ON p.id = rp.permission_id AND p.del_flag=0
WHERE rp.role_id = :roleId
ORDER BY p.id;

-- 4.7 角色授权（先删后插）
DELETE FROM sys_role_permission WHERE role_id = :roleId;
INSERT INTO sys_role_permission (id, role_id, permission_id, create_date)
VALUES
  (:id1, :roleId, :permId1, @now_ms),
  (:id2, :roleId, :permId2, @now_ms);


-- =========================================================
-- 5) SaaS 套餐/租户/订阅（saas_plan / saas_tenant / saas_subscription）
-- =========================================================

-- 5.1 套餐列表
SELECT id, plan_code, plan_name, billing_cycle, currency_code, base_price, enabled, del_flag
FROM saas_plan
WHERE del_flag=0
ORDER BY id DESC
LIMIT :offset, :size;

-- 5.2 新增/修改/禁用套餐
INSERT INTO saas_plan (id, plan_code, plan_name, billing_cycle, currency_code, base_price, enabled, del_flag, create_date, update_date)
VALUES (:id, :planCode, :planName, :billingCycle, :currencyCode, :basePrice, 1, 0, @now_ms, @now_ms);

UPDATE saas_plan
SET plan_name=:planName, billing_cycle=:billingCycle, currency_code=:currencyCode, base_price=:basePrice, enabled=:enabled, update_date=@now_ms
WHERE id=:planId AND del_flag=0;

UPDATE saas_plan SET enabled=0, update_date=@now_ms WHERE id=:planId AND del_flag=0;
UPDATE saas_plan SET enabled=1, update_date=@now_ms WHERE id=:planId AND del_flag=0;

-- 5.3 租户列表
SELECT id, tenant_code, tenant_name, tenant_state, plan_id, expire_date, shard_id, db_name, del_flag
FROM saas_tenant
WHERE del_flag=0
ORDER BY id DESC
LIMIT :offset, :size;

-- 5.4 新增/修改/冻结/解冻租户
INSERT INTO saas_tenant (id, tenant_code, tenant_name, tenant_state, plan_id, expire_date, shard_id, db_name, del_flag, create_date, update_date)
VALUES (:id, :tenantCode, :tenantName, 1, :planId, :expireDate, :shardId, :dbName, 0, @now_ms, @now_ms);

UPDATE saas_tenant
SET tenant_name=:tenantName, plan_id=:planId, expire_date=:expireDate, shard_id=:shardId, db_name=:dbName, update_date=@now_ms
WHERE id=:tenantId AND del_flag=0;

UPDATE saas_tenant SET tenant_state=2, update_date=@now_ms WHERE id=:tenantId AND del_flag=0; -- 冻结
UPDATE saas_tenant SET tenant_state=1, update_date=@now_ms WHERE id=:tenantId AND del_flag=0; -- 解冻

-- 5.5 订阅列表（按租户）
SELECT id, tenant_id, plan_id, sub_state, auto_renew, start_date, end_date, del_flag
FROM saas_subscription
WHERE del_flag=0 AND (:tenantId IS NULL OR tenant_id=:tenantId)
ORDER BY id DESC
LIMIT :offset, :size;

-- 5.6 新增/修改订阅
INSERT INTO saas_subscription (id, tenant_id, plan_id, sub_state, auto_renew, start_date, end_date, del_flag, create_date, update_date)
VALUES (:id, :tenantId, :planId, :subState, :autoRenew, :startDate, :endDate, 0, @now_ms, @now_ms);

UPDATE saas_subscription
SET plan_id=:planId, sub_state=:subState, auto_renew=:autoRenew, start_date=:startDate, end_date=:endDate, update_date=@now_ms
WHERE id=:subId AND del_flag=0;