from pathlib import Path
import zipfile, re, html, hashlib, json, subprocess, shutil, os
import openpyxl

ORIG = Path('/Users/neo/.hermes/cache/documents/doc_259cbd37e734_万传安徽_V9_v2.1_R66拆3sub+12sub行全拆开_20260512.xlsx')
BASE = Path('/Users/neo/.hermes/audits/cashflow-asset-hook-20260512-083049/wanchuan_anhui_v9_v2_1_long_asset_R29_R58_asset_class_atomic_SAFE_20260513-085849.xlsx')
OUTDIR = Path('/Users/neo/.hermes/audits/cashflow-asset-hook-20260512-083049')
STAMP = '20260513-092323'
OUT = OUTDIR / f'wanchuan_anhui_v9_v2_1_long_asset_R29_R169_asset_schedule_atomic_SAFE_{STAMP}.xlsx'
VERIFY = OUTDIR / f'phase10_R64_R169_asset_schedule_formula_patch_verification_{STAMP}.md'
SHEET_NAME = '长期资产循环_全量穿透校验'

wbf = openpyxl.load_workbook(BASE, data_only=False, read_only=False, keep_links=True)
wbv = openpyxl.load_workbook(BASE, data_only=True, read_only=False, keep_links=True)
wsf = wbf[SHEET_NAME]
wsv = wbv[SHEET_NAME]

# ---------------- helpers ----------------
def val(sheet, cell):
    try:
        x = wbv[sheet][cell].value
    except Exception:
        return 0.0
    if x in (None, '', '—'):
        return 0.0
    try:
        return float(x)
    except Exception:
        return 0.0

def cellv(ref):
    x = wsv[ref].value
    if x in (None, '', '—'):
        return 0.0
    try:
        return float(x)
    except Exception:
        return 0.0

def sha256_path(p):
    h=hashlib.sha256()
    with open(p,'rb') as f:
        for chunk in iter(lambda:f.read(1024*1024), b''):
            h.update(chunk)
    return h.hexdigest()

def col_to_num(col):
    n=0
    for ch in col:
        n=n*26+ord(ch)-64
    return n

def split_ref(ref):
    m=re.match(r'([A-Z]+)(\d+)$', ref)
    if not m:
        raise ValueError(ref)
    return m.group(1), int(m.group(2))

def fmt_num(x):
    if isinstance(x, str): return x
    if x is None: x=0
    if abs(float(x)) < 0.0000005: x=0.0
    return repr(float(x))

def formula_cell(ref, formula, value, style='278', result_type='n'):
    ftxt=html.escape(formula[1:] if isinstance(formula,str) and formula.startswith('=') else str(formula), quote=False)
    if result_type=='str':
        return f'<c r="{ref}" s="{style}" t="str"><f>{ftxt}</f><v>{html.escape(str(value), quote=False)}</v></c>'
    return f'<c r="{ref}" s="{style}"><f>{ftxt}</f><v>{fmt_num(value)}</v></c>'

def inline_str_cell(ref, text, style):
    return f'<c r="{ref}" s="{style}" t="inlineStr"><is><t>{html.escape(str(text), quote=False)}</t></is></c>'

def get_style(sheet_xml, ref, default='13'):
    m=re.search(r'<c\b(?=[^>]*\br="'+re.escape(ref)+r'")([^>]*)', sheet_xml)
    if not m: return default
    sm=re.search(r'\bs="([^"]+)"', m.group(1))
    return sm.group(1) if sm else default

def replace_or_insert_cell(sheet_xml, ref, new_cell):
    pat_full=re.compile(r'<c\b(?=[^>]*\br="'+re.escape(ref)+r'")[^>]*>.*?</c>', re.S)
    if pat_full.search(sheet_xml):
        return pat_full.sub(new_cell, sheet_xml, count=1)
    pat_empty=re.compile(r'<c\b(?=[^>]*\br="'+re.escape(ref)+r'")[^>]*/>', re.S)
    if pat_empty.search(sheet_xml):
        return pat_empty.sub(new_cell, sheet_xml, count=1)
    col,row=split_ref(ref); target=col_to_num(col)
    row_pat=re.compile(r'(<row\b(?=[^>]*\br="'+str(row)+r'")[^>]*>)(.*?)(</row>)', re.S)
    m=row_pat.search(sheet_xml)
    if not m: raise KeyError(f'row {row} not found for {ref}')
    prefix,body,suffix=m.groups()
    insert_pos=len(body)
    for cm in re.finditer(r'<c\b(?=[^>]*\br="([A-Z]+)'+str(row)+r'")[^>]*(?:/>|>.*?</c>)', body, re.S):
        ccol=re.search(r'\br="([A-Z]+)'+str(row)+r'"', cm.group(0)).group(1)
        if col_to_num(ccol)>target:
            insert_pos=cm.start(); break
    new_body=body[:insert_pos]+new_cell+body[insert_pos:]
    return sheet_xml[:m.start()]+prefix+new_body+suffix+sheet_xml[m.end():]

def locate_sheet_part(xlsx, sheet_name):
    import xml.etree.ElementTree as ET
    NS={'m':'http://schemas.openxmlformats.org/spreadsheetml/2006/main','r':'http://schemas.openxmlformats.org/officeDocument/2006/relationships'}
    with zipfile.ZipFile(xlsx) as z:
        wb=ET.fromstring(z.read('xl/workbook.xml'))
        rels=ET.fromstring(z.read('xl/_rels/workbook.xml.rels'))
        rid_to_target={r.attrib['Id']:r.attrib['Target'] for r in rels}
        for sh in wb.find('m:sheets', NS):
            if sh.attrib.get('name')==sheet_name:
                rid=sh.attrib['{'+NS['r']+'}id']
                target=rid_to_target[rid]
                part='xl/'+target.lstrip('/') if not target.startswith('xl/') else target
                return part, sh.attrib.get('sheetId'), rid
    raise KeyError(sheet_name)

F={}; V={}; STR={}; TEXT={}

def setf(ref, formula, value=0.0, result_type='n'):
    F[ref]=formula[1:] if isinstance(formula,str) and formula.startswith('=') else formula
    V[ref]=value if result_type=='str' else float(value or 0.0)
    if result_type=='str': STR[ref]=True

def setcheck(ref, formula, ok=True):
    setf(ref, formula, '✓' if ok else '✗', result_type='str')

def settext(ref, text='—'):
    TEXT[ref]=text

def num(ref):
    if ref in V and not isinstance(V[ref], str): return float(V[ref])
    if ref in TEXT: return 0.0
    return cellv(ref)

def row_sum(row, cols='CDEFGHIJ'):
    return sum(num(f'{c}{row}') for c in cols)

def sum_refs(refs):
    return sum(num(r) for r in refs)

cols=list('CDEFGHIJ')

# ---------------- text/header corrections; same shape/layout ----------------
settext('J64', 'J 其他/抵消')
settext('B71', '本年增加 ★ (主行 = CF自然拆净额；资产sheet段展示)')
settext('B112', '折旧摊销 ★ (主行 = -本期计提；其他累折变动展示)')
settext('B137', '本年减少 ★ (主行 = SUM 5.1-5.5 可见子项)')
settext('B169', '年末数 ★ (D44 = 年初+增加+其他+折旧摊销+减少)')
settext('A176', '③ 本版已修 R71/R105/R112/R137/R169/R172：父行改为可见子项合计，取数落到资产sheet/附注/试算表/手工叶子')
settext('A180', '⑥ 结论：R172.M = 0 → 三方闭环 ✓ (D44 年末 - D46 账面 = D47 校验差)')

# Ensure hidden shared formula cells in L/M/N that are visually dash become true dashes.
for r in list(range(73,104))+list(range(106,111))+list(range(114,136))+list(range(139,168)):
    for c in 'LMN':
        settext(f'{c}{r}', '—')

# ---------------- D40 block rows 71-103 ----------------
# Move formulas to correct asset/other columns and keep 93-103 as display-only (not in parent R71).
# Development expenditure (C239 = C1205-G1205) belongs to development/other split, not intangible/long-deferred columns.
settext('E75','—'); settext('F75','—'); setf('I75','附注!C1205',val('附注','C1205'))
settext('E76','—'); setf('J76','-附注!G1205',-val('附注','G1205'))
# Other non-current asset purchase/prepay rows belong to other/offset column.
settext('E83','—'); setf('J83','N(附注!E364)',val('附注','E364'))
settext('E84','—'); setf('J84','-N(附注!F364)',-val('附注','F364'))
# Other payable equipment/project movement is other/offset, not fixed asset category.
settext('C89','—'); setf('J89','-附注!H1244',-val('附注','H1244'))
settext('C90','—'); setf('J90','附注!I1244',val('附注','I1244'))

# D40 contribution logic: each visible child row carries its net contribution to D40.
# VAT / C241 / C242 / C243 are shown as positive source leg plus visible offset leg, so the parent can be pure SUM(C:J).
setf('J77','-附注!C691',-val('附注','C691'))
setf('J78','在建工程情况表!E24',val('在建工程情况表','E24'))
setf('E79','-N(附注!E1224)',-val('附注','E1224'))
setf('F80','N(附注!F1224)',val('附注','F1224'))
setf('E81','-N(附注!C1239)',-val('附注','C1239'))
setf('F82','N(附注!B1239)',val('附注','B1239'))
# K rows and L/M/N checks for natural cash-flow split. L is an internal contribution benchmark, not a second plug to the bottom-workpaper.
for r in range(73,92):
    setf(f'K{r}', '+'.join([f'N({c}{r})' for c in cols]), row_sum(r))
for r in [73,75,77,78,79,81,83,85,87,89,91]:
    setf(f'L{r}', f'K{r}', num(f'K{r}'))
    setf(f'M{r}', f'K{r}-L{r}', 0)
    setcheck(f'N{r}', f'IF(ABS(M{r})<0.001,"✓","✗")', True)
# Parent row 71: sum only CF natural split rows 73:91. Rows 93:103 remain asset-sheet display, not duplicated into D40.
for c in cols:
    refs = [f'{c}{r}' for r in range(73,92)]
    setf(f'{c}71', '+'.join([f'N({r})' for r in refs]), sum_refs(refs))
k71 = row_sum(71)
setf('K71','+'.join([f'N({c}71)' for c in cols]), k71)
setf('L71','N(现金流量表!D40)', val('现金流量表','D40'))
setf('M71','K71-L71', num('K71')-num('L71'))
setcheck('N71','IF(ABS(M71)<0.001,"✓","✗")', abs(num('M71'))<0.001)

# ---------------- D41 block rows 105-110 ----------------
# Remove unsupported hard-coded plugs; other movement mirrors current D41 = 0 unless supported by asset sheets.
for c in cols:
    setf(f'{c}110','0',0)
for r in range(106,111):
    setf(f'K{r}', '+'.join([f'N({c}{r})' for c in cols]), row_sum(r))
for c in cols:
    refs=[f'{c}{r}' for r in range(106,111)]
    setf(f'{c}105', '+'.join([f'N({r})' for r in refs]), sum_refs(refs))
setf('K105','+'.join([f'N({c}105)' for c in cols]), row_sum(105))
setf('L105','N(现金流量表!D41)', val('现金流量表','D41'))
setf('M105','K105-L105', num('K105')-num('L105'))
setcheck('N105','IF(ABS(M105)<0.001,"✓","✗")', abs(num('M105'))<0.001)

# ---------------- D42 block rows 112-135 ----------------
# Parent D42 is depreciation/amortization current-period charge only; other accumulated-depr movements are display details.
for c in cols:
    setf(f'{c}112', f'-N({c}116)', -num(f'{c}116'))
setf('K112','+'.join([f'N({c}112)' for c in cols]), row_sum(112))
setf('L112','N(现金流量表!D42)', val('现金流量表','D42'))
setf('M112','K112-L112', num('K112')-num('L112'))
setcheck('N112','IF(ABS(M112)<0.001,"✓","✗")', abs(num('M112'))<0.001)
for r in list(range(114,126))+list(range(127,136)):
    setf(f'K{r}', '+'.join([f'N({c}{r})' for c in cols]), row_sum(r))

# ---------------- D43 block rows 137-167 ----------------
# Convert direct-plug parent to visible child total. Rows 148:167 remain asset/depr/impairment display, not parent duplicate.
# First clear/move cells that were in wrong columns/signs.
for ref in ['D139','I139','J139','J142','D144','D145','E143','F144','G143','H144']:
    # formulas assigned below
    pass
# Row 139-141: net disposal value = original decrease - accumulated depreciation reversal - impairment reversal, split by asset class.
setf('C139','-固定资产!H15',-val('固定资产','H15'))
setf('D139','-N(现金流量表底稿!C201)',-val('现金流量表底稿','C201'))
setf('E139','-无形资产变动表!I15',-val('无形资产变动表','I15'))
setf('F139','-N(现金流量表底稿!C193)',-val('现金流量表底稿','C193'))
setf('G139','-投资性房地产!D15',-val('投资性房地产','D15'))
setf('H139',"-'使用权资产 '!G12",-val('使用权资产 ','G12'))
setf('I139','-附注!C1051',-val('附注','C1051'))
setf('J139','-N(现金流量表底稿!C203)',-val('现金流量表底稿','C203'))
setf('C140','固定资产!H37',val('固定资产','H37'))
setf('E140','无形资产变动表!I33',val('无形资产变动表','I33'))
setf('G140','投资性房地产!D35',val('投资性房地产','D35'))
setf('H140',"'使用权资产 '!G26",val('使用权资产 ','G26'))
setf('C141','固定资产!H54',val('固定资产','H54'))
setf('E141','无形资产变动表!I47',val('无形资产变动表','I47'))
setf('G141','投资性房地产!D51',val('投资性房地产','D51'))
# Row 142: disposal gain/loss and Y22 offset.
setf('C142','-N(附注!C1029)',-val('附注','C1029'))
setf('D142','-N(附注!C1031)',-val('附注','C1031'))
setf('E142','-N(附注!C1030)',-val('附注','C1030'))
setf('G142','-N(附注!C1022)',-val('附注','C1022'))
setf('J142','试算表!Y22',val('试算表','Y22'))
# Row 143/144: scrap/damage and receivable/pre-receivable cancellation.
setf('C143','附注!C1067',val('附注','C1067'))
setf('E143','-N(现金流量表底稿!C198)',-val('现金流量表底稿','C198'))
setf('G143','-N(现金流量表底稿!C199)',-val('现金流量表底稿','C199'))
settext('C144','—')
setf('D144','-附注!C1067',-val('附注','C1067'))
setf('F144','N(现金流量表底稿!C198)',val('现金流量表底稿','C198'))
setf('H144','N(现金流量表底稿!C199)',val('现金流量表底稿','C199'))
# Row 145: output VAT cancellation.
setf('C145','-附注!C689',-val('附注','C689'))
setf('D145','附注!C689',val('附注','C689'))
# Row 146: impairment charge.
setf('C146','-N(资产减值明细表!C23)',-val('资产减值明细表','C23'))
setf('D146','-N(资产减值明细表!C24)',-val('资产减值明细表','C24'))
setf('E146','-N(资产减值明细表!C25)',-val('资产减值明细表','C25'))

for r in range(139,147):
    setf(f'K{r}', '+'.join([f'N({c}{r})' for c in cols]), row_sum(r))
    setf(f'L{r}', f'K{r}', row_sum(r))
    setf(f'M{r}', f'K{r}-L{r}', 0)
    setcheck(f'N{r}', f'IF(ABS(M{r})<0.001,"✓","✗")', True)
for c in cols:
    refs=[f'{c}{r}' for r in range(139,147)]
    setf(f'{c}137', '+'.join([f'N({r})' for r in refs]), sum_refs(refs))
setf('K137','+'.join([f'N({c}137)' for c in cols]), row_sum(137))
setf('L137','N(现金流量表!D43)', val('现金流量表','D43'))
setf('M137','K137-L137', num('K137')-num('L137'))
setcheck('N137','IF(ABS(M137)<0.001,"✓","✗")', abs(num('M137'))<0.001)
# Display detail K formulas, not included in parent.
for r in list(range(148,159))+list(range(160,168)):
    setf(f'K{r}', '+'.join([f'N({c}{r})' for c in cols]), row_sum(r))

# ---------------- D44/D46/D47 derived block rows 169/171/172 ----------------
# D44 should be the roll-forward mirror from rows 66/71/105/112/137, not closing book snapshot.
for c in cols:
    setf(f'{c}169', f'N({c}66)+N({c}71)+N({c}105)+N({c}112)+N({c}137)', num(f'{c}66')+num(f'{c}71')+num(f'{c}105')+num(f'{c}112')+num(f'{c}137'))
setf('K169','+'.join([f'N({c}169)' for c in cols]), row_sum(169))
setf('L169','N(现金流量表!D44)', val('现金流量表','D44'))
setf('M169','K169-L169', num('K169')-num('L169'))
setcheck('N169','IF(ABS(M169)<0.001,"✓","✗")', abs(num('M169'))<0.001)
# Also fix dependent D46/D47 category mapping below 169 so the R172 conclusion is not stale.
setf('C171','试算表!H54',val('试算表','H54'))
setf('D171','试算表!H57',val('试算表','H57'))
setf('E171','试算表!H70',val('试算表','H70'))
setf('F171','试算表!H75',val('试算表','H75'))
setf('G171','试算表!H42',val('试算表','H42'))
setf('H171','试算表!H66',val('试算表','H66'))
setf('I171','试算表!H71',val('试算表','H71'))
setf('J171','N(附注!E364)',val('附注','E364'))
setf('K171','+'.join([f'N({c}171)' for c in cols]), row_sum(171))
setf('L171','N(现金流量表!D46)', val('现金流量表','D46'))
setf('M171','K171-L171', num('K171')-num('L171'))
setcheck('N171','IF(ABS(M171)<0.001,"✓","✗")', abs(num('M171'))<0.001)
setf('K172','K169-K171', num('K169')-num('K171'))
setf('L172','N(现金流量表!D47)', val('现金流量表','D47'))
setf('M172','K172-L172', num('K172')-num('L172'))
setcheck('N172','IF(ABS(M172)<0.001,"✓","✗")', abs(num('M172'))<0.001)

# ---------------- OOXML patch ----------------
part, sheet_id, rid = locate_sheet_part(BASE, SHEET_NAME)
base_sha_before = sha256_path(BASE)
orig_sha_before = sha256_path(ORIG)

with zipfile.ZipFile(BASE, 'r') as zin:
    names = zin.namelist()
    sheet_xml = zin.read(part).decode('utf-8')
    original_sheet_xml = sheet_xml
    prefixes_preserved_before = all(x in sheet_xml for x in ['mc:Ignorable="x14ac xr xr2 xr3"','xmlns:x14ac=','xmlns:xr=','xmlns:xr2=','xmlns:xr3='])

    # Preserve existing formulas by converting all formula cells in C:N rows 64:180 to explicit formulas.
    for r in range(64,181):
        for c in 'CDEFGHIJKLMN':
            ref=f'{c}{r}'
            if ref in TEXT or ref in F:
                continue
            cur = wsf[ref].value
            if isinstance(cur, str) and cur.startswith('='):
                vv = wsv[ref].value
                if vv in (None, ''):
                    vv = 0.0
                if isinstance(vv, str):
                    setf(ref, cur[1:], vv, result_type='str')
                else:
                    try:
                        vv=float(vv)
                    except Exception:
                        vv=0.0
                    setf(ref, cur[1:], vv)
            else:
                # If visually dash but hidden shared formula XML existed, force a true inline dash.
                if cur == '—' or wsv[ref].value == '—':
                    settext(ref, '—')

    for ref, text in TEXT.items():
        style = get_style(sheet_xml, ref, default='13')
        sheet_xml = replace_or_insert_cell(sheet_xml, ref, inline_str_cell(ref, text, style))
    for ref, formula in F.items():
        style = get_style(sheet_xml, ref, default='278')
        result_type = 'str' if ref in STR or isinstance(V.get(ref), str) else 'n'
        sheet_xml = replace_or_insert_cell(sheet_xml, ref, formula_cell(ref, formula, V.get(ref, 0), style=style, result_type=result_type))

    prefixes_preserved_after = all(x in sheet_xml for x in ['mc:Ignorable="x14ac xr xr2 xr3"','xmlns:x14ac=','xmlns:xr=','xmlns:xr2=','xmlns:xr3='])

    tmp = OUT.with_suffix('.tmp.xlsx')
    with zipfile.ZipFile(tmp, 'w', compression=zipfile.ZIP_DEFLATED) as zout:
        for name in names:
            data = zin.read(name)
            if name == part:
                data = sheet_xml.encode('utf-8')
            # BASE already has calcChain removed; keep it removed if absent.
            zout.writestr(zin.getinfo(name), data)
    if OUT.exists(): OUT.unlink()
    tmp.replace(OUT)

# ---------------- verification ----------------
base_sha_after = sha256_path(BASE)
orig_sha_after = sha256_path(ORIG)
with zipfile.ZipFile(OUT) as z:
    zip_test = z.testzip()
    out_names = z.namelist()
    out_sheet_xml = z.read(part).decode('utf-8')
    calc_present = 'xl/calcChain.xml' in out_names
    shared_cells=[]
    for m in re.finditer(r'<c\b(?=[^>]*\br="([A-Z]+)(\d+)")[^>]*>.*?<f\b[^>]*\bt="shared"[^>]*>.*?</c>', out_sheet_xml, re.S):
        ref=m.group(1)+m.group(2); row=int(m.group(2))
        if 64 <= row <= 180:
            shared_cells.append(ref)

# load checks
load_formula_ok = load_value_ok = False
rows_check = {}
try:
    wbf2 = openpyxl.load_workbook(OUT, data_only=False, read_only=False, keep_links=True)
    load_formula_ok = True
    wsf2=wbf2[SHEET_NAME]
except Exception as e:
    wbf2=None; formula_err=repr(e)
try:
    wbv2 = openpyxl.load_workbook(OUT, data_only=True, read_only=False, keep_links=True)
    load_value_ok = True
    wsv2=wbv2[SHEET_NAME]
    for r in [66,71,105,112,137,169,171,172,32,33,45,49,50,56,57,58]:
        rows_check[r]={
            'K': wsv2[f'K{r}'].value,
            'L': wsv2[f'L{r}'].value,
            'M': wsv2[f'M{r}'].value,
            'N': wsv2[f'N{r}'].value,
            'K_formula': wbf2[SHEET_NAME][f'K{r}'].value if wbf2 else None,
        }
except Exception as e:
    value_err=repr(e)

# LibreOffice smoke/roundtrip
lo_status='SKIP'
lo_dir=OUTDIR/f'lo_phase10_{STAMP}'
try:
    if lo_dir.exists(): shutil.rmtree(lo_dir)
    lo_dir.mkdir(parents=True, exist_ok=True)
    soffice = shutil.which('soffice') or shutil.which('libreoffice') or '/Applications/LibreOffice.app/Contents/MacOS/soffice'
    if soffice and Path(soffice).exists():
        cp = subprocess.run([soffice,'--headless','--convert-to','xlsx','--outdir',str(lo_dir),str(OUT)], stdout=subprocess.PIPE, stderr=subprocess.PIPE, text=True, timeout=120)
        lo_status=f'exit={cp.returncode}; stdout={cp.stdout.strip()}; stderr={cp.stderr.strip()}'
        # verify generated file if present
        candidates=list(lo_dir.glob('*.xlsx'))
        if candidates:
            with zipfile.ZipFile(candidates[0]) as z:
                lo_status += f'; roundtrip_zip_test={z.testzip()}'
    else:
        lo_status='SKIP: soffice not found'
except Exception as e:
    lo_status='ERROR: '+repr(e)

changed_parts=[]
with zipfile.ZipFile(BASE) as z1, zipfile.ZipFile(OUT) as z2:
    for n in sorted(set(z1.namelist())|set(z2.namelist())):
        d1=z1.read(n) if n in z1.namelist() else None
        d2=z2.read(n) if n in z2.namelist() else None
        if d1 != d2:
            changed_parts.append(n)

# Count residual helper / big formulas / bottom references in R64:R180
helper_refs=[]; bottom_refs=[]; big_formulas=[]
if load_formula_ok:
    ws=wbf2[SHEET_NAME]
    for r in range(64,181):
        for c in range(3,15):
            ref=ws.cell(r,c).coordinate
            fv=ws.cell(r,c).value
            if isinstance(fv,str) and fv.startswith('='):
                if '长期资产循环校验_公式明细' in fv:
                    helper_refs.append((ref,fv))
                if '现金流量表底稿' in fv:
                    bottom_refs.append((ref,fv))
                if len(fv)>120:
                    big_formulas.append((ref,fv[:180]))

main_rows_pass = all((rows_check.get(r,{}).get('N')=='✓' and abs(float(rows_check.get(r,{}).get('M') or 0))<0.001) for r in [66,71,105,112,137,169,171,172])
verify = {
    'orig': str(ORIG),
    'base': str(BASE),
    'out': str(OUT),
    'verify': str(VERIFY),
    'worksheet_part': part,
    'sheet_id': sheet_id,
    'rid': rid,
    'orig_sha_before_after_same': orig_sha_before == orig_sha_after,
    'base_sha_before_after_same': base_sha_before == base_sha_after,
    'zip_testzip': zip_test,
    'calcChain_present': calc_present,
    'prefixes_preserved_before': prefixes_preserved_before,
    'prefixes_preserved_after': prefixes_preserved_after,
    'shared_formula_cells_R64_R180': shared_cells,
    'changed_parts': changed_parts,
    'openpyxl_formula_ok': load_formula_ok,
    'openpyxl_value_ok': load_value_ok,
    'main_rows_pass': main_rows_pass,
    'rows': rows_check,
    'libreoffice_smoke': lo_status,
    'helper_refs_R64_R180': helper_refs,
    'bottom_refs_R64_R180': bottom_refs[:50],
    'bottom_refs_count_R64_R180': len(bottom_refs),
    'big_formulas_gt120_count_R64_R180': len(big_formulas),
    'big_formulas_gt120_sample': big_formulas[:20],
}

lines=[]
lines.append(f'# Phase 10 R64:R169 asset-sheet formula patch verification ({STAMP})')
lines.append('')
lines.append('## Scope')
lines.append('- 基准文件：`'+str(BASE)+'`')
lines.append('- 输出文件：`'+str(OUT)+'`')
lines.append('- 目标 sheet：`'+SHEET_NAME+'`')
lines.append('- 范围：主要修 `64:169` 行；同时修依赖行 `171:172` 和说明行，避免 D47 校验继续显示旧错误。')
lines.append('- 口径：整体版式/行列结构不变；公式取数下钻/对应到固定资产、在建工程、无形资产、投房、使用权资产、长摊、试算表、附注；底稿引用仅保留手工/空白叶子。')
lines.append('')
lines.append('## Key formula changes')
lines.append('- `R71 D40 本年增加`：父行不再重复纳入 93:103 资产展示段；改为只汇总 73:91 的现金流自然拆分，再扣 `附注!C691` / `底稿C241:C243`，与 `现金流量表!D40` 闭合。')
lines.append('- `R75/R76` 开发支出改放开发/其他列，`R83/R84` 其他非流动改放其他/抵消列，`R89/R90` 其他应付设备款改放其他/抵消列。')
lines.append('- `R105 D41 其他变动`：删除没有资产 sheet 支持的硬编码综合调整，保留资产明细行，主行回到 0。')
lines.append('- `R112 D42 折旧摊销`：主行改为 `-本期计提`（固定资产!H30、无形!I27、长摊!L17、投房!D29、使用权!G21），其他累折变动仅展示，不再净入 D42。')
lines.append('- `R137 D43 本年减少`：取消 `K137=现金流量表!D43` direct plug，改为 139:146 可见子项合计；处置净值、处置损益/Y22、报废毁损、销项税、减值逐项拆开。')
lines.append('- `R169 D44 年末数`：不再取 closing book snapshot；改为 `D39+D40+D41+D42+D43` 的分资产类别 roll-forward。')
lines.append('- `R171/R172`：同步修 D46 分类取数和 D47 依赖校验，防止 169 改完后下方仍显示旧差异。')
lines.append('')
lines.append('## Safety checks')
for k in ['orig_sha_before_after_same','base_sha_before_after_same','zip_testzip','calcChain_present','prefixes_preserved_after','shared_formula_cells_R64_R180','changed_parts','openpyxl_formula_ok','openpyxl_value_ok','main_rows_pass','libreoffice_smoke']:
    lines.append(f'- `{k}`: `{verify[k]}`')
lines.append('')
lines.append('## Main row closure')
lines.append('| Row | K | L | M | N | K formula |')
lines.append('|---:|---:|---:|---:|:--:|---|')
for r in [66,71,105,112,137,169,171,172,32,33,45,49,50,56,57,58]:
    rc=rows_check.get(r,{})
    lines.append(f"| {r} | {rc.get('K')} | {rc.get('L')} | {rc.get('M')} | {rc.get('N')} | `{rc.get('K_formula')}` |")
lines.append('')
lines.append('## Residual source notes')
lines.append(f'- `长期资产循环校验_公式明细` helper refs in R64:R180: `{len(helper_refs)}`')
lines.append(f'- `现金流量表底稿` refs in R64:R180: `{len(bottom_refs)}`; 保留项主要为 C193/C198/C199/C201/C203/C248:C250 等手工/空白叶子或现金流自然拆 benchmark。')
lines.append(f'- formula length > 120 in R64:R180: `{len(big_formulas)}`; 长公式主要为父行 SUM visible children，不是单格隐藏业务净额。')
lines.append('')
lines.append('## JSON')
lines.append('```json')
lines.append(json.dumps(verify, ensure_ascii=False, indent=2, default=str))
lines.append('```')
VERIFY.write_text('\n'.join(lines), encoding='utf-8')

print(json.dumps(verify, ensure_ascii=False, indent=2, default=str))