-- ============================================
-- SPA管理系统 - 财务报表统计SQL
-- ============================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. 财务账户表
DROP TABLE IF EXISTS `spa_financial_account`;
CREATE TABLE `spa_financial_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,
  `account_name` varchar(100) NOT NULL COMMENT '账户名称',
  `account_type` tinyint NOT NULL COMMENT '账户类型 1现金 2银行 3支付宝 4微信',
  `account_no` varchar(100) DEFAULT NULL COMMENT '账号',
  `balance` decimal(12,2) DEFAULT 0.00 COMMENT '账户余额',
  `status` tinyint DEFAULT 1 COMMENT '状态 1正常 2冻结',
  PRIMARY KEY (`id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='财务账户表';

-- 2. 资金流水表
DROP TABLE IF EXISTS `spa_financial_flow`;
CREATE TABLE `spa_financial_flow` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `flow_no` varchar(50) NOT NULL COMMENT '流水号',
  `account_id` bigint unsigned NOT NULL COMMENT '账户ID',
  `flow_type` tinyint NOT NULL COMMENT '流水类型 1收入 2支出',
  `amount` decimal(10,2) NOT NULL COMMENT '金额',
  `before_balance` decimal(12,2) NOT NULL COMMENT '变动前余额',
  `after_balance` decimal(12,2) NOT NULL COMMENT '变动后余额',
  `business_type` tinyint NOT NULL COMMENT '业务类型 1订单收入 2提现支出 3工资支出 4采购支出 5其他',
  `related_id` bigint unsigned DEFAULT NULL COMMENT '关联业务ID',
  `related_no` varchar(50) DEFAULT NULL COMMENT '关联业务单号',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_flow_no` (`flow_no`),
  KEY `idx_account_id` (`account_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资金流水表';

-- 3. 对账记录表
DROP TABLE IF EXISTS `spa_reconciliation`;
CREATE TABLE `spa_reconciliation` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `reconciliation_date` date NOT NULL COMMENT '对账日期',
  `account_id` bigint unsigned NOT NULL COMMENT '账户ID',
  `system_balance` decimal(12,2) NOT NULL COMMENT '系统余额',
  `actual_balance` decimal(12,2) NOT NULL COMMENT '实际余额',
  `difference` decimal(12,2) DEFAULT 0.00 COMMENT '差额',
  `status` tinyint DEFAULT 1 COMMENT '状态 1待对账 2已对账 3有差异',
  `operator_id` bigint unsigned DEFAULT NULL COMMENT '操作人ID',
  `remark` text COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_account_id` (`account_id`),
  KEY `idx_reconciliation_date` (`reconciliation_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='对账记录表';

-- 4. 发票管理表
DROP TABLE IF EXISTS `spa_invoice`;
CREATE TABLE `spa_invoice` (
  `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,
  `invoice_no` varchar(50) NOT NULL COMMENT '发票号码',
  `order_id` bigint unsigned NOT NULL COMMENT '订单ID',
  `customer_id` bigint unsigned NOT NULL COMMENT '客户ID',
  `invoice_type` tinyint NOT NULL COMMENT '发票类型 1普通 2专用',
  `invoice_title` varchar(200) NOT NULL COMMENT '发票抬头',
  `tax_no` varchar(50) DEFAULT NULL COMMENT '税号',
  `amount` decimal(10,2) NOT NULL COMMENT '开票金额',
  `status` tinyint DEFAULT 1 COMMENT '状态 1待开票 2已开票 3已作废',
  `issue_time` datetime DEFAULT NULL COMMENT '开票时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_invoice_no` (`invoice_no`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='发票管理表';

-- 5. 日统计表
DROP TABLE IF EXISTS `spa_daily_statistics`;
CREATE TABLE `spa_daily_statistics` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `stat_date` date NOT NULL COMMENT '统计日期',
  `store_id` bigint unsigned DEFAULT NULL COMMENT '店铺ID 0表示全部',
  `order_count` int DEFAULT 0 COMMENT '订单数',
  `order_amount` decimal(12,2) DEFAULT 0.00 COMMENT '订单金额',
  `paid_count` int DEFAULT 0 COMMENT '支付订单数',
  `paid_amount` decimal(12,2) DEFAULT 0.00 COMMENT '支付金额',
  `refund_count` int DEFAULT 0 COMMENT '退款订单数',
  `refund_amount` decimal(12,2) DEFAULT 0.00 COMMENT '退款金额',
  `new_customer_count` int DEFAULT 0 COMMENT '新增客户数',
  `active_customer_count` int DEFAULT 0 COMMENT '活跃客户数',
  `technician_count` int DEFAULT 0 COMMENT '在职技师数',
  `service_count` int DEFAULT 0 COMMENT '服务次数',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_store_date` (`store_id`, `stat_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日统计表';

-- 6. 月统计表
DROP TABLE IF EXISTS `spa_monthly_statistics`;
CREATE TABLE `spa_monthly_statistics` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `stat_month` varchar(7) NOT NULL COMMENT '统计月份 YYYY-MM',
  `store_id` bigint unsigned DEFAULT NULL COMMENT '店铺ID 0表示全部',
  `order_count` int DEFAULT 0 COMMENT '订单数',
  `order_amount` decimal(12,2) DEFAULT 0.00 COMMENT '订单金额',
  `paid_count` int DEFAULT 0 COMMENT '支付订单数',
  `paid_amount` decimal(12,2) DEFAULT 0.00 COMMENT '支付金额',
  `refund_count` int DEFAULT 0 COMMENT '退款订单数',
  `refund_amount` decimal(12,2) DEFAULT 0.00 COMMENT '退款金额',
  `new_customer_count` int DEFAULT 0 COMMENT '新增客户数',
  `total_customer_count` int DEFAULT 0 COMMENT '累计客户数',
  `commission_amount` decimal(12,2) DEFAULT 0.00 COMMENT '分佣总额',
  `withdrawal_amount` decimal(12,2) DEFAULT 0.00 COMMENT '提现总额',
  `profit_amount` decimal(12,2) DEFAULT 0.00 COMMENT '利润',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_store_month` (`store_id`, `stat_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='月统计表';

-- 7. 技师绩效表
DROP TABLE IF EXISTS `spa_technician_performance`;
CREATE TABLE `spa_technician_performance` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `technician_id` bigint unsigned NOT NULL COMMENT '技师ID',
  `stat_month` varchar(7) NOT NULL COMMENT '统计月份 YYYY-MM',
  `service_count` int DEFAULT 0 COMMENT '服务次数',
  `service_amount` decimal(12,2) DEFAULT 0.00 COMMENT '服务金额',
  `commission_amount` decimal(12,2) DEFAULT 0.00 COMMENT '提成金额',
  `avg_rating` decimal(3,2) DEFAULT 0.00 COMMENT '平均评分',
  `complaint_count` int DEFAULT 0 COMMENT '投诉次数',
  `attendance_days` int DEFAULT 0 COMMENT '出勤天数',
  `rank` int DEFAULT 0 COMMENT '排名',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_tech_month` (`technician_id`, `stat_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='技师绩效表';

-- 8. 店铺绩效表
DROP TABLE IF EXISTS `spa_store_performance`;
CREATE TABLE `spa_store_performance` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `store_id` bigint unsigned NOT NULL COMMENT '店铺ID',
  `stat_month` varchar(7) NOT NULL COMMENT '统计月份 YYYY-MM',
  `order_count` int DEFAULT 0 COMMENT '订单数',
  `order_amount` decimal(12,2) DEFAULT 0.00 COMMENT '订单金额',
  `customer_count` int DEFAULT 0 COMMENT '客户数',
  `technician_count` int DEFAULT 0 COMMENT '技师数',
  `avg_rating` decimal(3,2) DEFAULT 0.00 COMMENT '平均评分',
  `profit_amount` decimal(12,2) DEFAULT 0.00 COMMENT '利润',
  `rank` int DEFAULT 0 COMMENT '排名',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_store_month` (`store_id`, `stat_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='店铺绩效表';

SET FOREIGN_KEY_CHECKS = 1;