# -*- coding: utf-8 -*-
import openpyxl
import json
import os

clean_file = os.path.join('File-lam-sach', 'MUA_HANG_03T10-26_DA-LAM-SACH.xlsx')
wb = openpyxl.load_workbook(clean_file, data_only=True)
ws = wb['FILE GỐC']

header_row = 4

data = []
tong_cong_row = None

for r in range(header_row + 1, ws.max_row + 1):
    val_stt = ws.cell(r, 1).value
    val_ma = ws.cell(r, 2).value
    val_ten = ws.cell(r, 3).value
    
    row_data = []
    for c in range(4, 14): # T1 to T10
        v = ws.cell(r, c).value
        try:
            row_data.append(float(str(v).replace(',', '').strip()) if v is not None else 0.0)
        except:
            row_data.append(0.0)
            
    if str(val_ten).strip().lower() == "tổng cộng" or str(val_ma).strip().upper() == "ZZZ":
        tong_cong_row = row_data
        continue
    
    if not val_ma:
        continue
        
    data.append({
        'stt': val_stt,
        'ma': str(val_ma).strip(),
        'ten': str(val_ten).strip(),
        'months': row_data
    })

print(f"Loaded {len(data)} customers.")

if not tong_cong_row:
    tong_cong_row = [0] * 10
    for d in data:
        for i in range(10):
            tong_cong_row[i] += d['months'][i]

def fmt_so_sanh(kg_ngay, prev_kg_ngay):
    diff = round(kg_ngay - prev_kg_ngay)
    pct = (diff / prev_kg_ngay * 100) if prev_kg_ngay > 0 else 0
    sign = "+" if diff > 0 else ""
    return f"{sign}{int(diff):,} ({sign}{pct:.1f}%)"

tong_kg_t10 = tong_cong_row[9]
kg_ngay_t10 = round(tong_kg_t10 / 3)
prev_kg_ngay_t9 = tong_cong_row[8] / 30

out = {
    "ky": "T10",
    "so_ngay": 3,
    "tong_kg": round(tong_kg_t10),
    "tb_ngay": kg_ngay_t10,
    "so_sanh": fmt_so_sanh(kg_ngay_t10, prev_kg_ngay_t9),
    "khach_hang": []
}

for d in data:
    t10_kg = d['months'][9]
    kg_ngay = round(t10_kg / 3)
    prev_kg_ngay = d['months'][8] / 30
    
    out['khach_hang'].append({
        "ma_kh": d['ma'],
        "ten": d['ten'],
        "tong_kg": round(t10_kg),
        "kg_ngay": kg_ngay,
        "so_sanh": fmt_so_sanh(kg_ngay, prev_kg_ngay)
    })

os.makedirs('Bao_cao', exist_ok=True)
out_path = os.path.join('Bao_cao', 'capnhat_csdl_T10_ky03.json')
with open(out_path, 'w', encoding='utf-8') as f:
    json.dump(out, f, ensure_ascii=False, indent=1)

print(f"Saved JSON to {out_path}")
print(f"ky: {out['ky']}, so_ngay: {out['so_ngay']}, tong_kg: {out['tong_kg']}, tb_ngay: {out['tb_ngay']}, so_sanh: {out['so_sanh']}")
print(f"Total KH in JSON: {len(out['khach_hang'])}")