-- 数据库备份功能：记录表、JSON配置、系统菜单和按钮权限

SET @tenant_id := 1;

CREATE TABLE IF NOT EXISTS `db_backup_record` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `backup_time` datetime NOT NULL COMMENT '备份开始时间',
  `tables_dumped` text COMMENT '备份的表列表(JSON数组)',
  `file_name` varchar(255) DEFAULT NULL COMMENT '备份目录名',
  `file_size` bigint DEFAULT NULL COMMENT '文件大小(字节)',
  `status` varchar(32) NOT NULL DEFAULT 'RUNNING' COMMENT '状态:RUNNING/SUCCESS/FAILED',
  `trigger_type` varchar(32) NOT NULL DEFAULT 'MANUAL' COMMENT '触发方式:MANUAL/SCHEDULED',
  `error_message` varchar(1024) DEFAULT NULL COMMENT '失败原因',
  `upload_status` varchar(32) DEFAULT NULL COMMENT '上传状态:SKIPPED/SUCCESS/FAILED',
  `upload_time` datetime DEFAULT NULL COMMENT '上传时间',
  `upload_error` varchar(512) DEFAULT NULL COMMENT '上传失败原因',
  `duration_ms` bigint DEFAULT NULL COMMENT '备份耗时(毫秒)',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_db_backup_record_status` (`status`),
  KEY `idx_db_backup_record_time` (`backup_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据库备份记录';

INSERT INTO sys_config (
  uuid, name, flag, type, val, content, status, deleted, tenant_id,
  create_by, create_time, update_by, update_time, memo
)
SELECT
  CONCAT(UNIX_TIMESTAMP(NOW(3)), '001'),
  '数据库备份配置',
  'dbBackupConfig',
  4,
  '{"backupEnabled":true,"backupCron":"0 0 2 * * ?","backupTables":[],"backupExcludeTables":[],"backupPath":"../stock/out/wms/dbBackups","retentionCount":10,"batchSize":1000,"uploadEnabled":false,"uploadUrl":"","uploadToken":""}',
  '数据库备份配置',
  1,
  0,
  @tenant_id,
  1,
  NOW(),
  1,
  NOW(),
  '数据库备份JSON配置'
FROM dual
WHERE NOT EXISTS (
  SELECT 1 FROM sys_config WHERE deleted = 0 AND tenant_id = @tenant_id AND flag = 'dbBackupConfig'
);

SET @system_menu_id := (
  SELECT id
  FROM sys_menu
  WHERE deleted = 0
    AND tenant_id = @tenant_id
    AND type = 0
    AND (route = '/system' OR component = 'system' OR name = 'menu.system')
  ORDER BY id
  LIMIT 1
);

INSERT INTO sys_menu (
  name, parent_id, parent_name, path, path_name, route, component, brief,
  code, type, authority, icon, sort, meta, tenant_id, status, deleted,
  create_time, create_by, update_time, update_by, memo
)
SELECT
  'menu.dbBackup',
  @system_menu_id,
  'menu.system',
  '/system/dbBackup',
  '/system/dbBackup',
  '/system/dbBackup',
  'dbBackup',
  '数据库备份',
  NULL,
  0,
  'system:dbBackup:list',
  'DatabaseBackup',
  91,
  '{"title":"menus.system.dbBackup","icon":"DatabaseBackup","keepAlive":true}',
  @tenant_id,
  1,
  0,
  NOW(),
  1,
  NOW(),
  1,
  '数据库备份配置、执行、历史记录'
FROM dual
WHERE @system_menu_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM sys_menu
    WHERE deleted = 0
      AND tenant_id = @tenant_id
      AND type = 0
      AND (route = '/system/dbBackup' OR component = 'dbBackup' OR authority = 'system:dbBackup:list')
  );

SET @db_backup_menu_id := (
  SELECT id
  FROM sys_menu
  WHERE deleted = 0
    AND tenant_id = @tenant_id
    AND type = 0
    AND (route = '/system/dbBackup' OR component = 'dbBackup' OR authority = 'system:dbBackup:list')
  ORDER BY id
  LIMIT 1
);

INSERT INTO sys_menu (
  name, parent_id, parent_name, path, path_name, route, component, brief,
  code, type, authority, icon, sort, meta, tenant_id, status, deleted,
  create_time, create_by, update_time, update_by, memo
)
SELECT permission_name, @db_backup_menu_id, 'menu.dbBackup', '1', 'menu.system', NULL, NULL, permission_name,
       NULL, 1, permission_authority, NULL, permission_sort, NULL, @tenant_id, 1, 0,
       NOW(), 1, NOW(), 1, permission_name
FROM (
  SELECT '查看数据库备份' permission_name, 'system:dbBackup:list' permission_authority, 1 permission_sort
  UNION ALL SELECT '保存数据库备份配置', 'system:dbBackup:save', 2
  UNION ALL SELECT '执行数据库备份', 'system:dbBackup:run', 3
  UNION ALL SELECT '下载数据库备份', 'system:dbBackup:download', 4
  UNION ALL SELECT '删除数据库备份', 'system:dbBackup:remove', 5
) permissions
WHERE @db_backup_menu_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM sys_menu
    WHERE deleted = 0
      AND tenant_id = @tenant_id
      AND type = 1
      AND authority = permissions.permission_authority
  );

SET @admin_role_id := (
  SELECT id
  FROM sys_role
  WHERE deleted = 0
    AND tenant_id = @tenant_id
    AND (id = 1 OR code IN ('ADMIN', 'R_ADMIN', 'R_SUPER') OR name IN ('管理员', '超级管理员'))
  ORDER BY id
  LIMIT 1
);

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT @admin_role_id, sm.id
FROM sys_menu sm
WHERE @admin_role_id IS NOT NULL
  AND sm.deleted = 0
  AND sm.tenant_id = @tenant_id
  AND (
    sm.id = @db_backup_menu_id
    OR sm.authority IN (
      'system:dbBackup:list',
      'system:dbBackup:save',
      'system:dbBackup:run',
      'system:dbBackup:download',
      'system:dbBackup:remove'
    )
  )
  AND NOT EXISTS (
    SELECT 1
    FROM sys_role_menu srm
    WHERE srm.role_id = @admin_role_id
      AND srm.menu_id = sm.id
  );