-- SQL script to remove manually added hymn titles from bulletins
-- This will clean up patterns like "#319 - Amazing Grace" back to just "#319"
-- Run these in order and test on a backup first!

-- STEP 1: Preview what will be changed (RUN THIS FIRST)
-- This shows what changes would be made without actually making them
SELECT 
    id, 
    title, 
    date,
    'divine_worship' as field_name,
    divine_worship as original_content,
    -- Clean up various hymn title patterns
    REGEXP_REPLACE(
        REGEXP_REPLACE(
            REGEXP_REPLACE(
                REGEXP_REPLACE(divine_worship, 
                    '#([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: #123 - Title
                    '#\1', 'g'),
                'Hymn\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: Hymn 123 - Title  
                'Hymn \1', 'g'),
            'No\.\s*([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: No. 123 - Title
            'No. \1', 'g'),
        'Number\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: Number 123 - Title
        'Number \1', 'g'
    ) as cleaned_content
FROM bulletins 
WHERE divine_worship IS NOT NULL 
  AND (divine_worship LIKE '%#[0-9]%-%' OR 
       divine_worship LIKE '%Hymn [0-9]%-%' OR
       divine_worship LIKE '%No. [0-9]%-%' OR
       divine_worship LIKE '%Number [0-9]%-%')

UNION ALL

SELECT 
    id, 
    title, 
    date,
    'sabbath_school' as field_name,
    sabbath_school as original_content,
    -- Clean up various hymn title patterns
    REGEXP_REPLACE(
        REGEXP_REPLACE(
            REGEXP_REPLACE(
                REGEXP_REPLACE(sabbath_school, 
                    '#([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: #123 - Title
                    '#\1', 'g'),
                'Hymn\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: Hymn 123 - Title  
                'Hymn \1', 'g'),
            'No\.\s*([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: No. 123 - Title
            'No. \1', 'g'),
        'Number\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', -- Pattern: Number 123 - Title
        'Number \1', 'g'
    ) as cleaned_content
FROM bulletins 
WHERE sabbath_school IS NOT NULL 
  AND (sabbath_school LIKE '%#[0-9]%-%' OR 
       sabbath_school LIKE '%Hymn [0-9]%-%' OR
       sabbath_school LIKE '%No. [0-9]%-%' OR
       sabbath_school LIKE '%Number [0-9]%-%')
ORDER BY date DESC;

-- STEP 2: BACKUP YOUR DATA FIRST!
-- CREATE TABLE bulletins_backup AS SELECT * FROM bulletins;

-- STEP 3: Actually clean up divine_worship field (ONLY RUN AFTER TESTING)
/*
UPDATE bulletins 
SET divine_worship = REGEXP_REPLACE(
    REGEXP_REPLACE(
        REGEXP_REPLACE(
            REGEXP_REPLACE(divine_worship, 
                '#([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', 
                '#\1', 'g'),
            'Hymn\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', 
            'Hymn \1', 'g'),
        'No\.\s*([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', 
        'No. \1', 'g'),
    'Number\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', 
    'Number \1', 'g'
)
WHERE divine_worship IS NOT NULL 
  AND (divine_worship LIKE '%#[0-9]%-%' OR 
       divine_worship LIKE '%Hymn [0-9]%-%' OR
       divine_worship LIKE '%No. [0-9]%-%' OR
       divine_worship LIKE '%Number [0-9]%-%');
*/

-- STEP 4: Actually clean up sabbath_school field (ONLY RUN AFTER TESTING)
/*
UPDATE bulletins 
SET sabbath_school = REGEXP_REPLACE(
    REGEXP_REPLACE(
        REGEXP_REPLACE(
            REGEXP_REPLACE(sabbath_school, 
                '#([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', 
                '#\1', 'g'),
            'Hymn\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', 
            'Hymn \1', 'g'),
        'No\.\s*([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', 
        'No. \1', 'g'),
    'Number\s+([0-9]{1,3})\s*-\s*[^.!?]*?(?=\s|$|<|\.|\n)', 
    'Number \1', 'g'
)
WHERE sabbath_school IS NOT NULL 
  AND (sabbath_school LIKE '%#[0-9]%-%' OR 
       sabbath_school LIKE '%Hymn [0-9]%-%' OR
       sabbath_school LIKE '%No. [0-9]%-%' OR
       sabbath_school LIKE '%Number [0-9]%-%');
*/

-- STEP 5: Verify the cleanup worked
-- SELECT COUNT(*) FROM bulletins WHERE divine_worship LIKE '%#[0-9]%-%' OR sabbath_school LIKE '%#[0-9]%-%';