gangbi_web/database/init.sql

-- 创建数据库
DROP DATABASE IF EXISTS game_categories;
CREATE DATABASE game_categories DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE game_categories;

-- 管理员表
CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    role ENUM('superadmin', 'admin', 'editor') DEFAULT 'editor',
    status ENUM('active', 'inactive') DEFAULT 'active',
    last_login DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 管理员权限表
CREATE TABLE admin_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    permission VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
    UNIQUE KEY unique_admin_permission (admin_id, permission)
) ENGINE=InnoDB;

-- 分类表
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 游戏表
CREATE TABLE games (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    description TEXT,
    category_id INT,
    image VARCHAR(255),
    release_date DATE,
    developer VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- 游戏平台关联表
CREATE TABLE game_platforms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    game_id INT,
    platform VARCHAR(50),
    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 游戏标签关联表
CREATE TABLE game_tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    game_id INT,
    tag VARCHAR(50),
    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 游戏标签主表
CREATE TABLE game_tag_master (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 系统配置表
CREATE TABLE system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(50) NOT NULL,
    `key` VARCHAR(50) NOT NULL,
    value TEXT,
    description VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_setting (`category`, `key`)
) ENGINE=InnoDB;

-- 留言表
CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    content TEXT NOT NULL,
    status ENUM('unread', 'read') DEFAULT 'unread',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    read_by INT,
    read_at TIMESTAMP NULL,
    FOREIGN KEY (read_by) REFERENCES admins(id)
) ENGINE=InnoDB;

-- 创建索引
CREATE INDEX idx_games_category ON games(category_id);
CREATE INDEX idx_games_title ON games(title);

-- 初始化数据
-- 创建超级管理员账号 (密码: 123qwe)
INSERT INTO admins (username, password, email, role, status) VALUES 
('admin', '$2a$10$YqFCm8pRCKOPtPDzqvwsUOQY7ZeUFNHDHI8Qc3ej3wqgNWO6GEBqe', 'admin@example.com', 'superadmin', 'active');

-- 添加管理员权限
INSERT INTO admin_permissions (admin_id, permission) VALUES
(1, 'user:manage'),
(1, 'game:manage'),
(1, 'category:manage'),
(1, 'media:manage'),
(1, 'system:manage'),
(1, 'message:manage');

-- 插入游戏分类
INSERT INTO categories (name, description) VALUES
('动作游戏', '包含格斗、射击等动作元素的游戏'),
('角色扮演', '玩家可以扮演角色进行冒险的游戏'),
('策略游戏', '需要战略思维的游戏'),
('体育竞技', '模拟各种体育运动的游戏'),
('休闲益智', '简单有趣的休闲游戏');

-- 插入示例游戏数据
INSERT INTO games (title, description, category_id, developer, release_date) VALUES
('魔兽世界', '著名的大型多人在线角色扮演游戏', 2, 'Blizzard', '2004-11-23'),
('FIFA 23', '最新的足球体育游戏', 4, 'EA Sports', '2022-09-30'),
('俄罗斯方块', '经典的休闲益智游戏', 5, 'Various', '1984-06-06');

-- 添加游戏平台数据
INSERT INTO game_platforms (game_id, platform) VALUES
(1, 'PC'),
(1, 'Mac'),
(2, 'PC'),
(2, 'PS5'),
(2, 'Xbox'),
(3, 'PC'),
(3, 'Mobile');

-- 添加游戏标签
INSERT INTO game_tags (game_id, tag) VALUES
(1, 'MMORPG'),
(1, '奇幻'),
(2, '体育'),
(2, '足球'),
(3, '益智'),
(3, '经典');

-- 插入一些默认标签
INSERT INTO game_tag_master (name, description) VALUES
('RPG', '角色扮演游戏'),
('动作', '动作类游戏'),
('策略', '策略类游戏'),
('射击', '射击类游戏'),
('冒险', '冒险类游戏'),
('体育', '体育类游戏'),
('竞速', '竞速类游戏'),
('模拟', '模拟类游戏');

-- 插入初始配置数据
INSERT INTO system_settings (category, `key`, value, description) VALUES
-- 公司信息
('company', 'name', 'Game Categories', '公司名称'),
('company', 'description', 'Game Categories 成立于2024年，是一家专注于游戏分类和推荐的创新型科技公司。', '公司简介'),
('company', 'mission', '让每个玩家都能找到最适合自己的游戏，创造快乐的游戏体验。', '公司使命'),
('company', 'vision', '成为全球领先的游戏分类和推荐平台，引领游戏文化的发展。', '公司愿景'),
-- 联系方式
('contact', 'address', '北京市朝阳区xxx大厦', '公司地址'),
('contact', 'email', 'contact@example.com', '联系邮箱'),
('contact', 'phone', '+86 123 4567 8900', '联系电话'),
-- 核心价值观
('values', 'value1', '{"title":"用户至上","description":"始终以用户需求为中心，提供最优质的服务"}', '核心价值观1'),
('values', 'value2', '{"title":"创新驱动","description":"持续创新，推动技术与产品的进步"}', '核心价值观2'),
('values', 'value3', '{"title":"品质保证","description":"严格把控质量，确保服务的可靠性"}', '核心价值观3'),
('values', 'value4', '{"title":"开放共赢","description":"与合作伙伴共同成长，实现价值共享"}', '核心价值观4');

-- 更新默认权限设置
UPDATE admins SET role = 'admin' WHERE role = 'editor';  -- 将编辑角色升级为管理员