-- ============================================================================
-- QUICK VERIFICATION SCRIPT - Run After Production Deployment
-- ============================================================================
-- Purpose: Verify that the payment recovery backfill was successful
-- Run this after executing PRODUCTION_payment_recovery_backfill.sql
-- ============================================================================

SELECT '========================================' AS info
UNION ALL SELECT 'PAYMENT RECOVERY VERIFICATION'
UNION ALL SELECT '========================================';

-- 1. Check if columns exist
SELECT 
    'Column Check' AS test_name,
    CASE 
        WHEN COUNT(*) >= 12 THEN '✅ PASS'
        ELSE '❌ FAIL'
    END AS status,
    COUNT(*) AS columns_found,
    'Expected: 12 new columns' AS expected
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'uid_registration_recoveries'
    AND COLUMN_NAME IN (
        'new_payment_id', 'payment_completed_at', 'payment_method',
        'days_to_convert', 'conversion_type', 'recovery_channel',
        'follow_up_count', 'last_follow_up_at', 'recovery_notes',
        'is_verified', 'verified_by', 'verified_at'
    );

-- 2. Check if data was matched
SELECT 
    'Data Matching' AS test_name,
    CASE 
        WHEN SUM(matched) > 0 THEN '✅ PASS'
        ELSE '⚠️  WARNING - No matches found'
    END AS status,
    SUM(matched) AS total_matched,
    'Check if users have paid after recovery email' AS note
FROM (
    SELECT COUNT(*) AS matched FROM uid_registration_recoveries WHERE new_payment_id IS NOT NULL
    UNION ALL
    SELECT COUNT(*) FROM membership_renewal_recoveries WHERE new_payment_id IS NOT NULL
    UNION ALL
    SELECT COUNT(*) FROM certificate_payment_recoveries WHERE new_payment_id IS NOT NULL
) data;

-- 3. Check if views were created
SELECT 
    'Views Created' AS test_name,
    CASE 
        WHEN COUNT(*) = 2 THEN '✅ PASS'
        ELSE '❌ FAIL'
    END AS status,
    COUNT(*) AS views_found,
    'Expected: 2 views' AS expected
FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME IN ('v_payment_recovery_dashboard', 'v_high_priority_pending');

-- 4. Test dashboard view
SELECT 
    'Dashboard View' AS test_name,
    CASE 
        WHEN COUNT(*) > 0 THEN '✅ PASS'
        ELSE '❌ FAIL'
    END AS status,
    COUNT(*) AS rows_returned,
    'Dashboard view is working' AS note
FROM v_payment_recovery_dashboard;

-- 5. Quick Summary
SELECT '========================================' AS info
UNION ALL SELECT 'QUICK SUMMARY'
UNION ALL SELECT '========================================';

SELECT * FROM v_payment_recovery_dashboard;

-- 6. Sample matched records
SELECT '========================================' AS info
UNION ALL SELECT 'SAMPLE MATCHED RECORDS (First 5)'
UNION ALL SELECT '========================================';

SELECT 
    id,
    user_id,
    new_payment_id,
    amount,
    conversion_type,
    days_to_convert,
    DATE_FORMAT(payment_completed_at, '%Y-%m-%d') AS payment_date
FROM uid_registration_recoveries
WHERE new_payment_id IS NOT NULL
LIMIT 5;

-- 7. High priority pending
SELECT '========================================' AS info
UNION ALL SELECT 'HIGH PRIORITY PENDING (Sample)'
UNION ALL SELECT '========================================';

SELECT * FROM v_high_priority_pending LIMIT 5;

SELECT '========================================' AS info
UNION ALL SELECT '✅ VERIFICATION COMPLETE'
UNION ALL SELECT '========================================';
