-- 迁移: 一次性佣金功能
-- 变更ID: add-one-time-commission
-- 说明:
--   1. 为 tb_shop_series_allocation 添加一次性佣金配置字段
--   2. 创建 tb_shop_series_one_time_commission_tier 梯度配置表
--   3. 简化 tb_commission_record 表结构（删除冻结字段，新增来源字段）

-- ========================================
-- 1. tb_shop_series_allocation 添加一次性佣金配置字段
-- ========================================

ALTER TABLE tb_shop_series_allocation
    ADD COLUMN IF NOT EXISTS enable_one_time_commission BOOLEAN NOT NULL DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS one_time_commission_type VARCHAR(20),
    ADD COLUMN IF NOT EXISTS one_time_commission_trigger VARCHAR(30),
    ADD COLUMN IF NOT EXISTS one_time_commission_threshold BIGINT DEFAULT 0,
    ADD COLUMN IF NOT EXISTS one_time_commission_mode VARCHAR(20),
    ADD COLUMN IF NOT EXISTS one_time_commission_value BIGINT DEFAULT 0;

-- 添加字段注释
COMMENT ON COLUMN tb_shop_series_allocation.enable_one_time_commission IS '是否启用一次性佣金';
COMMENT ON COLUMN tb_shop_series_allocation.one_time_commission_type IS '一次性佣金类型 fixed-固定 tiered-梯度';
COMMENT ON COLUMN tb_shop_series_allocation.one_time_commission_trigger IS '触发条件 single_recharge-单次充值 accumulated_recharge-累计充值';
COMMENT ON COLUMN tb_shop_series_allocation.one_time_commission_threshold IS '最低阈值(分)';
COMMENT ON COLUMN tb_shop_series_allocation.one_time_commission_mode IS '返佣模式 fixed-固定金额 percent-百分比';
COMMENT ON COLUMN tb_shop_series_allocation.one_time_commission_value IS '佣金金额(分)或比例(千分比)';

-- ========================================
-- 2. 创建 tb_shop_series_one_time_commission_tier 梯度配置表
-- ========================================

CREATE TABLE IF NOT EXISTS tb_shop_series_one_time_commission_tier (
    id BIGSERIAL PRIMARY KEY,
    allocation_id BIGINT NOT NULL,
    tier_type VARCHAR(20) NOT NULL,
    threshold_value BIGINT NOT NULL,
    commission_mode VARCHAR(20) NOT NULL DEFAULT 'fixed',
    commission_value BIGINT NOT NULL,
    status INT NOT NULL DEFAULT 1,
    creator BIGINT NOT NULL,
    updater BIGINT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE
);

-- 添加索引
CREATE INDEX IF NOT EXISTS idx_one_time_tier_allocation_id ON tb_shop_series_one_time_commission_tier(allocation_id);
CREATE INDEX IF NOT EXISTS idx_one_time_tier_tier_type ON tb_shop_series_one_time_commission_tier(tier_type);
CREATE INDEX IF NOT EXISTS idx_one_time_tier_threshold ON tb_shop_series_one_time_commission_tier(threshold_value);

-- 添加表注释
COMMENT ON TABLE tb_shop_series_one_time_commission_tier IS '一次性佣金梯度配置表，基于销售业绩的一次性佣金档位';
COMMENT ON COLUMN tb_shop_series_one_time_commission_tier.allocation_id IS '系列分配ID';
COMMENT ON COLUMN tb_shop_series_one_time_commission_tier.tier_type IS '梯度类型 sales_count-销量 sales_amount-销售额';
COMMENT ON COLUMN tb_shop_series_one_time_commission_tier.threshold_value IS '梯度阈值(销量或销售额分)';
COMMENT ON COLUMN tb_shop_series_one_time_commission_tier.commission_mode IS '返佣模式 fixed-固定金额 percent-百分比';
COMMENT ON COLUMN tb_shop_series_one_time_commission_tier.commission_value IS '返佣值(分或千分比)';
COMMENT ON COLUMN tb_shop_series_one_time_commission_tier.status IS '状态 1-启用 2-停用';
COMMENT ON COLUMN tb_shop_series_one_time_commission_tier.creator IS '创建人ID';
COMMENT ON COLUMN tb_shop_series_one_time_commission_tier.updater IS '更新人ID';

-- ========================================
-- 3. 修改 tb_commission_record 表结构
-- ========================================

-- 3.1 删除旧字段
ALTER TABLE tb_commission_record
    DROP COLUMN IF EXISTS agent_id,
    DROP COLUMN IF EXISTS rule_id,
    DROP COLUMN IF EXISTS commission_type,
    DROP COLUMN IF EXISTS unfrozen_at;

-- 3.2 添加新字段
ALTER TABLE tb_commission_record
    ADD COLUMN IF NOT EXISTS commission_source VARCHAR(20) NOT NULL DEFAULT 'cost_diff',
    ADD COLUMN IF NOT EXISTS iot_card_id BIGINT,
    ADD COLUMN IF NOT EXISTS device_id BIGINT,
    ADD COLUMN IF NOT EXISTS remark VARCHAR(500);

-- 3.3 添加索引
CREATE INDEX IF NOT EXISTS idx_commission_record_commission_source ON tb_commission_record(commission_source);
CREATE INDEX IF NOT EXISTS idx_commission_record_iot_card_id ON tb_commission_record(iot_card_id);
CREATE INDEX IF NOT EXISTS idx_commission_record_device_id ON tb_commission_record(device_id);

-- 3.4 更新字段注释
COMMENT ON COLUMN tb_commission_record.shop_id IS '店铺ID（佣金归属）';
COMMENT ON COLUMN tb_commission_record.order_id IS '订单ID';
COMMENT ON COLUMN tb_commission_record.commission_source IS '佣金来源 cost_diff-成本价差 one_time-一次性佣金 tier_bonus-梯度奖励';
COMMENT ON COLUMN tb_commission_record.iot_card_id IS '关联卡ID（可空）';
COMMENT ON COLUMN tb_commission_record.device_id IS '关联设备ID（可空）';
COMMENT ON COLUMN tb_commission_record.amount IS '佣金金额(分)';
COMMENT ON COLUMN tb_commission_record.balance_after IS '入账后钱包余额（分）';
COMMENT ON COLUMN tb_commission_record.status IS '状态 1-已入账 2-已失效';
COMMENT ON COLUMN tb_commission_record.released_at IS '入账时间';
COMMENT ON COLUMN tb_commission_record.remark IS '备注';