# -*- coding: utf-8 -*-
r"""B180: rà 10 vụ trong file KPH tuần 3 (14/10) trên 3 nơi: portal index.html, su_co_kph, ho_so_kph."""
import io, os, re, sqlite3

PORTAL = r'C:\Claw 2\Bao cao - Hanh chinh\BAOCAOTONGHOP\HTML\KPH_TongHop_0508\index.html'
BT = r'C:\c\deepseek-harness\claw2-db\btchem.db'
KPH = r'C:\Claw\Khieu Nai KHach Hang\btchem_kph.db'

VU = [
    ('TVT White',        ['TVT White', 'Tân Việt Tiến', 'ngả vàng']),
    ('Hành Mỹ Bex',      ['Bex', '03-2402', 'Hành Mỹ']),
    ('MK 12/17 H',       ['MK 12/17', 'Mekong 3', '3012 H']),
    ('TTH 270',          ['TTH 270', 'Tiến Hưng']),
    ('THTH 91',          ['THTH 91', 'Thành Thắng']),
    ('HP 269',           ['HP 269', 'Hạnh Phúc']),
    ('DL 16/19',         ['DL 16/19', 'Đăng Long', '16/19']),
    ('SW2-2108 H 17/17', ['SW2', '2108', '17/17']),
    ('TVIET 246/299A',   ['TVIET', '299A', 'Thịnh Việt']),
    ('MHT 02 AX 123',    ['MHT 02', 'AX 123', 'Minh Hưng Tiến']),
]

# ── portal ──────────────────────────────────────────────────────────────
h = io.open(PORTAL, encoding='utf-8').read()
print('  PORTAL: %d byte | %d khoi class="inc"' % (len(h.encode()), h.count('class="inc"')))
khoi = re.findall(r'<!-- =+ (\d+)\. ([\w\-]+) =+ -->\s*\n?<div class="inc"(?: id="([^"]*)")?', h)
print('    so khoi nhan dien: %d' % len(khoi))
if not khoi:
    ids = re.findall(r'<div class="inc" id="([^"]+)"', h)
    print('    ids:', len(ids))
# tach khoi theo comment → lay id + h3
for m in re.finditer(r'<!-- =+ (\d+)\. ([\w\-]+) =+ -->', h):
    no, slug = m.group(1), m.group(2)
    i0 = m.end()
    i1 = h.find('<!-- ============', i0)
    seg = h[i0:i1 if i1 > 0 else i0 + 9000]
    h3 = re.search(r'<h3>(.*?)</h3>', seg, re.S)
    tieu = re.sub(r'\s+', ' ', re.sub(r'<[^>]+>', '', h3.group(1))).strip() if h3 else '?'
    khop = [t for t in sum([v for _, v in VU], []) if t.lower() in seg.lower()]
    if khop:
        print('    #%-3s %-22s %s' % (no, slug, tieu[:88]))
        print('        khop: %s' % ', '.join(sorted(set(khop))[:6]))

# ── 2 CSDL ──────────────────────────────────────────────────────────────
c = sqlite3.connect('file:%s?mode=ro' % BT.replace('\\', '/'), uri=True)
k = sqlite3.connect('file:%s?mode=ro' % KPH.replace('\\', '/'), uri=True)
print('\n  su_co_kph: %d dong | ho_so_kph: %d ho so' % (
    c.execute('select count(*) from su_co_kph').fetchone()[0],
    k.execute('select count(*) from ho_so_kph').fetchone()[0]))
for ten, tus in VU:
    print('\n  == %s' % ten)
    for tu in tus:
        r = c.execute("""select id, coalesce(ten_vu_viec,''), coalesce(trang_thai,''), length(coalesce(mo_ta_su_co,''))
                         from su_co_kph where ten_vu_viec like ? or san_pham_mau like ? or mo_ta_su_co like ?""",
                      ('%' + tu + '%',) * 3).fetchall()
        if r:
            print('     su_co_kph <- %-16s %s' % (tu, '; '.join('#%d(%s|%s|%dc)' % x for x in r[:4])))
        q = k.execute("""select id, coalesce(ma_hs,''), coalesce(san_pham_raw,''), coalesce(ngay_nhan,''),
                                length(coalesce(cap_nhat,''))
                         from ho_so_kph where san_pham_raw like ? or hien_tuong_raw like ? or mo_ta like ?
                         or cap_nhat like ? or ghi_chu like ?""", ('%' + tu + '%',) * 5).fetchall()
        if q:
            print('     ho_so_kph <- %-16s %s' % (tu, '; '.join('#%d(%s|%s|%s)' % (a, b, d, e) for a, b, d, e, f in q[:4])))