-- Birth-AddFromChristenBaptism_RM11_ByDate.sql
-- 2026-08-05 Generated by Google Gemini under the direction of Tom Holden
--
-- 1. Adds a Birth event (FactTypeID = 1) using the 'By' modifier (DY / Flag 3)
--    for individuals with a Christening (3) or Baptism (7) event but no Birth event.
-- 2. Updates PersonTable.UTCModDate to refresh "Date Last Edited" in the People view.
-- 3. Updates NameTable.BirthYear with the year extracted from the new Birth event date.

BEGIN TRANSACTION;

-- Ensure no lingering temp table exists from an interrupted connection session
DROP TABLE IF EXISTS tNewBirths;

-- Create temporary table to hold the new Birth events
CREATE TEMP TABLE tNewBirths AS
SELECT 
    1 AS EventType,                                               -- 1 = Birth
    OwnerType,
    OwnerID,
    FamilyID,
    PlaceID,
    SiteID,
    
    -- Convert Date string prefix to 'DY' (By)
    CASE 
        WHEN Date LIKE 'D%' THEN 'DY' || SUBSTR(Date, 3)
        ELSE Date 
    END AS Date,
    
    -- Adjust SortDate bits (0-9) to 3 ('By' flag)
    CASE 
        WHEN SortDate IS NOT NULL AND SortDate > 0 AND SortDate < 9223372036854775807 THEN
            (SortDate - (SortDate & 1023)) + 3
        ELSE SortDate 
    END AS SortDate,
    
    IsPrimary,
    IsPrivate,
    Proof,
    Status,
    Sentence,
    Details,
    Note,
    (julianday('now') - 2415018.5) AS UTCModDate                  -- RM11 Julian Day timestamp
FROM (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY OwnerID 
            ORDER BY 
                CASE EventType WHEN 3 THEN 1 WHEN 7 THEN 2 ELSE 3 END ASC, -- CHR (3) before BAPM (7)
                IsPrimary DESC,                                           -- Primary event first
                SortDate ASC                                              -- Earliest date first
        ) AS RowNum
    FROM EventTable
    WHERE OwnerType = 0                                                   -- Individual events
      AND EventType IN (3, 7)                                             -- Christening (3) or Baptism (7)
      AND OwnerID NOT IN (
          -- Exclude anyone who already has a Birth event
          SELECT OwnerID 
          FROM EventTable 
          WHERE OwnerType = 0 AND EventType = 1
      )
) AS EligibleEvents
WHERE RowNum = 1;


-- STEP 1: Insert new Birth events into EventTable
INSERT INTO EventTable (
    EventType, OwnerType, OwnerID, FamilyID, PlaceID, SiteID,
    Date, SortDate, IsPrimary, IsPrivate, Proof, Status,
    Sentence, Details, Note, UTCModDate
)
SELECT 
    EventType, OwnerType, OwnerID, FamilyID, PlaceID, SiteID,
    Date, SortDate, IsPrimary, IsPrivate, Proof, Status,
    Sentence, Details, Note, UTCModDate
FROM tNewBirths;


-- STEP 2: Update PersonTable.UTCModDate ("Date Last Edited")
UPDATE PersonTable
SET UTCModDate = (
    SELECT t.UTCModDate 
    FROM tNewBirths t 
    WHERE t.OwnerID = PersonTable.PersonID
)
WHERE PersonID IN (SELECT OwnerID FROM tNewBirths);


-- STEP 3: Update NameTable.BirthYear (Refreshes UI birth year displays)
UPDATE NameTable
SET BirthYear = (
    SELECT 
        CASE 
            WHEN t.Date LIKE '__+%' AND LENGTH(t.Date) >= 7 AND CAST(SUBSTR(t.Date, 4, 4) AS INT) > 0 
            THEN CAST(SUBSTR(t.Date, 4, 4) AS INT)
            ELSE 0
        END
    FROM tNewBirths t 
    WHERE t.OwnerID = NameTable.OwnerID
)
WHERE OwnerID IN (SELECT OwnerID FROM tNewBirths);


-- Clean up temporary table
DROP TABLE IF EXISTS tNewBirths;

COMMIT;