# -*- coding: utf-8 -*-
r"""B78: soi cột co_don_hang + tìm 6 dòng file có mã nhưng KHÔNG vào DB (bị UNIQUE nuốt) để báo cáo trung thực."""
import sqlite3
import openpyxl

DB = r'C:\c\deepseek-harness\claw2-db\btchem.db'
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'

c = sqlite3.connect('file:%s?mode=ro' % DB.replace('\\', '/'), uri=True)
print('  co_don_hang T41 — các giá trị:', c.execute("select ifnull(co_don_hang,'(NULL)'), count(*) from mau_lam where tuan='T41' group by 1").fetchall())
print()
print('  số dòng T41 trong DB:', c.execute("select count(*) from mau_lam where tuan='T41'").fetchone()[0])

# các mã trong file (có mã) vs trong DB
wb = openpyxl.load_workbook(SACH, read_only=True, data_only=True)
ws = wb[wb.sheetnames[0]]
rows = [r for r in ws.iter_rows(values_only=True) if any(x not in (None, '') for x in r)]
hdr = [str(x or '').strip() for x in rows[0]]
i_mm = hdr.index('Mã NM') if 'Mã NM' in hdr else None
i_pt = hdr.index('Mã PT/KH') if 'Mã PT/KH' in hdr else None
i_nm = hdr.index('Nhà máy')
file_ma = []
cur = ''
for r in rows[1:]:
    nm = str(r[i_nm] or '').strip() or cur
    cur = nm
    ma = ''
    if i_mm is not None and i_mm < len(r) and r[i_mm]:
        ma = str(r[i_mm]).strip()
    elif i_pt is not None and i_pt < len(r) and r[i_pt]:
        ma = str(r[i_pt]).strip()
    if ma:
        file_ma.append((nm, ma))
wb.close()
print('  file: %d dòng có mã' % len(file_ma))

db_ma = set()
for r in c.execute("select ifnull(ma_mau,''), ifnull(ma_hien,'') from mau_lam where tuan='T41'"):
    db_ma.add(r[0].strip())
    db_ma.add(r[1].strip())
thieu = [(nm, ma) for nm, ma in file_ma if ma not in db_ma]
print('  mã có trong file nhưng KHÔNG thấy trong DB: %d' % len(thieu))
for nm, ma in thieu[:12]:
    print('     - %s | %s' % (nm[:26], ma[:30]))
c.close()