/*好友系統*/
CREATE TABLE `friend_list` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '編號',
    `user_id` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '加入人',
    `friend_id` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '加入對象',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '資料更新時間',
    `deleted_at` DATETIME DEFAULT NULL COMMENT '刪除時間 (軟刪除)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='好友清單';

/*遊玩關卡紀錄*/
CREATE TABLE `log_level_detail` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '編號',
    `user_id` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '使用者',
    `accent` ENUM('four','sea','big','an','rap','sfour') NOT NULL COMMENT '腔調別',
    `chapter` TINYINT UNSIGNED NOT NULL COMMENT '章節',
    `level` TINYINT UNSIGNED NOT NULL COMMENT '關卡',
    `number` TINYINT UNSIGNED NOT NULL COMMENT '目前題號',
    `point` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '暫存積分',
    `pass_time` DATETIME DEFAULT NULL COMMENT '通關時間',
    `used_time` DATETIME DEFAULT NULL COMMENT '總花費時間',
    `correct` TINYINT UNSIGNED DEFAULT '0' COMMENT '答對數',
    `wrong` TINYINT UNSIGNED DEFAULT '0' COMMENT '答錯數',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '資料更新時間',
    `deleted_at` DATETIME DEFAULT NULL COMMENT '刪除時間 (軟刪除)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='遊玩關卡紀錄';

/*各題花費時間*/
CREATE TABLE `log_level_time` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '編號',
    `level_id` INT UNSIGNED NOT NULL COMMENT '遊玩編號',
    `number` TINYINT UNSIGNED NOT NULL COMMENT '題號',
    `start_time` DATETIME NOT NULL COMMENT '開始時間',
    `end_time` DATETIME DEFAULT NULL COMMENT '答題時間',
    `used_time` DATETIME DEFAULT NULL COMMENT '花費時間',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '資料更新時間',
    `deleted_at` DATETIME DEFAULT NULL COMMENT '刪除時間 (軟刪除)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='各題花費時間';

/*關卡錯題紀錄(逐題更新)*/
CREATE TABLE `log_level_wrong` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '編號',
    `user_id` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '使用者',
    `accent` ENUM('four','sea','big','an','rap','sfour') NOT NULL COMMENT '腔調別',
    `chapter` TINYINT UNSIGNED NOT NULL COMMENT '章節',
    `level` TINYINT UNSIGNED NOT NULL COMMENT '關卡',
    `number` TINYINT UNSIGNED NOT NULL COMMENT '題號',
    `times` TINYINT UNSIGNED NOT NULL DEFAULT '1' COMMENT '錯誤次數',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '資料更新時間',
    `deleted_at` DATETIME DEFAULT NULL COMMENT '刪除時間 (軟刪除)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='關卡錯題紀錄';

/*用戶能量*/
CREATE TABLE `log_energy` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '編號',
    `user_id` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '使用者帳號',
    `energy` TINYINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '花費能量',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '資料更新時間',
    `deleted_at` DATETIME DEFAULT NULL COMMENT '刪除時間 (軟刪除)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用戶能量';