-- Cleanup Test Data from Production Database
-- This script identifies and removes test data that was accidentally stored in production

-- First, let's see what we're about to delete
SELECT 'Test data to be removed:' as info;

SELECT COUNT(*) as test_entries_count,
       STRING_AGG(DISTINCT LEFT(content, 30), ', ' ORDER BY LEFT(content, 30)) as sample_content
FROM memories 
WHERE 
    -- Test patterns from unit tests
    content LIKE 'Batch test content%' OR
    content LIKE 'Memory #%' OR
    content LIKE 'Test content%' OR
    content LIKE 'Content with%tier' OR
    content LIKE 'Content for%test%' OR
    content LIKE 'Normal content%' OR
    content LIKE 'Concurrent%test%' OR
    content LIKE 'Recovery test%' OR
    
    -- Simple test entries
    content = 'Python code' OR
    content = 'Rust code' OR
    content = 'Python tutorial' OR
    content = 'No tags content' OR
    content = 'Important data starting in working tier' OR
    
    -- E2E test patterns
    content LIKE 'User''s important note without any AI processing' OR
    content LIKE 'Error: Connection timeout at line 42' OR
    content LIKE 'Just the content, nothing else' OR
    content LIKE 'The complete implementation of the authentication system%' OR
    content LIKE 'Pre-interruption content' OR
    content LIKE 'Recovery%content%' OR
    content LIKE 'Initial content' OR
    content LIKE 'Duplicate content test' OR
    
    -- Test tags patterns
    (tags && ARRAY['test', 'integration', 'mcp', 'stress-test', 'recovery-test']) OR
    EXISTS (
        SELECT 1 FROM unnest(tags) AS tag 
        WHERE tag LIKE 'batch-%' OR 
              tag LIKE 'concurrent-%' OR
              tag LIKE 'tag%' OR
              tag = 'single' OR
              tag = 'partial'
    );

-- Show breakdown by pattern
SELECT 'Breakdown by pattern:' as info;

SELECT 
    CASE 
        WHEN content LIKE 'Batch test%' THEN 'Batch tests'
        WHEN content LIKE 'Memory #%' THEN 'Memory numbered tests'  
        WHEN content LIKE 'Test content%' THEN 'Test content'
        WHEN content LIKE 'Content with%' THEN 'Content with tests'
        WHEN content LIKE '%test%' THEN 'Other test content'
        WHEN content IN ('Python code', 'Rust code', 'Python tutorial', 'No tags content') THEN 'Simple test entries'
        ELSE 'Other patterns'
    END as pattern_type,
    COUNT(*) as count
FROM memories
WHERE 
    content LIKE 'Batch test%' OR
    content LIKE 'Memory #%' OR
    content LIKE '%test%' OR
    content IN ('Python code', 'Rust code', 'Python tutorial', 'No tags content')
GROUP BY pattern_type
ORDER BY count DESC;

-- Create backup of data before deletion (just the IDs for reference)
SELECT 'Creating backup list of IDs to be deleted...' as info;

-- Actual deletion (commented out for safety - uncomment to execute)
-- DELETE FROM memories 
-- WHERE 
--     -- Test patterns from unit tests
--     content LIKE 'Batch test content%' OR
--     content LIKE 'Memory #%' OR
--     content LIKE 'Test content%' OR
--     content LIKE 'Content with%tier' OR
--     content LIKE 'Content for%test%' OR
--     content LIKE 'Normal content%' OR
--     content LIKE 'Concurrent%test%' OR
--     content LIKE 'Recovery test%' OR
--     
--     -- Simple test entries
--     content = 'Python code' OR
--     content = 'Rust code' OR
--     content = 'Python tutorial' OR
--     content = 'No tags content' OR
--     content = 'Important data starting in working tier' OR
--     
--     -- E2E test patterns
--     content LIKE 'User''s important note without any AI processing' OR
--     content LIKE 'Error: Connection timeout at line 42' OR
--     content LIKE 'Just the content, nothing else' OR
--     content LIKE 'The complete implementation of the authentication system%' OR
--     content LIKE 'Pre-interruption content' OR
--     content LIKE 'Recovery%content%' OR
--     content LIKE 'Initial content' OR
--     content LIKE 'Duplicate content test' OR
--     
--     -- Test tags patterns
--     (tags && ARRAY['test', 'integration', 'mcp', 'stress-test', 'recovery-test']) OR
--     EXISTS (
--         SELECT 1 FROM unnest(tags) AS tag 
--         WHERE tag LIKE 'batch-%' OR 
--               tag LIKE 'concurrent-%' OR
--               tag LIKE 'tag%' OR
--               tag = 'single' OR
--               tag = 'partial'
--     )
-- RETURNING id, LEFT(content, 50) as deleted_content;

SELECT 'To execute deletion, uncomment the DELETE statement above' as warning;