"""WEB sales Excel — prodaja files (country-exact) + DB gap fill for May 17-24."""
import sys, os, glob
sys.stdout.reconfigure(encoding="utf-8")
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
import pandas as pd
from pathlib import Path
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from sqlalchemy import text
from backend.repositories.upload_repo import parse_excel_file
from backend.models.database import SessionLocal

WEB_TIPS = {"WSA","WSB","WSC","WSD"}
PRODAJA = {"cro":"HR","slo":"SI","asutri":"AT","austri":"AT","avstr":"AT"}
MONTHS = ["Mar","Apr","May"]
COUNTRIES = ["HR","SI","AT"]

# ── styling ──
F="Arial"
def font(bold=False,color="000000"): return Font(name=F,size=10,bold=bold,color=color)
HEAD=Font(name=F,size=10,bold=True,color="FFFFFF")
HFILL=PatternFill("solid",fgColor="1F3864"); TFILL=PatternFill("solid",fgColor="D9E1F2")
NFILL=PatternFill("solid",fgColor="FFF2CC")
thin=Side(style="thin",color="BFBFBF"); BORD=Border(left=thin,right=thin,top=thin,bottom=thin)
EUR='#,##0;(#,##0);"-"'; NUM='#,##0;(#,##0);"-"'
def hrow(ws,r,cols):
    for i,l in enumerate(cols):
        c=ws.cell(r,i+1,l); c.font=HEAD; c.fill=HFILL
        c.alignment=Alignment(horizontal="left" if i==0 else "center"); c.border=BORD
def brow(ws,r,n):
    for i in range(n): ws.cell(r,i+1).border=BORD

# ── load prodaja files (country-exact, Mar-May 16) ──
frames=[]
for fp in sorted(glob.glob("data/prodaja/*.xlsx")):
    fn=os.path.basename(fp).lower()
    country=next((v for k,v in PRODAJA.items() if k in fn),None)
    if not country: continue
    df=parse_excel_file(Path(fp).read_bytes(),filename=fn)["df"]
    df=df[df["tip"].isin(WEB_TIPS)].copy()
    df["country"]=country
    df["ruc"]=pd.to_numeric(df["ruc"],errors="coerce").fillna(0)
    frames.append(df)
prodaja_df=pd.concat(frames,ignore_index=True)
prodaja_df["month"]=prodaja_df["date"].dt.month.map({3:"Mar",4:"Apr",5:"May"})
prodaja_max_may=prodaja_df[prodaja_df["month"]=="May"]["date"].max()
print(f"  Prodaja May coverage: up to {prodaja_max_may.date()}")

# ── load DB gap: May 17-24, all channels WSA-WSD ──
db=SessionLocal()
# canonical SKU catalog
cat_rows=db.execute(text("""SELECT dp.sku,dp.name,COALESCE(dc.name,'(uncat)') cat
    FROM dim_products dp LEFT JOIN dim_categories dc ON dp.category_id=dc.id""")).fetchall()
catalog={r[0]:(r[1],r[2]) for r in cat_rows}

gap_rows=db.execute(text("""
    SELECT t.transaction_date dt, dp.sku, t.quantity qty,
           COALESCE(t.ruc_eur,0) ruc, t.total_value val, cm.doc_type tip
    FROM erp_transactions t
    JOIN lookup_channel_map cm ON t.channel_map_id=cm.id
    JOIN dim_products dp ON t.product_id=dp.id
    WHERE cm.doc_type = ANY(:tips)
      AND t.transaction_date BETWEEN '2026-05-17' AND '2026-05-24'
"""),{"tips":list(WEB_TIPS)}).fetchall()
db.close()

gap_df=pd.DataFrame(gap_rows,columns=["date","sku","qty","ruc","val","tip"])
gap_df["ruc"]=gap_df["ruc"].astype(float)
gap_df["qty"]=gap_df["qty"].astype(float)
gap_df["val"]=gap_df["val"].astype(float)
gap_df["month"]="May"
gap_df["country"]="HR*"  # DB has no country; ~99% HR, flagged with *
gap_df["name"]=gap_df["sku"].map({k:v[0] for k,v in catalog.items()})
gap_df["cat"]=gap_df["sku"].map({k:v[1] for k,v in catalog.items()})
print(f"  DB gap rows (May 17-24): {len(gap_df)}, RUC €{gap_df['ruc'].sum():,.0f}")

# apply canonical names/cats to prodaja too
prodaja_df["name"]=prodaja_df["sku"].map({k:v[0] for k,v in catalog.items()}).fillna(prodaja_df["name"])
prodaja_df["cat"]=prodaja_df["sku"].map({k:v[1] for k,v in catalog.items()}).fillna(prodaja_df["cat"])

# ── combined: prodaja for country sheets, gap for totals ──
# For all-countries totals: use prodaja + gap (no double-count — gap starts May 17)
all_df=pd.concat([prodaja_df.rename(columns={"value":"val"})[["date","sku","name","cat","qty","ruc","val","month","country"]],
                  gap_df[["date","sku","name","cat","qty","ruc","val","month","country"]]],ignore_index=True)

# For per-country: prodaja only (country-exact) + gap as HR*
country_df=all_df.copy()  # same — HR* is in there

wb=Workbook()

# ── Sheet 1: By Month (all countries) ──
ws=wb.active; ws.title="By Month"; ws.sheet_view.showGridLines=False
ws["A1"]="WEB Sales by Month — all countries (RUC €)"; ws["A1"].font=Font(name=F,size=13,bold=True,color="1F3864")
ws["A2"]="May = prodaja May 1-16 + DB May 17-24 (combined). Mar/Apr from prodaja only."
ws["A2"].font=Font(name=F,size=9,italic=True,color="595959"); ws.merge_cells("A2:D2")
hrow(ws,3,["Month","RUC €","Revenue €","Units"])
r=4
for m in MONTHS:
    d=all_df[all_df["month"]==m]
    ws.cell(r,1,m+"*" if m=="May" else m).font=font(bold=True)
    ws.cell(r,2,round(float(d["ruc"].sum()),0)).number_format=EUR
    ws.cell(r,3,round(float(d["val"].sum()),0)).number_format=EUR
    ws.cell(r,4,round(float(d["qty"].sum()),0)).number_format=NUM
    for cc in range(2,5): ws.cell(r,cc).font=font(); brow(ws,r,4); r+=1
ws.cell(r,1,"TOTAL").font=font(bold=True)
for cc,f in [(2,f"=SUM(B4:B{r-1})"),(3,f"=SUM(C4:C{r-1})"),(4,f"=SUM(D4:D{r-1})")]:
    ws.cell(r,cc,f).number_format=EUR if cc<4 else NUM
    ws.cell(r,cc).font=font(bold=True); ws.cell(r,cc).fill=TFILL; ws.cell(r,cc).border=BORD
ws.cell(r,1).fill=TFILL; ws.cell(r,1).border=BORD
for col,w in zip("ABCD",[10,14,14,10]): ws.column_dimensions[col].width=w

# ── Sheet 2: By Month × Country ──
ws=wb.create_sheet("By Month x Country"); ws.sheet_view.showGridLines=False
ws["A1"]="WEB Sales by Month and Country (RUC €)"; ws["A1"].font=Font(name=F,size=13,bold=True,color="1F3864")
note="HR* = DB May 17-24 (no country in DB, assumed HR). All other cells = prodaja source files (country exact)."
ws["A2"]=note; ws["A2"].font=Font(name=F,size=9,italic=True,color="833C00")
ws.merge_cells(f"A2:{chr(64+1+len(COUNTRIES)*2+2)}2")
all_c=COUNTRIES+["HR*"]
cols=["Month"]+[f"{c} RUC €" for c in all_c]+["TOTAL RUC €"]
hrow(ws,3,cols)
r=4
for m in MONTHS:
    dm=country_df[country_df["month"]==m]
    ws.cell(r,1,m).font=font()
    col=2
    for c in all_c:
        dc=dm[dm["country"]==c]
        ws.cell(r,col,round(float(dc["ruc"].sum()),0) or None).number_format=EUR
        ws.cell(r,col).font=font()
        if c=="HR*": ws.cell(r,col).fill=NFILL
        col+=1
    ws.cell(r,col,round(float(dm["ruc"].sum()),0)).number_format=EUR; ws.cell(r,col).font=font(bold=True)
    brow(ws,r,len(cols)); r+=1
ws.cell(r,1,"TOTAL").font=font(bold=True)
for cc in range(2,len(cols)+1):
    ws.cell(r,cc,f"=SUM({chr(64+cc)}4:{chr(64+cc)}{r-1})").number_format=EUR
    ws.cell(r,cc).font=font(bold=True); ws.cell(r,cc).fill=TFILL; ws.cell(r,cc).border=BORD
ws.cell(r,1).fill=TFILL; ws.cell(r,1).border=BORD
for i,w in enumerate([10]+[13]*len(all_c)+[14]): ws.column_dimensions[chr(65+i)].width=w

# ── Sheet 3: All Articles ──
ws=wb.create_sheet("All Articles"); ws.sheet_view.showGridLines=False
ws["A1"]="All WEB Articles — RUC by month (all countries combined)"; ws["A1"].font=Font(name=F,size=13,bold=True,color="1F3864")
ws["A2"]="May = prodaja May 1-16 + DB May 17-24. Sorted by May RUC desc."
ws["A2"].font=Font(name=F,size=9,italic=True,color="595959"); ws.merge_cells("A2:H2")
acols=["SKU","Article","Category","Mar RUC €","Apr RUC €","May RUC €","Apr Units","May Units"]
hrow(ws,3,acols)
art=all_df.groupby(["sku","name","cat","month"]).agg(ruc=("ruc","sum"),qty=("qty","sum")).reset_index()
pivot=art.pivot_table(index=["sku","name","cat"],columns="month",values=["ruc","qty"],aggfunc="sum",fill_value=0).reset_index()
pivot.columns=["_".join(c).strip("_") if c[1] else c[0] for c in pivot.columns]
pivot=pivot.sort_values("ruc_May",ascending=False)
r=4
for _,row in pivot.iterrows():
    ws.cell(r,1,row["sku"]); ws.cell(r,2,str(row["name"])[:42]); ws.cell(r,3,str(row["cat"]))
    for cc in range(1,4): ws.cell(r,cc).font=font()
    for cc,val,fmt in [
        (4,row.get("ruc_Mar",0),EUR),(5,row.get("ruc_Apr",0),EUR),(6,row.get("ruc_May",0),EUR),
        (7,row.get("qty_Apr",0),NUM),(8,row.get("qty_May",0),NUM)]:
        v=round(float(val),0); ws.cell(r,cc,v if v else None).number_format=fmt; ws.cell(r,cc).font=font()
    brow(ws,r,8); r+=1
ws.freeze_panes="A4"; ws.auto_filter.ref="A3:H3"
for col,w in zip("ABCDEFGH",[11,42,18,12,12,12,10,10]): ws.column_dimensions[col].width=w

wb.calculation.fullCalcOnLoad=True
out="WEB_Quick_May2026.xlsx"
wb.save(out)
print(f"saved {out}")