AiDOPWarehouse/scripts/s8/seed-s8-business-roles.sql

-- =============================================================================
-- seed-s8-business-roles.sql
-- S8-SEED-DURABILITY-1: idempotent seed for S8 business roles
--
-- Purpose: Restore S8 business SysRole entries and related config that
--          S8SysRoleSeedData / S8RolePermissionSeedData would normally insert
--          when EnableIncreSeed=true. This script is safe to run repeatedly.
--
-- Root cause: EnableIncreSeed=false in Database.json (intentional, avoids
--             unintended re-seed of other modules on restart).
--
-- Usage:
--   mysql -h <host> -P 3306 -u <user> -p<pwd> <db> < seed-s8-business-roles.sql
--
-- Idempotency: INSERT IGNORE skips rows with duplicate primary keys.
--              No DROP / TRUNCATE / DELETE on any table.
--
-- Targets (dev DB): aidopdev
-- Last updated: 2026-04-29
-- =============================================================================

-- ---------------------------------------------------------------------------
-- Section 1: S8 business roles → SysRole
-- Matches S8SysRoleSeedData.HasData() exactly.
-- TenantId = 1300000000001 (SqlSugarConst.DefaultTenantId)
-- DataScope = 4 (DataScopeEnum.Self)
-- Status = 1 (StatusEnum.Enable)
-- ---------------------------------------------------------------------------
INSERT IGNORE INTO SysRole
    (Id, Name, Code, OrderNo, DataScope, Remark, Status, TenantId, CreateTime)
VALUES
    (1329908000101, '订单计划员', 'ROLE_ORDER_PLANNER',      800, 4, 'S8 业务角色（异常协同承接）', 1, 1300000000001, '2026-04-18 00:00:00'),
    (1329908000102, '生产计划员', 'ROLE_PRODUCTION_PLANNER', 801, 4, 'S8 业务角色（异常协同承接）', 1, 1300000000001, '2026-04-18 00:00:00'),
    (1329908000103, '采购员',     'ROLE_PURCHASER',          802, 4, 'S8 业务角色（异常协同承接）', 1, 1300000000001, '2026-04-18 00:00:00'),
    (1329908000104, '仓库收货员', 'ROLE_WH_INBOUND',         803, 4, 'S8 业务角色（异常协同承接）', 1, 1300000000001, '2026-04-18 00:00:00'),
    (1329908000105, '仓库发货员', 'ROLE_WH_OUTBOUND',        804, 4, 'S8 业务角色（异常协同承接）', 1, 1300000000001, '2026-04-18 00:00:00'),
    (1329908000106, '质检员',     'ROLE_QC',                 805, 4, 'S8 业务角色（异常协同承接）', 1, 1300000000001, '2026-04-18 00:00:00'),
    (1329908000107, '设备维修员', 'ROLE_EQUIP_MAINT',        806, 4, 'S8 业务角色（异常协同承接）', 1, 1300000000001, '2026-04-18 00:00:00');

SELECT CONCAT('SysRole inserted: ', ROW_COUNT()) AS result;

-- ---------------------------------------------------------------------------
-- Section 2: S8 role permission baseline → ado_s8_role_permission_config
-- Matches S8RolePermissionSeedData.HasData() exactly.
-- TenantId=0 / FactoryId=0 = global baseline (not tenant-scoped).
-- ---------------------------------------------------------------------------
INSERT IGNORE INTO ado_s8_role_permission_config
    (id, tenant_id, factory_id, role_code, permission_codes, created_at)
VALUES
    (1329908000001, 0, 0, 'ROLE_ORDER_PLANNER',      's8:exception:read,s8:exception:assign', '2026-04-18 00:00:00'),
    (1329908000002, 0, 0, 'ROLE_PRODUCTION_PLANNER', 's8:exception:read,s8:exception:assign', '2026-04-18 00:00:00'),
    (1329908000003, 0, 0, 'ROLE_PURCHASER',          's8:exception:read,s8:exception:assign', '2026-04-18 00:00:00'),
    (1329908000004, 0, 0, 'ROLE_WH_INBOUND',         's8:exception:read,s8:exception:assign', '2026-04-18 00:00:00'),
    (1329908000005, 0, 0, 'ROLE_WH_OUTBOUND',        's8:exception:read,s8:exception:assign', '2026-04-18 00:00:00'),
    (1329908000006, 0, 0, 'ROLE_QC',                 's8:exception:read,s8:exception:assign', '2026-04-18 00:00:00'),
    (1329908000007, 0, 0, 'ROLE_EQUIP_MAINT',        's8:exception:read,s8:exception:assign', '2026-04-18 00:00:00');

SELECT CONCAT('S8RolePermissionConfig inserted: ', ROW_COUNT()) AS result;

-- ---------------------------------------------------------------------------
-- Section 3: S8 business role → test user bindings → SysUserRole
-- Demo01 UserId=1300000000117 (Account='Demo01')
-- Demo02 UserId=789819678195781 (Account='Demo02')
-- Strategy: Demo01 → planning/purchasing/warehouse; Demo02 → production/QC/maintenance
-- All 7 roles must resolve to ≥1 userId for notification chain to work.
-- ---------------------------------------------------------------------------
INSERT IGNORE INTO SysUserRole (Id, UserId, RoleId)
VALUES
    (1329909000001, 1300000000117,   1329908000101),  -- Demo01 → ROLE_ORDER_PLANNER
    (1329909000002, 789819678195781, 1329908000102),  -- Demo02 → ROLE_PRODUCTION_PLANNER
    (1329909000003, 1300000000117,   1329908000103),  -- Demo01 → ROLE_PURCHASER
    (1329909000004, 1300000000117,   1329908000104),  -- Demo01 → ROLE_WH_INBOUND
    (1329909000005, 1300000000117,   1329908000105),  -- Demo01 → ROLE_WH_OUTBOUND
    (1329909000006, 789819678195781, 1329908000106),  -- Demo02 → ROLE_QC
    (1329909000007, 789819678195781, 1329908000107);  -- Demo02 → ROLE_EQUIP_MAINT

SELECT CONCAT('SysUserRole (S8 role bindings) inserted: ', ROW_COUNT()) AS result;

-- ---------------------------------------------------------------------------
-- Section 4: notification_layer zero-datetime guard
-- ado_s8_notification_layer.created_at is NOT NULL with no default.
-- If rows exist with '0000-00-00 00:00:00', SqlSugar DateTime binding fails.
-- Fix any zero-date rows (safe: only affects layers that already have bad data).
-- ---------------------------------------------------------------------------
-- YEAR(created_at)=0 avoids the '0000-00-00' literal which MySQL strict mode rejects.
UPDATE ado_s8_notification_layer
SET created_at = '2026-04-29 00:00:00'
WHERE YEAR(created_at) = 0;

SELECT CONCAT('notification_layer zero-date fixed: ', ROW_COUNT()) AS result;