import os
import re
import sqlite3
from collections import defaultdict

# ==== 配置部分 ====
DB_PATH = r"C:\Users\数据库\database.db"
OUTPUT_BASE = r"D:\导出"

# ==== 工具函数 ====
INVALID_FILENAME_CHARS = r'[\\/:*?"<>|]'

def sanitize_filename(name: str) -> str:
    """清理 Windows 非法文件名字符"""
    name = name or "未命名"
    return re.sub(INVALID_FILENAME_CHARS, "_", name).strip()

def ensure_unique_path(path: str) -> str:
    """避免文件名冲突"""
    base, ext = os.path.splitext(path)
    counter = 1
    p = path
    while os.path.exists(p):
        p = f"{base}_{counter}{ext}"
        counter += 1
    return p

def to_text(content) -> str:
    """将 blobs.content 安全转成 str"""
    if content is None:
        return ""
    if isinstance(content, bytes):
        for enc in ("utf-8", "utf-8-sig", "gb18030", "latin-1"):
            try:
                return content.decode(enc, errors="ignore")
            except Exception:
                pass
        return ""  # 实在解码不了就当空
    return str(content)

def clean_content(content: str) -> str:
    """替换指定字符串"""
    return content.replace("1111111111", "222222222222") if content else ""

def is_blank_content(content: str) -> bool:
    """判断正文是否为空（去除空白与不可见字符后）"""
    if not content:
        return True
    # 去除所有空白字符与零宽字符
    s = re.sub(r'[\s\u200b\u200c\u200d\uFEFF]+', '', content)
    return s == "" or s == "0"

YEAR_REGEX = re.compile(r'(?<!\d)(\d{4})(?!\d)')

def extract_years(text: str):
    """从任意日期字符串里提取所有 4 位年份，返回 int 列表"""
    if not text:
        return []
    years = []
    for m in YEAR_REGEX.findall(text):
        y = int(m)
        # 合理年份范围（放宽一些）
        if 1000 <= y <= 2999:
            years.append(y)
    return years

def pick_max_year(date_list):
    """从多个日期字符串里提取所有年份并返回最大年份；若无可用年份则返回 None"""
    all_years = []
    for d in date_list:
        all_years.extend(extract_years(d))
    return max(all_years) if all_years else None

def dedup_preserve_order(items):
    """去重但保留首次出现顺序"""
    seen = set()
    out = []
    for x in items:
        if x not in seen:
            seen.add(x)
            out.append(x)
    return out

# ==== 递归查找（日期/属性通用） ====
def get_recursive_values(note_id, name_val, attrs_map, branches_map):
    """
    递归向上收集 attributes：
      - 当前 note：匹配 (type=='label' AND name==name_val AND isDeleted==0)
      - 父级及以上：在上述基础上另加 isInheritable==1
    防死循环：visited 集
    """
    results = []
    visited = set()
    current_id = note_id
    depth = 0

    while True:
        if current_id in visited:
            break
        visited.add(current_id)

        entries = attrs_map.get(current_id, [])
        for (t, n, isDel, inh), v in entries:
            # 统一成 int 方便比较
            inh_val = 0
            try:
                inh_val = int(inh)
            except Exception:
                inh_val = 0
            if t == "label" and n == name_val and isDel == 0:
                if depth == 0 or inh_val == 1:
                    if v is not None:
                        results.append(str(v))

        parent_id = branches_map.get(current_id)
        if not parent_id or parent_id == "root":
            break

        depth += 1
        current_id = parent_id

    # 去重+简单排序（字符串排序即可；需求未要求重排格式）
    results = dedup_preserve_order(results)
    results = sorted(results)
    return results

# ==== 主逻辑 ====
def main():
    # 确保输出基础目录
    os.makedirs(OUTPUT_BASE, exist_ok=True)

    # 连接数据库并一次性读入内存
    conn = sqlite3.connect(DB_PATH)
    cur = conn.cursor()

    cur.execute("SELECT noteId, type, mime, isDeleted, title, blobId FROM notes")
    notes = [
        dict(noteId=nid, type=t, mime=m, isDeleted=isd, title=ti, blobId=bid)
        for nid, t, m, isd, ti, bid in cur.fetchall()
    ]

    cur.execute("SELECT noteId, type, name, value, isDeleted, isInheritable FROM attributes")
    attrs_map = defaultdict(list)
    for nid, t, n, v, isDel, inh in cur.fetchall():
        # 统一 isDeleted / isInheritable 成 int
        try:
            isDel_i = int(isDel)
        except Exception:
            isDel_i = 0
        try:
            inh_i = int(inh)
        except Exception:
            inh_i = 0
        attrs_map[nid].append(((t, n, isDel_i, inh_i), v))

    cur.execute("SELECT noteId, parentNoteId FROM branches")
    branches_map = {nid: pid for nid, pid in cur.fetchall()}

    cur.execute("SELECT blobId, content FROM blobs")
    blobs_map = {bid: to_text(c) for bid, c in cur.fetchall()}

    conn.close()

    # 过滤 notes（仅 text / code 且未删除）
    target_notes = [n for n in notes if n["type"] in ("text", "code") and int(n["isDeleted"]) == 0]

    for note in target_notes:
        note_id = note["noteId"]
        raw_title = note.get("title") or ""
        title_for_file = sanitize_filename(raw_title)
        blob_id = note["blobId"]

        # ===== 第2行：日期 =====
        date_values = get_recursive_values(note_id, "dateNote", attrs_map, branches_map)
        if date_values:
            date_str = "；".join(date_values)
            max_year = pick_max_year(date_values)  # <- 修复：稳健提取年份
        else:
            date_str = "未知年份"
            max_year = None

        # ===== 第3行：属性 =====
        attr_values = get_recursive_values(note_id, "属性", attrs_map, branches_map)
        attr_str = "；".join(attr_values) if attr_values else ""

        # ===== 正文 =====
        content = clean_content(blobs_map.get(blob_id, ""))
        blank_body = is_blank_content(content)

        # ===== 文件夹分类 =====
        if (not date_values) and blank_body:
            folder = os.path.join(OUTPUT_BASE, "其他")
        elif (not date_values) and (not blank_body):
            folder = os.path.join(OUTPUT_BASE, "未知年份")
        elif date_values and blank_body:
            # 有日期但正文为空：按规则直接放“正文空白”
            folder = os.path.join(OUTPUT_BASE, "正文空白")
        else:
            # 有日期且有正文：优先用最大年份；若没解析出年份则退回“未知年份”
            folder = os.path.join(OUTPUT_BASE, str(max_year)) if max_year else os.path.join(OUTPUT_BASE, "未知年份")

        os.makedirs(folder, exist_ok=True)

        # ===== 文件路径 =====
        file_path = ensure_unique_path(os.path.join(folder, f"{title_for_file}.md"))

        # ===== 写入文件 =====
        with open(file_path, "w", encoding="utf-8") as f:
            f.write(f"标题:{raw_title}\n")
            f.write(f"日期:{date_str}\n")
            f.write(f"属性:{attr_str}\n")
            if content:
                # 第四行开始正文
                f.write(content)

    print("导出完成！结果已保存到：", OUTPUT_BASE)

if __name__ == "__main__":
    main()