# -*- coding: utf-8 -*-
"""공공기관 정합성 QA (읽기전용). L/M/N/O/P/Q 이상치 점검 리포트.
사용: python _공공기관_qa.py [기관명]
"""
import sys, glob, os
import openpyxl

OUTDIR = r'D:\01.프로젝트\DB수집\공공기관'


def check(f):
    wb = openpyxl.load_workbook(f, data_only=True)
    ws = wb.active
    name = os.path.basename(f)[:-5]
    issues = {'사이트오염': [], '게시판M0': [], '페이지M비1': [], 'N빈(비사이트)': [],
              'O부착P/Q빈': [], 'O미부착P/Q채움': [], 'N영상오디오': [], '접근실패': []}
    for r in range(3, ws.max_row + 1):
        if ws.cell(r, 2).value is None:
            break
        L = ws.cell(r, 12).value
        M = ws.cell(r, 13).value
        N = ws.cell(r, 14).value
        O = ws.cell(r, 15).value
        Pp = ws.cell(r, 16).value
        Q = ws.cell(r, 17).value
        S = ws.cell(r, 19).value or ''
        if '접근 실패' in S:
            issues['접근실패'].append(r); continue
        if L == '사이트':
            if M not in (None, '') or N not in (None, '') or (O not in (None, '')):
                issues['사이트오염'].append(r)
            continue
        if L == '게시판' and (M in (None, '', 0)):
            issues['게시판M0'].append(r)
        if L == '페이지' and M not in (1, None, ''):
            issues['페이지M비1'].append(r)
        if L in ('게시판', '페이지') and (N in (None, '')):
            issues['N빈(비사이트)'].append(r)
        if O and O != '미부착':
            if not Pp or not Q:
                issues['O부착P/Q빈'].append(r)
        if (O in (None, '', '미부착')) and (Pp or Q):
            issues['O미부착P/Q채움'].append(r)
        if N and ('영상' in N or '오디오' in N):
            issues['N영상오디오'].append(r)
    return name, issues


def main():
    only = sys.argv[1:]
    files = [os.path.join(OUTDIR, f'{only[0]}.xlsx')] if only else sorted(glob.glob(os.path.join(OUTDIR, '*.xlsx')))
    tot = {}
    for f in files:
        name, iss = check(f)
        flags = {k: v for k, v in iss.items() if v}
        if flags:
            print(f'\n[{name}]')
            for k, v in flags.items():
                tot[k] = tot.get(k, 0) + len(v)
                show = v[:12]
                print(f'  {k}: {len(v)}행 {show}{"..." if len(v)>12 else ""}')
    print('\n=== 전체 합계 ===')
    for k, v in sorted(tot.items(), key=lambda x: -x[1]):
        print(f'  {k}: {v}')


if __name__ == '__main__':
    main()