# -*- coding: utf-8 -*-
"""익산 91~끝: 부모컨테이너 페이지의 .snb 자식탭을 lc+1열에 전개(재정운용상황공개식).
가드: 행 menuUid가 snb탭 목록에 없음(=부모컨테이너) & 탭>=2 & lc+1 빈칸.
부모행 lc+1=첫탭·나머지 탭 행삽입·lc병합·process_row(L/M/N). 1~90 보존.
사용: python -X utf8 _익산_snb탭.py [--write]
"""
import sys, os, re, shutil, importlib.util
from urllib.parse import urljoin
import openpyxl
from openpyxl.worksheet.hyperlink import Hyperlink

HERE=os.path.dirname(os.path.abspath(__file__))
s=importlib.util.spec_from_file_location('m',os.path.join(HERE,'_jeonbuk_phase234_all.py'))
M=importlib.util.module_from_spec(s); s.loader.exec_module(M)
XLSX=r'D:\01.프로젝트\DB수집\작업파일\광역_사이트맵\전북특별자치도\9.익산시\전북특별자치도_익산시.xlsx'
BASE='https://www.iksan.go.kr'
START_ROW=91
SESS=M.make_session()

def clean(t): return re.sub(r'현재단계|현재페이지|하위메뉴.*|닫기|열기','',t or '').strip()
def muid(u):
    mm=re.search(r'menuUid=([0-9a-f]+)',u or ''); return mm.group(1) if mm else None
def leafcol(ws,r):
    for c in range(10,3,-1):
        if ws.cell(r,c).value not in (None,''): return c
    return None
def snb_tabs(url):
    soup=M.fetch(SESS,url)
    if soup is None: return None
    snb=soup.select_one('ul.snb') or soup.select_one('.snb')
    if not snb: return None
    out=[]
    for li in snb.find_all('li',recursive=False):
        a=li.find('a',href=True)
        if a:
            t=clean(a.get_text());
            if t: out.append((t, urljoin(BASE,a['href'])))
    return out

def remap_insert(ws, ins, n):
    ranges=[(x.min_col,x.min_row,x.max_col,x.max_row) for x in list(ws.merged_cells.ranges)]
    for x in list(ws.merged_cells.ranges): ws.unmerge_cells(str(x))
    ws.insert_rows(ins,n)
    for mc,mr,xc,xr in ranges:
        if mr>=ins: mr+=n; xr+=n
        elif xr>=ins: xr+=n
        ws.merge_cells(start_row=mr,start_column=mc,end_row=xr,end_column=xc)

def main():
    write='--write' in sys.argv
    wb=openpyxl.load_workbook(XLSX); ws=wb.active
    C=ws.cell(3,3).value
    plan=[]
    for r in range(START_ROW, ws.max_row+1):
        K=ws.cell(r,11).value
        if not(isinstance(K,str) and 'menuUid=' in K): continue
        lc=leafcol(ws,r)
        if not lc or lc>=10: continue
        if ws.cell(r,lc+1).value not in (None,''): continue  # 이미 전개됨
        tabs=snb_tabs(K)
        if not tabs or len(tabs)<2: continue
        tabids=[muid(u) for _,u in tabs]
        if muid(K) in tabids: continue   # 행이 탭 중 하나(리프/형제) → 부모아님
        plan.append((r,lc,ws.cell(r,lc).value,tabs))
    print('전개대상 %d개'%len(plan))
    for r,lc,lab,tabs in plan:
        print(' r%d lc%d %s → %d탭: %s'%(r,lc,lab,len(tabs),[t for t,_ in tabs]))
    if not write: return
    shutil.copy(XLSX, XLSX.replace('.xlsx','_backup_snb탭전.xlsx'))
    tot=0
    for r,lc,lab,tabs in sorted(plan,key=lambda t:-t[0]):  # bottom-up
        ws.cell(r,lc+1).value=tabs[0][0]
        out0=M.process_row(SESS,tabs[0][1],M.BODY_SEL)
        ws.cell(r,11).value=tabs[0][1]
        ws.cell(r,12).value=out0['L'] or '페이지'
        ws.cell(r,13).value=out0['M'] if out0['M']!='' else 1
        ws.cell(r,14).value=out0['N']; ws.cell(r,15).value=out0['O']; ws.cell(r,16).value=out0['P']; ws.cell(r,17).value=out0['Q']
        rest=tabs[1:]; n=len(rest)
        if n:
            remap_insert(ws,r+1,n)
            for i,(clab,curl) in enumerate(rest):
                rr=r+1+i
                out=M.process_row(SESS,curl,M.BODY_SEL)
                ws.cell(rr,3).value=C
                ws.cell(rr,lc+1).value=clab
                ws.cell(rr,11).value=curl
                ws.cell(rr,12).value=out['L'] or '페이지'
                ws.cell(rr,13).value=out['M'] if out['M']!='' else 1
                ws.cell(rr,14).value=out['N']; ws.cell(rr,15).value=out['O']; ws.cell(rr,16).value=out['P']; ws.cell(rr,17).value=out['Q']
                ws.row_dimensions[rr].height=15
            ws.merge_cells(start_row=r,start_column=lc,end_row=r+n,end_column=lc)
        tot+=n
        print(' r%d %s +%d (첫탭=%s)'%(r,lab,n,tabs[0][0]))
    for r in range(3,ws.max_row+1):
        v=ws.cell(r,11).value; cell=ws.cell(r,11)
        if isinstance(v,str) and v.startswith('http'): cell.hyperlink=Hyperlink(ref=cell.coordinate,target=v)
        else: cell.hyperlink=None
    wb.save(XLSX)
    print('적용: +%d행 · 하이퍼링크재구성 · 백업'%tot)

if __name__=='__main__': main()