58 lines
3.2 KiB
SQL
58 lines
3.2 KiB
SQL
-- +goose Up
|
|
|
|
ALTER TABLE rental_listings
|
|
ADD COLUMN handoff_mode VARCHAR(16) NOT NULL DEFAULT 'owner' COMMENT '交接模式: owner号主交接/platform平台代管' AFTER review_status,
|
|
ADD COLUMN settlement_mode VARCHAR(32) NOT NULL DEFAULT 'owner_wallet' COMMENT '结算模式: owner_wallet号主钱包/platform_managed平台线下结算' AFTER handoff_mode,
|
|
ADD COLUMN managed_admin_id BIGINT UNSIGNED NULL COMMENT '平台代管负责客服ID' AFTER settlement_mode,
|
|
ADD KEY idx_rental_listings_handoff_mode (handoff_mode, status, review_status),
|
|
ADD KEY idx_rental_listings_managed_admin (managed_admin_id);
|
|
|
|
ALTER TABLE rental_orders
|
|
ADD COLUMN handoff_mode VARCHAR(16) NOT NULL DEFAULT 'owner' COMMENT '交接模式快照: owner号主交接/platform平台代管' AFTER handoff_status,
|
|
ADD COLUMN settlement_mode VARCHAR(32) NOT NULL DEFAULT 'owner_wallet' COMMENT '结算模式快照: owner_wallet号主钱包/platform_managed平台线下结算' AFTER handoff_mode,
|
|
ADD COLUMN managed_admin_id BIGINT UNSIGNED NULL COMMENT '平台代管负责客服ID快照' AFTER settlement_mode,
|
|
ADD COLUMN offline_settlement_status VARCHAR(16) NOT NULL DEFAULT 'none' COMMENT '线下结算状态: none无/pending待线下结算/settled已线下结算' AFTER settlement_status,
|
|
ADD COLUMN offline_settlement_amount_cent BIGINT NOT NULL DEFAULT 0 COMMENT '待线下结算给卖家的金额(分)' AFTER offline_settlement_status,
|
|
ADD COLUMN offline_settlement_remark VARCHAR(255) NOT NULL DEFAULT '' COMMENT '线下结算备注' AFTER offline_settlement_amount_cent,
|
|
ADD COLUMN offline_settled_by BIGINT UNSIGNED NULL COMMENT '确认线下结算的管理员ID' AFTER offline_settlement_remark,
|
|
ADD COLUMN offline_settled_at DATETIME NULL COMMENT '线下结算确认时间' AFTER offline_settled_by,
|
|
ADD KEY idx_rental_orders_handoff_mode (handoff_mode, status, handoff_status),
|
|
ADD KEY idx_rental_orders_offline_settlement (offline_settlement_status, settlement_status);
|
|
|
|
UPDATE rental_listings AS l
|
|
JOIN listing_uploads AS lu ON lu.listing_id = l.id
|
|
JOIN users AS u ON u.id = l.owner_id AND u.phone LIKE 'admin:%'
|
|
SET l.handoff_mode = 'platform',
|
|
l.settlement_mode = 'platform_managed',
|
|
l.managed_admin_id = lu.matched_admin_id
|
|
WHERE l.handoff_mode = 'owner';
|
|
|
|
UPDATE rental_orders AS o
|
|
JOIN listing_uploads AS lu ON lu.listing_id = o.listing_id
|
|
JOIN users AS u ON u.id = o.owner_id AND u.phone LIKE 'admin:%'
|
|
SET o.handoff_mode = 'platform',
|
|
o.settlement_mode = 'platform_managed',
|
|
o.managed_admin_id = lu.matched_admin_id
|
|
WHERE o.handoff_mode = 'owner';
|
|
|
|
-- +goose Down
|
|
|
|
ALTER TABLE rental_orders
|
|
DROP KEY idx_rental_orders_offline_settlement,
|
|
DROP KEY idx_rental_orders_handoff_mode,
|
|
DROP COLUMN offline_settled_at,
|
|
DROP COLUMN offline_settled_by,
|
|
DROP COLUMN offline_settlement_remark,
|
|
DROP COLUMN offline_settlement_amount_cent,
|
|
DROP COLUMN offline_settlement_status,
|
|
DROP COLUMN managed_admin_id,
|
|
DROP COLUMN settlement_mode,
|
|
DROP COLUMN handoff_mode;
|
|
|
|
ALTER TABLE rental_listings
|
|
DROP KEY idx_rental_listings_managed_admin,
|
|
DROP KEY idx_rental_listings_handoff_mode,
|
|
DROP COLUMN managed_admin_id,
|
|
DROP COLUMN settlement_mode,
|
|
DROP COLUMN handoff_mode;
|