-- 数据库初始化脚本
-- 创建数据库和用户，配置字符集和时区

-- 设置字符集和编码
SET NAMES utf8mb4;
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_database = utf8mb4;
SET character_set_results = utf8mb4;
SET character_set_server = utf8mb4;
SET collation_connection = utf8mb4_unicode_ci;
SET collation_database = utf8mb4_unicode_ci;
SET collation_server = utf8mb4_unicode_ci;

-- 禁用外键检查以避免初始化时的依赖问题
SET FOREIGN_KEY_CHECKS = 0;

-- 设置时区为中国标准时间
SET time_zone = '+8:00';
SET GLOBAL time_zone = '+8:00';

-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS `anxin` 
    DEFAULT CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE `anxin`;

-- 创建应用用户（如果不存在）
-- 使用环境变量或默认值
SET @app_user = IFNULL(@MYSQL_USER, 'anxin');
SET @app_password = IFNULL(@MYSQL_PASSWORD, 'anxin123');

-- 创建用户并设置密码
SET @sql = CONCAT('CREATE USER IF NOT EXISTS ''', @app_user, '''@''%'' IDENTIFIED BY ''', @app_password, '''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 授予数据库权限
SET @sql = CONCAT('GRANT ALL PRIVILEGES ON `anxin`.* TO ''', @app_user, '''@''%''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 授予必要的系统权限（用于应用正常运行）
SET @sql = CONCAT('GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `anxin`.* TO ''', @app_user, '''@''%''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 刷新权限
FLUSH PRIVILEGES;

-- 恢复外键检查
SET FOREIGN_KEY_CHECKS = 1;

-- 显示创建的数据库和用户信息
SELECT 'Database anxin created successfully' as Status;
SELECT CONCAT('User ', @app_user, ' created and granted permissions') as UserStatus;