-- ================================================
-- Game Guild Backend Database Init Script
-- 数据库：ggdev
-- 生成日期：2025-12-19
-- ================================================

-- 设置字符集
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ================================================
-- 1. 创建用户表 (users)
-- ================================================
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` varchar(36) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `role` enum('admin','user') NOT NULL DEFAULT 'user',
  `isMember` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否为会员',
  `memberExpireAt` datetime DEFAULT NULL COMMENT '会员到期时间',
  `lastLoginIp` varchar(50) DEFAULT NULL COMMENT '最后登录IP',
  `lastLoginAt` datetime DEFAULT NULL COMMENT '最后登录时间',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `UQ_username` (`username`),
  UNIQUE KEY `UQ_email` (`email`),
  UNIQUE KEY `UQ_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- ================================================
-- 2. 创建小组表 (groups)
-- ================================================
DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
  `id` varchar(36) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text,
  `avatar` varchar(255) DEFAULT NULL,
  `ownerId` varchar(36) NOT NULL,
  `type` varchar(20) NOT NULL DEFAULT 'normal' COMMENT '类型: normal/guild',
  `parentId` varchar(36) DEFAULT NULL COMMENT '父组ID，用于子组',
  `announcement` text COMMENT '公示信息',
  `maxMembers` int NOT NULL DEFAULT '50' COMMENT '最大成员数',
  `currentMembers` int NOT NULL DEFAULT '1' COMMENT '当前成员数',
  `isActive` tinyint(1) NOT NULL DEFAULT '1',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `FK_groups_ownerId` (`ownerId`),
  KEY `FK_groups_parentId` (`parentId`),
  CONSTRAINT `FK_groups_ownerId` FOREIGN KEY (`ownerId`) REFERENCES `users` (`id`),
  CONSTRAINT `FK_groups_parentId` FOREIGN KEY (`parentId`) REFERENCES `groups` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='小组表';

-- ================================================
-- 3. 创建小组成员表 (group_members)
-- ================================================
DROP TABLE IF EXISTS `group_members`;
CREATE TABLE `group_members` (
  `id` varchar(36) NOT NULL,
  `groupId` varchar(36) NOT NULL,
  `userId` varchar(36) NOT NULL,
  `role` enum('owner','admin','member') NOT NULL DEFAULT 'member',
  `nickname` varchar(50) DEFAULT NULL COMMENT '组内昵称',
  `isActive` tinyint(1) NOT NULL DEFAULT '1',
  `joinedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `UQ_groupId_userId` (`groupId`, `userId`),
  KEY `FK_group_members_groupId` (`groupId`),
  KEY `FK_group_members_userId` (`userId`),
  CONSTRAINT `FK_group_members_groupId` FOREIGN KEY (`groupId`) REFERENCES `groups` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_group_members_userId` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='小组成员表';

-- ================================================
-- 4. 创建游戏表 (games)
-- ================================================
DROP TABLE IF EXISTS `games`;
CREATE TABLE `games` (
  `id` varchar(36) NOT NULL,
  `name` varchar(100) NOT NULL,
  `coverUrl` varchar(255) DEFAULT NULL,
  `description` text,
  `maxPlayers` int NOT NULL COMMENT '最大玩家数',
  `minPlayers` int NOT NULL DEFAULT '1' COMMENT '最小玩家数',
  `platform` varchar(50) DEFAULT NULL COMMENT '平台',
  `tags` text COMMENT '游戏标签',
  `isActive` tinyint(1) NOT NULL DEFAULT '1',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='游戏表';

-- ================================================
-- 5. 创建预约表 (appointments)
-- ================================================
DROP TABLE IF EXISTS `appointments`;
CREATE TABLE `appointments` (
  `id` varchar(36) NOT NULL,
  `groupId` varchar(36) NOT NULL,
  `gameId` varchar(36) NOT NULL,
  `initiatorId` varchar(36) NOT NULL,
  `title` varchar(200) DEFAULT NULL,
  `description` text,
  `startTime` datetime NOT NULL,
  `endTime` datetime DEFAULT NULL,
  `maxParticipants` int NOT NULL COMMENT '最大参与人数',
  `currentParticipants` int NOT NULL DEFAULT '0' COMMENT '当前参与人数',
  `status` enum('pending','open','full','cancelled','finished') NOT NULL DEFAULT 'open',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `FK_appointments_groupId` (`groupId`),
  KEY `FK_appointments_gameId` (`gameId`),
  KEY `FK_appointments_initiatorId` (`initiatorId`),
  CONSTRAINT `FK_appointments_groupId` FOREIGN KEY (`groupId`) REFERENCES `groups` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_appointments_gameId` FOREIGN KEY (`gameId`) REFERENCES `games` (`id`),
  CONSTRAINT `FK_appointments_initiatorId` FOREIGN KEY (`initiatorId`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='预约表';

-- ================================================
-- 6. 创建预约参与者表 (appointment_participants)
-- ================================================
DROP TABLE IF EXISTS `appointment_participants`;
CREATE TABLE `appointment_participants` (
  `id` varchar(36) NOT NULL,
  `appointmentId` varchar(36) NOT NULL,
  `userId` varchar(36) NOT NULL,
  `status` enum('joined','pending','rejected') NOT NULL DEFAULT 'joined',
  `note` text COMMENT '备注',
  `joinedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `UQ_appointmentId_userId` (`appointmentId`, `userId`),
  KEY `FK_appointment_participants_appointmentId` (`appointmentId`),
  KEY `FK_appointment_participants_userId` (`userId`),
  CONSTRAINT `FK_appointment_participants_appointmentId` FOREIGN KEY (`appointmentId`) REFERENCES `appointments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_appointment_participants_userId` FOREIGN KEY (`userId`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='预约参与者表';

-- ================================================
-- 7. 创建资产表 (assets)
-- ================================================
DROP TABLE IF EXISTS `assets`;
CREATE TABLE `assets` (
  `id` varchar(36) NOT NULL,
  `groupId` varchar(36) NOT NULL,
  `type` enum('account','item') NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text COMMENT '描述',
  `accountCredentials` text COMMENT '加密的账号凭据',
  `quantity` int NOT NULL DEFAULT '1' COMMENT '数量（用于物品）',
  `status` enum('available','in_use','borrowed','maintenance') NOT NULL DEFAULT 'available',
  `currentBorrowerId` varchar(36) DEFAULT NULL COMMENT '当前借用人ID',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `FK_assets_groupId` (`groupId`),
  CONSTRAINT `FK_assets_groupId` FOREIGN KEY (`groupId`) REFERENCES `groups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='资产表';

-- ================================================
-- 8. 创建资产日志表 (asset_logs)
-- ================================================
DROP TABLE IF EXISTS `asset_logs`;
CREATE TABLE `asset_logs` (
  `id` varchar(36) NOT NULL,
  `assetId` varchar(36) NOT NULL,
  `userId` varchar(36) NOT NULL,
  `action` enum('borrow','return','add','remove') NOT NULL,
  `quantity` int NOT NULL DEFAULT '1' COMMENT '数量',
  `note` text COMMENT '备注',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `FK_asset_logs_assetId` (`assetId`),
  KEY `FK_asset_logs_userId` (`userId`),
  CONSTRAINT `FK_asset_logs_assetId` FOREIGN KEY (`assetId`) REFERENCES `assets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_asset_logs_userId` FOREIGN KEY (`userId`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='资产日志表';

-- ================================================
-- 9. 创建积分表 (points)
-- ================================================
DROP TABLE IF EXISTS `points`;
CREATE TABLE `points` (
  `id` varchar(36) NOT NULL,
  `userId` varchar(36) NOT NULL,
  `groupId` varchar(36) NOT NULL,
  `amount` int NOT NULL COMMENT '积分变动值，正为增加，负为减少',
  `reason` varchar(100) NOT NULL COMMENT '原因',
  `description` text COMMENT '详细说明',
  `relatedId` varchar(36) DEFAULT NULL COMMENT '关联ID（如活动ID、预约ID）',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `FK_points_userId` (`userId`),
  KEY `FK_points_groupId` (`groupId`),
  CONSTRAINT `FK_points_userId` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_points_groupId` FOREIGN KEY (`groupId`) REFERENCES `groups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='积分表';

-- ================================================
-- 10. 创建账本表 (ledgers)
-- ================================================
DROP TABLE IF EXISTS `ledgers`;
CREATE TABLE `ledgers` (
  `id` varchar(36) NOT NULL,
  `groupId` varchar(36) NOT NULL,
  `creatorId` varchar(36) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `type` enum('income','expense') NOT NULL,
  `category` varchar(50) DEFAULT NULL COMMENT '分类',
  `description` text,
  `proofImages` text COMMENT '凭证图片',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `FK_ledgers_groupId` (`groupId`),
  KEY `FK_ledgers_creatorId` (`creatorId`),
  CONSTRAINT `FK_ledgers_groupId` FOREIGN KEY (`groupId`) REFERENCES `groups` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_ledgers_creatorId` FOREIGN KEY (`creatorId`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='账本表';

-- ================================================
-- 11. 创建荣誉表 (honors)
-- ================================================
DROP TABLE IF EXISTS `honors`;
CREATE TABLE `honors` (
  `id` varchar(36) NOT NULL,
  `groupId` varchar(36) NOT NULL,
  `title` varchar(200) NOT NULL,
  `description` text,
  `mediaUrls` text COMMENT '媒体文件URLs',
  `eventDate` date NOT NULL COMMENT '事件日期',
  `participantIds` text COMMENT '参与者ID列表',
  `creatorId` varchar(36) NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `FK_honors_groupId` (`groupId`),
  KEY `FK_honors_creatorId` (`creatorId`),
  CONSTRAINT `FK_honors_groupId` FOREIGN KEY (`groupId`) REFERENCES `groups` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_honors_creatorId` FOREIGN KEY (`creatorId`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='荣誉表';

-- ================================================
-- 12. 创建日程表 (schedules)
-- ================================================
DROP TABLE IF EXISTS `schedules`;
CREATE TABLE `schedules` (
  `id` varchar(36) NOT NULL,
  `userId` varchar(36) NOT NULL,
  `groupId` varchar(36) NOT NULL,
  `availableSlots` text NOT NULL COMMENT '空闲时间段 JSON: { "mon": ["20:00-23:00"], ... }',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `FK_schedules_userId` (`userId`),
  KEY `FK_schedules_groupId` (`groupId`),
  CONSTRAINT `FK_schedules_userId` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_schedules_groupId` FOREIGN KEY (`groupId`) REFERENCES `groups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='日程表';

-- ================================================
-- 13. 创建黑名单表 (blacklists)
-- ================================================
DROP TABLE IF EXISTS `blacklists`;
CREATE TABLE `blacklists` (
  `id` varchar(36) NOT NULL,
  `targetGameId` varchar(100) NOT NULL COMMENT '目标游戏ID或用户名',
  `reason` text NOT NULL,
  `reporterId` varchar(36) NOT NULL,
  `proofImages` text COMMENT '证据图片',
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `reviewerId` varchar(36) DEFAULT NULL COMMENT '审核人ID',
  `reviewNote` text COMMENT '审核意见',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `FK_blacklists_reporterId` (`reporterId`),
  KEY `FK_blacklists_reviewerId` (`reviewerId`),
  CONSTRAINT `FK_blacklists_reporterId` FOREIGN KEY (`reporterId`) REFERENCES `users` (`id`),
  CONSTRAINT `FK_blacklists_reviewerId` FOREIGN KEY (`reviewerId`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='黑名单表';

-- ================================================
-- 14. 创建竞猜表 (bets)
-- ================================================
DROP TABLE IF EXISTS `bets`;
CREATE TABLE `bets` (
  `id` varchar(36) NOT NULL,
  `appointmentId` varchar(36) NOT NULL,
  `userId` varchar(36) NOT NULL,
  `betOption` varchar(100) NOT NULL COMMENT '下注选项',
  `amount` int NOT NULL COMMENT '下注积分',
  `status` enum('pending','won','cancelled','lost') NOT NULL DEFAULT 'pending',
  `winAmount` int NOT NULL DEFAULT '0' COMMENT '赢得的积分',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `FK_bets_appointmentId` (`appointmentId`),
  KEY `FK_bets_userId` (`userId`),
  CONSTRAINT `FK_bets_appointmentId` FOREIGN KEY (`appointmentId`) REFERENCES `appointments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_bets_userId` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='竞猜表';

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

-- ================================================
-- 初始化测试数据（可选）
-- ================================================

-- 插入一个管理员账户 (密码: Admin@123，需要通过程序加密)
-- INSERT INTO `users` (`id`, `username`, `email`, `role`, `password`) 
-- VALUES (UUID(), 'admin', 'admin@example.com', 'admin', 'hashed_password_here');

-- 插入一些示例游戏
-- INSERT INTO `games` (`id`, `name`, `maxPlayers`, `minPlayers`, `platform`, `description`) VALUES
-- (UUID(), 'DOTA 2', 10, 2, 'Steam', '经典MOBA游戏'),
-- (UUID(), 'CS:GO', 10, 2, 'Steam', '经典FPS游戏'),
-- (UUID(), '王者荣耀', 10, 2, 'Mobile', '热门MOBA手游');