#!/usr/bin/env python3
"""Read/save experiment on a COPY. Does not execute Excel or refresh queries."""
import argparse,hashlib,json,platform,time,warnings
from pathlib import Path
from zipfile import ZipFile
from xml.etree import ElementTree as ET
import openpyxl

NS={'x':'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
def sha(path):return hashlib.sha256(path.read_bytes()).hexdigest()
def inspect(path):
    with ZipFile(path) as z:
        parts=sorted(z.namelist());formula_count=0;formula_values=0
        for name in parts:
            if name.startswith('xl/worksheets/sheet') and name.endswith('.xml'):
                root=ET.fromstring(z.read(name))
                for cell in root.findall('.//x:c',NS):
                    if cell.find('x:f',NS) is not None:
                        formula_count+=1
                        v=cell.find('x:v',NS)
                        if v is not None and v.text is not None:formula_values+=1
        return dict(sha256=sha(path),bytes=path.stat().st_size,parts=parts,formulas=formula_count,formulas_with_saved_value=formula_values,
                    connection_parts=[n for n in parts if n.startswith('xl/connections')],query_table_parts=[n for n in parts if n.startswith('xl/queryTables/') and n.endswith('.xml')],
                    custom_xml_parts=[n for n in parts if n.startswith('customXml/')],
                    chart_parts=[n for n in parts if n.startswith('xl/charts/chart') and n.endswith('.xml')],
                    pivot_parts=[n for n in parts if n.startswith('xl/pivotTables/') and n.endswith('.xml')])
def run(source,output):
    if output.resolve()==source.resolve() or output.exists():raise ValueError('Use a new output path; never overwrite the source or an existing result')
    before=inspect(source)
    with warnings.catch_warnings(record=True) as caught:
        warnings.simplefilter('always')
        start=time.perf_counter();wb=openpyxl.load_workbook(source)
        loaded=time.perf_counter();wb.save(output);saved=time.perf_counter();wb.close()
    after=inspect(output)
    assert sha(source)==before['sha256'],'Source file changed'
    return dict(experiment='openpyxl-no-edit-round-trip',python=platform.python_version(),openpyxl=openpyxl.__version__,platform=platform.system(),source_filename=source.name,
                before=before,after=after,removed_parts=sorted(set(before['parts'])-set(after['parts'])),added_parts=sorted(set(after['parts'])-set(before['parts'])),
                warnings=sorted(set(str(w.message) for w in caught)),load_seconds=round(loaded-start,6),save_seconds=round(saved-loaded,6),
                interpretation='One workbook, one no-edit load/save. ZIP inspection only; output not validated by native Excel. Timings are local Python wall time, not Panes runtime, refresh time or task cost.')
if __name__=='__main__':
    p=argparse.ArgumentParser();p.add_argument('source',type=Path);p.add_argument('output',type=Path);p.add_argument('--report',type=Path,required=True);a=p.parse_args()
    r=run(a.source,a.output);a.report.write_text(json.dumps(r,indent=2)+'\n');print(json.dumps({k:r[k] for k in ['experiment','python','openpyxl','removed_parts','added_parts','warnings','load_seconds','save_seconds']},indent=2))