-- Lucky Draw Database Schema
-- 抽奖游戏管理系统数据库结构
-- 版本: 1.0.0
-- 创建日期: 2025-12-03

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

-- 数据库: ldd

-- --------------------------------------------------------

-- 表1: admins - 管理员表
CREATE TABLE IF NOT EXISTS `admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL COMMENT '登录用户名',
  `password` varchar(255) NOT NULL COMMENT '密码哈希(bcrypt)',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `is_active` tinyint(1) DEFAULT 1 COMMENT '启用状态',
  `last_login` datetime DEFAULT NULL COMMENT '最后登录时间',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员表';

-- 插入默认管理员 (密码: admin123)
INSERT INTO `admins` (`username`, `password`, `email`, `is_active`) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@example.com', 1);

-- --------------------------------------------------------

-- 表2: game_templates - 游戏模板表
CREATE TABLE IF NOT EXISTS `game_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '游戏名称',
  `type` enum('wheel','grid','scratch') NOT NULL COMMENT '游戏类型',
  `description` text DEFAULT NULL COMMENT '游戏描述',
  `is_active` tinyint(1) DEFAULT 1 COMMENT '启用状态',
  `style_config` json DEFAULT NULL COMMENT '样式配置',
  `game_config` json DEFAULT NULL COMMENT '游戏配置',
  `hide_title` tinyint(1) DEFAULT 0 COMMENT '隐藏主标题',
  `hide_subtitle` tinyint(1) DEFAULT 0 COMMENT '隐藏副标题',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='游戏模板表';

-- --------------------------------------------------------

-- 表3: access_tokens - 访问令牌表
CREATE TABLE IF NOT EXISTS `access_tokens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL COMMENT '关联游戏模板',
  `token` varchar(32) NOT NULL COMMENT '访问令牌(MD5)',
  `name` varchar(100) DEFAULT NULL COMMENT '令牌名称/备注',
  `is_enabled` tinyint(1) DEFAULT 1 COMMENT '启用状态',
  `max_uses` int(11) DEFAULT NULL COMMENT '最大使用次数(NULL=无限)',
  `used_count` int(11) DEFAULT 0 COMMENT '已使用次数',
  `expired_at` datetime DEFAULT NULL COMMENT '过期时间',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `game_id` (`game_id`),
  CONSTRAINT `fk_tokens_game` FOREIGN KEY (`game_id`) REFERENCES `game_templates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='访问令牌表';

-- --------------------------------------------------------

-- 表4: prizes - 奖品表
CREATE TABLE IF NOT EXISTS `prizes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL COMMENT '关联游戏模板',
  `name` varchar(100) NOT NULL COMMENT '奖品名称',
  `image` varchar(255) DEFAULT NULL COMMENT '奖品图片路径',
  `probability` decimal(5,2) DEFAULT 0.00 COMMENT '中奖概率(%)',
  `stock` int(11) DEFAULT -1 COMMENT '库存(-1=无限)',
  `original_stock` int(11) DEFAULT -1 COMMENT '初始库存',
  `weight` int(11) DEFAULT 1 COMMENT '排序权重',
  `is_active` tinyint(1) DEFAULT 1 COMMENT '启用状态',
  `display_order` int(11) DEFAULT 0 COMMENT '显示顺序',
  `bg_color` varchar(20) DEFAULT NULL COMMENT '转盘背景色',
  `icon_scale` decimal(3,1) DEFAULT 1.0 COMMENT '图标缩放倍数',
  `icon_rotation` int(11) DEFAULT 0 COMMENT '图标旋转角度',
  `icon_position` decimal(3,2) DEFAULT 0.75 COMMENT '图标位置(0.1-0.9)',
  `text_offset_x` int(11) DEFAULT 0 COMMENT '文字X偏移(像素)',
  `text_offset_y` int(11) DEFAULT 0 COMMENT '文字Y偏移(像素)',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `game_id` (`game_id`),
  CONSTRAINT `fk_prizes_game` FOREIGN KEY (`game_id`) REFERENCES `game_templates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='奖品表';

-- --------------------------------------------------------

-- 表5: customers - 顾客表
CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '顾客姓名',
  `phone` varchar(20) DEFAULT NULL COMMENT '电话号码',
  `seat_no` varchar(20) DEFAULT NULL COMMENT '座位号',
  `server_name` varchar(50) DEFAULT NULL COMMENT '服务员姓名',
  `ip_address` varchar(45) DEFAULT NULL COMMENT 'IP地址',
  `user_agent` text DEFAULT NULL COMMENT '浏览器UA',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `phone` (`phone`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='顾客表';

-- --------------------------------------------------------

-- 表6: win_records - 中奖记录表
CREATE TABLE IF NOT EXISTS `win_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL COMMENT '游戏ID',
  `token_id` int(11) NOT NULL COMMENT '令牌ID',
  `prize_id` int(11) DEFAULT NULL COMMENT '奖品ID(NULL=未中奖)',
  `customer_id` int(11) NOT NULL COMMENT '顾客ID',
  `seat_no` varchar(20) DEFAULT NULL COMMENT '座位号',
  `is_won` tinyint(1) DEFAULT 0 COMMENT '是否中奖',
  `win_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '中奖时间',
  `ip_address` varchar(45) DEFAULT NULL COMMENT 'IP地址',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `game_id` (`game_id`),
  KEY `token_id` (`token_id`),
  KEY `prize_id` (`prize_id`),
  KEY `customer_id` (`customer_id`),
  KEY `win_time` (`win_time`),
  CONSTRAINT `fk_records_game` FOREIGN KEY (`game_id`) REFERENCES `game_templates` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_records_token` FOREIGN KEY (`token_id`) REFERENCES `access_tokens` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_records_prize` FOREIGN KEY (`prize_id`) REFERENCES `prizes` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_records_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中奖记录表';

-- --------------------------------------------------------

-- 表7: remote_controls - 远程控制表
CREATE TABLE IF NOT EXISTS `remote_controls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL COMMENT '游戏ID',
  `token_id` int(11) DEFAULT NULL COMMENT '令牌ID(NULL=全局)',
  `prize_id` int(11) NOT NULL COMMENT '指定中奖奖品ID',
  `is_used` tinyint(1) DEFAULT 0 COMMENT '是否已使用',
  `used_at` datetime DEFAULT NULL COMMENT '使用时间',
  `customer_id` int(11) DEFAULT NULL COMMENT '使用者顾客ID',
  `created_by` int(11) NOT NULL COMMENT '创建管理员ID',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `game_id` (`game_id`),
  KEY `token_id` (`token_id`),
  KEY `prize_id` (`prize_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `fk_remote_game` FOREIGN KEY (`game_id`) REFERENCES `game_templates` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_remote_token` FOREIGN KEY (`token_id`) REFERENCES `access_tokens` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_remote_prize` FOREIGN KEY (`prize_id`) REFERENCES `prizes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_remote_admin` FOREIGN KEY (`created_by`) REFERENCES `admins` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='远程控制表';

-- --------------------------------------------------------

-- 表8: system_logs - 系统日志表
CREATE TABLE IF NOT EXISTS `system_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_id` int(11) DEFAULT NULL COMMENT '操作管理员ID',
  `action` varchar(50) NOT NULL COMMENT '操作类型',
  `resource_type` varchar(50) DEFAULT NULL COMMENT '资源类型',
  `resource_id` int(11) DEFAULT NULL COMMENT '资源ID',
  `description` text DEFAULT NULL COMMENT '操作描述',
  `ip_address` varchar(45) DEFAULT NULL COMMENT 'IP地址',
  `user_agent` text DEFAULT NULL COMMENT '浏览器UA',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `admin_id` (`admin_id`),
  KEY `action` (`action`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统日志表';

-- --------------------------------------------------------

-- 表9: rooms - 房间表
CREATE TABLE IF NOT EXISTS `rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '房间名称',
  `is_active` tinyint(1) DEFAULT 1 COMMENT '启用状态',
  `sort_order` int(11) DEFAULT 0 COMMENT '排序',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='房间表';

-- --------------------------------------------------------

-- 表10: servers - 服务员表
CREATE TABLE IF NOT EXISTS `servers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '服务员姓名',
  `is_active` tinyint(1) DEFAULT 1 COMMENT '启用状态',
  `sort_order` int(11) DEFAULT 0 COMMENT '排序',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务员表';

-- --------------------------------------------------------

-- 表11: system_config_kv - 系统配置表
CREATE TABLE IF NOT EXISTS `system_config_kv` (
  `config_key` varchar(100) NOT NULL COMMENT '配置键',
  `config_value` text DEFAULT NULL COMMENT '配置值',
  `description` varchar(255) DEFAULT NULL COMMENT '配置说明',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';

-- 插入默认UI配置
INSERT INTO `system_config_kv` (`config_key`, `config_value`, `description`) VALUES
('wheel_border_effect', 'none', '转盘边框效果'),
('wheel_border_rotation', 'none', '边框旋转方向'),
('wheel_border_color', '#ff6b6b', '边框颜色'),
('pointer_glow', '0', '指针发光效果'),
('particle_effect', '0', '粒子效果'),
('entrance_animation', '0', '入场动画'),
('win_effect', '0', '中奖特效'),
('trail_effect', '0', '轨迹特效'),
('sound_enabled', '1', '音效开关'),
('grid_size', '3x3', '九宫格尺寸'),
('mobile_spin_cooldown', '3', '手机端冷却时间(秒)');

-- --------------------------------------------------------

-- 表12: display_queue - 显示队列表
CREATE TABLE IF NOT EXISTS `display_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `win_record_id` int(11) NOT NULL COMMENT '中奖记录ID',
  `token_id` int(11) NOT NULL COMMENT '令牌ID',
  `prize_id` int(11) DEFAULT NULL COMMENT '奖品ID',
  `is_won` tinyint(1) DEFAULT 0 COMMENT '是否中奖',
  `customer_name` varchar(100) DEFAULT NULL COMMENT '顾客姓名',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `consumed_at` datetime DEFAULT NULL COMMENT '消费时间',
  PRIMARY KEY (`id`),
  KEY `token_id` (`token_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='显示队列表';

-- --------------------------------------------------------

-- 表13: poll_throttle - 轮询限流表
CREATE TABLE IF NOT EXISTS `poll_throttle` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `token_id` int(11) NOT NULL COMMENT '令牌ID',
  `ip` varchar(45) NOT NULL COMMENT 'IP地址',
  `last_poll_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后轮询时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `token_ip` (`token_id`, `ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='轮询限流表';

-- --------------------------------------------------------

COMMIT;