-- 006_create_role_permissions.sql
-- 角色权限关系表：把每个角色拥有的权限点落库，后台才能动态分配权限。

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id INT UNSIGNED NOT NULL COMMENT '角色 ID',
  permission_code VARCHAR(100) NOT NULL COMMENT '权限点编码',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (role_id, permission_code),
  KEY idx_role_permissions_permission_code (permission_code),
  CONSTRAINT fk_role_permissions_role_id FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色权限关系表';

-- 初始化内置角色的默认后台权限，保持历史行为，同时给管理员开放权限分配能力。
INSERT IGNORE INTO role_permissions (role_id, permission_code)
SELECT r.id, p.permission_code
FROM roles r
INNER JOIN (
  SELECT 'admin' AS role_code, 'store:view' AS permission_code
  UNION ALL SELECT 'admin', 'store:manage'
  UNION ALL SELECT 'admin', 'role:view'
  UNION ALL SELECT 'admin', 'role:manage'
  UNION ALL SELECT 'admin', 'employee:view:all'
  UNION ALL SELECT 'admin', 'employee:manage'
  UNION ALL SELECT 'admin', 'permission:view'
  UNION ALL SELECT 'admin', 'permission:manage'
  UNION ALL SELECT 'store_manager', 'employee:view:store'
) p ON p.role_code = r.code;