40 lines
2.4 KiB
SQL
40 lines
2.4 KiB
SQL
-- 回滚: 恢复梯度返佣(TierCommission)相关的表和字段
|
|
-- 注意: 此回滚仅恢复表结构,不恢复已删除的数据
|
|
|
|
-- 1. 重新创建 tb_shop_series_commission_tier 表
|
|
CREATE TABLE IF NOT EXISTS tb_shop_series_commission_tier (
|
|
id SERIAL PRIMARY KEY,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at TIMESTAMP WITH TIME ZONE,
|
|
creator INT NOT NULL DEFAULT 0,
|
|
updater INT NOT NULL DEFAULT 0,
|
|
allocation_id INT NOT NULL,
|
|
tier_type VARCHAR(20) NOT NULL,
|
|
period_type VARCHAR(20) NOT NULL,
|
|
period_start_date TIMESTAMP WITH TIME ZONE,
|
|
period_end_date TIMESTAMP WITH TIME ZONE,
|
|
threshold_value BIGINT NOT NULL,
|
|
commission_mode VARCHAR(20) NOT NULL DEFAULT 'percent',
|
|
commission_value BIGINT NOT NULL
|
|
);
|
|
|
|
COMMENT ON COLUMN tb_shop_series_commission_tier.tier_type IS '梯度类型 sales_count-销量 sales_amount-销售额';
|
|
COMMENT ON COLUMN tb_shop_series_commission_tier.period_type IS '周期类型 monthly-月度 quarterly-季度 yearly-年度 custom-自定义';
|
|
COMMENT ON COLUMN tb_shop_series_commission_tier.period_start_date IS '自定义周期开始日期';
|
|
COMMENT ON COLUMN tb_shop_series_commission_tier.period_end_date IS '自定义周期结束日期';
|
|
COMMENT ON COLUMN tb_shop_series_commission_tier.threshold_value IS '阈值（销量或金额分）';
|
|
COMMENT ON COLUMN tb_shop_series_commission_tier.commission_mode IS '达标后返佣模式 fixed-固定金额 percent-百分比';
|
|
COMMENT ON COLUMN tb_shop_series_commission_tier.commission_value IS '达标后返佣值（分或千分比）';
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_tier_allocation_id ON tb_shop_series_commission_tier(allocation_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tier_deleted_at ON tb_shop_series_commission_tier(deleted_at);
|
|
|
|
-- 2. 恢复 tb_shop_series_allocation 表的 enable_tier_commission 字段
|
|
ALTER TABLE tb_shop_series_allocation ADD COLUMN IF NOT EXISTS enable_tier_commission BOOLEAN NOT NULL DEFAULT FALSE;
|
|
COMMENT ON COLUMN tb_shop_series_allocation.enable_tier_commission IS '是否启用梯度返佣';
|
|
|
|
-- 3. 恢复 tb_shop_series_allocation_config 表的 enable_tier_commission 字段
|
|
ALTER TABLE tb_shop_series_allocation_config ADD COLUMN IF NOT EXISTS enable_tier_commission BOOLEAN NOT NULL;
|
|
COMMENT ON COLUMN tb_shop_series_allocation_config.enable_tier_commission IS '是否启用梯度返佣(配置快照)';
|