"""Export matched experimental data and ESMFold structures; never substitute sequences."""
import csv
import hashlib
import json
import shutil
from pathlib import Path

ROOT=Path(__file__).resolve().parents[1]
AUDIT=ROOT.parent/'Ouroboros_Showcase_Research/wetlab_20260927'
PUBLIC=ROOT/'public'
AA=dict(zip('ALA ARG ASN ASP CYS GLN GLU GLY HIS ILE LEU LYS MET PHE PRO SER THR TRP TYR VAL UNK'.split(),'ARNDCQEGHILKMFPSTWYVX'))

def digest(path):return hashlib.sha256(path.read_bytes()).hexdigest()
def save(path,data):path.write_text(json.dumps(data,ensure_ascii=False,indent=2)+'\n')

def main():
    import xlrd
    original=AUDIT/'PETde实验情况20260927.xls'
    sheet=xlrd.open_workbook(original,use_mmap=False).sheet_by_index(0)
    sequences=json.loads((AUDIT/'sequence_mapping.json').read_text())
    generation=json.loads((AUDIT/'de2_generation.json').read_text())
    manifest=json.loads((AUDIT/'fold_results/manifest.json').read_text())
    ids=['de2','rcsb_5XG0_IsPETase','rcsb_4EB0_LCC','rcsb_7EOA_BhrPETase']
    assert len(manifest)==len(ids)
    names={'rcsb_5XG0_IsPETase':('IsPETase','Y1','5XG0'),'rcsb_4EB0_LCC':('LCC','Y2','4EB0'),'rcsb_7EOA_BhrPETase':('BhrPETase','Y3','7EOA')}
    cases=[]
    for index,fid in enumerate(ids):
        row=next(r for r in sequences if r['id']==fid)
        fold=next(r for r in manifest if r['sequence_id']==f'{index:04d}')
        seq=row['sequence'];assert hashlib.sha256(seq.encode()).hexdigest()==fold['source_pred_seq_sha256']
        pdb=AUDIT/'fold_results'/fold['pdb_file']
        atoms=[line for line in pdb.read_text().splitlines() if line.startswith('ATOM') and line[12:16].strip()=='CA']
        assert ''.join(AA[s[17:20]] for s in atoms)==seq
        residues=[dict(index=int(s[22:26]),chain=s[21].strip(),code=AA[s[17:20]],confidence=float(s[60:66])) for s in atoms]
        design=fid=='de2'
        name,sample,pdb_id=('de2','de2',None) if design else names[fid]
        if design:assert generation['sequence'].replace(' ','')==seq
        provenance=(f"Exact sequence match: submitted de2 FASTA → group_02.part_1530 → petase_lora_2891 → lora_petase_3epoch/test/test_result.json, prediction index {generation['predictionIndex0']} (zero-based). Original prompt recovered from the same generation record." if design else f"Supplied wild-type reference FASTA: {fid}.fasta, labeled {pdb_id}. Matched by name to assay sample {sample}. This view is a new ESMFold prediction of the supplied sequence.")
        case=dict(id=name,assetId='wetlab-'+fid,title=name,kind='design' if design else 'wildtype',modelLabel='LoRA-generated' if design else 'WT reference',assaySample=sample,cohort='PETase wet-lab report · 2026-09-27',rank=1 if design else 0,cohortSize=4,sequence=seq,length=len(seq),residues=residues,prompt=generation['prompt'] if design else '',plddt=sum(r['confidence'] for r in residues)/len(residues),pae=fold['mean_pae'],pdbUrl=f'/structures/wetlab-{fid}.pdb',fastaUrl=f'/structures/wetlab-{fid}.fasta',selection='Selected by highest reported TPA + MHET among the four measured designs.' if design else f'Wild-type reference {name}, assay sample {sample}.',structureStatus='ESMFold-v1 prediction of submitted FASTA',experimentalStatus='Reported TPA and MHET concentrations are available in the 2026-09-27 workbook. Assay conditions and replication are awaiting confirmation.',provenance=provenance,pdbSha256=digest(pdb),sequenceSha256=row['sha256'],foldJob='ouroboros-wetlab-de2-1009b',foldMethod='ESMFold-v1',residueConfidenceDefinition='Mean of CA B factors in the exported ESMFold PDB')
        shutil.copyfile(pdb,PUBLIC/'structures'/f'wetlab-{fid}.pdb')
        cases.append(case)
    audit=json.loads((AUDIT/'assay_audit.json').read_text())
    for row in audit['rows']:
        i=row['sourceRow']-1
        assert sheet.cell_value(i,1)==row['id']
        for col,key in [(3,'tpa'),(4,'mhet'),(5,'total')]:assert sheet.cell_value(i,col)==row[key]
        assert abs(row['tpa']+row['mhet']-row['total'])<1e-7
    for row in sequences:shutil.copyfile(AUDIT/(row['id']+'.fasta'),PUBLIC/'structures'/('wetlab-'+row['id']+'.fasta'))
    audit['generation']={k:v for k,v in generation.items() if k not in ['referenceSequence','sequence']}
    audit['structurePrediction']={'method':'ESMFold-v1','job':'ouroboros-wetlab-de2-1009b','manifestSha256':digest(AUDIT/'fold_results/manifest.json'),'date':'2026-10-09','experimentalStructure':False}
    save(PUBLIC/'data/wetlab.json',audit)
    save(PUBLIC/'data/petase-wetlab-structures.json',cases)
    save(PUBLIC/'data/petase.json',[c for c in cases if c['id'] in ['de2','IsPETase']])
    provenance=json.loads((PUBLIC/'data/provenance.json').read_text())
    provenance['petase']='de2 from the LoRA 3-epoch generation archive; featured by reported wet-lab product concentration. Three provided WT reference sequences are included. Earlier confidence-ranked candidates remain in petase-archive.json.'
    provenance['wetlab']={'report':audit['source'],'sequenceMatches':[{'id':r['id'],'sha256':r['sha256'],'generationMatches':r.get('exactMatches',[])} for r in sequences],'structurePrediction':audit['structurePrediction']}
    save(PUBLIC/'data/provenance.json',provenance)
    print(json.dumps([{'id':r['id'],'length':r['length'],'plddt':r['plddt']} for r in cases],indent=2))

if __name__=='__main__':main()