\set ON_ERROR_STOP on
BEGIN;
CREATE TABLE ops_bad_pdf_delete_20260613_191738_target AS
WITH c(task_id) AS (SELECT unnest(ARRAY[432981,433168,434093,438102,438133,440116,440887,440913,440928,440930,440934,440950,440985,440994,440999,441018,441026,441041,441046,441062,441094,441098,441102,441116,441118,441151,441204,441213,441223,441225,441227,441266,441298,441299,441322,441329,441334,441339,441357,441358,441378,441388,441393,441422,441475,441478,441508,441524,441525,441527,441573,441574,441580,441590,441607,441630,441636,441651,441668,441672,441712,441751,441829,441851,441853,441902,441908,441916,441920,441926,441930,441937,441958,441979,442000,442013,442061,442072,442074,442077,442079,442132,442166,442170,442172,442178,442193,442195,442197,442198,442235,442279,442281,442282,442285,442328,442329,442335,442337,442348,442389,442393,442435,442448,442515,442540,442543,442551,442552,442560,442570,442589,442637,442674,442697,442715,442739,442746,442817,442831,442841,442855,442875,442887,442891,442903,442910,442917,442970,442979,443011,443029,443042,443049,443073,443149,443157,443159,443178,443227,443281,443302,443310,443325,443329,443333,443347,443352,443354,443373,443399,443408,443421,443429,443460,443500,443527,443530,443555,443566,443630,443657,443659,443662,443683,443685,443688,443690,443704,443726,443734,443746,443763,443791,443793,443817,443930,443935,443971,443995,443999,444039,444063,444089,444129,444137,444145,444146,444174,444196,444205,444225,444257,444280,444306,444323,444373,444425,444432,444444,444458,444478,444480,444506,444519,444527,444534,444553,444569,444581,444582,444599,444624,444684,444712,444719,444741,444752,444765,444789,444814,444839,444841,444878,444883,444890,444908,444937,445043,445045,445062,445068,445080,445084,445088,445118,445125,445161,445194,445207,445218,445236,445255,445256,445271,445310,445367,445374,445465,445483,445519,445556,445579,445603,445636,445665,445704,445721,445741,445757,445761,445782,445807,445811,445822,445826,445839,445855,445895,445935,445946,445961,446027,446033,446034,446037,446044,446085,446094,446109,446144,446153,446176,446180,446183,446206,446215,446225,446275,446332,446395,446405,446440,446467,446474,446493,446531,446549,446568,446622,446652,446678,446700,446706,446708,446718,446739,446750,446755,446763,446766,446783,446789,446833,446837,446927,446982,446986,447014,447019,447022,447057,447085,447100,447120,447132,447134,447137,447140,447153,447236,447274,447282,447317,447322,447336,447347,447348,447362,447393,447447,447463,447477,447485,447592,447630,447642,447675,447681,447689,447694,447705,447710,447745,447747,447769,447799,447820,447824,447855,447862,447865,447924,447970,447996,448003,448005,448034,448037,448043,448064,448073,448084,448105,448131,448163,448199,448209,448219,448241,448256,448265,448294,448317,448320,448327,448331,448346,448363,448374,448381,448394,448401,448413,448437,448462,448473,448490,448510,448531,448541,448546,448578,448589,448590,448612,448641,448677,448697,448731,448738,448741,448760,448762,448768,448823,448834,448900,448904,448909,448929,448952,448959,448964,448967,449015,449017,449022,449040,449084,449086,449108,449120,449151,449153,449173,449194,449210,449231,449233,449243,449266,449290,449321,449323,449346,449409,449413,449428,449431,449470,449486,449542,449546,449548,449589,449591,449601,449609,449630,449647,449653,449667,449695,449719,449739,449761,449782,449789,449791,449797,449803,449804,449845,449855,449880,449885,449889,449894,449906,449924,449925,449947,449979,449987,449995,450031,450033,450079,450103,450129,450155,450164,450185,450192,450195,450202,450207,450216,450232,450255,450267,450268,450283,450342,450352,450357,450375,450391,450400,450469,450470,450477,450510,450513,450523,450590,450599,450615,450646,450669,450696,450717,450722,450733,450734,450739,450747,450803,450834,450853,450858,450859,450876,450883,450905,450906,450908,450964,450981,450984,451022,451030,451070,451085,451096,451102,451132,451146,451151,451212,451234,451240,451258,451272,451278,451322,451331,451365,451378,451413,451426,451456,451458,451498,451514,451545,451551,451554,451619,451623,451679,451682,451712,451755,451787,451788,451799,451824,451827,451834,451843,451849,451880,451892,451943,451948,451981,451996,451999,452033,452045,452067,452082,452126,452136,452154,452220,452223,452270,452275,452277,452278,452294,452295,452310,452316,452327,452329,452337,452353,452356,452370,452375,452403,452422,452436,452442,452466,452475,452478,452499,452515,452533,452553,452590,452596,452624,452675,452691,452720,452734,452750,452794,452807,452840,452843,452901,452904,452906,452956,452988,453028,453039,453046,453058,453082,453128,453140,453180,453203,453241,453252,453269,453284,453285,453309,453348,453352,453389,453391,453392,453411,453466,453483,453529,453557,453627,453649,453665,453676,453690,453702,453707,453732,453735,453742,453751,453863,453865,453934,453952,453962,454003,454006,454017,454025,454047,454055,454056,454101,454114,454142,454143,454149,454162,454165,454200,454202,454212,454223,454227,454251,454272,454285,454296,454307,454311,454373,454376]::bigint[]))
SELECT t.id task_id, t.related_id doc_id, (t.task_arguments::jsonb->>'file_id')::bigint file_id, d.doc_file_id, f.path, f.file_size, df.doc_type, df.related_user_id, df.related_org_id, df.related_industry_id
FROM c
JOIN tasks t ON t.id=c.task_id
JOIN docs d ON d.id=t.related_id AND d.deleted_at IS NULL
JOIN doc_files df ON df.id=d.doc_file_id AND df.deleted_at IS NULL
JOIN files f ON f.id=(t.task_arguments::jsonb->>'file_id')::bigint AND f.deleted_at IS NULL AND f.id=df.file_id
WHERE t.deleted_at IS NULL AND t.task_status='pending' AND t.task_type='文档解析';

CREATE TABLE ops_bad_pdf_delete_20260613_191738_tasks AS SELECT t.* FROM tasks t JOIN ops_bad_pdf_delete_20260613_191738_target x ON x.task_id=t.id;
CREATE TABLE ops_bad_pdf_delete_20260613_191738_docs AS SELECT d.* FROM docs d JOIN ops_bad_pdf_delete_20260613_191738_target x ON x.doc_id=d.id;
CREATE TABLE ops_bad_pdf_delete_20260613_191738_doc_files AS SELECT df.* FROM doc_files df JOIN ops_bad_pdf_delete_20260613_191738_target x ON x.doc_file_id=df.id;
CREATE TABLE ops_bad_pdf_delete_20260613_191738_files AS SELECT f.* FROM files f JOIN ops_bad_pdf_delete_20260613_191738_target x ON x.file_id=f.id;
CREATE TABLE ops_bad_pdf_delete_20260613_191738_favor AS SELECT u.* FROM user_favor_docs u JOIN ops_bad_pdf_delete_20260613_191738_target x ON x.doc_id=u.doc_id;
CREATE TABLE ops_bad_pdf_delete_20260613_191738_color_tags AS SELECT u.* FROM user_doc_color_tags u JOIN ops_bad_pdf_delete_20260613_191738_target x ON x.doc_id=u.doc_id;
CREATE TABLE ops_bad_pdf_delete_20260613_191738_attachments AS
SELECT f.* FROM files f JOIN ops_bad_pdf_delete_20260613_191738_target x ON f.file_remark IN ('attachment for ' || x.path, 'attachment for ../../' || x.path) WHERE f.deleted_at IS NULL;

-- Safety checks: abort if target changed into shared/non-isolated rows.
DO $$
DECLARE
  n_target int;
  n_shared int;
  n_other_tasks int;
BEGIN
  SELECT count(*) INTO n_target FROM ops_bad_pdf_delete_20260613_191738_target;
  IF n_target = 0 THEN
    RAISE EXCEPTION 'No current pending targets to delete';
  END IF;
  SELECT count(*) INTO n_shared
  FROM (
    SELECT x.file_id, count(df.id) refs
    FROM ops_bad_pdf_delete_20260613_191738_target x
    JOIN doc_files df ON df.file_id=x.file_id AND df.deleted_at IS NULL
    GROUP BY x.file_id
    HAVING count(df.id) > 1
  ) s;
  IF n_shared > 0 THEN
    RAISE EXCEPTION 'Shared file refs detected: %', n_shared;
  END IF;
  SELECT count(*) INTO n_other_tasks
  FROM ops_bad_pdf_delete_20260613_191738_target x
  JOIN tasks t ON t.related_id=x.doc_id AND t.deleted_at IS NULL AND t.id<>x.task_id;
  IF n_other_tasks > 0 THEN
    RAISE EXCEPTION 'Other active tasks detected: %', n_other_tasks;
  END IF;
END $$;

DELETE FROM user_favor_docs u USING ops_bad_pdf_delete_20260613_191738_target x WHERE u.doc_id=x.doc_id;
DELETE FROM user_doc_color_tags u USING ops_bad_pdf_delete_20260613_191738_target x WHERE u.doc_id=x.doc_id;
DELETE FROM files f USING ops_bad_pdf_delete_20260613_191738_attachments a WHERE f.id=a.id;
DELETE FROM tasks t USING ops_bad_pdf_delete_20260613_191738_target x WHERE t.id=x.task_id;
DELETE FROM docs d USING ops_bad_pdf_delete_20260613_191738_target x WHERE d.id=x.doc_id;
DELETE FROM doc_files df USING ops_bad_pdf_delete_20260613_191738_target x WHERE df.id=x.doc_file_id;
DELETE FROM files f USING ops_bad_pdf_delete_20260613_191738_target x WHERE f.id=x.file_id;

SELECT 'target', count(*) FROM ops_bad_pdf_delete_20260613_191738_target
UNION ALL SELECT 'backup_tasks', count(*) FROM ops_bad_pdf_delete_20260613_191738_tasks
UNION ALL SELECT 'backup_docs', count(*) FROM ops_bad_pdf_delete_20260613_191738_docs
UNION ALL SELECT 'backup_doc_files', count(*) FROM ops_bad_pdf_delete_20260613_191738_doc_files
UNION ALL SELECT 'backup_files', count(*) FROM ops_bad_pdf_delete_20260613_191738_files
UNION ALL SELECT 'backup_attachments', count(*) FROM ops_bad_pdf_delete_20260613_191738_attachments;
COMMIT;
