-- =====================================================
-- 订单结算模块数据库Schema
-- 创建时间: 2026-04-04
-- 描述: 订单结算、结算周期、结算报表相关表
-- =====================================================

-- 订单结算表
CREATE TABLE IF NOT EXISTS `order_settlement` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `settlement_no` VARCHAR(64) NOT NULL COMMENT '结算单号' unique,
    `order_id` BIGINT UNSIGNED NOT NULL COMMENT '关联订单ID',
    `order_short_id` VARCHAR(32) NOT NULL COMMENT '订单短ID',
    `platform_order_sn` VARCHAR(64) NOT NULL COMMENT '平台订单号',
    `platform` VARCHAR(32) NOT NULL COMMENT '平台编码',
    `shop_id` BIGINT UNSIGNED NOT NULL COMMENT '店铺ID',
    `shop_name` VARCHAR(128) NOT NULL COMMENT '店铺名称',
    `order_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '订单金额（商品金额）',
    `discount_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '优惠金额',
    `freight` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '运费',
    `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '订单总金额',
    `platform_fee` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '平台服务费/佣金',
    `platform_commission_rate` DECIMAL(6,4) NOT NULL DEFAULT 0.0060 COMMENT '平台佣金率',
    `settlement_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '实际结算金额',
    `settlement_status` ENUM('pending','calculating','settled','frozen') NOT NULL DEFAULT 'pending' COMMENT '结算状态',
    `settlement_period_id` BIGINT UNSIGNED COMMENT '结算周期ID',
    `settlement_date` DATETIME COMMENT '结算日期',
    `payment_time` DATETIME COMMENT '支付时间',
    `delivery_time` DATETIME COMMENT '发货时间',
    `confirm_time` DATETIME COMMENT '确认收货时间',
    `confirm_settlement_time` DATETIME COMMENT '结算确认时间',
    `remark` TEXT COMMENT '备注',
    `tenant_id` BIGINT UNSIGNED COMMENT '租户ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at` DATETIME COMMENT '删除时间',
    PRIMARY KEY (`id`),
    INDEX `idx_settlement_no` (`settlement_no`),
    INDEX `idx_order_id` (`order_id`),
    INDEX `idx_platform_order_sn` (`platform_order_sn`),
    INDEX `idx_platform` (`platform`),
    INDEX `idx_shop_id` (`shop_id`),
    INDEX `idx_settlement_status` (`settlement_status`),
    INDEX `idx_settlement_period_id` (`settlement_period_id`),
    INDEX `idx_payment_time` (`payment_time`),
    INDEX `idx_tenant_id` (`tenant_id`),
    INDEX `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单结算表';

-- 结算周期表
CREATE TABLE IF NOT EXISTS `settlement_period` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `period_no` VARCHAR(64) NOT NULL COMMENT '结算周期编号' unique,
    `period_type` ENUM('weekly','monthly','daily') NOT NULL COMMENT '周期类型',
    `start_date` DATE NOT NULL COMMENT '周期开始日期',
    `end_date` DATE NOT NULL COMMENT '周期结束日期',
    `period_status` ENUM('open','closed','settled') NOT NULL DEFAULT 'open' COMMENT '周期状态',
    `order_count` INT NOT NULL DEFAULT 0 COMMENT '周期内订单数',
    `total_order_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '周期内订单总金额',
    `total_settlement_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '周期内结算金额',
    `total_platform_fee` DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '周期内平台佣金',
    `settlement_time` DATETIME COMMENT '结算完成时间',
    `settle_operator_id` BIGINT UNSIGNED COMMENT '结算确认人ID',
    `settle_operator_name` VARCHAR(64) COMMENT '结算确认人名称',
    `remark` TEXT COMMENT '备注',
    `tenant_id` BIGINT UNSIGNED COMMENT '租户ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at` DATETIME COMMENT '删除时间',
    PRIMARY KEY (`id`),
    INDEX `idx_period_no` (`period_no`),
    INDEX `idx_period_type` (`period_type`),
    INDEX `idx_period_status` (`period_status`),
    INDEX `idx_start_date` (`start_date`),
    INDEX `idx_end_date` (`end_date`),
    INDEX `idx_tenant_id` (`tenant_id`),
    INDEX `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='结算周期表';

-- 结算报表表
CREATE TABLE IF NOT EXISTS `settlement_report` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `report_no` VARCHAR(64) NOT NULL COMMENT '报表编号' unique,
    `report_type` ENUM('shop','platform','period') NOT NULL COMMENT '报表类型',
    `shop_id` BIGINT UNSIGNED COMMENT '关联店铺ID（店铺维度时）',
    `shop_name` VARCHAR(128) COMMENT '店铺名称',
    `platform` VARCHAR(32) COMMENT '平台编码',
    `settlement_period_id` BIGINT UNSIGNED COMMENT '结算周期ID',
    `report_date` DATE NOT NULL COMMENT '报表日期',
    `start_date` DATE NOT NULL COMMENT '起始日期',
    `end_date` DATE NOT NULL COMMENT '结束日期',
    `order_count` INT NOT NULL DEFAULT 0 COMMENT '订单数量',
    `total_order_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '订单总金额',
    `total_discount_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '优惠总金额',
    `total_freight` DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '运费总金额',
    `platform_fee` DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '平台佣金',
    `settlement_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '实际结算金额',
    `settled_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '已结算金额',
    `pending_settlement_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '待结算金额',
    `frozen_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT '已冻结金额',
    `report_status` ENUM('generating','generated','confirmed') NOT NULL DEFAULT 'generating' COMMENT '报表状态',
    `generated_at` DATETIME COMMENT '生成时间',
    `confirmed_at` DATETIME COMMENT '确认时间',
    `confirmed_by` BIGINT UNSIGNED COMMENT '确认人ID',
    `remark` TEXT COMMENT '备注',
    `tenant_id` BIGINT UNSIGNED COMMENT '租户ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at` DATETIME COMMENT '删除时间',
    PRIMARY KEY (`id`),
    INDEX `idx_report_no` (`report_no`),
    INDEX `idx_report_type` (`report_type`),
    INDEX `idx_shop_id` (`shop_id`),
    INDEX `idx_platform` (`platform`),
    INDEX `idx_settlement_period_id` (`settlement_period_id`),
    INDEX `idx_report_date` (`report_date`),
    INDEX `idx_report_status` (`report_status`),
    INDEX `idx_tenant_id` (`tenant_id`),
    INDEX `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='结算报表表';

-- =====================================================
-- 测试数据
-- =====================================================

INSERT INTO `order_settlement` (
    settlement_no, order_id, order_short_id, platform_order_sn, platform, shop_id, shop_name,
    order_amount, discount_amount, freight, total_amount, platform_fee, platform_commission_rate,
    settlement_amount, settlement_status, payment_time, delivery_time
) VALUES
('STL202604041000000001', 1001, 'O20260401001', 'PG23041200012345', 'pinduoduo', 1, '拼多多旗舰店',
 199.00, 0.00, 10.00, 209.00, 1.25, 0.0060, 207.75, 'pending', '2026-04-01 10:00:00', '2026-04-01 15:30:00'),
('STL202604041000000002', 1002, 'O20260401002', 'TB23041200023456', 'taobao', 2, '淘宝优选店',
 399.00, 20.00, 15.00, 394.00, 2.36, 0.0060, 391.64, 'pending', '2026-04-01 11:00:00', '2026-04-01 16:00:00'),
('STL202604041000000003', 1003, 'O20260401003', 'JD23041200034567', 'jd', 3, '京东自营店',
 599.00, 50.00, 0.00, 549.00, 3.29, 0.0060, 545.71, 'settled', '2026-04-01 12:00:00', '2026-04-01 17:00:00');

INSERT INTO `settlement_period` (
    period_no, period_type, start_date, end_date, period_status, order_count,
    total_order_amount, total_settlement_amount, total_platform_fee
) VALUES
('WKL20260401001', 'weekly', '2026-03-31', '2026-04-06', 'open', 0, 0.00, 0.00, 0.00),
('MTH20260401001', 'monthly', '2026-04-01', '2026-04-30', 'open', 0, 0.00, 0.00, 0.00);
