e2a93e22cf
- tasks 新增 recovery_enabled/recovery_due_at/recovery_instructions/recovery_example_images，回收挂自己名下（先发布后交截图），不再置 complete - 存量 screenshot_collect 迁移并入父任务后删除（mysql/0020 含 DELETE，生产执行前务必备份） - 新 action task_enable_recovery：ghost 面板一键开通回收，截止默认=发布截止 - 企微催办双阶段（待发布/截图待提交）；超期自动释放仅发布阶段 - admin 删 groupTaskMasters 聚合改单卡双面板；portal 详情页回收截图区块+批次回收进度 - 测试 309 全绿 + Playwright E2E 20/20
126 lines
6.0 KiB
SQL
126 lines
6.0 KiB
SQL
-- 任务模型物理合并：回收成为分发任务的第二阶段（单任务双阶段）
|
||
-- 上线前请先备份生产库；本迁移有 DELETE，回滚需依赖备份
|
||
|
||
ALTER TABLE tasks ADD COLUMN recovery_enabled INTEGER NOT NULL DEFAULT 1 AFTER related_task_id;
|
||
-- statement-breakpoint
|
||
ALTER TABLE tasks ADD COLUMN recovery_due_at TEXT NOT NULL DEFAULT ('') AFTER recovery_enabled;
|
||
-- statement-breakpoint
|
||
ALTER TABLE tasks ADD COLUMN recovery_instructions TEXT NOT NULL DEFAULT ('') AFTER recovery_due_at;
|
||
-- statement-breakpoint
|
||
ALTER TABLE tasks ADD COLUMN recovery_example_images TEXT NOT NULL DEFAULT ('[]') AFTER recovery_instructions;
|
||
-- statement-breakpoint
|
||
-- 老分发任务默认关回收；下方为有伴生回收的父任务重新置 1
|
||
UPDATE tasks SET recovery_enabled = 0 WHERE task_type = 'content_publish';
|
||
-- statement-breakpoint
|
||
-- 配对回收：回收字段并入父任务（说明/示例图取回收任务首条内容，截止取回收任务截止）
|
||
UPDATE tasks parent
|
||
JOIN tasks child ON child.related_task_id = parent.id
|
||
JOIN (
|
||
SELECT task_id, body, image_assets
|
||
FROM contents
|
||
WHERE id IN (SELECT MIN(id) FROM contents GROUP BY task_id)
|
||
) first_content ON first_content.task_id = child.id
|
||
SET parent.recovery_due_at = child.due_at,
|
||
parent.recovery_instructions = first_content.body,
|
||
parent.recovery_example_images = first_content.image_assets,
|
||
parent.recovery_enabled = 1
|
||
WHERE child.task_type = 'screenshot_collect';
|
||
-- statement-breakpoint
|
||
-- 配对回收：父任务无内容兜底（回收任务没有 contents 行时仍开通回收）
|
||
UPDATE tasks parent
|
||
JOIN tasks child ON child.related_task_id = parent.id
|
||
SET parent.recovery_due_at = child.due_at,
|
||
parent.recovery_instructions = '请提交发布后的笔记截图',
|
||
parent.recovery_example_images = '[]',
|
||
parent.recovery_enabled = 1
|
||
WHERE child.task_type = 'screenshot_collect'
|
||
AND parent.recovery_enabled = 0;
|
||
-- statement-breakpoint
|
||
-- 配对回收：截图提交记录搬到父任务同 content+partner 的分发行（父行已有提交时保留父行）
|
||
UPDATE distributions target
|
||
JOIN contents legacy_content ON legacy_content.id = target.content_id
|
||
JOIN distributions legacy ON legacy.content_id = legacy_content.related_content_id
|
||
AND legacy.partner_id = target.partner_id
|
||
JOIN tasks legacy_task ON legacy_task.id = legacy.task_id
|
||
SET target.result_screenshot_key = legacy.result_screenshot_key,
|
||
target.result_submitted_at = legacy.result_submitted_at
|
||
WHERE legacy_task.task_type = 'screenshot_collect'
|
||
AND legacy_task.related_task_id = target.task_id
|
||
AND target.result_submitted_at IS NULL;
|
||
-- statement-breakpoint
|
||
-- 配对回收：父任务里没有对应分发行时，保留为一条只挂回收字段的分发记录
|
||
INSERT INTO distributions (id, task_id, content_id, partner_id, status, result_screenshot_key, result_submitted_at, claimed_at)
|
||
SELECT
|
||
CONCAT('dist-m', LEFT(REPLACE(UUID(), '-', ''), 20)),
|
||
legacy_task.related_task_id,
|
||
legacy_content.related_content_id,
|
||
legacy.partner_id,
|
||
'claimed',
|
||
legacy.result_screenshot_key,
|
||
legacy.result_submitted_at,
|
||
legacy.claimed_at
|
||
FROM distributions legacy
|
||
JOIN contents legacy_content ON legacy_content.id = legacy.content_id
|
||
JOIN tasks legacy_task ON legacy_task.id = legacy.task_id
|
||
WHERE legacy_task.task_type = 'screenshot_collect'
|
||
AND legacy_task.related_task_id != ''
|
||
AND legacy_content.related_content_id != ''
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM distributions parent_dist
|
||
WHERE parent_dist.task_id = legacy_task.related_task_id
|
||
AND parent_dist.content_id = legacy_content.related_content_id
|
||
AND parent_dist.partner_id = legacy.partner_id
|
||
);
|
||
-- statement-breakpoint
|
||
-- 配对回收：清理伴生回收任务的分发/领取/内容/任务行
|
||
DELETE FROM distributions
|
||
WHERE task_id IN (SELECT id FROM (SELECT id FROM tasks WHERE task_type = 'screenshot_collect' AND related_task_id != '') AS doomed);
|
||
-- statement-breakpoint
|
||
DELETE FROM claims
|
||
WHERE task_id IN (SELECT id FROM (SELECT id FROM tasks WHERE task_type = 'screenshot_collect' AND related_task_id != '') AS doomed);
|
||
-- statement-breakpoint
|
||
DELETE FROM contents
|
||
WHERE task_id IN (SELECT id FROM (SELECT id FROM tasks WHERE task_type = 'screenshot_collect' AND related_task_id != '') AS doomed);
|
||
-- statement-breakpoint
|
||
DELETE FROM tasks WHERE task_type = 'screenshot_collect' AND related_task_id != '';
|
||
-- statement-breakpoint
|
||
-- 独立回收（无关联分发）：无领取记录的直接删除
|
||
DELETE FROM contents
|
||
WHERE task_id IN (
|
||
SELECT id FROM (
|
||
SELECT t.id FROM tasks t
|
||
WHERE t.task_type = 'screenshot_collect'
|
||
AND (t.related_task_id IS NULL OR t.related_task_id = '')
|
||
AND NOT EXISTS (SELECT 1 FROM distributions d WHERE d.task_id = t.id)
|
||
) AS doomed
|
||
);
|
||
-- statement-breakpoint
|
||
DELETE FROM tasks
|
||
WHERE task_type = 'screenshot_collect'
|
||
AND (related_task_id IS NULL OR related_task_id = '')
|
||
AND id NOT IN (SELECT DISTINCT task_id FROM distributions);
|
||
-- statement-breakpoint
|
||
-- 独立回收（有领取记录）：原地转正为单模型任务（发布阶段字段留空，仅存历史数据）
|
||
UPDATE tasks standalone
|
||
JOIN (
|
||
SELECT task_id, body, image_assets
|
||
FROM contents
|
||
WHERE id IN (SELECT MIN(id) FROM contents GROUP BY task_id)
|
||
) first_content ON first_content.task_id = standalone.id
|
||
SET standalone.task_type = 'content_publish',
|
||
standalone.recovery_due_at = standalone.due_at,
|
||
standalone.recovery_instructions = first_content.body,
|
||
standalone.recovery_example_images = first_content.image_assets,
|
||
standalone.recovery_enabled = 1
|
||
WHERE standalone.task_type = 'screenshot_collect';
|
||
-- statement-breakpoint
|
||
UPDATE tasks standalone
|
||
SET standalone.task_type = 'content_publish',
|
||
standalone.recovery_due_at = standalone.due_at,
|
||
standalone.recovery_instructions = '请提交发布后的笔记截图',
|
||
standalone.recovery_example_images = '[]',
|
||
standalone.recovery_enabled = 1
|
||
WHERE standalone.task_type = 'screenshot_collect';
|
||
-- statement-breakpoint
|
||
UPDATE contents SET related_content_id = '' WHERE related_content_id IS NOT NULL AND related_content_id != '';
|