# -*- coding: utf-8 -*-
"""Nap bao cao mua hang ky T10 (1->3/10) vao btchem.db — CO BACKUP.
Giu ten khach hang theo DB cu (khop ma_kh) de khong tach chuoi theo ten."""
import json, os, 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_ky03.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()

# ten chuan theo DB cu (khop ma_kh)
old = {}
for ma, ten in cur.execute("select ma_kh, khach_hang from mua_hang where ky = (select max(ky) from mua_hang)"):
    old[ma] = ten

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

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)' % (ky, sau, s, d['tong_kg']))
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:
    print('   ', x[0], '|', x[1], '<-', x[2])
print('khach hang moi trong ky (%d):' % len(moi))
for x in moi:
    print('   ', x)
# truy van thu 1 khach de kiem chuoi
row = cur.execute("select ky, tong_kg from mua_hang where ma_kh='KH1090' order by ky").fetchall()
print('chuoi KH1090:', row)
c.close()