--CoupleCousins-09.sql
/* 2025-07-17 Tom Holden ve3meo directing Google Gemini 2.5 Flash

Generates list of couples who are cousins
https://g.co/gemini/share/0b73c3f2cff8
"Revised Query (Attempt 7 - Performance Focused Common Ancestor Naming)"
subsequently corrected so that unknown MRCA does not null the value displayed.
Results limited to MRCA instead of all common ancestors,
Includes relationships via "Unknown" ancestors.
Works 30x faster than earliest version.
Correctly reporta descendents of the same known and unknown (ParentID=0) couple
as "full cousins" instead of "half cousins". 
Reformatted names to correspond to RM style.
Added column for number of children. 
For full cousins, presents both common ancestors
*/

WITH RECURSIVE
    -- 1. Optimized Ancestors CTE: Trace paths and capture the FamilyID that this ancestor *parents*
    Ancestors(person_id, ancestor_id, generation, family_id_where_ancestor_is_parent) AS (
        -- Anchor members: A person is their own 0th generation ancestor
        SELECT
            T2.PersonID AS person_id,
            T2.PersonID AS ancestor_id,
            0 AS generation,
            NULL AS family_id_where_ancestor_is_parent
        FROM PersonTable AS T2
        WHERE T2.PersonID IN (SELECT FatherID FROM FamilyTable WHERE FatherID IS NOT NULL AND FatherID != 0 UNION ALL SELECT MotherID FROM FamilyTable WHERE MotherID IS NOT NULL AND MotherID != 0)

        UNION ALL

        -- Recursive member for fathers:
        SELECT
            a.person_id,
            f.FatherID AS ancestor_id,
            a.generation + 1 AS generation,
            c.FamilyID AS family_id_where_ancestor_is_parent
        FROM Ancestors AS a
        JOIN ChildTable AS c ON a.ancestor_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

        UNION ALL

        -- Recursive member for mothers (similar logic)
        SELECT
            a.person_id,
            f.MotherID AS ancestor_id,
            a.generation + 1 AS generation,
            c.FamilyID AS family_id_where_ancestor_is_parent
        FROM Ancestors AS a
        JOIN ChildTable AS c ON a.ancestor_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
    ),

    -- 2. Cleaned Ancestors: Filter out self and unknown ancestors
    CleanAncestors AS (
        SELECT DISTINCT person_id, ancestor_id, generation, family_id_where_ancestor_is_parent
        FROM Ancestors
        WHERE person_id != ancestor_id
          AND ancestor_id IS NOT NULL
          AND ancestor_id != 0
    ),

    -- 3. All Potential Common Ancestors and their path details
    AllPotentialCousinPaths AS (
        SELECT
            ft.FatherID AS father_id,
            ft.MotherID AS mother_id,
            af.ancestor_id AS common_ancestor_id,
            af.generation AS father_generations_back,
            am.generation AS mother_generations_back,
            (af.generation + am.generation) AS total_generations_back,
            af.family_id_where_ancestor_is_parent AS father_line_ancestral_family_id,
            am.family_id_where_ancestor_is_parent AS mother_line_ancestral_family_id
        FROM FamilyTable AS ft
        JOIN CleanAncestors AS af ON ft.FatherID = af.person_id
        JOIN CleanAncestors AS am ON ft.MotherID = am.person_id
        WHERE af.ancestor_id = am.ancestor_id
          AND ft.FatherID != ft.MotherID
          AND ft.FatherID IS NOT NULL AND ft.MotherID IS NOT NULL
          AND af.generation >= 2 AND am.generation >= 2
          AND NOT EXISTS (SELECT 1 FROM ChildTable AS cf WHERE cf.ChildID = ft.FatherID AND cf.FamilyID = ft.FamilyID AND (cf.RelMother = 0 OR cf.RelMother = 1))
          AND NOT EXISTS (SELECT 1 FROM ChildTable AS cm WHERE cm.ChildID = ft.MotherID AND cm.FamilyID = ft.FamilyID AND (cm.RelFather = 0 OR cm.RelFather = 1))
    ),

    -- 4. Find the minimum total_generations_back for each couple
    MinTotalGenerations AS (
        SELECT
            father_id,
            mother_id,
            MIN(total_generations_back) AS min_total_gen
        FROM AllPotentialCousinPaths
        GROUP BY father_id, mother_id
    ),

    -- 5. Select the MRCA path details for each couple, using the minimum total generations found.
    --    Handle ties by picking the common_ancestor_id with the lowest ID.
    MRCA_Paths AS (
        SELECT
            t1.father_id,
            t1.mother_id,
            t1.common_ancestor_id,
            t1.father_generations_back,
            t1.mother_generations_back,
            t1.total_generations_back,
            t1.father_line_ancestral_family_id,
            t1.mother_line_ancestral_family_id
        FROM AllPotentialCousinPaths AS t1
        JOIN MinTotalGenerations AS mtg
            ON t1.father_id = mtg.father_id
           AND t1.mother_id = mtg.mother_id
           AND t1.total_generations_back = mtg.min_total_gen
        WHERE NOT EXISTS (
            SELECT 1
            FROM AllPotentialCousinPaths AS t2
            WHERE t2.father_id = t1.father_id
              AND t2.mother_id = t1.mother_id
              AND t2.total_generations_back = t1.total_generations_back
              AND t2.common_ancestor_id < t1.common_ancestor_id
        )
    ),

    -- 6. Determine Full/Half status and common ancestral family ID
    CousinRelationships AS (
        SELECT
            mrca.father_id,
            mrca.mother_id,
            mrca.common_ancestor_id,
            mrca.father_generations_back,
            mrca.mother_generations_back,
            mrca.total_generations_back,
            CASE
                WHEN mrca.father_line_ancestral_family_id = mrca.mother_line_ancestral_family_id
                     AND mrca.father_line_ancestral_family_id IS NOT NULL
                     AND (
                            (SELECT FatherID FROM FamilyTable WHERE FamilyID = mrca.father_line_ancestral_family_id) = mrca.common_ancestor_id
                            OR
                            (SELECT MotherID FROM FamilyTable WHERE FamilyID = mrca.father_line_ancestral_family_id) = mrca.common_ancestor_id
                         )
                THEN 'Full'
                ELSE 'Half'
            END AS CousinType,
            CASE
                WHEN mrca.father_line_ancestral_family_id = mrca.mother_line_ancestral_family_id
                THEN mrca.father_line_ancestral_family_id
                ELSE NULL
            END AS common_ancestral_family_id
        FROM MRCA_Paths AS mrca
    ),

    -- NEW CTE: Pre-calculate Common Ancestor Name string for performance
    MRCA_Common_Ancestor_Names AS (
        SELECT
            cr.father_id,
            cr.mother_id,
            CASE
                WHEN cr.CousinType = 'Full' AND cr.common_ancestral_family_id IS NOT NULL THEN
                    -- Get Father and Mother of the common ancestral family directly here
                    -- Handle ID=0 for Father
                    CASE ft_mrca.FatherID
                        WHEN 0 THEN 'Unknown'
                        ELSE (SELECT COALESCE(n_f.Given || ' ' || COALESCE(n_f.Surname, ''), 'Unknown') FROM NameTable n_f WHERE n_f.OwnerID = ft_mrca.FatherID AND n_f.IsPrimary = 1)
                    END ||
                    CASE WHEN ft_mrca.FatherID != 0 AND ft_mrca.MotherID != 0 THEN '-' || ft_mrca.FatherID ELSE '' END ||
                    '=' ||
                    -- Handle ID=0 for Mother
                    CASE ft_mrca.MotherID
                        WHEN 0 THEN 'Unknown'
                        ELSE (SELECT COALESCE(n_m.Given || ' ' || COALESCE(n_m.Surname, ''), 'Unknown') FROM NameTable n_m WHERE n_m.OwnerID = ft_mrca.MotherID AND n_m.IsPrimary = 1)
                    END ||
                    CASE WHEN ft_mrca.FatherID != 0 AND ft_mrca.MotherID != 0 THEN '-' || ft_mrca.MotherID ELSE '' END
                ELSE
                    -- For half cousins, get the single common ancestor name
                    nca.Given || ' ' || COALESCE(nca.Surname, '') || '-' || cr.common_ancestor_id
            END AS formatted_common_ancestor_name
        FROM CousinRelationships AS cr
        LEFT JOIN FamilyTable AS ft_mrca ON cr.common_ancestral_family_id = ft_mrca.FamilyID
        JOIN NameTable AS nca ON cr.common_ancestor_id = nca.OwnerID AND nca.IsPrimary = 1 -- Keep this for half-cousins
    )

SELECT
    nf.Given || ' ' || COALESCE(nf.Surname, '') || '-' || cr.father_id AS FatherName,
    nm.Given || ' ' || COALESCE(nm.Surname, '') || '-' || cr.mother_id AS MotherName,
    mrca_names.formatted_common_ancestor_name AS CommonAncestor, -- Use the pre-calculated name
    cr.CousinType || ' ' ||
    (CASE
        WHEN cr.father_generations_back = 2 AND cr.mother_generations_back = 2 THEN 'First Cousins'
        WHEN cr.father_generations_back = 3 AND cr.mother_generations_back = 3 THEN 'Second Cousins'
        WHEN cr.father_generations_back = 4 AND cr.mother_generations_back = 4 THEN 'Third Cousins'
        ELSE
            (CAST(MIN(cr.father_generations_back, cr.mother_generations_back) - 1 AS TEXT) || 'th Cousin') ||
            CASE
                WHEN ABS(cr.father_generations_back - cr.mother_generations_back) > 0 THEN
                    ' ' || ABS(cr.father_generations_back - cr.mother_generations_back) || ' time(s) removed'
                ELSE ''
            END
    END) AS RelationshipDescription,
    (
        SELECT COUNT(ct.ChildID)
        FROM FamilyTable AS ft_children
        JOIN ChildTable AS ct ON ft_children.FamilyID = ct.FamilyID
        WHERE ft_children.FatherID = cr.father_id AND ft_children.MotherID = cr.mother_id
    ) AS NumberOfChildren
    ,cr.father_generations_back AS FatherGenerationsBack,
    cr.mother_generations_back AS MotherGenerationsBack,
    cr.total_generations_back
FROM CousinRelationships AS cr
JOIN NameTable AS nf ON cr.father_id = nf.OwnerID AND nf.IsPrimary = 1
JOIN NameTable AS nm ON cr.mother_id = nm.OwnerID AND nm.IsPrimary = 1
JOIN MRCA_Common_Ancestor_Names AS mrca_names ON cr.father_id = mrca_names.father_id AND cr.mother_id = mrca_names.mother_id -- Join to the new CTE
ORDER BY
    cr.total_generations_back ASC,
    CASE WHEN cr.CousinType = 'Full' THEN 0 ELSE 1 END ASC,
    FatherName, MotherName;