"""Bounded, non-executing CSV/XLSX accounting intake with explicit record types.

Rows retain file and row provenance. Unknown/malformed rows fail as a whole;
missing numeric values are never silently converted to zero.
"""
import csv
from datetime import date, datetime
from decimal import Decimal, InvalidOperation
from hashlib import sha256
from io import BytesIO, StringIO
from pathlib import Path
import math
import tempfile
import zipfile

MAX_ROWS=2500
MAX_COLUMNS=80
MAX_CELL=4096
KINDS={'invoice','payment','credit','quote','pricing','invoice_line','conversion','return','payment_allocation'}
ALIASES={'record_type':'recordType','invoice_number':'invoiceNumber','invoice_date':'invoiceDate',
 'order_date':'orderDate','po':'poNumber','po_number':'poNumber','job_address':'jobAddress',
 'invoice_total':'invoiceTotal','payment_date':'paymentDate','reference_number':'referenceNumber',
 'applied_invoices':'appliedInvoices','credit_number':'creditNumber','credit_date':'creditDate',
 'related_invoice_number':'relatedInvoiceNumber','quote_number':'quoteNumber','quote_date':'quoteDate',
 'valid_from':'validFrom','valid_to':'validTo','unit_price':'unitPrice','line_number':'lineNumber',
 'source_reference':'sourceReference','source_type':'sourceType','source_date':'sourceDate',
 'baseline_id':'baselineId','payment_status':'paymentStatus','payment_reference':'paymentReference','return_id':'returnId','expected_credit':'expectedCredit','receipt_reference':'receiptReference','record_id':'recordId','reference_ids':'referenceIds','approval_status':'approvalStatus','date_basis':'dateBasis','from_unit':'fromUnit','to_unit':'toUnit','units_per_reference':'unitsPerReference'}
REQUIRED={'payment_allocation':('paymentReference','invoiceNumber','amount','sourceReference'),
 'return':('returnId','relatedInvoiceNumber','vendor','sku','quantity','unit','sourceReference','approvalStatus'),
 'invoice_line':('invoiceNumber','lineNumber','sku','quantity','unit','unitPrice','vendor','sourceReference','referenceIds'),
 'conversion':('sku','vendor','fromUnit','toUnit','unitsPerReference','approvalStatus','sourceReference'),
 'invoice':('invoiceNumber','invoiceDate','invoiceTotal'),
 'payment':('referenceNumber','paymentDate','amount','paymentStatus'),
 'credit':('creditNumber','creditDate','amount','relatedInvoiceNumber'),
 'quote':('quoteNumber','quoteDate'),
 'pricing':('baselineId','sourceType','sourceDate','validFrom','validTo','sku','unit','unitPrice')}
DATE_FIELDS={'invoiceDate','orderDate','paymentDate','creditDate','quoteDate','validFrom','validTo','sourceDate'}
NUMBER_FIELDS={'invoiceTotal','amount','quantity','unitPrice','unitsPerReference','expectedCredit'}

def table(blob,extension):
 if extension=='.csv':
  text=blob.decode('utf-8-sig',errors='strict')
  if '\x00' in text:raise ValueError('CSV contains binary data.')
  rows=[]
  try:
   for row in csv.reader(StringIO(text),strict=True):
    rows.append(row)
    if len(rows)>MAX_ROWS+1:raise ValueError('At most 2500 data rows are supported.')
  except csv.Error:
   raise ValueError('CSV structure or cell size is unsupported.') from None
 else:
  from tools.pipeline.xlsx_reader import load_workbook
  with zipfile.ZipFile(BytesIO(blob)) as z:
   members=z.infolist()
   if len({i.filename for i in members})!=len(members):raise ValueError('Duplicate workbook members are not accepted.')
   if len(members)>200 or sum(i.file_size for i in members)>12*1024*1024:
    raise ValueError('Workbook exceeds the expanded size or member limit.')
   if any(i.filename.lower().endswith(('vbaproject.bin',)) or 'externallinks/' in i.filename.lower() for i in members):
    raise ValueError('Macros and external links are not accepted.')
   # Bound sparse row/column coordinates before the generic XLSX reader allocates lists.
   import xml.etree.ElementTree as ET
   from tools.pipeline.xlsx_reader import _column
   for member in members:
    if member.filename.startswith('xl/worksheets/') and member.filename.endswith('.xml'):
     xml=z.read(member)
     if b'<!DOCTYPE' in xml or b'<!ENTITY' in xml:raise ValueError('Unsupported XML declaration.')
     document=ET.fromstring(xml)
     ns={'s':'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
     for r in document.findall('.//s:row',ns):
      if not 1<=int(r.get('r','0'))<=MAX_ROWS+1:raise ValueError('Worksheet row coordinate exceeds the intake limit.')
      for c in r.findall('s:c',ns):
       if _column(c.get('r',''))>=MAX_COLUMNS:raise ValueError('Worksheet column coordinate exceeds the intake limit.')
  with tempfile.NamedTemporaryFile(suffix='.xlsx') as f:
   f.write(blob);f.flush();book=load_workbook(f.name)
  if len(book.worksheets)!=1:raise ValueError('Upload one worksheet at a time for explicit column mapping.')
  rows=list(book.worksheets[0].iter_rows(values_only=True))
 if not rows or len(rows)>MAX_ROWS+1:raise ValueError('Use a header and at most 2500 data rows.')
 if any(len(r)>MAX_COLUMNS for r in rows):raise ValueError('At most 80 columns are supported.')
 logical_characters=0
 for row in rows:
  for value in row:
   size=len(str(value)) if value is not None else 0
   if size>MAX_CELL:raise ValueError('A spreadsheet cell exceeds the 4096-character limit.')
   logical_characters+=size
  if logical_characters>8*1024*1024:raise ValueError('Expanded cell values exceed the 8 MiB logical-data limit.')
 return rows

def normalize(rows,source):
 header=[ALIASES.get(str(v or '').strip(),str(v or '').strip()) for v in rows[0]]
 if not all(header) or len(header)!=len(set(header)) or 'recordType' not in header:
  raise ValueError('Unique named columns including recordType are required; use the accounting template.')
 records=[]
 for line,values in enumerate(rows[1:],2):
  if not any(v is not None and str(v).strip() for v in values):continue
  if len(values)>len(header):raise ValueError(f'Row {line} exceeds its header width.')
  row={}
  for key,value in zip(header,values):
   if isinstance(value,bool):raise ValueError(f'Row {line}: boolean cells require explicit text values.')
   if isinstance(value,datetime):value=value.date().isoformat()
   value=str(value if value is not None else '').strip()
   if len(value)>MAX_CELL or '\x00' in value:raise ValueError(f'Row {line}: oversized or binary cell.')
   row[key]=value
  kind=row.get('recordType','').lower()
  if kind not in KINDS:raise ValueError(f'Row {line}: recordType must be one of {", ".join(sorted(KINDS))}.')
  if any(not row.get(k) for k in REQUIRED[kind]):raise ValueError(f'Row {line}: missing required {kind} fields.')
  for key in DATE_FIELDS:
   if row.get(key):
    try:date.fromisoformat(row[key])
    except ValueError:raise ValueError(f'Row {line}: {key} must be YYYY-MM-DD.') from None
  for key in NUMBER_FIELDS:
   if row.get(key):
    try:v=Decimal(row[key])
    except InvalidOperation:raise ValueError(f'Row {line}: invalid number in {key}.') from None
    if not v.is_finite() or abs(v)>Decimal('1000000000'):raise ValueError(f'Row {line}: finite bounded numeric value required.')
    row[key]=str(v)
  if kind=='payment' and row['paymentStatus'].lower() not in ('completed','scheduled','pending','failed'):
   raise ValueError(f'Row {line}: paymentStatus must be completed, scheduled, pending or failed.')
  if kind=='pricing' and row['validTo']<row['validFrom']:raise ValueError(f'Row {line}: invalid pricing date range.')
  if kind in ('payment','credit','payment_allocation') and Decimal(row['amount'])<=0:raise ValueError(f'Row {line}: use a positive payment or credit amount and the correct record type.')
  if kind=='invoice' and Decimal(row['invoiceTotal'])<0:raise ValueError(f'Row {line}: use a credit record for a negative invoice.')
  if kind in ('invoice_line','pricing') and Decimal(row['unitPrice'])<0:raise ValueError(f'Row {line}: unit price must be nonnegative.')
  if kind=='return' and row.get('expectedCredit') and Decimal(row['expectedCredit'])<0:raise ValueError(f'Row {line}: expected credit must be nonnegative.')
  if kind=='conversion' and Decimal(row['unitsPerReference'])<=0:raise ValueError(f'Row {line}: conversion factor must be positive.')
  if kind=='invoice_line' and Decimal(row['quantity'])<=0:raise ValueError(f'Row {line}: quantity must be positive; use credit records for returns.')
  row.update(recordType=kind,source=source,sourceRow=line)
  records.append(row)
 if not records:raise ValueError('No accounting records were found.')
 return records


def apply_column_mapping(rows,mapping):
 if mapping is None:return rows
 import json
 if not isinstance(mapping,dict) or set(mapping)-{'fields','defaults','date_format','usd_numbers','amount_sign'} or len(json.dumps(mapping).encode())>4096:
  raise ValueError('Column mapping is unsupported or exceeds 4 KiB.')
 fields=mapping.get('fields',{});defaults=mapping.get('defaults',{})
 allowed=set(ALIASES.values())|set(DATE_FIELDS)|set(NUMBER_FIELDS)|{'recordType','vendor','project','sku','unit','paymentStatus','description'}
 for required in REQUIRED.values():allowed.update(required)
 if not isinstance(fields,dict) or not isinstance(defaults,dict) or set(fields)&set(defaults):raise ValueError('Choose either a column or a fixed value for each field.')
 if not fields:raise ValueError('Choose at least one source column.')
 if (set(fields)|set(defaults))-allowed:raise ValueError('Unknown accounting field in mapping.')
 width=len(rows[0])
 if any(type(index) is not int or not 0<=index<width for index in fields.values()):raise ValueError('Invalid source column index.')
 if any(not isinstance(value,str) or len(value)>256 for value in defaults.values()):raise ValueError('Fixed mapping values must be text of at most 256 characters.')
 date_format=mapping.get('date_format','ISO')
 if date_format not in ('ISO','MM/DD/YYYY','MM/DD/YY'):raise ValueError('Unsupported date format.')
 if type(mapping.get('usd_numbers',False)) is not bool:raise ValueError('Choose plain numbers or USD formatting.')
 if mapping.get('amount_sign','positive') not in ('positive','negative'):raise ValueError('Unsupported payment/credit sign convention.')
 header=sorted(set(fields)|set(defaults))
 if 'recordType' not in header:raise ValueError('Choose a record-type column or fixed record type.')
 mapped=[header]
 for row in rows[1:]:
  if len(row)>width:raise ValueError('A source row extends beyond its header columns.')
  if not any(value is not None and str(value).strip() for value in row):mapped.append([]);continue
  values=[]
  for name in header:
   values.append(row[fields[name]] if name in fields and fields[name]<len(row) else '' if name in fields else defaults[name])
  prepared=dict(zip(header,values))
  for name,value in list(prepared.items()):
   if value is None or str(value).strip()=='':continue
   if name in DATE_FIELDS and date_format!='ISO' and not isinstance(value,(date,datetime)):
    try:prepared[name]=datetime.strptime(str(value).strip(),'%m/%d/%Y' if date_format=='MM/DD/YYYY' else '%m/%d/%y').date().isoformat()
    except ValueError:raise ValueError('A mapped date does not match the selected date format.') from None
   if name in NUMBER_FIELDS and mapping.get('usd_numbers'):
    import re
    text=str(value).strip()
    if text.startswith('(') and text.endswith(')'):text='-'+text[1:-1]
    if not re.fullmatch(r'-?\$?\s*(?:\d{1,3}(?:,\d{3})+|\d+)(?:\.\d+)?',text):raise ValueError('A mapped number does not use the selected USD format.')
    prepared[name]=text.replace('$','').replace(',','').replace(' ','')
  if mapping.get('amount_sign')=='negative':
   if str(prepared.get('recordType','')).lower() not in ('payment','credit') or not prepared.get('amount'):
    raise ValueError('Negative outflow/credit convention applies only to payment or credit amounts.')
   try:amount=Decimal(str(prepared['amount']))
   except InvalidOperation:raise ValueError('Invalid signed amount.') from None
   if not amount.is_finite() or amount>=0:raise ValueError('Expected negative source amounts; separate other transaction types before importing.')
   prepared['amount']=str(-amount)
  mapped.append([prepared[name] for name in header])
 return mapped

def inspect_structured(blob,filename,column_mapping=None):
 suffix=Path(filename).suffix.lower()
 if suffix not in ('.csv','.xlsx'):raise ValueError('Structured records must be CSV or XLSX.')
 rows=normalize(apply_column_mapping(table(blob,suffix),column_mapping),'pending'+suffix)
 return {'sha256':sha256(blob).hexdigest(),'document_pages':math.ceil(len(rows)/50),
  'required_ocr_pages':[],'sparse_text_pages_for_review':[], 'record_count':len(rows),
  'extension':suffix,'content_type':'text/csv' if suffix=='.csv' else 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  'metering_note':'50 accounting rows or part thereof use one document-page credit; no OCR credits.',
  'records':rows}

def stage_records(records,source):
 """Convert validated customer rows into existing pipeline schemas, retaining origins."""
 result={k:[] for k in ('invoices','payments','credits','quotes','pricingBaselines','notes','lineRecords','unitConversions','returnRecords','paymentApplications')}
 for supplied in records:
  row=dict(supplied);kind=row.pop('recordType');row['source']=source
  if kind=='payment_allocation':
   row.update(paidAmount=float(row['amount']),openAmount=0.0,refType='IN');result['paymentApplications'].append(row)
  elif kind=='return':result['returnRecords'].append(row)
  elif kind=='invoice_line':result['lineRecords'].append(row)
  elif kind=='conversion':result['unitConversions'].append(row)
  elif kind=='invoice':
   row.update(id='structured:'+source+':'+str(row['sourceRow']),invoiceTotal=float(row['invoiceTotal']),correctedTotal=float(row['invoiceTotal']),overpayment=0.0,lineItems=[])
   result['invoices'].append(row)
  elif kind=='payment':
   if row['paymentStatus'].lower()!='completed':
    result['notes'].append({'id':source+':'+str(row['sourceRow']),'noteType':'uncompleted_payment','note':f"Payment {row['referenceNumber']} is {row['paymentStatus']}; not counted as paid.",'source':source,'sourceRow':row['sourceRow'],'status':'review'})
    continue
   row.update(amount=float(row['amount']),appliedInvoices=[v.strip() for v in row.get('appliedInvoices','').split(';') if v.strip()])
   result['payments'].append(row)
  elif kind=='credit':
   row.update(amount=abs(float(row['amount'])),applied=False)
   result['credits'].append(row)
  elif kind=='quote':result['quotes'].append(row)
  else:
   row.update(unitPrice=str(row['unitPrice']),sourceFile=source,totalAmount=0.0,confidence=0.0)
   result['pricingBaselines'].append(row)
 return result