# -*- coding: utf-8 -*-
"""KCOPA 본문탭 전수전개: GROUP2 점검도구소개(형제 +3) + GROUP1 C STORY/KCOPA REPORT(자식)."""
import openpyxl, copy, sys
from openpyxl.styles import PatternFill, Font
sys.stdout.reconfigure(encoding='utf-8')
SRC=r'D:\01.프로젝트\DB수집\작업파일\공공기관3\13.한국저작권보호원\한국저작권보호원.xlsx'
BASE='https://www.kcopa.or.kr'
DRY = '--write' not in sys.argv
BLUE=PatternFill(fill_type='solid',fgColor='FFBDD7EE',bgColor='FFBDD7EE')

wb=openpyxl.load_workbook(SRC); ws=wb.active
MAXR=ws.max_row
# map merged top-left for each (r,c)
owner={}
for mr in ws.merged_cells.ranges:
    for r in range(mr.min_row,mr.max_row+1):
        for c in range(mr.min_col,mr.max_col+1):
            owner[(r,c)]=(mr.min_row,mr.min_col)
def ctl(r,c):  # controlling cell coords
    return owner.get((r,c),(r,c))
def effval(r,c):
    o=ctl(r,c); return ws.cell(o[0],o[1]).value
def effstyle(r,c):
    o=ctl(r,c); return copy.copy(ws.cell(o[0],o[1])._style)

NCOL=27  # A..AA -> use cols 2..27 (B..AA)
def cap_record(r):
    rec={'orig':r,'cells':{}}
    for c in range(2,NCOL+1):
        rec['cells'][c]={'v':effval(r,c),'s':effstyle(r,c),
                         'hl':(ws.cell(r,c).hyperlink.target if ws.cell(r,c).hyperlink else None)}
    return rec
orig={r:cap_record(r) for r in range(3,MAXR+1)}

# base styles for NEW cells (from row3)
basestyle={c:copy.copy(ws.cell(3,c)._style) for c in range(4,16)}
kbase=copy.copy(ws.cell(3,11)._style)  # K hyperlink style

def U(p): return p if p.startswith('http') else BASE+p
def newrec(colvals,blue_cols,hl=None):
    """colvals: dict col->value. blue_cols: set of cols to paint blue."""
    rec={'orig':None,'cells':{}}
    for c in range(2,NCOL+1):
        v=colvals.get(c,None)
        s=copy.copy(kbase) if c==11 else copy.copy(basestyle.get(c, ws.cell(3,c)._style))
        rec['cells'][c]={'v':v,'s':s,'hl':hl if c==11 else None}
    for c in blue_cols:
        rec['cells'][c]['s']=copy.copy(rec['cells'][c]['s'])
    rec['_blue']=set(blue_cols)
    return rec

C='한국저작권보호원'
# ---- build new ordered record list ----
out=[]
for r in range(3,MAXR+1):
    if r==26:
        # transform row26 -> C STORY child (keep L/M/N/O from orig26)
        rec=orig[26]
        rec['cells'][7]={'v':'C STORY','s':copy.copy(basestyle[7]),'hl':None}  # G
        rec['cells'][11]={'v':U('/lay1/bbs/S1T495C234/F/39/list.do'),'s':copy.copy(kbase),
                          'hl':U('/lay1/bbs/S1T495C234/F/39/list.do')}            # K
        rec['_blue']={7,11}  # G new, K changed
        out.append(rec)
        # new KCOPA REPORT child
        kr=newrec({3:C,4:None,5:None,6:None,7:'KCOPA REPORT',
                   11:U('/lay1/bbs/S1T495C236/F/52/list.do'),12:'게시판',13:12,
                   14:'어문,이미지',15:'미부착'},
                  blue_cols={7,11,12,13,14,15}, hl=U('/lay1/bbs/S1T495C236/F/52/list.do'))
        out.append(kr)
    else:
        out.append(orig[r])
        if r==35:
            for lab,path in [('Standalone','/lay1/S1T241C244/contents.do'),
                             ('Web Module','/lay1/S1T241C245/contents.do'),
                             ('내 PC 폰트 점검기(교육기관용)','/lay1/S1T241C246/contents.do')]:
                nr=newrec({3:C,6:lab,11:U(path),12:'페이지',13:1,14:'어문',15:'미부착'},
                          blue_cols={6,11,12,13,14,15}, hl=U(path))
                out.append(nr)

# need eff D/E filled into new rows for re-merge. For GROUP1 new (KCOPA REPORT): D,E inherit.
# For GROUP2 new: D,E inherit (자료마당 / SW 점검도구). We'll fill-down below per column.

# Determine eff D/E/F for each out-row by carrying forward last non-None from above when None.
# (category cols 4..10). New rows left None for D/E/F where they should inherit.
carry={c:None for c in range(4,11)}
for rec in out:
    for c in range(4,11):
        v=rec['cells'][c]['v']
        if v not in (None,''):
            carry[c]=v
            # reset deeper carries
            for d in range(c+1,11): carry[d]=None
        else:
            rec['cells'][c]['_inherit']=carry[c]
# build eff value table
N=len(out)
eff=[[None]*11 for _ in range(N)]  # eff[i][c]
for i,rec in enumerate(out):
    for c in range(4,11):
        v=rec['cells'][c]['v']
        if v in (None,''):
            eff[i][c]=rec['cells'][c].get('_inherit')
        else:
            eff[i][c]=v

# ---- DRY: print resulting tree ----
def show():
    for i,rec in enumerate(out):
        cells=[rec['cells'][c]['v'] for c in (4,5,6,7)]
        cells=['' if v is None else str(v)[:24] for v in cells]
        k=rec['cells'][11]['v'] or ''
        L=rec['cells'][12]['v'] or ''; M=rec['cells'][13]['v']
        blue='*' if rec.get('_blue') else ' '
        tag='NEW' if rec['orig'] is None else str(rec['orig'])
        print(f"{blue}{i+3:>3} [{tag:>3}] D={cells[0]:<8} E={cells[1]:<14} F={cells[2]:<26} G={cells[3]:<14} {L} {M} {k.replace(BASE,'')}")
show()
print('total rows', N, 'DRY' if DRY else 'WRITE')

if DRY: sys.exit(0)
# ---- WRITE ----
# unmerge data merges (keep header rows 1-2)
for mr in list(ws.merged_cells.ranges):
    if mr.min_row>=3: ws.unmerge_cells(str(mr))
# clear data region
for r in range(3, MAXR+50):
    for c in range(2,NCOL+1):
        cell=ws.cell(r,c); cell.value=None; cell.hyperlink=None
# write records
for i,rec in enumerate(out):
    rr=i+3
    for c in range(2,NCOL+1):
        cell=ws.cell(rr,c)
        cell.value=rec['cells'][c]['v']
        cell._style=copy.copy(rec['cells'][c]['s'])
        if rec.get('_blue') and c in rec['_blue']:
            cell.fill=copy.copy(BLUE)
        hl=rec['cells'][c]['hl']
        if c==11 and (hl or rec['cells'][c]['v']):
            tgt=hl or rec['cells'][c]['v']
            if isinstance(tgt,str) and tgt.startswith('http'):
                cell.hyperlink=tgt
    ws.cell(rr,2).value=i+1  # B renumber
    ws.row_dimensions[rr].height=17
# re-merge D..J equal runs with parent-boundary
for c in range(4,11):
    i=0
    while i<N:
        v=eff[i][c]
        if v in (None,''):
            i+=1; continue
        j=i
        while j+1<N and eff[j+1][c]==v and all(eff[j+1][cc]==eff[i][cc] for cc in range(4,c)):
            j+=1
        if j>i:
            ws.merge_cells(start_row=i+3,start_column=c,end_row=j+3,end_column=c)
        i=j+1
wb.save(SRC)
print('SAVED', SRC)