"""Independent raw OOXML -> normalized workbook verification; no intake import."""
import hashlib
import re
import zipfile
from decimal import Decimal
from xml.etree import ElementTree as ET
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from verify import raw_rows, NS


def verify_intake(root, normalized, entries):
    observed={(e['file'],e['sheet']) for e in entries}
    assert len(observed)==len(entries), 'duplicate source sheet'
    expected=set()
    for path in root.iterdir():
        if path.is_file() and not path.name.startswith('~$') and path.suffix.lower() in ('.xlsx','.xlsm'):
            with zipfile.ZipFile(path) as z:
                expected.update((path.name,s.attrib['name']) for s in ET.fromstring(z.read('xl/workbook.xml')).find('s:sheets',NS))
    assert observed==expected,'source sheet inventory mismatch'
    verified=0
    for e in entries:
        path=root/e['file']
        assert hashlib.sha256(path.read_bytes()).hexdigest()==e['sha256']
        if e['status']!='normalized':continue
        cols={f:get_column_letter(n) for f,n in e['columns'].items()}
        raw=raw_rows(path,e['sheet'],allowed_na_column=cols['price'])
        source_rows={n:r for n,r in raw.items() if n>e['header_row'] and any(v is not None for v in r.values())}
        mappings=e['row_map']
        assert {m['original_row'] for m in mappings}==set(source_rows)
        assert len(mappings)==len(source_rows)
        wb=load_workbook(normalized/e['normalized_file'],data_only=False);ws=wb['报价']
        assert len(mappings)==ws.max_row-1
        assert {m['normalized_row'] for m in mappings}==set(range(2,ws.max_row+1))
        expected_events=[]
        for m in mappings:
            cells=source_rows[m['original_row']]
            val={f:cells.get(c) for f,c in cols.items()}
            name=(val['item'] or '').strip(); assert name
            spec=val.get('spec')
            if 'spec' not in cols:
                bracket=re.findall(r'【([^】]+)】',name);spec=bracket[-1] if bracket else None
            note=val.get('note');price=val.get('price')
            if price=='#N/A':
                coord=cols['price']+str(m['original_row'])
                expected_events.append({'row':m['original_row'],'cell':coord,'raw':'#N/A','action':'missing_quote'})
                extra=f'原始报价#N/A按缺报价留空（{e["file"]}/{e["sheet"]}/{coord}）'
                note='；'.join(v for v in (note,extra) if v);price=None
            expected=[name,val.get('unit'),spec,price,val.get('quantity'),note,val.get('previous'),val.get('previous_date')]
            actual=[c.value for c in ws[m['normalized_row']]]
            assert len(actual)==8
            for i,(a,b) in enumerate(zip(actual,expected)):
                if i in (3,4,6) and b not in (None,''):
                    assert a is not None and Decimal(str(a))==Decimal(b), (e['file'],m['original_row'],i)
                else:assert a==(None if b=='' else b), (e['file'],m['original_row'],i)
            assert not any(c.data_type in ('f','e') for c in ws[m['normalized_row']])
            verified+=1
        assert e['events']==expected_events,'N/A audit trail mismatch'
    return {'verified':True,'inventoried_sheets':len(entries),'verified_normalized_rows':verified,
            'blocked_sheets':sum(e['status']=='blocked' for e in entries),'source_hashes_unchanged':True}