"""Standard-layout result-only ZIP and item-level procurement master from finalized categories."""
import argparse
from collections import Counter
from decimal import Decimal
import hashlib
import json
from pathlib import Path
import shutil
import zipfile
from xml.etree import ElementTree as ET
from openpyxl import Workbook, load_workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter

BASE=['商品','单位','规格','选定供应商','上期价格','上期价格日期','最终价格','涨跌幅','采购数','小计','备注']
TAIL=['最低价供应商','最低报价','并列最低供应商数']
BLUE='FFD9E2F3'


def style(cell, header=False, first=False, numeric=False):
    cell.font=Font(name='Arial',size=10,bold=header or first,color='FF000000')
    cell.alignment=Alignment(horizontal='center' if header else None,vertical='center',wrap_text=True)
    cell.border=Border(**{side:Side(style='thin',color='FF7F7F7F' if header else 'FFBFBFBF') for side in ('left','right','top','bottom')})
    if header or first:cell.fill=PatternFill('solid',fgColor=BLUE)
    if numeric:cell.number_format='#,##0.00'


def put(ws, rows, widths, numeric=()):
    ws.sheet_view.showGridLines=False
    for row in rows:
        ws.append(row)
    for row in ws:
        for c in row:
            if isinstance(c.value,str):c.data_type='s'
            style(c,header=c.row==1,numeric=c.row>1 and c.column in numeric)
    for i,width in enumerate(widths,1):ws.column_dimensions[get_column_letter(i)].width=width
    ws.freeze_panes='A2'


def save_cached(wb, path, formulas):
    """Write actual Excel formulas with independently supplied checked numeric caches."""
    wb.save(path)
    if not formulas:return
    ns={'s':'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
    with zipfile.ZipFile(path) as z:data={n:z.read(n) for n in z.namelist()}
    for index, sheet in enumerate(wb.sheetnames,1):
        changes=formulas.get(sheet,{})
        if not changes:continue
        name=f'xl/worksheets/sheet{index}.xml';xml=ET.fromstring(data[name])
        for c in xml.findall('.//s:sheetData/s:row/s:c',ns):
            if c.attrib['r'] not in changes:continue
            formula,value=changes[c.attrib['r']]
            for child in list(c):c.remove(child)
            ET.SubElement(c,'{'+ns['s']+'}f').text=formula.lstrip('=')
            v=ET.SubElement(c,'{'+ns['s']+'}v')
            if value is None:
                c.set('t','str');v.text=''
            else:
                c.attrib.pop('t',None)
                v.text=str(value)
        data[name]=ET.tostring(xml,encoding='utf-8',xml_declaration=True)
    with zipfile.ZipFile(path,'w',zipfile.ZIP_DEFLATED) as z:
        for n,b in data.items():z.writestr(n,b)


def format_comparison(ws, headers, rows, include_category=False):
    n=len(headers)-len(BASE)-len(TAIL)-(1 if include_category else 0)
    widths=[34,10,18,34,13,15,13,12,10,13,24]+[33]*n+[34,13,15]+([16] if include_category else [])
    put(ws,[headers,*rows],widths,numeric=list(range(5,11))+list(range(12,15+n)))
    formulas={};last_quote=get_column_letter(11+n);low_col=get_column_letter(13+n)
    for i,row in enumerate(rows,2):
        ws.cell(i,9).number_format='#,##0.##'
        lookup=f'INDEX(L{i}:{last_quote}{i},1,MATCH(D{i},L$1:{last_quote}$1,0))'
        f=f'=IFERROR(IF({lookup}>0,{lookup},""),"")'
        sub=f'=IF(OR(G{i}="",I{i}=""),"",G{i}*I{i})'
        ws.cell(i,7,f);ws.cell(i,10,sub)
        formulas[f'G{i}']=(f,row[6]);formulas[f'J{i}']=(sub,row[9])
    if rows:
        ws.conditional_formatting.add(f'L2:{last_quote}{len(rows)+1}',CellIsRule(operator='equal',formula=[f'{low_col}2'],fill=PatternFill('solid',fgColor='FFDDEBF7')))
    return formulas


def read_categories(directory):
    results=[]
    for path in sorted(Path(directory).glob('*.xlsx')):
        wb=load_workbook(path,data_only=True)
        if '比较结果' not in wb.sheetnames or '最低价统计' not in wb.sheetnames:continue
        ws=wb['比较结果'];values=list(ws.values);headers=list(values[0]);rows=[list(r) for r in values[1:] if r[0] is not None]
        if headers[:11]!=BASE or headers[-3:]!=TAIL:raise ValueError(f'{path.name}: nonstandard comparison columns')
        vendors=headers[11:-3];stats=wb['最低价统计'];standard=stats['A1'].value=='类别'
        category=stats['B1'].value if standard else path.stem
        counts=Counter({v:0 for v in vendors})
        for row in rows:
            prices={v:Decimal(str(row[11+i])) for i,v in enumerate(vendors) if row[11+i] is not None and Decimal(str(row[11+i]))>0}
            low=min(prices.values()) if prices else None
            winners=[v for v in vendors if low is not None and prices.get(v)==low]
            for v in winners:counts[v]+=1
            expected=prices.get(row[3])
            if (None if row[6] is None else Decimal(str(row[6])))!=expected:raise ValueError('selected quote/final price mismatch')
            subtotal=expected*Decimal(str(row[8])) if expected is not None and row[8] is not None else None
            if (None if row[9] is None else Decimal(str(row[9])))!=subtotal:raise ValueError('subtotal mismatch or absent formula cache')
            if (row[-3] or '')!='、'.join(winners) or row[-1] not in (len(winners), None if not winners else len(winners)):
                raise ValueError('minimum winner/count mismatch')
            if (None if row[-2] is None else Decimal(str(row[-2])))!=low:raise ValueError('minimum price mismatch')
        selected={r[3] for r in rows}
        if len(selected)!=1:raise ValueError('category must retain a single selected supplier')
        chosen=next(iter(selected))
        if chosen is not None and (chosen not in counts or counts[chosen]!=max(counts.values())):raise ValueError('selected supplier is not category count leader')
        # Duplicate rows in finalized results are preserved; this is not raw-source entity matching.
        result={'path':path,'category':category,'vendors':vendors,'headers':headers,'rows':rows,'counts':dict(counts),'selected':chosen,
                'source':stats['B2'].value if standard else path.name,'standard':standard}
        results.append(result)
    if not results:raise ValueError('no finalized category results')
    if len({r['category'] for r in results})!=len(results):raise ValueError('duplicate category files')
    return results


def statistics(ws, r):
    valid=sum(row[-2] is not None for row in r['rows']);ties=sum((row[-1] or 0)>1 for row in r['rows'])
    rows=[['类别',r['category']],['来源文件',r['source']],['有效统计行数',valid],['空值/0报价行数',len(r['rows'])-valid],['最低价并列行数',ties]]
    for row in rows:ws.append(row)
    ws.sheet_view.showGridLines=False
    for cells in ws:
        for c in cells:
            style(c,header=c.column==1)
            if c.column==1:c.alignment=Alignment(horizontal='left',vertical='center',wrap_text=True)
    ws.append([None,None]);ws.append(['供应商','最低价次数'])
    ranked=sorted(r['vendors'],key=lambda v:-r['counts'][v])
    for v in ranked:ws.append([v,r['counts'][v]])
    for cells in ws.iter_rows(min_row=7):
        for c in cells:style(c,header=c.row==7,first=c.row==8)
    ws.column_dimensions['A'].width=40;ws.column_dimensions['B'].width=28;ws.freeze_panes='A8'


def build(directory, output, batch=''):
    source=Path(directory).resolve();output=Path(output).resolve()
    if output.exists() or output.with_suffix('.zip').exists() or output==source or output.is_relative_to(source):raise ValueError('new output directory outside source required')
    reports=read_categories(source);output.mkdir(parents=True)
    vendors=list(dict.fromkeys(v for r in reports for v in r['vendors']))
    summaries=[];all_rows=[];files=[]
    for r in reports:
        path=r['path'];dest=output/path.name
        if r['standard']:
            shutil.copy2(path,dest)  # Exact original layout, cached formulas and bytes.
        else:
            wb=Workbook();wb.active.title='比较结果';formulas=format_comparison(wb.active,r['headers'],r['rows'])
            statistics(wb.create_sheet('最低价统计'),r);save_cached(wb,dest,{'比较结果':formulas})
        files.append(dest)
        for row in r['rows']:
            quotes=dict(zip(r['vendors'],row[11:-3]))
            all_rows.append([*row[:11],*[quotes.get(v) for v in vendors],*row[-3:],r['category']])
        valid=sum(row[-2] is not None for row in r['rows']);tie=sum((row[-1] or 0)>1 for row in r['rows'])
        top=max(r['counts'].values());leaders=[v for v in r['vendors'] if top and r['counts'][v]==top]
        summaries.append([r['category'],r['selected'],top,'是' if len(leaders)>1 else '否',valid,len(r['rows'])-valid,tie,*[r['counts'].get(v,0) for v in vendors],r['source']])
    summary_name=f'分类最低价最多供应商汇总{("_"+batch) if batch else ""}.xlsx'
    existing=[]
    for p in source.glob('*.xlsx'):
        book=load_workbook(p,data_only=True,read_only=True)
        if '分类最低价最多供应商' in book.sheetnames:
            header=list(next(book['分类最低价最多供应商'].values))
            if header[:7]==['类别','最低价次数最多供应商','最低价次数','是否并列第一','有效统计行数','空值/0报价行数','最低价并列行数']:existing.append(p)
    # Preserve existing standard summary only after matching every category's decision/counts.
    if len(existing)==1:
        old=list(load_workbook(existing[0],data_only=True).active.values)
        old_headers=list(old[0]);expected_headers=['类别','最低价次数最多供应商','最低价次数','是否并列第一','有效统计行数','空值/0报价行数','最低价并列行数',*vendors,'来源文件']
        if old_headers!=expected_headers or {r[0]:list(r) for r in old[1:]}!={r[0]:r for r in summaries}:raise ValueError('existing summary/category discrepancy')
        dest=output/existing[0].name;shutil.copy2(existing[0],dest)
    else:
        dest=output/summary_name;wb=Workbook();wb.active.title='分类最低价最多供应商'
        put(wb.active,[['类别','最低价次数最多供应商','最低价次数','是否并列第一','有效统计行数','空值/0报价行数','最低价并列行数',*vendors,'来源文件'],*summaries],[16,38,14,14,16,16,16]+[34]*len(vendors)+[58]);wb.save(dest)
    files.append(dest)
    master=output/f'总采购结果{("_"+batch) if batch else ""}.xlsx'
    wb=Workbook();wb.active.title='总采购结果';headers=[*BASE,*vendors,*TAIL,'类别']
    formulas=format_comparison(wb.active,headers,all_rows,include_category=True);save_cached(wb,master,{'总采购结果':formulas});files.append(master)
    # Independent row-by-row readback of final master against finalized category values, preserving duplicates/order.
    reread=list(load_workbook(master,data_only=True).active.values)
    if list(reread[0])!=headers or [list(r) for r in reread[1:]]!=all_rows:raise AssertionError('master/category row mismatch')
    archive=output.with_suffix('.zip')
    if archive.exists():raise ValueError('archive exists; choose another output')
    with zipfile.ZipFile(archive,'w',zipfile.ZIP_DEFLATED) as z:
        for f in files:z.write(f,f.name)
    with zipfile.ZipFile(archive) as z:
        assert z.testzip() is None and len(z.namelist())==len(reports)+2
        assert all(n.endswith('.xlsx') and '/' not in n for n in z.namelist())
    return {'categories':len(reports),'items':len(all_rows),'files':len(files),'master':str(master),'zip':str(archive),
            'original_standard_files_unchanged':all(hashlib.sha256(r['path'].read_bytes()).digest()==hashlib.sha256((output/r['path'].name).read_bytes()).digest() for r in reports if r['standard'])}

if __name__=='__main__':
    p=argparse.ArgumentParser();p.add_argument('source');p.add_argument('output');p.add_argument('--batch',default='');a=p.parse_args()
    print(json.dumps(build(a.source,a.output,a.batch),ensure_ascii=False,indent=2))