# -*- coding: utf-8 -*-
r"""B74: (1) xem ngữ cảnh '28/09' & '04/10' trong HTML (nghi là ngày dữ liệu, không phải kỳ cũ);
(2) BACKUP btchem.db bằng API sqlite3; (3) nạp mau_lam tuần 41 qua loader; (4) verify TRƯỚC→SAU mọi bảng."""
import importlib.util, os, re, sqlite3, sys, time

DB = r'C:\c\deepseek-harness\claw2-db\btchem.db'
LOADER = 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'
HTML = r'C:\Claw 2\Bao cao - Hanh chinh\BAOCAOTONGHOP\HTML\Tuan 41\Bao_cao_LamMau_Tuan41_Portal.html'

print('  === 1. ngữ cảnh 2 chuỗi ngày trong HTML ===')
s = open(HTML, encoding='utf-8', errors='replace').read()
for pat in ('28/09', '04/10'):
    for m in list(re.finditer(re.escape(pat), s))[:3]:
        c = s[max(0, m.start() - 70):m.end() + 30].replace('\n', ' ')
        print('   %s … %s' % (pat, c[-110:]))

print()
print('  === 2. BACKUP btchem.db (API sqlite3 — server 8095 đang giữ DB) ===')
tem = time.strftime('%Y%m%d_%H%M%S')
BAK = DB.replace('btchem.db', 'btchem.db_bak_%s_truoc_nap_mau_lam_T41' % tem)
src = sqlite3.connect(DB)
dst = sqlite3.connect(BAK)
with dst:
    src.backup(dst)
dst.close()
src.close()
print('   đã backup:', os.path.basename(BAK), os.path.getsize(BAK), 'B')

print()
print('  === 3. NẠP mau_lam tuần 41 qua loader ===')
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}
truoc_t41 = c.execute("select count(*) from mau_lam where tuan='41'").fetchone()[0]
c.close()
print('   TRƯỚC: mau_lam tổng %d | mau_lam tuần 41 = %d' % (truoc['mau_lam'], truoc_t41))

spec = importlib.util.spec_from_file_location('napdu', LOADER)
m = importlib.util.module_from_spec(spec)
spec.loader.exec_module(m)
ld = m.Loader(DB)
try:
    m.process_file(ld, 'mau', SACH, None)
    print('   loader: đã chạy process_file(mau, file T41)')
except Exception as e:
    print('   loader LỖI:', type(e).__name__, str(e)[:200])

print()
print('  === 4. VERIFY TRƯỚC → SAU (mọi bảng) ===')
c = sqlite3.connect(DB)
for b in BANG:
    n = c.execute('select count(*) from "%s"' % b).fetchone()[0]
    d = n - truoc[b]
    if d or b in ('mau_lam', 'nha_may', 'phai_vien_kt', 'file_loaded'):
        print('   %-22s %6d -> %6d  (%+d)' % (b, truoc[b], n, d))
print()
r = c.execute('''select count(*), count(distinct nha_may_id), sum(case when nha_may_id is null then 1 else 0 end)
                 from mau_lam where tuan='41' ''').fetchone()
r2 = c.execute("select sum(so_luong) from mau_lam where tuan='41'").fetchone()[0]
r3 = c.execute("select trang_thai, count(*) from mau_lam where tuan='41' group by 1 order by 2 desc").fetchall()
print('   mau_lam T41: %d dòng | %d nhóm nhà máy | %d dòng nha_may_id NULL | tổng SL %s' % (r[0], r[1], r[2], r2))
print('   trạng thái T41:', r3)
print('   integrity_check:', c.execute('pragma integrity_check').fetchone()[0])
c.close()