# -*- coding: utf-8 -*-
"""KIEM CHUNG DOC LAP bao cao lam mau T40 (khong tin bao cao tu khai cua agy)."""
import glob, hashlib, os, unicodedata, openpyxl

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


def load_rows(path, sheet=None):
    wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
    ws = wb[sheet] if sheet else wb[wb.sheetnames[0]]
    rows = [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 rows


def find(pattern):
    g = glob.glob(os.path.join(BASE, pattern))
    return g[0] if g else None


print('========== 1. BACKUP: khong file cu nao bi sua ==========')
changed = []
for f in os.listdir(BAK):
    pb, pc = os.path.join(BAK, f), os.path.join(SACH, f)
    if not os.path.exists(pc):
        changed.append((f, 'MAT'))
        continue
    h1 = hashlib.md5(open(pb, 'rb').read()).hexdigest()
    h2 = hashlib.md5(open(pc, 'rb').read()).hexdigest()
    if h1 != h2:
        changed.append((f, 'KHAC'))
print('  so file trong backup:', len(os.listdir(BAK)))
print('  file cu bi sua/mat:', changed if changed else 'KHONG (0 file)')
now = set(os.listdir(SACH))
print('  file MOI them trong da lam sach:', sorted(now - set(os.listdir(BAK))))

print()
print('========== 2. CONG 3 FILE THANH PHAN = FILE GOP (so dong + san luong) ==========')
HDR = ['Stt', 'Nhà máy', 'Mã PT/KH', 'Mã NM', 'Khách hàng', 'Ngày nhận', 'Ngày HT', 'Loại mẫu', 'SP', 'Số lượng',
       'Kỹ thuật', 'Trạng thái', 'Có ĐH', 'Ghi chú']
def parse(path):
    rows = load_rows(path)
    assert [str(c).strip() for c in rows[0][:14]] == HDR, ('header la:', rows[0])
    body = rows[1:]
    def sl(r):
        try:
            return float(str(r[9]).replace(',', '').strip() or 0)
        except Exception:
            return 0.0
    return body, sl

tong_rows = tong_sl = 0
for tag, pat in [('File1_Chinh', 'Tuan_40_File1_Chinh_DA-LAM-SACH.xlsx'),
                 ('File2_KQM', 'Tuan_40_File2_KQM_DA-LAM-SACH.xlsx'),
                 ('File3_W40', 'Tuan_40_File3_W40_DA-LAM-SACH.xlsx')]:
    body, sl = parse(os.path.join(SACH, pat))
    s = sum(sl(r) for r in body)
    tong_rows += len(body)
    tong_sl += s
    print('  %-12s dong: %3d | tong So luong: %7.0f' % (tag, len(body), s))
mbody, msl = parse(os.path.join(SACH, 'Tuan_40_28.09-04.10_DA-LAM-SACH.xlsx'))
print('  GOP          dong: %3d | tong So luong: %7.0f' % (len(mbody), sum(msl(r) for r in mbody)))
print('  => KHOP DONG:', tong_rows == len(mbody), '| KHOP SAN LUONG:', tong_sl == sum(msl(r) for r in mbody))

print()
print('========== 3. THONG KE LAI TU FILE GOP ==========')
nm = set()
for r in mbody:
    if r[1] and str(r[1]).strip():
        nm.add(str(r[1]).strip())
from collections import Counter
cl = Counter((str(r[7]).strip() if r[7] else '(trong)') for r in mbody)
ct = Counter((str(r[11]).strip() if r[11] else '(trong)') for r in mbody)
codh = sum(1 for r in mbody if str(r[12] or '').strip() != '')
print('  nha may:', len(nm), '| dong:', len(mbody), '| san luong:', sum(msl(r) for r in mbody), '| co DH:', codh)
print('  Loai mau:', dict(cl))
print('  Trang thai:', dict(ct))

print()
print('========== 4. DOI CHIEU NGUOC VE 3 FILE NGUON ==========')
src1 = find('TU*N 40 (28.09-04.10).xlsx')
ws = openpyxl.load_workbook(src1, read_only=True, data_only=True)
s1 = ws[ws.sheetnames[0]]
r1 = [r for r in s1.iter_rows(values_only=True)]
print('  File Men: %d dong tho; dong 1-11:' % len(r1))
for i in range(0, 11):
    print('     d%-2d' % (i + 1), [str(c)[:16] if c is not None else '' for c in r1[i][:10]])
def sl_of(r, idx):
    try:
        return float(str(r[idx]).replace(',', '').strip() or 0)
    except Exception:
        return 0.0
# tim cot "So luong" trong file Men
hdr_i = None
for i, r in enumerate(r1[:15]):
    if any(str(c).strip().lower().startswith('s.l') or str(c).strip().lower() in ('số lượng', 'sl') for c in r if c):
        hdr_i = i
        break
print('  dong tieu de bang chi tiet trong file Men: dong', hdr_i + 1 if hdr_i is not None else '?')
if hdr_i is not None:
    cols = [str(c).strip() if c else '' for c in r1[hdr_i]]
    print('  cot:', cols[:16])
    data1 = [r for r in r1[hdr_i + 1:] if any(c is not None and str(c).strip() != '' for c in r)]
    print('  so dong du lieu file Men:', len(data1))
ws.close()
src2 = find('K*T QU* M*U TU*N 40.xlsx')
src3 = find('W40.xlsx')
print('  File Thu:', unicodedata.normalize('NFC', os.path.basename(src2)), os.path.getsize(src2), 'byte')
print('  File Lan:', os.path.basename(src3), os.path.getsize(src3), 'byte')
print('  File 3 sach lay tu sheet 40 dong 604-635 (32 dong) — da khop so dong thanh phan o muc 2.')