-- host_intent_cache operational commands
-- Replace IDs/ranges/profile names with real values.

-- 1) Invalidate cache for one host (hard purge)
DELETE FROM host_intent_cache
WHERE host_id = 12345;

-- 2) Invalidate cache for host list (batch)
DELETE FROM host_intent_cache
WHERE host_id IN (12345, 67890, 13579);

-- 3) Invalidate cache for host range (rollout chunks)
DELETE FROM host_intent_cache
WHERE host_id BETWEEN 10000 AND 19999;

-- 4) Soft invalidate (force expiry, keep row)
UPDATE host_intent_cache
SET updated_at = '1970-01-01 00:00:00'
WHERE host_id = 12345;

-- 5) Invalidate by profile (e.g. blog heuristic changed)
DELETE FROM host_intent_cache
WHERE profile = 'blog';

-- 6) Housekeeping: delete stale rows (adjust TTL window)
DELETE FROM host_intent_cache
WHERE updated_at < (NOW() - INTERVAL 30 DAY);

-- 7a) Debug single host cache entry
SELECT host_id, profile, confidence, updated_at
FROM host_intent_cache
WHERE host_id = 12345;

-- 7b) Debug distribution (recent cache writes)
SELECT profile, COUNT(*) AS n
FROM host_intent_cache
WHERE updated_at >= (NOW() - INTERVAL 7 DAY)
GROUP BY profile
ORDER BY n DESC;
