ALTER TABLE ccdi_project
    MODIFY COLUMN status CHAR(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0'
        COMMENT '项目状态：0-进行中，1-已完成，2-已归档，3-打标中，4-打标失败';

INSERT INTO sys_dict_data (
    dict_sort,
    dict_label,
    dict_value,
    dict_type,
    css_class,
    list_class,
    is_default,
    status,
    create_by,
    create_time
)
SELECT
    5,
    '打标失败',
    '4',
    'ccdi_project_status',
    '',
    'danger',
    'N',
    '0',
    'admin',
    NOW()
WHERE NOT EXISTS (
    SELECT 1
    FROM sys_dict_data
    WHERE dict_type = 'ccdi_project_status'
      AND dict_value = '4'
);

UPDATE ccdi_project project
JOIN (
    SELECT latest.project_id, latest.status
    FROM ccdi_bank_tag_task latest
    JOIN (
        SELECT project_id, MAX(id) AS latest_id
        FROM ccdi_bank_tag_task
        GROUP BY project_id
    ) latest_id
        ON latest.id = latest_id.latest_id
) latest_task
    ON latest_task.project_id = project.project_id
SET project.status = '4',
    project.update_by = 'system',
    project.update_time = NOW()
WHERE latest_task.status = 'FAILED'
  AND project.status IN ('0', '3')
  AND (project.is_archived IS NULL OR project.is_archived = 0)
  AND project.del_flag = '0';
