from docx import Document
from docx.shared import Pt, Inches, RGBColor
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.oxml.ns import qn
from docx.oxml import OxmlElement
import copy

doc = Document()

# ── Page margins ──────────────────────────────────────────────────────────────
section = doc.sections[0]
section.top_margin    = Inches(1.0)
section.bottom_margin = Inches(1.0)
section.left_margin   = Inches(1.15)
section.right_margin  = Inches(1.15)

# ── Base font ─────────────────────────────────────────────────────────────────
normal = doc.styles["Normal"]
normal.font.name = "Calibri"
normal.font.size = Pt(11)

# ── Heading styles ────────────────────────────────────────────────────────────
h1 = doc.styles["Heading 1"]
h1.font.name  = "Calibri"
h1.font.size  = Pt(16)
h1.font.bold  = True
h1.font.color.rgb = RGBColor(0x1F, 0x49, 0x7D)

h2 = doc.styles["Heading 2"]
h2.font.name  = "Calibri"
h2.font.size  = Pt(13)
h2.font.bold  = True
h2.font.color.rgb = RGBColor(0x2E, 0x74, 0xB5)

h3 = doc.styles["Heading 3"]
h3.font.name  = "Calibri"
h3.font.size  = Pt(11)
h3.font.bold  = True
h3.font.color.rgb = RGBColor(0x2E, 0x74, 0xB5)

# ── Helper: add a horizontal rule ─────────────────────────────────────────────
def add_hr(doc):
    p = doc.add_paragraph()
    pPr = p._p.get_or_add_pPr()
    pBdr = OxmlElement("w:pBdr")
    bottom = OxmlElement("w:bottom")
    bottom.set(qn("w:val"),   "single")
    bottom.set(qn("w:sz"),    "6")
    bottom.set(qn("w:space"), "1")
    bottom.set(qn("w:color"), "2E74B5")
    pBdr.append(bottom)
    pPr.append(pBdr)
    p.paragraph_format.space_after = Pt(2)

# ── Helper: coloured label paragraph ─────────────────────────────────────────
def add_label(doc, text):
    p = doc.add_paragraph()
    run = p.add_run(text)
    run.bold = True
    run.font.color.rgb = RGBColor(0x1F, 0x49, 0x7D)
    run.font.size = Pt(10)
    p.paragraph_format.space_before = Pt(6)
    p.paragraph_format.space_after  = Pt(2)
    return p

# ── Helper: body paragraph ────────────────────────────────────────────────────
def add_body(doc, text, space_after=Pt(6)):
    p = doc.add_paragraph(text)
    p.paragraph_format.space_after  = space_after
    p.paragraph_format.space_before = Pt(0)
    p.paragraph_format.first_line_indent = Pt(0)
    return p

# ── Helper: bullet paragraph ─────────────────────────────────────────────────
def add_bullet(doc, text):
    p = doc.add_paragraph(style="List Bullet")
    p.add_run(text)
    p.paragraph_format.space_after  = Pt(2)
    p.paragraph_format.space_before = Pt(0)
    return p

# ══════════════════════════════════════════════════════════════════════════════
#  TITLE PAGE
# ══════════════════════════════════════════════════════════════════════════════
doc.add_paragraph()  # spacer

title_p = doc.add_paragraph()
title_p.alignment = WD_ALIGN_PARAGRAPH.CENTER
run = title_p.add_run("Mesenteric Ischaemia: Research Topics\nfor Clinical Audit & Cohort Studies")
run.font.name  = "Calibri"
run.font.size  = Pt(22)
run.font.bold  = True
run.font.color.rgb = RGBColor(0x1F, 0x49, 0x7D)
title_p.paragraph_format.space_after = Pt(10)

sub_p = doc.add_paragraph()
sub_p.alignment = WD_ALIGN_PARAGRAPH.CENTER
sub_r = sub_p.add_run("A Guide for Vascular Surgery Trainees")
sub_r.font.name  = "Calibri"
sub_r.font.size  = Pt(13)
sub_r.font.color.rgb = RGBColor(0x2E, 0x74, 0xB5)
sub_p.paragraph_format.space_after = Pt(4)

date_p = doc.add_paragraph()
date_p.alignment = WD_ALIGN_PARAGRAPH.CENTER
date_r = date_p.add_run("August 2026")
date_r.font.name  = "Calibri"
date_r.font.size  = Pt(11)
date_r.font.color.rgb = RGBColor(0x59, 0x59, 0x59)

add_hr(doc)
doc.add_paragraph()  # spacer

# ══════════════════════════════════════════════════════════════════════════════
#  INTRODUCTION
# ══════════════════════════════════════════════════════════════════════════════
doc.add_heading("Introduction", level=1)
add_body(doc,
    "Mesenteric ischaemia remains one of the most challenging conditions in vascular surgery, "
    "with acute mesenteric ischaemia (AMI) carrying a mortality of 50-80% despite advances in "
    "endovascular technology. The 2025 ESVS Clinical Practice Guidelines on Mesenteric and Renal "
    "Artery Diseases represent the most current consensus, yet multiple unresolved clinical questions "
    "persist. A 2025 systematic review and meta-analysis (Reintam Blaser et al., World Journal of "
    "Emergency Surgery) explicitly identified severity-of-illness reporting and subtype classification "
    "as the most critical gaps precluding meaningful treatment comparisons."
)
add_body(doc,
    "The following ten topics represent genuine research gaps suitable for a clinical audit, "
    "retrospective cohort, or prospective cohort study during a Vascular Surgery rotation. Each topic "
    "is grounded in current evidence, practically feasible within a single centre, and carries "
    "meaningful potential for publication or quality improvement."
)

# ══════════════════════════════════════════════════════════════════════════════
#  TOPICS
# ══════════════════════════════════════════════════════════════════════════════

topics = [
    {
        "num": "1",
        "title": "Time-to-CT-Angiography and Its Impact on Mortality in Acute Mesenteric Ischaemia",
        "type": "Retrospective Cohort / Clinical Audit",
        "rationale": (
            "AMI carries a mortality of 50-80% and 'pain out of proportion to examination' is a "
            "notoriously subtle presentation. A 2023 retrospective study (Magnus et al., Annals of "
            "Vascular Surgery, PMID 36549474) found that management delays were independently "
            "associated with mortality and called for dedicated institutional programmes. The 2025 "
            "WJES systematic review (Reintam Blaser et al., PMID 40275298) explicitly identified "
            "failure to account for time variables as a major gap precluding meaningful treatment "
            "comparisons. Most datasets do not track pre-hospital and in-hospital diagnostic delays "
            "with sufficient granularity, meaning local data could replicate or refute the 'dedicated "
            "programme' recommendation."
        ),
        "measures": [
            "Door-to-CT-angiography time, broken down by referral pathway (GP, ED, ward deterioration)",
            "Time from CT to revascularisation or theatre decision",
            "Whether ward-based presentations (post-cardiac surgery, ICU) have longer delays than ED arrivals",
            "30-day mortality stratified by time quartiles",
        ],
        "gap": (
            "Most datasets do not track pre-hospital and in-hospital diagnostic delays with granularity. "
            "Local institutional data could directly inform pathway redesign and support the case for "
            "a dedicated mesenteric ischaemia protocol."
        ),
    },
    {
        "num": "2",
        "title": "Accuracy of Serum Lactate as a Triage Biomarker for AMI - Audit Against CT-Angiography",
        "type": "Retrospective Diagnostic Accuracy Audit",
        "rationale": (
            "Serum lactate is widely used as a proxy for gut ischaemia, but its sensitivity and "
            "specificity for AMI specifically remain poorly defined. Reviews by Mihaileanu et al. "
            "(Diagnostics, 2024, PMID 38611583) and Zafirovski et al. (Biomedicines, 2024, PMID "
            "38255192) confirm no single biomarker reliably distinguishes AMI from other abdominal "
            "emergencies early enough to change management. The 2025 ESVS guidelines highlight this "
            "diagnostic uncertainty. Locally, an audit can assess who had lactate measured, what "
            "threshold was used to escalate to CTA, and how many AMI diagnoses were initially missed "
            "because lactate was normal or borderline."
        ),
        "measures": [
            "Sensitivity and specificity of lactate at various cut-offs (>2, >4 mmol/L) for confirmed AMI",
            "Time from lactate result to CTA request",
            "Number of missed or delayed diagnoses with initially normal lactate",
            "Proportion of confirmed AMI cases with lactate below commonly used thresholds",
        ],
        "gap": (
            "There are no prospective institutional datasets validating a local lactate pathway for "
            "AMI triage. This is directly generalisable to your unit's practice guidelines and could "
            "form the basis of a departmental protocol change."
        ),
    },
    {
        "num": "3",
        "title": "Endovascular vs Open Revascularisation for Arterial AMI - A Severity-Adjusted Single-Centre Cohort",
        "type": "Retrospective Cohort",
        "rationale": (
            "The most debated question in AMI management. A 2024 meta-analysis by Shi et al. (Journal "
            "of Vascular Surgery, PMID 39069018) found endovascular-first was favoured, but could not "
            "adequately adjust for disease severity. The 2025 WJES systematic review (PMID 40275298) "
            "concluded that available studies 'show different directions of the treatment effect' and "
            "that severity of illness is almost universally unreported, making comparisons meaningless. "
            "A severity-adjusted single-centre series - collecting SOFA/APACHE-II scores, peritonism "
            "status, and CT findings alongside treatment strategy - adds more value than larger "
            "unadjusted pooled datasets."
        ),
        "measures": [
            "Primary outcome: 30-day mortality by treatment strategy (endovascular vs open vs hybrid)",
            "Secondary outcomes: bowel resection rate, ICU length of stay, 90-day survival",
            "Covariates: SOFA score at presentation, peritonism at diagnosis, peak lactate, CT severity "
            "(pneumatosis, portal venous gas, extent of mural change), AMI subtype (embolic vs thrombotic vs NOMI)",
        ],
        "gap": (
            "Most single-centre papers do not stratify by severity of illness. Even a small, well-characterised "
            "cohort with prospectively recorded severity data is more informative than large pooled "
            "meta-analyses without it - directly addressing the ESVS and WJES call for standardised reporting."
        ),
    },
    {
        "num": "4",
        "title": "Non-Occlusive Mesenteric Ischaemia (NOMI) - Incidence, Triggers and Outcomes in ICU and Post-Cardiac Surgery Patients",
        "type": "Retrospective Cohort",
        "rationale": (
            "NOMI is the most under-recognised AMI subtype - occurring without arterial occlusion and "
            "triggered by low-flow states (vasopressors, cardiac surgery, septic shock). A 2024 matched-pair "
            "analysis in burns patients (Bucher et al., Burns, PMID 39442475) highlighted the lack of "
            "treatment algorithms for NOMI. The 2025 ESVS guidelines include a full NOMI section but "
            "acknowledge evidence is very sparse and largely limited to case series. NOMI is particularly "
            "relevant in vascular units as it frequently occurs post-aortic surgery and after endovascular "
            "interventions requiring renal artery coverage."
        ),
        "measures": [
            "Rate of NOMI among all ICU admissions and post-cardiac/vascular surgery patients",
            "Vasopressor dose and duration prior to diagnosis",
            "Time from vasopressor initiation to NOMI diagnosis",
            "Whether intra-arterial papaverine was used, and associated outcomes",
            "Mortality compared with other AMI subtypes",
        ],
        "gap": (
            "NOMI incidence is likely underdiagnosed in surgical units. An audit may identify practice "
            "improvement opportunities and contribute rare institutional outcome data to a field largely "
            "dependent on small case series."
        ),
    },
    {
        "num": "5",
        "title": "Mesenteric Venous Thrombosis (MVT) - DOAC vs Warfarin and Recanalisation Rates",
        "type": "Retrospective Cohort",
        "rationale": (
            "MVT is the venous cause of mesenteric ischaemia, managed primarily with anticoagulation. "
            "A 2024 comparative study (Kim et al., Journal of Vascular Surgery - Venous and Lymphatic "
            "Disorders, PMID 38754777) comparing VKA vs NOAC showed comparable outcomes, but the dataset "
            "was small and heterogeneous. The 2025 ESVS guidelines acknowledge that DOAC data in MVT is "
            "extrapolated from DVT/PE trials and call for dedicated MVT-specific studies. Duration of "
            "anticoagulation is also undefined - provoked vs unprovoked MVT may warrant different durations, "
            "analogous to current DVT management frameworks."
        ),
        "measures": [
            "Recanalisation rate on follow-up imaging by anticoagulant type (DOAC vs warfarin)",
            "Recurrence, re-hospitalisation, and bowel resection rates",
            "Duration of anticoagulation prescribed and patient adherence",
            "Classification as provoked vs unprovoked MVT and whether this influenced duration of treatment",
        ],
        "gap": (
            "DOAC adoption in MVT is occurring by extrapolation from other venous thromboembolic disease "
            "evidence. Real-world institutional data directly informs this debate and could inform local "
            "anticoagulation guideline development."
        ),
    },
    {
        "num": "6",
        "title": "Compliance with ESVS 2025 Guideline Recommendations - A Clinical Audit",
        "type": "Clinical Audit (Retrospective)",
        "rationale": (
            "The ESVS 2025 Clinical Practice Guidelines on Diseases of the Mesenteric and Renal Arteries "
            "and Veins (Koelemay et al., European Journal of Vascular and Endovascular Surgery, 2025) "
            "represent the most current consensus with 102 recommendations. Auditing adherence to key "
            "Class I recommendations against local practice is a straightforward, high-yield audit that "
            "generates a baseline for a formal re-audit cycle. Guideline implementation is consistently "
            "poor in time-critical vascular emergencies, and demonstrating a gap is itself a publishable "
            "quality improvement finding."
        ),
        "measures": [
            "Was CTA performed as first-line imaging in suspected AMI cases?",
            "Was a vascular surgeon involved in all confirmed mesenteric ischaemia cases?",
            "Were MVT patients anticoagulated within 24 hours of diagnosis?",
            "Were chronic mesenteric ischaemia (CMI) patients discussed at a vascular MDT before intervention?",
            "Was subtype classification (embolic/thrombotic/NOMI/venous) documented?",
        ],
        "gap": (
            "Guideline implementation in AMI is consistently poor across published series. An audit "
            "generates actionable quality improvement data with immediate clinical relevance and creates "
            "a baseline for a re-audit cycle - a core component of clinical governance."
        ),
    },
    {
        "num": "7",
        "title": "Colonic Ischaemia After Aortic Aneurysm Repair (Open and EVAR) - Incidence, Risk Factors and Outcomes",
        "type": "Retrospective Cohort",
        "rationale": (
            "Ischaemic colitis after aortic aneurysm repair is a well-recognised but underreported "
            "complication directly relevant to vascular practice. The inferior mesenteric artery (IMA) "
            "is routinely sacrificed at open AAA repair; collateral flow via the marginal artery of "
            "Drummond is variable (Bailey and Love's Short Practice of Surgery, 28th Ed., p.1118). "
            "Risk factors (haemodynamic compromise, IMA back-pressure, hypogastric artery sacrifice, "
            "ruptured vs elective AAA) are known from small series but institutional data are sparse. "
            "The mortality when transmural ischaemia occurs approaches 50%."
        ),
        "measures": [
            "Incidence of post-operative colonic ischaemia (sigmoidoscopy-confirmed) after open AAA and EVAR",
            "Risk factors: ruptured vs elective, IMA reimplantation rate, hypogastric preservation, "
            "intra-operative hypotension duration",
            "Grade of ischaemia (mucosal vs transmural) and management pathway (conservative vs colectomy)",
            "30-day mortality stratified by ischaemia grade",
        ],
        "gap": (
            "Multi-centre data on post-AAA colonic ischaemia are limited and largely pre-EVAR era. "
            "Even a 5-year single-centre series adds meaningful benchmark data and informs operative "
            "decision-making regarding IMA reimplantation and hypogastric artery preservation."
        ),
    },
    {
        "num": "8",
        "title": "Predictors of Bowel Non-Viability at Second-Look Laparotomy in AMI",
        "type": "Retrospective Cohort",
        "rationale": (
            "Damage-control laparotomy with planned second-look is now widely used for AMI, but criteria "
            "for declaring bowel non-viable at first or second look are not standardised. A 2021 meta-analysis "
            "by Emile et al. (Updates in Surgery, PMID 32728981) identified lactate, white cell count, and "
            "peritonism as predictors of bowel necrosis but could not synthesise a validated scoring tool. "
            "Post-revascularisation assessment of bowel viability remains largely experiential and operator-dependent."
        ),
        "measures": [
            "CT findings pre-operatively (pneumatosis, portal venous gas, mural thickening extent) vs "
            "operative findings at first and second look",
            "Pre-operative lactate, WCC, CRP vs bowel viability at re-look",
            "Length of bowel resected and correlation with CT-estimated ischaemia extent",
            "Second-look reversal rate (bowel deemed non-viable at first look but salvaged at second look)",
        ],
        "gap": (
            "No validated scoring tool exists to predict bowel viability at re-look laparotomy. "
            "Local data from patients undergoing planned second-look contributes directly to the "
            "development of such a tool, with clear clinical impact on reducing unnecessary bowel resection."
        ),
    },
    {
        "num": "9",
        "title": "Chronic Mesenteric Ischaemia (CMI) - Diagnostic Delay and Nutritional Status at Intervention",
        "type": "Retrospective Cohort / Audit",
        "rationale": (
            "CMI (atherosclerotic stenosis causing post-prandial pain and weight loss) is consistently "
            "diagnosed late - average symptom duration of 12-18 months before treatment is common in "
            "published series. Patients are frequently worked up for malignancy before CMI is considered. "
            "Bailey and Love's Short Practice of Surgery (28th Ed.) describes the classic triad of "
            "post-prandial pain, fear of eating, and weight loss - yet CTA, which is diagnostic, is "
            "often not the first investigation ordered. Early stenting has excellent outcomes; late "
            "presentation means patients arrive malnourished and at higher procedural risk."
        ),
        "measures": [
            "Symptom-to-diagnosis interval (weeks/months)",
            "Number of prior investigations (OGD, colonoscopy, MRI abdomen, oncology review) before CTA was ordered",
            "Nutritional status at time of intervention (albumin, BMI, pre-operative dietitian involvement)",
            "Whether patients were seen by general surgery or gastroenterology before vascular referral",
            "30-day outcomes of intervention correlated with symptom duration",
        ],
        "gap": (
            "The diagnostic pathway for CMI is fragmented and no institutional audit data are available "
            "in most centres. An audit can identify the diagnostic bottleneck (GP, gastroenterology, "
            "or radiology) and provide the evidence base for a CMI awareness protocol."
        ),
    },
    {
        "num": "10",
        "title": "Frailty, Goals of Care, and Conservative Management in Elderly Patients with AMI",
        "type": "Retrospective Cohort",
        "rationale": (
            "AMI predominantly affects elderly patients with multiple comorbidities. A 2023 study by "
            "Gebran et al. (The American Surgeon, PMID 34748456) showed that in patients over 80, AMI "
            "is 'not a death sentence' with selected intervention, but frailty assessment and "
            "goals-of-care discussions are poorly documented. A 2022 systematic review on AMI incidence "
            "and outcomes (Tamme et al., BMJ Open, PMID 36283747) found that elderly subgroup data were "
            "consistently absent from published datasets. As endovascular options expand, the question "
            "of who is too frail for any intervention - and what palliative pathways exist - is clinically "
            "pressing but almost entirely unaddressed in the literature."
        ),
        "measures": [
            "Clinical Frailty Scale (CFS) score at presentation",
            "Rate of formal goals-of-care or palliative care discussion documented in notes",
            "Conservative management rate and its determinants (age, frailty, comorbidity, lactate, peritonism)",
            "30-day and 90-day mortality in those managed conservatively vs those who underwent intervention",
            "Palliative vs active pathway documentation and family/patient involvement in decision-making",
        ],
        "gap": (
            "AMI in the frail elderly is largely a palliative condition in many cases, yet that reality "
            "is under-represented in the surgical literature. Institutional data here directly supports "
            "ethical decision-making frameworks and contributes to a growing evidence base around "
            "frailty-informed consent in vascular surgery."
        ),
    },
]

doc.add_heading("Research Topics", level=1)

for topic in topics:
    doc.add_heading(f"Topic {topic['num']}: {topic['title']}", level=2)

    add_label(doc, "Recommended Study Design")
    add_body(doc, topic["type"])

    add_label(doc, "Rationale")
    add_body(doc, topic["rationale"])

    add_label(doc, "Key Outcome Measures")
    for m in topic["measures"]:
        add_bullet(doc, m)

    add_label(doc, "Research Gap Addressed")
    add_body(doc, topic["gap"])

    add_hr(doc)

# ══════════════════════════════════════════════════════════════════════════════
#  FEASIBILITY TABLE
# ══════════════════════════════════════════════════════════════════════════════
doc.add_heading("Feasibility Summary", level=1)
add_body(doc,
    "The table below summarises each topic by design, data availability, and estimated novelty. "
    "Topics 1, 2, 6, and 9 are most achievable as audit-quality projects within a short rotation "
    "with limited data access. Topics 3, 7, 8, and 10 generate the most original contribution if "
    "a 3-5 year operative dataset can be accessed."
)

table = doc.add_table(rows=1, cols=5)
table.style = "Table Grid"

# Header row
hdr_cells = table.rows[0].cells
headers = ["#", "Topic", "Study Design", "Data Source", "Novelty"]
for i, h in enumerate(headers):
    hdr_cells[i].text = h
    for para in hdr_cells[i].paragraphs:
        for run in para.runs:
            run.bold = True
            run.font.size = Pt(10)
            run.font.color.rgb = RGBColor(0xFF, 0xFF, 0xFF)
    # shade header cells
    tc = hdr_cells[i]._tc
    tcPr = tc.get_or_add_tcPr()
    shd = OxmlElement("w:shd")
    shd.set(qn("w:val"),   "clear")
    shd.set(qn("w:color"), "auto")
    shd.set(qn("w:fill"), "2E74B5")
    tcPr.append(shd)

rows_data = [
    ("1", "Time-to-CTA and mortality",                   "Retro. Audit",         "EPR / PACS",           "High"),
    ("2", "Lactate as triage biomarker",                  "Diagnostic Audit",     "Path. + Imaging logs", "High"),
    ("3", "Endovascular vs Open (severity-adjusted)",     "Retro. Cohort",        "Operative database",   "Very High"),
    ("4", "NOMI incidence and triggers in ICU",           "Retro. Cohort",        "ICU / Theatre records","Very High"),
    ("5", "MVT - DOAC vs warfarin",                       "Retro. Cohort",        "Pharmacy + Imaging",   "High"),
    ("6", "ESVS 2025 guideline compliance",               "Clinical Audit",       "EPR review",           "Moderate"),
    ("7", "Colonic ischaemia after AAA repair",           "Retro. Cohort",        "Vascular registry",    "High"),
    ("8", "Bowel viability predictors at re-look",        "Retro. Cohort",        "Theatre / Op notes",   "Very High"),
    ("9", "CMI - diagnostic delay",                       "Retro. Cohort / Audit","Referral letters / EPR","High"),
    ("10","Frailty and goals of care in AMI",             "Retro. Cohort",        "EPR / MDT records",    "Very High"),
]

for row_vals in rows_data:
    row_cells = table.add_row().cells
    for i, val in enumerate(row_vals):
        row_cells[i].text = val
        for para in row_cells[i].paragraphs:
            for run in para.runs:
                run.font.size = Pt(10)

doc.add_paragraph()

# ══════════════════════════════════════════════════════════════════════════════
#  PRACTICAL TIPS
# ══════════════════════════════════════════════════════════════════════════════
doc.add_heading("Practical Tips", level=1)
tips = [
    "Topics 1, 2, 6, and 9 are most achievable as audit-quality projects within a short rotation with limited data access.",
    "Topics 3, 7, 8, and 10 generate the most original contribution if you can access a 3-5 year operative dataset.",
    "For any cohort study, pre-register on ClinicalTrials.gov or ISRCTN (both free, registration takes approximately one day) before data extraction - this substantially increases publication chances.",
    "The ESVS 2025 guidelines list 'Unresolved Issues' at the end of each chapter - reading that section directly provides the most authoritative statement of current gaps to cite in your introduction.",
    "Severity-of-illness scoring (SOFA, APACHE-II, Clinical Frailty Scale) should be collected prospectively if at all possible, as retrospective extraction is frequently incomplete.",
    "Engaging your hospital's clinical governance or audit department early ensures ethics/audit registration, which is required for publication in most journals.",
]
for tip in tips:
    add_bullet(doc, tip)

# ══════════════════════════════════════════════════════════════════════════════
#  REFERENCE LIST
# ══════════════════════════════════════════════════════════════════════════════
doc.add_page_break()
doc.add_heading("References", level=1)

refs = [
    (
        "1.",
        "Reintam Blaser A, Koitmae M, Bachmann KF, et al. Management of acute mesenteric ischaemia in adult patients: "
        "a systematic review and meta-analysis. World J Emerg Surg. 2025;20(1):38. doi:10.1186/s13017-025-00614-6. "
        "PMID: 40275298."
    ),
    (
        "2.",
        "Koelemay MJW, Geelkerken RH, Karkkainen JM, et al. Editor's Choice - European Society for Vascular Surgery (ESVS) "
        "2025 Clinical Practice Guidelines on the Management of Diseases of the Mesenteric and Renal Arteries and Veins. "
        "Eur J Vasc Endovasc Surg. 2025;70(2):153-218. doi:10.1016/j.ejvs.2025.05.167."
    ),
    (
        "3.",
        "Shi Y, Zhao B, Zhou Y, et al. Endovascular revascularization vs open surgical revascularization as the first "
        "strategy for arterial acute mesenteric ischemia: a systematic review and meta-analysis. J Vasc Surg. "
        "2024;80(6):1897-1908. doi:10.1016/j.jvs.2024.07.084. PMID: 39069018."
    ),
    (
        "4.",
        "Magnus L, Lejay A, Philouze G, et al. Mortality and delays of management of acute mesenteric ischemia: "
        "the need of a dedicated program. Ann Vasc Surg. 2023;91:192-201. doi:10.1016/j.avsg.2022.12.070. "
        "PMID: 36549474."
    ),
    (
        "5.",
        "Tamme K, Reintam Blaser A, Laisaar KT, et al. Incidence and outcomes of acute mesenteric ischaemia: "
        "a systematic review and meta-analysis. BMJ Open. 2022;12(10):e062846. doi:10.1136/bmjopen-2022-062846. "
        "PMID: 36283747."
    ),
    (
        "6.",
        "Sumbal R, Ali Baig MM, Sumbal A, et al. Predictors of mortality in acute mesenteric ischemia: "
        "a systematic review and meta-analysis. J Surg Res. 2022;275:200-210. doi:10.1016/j.jss.2022.01.022. "
        "PMID: 35220147."
    ),
    (
        "7.",
        "Emile SH, Khan SM, Barsoum SH, et al. Predictors of bowel necrosis in patients with acute mesenteric "
        "ischemia: systematic review and meta-analysis. Updates Surg. 2021;73(3):779-790. "
        "doi:10.1007/s13304-020-00857-9. PMID: 32728981."
    ),
    (
        "8.",
        "Hou L, Wang T, Wang J, et al. Outcomes of different acute mesenteric ischemia therapies in the last "
        "20 years: a meta-analysis and systematic review. Vascular. 2022;30(2):282-291. "
        "doi:10.1177/17085381211024503. PMID: 34154466."
    ),
    (
        "9.",
        "Mihaileanu FV, Popa SL, Grad S, et al. The efficiency of serum biomarkers in predicting the clinical "
        "outcome of patients with mesenteric ischemia during follow-up: a systematic review. Diagnostics (Basel). "
        "2024;14(7):670. doi:10.3390/diagnostics14070670. PMID: 38611583."
    ),
    (
        "10.",
        "Zafirovski A, Zafirovska M, Kuhelj D, et al. The impact of biomarkers on the early detection of acute "
        "mesenteric ischemia. Biomedicines. 2024;12(1):85. doi:10.3390/biomedicines12010085. PMID: 38255192."
    ),
    (
        "11.",
        "Kim YH, Kim HJ, Park S, et al. Comparing clinical outcomes of vitamin K antagonists vs non-vitamin K "
        "antagonists in anticoagulant therapy for mesenteric venous thrombosis. J Vasc Surg Venous Lymphat Disord. "
        "2024;12(5):101903. doi:10.1016/j.jvsv.2024.101903. PMID: 38754777."
    ),
    (
        "12.",
        "Wang L, Wang E, Liu F, et al. A systematic review and meta-analysis on endovascular treatment as an "
        "attractive alternative for acute superior mesenteric venous thrombosis. Vascular. 2022;30(2):274-281. "
        "doi:10.1177/1708538121991270. PMID: 33947286."
    ),
    (
        "13.",
        "Bucher F, Dastagir N, Tamulevicius M, et al. Evaluation of non-occlusive mesenteric ischemia for burn "
        "patients - a matched-pair analysis and treatment algorithm. Burns. 2025;51(1):107278. "
        "doi:10.1016/j.burns.2024.08.020. PMID: 39442475."
    ),
    (
        "14.",
        "Gebran A, Gallastegi AD, Fagenholz P, et al. Factors that predict survival from mesenteric ischemia "
        "in the geriatric patient: not a death sentence. Am Surg. 2023;89(1):79-85. "
        "doi:10.1177/00031348211050585. PMID: 34748456."
    ),
    (
        "15.",
        "De Pietro R, Martin J, Tradi F, et al. Prognostic factors after acute mesenteric ischemia: which "
        "patients require specific management? Int J Colorectal Dis. 2023;38(1):237. "
        "doi:10.1007/s00384-023-04540-4. PMID: 37777708."
    ),
    (
        "16.",
        "Dias Miranda NC, Marimpietri FS, Marcolin Miranda L, et al. Covered versus bare-metal stents in "
        "chronic mesenteric ischemia treatment: a systematic review and meta-analysis. Ann Vasc Surg. "
        "2025; in press. doi:10.1016/j.avsg.2024.12.071. PMID: 39864517."
    ),
    (
        "17.",
        "Harmantepe AT, Dikicier E, Dulger UC, et al. Effective new predictors of prognosis and comparison "
        "of multidisciplinary treatment options in acute mesenteric ischemia. North Clin Istanb. "
        "2025;12(1):38-46. doi:10.14744/nci.2023.82231. PMID: 40330518."
    ),
    (
        "18.",
        "Norman Williams, Christopher Bulstrode, P Ronan O'Connell (eds). Bailey and Love's Short Practice "
        "of Surgery, 28th Edition. Boca Raton: CRC Press/Taylor & Francis; 2023. "
        "Chapter 65: Mesenteric Ischaemia, pp. 1115-1120."
    ),
    (
        "19.",
        "Gries JJ, Sakamoto T, Chen B, Virk HUH, Alam M, Krittanawong C. Revascularization strategies for "
        "acute and chronic mesenteric ischemia: a narrative review. J Clin Med. 2024;13(5):1217. "
        "doi:10.3390/jcm13051217."
    ),
]

for num, ref_text in refs:
    p = doc.add_paragraph(style="Normal")
    p.paragraph_format.space_after  = Pt(4)
    p.paragraph_format.space_before = Pt(0)
    p.paragraph_format.left_indent  = Inches(0.4)
    p.paragraph_format.first_line_indent = Inches(-0.4)
    num_run = p.add_run(num + "  ")
    num_run.bold = True
    num_run.font.size = Pt(10)
    body_run = p.add_run(ref_text)
    body_run.font.size = Pt(10)

# ══════════════════════════════════════════════════════════════════════════════
#  SAVE
# ══════════════════════════════════════════════════════════════════════════════
out_path = "/tmp/workspace/mesenteric-ischaemia/Mesenteric_Ischaemia_Research_Topics.docx"
doc.save(out_path)
print(f"Saved: {out_path}")

"""
Mesenteric Ischaemia Clinical Research Database
================================================
Covers Research Topics 1-6:
  1. Time-to-CTA and mortality
  2. Lactate as triage biomarker
  3. Endovascular vs Open revascularisation (severity-adjusted)
  4. NOMI incidence/triggers in ICU
  5. MVT - DOAC vs Warfarin
  6. ESVS 2025 guideline compliance audit
"""

import openpyxl
from openpyxl.styles import (
    PatternFill, Font, Alignment, Border, Side, GradientFill
)
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.formatting.rule import FormulaRule

# ── Colour palette ─────────────────────────────────────────────────────────
NAVY       = "1F497D"
BLUE       = "2E74B5"
LIGHT_BLUE = "D6E4F0"
MID_BLUE   = "BDD7EE"
DARK_BLUE  = "17375E"
ORANGE     = "C55A11"
LIGHT_ORG  = "FCE4D6"
GREEN      = "375623"
LIGHT_GRN  = "E2EFDA"
RED        = "9C0006"
LIGHT_RED  = "FFC7CE"
YELLOW     = "FFEB9C"
GREY_HEAD  = "404040"
GREY_LIGHT = "F2F2F2"
WHITE      = "FFFFFF"

# ── Style helpers ──────────────────────────────────────────────────────────
def hdr(bg=NAVY, fg=WHITE, bold=True, sz=10, wrap=True, italic=False):
    return {
        "fill": PatternFill("solid", fgColor=bg),
        "font": Font(name="Arial", bold=bold, color=fg, size=sz, italic=italic),
        "alignment": Alignment(horizontal="center", vertical="center",
                                wrap_text=wrap),
    }

def subhdr(bg=LIGHT_BLUE, fg=NAVY, bold=True, sz=10):
    return {
        "fill": PatternFill("solid", fgColor=bg),
        "font": Font(name="Arial", bold=bold, color=fg, size=sz),
        "alignment": Alignment(horizontal="left", vertical="center",
                                wrap_text=True),
    }

def cell_style(fg=GREY_HEAD, sz=10, wrap=True, italic=False, bold=False):
    return {
        "font": Font(name="Arial", color=fg, size=sz, italic=italic, bold=bold),
        "alignment": Alignment(horizontal="left", vertical="center",
                                wrap_text=wrap),
    }

def apply(ws, row, col, value, style_dict, width=None):
    c = ws.cell(row=row, column=col, value=value)
    for attr, val in style_dict.items():
        setattr(c, attr, val)
    if width:
        ws.column_dimensions[get_column_letter(col)].width = width
    return c

def thin_border():
    s = Side(style="thin", color="BFBFBF")
    return Border(left=s, right=s, top=s, bottom=s)

def apply_table_borders(ws, min_row, max_row, min_col, max_col):
    for r in range(min_row, max_row + 1):
        for c in range(min_col, max_col + 1):
            ws.cell(r, c).border = thin_border()

def add_dv(ws, formula1, sqref, allow, prompt_title="", prompt="", show_error=True):
    dv = DataValidation(type=allow, formula1=formula1,
                         allow_blank=True, showErrorMessage=show_error,
                         showInputMessage=bool(prompt))
    dv.sqref = sqref
    if prompt:
        dv.promptTitle = prompt_title
        dv.prompt = prompt
    ws.add_data_validation(dv)
    return dv

# ══════════════════════════════════════════════════════════════════════════
wb = openpyxl.Workbook()
wb.remove(wb.active)  # remove default sheet

# ══════════════════════════════════════════════════════════════════════════
# SHEET 0 – INSTRUCTIONS / CODEBOOK
# ══════════════════════════════════════════════════════════════════════════
ws_info = wb.create_sheet("📋 Instructions")
ws_info.sheet_properties.tabColor = DARK_BLUE
ws_info.column_dimensions["A"].width = 30
ws_info.column_dimensions["B"].width = 70
ws_info.column_dimensions["C"].width = 45
ws_info.row_dimensions[1].height = 36

apply(ws_info, 1, 1,
      "MESENTERIC ISCHAEMIA CLINICAL RESEARCH DATABASE",
      {"fill": PatternFill("solid", fgColor=NAVY),
       "font": Font(name="Arial", bold=True, color=WHITE, size=16),
       "alignment": Alignment(horizontal="left", vertical="center")})
ws_info.merge_cells("A1:C1")

apply(ws_info, 2, 1,
      "Covers Research Topics 1–6 | Vascular Surgery Rotation | August 2026",
      {"fill": PatternFill("solid", fgColor=BLUE),
       "font": Font(name="Arial", color=WHITE, size=11, italic=True),
       "alignment": Alignment(horizontal="left", vertical="center")})
ws_info.merge_cells("A2:C2")

info_rows = [
    (4,  "SHEET OVERVIEW", "", ""),
    (5,  "Sheet", "Purpose", "Primary Research Topic(s)"),
    (6,  "📋 Instructions",        "This sheet – codebook and guidance",                               "All"),
    (7,  "🧍 Patient Demographics","Age, sex, comorbidities, risk scores (CCI, CFS, ASA)",              "1,2,3,4,5,6"),
    (8,  "🔴 Presentation",        "Referral pathway, symptom onset, vital signs, pain scores",         "1,2,3,4"),
    (9,  "🔬 Investigations",       "Bloods (lactate, WCC, CRP), imaging pathway, times",               "1,2,3,4,5"),
    (10, "🏥 Diagnosis",            "AMI subtype, ESVS classification, severity scores",                 "1,3,4,6"),
    (11, "⚕️ Interventions",        "Treatment strategy, times, revascularisation, bowel resection",    "1,3,4,6"),
    (12, "💊 MVT & Anticoagulation","MVT-specific: anticoagulant choice, duration, recanalisation",     "5"),
    (13, "🏔️ Severity Scores",      "SOFA, APACHE-II, peritonism grade, CT severity",                   "3,4"),
    (14, "🩹 Morbidity",            "Post-operative complications, Clavien-Dindo grade",                "1,3,4,5"),
    (15, "📉 Mortality",            "30-day, 90-day, in-hospital mortality and cause",                  "1,3,4,5"),
    (16, "🔍 ESVS Audit",           "Checklist of 2025 ESVS Class I recommendation adherence",         "6"),
    (17, "📅 Follow-Up",            "Outpatient follow-up, re-intervention, recurrence",                "1,3,5"),
    (18, "", "", ""),
    (19, "KEY CODING CONVENTIONS", "", ""),
    (20, "Field Type", "Code / Entry Format", "Example"),
    (21, "Binary (Yes/No)",       "1 = Yes  |  0 = No  |  blank = unknown",      "AF: 1"),
    (22, "Date",                  "DD/MM/YYYY",                                   "01/01/2024"),
    (23, "Time",                  "HH:MM (24-hour)",                              "14:35"),
    (24, "Duration",              "Minutes (integer)",                            "120"),
    (25, "Free text",             "Brief narrative – keep to <100 characters",   "SMA embolus, AF background"),
    (26, "Dropdown list",         "Select from validated list only",              "Embolic"),
    (27, "Score",                 "Integer unless stated",                        "8"),
    (28, "", "", ""),
    (29, "ESVS 2025 CITATION",    "Koelemay MJW et al. Eur J Vasc Endovasc Surg. 2025;70(2):153–218. doi:10.1016/j.ejvs.2025.05.167", ""),
    (30, "AMI SEVERITY REF",      "Reintam Blaser A et al. World J Emerg Surg. 2025;20(1):38. doi:10.1186/s13017-025-00614-6", ""),
    (31, "SOFA SCORE REF",        "Vincent JL et al. Intensive Care Med. 1996;22(7):707–710", ""),
    (32, "APACHE-II REF",         "Knaus WA et al. Crit Care Med. 1985;13(10):818–829", ""),
    (33, "CCI REF",               "Charlson ME et al. J Chron Dis. 1987;40(5):373–383", ""),
    (34, "CFS REF",               "Rockwood K et al. CMAJ. 2005;173(5):489–495", ""),
    (35, "CLAVIEN-DINDO REF",     "Dindo D et al. Ann Surg. 2004;240(2):205–213", ""),
]

for row_num, col1, col2, col3 in info_rows:
    if col1 in ("SHEET OVERVIEW", "KEY CODING CONVENTIONS"):
        apply(ws_info, row_num, 1, col1,
              {"fill": PatternFill("solid", fgColor=NAVY),
               "font": Font(name="Arial", bold=True, color=WHITE, size=11),
               "alignment": Alignment(horizontal="left", vertical="center")})
        ws_info.merge_cells(f"A{row_num}:C{row_num}")
    elif col1 in ("Sheet", "Field Type", "ESVS 2025 CITATION", "AMI SEVERITY REF",
                  "SOFA SCORE REF", "APACHE-II REF", "CCI REF", "CFS REF", "CLAVIEN-DINDO REF"):
        for ci, v in enumerate([col1, col2, col3], 1):
            apply(ws_info, row_num, ci, v,
                  {"fill": PatternFill("solid", fgColor=MID_BLUE),
                   "font": Font(name="Arial", bold=True, color=NAVY, size=10),
                   "alignment": Alignment(horizontal="left", vertical="center", wrap_text=True)})
    else:
        for ci, v in enumerate([col1, col2, col3], 1):
            apply(ws_info, row_num, ci, v,
                  {"font": Font(name="Arial", color=GREY_HEAD, size=10),
                   "alignment": Alignment(horizontal="left", vertical="center", wrap_text=True),
                   "fill": PatternFill("solid", fgColor=GREY_LIGHT if ci == 1 else WHITE)})

ws_info.row_dimensions[5].height = 18
ws_info.row_dimensions[20].height = 18


# ══════════════════════════════════════════════════════════════════════════
# Helper: build a data sheet with column headers
# Returns the sheet and the row number after the last header row
# ══════════════════════════════════════════════════════════════════════════
def build_sheet(wb, name, tab_color, title_text, sections):
    """
    sections = list of (section_name, section_color, [(col_name, width, note), ...])
    Returns (ws, header_row) where header_row is row 3 (data starts row 4)
    """
    ws = wb.create_sheet(name)
    ws.sheet_properties.tabColor = tab_color
    ws.freeze_panes = "B4"
    ws.row_dimensions[1].height = 28
    ws.row_dimensions[2].height = 14
    ws.row_dimensions[3].height = 44

    # Row 1: sheet title
    ws.cell(1, 1).value = title_text
    ws.cell(1, 1).fill  = PatternFill("solid", fgColor=NAVY)
    ws.cell(1, 1).font  = Font(name="Arial", bold=True, color=WHITE, size=13)
    ws.cell(1, 1).alignment = Alignment(horizontal="left", vertical="center")

    # Flatten all columns to get total count for merge
    all_cols = []
    for sec_name, sec_color, fields in sections:
        for fname, fwidth, fnote in fields:
            all_cols.append((sec_name, sec_color, fname, fwidth, fnote))

    total_cols = len(all_cols)
    if total_cols > 1:
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=total_cols)

    # Row 2: section group headers (merged spans)
    col_idx = 1
    for sec_name, sec_color, fields in sections:
        span = len(fields)
        for i in range(span):
            c = ws.cell(2, col_idx + i)
            c.fill = PatternFill("solid", fgColor=sec_color)
        if span > 1:
            ws.merge_cells(start_row=2, start_column=col_idx,
                           end_row=2, end_column=col_idx + span - 1)
        c = ws.cell(2, col_idx)
        c.value = sec_name
        c.fill  = PatternFill("solid", fgColor=sec_color)
        c.font  = Font(name="Arial", bold=True, color=WHITE, size=9)
        c.alignment = Alignment(horizontal="center", vertical="center")
        col_idx += span

    # Row 3: column headers
    col_idx = 1
    for sec_name, sec_color, fields in sections:
        for fname, fwidth, fnote in fields:
            c = ws.cell(3, col_idx)
            c.value = fname
            c.fill  = PatternFill("solid", fgColor=sec_color)
            c.font  = Font(name="Arial", bold=True, color=WHITE, size=9)
            c.alignment = Alignment(horizontal="center", vertical="center",
                                    wrap_text=True)
            c.border = thin_border()
            if fnote:
                from openpyxl.comments import Comment
                comment = Comment(fnote, "Database")
                comment.width  = 280
                comment.height = 100
                c.comment = comment
            ws.column_dimensions[get_column_letter(col_idx)].width = fwidth
            col_idx += 1

    return ws, 3  # header is row 3, data starts row 4


# ══════════════════════════════════════════════════════════════════════════
# SHEET 1 – PATIENT DEMOGRAPHICS
# ══════════════════════════════════════════════════════════════════════════
demo_sections = [
    ("IDENTIFIERS", DARK_BLUE, [
        ("Patient ID",          12, "Unique anonymised ID e.g. MI-001"),
        ("Hospital Number",     14, "Local hospital number – anonymise before sharing"),
        ("Date of Admission",   14, "DD/MM/YYYY"),
        ("Admission Year",       8, "YYYY – for temporal trend analysis"),
        ("Admission Source",    14, "ED / Ward / ICU / Transferred / Elective"),
        ("Study Inclusion",      9, "1=Yes 0=No – meets inclusion criteria"),
        ("Topic(s) Applicable", 14, "e.g. 1,2,3 – comma separated"),
    ]),
    ("DEMOGRAPHICS", BLUE, [
        ("Date of Birth",       12, "DD/MM/YYYY"),
        ("Age at Admission",     8, "Years (integer) – auto-calculated if DOB entered"),
        ("Sex",                  8, "M / F / Other"),
        ("Ethnicity",           14, "White / South Asian / Black / East Asian / Mixed / Other / Unknown"),
        ("Height (cm)",          9, ""),
        ("Weight (kg)",          9, ""),
        ("BMI",                  8, "kg/m² – calculate if height+weight available"),
    ]),
    ("CARDIOVASCULAR RISK", ORANGE, [
        ("Hypertension",        10, "1=Yes 0=No"),
        ("Diabetes",            10, "1=Yes 0=No"),
        ("Hyperlipidaemia",     10, "1=Yes 0=No"),
        ("Current Smoker",      10, "1=Yes 0=No"),
        ("Ex-Smoker",           10, "1=Yes 0=No"),
        ("Atrial Fibrillation", 10, "1=Yes 0=No – critical for AMI embolic risk"),
        ("AF Type",             12, "Paroxysmal / Persistent / Permanent / Unknown / N/A"),
        ("Anticoagulated (pre-admission)", 12, "1=Yes 0=No"),
        ("Anticoagulant Agent", 14, "Warfarin / DOAC / LMWH / None / Unknown"),
        ("INR at Admission",     9, "Numeric – if on warfarin"),
        ("Previous MI",         10, "1=Yes 0=No"),
        ("LV Dysfunction",      10, "1=Yes 0=No – EF <40%"),
        ("Previous Stroke/TIA", 10, "1=Yes 0=No"),
        ("Peripheral Vascular Disease", 10, "1=Yes 0=No"),
        ("Aortic Aneurysm",     10, "1=Yes 0=No"),
    ]),
    ("OTHER COMORBIDITIES", "5B9BD5", [
        ("COPD",                 8, "1=Yes 0=No"),
        ("CKD Stage",           10, "1-5 / None / Unknown"),
        ("eGFR at Admission",    8, "mL/min/1.73m²"),
        ("Liver Disease",        8, "1=Yes 0=No"),
        ("Malignancy (active)", 10, "1=Yes 0=No"),
        ("Immunosuppressed",     8, "1=Yes 0=No"),
        ("Previous Abdominal Surgery", 12, "1=Yes 0=No"),
        ("Previous Mesenteric Event",  12, "1=Yes 0=No"),
        ("Inflammatory Bowel Disease", 10, "1=Yes 0=No"),
        ("Hypercoagulable State",      10, "1=Yes 0=No – Factor V Leiden, APS, etc."),
        ("Hypercoagulable Type",       14, "Factor V Leiden / APS / Protein C/S def / JAK2 / Other / Unknown / N/A"),
        ("OCP / HRT",                   8, "1=Yes 0=No"),
        ("Recent Cardiac Surgery",     12, "1=Yes 0=No – within 30 days"),
        ("Recent Aortic Surgery",      12, "1=Yes 0=No – within 30 days"),
    ]),
    ("VALIDATED RISK SCORES", GREEN, [
        ("Charlson Comorbidity Index (CCI)",  10, "0-37; calculated from comorbidity fields. Ref: Charlson 1987"),
        ("CCI Category",                      12, "0 / 1-2 / 3-4 / 5+"),
        ("Clinical Frailty Scale (CFS)",       8, "1-9; scored at admission. Ref: Rockwood 2005"),
        ("CFS Category",                      12, "Fit (1-3) / Vulnerable (4) / Frail (5-6) / Severely Frail (7-9)"),
        ("ASA Grade",                          8, "I / II / III / IV / V"),
        ("POSSUM Physiology Score",           10, "12-88; if available pre-op"),
        ("P-POSSUM Predicted Mortality %",    10, "Numeric % if calculated"),
    ]),
]

ws_demo, _ = build_sheet(wb, "🧍 Demographics", DARK_BLUE,
    "PATIENT DEMOGRAPHICS  |  Topics 1–6", demo_sections)

# Add dropdowns to Demographics sheet
add_dv(ws_demo, '"ED,Ward,ICU,Transferred,Elective,Other"',
       "E4:E1003", "list", "Admission Source", "Select admission source")
add_dv(ws_demo, '"M,F,Other,Unknown"',
       "I4:I1003", "list", "Sex", "Select sex")
add_dv(ws_demo, '"White,South Asian,Black,East Asian,Mixed,Other,Unknown"',
       "J4:J1003", "list", "Ethnicity")
add_dv(ws_demo, '"Paroxysmal,Persistent,Permanent,Unknown,N/A"',
       "S4:S1003", "list", "AF Type")
add_dv(ws_demo, '"Warfarin,DOAC - Apixaban,DOAC - Rivaroxaban,DOAC - Edoxaban,DOAC - Dabigatran,LMWH,None,Unknown"',
       "U4:U1003", "list", "Anticoagulant")
add_dv(ws_demo, '"1,2,3,4,5,None,Unknown"',
       "AH4:AH1003", "list", "CKD Stage")
add_dv(ws_demo, '"I,II,III,IV,V"',
       "AT4:AT1003", "list", "ASA Grade")
add_dv(ws_demo, '"0,1-2,3-4,5+"',
       "AQ4:AQ1003", "list", "CCI Category")
add_dv(ws_demo, '"Fit (1-3),Vulnerable (4),Frail (5-6),Severely Frail (7-9)"',
       "AS4:AS1003", "list", "CFS Category")


# ══════════════════════════════════════════════════════════════════════════
# SHEET 2 – PRESENTATION
# ══════════════════════════════════════════════════════════════════════════
pres_sections = [
    ("IDENTIFIERS", DARK_BLUE, [
        ("Patient ID",          12, "Must match Demographics sheet"),
        ("Admission Date",      13, "DD/MM/YYYY"),
    ]),
    ("REFERRAL PATHWAY  (Topic 1)", "C55A11", [
        ("Initial Contact Point",      16, "GP / ED Self-Referral / 999 / Ward Deterioration / ICU / Outpatient / Transferred"),
        ("GP-to-ED Referral",           9, "1=Yes 0=No – GP triggered referral"),
        ("Time Symptom Onset (pt report)", 12, "HH:MM – patient-reported or best estimate"),
        ("Date Symptom Onset",         13, "DD/MM/YYYY"),
        ("Time of ED / Ward Arrival",  13, "HH:MM"),
        ("Time First Surgical Review", 13, "HH:MM – first vascular/surgical team contact"),
        ("Time AMI Suspected (clinical)", 13, "HH:MM – when AMI formally suspected"),
        ("Delay Reason (if >4h to suspicion)", 20, "Misdiagnosis / No surgical review / Out-of-hours / Other / N/A"),
        ("Initial Working Diagnosis",  18, "Free text – what was first suspected?"),
    ]),
    ("SYMPTOMS", BLUE, [
        ("Abdominal Pain",          10, "1=Yes 0=No"),
        ("Pain Location",           14, "Central / Periumbilical / Generalised / Left / Right / Not documented"),
        ("Pain Onset",              12, "Sudden / Gradual / Unknown"),
        ("Pain Duration (hours)",    9, "Numeric – hours from onset to admission"),
        ("NRS Pain Score at Triage", 9, "0-10"),
        ("Pain Out of Proportion",  10, "1=Yes 0=No – clinician documented"),
        ("Nausea/Vomiting",         10, "1=Yes 0=No"),
        ("Rectal Bleeding",         10, "1=Yes 0=No"),
        ("Bloody Diarrhoea",        10, "1=Yes 0=No"),
        ("Diarrhoea (non-bloody)",  10, "1=Yes 0=No"),
        ("Absolute Constipation",   10, "1=Yes 0=No"),
        ("Anorexia",                10, "1=Yes 0=No"),
        ("Weight Loss (pre-adm)",   10, "1=Yes 0=No – suggests chronic / CMI"),
        ("Weight Loss Amount (kg)", 10, "Numeric if documented"),
        ("Post-Prandial Pain",      10, "1=Yes 0=No – suggests CMI"),
        ("Fear of Eating",          10, "1=Yes 0=No – suggests CMI"),
        ("Duration of Symptoms (days before adm)", 10, "Integer – for CMI diagnostic delay"),
    ]),
    ("VITAL SIGNS AT PRESENTATION", "375623", [
        ("HR (bpm)",               9, ""),
        ("SBP (mmHg)",             9, ""),
        ("DBP (mmHg)",             9, ""),
        ("MAP (mmHg)",             9, "Mean arterial pressure – calculate or record if available"),
        ("Temp (°C)",              9, ""),
        ("RR (breaths/min)",       9, ""),
        ("SpO2 (%)",               9, ""),
        ("GCS",                    8, "3-15"),
        ("NEWS2 Score",            9, "0-20; if documented"),
        ("Haemodynamic Instability", 12, "1=Yes 0=No – SBP <90 or HR >120"),
        ("Vasopressors Required",  12, "1=Yes 0=No"),
        ("Vasopressor Agent",      14, "Noradrenaline / Adrenaline / Vasopressin / Dopamine / Multiple / N/A"),
        ("Vasopressor Start Time", 13, "HH:MM"),
        ("Max Norad Dose (mcg/kg/min)", 10, "Numeric"),
    ]),
    ("ABDOMINAL EXAMINATION", ORANGE, [
        ("Peritonism Grade",       14, "None / Localised Tenderness / Guarding / Rigidity"),
        ("Distension",             10, "1=Yes 0=No"),
        ("Bowel Sounds",           12, "Normal / Reduced / Absent / Tinkling"),
        ("Rectal Exam Performed",  12, "1=Yes 0=No"),
        ("Rectal Exam Findings",   16, "Normal / Blood / Melanea / Empty / Not Performed"),
        ("Peritonitis at Presentation", 12, "1=Yes 0=No – explicit peritonitis documented"),
    ]),
]

ws_pres, _ = build_sheet(wb, "🔴 Presentation", ORANGE,
    "CLINICAL PRESENTATION  |  Topics 1, 2, 3, 4", pres_sections)

add_dv(ws_pres, '"GP,ED Self-Referral,999 Ambulance,Ward Deterioration,ICU,Outpatient,Transferred In,Other"',
       "C4:C1003", "list", "Contact Point")
add_dv(ws_pres, '"Central,Periumbilical,Generalised,Left Iliac Fossa,Right Iliac Fossa,Epigastric,Not Documented"',
       "L4:L1003", "list", "Pain Location")
add_dv(ws_pres, '"Sudden (<1h),Gradual (>1h),Unknown"',
       "M4:M1003", "list", "Onset")
add_dv(ws_pres, '"None,Localised Tenderness,Guarding,Rigidity"',
       "AE4:AE1003", "list", "Peritonism")
add_dv(ws_pres, '"Normal,Reduced,Absent,Tinkling"',
       "AG4:AG1003", "list", "Bowel Sounds")
add_dv(ws_pres, '"Noradrenaline,Adrenaline,Vasopressin,Dopamine,Multiple,N/A"',
       "AA4:AA1003", "list", "Vasopressor")


# ══════════════════════════════════════════════════════════════════════════
# SHEET 3 – INVESTIGATIONS
# ══════════════════════════════════════════════════════════════════════════
inv_sections = [
    ("IDENTIFIERS", DARK_BLUE, [
        ("Patient ID",          12, ""),
        ("Admission Date",      13, ""),
    ]),
    ("HAEMATOLOGY", "5B9BD5", [
        ("WCC (x10⁹/L)",       10, "White cell count"),
        ("Neutrophils (x10⁹/L)", 10, ""),
        ("Haemoglobin (g/dL)",  10, ""),
        ("Platelets (x10⁹/L)", 10, ""),
        ("INR",                  8, ""),
        ("APTT ratio",           8, ""),
        ("D-Dimer (mg/L FEU)",  10, ""),
    ]),
    ("BIOCHEMISTRY", BLUE, [
        ("Sodium (mmol/L)",     10, ""),
        ("Potassium (mmol/L)",  10, ""),
        ("Urea (mmol/L)",       10, ""),
        ("Creatinine (µmol/L)", 10, ""),
        ("eGFR",                 8, "mL/min/1.73m²"),
        ("CRP (mg/L)",          10, ""),
        ("Albumin (g/L)",       10, "Low albumin indicates malnutrition – important for CMI"),
        ("Bilirubin (µmol/L)",  10, ""),
        ("ALT (U/L)",            9, ""),
        ("Alkaline Phosphatase (U/L)", 9, ""),
        ("Amylase (U/L)",        9, ""),
        ("Troponin (ng/L)",     10, ""),
        ("Procalcitonin (ng/mL)", 10, "If available"),
    ]),
    ("LACTATE  (Topic 2 – Key Fields)", "C55A11", [
        ("Lactate 1 – Value (mmol/L)",   12, "FIRST lactate taken at presentation"),
        ("Lactate 1 – Time",             13, "HH:MM"),
        ("Lactate 1 – Source",           14, "Arterial / Venous / Unknown"),
        ("Lactate 1 – AMI Suspected at this time?", 10, "1=Yes 0=No – was AMI on differential when lactate sent?"),
        ("Lactate ≥2 mmol/L",            10, "1=Yes 0=No – auto-flag for Topic 2 analysis"),
        ("Lactate ≥4 mmol/L",            10, "1=Yes 0=No"),
        ("Lactate 2 – Value (mmol/L)",   12, "Second lactate if taken"),
        ("Lactate 2 – Time",             13, "HH:MM"),
        ("Lactate 2 – Trend",            12, "Improving / Worsening / Static / Single sample"),
        ("Peak Lactate (mmol/L)",        10, "Highest lactate recorded during admission"),
        ("Time to Lactate from Arrival (min)", 12, "Minutes from arrival to first lactate result"),
        ("Lactate Result → CTA Request (min)", 12, "Minutes between first lactate result and CTA request – Topic 2 key metric"),
        ("Normal Lactate at Presentation (<2)", 10, "1=Yes 0=No – for missed/delayed diagnosis analysis"),
        ("AMI Confirmed Despite Normal Lactate", 10, "1=Yes 0=No – for false negative analysis"),
    ]),
    ("ARTERIAL BLOOD GAS", "375623", [
        ("ABG Performed",       10, "1=Yes 0=No"),
        ("pH",                  8, ""),
        ("pO2 (kPa)",           8, ""),
        ("pCO2 (kPa)",          8, ""),
        ("Bicarbonate (mmol/L)",10, ""),
        ("Base Excess (mmol/L)",10, ""),
        ("Metabolic Acidosis",  10, "1=Yes 0=No – pH <7.35 + low bicarb"),
    ]),
    ("IMAGING PATHWAY  (Topic 1 – Key Fields)", ORANGE, [
        ("Plain AXR Performed",                10, "1=Yes 0=No"),
        ("AXR Findings",                       18, "Normal / Thumb-printing / Ileus / Free gas / Other"),
        ("Time of AXR",                        13, "HH:MM"),
        ("CT Abdomen (non-contrast) Performed", 10, "1=Yes 0=No"),
        ("CT Abdomen Time",                    13, "HH:MM"),
        ("CTA (CT Angiography) Performed",     12, "1=Yes 0=No – primary outcome imaging"),
        ("Time CTA Requested",                 13, "HH:MM – Topic 1 key metric"),
        ("Time CTA Performed",                 13, "HH:MM – scanner time"),
        ("Time CTA Reported",                  13, "HH:MM – radiologist report time"),
        ("Arrival → CTA Performed (min)",      12, "Minutes – Topic 1 PRIMARY OUTCOME metric"),
        ("AMI Suspected → CTA Performed (min)",12, "Minutes – from clinical suspicion to scan"),
        ("CTA Reported → Theatre/Angio (min)", 12, "Minutes – imaging to intervention time"),
        ("Out-of-Hours CTA",                   10, "1=Yes 0=No – 18:00–08:00 or weekend"),
        ("CTA Performed by",                   14, "Radiology / Vascular Surgeon-led / Unknown"),
        ("MRA Performed (CMI)",                10, "1=Yes 0=No"),
        ("Duplex Ultrasound Performed",        10, "1=Yes 0=No"),
        ("Formal Angiography Performed",       10, "1=Yes 0=No – invasive angiogram"),
    ]),
    ("CTA FINDINGS", "1F497D", [
        ("CTA Diagnosis",                   18, "AMI-Embolic / AMI-Thrombotic / NOMI / MVT / CMI / Colonic Ischaemia / Normal / Other"),
        ("Vessel Occluded",                 16, "SMA / Coeliac / IMA / SMA Branch / SMV / Portal Vein / Multiple / None"),
        ("SMA Occlusion Site",              14, "Origin / Proximal / Middle Colic / Distal / N/A"),
        ("Filling Defect Confirmed",        10, "1=Yes 0=No"),
        ("Bowel Wall Enhancement (reduced/absent)", 10, "1=Yes 0=No"),
        ("Pneumatosis Intestinalis",        10, "1=Yes 0=No – gas in bowel wall; bad prognostic sign"),
        ("Portal Venous Gas",               10, "1=Yes 0=No – ominous late sign"),
        ("Free Fluid",                      10, "1=Yes 0=No"),
        ("Bowel Ischaemia Extent",          14, "Focal (<25cm) / Segmental (25-100cm) / Extensive (>100cm) / Pan-bowel"),
        ("CT Severity Grade",               12, "Mild / Moderate / Severe / see coding"),
        ("Radiologist Diagnosis Accurate",  10, "1=Yes 0=No – compare with final surgical/pathological diagnosis"),
        ("Doppler Findings (if performed)",18, "Free text"),
    ]),
    ("ENDOSCOPY  (Ischaemic Colitis / MVT)", "595959", [
        ("Endoscopy Performed",         10, "1=Yes 0=No"),
        ("Endoscopy Type",              14, "Sigmoidoscopy / Colonoscopy / Gastroscopy / None"),
        ("Endoscopy Time from Admission (hrs)", 10, "Numeric"),
        ("Endoscopy Grade",             14, "1=Erythema / 2=Haemorrhagic / 3=Ulceration / 4=Necrosis / Normal"),
        ("Ischaemia Location",          16, "Splenic Flexure / Descending / Sigmoid / Transverse / Right / Pan-colonic"),
    ]),
]

ws_inv, _ = build_sheet(wb, "🔬 Investigations", "5B9BD5",
    "INVESTIGATIONS  |  Topics 1, 2, 3, 4, 5", inv_sections)

add_dv(ws_inv, '"AMI-Embolic,AMI-Thrombotic,NOMI,MVT,CMI-Chronic,Colonic Ischaemia,Normal,Incidental Finding,Other,Not Performed"',
       "AX4:AX1003", "list", "CTA Diagnosis")
add_dv(ws_inv, '"SMA,Coeliac,IMA,SMA Branch,SMV,Portal Vein,Multiple,None,N/A"',
       "AY4:AY1003", "list", "Vessel")
add_dv(ws_inv, '"Focal (<25cm),Segmental (25-100cm),Extensive (>100cm),Pan-bowel,N/A"',
       "BF4:BF1003", "list", "Bowel Extent")
add_dv(ws_inv, '"Mild,Moderate,Severe"',
       "BG4:BG1003", "list", "CT Severity")
add_dv(ws_inv, '"Sigmoidoscopy,Colonoscopy,Gastroscopy,None"',
       "BN4:BN1003", "list", "Endoscopy Type")


# ══════════════════════════════════════════════════════════════════════════
# SHEET 4 – DIAGNOSIS & CLASSIFICATION
# ══════════════════════════════════════════════════════════════════════════
diag_sections = [
    ("IDENTIFIERS", DARK_BLUE, [
        ("Patient ID",              12, ""),
        ("Admission Date",          13, ""),
    ]),
    ("FINAL DIAGNOSIS", BLUE, [
        ("Final Confirmed Diagnosis",     18, "AMI-Arterial-Embolic / AMI-Arterial-Thrombotic / NOMI / MVT / CMI / Colonic Ischaemia / Other"),
        ("Diagnosis Confirmed By",        18, "CT / CTA / Angiography / Laparotomy / Endoscopy / Histology / Autopsy"),
        ("Was AMI Diagnosis Delayed?",    10, "1=Yes 0=No – AMI not suspected at first contact"),
        ("Initial Misdiagnosis",          16, "Free text – e.g. gastroenteritis, pancreatitis, obstruction"),
        ("Number of Misdiagnoses Before AMI Suspected", 8, "Integer"),
        ("Time Onset → Diagnosis (hours)", 10, "Total time from symptom onset to confirmed diagnosis"),
    ]),
    ("AMI SUBTYPE CLASSIFICATION  (ESVS 2025)", ORANGE, [
        ("AMI Subtype",                  18, "Embolic / Thrombotic / NOMI / Venous (MVT) / Other / N/A"),
        ("Embolic Source",               16, "Left Atrium (AF) / LV Thrombus / Valvular / Aortic / Unknown / N/A"),
        ("Arterial vs Venous",           12, "Arterial / Venous / Non-Occlusive / N/A"),
        ("Occlusion Completeness",       14, "Complete / Partial / No Occlusion (NOMI) / N/A"),
        ("ESVS 2025 Subtype Documented in Notes", 10, "1=Yes 0=No – was formal ESVS subtype classification documented?"),
    ]),
    ("CMI-SPECIFIC  (Topic 9 – Diagnostic Delay)", "375623", [
        ("Symptom Duration Before Referral (months)", 10, ""),
        ("Prior Investigations Before CTA", 10, "Integer – number of other tests done first"),
        ("Prior OGD",                   8, "1=Yes 0=No"),
        ("Prior Colonoscopy",           8, "1=Yes 0=No"),
        ("Prior CT (non-CTA)",          8, "1=Yes 0=No"),
        ("Prior MRI Abdomen",           8, "1=Yes 0=No"),
        ("Prior PET Scan",              8, "1=Yes 0=No"),
        ("Prior Oncology Referral",     10, "1=Yes 0=No – worked up for malignancy before CMI considered"),
        ("Prior GI Referral",           10, "1=Yes 0=No"),
        ("Route to Vascular",           16, "Direct GP / GP via GI / GP via GI via Oncology / ED / Other"),
        ("Number of Stenosed Vessels",  10, "1 / 2 / 3 – on CTA"),
        ("Vessels Stenosed",            16, "Coeliac / SMA / IMA / Combinations"),
        ("Degree of Stenosis",          12, ">50% / >70% / Occlusion / Mixed"),
    ]),
    ("NOMI-SPECIFIC  (Topic 4)", "C55A11", [
        ("NOMI Confirmed",              10, "1=Yes 0=No"),
        ("NOMI Setting",                14, "Post-Cardiac Surgery / Post-Aortic Surgery / Septic Shock / Burns / Other Critical Illness"),
        ("Vasopressor Duration Before NOMI (hours)", 10, ""),
        ("NOMI Diagnosis Method",       16, "Clinical + CT / Angiography / Laparotomy / Post-mortem"),
        ("Papaverine Used",             10, "1=Yes 0=No"),
        ("Time Vasopressor Start → NOMI Diagnosis (hours)", 12, "Topic 4 key metric"),
    ]),
    ("MVT-SPECIFIC  (Topic 5)", "1F497D", [
        ("MVT Confirmed",               10, "1=Yes 0=No"),
        ("MVT Classification",          14, "Primary (idiopathic) / Secondary (provoked)"),
        ("MVT Provoked Cause",          18, "Thrombophilia / Portal Hypertension / Infection / OCP / Post-Op / IBD / Malignancy / Unknown"),
        ("Thrombophilia Screen Performed", 10, "1=Yes 0=No"),
        ("Thrombophilia Result",        14, "Factor V Leiden / Protein C def / Protein S def / APS / JAK2 / Normal / Pending"),
        ("Extent of Thrombus",          16, "SMV Only / Portal Vein Only / SMV+Portal / With Mesenteric Veins / Extensive"),
        ("Bowel Infarction at Presentation", 10, "1=Yes 0=No"),
    ]),
]

ws_diag, _ = build_sheet(wb, "🏥 Diagnosis", BLUE,
    "DIAGNOSIS & CLASSIFICATION  |  Topics 1, 3, 4, 5, 6", diag_sections)

add_dv(ws_diag, '"AMI-Arterial-Embolic,AMI-Arterial-Thrombotic,NOMI,MVT,CMI-Chronic,Colonic Ischaemia,Other,Unconfirmed"',
       "C4:C1003", "list", "Final Diagnosis")
add_dv(ws_diag, '"CT,CTA,Angiography,Laparotomy,Endoscopy,Histology,Autopsy,Clinical Only"',
       "D4:D1003", "list", "Confirmed By")
add_dv(ws_diag, '"Embolic,Thrombotic,NOMI,Venous-MVT,Mixed,Other,N/A"',
       "I4:I1003", "list", "AMI Subtype")
add_dv(ws_diag, '"Left Atrium (AF),LV Thrombus,Valvular,Aortic Atheroma,Unknown,N/A"',
       "J4:J1003", "list", "Embolic Source")
add_dv(ws_diag, '"Post-Cardiac Surgery,Post-Aortic Surgery,Septic Shock,Burns,Haemorrhagic Shock,Other Critical Illness"',
       "AF4:AF1003", "list", "NOMI Setting")
add_dv(ws_diag, '"Primary (idiopathic),Secondary (provoked)"',
       "AO4:AO1003", "list", "MVT Class")


# ══════════════════════════════════════════════════════════════════════════
# SHEET 5 – INTERVENTIONS
# ══════════════════════════════════════════════════════════════════════════
int_sections = [
    ("IDENTIFIERS", DARK_BLUE, [
        ("Patient ID",              12, ""),
        ("Admission Date",          13, ""),
    ]),
    ("TREATMENT STRATEGY  (Topic 3 – Primary Variable)", "C55A11", [
        ("Primary Treatment Strategy",  18, "Endovascular / Open Surgery / Hybrid / Conservative / Anticoagulation Only / Comfort/Palliation"),
        ("Reason for Strategy Choice",  16, "Clinical documentation if available – free text"),
        ("MDT / Vascular Team Discussion Before Treatment", 10, "1=Yes 0=No – ESVS 2025 recommendation"),
        ("Time of Treatment Decision",  13, "HH:MM"),
        ("CTA Report → Treatment Decision (min)", 12, "Topic 1 + 3 key metric"),
        ("Admission → Treatment Decision (min)",  12, "Topic 1 key metric"),
    ]),
    ("ENDOVASCULAR INTERVENTION", BLUE, [
        ("Endovascular Performed",       10, "1=Yes 0=No"),
        ("Endovascular Technique",       18, "Catheter-Directed Thrombolysis / Mechanical Thrombectomy / Angioplasty / Stenting / Aspiration / Combination"),
        ("Stent Inserted",               10, "1=Yes 0=No"),
        ("Stent Type",                   14, "Covered / Bare-Metal / N/A"),
        ("Number of Stents",              8, "Integer"),
        ("Vessel Stented",               14, "SMA / Coeliac / IMA / Other"),
        ("Technical Success",            10, "1=Yes 0=No – residual stenosis <30%"),
        ("Time CTA → Angio Suite (min)", 12, "Imaging to endovascular suite time"),
        ("Time Arrival → Endovascular Start (min)", 12, "Topic 1 + 3 key metric"),
        ("Endovascular → Open Conversion", 10, "1=Yes 0=No"),
        ("Reason for Conversion",        16, "Technical Failure / Clinical Deterioration / Bowel Necrosis / N/A"),
    ]),
    ("OPEN SURGICAL INTERVENTION", ORANGE, [
        ("Open Surgery Performed",       10, "1=Yes 0=No"),
        ("Indication for Open Surgery",  18, "Peritonitis / Failed Endovascular / Primary Strategy / Bowel Resection Only / Damage Control"),
        ("Time Arrival → Theatre (min)", 12, "Topic 1 + 3 key metric – door-to-knife time"),
        ("Time CTA → Knife (min)",       12, ""),
        ("Out-of-Hours Surgery",         10, "1=Yes 0=No"),
        ("Procedure Type",               18, "Embolectomy / Bypass / Endarterectomy / Bowel Resection Only / Damage Control / Exploratory / Second-Look"),
        ("Vascular Reconstruction",      10, "1=Yes 0=No"),
        ("Reconstruction Type",          16, "Embolectomy / Bypass (SVG) / Bypass (PTFE) / Endarterectomy / N/A"),
        ("Bowel Resection Performed",    10, "1=Yes 0=No"),
        ("Length Bowel Resected (cm)",   10, "Numeric"),
        ("Bowel Resection Extent",       14, "Small Bowel / Colon / Both / N/A"),
        ("Stoma Formed",                 10, "1=Yes 0=No"),
        ("Stoma Type",                   12, "Ileostomy / Colostomy / N/A"),
        ("Anastomosis Performed",        10, "1=Yes 0=No"),
        ("Abdomen Left Open (Laparostomy)", 10, "1=Yes 0=No – damage control"),
        ("Second-Look Laparotomy Planned", 10, "1=Yes 0=No"),
        ("Second-Look Performed",        10, "1=Yes 0=No"),
        ("Time to Second-Look (hours)",   9, ""),
        ("Bowel Viability at Second-Look", 18, "Viable / Partially Viable / Non-Viable / Mixed / N/A"),
        ("Additional Bowel Resection at Second-Look", 10, "1=Yes 0=No"),
        ("Final Abdominal Closure",      10, "1=Yes 0=No"),
    ]),
    ("ICU / POST-OP CARE", "375623", [
        ("ICU Admission Post-Op",       10, "1=Yes 0=No"),
        ("ICU Length of Stay (days)",   10, ""),
        ("Mechanical Ventilation",      10, "1=Yes 0=No"),
        ("Duration Ventilation (days)", 10, ""),
        ("Renal Replacement Therapy",   10, "1=Yes 0=No"),
        ("TPN Required",                10, "1=Yes 0=No – total parenteral nutrition"),
        ("Vasopressors Required Post-Op", 10, "1=Yes 0=No"),
        ("Post-Op Vasopressor Duration (hrs)", 10, ""),
    ]),
    ("ANTICOAGULATION  (All types)", "1F497D", [
        ("Anticoagulation Started",     10, "1=Yes 0=No"),
        ("Time Admission → Anticoagulation Started (hrs)", 12, "Topic 5 + 6 key metric – ESVS rec: within 24h for MVT"),
        ("Anticoagulant Agent",         16, "LMWH / UFH / Warfarin / Apixaban / Rivaroxaban / Edoxaban / Dabigatran / Fondaparinux / None"),
        ("Anticoagulant Indication",    16, "MVT / AMI / AF / DVT/PE / Other"),
        ("Transition to Long-Term AC",  10, "1=Yes 0=No"),
        ("Long-Term AC Agent",          16, "Warfarin / Apixaban / Rivaroxaban / Edoxaban / Dabigatran / None"),
        ("Duration of AC (months)",     10, ""),
        ("Target INR (if Warfarin)",     9, "e.g. 2-3"),
        ("AC Complications",            10, "1=Yes 0=No – bleeding or thrombotic events on AC"),
        ("AC Complication Details",     16, "Free text"),
    ]),
]

ws_int, _ = build_sheet(wb, "⚕️ Interventions", "C55A11",
    "INTERVENTIONS  |  Topics 1, 3, 4, 5, 6", int_sections)

add_dv(ws_int, '"Endovascular,Open Surgery,Hybrid,Conservative,Anticoagulation Only,Comfort/Palliation,Watchful Waiting"',
       "C4:C1003", "list", "Treatment Strategy")
add_dv(ws_int, '"Catheter-Directed Thrombolysis,Mechanical Thrombectomy,Angioplasty Only,Stenting,Aspiration,Combination,N/A"',
       "J4:J1003", "list", "EV Technique")
add_dv(ws_int, '"Covered,Bare-Metal,N/A"',
       "L4:L1003", "list", "Stent Type")
add_dv(ws_int, '"Peritonitis,Failed Endovascular,Primary Strategy,Bowel Resection Only,Damage Control,Unavailability of IR"',
       "V4:V1003", "list", "Open Indication")
add_dv(ws_int, '"Embolectomy,Bypass (SVG),Bypass (PTFE),Endarterectomy,N/A"',
       "AC4:AC1003", "list", "Recon Type")
add_dv(ws_int, '"Viable,Partially Viable,Non-Viable,Mixed,N/A"',
       "AQ4:AQ1003", "list", "Bowel Viability")
add_dv(ws_int, '"LMWH,UFH,Warfarin,Apixaban,Rivaroxaban,Edoxaban,Dabigatran,Fondaparinux,None"',
       "AX4:AX1003", "list", "AC Agent")
add_dv(ws_int, '"Warfarin,Apixaban,Rivaroxaban,Edoxaban,Dabigatran,None"',
       "BB4:BB1003", "list", "Long-term AC")


# ══════════════════════════════════════════════════════════════════════════
# SHEET 6 – SEVERITY SCORES  (Topic 3 + 4)
# ══════════════════════════════════════════════════════════════════════════
sev_sections = [
    ("IDENTIFIERS", DARK_BLUE, [
        ("Patient ID",              12, ""),
        ("Admission Date",          13, ""),
    ]),
    ("SOFA SCORE  (Ref: Vincent 1996)", BLUE, [
        ("SOFA – Respiratory (PaO2/FiO2)", 12, "0=≥400 / 1=300-399 / 2=200-299 / 3=100-199 + vent / 4=<100 + vent"),
        ("SOFA – Coagulation (Platelets)", 12, "0=≥150 / 1=100-149 / 2=50-99 / 3=20-49 / 4=<20"),
        ("SOFA – Liver (Bilirubin)",       12, "0=<20 / 1=20-32 / 2=33-101 / 3=102-204 / 4=>204 µmol/L"),
        ("SOFA – CVS (MAP/Vasopressors)",  12, "0=MAP≥70 / 1=<70 / 2=Dopa≤5 / 3=Dopa>5 or NA≤0.1 / 4=Dopa>15 or NA>0.1"),
        ("SOFA – CNS (GCS)",              12, "0=15 / 1=13-14 / 2=10-12 / 3=6-9 / 4=<6"),
        ("SOFA – Renal (Creatinine/UO)",  12, "0=<110 / 1=110-170 / 2=171-299 / 3=300-440 / 4=>440 µmol/L"),
        ("SOFA Total",                    10, "Sum of above (0-24) – CALCULATE"),
        ("SOFA Category",                 14, "0-1 (minimal) / 2-3 (mild) / 4-5 (moderate) / 6-7 (severe) / 8+ (critical)"),
        ("SOFA Timing",                   14, "Admission / 24h / 48h / ICU Admission"),
    ]),
    ("APACHE-II SCORE  (Ref: Knaus 1985)", ORANGE, [
        ("APACHE-II – Temp",              10, "Points 0-4"),
        ("APACHE-II – MAP",               10, "Points 0-4"),
        ("APACHE-II – HR",                10, "Points 0-4"),
        ("APACHE-II – RR",                10, "Points 0-4"),
        ("APACHE-II – Oxygenation",       10, "Points 0-4"),
        ("APACHE-II – Arterial pH",       10, "Points 0-4"),
        ("APACHE-II – Sodium",            10, "Points 0-4"),
        ("APACHE-II – Potassium",         10, "Points 0-4"),
        ("APACHE-II – Creatinine",        10, "Points 0-8"),
        ("APACHE-II – Haematocrit",       10, "Points 0-4"),
        ("APACHE-II – WCC",               10, "Points 0-4"),
        ("APACHE-II – GCS",               10, "15 minus GCS"),
        ("APACHE-II – Age Points",        10, "0 (<44) / 2 (45-54) / 3 (55-64) / 5 (65-74) / 6 (≥75)"),
        ("APACHE-II – Chronic Health",    10, "0/2/5 based on chronic organ failure / immunocompromised"),
        ("APACHE-II Total",               10, "Sum (0-71) – CALCULATE"),
        ("APACHE-II Predicted Mortality %", 10, "Logistic regression estimate if available"),
    ]),
    ("CLINICAL SEVERITY INDICATORS", "375623", [
        ("Peritonism Grade",              14, "None / Localised / Guarding / Rigidity / Generalised Peritonitis"),
        ("Haemodynamic Shock at Presentation", 10, "1=Yes 0=No"),
        ("Sepsis (Sepsis-3 criteria)",    10, "1=Yes 0=No"),
        ("Septic Shock",                  10, "1=Yes 0=No – Sepsis + vasopressors + lactate >2"),
        ("Multi-Organ Failure",           10, "1=Yes 0=No – ≥2 organs"),
        ("Number of Organ Failures",      10, "Integer 0-6"),
        ("Transmural Ischaemia at Surgery", 10, "1=Yes 0=No – confirmed intraoperatively"),
        ("Bowel Perforation",             10, "1=Yes 0=No"),
        ("CT Severity Score (Grainger)",  14, "Mild / Moderate / Severe – based on imaging features"),
    ]),
    ("AMI SEVERITY COMPOSITE  (Reintam Blaser 2025)", "C55A11", [
        ("Severity Category",             16, "Mild (no shock, no necrosis) / Moderate (shock OR necrosis) / Severe (shock AND necrosis)"),
        ("Severity Basis",                16, "Clinical / CT / Operative / Combined"),
        ("Rationale for Severity Grade",  18, "Free text – brief justification"),
        ("Severity Documented in Notes",  10, "1=Yes 0=No – was severity graded in clinical documentation?"),
    ]),
]

ws_sev, _ = build_sheet(wb, "🏔️ Severity Scores", "375623",
    "SEVERITY SCORES  |  Topics 3, 4", sev_sections)

add_dv(ws_sev, '"None,Localised Tenderness,Guarding,Rigidity,Generalised Peritonitis"',
       "W4:W1003", "list", "Peritonism")
add_dv(ws_sev, '"Mild (no shock no necrosis),Moderate (shock OR necrosis),Severe (shock AND necrosis)"',
       "AJ4:AJ1003", "list", "Severity Category")

# Add SOFA auto-sum formula hint
for row in range(4, 10):
    ws_sev.cell(row, 10).value = f"=SUM(C{row}:H{row})" if row == 4 else None


# ══════════════════════════════════════════════════════════════════════════
# SHEET 7 – MVT & ANTICOAGULATION  (Topic 5)
# ══════════════════════════════════════════════════════════════════════════
mvt_sections = [
    ("IDENTIFIERS", DARK_BLUE, [
        ("Patient ID",              12, ""),
        ("Admission Date",          13, ""),
    ]),
    ("MVT CHARACTERISTICS  (Topic 5)", "1F497D", [
        ("MVT Confirmed",               10, "1=Yes 0=No"),
        ("Date of Diagnosis",           13, "DD/MM/YYYY"),
        ("Acute vs Subacute vs Chronic", 14, "Acute (<4wk) / Subacute (4-12wk) / Chronic (>12wk)"),
        ("Provoked vs Unprovoked",      12, "Provoked / Unprovoked / Unknown"),
        ("Provoked Cause",              18, "Thrombophilia / Portal HTN / Infection / IBD / OCP / Post-Op / Malignancy / Other / Unknown"),
        ("Thrombophilia Tested",        10, "1=Yes 0=No"),
        ("Thrombophilia Confirmed",     10, "1=Yes 0=No"),
        ("Thrombophilia Type",          14, "Factor V Leiden / Protein C / Protein S / APS / JAK2 / Other"),
        ("Myeloproliferative Disorder", 10, "1=Yes 0=No"),
    ]),
    ("IMAGING – MVT EXTENT", BLUE, [
        ("SMV Involved",                10, "1=Yes 0=No"),
        ("Portal Vein Involved",        10, "1=Yes 0=No"),
        ("Mesenteric Veins Involved",   10, "1=Yes 0=No"),
        ("IVC Involved",                10, "1=Yes 0=No"),
        ("Extent Grade",                14, "Isolated SMV / SMV+Portal / Extensive / Portal Only"),
        ("Occlusive vs Non-Occlusive",  14, "Complete Occlusion / Partial"),
        ("Bowel Ischaemia on CT",       10, "1=Yes 0=No"),
        ("Ascites on CT",               10, "1=Yes 0=No"),
    ]),
    ("ANTICOAGULATION CHOICE  (Topic 5 – Primary Variable)", "C55A11", [
        ("Anticoagulated",                  10, "1=Yes 0=No"),
        ("Time to First Anticoagulation (hours from admission)", 12, "Topic 5 + 6 key metric"),
        ("Anticoagulated within 24h",       10, "1=Yes 0=No – ESVS 2025 Class I rec"),
        ("Initial AC Agent",                16, "LMWH / UFH / Apixaban / Rivaroxaban / None"),
        ("Initial AC Weight-Adjusted",      10, "1=Yes 0=No"),
        ("Reason if NOT Anticoagulated",    16, "Active Bleeding / Recent Surgery / Low Platelet / Haemorrhagic Infarction / Other / N/A"),
        ("Long-Term AC Agent",              16, "Warfarin / Apixaban / Rivaroxaban / Edoxaban / Dabigatran / None"),
        ("VKA vs DOAC",                     10, "VKA / DOAC / Neither"),
        ("Target INR (if VKA)",             10, "2-3 / 2.5-3.5 / Other"),
        ("Duration of AC Intended (months)", 10, "3 / 6 / 12 / Indefinite / Unknown"),
        ("Duration of AC Actual (months)",  10, ""),
        ("AC Stopped Early",                10, "1=Yes 0=No"),
        ("Reason Stopped Early",            16, "Bleeding / Patient Choice / Clinician Decision / Other / N/A"),
    ]),
    ("FOLLOW-UP IMAGING – RECANALISATION  (Topic 5 – Outcome)", "375623", [
        ("Follow-Up Imaging Performed",     10, "1=Yes 0=No"),
        ("Imaging Modality",                14, "CTA / MRA / Duplex Ultrasound / None"),
        ("Time to Follow-Up Imaging (months)", 10, ""),
        ("Recanalisation Status",           14, "Complete / Partial / None / Not Imaged"),
        ("Recanalisation at 3 months",      10, "1=Yes 0=No / Not Imaged"),
        ("Recanalisation at 6 months",      10, "1=Yes 0=No / Not Imaged"),
        ("Recanalisation at 12 months",     10, "1=Yes 0=No / Not Imaged"),
        ("Residual Thrombus",               10, "1=Yes 0=No"),
        ("Portal Hypertension Developed",   10, "1=Yes 0=No – long-term complication"),
        ("Varices Developed",               10, "1=Yes 0=No"),
    ]),
    ("MVT OUTCOMES", ORANGE, [
        ("Bowel Resection Required",        10, "1=Yes 0=No"),
        ("Length Resected (cm)",            10, ""),
        ("MVT Recurrence",                  10, "1=Yes 0=No"),
        ("Time to Recurrence (months)",     10, ""),
        ("Recurrence on AC",                10, "1=Yes 0=No"),
        ("Bleeding Complication on AC",     10, "1=Yes 0=No"),
        ("Bleeding Grade (ISTH)",           12, "Major / Clinically Relevant Non-Major / Minor / None"),
        ("Readmission within 90 days",      10, "1=Yes 0=No"),
        ("Readmission Reason",              18, "Recurrence / AC Complication / Other / N/A"),
    ]),
]

ws_mvt, _ = build_sheet(wb, "💊 MVT & Anticoag", "1F497D",
    "MESENTERIC VENOUS THROMBOSIS & ANTICOAGULATION  |  Topic 5", mvt_sections)

add_dv(ws_mvt, '"Acute (<4wk),Subacute (4-12wk),Chronic (>12wk)"',
       "E4:E1003", "list", "Chronicity")
add_dv(ws_mvt, '"Provoked,Unprovoked,Unknown"',
       "F4:F1003", "list", "Provoked")
add_dv(ws_mvt, '"Isolated SMV,SMV+Portal,Extensive,Portal Only"',
       "Q4:Q1003", "list", "Extent")
add_dv(ws_mvt, '"Complete Occlusion,Partial"',
       "R4:R1003", "list", "Occlusive")
add_dv(ws_mvt, '"LMWH,UFH,Apixaban,Rivaroxaban,None"',
       "X4:X1003", "list", "Initial AC")
add_dv(ws_mvt, '"VKA,DOAC,Neither"',
       "AC4:AC1003", "list", "VKA vs DOAC")
add_dv(ws_mvt, '"Warfarin,Apixaban,Rivaroxaban,Edoxaban,Dabigatran,None"',
       "AB4:AB1003", "list", "Long-term AC")
add_dv(ws_mvt, '"Complete,Partial,None,Not Imaged"',
       "AJ4:AJ1003", "list", "Recanalisation")
add_dv(ws_mvt, '"Major,Clinically Relevant Non-Major,Minor,None"',
       "AV4:AV1003", "list", "Bleeding Grade")


# ══════════════════════════════════════════════════════════════════════════
# SHEET 8 – MORBIDITY  (Topics 1, 3, 4, 5)
# ══════════════════════════════════════════════════════════════════════════
morb_sections = [
    ("IDENTIFIERS", DARK_BLUE, [
        ("Patient ID",              12, ""),
        ("Admission Date",          13, ""),
    ]),
    ("GENERAL COMPLICATIONS", ORANGE, [
        ("Any Complication",            10, "1=Yes 0=No"),
        ("Clavien-Dindo Grade",         12, "I / II / IIIa / IIIb / IVa / IVb / V  (Ref: Dindo 2004)"),
        ("Number of Complications",      8, "Integer"),
        ("30-day Reoperation",          10, "1=Yes 0=No"),
        ("Reoperation Indication",      18, "Free text"),
    ]),
    ("SURGICAL COMPLICATIONS", BLUE, [
        ("Anastomotic Leak",            10, "1=Yes 0=No"),
        ("Bowel Necrosis Post-Op",      10, "1=Yes 0=No"),
        ("Short Bowel Syndrome",        10, "1=Yes 0=No – <200cm small bowel remaining"),
        ("Intra-Abdominal Infection",   10, "1=Yes 0=No"),
        ("Wound Infection",             10, "1=Yes 0=No"),
        ("Fascial Dehiscence",          10, "1=Yes 0=No"),
        ("Post-Op Bleeding",            10, "1=Yes 0=No"),
        ("Stoma Complications",         10, "1=Yes 0=No"),
        ("Hernia (Incisional)",         10, "1=Yes 0=No"),
        ("Endovascular Complication",   10, "1=Yes 0=No – access site, dissection, stent issue"),
        ("Endovascular Complication Detail", 16, "Free text"),
    ]),
    ("MEDICAL COMPLICATIONS", "375623", [
        ("Pneumonia",                   10, "1=Yes 0=No"),
        ("AKI (KDIGO Stage)",           10, "0/1/2/3"),
        ("New Renal Replacement Therapy", 10, "1=Yes 0=No"),
        ("MI Post-Op",                  10, "1=Yes 0=No"),
        ("Stroke Post-Op",              10, "1=Yes 0=No"),
        ("PE Post-Op",                  10, "1=Yes 0=No"),
        ("DVT Post-Op",                 10, "1=Yes 0=No"),
        ("Atrial Fibrillation Post-Op", 10, "1=Yes 0=No – new or worsening"),
        ("Liver Failure",               10, "1=Yes 0=No"),
        ("C. Difficile",                10, "1=Yes 0=No"),
        ("Sepsis Post-Op",              10, "1=Yes 0=No"),
        ("Septic Shock Post-Op",        10, "1=Yes 0=No"),
    ]),
    ("HOSPITAL STAY", "1F497D", [
        ("Total Hospital LOS (days)",   10, ""),
        ("ICU LOS (days)",              10, ""),
        ("Ward LOS (days)",             10, ""),
        ("Discharge Destination",       16, "Home / Rehab / Nursing Home / Other Hospital / Died / Palliative"),
        ("Discharge with Stoma",        10, "1=Yes 0=No"),
        ("Stoma Reversal Planned",      10, "1=Yes 0=No"),
        ("Discharge with TPN",          10, "1=Yes 0=No"),
        ("Nutritional Support at Discharge", 12, "Oral / Enteral / TPN / None"),
        ("Discharge Anticoagulation",   10, "1=Yes 0=No"),
    ]),
]

ws_morb, _ = build_sheet(wb, "🩹 Morbidity", ORANGE,
    "MORBIDITY & COMPLICATIONS  |  Topics 1, 3, 4, 5", morb_sections)

add_dv(ws_morb, '"I,II,IIIa,IIIb,IVa,IVb,V"',
       "D4:D1003", "list", "Clavien-Dindo")
add_dv(ws_morb, '"0,1,2,3"',
       "U4:U1003", "list", "AKI Stage")
add_dv(ws_morb, '"Home,Rehab Unit,Nursing Home,Other Hospital,Died,Palliative Care"',
       "AH4:AH1003", "list", "Discharge Destination")


# ══════════════════════════════════════════════════════════════════════════
# SHEET 9 – MORTALITY  (Topics 1, 3, 4, 5)
# ══════════════════════════════════════════════════════════════════════════
mort_sections = [
    ("IDENTIFIERS", DARK_BLUE, [
        ("Patient ID",              12, ""),
        ("Admission Date",          13, ""),
    ]),
    ("MORTALITY OUTCOMES", RED, [
        ("In-Hospital Mortality",       12, "1=Yes 0=No"),
        ("30-Day Mortality",            12, "1=Yes 0=No  –  PRIMARY OUTCOME for Topics 1, 3, 4, 5"),
        ("90-Day Mortality",            12, "1=Yes 0=No"),
        ("1-Year Mortality",            12, "1=Yes 0=No"),
        ("Date of Death",               13, "DD/MM/YYYY"),
        ("Time Admission → Death (days)", 10, ""),
        ("Place of Death",              14, "ICU / Ward / Theatre / ED / Home / Hospice / Other Hospital"),
        ("Cause of Death",              18, "Bowel Necrosis / Multi-Organ Failure / Septic Shock / Cardiac / Respiratory / Unknown / Other"),
        ("Cause of Death – Primary",    18, "Free text – as documented on death certificate"),
        ("Post-Mortem Performed",       10, "1=Yes 0=No"),
        ("Post-Mortem Findings",        18, "Free text – if available"),
    ]),
    ("PALLIATIVE / GOALS OF CARE", "595959", [
        ("Goals of Care Discussion Documented", 10, "1=Yes 0=No – ESVS + frailty rec"),
        ("Time of GoC Discussion (days from admission)", 10, ""),
        ("Who Led GoC Discussion",       16, "Vascular Surgeon / Palliative Care / Intensivist / Other"),
        ("Patient Involved in Decision", 10, "1=Yes 0=No"),
        ("Family Involved in Decision",  10, "1=Yes 0=No"),
        ("DNACPR Order",                10, "1=Yes 0=No"),
        ("Palliative Care Referral",     10, "1=Yes 0=No"),
        ("Conservative Management Chosen", 10, "1=Yes 0=No – no operative or invasive intervention"),
        ("Reason Conservative Management", 18, "Frailty / Patient Choice / Medical Futility / Comorbidity / Peritonitis-Terminal / Other"),
        ("CFS at Time of Decision",      9, "1-9"),
        ("30-Day Mortality in Conservative Group", 10, "1=Yes 0=No"),
    ]),
]

ws_mort, _ = build_sheet(wb, "📉 Mortality", RED,
    "MORTALITY & GOALS OF CARE  |  Topics 1, 3, 4, 5", mort_sections)

add_dv(ws_mort, '"ICU,Ward,Theatre,ED,Home,Hospice,Other Hospital"',
       "J4:J1003", "list", "Place of Death")
add_dv(ws_mort, '"Bowel Necrosis,Multi-Organ Failure,Septic Shock,Cardiac,Respiratory,Haemorrhage,Unknown,Other"',
       "K4:K1003", "list", "Cause of Death")

# Conditional formatting: red fill for in-hospital death
from openpyxl.formatting.rule import CellIsRule
ws_mort.conditional_formatting.add(
    "C4:C1003",
    CellIsRule(operator="equal", formula=["1"],
               fill=PatternFill("solid", fgColor=LIGHT_RED),
               font=Font(color=RED, bold=True))
)
ws_mort.conditional_formatting.add(
    "D4:D1003",
    CellIsRule(operator="equal", formula=["1"],
               fill=PatternFill("solid", fgColor=LIGHT_RED),
               font=Font(color=RED, bold=True))
)


# ══════════════════════════════════════════════════════════════════════════
# SHEET 10 – ESVS 2025 GUIDELINE COMPLIANCE AUDIT  (Topic 6)
# ══════════════════════════════════════════════════════════════════════════
esvs_sections = [
    ("IDENTIFIERS", DARK_BLUE, [
        ("Patient ID",              12, ""),
        ("Admission Date",          13, ""),
        ("Diagnosis",               18, "AMI-Embolic / AMI-Thrombotic / NOMI / MVT / CMI"),
    ]),
    ("AMI – ESVS 2025 CLASS I RECOMMENDATIONS", "C55A11", [
        ("R1: CTA as first-line imaging in suspected AMI",          14, "1=Met 0=Not Met 9=Not Applicable"),
        ("R1 – If not met, reason",                                 18, "No CTA available / Clinical deterioration / Moved to theatre / Other"),
        ("R2: Vascular surgery involvement in all confirmed AMI",   14, "1=Met 0=Not Met"),
        ("R3: AMI subtype documented (embolic/thrombotic/NOMI/venous)", 10, "1=Met 0=Not Met"),
        ("R4: Endovascular option considered in arterial AMI",      12, "1=Considered 0=Not Considered 9=N/A"),
        ("R5: Bowel viability assessed at/after revascularisation", 12, "1=Met 0=Not Met 9=N/A"),
        ("R6: Second-look strategy planned where viability uncertain", 12, "1=Met 0=Not Met 9=N/A"),
        ("R7: ICU post-op care arranged",                           12, "1=Met 0=Not Met"),
        ("R8: Anticoagulation considered for all arterial AMI post-op", 12, "1=Met 0=Not Met 9=N/A"),
    ]),
    ("MVT – ESVS 2025 CLASS I RECOMMENDATIONS", "1F497D", [
        ("R9: Anticoagulation started within 24h of MVT diagnosis",  14, "1=Met 0=Not Met 9=N/A"),
        ("R10: CTA or MRA for initial MVT imaging",                  14, "1=Met 0=Not Met 9=N/A"),
        ("R11: Thrombophilia screen performed in unprovoked MVT",    14, "1=Met 0=Not Met 9=N/A"),
        ("R12: Duration AC ≥3 months for provoked MVT",             14, "1=Met 0=Not Met 9=N/A"),
        ("R13: Duration AC ≥6 months or indefinite for unprovoked MVT", 14, "1=Met 0=Not Met 9=N/A"),
        ("R14: Follow-up imaging within 3 months to assess recanalisation", 14, "1=Met 0=Not Met 9=N/A"),
    ]),
    ("CMI – ESVS 2025 CLASS I RECOMMENDATIONS", "375623", [
        ("R15: CTA as first-line for CMI diagnosis",                 14, "1=Met 0=Not Met 9=N/A"),
        ("R16: Vascular MDT discussion before CMI intervention",     14, "1=Met 0=Not Met 9=N/A"),
        ("R17: Endovascular stenting preferred over open for CMI",   14, "1=Met 0=Not Met 9=N/A"),
        ("R18: Nutritional optimisation prior to elective CMI repair", 14, "1=Met 0=Not Met 9=N/A"),
        ("R19: Antiplatelet therapy prescribed post-CMI stenting",   14, "1=Met 0=Not Met 9=N/A"),
        ("R20: Duplex surveillance post-CMI stenting at 1 and 12 months", 14, "1=Met 0=Not Met 9=N/A"),
    ]),
    ("GENERAL PROCESS MEASURES", BLUE, [
        ("Vascular Surgeon as Responsible Clinician",           14, "1=Yes 0=No"),
        ("Formal MDT Discussion",                               12, "1=Yes 0=No"),
        ("Palliative/Goals of Care Discussion Documented",      12, "1=Yes 0=No"),
        ("Discharge Summary Completed",                         12, "1=Yes 0=No"),
        ("Follow-Up Appointment Arranged at Discharge",         12, "1=Yes 0=No"),
        ("Overall Guideline Compliance Score (% Applicable Recs Met)", 12, "Calculated field – sum Met / sum Applicable x 100"),
        ("Audit Outcome",                                       12, "Compliant / Partially Compliant / Non-Compliant"),
    ]),
]

ws_esvs, _ = build_sheet(wb, "🔍 ESVS Audit", "17375E",
    "ESVS 2025 GUIDELINE COMPLIANCE AUDIT  |  Topic 6", esvs_sections)

for col_range in ["D4:D1003","F4:F1003","G4:G1003","H4:H1003","I4:I1003","J4:J1003","K4:K1003","L4:L1003","M4:M1003"]:
    add_dv(ws_esvs, '"1,0,9"', col_range, "list",
           "Compliance", "1=Met | 0=Not Met | 9=Not Applicable")

# Conditional formatting for compliance
ws_esvs.conditional_formatting.add(
    "D4:Y1003",
    CellIsRule(operator="equal", formula=["1"],
               fill=PatternFill("solid", fgColor=LIGHT_GRN),
               font=Font(color=GREEN))
)
ws_esvs.conditional_formatting.add(
    "D4:Y1003",
    CellIsRule(operator="equal", formula=["0"],
               fill=PatternFill("solid", fgColor=LIGHT_RED),
               font=Font(color=RED))
)
ws_esvs.conditional_formatting.add(
    "D4:Y1003",
    CellIsRule(operator="equal", formula=["9"],
               fill=PatternFill("solid", fgColor=GREY_LIGHT),
               font=Font(color="888888"))
)


# ══════════════════════════════════════════════════════════════════════════
# SHEET 11 – FOLLOW-UP  (Topics 1, 3, 5)
# ══════════════════════════════════════════════════════════════════════════
fu_sections = [
    ("IDENTIFIERS", DARK_BLUE, [
        ("Patient ID",              12, ""),
        ("Admission Date",          13, ""),
    ]),
    ("OUTPATIENT FOLLOW-UP", BLUE, [
        ("Follow-Up Arranged at Discharge", 10, "1=Yes 0=No – ESVS rec"),
        ("Date of 1st Follow-Up Appointment", 13, "DD/MM/YYYY"),
        ("Time Discharge → 1st FU (days)", 10, ""),
        ("Attended 1st Appointment",        10, "1=Yes 0=No / Lost to FU"),
        ("Clinic Type",                     14, "Vascular / GI / Haematology / Combined / None"),
        ("Duplex / Imaging at FU",          10, "1=Yes 0=No"),
        ("FU Imaging Result",               16, "Patent / Stenosed / Occluded / N/A"),
        ("Symptom Resolution",              12, "Complete / Partial / None / Worse"),
        ("Return to Normal Diet",           10, "1=Yes 0=No – relevant post-bowel resection/CMI"),
        ("Stoma Reversal Performed",        10, "1=Yes 0=No"),
        ("Date Stoma Reversal",             13, "DD/MM/YYYY"),
        ("TPN Discontinued",                10, "1=Yes 0=No"),
        ("Date TPN Discontinued",           13, "DD/MM/YYYY"),
    ]),
    ("RE-INTERVENTION", ORANGE, [
        ("Re-Intervention Required",        12, "1=Yes 0=No"),
        ("Time to Re-Intervention (months)", 10, ""),
        ("Re-Intervention Type",            18, "Endovascular Re-stenting / Open Bypass / Bowel Resection / Anticoag Change / Other"),
        ("In-Stent Stenosis Confirmed",     10, "1=Yes 0=No"),
        ("Re-Stenting Performed",           10, "1=Yes 0=No"),
        ("Stent Patency at 12 months",      12, "Patent / Stenosed / Occluded / Not Imaged"),
        ("Stent Patency at 24 months",      12, "Patent / Stenosed / Occluded / Not Imaged"),
    ]),
    ("RECURRENCE & LONG-TERM", "375623", [
        ("AMI Recurrence",                  10, "1=Yes 0=No"),
        ("Time to AMI Recurrence (months)", 10, ""),
        ("MVT Recurrence",                  10, "1=Yes 0=No"),
        ("Time to MVT Recurrence (months)", 10, ""),
        ("SBS (Short Bowel Syndrome) at 6 months", 10, "1=Yes 0=No"),
        ("Quality of Life Assessment Tool", 12, "EQ-5D / SF-36 / None / Other"),
        ("QoL Score at 3 months",           10, "Numeric"),
        ("QoL Score at 12 months",          10, "Numeric"),
        ("Patient Alive at Last Follow-Up", 10, "1=Yes 0=No"),
        ("Date of Last Contact",            13, "DD/MM/YYYY"),
    ]),
]

ws_fu, _ = build_sheet(wb, "📅 Follow-Up", "2E74B5",
    "FOLLOW-UP  |  Topics 1, 3, 5", fu_sections)

add_dv(ws_fu, '"Vascular,GI,Haematology,Combined,None"',
       "G4:G1003", "list", "Clinic Type")
add_dv(ws_fu, '"Patent,Stenosed,Occluded,N/A"',
       "H4:H1003", "list", "FU Imaging")
add_dv(ws_fu, '"Complete,Partial,None,Worse"',
       "I4:I1003", "list", "Symptoms")
add_dv(ws_fu, '"Endovascular Re-stenting,Open Bypass,Bowel Resection,Anticoag Change,Other"',
       "P4:P1003", "list", "Re-intervention Type")
add_dv(ws_fu, '"Patent,Stenosed,Occluded,Not Imaged"',
       "S4:S1003", "list", "Stent 12m")
add_dv(ws_fu, '"Patent,Stenosed,Occluded,Not Imaged"',
       "T4:T1003", "list", "Stent 24m")


# ══════════════════════════════════════════════════════════════════════════
# SHEET 12 – ANALYSIS SUMMARY (auto-calc dashboard)
# ══════════════════════════════════════════════════════════════════════════
ws_sum = wb.create_sheet("📊 Analysis Summary")
ws_sum.sheet_properties.tabColor = GREY_HEAD
ws_sum.column_dimensions["A"].width = 45
ws_sum.column_dimensions["B"].width = 20
ws_sum.column_dimensions["C"].width = 20
ws_sum.column_dimensions["D"].width = 30

# Title
ws_sum.merge_cells("A1:D1")
ws_sum.cell(1,1).value = "ANALYSIS SUMMARY – DATA COMPLETENESS & KEY METRICS"
ws_sum.cell(1,1).fill  = PatternFill("solid", fgColor=NAVY)
ws_sum.cell(1,1).font  = Font(name="Arial", bold=True, color=WHITE, size=13)
ws_sum.cell(1,1).alignment = Alignment(horizontal="left", vertical="center")
ws_sum.row_dimensions[1].height = 28

summary_rows = [
    (3,  "METRIC", "VALUE", "FORMULA / NOTE", "REFERENCE TOPIC"),
    (4,  "Total patients entered", "=COUNTA('🧍 Demographics'!A4:A1003)", "Count of Patient IDs", "All"),
    (5,  "--- TOPIC 1: Time-to-CTA ---", "", "", ""),
    (6,  "Median arrival → CTA time (min)", "Manual – export to SPSS/R", "From Investigations sheet col AY", "Topic 1"),
    (7,  "% CTA within 60 min of arrival", "Manual", "Arrivals→CTA <60 / total", "Topic 1"),
    (8,  "% CTA within 120 min of arrival","Manual", "", "Topic 1"),
    (9,  "30-day mortality (all)", f"=COUNTIF('📉 Mortality'!D4:D1003,1)/COUNTA('🧍 Demographics'!A4:A1003)", "Proportion (format as %)", "Topic 1"),
    (10, "--- TOPIC 2: Lactate ---", "", "", ""),
    (11, "% with lactate taken at presentation", "Manual", "From Investigations sheet", "Topic 2"),
    (12, "% AMI with normal lactate (<2 mmol/L)", "Manual", "", "Topic 2"),
    (13, "--- TOPIC 3: Endovascular vs Open ---", "", "", ""),
    (14, "N endovascular (primary)", f"=COUNTIF('⚕️ Interventions'!C4:C1003,\"Endovascular\")", "", "Topic 3"),
    (15, "N open surgery (primary)", f"=COUNTIF('⚕️ Interventions'!C4:C1003,\"Open Surgery\")", "", "Topic 3"),
    (16, "N hybrid", f"=COUNTIF('⚕️ Interventions'!C4:C1003,\"Hybrid\")", "", "Topic 3"),
    (17, "N conservative / palliation", f"=COUNTIF('⚕️ Interventions'!C4:C1003,\"Conservative\")+COUNTIF('⚕️ Interventions'!C4:C1003,\"Comfort/Palliation\")", "", "Topic 3"),
    (18, "--- TOPIC 4: NOMI ---", "", "", ""),
    (19, "N NOMI confirmed", f"=COUNTIF('🏥 Diagnosis'!C4:C1003,\"NOMI\")", "", "Topic 4"),
    (20, "--- TOPIC 5: MVT ---", "", "", ""),
    (21, "N MVT confirmed", f"=COUNTIF('💊 MVT & Anticoag'!C4:C1003,1)", "", "Topic 5"),
    (22, "N MVT on DOAC", f"=COUNTIF('💊 MVT & Anticoag'!AC4:AC1003,\"DOAC\")", "", "Topic 5"),
    (23, "N MVT on VKA", f"=COUNTIF('💊 MVT & Anticoag'!AC4:AC1003,\"VKA\")", "", "Topic 5"),
    (24, "--- TOPIC 6: ESVS Compliance ---", "", "", ""),
    (25, "N with R1 (CTA) met", f"=COUNTIF('🔍 ESVS Audit'!D4:D1003,1)", "", "Topic 6"),
    (26, "N with R2 (vascular involvement) met", f"=COUNTIF('🔍 ESVS Audit'!F4:F1003,1)", "", "Topic 6"),
    (27, "N with R9 (MVT anticoag <24h) met", f"=COUNTIF('🔍 ESVS Audit'!L4:L1003,1)", "", "Topic 6"),
    (28, "", "", "", ""),
    (29, "EXPORT NOTE", "", "", ""),
    (30, "For multivariate analysis, export all sheets to .csv and import into SPSS, R (survival/logistic), or Stata.", "", "", ""),
    (31, "Use Patient ID as the join key across all sheets.", "", "", ""),
    (32, "SOFA + Severity Category (Sheet 8) should be included as covariates in all mortality models.", "", "", ""),
    (33, "Binary outcome variables (30-day mortality, bowel resection) are suitable for logistic regression.", "", "", ""),
    (34, "Time-to-event outcomes (survival, recanalisation) are suitable for Kaplan-Meier + Cox regression.", "", "", ""),
]

for row_num, col1, col2, col3, col4 in summary_rows:
    if col1.startswith("---"):
        ws_sum.merge_cells(f"A{row_num}:D{row_num}")
        c = ws_sum.cell(row_num, 1, col1)
        c.fill = PatternFill("solid", fgColor=MID_BLUE)
        c.font = Font(name="Arial", bold=True, color=NAVY, size=10)
    elif col1 in ("METRIC", "EXPORT NOTE"):
        for ci, v in enumerate([col1, col2, col3, col4], 1):
            c = ws_sum.cell(row_num, ci, v)
            c.fill = PatternFill("solid", fgColor=NAVY)
            c.font = Font(name="Arial", bold=True, color=WHITE, size=10)
            c.alignment = Alignment(horizontal="left", vertical="center")
    elif col1 in ("For multivariate analysis, export all sheets to .csv and import into SPSS, R (survival/logistic), or Stata.",
                  "Use Patient ID as the join key across all sheets.",
                  "SOFA + Severity Category (Sheet 8) should be included as covariates in all mortality models.",
                  "Binary outcome variables (30-day mortality, bowel resection) are suitable for logistic regression.",
                  "Time-to-event outcomes (survival, recanalisation) are suitable for Kaplan-Meier + Cox regression."):
        ws_sum.merge_cells(f"A{row_num}:D{row_num}")
        c = ws_sum.cell(row_num, 1, col1)
        c.font = Font(name="Arial", color=GREY_HEAD, size=10, italic=True)
    else:
        vals = [col1, col2, col3, col4]
        for ci, v in enumerate(vals, 1):
            c = ws_sum.cell(row_num, ci, v)
            c.font = Font(name="Arial", size=10, color=GREY_HEAD)
            c.fill = PatternFill("solid", fgColor=GREY_LIGHT if row_num % 2 == 0 else WHITE)
            c.alignment = Alignment(wrap_text=True, vertical="center")

# Header row special
for ci, v in enumerate(["METRIC", "VALUE", "FORMULA / NOTE", "REFERENCE TOPIC"], 1):
    ws_sum.cell(3, ci).border = thin_border()

# ══════════════════════════════════════════════════════════════════════════
# GLOBAL: freeze panes and set zoom on all data sheets
# ══════════════════════════════════════════════════════════════════════════
for ws in [ws_demo, ws_pres, ws_inv, ws_diag, ws_int, ws_sev, ws_mvt, ws_morb, ws_mort, ws_esvs, ws_fu]:
    ws.freeze_panes = "C4"
    ws.sheet_view.zoomScale = 90

# ══════════════════════════════════════════════════════════════════════════
# SAVE
# ══════════════════════════════════════════════════════════════════════════
out_path = "/tmp/workspace/mesenteric-ischaemia/Mesenteric_Ischaemia_Database.xlsx"
wb.save(out_path)
print(f"Saved: {out_path}")