#!/usr/bin/env python3
"""
builders/build_excel.py -- Enhanced Excel workbook builder.
Reads the skill's original builder and adds DuPont, SWOT, and Indian unit support.
"""
import json, sys, datetime
from pathlib import Path

try:
    from openpyxl import Workbook
    from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
    from openpyxl.utils import get_column_letter
    from openpyxl.chart import BarChart, LineChart, Reference
except ImportError:
    print("ERROR: pip install openpyxl", file=sys.stderr); sys.exit(1)

# -- Style constants -----------------------------------------------------------
HDR_FILL  = PatternFill("solid", fgColor="1F4E78")
HDR_FONT  = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
SEC_FILL  = PatternFill("solid", fgColor="D9E1F2")
SEC_FONT  = Font(name="Calibri", size=11, bold=True, color="1F4E78")
LBL_FONT  = Font(name="Calibri", size=11)
TTL_FONT  = Font(name="Calibri", size=16, bold=True, color="1F4E78")
DAT_FONT  = Font(name="Calibri", size=11)
GRN_FILL  = PatternFill("solid", fgColor="C6EFCE")
RED_FILL  = PatternFill("solid", fgColor="FFC7CE")
YLW_FILL  = PatternFill("solid", fgColor="FFEB9C")

THIN = Border(
    left=Side(style="thin", color="BFBFBF"), right=Side(style="thin", color="BFBFBF"),
    top=Side(style="thin",  color="BFBFBF"), bottom=Side(style="thin", color="BFBFBF"),
)
CENTER = Alignment(horizontal="center", vertical="center", wrap_text=True)
LEFT   = Alignment(horizontal="left",   vertical="center", wrap_text=True)
RIGHT  = Alignment(horizontal="right",  vertical="center")

FMT_MONEY = '#,##0;(#,##0);"--"'
FMT_DEC   = '#,##0.00;(#,##0.00);"--"'
FMT_PCT   = '0.0%;(0.0%);"--"'
FMT_RATIO = '0.00;(0.00);"--"'
FMT_DAYS  = '0.0;"--"'

# -- Helpers -------------------------------------------------------------------

def _safe(arr, i, default=None):
    try: return arr[i] if arr and arr[i] is not None else default
    except: return default

def _safe_div(num, den, default=None):
    try:
        if num is None or den is None: return default
        if den == 0: return default
        return num / den
    except: return default

def _hdr(ws, row, cols, start=1):
    for i, h in enumerate(cols):
        c = ws.cell(row=row, column=start+i, value=h)
        c.font, c.fill, c.alignment, c.border = HDR_FONT, HDR_FILL, CENTER, THIN

def _sec(ws, row, label, ncols, start=1):
    ws.cell(row=row, column=start, value=label).font = SEC_FONT
    ws.cell(row=row, column=start).fill = SEC_FILL
    ws.cell(row=row, column=start).alignment = LEFT
    for col in range(start+1, start+ncols):
        ws.cell(row=row, column=col).fill = SEC_FILL

def _data_row(ws, row, label, values, fmt=FMT_MONEY, start=1):
    c = ws.cell(row=row, column=start, value=label)
    c.font, c.alignment, c.border = LBL_FONT, LEFT, THIN
    for i, v in enumerate(values):
        cell = ws.cell(row=row, column=start+1+i, value=v if v is not None else "--")
        cell.font, cell.alignment, cell.border = DAT_FONT, RIGHT, THIN
        if v is not None and isinstance(v, (int, float)):
            cell.number_format = fmt

def _title(ws, title, subtitle=None):
    ws.cell(row=1, column=1, value=title).font = TTL_FONT
    if subtitle:
        ws.cell(row=2, column=1, value=subtitle).font = Font(name="Calibri", size=11, italic=True, color="595959")

def _widths(ws, widths):
    for i, w in enumerate(widths):
        ws.column_dimensions[get_column_letter(i+1)].width = w

def _freeze(ws, hdr_row):
    ws.freeze_panes = ws.cell(row=hdr_row+1, column=2)

def _last(arr):
    if not arr: return None
    for v in reversed(arr):
        if v is not None: return v
    return None

# -- Statement section helper --------------------------------------------------

def _stmt_section(ws, row, title, items, data_dict, years, fmt=FMT_MONEY):
    _sec(ws, row, title, 1+len(years))
    row += 1
    for label, key in items:
        vals = data_dict.get(key, [None]*len(years))
        vals = (vals + [None]*len(years))[:len(years)]
        _data_row(ws, row, label, vals, fmt)
        row += 1
    return row

# -- Sheet builders ------------------------------------------------------------

def _summary_sheet(wb, data):
    ws = wb.create_sheet("Summary")
    co = data.get("company", {})
    units_label = data.get("units_label", data.get("units", "Crores"))
    currency    = data.get("currency", co.get("currency", "INR"))
    _title(ws, f"{co.get('name','')} ({co.get('ticker','')}) -- Financial Analysis",
           f"Report date: {co.get('report_date', datetime.date.today().isoformat())}")
    r = 4
    for label, val in [
        ("Company",         co.get("name","")),
        ("Ticker",          co.get("ticker","")),
        ("Exchange",        co.get("exchange","")),
        ("Industry",        co.get("industry","")),
        ("Sector",          co.get("sector","")),
        ("Currency",        currency),
        ("Reporting Units", units_label),
        ("Fiscal Year End", co.get("fiscal_year_end","")),
        ("Analysis Period", f"{len(data.get('years',[]))} years ({', '.join(data.get('years',[]))})"),
        ("Share Price",     co.get("share_price")),
        ("Market Cap",      co.get("market_cap")),
        ("Data Source",     data.get("source", "Yahoo Finance (yahooquery)")),
        ("Models Used",     ", ".join(data.get("models_used", []) or ["None"])),
        ("Warnings",        " | ".join(data.get("warnings", []))),
    ]:
        ws.cell(row=r, column=1, value=label).font = LBL_FONT
        ws.cell(row=r, column=2, value=val).font   = DAT_FONT
        r += 1
    _widths(ws, [28, 50])


def _income_sheet(wb, data):
    ws = wb.create_sheet("Income Statement")
    co, years = data.get("company",{}), data.get("years",[])
    units_label = data.get("units_label", data.get("units", "Crores"))
    currency    = data.get("currency", co.get("currency", "INR"))
    _title(ws, f"Income Statement -- {co.get('ticker','')}",
           f"All figures in {units_label}")
    _hdr(ws, 4, [f"({units_label})"] + years)
    inc = data.get("income_statement", {})
    r = _stmt_section(ws, 5, "Revenue & Profitability", [
        ("Revenue",              "revenue"),
        ("Cost of Revenue",      "cost_of_revenue"),
        ("Gross Profit",         "gross_profit"),
        ("Operating Expenses",   "operating_expenses"),
        ("Operating Income (EBIT)","operating_income"),
        ("D&A",                  "depreciation_amort"),
        ("EBITDA",               "ebitda"),
        ("Interest Expense",     "interest_expense"),
        ("Pretax Income",        "pretax_income"),
        ("Tax Expense",          "tax_expense"),
        ("Net Income",           "net_income"),
    ], inc, years)
    r += 1
    _sec(ws, r, "Per-Share Data", 1+len(years)); r += 1
    _data_row(ws, r, "Diluted EPS",    inc.get("diluted_eps",[None]*len(years))[:len(years)], FMT_DEC); r += 1
    _data_row(ws, r, "Diluted Shares (M)", inc.get("diluted_shares",[None]*len(years))[:len(years)], FMT_MONEY)

    # Add revenue bar chart
    if len(years) >= 2:
        chart = BarChart()
        chart.title = f"Revenue Trend ({units_label})"
        chart.style = 10
        chart.y_axis.title = units_label
        rev_row = 6
        data_ref = Reference(ws, min_col=2, max_col=1+len(years), min_row=rev_row, max_row=rev_row)
        cats_ref = Reference(ws, min_col=2, max_col=1+len(years), min_row=4, max_row=4)
        chart.add_data(data_ref)
        chart.set_categories(cats_ref)
        chart.shape = 4
        ws.add_chart(chart, f"A{r+3}")

    _widths(ws, [34]+[16]*len(years)); _freeze(ws, 4)


def _balance_sheet(wb, data):
    ws = wb.create_sheet("Balance Sheet")
    co, years = data.get("company",{}), data.get("years",[])
    units_label = data.get("units_label", data.get("units", "Crores"))
    _title(ws, f"Balance Sheet -- {co.get('ticker','')}",
           f"As of fiscal year-end. {units_label}")
    _hdr(ws, 4, [f"({units_label})"] + years)
    bs = data.get("balance_sheet", {})
    r = 5
    for sec_title, items in [
        ("Current Assets", [
            ("Cash & Equivalents","cash_and_equivalents"),("Short-term Investments","short_term_investments"),
            ("Receivables","receivables"),("Inventory","inventory"),("Other Current Assets","other_current_assets"),
            ("Total Current Assets","total_current_assets"),
        ]),
        ("Non-Current Assets", [
            ("PP&E (Net)","ppe_net"),("Goodwill","goodwill"),("Intangibles","intangibles"),
            ("Other LT Assets","other_long_term_assets"),("Total Assets","total_assets"),
        ]),
        ("Current Liabilities", [
            ("Accounts Payable","accounts_payable"),("Short-term Debt","short_term_debt"),
            ("Other Current Liabilities","other_current_liabilities"),
            ("Total Current Liabilities","total_current_liabilities"),
        ]),
        ("Non-Current Liabilities & Equity", [
            ("Long-term Debt","long_term_debt"),("Other LT Liabilities","other_long_term_liabilities"),
            ("Total Liabilities","total_liabilities"),("Total Equity","total_equity"),
            ("Total Liabilities + Equity","total_liab_and_equity"),
        ]),
    ]:
        r = _stmt_section(ws, r, sec_title, items, bs, years)
    _widths(ws, [36]+[16]*len(years)); _freeze(ws, 4)


def _cashflow_sheet(wb, data):
    ws = wb.create_sheet("Cash Flow")
    co, years = data.get("company",{}), data.get("years",[])
    units_label = data.get("units_label", data.get("units", "Crores"))
    _title(ws, f"Cash Flow Statement -- {co.get('ticker','')}",
           f"All figures in {units_label}")
    _hdr(ws, 4, [f"({units_label})"] + years)
    cf = data.get("cash_flow", {})
    r = 5
    for sec_title, items in [
        ("Operating Activities", [
            ("Net Income","net_income"),("D&A","depreciation_amort"),("Stock-Based Comp","stock_based_comp"),
            ("Working Capital Changes","working_capital_changes"),("Cash from Operations","cash_from_operations"),
        ]),
        ("Investing Activities", [
            ("Capital Expenditures","capex"),("Acquisitions","acquisitions"),("Cash from Investing","cash_from_investing"),
        ]),
        ("Financing Activities", [
            ("Debt Issuance/(Repayment)","debt_issuance_repayment"),("Buybacks","stock_buybacks"),
            ("Dividends Paid","dividends_paid"),("Cash from Financing","cash_from_financing"),
        ]),
        ("Summary", [
            ("Net Change in Cash","net_change_in_cash"),("Free Cash Flow","free_cash_flow"),
        ]),
    ]:
        r = _stmt_section(ws, r, sec_title, items, cf, years)
    _widths(ws, [36]+[16]*len(years)); _freeze(ws, 4)


RATIO_SECTIONS = [
    ("Profitability", [
        ("gross_margin","Gross Margin",FMT_PCT),("operating_margin","Operating Margin",FMT_PCT),
        ("ebitda_margin","EBITDA Margin",FMT_PCT),("net_margin","Net Margin",FMT_PCT),
        ("roe","ROE",FMT_PCT),("roa","ROA",FMT_PCT),("roic","ROIC",FMT_PCT),
    ]),
    ("Liquidity", [
        ("current_ratio","Current Ratio",FMT_RATIO),("quick_ratio","Quick Ratio",FMT_RATIO),
        ("cash_ratio","Cash Ratio",FMT_RATIO),
    ]),
    ("Solvency / Leverage", [
        ("debt_to_equity","Debt-to-Equity",FMT_RATIO),("debt_to_assets","Debt-to-Assets",FMT_RATIO),
        ("net_debt_to_ebitda","Net Debt/EBITDA",FMT_RATIO),("interest_coverage","Interest Coverage",FMT_RATIO),
    ]),
    ("Efficiency", [
        ("asset_turnover","Asset Turnover",FMT_RATIO),("inventory_turnover","Inventory Turnover",FMT_RATIO),
        ("days_inventory","Days Inventory (DIO)",FMT_DAYS),("days_sales_outstanding","DSO",FMT_DAYS),
        ("days_payables","DPO",FMT_DAYS),("cash_conversion_cycle","Cash Conversion Cycle",FMT_DAYS),
    ]),
    ("Cash Flow Quality", [
        ("ocf_to_net_income","OCF/Net Income",FMT_RATIO),("fcf_margin","FCF Margin",FMT_PCT),
        ("fcf_conversion","FCF Conversion",FMT_RATIO),("capex_intensity","Capex/Revenue",FMT_PCT),
    ]),
]


def _ratios_sheet(wb, data):
    ws = wb.create_sheet("Ratios")
    years = data.get("years", [])
    _title(ws, f"Financial Ratios -- {data.get('company',{}).get('ticker','')}", "5-year trend")
    _hdr(ws, 4, ["Ratio"] + years)
    ratios = data.get("ratios", {})
    r = 5
    for sec_name, items in RATIO_SECTIONS:
        _sec(ws, r, sec_name, 1+len(years)); r += 1
        for key, label, fmt in items:
            vals = ratios.get(key, [None]*len(years))
            vals = (vals + [None]*len(years))[:len(years)]
            _data_row(ws, r, label, vals, fmt); r += 1
    _widths(ws, [40]+[14]*len(years)); _freeze(ws, 4)


def _dupont_sheet(wb, data):
    ws = wb.create_sheet("DuPont Analysis")
    years = data.get("years", [])
    _title(ws, "DuPont Decomposition", "ROE = Net Margin × Asset Turnover × Equity Multiplier")
    _hdr(ws, 4, ["Component"] + years)
    dp = data.get("dupont", {})
    r = 5
    for label, key, fmt in [
        ("Net Margin",       "net_margin",        FMT_PCT),
        ("× Asset Turnover", "asset_turnover",     FMT_RATIO),
        ("× Equity Multiplier","equity_multiplier",FMT_RATIO),
        ("= ROE (DuPont)",   "roe_computed",       FMT_PCT),
        ("ROE (Reported)",   "roe_reported",       FMT_PCT),
    ]:
        vals = dp.get(key, [None]*len(years))
        vals = (vals + [None]*len(years))[:len(years)]
        _data_row(ws, r, label, vals, fmt)
        r += 1
    _widths(ws, [28]+[14]*len(years))


def _growth_sheet(wb, data):
    ws = wb.create_sheet("Growth Metrics")
    years = data.get("years", [])
    yoy_hdrs = [f"{years[i]} vs {years[i-1]}" for i in range(1, len(years))] if len(years) > 1 else []
    _title(ws, "Growth Metrics", "YoY growth and CAGRs")
    _hdr(ws, 4, ["Metric"] + yoy_hdrs + ["CAGR"])
    g = data.get("growth_metrics", {})
    r = 5
    for label, yoy_key, cagr_key in [
        ("Revenue",        "revenue_yoy",        "revenue_cagr_5y"),
        ("Operating Income","operating_income_yoy","operating_income_cagr_5y"),
        ("Net Income",     "net_income_yoy",      "net_income_cagr_5y"),
        ("Diluted EPS",    "eps_yoy",             "eps_cagr_5y"),
        ("Free Cash Flow", "fcf_yoy",             "fcf_cagr_5y"),
    ]:
        yoy = g.get(yoy_key, [])[1:]  # skip first None
        cagr = g.get(cagr_key)
        vals = (yoy + [None]*len(yoy_hdrs))[:len(yoy_hdrs)] + [cagr]
        _data_row(ws, r, label, vals, FMT_PCT); r += 1
    _widths(ws, [28]+[16]*(len(yoy_hdrs)+1))


def _peers_sheet(wb, data):
    ws = wb.create_sheet("Peer Comparison")
    co = data.get("company", {})
    _title(ws, f"Peer Comparison -- {co.get('ticker','')}", "Latest fiscal year")
    cols = ["Company","Ticker","Revenue","Gross Margin","Op Margin","Net Margin",
            "ROE","ROIC","D/E","Current Ratio","FCF Margin","P/E","EV/EBITDA","Rationale"]
    _hdr(ws, 4, cols)
    r = 5
    years = data.get("years", [])
    rat = data.get("ratios", {}); inc = data.get("income_statement", {})
    val = data.get("valuation_snapshot", {})
    # Target row
    target = [
        co.get("name",""), co.get("ticker",""),
        _last(inc.get("revenue",[])), _last(rat.get("gross_margin",[])),
        _last(rat.get("operating_margin",[])), _last(rat.get("net_margin",[])),
        _last(rat.get("roe",[])), _last(rat.get("roic",[])),
        _last(rat.get("debt_to_equity",[])), _last(rat.get("current_ratio",[])),
        _last(rat.get("fcf_margin",[])), val.get("pe_ttm"), val.get("ev_ebitda"),
        "Target company",
    ]
    fmts = ["","","money","pct","pct","pct","pct","pct","ratio","ratio","pct","ratio","ratio",""]
    _write_peer_row(ws, r, target, fmts, is_target=True); r += 1
    for p in data.get("peers", []):
        peer_row = [
            p.get("name",""), p.get("ticker",""), p.get("revenue"),
            p.get("gross_margin"), p.get("operating_margin"), p.get("net_margin"),
            p.get("roe"), p.get("roic"), p.get("debt_to_equity"), p.get("current_ratio"),
            p.get("fcf_margin"), p.get("pe_ttm"), p.get("ev_ebitda"), p.get("rationale",""),
        ]
        _write_peer_row(ws, r, peer_row, fmts); r += 1
    _widths(ws, [24,10,16,12,12,12,10,10,10,12,12,10,12,50])


def _write_peer_row(ws, row, values, fmts, is_target=False):
    fmt_map = {"money":FMT_MONEY,"pct":FMT_PCT,"ratio":FMT_RATIO}
    fill = PatternFill("solid", fgColor="FFF2CC") if is_target else None
    for i, v in enumerate(values):
        c = ws.cell(row=row, column=i+1, value=v if v is not None else "--")
        c.border = THIN
        c.font = Font(name="Calibri", size=11, bold=is_target)
        if fill: c.fill = fill
        fmt = fmts[i] if i < len(fmts) else ""
        if fmt in fmt_map and isinstance(v, (int, float)):
            c.number_format = fmt_map[fmt]
            c.alignment = RIGHT
        else:
            c.alignment = LEFT


def _swot_sheet(wb, data):
    ws = wb.create_sheet("SWOT Analysis")
    _title(ws, "SWOT Analysis", "Strengths, Weaknesses, Opportunities, Threats")
    swot = data.get("swot", {})
    r = 4
    colors = {"Strengths":"C6EFCE","Weaknesses":"FFC7CE","Opportunities":"DDEBF7","Threats":"FFEB9C"}
    for category, key in [("Strengths","strengths"),("Weaknesses","weaknesses"),
                           ("Opportunities","opportunities"),("Threats","threats")]:
        fill = PatternFill("solid", fgColor=colors[category])
        hdr_cell = ws.cell(row=r, column=1, value=category)
        hdr_cell.font = Font(name="Calibri", size=12, bold=True)
        hdr_cell.fill = fill
        r += 1
        for item in swot.get(key, []):
            c = ws.cell(row=r, column=1, value=f"• {item}")
            c.font = Font(name="Calibri", size=11)
            c.alignment = Alignment(horizontal="left", vertical="top", wrap_text=True)
            ws.row_dimensions[r].height = 32
            r += 1
        r += 1
    _widths(ws, [90])


def _dcf_sheet(wb, data):
    ws = wb.create_sheet("DCF Valuation Model")
    _title(ws, "Interactive Discounted Cash Flow (DCF)", "Change the yellow highlighted cells to update assumptions")
    
    currency = data.get("currency", "USD")
    units = data.get("company", {}).get("units", "Millions")
    
    # 1. Historical data
    rev_hist = data.get("income_statement", {}).get("revenue", [])
    fcf_hist = data.get("cash_flow", {}).get("free_cash_flow", [])
    
    last_rev = _last(rev_hist) or 0
    last_fcf = _last(fcf_hist) or 0
    
    # Default assumptions
    default_wacc = 0.09
    default_tg = 0.025
    
    # Historical FCF Margin & Growth
    rev_cagr = data.get("growth_metrics", {}).get("revenue_cagr_5y")
    if rev_cagr is None:
        rev_cagr = 0.05
    rev_cagr = max(0.02, min(0.15, rev_cagr)) # Cap between 2% and 15%
    
    fcf_margins = [_safe_div(f, r) for f, r in zip(fcf_hist, rev_hist) if f is not None and r is not None and r != 0]
    avg_fcf_margin = sum(fcf_margins[-3:]) / len(fcf_margins[-3:]) if len(fcf_margins) > 0 else 0.10
    
    shares_out = _last(data.get("income_statement", {}).get("diluted_shares", [])) or 1
    if shares_out <= 0: shares_out = 1 # Prevent division by zero
    net_debt = _last(data.get("net_debt", [])) or 0
    
    # --- Assumptions Block ---
    r = 4
    _sec(ws, r, "1. Model Assumptions", 3)
    ws.cell(row=r, column=4, value="Yellow cells are editable").font = Font(italic=True, color="595959")
    r += 1
    
    def add_assumption(row_idx, label, default_val, is_pct=True):
        ws.cell(row=row_idx, column=1, value=label).font = LBL_FONT
        c = ws.cell(row=row_idx, column=2, value=default_val)
        c.fill = YLW_FILL
        c.font = Font(name="Calibri", size=11, bold=True, color="1F4E78")
        c.border = THIN
        c.alignment = RIGHT
        c.number_format = FMT_PCT if is_pct else FMT_DEC
        return c.coordinate
        
    wacc_cell = add_assumption(r, "Weighted Average Cost of Capital (WACC)", default_wacc); r += 1
    tg_cell = add_assumption(r, "Terminal Growth Rate", default_tg); r += 1
    rev_gr_cell = add_assumption(r, "Projected Revenue Growth (Years 1-5)", rev_cagr); r += 1
    fcf_m_cell = add_assumption(r, "Projected FCF Margin", avg_fcf_margin); r += 1
    
    r += 1
    
    # --- Projections Block ---
    _sec(ws, r, "2. Free Cash Flow Projections", 7)
    r += 1
    
    _hdr(ws, r, ["Metric", "Year 0 (Current)", "Year 1", "Year 2", "Year 3", "Year 4", "Year 5"])
    r += 1
    
    # Revenue row
    ws.cell(row=r, column=1, value=f"Revenue ({currency} {units})").font = LBL_FONT
    ws.cell(row=r, column=2, value=last_rev).number_format = FMT_MONEY
    ws.cell(row=r, column=2).font = DAT_FONT
    
    rev_cells = []
    for i in range(1, 6):
        c = ws.cell(row=r, column=2+i, value=f"={get_column_letter(2+i-1)}{r}*(1+{rev_gr_cell})")
        c.number_format = FMT_MONEY; c.font = DAT_FONT
        rev_cells.append(c.coordinate)
    r += 1
    
    # FCF row
    ws.cell(row=r, column=1, value=f"Free Cash Flow ({currency} {units})").font = LBL_FONT
    ws.cell(row=r, column=2, value=last_fcf).number_format = FMT_MONEY
    ws.cell(row=r, column=2).font = DAT_FONT
    
    fcf_cells = []
    for i in range(1, 6):
        c = ws.cell(row=r, column=2+i, value=f"={rev_cells[i-1]}*{fcf_m_cell}")
        c.number_format = FMT_MONEY; c.font = DAT_FONT
        fcf_cells.append(c.coordinate)
    r += 1
    
    # Discount Factor
    ws.cell(row=r, column=1, value="Discount Factor").font = LBL_FONT
    ws.cell(row=r, column=2, value=1.0).number_format = FMT_DEC
    ws.cell(row=r, column=2).font = Font(italic=True, color="595959")
    
    df_cells = []
    for i in range(1, 6):
        c = ws.cell(row=r, column=2+i, value=f"=1/((1+{wacc_cell})^{i})")
        c.number_format = FMT_DEC; c.font = Font(italic=True, color="595959")
        df_cells.append(c.coordinate)
    r += 1
    
    # PV of FCF
    ws.cell(row=r, column=1, value="Present Value of FCF").font = Font(name="Calibri", bold=True)
    ws.cell(row=r, column=2, value="--").alignment = RIGHT
    
    pv_fcf_cells = []
    for i in range(1, 6):
        c = ws.cell(row=r, column=2+i, value=f"={fcf_cells[i-1]}*{df_cells[i-1]}")
        c.number_format = FMT_MONEY; c.font = Font(name="Calibri", bold=True)
        pv_fcf_cells.append(c.coordinate)
    
    sum_pv_fcf_cell = f"SUM({pv_fcf_cells[0]}:{pv_fcf_cells[-1]})"
    r += 2
    
    # --- Valuation Bridge ---
    _sec(ws, r, "3. Valuation Bridge", 3)
    r += 1
    
    # TV
    ws.cell(row=r, column=1, value="Terminal Value (TV)").font = LBL_FONT
    tv_formula = f"={fcf_cells[-1]}*(1+{tg_cell})/({wacc_cell}-{tg_cell})"
    c = ws.cell(row=r, column=2, value=tv_formula); c.number_format = FMT_MONEY; c.font = DAT_FONT
    tv_cell = c.coordinate
    r += 1
    
    # PV of TV
    ws.cell(row=r, column=1, value="PV of Terminal Value").font = LBL_FONT
    pv_tv_formula = f"={tv_cell}*{df_cells[-1]}"
    c = ws.cell(row=r, column=2, value=pv_tv_formula); c.number_format = FMT_MONEY; c.font = DAT_FONT
    pv_tv_cell = c.coordinate
    r += 1
    
    # EV
    ws.cell(row=r, column=1, value="Implied Enterprise Value").font = Font(name="Calibri", bold=True, size=12)
    ev_formula = f"={sum_pv_fcf_cell}+{pv_tv_cell}"
    c = ws.cell(row=r, column=2, value=ev_formula); c.number_format = FMT_MONEY; c.font = Font(name="Calibri", bold=True, size=12)
    ev_cell = c.coordinate
    r += 1
    
    # Net Debt
    ws.cell(row=r, column=1, value="Less: Net Debt").font = LBL_FONT
    c = ws.cell(row=r, column=2, value=net_debt); c.number_format = FMT_MONEY; c.font = DAT_FONT
    nd_cell = c.coordinate
    r += 1
    
    # Equity Value
    ws.cell(row=r, column=1, value="Implied Equity Value").font = Font(name="Calibri", bold=True, size=12)
    eq_formula = f"={ev_cell}-{nd_cell}"
    c = ws.cell(row=r, column=2, value=eq_formula); c.number_format = FMT_MONEY; c.font = Font(name="Calibri", bold=True, size=12)
    eq_cell = c.coordinate
    r += 1
    
    # Shares
    ws.cell(row=r, column=1, value="Diluted Shares Outstanding").font = LBL_FONT
    c = ws.cell(row=r, column=2, value=shares_out); c.number_format = FMT_DEC; c.font = DAT_FONT
    shares_cell = c.coordinate
    r += 1
    
    # Implied Share Price
    ws.cell(row=r, column=1, value="Implied Share Price").font = Font(name="Calibri", bold=True, size=14, color="1F4E78")
    share_price_formula = f"={eq_cell}/{shares_cell}"
    c = ws.cell(row=r, column=2, value=share_price_formula); c.number_format = FMT_DEC; c.font = Font(name="Calibri", bold=True, size=14, color="1F4E78")
    c.fill = GRN_FILL
    r += 2
    
    # Format and fit
    _widths(ws, [35, 18, 18, 18, 18, 18, 18])


def _narrative_sheet(wb, data):
    ws = wb.create_sheet("Industry & AI Insights")
    _title(ws, "AI Narrative Analysis", "Generated by AI Models")
    r = 4
    
    exec_summary = data.get("executive_summary", "")
    if exec_summary:
        _sec(ws, r, "Executive Summary", 2); r += 1
        c = ws.cell(row=r, column=1, value=exec_summary)
        c.font = LBL_FONT; c.alignment = Alignment(wrap_text=True, vertical="top")
        ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=4)
        ws.row_dimensions[r].height = 100
        r += 2
        
    industry_trends = data.get("industry_trends", "")
    if industry_trends:
        _sec(ws, r, "Industry Trends", 2); r += 1
        c = ws.cell(row=r, column=1, value=industry_trends)
        c.font = LBL_FONT; c.alignment = Alignment(wrap_text=True, vertical="top")
        ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=4)
        ws.row_dimensions[r].height = 120
        r += 2
        
    _widths(ws, [100])


def _sources_sheet(wb, data):
    ws = wb.create_sheet("Sources")
    _title(ws, "Sources", "All data sources and retrieval dates")
    _hdr(ws, 4, ["#","URL","Description","Retrieved"])
    r = 5
    for i, src in enumerate(data.get("sources",[]), start=1):
        ws.cell(row=r, column=1, value=i).alignment = CENTER
        url = src.get("url","")
        uc = ws.cell(row=r, column=2, value=url)
        uc.font = Font(name="Calibri", size=10, color="0563C1", underline="single")
        uc.alignment = LEFT
        if url:
            try: uc.hyperlink = url
            except: pass
        ws.cell(row=r, column=3, value=src.get("description","")).alignment = LEFT
        ws.cell(row=r, column=4, value=src.get("retrieved","")).alignment = CENTER
        for col in range(1,5): ws.cell(row=r,column=col).border = THIN
        ws.row_dimensions[r].height = 28; r += 1
    _widths(ws, [6,70,50,16])


# -- Orchestrator --------------------------------------------------------------

def build_workbook(data: dict, output_path: str) -> None:
    wb = Workbook()
    wb.remove(wb.active)
    _summary_sheet(wb, data)
    _income_sheet(wb, data)
    _balance_sheet(wb, data)
    _cashflow_sheet(wb, data)
    _ratios_sheet(wb, data)
    _dupont_sheet(wb, data)
    _dcf_sheet(wb, data)
    _growth_sheet(wb, data)
    _peers_sheet(wb, data)
    _swot_sheet(wb, data)
    _sources_sheet(wb, data)
    wb.save(output_path)


if __name__ == "__main__":
    import argparse
    p = argparse.ArgumentParser()
    p.add_argument("--input", "-i", required=True)
    p.add_argument("--output", "-o", required=True)
    args = p.parse_args()
    with open(args.input, encoding="utf-8") as f:
        data = json.load(f)
    Path(args.output).parent.mkdir(parents=True, exist_ok=True)
    build_workbook(data, args.output)
    print(f"Wrote: {args.output}")