34 lines
1.1 KiB
SQL
34 lines
1.1 KiB
SQL
-- +goose Up
|
|
|
|
ALTER TABLE admin_pickups
|
|
ADD COLUMN account_source VARCHAR(32) NOT NULL DEFAULT 'internal' COMMENT '账号来源快照: internal站内上传/external_platform_managed外部上传平台代管' AFTER shop_name,
|
|
ADD COLUMN source_channel VARCHAR(32) NOT NULL DEFAULT '' COMMENT '外部上传来源渠道快照' AFTER account_source,
|
|
ADD KEY idx_admin_pickups_account_source (account_source, created_at);
|
|
|
|
UPDATE admin_pickups AS p
|
|
LEFT JOIN (
|
|
SELECT uploads.listing_id, uploads.source_channel
|
|
FROM listing_uploads AS uploads
|
|
INNER JOIN (
|
|
SELECT listing_id, MAX(id) AS upload_id
|
|
FROM listing_uploads
|
|
WHERE listing_id IS NOT NULL
|
|
GROUP BY listing_id
|
|
) AS latest ON latest.upload_id = uploads.id
|
|
) AS lu ON lu.listing_id = p.listing_id
|
|
SET p.account_source = CASE
|
|
WHEN lu.listing_id IS NULL THEN 'internal'
|
|
ELSE 'external_platform_managed'
|
|
END,
|
|
p.source_channel = CASE
|
|
WHEN lu.listing_id IS NULL THEN ''
|
|
ELSE COALESCE(lu.source_channel, '')
|
|
END;
|
|
|
|
-- +goose Down
|
|
|
|
ALTER TABLE admin_pickups
|
|
DROP KEY idx_admin_pickups_account_source,
|
|
DROP COLUMN source_channel,
|
|
DROP COLUMN account_source;
|