data_loader.py

"""Data loader for STM dashboard"""
import os
from collections import defaultdict

def pn(s):
    try: return float((s or '').strip().replace(',','.').replace('\xa0','').replace('"',''))
    except: return 0.0

def load_all():
    base = os.path.dirname(__file__)
    # Priority: stm5 > stm4 > old
    for fname in ['stm5_utf8.csv', 'stm4_utf8.csv', 'new_table_utf8.csv']:
        csv_file = os.path.join(base, fname)
        if os.path.exists(csv_file):
            print(f"📂 Loading: {fname}")
            break

    with open(csv_file, 'r', encoding='utf-8-sig') as f:
        lines = f.readlines()

    # Detect format by column count in data row
    sample = lines[3].strip().split('\t') if len(lines) > 3 else []
    ncols = len(sample)
    print(f"   Columns: {ncols}")

    pm = defaultdict(lambda:{'d':'','sg':'','cat':'','c':'','n':'','sup':'','ct':'',
        's':0.0,'r':0.0,'ch':0,'pr':0.0,'bon':0.0,'sl':[]})
    suppliers, contracts = set(), set()
    for line in lines[3:]:
        cols = line.rstrip('\n\r').split('\t')
        if len(cols) < 12: continue
        d,sg,cat,c,n = cols[0].strip(),cols[1].strip(),cols[2].strip(),cols[3].strip(),cols[4].strip()
        sup,ct = cols[5].strip().strip('"'),cols[6].strip().strip('"')
        if not c or not d or not sg or not cat: continue
        if 'итог' in d.lower() or 'итог' in c.lower(): continue
        if 'доставка' in d.lower(): continue
        if 'отдел заказов' in d.lower(): continue
        if d == '(Пусто)': continue
        if '(У)' in n or '(у)' in n: continue
        if sup == '(Пусто)': continue
        # Merge 10А and 10Б into one department
        if d.startswith('10А.') or d.startswith('10Б.'):
            d = '10А. Сезонные товары'

        s = pn(cols[7])   # Sales qty
        r = pn(cols[8])   # Revenue
        ch = int(pn(cols[10]))  # Checks

        if ncols >= 19:
            # stm5: profit from file, bonus col 11
            pr = pn(cols[9])
            bon = pn(cols[11])
        elif ncols >= 17:
            # stm4: no cost column
            pr = pn(cols[9])
            bon = pn(cols[11])
            cost = 0.0
        else:
            # old format
            ch = int(pn(cols[9]))
            pr = pn(cols[10])
            bon = 0.0
            cost = 0.0

        if sup: suppliers.add(sup)
        if ct: contracts.add(ct)
        p = pm[c]
        p['s'] += s; p['r'] += r; p['ch'] += ch; p['pr'] += pr
        p['bon'] += bon
        if r > p.get('mr', 0): p['mr'] = r; p['sup'] = sup; p['ct'] = ct
        if not p['c']: p['d'],p['sg'],p['cat'],p['c'],p['n'] = d,sg,cat,c,n[:150]
        if sup and r > 0: p['sl'].append({'name':sup,'contract':ct,'revenue':r})

    products = []
    ds = defaultdict(lambda:{'r':0,'pr':0,'s':0,'ch':0,'cnt':0,'bon':0})
    sm, cm = defaultdict(set), defaultdict(set)
    for c, p in pm.items():
        if not p['d'] or p['d'] in ('(Пусто)', 'Прочие', 'Смесители для ванной комнаты'): continue
        m = round((p['pr'] + p['bon']) / max(p['r'] + p['bon'], 0.01) * 100, 1)
        # Price = (Revenue + Bonuses) / Sales
        price = round((p['r'] + p['bon']) / p['s'], 2) if p['s'] > 0 else 0.0
        products.append({'id':len(products),'dept':p['d'],'subgroup':p['sg'],'category':p['cat'],
            'code':c,'name':p['n'],'supplier':p['sup'],'contract_type':p['ct'],
            'sales':round(p['s'],1),'revenue':round(p['r'],2),'checks':p['ch'],
            'profit':round(p['pr'],2),'margin':m,'price':price,
            'suppliers_count':len(p['sl'])})
        ds[p['d']]['r'] += p['r']; ds[p['d']]['pr'] += p['pr']; ds[p['d']]['s'] += p['s']
        ds[p['d']]['ch'] += p['ch']; ds[p['d']]['cnt'] += 1
        ds[p['d']]['bon'] += p['bon']
        sm[p['d']].add(p['sg']); cm[f"{p['d']}|||{p['sg']}"].add(p['cat'])

    hierarchy = {}
    departments = []
    for d in sorted(ds.keys()):
        hierarchy[d] = {sg:sorted(cm[f'{d}|||{sg}']) for sg in sorted(sm[d])}
        ma = (ds[d]['pr'] / ds[d]['r'] * 100) if ds[d]['r'] > 0 else 0
        departments.append({'name':d,'revenue':round(ds[d]['r']),'profit':round(ds[d]['pr']),
            'sales':round(ds[d]['s']),'checks':ds[d]['ch'],'count':ds[d]['cnt'],'margin':round(ma,1)})

    tr = sum(x['revenue'] for x in departments)
    tp = sum(x['profit'] for x in departments)
    summary = {
        'total_products':len(products),'total_revenue':round(tr),'total_profit':round(tp),
        'total_sales':round(sum(x['sales'] for x in departments)),
        'total_checks':sum(x['checks'] for x in departments),
        'avg_margin':round(tp/max(tr,1)*100,1),'departments_count':len(departments),
        'suppliers_count':len(suppliers),'contract_types':sorted(contracts)
    }
    print(f"✅ {len(products):,} products, {len(departments)} depts, {len(suppliers)} suppliers")
    return products, departments, hierarchy, summary, sorted(suppliers), sorted(contracts)