-- nl-video-api 数据库性能优化脚本
-- 执行前请备份数据库

-- ================================
-- 1. 索引优化
-- ================================

-- 用户表索引优化
ALTER TABLE `user` ADD INDEX `idx_username` (`username`);
ALTER TABLE `user` ADD INDEX `idx_phone` (`phone`);
ALTER TABLE `user` ADD INDEX `idx_email` (`email`);
ALTER TABLE `user` ADD INDEX `idx_status_vip` (`status`, `vip_level`);
ALTER TABLE `user` ADD INDEX `idx_created_at` (`created_at`);
ALTER TABLE `user` ADD INDEX `idx_last_login` (`last_login_at`);

-- 管理员表索引优化
ALTER TABLE `admin` ADD INDEX `idx_username` (`username`);
ALTER TABLE `admin` ADD INDEX `idx_email` (`email`);
ALTER TABLE `admin` ADD INDEX `idx_status` (`status`);

-- 影片表索引优化
ALTER TABLE `movie` ADD INDEX `idx_title` (`title`);
ALTER TABLE `movie` ADD INDEX `idx_type_category` (`type`, `category_id`);
ALTER TABLE `movie` ADD INDEX `idx_year` (`year`);
ALTER TABLE `movie` ADD INDEX `idx_country` (`country`);
ALTER TABLE `movie` ADD INDEX `idx_status` (`status`);
ALTER TABLE `movie` ADD INDEX `idx_rating` (`rating`);
ALTER TABLE `movie` ADD INDEX `idx_created_at` (`created_at`);
ALTER TABLE `movie` ADD INDEX `idx_updated_at` (`updated_at`);

-- 剧集表索引优化
ALTER TABLE `episode` ADD INDEX `idx_movie_id` (`movie_id`);
ALTER TABLE `episode` ADD INDEX `idx_episode_number` (`episode_number`);
ALTER TABLE `episode` ADD INDEX `idx_status` (`status`);

-- 分类表索引优化
ALTER TABLE `category` ADD INDEX `idx_parent_id` (`parent_id`);
ALTER TABLE `category` ADD INDEX `idx_sort` (`sort`);
ALTER TABLE `category` ADD INDEX `idx_status` (`status`);

-- 角色表索引优化
ALTER TABLE `role` ADD INDEX `idx_code` (`code`);
ALTER TABLE `role` ADD INDEX `idx_level` (`level`);
ALTER TABLE `role` ADD INDEX `idx_status` (`status`);

-- 权限表索引优化
ALTER TABLE `permission` ADD INDEX `idx_parent_id` (`parent_id`);
ALTER TABLE `permission` ADD INDEX `idx_type` (`type`);
ALTER TABLE `permission` ADD INDEX `idx_status` (`status`);

-- 角色权限关联表索引优化
ALTER TABLE `role_permission` ADD INDEX `idx_role_id` (`role_id`);
ALTER TABLE `role_permission` ADD INDEX `idx_permission_id` (`permission_id`);

-- ================================
-- 2. 表结构优化
-- ================================

-- 优化用户表字段类型
ALTER TABLE `user` MODIFY COLUMN `balance` DECIMAL(10,2) DEFAULT 0.00 COMMENT '余额';
ALTER TABLE `user` MODIFY COLUMN `points` INT UNSIGNED DEFAULT 0 COMMENT '积分';
ALTER TABLE `user` MODIFY COLUMN `vip_level` TINYINT UNSIGNED DEFAULT 0 COMMENT 'VIP等级';

-- 优化影片表字段类型
ALTER TABLE `movie` MODIFY COLUMN `rating` DECIMAL(3,1) DEFAULT 0.0 COMMENT '评分';
ALTER TABLE `movie` MODIFY COLUMN `duration` SMALLINT UNSIGNED DEFAULT 0 COMMENT '时长(分钟)';
ALTER TABLE `movie` MODIFY COLUMN `total_episodes` SMALLINT UNSIGNED DEFAULT 0 COMMENT '总集数';

-- 优化角色表字段类型
ALTER TABLE `role` MODIFY COLUMN `level` TINYINT UNSIGNED DEFAULT 1 COMMENT '角色等级';
ALTER TABLE `role` MODIFY COLUMN `sort` SMALLINT UNSIGNED DEFAULT 0 COMMENT '排序';

-- ================================
-- 3. 分区优化（适用于大数据量）
-- ================================

-- 用户表按注册时间分区（年度分区）
-- 注意：分区需要在表创建时定义，这里仅作为参考
/*
ALTER TABLE `user` PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);
*/

-- ================================
-- 4. 查询优化视图
-- ================================

-- 创建用户统计视图
CREATE OR REPLACE VIEW `v_user_stats` AS
SELECT 
    COUNT(*) as total_users,
    COUNT(CASE WHEN status = 1 THEN 1 END) as active_users,
    COUNT(CASE WHEN vip_level > 0 THEN 1 END) as vip_users,
    COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as today_new_users,
    AVG(balance) as avg_balance,
    SUM(balance) as total_balance
FROM `user`;

-- 创建影片统计视图
CREATE OR REPLACE VIEW `v_movie_stats` AS
SELECT 
    COUNT(*) as total_movies,
    COUNT(CASE WHEN type = 1 THEN 1 END) as movie_count,
    COUNT(CASE WHEN type = 2 THEN 1 END) as tv_series_count,
    COUNT(CASE WHEN status = 1 THEN 1 END) as published_count,
    AVG(rating) as avg_rating,
    COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as today_new_movies
FROM `movie`;

-- 创建热门影片视图
CREATE OR REPLACE VIEW `v_popular_movies` AS
SELECT 
    m.id,
    m.title,
    m.type,
    m.rating,
    m.year,
    c.name as category_name
FROM `movie` m
LEFT JOIN `category` c ON m.category_id = c.id
WHERE m.status = 1
ORDER BY m.rating DESC, m.created_at DESC;

-- ================================
-- 5. 存储过程优化
-- ================================

-- 用户余额更新存储过程
DELIMITER //
CREATE PROCEDURE `sp_update_user_balance`(
    IN p_user_id INT,
    IN p_amount DECIMAL(10,2),
    IN p_type TINYINT,
    IN p_remark VARCHAR(255)
)
BEGIN
    DECLARE v_current_balance DECIMAL(10,2) DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- 获取当前余额
    SELECT balance INTO v_current_balance FROM `user` WHERE id = p_user_id FOR UPDATE;
    
    -- 检查余额是否足够（扣款时）
    IF p_type = 2 AND v_current_balance < ABS(p_amount) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
    END IF;
    
    -- 更新余额
    IF p_type = 1 THEN
        UPDATE `user` SET balance = balance + p_amount WHERE id = p_user_id;
    ELSE
        UPDATE `user` SET balance = balance - ABS(p_amount) WHERE id = p_user_id;
    END IF;
    
    -- 记录余额变动日志（如果有日志表的话）
    -- INSERT INTO `balance_log` (user_id, amount, type, remark, created_at) 
    -- VALUES (p_user_id, p_amount, p_type, p_remark, NOW());
    
    COMMIT;
END //
DELIMITER ;

-- VIP升级存储过程
DELIMITER //
CREATE PROCEDURE `sp_upgrade_user_vip`(
    IN p_user_id INT,
    IN p_vip_level TINYINT,
    IN p_days INT
)
BEGIN
    DECLARE v_current_vip_expire DATETIME;
    DECLARE v_new_vip_expire DATETIME;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- 获取当前VIP到期时间
    SELECT vip_expire_at INTO v_current_vip_expire FROM `user` WHERE id = p_user_id;
    
    -- 计算新的到期时间
    IF v_current_vip_expire IS NULL OR v_current_vip_expire < NOW() THEN
        SET v_new_vip_expire = DATE_ADD(NOW(), INTERVAL p_days DAY);
    ELSE
        SET v_new_vip_expire = DATE_ADD(v_current_vip_expire, INTERVAL p_days DAY);
    END IF;
    
    -- 更新用户VIP信息
    UPDATE `user` SET 
        vip_level = p_vip_level,
        vip_expire_at = v_new_vip_expire,
        updated_at = NOW()
    WHERE id = p_user_id;
    
    COMMIT;
END //
DELIMITER ;

-- ================================
-- 6. 数据清理和维护
-- ================================

-- 清理过期的VIP用户
UPDATE `user` SET vip_level = 0 WHERE vip_expire_at < NOW() AND vip_level > 0;

-- 清理软删除的数据（超过30天）
DELETE FROM `movie` WHERE deleted_at IS NOT NULL AND deleted_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
DELETE FROM `user` WHERE deleted_at IS NOT NULL AND deleted_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- ================================
-- 7. 性能监控查询
-- ================================

-- 查看慢查询日志状态
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 查看索引使用情况
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    SUB_PART,
    PACKED,
    NULLABLE,
    INDEX_TYPE
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = 'nl_video_db'
ORDER BY TABLE_NAME, INDEX_NAME;

-- 查看表大小和行数
SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS 'Size(MB)',
    ROUND((DATA_LENGTH / 1024 / 1024), 2) AS 'Data(MB)',
    ROUND((INDEX_LENGTH / 1024 / 1024), 2) AS 'Index(MB)'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'nl_video_db'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- ================================
-- 8. 配置优化建议
-- ================================

/*
MySQL配置优化建议（my.cnf）：

[mysqld]
# 基础配置
default-storage-engine = InnoDB
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 内存配置
innodb_buffer_pool_size = 1G  # 设置为物理内存的70-80%
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
key_buffer_size = 256M
max_connections = 500
thread_cache_size = 50

# 查询缓存
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# InnoDB配置
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_open_files = 400

# 临时表配置
tmp_table_size = 256M
max_heap_table_size = 256M

# 排序和连接配置
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
*/

-- ================================
-- 9. 定期维护脚本
-- ================================

-- 创建定期维护事件
DELIMITER //
CREATE EVENT IF NOT EXISTS `ev_daily_maintenance`
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    -- 更新过期VIP用户
    UPDATE `user` SET vip_level = 0 WHERE vip_expire_at < NOW() AND vip_level > 0;
    
    -- 优化表
    OPTIMIZE TABLE `user`;
    OPTIMIZE TABLE `movie`;
    OPTIMIZE TABLE `episode`;
    
    -- 分析表
    ANALYZE TABLE `user`;
    ANALYZE TABLE `movie`;
    ANALYZE TABLE `episode`;
    
    -- 记录维护日志
    INSERT INTO `system_log` (type, message, created_at) 
    VALUES ('maintenance', 'Daily maintenance completed', NOW());
END //
DELIMITER ;

-- 启用事件调度器
SET GLOBAL event_scheduler = ON;

-- ================================
-- 10. 备份建议
-- ================================

/*
数据库备份脚本示例：

#!/bin/bash
# 数据库备份脚本

DB_NAME="nl_video_db"
DB_USER="root"
DB_PASS="password"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

# 创建备份目录
mkdir -p $BACKUP_DIR

# 全量备份
mysqldump -u$DB_USER -p$DB_PASS --single-transaction --routines --triggers $DB_NAME > $BACKUP_DIR/full_backup_$DATE.sql

# 压缩备份文件
gzip $BACKUP_DIR/full_backup_$DATE.sql

# 删除7天前的备份
find $BACKUP_DIR -name "full_backup_*.sql.gz" -mtime +7 -delete

echo "Backup completed: full_backup_$DATE.sql.gz"
*/

-- ================================
-- 执行完成提示
-- ================================

SELECT 'Database optimization completed successfully!' as message;
SELECT 'Please restart MySQL service to apply configuration changes.' as note;
SELECT 'Run SHOW PROCESSLIST; to monitor current queries.' as monitoring_tip;