"""PO Tracker blueprint — API-based brand report + Excel download."""
import io, json, logging, os, warnings
from datetime import date, timedelta

import pandas as pd
import requests
from flask import Blueprint, jsonify, request, send_file

warnings.filterwarnings("ignore", message="Unverified HTTPS")
logger = logging.getLogger("apex.po_tracker")
po_tracker_bp = Blueprint("po_tracker", __name__, url_prefix="/po-tracker")

API_BASE        = "https://wbdrpl.rptechindia.co"
COMPANY         = "RPPL"
SALES_DAYS      = 90
PREDICT_MONTHS  = 3
DOH_CRITICAL, DOH_LOW, DOH_SURPLUS = 15, 30, 45
PO_EXPIRY_ALERT = 5
BOS_PORT        = "3221"
STOCK_PORT      = "3223"

def _get(url):
    r = requests.get(url, timeout=180, verify=False)
    r.raise_for_status()
    return r.json()

def _unwrap(raw):
    if isinstance(raw, list): return raw
    if isinstance(raw, dict) and "value" in raw: return raw["value"]
    if isinstance(raw, dict) and "Data" in raw:
        inner = raw["Data"]
        return json.loads(inner) if isinstance(inner, str) and inner.strip() else inner or []
    return raw

def _norm(s):   return s.astype(str).str.strip()
def _plant(s):  return s.astype(str).str.strip().str.split(".").str[0]
def _so_api(s): return s.astype(str).str.strip().str.lstrip("0")
def _fmt_cr(v):
    cr = v / 1e7
    return f"₹{cr:.2f} Cr" if cr >= 0.01 else f"₹{v:,.0f}"

def _one_month_ago():
    t = date.today()
    m = t.month - 1
    y = t.year
    if m <= 0: m += 12; y -= 1
    return date(y, m, 1).strftime("%Y%m%d")

def fetch_bos(brand, from_dt, to_dt):
    """Fetch all brands data then filter by brand locally."""
    url = (f"{API_BASE}:{BOS_PORT}/reporting/lfrshales"
           f"?sap-client=900"
           f'&company=[{{"company":"{COMPANY}"}}]'
           f'&date=[{{"date":"{from_dt}","{to_dt}"}}]'
           f'&Distributionchannel=[{{"Distributionchannel":"ZC"}}]'
           f'&Division=[{{"Division":"ZR"}}]'
           f'&sdoctyp=[{{"sdoctyp":"ZRRA"}}]')
    logger.info("[PO Tracker] BOS → %s", url)
    raw = _unwrap(_get(url))
    if not raw: return pd.DataFrame()
    df = pd.DataFrame(raw)
    logger.info("[PO Tracker] BOS total rows: %d", len(df))

    return df

def fetch_stock(brand, today):
    url = (f"{API_BASE}:{STOCK_PORT}/reporting/stock?sap-client=900"
           f'&date=[{{date:{today},{today}}}]'
           f'&brand=[{{brand:{brand}}}]'
           f'&material=[{{material:}}]'
           f'&company=[{{company:{COMPANY}}}]&zerostk=Y&inrma=1')
    raw = _unwrap(_get(url))
    if not raw: return pd.DataFrame()
    df = pd.DataFrame(raw)
    return df[[c for c in ["MATNR","WERKS","BRANCH_NAM","ARP","QUANTITY_END_OF_PERIOD"] if c in df.columns]]

def fetch_sales(brand, today):
    from_dt = (date.fromisoformat(f"{today[:4]}-{today[4:6]}-{today[6:]}") - timedelta(days=SALES_DAYS)).strftime("%Y%m%d")
    url = (f"{API_BASE}:{STOCK_PORT}/reporting/sales?sap-client=900"
           f'&date=[{{date:{from_dt},{today}}}]'
           f'&brand=[{{brand:{brand}}}]'
           f'&company=[{{company:{COMPANY}}}]&nocndn=')
    raw = _unwrap(_get(url))
    if not raw: return pd.DataFrame()
    df = pd.DataFrame(raw)
    return df[[c for c in ["AUBEL","MATNR","WERKS","VBELN","FKDAT","FKIMG","CHAMPNAME"] if c in df.columns]]

def fetch_transit(brand, today):
    url = (f"{API_BASE}:{STOCK_PORT}/reporting/transit?sap-client=900"
           f'&date=[{{date:{today},{today}}}]'
           f'&brand=[{{brand:{brand}}}]'
           f'&material=[{{material:}}]'
           f'&company=[{{company:{COMPANY}}}]&inrma=')
    raw = _unwrap(_get(url))
    if not raw: return pd.DataFrame()
    df = pd.DataFrame(raw)
    return df[[c for c in ["MATNR","BUYINGPLANT","BWERKSNAME","FKIMG","FKDAT","VBELN","NETWR"] if c in df.columns]]

def map_bos_cols(df):
    col_map = {
        "WERKS":"Plant","NAME1":"Customer Name","KUNNR":"Customer Code",
        "PONO":"PO No.","BSTDK":"PO Date","BSTDK_E":"PO Expiry",
        "EKGRP":"Pur.Grp.","MATNR":"Material","ARKTX":"Desc.",
        "SOVBELN":"Sales Order","SOAUDAT":"SO Date",
        "SOKWMENG":"SO Qty","SOUNITPR":"SO unit Price","TO_SO_UNIT_PRIC":"Total SO Value",
        "INVBELN":"Invoice No.","FKDAT":"Invoice date",
        "INVKWMENG":"Invoice Qty.","V_UNIT":"Inv Unit Price","NETWR":"Total Inv Value",
        "PENDING":"Pending Qty.","TOT_PENDING_VAL":"Total Pending Value",
        "STATUS":"Status","MCOD1":"Courier Partner",
        "SIGNI":"Docket Number","DTDIS":"POD Start Date","DATEN":"POD End Date",
        "SHPCITY":"Ship City",
    }
    return df.rename(columns={k: v for k, v in col_map.items() if k in df.columns})

def enrich(bos, stk_raw, sal_raw):
    df = bos.copy()
    df = df[df["Status"].astype(str).str.strip().str.upper().isin(["OPEN","PENDING"])].copy().reset_index(drop=True)
    if df.empty: return df

    df["_mat"]  = _norm(df["Material"].astype(str))
    df["_plant"]= _plant(df["Plant"].astype(str))
    df["_so"]   = df.get("Sales Order", pd.Series(dtype=str)).astype(str).str.strip().str.split(".").str[0]

    if len(stk_raw):
        s = stk_raw.copy()
        s["_mat"] = _norm(s["MATNR"]); s["_plant"] = _plant(s["WERKS"].astype(str))
        sc = ["_mat","_plant"] + [c for c in ["BRANCH_NAM","ARP","QUANTITY_END_OF_PERIOD"] if c in s.columns]
        df = df.merge(s[sc].drop_duplicates(["_mat","_plant"]), on=["_mat","_plant"], how="left")
        df.rename(columns={"BRANCH_NAM":"Branch Name","ARP":"ARP Price","QUANTITY_END_OF_PERIOD":"Stock"}, inplace=True)
        if "Branch Name" in df.columns: df["Branch Name"] = df["Branch Name"].fillna(df["_plant"])
    else:
        df["Branch Name"] = df["_plant"]; df["ARP Price"] = df["Stock"] = None

    if len(sal_raw) and "AUBEL" in sal_raw.columns:
        sv = sal_raw.copy()
        sv["_so"] = _so_api(sv["AUBEL"]); sv["_mat"] = _norm(sv["MATNR"]); sv["_plant"] = _plant(sv["WERKS"].astype(str))
        sm = sv.drop(columns=["AUBEL","MATNR","WERKS"], errors="ignore").drop_duplicates(["_so","_mat","_plant"])
        df = df.merge(sm, on=["_so","_mat","_plant"], how="left")
        if "CHAMPNAME" in df.columns: df.rename(columns={"CHAMPNAME":"Champion Name"}, inplace=True)

    T = pd.Timestamp(date.today())
    df["SO Date"]   = pd.to_datetime(df.get("SO Date"),   errors="coerce")
    df["PO Expiry"] = pd.to_datetime(df.get("PO Expiry"), errors="coerce")
    df["Days Open"] = (T - df["SO Date"]).dt.days.clip(lower=0)
    df["Days to PO Expiry"] = (df["PO Expiry"] - T).dt.days

    def aging(d):
        if pd.isna(d): return "Unknown"
        d = int(d)
        return "0-15 Days" if d<=15 else "16-30 Days" if d<=30 else "31-45 Days" if d<=45 else "46-60 Days" if d<=60 else "60+ Days (Critical)"
    df["Aging Bucket"] = df["Days Open"].apply(aging)

    pq  = pd.to_numeric(df["Pending Qty."],  errors="coerce").fillna(0)
    sk  = pd.to_numeric(df.get("Stock", pd.Series([0]*len(df))), errors="coerce").fillna(0)
    ed  = pd.to_numeric(df.get("Days to PO Expiry", pd.Series(dtype=float)), errors="coerce")
    soq = pd.to_numeric(df["SO Qty"], errors="coerce").fillna(0)
    ivq = pd.to_numeric(df["Invoice Qty."], errors="coerce").fillna(0)
    pv  = pd.to_numeric(df["Total Pending Value"], errors="coerce").fillna(0)

    df["Stock Status"] = ["No Pending Qty" if p==0 else "Yet to Bill" if p<=s else "Stock Not Available" for p,s in zip(pq,sk)]

    def act(i):
        p,s,e,iv,sq = pq.iloc[i],sk.iloc[i],(ed.iloc[i] if i<len(ed) else None),(ivq.iloc[i] if i<len(ivq) else 0),(soq.iloc[i] if i<len(soq) else 0)
        if p==0: return "✅ No Action"
        if pd.notna(e) and e<=PO_EXPIRY_ALERT and iv==0 and s>0: return "🔴 URGENT — PO EXPIRING, BILL NOW"
        if pd.notna(e) and e<0 and iv==0: return "🔴 PO EXPIRED — CONTACT CUSTOMER"
        if s>=p and iv==0: return "🟢 BILL NOW — STOCK AVAILABLE"
        if s>0 and iv>0 and iv<sq: return "🟡 COMPLETE BILLING — PARTIAL DONE"
        if s==0: return "🔴 ARRANGE STOCK FIRST"
        if s<p: return "🟠 PARTIAL STOCK — BILL AVAILABLE QTY"
        return "⚪ REVIEW"
    df["Action Required"] = [act(i) for i in range(len(df))]
    df["Invoiced %"] = (ivq / soq.replace(0, None) * 100).round(1)

    def priority(i):
        e = ed.iloc[i] if i<len(ed) else None
        d, pv_v, ss_v = df["Days Open"].iloc[i], pv.iloc[i], df["Stock Status"].iloc[i]
        if pd.notna(e) and e<0:                return "P1 — PO Expired"
        if pd.notna(e) and e<=PO_EXPIRY_ALERT: return "P1 — Expiring Soon"
        if pd.notna(d) and d>60:               return "P1 — Overdue 60+ Days"
        if ss_v=="Yet to Bill":                return "P1 — Bill Now"
        if pv_v>=100000:                       return "P1 — High Value"
        if pd.notna(e) and e<=30:              return "P2 — Expiring in 30d"
        if pd.notna(d) and d>30:               return "P2 — Aging 31-60 Days"
        if ss_v=="Stock Not Available":        return "P2 — Arrange Stock"
        if pv_v>=25000:                        return "P2 — Medium Value"
        return "P3 — Monitor"
    df["Priority"] = [priority(i) for i in range(len(df))]
    df.drop(columns=["_mat","_plant","_so"], inplace=True, errors="ignore")
    return df

def build_branch_transfer(stk_raw, sal_raw, tr_raw):
    if not len(stk_raw): return {}
    stk = stk_raw.copy()
    stk["_p"] = _plant(stk["WERKS"].astype(str)); stk["_m"] = _norm(stk["MATNR"])
    stk["Stock"] = pd.to_numeric(stk.get("QUANTITY_END_OF_PERIOD", 0), errors="coerce").fillna(0)
    stk["Branch Name"] = stk.get("BRANCH_NAM", stk["_p"]).fillna(stk["_p"]) if "BRANCH_NAM" in stk.columns else stk["_p"]
    sg = stk.groupby(["_p","_m"]).agg(Stock=("Stock","sum"), Branch_Name=("Branch Name","first"), Material=("MATNR","first")).reset_index()

    tg = pd.DataFrame()
    if len(tr_raw) and "BUYINGPLANT" in tr_raw.columns:
        tr = tr_raw.copy(); tr["_p"] = _plant(tr["BUYINGPLANT"].astype(str)); tr["_m"] = _norm(tr["MATNR"])
        tr["_f"] = pd.to_numeric(tr["FKIMG"], errors="coerce").fillna(0)
        tg = tr.groupby(["_p","_m"]).agg(In_Transit=("_f","sum")).reset_index()

    vg = pd.DataFrame()
    if len(sal_raw) and "FKIMG" in sal_raw.columns:
        sv = sal_raw.copy(); sv["_p"] = _plant(sv["WERKS"].astype(str)); sv["_m"] = _norm(sv["MATNR"])
        sv["_f"] = pd.to_numeric(sv["FKIMG"], errors="coerce").fillna(0); sv = sv[sv["_f"]>0]
        vg = sv.groupby(["_p","_m"]).agg(Total_Sold=("_f","sum")).reset_index()
        vg["Avg Monthly Sales"] = (vg["Total_Sold"] / (SALES_DAYS/30)).round(1)

    m = sg.merge(tg[["_p","_m","In_Transit"]].rename(columns={"In_Transit":"In Transit"}), on=["_p","_m"], how="left") if len(tg) else sg.assign(**{"In Transit":0})
    m["In Transit"] = m["In Transit"].fillna(0)
    m["Total Available"] = m["Stock"] + m["In Transit"]
    m = m.merge(vg[["_p","_m","Total_Sold","Avg Monthly Sales"]], on=["_p","_m"], how="left") if len(vg) else m.assign(**{"Total_Sold":0,"Avg Monthly Sales":0})
    m[["Total_Sold","Avg Monthly Sales"]] = m[["Total_Sold","Avg Monthly Sales"]].fillna(0)
    m["Branch Name"] = m["Branch_Name"]; m.drop(columns=["Branch_Name"], inplace=True, errors="ignore")
    m["Predicted Demand (3M)"] = (m["Avg Monthly Sales"] * PREDICT_MONTHS).round(0)
    m["Stock Gap"] = (m["Total Available"] - m["Predicted Demand (3M)"]).round(0)
    m["DOH (days)"] = m.apply(lambda x: round(x["Stock"]/(x["Avg Monthly Sales"]/30),1) if x["Avg Monthly Sales"]>0 else 999, axis=1)

    def doh_l(d): return "NO DATA" if d==999 else "CRITICAL" if d<DOH_CRITICAL else "LOW" if d<DOH_LOW else "HEALTHY" if d<DOH_SURPLUS else "SURPLUS"
    m["DOH Status"] = m["DOH (days)"].apply(doh_l)

    def tf(row):
        pred,avail,stk,tr = row["Predicted Demand (3M)"],row["Total Available"],row["Stock"],row["In Transit"]
        if pred==0: return "No Sales Data"
        if avail>=pred: return "Transit Covering Gap" if stk<pred and tr>0 else "Sufficient"
        return "Short Even with Transit" if stk<pred and tr>0 else "Transfer Needed"
    m["Transfer Flag"] = m.apply(tf, axis=1)

    surplus = m[m["DOH Status"]=="SURPLUS"].copy()
    needy   = m[m["Transfer Flag"].isin(["Transfer Needed","Short Even with Transit"])].copy()
    suggestions = []
    for _, nr in needy.iterrows():
        donors = surplus[(surplus["_m"]==nr["_m"]) & (surplus["_p"]!=nr["_p"])].sort_values("Stock", ascending=False)
        if len(donors):
            donor = donors.iloc[0]; gap = abs(nr["Stock Gap"]); xfer = min(gap, donor["Stock"]*0.5)
            suggestions.append({"material":nr["Material"],"needy_branch":nr["Branch Name"],"needy_stock":int(nr["Stock"]),"needy_transit":int(nr["In Transit"]),"needy_gap":int(gap),"donor_branch":donor["Branch Name"],"donor_stock":int(donor["Stock"]),"donor_doh":donor["DOH (days)"],"suggest_xfer_qty":int(xfer),"after_xfer":int(nr["Total Available"]+xfer),"flag":nr["Transfer Flag"]})

    branch_summary = m.groupby(["_p","Branch Name"]).agg(
        total_skus=("_m","count"), need_transfer=("Transfer Flag",lambda x:(x=="Transfer Needed").sum()),
        short_transit=("Transfer Flag",lambda x:(x=="Short Even with Transit").sum()),
        transit_ok=("Transfer Flag",lambda x:(x=="Transit Covering Gap").sum()),
        sufficient=("Transfer Flag",lambda x:(x=="Sufficient").sum()),
        stock=("Stock","sum"), in_transit=("In Transit","sum"),
        total_available=("Total Available","sum"), predicted_3m=("Predicted Demand (3M)","sum"), stock_gap=("Stock Gap","sum"),
    ).reset_index().drop(columns=["_p"], errors="ignore").sort_values("need_transfer", ascending=False)

    m.drop(columns=["_p","_m"], inplace=True, errors="ignore")
    return {"detail":m.to_dict(orient="records"),"suggestions":suggestions,"branch_summary":branch_summary.fillna(0).to_dict(orient="records")}

def build_summary(df, bt):
    if df.empty: return {}
    pv  = pd.to_numeric(df["Total Pending Value"], errors="coerce").fillna(0)
    pq  = pd.to_numeric(df["Pending Qty."], errors="coerce").fillna(0)
    soq = pd.to_numeric(df["SO Qty"], errors="coerce").fillna(0)
    ivq = pd.to_numeric(df["Invoice Qty."], errors="coerce").fillna(0)
    sv  = pd.to_numeric(df["Total SO Value"], errors="coerce").fillna(0)
    ed  = pd.to_numeric(df.get("Days to PO Expiry", pd.Series(dtype=float)), errors="coerce")
    ss  = df.get("Stock Status", pd.Series(dtype=str))
    bill_mask = df["Action Required"].isin(["🟢 BILL NOW — STOCK AVAILABLE","🔴 URGENT — PO EXPIRING, BILL NOW"])

    ytb = df[ss=="Yet to Bill"].copy()
    ytb_pv = pd.to_numeric(ytb["Total Pending Value"], errors="coerce").fillna(0)
    ytb_pq = pd.to_numeric(ytb["Pending Qty."], errors="coerce").fillna(0)
    top_customers = []
    if len(ytb):
        ytb["_pv"]=ytb_pv; ytb["_pq"]=ytb_pq; ytb["_do"]=pd.to_numeric(ytb["Days Open"],errors="coerce").fillna(0)
        cg = ytb.groupby("Customer Name").agg(orders=("PO No.","count"),materials=("Material","nunique"),pending_qty=("_pq","sum"),value=("_pv","sum"),max_days=("_do","max")).reset_index().sort_values("value",ascending=False).head(15)
        for _,r in cg.iterrows():
            top_customers.append({"customer":r["Customer Name"],"orders":int(r["orders"]),"materials":int(r["materials"]),"pending_qty":int(r["pending_qty"]),"value":float(r["value"]),"value_fmt":_fmt_cr(r["value"]),"max_days":int(r["max_days"]),"is_total":False})
        top_customers.append({"customer":"TOTAL","orders":int(cg["orders"].sum()),"materials":int(cg["materials"].sum()),"pending_qty":int(cg["pending_qty"].sum()),"value":float(cg["value"].sum()),"value_fmt":_fmt_cr(cg["value"].sum()),"max_days":int(cg["max_days"].max()),"is_total":True})

    expiry_groups = []
    def eg(d):
        if pd.isna(d): return "No Expiry Date"
        if d<0: return "Already Expired"
        if d<=PO_EXPIRY_ALERT: return f"Expiring in ≤{PO_EXPIRY_ALERT} Days"
        if d<=30: return "Expiring in 6-30 Days"
        return "Safe (>30 Days)"
    df2=df.copy(); df2["_eg"]=ed.apply(eg); df2["_pv"]=pv
    epvt=df2.groupby("_eg").agg(Count=("_eg","count"),PV=("_pv","sum")).reset_index()
    for o in ["Already Expired",f"Expiring in ≤{PO_EXPIRY_ALERT} Days","Expiring in 6-30 Days","Safe (>30 Days)","No Expiry Date"]:
        row=epvt[epvt["_eg"]==o]
        if len(row): expiry_groups.append({"label":o,"count":int(row.iloc[0]["Count"]),"value_fmt":_fmt_cr(row.iloc[0]["PV"])})

    branch_col = "Branch Name" if "Branch Name" in df.columns else "Plant"
    branch_agg = df.groupby(branch_col).agg(
        total_pos=("PO No.","count"),
        pending_qty=("Pending Qty.",lambda x:pd.to_numeric(x,errors="coerce").fillna(0).sum()),
        pending_value=("Total Pending Value",lambda x:pd.to_numeric(x,errors="coerce").fillna(0).sum()),
        p1_count=("Priority",lambda x:x.str.startswith("P1").sum()),
    ).reset_index().rename(columns={branch_col:"branch"}).sort_values("pending_value",ascending=False).head(15)

    bt_summary = {}
    if bt and "branch_summary" in bt:
        bt_summary = {"need_transfer":sum(b.get("need_transfer",0) for b in bt["branch_summary"]),"suggestions_count":len(bt.get("suggestions",[]))}

    return {
        "total_pos":int(len(df)), "p1_count":int(df["Priority"].str.startswith("P1").sum()),
        "p2_count":int(df["Priority"].str.startswith("P2").sum()), "p3_count":int(df["Priority"].str.startswith("P3").sum()),
        "expiring_soon":int((pd.notna(ed)&(ed>=0)&(ed<=PO_EXPIRY_ALERT)).sum()), "expired":int((pd.notna(ed)&(ed<0)).sum()),
        "pending_qty":int(pq.sum()), "pending_value":_fmt_cr(pv.sum()),
        "total_so_value":_fmt_cr(sv.sum()), "overall_invoiced_pct":round(ivq.sum()/soq.sum()*100,1) if soq.sum()>0 else 0,
        "bill_today_value":_fmt_cr(pv[bill_mask].sum()), "bill_today_qty":int(pq[bill_mask].sum()),
        "stock_not_avail":int((ss=="Stock Not Available").sum()), "yet_to_bill":int((ss=="Yet to Bill").sum()),
        "yet_to_bill_value":_fmt_cr(ytb_pv.sum()), "yet_to_bill_qty":int(ytb_pq.sum()),
        "aging_counts":df["Aging Bucket"].value_counts().to_dict(),
        "priority_counts":df["Priority"].value_counts().to_dict(),
        "action_counts":df["Action Required"].value_counts().to_dict(),
        "expiry_groups":expiry_groups, "top_customers":top_customers,
        "branch_transfer":bt_summary, "branch_summary":branch_agg.to_dict(orient="records"),
        "top_p1":df[df["Priority"].str.startswith("P1")].head(10)[[
            "Plant","Customer Name","PO No.","Material","Desc.",
            "Pending Qty.","Total Pending Value","Days to PO Expiry",
            "Aging Bucket","Priority","Action Required","Stock Status"
        ]].fillna("").to_dict(orient="records"),
    }

def generate_excel(df, bt, brand):
    from openpyxl import Workbook
    from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
    from openpyxl.utils import get_column_letter
    from openpyxl.utils.dataframe import dataframe_to_rows

    def _thin(c="CCCCCC"):
        s=Side(style="thin",color=c); return Border(left=s,right=s,top=s,bottom=s)
    def _hdr(ws,r,c,v,bg,fg="FFFFFF",size=9):
        cell=ws.cell(row=r,column=c,value=v); cell.fill=PatternFill("solid",start_color=bg)
        cell.font=Font(bold=True,color=fg,name="Calibri",size=size)
        cell.alignment=Alignment(horizontal="center",vertical="center",wrap_text=True); cell.border=_thin(); return cell
    def _cell(ws,r,c,v,bg="FFFFFF",fg="000000",bold=False,fmt=None,align="left"):
        cell=ws.cell(row=r,column=c,value=v); cell.fill=PatternFill("solid",start_color=bg)
        cell.font=Font(bold=bold,color=fg,name="Calibri",size=9)
        cell.alignment=Alignment(horizontal=align,vertical="center"); cell.border=_thin()
        if fmt: cell.number_format=fmt; return cell

    AGING_C={"0-15 Days":("FFFFFF","000000"),"16-30 Days":("FFFFFF","000000"),"31-45 Days":("FFF3CD","856404"),"46-60 Days":("FFE0B2","7B3F00"),"60+ Days (Critical)":("FFCDD2","B71C1C"),"Unknown":("F5F5F5","888888")}
    SS_C={"Yet to Bill":("E8F5E9","000000"),"Stock Not Available":("FFCDD2","000000"),"No Pending Qty":("F5F5F5","000000")}
    ACT_C={"🟢 BILL NOW — STOCK AVAILABLE":("E8F5E9","000000"),"🔴 URGENT — PO EXPIRING, BILL NOW":("FFCDD2","000000"),"🔴 PO EXPIRED — CONTACT CUSTOMER":("FFCDD2","000000"),"🟡 COMPLETE BILLING — PARTIAL DONE":("FFF9C4","000000"),"🔴 ARRANGE STOCK FIRST":("FFCDD2","000000"),"🟠 PARTIAL STOCK — BILL AVAILABLE QTY":("FFE0B2","000000"),"✅ No Action":("F5F5F5","000000"),"⚪ REVIEW":("F5F5F5","000000")}
    PRI_C={"P1 — PO Expired":("FFCDD2","000000"),"P1 — Expiring Soon":("FFCDD2","000000"),"P1 — Overdue 60+ Days":("FFCDD2","000000"),"P1 — Bill Now":("FFCDD2","000000"),"P1 — High Value":("FFCDD2","000000"),"P2 — Expiring in 30d":("FFE0B2","000000"),"P2 — Aging 31-60 Days":("FFE0B2","000000"),"P2 — Arrange Stock":("FFE0B2","000000"),"P2 — Medium Value":("FFF9C4","000000"),"P3 — Monitor":("FFFFFF","000000")}
    TF_C={"Transfer Needed":("FFCDD2","000000"),"Short Even with Transit":("FFE0B2","000000"),"Transit Covering Gap":("FFF9C4","000000"),"Sufficient":("FFFFFF","000000"),"No Sales Data":("F5F5F5","000000")}

    wb = Workbook()

    # Sheet 1: PO Tracker
    ws1=wb.active; ws1.title="PO Tracker"; ws1.sheet_properties.tabColor="1F4E79"
    DATE_C={"PO Date","PO Expiry","SO Date","Invoice date"}
    INT_C={"SO Qty","Invoice Qty.","Pending Qty.","Days Open","Stock","Days to PO Expiry"}
    FLOAT_C={"SO unit Price","Total SO Value","Inv Unit Price","Total Inv Value","Total Pending Value","ARP Price"}
    
    # Clean cols — remove unmapped SAP cols
    KEEP_COLS = ["Plant","Customer Name","Customer Code","PO No.","PO Date","PO Expiry","Pur.Grp.","Material","Desc.","Sales Order","SO Date","SO Qty","SO unit Price","Total SO Value","Invoice No.","Invoice date","Invoice Qty.","Inv Unit Price","Total Inv Value","Pending Qty.","Total Pending Value","Status","Branch Name","ARP Price","Stock","Champion Name","Courier Partner","Docket Number","POD Start Date","POD End Date","Ship City","Days Open","Days to PO Expiry","Aging Bucket","Stock Status","Priority","Action Required","Invoiced %"]
    export_df = df[[c for c in KEEP_COLS if c in df.columns]].copy()
    
    for col in DATE_C:
        if col in export_df.columns: export_df[col]=pd.to_datetime(export_df[col],errors="coerce").dt.strftime("%d-%b-%Y")
    cols=list(export_df.columns)
    for ri,rd in enumerate(dataframe_to_rows(export_df,index=False,header=True),1):
        for ci,v in enumerate(rd,1): ws1.cell(row=ri,column=ci,value=v)
    HC={"Priority":"B71C1C","Action Required":"B71C1C","Stock Status":"4E342E","Aging Bucket":"4E342E","Days to PO Expiry":"4E342E","Days Open":"4E342E","Branch Name":"2E7D32","ARP Price":"2E7D32","Stock":"2E7D32"}
    for cell in ws1[1]:
        bg=HC.get(cell.value,"1F4E79"); cell.fill=PatternFill("solid",start_color=bg)
        cell.font=Font(bold=True,color="FFFFFF",name="Calibri",size=9)
        cell.alignment=Alignment(horizontal="center",vertical="center",wrap_text=True); cell.border=_thin()
    ws1.row_dimensions[1].height=40
    def ci(n,c=cols): return c.index(n)+1 if n in c else None
    ss_c=ci("Stock Status");ar_c=ci("Action Required");ag_c=ci("Aging Bucket");ex_c=ci("Days to PO Expiry");pr_c=ci("Priority")
    int_ci=[ci(c) for c in INT_C if c in cols]; float_ci=[ci(c) for c in FLOAT_C if c in cols]
    for row in ws1.iter_rows(min_row=2,max_row=ws1.max_row):
        pr_v=str(row[pr_c-1].value) if pr_c else ""; rbg="FFF5F5" if pr_v.startswith("P1") else "FFFFFF"
        for cell in row:
            cell.font=Font(name="Calibri",size=9,color="000000"); cell.border=_thin()
            cell.alignment=Alignment(vertical="center"); cell.fill=PatternFill("solid",start_color=rbg)
            if cell.column in int_ci and cell.value is not None:
                try: cell.value=int(float(str(cell.value)))
                except: pass
                cell.number_format="#,##0"
            if cell.column in float_ci and cell.value is not None: cell.number_format="₹#,##0.00"
            if ss_c and cell.column==ss_c and cell.value:
                bg,fg=SS_C.get(str(cell.value),("FFFFFF","000000")); cell.fill=PatternFill("solid",start_color=bg); cell.font=Font(name="Calibri",size=9,bold=True,color=fg); cell.alignment=Alignment(horizontal="center",vertical="center")
            if ar_c and cell.column==ar_c and cell.value:
                bg,fg=ACT_C.get(str(cell.value),("FFFFFF","000000")); cell.fill=PatternFill("solid",start_color=bg); cell.font=Font(name="Calibri",size=9,bold=True,color=fg); cell.alignment=Alignment(horizontal="center",vertical="center")
            if ag_c and cell.column==ag_c and cell.value:
                bg,fg=AGING_C.get(str(cell.value),("FFFFFF","000000")); cell.fill=PatternFill("solid",start_color=bg); cell.font=Font(name="Calibri",size=9,color=fg); cell.alignment=Alignment(horizontal="center",vertical="center")
            if pr_c and cell.column==pr_c and cell.value:
                bg,fg=PRI_C.get(str(cell.value),("FFFFFF","000000")); cell.fill=PatternFill("solid",start_color=bg); cell.font=Font(name="Calibri",size=9,bold=True,color=fg); cell.alignment=Alignment(horizontal="center",vertical="center")
            if ex_c and cell.column==ex_c and cell.value is not None:
                try:
                    d=int(cell.value)
                    if d<0: cell.fill=PatternFill("solid",start_color="FFCDD2"); cell.font=Font(name="Calibri",size=9,bold=True,color="000000")
                    elif d<=PO_EXPIRY_ALERT: cell.fill=PatternFill("solid",start_color="FFE0B2"); cell.font=Font(name="Calibri",size=9,bold=True,color="000000")
                except: pass
    WD={"Plant":8,"Customer Name":22,"PO No.":14,"Pur.Grp.":8,"Material":20,"Desc.":28,"SO Qty":9,"Invoice Qty.":11,"Pending Qty.":11,"Total Pending Value":17,"Branch Name":16,"Stock":10,"Days Open":10,"Days to PO Expiry":15,"Aging Bucket":18,"Stock Status":17,"Priority":20,"Action Required":30,"Invoiced %":11,"Champion Name":18,"Courier Partner":18,"Docket Number":20}
    for i,c in enumerate(cols,1): ws1.column_dimensions[get_column_letter(i)].width=WD.get(c,13)
    ws1.freeze_panes="A2"; ws1.auto_filter.ref=ws1.dimensions

    # Sheet 2: Branch Transfer Planner
    ws2=wb.create_sheet("Branch Transfer Planner"); ws2.sheet_properties.tabColor="2E7D32"
    if bt and "branch_summary" in bt and bt["branch_summary"]:
        ws2.cell(row=1,column=1,value="BRANCH TRANSFER PLANNER").font=Font(bold=True,size=13,color="2E7D32",name="Calibri")
        ws2.cell(row=2,column=1,value=f"As of {date.today().strftime('%d %b %Y')} | Brand:{brand} | Sales:last {SALES_DAYS}d | Predict:{PREDICT_MONTHS}M").font=Font(size=9,italic=True,color="595959",name="Calibri")
        ws2.cell(row=4,column=1,value="SECTION 1 — BRANCH SUMMARY").font=Font(bold=True,size=11,color="2E7D32",name="Calibri")
        bs_hdrs=["Branch","Total SKUs","Need Transfer ⚠","Short+Transit","Transit OK","Sufficient","Stock (Units)","In Transit","Total Available","Predicted 3M Demand","Stock Gap"]
        for ci2,h in enumerate(bs_hdrs,1): _hdr(ws2,6,ci2,h,"2E7D32"); ws2.row_dimensions[6].height=30
        for ri2,row in enumerate(bt["branch_summary"],7):
            nt=int(row.get("need_transfer",0)); gap=float(row.get("stock_gap",0))
            vals=[row.get("Branch Name",""),int(row.get("total_skus",0)),nt,int(row.get("short_transit",0)),int(row.get("transit_ok",0)),int(row.get("sufficient",0)),int(row.get("stock",0)),int(row.get("in_transit",0)),int(row.get("total_available",0)),int(row.get("predicted_3m",0)),int(gap)]
            for ci2,v in enumerate(vals,1):
                if ci2==3 and nt>0: _cell(ws2,ri2,ci2,v,bg="FFCDD2",bold=True,fmt="#,##0" if ci2>1 else None,align="center" if ci2>1 else "left")
                elif ci2==3: _cell(ws2,ri2,ci2,v,bg="E8F5E9",fmt="#,##0" if ci2>1 else None,align="center" if ci2>1 else "left")
                elif ci2==11 and gap<0: _cell(ws2,ri2,ci2,v,bg="FFCDD2",bold=True,fmt="#,##0",align="center")
                elif ci2==11: _cell(ws2,ri2,ci2,v,bg="E8F5E9",fmt="#,##0",align="center")
                else: _cell(ws2,ri2,ci2,v,fmt="#,##0" if ci2>1 else None,align="left" if ci2==1 else "center")
            ws2.row_dimensions[ri2].height=20
        if bt.get("suggestions"):
            sec2=7+len(bt["branch_summary"])+2
            ws2.cell(row=sec2,column=1,value="SECTION 2 — TRANSFER SUGGESTIONS").font=Font(bold=True,size=11,color="B71C1C",name="Calibri")
            sug_hdrs=["Material","Needy Branch","Needy Stock","In Transit","Gap","Donor Branch","Donor Stock","Donor DOH","Suggest Xfer","After Xfer","Flag"]
            for ci2,h in enumerate(sug_hdrs,1): _hdr(ws2,sec2+2,ci2,h,"B71C1C"); ws2.row_dimensions[sec2+2].height=26
            for ri2,sug in enumerate(bt["suggestions"],sec2+3):
                vals=[sug.get("material",""),sug.get("needy_branch",""),int(sug.get("needy_stock",0)),int(sug.get("needy_transit",0)),int(sug.get("needy_gap",0)),sug.get("donor_branch",""),int(sug.get("donor_stock",0)),sug.get("donor_doh",0),int(sug.get("suggest_xfer_qty",0)),int(sug.get("after_xfer",0)),sug.get("flag","")]
                for ci2,v in enumerate(vals,1):
                    if ci2==11:
                        bg,fg=TF_C.get(str(v),("FFFFFF","000000")); _cell(ws2,ri2,ci2,v,bg=bg,fg=fg,bold=True,align="center")
                    else: _cell(ws2,ri2,ci2,v,fmt="#,##0" if ci2 in[3,4,5,7,9,10] else None,align="left" if ci2<=2 else "center")
                ws2.row_dimensions[ri2].height=18
        for c,w in [(1,22),(2,22),(3,14),(4,12),(5,12),(6,22),(7,14),(8,12),(9,14),(10,14),(11,22)]:
            ws2.column_dimensions[get_column_letter(c)].width=w
        ws2.freeze_panes="A7"

    # Sheet 3: Summary Dashboard
    ws3=wb.create_sheet("Summary Dashboard"); ws3.sheet_properties.tabColor="4E342E"
    pv=pd.to_numeric(df["Total Pending Value"],errors="coerce").fillna(0)
    pq2=pd.to_numeric(df["Pending Qty."],errors="coerce").fillna(0)
    sv2=pd.to_numeric(df["Total SO Value"],errors="coerce").fillna(0)
    iq=pd.to_numeric(df["Invoice Qty."],errors="coerce").fillna(0)
    soq2=pd.to_numeric(df["SO Qty"],errors="coerce").fillna(0)
    exp=pd.to_numeric(df.get("Days to PO Expiry",pd.Series(dtype=float)),errors="coerce")
    ss2=df.get("Stock Status",pd.Series(dtype=str))
    ws3["A1"]="PO TRACKER — EXECUTIVE SUMMARY"; ws3["A1"].font=Font(bold=True,size=15,color="1F4E79",name="Calibri")
    ws3["A2"]=f"As of {date.today().strftime('%d %b %Y')} | Brand:{brand} | Showing OPEN + PENDING orders only"
    ws3["A2"].font=Font(size=9,italic=True,color="888888",name="Calibri")
    kpis=[
        ("Total Open/Pending Orders",len(df),"orders","1F4E79"),
        ("Total Pending Value",pv.sum(),"₹","B71C1C"),
        ("Total Pending Qty",int(pq2.sum()),"units","2E7D32"),
        ("Total SO Value",sv2.sum(),"₹","1B5E20"),
        ("Overall Invoiced %",f"{iq.sum()/soq2.sum()*100:.1f}%" if soq2.sum() else "N/A","","4A148C"),
        ("─── BILLING STATUS ───","","","888888"),
        ("🟢 Yet to Bill (Stock Ready)",int((ss2=="Yet to Bill").sum()),"orders","1B5E20"),
        ("🟢 Value Can Bill TODAY",pv[ss2=="Yet to Bill"].sum(),"₹","1B5E20"),
        ("🟢 Qty Can Bill TODAY",int(pq2[ss2=="Yet to Bill"].sum()),"units","1B5E20"),
        ("🔴 Stock Not Available",int((ss2=="Stock Not Available").sum()),"orders","B71C1C"),
        ("─── PO EXPIRY ───","","","888888"),
        ("🔴 POs Already Expired",int((exp<0).sum()),"POs","B71C1C"),
        (f"🔴 Expiring ≤{PO_EXPIRY_ALERT} Days",int(((exp>=0)&(exp<=PO_EXPIRY_ALERT)).sum()),"POs","C62828"),
        ("🟡 Expiring in 6-30 Days",int(((exp>PO_EXPIRY_ALERT)&(exp<=30)).sum()),"POs","827717"),
        ("─── AGING ───","","","888888"),
        ("⚠ Critical 60+ Days Open",int((pd.to_numeric(df["Days Open"],errors="coerce").fillna(0)>60).sum()),"orders","B71C1C"),
        ("─── BRANCH TRANSFER ───","","","888888"),
        ("Branches Needing Transfer",sum(b.get("need_transfer",0) for b in bt.get("branch_summary",[])) if bt else 0,"branches","B71C1C"),
    ]
    for i,(label,value,unit,color) in enumerate(kpis,4):
        if label.startswith("───"):
            cell=ws3.cell(row=i,column=1,value=label); cell.font=Font(name="Calibri",size=9,bold=True,color=color,italic=True)
            cell.fill=PatternFill("solid",start_color="F5F5F5"); cell.border=_thin()
            ws3.merge_cells(start_row=i,start_column=1,end_row=i,end_column=3); ws3.row_dimensions[i].height=16; continue
        vs=f"₹{value:,.0f}" if unit=="₹" and isinstance(value,float) else str(value)
        _cell(ws3,i,1,label,bg="FAFAFA",fg="444444"); _cell(ws3,i,2,vs,bg="FFFFFF",fg="000000",bold=True,align="center"); _cell(ws3,i,3,unit if unit not in["₹",""] else "",bg="FAFAFA",fg="888888")
        ws3.cell(row=i,column=1).font=Font(name="Calibri",size=10,color="444444")
        ws3.cell(row=i,column=2).font=Font(name="Calibri",size=13,bold=True,color=color); ws3.row_dimensions[i].height=24
    ws3.column_dimensions["A"].width=32; ws3.column_dimensions["B"].width=18; ws3.column_dimensions["C"].width=12

    buf=io.BytesIO(); wb.save(buf); buf.seek(0)
    return buf

@po_tracker_bp.route("/report", methods=["GET"])
def get_report():
    brand   = request.args.get("brand","JBL").upper()
    today   = request.args.get("date", date.today().strftime("%Y%m%d"))
    from_dt = request.args.get("from", _one_month_ago())
    try:
        logger.info("[PO Tracker] brand=%s from=%s to=%s", brand, from_dt, today)
        bos_raw = fetch_bos(brand, from_dt, today)
        if bos_raw.empty: return jsonify({"error": f"No data for brand {brand}"}), 404
        bos = map_bos_cols(bos_raw)
        stk = fetch_stock(brand, today)
        sal = fetch_sales(brand, today)
        tr  = fetch_transit(brand, today)
        df  = enrich(bos, stk, sal)
        if df.empty: return jsonify({"error": "No OPEN/PENDING POs found for selected date range"}), 404
        bt  = build_branch_transfer(stk, sal, tr)
        summary = build_summary(df, bt)
        summary["brand"] = brand; summary["as_of"] = date.today().strftime("%d %b %Y")
        return jsonify({"status":"success","summary":summary,"branch_transfer":bt if bt else {}})
    except Exception as e:
        logger.error("[PO Tracker] Error: %s", e, exc_info=True)
        return jsonify({"error": str(e)}), 500

@po_tracker_bp.route("/download", methods=["GET"])
def download_excel():
    brand   = request.args.get("brand","JBL").upper()
    today   = request.args.get("date", date.today().strftime("%Y%m%d"))
    from_dt = request.args.get("from", _one_month_ago())
    try:
        bos_raw = fetch_bos(brand, from_dt, today)
        if bos_raw.empty: return jsonify({"error": f"No data for {brand}"}), 404
        bos = map_bos_cols(bos_raw)
        stk = fetch_stock(brand, today)
        sal = fetch_sales(brand, today)
        tr  = fetch_transit(brand, today)
        df  = enrich(bos, stk, sal)
        bt  = build_branch_transfer(stk, sal, tr)
        buf = generate_excel(df, bt, brand)
        fname = f"PO_Tracker_{brand}_{date.today().strftime('%d%b%Y')}.xlsx"
        return send_file(buf, as_attachment=True, download_name=fname,
                         mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    except Exception as e:
        logger.error("[PO Tracker] Download error: %s", e, exc_info=True)
        return jsonify({"error": str(e)}), 500

@po_tracker_bp.route("/brands", methods=["GET"])
def get_brands():
    from data.master_data import PURCHASING_GROUPS
    return jsonify([{"value":k,"label":f"{k} — {v}"} for k,v in sorted(PURCHASING_GROUPS.items())])