/*
财务结算接口手动迁移 SQL。

执行范围：
1. 新增统一结算主表 finance_settlement。
2. 新增统一结算明细表 finance_settlement_detail。
3. 如果表已经由接口自动创建，补齐 sale_mode 字段。

注意：
- 不修改旧 provider_settle_data 流程。
- 分销商结算记账依赖既有账户：
  account(accounting_orgid=上级机构, orgid=分销商机构, subject=分销商存放资金)
  account(accounting_orgid=上级机构, orgid=上级机构, subject=资金账号)
*/

CREATE TABLE IF NOT EXISTS `finance_settlement` (
  `id` varchar(32) NOT NULL COMMENT 'id',
  `settlement_no` varchar(64) DEFAULT NULL COMMENT '结算单号',
  `accounting_orgid` varchar(32) DEFAULT NULL COMMENT '账本机构id',
  `counterparty_type` varchar(16) DEFAULT NULL COMMENT '对手方类型 supplier/reseller',
  `counterparty_orgid` varchar(32) DEFAULT NULL COMMENT '对手方机构id',
  `counterparty_name` varchar(255) DEFAULT NULL COMMENT '对手方名称',
  `period_type` varchar(16) DEFAULT NULL COMMENT '账期类型 day/month',
  `period_start` date DEFAULT NULL COMMENT '账期开始日期',
  `period_end` date DEFAULT NULL COMMENT '账期结束日期',
  `sales_amount` double(18,8) DEFAULT 0.00000000 COMMENT '销售金额',
  `settlement_amount` double(18,8) DEFAULT 0.00000000 COMMENT '结算金额',
  `platform_income_amount` double(18,8) DEFAULT 0.00000000 COMMENT '平台收入',
  `bill_count` int(11) DEFAULT 0 COMMENT '账单数量',
  `status` varchar(16) DEFAULT 'draft' COMMENT 'draft/approving/approved/rejected/settled/failed/cancelled',
  `approval_id` varchar(64) DEFAULT NULL COMMENT '审批id',
  `approval_status` varchar(16) DEFAULT NULL COMMENT '审批状态',
  `failure_reason` varchar(1000) DEFAULT NULL COMMENT '失败原因',
  `settled_at` datetime DEFAULT NULL COMMENT '结算记账时间',
  `created_by` varchar(32) DEFAULT NULL COMMENT '创建人',
  `del_flg` varchar(1) DEFAULT '0' COMMENT '删除标志',
  `create_at` datetime DEFAULT current_timestamp() COMMENT '创建时间',
  `update_at` datetime DEFAULT current_timestamp() COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `finance_settlement_un` (`accounting_orgid`, `counterparty_type`, `counterparty_orgid`, `period_start`, `period_end`, `del_flg`),
  KEY `finance_settlement_idx1` (`accounting_orgid`, `status`),
  KEY `finance_settlement_idx2` (`counterparty_type`, `counterparty_orgid`),
  KEY `finance_settlement_idx3` (`period_start`, `period_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='财务结算单主表';

CREATE TABLE IF NOT EXISTS `finance_settlement_detail` (
  `id` varchar(32) NOT NULL COMMENT 'id',
  `settlement_id` varchar(32) DEFAULT NULL COMMENT '结算单id',
  `bill_id` varchar(32) DEFAULT NULL COMMENT '账单id',
  `order_id` varchar(32) DEFAULT NULL COMMENT '订单id',
  `bill_date` date DEFAULT NULL COMMENT '账单日期',
  `customerid` varchar(32) DEFAULT NULL COMMENT '客户id',
  `providerid` varchar(32) DEFAULT NULL COMMENT '供应商机构id',
  `productid` varchar(32) DEFAULT NULL COMMENT '产品id',
  `business_op` varchar(64) DEFAULT NULL COMMENT '业务操作',
  `sale_mode` varchar(1) DEFAULT NULL COMMENT '销售模式 0折扣/1代付费/2底价',
  `sales_amount` double(18,8) DEFAULT 0.00000000 COMMENT '销售金额',
  `settlement_amount` double(18,8) DEFAULT 0.00000000 COMMENT '结算金额',
  `platform_income_amount` double(18,8) DEFAULT 0.00000000 COMMENT '平台收入',
  `amount_source` varchar(32) DEFAULT 'bill_detail' COMMENT '金额来源',
  `del_flg` varchar(1) DEFAULT '0' COMMENT '删除标志',
  `create_at` datetime DEFAULT current_timestamp() COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `finance_settlement_detail_idx1` (`settlement_id`),
  KEY `finance_settlement_detail_idx2` (`bill_id`),
  KEY `finance_settlement_detail_idx3` (`providerid`, `productid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='财务结算单明细快照';

ALTER TABLE `finance_settlement_detail`
  ADD COLUMN IF NOT EXISTS `sale_mode` varchar(1) DEFAULT NULL COMMENT '销售模式 0折扣/1代付费/2底价' AFTER `business_op`;

-- 执行分销商结算前，可用以下 SQL 检查目标账户是否存在：
-- SELECT a.*
-- FROM account a
-- JOIN subject s ON a.subjectid=s.id
-- WHERE a.accounting_orgid='<上级机构orgid>'
--   AND a.orgid='<分销商orgid>'
--   AND s.name='分销商存放资金'
--   AND a.del_flg='0'
--   AND s.del_flg='0';
--
-- SELECT a.*
-- FROM account a
-- JOIN subject s ON a.subjectid=s.id
-- WHERE a.accounting_orgid='<上级机构orgid>'
--   AND a.orgid='<上级机构orgid>'
--   AND s.name='资金账号'
--   AND a.del_flg='0'
--   AND s.del_flg='0';