-- 創建玩家表
CREATE TABLE Player (
    PlayerID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Password VARCHAR(255) NOT NULL,
    RegisterDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    LastLoginDate DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 創建遊戲表
CREATE TABLE Game (
    GameID INT AUTO_INCREMENT PRIMARY KEY,
    GameName VARCHAR(100) NOT NULL,
    Genre VARCHAR(50) NOT NULL,
    ReleaseDate DATE NOT NULL,
    Description TEXT,
    CoverImage VARCHAR(255),
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 創建製造商表
CREATE TABLE Manufacturer (
    ManufacturerID INT AUTO_INCREMENT PRIMARY KEY,
    GameID INT UNIQUE,
    Name VARCHAR(100) NOT NULL,
    Country VARCHAR(50),
    FoundedYear INT,
    Description TEXT,
    LogoURL VARCHAR(255),
    Website VARCHAR(255),
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (GameID) REFERENCES Game(GameID) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 創建成就表
CREATE TABLE Achievement (
    AchievementID INT AUTO_INCREMENT PRIMARY KEY,
    GameID INT,
    AchievementName VARCHAR(100) NOT NULL,
    Description TEXT,
    Points INT DEFAULT 0,
    IconURL VARCHAR(255),
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (GameID) REFERENCES Game(GameID) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 創建遊戲進度表
CREATE TABLE GameProgress (
    PlayerID INT,
    GameID INT,
    ProgressPercentage DECIMAL(5,2) DEFAULT 0.00,
    LastPlayedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (PlayerID, GameID),
    FOREIGN KEY (PlayerID) REFERENCES Player(PlayerID) ON DELETE CASCADE,
    FOREIGN KEY (GameID) REFERENCES Game(GameID) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 創建收藏表
CREATE TABLE Favorites (
    PlayerID INT,
    GameID INT,
    AddedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (PlayerID, GameID),
    FOREIGN KEY (PlayerID) REFERENCES Player(PlayerID) ON DELETE CASCADE,
    FOREIGN KEY (GameID) REFERENCES Game(GameID) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• Game ↔ Spokesperson (1:1)
• Game → Achievement (1:N)
• Player → GameProgress (1:N)
• Player ↔ Achievement (M:N)

• 註冊
• 登錄
• 個人信息管理

• 遊戲列表展示
• 遊戲詳情頁
• 遊戲搜索
• 遊戲收藏

• 進度顯示
• 進度更新
• 歷史記錄

• 成就列表
• 解鎖記錄
• 完成度統計

GET  /api/users/{id}        # 獲取用戶信息
POST /api/users/register    # 用戶註冊
POST /api/users/login       # 用戶登錄

GET  /api/games            # 獲取遊戲列表
GET  /api/games/{id}       # 獲取遊戲詳情
GET  /api/games/search     # 搜索遊戲

GET  /api/progress/{userId} # 獲取進度
POST /api/progress/update   # 更新進度

GET  /api/achievements/{gameId}  # 獲取遊戲成就
POST /api/achievements/unlock    # 解鎖成就