-- Clean a plWN database of things that cause the API reading to break.
-- This is ad hoc, for cases when we need one-off dumps for internal usage.
-- Let's not involve SWORD, please.

-- Remove relations with types that don't exist or one of the endpoints that
-- don't exist.
DELETE synsetrelation
FROM synsetrelation
    LEFT JOIN relationtype ON REL_ID = relationtype.ID
    LEFT JOIN synset AS par_syn ON synsetrelation.PARENT_ID = par_syn.ID
    LEFT JOIN synset AS chl_syn ON CHILD_ID = chl_syn.ID
WHERE relationtype.ID IS NULL OR par_syn.ID IS NULL OR chl_syn.ID IS NULL;

DELETE lexicalrelation
FROM lexicalrelation
    LEFT JOIN relationtype ON REL_ID = relationtype.ID
    LEFT JOIN lexicalunit AS par_lu ON lexicalrelation.PARENT_ID = par_lu.ID
    LEFT JOIN lexicalunit AS chl_lu ON CHILD_ID = chl_lu.ID
WHERE relationtype.ID IS NULL OR par_lu.ID IS NULL OR chl_lu.ID IS NULL;

-- Remove relations that are instances of parent relations (if a relation
-- has children, only they are legal values.)
DELETE FROM synsetrelation WHERE REL_ID IN (
    SELECT rel_outer.ID
    FROM relationtype AS rel_outer
    WHERE rel_outer.PARENT_ID IS NULL
        AND EXISTS (
            SELECT 1
            FROM relationtype AS rel_inner
            WHERE rel_inner.PARENT_ID = rel_outer.ID
        )
);