#!/usr/bin/env python3
from __future__ import annotations
import os, sys, json, time, math, csv
from datetime import datetime, timedelta
from zoneinfo import ZoneInfo
from pathlib import Path
import requests
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.formatting.rule import ColorScaleRule

MSK=ZoneInfo('Europe/Moscow')
TOKEN=os.environ.get('WB_ANALYTICS_TOKEN') or os.environ.get('WB_API_KEY') or os.environ.get('WB_TOKEN') or os.environ.get('WB_ANALYTICS_JWT') or os.environ.get('WB_JWT')
if not TOKEN:
    print('ERROR: WB token env not found', file=sys.stderr); sys.exit(3)
URL='https://seller-analytics-api.wildberries.ru/api/analytics/v3/sales-funnel/products'
OUTDIR=Path('/var/www/hermes-artifacts/wb-drop-report'); OUTDIR.mkdir(parents=True, exist_ok=True)

def log(s): print(f'[{datetime.now(MSK):%H:%M:%S}] {s}', file=sys.stderr, flush=True)

def pct(sel, past):
    if past in (None,0): return None if not sel else math.inf
    return (sel-past)/past*100

def safe_num(x): return x if isinstance(x,(int,float)) else 0

def fetch_products(start, end, limit=1000):
    products=[]; offset=0
    while True:
        body={'selectedPeriod':{'start':start,'end':end},'limit':limit,'offset':offset}
        for a in range(5):
            r=requests.post(URL,headers={'Authorization':TOKEN,'Content-Type':'application/json'},json=body,timeout=90)
            if r.status_code==429:
                wait=60 if a else 20; log(f'429 products offset={offset}; sleep {wait}s'); time.sleep(wait); continue
            if r.status_code>=500:
                log(f'HTTP {r.status_code} offset={offset}: {r.text[:180]}'); time.sleep(5*(a+1)); continue
            break
        if r.status_code in (401,403): raise RuntimeError(f'Auth {r.status_code}: {r.text[:300]}')
        if r.status_code!=200: raise RuntimeError(f'HTTP {r.status_code}: {r.text[:500]}')
        data=r.json().get('data') or {}
        batch=data.get('products') or []
        products.extend(batch)
        log(f'products offset={offset}: +{len(batch)} total={len(products)}')
        if len(batch)<limit: break
        offset += limit
        time.sleep(20)
    return products

def unpack(row):
    p=row.get('product') or {}; st=p.get('stocks') or {}
    s=((row.get('statistic') or {}).get('selected') or {})
    past=((row.get('statistic') or {}).get('past') or {})
    comp=((row.get('statistic') or {}).get('comparison') or {})
    conv_s=s.get('conversions') or {}; conv_p=past.get('conversions') or {}
    stock_wb=safe_num(st.get('wb')); stock_mp=safe_num(st.get('mp')); balance=safe_num(st.get('balanceSum'))
    order_delta=pct(safe_num(s.get('orderCount')), safe_num(past.get('orderCount')))
    rev_delta=pct(safe_num(s.get('orderSum')), safe_num(past.get('orderSum')))
    open_delta=pct(safe_num(s.get('openCount')), safe_num(past.get('openCount')))
    cart_delta=pct(safe_num(s.get('cartCount')), safe_num(past.get('cartCount')))
    # first weak point: earliest/factual funnel metric that is <= -20%, conversions use pp <= -5
    first=''
    add_pp=safe_num(conv_s.get('addToCartPercent'))-safe_num(conv_p.get('addToCartPercent'))
    cto_pp=safe_num(conv_s.get('cartToOrderPercent'))-safe_num(conv_p.get('cartToOrderPercent'))
    for name, val, kind in [('показы',open_delta,'%'),('CR показ→корзина',add_pp,'pp'),('корзина',cart_delta,'%'),('CR корзина→заказ',cto_pp,'pp'),('заказы',order_delta,'%'),('выручка',rev_delta,'%')]:
        if kind=='%' and val is not None and val != math.inf and val <= -20: first=name; break
        if kind=='pp' and val <= -5: first=name; break
    return {
        'Бренд':p.get('brandName') or '', 'nmID':p.get('nmId'), 'Артикул продавца':p.get('vendorCode') or '',
        'Название':p.get('title') or '', 'Предмет':p.get('subjectName') or '',
        'Остаток WB':stock_wb, 'Остаток MP':stock_mp, 'Остаток всего':stock_wb+stock_mp, 'Остаток ₽':balance,
        'Заказы 7д':safe_num(s.get('orderCount')), 'Заказы прошл.7д':safe_num(past.get('orderCount')), 'Δ заказов %':order_delta,
        'Выручка 7д ₽':safe_num(s.get('orderSum')), 'Выручка прошл.7д ₽':safe_num(past.get('orderSum')), 'Δ выручки %':rev_delta,
        'Показы 7д':safe_num(s.get('openCount')), 'Показы прошл.7д':safe_num(past.get('openCount')), 'Δ показов %':open_delta,
        'Корзины 7д':safe_num(s.get('cartCount')), 'Корзины прошл.7д':safe_num(past.get('cartCount')), 'Δ корзин %':cart_delta,
        'CR показ→корзина %':safe_num(conv_s.get('addToCartPercent')), 'Δ CR показ→корзина п.п.':add_pp,
        'CR корзина→заказ %':safe_num(conv_s.get('cartToOrderPercent')), 'Δ CR корзина→заказ п.п.':cto_pp,
        'Отмены 7д':safe_num(s.get('cancelCount')), 'Рейтинг товара':p.get('productRating'), 'Рейтинг отзывов':p.get('feedbackRating'),
        'Первый слабый этап':first, 'Ссылка WB':f"https://www.wildberries.ru/catalog/{p.get('nmId')}/detail.aspx",
    }

def fmt_pct(v):
    if v is None: return '—'
    if v == math.inf: return '+∞'
    return f'{v:+.1f}%'

def build_xlsx(rows, all_rows, summary, path):
    wb=Workbook(); wb.remove(wb.active)
    header_fill=PatternFill('solid', fgColor='1F2937'); header_font=Font(color='FFFFFF', bold=True)
    red=PatternFill('solid', fgColor='FEE2E2'); yellow=PatternFill('solid', fgColor='FEF3C7')
    green=PatternFill('solid', fgColor='DCFCE7'); thin=Side(style='thin', color='D1D5DB'); border=Border(left=thin,right=thin,top=thin,bottom=thin)
    ws=wb.create_sheet('Сводка')
    ws['A1']='WB: карточки с остатками и снижением продаж'; ws['A1'].font=Font(size=16,bold=True)
    ws.merge_cells('A1:D1')
    lines=[('Период анализа', f"{summary['selected_start']} — {summary['selected_end']}"),('Сравнение с', f"{summary['past_start']} — {summary['past_end']}"),('Всего строк API', summary['api_products']),('Карточек с остатком > 0', summary['with_stock']),('Карточек с остатком и снижением продаж ≥20%', summary['drops_with_stock']),('Порог', 'снижение заказов или выручки на 20%+ к прошлым 7 дням')]
    for i,(k,v) in enumerate(lines,3): ws.cell(i,1,k).font=Font(bold=True); ws.cell(i,2,v)
    # by brand
    brand={}
    for r in rows:
        b=r['Бренд'] or '—'; brand.setdefault(b, {'cnt':0,'rev_loss':0,'orders_loss':0}); brand[b]['cnt']+=1; brand[b]['rev_loss']+=r['Выручка 7д ₽']-r['Выручка прошл.7д ₽']; brand[b]['orders_loss']+=r['Заказы 7д']-r['Заказы прошл.7д']
    ws.cell(11,1,'Бренд'); ws.cell(11,2,'SKU просадок'); ws.cell(11,3,'Δ выручки ₽'); ws.cell(11,4,'Δ заказов')
    for c in range(1,5): ws.cell(11,c).fill=header_fill; ws.cell(11,c).font=header_font
    for i,(b,a) in enumerate(sorted(brand.items(), key=lambda kv: kv[1]['rev_loss']),12):
        ws.cell(i,1,b); ws.cell(i,2,a['cnt']); ws.cell(i,3,a['rev_loss']); ws.cell(i,4,a['orders_loss'])
    for col in range(1,5): ws.column_dimensions[get_column_letter(col)].width=24
    fields=list(rows[0].keys()) if rows else ['Бренд','nmID','Название']
    for name, data in [('Просадки с остатками', rows), ('Все карточки API', all_rows)]:
        sh=wb.create_sheet(name)
        for c,h in enumerate(fields,1):
            cell=sh.cell(1,c,h); cell.fill=header_fill; cell.font=header_font; cell.alignment=Alignment(horizontal='center',wrap_text=True); cell.border=border
        for r_i,row in enumerate(data,2):
            for c,h in enumerate(fields,1):
                v=row.get(h)
                if isinstance(v,float) and (math.isinf(v) or math.isnan(v)): v=None
                cell=sh.cell(r_i,c,v); cell.border=border; cell.alignment=Alignment(vertical='top', wrap_text=(h in ('Название','Ссылка WB')))
                if h in ('Δ заказов %','Δ выручки %','Δ показов %','Δ корзин %') and isinstance(v,(int,float)):
                    if v<=-20: cell.fill=red
                    elif v>=20: cell.fill=green
                if h.startswith('Δ CR') and isinstance(v,(int,float)) and v<=-5: cell.fill=red
            if row.get('Остаток всего',0)<=0:
                for c in range(1,len(fields)+1): sh.cell(r_i,c).fill=yellow
        if data:
            ref=f"A1:{get_column_letter(len(fields))}{len(data)+1}"
            tab=Table(displayName=('DropsTable' if name.startswith('Просадки') else 'AllTable'), ref=ref)
            tab.tableStyleInfo=TableStyleInfo(name='TableStyleMedium2', showRowStripes=True, showColumnStripes=False)
            sh.add_table(tab)
            for col_name in ('Δ заказов %','Δ выручки %'):
                idx=fields.index(col_name)+1; letter=get_column_letter(idx)
                sh.conditional_formatting.add(f'{letter}2:{letter}{len(data)+1}', ColorScaleRule(start_type='num',start_value=-100,start_color='B91C1C',mid_type='num',mid_value=0,mid_color='FFFFFF',end_type='num',end_value=100,end_color='15803D'))
        sh.freeze_panes='F2'; sh.auto_filter.ref=f"A1:{get_column_letter(len(fields))}{max(1,len(data)+1)}"
        widths={'Название':42,'Ссылка WB':44,'Бренд':16,'Артикул продавца':24,'Предмет':22,'Первый слабый этап':22}
        for i,h in enumerate(fields,1): sh.column_dimensions[get_column_letter(i)].width=widths.get(h,14)
    wb.save(path)

def main():
    today=datetime.now(MSK).date(); selected_end=today-timedelta(days=1); selected_start=selected_end-timedelta(days=6); past_end=selected_start-timedelta(days=1); past_start=past_end-timedelta(days=6)
    log(f'Selected {selected_start}..{selected_end}; past {past_start}..{past_end}')
    products=fetch_products(selected_start.isoformat(), selected_end.isoformat())
    all_rows=[unpack(x) for x in products]
    with_stock=[r for r in all_rows if (r['Остаток всего'] or 0)>0]
    drops=[]
    for r in with_stock:
        od=r['Δ заказов %']; rd=r['Δ выручки %']
        base_ok=(r['Заказы прошл.7д']>0 or r['Выручка прошл.7д ₽']>0)
        if base_ok and ((od is not None and od != math.inf and od<=-20) or (rd is not None and rd != math.inf and rd<=-20)):
            drops.append(r)
    drops.sort(key=lambda r: (r['Выручка 7д ₽']-r['Выручка прошл.7д ₽']))
    summary={'generated_at':datetime.now(MSK).isoformat(timespec='seconds'),'selected_start':selected_start.isoformat(),'selected_end':selected_end.isoformat(),'past_start':past_start.isoformat(),'past_end':past_end.isoformat(),'api_products':len(products),'with_stock':len(with_stock),'drops_with_stock':len(drops)}
    json_path=OUTDIR/'wb_products_drop_report.json'; csv_path=OUTDIR/'wb_products_drop_report.csv'; xlsx_path=OUTDIR/f"WB_карточки_с_остатками_просадка_{selected_start:%d%m}_{selected_end:%d%m}.xlsx"
    with open(json_path,'w',encoding='utf-8') as f: json.dump({'summary':summary,'drops':drops,'all_rows':all_rows},f,ensure_ascii=False,indent=2)
    fields=list(drops[0].keys()) if drops else (list(all_rows[0].keys()) if all_rows else [])
    with open(csv_path,'w',encoding='utf-8-sig',newline='') as f:
        w=csv.DictWriter(f,fieldnames=fields); w.writeheader(); w.writerows(drops)
    build_xlsx(drops, all_rows, summary, xlsx_path)
    # verification readback
    wb=load_workbook(xlsx_path, read_only=True, data_only=False)
    verify={'sheets': wb.sheetnames, 'rows_drops_sheet': wb['Просадки с остатками'].max_row-1 if 'Просадки с остатками' in wb.sheetnames else None}
    print(json.dumps({'summary':summary,'json':str(json_path),'csv':str(csv_path),'xlsx':str(xlsx_path),'verify':verify,'top15':drops[:15]},ensure_ascii=False,indent=2))
if __name__=='__main__': main()