lea.pet-spam-cleanup/scan.js

require("dotenv").config();

const { Client } = require("pg");
const fs = require("fs");

const KEYWORD_MATCHES = [
    "https://xn--68j5e377y.com",
    "https://荒らし.com",
    "https://ctkpaarr.org",
    "https://discord.gg/ctkpaarr",
    "画像が貼れなか",
];

const BLURHASHES = [
    "elJ+3xk9Oat6NgtKjcV[aeo0}7agoJofj?xabYkTj@j=p_jcsmWBow",
    "eRQ0Hyrq.8IV%MVD4T%gxuIUrqD%ozxuRj~q?bM{M{%M~qxuM{t6bH",
    "elJ+3xk9Oat6NgtKjcV[aeo0}7agoJofj?xabYkTj@j=p_jcs,WBow",
];

(async () => {
    const client = new Client({
        host: process.env.PG_HOST,
        port: process.env.PG_PORT,
        user: process.env.PG_USER,
        password: process.env.PG_PASS,
        database: process.env.PG_DATABASE,
    });
    await client.connect();

    console.log("Searching for suspicious profiles");

    let res = await client.query(
        `SELECT note.*, "user".username, "user".host
        FROM note
        INNER JOIN "user" ON "user"."id" = note."userId"
        WHERE LENGTH("user".username) = 10
        AND "user".username = LOWER("user".username)
        AND "user"."avatarId" IS NULL
        AND "user"."bannerId" IS NULL
        AND "user"."followingCount" = 0`
    );
    console.log("Query returned " + res.rowCount + " rows");

    const filtered = res.rows.filter((row) => {
        for (const keyword of KEYWORD_MATCHES) {
            if (row.text?.includes(keyword)) return true;
        }
        return false;
    });

    console.log("Found " + filtered.length + " rows after filtering");

    fs.writeFileSync("output-keyword.json", JSON.stringify(filtered));

    console.log("Searching for blurhash matches");

    res = await client.query(
        `SELECT drive_file.*
        FROM drive_file
        WHERE drive_file.blurhash = ANY($1::text[])`,
        [BLURHASHES]
    );

    console.log("Found " + res.rowCount + " files matching blurhash");
    fs.writeFileSync("output-blurhash.json", JSON.stringify(res.rows, null, 4));

    console.log("Searching for notes using found files");

    const notes = await client.query(
        `SELECT *
        FROM note
        WHERE note."fileIds"::text[] && $1::text[]`,
        [res.rows.map((row) => `${row.id}`)]
    );

    console.log("Found " + notes.rowCount + " notes matching files");

    fs.writeFileSync("output-file_notes.json", JSON.stringify(notes.rows, null, 4));

    await client.end();
})();