-- Simple cleanup: Strip everything after hymn numbers, keep just the number
-- This will clean #415 - Christ the Lord, All Power Possessing "..." down to just #415

-- STEP 1: Preview what changes will be made (run this first to see what gets cleaned)
SELECT 
    id, 
    title, 
    date,
    'divine_worship' as field,
    divine_worship as before_cleanup,
    REGEXP_REPLACE(
        REGEXP_REPLACE(
            REGEXP_REPLACE(divine_worship, 
                '#([0-9]{1,3})\s*-[^#\n]*', '#\1', 'g'     -- #123 - anything -> #123
            ),
            'Hymn\s+([0-9]{1,3})\s*-[^#\n]*', '#\1', 'g'   -- Hymn 123 - anything -> #123  
        ),
        'No\.\s*([0-9]{1,3})\s*-[^#\n]*', '#\1', 'g'       -- No. 123 - anything -> #123
    ) as after_cleanup
FROM bulletins 
WHERE divine_worship ~ '(#|Hymn\s+|No\.\s*)[0-9]{1,3}\s*-'

UNION ALL

SELECT 
    id, 
    title, 
    date,
    'sabbath_school' as field,
    sabbath_school as before_cleanup,
    REGEXP_REPLACE(
        REGEXP_REPLACE(
            REGEXP_REPLACE(sabbath_school, 
                '#([0-9]{1,3})\s*-[^#\n]*', '#\1', 'g'     -- #123 - anything -> #123
            ),
            'Hymn\s+([0-9]{1,3})\s*-[^#\n]*', '#\1', 'g'   -- Hymn 123 - anything -> #123  
        ),
        'No\.\s*([0-9]{1,3})\s*-[^#\n]*', '#\1', 'g'       -- No. 123 - anything -> #123
    ) as after_cleanup
FROM bulletins 
WHERE sabbath_school ~ '(#|Hymn\s+|No\.\s*)[0-9]{1,3}\s*-'
ORDER BY date DESC;

-- STEP 2: Create backup before running cleanup
-- CREATE TABLE bulletins_backup AS SELECT * FROM bulletins;

-- STEP 3: Actually do the cleanup (uncomment after reviewing preview)
/*
UPDATE bulletins 
SET divine_worship = REGEXP_REPLACE(
    REGEXP_REPLACE(
        REGEXP_REPLACE(divine_worship, 
            '#([0-9]{1,3})\s*-[^#\n]*', '#\1', 'g'
        ),
        'Hymn\s+([0-9]{1,3})\s*-[^#\n]*', '#\1', 'g'
    ),
    'No\.\s*([0-9]{1,3})\s*-[^#\n]*', '#\1', 'g'
)
WHERE divine_worship ~ '(#|Hymn\s+|No\.\s*)[0-9]{1,3}\s*-';

UPDATE bulletins 
SET sabbath_school = REGEXP_REPLACE(
    REGEXP_REPLACE(
        REGEXP_REPLACE(sabbath_school, 
            '#([0-9]{1,3})\s*-[^#\n]*', '#\1', 'g'
        ),
        'Hymn\s+([0-9]{1,3})\s*-[^#\n]*', '#\1', 'g'
    ),
    'No\.\s*([0-9]{1,3})\s*-[^#\n]*', '#\1', 'g'
)
WHERE sabbath_school ~ '(#|Hymn\s+|No\.\s*)[0-9]{1,3}\s*-';
*/

-- STEP 4: Verify cleanup worked
-- SELECT COUNT(*) FROM bulletins WHERE divine_worship ~ '#[0-9]{1,3}\s*-' OR sabbath_school ~ '#[0-9]{1,3}\s*-';