-- NM Studio 数据库建表 SQL
-- 使用前请先在 cPanel 创建数据库和用户


-- ----------------------------
-- 1. 用户表 (users)
-- ----------------------------
CREATE TABLE `users` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uid` varchar(10) NOT NULL COMMENT '用户编号 NM10042/NM80013',
  `telegram_id` bigint(20) NOT NULL COMMENT 'Telegram ID',
  `telegram_username` varchar(100) DEFAULT NULL COMMENT 'Telegram 用户名',
  `name` varchar(50) DEFAULT NULL COMMENT '昵称',
  `gender` enum('male','female') DEFAULT NULL COMMENT '性别',
  `age` tinyint(3) UNSIGNED DEFAULT NULL COMMENT '年龄',
  
  -- 身份开关
  `is_client` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否允许下单',
  `is_player` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否允许接单',
  
  -- 资产字段
  `nmcoin_balance` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'NM Coin 余额',
  `payable_amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '应付金额',
  
  -- 陪玩专属
  `commission_rate` decimal(3,2) DEFAULT 0.80 COMMENT '分成比例',
  `self_card_file_id` varchar(255) DEFAULT NULL COMMENT '自卡 Telegram file_id',
  `self_card_type` enum('image','video') DEFAULT NULL COMMENT '自卡类型',
  `is_available` tinyint(1) DEFAULT 1 COMMENT '是否接单中',
  `photos` json DEFAULT NULL COMMENT '照片列表',
  `bio` text DEFAULT NULL COMMENT '个人简介',
  
  -- 下单相关
  `referrer_uid` varchar(10) DEFAULT NULL COMMENT '直属陪玩 UID',
  `note` text DEFAULT NULL COMMENT 'CS 备注',
  
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_uid` (`uid`),
  UNIQUE KEY `idx_telegram_id` (`telegram_id`),
  KEY `idx_player` (`is_player`, `is_available`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- ----------------------------
-- 3. 服务类型表 (services)
-- ----------------------------
CREATE TABLE `services` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '服务名称',
  `price_per_hour` decimal(10,2) DEFAULT 30.00 COMMENT '参考单价',
  `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
  `sort_order` int(11) DEFAULT 0 COMMENT '排序',
  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务类型表';

-- 插入默认服务类型
INSERT INTO `services` (`name`, `price_per_hour`, `sort_order`) VALUES
('文字聊天', 20.00, 1),
('语音条', 25.00, 2),
('连麦', 30.00, 3),
('王者荣耀', 30.00, 4),
('和平精英', 30.00, 5);

-- ----------------------------
-- 4. 订单表 (orders)
-- ----------------------------
CREATE TABLE `orders` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_no` varchar(20) NOT NULL COMMENT '订单编号 NM251225-0001',
  
  `client_uid` varchar(10) DEFAULT NULL COMMENT '下单人 UID',
  `player_uid` varchar(10) DEFAULT NULL COMMENT '陪玩 UID',
  
  `service_id` int(11) UNSIGNED DEFAULT NULL COMMENT '服务类型',
  `duration` int(11) DEFAULT NULL COMMENT '时长(分钟)',
  `amount` decimal(10,2) DEFAULT NULL COMMENT '订单金额',
  
  `status` enum('pending','matched','selected','pending_payment','confirmed','waiting_settlement','pending_settlement','settled','cancelled','disputed') NOT NULL DEFAULT 'pending' COMMENT '订单状态',
  `payment_type` enum('nmcoin','cash') DEFAULT NULL COMMENT '支付方式',
  
  `is_proxy_order` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否代派单',
  `proxy_by` int(11) UNSIGNED DEFAULT NULL COMMENT '代派客服ID',
  
  `telegram_group_id` bigint(20) DEFAULT NULL COMMENT '来源群 ID',
  `telegram_message_id` int(11) DEFAULT NULL COMMENT '派单消息 ID',
  `dispatch_content` text DEFAULT NULL COMMENT '派单原文',
  
  `cs_id` int(11) UNSIGNED DEFAULT NULL COMMENT '记录订单的客服 ID',
  `note` text DEFAULT NULL COMMENT '订单备注',
  
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `paid_at` timestamp NULL DEFAULT NULL COMMENT '支付时间',
  `completed_at` timestamp NULL DEFAULT NULL COMMENT '完成时间',
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_order_no` (`order_no`),
  KEY `idx_client` (`client_uid`, `created_at`),
  KEY `idx_player` (`player_uid`, `created_at`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- ----------------------------
-- 5. 派单匹配记录 (dispatch_records)
-- ----------------------------
CREATE TABLE `dispatch_records` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` int(11) UNSIGNED NOT NULL COMMENT '订单 ID',
  `player_uid` varchar(10) NOT NULL COMMENT '陪玩 UID',
  `telegram_group_id` bigint(20) DEFAULT NULL COMMENT '发自卡的群',
  `self_card_message_id` int(11) DEFAULT NULL COMMENT '自卡消息 ID',
  `status` enum('invited','accepted','selected','withdrawn','expired') NOT NULL DEFAULT 'invited' COMMENT '状态',
  `invited_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '邀请时间',
  `responded_at` timestamp NULL DEFAULT NULL COMMENT '响应时间',
  
  PRIMARY KEY (`id`),
  KEY `idx_order` (`order_id`),
  KEY `idx_player` (`player_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='派单匹配记录';

-- ----------------------------
-- 6. 报备单 (reports)
-- ----------------------------
CREATE TABLE `reports` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_no` varchar(20) NOT NULL COMMENT '报备编号 RP251225-0001',
  
  `report_type` enum('topup','instant_pay') NOT NULL COMMENT '类型',
  `user_uid` varchar(10) NOT NULL COMMENT '充值给谁',
  `order_id` int(11) UNSIGNED DEFAULT NULL COMMENT '关联订单ID',
  `amount` decimal(10,2) NOT NULL COMMENT '金额',
  
  `proof_image` varchar(255) DEFAULT NULL COMMENT '转账截图',
  
  `created_by` int(11) UNSIGNED NOT NULL COMMENT '提交客服ID',
  `auditor_id` int(11) UNSIGNED DEFAULT NULL COMMENT '审核人ID',
  `status` enum('pending','approved','rejected','disputed') NOT NULL DEFAULT 'pending' COMMENT '状态',
  `audit_note` text DEFAULT NULL COMMENT '审核备注',
  
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `audited_at` timestamp NULL DEFAULT NULL COMMENT '审核时间',
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_report_no` (`report_no`),
  KEY `idx_status` (`status`, `created_at`),
  KEY `idx_user` (`user_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报备单';

-- ----------------------------
-- 7. 资产变动记录 (transactions)
-- ----------------------------
CREATE TABLE `transactions` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_uid` varchar(10) NOT NULL COMMENT '用户 UID',
  `user_type` enum('user','admin') NOT NULL DEFAULT 'user' COMMENT '用户类型',
  
  `asset_type` enum('nmcoin','payable') NOT NULL COMMENT '资产类型',
  `type` enum('topup','payment','earning','commission','refund','withdraw') NOT NULL COMMENT '变动类型',
  `amount` decimal(10,2) NOT NULL COMMENT '变动金额',
  `balance_after` decimal(10,2) NOT NULL COMMENT '变动后余额',
  
  `related_order_id` int(11) UNSIGNED DEFAULT NULL COMMENT '关联订单',
  `related_report_id` int(11) UNSIGNED DEFAULT NULL COMMENT '关联报备单',
  `note` varchar(255) DEFAULT NULL COMMENT '备注',
  
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_user_asset_time` (`user_uid`, `asset_type`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资产变动记录';

-- ----------------------------
-- 8. 用户备注历史 (user_notes)
-- ----------------------------
CREATE TABLE `user_notes` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_uid` varchar(10) NOT NULL COMMENT '用户 UID',
  `admin_id` int(11) UNSIGNED NOT NULL COMMENT '管理员 ID',
  `content` text NOT NULL COMMENT '备注内容',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户备注历史';

-- ----------------------------
-- 10. 亲密度表 (intimacy)
-- ----------------------------
CREATE TABLE `intimacy` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `boss_uid` varchar(10) NOT NULL COMMENT '老板 UID',
  `player_uid` varchar(10) NOT NULL COMMENT '陪玩 UID',
  `score` int(11) NOT NULL DEFAULT 0 COMMENT '亲密度分数',
  `order_count` int(11) NOT NULL DEFAULT 0 COMMENT '订单数',
  `total_amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '总消费',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_boss_player` (`boss_uid`, `player_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='亲密度';

-- ----------------------------
-- 11. Telegram 群组表 (telegram_groups)
-- ----------------------------
CREATE TABLE `telegram_groups` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id` bigint(20) NOT NULL COMMENT 'Telegram 群 ID',
  `group_name` varchar(100) DEFAULT NULL COMMENT '群名称',
  `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Telegram 群组';

-- ----------------------------
-- 12. 每日计数器 (daily_counters)
-- ----------------------------
CREATE TABLE `daily_counters` (
  `type` enum('order','report') NOT NULL COMMENT '计数器类型',
  `date_ymd` char(6) NOT NULL COMMENT '日期 YYMMDD',
  `seq` int(11) NOT NULL DEFAULT 0 COMMENT '当日序号',
  
  PRIMARY KEY (`type`, `date_ymd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='每日计数器';

