-- Verification Script for Intelligence System Migration
-- Run this after applying migrations to verify success

SELECT 'Starting Intelligence Migration Verification...' as Status;

-- Check if tables exist
SELECT 
    CASE 
        WHEN COUNT(*) = 2 THEN '✅ All intelligence tables created successfully'
        ELSE '❌ Missing intelligence tables'
    END as TableStatus
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
AND table_name IN ('intelligence_cache', 'prediction_accuracy');

-- Check foreign key constraints
SELECT 
    CASE 
        WHEN COUNT(*) >= 2 THEN '✅ Foreign key constraints created successfully'
        ELSE '❌ Missing foreign key constraints'
    END as ConstraintStatus
FROM information_schema.key_column_usage 
WHERE table_schema = DATABASE()
AND table_name IN ('intelligence_cache', 'prediction_accuracy')
AND referenced_table_name IS NOT NULL;

-- Check indexes for intelligence_cache
SELECT 
    'intelligence_cache' as table_name,
    COUNT(*) as index_count,
    CASE 
        WHEN COUNT(*) >= 4 THEN '✅ All indexes created'
        ELSE '⚠️ Some indexes missing'
    END as IndexStatus
FROM information_schema.statistics 
WHERE table_schema = DATABASE()
AND table_name = 'intelligence_cache';

-- Check indexes for prediction_accuracy  
SELECT 
    'prediction_accuracy' as table_name,
    COUNT(*) as index_count,
    CASE 
        WHEN COUNT(*) >= 5 THEN '✅ All indexes created'
        ELSE '⚠️ Some indexes missing'
    END as IndexStatus
FROM information_schema.statistics 
WHERE table_schema = DATABASE()
AND table_name = 'prediction_accuracy';

-- Test basic functionality (only if organizations table has data)
INSERT INTO intelligence_cache (cache_key, cache_value, organization_id, cache_type) 
SELECT 'test_migration_key', '{"test": "migration_success"}', id, 'analytical'
FROM organizations 
LIMIT 1;

-- Verify insert worked
SELECT 
    CASE 
        WHEN COUNT(*) > 0 THEN '✅ Intelligence cache table working correctly'
        ELSE '❌ Intelligence cache table not functioning'
    END as FunctionalityTest
FROM intelligence_cache 
WHERE cache_key = 'test_migration_key';

-- Cleanup test data
DELETE FROM intelligence_cache WHERE cache_key = 'test_migration_key';

-- Show table structures for verification
SELECT '--- INTELLIGENCE_CACHE TABLE STRUCTURE ---' as Info;
DESCRIBE intelligence_cache;

SELECT '--- PREDICTION_ACCURACY TABLE STRUCTURE ---' as Info;  
DESCRIBE prediction_accuracy;

-- Show foreign key relationships
SELECT '--- FOREIGN KEY CONSTRAINTS ---' as Info;
SELECT 
    CONSTRAINT_NAME, 
    TABLE_NAME, 
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE 
    TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME IN ('intelligence_cache', 'prediction_accuracy') 
    AND REFERENCED_TABLE_NAME IS NOT NULL;

SELECT '✅ Intelligence Migration Verification Complete' as Status;
SELECT 'Your Proactive Intelligence System is ready to use advanced caching!' as Message;