# -*- coding: utf-8 -*-
r"""B116: nạp bao_cao_sx tuần 41 từ JSON (UPSERT — tuần mới nên INSERT). Backup trước, verify sau."""
import json, os, shutil, sqlite3, sys
from datetime import datetime

DB = r"C:\c\deepseek-harness\claw2-db\btchem.db"
FAC = json.load(open(r"C:\Claw 2\QC\Base\sx_t41_excel.json", encoding="utf-8"))
TUAN = "T41"

bk = DB.replace('.db', '_bak_%s.db' % datetime.now().strftime('%Y%m%d_%H%M%S'))
shutil.copy2(DB, bk)
print('  backup:', os.path.basename(bk), os.path.getsize(bk), 'byte')

con = sqlite3.connect(DB, timeout=90, isolation_level=None)
con.execute("PRAGMA busy_timeout=90000")
try:
    con.execute("BEGIN IMMEDIATE")
except sqlite3.OperationalError as e:
    sys.exit("không mở được giao dịch: %s" % e)
cur = con.cursor()

before = cur.execute("SELECT COUNT(*) FROM bao_cao_sx WHERE tuan=?", (TUAN,)).fetchone()[0]
bx = cur.execute("SELECT COUNT(*) FROM bao_cao_sx").fetchone()[0]
have = {r[0]: r[1] for r in cur.execute("SELECT nha_may_id, COALESCE(tinh_hinh_su_co,'') FROM bao_cao_sx WHERE tuan=?", (TUAN,))}

upd = ins = 0
for f in FAC:
    nid = f["nha_may_id"]
    if not nid:
        print("  ⚠ bỏ qua (chưa map):", f["ten"]); continue
    th_excel = (f["tinh_hinh"] or "").strip()
    if nid in have:
        giu = (have[nid] or "").strip()
        th = giu if len(giu) >= len(th_excel) else th_excel
        cur.execute("""UPDATE bao_cao_sx SET so_cont=?, chi_tiet=?, mau_moi=?, tinh_trang=?,
                       tinh_hinh_su_co=?, canh_tranh=? WHERE tuan=? AND nha_may_id=?""",
                    (f["so_cont"], f["chi_tiet"], f["mau_moi"], f["tinh_trang"], th, f["canh_tranh"], TUAN, nid))
        upd += 1
    else:
        cur.execute("""INSERT INTO bao_cao_sx (tuan, nha_may_id, so_cont, chi_tiet, mau_moi,
                       tinh_trang, tinh_hinh_su_co, canh_tranh) VALUES (?,?,?,?,?,?,?,?)""",
                    (TUAN, nid, f["so_cont"], f["chi_tiet"], f["mau_moi"], f["tinh_trang"], th_excel, f["canh_tranh"]))
        ins += 1
con.execute("COMMIT")

after = cur.execute("SELECT COUNT(*) FROM bao_cao_sx WHERE tuan=?", (TUAN,)).fetchone()[0]
ax = cur.execute("SELECT COUNT(*) FROM bao_cao_sx").fetchone()[0]
tong = cur.execute("SELECT SUM(so_cont) FROM bao_cao_sx WHERE tuan=?", (TUAN,)).fetchone()[0]
mx = cur.execute("SELECT COUNT(*) FROM bao_cao_sx WHERE tuan=? AND lower(COALESCE(tinh_trang,'')) LIKE '%xong%'", (TUAN,)).fetchone()[0]
ct = cur.execute("""SELECT COUNT(*) FROM bao_cao_sx WHERE tuan=? AND COALESCE(canh_tranh,'') <> ''
                    AND lower(canh_tranh) NOT LIKE '%không có%'""", (TUAN,)).fetchone()[0]
print('  T41: %d → %d dòng (cập nhật %d, thêm %d) | tổng bảng: %d → %d' % (before, after, upd, ins, bx, ax))
print('  tổng cont T41:', tong, '| mẫu xong:', mx, '| có cạnh tranh:', ct)
print('  5 dòng cont cao nhất:')
for r in cur.execute("""SELECT n.ten, b.so_cont, b.tinh_trang FROM bao_cao_sx b JOIN nha_may n ON n.id=b.nha_may_id
                        WHERE b.tuan=? ORDER BY b.so_cont DESC LIMIT 5""", (TUAN,)):
    print('   ', r)
print('  integrity_check:', cur.execute("PRAGMA integrity_check").fetchone()[0])
con.close()
print()
print('  ĐỐI CHIẾU 3 NGUỒN (portal / JSON / DB):')
print('   portal : 62 NM · 530.0 cont · 16 mẫu xong · 38 có cạnh tranh')
print('   JSON   : %d NM · %s cont · %d mẫu xong · %d có cạnh tranh' % (len(FAC), round(sum(x['so_cont'] or 0 for x in FAC), 1),
      sum(1 for x in FAC if 'xong' in (x['tinh_trang'] or '').lower()),
      sum(1 for x in FAC if (x['canh_tranh'] or '').strip() and 'không có' not in (x['canh_tranh'] or '').lower())))
print('   DB     : %d NM · %s cont · %d mẫu xong · %d có cạnh tranh' % (after, tong, mx, ct))