# -*- coding: utf-8 -*-
import os, glob, datetime
import openpyxl
BASE=os.path.join(os.path.dirname(os.path.abspath(__file__)),'광역_사이트맵')
WANT={datetime.date(2026,6,4),datetime.date(2026,6,5)}
def asdate(v):
    if isinstance(v,datetime.datetime): return v.date()
    if isinstance(v,datetime.date): return v
    if isinstance(v,str):
        for f in ('%Y-%m-%d','%Y.%m.%d','%Y/%m/%d'):
            try: return datetime.datetime.strptime(v.strip(),f).date()
            except: pass
    return None
rows=[]
allfiles=[]
for p in glob.glob(os.path.join(BASE,'*','*','*.xlsx')):
    bn=os.path.basename(p)
    if bn.startswith('_') or bn.startswith('~$') or 'backup' in bn: continue
    prov=p.replace(BASE+os.sep,'').split(os.sep)[0]
    inst=bn.replace('.xlsx','').replace(prov+'_','')
    try:
        wb=openpyxl.load_workbook(p,read_only=True); ws=wb.active
        z=ws.cell(3,26).value; aa=ws.cell(3,27).value
        n=sum(1 for r in ws.iter_rows(min_row=3,min_col=2,max_col=2) if r[0].value not in (None,''))
        wb.close()
    except Exception as e:
        z=aa=None; n=0
    d=asdate(z)
    allfiles.append((prov,inst,d,aa,n))
    if d in WANT:
        rows.append((d,aa or '(미기입)',prov,inst,n))
rows.sort(key=lambda x:(x[0],str(x[1])))
DLAB={datetime.date(2026,6,4):'06-04(어제)',datetime.date(2026,6,5):'06-05(오늘)'}
print('■ Z(확인날짜)=06-04/06-05 파일 — 담당자별·날짜별 (행)')
print('| 날짜 | 담당자(AA) | 기관수 | 총행수 | 기관 |')
print('|---|---|---:|---:|---|')
agg={}
for d,aa,prov,inst,n in rows:
    agg.setdefault((d,aa),[]).append((inst,n))
for (d,aa) in sorted(agg,key=lambda k:(k[0],str(k[1]))):
    it=agg[(d,aa)]
    print(f'| {DLAB[d]} | {aa} | {len(it)} | {sum(n for _,n in it)} | {"·".join(i for i,_ in it)} |')
print(f'\n총 {len(rows)}개 파일에 Z=06-04/05 기록됨')
# 참고: AA 담당자 전체 분포
from collections import Counter
c=Counter(aa for _,_,_,aa,_ in allfiles)
print('\n■ 참고: 전체 파일 AA 분포:', dict(c))