-- AI近视防控系统 - 测试账号创建脚本

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

-- 使用数据库
USE ai_myopia_db;

-- 创建用户表（如果不存在）
CREATE TABLE IF NOT EXISTS user_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(64) NOT NULL,
    phone VARCHAR(20) NOT NULL UNIQUE,
    user_type ENUM('student', 'parent', 'teacher', 'admin') NOT NULL,
    status TINYINT DEFAULT 1,
    last_login_at DATETIME NULL,
    last_login_ip VARCHAR(45) DEFAULT '',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 创建测试管理员账号
DELETE FROM user_accounts WHERE username = 'admin';
INSERT INTO user_accounts (username, password_hash, name, phone, user_type, status) VALUES (
    'admin',
    '$2a$10$ES13mXJ4KzObj4wHXxVtzuGbBsy7.Wu8vpa6Z1ZSRdW332itPCO4i',  -- Admin123!@# 的bcrypt哈希
    '系统管理员',
    '13800138000',
    'admin',
    1
);

-- 创建测试老师账号
DELETE FROM user_accounts WHERE username = 'teacher';
INSERT INTO user_accounts (username, password_hash, name, phone, user_type, status) VALUES (
    'teacher',
    '$2a$10$ES13mXJ4KzObj4wHXxVtzuGbBsy7.Wu8vpa6Z1ZSRdW332itPCO4i',  -- Admin123!@# 的bcrypt哈希
    '测试老师',
    '13800138001',
    'teacher',
    1
);

-- 创建测试学生账号
DELETE FROM user_accounts WHERE username = 'student';
INSERT INTO user_accounts (username, password_hash, name, phone, user_type, status) VALUES (
    'student',
    '$2a$10$ES13mXJ4KzObj4wHXxVtzuGbBsy7.Wu8vpa6Z1ZSRdW332itPCO4i',  -- Admin123!@# 的bcrypt哈希
    '测试学生',
    '13800138002',
    'student',
    1
);

-- 创建测试家长账号
DELETE FROM user_accounts WHERE username = 'parent';
INSERT INTO user_accounts (username, password_hash, name, phone, user_type, status) VALUES (
    'parent',
    '$2a$10$ES13mXJ4KzObj4wHXxVtzuGbBsy7.Wu8vpa6Z1ZSRdW332itPCO4i',  -- Admin123!@# 的bcrypt哈希
    '测试家长',
    '13800138003',
    'parent',
    1
);

-- 验证账号创建
SELECT 
    id,
    username,
    name,
    phone,
    user_type,
    status
FROM user_accounts 
WHERE username IN ('admin', 'teacher', 'student', 'parent');

-- 输出测试信息
SELECT '--- 测试账号信息 ---' as info;
SELECT '管理员账号: admin / Admin123!@#' as admin_info;
SELECT '老师账号: teacher / Admin123!@#' as teacher_info;
SELECT '学生账号: student / Admin123!@#' as student_info;
SELECT '家长账号: parent / Admin123!@#' as parent_info;