unis_crm/sql/upgrade_dashboard_card_permissions_pg17.sql

set search_path to public;

do $$
declare
  v_system_id bigint;
  v_frontend_home_id bigint;
  v_todo_perm_id bigint;
  v_activity_perm_id bigint;
  v_has_role_permission_tenant boolean;
begin
  -- 1. 检查 sys_role_permission 是否带 tenant_id
  select exists (
    select 1
    from information_schema.columns
    where table_schema = current_schema()
      and table_name = 'sys_role_permission'
      and column_name = 'tenant_id'
  ) into v_has_role_permission_tenant;

  -- 2. 定位系统管理
  select perm_id
    into v_system_id
  from sys_permission
  where code = 'system'
    and coalesce(is_deleted, 0) = 0
  order by perm_id
  limit 1;

  if v_system_id is null then
    raise exception '未找到 system 权限，请先确认系统管理目录存在';
  end if;

  -- 3. 创建或修复“前台首页”分组
  select perm_id
    into v_frontend_home_id
  from sys_permission
  where code = 'menu:frontend-home'
  order by coalesce(is_deleted, 0) asc, perm_id asc
  limit 1;

  if v_frontend_home_id is null then
    insert into sys_permission (
      parent_id, name, code, perm_type, level, path, component, icon,
      sort_order, is_visible, status, description, meta, is_deleted, created_at, updated_at
    ) values (
      v_system_id,
      '前台首页',
      'menu:frontend-home',
      'menu',
      2,
      null,
      null,
      'HomeOutlined',
      7,
      0,
      1,
      '前台首页相关权限分组',
      '{}'::jsonb,
      0,
      now(),
      now()
    )
    returning perm_id into v_frontend_home_id;
  else
    update sys_permission
    set parent_id = v_system_id,
        name = '前台首页',
        code = 'menu:frontend-home',
        perm_type = 'menu',
        level = 2,
        path = null,
        component = null,
        icon = 'HomeOutlined',
        sort_order = 7,
        is_visible = 0,
        status = 1,
        description = '前台首页相关权限分组',
        meta = '{}'::jsonb,
        is_deleted = 0,
        updated_at = now()
    where perm_id = v_frontend_home_id;
  end if;

  -- 4. 创建或修复“查看首页待办卡片”
  select perm_id
    into v_todo_perm_id
  from sys_permission
  where code = 'dashboard_todo_card:view'
  order by coalesce(is_deleted, 0) asc, perm_id asc
  limit 1;

  if v_todo_perm_id is null then
    insert into sys_permission (
      parent_id, name, code, perm_type, level, path, component, icon,
      sort_order, is_visible, status, description, meta, is_deleted, created_at, updated_at
    ) values (
      v_frontend_home_id,
      '查看首页待办卡片',
      'dashboard_todo_card:view',
      'button',
      3,
      null,
      null,
      null,
      20,
      1,
      1,
      '控制首页待办事项卡片是否可见',
      '{}'::jsonb,
      0,
      now(),
      now()
    )
    returning perm_id into v_todo_perm_id;
  else
    update sys_permission
    set parent_id = v_frontend_home_id,
        name = '查看首页待办卡片',
        code = 'dashboard_todo_card:view',
        perm_type = 'button',
        level = 3,
        path = null,
        component = null,
        icon = null,
        sort_order = 20,
        is_visible = 1,
        status = 1,
        description = '控制首页待办事项卡片是否可见',
        meta = '{}'::jsonb,
        is_deleted = 0,
        updated_at = now()
    where perm_id = v_todo_perm_id;
  end if;

  -- 5. 创建或修复“查看首页最新动态卡片”
  select perm_id
    into v_activity_perm_id
  from sys_permission
  where code = 'dashboard_activity_card:view'
  order by coalesce(is_deleted, 0) asc, perm_id asc
  limit 1;

  if v_activity_perm_id is null then
    insert into sys_permission (
      parent_id, name, code, perm_type, level, path, component, icon,
      sort_order, is_visible, status, description, meta, is_deleted, created_at, updated_at
    ) values (
      v_frontend_home_id,
      '查看首页最新动态卡片',
      'dashboard_activity_card:view',
      'button',
      3,
      null,
      null,
      null,
      21,
      1,
      1,
      '控制首页最新动态卡片是否可见',
      '{}'::jsonb,
      0,
      now(),
      now()
    )
    returning perm_id into v_activity_perm_id;
  else
    update sys_permission
    set parent_id = v_frontend_home_id,
        name = '查看首页最新动态卡片',
        code = 'dashboard_activity_card:view',
        perm_type = 'button',
        level = 3,
        path = null,
        component = null,
        icon = null,
        sort_order = 21,
        is_visible = 1,
        status = 1,
        description = '控制首页最新动态卡片是否可见',
        meta = '{}'::jsonb,
        is_deleted = 0,
        updated_at = now()
    where perm_id = v_activity_perm_id;
  end if;

  -- 6. 软删重复的前台首页分组
  update sys_permission
  set is_deleted = 1,
      updated_at = now()
  where code = 'menu:frontend-home'
    and perm_id <> v_frontend_home_id;

  -- 7. 软删重复的待办权限
  update sys_permission
  set is_deleted = 1,
      updated_at = now()
  where code = 'dashboard_todo_card:view'
    and perm_id <> v_todo_perm_id;

  -- 8. 软删重复的动态权限
  update sys_permission
  set is_deleted = 1,
      updated_at = now()
  where code = 'dashboard_activity_card:view'
    and perm_id <> v_activity_perm_id;

  -- 9. 给管理员类角色补齐 system / 前台首页 / 待办卡片 / 最新动态卡片 权限
  if v_has_role_permission_tenant then
    insert into sys_role_permission (role_id, perm_id, tenant_id, is_deleted, created_at, updated_at)
    select
      r.role_id,
      p.perm_id,
      r.tenant_id,
      0,
      now(),
      now()
    from sys_role r
    join sys_permission p
      on p.code in ('system', 'menu:frontend-home', 'dashboard_todo_card:view', 'dashboard_activity_card:view')
     and coalesce(p.is_deleted, 0) = 0
    where coalesce(r.is_deleted, 0) = 0
      and (
        r.role_code in ('TENANT_ADMIN', 'ADMIN', 'SYS_ADMIN', 'PLATFORM_ADMIN', 'SUPER_ADMIN')
        or r.role_name ilike '%管理员%'
        or r.role_name ilike '%admin%'
      )
      and not exists (
        select 1
        from sys_role_permission rp
        where rp.role_id = r.role_id
          and rp.perm_id = p.perm_id
      );

    update sys_role_permission rp
    set tenant_id = coalesce(rp.tenant_id, r.tenant_id),
        is_deleted = 0,
        updated_at = now()
    from sys_role r,
         sys_permission p
    where rp.role_id = r.role_id
      and p.perm_id = rp.perm_id
      and coalesce(r.is_deleted, 0) = 0
      and p.code in ('system', 'menu:frontend-home', 'dashboard_todo_card:view', 'dashboard_activity_card:view')
      and (
        r.role_code in ('TENANT_ADMIN', 'ADMIN', 'SYS_ADMIN', 'PLATFORM_ADMIN', 'SUPER_ADMIN')
        or r.role_name ilike '%管理员%'
        or r.role_name ilike '%admin%'
      );
  else
    insert into sys_role_permission (role_id, perm_id, is_deleted, created_at, updated_at)
    select
      r.role_id,
      p.perm_id,
      0,
      now(),
      now()
    from sys_role r
    join sys_permission p
      on p.code in ('system', 'menu:frontend-home', 'dashboard_todo_card:view', 'dashboard_activity_card:view')
     and coalesce(p.is_deleted, 0) = 0
    where coalesce(r.is_deleted, 0) = 0
      and (
        r.role_code in ('TENANT_ADMIN', 'ADMIN', 'SYS_ADMIN', 'PLATFORM_ADMIN', 'SUPER_ADMIN')
        or r.role_name ilike '%管理员%'
        or r.role_name ilike '%admin%'
      )
      and not exists (
        select 1
        from sys_role_permission rp
        where rp.role_id = r.role_id
          and rp.perm_id = p.perm_id
      );

    update sys_role_permission rp
    set is_deleted = 0,
        updated_at = now()
    where exists (
      select 1
      from sys_role r
      where r.role_id = rp.role_id
        and coalesce(r.is_deleted, 0) = 0
        and (
          r.role_code in ('TENANT_ADMIN', 'ADMIN', 'SYS_ADMIN', 'PLATFORM_ADMIN', 'SUPER_ADMIN')
          or r.role_name ilike '%管理员%'
          or r.role_name ilike '%admin%'
        )
    )
    and exists (
      select 1
      from sys_permission p
      where p.perm_id = rp.perm_id
        and p.code in ('system', 'menu:frontend-home', 'dashboard_todo_card:view', 'dashboard_activity_card:view')
        and coalesce(p.is_deleted, 0) = 0
    );
  end if;
end
$$;