# -*- coding: utf-8 -*-
"""Nap bao cao mua hang ky T10 (1->10/10/2026, 10 ngay) vao btchem.db — CO BACKUP.
- XOA cac dong ky='T10' cu (ban 1->3/10) de khong lan 2 ban trong cung 1 ky.
- Giu ten khach hang theo DB cu (khop ma_kh) de khong tach chuoi theo ten.
"""
import json, os, re, shutil, sqlite3, time

DB = r'C:\c\deepseek-harness\claw2-db\btchem.db'
JS = r'C:\Claw 2\Bao cao - Hanh chinh\bao cao mua hang\Bao_cao\capnhat_csdl_T10_ky10.json'
BAKDIR = r'C:\c\deepseek-harness\claw2-db\_backup'

d = json.load(open(JS, encoding='utf-8'))
ky = d['ky']
print('JSON: ky %s | so_ngay %s | tong_kg %s | tb_ngay %s | so_sanh %s | so KH %d'
      % (ky, d['so_ngay'], d['tong_kg'], d['tb_ngay'], d['so_sanh'], len(d['khach_hang'])))

os.makedirs(BAKDIR, exist_ok=True)
bk = os.path.join(BAKDIR, 'btchem_%s.db' % time.strftime('%Y%m%d_%H%M%S'))
shutil.copy2(DB, bk)
print('backup:', bk, os.path.getsize(bk), 'byte')

c = sqlite3.connect(DB); cur = c.cursor()


def so_t(s):
    m = re.match(r'^T(\d+)$', str(s or '').strip())
    return int(m.group(1)) if m else -1


cac_ky = sorted({r[0] for r in cur.execute('select distinct ky from mua_hang')}, key=so_t)
truoc_ky = [k for k in cac_ky if so_t(k) < so_t(ky)]
ky_goc = truoc_ky[-1] if truoc_ky else None
old = {}
if ky_goc:
    for ma, ten in cur.execute('select ma_kh, khach_hang from mua_hang where ky=?', (ky_goc,)):
        old[ma] = ten
print('ky truoc de doi ten:', ky_goc, '| so KH co ten chuan:', len(old))

truoc = cur.execute('select count(*) from mua_hang where ky=?', (ky,)).fetchone()[0]
print('TRUOC: mua_hang ky %s = %d dong | tong_quan %s = %d dong'
      % (ky, truoc, ky, cur.execute('select count(*) from mua_hang_tong_quan where thang=?', (ky,)).fetchone()[0]))

# xoa ban cu cua ky nay (ban 1->3/10) roi nap lai ban 10 ngay
if truoc:
    cur.execute('delete from mua_hang where ky=?', (ky,))
    print('   da xoa %d dong cu cua ky %s' % (truoc, ky))

cur.execute("""insert into mua_hang_tong_quan (thang, tong_kg, so_ngay, tb_ngay, so_sanh) values (?,?,?,?,?)
               on conflict(thang) do update set tong_kg=excluded.tong_kg, so_ngay=excluded.so_ngay,
               tb_ngay=excluded.tb_ngay, so_sanh=excluded.so_sanh""",
            (ky, d['tong_kg'], d['so_ngay'], d['tb_ngay'], d['so_sanh']))

giu_ten, moi = [], []
for k in d['khach_hang']:
    ma = k.get('ma_kh')
    ten = old.get(ma, k['ten'])
    if ma in old and old[ma] != k['ten']:
        giu_ten.append((ma, old[ma], k['ten']))
    if ma not in old:
        moi.append((ma, k['ten']))
    cur.execute("""insert into mua_hang (ma_kh, khach_hang, ky, kg_ngay, tong_kg, so_sanh) values (?,?,?,?,?,?)
                   on conflict(khach_hang, ky) do update set ma_kh=excluded.ma_kh, kg_ngay=excluded.kg_ngay,
                   tong_kg=excluded.tong_kg, so_sanh=excluded.so_sanh""",
                (ma, ten, ky, k['kg_ngay'], k['tong_kg'], k.get('so_sanh')))
c.commit()

sau = cur.execute('select count(*) from mua_hang where ky=?', (ky,)).fetchone()[0]
s = cur.execute('select sum(tong_kg) from mua_hang where ky=?', (ky,)).fetchone()[0]
print('SAU: mua_hang ky %s = %d dong | tong kg trong DB = %s (bang tinh: %s) -> %s'
      % (ky, sau, s, d['tong_kg'], 'KHỚP' if abs((s or 0) - d['tong_kg']) < 2 else 'LỆCH!'))
print('tong_quan %s:' % ky, cur.execute('select * from mua_hang_tong_quan where thang=?', (ky,)).fetchone())
print('cac ky trong DB:', [r[0] for r in cur.execute('select thang from mua_hang_tong_quan order by thang')])
print('tong dong mua_hang toan bang:', cur.execute('select count(*) from mua_hang').fetchone()[0])
print('giu ten cu theo DB (%d):' % len(giu_ten))
for x in giu_ten[:10]:
    print('   ', x[0], '|', x[1], '<-', x[2])
print('khach hang moi trong ky (%d):' % len(moi))
for x in moi[:10]:
    print('   ', x)
print('chuoi KH1090:', cur.execute("select ky, tong_kg from mua_hang where ma_kh='KH1090' order by ky").fetchall())
c.close()