import openpyxl
import json
import math

wb = openpyxl.load_workbook('File-lam-sach/MUA_HANG_26T9-26_DA-LAM-SACH.xlsx', data_only=True)
ws = wb.active

header_row = 5

data = []
tong_cong_row = None

# Read rows
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, 13): # T1 to T9
        v = ws.cell(r, c).value
        try:
            row_data.append(float(v) if v is not None else 0.0)
        except:
            row_data.append(0.0)
            
    if str(val_ten).strip().lower() == "tổng cộng":
        tong_cong_row = row_data
        continue
    
    if not val_ma:
        continue
        
    data.append({
        'ma': val_ma,
        'ten': val_ten,
        'months': row_data
    })

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

days = [31, 28, 31, 30, 31, 30, 31, 31, 26]

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}%)".replace(",", ".")

tong_kg_t9 = tong_cong_row[8]
kg_ngay_t9 = round(tong_kg_t9 / 26)
prev_kg_ngay_t8 = tong_cong_row[7] / 31

out = {
    "ky": "T9",
    "so_ngay": 26,
    "tong_kg": round(tong_kg_t9),
    "tb_ngay": kg_ngay_t9,
    "so_sanh": fmt_so_sanh(kg_ngay_t9, prev_kg_ngay_t8),
    "khach_hang": []
}

for d in data:
    t9_kg = d['months'][8]
    kg_ngay = round(t9_kg / 26)
    prev_kg_ngay = d['months'][7] / 31
    
    out['khach_hang'].append({
        "ma_kh": d['ma'],
        "ten": d['ten'],
        "tong_kg": round(t9_kg),
        "kg_ngay": kg_ngay,
        "so_sanh": fmt_so_sanh(kg_ngay, prev_kg_ngay)
    })

out_path = 'Bao_cao/capnhat_csdl_T9_ky26.json'
with open(out_path, 'w', encoding='utf-8') as f:
    json.dump(out, f, ensure_ascii=False, indent=2)

print(f"Saved JSON to {out_path}")

with open('NHAT_KY_agy_T9_26.md', 'a', encoding='utf-8') as f:
    f.write(f"- Bước 4: Đã xuất file {out_path} gồm tổng {round(tong_kg_t9)} Kg và chi tiết {len(data)} KH để cập nhật CSDL.\n")
    f.write("- HOAN TAT\n")