# -*- coding: utf-8 -*-
r"""B77: NẠP CHÍNH THỨC mau_lam tuần 41 (process_file + close để COMMIT) rồi verify TRƯỚC->SAU."""
import importlib.util, sqlite3, time

DB = r'C:\c\deepseek-harness\claw2-db\btchem.db'
LO = r'C:\c\deepseek-harness\claw2-db\nap_du_lieu.py'
SACH = r'C:\Claw 2\Bao cao - Hanh chinh\Bao cao lam mau\da lam sach\Tuan_41_05.10-11.10_DA-LAM-SACH.xlsx'
BAK = r'C:\c\deepseek-harness\claw2-db\btchem.db_bak_20261012_190627_truoc_nap_mau_lam_T41'

c = sqlite3.connect(DB)
BANG = [r[0] for r in c.execute("select name from sqlite_master where type='table' order by name")]
truoc = {b: c.execute('select count(*) from "%s"' % b).fetchone()[0] for b in BANG}
c.close()
print('  TRƯỚC: mau_lam=%d | nha_may=%d | phai_vien_kt=%d | file_loaded=%d' % (
    truoc['mau_lam'], truoc['nha_may'], truoc['phai_vien_kt'], truoc['file_loaded']))
print('  (backup đã có: %s)' % BAK.split('\\')[-1])

spec = importlib.util.spec_from_file_location('napdu', LO)
m = importlib.util.module_from_spec(spec)
spec.loader.exec_module(m)
ld = m.Loader(DB)
res = m.process_file(ld, 'mau', SACH, None)
print('  process_file: inserted=%s | class=%s | skip=%s | error=%s' % (res['inserted'], res['class'], res['skip'], res['error']))
ld.close()      # ← commit
print('  đã close() → COMMIT')

c = sqlite3.connect('file:%s?mode=ro' % DB.replace('\\', '/'), uri=True)
print()
print('  === VERIFY TRƯỚC -> SAU (mọi bảng có thay đổi) ===')
for b in BANG:
    n = c.execute('select count(*) from "%s"' % b).fetchone()[0]
    if n != truoc[b] or b in ('mau_lam', 'nha_may', 'phai_vien_kt', 'file_loaded'):
        print('   %-20s %6d -> %6d  (%+d)' % (b, truoc[b], n, n - truoc[b]))
print()
r = c.execute("select count(*), count(distinct nha_may_id), sum(case when nha_may_id is null then 1 else 0 end), sum(so_luong) from mau_lam where tuan='T41'").fetchone()
print('  mau_lam T41: %s dòng | %s nhóm nhà máy | %s dòng chưa gắn nhà máy | tổng SL %s' % r)
print('  trạng thái:', c.execute("select trang_thai, count(*) from mau_lam where tuan='T41' group by 1 order by 2 desc").fetchall())
print('  loại mẫu  :', c.execute("select loai_mau, count(*) from mau_lam where tuan='T41' group by 1 order by 2 desc").fetchall())
print('  mẫu có đơn hàng:', c.execute("select count(*) from mau_lam where tuan='T41' and ifnull(co_don_hang,'')<>''").fetchone()[0])
print('  dòng thiếu Kỹ thuật:', c.execute("select count(*) from mau_lam where tuan='T41' and ifnull(ky_thuat_id,'')=''").fetchone()[0])
print('  integrity_check:', c.execute('pragma integrity_check').fetchone()[0])
print()
print('  === so sánh 3 tuần gần nhất ===')
for r in c.execute("select tuan, count(*), count(distinct nha_may_id), sum(so_luong) from mau_lam where tuan in ('T39','T40','T41') group by tuan order by tuan"):
    print('   %-5s %4d dòng | %2d nhóm NM | SL %s' % r)
c.close()