# -*- coding: utf-8 -*-
r"""B181: (a) liệt kê khối portal + vụ khớp; (b) soi kỹ HP 269 / TVT White / Hành Mỹ Bex trong 2 CSDL."""
import io, 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'
h = io.open(PORTAL, encoding='utf-8').read()
nl = 'CRLF' if h.count('\r\n') else 'LF'
print('  PORTAL: %d byte · %s · %d khoi class="inc"' % (len(h.encode()), nl, h.count('class="inc"')))

khoi = []
for m in re.finditer(r'<!-- =+ (\d+)\. ([\w\-.]+) =+ -->', h):
    i0 = m.end()
    i1 = h.find('<!-- ============', i0)
    seg = h[i0:i1 if i1 > 0 else len(h)]
    h3 = re.search(r'<h3>(.*?)</h3>', seg, re.S)
    tieu = re.sub(r'\s+', ' ', re.sub(r'<[^>]+>', '', h3.group(1))).strip() if h3 else '(?)'
    khoi.append((m.group(1), m.group(2), tieu, seg))
print('  so khoi theo comment: %d' % len(khoi))
TU = {'TVT White ngả vàng': ['TVT White', 'Tân Việt Tiến'],
      'Hành Mỹ Bex 03-2402 H 25%': ['03-2402', 'Bex', 'Hành Mỹ'],
      'MK 12/17 H': ['MK 12/17', '12/17 H', '3012 H'],
      'TTH 270': ['TTH 270', 'TTH270'],
      'THTH 91': ['THTH 91'],
      'HP 269': ['HP 269', 'Hạnh Phúc'],
      'DL 16/19': ['DL 16/19', 'DLO 16/19', 'glaze'],
      'SW2-2108 H (17/17 H)': ['SW2', '2108 H', '17/17'],
      'TVIET 246/299A': ['TVIET 246', '299A', 'TV 299'],
      'MHT 02 (AX 123)': ['MHT 02', 'AX 123']}
for ten, tus in TU.items():
    print('\n  == %s' % ten)
    for no, slug, tieu, seg in khoi:
        k = [t for t in tus if t.lower() in seg.lower()]
        if k:
            print('     portal #%-3s %-22s %s  [khop: %s]' % (no, slug, tieu[:70], ', '.join(k[:3])))

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 chua co HP 269 / TVT White?')
for tu in ('Hạnh Phúc', 'HP 269', '269', 'Tân Việt Tiến', 'TVT'):
    r = c.execute("""select id, substr(coalesce(ten_vu_viec,''),1,95), coalesce(trang_thai,'')
                     from su_co_kph where ten_vu_viec like ? or san_pham_mau like ? or mo_ta_su_co like ?""",
                  ('%' + tu + '%',) * 3).fetchall()
    print('     %-16s %d dong %s' % (tu, len(r), '; '.join('#%d %s (%s)' % x for x in r[:3])))
print('\n  --- ho_so_kph: 3 ho so TVT White + 59-26 Bex 03-2402:')
for i in (3, 8, 819):
    r = k.execute("""select id, coalesce(ma_hs,''), coalesce(ngay_nhan,''), coalesce(san_pham_raw,''),
                            substr(coalesce(hien_tuong_raw,''),1,120), length(coalesce(mo_ta,'')),
                            length(coalesce(cap_nhat,''))
                     from ho_so_kph where id=?""", (i,)).fetchone()
    if r:
        print('     #%d | %s | %s | %s' % (r[0], r[1], r[2], r[3][:95]))
        print('         hien_tuong: %s | mo_ta %dc | cap_nhat %dc' % (r[4].replace('\n', ' ')[:100], r[5], r[6]))