76 lines
3.2 KiB
SQL
76 lines
3.2 KiB
SQL
CREATE TABLE IF NOT EXISTS tb_exchange_order (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ,
|
|
creator BIGINT NOT NULL DEFAULT 0,
|
|
updater BIGINT NOT NULL DEFAULT 0,
|
|
|
|
exchange_no VARCHAR(50) NOT NULL,
|
|
|
|
old_asset_type VARCHAR(20) NOT NULL,
|
|
old_asset_id BIGINT NOT NULL,
|
|
old_asset_identifier VARCHAR(100) NOT NULL,
|
|
|
|
new_asset_type VARCHAR(20) DEFAULT '',
|
|
new_asset_id BIGINT,
|
|
new_asset_identifier VARCHAR(100) DEFAULT '',
|
|
|
|
recipient_name VARCHAR(50) DEFAULT '',
|
|
recipient_phone VARCHAR(20) DEFAULT '',
|
|
recipient_address TEXT DEFAULT '',
|
|
|
|
express_company VARCHAR(100) DEFAULT '',
|
|
express_no VARCHAR(100) DEFAULT '',
|
|
|
|
migrate_data BOOLEAN NOT NULL DEFAULT FALSE,
|
|
migration_completed BOOLEAN NOT NULL DEFAULT FALSE,
|
|
migration_balance BIGINT NOT NULL DEFAULT 0,
|
|
|
|
exchange_reason VARCHAR(100) NOT NULL,
|
|
remark TEXT,
|
|
status INT NOT NULL DEFAULT 1,
|
|
|
|
shop_id BIGINT
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_exchange_order_no
|
|
ON tb_exchange_order (exchange_no)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_exchange_order_old_asset
|
|
ON tb_exchange_order (old_asset_type, old_asset_id)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_exchange_order_status
|
|
ON tb_exchange_order (status)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_exchange_order_shop_id
|
|
ON tb_exchange_order (shop_id)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_exchange_order_created_at
|
|
ON tb_exchange_order (created_at DESC);
|
|
|
|
COMMENT ON TABLE tb_exchange_order IS '换货订单表';
|
|
COMMENT ON COLUMN tb_exchange_order.exchange_no IS '换货单号(EXC+日期+随机数)';
|
|
COMMENT ON COLUMN tb_exchange_order.old_asset_type IS '旧资产类型(iot_card/device)';
|
|
COMMENT ON COLUMN tb_exchange_order.old_asset_id IS '旧资产ID';
|
|
COMMENT ON COLUMN tb_exchange_order.old_asset_identifier IS '旧资产标识符(ICCID/虚拟号)';
|
|
COMMENT ON COLUMN tb_exchange_order.new_asset_type IS '新资产类型(iot_card/device)';
|
|
COMMENT ON COLUMN tb_exchange_order.new_asset_id IS '新资产ID';
|
|
COMMENT ON COLUMN tb_exchange_order.new_asset_identifier IS '新资产标识符(ICCID/虚拟号)';
|
|
COMMENT ON COLUMN tb_exchange_order.recipient_name IS '收件人姓名';
|
|
COMMENT ON COLUMN tb_exchange_order.recipient_phone IS '收件人电话';
|
|
COMMENT ON COLUMN tb_exchange_order.recipient_address IS '收货地址';
|
|
COMMENT ON COLUMN tb_exchange_order.express_company IS '快递公司';
|
|
COMMENT ON COLUMN tb_exchange_order.express_no IS '快递单号';
|
|
COMMENT ON COLUMN tb_exchange_order.migrate_data IS '是否执行全量迁移';
|
|
COMMENT ON COLUMN tb_exchange_order.migration_completed IS '迁移是否已完成';
|
|
COMMENT ON COLUMN tb_exchange_order.migration_balance IS '迁移转移金额(分)';
|
|
COMMENT ON COLUMN tb_exchange_order.exchange_reason IS '换货原因';
|
|
COMMENT ON COLUMN tb_exchange_order.remark IS '备注';
|
|
COMMENT ON COLUMN tb_exchange_order.status IS '换货状态 1-待填写信息 2-待发货 3-已发货待确认 4-已完成 5-已取消';
|
|
COMMENT ON COLUMN tb_exchange_order.shop_id IS '所属店铺ID';
|