#!/usr/bin/env python3
"""Local synthetic identity hunt. No cloud credentials, network, or persistent DB."""
import json
import sqlite3

QUERY = """
WITH unique_events AS (
  SELECT DISTINCT event_id, tenant, user_id, ip, time_s, kind FROM events
), candidates AS (
  SELECT s.event_id AS sign_in_id, s.tenant, s.user_id, s.time_s,
         COUNT(f.event_id) AS failures
  FROM unique_events s JOIN unique_events f
    ON f.tenant=s.tenant AND f.user_id=s.user_id AND f.ip=s.ip
   AND f.kind='failure' AND f.time_s >= s.time_s-600
   AND f.time_s < s.time_s
  WHERE s.kind='success'
  GROUP BY s.event_id, s.tenant, s.user_id, s.time_s
  HAVING COUNT(f.event_id) >= 3
)
SELECT DISTINCT c.tenant, c.user_id, c.sign_in_id, p.event_id AS grant_id
FROM candidates c JOIN unique_events p
  ON p.tenant=c.tenant AND p.user_id=c.user_id
 AND p.kind='role_grant' AND p.time_s >= c.time_s
 AND p.time_s <= c.time_s+300
ORDER BY c.tenant, c.user_id, c.sign_in_id, p.event_id
"""


def events(case, tenant='tenant-a', failures=3, grant_time=250):
    rows = [(f'{case}-f{i}', tenant, case, '192.0.2.10', 100+i, 'failure')
            for i in range(failures)]
    rows += [(f'{case}-s', tenant, case, '192.0.2.10', 200, 'success'),
             (f'{case}-g', tenant, case, '192.0.2.10', grant_time, 'role_grant')]
    return rows


def hunt(rows):
    with sqlite3.connect(':memory:') as conn:
        conn.execute('CREATE TABLE events(event_id TEXT, tenant TEXT, user_id TEXT, ip TEXT, time_s INTEGER, kind TEXT)')
        conn.executemany('INSERT INTO events VALUES (?, ?, ?, ?, ?, ?)', rows)
        return conn.execute(QUERY).fetchall()


def main():
    cases = {
        'positive_sequence': (events('positive'), 1),
        'ordinary_admin': (events('ordinary', failures=0), 0),
        'two_failures': (events('two', failures=2), 0),
        'late_grant': (events('late', grant_time=501), 0),
        'boundary_grant': (events('boundary', grant_time=500), 1),
    }
    cross = events('same-id')
    cross[-1] = (cross[-1][0], 'tenant-b', *cross[-1][2:])
    cases['cross_tenant_join'] = (cross, 0)
    duplicates = events('duplicate', failures=1)
    duplicates.extend([duplicates[0], duplicates[0]])
    cases['duplicate_delivery'] = (duplicates, 0)
    missing = events('missing')
    missing = [r for r in missing if r[-1] != 'success']
    cases['missing_success_telemetry'] = (missing, 0)
    results = {}
    combined = []
    for name, (rows, expected) in cases.items():
        found = hunt(rows)
        assert len(found) == expected, (name, found)
        results[name] = {'expected_matches': expected, 'observed_matches': len(found)}
        combined.extend(rows)
    assert len(hunt(combined)) == 2
    print(json.dumps({'fixture_rows': len(combined), 'cases': results,
                      'combined_matches': hunt(combined),
                      'limitation': 'Normalized synthetic records; no Entra or Sentinel detection tested.'}, indent=2))


if __name__ == '__main__':
    main()