DB_JOB/_김제_확장.py

# -*- coding: utf-8 -*-
"""김제시 누락 하위메뉴 확장 — 공식 사이트맵(전체메뉴) 기준.

basic_tab 부모병합 등으로 빠진 말단 하위메뉴(각자 menuCd 보유)를 사이트맵 트리에서
복원해 부모 아래 올바른 컬럼(D=4+tree_depth)·위치로 삽입한다. 부모행(병합 survivor,
M=탭수)은 자식이 분리되므로 자기 페이지로 Phase2~4 재수집(M=1 등). 기존 행은 보존.

L 판별은 김제 방식(본문 리스트클래스=게시판) + 미디어/KOGL은 전북 phase234 로직 재사용.
사용: python -X utf8 _김제_확장.py plan | run
"""
import os, sys, re, io, time, importlib.util, warnings
from urllib.parse import urljoin
from copy import copy
from concurrent.futures import ThreadPoolExecutor, as_completed
warnings.filterwarnings('ignore')
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8', line_buffering=True)
import requests
from bs4 import BeautifulSoup
import openpyxl

HERE = os.path.dirname(os.path.abspath(__file__))
def _imp(name, path):
    spec = importlib.util.spec_from_file_location(name, path)
    m = importlib.util.module_from_spec(spec); spec.loader.exec_module(m); return m
dd = _imp('dd', os.path.join(HERE, '_스크립트', '_dedup_all.py'))
ph = _imp('ph', os.path.join(HERE, '_스크립트', '_jeonbuk_phase234_all.py'))

XP = os.path.join(HERE, '작업파일', '광역_사이트맵', '전북특별자치도', '3.김제시', '전북특별자치도_김제시.xlsx')
BASE = 'https://www.gimje.go.kr'
ALLMENU = BASE + '/index.gimje?menuCd=DOM_000000107002000000'
LIST = re.compile(r'class="[^"]*(bbs_list|news_list|photo_list|video_list|magazine_list|gallery_list|board_list)[^"]*"')
NEWWIN = re.compile(r'\s*새\s*창\s*열림\s*$')
UA = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/120 Safari/537.36'}


def mc_of(s):
    m = re.search(r'menuCd=(DOM_\w+)', str(s) or ''); return m.group(1) if m else None


def parse_tree():
    """반환: nodes(dict mc->{label,url,depth,parent,children[]}), order(list of mc in DFS)."""
    r = requests.get(ALLMENU, headers=UA, verify=False, timeout=20)
    soup = BeautifulSoup(r.content, 'html.parser')
    smap = soup.select_one('div.sitemap')
    nodes = {}; order = []

    def add(mc, label, url, depth, parent):
        label = NEWWIN.sub('', label).strip()
        if mc in nodes:
            return
        nodes[mc] = {'label': label, 'url': url, 'depth': depth,
                     'parent': parent, 'children': []}
        order.append(mc)
        if parent and parent in nodes:
            nodes[parent]['children'].append(mc)

    def walk(ul, depth, parent):
        for li in ul.find_all('li', recursive=False):
            a = li.find('a', recursive=False) or li.find('a')
            if not a:
                continue
            mc = mc_of(a.get('href'))
            if not mc:
                continue
            label = a.get_text(' ', strip=True)
            url = urljoin(BASE, a.get('href'))
            add(mc, label, url, depth, parent)
            sub = li.find('ul', recursive=False)
            if sub:
                walk(sub, depth + 1, mc)

    for mdiv in smap.find_all('div', recursive=False):
        head = mdiv.find(['h2', 'h3', 'strong', 'a'])
        if not head:
            continue
        # 대분류 head 의 menuCd (a면) 아니면 라벨만 (컨테이너) — 트리 노드로 등록(depth0)
        hmc = mc_of(head.get('href')) if head.name == 'a' else None
        htxt = head.get_text(' ', strip=True)
        if not hmc:
            # 컨테이너 라벨용 가짜 mc
            hmc = 'CAT_' + str(len(nodes))
        add(hmc, htxt, urljoin(BASE, head.get('href')) if head.name == 'a' and head.get('href') else '', 0, None)
        topul = mdiv.find('ul')
        if topul:
            walk(topul, 1, hmc)
    return nodes, order


def subtree(nodes, mc):
    out = set()
    stack = [mc]
    while stack:
        x = stack.pop(); out.add(x)
        stack.extend(nodes[x]['children'])
    return out


def path_labels(nodes, mc):
    """mc 의 조상→자기 라벨 리스트 (depth 순)."""
    chain = []
    cur = mc
    while cur is not None:
        chain.append(cur)
        cur = nodes[cur]['parent']
    chain.reverse()
    return chain  # list of mc by depth


# ---- Phase 2~4 (김제 list-class L + 전북 미디어/KOGL) ----
def collect(session, url):
    out = {'L': '', 'M': '', 'N': '', 'O': '', 'P': '', 'Q': '', 'note': ''}
    try:
        r = session.get(url, headers=UA, verify=False, timeout=8, allow_redirects=True)
        meta = re.search(rb'<meta[^>]*charset=["\']?\s*([\w-]+)', r.content[:4096], re.I)
        r.encoding = meta.group(1).decode('ascii', 'ignore') if meta else r.apparent_encoding
        if r.status_code != 200:
            out['note'] = '접근 실패'; return out
        html = r.text
    except Exception:
        out['note'] = '접근 실패'; return out
    soup = BeautifulSoup(html, 'html.parser')
    body = ph.get_body(soup, ph.BODY_SEL)
    is_board = bool(LIST.search(html))
    if is_board:
        out['L'] = '게시판'
        _, cnt = ph.detect_form(body)
        out['M'] = cnt
    else:
        out['L'] = '페이지'; out['M'] = 1
    has_img, has_vid, has_txt = ph.detect_media(body)
    types, q = ph.detect_kogl(body)
    if is_board:
        for du in ph.extract_detail_urls(body, url, limit=2):
            try:
                dr = session.get(du, headers=UA, verify=False, timeout=8)
                ds = BeautifulSoup(dr.content, 'html.parser')
                db = ph.get_body(ds, ph.BODY_SEL)
                di, dv, dt = ph.detect_media(db)
                has_img |= di; has_vid |= dv; has_txt |= dt
                dt_types, dt_q = ph.detect_kogl(db)
                if dt_types and not types:
                    out['P'] = '게시물'
                types |= dt_types
                if dt_q == 'Y':
                    q = 'Y'
            except Exception:
                pass
    out['N'] = ph.n_string(has_txt, has_img, has_vid)
    if types:
        out['O'] = ','.join(f'{n}유형' for n in sorted(types))
        out['P'] = out['P'] or '게시판'
        out['Q'] = q or 'N'
    else:
        out['O'] = '미부착'
    return out


def main():
    mode = sys.argv[1] if len(sys.argv) > 1 else 'plan'
    nodes, order = parse_tree()
    real = {mc: n for mc, n in nodes.items() if not mc.startswith('CAT_')}
    leaves = [mc for mc in order if not mc.startswith('CAT_') and not nodes[mc]['children']]

    wb = openpyxl.load_workbook(XP); ws = wb.active
    rows = dd.load_flat(ws)
    pos = {}; row_by_mc = {}
    for i, r in enumerate(rows):
        mc = mc_of(r['vals'].get(11))
        if mc:
            pos[mc] = i; row_by_mc[mc] = r
    have = set(row_by_mc)

    missing = [mc for mc in leaves if mc not in have]
    # parents that will gain children
    gain_parents = {}
    for mc in missing:
        p = nodes[mc]['parent']
        gain_parents.setdefault(p, []).append(mc)

    # anchor index for each parent group: after last existing row in subtree of parent
    inserts = {}  # anchor_idx -> [mc,...] in tree order
    no_anchor = []
    for p, kids in gain_parents.items():
        # nearest existing ancestor (incl parent) to anchor after its subtree
        anc = p
        anchor_idx = None
        while anc is not None:
            st = subtree(nodes, anc)
            present = [pos[m] for m in st if m in pos]
            if present:
                anchor_idx = max(present); break
            anc = nodes[anc]['parent']
        if anchor_idx is None:
            no_anchor.append((p, kids)); continue
        # order kids by their order in tree (children list of p)
        ordered = [m for m in nodes[p]['children'] if m in kids]
        inserts.setdefault(anchor_idx, []).extend(ordered)

    survivors = [p for p in gain_parents if p in have]
    print('=== 김제 확장 plan ===')
    print(f'트리 노드(실): {len(real)}  말단leaf: {len(leaves)}  기존행: {len(rows)}')
    print(f'누락 말단메뉴(추가대상): {len(missing)}')
    print(f'자식 얻는 부모: {len(gain_parents)}  (그중 기존행=재수집대상 survivor: {len(survivors)})')
    print(f'앵커 못찾음: {len(no_anchor)}')
    # sample
    def lab(mc):
        return ' > '.join(nodes[m]['label'] for m in path_labels(nodes, mc) if not m.startswith('CAT_') or nodes[m]['label'])
    print('\n[샘플 추가 행 20]')
    for mc in missing[:20]:
        d = nodes[mc]['depth']; col = chr(ord('A') + 3 + d)
        print(f'  +{col}({d}) {lab(mc)}  ({mc})')
    print('\n[survivor 부모 재수집 대상]')
    for p in survivors:
        r = row_by_mc[p]; v = r['vals']
        print(f'  r{r["src"]} M={v.get(13)} L={v.get(12)} [{nodes[p]["label"]}] 자식 {len(gain_parents[p])}개')
    if no_anchor:
        print('\n[!] 앵커 못찾은 그룹:')
        for p, kids in no_anchor:
            print(f'   parent {p} kids {len(kids)}')

    if mode != 'run':
        return

    # ---- build new rows + fetch ----
    session = requests.Session()
    template = row_by_mc.get(list(survivors)[0]) if survivors else rows[0]
    tmpl_styles = template['styles']

    def make_row(mc):
        v = {c: None for c in range(1, dd.MAXCOL + 1)}
        v[3] = '김제시'
        chain = path_labels(nodes, mc)
        for m in chain:
            d = nodes[m]['depth']
            col = 4 + d
            if col <= 10 and nodes[m]['label']:
                v[col] = nodes[m]['label']
        v[11] = nodes[mc]['url']
        return {'src': None, 'vals': v,
                'styles': {c: tuple(copy(x) if hasattr(x, 'copy') or True else x for x in tmpl_styles[c]) for c in tmpl_styles},
                'hyperlink': nodes[mc]['url']}

    # fetch all missing + survivor parents
    fetch_targets = list(missing) + survivors
    print(f'\nPhase2~4 수집 {len(fetch_targets)}건 ...')
    res = {}
    t0 = time.time()
    def work(mc):
        return mc, collect(session, nodes[mc]['url'])
    with ThreadPoolExecutor(max_workers=10) as ex:
        futs = [ex.submit(work, mc) for mc in fetch_targets]
        done = 0
        for f in as_completed(futs):
            mc, o = f.result(); res[mc] = o; done += 1
            if done % 30 == 0 or done == len(fetch_targets):
                print(f'  {done}/{len(fetch_targets)} ({time.time()-t0:.0f}s)')

    # apply to survivors (overwrite own page data; M back to own)
    for p in survivors:
        v = row_by_mc[p]['vals']; o = res.get(p, {})
        for col, key in [(12, 'L'), (13, 'M'), (14, 'N'), (15, 'O'), (16, 'P'), (17, 'Q')]:
            if o.get(key) != '':
                v[col] = o[key]

    # build new row objects with phase data
    new_obj = {}
    for mc in missing:
        ro = make_row(mc); o = res.get(mc, {})
        v = ro['vals']
        for col, key in [(12, 'L'), (13, 'M'), (14, 'N'), (15, 'O'), (16, 'P'), (17, 'Q')]:
            if o.get(key) != '':
                v[col] = o[key]
        if o.get('note'):
            v[19] = o['note']
        new_obj[mc] = ro

    # assemble final ordered list
    out = []
    for i, r in enumerate(rows):
        out.append(r)
        if i in inserts:
            for mc in inserts[i]:
                out.append(new_obj[mc])

    # backup + write
    import shutil
    bak = XP.replace('.xlsx', '_backup_확장전.xlsx')
    shutil.copy(XP, bak)
    dd.write_back(ws, out)
    try:
        wb.save(XP)
        print(f'\n저장완료 {len(rows)}→{len(out)}행. 백업 {os.path.basename(bak)}')
    except PermissionError:
        alt = XP.replace('.xlsx', '_LP.xlsx'); wb.save(alt)
        print(f'\n!! 원본 잠김 → {os.path.basename(alt)} 로 저장')


if __name__ == '__main__':
    main()