# -*- coding: utf-8 -*-
"""Xuất dữ liệu nạp CSDL kỳ T10 (10 ngày: 01/10 -> 10/10/2026).
Nguồn dữ liệu: File-lam-sach/MUA_HANG_10T10-26_DA-LAM-SACH.xlsx
Lưu: Bao_cao/capnhat_csdl_T10_ky10.json
"""
import openpyxl, json, os, sqlite3

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
clean_file = os.path.join(BASE_DIR, 'File-lam-sach', 'MUA_HANG_10T10-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 = str(ws.cell(r, 2).value or '').strip()
    val_ten = str(ws.cell(r, 3).value or '').strip()
    
    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 val_ten.lower() == "tổng cộng" or val_ma.upper() == "ZZZ":
        tong_cong_row = row_data
        continue
    
    if not val_ma:
        continue
        
    data.append({
        'stt': val_stt,
        'ma': val_ma,
        'ten': val_ten,
        'months': row_data
    })

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

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]

# Mape nhà máy từ btchem.db
dbp = r"C:\c\deepseek-harness\claw2-db\btchem.db"
nm_map = {}
try:
    conn = sqlite3.connect(f"file:{dbp}?mode=ro", uri=True)
    _ten = {r[0]: r[1] for r in conn.execute("SELECT id, ten FROM nha_may")}
    _als = list(conn.execute("SELECT nha_may_id, alias FROM nha_may_alias"))
    for d in data:
        up = d['ten'].upper()
        nm = ''
        for nid, al in _als:
            a = (al or '').strip().upper()
            if len(a) < 4:
                continue
            if a == d['ma'].upper() or (len(a) >= 8 and a in up):
                nm = _ten.get(nid, '')
                break
        nm_map[d['ma']] = nm
    conn.close()
except Exception as e:
    print("! Cảnh báo không kết nối được CSDL để lấy nhà máy:", e)

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 (100.0 if kg_ngay > 0 else 0.0)
    sign = "+" if diff > 0 else ""
    return f"{sign}{int(diff):,} ({sign}{pct:.1f}%)" if diff != 0 else "0 (0.0%)"

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

out = {
    "ky": "T10",
    "so_ngay": 10,
    "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 / 10)
    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),
        "nha_may": nm_map.get(d['ma'], '')
    })

os.makedirs('Bao_cao', exist_ok=True)
out_path = os.path.join(BASE_DIR, 'Bao_cao', 'capnhat_csdl_T10_ky10.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'])}")