import re
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from pathlib import Path
from IPython.display import display

pd.set_option("display.max_columns", 200)
pd.set_option("display.width", 180)
pd.set_option("display.float_format", lambda x: f"{x:,.4f}")


def locate_base_dir(start: Path) -> Path:
    for p in [start, *start.parents]:
        if (p / "DATA" / "csv_export").exists() and (p / "Mini-projects").exists():
            return p
    raise FileNotFoundError("Could not locate the course root from the current working directory.")


BASE_DIR = locate_base_dir(Path.cwd())
CH02_OUT_DIR = BASE_DIR / "Mini-projects" / "outputs"
OUT_DIR = BASE_DIR / "Mini projects" / "outputs"
OUT_DIR.mkdir(parents=True, exist_ok=True)

CH02_PACK_PATH = CH02_OUT_DIR / "unit1_tidy_historical_pack_usdm.csv"
CH02_AUDIT_PATH = CH02_OUT_DIR / "unit1_tidy_historical_audit.csv"

ch02_tidy_raw = pd.read_csv(CH02_PACK_PATH, parse_dates=["Date"])
ch02_audit_raw = pd.read_csv(CH02_AUDIT_PATH)

print("Loaded quarterly Chapter 2 base files:")
print(f"  Historical pack: {ch02_tidy_raw.shape} -> {CH02_PACK_PATH}")
print(f"  Audit table    : {ch02_audit_raw.shape} -> {CH02_AUDIT_PATH}")
print("Chapter 3 output folder:", OUT_DIR)

# Running the actuall functions to get the tidy data ready to go 
hist, audit, schema_map = prepare_history_from_ch02(ch02_tidy_raw, ch02_audit_raw)

print(f"History window: {hist.index.min()} to {hist.index.max()} ({len(hist)} quarters)")
print()
print("Schema map:")
display(schema_map)
print()
print("Chapter 2 audit carry-forward (tail):")
display(audit.tail(12))
print()
print("Quarterly income statement history (tail, USDm):")
display(hist[[
    "FiscalYear", "Quarter", "Revenue", "COGS", "GrossProfit", "OperatingExpenses", "OperatingIncome",
    "EBIT", "DA", "EBITDA", "InterestExpense", "EBT", "TaxExpense", "NetIncome"
]].round(2))
ebitda_desc = hist["NetIncome"].describe().T
display(ebitda_desc)

def build_revenue_drivers_bottom_up(
    hist: pd.DataFrame,
    periods: list[str],
    pvm_foundation: dict[str, dict[str, list[float]]],
    forecast_quarters: int = 8,
) -> tuple[pd.DataFrame, pd.DataFrame, pd.DataFrame, pd.DataFrame]:

    metric_cols = [
        "RevenueGrowth_q",
        "PriceGrowthEff_q",
        "VolumeGrowthEff_q",
        "MixGrowthEff_q",
    ]

    def split_period_key(period_key: str) -> tuple[int, int]:
        year_str, quarter_str = str(period_key).split("-Q")
        return int(year_str), int(quarter_str)

    def next_periods(start_period: str, periods_forward: int) -> list[str]:
        year, quarter = split_period_key(start_period)
        out = []
        for _ in range(periods_forward):
            quarter += 1
            if quarter > 4:
                year += 1
                quarter = 1
            out.append(f"{year}-Q{quarter}")
        return out

    def safe_recent_median(fit_df: pd.DataFrame) -> pd.Series:
        if fit_df.empty:
            return pd.Series({col: 0.0 for col in metric_cols}, dtype=float)
        out = fit_df.tail(min(8, len(fit_df)))[metric_cols].median(numeric_only=True)
        return out.reindex(metric_cols).fillna(0.0)

    def build_blended_series(
        quarter_series: pd.Series,
        seasonal: pd.DataFrame,
        recent: pd.Series,
        metric: str,
        weights: np.ndarray,
    ) -> list[float]:
        values = []
        recent_value = float(recent.get(metric, 0.0))
        if pd.isna(recent_value):
            recent_value = 0.0

        for quarter, weight in zip(quarter_series, weights):
            if metric in seasonal.columns and quarter in seasonal.index:
                seasonal_value = seasonal.loc[quarter, metric]
            else:
                seasonal_value = recent_value

            if pd.isna(seasonal_value):
                seasonal_value = recent_value

            values.append(weight * float(seasonal_value) + (1.0 - weight) * recent_value)

        return values

    # 1) Expand the regional PVM foundation into a long flat table
    rows = []
    for region, values in pvm_foundation.items():
        if len(values["Volume"]) != len(periods) or len(values["Revenue"]) != len(periods):
            raise ValueError(f"PVM series length mismatch for {region}.")
        for period, volume, revenue in zip(periods, values["Volume"], values["Revenue"]):
            rows.append(
                {
                    "Region": region,
                    "Period": period,
                    "Volume": volume,
                    "Revenue_USDm": revenue,
                }
            )

    pvm_ex = pd.DataFrame(rows)
    pvm_ex["FiscalYear"] = pvm_ex["Period"].str.split("-Q").str[0].astype(int)
    pvm_ex["Quarter"] = pvm_ex["Period"].str.split("-Q").str[1].astype(int)
    pvm_ex = pvm_ex.sort_values(["Region", "FiscalYear", "Quarter"]).reset_index(drop=True)
    pvm_ex["ASP"] = pvm_ex["Revenue_USDm"] / pvm_ex["Volume"].replace(0, np.nan)

    # 2) Decide the forecast anchor
    # We keep the same notebook behavior: forecast starts after the last quarter in hist.
    hist_last_period = str(hist.index[-1])
    hist_last_revenue = float(hist["Revenue"].iloc[-1])

    pvm_last_period = max(pvm_ex["Period"].unique(), key=split_period_key)

    # If the regional PVM table ends before hist, allocate the latest company revenue
    # back to regions using the most recent regional revenue mix.
    latest_mix = pvm_ex.loc[pvm_ex["Period"] == pvm_last_period, ["Region", "Revenue_USDm"]].copy()
    latest_mix["RevenueShare"] = latest_mix["Revenue_USDm"] / latest_mix["Revenue_USDm"].sum()

    if split_period_key(hist_last_period) >= split_period_key(pvm_last_period):
        forecast_anchor_period = hist_last_period
        company_anchor_revenue = hist_last_revenue

        if hist_last_period == pvm_last_period:
            anchor_method = "Actual regional revenue at the forecast anchor period"
            anchor_revenue_by_region = latest_mix.set_index("Region")["Revenue_USDm"].to_dict()
        else:
            anchor_method = (
                f"Scaled latest regional mix from {pvm_last_period} "
                f"to company anchor revenue at {hist_last_period}"
            )
            anchor_revenue_by_region = (
                latest_mix.set_index("Region")["RevenueShare"] * company_anchor_revenue
            ).to_dict()
    else:
        forecast_anchor_period = pvm_last_period
        company_anchor_revenue = float(latest_mix["Revenue_USDm"].sum())
        anchor_method = "Actual regional revenue at the latest PVM period"
        anchor_revenue_by_region = latest_mix.set_index("Region")["Revenue_USDm"].to_dict()

    future_periods = next_periods(forecast_anchor_period, forecast_quarters)
    weights = np.linspace(1.0, 0.55, forecast_quarters)

    regional_history_parts = []
    regional_forecast_parts = []
    summary_rows = []

    # 3) Decompose and forecast each region separately
    for region in sorted(pvm_ex["Region"].unique()):
        region_hist = pvm_ex.loc[pvm_ex["Region"] == region].copy()
        region_hist = region_hist.sort_values(["FiscalYear", "Quarter"]).reset_index(drop=True)

        region_hist["PrevRevenue_USDm"] = region_hist["Revenue_USDm"].shift(1)
        region_hist["PrevVolume"] = region_hist["Volume"].shift(1)
        region_hist["PrevASP"] = region_hist["ASP"].shift(1)

        region_hist["RevenueGrowth_q"] = region_hist["Revenue_USDm"].pct_change()

        region_hist["PriceEffect_USDm"] = (
            (region_hist["ASP"] - region_hist["PrevASP"]) * region_hist["PrevVolume"]
        )
        region_hist["VolumeEffect_USDm"] = (
            region_hist["PrevASP"] * (region_hist["Volume"] - region_hist["PrevVolume"])
        )
        region_hist["MixEffect_USDm"] = (
            region_hist["Revenue_USDm"]
            - region_hist["PrevRevenue_USDm"]
            - region_hist["PriceEffect_USDm"]
            - region_hist["VolumeEffect_USDm"]
        )

        base_prev_revenue = region_hist["PrevRevenue_USDm"].replace(0, np.nan)
        region_hist["PriceGrowthEff_q"] = region_hist["PriceEffect_USDm"] / base_prev_revenue
        region_hist["VolumeGrowthEff_q"] = region_hist["VolumeEffect_USDm"] / base_prev_revenue
        region_hist["MixGrowthEff_q"] = region_hist["MixEffect_USDm"] / base_prev_revenue

        region_hist = region_hist.replace([np.inf, -np.inf], np.nan)

        fit = region_hist[["Quarter", *metric_cols]].dropna().copy()

        if fit.empty:
            seasonal = pd.DataFrame(columns=metric_cols)
            recent = pd.Series({col: 0.0 for col in metric_cols}, dtype=float)
        else:
            seasonal = fit.groupby("Quarter")[metric_cols].median(numeric_only=True)
            recent = safe_recent_median(fit)

        region_future = pd.DataFrame(index=future_periods)
        region_future["Region"] = region
        region_future["Period"] = region_future.index
        region_future["FiscalYear"] = [split_period_key(p)[0] for p in region_future.index]
        region_future["Quarter"] = [split_period_key(p)[1] for p in region_future.index]

        region_future["PriceSignal_Assump_q"] = build_blended_series(
            region_future["Quarter"], seasonal, recent, "PriceGrowthEff_q", weights
        )
        region_future["VolumeSignal_Assump_q"] = build_blended_series(
            region_future["Quarter"], seasonal, recent, "VolumeGrowthEff_q", weights
        )
        region_future["MixSignal_Assump_q"] = build_blended_series(
            region_future["Quarter"], seasonal, recent, "MixGrowthEff_q", weights
        )

        region_future["FinalRevenueGrowth_q"] = (
            region_future["PriceSignal_Assump_q"]
            + region_future["VolumeSignal_Assump_q"]
            + region_future["MixSignal_Assump_q"]
        ).clip(lower=-0.25, upper=0.30)

        anchor_revenue = float(anchor_revenue_by_region.get(region, region_hist["Revenue_USDm"].iloc[-1]))

        revenue = anchor_revenue
        prev_revenues = []
        revenue_path = []

        for growth in region_future["FinalRevenueGrowth_q"]:
            prev_revenues.append(revenue)
            revenue = revenue * (1.0 + float(growth))
            revenue_path.append(revenue)

        region_future["AnchorPeriod"] = forecast_anchor_period
        region_future["AnchorMethod"] = anchor_method
        region_future["AnchorRevenue_USDm"] = anchor_revenue
        region_future["PrevRevenue_USDm"] = prev_revenues
        region_future["RevenueForecast"] = revenue_path

        # Store forecast contributions in USDm so total-company drivers can be
        # aggregated after regional forecasting is complete.
        denom = region_future["PrevRevenue_USDm"].replace(0, np.nan)
        region_future["PriceEffectForecast_USDm"] = denom * region_future["PriceSignal_Assump_q"]
        region_future["VolumeEffectForecast_USDm"] = denom * region_future["VolumeSignal_Assump_q"]
        region_future["MixEffectForecast_USDm"] = denom * region_future["MixSignal_Assump_q"]

        regional_history_parts.append(region_hist)
        regional_forecast_parts.append(region_future.reset_index(drop=True))

        summary_rows.append(
            {
                "Region": region,
                "LastActualPVMPeriod": str(region_hist["Period"].iloc[-1]),
                "ForecastAnchorPeriod": forecast_anchor_period,
                "AnchorMethod": anchor_method,
                "AnchorRevenue_USDm": anchor_revenue,
                "RecentRevenueGrowth_q": float(recent.get("RevenueGrowth_q", 0.0)),
                "RecentPrice_q": float(recent.get("PriceGrowthEff_q", 0.0)),
                "RecentVolume_q": float(recent.get("VolumeGrowthEff_q", 0.0)),
                "RecentMix_q": float(recent.get("MixGrowthEff_q", 0.0)),
            }
        )

    regional_history = pd.concat(regional_history_parts, ignore_index=True)
    regional_history = regional_history.sort_values(["Region", "FiscalYear", "Quarter"]).reset_index(drop=True)

    regional_forecast = pd.concat(regional_forecast_parts, ignore_index=True)
    regional_forecast = regional_forecast.sort_values(["Region", "FiscalYear", "Quarter"]).reset_index(drop=True)

    # 4) Aggregate to company level only after all regional forecasts are done
    company_forecast = (
        regional_forecast.groupby(["Period", "FiscalYear", "Quarter"], as_index=False)[
            [
                "PrevRevenue_USDm",
                "PriceEffectForecast_USDm",
                "VolumeEffectForecast_USDm",
                "MixEffectForecast_USDm",
                "RevenueForecast",
            ]
        ]
        .sum()
        .sort_values(["FiscalYear", "Quarter"])
    )

    company_forecast["RevenueGrowth_q"] = (
        company_forecast["RevenueForecast"] / company_forecast["PrevRevenue_USDm"].replace(0, np.nan)
    ) - 1.0
    company_forecast["PriceSignal_Assump_q"] = (
        company_forecast["PriceEffectForecast_USDm"] / company_forecast["PrevRevenue_USDm"].replace(0, np.nan)
    )
    company_forecast["VolumeSignal_Assump_q"] = (
        company_forecast["VolumeEffectForecast_USDm"] / company_forecast["PrevRevenue_USDm"].replace(0, np.nan)
    )
    company_forecast["MixSignal_Assump_q"] = (
        company_forecast["MixEffectForecast_USDm"] / company_forecast["PrevRevenue_USDm"].replace(0, np.nan)
    )

    company_forecast = company_forecast.replace([np.inf, -np.inf], np.nan)
    company_forecast = company_forecast.set_index("Period")

    # 5) Build total-company summary from regional decomposition after the fact
    company_history = (
        regional_history.groupby(["Period", "FiscalYear", "Quarter"], as_index=False)[
            [
                "Revenue_USDm",
                "Volume",
                "PriceEffect_USDm",
                "VolumeEffect_USDm",
                "MixEffect_USDm",
            ]
        ]
        .sum()
        .sort_values(["FiscalYear", "Quarter"])
    )

    company_history["PrevRevenue_USDm"] = company_history["Revenue_USDm"].shift(1)
    company_history["RevenueGrowth_q"] = company_history["Revenue_USDm"].pct_change()

    company_base = company_history["PrevRevenue_USDm"].replace(0, np.nan)
    company_history["PriceGrowthEff_q"] = company_history["PriceEffect_USDm"] / company_base
    company_history["VolumeGrowthEff_q"] = company_history["VolumeEffect_USDm"] / company_base
    company_history["MixGrowthEff_q"] = company_history["MixEffect_USDm"] / company_base

    company_fit = company_history[metric_cols].replace([np.inf, -np.inf], np.nan).dropna()
    company_recent = safe_recent_median(company_fit)

    summary_rows.append(
        {
            "Region": "Total Company",
            "LastActualPVMPeriod": pvm_last_period,
            "ForecastAnchorPeriod": forecast_anchor_period,
            "AnchorMethod": anchor_method,
            "AnchorRevenue_USDm": company_anchor_revenue,
            "RecentRevenueGrowth_q": float(company_recent.get("RevenueGrowth_q", 0.0)),
            "RecentPrice_q": float(company_recent.get("PriceGrowthEff_q", 0.0)),
            "RecentVolume_q": float(company_recent.get("VolumeGrowthEff_q", 0.0)),
            "RecentMix_q": float(company_recent.get("MixGrowthEff_q", 0.0)),
        }
    )

    summary = pd.DataFrame(summary_rows)

    return regional_history, regional_forecast, company_forecast, summary

#Printing the summaries and outputs
print("Quarterly PVM driver summary:")
print(f"  Recent revenue growth median: {revenue_summary['recent_revenue_growth_q']:.4f}")
print(f"  Recent price effect median  : {revenue_summary['recent_price_q']:.4f}")
print(f"  Recent volume effect median : {revenue_summary['recent_volume_q']:.4f}")
print(f"  Recent mix effect median    : {revenue_summary['recent_mix_q']:.4f}")
print()
print("PVM quarterly decomposition sample:")
display(q_agg[["FiscalYear", "Quarter", "Revenue_USDm", "Volume", "ASP", "PriceGrowthEff_q", "VolumeGrowthEff_q", "MixGrowthEff_q"]].tail(8).round(6))
print()
print("Forecast revenue driver table:")
display(rev_assumptions.round(6))

def same_quarter_blend(seasonal_series: pd.Series, overall_value: float, quarter_index: pd.Series, seasonal_weight: float = 0.70) -> pd.Series:
    values = []
    for quarter in quarter_index:
        seasonal_value = float(seasonal_series.get(quarter, overall_value))
        if pd.isna(seasonal_value):
            seasonal_value = overall_value
        if pd.isna(overall_value):
            overall_used = seasonal_value
        else:
            overall_used = overall_value
        values.append(seasonal_weight * seasonal_value + (1.0 - seasonal_weight) * overall_used)
    return pd.Series(values, index=quarter_index.index)


def build_income_statement_model(hist: pd.DataFrame, rev_assumptions: pd.DataFrame) -> tuple[pd.DataFrame, pd.DataFrame]:
    hist_model = hist.copy()

    recent_gm = float(hist_model["GrossMargin"].tail(8).median())
    recent_opex = float(hist_model["OpExMargin"].tail(8).median())
    recent_da = float(hist_model["DAMargin"].tail(8).median())
    recent_ebit_bridge = float(hist_model["EBITBridgeMargin"].tail(8).median())
    interest_margin = float(hist_model["InterestMargin"].tail(8).median())
    tax_rate_norm = float(hist_model.loc[hist_model["EBT"] > 0, "TaxRate"].tail(8).median())

    defaults = {
        "recent_gm": 0.65,
        "recent_opex": 0.55,
        "recent_da": 0.03,
        "recent_ebit_bridge": 0.0,
        "interest_margin": 0.007,
        "tax_rate_norm": 0.21,
    }
    if pd.isna(recent_gm):
        recent_gm = defaults["recent_gm"]
    if pd.isna(recent_opex):
        recent_opex = defaults["recent_opex"]
    if pd.isna(recent_da):
        recent_da = defaults["recent_da"]
    if pd.isna(recent_ebit_bridge):
        recent_ebit_bridge = defaults["recent_ebit_bridge"]
    if pd.isna(interest_margin):
        interest_margin = defaults["interest_margin"]
    if pd.isna(tax_rate_norm):
        tax_rate_norm = defaults["tax_rate_norm"]

    forecast = rev_assumptions.copy()
    forecast["GrossMargin"] = same_quarter_blend(
        hist_model.groupby("Quarter")["GrossMargin"].median(),
        recent_gm,
        forecast["Quarter"],
    ).clip(0.25, 0.80)
    forecast["Revenue"] = forecast["RevenueForecast"]
    forecast["COGS"] = forecast["Revenue"] * (1.0 - forecast["GrossMargin"])
    forecast["GrossProfit"] = forecast["Revenue"] - forecast["COGS"]

    forecast["OpExMargin"] = same_quarter_blend(
        hist_model.groupby("Quarter")["OpExMargin"].median(),
        recent_opex,
        forecast["Quarter"],
    ).clip(0.30, 0.80)
    forecast["OperatingExpenses"] = forecast["Revenue"] * forecast["OpExMargin"]
    forecast["OperatingIncome"] = forecast["GrossProfit"] - forecast["OperatingExpenses"]

    forecast["EBITBridgeMargin"] = same_quarter_blend(
        hist_model.groupby("Quarter")["EBITBridgeMargin"].median(),
        recent_ebit_bridge,
        forecast["Quarter"],
    ).clip(-0.05, 0.05)
    forecast["EBITBridge"] = forecast["Revenue"] * forecast["EBITBridgeMargin"]
    forecast["EBIT"] = forecast["OperatingIncome"] + forecast["EBITBridge"]

    forecast["DAMargin"] = same_quarter_blend(
        hist_model.groupby("Quarter")["DAMargin"].median(),
        recent_da,
        forecast["Quarter"],
    ).clip(0.0, 0.10)
    forecast["DA"] = forecast["Revenue"] * forecast["DAMargin"]
    forecast["EBITDA"] = forecast["EBIT"] + forecast["DA"]
    forecast["InterestExpense"] = forecast["Revenue"] * interest_margin
    forecast["EBT"] = forecast["EBIT"] - forecast["InterestExpense"]
    forecast["TaxRate"] = tax_rate_norm
    forecast["TaxExpense"] = np.where(forecast["EBT"] > 0, forecast["EBT"] * forecast["TaxRate"], 0.0)
    forecast["NetIncome"] = forecast["EBT"] - forecast["TaxExpense"]
    forecast["NetMargin"] = forecast["NetIncome"] / forecast["Revenue"].replace(0, np.nan)
    forecast["EBITMargin"] = forecast["EBIT"] / forecast["Revenue"].replace(0, np.nan)
    forecast["Stage"] = "Forecast"

    historical = hist_model.copy()
    historical["Stage"] = "Historical"

    model_cols = [
        "FiscalYear", "Quarter", "Stage", "Revenue", "COGS", "GrossProfit", "GrossMargin", "OperatingExpenses",
        "OpExMargin", "OperatingIncome", "EBITBridge", "EBITBridgeMargin", "EBIT", "EBITMargin", "DA", "DAMargin",
        "EBITDA", "InterestExpense", "EBT", "TaxRate", "TaxExpense", "NetIncome", "NetMargin"
    ]

    model_is = pd.concat([historical[model_cols], forecast[model_cols]], axis=0)
    model_is["RevenueGrowth"] = model_is["Revenue"].pct_change()
    model_is = model_is[[
        "FiscalYear", "Quarter", "Stage", "Revenue", "RevenueGrowth", "COGS", "GrossProfit", "GrossMargin",
        "OperatingExpenses", "OpExMargin", "OperatingIncome", "EBITBridge", "EBITBridgeMargin", "EBIT", "EBITMargin",
        "DA", "DAMargin", "EBITDA", "InterestExpense", "EBT", "TaxRate", "TaxExpense", "NetIncome", "NetMargin"
    ]]

    assumption_table = pd.DataFrame(
        {
            "Assumption": [
                "Quarterly price effect",
                "Quarterly volume effect",
                "Quarterly mix effect",
                "Gross margin path",
                "Operating expense margin path",
                "EBIT bridge margin path",
                "DA margin path",
                "Interest expense burden",
                "Normalized tax rate",
                "Forecast horizon (quarters)",
            ],
            "Value": [
                float(rev_assumptions["PriceSignal_Assump_q"].median()),
                float(rev_assumptions["VolumeSignal_Assump_q"].median()),
                float(rev_assumptions["MixSignal_Assump_q"].median()),
                float(forecast["GrossMargin"].median()),
                float(forecast["OpExMargin"].median()),
                float(forecast["EBITBridgeMargin"].median()),
                float(forecast["DAMargin"].median()),
                interest_margin,
                tax_rate_norm,
                int(len(forecast)),
            ],
            "Basis": [
                "Same-quarter PVM median blended with recent overall median",
                "Same-quarter PVM median blended with recent overall median",
                "Same-quarter PVM median blended with recent overall median",
                "Same-quarter historical median blended with trailing eight-quarter median",
                "Same-quarter historical median blended with trailing eight-quarter median",
                "Same-quarter historical median blended with trailing eight-quarter median",
                "Same-quarter historical median blended with trailing eight-quarter median",
                "Trailing eight-quarter median as a percent of revenue",
                "Median tax rate on positive-EBT quarters",
                "Configured forecast horizon",
            ],
        }
    )

    return model_is, assumption_table


model_is, assumption_table = build_income_statement_model(hist, rev_assumptions)

print("Explicit quarterly modeling assumptions:")
display(assumption_table)
print()
print("Model-ready quarterly income statement (tail):")
display(model_is.tail(20).round(4))

def build_exception_report(
    model_df: pd.DataFrame,
    margin_thresholds_bps: dict[str, float] | None = None,
    z_threshold: float = 2.0,
    tol: float = 1e-6,
) -> pd.DataFrame:
    if margin_thresholds_bps is None:
        margin_thresholds_bps = {
            "GrossMargin": 250.0,
            "EBITMargin": 300.0,
            "NetMargin": 300.0,
        }

    d = model_df.copy()
    rows = []

    for metric, threshold in margin_thresholds_bps.items():
        delta_bps = d[metric].diff(4) * 10_000
        bad = delta_bps[delta_bps.abs() > threshold].dropna()
        for period_key, value in bad.items():
            rows.append({
                "Period": period_key,
                "Stage": d.loc[period_key, "Stage"],
                "CheckType": "MarginDrift",
                "Rule": f"|YoY Delta {metric}| > {threshold:.0f} bps",
                "Value": float(value),
                "Severity": "Warning",
            })

    hist_mask = d["Stage"].eq("Historical")
    for metric in ["RevenueGrowth", "GrossMargin", "EBITMargin", "NetMargin"]:
        for quarter_num, base in d.loc[hist_mask, ["Quarter", metric]].dropna().groupby("Quarter"):
            base_series = base[metric]
            if len(base_series) < 3 or float(base_series.std(ddof=0)) == 0.0:
                continue
            mu = float(base_series.mean())
            sd = float(base_series.std(ddof=0))
            candidates = d.loc[d["Quarter"] == quarter_num, metric].dropna()
            z_scores = (candidates - mu) / sd
            bad = z_scores[z_scores.abs() > z_threshold]
            for period_key, value in bad.items():
                rows.append({
                    "Period": period_key,
                    "Stage": d.loc[period_key, "Stage"],
                    "CheckType": "Outlier",
                    "Rule": f"|SameQuarter z({metric})| > {z_threshold:.1f}",
                    "Value": float(value),
                    "Severity": "Warning",
                })

    gp_diff = (d["Revenue"] - d["COGS"] - d["GrossProfit"]).abs()
    for period_key, value in gp_diff[gp_diff > tol].items():
        rows.append({
            "Period": period_key,
            "Stage": d.loc[period_key, "Stage"],
            "CheckType": "Mechanics",
            "Rule": "Revenue - COGS must equal GrossProfit",
            "Value": float(value),
            "Severity": "Critical",
        })

    opinc_diff = (d["GrossProfit"] - d["OperatingExpenses"] - d["OperatingIncome"]).abs()
    for period_key, value in opinc_diff[opinc_diff > tol].items():
        rows.append({
            "Period": period_key,
            "Stage": d.loc[period_key, "Stage"],
            "CheckType": "Mechanics",
            "Rule": "GrossProfit - OpEx must equal OperatingIncome",
            "Value": float(value),
            "Severity": "Critical",
        })

    ebit_diff = (d["OperatingIncome"] + d["EBITBridge"] - d["EBIT"]).abs()
    for period_key, value in ebit_diff[ebit_diff > tol].items():
        rows.append({
            "Period": period_key,
            "Stage": d.loc[period_key, "Stage"],
            "CheckType": "Mechanics",
            "Rule": "OperatingIncome + EBITBridge must equal EBIT",
            "Value": float(value),
            "Severity": "Critical",
        })

    ebitda_diff = (d["EBIT"] + d["DA"] - d["EBITDA"]).abs()
    for period_key, value in ebitda_diff[ebitda_diff > tol].items():
        rows.append({
            "Period": period_key,
            "Stage": d.loc[period_key, "Stage"],
            "CheckType": "Mechanics",
            "Rule": "EBIT + DA must equal EBITDA",
            "Value": float(value),
            "Severity": "Critical",
        })

    ni_diff = (d["EBT"] - d["TaxExpense"] - d["NetIncome"]).abs()
    for period_key, value in ni_diff[ni_diff > tol].items():
        rows.append({
            "Period": period_key,
            "Stage": d.loc[period_key, "Stage"],
            "CheckType": "Mechanics",
            "Rule": "EBT - TaxExpense must equal NetIncome",
            "Value": float(value),
            "Severity": "Critical",
        })

    out = pd.DataFrame(rows)
    if len(out) == 0:
        return pd.DataFrame(columns=["Period", "Stage", "CheckType", "Rule", "Value", "Severity"])

    severity_order = {"Critical": 0, "Warning": 1}
    out["SeverityOrder"] = out["Severity"].map(severity_order).fillna(99)
    out = out.sort_values(["SeverityOrder", "Period", "CheckType", "Rule"]).drop(columns="SeverityOrder")
    return out.reset_index(drop=True)


exceptions = build_exception_report(model_is)

print(f"Exceptions found: {len(exceptions)}")
if len(exceptions) == 0:
    print("No exceptions triggered under current thresholds.")
else:
    display(exceptions)

fig, axes = plt.subplots(1, 2, figsize=(15, 4))
plot_idx = np.arange(len(model_is))
historical_cutoff = (model_is["Stage"] == "Historical").sum() - 0.5
xticks = np.arange(0, len(model_is), 4)
if xticks[-1] != len(model_is) - 1:
    xticks = np.append(xticks, len(model_is) - 1)

axes[0].plot(plot_idx, model_is["Revenue"], marker="o", label="Revenue")
axes[0].axvline(x=historical_cutoff, color="gray", linestyle="--", linewidth=1)
axes[0].set_title("Quarterly Revenue (USDm): historical + forecast")
axes[0].set_ylabel("USDm")
axes[0].set_xticks(xticks)
axes[0].set_xticklabels(model_is.index[xticks], rotation=45, ha="right")
axes[0].legend()

axes[1].plot(plot_idx, model_is["GrossMargin"] * 100, marker="o", label="GrossMargin")
axes[1].plot(plot_idx, model_is["EBITMargin"] * 100, marker="o", label="EBITMargin")
axes[1].plot(plot_idx, model_is["NetMargin"] * 100, marker="o", label="NetMargin")
axes[1].axvline(x=historical_cutoff, color="gray", linestyle="--", linewidth=1)
axes[1].set_title("Quarterly Margin Profile (%)")
axes[1].set_ylabel("Percent")
axes[1].set_xticks(xticks)
axes[1].set_xticklabels(model_is.index[xticks], rotation=45, ha="right")
axes[1].legend()

plt.tight_layout()
plt.show()