# -*- coding: utf-8 -*-
r"""B115: parse file sạch T41 -> JSON (sx_t41_excel.json) + map nha_may.id (khớp chính xác, rồi fallback tiền tố >=6 ký tự).
DỪNG nếu còn nhà máy chưa map được (không ghi DB)."""
import json, os, re, sqlite3, unicodedata
import openpyxl

SACH = r'C:\Claw 2\Bao cao - Hanh chinh\Bao-cao-sx\LAMSACH\KETQUA\BANG_BC_SX_TUAN_41_DA-LAM-SACH.xlsx'
JSON_OUT = r'C:\Claw 2\QC\Base\sx_t41_excel.json'
DB = r'C:\c\deepseek-harness\claw2-db\btchem.db'


def chuan(t):
    t = unicodedata.normalize('NFD', (t or '').strip().lower()).replace('đ', 'd')
    t = ''.join(c for c in t if not unicodedata.combining(c))
    return re.sub(r'[^a-z0-9&]', '', t)


def so(x):
    if x is None or x == '':
        return 0.0
    s = str(x).replace(',', '.').strip()
    try:
        return float(s)
    except Exception:
        return None


wb = openpyxl.load_workbook(SACH, data_only=True)
ws = wb[wb.sheetnames[0]]
print('  sheet:', ws.title, '| dims:', ws.calculate_dimension())

ds, kv_hien = [], ''
for row in ws.iter_rows(min_row=4, values_only=True):
    nm = (row[1] or '').strip() if len(row) > 1 else ''
    if not nm:
        continue
    if nm.upper().startswith('KV:'):
        kv_hien = nm.replace('KV:', '', 1).strip(); continue
    if 'NHÓM' in nm.upper():
        continue
    if nm.upper().startswith('BÁO CÁO') or nm.upper().startswith('TT'):
        continue
    ds.append({'ten': nm, 'kv': (row[2] or kv_hien).strip() if len(row) > 2 else kv_hien,
               'so_cont': so(row[3]) if len(row) > 3 else None,
               'chi_tiet': (row[5] or '').strip() if len(row) > 5 else '',
               'mau_moi': (str(row[6]).strip() if row[6] not in (None, '') else '') if len(row) > 6 else '',
               'tinh_trang': (row[7] or '').strip() if len(row) > 7 else '',
               'tinh_hinh': (row[8] or '').strip() if len(row) > 8 else '',
               'canh_tranh': (row[9] or '').strip() if len(row) > 9 else ''})
print('  dòng nhà máy:', len(ds))
print('  tổng cont:', round(sum(x['so_cont'] or 0 for x in ds), 1))

con = sqlite3.connect('file:%s?mode=ro' % DB.replace('\\', '/'), uri=True)
nm_db = [(r[0], r[1]) for r in con.execute('select id, ten from nha_may')]
con.close()
theo_chuan, theo_tien_to = {}, {}
for i, t in nm_db:
    c = chuan(t)
    theo_chuan.setdefault(c, i)
    theo_tien_to.setdefault(c[:6], i)

chua_map, dung_ft = [], []
for f in ds:
    c = chuan(f['ten'])
    fid = theo_chuan.get(c)
    if not fid and len(c) >= 6:
        fid = theo_tien_to.get(c[:6])
        if fid:
            dung_ft.append((f['ten'], [t for i, t in nm_db if i == fid][0]))
    f['nha_may_id'] = fid
    if not fid:
        chua_map.append(f['ten'])

print('  map được:', len(ds) - len(chua_map), '/', len(ds))
print('  dùng fallback tiền tố:', len(dung_ft), dung_ft)
if chua_map:
    print('  ⛔ CHƯA MAP ĐƯỢC:', chua_map)
    raise SystemExit('DỪNG — không ghi DB cho tới khi map xong')

json.dump(ds, open(JSON_OUT, 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
print('  -> lưu JSON:', JSON_OUT, os.path.getsize(JSON_OUT), 'B')
mx = sum(1 for x in ds if 'xong' in (x['tinh_trang'] or '').lower())
ct = sum(1 for x in ds if (x['canh_tranh'] or '').strip() and 'không có' not in (x['canh_tranh'] or '').lower())
print('  ĐỐI CHIẾU PORTAL → JSON: NM %d | cont %s | mẫu xong %d | có cạnh tranh %d' % (
    len(ds), round(sum(x['so_cont'] or 0 for x in ds), 1), mx, ct))