-- 测试数据库备份功能的SQL语句

-- 1. 创建SQL备份记录表
CREATE TABLE IF NOT EXISTS `kku_cleanup_sql_backups` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `backup_id` bigint(20) unsigned NOT NULL COMMENT '关联备份记录ID',
  `table_name` varchar(100) NOT NULL COMMENT '表名',
  `sql_content` longtext NOT NULL COMMENT 'INSERT语句内容',
  `records_count` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '记录数量',
  `content_size` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '内容大小(字节)',
  `content_hash` varchar(64) DEFAULT NULL COMMENT '内容SHA256哈希',
  `backup_conditions` json DEFAULT NULL COMMENT '备份条件',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_backup_id` (`backup_id`),
  KEY `idx_table_name` (`table_name`),
  KEY `idx_records_count` (`records_count`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='SQL备份记录表';

-- 2. 测试数据库备份功能
-- 假设我们有一个测试表和一些测试数据

-- 创建测试表
CREATE TABLE IF NOT EXISTS `kku_test_backup_demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `status` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='备份功能测试表';

-- 插入测试数据
INSERT INTO `kku_test_backup_demo` (`name`, `email`, `status`) VALUES
('张三', 'zhangsan@example.com', 1),
('李四', 'lisi@example.com', 1),
('王五', 'wangwu@example.com', 0),
('赵六', 'zhaoliu@example.com', 1);

-- 3. 查询SQL备份记录的示例
-- 查看所有SQL备份记录
SELECT 
    sb.id,
    sb.backup_id,
    sb.table_name,
    sb.records_count,
    sb.content_size,
    ROUND(sb.content_size / 1024, 2) as size_kb,
    sb.created_at,
    b.backup_name,
    b.backup_type
FROM kku_cleanup_sql_backups sb
LEFT JOIN kku_cleanup_backups b ON sb.backup_id = b.id
ORDER BY sb.created_at DESC;

-- 查看特定表的SQL备份
SELECT 
    sb.*,
    LEFT(sb.sql_content, 200) as sql_preview
FROM kku_cleanup_sql_backups sb
WHERE sb.table_name = 'kku_test_backup_demo'
ORDER BY sb.created_at DESC;

-- 统计SQL备份信息
SELECT 
    COUNT(*) as total_backups,
    COUNT(DISTINCT table_name) as unique_tables,
    SUM(records_count) as total_records,
    SUM(content_size) as total_size_bytes,
    ROUND(SUM(content_size) / 1024 / 1024, 2) as total_size_mb,
    AVG(records_count) as avg_records_per_backup,
    AVG(content_size) as avg_size_per_backup
FROM kku_cleanup_sql_backups;

-- 按表名分组的统计
SELECT 
    table_name,
    COUNT(*) as backup_count,
    SUM(records_count) as total_records,
    SUM(content_size) as total_size,
    ROUND(SUM(content_size) / 1024, 2) as size_kb,
    MAX(created_at) as latest_backup
FROM kku_cleanup_sql_backups
GROUP BY table_name
ORDER BY total_size DESC;

-- 4. 清理测试数据（可选）
-- DROP TABLE IF EXISTS `kku_test_backup_demo`;