SQL script to show / remove the asset folder id's that are empty? (proposal included)

[e4rthdog]

By now i think that the only issue i have with wiki.js and github sync is the fact that it creates orhpan folders if you remove files from them.

Is there anyone that has any script to search through assets and assetFolders tables and print out the IDs of the folders that are empty, taking care also the parent->child relationship[s of folders?

Meaning that i would need to clear out the whole folder tree if all of them are empty.

More Info:

After playing with Chat GPT i got his script that i think it delivers. For anyone that can verify:

WITH RECURSIVE folder_paths AS (
    SELECT
        "id",
        "name",
        "parentId",
        CAST('.\' || "name" AS TEXT) AS "path"
    FROM
        "assetFolders"
    WHERE
        "parentId" IS NULL

    UNION ALL

    SELECT
        af."id",
        af."name",
        af."parentId",
        fp."path" || '\' || af."name"
    FROM
        "assetFolders" af
        JOIN folder_paths fp ON af."parentId" = fp."id"
),
non_empty_folders AS (
    SELECT DISTINCT
        a."folderId"
    FROM
        "assets" a
    WHERE
        a."folderId" IS NOT NULL

    UNION

    SELECT DISTINCT
        af."parentId"
    FROM
        "assetFolders" af
        INNER JOIN "assets" a ON af."id" = a."folderId"
    WHERE
        af."parentId" IS NOT NULL
),
recursive_parents AS (
    SELECT
        af."id",
        af."parentId"
    FROM
        "assetFolders" af
    WHERE
        af."id" IN (SELECT "folderId" FROM non_empty_folders)

    UNION ALL

    SELECT
        af."parentId",
        af2."parentId"
    FROM
        "assetFolders" af
        JOIN recursive_parents rp ON af."id" = rp."parentId"
        JOIN "assetFolders" af2 ON af."parentId" = af2."id"
    WHERE
        af."parentId" IS NOT NULL
),
all_non_empty_folders AS (
    SELECT
        "folderId" AS "id"
    FROM
        non_empty_folders

    UNION DISTINCT

    SELECT
        rp."id"
    FROM
        recursive_parents rp
)
SELECT
    fp."id",
    fp."path"
FROM
    folder_paths fp
LEFT JOIN all_non_empty_folders anef ON fp."id" = anef."id"
WHERE
    anef."id" IS NULL
ORDER BY
    fp."id";