#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""基于用户已下载的知识图谱骨架（importTopic 导出文件），补全内容列并融合本项目知识库。
- 保留用户全部节点（区间式层级：A-G 各层连续排列）
- 为每个三级/四级知识点补：前置/后置/关联(H-J)、标签(K)、认知维度(L)、分类(M)、教学目标(N)、说明(O)
- 内容来源：本项目知识库 110 小节 + 题目全集 1299 题 + 目录
输出：知识图谱导入-人工智能基础-增强版.xlsx（与用户文件同构）"""
import zipfile, re, json, os, html
from collections import defaultdict

SRC = "/home/zyw/Downloads/dl-hub/用户上传/人工智能基础-课程知识点.xlsx"
BASE = "/home/zyw/Downloads/dl-hub/01-AI课程设计项目/知识库问答库"
OUT = "/home/zyw/Downloads/dl-hub/01-AI课程设计项目/应用报告/知识图谱导入-人工智能基础-增强版.xlsx"

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

# 1) 读取用户骨架
z = zipfile.ZipFile(SRC)
xml = z.read('xl/worksheets/sheet1.xml').decode('utf-8')
ssxml = z.read('xl/sharedStrings.xml').decode('utf-8')
sis = re.findall(r'<si>(.*?)</si>', ssxml, re.S)
strings = [''.join(re.findall(r'<t[^>]*>([^<]*)</t>', si)) for si in sis]
cells = re.findall(r'<c r="([A-Z]+)(\d+)"[^>]*>(.*?)</c>', xml, re.S)
rows = defaultdict(dict)
for col, row, body in cells:
    m = re.search(r'<v>(\d+)</v>', body)
    if m and int(m.group(1)) < len(strings):
        rows[int(row)][col] = strings[int(m.group(1))]
    else:
        it = re.search(r'<is>.*?<t[^>]*>([^<]*)</t>', body, re.S)
        if it: rows[int(row)][col] = it.group(1)

maxrow = max(rows)
data_rows = sorted(r for r in rows if r >= 3)
print("用户骨架数据行数:", len(data_rows))

# 2) 构建知识库索引（标题 → 内容）
KB = os.path.join(BASE, "知识库")
kb_index = {}   # 标题精确 → {text, keys, type}
kb_by_key = {}  # 关键词 → 标题
for ch in os.listdir(KB):
    cd = os.path.join(KB, ch)
    if not os.path.isdir(cd): continue
    for fn in os.listdir(cd):
        if not fn.endswith('.md') or fn == '生成日志.md': continue
        m = re.match(r'^(.+?)-\s*(.+)\.md$', fn)
        if not m: continue
        nid, title = m.group(1), m.group(2)
        text = open(os.path.join(cd, fn), encoding='utf-8').read()
        kb_index[title.strip()] = {'nid': nid, 'title': title, 'text': text}
        # 简名（去常见前缀）
        for k in [title.strip(), title.replace('NumPy','').replace('pandas',''), 
                  re.sub(r'^(Python|NumPy|pandas|人工智能|自然语言处理)\s*','',title)]:
            kb_by_key[k] = title

ALLQ = json.load(open(os.path.join(BASE, "题目全集.json"), encoding='utf-8'))
qs_by_cat = defaultdict(list)
for q in ALLQ:
    seg = q['cat'].split('/')[-1]
    qs_by_cat[seg].append(q)

def find_kb(name):
    """按节点名匹配知识库小节"""
    n = name.strip()
    if n in kb_index: return kb_index[n]
    if n in kb_by_key: return kb_index[kb_by_key[n]]
    # 精确/紧凑匹配：标题完整包含节点名，或节点名完整包含标题且长度接近
    for t, v in kb_index.items():
        tl = t.replace(" ", "")
        nl = n.replace(" ", "")
        if len(nl) >= 4 and nl in tl:
            return v
        if len(tl) >= 4 and tl in nl and abs(len(tl)-len(nl)) <= 1:
            return v
        # 去除公共前缀后匹配（如 "Python" "NumPy"）
        for pre in ["Python","NumPy","pandas","人工智能","自然语言处理"]:
            t2 = tl.replace(pre,""); n2 = nl.replace(pre,"")
            if len(n2) >= 4 and n2 in t2:
                return v
    return None

def cognitive(name, text, nid):
    qs = qs_by_cat.get(nid, []) or qs_by_cat.get(re.sub(r'^\d+\.','',nid or ''), [])
    shots = [q['q'] for q in qs if q['type']=='简答题']
    joined = "".join(shots)
    evals = ['评价','论证','立场','你认为','裁决','利弊','方案','对错','观点']
    anas = ['分析','比较','为什么','原因','区别','流程','步骤','影响']
    apps = ['编写','实现','运行','代码','命令','创建','使用','操作','函数','导入']
    if any(k in joined for k in evals): return "评价"
    if any(k in joined for k in anas): return "分析"
    if any(k in joined for k in apps) or '代码' in name or '程序' in name or '函数' in name or '实验' in name:
        return "应用"
    return "理解"

def classify(name):
    apps = ['程序','代码','函数','命令','操作','安装','创建','实验','文件','导入','运行']
    facts = ['发展','历史','概念','概述','应用','简介','风险','挑战','结构','环境','简介','流派','会议','理论']
    if any(k in name for k in apps): return "程序性"
    if any(k in name for k in facts): return "事实性"
    return "概念性"

def labels(name, text):
    labs = ["考点"]
    if any(k in name for k in ['代码','程序','函数','操作','命令','实验','循环','条件','异常']):
        labs.append("难点")
    sz = ['风险','伦理','挑战','发展历程','应用领域','对话','舆论','舆情','人脸','隐私','安全','责任','道德','电影']
    if any(k in name for k in sz) or '课程思政' in (text or ''):
        labs.append("课程思政")
    return ";".join(labs)

def objective(text, name):
    if not text: return f"掌握「{name}」的基本概念与要点"[:200]
    m = re.search(r'## 关键要点\n(.*?)(?=\n##|\Z)', text, re.S)
    pts = [p.lstrip('- ').strip() for p in (m.group(1).splitlines() if m else []) if p.strip()]
    if pts: return "；".join(pts[:3])[:300]
    return f"掌握「{name}」的基本概念与要点"[:200]

def desc(text, name):
    if not text: return ""
    body = re.sub(r'^#.*$','', text, flags=re.M)
    body = re.sub(r'## 关键要点.*$','', body, flags=re.S)
    return re.sub(r'\s+',' ', body).strip()[:600]

# 3) 生成行（区间式：保留用户的 A-G 层级填充，补 H-O）
hdr = ["一级知识点","二级知识点","三级知识点","四级知识点","五级知识点","六级知识点","七级知识点",
       "前置知识点","后置知识点","关联知识点","标签","认知维度","分类","教学目标","知识点说明"]

# 树路径跟踪：维护当前各级节点名，用于对齐每行的层级
current = {c: "" for c in "ABCDEFG"}
pre_map = defaultdict(list)  # 节点 → 前置（章内前一节点 + 上级）
post_map = defaultdict(list)
node_rows = {}  # 完整路径 → 行号（用于关系字段）

body = []
for r in data_rows:
    row = rows[r]
    # 更新当前路径
    for c in "ABCDEFG":
        if row.get(c): current[c] = row[c].strip()
    # 关键：保留用户原样 A-G 填充（区间式），仅当行含任意层级才处理内容列
    path = tuple(current[c] for c in "ABCDEFG" if current[c])
    node_name = current["G"] or current["F"] or current["E"] or current["D"] or current["C"] or current["B"] or current["A"]
    kb = find_kb(node_name)
    # 权重的层级：该行实际填写的最高层级列的节点 = 本行主题
    filled_cols = [c for c in "ABCDEFG" if row.get(c)]
    topic_col = filled_cols[-1] if filled_cols else "C"
    topic = current[topic_col]
    # 内容列仅在"节点级"行补充（B/C/D 级知识点），A 章节行只给标签不补说明
    if topic_col in "ABCD" and topic:
        t_ = kb['text'] if kb else ""
        nid = kb['nid'] if kb else ""
        dim = cognitive(topic, t_, nid)
        clo = classify(topic)
        lab = labels(topic, t_)
        obj = objective(t_, topic) if kb else f"掌握「{topic}」基本内容"
        dsc = desc(t_, topic) if kb else ""
        H = ";".join(pre_map[topic]) if pre_map[topic] else ""
        I = ";".join(post_map[topic]) if post_map[topic] else ""
        J = ""  # 关联知识在第二轮填
    else:
        dim = ""; clo = ""; lab = ""; obj = ""; dsc = ""; H = ""; I = ""; J = ""
    # 保留 A-G 原值
    rowvals = [row.get(c,'') for c in "ABCDEFG"] + [H, I, J, lab, dim, clo, obj, dsc]
    body.append((r, rowvals, topic, topic_col, path))

# 4) 章间 / 章内关系（用二级章节名）
def order_key(name):
    m = re.search(r'第(\d+)章', name)
    return int(m.group(1)) if m else 99

# 先收集二级节名 →（二次）用于章间前置：代码章在分析库之前等
second_level = {}
for r, rowvals, topic, tc, path in body:
    b = rowvals[1] if len(rowvals)>1 else ""
    if b: second_level.setdefault(b, []).append((r, topic, path))

# 5) 重写行：填充关系（章内 B→C→D 顺序）
pre_map2 = defaultdict(list); post_map2 = defaultdict(list)
chap_buckets = defaultdict(list)
for r, rowvals, topic, tc, path in body:
    a = rowvals[0] if rowvals else ""
    chap_buckets[a].append((r, topic, tc, path))
for a, items in chap_buckets.items():
    # 按出现顺序建立 二级节指针
    seq = []  # (二级或节点名)
    for r, topic, tc, path in items:
        if tc == "B":
            seq.append(topic)
    # 章内 B 节序列作为前置链（第二节前置第一节）
    for i in range(1, len(seq)):
        pre_map2[seq[i]].append(seq[i-1])
        post_map2[seq[i-1]].append(seq[i])

# 章间前置
inter_pre = {
 "第2章 python程序设计基础": [],
 "第3章 Numpy数值分析库": ["Python基础知识"],
 "第4章Pandas数据分析库": ["Python基础知识","NumPy数组创建与操作"],
 "第5章 计算机视觉技术与应用": ["人工智能的核心技术"],
 "第6章 智能语音处理与应用": ["人工智能的核心技术"],
 "第7章 自然语言处理与应用": ["人工智能的核心技术"],
 "第8章 生成式大模型应用与文心一言": ["自然语言处理常见应用"],
}
for r, rowvals, topic, tc, path in body:
    a = rowvals[0]
    if a in inter_pre and tc in "CD":
        for pre in inter_pre[a]:
            if pre not in pre_map2[topic]:
                pre_map2[topic].append(pre)

# 6) 最终组装（保持用户原始行顺序与层级填充）
rows_xml = []
hdr_cells = "".join(f'<c r="{c}2" t="inlineStr"><is><t xml:space="preserve">{esc(h)}</t></is></c>' for c, h in zip("ABCDEFGHIJKLMNO", hdr))
rows_xml.append('<row r="2">' + hdr_cells + '</row>')
n_with_content = 0
for r, rowvals, topic, tc, path in body:
    H = ";".join(pre_map2.get(topic, []))
    I = ";".join(post_map2.get(topic, []))
    J = ""
    # 复算内容列（与前面一致）
    kb = find_kb(topic)
    t_ = kb['text'] if kb else ""
    nid = kb['nid'] if kb else ""
    dim = cognitive(topic, t_, nid) if tc in "ABCD" and topic else ""
    clo = classify(topic) if tc in "ABCD" and topic else ""
    lab = labels(topic, t_) if tc in "ABCD" and topic else ""
    obj = objective(t_, topic) if kb and tc in "ABCD" else (f"掌握「{topic}」基本内容" if tc in "ABCD" else "")
    dsc = desc(t_, topic) if kb and tc in "ABCD" else ""
    if tc in "ABCD" and topic and (dsc or lab): n_with_content += 1
    vals = [rowvals[0],rowvals[1],rowvals[2],rowvals[3],rowvals[4],rowvals[5],rowvals[6],
            H,I,J,lab,dim,clo,obj,dsc]
    cells = "".join(f'<c r="{c}{r}" t="inlineStr"><is><t xml:space="preserve">{esc(v)}</t></is></c>' for c, v in zip("ABCDEFGHIJKLMNO", vals))
    rows_xml.append(f'<row r="{r}">{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, os.path.getsize(OUT), "字节")
print("行数:", len(body), "| 有内容填充的知识点:", n_with_content)