-- ============================================
-- SPA管理系统 - 提现系统SQL
-- ============================================
-- 版本: v1.0
-- 创建时间: 2025-12-30
-- 数据库: MySQL 8.0+
-- 字符集: utf8mb4
-- ============================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================
-- 提现系统相关表
-- ============================================

-- 1. 提现申请表
DROP TABLE IF EXISTS `spa_withdrawal_apply`;
CREATE TABLE `spa_withdrawal_apply` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,

  `withdrawal_no` varchar(50) NOT NULL COMMENT '提现单号',
  `user_type` tinyint NOT NULL COMMENT '用户类型 1客户 2技师 3代理',
  `user_id` bigint unsigned NOT NULL COMMENT '用户ID',
  `user_name` varchar(100) DEFAULT NULL COMMENT '用户姓名',

  `withdrawal_amount` decimal(10,2) NOT NULL COMMENT '提现金额',
  `service_fee` decimal(10,2) DEFAULT 0.00 COMMENT '手续费',
  `actual_amount` decimal(10,2) NOT NULL COMMENT '实际到账金额',

  `withdrawal_type` tinyint NOT NULL COMMENT '提现方式 1银行卡 2支付宝 3微信',
  `account_name` varchar(100) DEFAULT NULL COMMENT '账户名',
  `account_number` varchar(100) DEFAULT NULL COMMENT '账号',
  `bank_name` varchar(100) DEFAULT NULL COMMENT '开户银行',
  `bank_branch` varchar(200) DEFAULT NULL COMMENT '开户支行',

  `status` tinyint DEFAULT 1 COMMENT '状态 1待审核 2审核通过 3审核拒绝 4处理中 5已完成 6已失败',
  `audit_user_id` bigint unsigned DEFAULT NULL COMMENT '审核人ID',
  `audit_time` datetime DEFAULT NULL COMMENT '审核时间',
  `audit_remark` varchar(500) DEFAULT NULL COMMENT '审核备注',

  `transfer_time` datetime DEFAULT NULL COMMENT '转账时间',
  `transfer_voucher` varchar(500) DEFAULT NULL COMMENT '转账凭证',
  `transfer_remark` varchar(500) DEFAULT NULL COMMENT '转账备注',

  `fail_reason` varchar(500) DEFAULT NULL COMMENT '失败原因',
  `remark` text COMMENT '备注',

  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_withdrawal_no` (`withdrawal_no`),
  KEY `idx_user` (`user_type`, `user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='提现申请表';

-- 2. 提现配置表
DROP TABLE IF EXISTS `spa_withdrawal_config`;
CREATE TABLE `spa_withdrawal_config` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,

  `user_type` tinyint NOT NULL COMMENT '用户类型 1客户 2技师 3代理',
  
  `min_amount` decimal(10,2) DEFAULT 100.00 COMMENT '最低提现金额',
  `max_amount` decimal(10,2) DEFAULT 50000.00 COMMENT '最高提现金额',
  `service_fee_rate` decimal(5,2) DEFAULT 1.00 COMMENT '手续费比例%',
  `min_service_fee` decimal(10,2) DEFAULT 1.00 COMMENT '最低手续费',

  `daily_limit` int DEFAULT 1 COMMENT '每日提现次数限制',
  `monthly_limit` int DEFAULT 10 COMMENT '每月提现次数限制',

  `need_real_name` tinyint DEFAULT 1 COMMENT '是否需要实名认证 0否 1是',
  `need_bind_account` tinyint DEFAULT 1 COMMENT '是否需要绑定账户 0否 1是',

  `audit_required` tinyint DEFAULT 1 COMMENT '是否需要审核 0否 1是',
  `auto_transfer` tinyint DEFAULT 0 COMMENT '是否自动转账 0否 1是',

  `work_time_start` time DEFAULT '09:00:00' COMMENT '工作时间开始',
  `work_time_end` time DEFAULT '21:00:00' COMMENT '工作时间结束',

  `status` tinyint DEFAULT 1 COMMENT '状态 1启用 2停用',

  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_type` (`user_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='提现配置表';

-- 3. 提现账户表
DROP TABLE IF EXISTS `spa_withdrawal_account`;
CREATE TABLE `spa_withdrawal_account` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,

  `user_type` tinyint NOT NULL COMMENT '用户类型 1客户 2技师 3代理',
  `user_id` bigint unsigned NOT NULL COMMENT '用户ID',

  `account_type` tinyint NOT NULL COMMENT '账户类型 1银行卡 2支付宝 3微信',
  `account_name` varchar(100) NOT NULL COMMENT '账户名',
  `account_number` varchar(100) NOT NULL COMMENT '账号',
  `bank_name` varchar(100) DEFAULT NULL COMMENT '开户银行',
  `bank_branch` varchar(200) DEFAULT NULL COMMENT '开户支行',

  `is_default` tinyint DEFAULT 0 COMMENT '是否默认 0否 1是',
  `is_verified` tinyint DEFAULT 0 COMMENT '是否已验证 0否 1是',
  `verify_time` datetime DEFAULT NULL COMMENT '验证时间',

  `status` tinyint DEFAULT 1 COMMENT '状态 1正常 2冻结',

  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_type`, `user_id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='提现账户表';

-- 4. 提现统计表
DROP TABLE IF EXISTS `spa_withdrawal_statistics`;
CREATE TABLE `spa_withdrawal_statistics` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,

  `user_type` tinyint NOT NULL COMMENT '用户类型 1客户 2技师 3代理',
  `user_id` bigint unsigned NOT NULL COMMENT '用户ID',
  `stat_date` date NOT NULL COMMENT '统计日期',

  `today_count` int DEFAULT 0 COMMENT '今日提现次数',
  `today_amount` decimal(12,2) DEFAULT 0.00 COMMENT '今日提现金额',

  `month_count` int DEFAULT 0 COMMENT '本月提现次数',
  `month_amount` decimal(12,2) DEFAULT 0.00 COMMENT '本月提现金额',

  `total_count` int DEFAULT 0 COMMENT '累计提现次数',
  `total_amount` decimal(12,2) DEFAULT 0.00 COMMENT '累计提现金额',
  `total_fee` decimal(12,2) DEFAULT 0.00 COMMENT '累计手续费',

  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_date` (`user_type`, `user_id`, `stat_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='提现统计表';

-- ============================================
-- 初始化提现配置
-- ============================================

INSERT INTO `spa_withdrawal_config` (`user_type`, `min_amount`, `max_amount`, `service_fee_rate`, `min_service_fee`, `daily_limit`, `monthly_limit`, `status`) VALUES
(1, 100.00, 50000.00, 1.00, 1.00, 1, 10, 1),
(2, 100.00, 50000.00, 1.00, 1.00, 1, 10, 1),
(3, 100.00, 50000.00, 1.00, 1.00, 1, 10, 1);

SET FOREIGN_KEY_CHECKS = 1;