--RM10-PlaceReverse.sql
/* 2025-08-09 Tom Holden ve3meo directed DeepSeek to generate this query.

Updates the Reverse column in PlaceTable with the reversed order of comma-separated
values from the Name column for ALL non-LDS Temple Place records but not Place Details records. 
Handles any number of elements.
Very efficient - processed 48,000 records in 2-3s on Intel i5-6200U CPU with SSD.
*/

-- Enable recursive CTEs (usually enabled by default in SQLite)
PRAGMA recursive_truncate = ON;

-- Update the Reverse column using a recursive CTE
WITH RECURSIVE split(rowid, part, rest, n) AS (
    SELECT 
        ROWID,
        TRIM(SUBSTR(Name || ',', 1, INSTR(Name || ',', ',') - 1)),
        LTRIM(SUBSTR(Name || ',', INSTR(Name || ',', ',') + 1)),
        1
    FROM PlaceTable
    WHERE PlaceType = 0 AND Name IS NOT NULL
    UNION ALL
    SELECT 
        rowid,
        TRIM(SUBSTR(rest, 1, INSTR(rest || ',', ',') - 1)),
        LTRIM(SUBSTR(rest, INSTR(rest || ',', ',') + 1)),
        n + 1
    FROM split
    WHERE rest <> ''
),
reversed AS (
    SELECT rowid, 
           GROUP_CONCAT(part, ', ') AS reversed_name
    FROM (
        SELECT rowid, part
        FROM split
        ORDER BY rowid, n DESC
    )
    GROUP BY rowid
)
UPDATE PlaceTable
SET Reverse = CASE 
    WHEN Name IS NULL THEN NULL
    ELSE COALESCE((
        SELECT reversed_name 
        FROM reversed 
        WHERE reversed.rowid = PlaceTable.rowid
    ), '')  -- Handle empty strings
END
WHERE PlaceType = 0;