gmal-scope-builder/backend/app/services/export_excel.py

"""Export ratecard data to Excel."""

import io
import logging
from collections import defaultdict

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from app.models.gmal import GmalAsset, Role
from app.models.project import Project, ClientAsset, Match, RatecardLine
from app.services.team_shape import calculate_team_shape

logger = logging.getLogger(__name__)

HEADER_FILL = PatternFill(start_color="1F4E79", end_color="1F4E79", fill_type="solid")
HEADER_FONT = Font(color="FFFFFF", bold=True, size=11)
DISCIPLINE_FILL = PatternFill(start_color="D6E4F0", end_color="D6E4F0", fill_type="solid")
THIN_BORDER = Border(
    left=Side(style="thin"),
    right=Side(style="thin"),
    top=Side(style="thin"),
    bottom=Side(style="thin"),
)


async def export_ratecard_excel(db: AsyncSession, project: Project, efficiency_levels: list[int] | None = None) -> bytes:
    """Generate an Excel workbook with the ratecard data.

    If efficiency_levels is provided (e.g. [10, 25, 50]), generates additional
    Team Shape tabs showing adjusted FTE at each efficiency level.

    Returns the workbook as bytes.
    """
    wb = Workbook()

    # Load all data
    lines_result = await db.execute(
        select(RatecardLine).where(RatecardLine.project_id == project.id)
    )
    lines = lines_result.scalars().all()

    if not lines:
        ws = wb.active
        ws.title = "Ratecard"
        ws["A1"] = "No ratecard data available"
        return _workbook_to_bytes(wb)

    # Load related entities
    role_ids = list(set(l.role_id for l in lines))
    asset_ids = list(set(l.client_asset_id for l in lines))
    gmal_ids = list(set(l.gmal_asset_id for l in lines))

    roles_result = await db.execute(select(Role).where(Role.id.in_(role_ids)))
    roles = {r.id: r for r in roles_result.scalars().all()}

    assets_result = await db.execute(select(ClientAsset).where(ClientAsset.id.in_(asset_ids)))
    client_assets = {a.id: a for a in assets_result.scalars().all()}

    gmals_result = await db.execute(select(GmalAsset).where(GmalAsset.id.in_(gmal_ids)))
    gmals = {g.id: g for g in gmals_result.scalars().all()}

    # Load selected matches for caveat lookup
    matches_result = await db.execute(
        select(Match).where(
            Match.client_asset_id.in_(asset_ids),
            Match.is_selected == True,
        )
    )
    selected_matches = matches_result.scalars().all()

    match_caveat_by_asset: dict[int, str] = {}
    gmal_caveat_by_asset: dict[int, str] = {}
    for m in selected_matches:
        if m.caveat_text:
            match_caveat_by_asset[m.client_asset_id] = m.caveat_text
        gmal = gmals.get(m.gmal_asset_id)
        if gmal and gmal.caveats:
            gmal_caveat_by_asset[m.client_asset_id] = gmal.caveats

    # Sheet 1: Ratecard Summary (roles x assets matrix)
    ws1 = wb.active
    ws1.title = "Ratecard Summary"
    _build_ratecard_sheet(
        ws1, lines, roles, client_assets, gmals,
        match_caveats=match_caveat_by_asset,
        gmal_caveats=gmal_caveat_by_asset,
    )

    # Sheet 2: Asset Detail
    ws2 = wb.create_sheet("Asset Detail")
    await _build_asset_detail_sheet(ws2, db, project, client_assets, gmals)

    # Sheet 3: Brief Analysis (if available)
    if project.brief_analysis:
        ws_brief = wb.create_sheet("Brief Analysis")
        _build_brief_analysis_sheet(ws_brief, project)

    # Sheet 4: Assumptions & Rates
    ws_rates = wb.create_sheet("Assumptions & Rates")
    _build_assumptions_sheet(ws_rates, roles, lines)

    # Sheet 4: Team Shape (base)
    ws3 = wb.create_sheet("Team Shape")
    await _build_team_shape_sheet(ws3, db, project, efficiency_pct=0)

    # Additional sheets for each efficiency level
    if efficiency_levels:
        for level in efficiency_levels:
            ws_eff = wb.create_sheet(f"Team - {level}% AI Efficiency")
            await _build_team_shape_sheet(ws_eff, db, project, efficiency_pct=level)

    return _workbook_to_bytes(wb)


def _build_ratecard_sheet(
    ws, lines, roles, client_assets, gmals,
    match_caveats: dict | None = None,
    gmal_caveats: dict | None = None,
):
    """Build the main ratecard matrix: rows=roles, cols=client assets."""
    if match_caveats is None:
        match_caveats = {}
    if gmal_caveats is None:
        gmal_caveats = {}
    # Get unique sorted client assets and roles
    asset_ids_ordered = sorted(client_assets.keys())
    role_ids_ordered = sorted(roles.keys(), key=lambda rid: (roles[rid].discipline, roles[rid].sort_order or 0))

    # Build hours lookup: {(role_id, client_asset_id): total_effort}
    # total_hours / manual_override are stored per-1-asset; multiply by volume for display.
    hours_map = {}
    for line in lines:
        per_asset = line.manual_override if line.manual_override is not None else line.total_hours
        volume = line.volume or 1
        hours_map[(line.role_id, line.client_asset_id)] = float(per_asset or 0) * volume

    # Headers
    ws.cell(row=1, column=1, value="Discipline").font = HEADER_FONT
    ws.cell(row=1, column=1).fill = HEADER_FILL
    ws.cell(row=1, column=2, value="Role").font = HEADER_FONT
    ws.cell(row=1, column=2).fill = HEADER_FILL

    for col_idx, asset_id in enumerate(asset_ids_ordered, 3):
        ca = client_assets[asset_id]
        gmal_id = None
        for line in lines:
            if line.client_asset_id == asset_id:
                g = gmals.get(line.gmal_asset_id)
                gmal_id = g.gmal_id if g else None
                break

        header = f"{ca.raw_name}\n(Vol: {ca.volume})"
        if gmal_id:
            header += f"\n[{gmal_id}]"

        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.font = HEADER_FONT
        cell.fill = HEADER_FILL
        cell.alignment = Alignment(wrap_text=True, horizontal="center")

    # Total column
    total_col = len(asset_ids_ordered) + 3
    ws.cell(row=1, column=total_col, value="Total Hours").font = HEADER_FONT
    ws.cell(row=1, column=total_col).fill = HEADER_FILL

    # Metadata rows (rows 2-4): Tier, Match Summary, GMAL Standard Caveats
    TIER_FILL = PatternFill(start_color="EEF2FF", end_color="EEF2FF", fill_type="solid")
    TIER_LABEL_FONT = Font(italic=True, bold=True, size=9, color="3730A3")
    TIER_VALUE_FONT = Font(bold=True, size=10, color="1E1B4B")
    CAVEAT_FONT = Font(italic=True, size=9, color="555555")
    CAVEAT_LABEL_FONT = Font(italic=True, bold=True, size=9, color="92400E")
    MATCH_CAVEAT_FILL = PatternFill(start_color="FFFBF0", end_color="FFFBF0", fill_type="solid")
    GMAL_CAVEAT_FILL = PatternFill(start_color="FEF3F2", end_color="FEF3F2", fill_type="solid")

    # Row 2: Tier (A/B/C)
    ws.cell(row=2, column=1).fill = TIER_FILL
    label2 = ws.cell(row=2, column=2, value="Tier")
    label2.font = TIER_LABEL_FONT
    label2.fill = TIER_FILL
    for col_idx, asset_id in enumerate(asset_ids_ordered, 3):
        ca = client_assets[asset_id]
        tier = (ca.client_tier or "").strip() if getattr(ca, "client_tier", None) else ""
        cell = ws.cell(row=2, column=col_idx, value=tier or "—")
        cell.font = TIER_VALUE_FONT
        cell.fill = TIER_FILL
        cell.alignment = Alignment(horizontal="center", vertical="center")
    ws.cell(row=2, column=total_col).fill = TIER_FILL

    # Row 3: Match Summary (per-match caveat / reasoning)
    ws.cell(row=3, column=1).fill = MATCH_CAVEAT_FILL
    label3 = ws.cell(row=3, column=2, value="Match Summary")
    label3.font = CAVEAT_LABEL_FONT
    label3.fill = MATCH_CAVEAT_FILL
    for col_idx, asset_id in enumerate(asset_ids_ordered, 3):
        cell = ws.cell(row=3, column=col_idx, value=match_caveats.get(asset_id, ""))
        cell.font = CAVEAT_FONT
        cell.fill = MATCH_CAVEAT_FILL
        cell.alignment = Alignment(wrap_text=True, vertical="top")
    ws.cell(row=3, column=total_col).fill = MATCH_CAVEAT_FILL
    ws.row_dimensions[3].height = 60

    # Row 4: GMAL Standard Caveats
    ws.cell(row=4, column=1).fill = GMAL_CAVEAT_FILL
    label4 = ws.cell(row=4, column=2, value="GMAL Standard Caveats")
    label4.font = CAVEAT_LABEL_FONT
    label4.fill = GMAL_CAVEAT_FILL
    for col_idx, asset_id in enumerate(asset_ids_ordered, 3):
        cell = ws.cell(row=4, column=col_idx, value=gmal_caveats.get(asset_id, ""))
        cell.font = CAVEAT_FONT
        cell.fill = GMAL_CAVEAT_FILL
        cell.alignment = Alignment(wrap_text=True, vertical="top")
    ws.cell(row=4, column=total_col).fill = GMAL_CAVEAT_FILL
    ws.row_dimensions[4].height = 60

    # Data rows (start at row 5 — below headers + tier + 2 caveat rows)
    current_discipline = None
    row_idx = 5

    for role_id in role_ids_ordered:
        role = roles[role_id]

        # Check if this role has any hours at all
        role_total = sum(hours_map.get((role_id, aid), 0) for aid in asset_ids_ordered)
        if role_total == 0:
            continue

        # Discipline grouping
        if role.discipline != current_discipline:
            current_discipline = role.discipline
            ws.cell(row=row_idx, column=1, value=current_discipline).font = Font(bold=True)
            ws.cell(row=row_idx, column=1).fill = DISCIPLINE_FILL
            for c in range(1, total_col + 1):
                ws.cell(row=row_idx, column=c).fill = DISCIPLINE_FILL
            row_idx += 1

        ws.cell(row=row_idx, column=1, value=role.discipline)
        ws.cell(row=row_idx, column=2, value=role.role_title)

        for col_idx, asset_id in enumerate(asset_ids_ordered, 3):
            hours = hours_map.get((role_id, asset_id), 0)
            if hours > 0:
                ws.cell(row=row_idx, column=col_idx, value=round(hours, 2))

        # Total Hours = SUM formula across asset columns
        first_col = get_column_letter(3)
        last_col = get_column_letter(total_col - 1)
        total_cell = ws.cell(row=row_idx, column=total_col)
        total_cell.value = f"=SUM({first_col}{row_idx}:{last_col}{row_idx})"
        total_cell.font = Font(bold=True)
        total_cell.number_format = '#,##0.00'
        row_idx += 1

    # Grand total row with SUM formulas down each column
    data_start_row = 5  # first data row (after headers + tier + 2 caveat rows)
    row_idx += 1
    ws.cell(row=row_idx, column=1, value="TOTAL").font = Font(bold=True, size=12)
    for col_idx in range(3, total_col + 1):
        col_letter = get_column_letter(col_idx)
        cell = ws.cell(row=row_idx, column=col_idx)
        cell.value = f"=SUM({col_letter}{data_start_row}:{col_letter}{row_idx - 1})"
        cell.font = Font(bold=True, size=12 if col_idx == total_col else 11)
        cell.number_format = '#,##0.00'

    # Column widths
    ws.column_dimensions["A"].width = 25
    ws.column_dimensions["B"].width = 35
    for col_idx in range(3, total_col + 1):
        ws.column_dimensions[get_column_letter(col_idx)].width = 18


async def _build_asset_detail_sheet(ws, db, project, client_assets, gmals):
    """Build the asset detail sheet showing matches and caveats."""
    headers = ["Client Asset", "Volume", "Matched GMAL", "GMAL Name", "Confidence", "Score", "Match Caveats", "GMAL Standard Caveats"]
    for col_idx, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.font = HEADER_FONT
        cell.fill = HEADER_FILL

    # Load matches
    from app.models.project import Match
    matches_result = await db.execute(
        select(Match).where(
            Match.client_asset_id.in_(list(client_assets.keys())),
            Match.is_selected == True,
        )
    )
    matches = matches_result.scalars().all()
    match_by_asset = {m.client_asset_id: m for m in matches}

    row_idx = 2
    for asset_id in sorted(client_assets.keys()):
        ca = client_assets[asset_id]
        match = match_by_asset.get(asset_id)

        ws.cell(row=row_idx, column=1, value=ca.raw_name)
        ws.cell(row=row_idx, column=2, value=ca.volume)

        if match:
            gmal = gmals.get(match.gmal_asset_id)
            ws.cell(row=row_idx, column=3, value=gmal.gmal_id if gmal else "")
            ws.cell(row=row_idx, column=4, value=gmal.unique_name if gmal else "")
            ws.cell(row=row_idx, column=5, value=match.confidence.value)
            ws.cell(row=row_idx, column=6, value=float(match.confidence_score) if match.confidence_score else 0)
            ws.cell(row=row_idx, column=7, value=match.caveat_text or "")
            ws.cell(row=row_idx, column=7).alignment = Alignment(wrap_text=True, vertical="top")
            gmal_caveats = (gmal.caveats or "") if gmal else ""
            ws.cell(row=row_idx, column=8, value=gmal_caveats)
            ws.cell(row=row_idx, column=8).alignment = Alignment(wrap_text=True, vertical="top")
        else:
            ws.cell(row=row_idx, column=3, value="No match")

        row_idx += 1

    # Column widths
    widths = [30, 10, 15, 40, 12, 10, 60, 60]
    for i, w in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(i)].width = w


BRIEF_FILL = PatternFill(start_color="1565C0", end_color="1565C0", fill_type="solid")
PRIORITY_COLORS = {
    "red": Font(bold=True, color="D32F2F"),
    "amber": Font(bold=True, color="F57F17"),
    "green": Font(bold=True, color="2E7D32"),
}


def _build_brief_analysis_sheet(ws, project):
    """Build the brief analysis sheet from stored JSON."""
    import json
    try:
        analysis = json.loads(project.brief_analysis)
    except (json.JSONDecodeError, TypeError):
        ws["A1"] = "No brief analysis available"
        return

    ws.cell(row=1, column=1, value=f"Brief Analysis - {project.name}").font = Font(bold=True, size=14)

    row = 3
    if analysis.get("summary"):
        ws.cell(row=row, column=1, value="SUMMARY").font = Font(bold=True)
        ws.cell(row=row, column=1).fill = BRIEF_FILL
        ws.cell(row=row, column=1).font = HEADER_FONT
        row += 1
        ws.cell(row=row, column=1, value=analysis["summary"]).alignment = Alignment(wrap_text=True)
        row += 2

    for section, label in [("objectives", "OBJECTIVES"), ("channels", "CHANNELS"),
                            ("deliverable_categories", "DELIVERABLE CATEGORIES"),
                            ("audiences", "AUDIENCES"), ("constraints", "CONSTRAINTS")]:
        items = analysis.get(section, [])
        if items:
            ws.cell(row=row, column=1, value=label).font = Font(bold=True)
            row += 1
            for item in items:
                ws.cell(row=row, column=1, value=f"• {item}")
                row += 1
            row += 1

    if analysis.get("complexity_assessment"):
        ws.cell(row=row, column=1, value=f"Complexity: {analysis['complexity_assessment'].upper()}").font = Font(bold=True)
        row += 2

    # Discovery questions
    questions = analysis.get("missing_info", [])
    if questions:
        ws.cell(row=row, column=1, value="DISCOVERY QUESTIONS").font = Font(bold=True, size=12)
        row += 1
        headers = ["Priority", "Category", "Question", "Rationale"]
        for col, h in enumerate(headers, 1):
            ws.cell(row=row, column=col, value=h).font = HEADER_FONT
            ws.cell(row=row, column=col).fill = BRIEF_FILL
        row += 1

        for q in questions:
            priority = q.get("priority", "")
            ws.cell(row=row, column=1, value=priority.upper())
            if priority in PRIORITY_COLORS:
                ws.cell(row=row, column=1).font = PRIORITY_COLORS[priority]
            ws.cell(row=row, column=2, value=q.get("category", ""))
            ws.cell(row=row, column=3, value=q.get("question", "")).alignment = Alignment(wrap_text=True)
            ws.cell(row=row, column=4, value=q.get("rationale", "")).alignment = Alignment(wrap_text=True)
            row += 1

    ws.column_dimensions["A"].width = 15
    ws.column_dimensions["B"].width = 20
    ws.column_dimensions["C"].width = 60
    ws.column_dimensions["D"].width = 40


ASSUMPTIONS_FILL = PatternFill(start_color="4A148C", end_color="4A148C", fill_type="solid")
INPUT_FILL = PatternFill(start_color="FFF9C4", end_color="FFF9C4", fill_type="solid")


def _build_assumptions_sheet(ws, roles, lines):
    """Build an editable Assumptions & Rates sheet that other sheets can reference."""
    ws.cell(row=1, column=1, value="Assumptions & Rates").font = Font(bold=True, size=14)
    ws.cell(row=2, column=1, value="Edit the yellow cells to adjust the financial model").font = Font(italic=True, color="666666")

    # Global assumptions
    ws.cell(row=4, column=1, value="GLOBAL ASSUMPTIONS").font = Font(bold=True, size=12)
    assumptions = [
        ("Hours per FTE per year", 1800),
        ("Margin %", 15),
        ("Overhead %", 10),
    ]
    for i, (label, value) in enumerate(assumptions):
        row = 5 + i
        ws.cell(row=row, column=1, value=label).font = Font(bold=True)
        cell = ws.cell(row=row, column=2, value=value)
        cell.fill = INPUT_FILL
        cell.number_format = '#,##0' if isinstance(value, int) else '0.0%'

    # Role rates table
    ws.cell(row=10, column=1, value="ROLE RATES").font = Font(bold=True, size=12)
    headers = ["Discipline", "Role", "Day Rate (£)", "Annual Salary (£)"]
    for col_idx, header in enumerate(headers, 1):
        cell = ws.cell(row=11, column=col_idx, value=header)
        cell.font = HEADER_FONT
        cell.fill = ASSUMPTIONS_FILL

    # Get unique roles that appear in the ratecard
    role_ids_used = set(l.role_id for l in lines)
    role_list = sorted(
        [r for r in roles.values() if r.id in role_ids_used],
        key=lambda r: (r.discipline, r.sort_order or 0)
    )

    row_idx = 12
    for role in role_list:
        ws.cell(row=row_idx, column=1, value=role.discipline)
        ws.cell(row=row_idx, column=2, value=role.role_title)
        # Day rate - editable (yellow)
        day_rate_cell = ws.cell(row=row_idx, column=3, value=0)
        day_rate_cell.fill = INPUT_FILL
        day_rate_cell.number_format = '£#,##0'
        # Annual salary - editable (yellow)
        salary_cell = ws.cell(row=row_idx, column=4, value=0)
        salary_cell.fill = INPUT_FILL
        salary_cell.number_format = '£#,##0'
        row_idx += 1

    ws.cell(row=row_idx + 1, column=1, value="Fill in day rates and annual salaries above.").font = Font(italic=True, color="666666")
    ws.cell(row=row_idx + 2, column=1, value="These values will be used in future formula-linked exports.").font = Font(italic=True, color="666666")

    ws.column_dimensions["A"].width = 30
    ws.column_dimensions["B"].width = 40
    ws.column_dimensions["C"].width = 18
    ws.column_dimensions["D"].width = 20


TEAM_HEADER_FILL = PatternFill(start_color="2E7D32", end_color="2E7D32", fill_type="solid")
PROGRAMME_FILL = PatternFill(start_color="FFF3E0", end_color="FFF3E0", fill_type="solid")
FTE_FONT = Font(bold=True, size=11, color="1B5E20")


async def _build_team_shape_sheet(ws, db, project, efficiency_pct: float = 0):
    """Build the team shape sheet: FTE per role from ratecard hours / 1800.

    If efficiency_pct > 0, shows original + adjusted columns.
    """
    import math

    team = await calculate_team_shape(db, project, efficiency_pct=efficiency_pct)

    if not team:
        ws["A1"] = "No ratecard data - build ratecard first"
        return

    has_efficiency = efficiency_pct > 0
    total_hours = sum(t["total_hours"] for t in team)
    total_fte = sum(t["fte"] for t in team)
    adjusted_hours = sum(t["adjusted_hours"] for t in team)
    adjusted_fte = sum(t["adjusted_fte"] for t in team)

    # Title
    title = f"Team Shape - {project.name}"
    if has_efficiency:
        title += f" ({efficiency_pct}% AI/Automation Efficiency)"
    ws.merge_cells("A1:I1")
    ws.cell(row=1, column=1, value=title).font = Font(bold=True, size=14)

    subtitle = f"Based on {total_hours:,.0f} total hours / 1,800 hours per FTE = {total_fte:.2f} FTE"
    if has_efficiency:
        subtitle += f" → {adjusted_hours:,.0f} adjusted hours = {adjusted_fte:.2f} FTE ({efficiency_pct}% efficiency on delivery roles)"
    ws.cell(row=2, column=1, value=subtitle).font = Font(italic=True, color="666666")

    # Headers
    if has_efficiency:
        headers = ["Discipline", "Role", "Type", "Original Hours", "Original FTE",
                    "Adjusted Hours", "Adjusted FTE", "Hours Saved", "Headcount"]
    else:
        headers = ["Discipline", "Role", "Type", "Total Hours", "FTE", "Headcount"]

    num_cols = len(headers)
    for col_idx, header in enumerate(headers, 1):
        cell = ws.cell(row=4, column=col_idx, value=header)
        cell.font = HEADER_FONT
        cell.fill = TEAM_HEADER_FILL

    # Data rows
    row_idx = 5
    current_discipline = None
    SAVED_FONT = Font(color="2E7D32", italic=True)

    for t in team:
        if t["discipline"] != current_discipline:
            current_discipline = t["discipline"]
            ws.cell(row=row_idx, column=1, value=current_discipline).font = Font(bold=True)
            for c in range(1, num_cols + 1):
                ws.cell(row=row_idx, column=c).fill = DISCIPLINE_FILL
            row_idx += 1

        role_type = "Programme" if t["is_programme_role"] else "Delivery"
        ws.cell(row=row_idx, column=1, value=t["discipline"])
        ws.cell(row=row_idx, column=2, value=t["role_title"])
        ws.cell(row=row_idx, column=3, value=role_type)

        if has_efficiency:
            # Col D: Original Hours (value)
            ws.cell(row=row_idx, column=4, value=t["total_hours"]).number_format = '#,##0.00'
            # Col E: Original FTE = D/1800 (formula)
            ws.cell(row=row_idx, column=5).value = f"=D{row_idx}/1800"
            ws.cell(row=row_idx, column=5).number_format = '0.00'
            # Col F: Adjusted Hours = D * (1 - eff%) (formula)
            eff_pct = t.get("efficiency_pct", efficiency_pct) or 0
            ws.cell(row=row_idx, column=6).value = f"=D{row_idx}*(1-{eff_pct/100})"
            ws.cell(row=row_idx, column=6).number_format = '#,##0.00'
            # Col G: Adjusted FTE = F/1800 (formula)
            adj_cell = ws.cell(row=row_idx, column=7)
            adj_cell.value = f"=F{row_idx}/1800"
            adj_cell.number_format = '0.00'
            # Col H: Hours Saved = D - F (formula)
            saved = ws.cell(row=row_idx, column=8)
            saved.value = f"=D{row_idx}-F{row_idx}"
            saved.number_format = '#,##0.00'
            saved.font = SAVED_FONT
            # Col I: Headcount = CEILING or 0.5 (formula)
            ws.cell(row=row_idx, column=9).value = f'=IF(G{row_idx}>=0.5,CEILING(G{row_idx},1),IF(G{row_idx}>0,0.5,0))'
            ws.cell(row=row_idx, column=9).number_format = '0.0'
        else:
            # Col D: Total Hours (value)
            ws.cell(row=row_idx, column=4, value=t["total_hours"]).number_format = '#,##0.00'
            # Col E: FTE = D/1800 (formula)
            ws.cell(row=row_idx, column=5).value = f"=D{row_idx}/1800"
            ws.cell(row=row_idx, column=5).number_format = '0.00'
            # Col F: Headcount (formula)
            ws.cell(row=row_idx, column=6).value = f'=IF(E{row_idx}>=0.5,CEILING(E{row_idx},1),IF(E{row_idx}>0,0.5,0))'
            ws.cell(row=row_idx, column=6).number_format = '0.0'

        if t["is_programme_role"]:
            for c in range(1, num_cols + 1):
                ws.cell(row=row_idx, column=c).fill = PROGRAMME_FILL

        row_idx += 1

    # Summary
    row_idx += 1
    ws.cell(row=row_idx, column=1, value="SUMMARY").font = Font(bold=True, size=12)
    row_idx += 1

    delivery_hours = sum(t["total_hours"] for t in team if not t["is_programme_role"])
    delivery_fte = sum(t["fte"] for t in team if not t["is_programme_role"])
    prog_hours = sum(t["total_hours"] for t in team if t["is_programme_role"])
    prog_fte = sum(t["fte"] for t in team if t["is_programme_role"])

    if has_efficiency:
        adj_del_hours = sum(t["adjusted_hours"] for t in team if not t["is_programme_role"])
        adj_del_fte = sum(t["adjusted_fte"] for t in team if not t["is_programme_role"])

        ws.cell(row=row_idx, column=3, value="Orig Hours").font = Font(bold=True)
        ws.cell(row=row_idx, column=4, value="Orig FTE").font = Font(bold=True)
        ws.cell(row=row_idx, column=5, value="Adj Hours").font = Font(bold=True)
        ws.cell(row=row_idx, column=6, value="Adj FTE").font = Font(bold=True)
        ws.cell(row=row_idx, column=7, value="Saved").font = Font(bold=True)
        row_idx += 1

        summary_rows = [
            ("Delivery Roles", delivery_hours, delivery_fte, adj_del_hours, adj_del_fte),
            ("Programme Roles (unchanged)", prog_hours, prog_fte, prog_hours, prog_fte),
            ("TOTAL", total_hours, total_fte, adjusted_hours, adjusted_fte),
        ]
        for label, hrs, fte, adj_hrs, adj_fte in summary_rows:
            is_total = label == "TOTAL"
            ws.cell(row=row_idx, column=2, value=label).font = Font(bold=is_total)
            ws.cell(row=row_idx, column=3, value=round(hrs, 2)).number_format = '#,##0.00'
            ws.cell(row=row_idx, column=4, value=round(fte, 2)).number_format = '0.00'
            ws.cell(row=row_idx, column=5, value=round(adj_hrs, 2)).number_format = '#,##0.00'
            adj_cell = ws.cell(row=row_idx, column=6, value=round(adj_fte, 2))
            adj_cell.number_format = '0.00'
            if is_total:
                adj_cell.font = Font(bold=True, size=12)
            saved = ws.cell(row=row_idx, column=7, value=round(hrs - adj_hrs, 2))
            saved.number_format = '#,##0.00'
            if hrs - adj_hrs > 0:
                saved.font = Font(color="2E7D32", italic=True)
            row_idx += 1
    else:
        summary_data = [
            ("Delivery Roles", delivery_hours, delivery_fte),
            ("Programme Roles", prog_hours, prog_fte),
            ("TOTAL", total_hours, total_fte),
        ]
        ws.cell(row=row_idx, column=3, value="Hours").font = Font(bold=True)
        ws.cell(row=row_idx, column=4, value="FTE").font = Font(bold=True)
        row_idx += 1

        for label, hours, fte in summary_data:
            ws.cell(row=row_idx, column=2, value=label).font = Font(bold=(label == "TOTAL"))
            ws.cell(row=row_idx, column=3, value=round(hours, 2)).number_format = '#,##0.00'
            fte_cell = ws.cell(row=row_idx, column=4, value=round(fte, 2))
            fte_cell.number_format = '0.00'
            if label == "TOTAL":
                fte_cell.font = Font(bold=True, size=12)
            row_idx += 1

    # Column widths
    ws.column_dimensions["A"].width = 25
    ws.column_dimensions["B"].width = 40
    ws.column_dimensions["C"].width = 12
    ws.column_dimensions["D"].width = 15
    ws.column_dimensions["E"].width = 15
    ws.column_dimensions["F"].width = 15
    ws.column_dimensions["G"].width = 15
    ws.column_dimensions["H"].width = 15
    ws.column_dimensions["I"].width = 15


def _workbook_to_bytes(wb: Workbook) -> bytes:
    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)
    return buf.read()