#!/usr/bin/env python3
"""Build the full COO Outage Board Packet."""

import csv, os, datetime
from pathlib import Path

BASE = Path("/private/var/folders/t6/_sx_03q50_v9w2jpnq683jmw0000gn/T/claude-run-9wb1tfzz")
OUT  = BASE / "deliverables"
OUT.mkdir(exist_ok=True)

# ── Load data ─────────────────────────────────────────────────────────
def load_csv(name):
    with open(BASE / "inputs" / name) as f:
        return list(csv.DictReader(f))

timeline  = load_csv("incident_timeline.csv")
hourly    = load_csv("support_metrics_hourly.csv")
daily_kpi = load_csv("service_kpis_daily.csv")
accounts  = load_csv("customer_impact_sample.csv")

# Derived metrics
baseline_inbound   = (int(hourly[0]["inbound_tickets"]) + int(hourly[1]["inbound_tickets"])) / 2  # 06-07h avg
peak_inbound       = max(int(r["inbound_tickets"]) for r in hourly)
surge_ratio        = round(peak_inbound / baseline_inbound, 1)
baseline_backlog   = int(hourly[0]["open_backlog"])
max_backlog        = max(int(r["open_backlog"]) for r in hourly)
backlog_delta      = max_backlog - baseline_backlog
baseline_csat      = (float(hourly[0]["csat_pct"]) + float(hourly[1]["csat_pct"])) / 2
min_csat           = min(float(r["csat_pct"]) for r in hourly)
csat_drop_pp       = round(baseline_csat - min_csat, 1)
total_escalations  = sum(int(r["escalations"]) for r in hourly)
total_impacted_mrr = sum(int(r["monthly_recurring_revenue_usd"]) for r in accounts)
enterprise_esc     = sum(1 for a in accounts if a["escalated_to_csm"].strip() == "yes")
avg_degrade_min    = round(sum(int(a["minutes_degraded"]) for a in accounts) / len(accounts), 1)

# ═══════════════════════════════════════════════════════════════════════
# 1) INCIDENT SUMMARY — Word (.docx)
# ═══════════════════════════════════════════════════════════════════════
from docx import Document
from docx.shared import Pt, Inches, RGBColor
from docx.enum.text import WD_ALIGN_PARAGRAPH

doc = Document()

style = doc.styles['Normal']
font = style.font
font.name = 'Calibri'
font.size = Pt(11)

# Title
title = doc.add_heading('Incident Summary — Support Platform Auth Outage', level=0)
title.alignment = WD_ALIGN_PARAGRAPH.CENTER

doc.add_paragraph(
    f"Date: 2026-04-14 | Prepared for: COO Review | Classification: Internal – Confidential"
).alignment = WD_ALIGN_PARAGRAPH.CENTER

doc.add_paragraph("")

# Section 1 — Incident Window
doc.add_heading('1. Incident Window', level=1)
tbl = doc.add_table(rows=4, cols=2, style='Light Shading Accent 1')
for i, (label, val) in enumerate([
    ("Detection", "07:58 PT — Datadog SEV-2 alert on API error rate (4.8 %)"),
    ("Escalation to SEV-1", "08:07 PT — War room opened by Incident Commander"),
    ("Mitigation Start", "08:31 PT — Rollback canary deployed to 30 % traffic"),
    ("Stable State", "09:12 PT — Auth latency returned to normal operating range"),
]):
    tbl.rows[i].cells[0].text = label
    tbl.rows[i].cells[1].text = val

# Section 2 — Root Cause
doc.add_heading('2. Root Cause', level=1)
doc.add_paragraph(
    "Known cause: A misconfigured auth-cache TTL in the us-east app cluster caused token-refresh "
    "calls to time out under load. The faulty configuration was deployed as part of a routine "
    "infrastructure change the prior evening (2026-04-13 22:15 PT)."
)
doc.add_paragraph(
    "Pending validation: Engineering is confirming whether secondary read-replica failover "
    "contributed to the elevated timeout duration. Full root-cause analysis is due by 2026-04-17."
)

# Section 3 — Scope of Impact
doc.add_heading('3. Scope of Impact', level=1)
doc.add_paragraph(f"• Ticket surge ratio: {surge_ratio}x above pre-outage baseline")
doc.add_paragraph(f"• Peak open backlog: {max_backlog} tickets (Δ {backlog_delta} above baseline)")
doc.add_paragraph(f"• CSAT drop: {csat_drop_pp} percentage points at trough (82.4 % vs {baseline_csat:.1f} % baseline)")
doc.add_paragraph(f"• Total escalations during incident window: {total_escalations}")
doc.add_paragraph(f"• Impacted enterprise MRR exposure: ${total_impacted_mrr:,}")
doc.add_paragraph(f"• {enterprise_esc} enterprise accounts escalated via CSM channel")
doc.add_paragraph(f"• Average degradation per affected account: {avg_degrade_min} minutes")

# Section 4 — Response Actions
doc.add_heading('4. Response Actions & Ownership', level=1)
tbl2 = doc.add_table(rows=len(timeline)+1, cols=4, style='Light Shading Accent 1')
for j, hdr in enumerate(["Time (PT)", "Severity", "Action", "Owner"]):
    tbl2.rows[0].cells[j].text = hdr
for i, ev in enumerate(timeline):
    tbl2.rows[i+1].cells[0].text = ev["timestamp_pt"].split(" ")[1]
    tbl2.rows[i+1].cells[1].text = ev["severity"]
    tbl2.rows[i+1].cells[2].text = ev["event"]
    tbl2.rows[i+1].cells[3].text = ev["owner"]

# Section 5 — Next Steps
doc.add_heading('5. Immediate Next Steps (7-Day Window)', level=1)
for step in [
    "Complete full root-cause analysis by 2026-04-17.",
    "Deploy permanent auth-cache fix with config-guard validation by 2026-04-18.",
    "Run load-test regression on auth refresh path by 2026-04-19.",
    "Publish customer postmortem on status page by 2026-04-16.",
    "Conduct support debrief and update escalation playbook by 2026-04-20.",
    "Present final findings to COO/VP Engineering by 2026-04-21.",
]:
    doc.add_paragraph(step, style='List Bullet')

doc.save(str(OUT / "Incident_Summary.docx"))
print("✓ Incident_Summary.docx")

# ═══════════════════════════════════════════════════════════════════════
# 2) KPI SHEET — Excel (.xlsx)
# ═══════════════════════════════════════════════════════════════════════
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, numbers
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.utils import get_column_letter

wb = Workbook()

# ── Tab 1: Hourly_Support_Impact ──
ws1 = wb.active
ws1.title = "Hourly_Support_Impact"
h1_headers = ["Hour (PT)", "Inbound Tickets", "Resolved Tickets", "Open Backlog",
              "Avg First Response (min)", "Avg Resolution (min)", "CSAT %", "Escalations",
              "Surge Ratio", "Backlog Delta", "CSAT Drop (pp)"]
header_fill = PatternFill("solid", fgColor="4472C4")
header_font = Font(bold=True, color="FFFFFF", size=11)
for c, h in enumerate(h1_headers, 1):
    cell = ws1.cell(row=1, column=c, value=h)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(horizontal="center")

for r, row in enumerate(hourly, 2):
    ws1.cell(r, 1, row["hour_pt"])
    ws1.cell(r, 2, int(row["inbound_tickets"]))
    ws1.cell(r, 3, int(row["resolved_tickets"]))
    ws1.cell(r, 4, int(row["open_backlog"]))
    ws1.cell(r, 5, int(row["avg_first_response_min"]))
    ws1.cell(r, 6, int(row["avg_resolution_min"]))
    ws1.cell(r, 7, float(row["csat_pct"]))
    ws1.cell(r, 8, int(row["escalations"]))
    # Formulas
    ws1.cell(r, 9).value = f"=B{r}/{baseline_inbound:.1f}"       # Surge Ratio
    ws1.cell(r, 9).number_format = '0.0'
    ws1.cell(r, 10).value = f"=D{r}-{baseline_backlog}"          # Backlog Delta
    ws1.cell(r, 11).value = f"={baseline_csat:.1f}-G{r}"         # CSAT Drop pp
    ws1.cell(r, 11).number_format = '0.0'

for col in range(1, 12):
    ws1.column_dimensions[get_column_letter(col)].width = 18

# Chart — Inbound vs Resolved bar chart
chart1 = BarChart()
chart1.title = "Hourly Inbound vs Resolved Tickets"
chart1.y_axis.title = "Ticket Count"
chart1.x_axis.title = "Hour (PT)"
chart1.style = 10
data_ref = Reference(ws1, min_col=2, max_col=3, min_row=1, max_row=len(hourly)+1)
cats = Reference(ws1, min_col=1, min_row=2, max_row=len(hourly)+1)
chart1.add_data(data_ref, titles_from_data=True)
chart1.set_categories(cats)
chart1.width = 22
ws1.add_chart(chart1, "A12")

# ── Tab 2: Daily_Service_KPIs ──
ws2 = wb.create_sheet("Daily_Service_KPIs")
d_headers = ["Date", "Uptime %", "API p95 (ms)", "Error Rate %", "Payment Success %",
             "Active Tenants", "SEV Incidents", "Notes"]
for c, h in enumerate(d_headers, 1):
    cell = ws2.cell(1, c, h)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(horizontal="center")
for r, row in enumerate(daily_kpi, 2):
    ws2.cell(r, 1, row["date_pt"])
    ws2.cell(r, 2, float(row["uptime_pct"]))
    ws2.cell(r, 3, int(row["api_p95_ms"]))
    ws2.cell(r, 4, float(row["error_rate_pct"]))
    ws2.cell(r, 5, float(row["payment_success_pct"]))
    ws2.cell(r, 6, int(row["active_tenants"]))
    ws2.cell(r, 7, int(row["sev_incidents"]))
    ws2.cell(r, 8, row["notes"])
for col in range(1, 9):
    ws2.column_dimensions[get_column_letter(col)].width = 18

# Chart — Error rate trend
chart2 = LineChart()
chart2.title = "Daily Error Rate % (14-Day Trend)"
chart2.y_axis.title = "Error Rate %"
chart2.x_axis.title = "Date"
chart2.style = 10
err_data = Reference(ws2, min_col=4, min_row=1, max_row=len(daily_kpi)+1)
err_cats = Reference(ws2, min_col=1, min_row=2, max_row=len(daily_kpi)+1)
chart2.add_data(err_data, titles_from_data=True)
chart2.set_categories(err_cats)
chart2.width = 22
ws2.add_chart(chart2, "A18")

# ── Tab 3: Affected_Accounts ──
ws3 = wb.create_sheet("Affected_Accounts")
a_headers = ["Account", "Segment", "Region", "MRR (USD)", "Minutes Degraded",
             "Support Tickets", "Escalated to CSM", "Primary Issue"]
for c, h in enumerate(a_headers, 1):
    cell = ws3.cell(1, c, h)
    cell.fill = header_fill
    cell.font = header_font
for r, row in enumerate(accounts, 2):
    ws3.cell(r, 1, row["account_name"])
    ws3.cell(r, 2, row["segment"])
    ws3.cell(r, 3, row["region"])
    ws3.cell(r, 4, int(row["monthly_recurring_revenue_usd"]))
    ws3.cell(r, 4).number_format = '#,##0'
    ws3.cell(r, 5, int(row["minutes_degraded"]))
    ws3.cell(r, 6, int(row["support_tickets"]))
    ws3.cell(r, 7, row["escalated_to_csm"].strip())
    ws3.cell(r, 8, row["primary_issue"])
for col in range(1, 9):
    ws3.column_dimensions[get_column_letter(col)].width = 22

# ── Tab 4: Executive_Summary ──
ws4 = wb.create_sheet("Executive_Summary")
ws4.column_dimensions['A'].width = 35
ws4.column_dimensions['B'].width = 25
summary_rows = [
    ("Metric", "Value"),
    ("Incident Window", "07:58 - 09:12 PT (74 min)"),
    ("Peak Severity", "SEV-1"),
    ("Ticket Surge Ratio", f"{surge_ratio}x"),
    ("Peak Open Backlog", str(max_backlog)),
    ("Backlog Delta vs Baseline", f"+{backlog_delta}"),
    ("CSAT Trough", f"{min_csat} %"),
    ("CSAT Drop from Baseline", f"{csat_drop_pp} pp"),
    ("Total Escalations", str(total_escalations)),
    ("Enterprise Accounts Escalated", str(enterprise_esc)),
    ("Impacted MRR Exposure", f"${total_impacted_mrr:,}"),
    ("Avg Degradation per Account", f"{avg_degrade_min} min"),
    ("Uptime on Outage Day", "99.41 %"),
    ("Error Rate on Outage Day", "3.87 %"),
    ("Payment Success on Outage Day", "97.92 %"),
]
for r, (label, val) in enumerate(summary_rows, 1):
    c1 = ws4.cell(r, 1, label)
    c2 = ws4.cell(r, 2, val)
    if r == 1:
        c1.fill = header_fill; c1.font = header_font
        c2.fill = header_fill; c2.font = header_font

wb.save(str(OUT / "Outage_KPI_Sheet.xlsx"))
print("✓ Outage_KPI_Sheet.xlsx")

# ═══════════════════════════════════════════════════════════════════════
# 3) 6-SLIDE RECAP — PowerPoint (.pptx)
# ═══════════════════════════════════════════════════════════════════════
from pptx import Presentation
from pptx.util import Inches, Pt, Emu
from pptx.dml.color import RGBColor as PptRGB
from pptx.enum.text import PP_ALIGN

prs = Presentation()
prs.slide_width  = Inches(13.333)
prs.slide_height = Inches(7.5)

TITLE_LAYOUT = prs.slide_layouts[0]
CONTENT_LAYOUT = prs.slide_layouts[1]
BLANK_LAYOUT = prs.slide_layouts[6]

def add_slide(title_text, bullets, layout=CONTENT_LAYOUT):
    slide = prs.slides.add_slide(layout)
    slide.shapes.title.text = title_text
    if hasattr(slide.placeholders, '__getitem__') and len(slide.placeholders) > 1:
        body = slide.placeholders[1]
        tf = body.text_frame
        tf.clear()
        for i, b in enumerate(bullets):
            p = tf.paragraphs[0] if i == 0 else tf.add_paragraph()
            p.text = b
            p.font.size = Pt(18)
            p.space_after = Pt(6)
    return slide

# Slide 1 — Incident Overview
add_slide("Auth Outage Incident Overview — 2026-04-14", [
    "Severity: SEV-1 | Duration: 74 minutes (07:58 – 09:12 PT)",
    f"Root cause: Auth-cache TTL misconfiguration in us-east cluster",
    f"Ticket surge: {surge_ratio}x baseline | Peak backlog: {max_backlog} tickets",
    f"CSAT impact: dropped {csat_drop_pp} pp to 82.4 % at trough",
    f"Impacted MRR exposure: ${total_impacted_mrr:,} across {len(accounts)} accounts",
    "Status: Stable — rollback deployed, monitoring in place",
])

# Slide 2 — Timeline of Events
add_slide("Timeline of Events (all times PT)", [
    "07:58 — Datadog SEV-2 alert: API error rate 4.8 %",
    "08:07 — SEV-1 declared, war room opened",
    "08:11 — Root cause identified: auth token timeout in us-east",
    "08:22 — Hotfix branch created for auth-cache rollback",
    "08:31 — Canary rollback deployed (30 % traffic)",
    "08:44 — Rollback expanded to 100 % production",
    "09:12 — Auth latency back to normal operating range",
    "10:18 — Customer-facing status update published",
])

# Slide 3 — Customer + Revenue Exposure
add_slide("Customer & Revenue Exposure", [
    f"{enterprise_esc} enterprise accounts escalated via CSM channel",
    f"Total impacted MRR: ${total_impacted_mrr:,}",
    f"Average degradation: {avg_degrade_min} minutes per account",
    "Top affected: Northstar Health ($184K MRR, 47 min), Monument Finance ($176K, 44 min)",
    "Primary issues: login timeout, ticket-portal auth failures, SSO callback timeout",
    "Customer postmortem to be published by 2026-04-16",
])

# Slide 4 — Support KPI Impact
add_slide("Support KPI Impact During Outage Window", [
    f"Inbound ticket surge: {surge_ratio}x above baseline ({peak_inbound} vs ~{baseline_inbound:.0f}/hr)",
    f"Open backlog peaked at {max_backlog} (Δ +{backlog_delta} above baseline {baseline_backlog})",
    f"CSAT dropped from {baseline_csat:.1f} % to {min_csat} % (−{csat_drop_pp} pp)",
    f"Total escalations: {total_escalations} (vs ~1/hr baseline)",
    "Avg first-response time peaked at 29 min (baseline: ~11 min)",
    "Recovery trend: backlog clearing by 11:00, CSAT recovering toward 91+ % by 13:00",
])

# Slide 5 — Service KPI Trend & Recovery
add_slide("Service KPI Trend & Recovery (14-Day View)", [
    "Pre-outage baseline: 99.95 % uptime, 0.42 % error rate, 99.32 % payment success",
    "Outage day (Apr 14): 99.41 % uptime, 3.87 % error rate, 97.92 % payment success",
    "API p95 latency spiked to 612 ms (baseline: ~340 ms)",
    "Active tenants: 1,322 (−2 vs prior day — minimal churn signal so far)",
    "Error rate dropped from 6.2 % peak to 2.1 % within 7 minutes of canary deploy",
    "Full recovery confirmed at 09:12 PT; monitoring continues",
])

# Slide 6 — Corrective Actions + Ask
add_slide("Corrective Actions & Ask from Leadership", [
    "Action 1: Permanent auth-cache fix with config-guard — deploy by Apr 18",
    "Action 2: Load-test regression on auth refresh path — complete by Apr 19",
    "Action 3: Update support escalation playbook with auth-outage runbook — by Apr 20",
    "Action 4: Publish customer postmortem on status page — by Apr 16",
    "Action 5: Present final RCA to COO + VP Engineering — by Apr 21",
    "ASK: Approve 2-sprint engineering allocation for config-guard + monitoring uplift",
])

assert len(prs.slides) == 6, f"Expected 6 slides, got {len(prs.slides)}"
prs.save(str(OUT / "COO_Recap_6_Slides.pptx"))
print("✓ COO_Recap_6_Slides.pptx")

# ═══════════════════════════════════════════════════════════════════════
# 4) PDF EXPORTS
# ═══════════════════════════════════════════════════════════════════════
from fpdf import FPDF
from fpdf.enums import XPos, YPos

# ── PDF: Incident Summary ──
pdf = FPDF()
pdf.set_auto_page_break(auto=True, margin=15)
pdf.add_page()
pdf.set_font("Helvetica", "B", 18)
pdf.cell(0, 12, "Incident Summary - Support Platform Auth Outage", new_x=XPos.LMARGIN, new_y=YPos.NEXT, align="C")
pdf.set_font("Helvetica", "", 10)
pdf.cell(0, 8, "Date: 2026-04-14 | Prepared for: COO Review | Internal - Confidential", new_x=XPos.LMARGIN, new_y=YPos.NEXT, align="C")
pdf.ln(6)

def pdf_heading(pdf, text):
    pdf.set_font("Helvetica", "B", 13)
    pdf.set_x(pdf.l_margin)
    pdf.cell(w=pdf.epw, h=10, text=text, new_x=XPos.LMARGIN, new_y=YPos.NEXT)
    pdf.set_font("Helvetica", "", 10)

def pdf_bullet(pdf, text):
    pdf.set_x(pdf.l_margin)
    pdf.multi_cell(w=pdf.epw, h=6, text=f"  - {text}")

def pdf_para(pdf, text):
    pdf.set_x(pdf.l_margin)
    pdf.multi_cell(w=pdf.epw, h=6, text=text)

pdf_heading(pdf, "1. Incident Window")
for label, val in [
    ("Detection", "07:58 PT - Datadog SEV-2 alert on API error rate (4.8%)"),
    ("Escalation to SEV-1", "08:07 PT - War room opened"),
    ("Mitigation Start", "08:31 PT - Rollback canary deployed (30% traffic)"),
    ("Stable State", "09:12 PT - Auth latency back to normal"),
]:
    pdf_bullet(pdf, f"{label}: {val}")
pdf.ln(4)

pdf_heading(pdf, "2. Root Cause")
pdf_para(pdf, "Known cause: Misconfigured auth-cache TTL in us-east app cluster caused token-refresh timeouts. Deployed in routine infra change 2026-04-13 22:15 PT.")
pdf_para(pdf, "Pending: Engineering validating secondary read-replica failover contribution. Full RCA due 2026-04-17.")
pdf.ln(4)

pdf_heading(pdf, "3. Scope of Impact")
for line in [
    f"Ticket surge ratio: {surge_ratio}x above baseline",
    f"Peak open backlog: {max_backlog} (Delta +{backlog_delta})",
    f"CSAT drop: {csat_drop_pp} pp (82.4% vs {baseline_csat:.1f}% baseline)",
    f"Total escalations: {total_escalations}",
    f"Impacted MRR exposure: ${total_impacted_mrr:,}",
    f"{enterprise_esc} enterprise accounts escalated via CSM",
    f"Avg degradation per account: {avg_degrade_min} min",
]:
    pdf_bullet(pdf, line)
pdf.ln(4)

pdf_heading(pdf, "4. Response Actions")
pdf.set_font("Helvetica", "B", 9)
col_w = [20, 15, 80, 35]
for h, w in zip(["Time", "SEV", "Action", "Owner"], col_w):
    pdf.cell(w, 7, h, border=1)
pdf.ln()
pdf.set_font("Helvetica", "", 8)
for ev in timeline:
    t = ev["timestamp_pt"].split(" ")[1]
    pdf.cell(col_w[0], 6, t, border=1)
    pdf.cell(col_w[1], 6, ev["severity"], border=1)
    pdf.cell(col_w[2], 6, ev["event"][:62], border=1)
    pdf.cell(col_w[3], 6, ev["owner"], border=1)
    pdf.ln()
pdf.ln(4)

pdf_heading(pdf, "5. Immediate Next Steps (7 Days)")
for step in [
    "Full RCA by Apr 17", "Permanent fix deploy by Apr 18",
    "Load-test regression by Apr 19", "Customer postmortem by Apr 16",
    "Support debrief by Apr 20", "Final findings to COO by Apr 21",
]:
    pdf_bullet(pdf, step)

pdf.output(str(OUT / "Incident_Summary.pdf"))
print("✓ Incident_Summary.pdf")

# ── PDF: KPI Sheet ──
pdf2 = FPDF()
pdf2.set_auto_page_break(auto=True, margin=15)

# Page 1 — Executive Summary
pdf2.add_page()
pdf2.set_font("Helvetica", "B", 16)
pdf2.cell(0, 12, "Outage KPI Sheet - Executive Summary", new_x=XPos.LMARGIN, new_y=YPos.NEXT, align="C")
pdf2.ln(4)
pdf2.set_font("Helvetica", "B", 9)
pdf2.cell(80, 7, "Metric", border=1, align="C")
pdf2.cell(60, 7, "Value", border=1, align="C")
pdf2.ln()
pdf2.set_font("Helvetica", "", 9)
for label, val in summary_rows[1:]:
    pdf2.cell(80, 6, label, border=1)
    pdf2.cell(60, 6, val, border=1)
    pdf2.ln()

# Page 2 — Hourly Support Impact
pdf2.add_page("L")
pdf2.set_font("Helvetica", "B", 14)
pdf2.cell(0, 10, "Hourly Support Impact", new_x=XPos.LMARGIN, new_y=YPos.NEXT, align="C")
pdf2.set_font("Helvetica", "B", 8)
h_cols = ["Hour", "Inbound", "Resolved", "Backlog", "FRT(m)", "Res(m)", "CSAT%", "Esc"]
h_widths = [20, 22, 22, 22, 22, 22, 22, 18]
for h, w in zip(h_cols, h_widths):
    pdf2.cell(w, 7, h, border=1, align="C")
pdf2.ln()
pdf2.set_font("Helvetica", "", 8)
for row in hourly:
    vals = [row["hour_pt"], row["inbound_tickets"], row["resolved_tickets"],
            row["open_backlog"], row["avg_first_response_min"], row["avg_resolution_min"],
            row["csat_pct"], row["escalations"]]
    for v, w in zip(vals, h_widths):
        pdf2.cell(w, 6, str(v), border=1, align="C")
    pdf2.ln()

# Page 3 — Daily Service KPIs
pdf2.add_page("L")
pdf2.set_font("Helvetica", "B", 14)
pdf2.cell(0, 10, "Daily Service KPIs (14-Day Trend)", new_x=XPos.LMARGIN, new_y=YPos.NEXT, align="C")
pdf2.set_font("Helvetica", "B", 8)
d_cols = ["Date", "Uptime%", "p95(ms)", "Err%", "Pay%", "Tenants", "SEV", "Notes"]
d_widths = [25, 20, 20, 18, 20, 20, 15, 80]
for h, w in zip(d_cols, d_widths):
    pdf2.cell(w, 7, h, border=1, align="C")
pdf2.ln()
pdf2.set_font("Helvetica", "", 7)
for row in daily_kpi:
    vals = [row["date_pt"], row["uptime_pct"], row["api_p95_ms"],
            row["error_rate_pct"], row["payment_success_pct"],
            row["active_tenants"], row["sev_incidents"], row["notes"][:40]]
    for v, w in zip(vals, d_widths):
        pdf2.cell(w, 5, str(v), border=1, align="C")
    pdf2.ln()

# Page 4 — Affected Accounts
pdf2.add_page("L")
pdf2.set_font("Helvetica", "B", 14)
pdf2.cell(0, 10, "Affected Accounts", new_x=XPos.LMARGIN, new_y=YPos.NEXT, align="C")
pdf2.set_font("Helvetica", "B", 8)
a_cols = ["Account", "Segment", "Region", "MRR", "Min Deg", "Tickets", "CSM Esc", "Issue"]
a_widths = [38, 25, 22, 22, 20, 18, 18, 45]
for h, w in zip(a_cols, a_widths):
    pdf2.cell(w, 7, h, border=1, align="C")
pdf2.ln()
pdf2.set_font("Helvetica", "", 8)
for row in accounts:
    vals = [row["account_name"], row["segment"], row["region"],
            f"${int(row['monthly_recurring_revenue_usd']):,}",
            row["minutes_degraded"], row["support_tickets"],
            row["escalated_to_csm"].strip(), row["primary_issue"][:25]]
    for v, w in zip(vals, a_widths):
        pdf2.cell(w, 6, str(v), border=1, align="C")
    pdf2.ln()

pdf2.output(str(OUT / "Outage_KPI_Sheet.pdf"))
print("✓ Outage_KPI_Sheet.pdf")

# ── PDF: Slide Recap ──
pdf3 = FPDF()
pdf3.set_auto_page_break(auto=True, margin=15)
slide_content = [
    ("Slide 1: Auth Outage Incident Overview", [
        f"Severity: SEV-1 | Duration: 74 minutes (07:58-09:12 PT)",
        f"Root cause: Auth-cache TTL misconfiguration in us-east cluster",
        f"Ticket surge: {surge_ratio}x baseline | Peak backlog: {max_backlog}",
        f"CSAT impact: dropped {csat_drop_pp} pp to 82.4%",
        f"Impacted MRR exposure: ${total_impacted_mrr:,}",
        "Status: Stable - rollback deployed, monitoring continues",
    ]),
    ("Slide 2: Timeline of Events", [
        "07:58 - API error-rate alert (4.8%)",
        "08:07 - SEV-1 declared, war room opened",
        "08:11 - Auth token timeout identified",
        "08:22 - Hotfix branch created",
        "08:31 - Canary rollback (30%)",
        "08:44 - Full production rollback",
        "09:12 - Normal auth latency restored",
        "10:18 - Customer status update published",
    ]),
    ("Slide 3: Customer & Revenue Exposure", [
        f"{enterprise_esc} enterprise accounts escalated",
        f"Total impacted MRR: ${total_impacted_mrr:,}",
        f"Avg degradation: {avg_degrade_min} min/account",
        "Top: Northstar Health ($184K, 47m), Monument Finance ($176K, 44m)",
        "Issues: login timeout, auth failures, SSO callback timeout",
    ]),
    ("Slide 4: Support KPI Impact", [
        f"Inbound surge: {surge_ratio}x ({peak_inbound} vs ~{baseline_inbound:.0f}/hr)",
        f"Backlog peak: {max_backlog} (+{backlog_delta})",
        f"CSAT: {baseline_csat:.1f}% -> {min_csat}% (-{csat_drop_pp} pp)",
        f"Escalations: {total_escalations} total",
        "FRT peaked at 29 min (baseline ~11 min)",
    ]),
    ("Slide 5: Service KPI Trend & Recovery", [
        "Baseline: 99.95% uptime, 0.42% error rate",
        "Outage day: 99.41% uptime, 3.87% error rate",
        "API p95: 612 ms (baseline ~340 ms)",
        "Error rate: 6.2% peak -> 2.1% within 7 min of canary",
        "Full recovery at 09:12 PT",
    ]),
    ("Slide 6: Corrective Actions & Leadership Ask", [
        "Permanent auth-cache fix + config-guard by Apr 18",
        "Load-test regression by Apr 19",
        "Escalation playbook update by Apr 20",
        "Customer postmortem by Apr 16",
        "Final RCA to COO + VP Eng by Apr 21",
        "ASK: Approve 2-sprint allocation for config-guard + monitoring",
    ]),
]
for title, bullets in slide_content:
    pdf3.add_page("L")
    pdf3.set_font("Helvetica", "B", 20)
    pdf3.cell(0, 14, title, new_x=XPos.LMARGIN, new_y=YPos.NEXT, align="C")
    pdf3.ln(6)
    pdf3.set_font("Helvetica", "", 14)
    for b in bullets:
        pdf3.cell(0, 10, f"  * {b}", new_x=XPos.LMARGIN, new_y=YPos.NEXT)

pdf3.output(str(OUT / "COO_Recap_6_Slides.pdf"))
print("✓ COO_Recap_6_Slides.pdf")

print("\n=== All Office + PDF files generated ===")