-- remotes/honeybot/queries/intel_hostile_dictionary.sql

WITH KnownHostileIPs AS (
    -- Stage 1: The Trap (Identify the bad actors)
    -- We cast a wide net using the most common script-kiddie tells.
    SELECT DISTINCT l.ip_id
    FROM daily_logs l
    JOIN paths p ON l.path_id = p.id
    WHERE l.status = 404
      AND l.date >= date('now', '-30 days') -- Look back further for hostile patterns
      AND (
          p.value LIKE '%.php%' OR 
          p.value LIKE '%wp-%' OR 
          p.value LIKE '%.env%' OR 
          p.value LIKE '%.git%' OR
          p.value LIKE '%/cgi-bin/%' OR
          p.value LIKE '%/admin%'
      )
)
-- Stage 2: The Interrogation (What else did they ask for?)
-- We query all 404s generated by those specific IPs, 
-- EXCLUDING the known trap strings so we only see the *new* garbage.
SELECT 
    p.value as hostile_path_discovery, 
    SUM(l.count) as total_probes,
    COUNT(DISTINCT l.ip_id) as unique_hostile_ips
FROM daily_logs l
JOIN paths p ON l.path_id = p.id
JOIN KnownHostileIPs k ON l.ip_id = k.ip_id
WHERE l.status = 404
  AND l.date >= date('now', '-30 days')
  -- Filter out the stuff we already know is bad
  AND p.value NOT LIKE '%.php%' 
  AND p.value NOT LIKE '%wp-%' 
  AND p.value NOT LIKE '%.env%' 
  AND p.value NOT LIKE '%.git%'
  AND p.value NOT LIKE '%/cgi-bin/%'
  AND p.value NOT LIKE '%/admin%'
GROUP BY p.id
ORDER BY total_probes DESC
LIMIT 50;