--RM-LineageLoopDetector.sql
/* 
2025-07-20 Google Gemini Flash 2.5 directed by Tom Holden ve3meo@gmail.com
 Gemini Attempt 18 with revised order of columns
 https://g.co/gemini/share/2cdd5db67e6f

Examines ancestry of each child in the database for a loop in its lineage.
Outputs the loop path and the name(s)of the problematic father and/or mother in the path.
Also identifies the Family in which the problematic parent is found.
These outputs help the RM user examine their database with RootsMagic to locate
and break the link that causes the loop.

Tested using SQLiteSpy 1.9.28 Win64.
Should work on all versions of RM from 4 to 10.
*/

WITH RECURSIVE
    -- CycleFinder (same as Attempt 17)
    CycleFinder(
        start_person_id,
        current_person_id,
        loop_closing_family_id,
        path_ids_str,
        depth
    ) AS (
        -- Anchor member: Start with every direct child-parent relationship.
        SELECT
            ct.ChildID AS start_person_id,
            ft.FatherID AS current_person_id,
            ft.FamilyID AS loop_closing_family_id,
            CAST(ct.ChildID AS TEXT) || '->' || CAST(ft.FatherID AS TEXT) AS path_ids_str,
            1 AS depth
        FROM ChildTable AS ct
        JOIN FamilyTable AS ft ON ct.FamilyID = ft.FamilyID
        WHERE ft.FatherID IS NOT NULL AND ft.FatherID != 0

        UNION ALL

        SELECT
            ct.ChildID AS start_person_id,
            ft.MotherID AS current_person_id,
            ft.FamilyID AS loop_closing_family_id,
            CAST(ct.ChildID AS TEXT) || '->' || CAST(ft.MotherID AS TEXT) AS path_ids_str,
            1 AS depth
        FROM ChildTable AS ct
        JOIN FamilyTable AS ft ON ct.FamilyID = ft.FamilyID
        WHERE ft.MotherID IS NOT NULL AND ft.MotherID != 0

        UNION ALL

        SELECT
            cf.start_person_id,
            f.FatherID AS current_person_id,
            cf.loop_closing_family_id,
            cf.path_ids_str || '->' || CAST(f.FatherID AS TEXT) AS path_ids_str,
            cf.depth + 1 AS depth
        FROM CycleFinder AS cf
        JOIN ChildTable AS c ON cf.current_person_id = c.ChildID
        JOIN FamilyTable AS f ON c.FamilyID = f.FamilyID
        WHERE
            (c.RelFather = 0 OR c.RelFather = 1)
            AND f.FatherID IS NOT NULL AND f.FatherID != 0
            AND cf.depth < 100
            AND INSTR(cf.path_ids_str || '->', '->' || CAST(f.FatherID AS TEXT) || '->') = 0

        UNION ALL

        SELECT
            cf.start_person_id,
            f.MotherID AS current_person_id,
            cf.loop_closing_family_id,
            cf.path_ids_str || '->' || CAST(f.MotherID AS TEXT) AS path_ids_str,
            cf.depth + 1 AS depth
        FROM CycleFinder AS cf
        JOIN ChildTable AS c ON cf.current_person_id = c.ChildID
        JOIN FamilyTable AS f ON c.FamilyID = f.FamilyID
        WHERE
            (c.RelMother = 0 OR c.RelMother = 1)
            AND f.MotherID IS NOT NULL AND f.MotherID != 0
            AND cf.depth < 100
            AND INSTR(cf.path_ids_str || '->', '->' || CAST(f.MotherID AS TEXT) || '->') = 0
    ),
    -- FoundLoops: Select only the paths that successfully found a cycle
    FoundLoops AS (
        SELECT DISTINCT
            cf.start_person_id,
            cf.loop_closing_family_id,
            ft.FatherID AS family_father_id,
            ft.MotherID AS family_mother_id,
            cf.path_ids_str
        FROM CycleFinder AS cf
        JOIN FamilyTable AS ft ON cf.loop_closing_family_id = ft.FamilyID
        WHERE cf.current_person_id = cf.start_person_id
    ),
    -- RankedLoops: Select only the "most direct" one if multiple are found for the same underlying cycle
    RankedLoops AS (
        SELECT
            fl.*,
            ROW_NUMBER() OVER (
                PARTITION BY
                    (SELECT GROUP_CONCAT(T2.value, '-')
                     FROM (SELECT DISTINCT T3.value FROM json_each(
                             '[' || REPLACE(REPLACE(fl.path_ids_str, '->', ','), ' ', '') || ']'
                         ) AS T3 ORDER BY T3.value ASC) AS T2
                    )
                ORDER BY
                    LENGTH(fl.path_ids_str) ASC,
                    fl.loop_closing_family_id ASC
            ) AS rn
        FROM FoundLoops AS fl
    )
SELECT
    n_child.Given || ' ' || COALESCE(n_child.Surname, '') || '-' || rl.start_person_id AS 'Child in Problematic Family Link',
    rl.path_ids_str AS 'Full Loop Path (IDs)',
    CASE
        WHEN rl.family_father_id IS NOT NULL AND INSTR(rl.path_ids_str, '->' || CAST(rl.family_father_id AS TEXT) || '->') > 0 THEN
            n_father.Given || ' ' || COALESCE(n_father.Surname, '') || '-' || rl.family_father_id
        ELSE 'N/A'
    END AS 'Problematic Father (Descendant of Child)',
    CASE
        WHEN rl.family_mother_id IS NOT NULL AND INSTR(rl.path_ids_str, '->' || CAST(rl.family_mother_id AS TEXT) || '->') > 0 THEN
            n_mother.Given || ' ' || COALESCE(n_mother.Surname, '') || '-' || rl.family_mother_id
        ELSE 'N/A'
    END AS 'Problematic Mother (Descendant of Child)',
    -- New Family Where Loop Occurs Column
    COALESCE(
        CASE
            WHEN n_loop_father.Given IS NOT NULL OR n_loop_father.Surname IS NOT NULL THEN
                n_loop_father.Given || ' ' || COALESCE(n_loop_father.Surname, '') || '-' || rl.family_father_id
            ELSE 'N/A'
        END, 'N/A'
    ) || '=' ||
    COALESCE(
        CASE
            WHEN n_loop_mother.Given IS NOT NULL OR n_loop_mother.Surname IS NOT NULL THEN
                n_loop_mother.Given || ' ' || COALESCE(n_loop_mother.Surname, '') || '-' || rl.family_mother_id
            ELSE 'N/A'
        END, 'N/A'
    ) AS 'Family Where Loop Occurs'
FROM RankedLoops AS rl
JOIN NameTable AS n_child ON rl.start_person_id = n_child.OwnerID AND n_child.IsPrimary = 1
LEFT JOIN NameTable AS n_father ON rl.family_father_id = n_father.OwnerID AND n_father.IsPrimary = 1
LEFT JOIN NameTable AS n_mother ON rl.family_mother_id = n_mother.OwnerID AND n_mother.IsPrimary = 1
LEFT JOIN NameTable AS n_loop_father ON rl.family_father_id = n_loop_father.OwnerID AND n_loop_father.IsPrimary = 1
LEFT JOIN NameTable AS n_loop_mother ON rl.family_mother_id = n_loop_mother.OwnerID AND n_loop_mother.IsPrimary = 1
WHERE rl.rn = 1
ORDER BY 'Child in Problematic Family Link';