# -*- coding: utf-8 -*-
"""Soi ky: so nha may 82 (agy khai) vs 51 (toi dem tu cot Nha may) — tim nguyen nhan."""
import glob, os, re, unicodedata
from collections import Counter
import openpyxl

BASE = r'C:\Claw 2\Bao cao - Hanh chinh\Bao cao lam mau'
SACH = os.path.join(BASE, 'da lam sach')


def rows_of(path, sheet=None):
    wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
    ws = wb[sheet] if sheet else wb[wb.sheetnames[0]]
    rr = [r for r in ws.iter_rows(values_only=True) if any(c is not None and str(c).strip() != '' for c in r)]
    wb.close()
    return rr


def norm(s):
    return unicodedata.normalize('NFC', str(s).strip()).upper()


m = rows_of(os.path.join(SACH, 'Tuan_40_28.09-04.10_DA-LAM-SACH.xlsx'))
body = m[1:]
sts = [str(r[0]).strip() for r in body if str(r[0] or '').strip()]
print('dong co Stt:', len(sts), '| Stt dau:', sts[:5], '| Stt cuoi:', sts[-5:])
try:
    print('max Stt:', max(int(float(x)) for x in sts))
except Exception as e:
    print('loi Stt:', e)
names = Counter(norm(r[1]) for r in body if str(r[1] or '').strip())
print('so nha may khac nhau (cot B):', len(names))
print('danh sach:', sorted(names.items(), key=lambda x: -x[1]))
print()
print('=== 5 dong dau / 5 dong cuoi ===')
for r in m[:3] + m[-4:]:
    print('  ', [str(c)[:18] if c is not None else '' for c in r[:13]])
print()
print('=== so sanh voi file nguon Men ===')
src1 = glob.glob(os.path.join(BASE, 'TU*N 40 (28.09-04.10).xlsx'))[0]
r1 = rows_of(src1)
for i, r in enumerate(r1[8:14], start=9):
    print('  d%-3d' % i, [str(c)[:18] if c is not None else '' for c in r[:13]])
try:
    nguon = Counter(norm(r[1]) for r in r1[11:] if r and len(r) > 1 and str(r[1] or '').strip())
    print('  so nha may trong file Men (tu dong 12):', len(nguon))
    print('  ', sorted(nguon.items(), key=lambda x: -x[1])[:60])
except Exception as e:
    print('  loi:', e)