-- ============================================================
-- 晓风聚合支付系统 - MySQL数据库初始化脚本
-- 创建时间: 2026-04-07
-- 数据库名: xiaofeng_pay
-- ============================================================

-- 创建数据库
CREATE DATABASE IF NOT EXISTS `xiaofeng_pay` 
  DEFAULT CHARACTER SET utf8mb4 
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE `xiaofeng_pay`;

-- ============================================================
-- 1. 商户表
-- ============================================================
CREATE TABLE `merchants` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商户ID',
  `pid` VARCHAR(32) NOT NULL COMMENT '商户PID(唯一)',
  `username` VARCHAR(64) NOT NULL COMMENT '登录用户名',
  `password` VARCHAR(128) NOT NULL COMMENT '登录密码(bcrypt)',
  `token_version` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '会话版本，改密后递增使旧JWT失效',
  `key` VARCHAR(64) NOT NULL COMMENT '商户Key(用于签名)',
  `realname` VARCHAR(64) DEFAULT NULL COMMENT '真实姓名',
  `email` VARCHAR(128) DEFAULT NULL COMMENT '邮箱',
  `mobile` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
  `qq` VARCHAR(32) DEFAULT NULL COMMENT '联系QQ',
  `pay_tip` VARCHAR(200) DEFAULT NULL COMMENT '支付提示语',
  `pay_popup_tip` VARCHAR(300) DEFAULT NULL COMMENT '支付弹窗提醒内容',
  `pay_contact` VARCHAR(100) DEFAULT NULL COMMENT '支付页联系方式',
  `balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '账户余额',
  `frozen_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '冻结余额',
  `rate` DECIMAL(5,4) NOT NULL DEFAULT 0.0300 COMMENT '费率(0.03=3%)',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态:0禁用1正常2待审核',
  `disable_reason` VARCHAR(200) DEFAULT NULL COMMENT '禁用原因',
  `offline_email_notify` TINYINT NOT NULL DEFAULT 0 COMMENT '通道离线是否邮件通知:0否1是',
  `order_email_notify` TINYINT NOT NULL DEFAULT 0 COMMENT '订单成功是否邮件通知:0否1是',
  `allow_offline_collect` TINYINT NOT NULL DEFAULT 0 COMMENT '通道离线是否继续收款:0否1是',
  `alipay_no_ck_pay_mode` VARCHAR(16) NOT NULL DEFAULT 'qrcode' COMMENT '支付宝免CK收银展示:qrcode二维码 transfer转账唤起',
  `alipay_transfer_account` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '转账模式对方账号(手机/邮箱等)',
  `alipay_cookie_qr_display_type` VARCHAR(16) NOT NULL DEFAULT 'qrcode' COMMENT '支付宝COOKIE收银展示:qrcode二维码 transfer免输入转账模式',
  `order_expire_minutes` TINYINT NOT NULL DEFAULT 5 COMMENT '支付订单过期分钟数:仅允许3/5/7/10',
  `cashier_template` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT '前台收银台模板ID(default/minimal等)',
  `last_login_ip` VARCHAR(45) DEFAULT NULL COMMENT '最后登录IP',
  `last_login_time` DATETIME DEFAULT NULL COMMENT '最后登录时间',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_pid` (`pid`),
  UNIQUE KEY `uk_username` (`username`),
  UNIQUE KEY `uk_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商户表';

-- ============================================================
-- 2. 商户通道配置表
-- ============================================================
CREATE TABLE `merchant_channels` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `merchant_id` INT UNSIGNED NOT NULL COMMENT '商户ID',
  `channel_code` VARCHAR(32) NOT NULL COMMENT '通道代码(由系统源码定义)',
  `is_enabled` TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用',
  `custom_rate` DECIMAL(5,4) DEFAULT NULL COMMENT '自定义费率(为空则用通道默认)',
  `config_data` JSON DEFAULT NULL COMMENT '通道配置(JSON)',
  `qrcode_url` VARCHAR(512) DEFAULT NULL COMMENT '收款码URL(可选)',
  `remark` VARCHAR(128) DEFAULT NULL COMMENT '通道备注',
  `last_heartbeat_at` DATETIME DEFAULT NULL COMMENT '最近心跳时间',
  `offline_notified_at` DATETIME DEFAULT NULL COMMENT '最近离线通知时间',
  `offline_notify_try_at` DATETIME DEFAULT NULL COMMENT '最近离线通知尝试时间',
  `last_offline_at` DATETIME DEFAULT NULL COMMENT '最近一次离线时间（恢复在线时记录）',
  `is_online` TINYINT NOT NULL DEFAULT 0 COMMENT '是否在线(0离线1在线)',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_merchant_id` (`merchant_id`),
  KEY `idx_merchant_id_channel_code` (`merchant_id`, `channel_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商户通道配置表';

-- ============================================================
-- 3. 订单表
-- ============================================================
CREATE TABLE `orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `trade_no` VARCHAR(64) NOT NULL COMMENT '平台订单号',
  `out_trade_no` VARCHAR(64) NOT NULL COMMENT '商户订单号',
  `merchant_id` INT UNSIGNED NOT NULL COMMENT '商户ID',
  `channel_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商户通道ID',
  `buyer_merchant_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '购买方商户ID(套餐购买时使用)',
  `package_plan_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '套餐ID(套餐购买时使用)',
  `biz_type` VARCHAR(32) NOT NULL DEFAULT 'pay' COMMENT '业务类型:pay/package_purchase',
  `channel_code` VARCHAR(32) NOT NULL COMMENT '支付通道代码',
  `subject` VARCHAR(256) NOT NULL DEFAULT '' COMMENT '支付商品名称',
  `channel_pay_url` VARCHAR(1024) DEFAULT NULL COMMENT '通道侧支付链接(如支付宝网页支付)',
  `channel_pay_qrcode` VARCHAR(1024) DEFAULT NULL COMMENT '通道侧二维码内容(如支付宝当面付)',
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '订单实付金额(去重加价后)',
  `original_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '商户请求金额(异步通知与查询money用，去重加价前)',
  `usdt_cny_rate` DECIMAL(16,8) NOT NULL DEFAULT 0 COMMENT '下单时1USDT=?CNY',
  `chain_tx_hash` VARCHAR(160) NOT NULL DEFAULT '' COMMENT 'USDT链上确认交易哈希',
  `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态:0待支付1已支付2已超时',
  `notify_url` VARCHAR(512) NOT NULL COMMENT '异步通知地址',
  `return_url` VARCHAR(512) DEFAULT NULL COMMENT '同步返回地址',
  `app_callback_token` VARCHAR(64) NOT NULL COMMENT '挂机APP回调令牌',
  `app_paid_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'APP回传支付金额',
  `app_paid_at` DATETIME DEFAULT NULL COMMENT 'APP回传支付时间',
  `expire_at` DATETIME NOT NULL COMMENT '订单超时时间',
  `notify_status` TINYINT NOT NULL DEFAULT 0 COMMENT '通知状态:0待通知1成功2失败',
  `notify_last_at` DATETIME DEFAULT NULL COMMENT '最后通知时间',
  `notify_resp` VARCHAR(256) DEFAULT NULL COMMENT '最后通知响应摘要',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_trade_no` (`trade_no`),
  UNIQUE KEY `uk_out_trade_no_merchant` (`out_trade_no`, `merchant_id`),
  KEY `idx_merchant_id` (`merchant_id`),
  KEY `idx_channel_id` (`channel_id`),
  KEY `idx_buyer_merchant_id` (`buyer_merchant_id`),
  KEY `idx_status` (`status`),
  KEY `idx_expire_at` (`expire_at`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';

-- ============================================================
-- 4. USDT链上回执表
-- ============================================================
CREATE TABLE `usdt_tx_receipts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tx_hash` VARCHAR(160) NOT NULL,
  `trade_no` VARCHAR(64) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_usdt_tx_hash` (`tx_hash`),
  KEY `idx_usdt_trade_no` (`trade_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='USDT链上交易回执';

-- ============================================================
-- 5. 管理员表
-- ============================================================
CREATE TABLE `admins` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '管理员ID',
  `username` VARCHAR(64) NOT NULL COMMENT '用户名',
  `password` VARCHAR(128) NOT NULL COMMENT '密码',
  `token_version` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '会话版本，改密后递增使旧JWT失效',
  `realname` VARCHAR(64) DEFAULT NULL COMMENT '真实姓名',
  `mobile` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
  `email` VARCHAR(128) DEFAULT NULL COMMENT '邮箱',
  `role` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '角色:admin超管/operation运营/customer客服',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态:0禁用1正常',
  `last_login_ip` VARCHAR(45) DEFAULT NULL COMMENT '最后登录IP',
  `last_login_time` DATETIME DEFAULT NULL COMMENT '最后登录时间',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='管理员表';

-- 插入超级管理员 (登录邮箱: admin@xiaofengpay.com, 密码: 123456)
INSERT INTO `admins` (`username`, `password`, `realname`, `email`, `role`) VALUES
('admin', '$2a$10$9/pIBdkGKq4d7Da5vCl4ku5pvzx2ZdaWqZs12HiJihb27wfvnKT8a', '系统管理员', 'admin@xiaofengpay.com', 'admin');

-- ============================================================
-- 6. 系统配置表
-- ============================================================
CREATE TABLE `system_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '配置ID',
  `setting_key` VARCHAR(64) NOT NULL COMMENT '配置键',
  `setting_value` TEXT DEFAULT NULL COMMENT '配置值',
  `setting_type` VARCHAR(16) NOT NULL DEFAULT 'string' COMMENT '类型:string/json/int/bool',
  `description` VARCHAR(256) DEFAULT NULL COMMENT '说明',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统配置表';

-- 初始化系统配置
INSERT INTO `system_settings` (`setting_key`, `setting_value`, `setting_type`, `description`) VALUES
('site_name', '晓风聚合支付系统', 'string', '站点名称'),
('site_desc', '安全稳定的聚合支付平台', 'string', '站点描述'),
('site_footer_icp', '', 'string', 'ICP备案号'),
('site_footer_gongan', '', 'string', '公安备案号'),
('site_contact', '', 'string', '联系方式'),
('site_team_name', '晓风团队', 'string', '首页团队名称'),
('site_team_info', '晓风团队专注支付系统研发与运维，持续打磨稳定、安全、可扩展的聚合支付能力。', 'string', '首页团队信息'),
('site_team_contact', '', 'string', '首页团队联系方式'),
('site_global_html', '', 'string', '全局HTML代码'),
('smtp_host', '', 'string', 'SMTP主机'),
('smtp_port', '', 'string', 'SMTP端口'),
('smtp_user', '', 'string', 'SMTP账号'),
('smtp_pass', '', 'string', 'SMTP密码'),
('smtp_from', '', 'string', 'SMTP发件人'),
('site_url', 'https://pay.example.com', 'string', '站点URL'),
('site_logo', '', 'string', '站点LOGO'),
('site_favicon', '', 'string', '站点ICO'),
('default_rate', '0.0300', 'string', '默认费率'),
('min_recharge', '10.00', 'string', '最低充值金额'),
('channel_offline_mail_interval_seconds', '1800', 'string', '通道离线邮件最小发送间隔（秒）'),
('min_withdraw', '10.00', 'string', '最低提现金额'),
('withdraw_fee', '1.00', 'string', '提现手续费'),
('auto_audit_merchant', '1', 'int', '自动审核商户:0关闭1开启'),
('callback_timeout', '300', 'int', '回调超时时间(秒)'),
('callback_retry_times', '5', 'int', '回调重试次数'),
('callback_retry_interval', '60', 'int', '回调重试间隔(秒)'),
('order_expire_minutes', '30', 'int', '订单过期时间(分钟)'),
('usdt_rate', '7.25', 'string', 'USDT兑换人民币汇率'),
('usdt_trc20_confirmations', '15', 'int', 'TRC20确认数'),
('usdt_erc20_confirmations', '15', 'int', 'ERC20确认数'),
('wechat_app_enabled', '1', 'int', '微信APP挂机是否启用'),
('qq_app_enabled', '1', 'int', 'QQ APP挂机是否启用'),
('alipay_app_enabled', '1', 'int', '支付宝APP挂机是否启用'),
('wechat_pc_enabled', '1', 'int', '微信PC挂机是否启用'),
('wechat_native_enabled', '1', 'int', '微信Native是否启用'),
('alipay_no_ck_enabled', '1', 'int', '支付宝免CK是否启用'),
('alipay_face_enabled', '1', 'int', '支付宝当面付是否启用'),
('alipay_web_enabled', '1', 'int', '支付宝网站支付是否启用'),
('alipay_cookie_enabled', '1', 'int', '支付宝COOKIE是否启用'),
('usdt_trc20_enabled', '1', 'int', 'USDT-TRC20是否启用'),
('usdt_erc20_enabled', '1', 'int', 'USDT-ERC20是否启用'),
('maintenance_mode', '0', 'int', '维护模式:0关闭1开启'),
('maintenance_msg', '系统维护中，请稍后再试', 'string', '维护提示信息'),
('pay_notify_base_url', '', 'string', '支付回调基础域名(为空则自动取当前请求域名)'),
('system_pay_pid', '', 'string', '系统收款商户PID'),
('package_pay_types', 'alipay,wxpay,qqpay,usdt', 'string', '套餐购买可选支付方式'),
('home_pay_types', 'alipay,wxpay,qqpay,usdt', 'string', '首页公开测试可选支付方式'),
('register_gift_enabled', '0', 'number', '新用户注册自动赠送套餐开关(0/1)'),
('register_gift_package_id', '0', 'number', '新用户注册自动赠送套餐ID'),
('register_email_verify_enabled', '1', 'int', '注册时邮箱验证码开关(0关闭1开启)'),
('monitor_download_android_url', 'https://cyjl.ljwx.site/xiaofeng-pay/监控程序/安卓.apk', 'string', '安卓微信APP自挂监控下载地址'),
('monitor_download_pc_url', 'https://cyjl.ljwx.site/xiaofeng-pay/监控程序/晓风聚合支付系统PC自挂.exe', 'string', '微信PC自挂监控下载地址'),
('alipay_no_ck_poll_debug', '0', 'string', '支付宝免CK轮询调试日志(0关1开)'),
('alipay_cookie_poll_debug', '0', 'string', '支付宝Cookie轮询调试日志(0关1开)'),
('usdt_cny_rate', '7.2', 'string', '1 USDT 折合 CNY（自动或人工）'),
('usdt_cny_rate_updated_at', '', 'string', '汇率最近更新时间 ISO8601'),
('usdt_fx_auto_enabled', '1', 'string', 'USDT/CNY自动刷新(0关1开)'),
('usdt_fx_max_change_pct', '15', 'string', '汇率环比变化超过该百分比则丢弃本次行情'),
('tron_api_key', '', 'string', 'TronGrid API Key(可选)'),
('etherscan_api_key', '', 'string', 'Etherscan API Key(ERC20轮询)'),
('usdt_chain_poll_seconds', '45', 'string', 'USDT链上轮询间隔秒'),
('erc20_confirm_blocks', '12', 'string', 'ERC20确认块数'),
('usdt_chain_poll_debug', '0', 'string', 'USDT调试日志：链上轮询+汇率刷新(0关1开)'),
('usdt_fx_source', 'coingecko', 'string', 'USDT/CNY自动刷新行情源：coingecko 或 exchangerate_usd_cny'),
('package_pay_mode', 'system', 'string', '套餐购买付款方式:system系统收款/external外部支付'),
('package_external_pay_url', '', 'string', '外部支付接口地址(如https://pay.example.com)'),
('package_external_pay_pid', '', 'string', '外部支付商户PID'),
('package_external_pay_key', '', 'string', '外部支付商户Key');

-- ============================================================
-- 7. 套餐表
-- ============================================================
CREATE TABLE `package_plans` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '套餐ID',
  `name` VARCHAR(64) NOT NULL COMMENT '套餐名称',
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '售卖价格',
  `pay_amount_limit` DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '支付额度',
  `channel_quota` INT NOT NULL DEFAULT 1 COMMENT '通道配额数量',
  `duration_days` INT NOT NULL DEFAULT 30 COMMENT '套餐天数',
  `supported_channels` VARCHAR(512) NOT NULL COMMENT '支持通道代码,逗号分隔',
  `is_enabled` TINYINT NOT NULL DEFAULT 1 COMMENT '状态:0禁用1启用',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='套餐定义表';

-- ============================================================
-- 8. 商户套餐状态表
-- ============================================================
CREATE TABLE `merchant_package_states` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `merchant_id` INT UNSIGNED NOT NULL COMMENT '商户ID',
  `package_plan_id` INT UNSIGNED NOT NULL COMMENT '当前套餐ID',
  `package_name` VARCHAR(64) NOT NULL COMMENT '当前套餐名称',
  `pay_amount_limit` DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '套餐总额度',
  `remaining_pay_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '剩余额度',
  `channel_quota` INT NOT NULL DEFAULT 1 COMMENT '通道配额',
  `supported_channels` VARCHAR(512) NOT NULL COMMENT '支持通道代码',
  `expire_at` DATETIME NOT NULL COMMENT '到期时间',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_merchant_id` (`merchant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商户套餐状态表';

-- ============================================================
-- 9. 外部套餐购买订单表
-- ============================================================
CREATE TABLE `external_package_orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `out_trade_no` VARCHAR(64) NOT NULL COMMENT '本系统订单号(发起支付时生成)',
  `trade_no` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '外部系统订单号(异步通知回填)',
  `buyer_merchant_id` INT UNSIGNED NOT NULL COMMENT '购买方商户ID',
  `package_plan_id` INT UNSIGNED NOT NULL COMMENT '套餐ID',
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '订单金额',
  `pay_type` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '支付方式:alipay/wxpay/qqpay/usdt',
  `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态:0待支付1已支付2失败3超时',
  `pay_url` VARCHAR(1024) NOT NULL DEFAULT '' COMMENT '外部支付跳转URL',
  `fail_reason` VARCHAR(256) NOT NULL DEFAULT '' COMMENT '失败原因',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `paid_at` DATETIME DEFAULT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_out_trade_no` (`out_trade_no`),
  KEY `idx_trade_no` (`trade_no`),
  KEY `idx_buyer_merchant_id` (`buyer_merchant_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='外部套餐购买订单表';

-- ============================================================
-- 10. 操作日志表
-- ============================================================
CREATE TABLE `operation_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `merchant_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '归属商户ID，管理员日志为0',
  `actor_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '操作人ID',
  `actor_role` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '操作人角色：admin/merchant',
  `actor_name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '操作人账号',
  `log_type` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '日志类型：admin_login/merchant_login/callback',
  `action` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '动作名称',
  `target_trade_no` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '关联订单号',
  `remark` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '备注',
  `ip` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '请求IP',
  `user_agent` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'User-Agent',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_merchant_id` (`merchant_id`),
  KEY `idx_log_type` (`log_type`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志表';

-- ============================================================
-- 10. 公告表
-- ============================================================
CREATE TABLE `announcements` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '公告ID',
  `title` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '标题',
  `content` TEXT NOT NULL COMMENT '内容',
  `ann_type` VARCHAR(32) NOT NULL DEFAULT 'normal' COMMENT '类型:site_popup/merchant_popup/normal',
  `is_enabled` TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用:0否1是',
  `sort` INT NOT NULL DEFAULT 0 COMMENT '排序(升序)',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ann_type_enabled` (`ann_type`, `is_enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='公告表';

-- ============================================================
-- 11. 数据库迁移记录表
-- ============================================================
CREATE TABLE `schema_migrations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `version` VARCHAR(128) NOT NULL,
  `applied_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_version` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='数据库迁移记录表';

-- 初始化套餐
INSERT INTO `package_plans` (`name`, `price`, `pay_amount_limit`, `channel_quota`, `duration_days`, `supported_channels`, `is_enabled`) VALUES
('基础版', 1.00, 100000.00, 2, 30, 'wechat_app,qq_app,alipay_app,alipay_no_ck,alipay_face', 1),
('进阶版', 2.00, 500000.00, 5, 30, 'wechat_app,qq_app,alipay_app,wechat_pc,alipay_no_ck,alipay_face,usdt_trc20', 1),
('旗舰版', 3.00, 2000000.00, 20, 30, 'wechat_app,qq_app,alipay_app,wechat_pc,wechat_native,alipay_no_ck,alipay_face,alipay_web,alipay_cookie,usdt_trc20,usdt_erc20', 1);

-- ============================================================
-- 初始化测试商户，密码123456
-- ============================================================
INSERT INTO `merchants` (`pid`, `username`, `password`, `key`, `realname`, `rate`, `status`) VALUES
('M10001', 'test01', '$2a$10$qtwFj1xLGX0UC9n83Yx1iOqtVlG6X5UJCMaxg/pv4EBZoWtj2W80a', 'test_key_abc123456', '测试商户', 0.0300, 1);

-- 给测试商户添加一个通道配置示例
INSERT INTO `merchant_channels` (`merchant_id`, `channel_code`, `is_enabled`, `remark`)
VALUES (1, 'wechat_app', 1, '测试通道');

-- 给测试商户绑定基础版套餐
INSERT INTO `merchant_package_states` (`merchant_id`, `package_plan_id`, `package_name`, `pay_amount_limit`, `remaining_pay_amount`, `channel_quota`, `supported_channels`, `expire_at`)
VALUES (1, 1, '基础版', 100000.00, 100000.00, 2, 'wechat_app,qq_app,alipay_app,alipay_no_ck', DATE_ADD(NOW(), INTERVAL 30 DAY));