-- SQL queries to find bulletins with manually added hymn titles
-- These would show up as patterns like "#319 - Amazing Grace" in the content

-- Search for hymn patterns with manually added titles in divine_worship
SELECT id, title, date, 
       divine_worship
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]%-%'
  )
ORDER BY date DESC;

-- Search for hymn patterns with manually added titles in sabbath_school
SELECT id, title, date, 
       sabbath_school
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]%-%'
  )
ORDER BY date DESC;

-- More specific patterns - looking for common hymn title patterns
SELECT id, title, date, divine_worship, sabbath_school
FROM bulletins 
WHERE (divine_worship LIKE '%#[0-9][0-9][0-9]%-%' OR 
       sabbath_school LIKE '%#[0-9][0-9][0-9]%-%' OR
       divine_worship LIKE '%Hymn [0-9][0-9][0-9]%-%' OR 
       sabbath_school LIKE '%Hymn [0-9][0-9][0-9]%-%')
ORDER BY date DESC
LIMIT 20;

-- Count how many bulletins might have manual hymn titles
SELECT 
    COUNT(*) as total_bulletins_with_manual_titles,
    COUNT(CASE WHEN divine_worship LIKE '%#[0-9]%-%' OR divine_worship LIKE '%Hymn [0-9]%-%' THEN 1 END) as divine_worship_with_titles,
    COUNT(CASE WHEN sabbath_school LIKE '%#[0-9]%-%' OR sabbath_school LIKE '%Hymn [0-9]%-%' THEN 1 END) as sabbath_school_with_titles
FROM bulletins 
WHERE divine_worship IS NOT NULL OR sabbath_school IS NOT NULL;