import sys, io, shutil, copy, os
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')
import openpyxl

P = r'D:\01.프로젝트\DB수집\작업파일\광역_사이트맵\충청남도\10.아산시\충청남도_아산시.xlsx'
BK = P.replace('.xlsx', '_backup_openInfo탭+S삭제전.xlsx')
if not os.path.exists(BK):
    shutil.copy2(P, BK); print('backup ->', BK)

wb = openpyxl.load_workbook(P); ws = wb.active
ANCHOR = 37          # row 37 = no=55 정보공개제도안내
N_NEW = 9            # 10 total = 1 existing + 9 inserted
SHIFT = N_NEW

labels = ['행정정보공개란?','행정정보공개제도의 필요성','청구권자와 대상정보',
          '공개대상에서 제외되는 정보','청구 및 처리 절차','행정정보공개는 어떻게 하나요?',
          '불복구제절차란?','공공기관의 의무','정보공개 수수료','비공개대상정보']
BASE = 'https://www.asan.go.kr/main/cms/?no=55'

orig_max = ws.max_row

# 1) snapshot merges, then remove all with max_row >= ANCHOR
removed = [m for m in ws.merged_cells.ranges if m.max_row >= ANCHOR]
for m in list(removed):
    ws.unmerge_cells(str(m))

# 2) insert 9 blank rows at ANCHOR+1 (moves old 38..end down by 9, with styles/values)
ws.insert_rows(ANCHOR + 1, N_NEW)

# 3) rebuild merges
def addmerge(min_col, min_row, max_col, max_row):
    ws.merge_cells(start_row=min_row, start_column=min_col, end_row=max_row, end_column=max_col)
for m in removed:
    if m.min_row <= ANCHOR <= m.max_row:      # spans anchor -> extend downward
        addmerge(m.min_col, m.min_row, m.max_col, m.max_row + SHIFT)
    else:                                      # entirely below -> shift
        addmerge(m.min_col, m.min_row + SHIFT, m.max_col, m.max_row + SHIFT)
# parent F merge for the 10 openInfo rows
addmerge(6, ANCHOR, 6, ANCHOR + 9)

# 4) style template = row 37 per-column style; copy to new rows 38..46
from openpyxl.utils import get_column_letter
for col in range(1, ws.max_column + 1):
    src = ws.cell(ANCHOR, col)
    for i in range(1, N_NEW + 1):
        dst = ws.cell(ANCHOR + i, col)
        dst._style = copy.copy(src._style)

# 5) fill the 10 openInfo rows
def setrow(r, pg, label, first=False):
    ws.cell(r, 2).value = r - 2                      # B 순번 = row-2
    ws.cell(r, 3).value = '충청남도 아산시'           # C
    if first:
        ws.cell(r, 4).value = '정보공개'             # D (merged anchor)
        ws.cell(r, 5).value = '정보공개'             # E (merged anchor)
        ws.cell(r, 6).value = '정보공개제도안내'      # F (merged anchor)
    # non-first D/E/F are merged (read-only) -> leave as-is
    ws.cell(r, 7).value = label                      # G 소분류 = tab label
    for c in (8, 9, 10):                              # H,I,J
        ws.cell(r, c).value = None
    url = f'{BASE}&pg={pg}'
    kc = ws.cell(r, 11); kc.value = url; kc.hyperlink = url   # K
    ws.cell(r, 12).value = '페이지'                  # L
    ws.cell(r, 13).value = 1                          # M
    ws.cell(r, 14).value = '어문'                    # N
    ws.cell(r, 15).value = '미부착'                  # O
    for c in (16, 17, 18, 19):                        # P,Q,R,S
        ws.cell(r, c).value = None

for i in range(10):
    setrow(ANCHOR + i, i, labels[i], first=(i == 0))

# 6) renumber B for all data rows >= ANCHOR (= row-2); fix all K hyperlinks in shifted region
for r in range(ANCHOR, ws.max_row + 1):
    ws.cell(r, 2).value = r - 2
    kc = ws.cell(r, 11)
    if kc.value and isinstance(kc.value, str) and kc.value.startswith('http'):
        kc.hyperlink = kc.value
    else:
        kc.hyperlink = None

# 7) clear S where value == '외부링크' (whole sheet, position-independent)
s_cleared = 0
for r in range(3, ws.max_row + 1):
    sc = ws.cell(r, 19)
    if sc.value is not None and str(sc.value).strip() == '외부링크':
        sc.value = None; s_cleared += 1

wb.save(P)
print(f'orig_max={orig_max} new_max={ws.max_row} (expected {orig_max+SHIFT})')
print(f'S 외부링크 cleared: {s_cleared}')
print('saved', P)