#!/usr/bin/env python3
"""
08_build_excel.py  ->  ../output/sequences.fasta
                       ../output/Trastuzumab_IgG1_GoldenGate_CHO_Data.xlsx

Order-ready deliverables: the 5 BsaI-flanked synthesis fragments and the 10
colony-PCR primers, plus the assembled plasmid, in FASTA and a styled workbook.
Every oligo/fragment sequence is taken from the pipeline JSONs so it is
byte-identical to the GenBank and the report.
"""
import json, os
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

HERE = os.path.dirname(os.path.abspath(__file__))
OUT = os.path.abspath(os.path.join(HERE, "..", "output"))
opt = json.load(open(os.path.join(HERE, "optimized_cds.json")))
fus = json.load(open(os.path.join(HERE, "fusion_sites.json")))
frag = json.load(open(os.path.join(HERE, "fragments.json")))
prim = json.load(open(os.path.join(HERE, "primers.json")))
spec = json.load(open(os.path.join(HERE, "primer_specificity.json")))
assembled = open(os.path.join(HERE, "assembled.fasta")).read().split("\n", 1)[1].replace("\n", "")

FRAG_ROLE = {"F1_promoter": "CMV enhancer/promoter + chimeric intron",
             "F2_heavy": "Kozak + Ig-kappa leader-trastuzumab heavy chain (IgG1)",
             "F3_IRES_light": "EMCV IRES + leader-trastuzumab light chain + WPRE + bGH pA",
             "F4_puro": "SV40 promoter + puromycin N-acetyltransferase + SV40 pA",
             "F5_backbone": "pUC origin + AmpR (bla)"}
ORDER = frag["fragment_order"]

# ---------------------------------------------------------------- FASTA
def wrap(s, w=70): return "\n".join(s[i:i+w] for i in range(0, len(s), w))
fa = [f">pTRAST_GG_assembled|{len(assembled)}bp|circular trastuzumab IgG1 CHO Golden Gate vector", wrap(assembled)]
for i, name in enumerate(ORDER, 1):
    f = frag["fragments"][name]
    fa.append(f">GGfrag_{i}_{name}|{f['synth_len']}bp|BsaI-flanked synthesis fragment|OH_L={f['left_oh']} OH_R={f['right_oh']}|{FRAG_ROLE[name]}")
    fa.append(wrap(f["synth"]))
for j, d in prim.items():
    tag = j.split('_')[0]
    fa.append(f">{tag}_F|colony-PCR fwd|{j}|Tm={d['forward']['tm']}C")
    fa.append(d["forward"]["seq"])
    fa.append(f">{tag}_R|colony-PCR rev|{j}|Tm={d['reverse']['tm']}C")
    fa.append(d["reverse"]["seq"])
open(os.path.join(OUT, "sequences.fasta"), "w").write("\n".join(fa) + "\n")

# ---------------------------------------------------------------- Excel
NAVY = "1B2A4A"; SHADE = "F5F5F5"; WHITE = "FFFFFF"
hdr_fill = PatternFill("solid", fgColor=NAVY); hdr_font = Font(bold=True, color=WHITE, size=10)
thin = Side(style="thin", color="D0D0D0"); border = Border(thin, thin, thin, thin)
mono = Font(name="Consolas", size=9)

wb = openpyxl.Workbook()
def sheet(title, headers, rows, widths, mono_cols=()):
    ws = wb.create_sheet(title)
    for c, h in enumerate(headers, 1):
        cell = ws.cell(1, c, h); cell.fill = hdr_fill; cell.font = hdr_font
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True); cell.border = border
    for r, row in enumerate(rows, 2):
        for c, val in enumerate(row, 1):
            cell = ws.cell(r, c, val); cell.border = border
            if (c - 1) in mono_cols: cell.font = mono
            if r % 2 == 0: cell.fill = PatternFill("solid", fgColor=SHADE)
    for c, w in enumerate(widths, 1):
        ws.column_dimensions[openpyxl.utils.get_column_letter(c)].width = w
    ws.row_dimensions[1].height = 30; ws.freeze_panes = "A2"
    return ws

wb.remove(wb.active)
# 1) Overview
sheet("Overview",
      ["Property", "Value"],
      [["Construct", "Single-vector trastuzumab IgG1 mAb expression vector (CHO-K1)"],
       ["Assembly method", "One-pot BsaI Golden Gate, 5 fragments"],
       ["Assembled size", f"{len(assembled)} bp (circular)"],
       ["Architecture", "CMV - intron - [SP-heavy chain] - EMCV IRES - [SP-light chain] - WPRE - bGH pA ; SV40p - puro - SV40 pA ; pUC ori ; AmpR"],
       ["Internal BsaI / BsmBI / SapI", "0 / 0 / 0 (verified in final product)"],
       ["Fusion-site set", " ".join(fus["overhangs"]) + f"  (predicted fidelity {fus['predicted_set_fidelity_pct']}%)"],
       ["Heavy chain CAI (CHO-K1)", opt["heavy_chain"]["CAI"]],
       ["Light chain CAI (CHO-K1)", opt["light_chain"]["CAI"]],
       ["pac CAI (CHO-K1)", opt["pac"]["CAI"]],
       ["Primer specificity vs CHO-K1 PICRH-1.0", spec["summary"]["verdict"]],
       ["Selection marker", "Puromycin (pac); E. coli: ampicillin"]],
      [42, 95])
# 2) Synthesis fragments
sheet("Synthesis fragments",
      ["Fragment", "Module", "Synth length (bp)", "Left overhang", "Right overhang", "BsaI-flanked sequence (5'->3', order this)"],
      [[f"GGfrag_{i}_{n}", FRAG_ROLE[n], frag["fragments"][n]["synth_len"],
        frag["fragments"][n]["left_oh"], frag["fragments"][n]["right_oh"], frag["fragments"][n]["synth"]]
       for i, n in enumerate(ORDER, 1)],
      [24, 52, 16, 13, 14, 90], mono_cols=(5,))
# 3) Colony-PCR primers
prows = []
for j, d in prim.items():
    tag = j.split('_')[0]
    prows.append([f"{tag}_F", d["forward"]["seq"], d["forward"]["len"], d["forward"]["tm"], d["forward"]["gc"],
                  j, f"{d['product_size']} bp", "verifies "+j])
    prows.append([f"{tag}_R", d["reverse"]["seq"], d["reverse"]["len"], d["reverse"]["tm"], d["reverse"]["gc"],
                  j, f"{d['product_size']} bp", ""])
sheet("Colony-PCR primers",
      ["Primer", "Sequence (5'->3')", "Len", "Tm (C)", "GC%", "Junction", "Amplicon", "Notes"],
      prows, [12, 30, 6, 8, 7, 20, 11, 22], mono_cols=(1,))
# 4) Fusion overhangs
sheet("Fusion overhangs",
      ["Junction", "Overhang (5'->3')", "GC (of 4)", "Palindrome?", "Notes"],
      [[j, o, fus["gc_per_overhang"][o], "no", "selected from Potapov/Pryor BsaI-HFv2 matrix"] for j, o in fus["assignment"].items()],
      [22, 18, 10, 12, 36], mono_cols=(1,))
# 5) Codon optimization
sheet("Codon optimization",
      ["ORF", "Length (aa)", "Length (bp)", "CAI (CHO-K1)", "GC%", "Internal BsaI/BsmBI/SapI"],
      [["Heavy chain (SP+VH+IgG1)", opt["heavy_chain"]["len_aa"], opt["heavy_chain"]["len_bp"], opt["heavy_chain"]["CAI"], opt["heavy_chain"]["GC_pct"], "0 / 0 / 0"],
       ["Light chain (SP+VL+Ck)", opt["light_chain"]["len_aa"], opt["light_chain"]["len_bp"], opt["light_chain"]["CAI"], opt["light_chain"]["GC_pct"], "0 / 0 / 0"],
       ["pac (PuroR)", opt["pac"]["len_aa"], opt["pac"]["len_bp"], opt["pac"]["CAI"], opt["pac"]["GC_pct"], "0 / 0 / 0"]],
      [30, 14, 14, 16, 8, 26])

wb.save(os.path.join(OUT, "Trastuzumab_IgG1_GoldenGate_CHO_Data.xlsx"))

# QC: oligo byte-identity FASTA<->Excel (read back)
fa_text = open(os.path.join(OUT, "sequences.fasta")).read()
ok = all(d["forward"]["seq"] in fa_text and d["reverse"]["seq"] in fa_text for d in prim.values())
print("wrote sequences.fasta and Trastuzumab_IgG1_GoldenGate_CHO_Data.xlsx")
print("all primer seqs present in FASTA (byte-identity source):", ok)
print(f"FASTA records: 1 plasmid + {len(ORDER)} fragments + {2*len(prim)} primers")
