-- =============================================================================
-- check-s8-business-roles.sql
-- S8-SEED-DURABILITY-1: read-only verification for S8 business role seed
--
-- Usage:
--   mysql -h <host> -P 3306 -u <user> -p<pwd> <db> < check-s8-business-roles.sql
--
-- Expected output:
--   SysRole count:                7
--   S8RolePermConfig count:       7
--   Demo01 binding count:         1
--   Notification layer bad dates: 0
-- =============================================================================

SELECT '=== SysRole: S8 business roles ===' AS '';
SELECT Id, Code, Name, Status
FROM SysRole
WHERE Id BETWEEN 1329908000101 AND 1329908000107
ORDER BY Id;

SELECT CONCAT(
    'SysRole count: ', COUNT(*),
    ' (expected 7)'
) AS check_result
FROM SysRole
WHERE Id BETWEEN 1329908000101 AND 1329908000107;

SELECT '=== ado_s8_role_permission_config ===' AS '';
SELECT id, role_code, permission_codes
FROM ado_s8_role_permission_config
WHERE id BETWEEN 1329908000001 AND 1329908000007
ORDER BY id;

SELECT CONCAT(
    'S8RolePermConfig count: ', COUNT(*),
    ' (expected 7)'
) AS check_result
FROM ado_s8_role_permission_config
WHERE id BETWEEN 1329908000001 AND 1329908000007;

SELECT '=== SysUserRole: S8 business role bindings ===' AS '';
SELECT ur.Id, ur.UserId, u.Account, r.Code AS RoleCode, r.Name AS RoleName
FROM SysUserRole ur
JOIN SysRole r ON ur.RoleId = r.Id
JOIN SysUser u ON ur.UserId = u.Id
WHERE ur.Id BETWEEN 1329909000001 AND 1329909000007
ORDER BY ur.Id;

SELECT CONCAT(
    'S8 role binding count: ', COUNT(*),
    ' (expected 7)'
) AS check_result
FROM SysUserRole
WHERE Id BETWEEN 1329909000001 AND 1329909000007;

SELECT CONCAT(
    'Roles without any binding: ',
    COUNT(*),
    ' (expected 0)'
) AS check_result
FROM SysRole r
WHERE r.Id BETWEEN 1329908000101 AND 1329908000107
  AND NOT EXISTS (
      SELECT 1 FROM SysUserRole ur WHERE ur.RoleId = r.Id
  );

SELECT '=== notification_layer zero-date guard ===' AS '';
SELECT CONCAT(
    'Notification layer bad dates: ', COUNT(*),
    ' (expected 0)'
) AS check_result
FROM ado_s8_notification_layer
WHERE YEAR(created_at) = 0;