import io,csv,re,zipfile
from decimal import Decimal
from xml.etree import ElementTree as ET
import pdfplumber
from scoring import ms

def validate(rows):
 if not rows:raise ValueError('No timing rows found; scanned PDFs need verified transcription, not automatic guessing.')
 seen=set()
 for r in rows:
  if r['number'] in seen:raise ValueError('Duplicate number '+r['number']+' in one session')
  seen.add(r['number'])
  if r['raw_class'] not in list('ABCDEF'):raise ValueError('Invalid class for '+r['name'])
  if r['laps']<0:raise ValueError('Negative lap count')
  if r['status'] not in ('CLASSIFIED','DNS','DNF','DSQ','NOT CLASSIFIED'):raise ValueError('Invalid status')
 if len(rows)>1500:raise ValueError('Maximum 1500 rows per import')
 return rows

def pdf(content):
 rows=[];texts=[];stage='CLASSIFIED'
 if not content.startswith(b'%PDF-'):raise ValueError('Not a PDF file')
 with pdfplumber.open(io.BytesIO(content)) as doc:
  if len(doc.pages)>30:raise ValueError('Maximum 30 pages per timing file')
  for page in doc.pages:
   words=page.extract_words();texts.append(page.extract_text() or '')
   pos=next((w for w in words if w['text']=='Pos'),None)
   if not pos:continue
   header=sorted([w for w in words if abs(w['top']-pos['top'])<3],key=lambda w:w['x0'])
   bi=next((i for i,w in enumerate(header) if w['text']=='Best'),None)
   if bi is None:raise ValueError('Best-lap column not recognised')
   right=header[bi+1]['x1'] if header[bi+1]['text']=='Tm' else header[bi]['x1']
   lines=[]
   for w in words:
    g=next((g for g in lines if abs(g[0]-w['top'])<2),None)
    if g:g[1].append(w)
    else:lines.append([w['top'],[w]])
   for top,ww in sorted(lines):
    if top<=pos['top']+3:continue
    ww.sort(key=lambda w:w['x0']);line=' '.join(w['text'] for w in ww)
    if line.startswith('Not classified'):stage='NOT CLASSIFIED';continue
    if line.startswith('Margin of Victory'):break
    match=re.match(r'^(.*?)\s+BMW\s+([A-F])\s*(.*)$',line)
    if not match:continue
    prefix,cls,tail=match.groups();p=prefix.split();status=stage;position=None
    if p[0] in ('DNS','DNF','DSQ','DQ'):status=p.pop(0);status='DSQ' if status=='DQ' else status
    if len(p)>2 and all(x.isdigit() for x in p[:3]):position=int(p.pop(0))
    if len(p)<3 or not p[0].isdigit() or not p[1].isdigit():raise ValueError('Unrecognised timing row '+line[:90])
    number=p.pop(0);msa=p.pop(0);name=' '.join(p);parts=tail.split();laps=0;total=None
    if 'DNS' in parts:status='DNS'
    elif parts and parts[0].isdigit():
     laps=int(parts.pop(0))
     if parts and re.fullmatch(r'\d+:\d{2}\.\d{3}',parts[0]):total=ms(parts[0])
    best=[w['text'] for w in ww if abs(w['x1']-right)<8 and re.fullmatch(r'\d+:\d{2}\.\d{3}',w['text'])]
    if len(best)>1:raise ValueError('Ambiguous best lap for '+name)
    rows.append({'position':position,'number':number,'msa':msa,'name':name,'raw_class':cls,'laps':laps,'total_ms':total,'best_ms':ms(best[0]) if best else None,'second_ms':None,'status':status,'source_line':line})
 text='\n'.join(texts);d=re.search(r'(20\d\d)/(\d\d)/(\d\d)',text);l=re.search(r'Race\s*\((\d+) Laps?\)',text)
 return {'rows':validate(rows),'session':'Q' if re.search('Qualif',text,re.I) else 'R2' if 'Race 2' in text else 'R1','date':'-'.join(d.groups()) if d else '', 'scheduled_laps':int(l.group(1)) if l else 0,'source_provisional':'provisional' in text.lower()}

def xlsx(content):
 ns={'x':'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
 def xml(b):
  if b'<!DOCTYPE' in b or b'<!ENTITY' in b:raise ValueError('Unsafe XML declaration')
  return ET.fromstring(b)
 with zipfile.ZipFile(io.BytesIO(content)) as z:
  if sum(x.file_size for x in z.infolist())>30*1024*1024:raise ValueError('Workbook too large after expansion')
  if any('vbaProject' in n for n in z.namelist()):raise ValueError('Macros are not accepted')
  strings=[]
  if 'xl/sharedStrings.xml' in z.namelist():strings=[''.join(x.itertext()) for x in xml(z.read('xl/sharedStrings.xml')).findall('x:si',ns)]
  files=sorted(n for n in z.namelist() if re.fullmatch(r'xl/worksheets/sheet\d+\.xml',n))
  if not files:raise ValueError('No worksheet found')
  out=[]
  for row in xml(z.read(files[0])).findall('.//x:sheetData/x:row',ns):
   vals={}
   for c in row.findall('x:c',ns):
    col=0
    for ch in re.sub(r'\d','',c.attrib.get('r','A')):col=col*26+ord(ch)-64
    v=c.find('x:v',ns);v=v.text if v is not None else '';typ=c.attrib.get('t')
    if typ=='s':v=strings[int(v)] if v else ''
    elif typ=='inlineStr':v=''.join(c.find('x:is',ns).itertext())
    elif c.find('x:f',ns) is not None and not v:raise ValueError('Export calculated values instead of uncached formulas')
    vals[col-1]=v
   out.append([vals.get(i,'') for i in range(max(vals,default=-1)+1)])
   if len(out)>1550:raise ValueError('Too many worksheet rows')
  return out

def parse(content,name):
 if len(content)>12*1024*1024:raise ValueError('Timing file limit: 12 MB')
 ext=name.rsplit('.',1)[-1].lower()
 if ext=='pdf':return pdf(content)
 if ext=='xlsx':table=xlsx(content)
 elif ext=='csv':
  text=content.decode('utf-8-sig')
  try:dialect=csv.Sniffer().sniff(text[:8192],delimiters=',;\t')
  except csv.Error:dialect=csv.excel
  table=list(csv.reader(io.StringIO(text),dialect))
 else:raise ValueError('Use PDF, CSV or .xlsx. Save old .xls files as .xlsx first.')
 norm=lambda s:re.sub(r'[^a-z0-9]','',str(s).lower())
 hi=next((i for i,row in enumerate(table[:25]) if any(norm(v) in ('no','number','racenumber','carnumber') for v in row) and any(norm(v) in ('name','driver','drivername') for v in row)),None)
 if hi is None:raise ValueError('Required Number and Name columns not found')
 rows=[]
 for values in table[hi+1:]:
  row={norm(k):v for k,v in zip(table[hi],values)}
  def get(*names):return next((row[norm(k)] for k in names if str(row.get(norm(k),'' )).strip()),'')
  num=str(get('No','Number','Race Number','Car Number')).strip();name=str(get('Name','Driver','Driver Name')).strip()
  if not num and not name:continue
  if not num or not name:raise ValueError('Missing driver name or number')
  if num.endswith('.0'):num=num[:-2]
  def tm(*keys):
   v=get(*keys)
   if ext=='xlsx' and v and ':' not in str(v):
    try:
     n=Decimal(str(v))
     if 0<n<1:return int(n*86400000+Decimal('.5'))
    except Exception:pass
   return ms(v)
  pos=str(get('Pos','Position','Overall Pos'));status=str(get('Status','Timing Status') or ('DNS' if pos=='DNS' else 'CLASSIFIED')).upper();status='DSQ' if status=='DQ' else status
  if status=='CLASSIFIED' and not pos.isdigit():raise ValueError('Classified row needs official position for '+name)
  rows.append({'position':int(pos) if pos.isdigit() else None,'number':num,'name':name,'msa':str(get('MSA#','MSA')), 'raw_class':str(get('Class','Class Code')).replace('BMW','').strip().upper(),'laps':int(float(get('Laps') or 0)),'best_ms':tm('Best Tm','Best Lap','Fastest Lap'),'total_ms':tm('Total Tm','Total Time'),'second_ms':tm('2nd Best'),'status':status,'source_line':repr(values)})
 return {'rows':validate(rows),'session':None,'date':'','scheduled_laps':0,'source_provisional':True}
