import pandas as pd,re,glob,os,json,collections
files=[f'/mnt/data/files/2024-{m:02d}'+ ('(1).csv' if m in range(2,9) or m==10 else '(2).csv' if m==1 else '.csv') for m in range(1,13)]
files[8]='/mnt/data/files/All_THP_Violations_2024_Preliminary_9(1).csv'
# Note: months 3-8 use (1); 9 preliminary.
assert all(os.path.exists(f) for f in files),files
pat=re.compile(r'(?<![A-Z0-9])(?:I\s*[- ]?\s*H?\s*[- ]?|IH\s*[- ]?)(35[EW]?|10|20|45|30|40|27|37|2|14|44|69|49|635|610|820|410|110|169|345|369)(?!\d)|(?<![A-Z0-9])(?:US|U\.S\.)\s*[- ]?\s*(\d{1,3})(?!\d)|(?<![A-Z0-9])(?:SH|TX|STATE\s+HIGHWAY)\s*[- ]?\s*(\d{1,3})(?!\d)|(?<![A-Z0-9])(?:FM|F\.?M\.?)\s*[- ]?\s*(\d{1,4})(?!\d)',re.I)
def routes(s):
 out=[]
 for m in pat.finditer(s.upper()):
  a,b,c,d=m.groups()
  r=('I-'+('35' if a.startswith('35') else a)) if a else 'US '+b if b else 'SH '+c if c else 'FM '+d
  if r not in out:out.append(r)
 return out
seen=set(); totals=collections.Counter(); excluded=collections.Counter(); rows=collections.Counter(); examples=[]
for path in files:
 month=os.path.basename(path)
 for chunk in pd.read_csv(path,usecols=['Ticket_Number','Violation_Type','Route'],dtype=str,chunksize=70000,keep_default_na=False,low_memory=False):
  rows['all']+=len(chunk)
  rows['citation_rows']+=(chunk.Violation_Type.str.strip().str.lower()=='citation').sum()
  rows['warning_rows']+=(chunk.Violation_Type.str.strip().str.lower()=='warning').sum()
  rows['blank_rows']+=(chunk.Violation_Type.str.strip()=='').sum()
  rows['other_non_citation_warning']+=((~chunk.Violation_Type.str.strip().str.lower().isin(['citation','warning']))).sum()
  c=chunk[chunk.Violation_Type.str.strip().str.lower()=='citation']
  for ticket,route in zip(c.Ticket_Number,c.Route):
   if not ticket.strip() or ticket in seen:continue
   seen.add(ticket)
   rr=routes(route)
   if not rr:excluded['no_recognized_route']+=1
   elif len(rr)>1:
    excluded['multiple_routes']+=1
    if len(examples)<12:examples.append([ticket,route,rr])
   else:totals[rr[0]]+=1
 print(month, 'unique cumulative',len(seen),flush=True)
print('ROWS',dict(rows));print('TICKETS',len(seen));print('EXCLUDED',dict(excluded));print('TOP',totals.most_common(25));print('EXAMPLES',examples)
with open('/mnt/data/tpr_2024_route_verification.json','w') as f:json.dump({'rows':{k:int(v) for k,v in rows.items()},'unique_tickets':len(seen),'excluded':dict(excluded),'ranking':totals.most_common(),'examples':examples,'files':[os.path.basename(x) for x in files]},f,indent=2)