# -*- coding: utf-8 -*-
import os, glob, datetime
import openpyxl
BASE=os.path.join(os.path.dirname(os.path.abspath(__file__)),'광역_사이트맵')
DAYS={datetime.date(2026,6,4):'06-04(어제)', datetime.date(2026,6,5):'06-05(오늘)'}
def fl(prov):
    if any(k in prov for k in ('충청남도','충청북도')): return '프리랜서1(충남·충북)'
    if any(k in prov for k in ('전북','제주')): return '프리랜서2(전북·제주)'
    return '기타/'+prov
def datarows(p):
    try:
        wb=openpyxl.load_workbook(p,read_only=True); ws=wb.active
        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(); return n
    except: return 0
rows=[]
for p in glob.glob(os.path.join(BASE,'*','*','*.xlsx')):
    bn=os.path.basename(p)
    if bn.startswith('_') or 'backup' in bn: continue
    d=datetime.date.fromtimestamp(os.path.getmtime(p))
    if d not in DAYS: continue
    prov=p.replace(BASE+os.sep,'').split(os.sep)[0]
    inst=bn.replace('.xlsx','').replace(prov+'_','')
    rows.append((d,fl(prov),inst,datarows(p),os.path.getmtime(p)))
rows.sort(key=lambda x:(x[0],x[1],x[4]))

print('■ 상세 (파일 단위)')
print('| 날짜 | 프리랜서 | 기관 | 행수 | 수정시각 |')
print('|---|---|---|---:|---|')
for d,f,inst,n,mt in rows:
    print(f'| {DAYS[d]} | {f} | {inst} | {n} | {datetime.datetime.fromtimestamp(mt).strftime("%H:%M")} |')

print('\n■ 프리랜서별·날짜별 집계 (행)')
print('| 날짜 | 프리랜서 | 기관수 | 총행수 | 기관 |')
print('|---|---|---:|---:|---|')
agg={}
for d,f,inst,n,mt in rows:
    agg.setdefault((d,f),[]).append((inst,n))
for (d,f) in sorted(agg):
    items=agg[(d,f)]
    print(f'| {DAYS[d]} | {f} | {len(items)} | {sum(n for _,n in items)} | {"·".join(i for i,_ in items)} |')

print('\n■ 프리랜서 합계(어제+오늘)')
print('| 프리랜서 | 기관수 | 총행수 |')
print('|---|---:|---:|')
tot={}
for d,f,inst,n,mt in rows:
    tot.setdefault(f,[0,0]); tot[f][0]+=1; tot[f][1]+=n
for f in sorted(tot):
    print(f'| {f} | {tot[f][0]} | {tot[f][1]} |')