# -*- coding: utf-8 -*-
"""Build App_MuaHang_TuDong.html — nhúng SheetJS + dữ liệu mẫu T8 từ Excel."""
import json, openpyxl

import os
BASE = os.path.dirname(os.path.abspath(__file__))
PARENT_DIR = os.path.abspath(os.path.join(BASE, ".."))
TPL = os.path.join(BASE, "template_app.html")
LIB = os.path.join(BASE, "xlsx.full.min.js")
OUT = os.path.join(BASE, "App_MuaHang_TuDong.html")
SRC = os.path.join(PARENT_DIR, "MUA HÀNG THÁNG 8-26.xlsx")

# ── Trích dữ liệu mẫu từ Excel (cùng logic với app JS) ──
DAYS_FULL = {1:31,2:28,3:31,4:30,5:31,6:30,7:31,8:31,9:30,10:31,11:30,12:31}

def pnum(v):
    if v is None: return None
    if isinstance(v, (int, float)): return v
    s = str(v).strip().replace(',','').replace(' ','')
    if s in ('','-','—'): return None
    try: return float(s)
    except: return None

wb = openpyxl.load_workbook(SRC, data_only=True)
sn = next((s for s in wb.sheetnames if 'FILE' in s.upper()), max(wb.sheetnames, key=lambda s: wb[s].max_row))
ws = wb[sn]

# tìm header row
hr = None
for i in range(min(15, ws.max_row)):
    a = ws.cell(i+1, 1).value
    if a is not None and str(a).strip().lower() == 'stt':
        hr = i  # 0-based
        break
assert hr is not None, 'Không tìm thấy header'

def parse_header(h):
    import re
    h = re.sub(r'\s+', ' ', str(h if h is not None else '').replace('\n',' ')).strip()
    m = re.search(r'1\s*->\s*(\d{1,2})', h) or re.search(r'\(1\s*-\s*(\d{1,2})\)', h)
    if m: return int(m.group(1))
    m = re.search(r'Tháng\s*0?(\d{1,2})', h, re.I)
    if m: return DAYS_FULL.get(int(m.group(1)), 30)
    m = re.search(r'T(\d{1,2})\b', h)
    if m: return DAYS_FULL.get(int(m.group(1)), 30)
    return 30

peri = []
for c in range(4, ws.max_column+1):
    h = ws.cell(hr+1, c).value
    if h is None or str(h).strip()=='': continue
    ngay = parse_header(h)
    short = 'T%d' % (int(__import__('re').search(r'Tháng\s*0?(\d{1,2})', str(h).replace('\n',' '), __import__('re').I).group(1))) if __import__('re').search(r'Tháng\s*0?(\d{1,2})', str(h).replace('\n',' '), __import__('re').I) else str(h).replace('\n',' ').strip()
    if '->' in str(h) or '1-' in str(h): short = short + ' (' + str(ngay) + ' ngày)'
    peri.append({'ten': str(h).replace('\n',' ').strip(), 'short': short, 'ngay': ngay, 'idx': c})

kh, tong = [], None
for r in range(hr+2, ws.max_row+1):
    ma = ws.cell(r, 2).value
    ten = ws.cell(r, 3).value
    if ma is None and ten is None: continue
    ten = str(ten).strip() if ten else ''
    if 'tổng cộng' in ten.lower():
        tong = [pnum(ws.cell(r, p['idx']).value) for p in peri]
        continue
    t = [pnum(ws.cell(r, p['idx']).value) or 0 for p in peri]
    tb = [round(t[i]/peri[i]['ngay'], 2) for i in range(len(peri))]
    kh.append({'stt': str(ws.cell(r,1).value or '').strip(), 'ma': str(ma).strip(), 'ten': ten, 't': t, 'tb': tb})
if not tong:
    tong = [round(sum(k['t'][i] for k in kh)) for i in range(len(peri))]
n = len(peri)
for k in kh:
    k['chenh'] = round((k['tb'][n-1]-k['tb'][n-2])*100)/100
    base = k['tb'][n-2]
    k['pct'] = round((k['chenh']/base*100), 2) if base else (100.0 if k['tb'][n-1]>0 else 0.0)

demo = {'file': 'MUA HÀNG THÁNG 8-26.xlsx', 'kh': kh, 'peri': peri, 'tong': tong}
tb_cty = [round(tong[i]/peri[i]['ngay']) for i in range(n)]
print('KH:', len(kh), '| kỳ:', [p['short'] for p in peri])
print('Tổng Kg các kỳ:', [f"{t:,}" for t in tong])
print('TB/ngày:', tb_cty, '| T8 vs T7:', f"{tb_cty[-1]-tb_cty[-2]:+,} ({(tb_cty[-1]-tb_cty[-2])/tb_cty[-2]*100:+.1f}%)")

# ── Build ──
with open(TPL, 'r', encoding='utf-8') as f:
    html = f.read()
with open(LIB, 'r', encoding='utf-8') as f:
    lib = f.read()

html = html.replace('@@XLSX@@', lib)
html = html.replace('__DEMO__', json.dumps(demo, ensure_ascii=False))

with open(OUT, 'w', encoding='utf-8') as f:
    f.write(html)
print('SAVED:', OUT, '|', round(len(html)/1024/1024, 2), 'MB')