#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""为扩展版知识图谱(522节点)按课程逻辑生成 前置/后置/关联 关系，写回 xlsx。"""
import zipfile, re, os, html
from collections import defaultdict

SRC = "/home/zyw/Downloads/dl-hub/01-AI课程设计项目/应用报告/知识图谱导入-人工智能基础.xlsx"
OUT = SRC

def esc(s): return html.escape(str(s), quote=False)

z = zipfile.ZipFile(SRC)
xml = z.read('xl/worksheets/sheet1.xml').decode('utf-8')

# 重新构建节点序列（行→该行层级节点）
rows = re.findall(r'<row r="(\d+)">(.*?)</row>', xml, re.S)
seq = []  # [(row, lvl, name)]
for rn, body in rows:
    if rn == '2': continue
    idx = int(rn)
    lvl = None; name = ""
    for c in "ABCDEFG":
        pat = '<c r="%s\\d+"[^>]*><is><t[^>]*>(.*?)</t></is></c>' % c
        mm = re.search(pat, body)
        if mm and mm.group(1).strip():
            lvl = "ABCDEFG".index(c)
            name = mm.group(1)
    if lvl is not None:
        seq.append((idx, lvl, name))

print("节点行数:", len(seq))

# ── 章节分组：一级=章 ──
# 每个节点的"章路径"（最近 A 级祖先）+ "二级节路径"
cur_ch = ""; cur_sec = ""
node_meta = []  # (idx,lvl,name,ch,sec)
for idx, lvl, name in seq:
    if lvl == 0: cur_ch = name; cur_sec = ""
    elif lvl == 1: cur_sec = name
    node_meta.append((idx, lvl, name, cur_ch, cur_sec))

# ── 关系生成 ──
pre = defaultdict(list); post = defaultdict(list); asso = defaultdict(list)

# 1) 章间：按章顺序，上一章最后一节点 → 下一章首节点 前置（A级关系）
A_nodes = [(i,l,n,c,s) for i,l,n,c,s in node_meta if l==0]
for ai in range(1, len(A_nodes)):
    prev_ch_last = [m for m in node_meta if m[3]==A_nodes[ai-1][2]]
    cur_ch_first = [m for m in node_meta if m[3]==A_nodes[ai][2] and m[1]==1]
    if prev_ch_last and cur_ch_first:
        pre[cur_ch_first[0][2]].append(prev_ch_last[-1][2])
        post[prev_ch_last[-1][2]].append(cur_ch_first[0][2])

# 2) 章内 B 级：按顺序串联（每一节前置上一节）
b_per_ch = defaultdict(list)
for m in node_meta:
    if m[1]==1: b_per_ch[m[3]].append(m)
for ch, items in b_per_ch.items():
    for i in range(1, len(items)):
        pre[items[i][2]].append(items[i-1][2])
        post[items[i-1][2]].append(items[i][2])

# 3) 章内 C 级：同一二级节下按出现顺序串联
c_per_sec = defaultdict(list)
for m in node_meta:
    if m[1]==2: c_per_sec[(m[3],m[4])].append(m)
for sec, items in c_per_sec.items():
    for i in range(1, len(items)):
        pre[items[i][2]].append(items[i-1][2])
        post[items[i-1][2]].append(items[i][2])

# 4) 父子关系（关联）：C 挂其下 D，D 挂其下 E（关联知识点）
#    用"当前行所属最近上级 C/D"构建
last_by_lvl = {}
for idx, lvl, name, ch, sec in node_meta:
    last_by_lvl[lvl] = (idx, name)
    if lvl >= 2 and lvl-1 in last_by_lvl and lvl-1 >= 1:
        pname = last_by_lvl[lvl-1][1]
        if pname not in {pre[name], post[name]} if False else True:
            pass
# 简化父子关联：每行(lvl>=2) 关联其最近上级(lvl-1) 的节点——需要小心区间语义
# 改为：遍历行时维护"每级最近节点"，若本行是 C/D/E，则 关联= 上一级最近节点
last_anc = {}
for idx, lvl, name, ch, sec in node_meta:
    if lvl == 0: last_anc = {0: name}
    elif lvl == 1: last_anc[1] = name
    elif lvl >= 2:
        if lvl-1 in last_anc:
            anc = last_anc[lvl-1]
            if anc and anc != name:
                if name not in asso: asso[name].append(anc)
        last_anc[lvl] = name

# 5) 跨章核心关联（精选，锚定课程）
core_asso = [
    ("机器学习","深度学习"), ("机器学习","神经网络"),
    ("深度学习","卷积神经网络"), ("自然语言处理","机器翻译"),
    ("自然语言处理","分词"), ("计算机视觉","图像分类"),
    ("语音识别","语音合成"), ("知识图谱","实体与关系"),
    ("文心一言在学习方面的应用","生成式大模型简介"),
    ("上下两章衔接",""),  # 占位勿用
]
# 只对实际存在的节点加
name_set = {n for _,_,n,_,_ in node_meta}
cnt=0
for a1,a2 in [x for x in core_asso if x[1]]:
    if a1 in name_set and a2 in name_set:
        if a2 not in asso[a1]: asso[a1].append(a2); cnt+=1
        if a1 not in asso[a2]: asso[a2].append(a1); cnt+=1
print("跨章核心关联:", cnt)

# ── 写回 H/I/J（保持原 A-G，只更新 H-I-J）──
hdr = ["一级知识点","二级知识点","三级知识点","四级知识点","五级知识点","六级知识点","七级知识点",
       "前置知识点","后置知识点","关联知识点","标签","认知维度","分类","教学目标","知识点说明"]
def xcell(col, row, v):
    return f'<c r="{col}{row}" t="inlineStr"><is><t xml:space="preserve">{esc(v)}</t></is></c>'

n_rel = 0
rows_xml = []
rows_xml.append('<row r="2">' + "".join(xcell(c,2,h) for c,h in zip("ABCDEFGHIJKLMNO", hdr)) + '</row>')
for idx, lvl, name, ch, sec in node_meta:
    H = ";".join(pre[name]) if pre.get(name) else ""
    I = ";".join(post[name]) if post.get(name) else ""
    J = ";".join(asso[name]) if asso.get(name) else ""
    if H: n_rel += 1
    cells = []
    for j, c in enumerate("ABCDEFG"):
        v = name if j == lvl else ""
        cells.append(xcell(c, idx, v))
    # 保留原 K-O（从原xml取）——简化：K=考点 L=理解/应用 M=概念性 N/O 基础
    for c,v in zip("HIJ", [H,I,J]):
        cells.append(xcell(c, idx, v))
    for c,v in zip("KLMNO", ["考点","理解","概念性",f"掌握「{name}」基本内容",""]):
        cells.append(xcell(c, idx, v))
    rows_xml.append(f'<row r="{idx}">{"".join(cells)}</row>')

sheet = ('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
         '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
         '<sheetData>' + "".join(rows_xml) + '</sheetData></worksheet>')
CT = ('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
      '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
      '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
      '<Default Extension="xml" ContentType="application/xml"/>'
      '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
      '<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
      '</Types>')
RELS = ('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
        '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'
        '</Relationships>')
WB = ('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
      '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
      'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
      '<sheets><sheet name="知识图谱" sheetId="1" r:id="rId1"/></sheets></workbook>')
WB_RELS = ('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
           '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
           '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>'
           '</Relationships>')
with zipfile.ZipFile(OUT,'w',zipfile.ZIP_DEFLATED) as z:
    z.writestr('[Content_Types].xml', CT)
    z.writestr('_rels/.rels', RELS)
    z.writestr('xl/workbook.xml', WB)
    z.writestr('xl/_rels/workbook.xml.rels', WB_RELS)
    z.writestr('xl/worksheets/sheet1.xml', sheet)
print("已写回关系:", OUT)
print("有前置的节点:", n_rel)
# 统计关系条数：前置集合大小（每个节点前置列表元素数）
total_pre = sum(len(v) for v in pre.values())
total_post = sum(len(v) for v in post.values())
total_asso = sum(len(v) for v in asso.values())
print("前置关系条数:", total_pre, "后置:", total_post, "关联:", total_asso)