#!/usr/bin/env python3
"""
08_build_data.py -- assemble client deliverables: sequences.fasta + the Excel workbook.
All oligo/donor sequences are pulled from the pipeline JSONs (single source of truth),
so they are byte-identical to what the report and figures use.
Outputs: output/sequences.fasta, output/TP53_mCherry_Knockin_Design.xlsx
"""
import json, os, textwrap
from Bio.Seq import Seq
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

HERE = os.path.dirname(os.path.abspath(__file__))
OUT = os.path.join(HERE, "..", "output")
locus = json.load(open(os.path.join(HERE, "locus.json")))
gd = {g["id"]: g for g in json.load(open(os.path.join(HERE, "guide_candidates.json")))["guides"]}
cris = json.load(open(os.path.join(HERE, "crispor_summary.json")))["guides"]
offt = json.load(open(os.path.join(HERE, "offtarget_results.json")))
ff = json.load(open(os.path.join(HERE, "flashfry_summary.json")))["guides"]
vs = json.load(open(os.path.join(HERE, "variant_screen.json")))
donor = json.load(open(os.path.join(HERE, "donor.json")))
prim = json.load(open(os.path.join(HERE, "primers.json")))["sets"]
mch = json.load(open(os.path.join(HERE, "mcherry_opt.json")))

def rc(s): return str(Seq(s).reverse_complement())

def clone_oligos(spacer):
    """pX330/pSpCas9(BB)-family BbsI cloning oligos. U6 needs a 5' G (+1)."""
    if spacer[0] == "G":
        return ("CACC" + spacer, "AAAC" + rc(spacer), "native 5' G")
    return ("CACCG" + spacer, "AAAC" + rc(spacer) + "C", "5' G appended for U6 (+1)")

# ---------------- FASTA ----------------
g3, g2 = gd["TP53ct_g3"], gd["TP53ct_g2"]
records = [
    ("TP53ct_g3_spacer_PRIMARY", g3["spacer"], f"SpCas9 primary guide, - strand, PAM {g3['pam']}, cut {g3['dist_to_insert']} bp from insertion; MIT 70 / CFD 87"),
    ("TP53ct_g2_spacer_BACKUP",  g2["spacer"], f"SpCas9 backup guide, + strand, PAM {g2['pam']}, cut {g2['dist_to_insert']} bp from insertion; MIT 55 / CFD 73"),
    ("mCherry_human_codon_opt_CDS_noStop", mch["mcherry_nt"], f"mCherry 236 aa, human codon-optimized, 0 BsaI/BsmBI, GC {mch['mcherry_gc']}%, NO stop (reuses native TGA)"),
    ("GS_linker_GGGGSx3", mch["linker_nt"], "flexible (GGGGS)x3 linker, codon-diversified"),
    ("insert_linker_mCherry", donor["insert_nt"], f"in-frame insert = (GGGGS)x3 + mCherry, {donor['insert_len']} nt, 0 BsaI/BsmBI"),
    ("HDR_donor_full_2353bp", donor["donor_nt"], "complete dsDNA HDR donor: 5'arm(800) + insert(753) + 3'arm(800); g2 silent PAM-block included"),
    ("HDR_5prime_homology_arm", donor["arm5_nt"], donor["arm5_chr17"]),
    ("HDR_3prime_homology_arm", donor["arm3_nt"], donor["arm3_chr17"]),
]
p5, p3, pin = prim["5_prime_junction"], prim["3_prime_junction"], prim["internal_across_insert"]
for nm, p, role in [("primer_5pJxn_FWD", p5, "5' junction (genomic, outside 5' arm)"),
                    ("primer_5pJxn_REV", p5, "5' junction (in insert)"),
                    ("primer_3pJxn_FWD", p3, "3' junction (in insert)"),
                    ("primer_3pJxn_REV", p3, "3' junction (genomic, outside 3' arm)"),
                    ("primer_internal_FWD", pin, "internal/zygosity (5' arm)"),
                    ("primer_internal_REV", pin, "internal/zygosity (3' arm)")]:
    seq = p["fwd"] if nm.endswith("FWD") else p["rev"]
    records.append((nm, seq, role))
# guide cloning oligos
for gid, g in [("g3", g3), ("g2", g2)]:
    top, bot, note = clone_oligos(g["spacer"])
    records.append((f"{gid}_clone_BbsI_TOP", top, f"pX330-family BbsI cloning, sense ({note})"))
    records.append((f"{gid}_clone_BbsI_BOT", bot, "pX330-family BbsI cloning, antisense"))

with open(os.path.join(OUT, "sequences.fasta"), "w") as fh:
    for name, seq, desc in records:
        fh.write(f">{name} {desc}\n")
        for i in range(0, len(seq), 70):
            fh.write(seq[i:i+70] + "\n")
print(f"[fasta] {len(records)} records -> output/sequences.fasta")

# ---------------- Excel ----------------
NAVY = "1B2A4A"; WHITE = "FFFFFF"; LGRAY = "F5F5F5"
hdr_font = Font(bold=True, color=WHITE, size=10); hdr_fill = PatternFill("solid", fgColor=NAVY)
title_font = Font(bold=True, color=NAVY, size=13); bold = Font(bold=True)
thin = Side(style="thin", color="CCCCCC"); border = Border(thin, thin, thin, thin)
wrap = Alignment(wrap_text=True, vertical="top")
mono = Font(name="Consolas", size=9)

wb = Workbook(); wb.remove(wb.active)

def sheet(name, title, headers, rows, widths, mono_cols=()):
    ws = wb.create_sheet(name)
    ws["A1"] = title; ws["A1"].font = title_font
    hr = 3
    for j, h in enumerate(headers, 1):
        c = ws.cell(hr, j, h); c.font = hdr_font; c.fill = hdr_fill
        c.alignment = Alignment(wrap_text=True, vertical="center", horizontal="center"); c.border = border
    for i, row in enumerate(rows):
        for j, val in enumerate(row, 1):
            c = ws.cell(hr+1+i, j, val); c.border = border; c.alignment = wrap
            if (j-1) in mono_cols: c.font = mono
            if i % 2 == 1: c.fill = PatternFill("solid", fgColor=LGRAY)
    for j, w in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(j)].width = w
    ws.freeze_panes = ws.cell(hr+1, 1)
    return ws

# 1) Summary
sumrows = [
    ("Target gene", "TP53 (tumor protein p53)"),
    ("Transcript / build", f"{locus['accession']} / {locus['genome_build']}"),
    ("Edit", "Endogenous C-terminal mCherry knock-in (fusion before native stop)"),
    ("Cell line", "HEK293T (hypotriploid; expresses SV40 large T antigen -- see report caveat)"),
    ("Nuclease", "SpCas9 (NGG PAM), HDR donor"),
    ("Insertion point", f"chr17:{locus['native_stop_chr17_1based'][0]} (before native stop, minus strand)"),
    ("Primary guide", f"TP53ct_g3  {g3['spacer']} ({g3['pam']}); cut {g3['dist_to_insert']} bp from insertion; MIT 70 / CFD 87"),
    ("Backup guide", f"TP53ct_g2  {g2['spacer']} ({g2['pam']}); cut {g2['dist_to_insert']} bp; MIT 55 / CFD 73"),
    ("Off-target tools", "Cas-OFFinder (exhaustive, hg38) + CRISPOR + FlashFry -- 3 independent tools cross-validated; 0 pseudogene/p53-family hits"),
    ("Binding-site SNPs", f"Ensembl Variation (GRCh38): {vs['n_variants']} cataloged variants under g3/g2, none common (MAF>=1%) -- no allele dropout (PASS)"),
    ("HDR donor", f"{donor['donor_len']} bp dsDNA; arms ~800 bp; (GGGGS)x3 linker; mCherry human codon-opt, 0 BsaI/BsmBI"),
    ("Re-cut protection", "g3: insertion splits protospacer (none needed). g2: silent PAM-block GGG->GGT (Gly)."),
    ("Genotyping", f"5' jxn {p5['product_size_ki']} bp + 3' jxn {p3['product_size_ki']} bp (KI-only); internal WT {pin['product_size_wt']} / KI {pin['product_size_ki']} bp"),
]
ws = sheet("Summary", "TP53-mCherry C-terminal knock-in -- design summary",
           ["Field", "Value"], sumrows, [22, 110])

# 2) Guides
gorder = ["TP53ct_g3","TP53ct_g2","TP53ct_g4","TP53ct_g1","TP53ct_g5","TP53ct_g6","TP53ct_g7"]
grows = []
for gid in gorder:
    g = gd[gid]; cr = cris[gid]; o = offt.get(gid, {})
    role = "PRIMARY" if gid=="TP53ct_g3" else ("backup" if gid=="TP53ct_g2" else "not recommended")
    f = ff.get(gid, {})
    grows.append((gid, role, g["spacer"], g["pam"], g["strand"], g["dist_to_insert"], g["gc"],
                  "yes" if g["starts_with_G"] else "no",
                  cr.get("mit_spec"), cr.get("cfd_spec"), cr.get("doench16"),
                  o.get("n_offtargets","-"), o.get("n_exonic","-"),
                  f.get("otCount","-"), round(f["hsu_spec"],1) if f.get("hsu_spec") is not None else "-"))
sheet("Guides", "Candidate guides at the TP53 stop codon -- scores cross-validated across 3 off-target tools",
      ["Guide","Role","Spacer 5'->3' (20 nt)","PAM","Strand","Cut dist to insert (bp)","GC%","5' G",
       "CRISPOR MIT","CRISPOR CFD","Doench'16","Cas-OFFinder OT","exonic OT","FlashFry OT","FlashFry Hsu"],
      grows, [12,15,26,7,7,12,7,6,11,11,9,13,9,11,11], mono_cols=(2,))

# 3) Off-targets g3 + g2 (top, from Cas-OFFinder w/ CRISPOR cross-ref note)
otrows = []
for gid in ("TP53ct_g3","TP53ct_g2"):
    for o in offt[gid]["top_offtargets"][:10]:
        otrows.append((gid, f"chr{o['chrom'].replace('chr','')}:{o['pos1']}", o["strand"], o["pam_class"], o["mm"],
                       ",".join(map(str,o["seed_mm"])) or "-", o["gene"] or "(intergenic)", o["region"], o["matched"]))
sheet("Off-targets (top)", "Lowest-mismatch off-targets (Cas-OFFinder; cross-validated by CRISPOR)",
      ["Guide","Locus (hg38)","Strand","PAM class","Mismatches","Seed mm pos","Gene","Region","Aligned (lowercase=mm)"],
      otrows, [12,20,8,9,11,11,16,10,26], mono_cols=(8,))

# 4) Donor components
dc = [
    ("5' homology arm", len(donor["arm5_nt"]), donor["arm5_chr17"], "genomic; incl. g2 silent PAM-block"),
    ("(GGGGS)x3 linker", len(mch["linker_nt"]), "insert", "codon-diversified, flexible"),
    ("mCherry CDS (no stop)", len(mch["mcherry_nt"]), "insert", f"human codon-opt, GC {mch['mcherry_gc']}%, 0 BsaI/BsmBI"),
    ("3' homology arm", len(donor["arm3_nt"]), donor["arm3_chr17"], "genomic; native TGA stop + 3'UTR"),
    ("FULL DONOR", donor["donor_len"], "5'arm+insert+3'arm", f"BsaI sites in donor: {len(donor['donor_bsaI'])} (arms only); BsmBI: {len(donor['donor_bsmBI'])} (arms only)"),
]
sheet("Donor components", "HDR donor architecture (2,353 bp dsDNA)",
      ["Component","Length (bp)","Location / context","Notes"], dc, [22,12,40,55])

# 5) Primers -- one row per pair (no blank cells)
prows = []
for key, lbl in [("5_prime_junction","5' junction"),("3_prime_junction","3' junction"),("internal_across_insert","internal/zygosity")]:
    p = prim[key]
    wt = p.get("product_size_wt")
    prows.append((lbl, p["fwd"], f"{p['fwd_tm']} C", p["rev"], f"{p['rev_tm']} C",
                  f"{p['product_size_ki']} bp", f"{wt} bp" if wt is not None else "none (KI-specific)"))
sheet("Primers", "Genotyping & Sanger primers (isPcr-verified unique on hg38)",
      ["Assay","Forward 5'->3'","Fwd Tm","Reverse 5'->3'","Rev Tm","KI product","WT product"],
      prows, [22,32,10,32,10,14,16], mono_cols=(1,3))

# 6) Guide cloning oligos
crows = []
for gid, g in [("TP53ct_g3 (primary)", g3), ("TP53ct_g2 (backup)", g2)]:
    top, bot, note = clone_oligos(g["spacer"])
    crows.append((gid+" TOP", top, note))
    crows.append((gid+" BOT", bot, "anneal with TOP -> ligate into BbsI-cut vector"))
sheet("Guide cloning oligos", "Guide cloning oligos -- pX330 / pSpCas9(BB) / px458 (BbsI)",
      ["Oligo","Sequence 5'->3'","Note"], crows, [22,34,46], mono_cols=(1,))

xlsx = os.path.join(OUT, "TP53_mCherry_Knockin_Design.xlsx")
wb.save(xlsx)
print(f"[xlsx] {len(wb.sheetnames)} sheets -> {os.path.basename(xlsx)}: {wb.sheetnames}")
