-- AGGRESSIVE CLEANUP for 71M+ odds records
-- This will clean up orphaned and old odds data

-- Step 1: Check what we have
SELECT 
    'Total Odds' as metric,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100 / (1024 * 1024), 2) as size_mb
FROM odds
UNION ALL
SELECT 
    'Odds with Fixtures',
    COUNT(DISTINCT o.fixture_id),
    ROUND(COUNT(DISTINCT o.fixture_id) * 100 / (1024 * 1024), 2)
FROM odds o
INNER JOIN fixtures f ON o.fixture_id = f.id
UNION ALL
SELECT 
    'Orphaned Odds (no fixture)',
    COUNT(*),
    ROUND(COUNT(*) * 100 / (1024 * 1024), 2)
FROM odds o
WHERE NOT EXISTS (SELECT 1 FROM fixtures f WHERE f.id = o.fixture_id);

-- Step 2: Delete orphaned odds (fixtures that don't exist)
-- Run this multiple times until 0 rows affected
DELETE FROM odds 
WHERE NOT EXISTS (
    SELECT 1 FROM fixtures f WHERE f.id = odds.fixture_id
)
LIMIT 1000000;

-- Step 3: Delete odds for finished fixtures older than 3 days (regardless of bets)
-- This is safe because bets are already settled
DELETE FROM odds
WHERE fixture_id IN (
    SELECT id FROM fixtures 
    WHERE status IN ('FT', 'AET', 'PEN', 'CANC', 'ABD', 'PST')
    AND date < DATE_SUB(NOW(), INTERVAL 3 DAY)
    LIMIT 10000
);

-- Step 4: Delete odds for old scheduled fixtures that never started (likely cancelled)
DELETE FROM odds
WHERE fixture_id IN (
    SELECT id FROM fixtures
    WHERE status IN ('NS', 'TBD', 'scheduled')
    AND date < DATE_SUB(NOW(), INTERVAL 7 DAY)
    LIMIT 10000
);

-- Step 5: Check final size
SELECT 
    COUNT(*) as total_odds_after,
    ROUND(COUNT(*) * 100 / (1024 * 1024), 2) as estimated_mb_after
FROM odds;

-- Step 6: Optimize table to reclaim disk space
OPTIMIZE TABLE odds;
