BEGIN;

CREATE TEMP TABLE tmp_failed_docs_for_requeue ON COMMIT DROP AS
SELECT
    d.id AS doc_id,
    d.name AS doc_name,
    d.creator_user_id,
    d.parsed_markdown_file_id,
    df.doc_type,
    df.file_id,
    df.folder_id,
    df.related_org_id,
    df.related_industry_id,
    f.file_name,
    f.file_type
FROM docs d
JOIN doc_files df ON df.id = d.doc_file_id AND df.deleted_at IS NULL
JOIN files f ON f.id = df.file_id AND f.deleted_at IS NULL
WHERE d.deleted_at IS NULL
  AND d.status = 解析失败
  AND NOT EXISTS (
      SELECT 1
      FROM tasks active_t
      WHERE active_t.related_id = d.id
        AND active_t.task_type = 文档解析
        AND active_t.deleted_at IS NULL
        AND active_t.task_status IN (pending, queued, running)
  );

SELECT selected_failed_docs, count(*) FROM tmp_failed_docs_for_requeue;

CREATE TEMP TABLE tmp_existing_failed_tasks_for_requeue ON COMMIT DROP AS
SELECT t.id AS task_id, t.related_id AS doc_id
FROM tasks t
JOIN tmp_failed_docs_for_requeue fd ON fd.doc_id = t.related_id
WHERE t.task_type = 文档解析
  AND t.deleted_at IS NULL
  AND t.task_status = failed;

SELECT existing_failed_tasks, count(*) FROM tmp_existing_failed_tasks_for_requeue;

CREATE TEMP TABLE tmp_docs_without_tasks_for_requeue ON COMMIT DROP AS
SELECT fd.*
FROM tmp_failed_docs_for_requeue fd
WHERE NOT EXISTS (
    SELECT 1
    FROM tasks t
    WHERE t.related_id = fd.doc_id
      AND t.task_type = 文档解析
      AND t.deleted_at IS NULL
);

SELECT docs_without_tasks, count(*) FROM tmp_docs_without_tasks_for_requeue;

UPDATE docs d
SET status = 待处理,
    parsed_markdown_file_id = 0,
    updated_at = now()
FROM tmp_failed_docs_for_requeue fd
WHERE d.id = fd.doc_id
  AND d.deleted_at IS NULL
  AND d.status = 解析失败;

SELECT docs_updated_to_pending, count(*)
FROM tmp_failed_docs_for_requeue fd
JOIN docs d ON d.id = fd.doc_id
WHERE d.status = 待处理;

UPDATE tasks t
SET task_status = pending,
    task_result = ,
    task_log = ,
    updated_at = now()
FROM tmp_existing_failed_tasks_for_requeue et
WHERE t.id = et.task_id
  AND t.deleted_at IS NULL
  AND t.task_status = failed;

SELECT tasks_reset_to_pending, count(*)
FROM tmp_existing_failed_tasks_for_requeue et
JOIN tasks t ON t.id = et.task_id
WHERE t.task_status = pending;

INSERT INTO tasks (
    created_at,
    updated_at,
    deleted_at,
    task_name,
    task_log,
    task_type,
    related_id,
    task_arguments,
    task_priority,
    task_status,
    task_user_id,
    task_result
)
SELECT
    now(),
    now(),
    NULL,
    解析 || coalesce(file_name, doc_name, (doc_ || doc_id::text)) ||  上传者： || creator_user_id::text,
    ,
    文档解析,
    doc_id,
    jsonb_strip_nulls(
        jsonb_build_object(
            file_id, file_id,
            folder_id, coalesce(folder_id, 0),
            file_name, coalesce(file_name, doc_name, (doc_ || doc_id::text)),
            type, coalesce(nullif(doc_type, ), personal),
            org_id, related_org_id,
            industry_id, related_industry_id,
            extract_picture, true,
            describe_picture, false,
            llm_describe, false
        )
    )::text,
    CASE WHEN doc_type = personal THEN 1 ELSE 2 END,
    pending,
    creator_user_id,
    
FROM tmp_docs_without_tasks_for_requeue;

SELECT tasks_inserted_for_no_task_docs, count(*)
FROM tmp_docs_without_tasks_for_requeue d
JOIN tasks t ON t.related_id = d.doc_id
WHERE t.task_type = 文档解析
  AND t.deleted_at IS NULL
  AND t.task_status = pending;

SELECT remaining_requeue_selected_still_failed, count(*)
FROM tmp_failed_docs_for_requeue fd
JOIN docs d ON d.id = fd.doc_id
WHERE d.status = 解析失败;

SELECT active_tasks_for_requeued_docs, count(*)
FROM tmp_failed_docs_for_requeue fd
JOIN tasks t ON t.related_id = fd.doc_id
WHERE t.task_type = 文档解析
  AND t.deleted_at IS NULL
  AND t.task_status IN (pending,queued,running);

COMMIT;
