-- RM10-LineageLoopDetector.sql
/*
2025-07-27 Google Gemini Flash 2.5 directed by Tom Holden ve3meo@gmail.com
Gemini Attempt 20 with enhancements:
1. "No loop detected" message when no loops are found.
2. Option to narrow search to a RootsMagic group named "SQLite Lineage Loop Detector".
   Includes error message if the group is not found.
3. Moved "Full Loop Path (IDs)" column to be second.
4. Changed "=" to "==" in "Family Where Loop Occurs" column.
This script 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, using user-friendly names.
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.
Compatible with RootsMagic database versions 8, 9, and 10 due to GroupTable/TagTable structure.
*/

WITH RECURSIVE
    -- Step 0: Find the PersonID ranges for the specified group
    TargetGroup AS (
        SELECT
            G.StartID,
            G.EndID
        FROM TagTable AS T
        JOIN GroupTable AS G ON T.TagValue = G.GroupID
        WHERE T.TagType = 0 -- 0 = Group
          AND T.TagName = 'SQLite Lineage Loop Detector'
          AND T.TagValue > 1000 -- Ensure it's a user-defined group
    ),
    -- Check if the target group was found
    GroupStatus AS (
        SELECT COUNT(*) AS GroupFoundCount FROM TargetGroup
    ),

    -- CycleFinder: This CTE builds potential paths, allowing the last step of a cycle to be included.
    -- Its anchor members are conditionally filtered based on TargetGroup.
    CycleFinder(
        start_person_id,
        current_person_id,
        loop_closing_family_id,
        path_ids_str,
        depth
    ) AS (
        -- Anchor member for fathers: Starts from ChildTable, conditionally filtered by TargetGroup
        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
        JOIN GroupStatus ON GroupStatus.GroupFoundCount > 0 -- Only run if group found
        LEFT JOIN TargetGroup tg ON ct.ChildID BETWEEN tg.StartID AND tg.EndID
        WHERE ft.FatherID IS NOT NULL AND ft.FatherID != 0
          AND (tg.StartID IS NOT NULL OR NOT EXISTS (SELECT 1 FROM TargetGroup)) -- Filter by group or include all if group not defined

        UNION ALL

        -- Anchor member for mothers: Starts from ChildTable, conditionally filtered by TargetGroup
        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
        JOIN GroupStatus ON GroupStatus.GroupFoundCount > 0 -- Only run if group found
        LEFT JOIN TargetGroup tg ON ct.ChildID BETWEEN tg.StartID AND tg.EndID
        WHERE ft.MotherID IS NOT NULL AND ft.MotherID != 0
          AND (tg.StartID IS NOT NULL OR NOT EXISTS (SELECT 1 FROM TargetGroup))

        UNION ALL

        -- Recursive member for fathers:
        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

        -- Recursive member for mothers:
        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
    )
-- Main SELECT statement to display results or messages
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)', -- Moved to second position
    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)',
    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'
    ) || '==' || -- Changed from '=' to '=='
    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
-- UNION ALL for messages
UNION ALL
-- Message when no loops are found AND the group was found (meaning search happened)
SELECT
    'No lineage loop detected in the group "SQLite Lineage Loop Detector".' AS 'Child in Problematic Family Link',
    NULL, NULL, NULL, NULL -- Maintain column count for UNION ALL
FROM GroupStatus
WHERE GroupStatus.GroupFoundCount > 0 AND NOT EXISTS (SELECT 1 FROM RankedLoops)

UNION ALL
-- Message when the specified group is NOT found
SELECT
    'Cannot find the group "SQLite Lineage Loop Detector". Please create it with this exact name.' AS 'Child in Problematic Family Link',
    NULL, NULL, NULL, NULL -- Maintain column count for UNION ALL
FROM GroupStatus
WHERE GroupStatus.GroupFoundCount = 0;