/* Tidy (DELETE FROM) then analyze a couple of iceshrimp.net tables */
/* Credit to Phasorburn in the iceshrimp.net dev channel (https://chat.iceshrimp.dev) */
DELETE
FROM "note" n
WHERE n."userHost" IS NOT NULL
  AND n."createdAt"::date <= (CURRENT_DATE - interval '5' DAY)

  AND NOT EXISTS
    (SELECT id
     FROM "following" f
     WHERE n."userId" = f."followeeId"
       OR n."userId" = f."followerId"
       OR n."renoteId" = f."followeeId"
       OR n."renoteId" = f."followerId")

  AND NOT EXISTS
    (SELECT id
     FROM "note_like" nl
     WHERE nl."userId" IN
         (SELECT id
          FROM "user" u
          WHERE u."host" IS NULL
            AND nl."noteId" = n.id))

  AND NOT EXISTS
    (SELECT id
     FROM "note_reaction" nr
     WHERE nr."userId" IN
         (SELECT id
          FROM "user" u
          WHERE u."host" IS NULL
            AND nr."noteId" = n.id))

  AND NOT EXISTS
    (SELECT id
     FROM "note_watching" nw
     WHERE nw."userId" IN
         (SELECT id
          FROM "user" u
          WHERE u."host" IS NULL
            AND nw."noteId" = n.id));

/* Tidy (ANALYZE) any table that has had a DELETE performed upon it */
ANALYZE public.note, public.note_like, public.note_reaction, public.note_watching;

/*
note tables
~~~~~~~~~~~
note_bookmark
note_edit
note_like
note_reaction
note_thread_muting
note_unread
note_watching
*/