import os
import uuid
from datetime import datetime
import pymysql
from dbutils.pooled_db import PooledDB


class MySQLPool:
    def __init__(self, host, port, user, password, database, pool_size=5):
        self.pool = PooledDB(
            creator=pymysql,
            maxconnections=pool_size,
            host=host,
            port=port,
            user=user,
            password=password,
            database=database,
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor,
        )

    def get_connection(self):
        return self.pool.connection()

    def exec(self, sql, params=None):
        conn = self.get_connection()
        try:
            with conn.cursor() as cursor:
                result = cursor.execute(sql, params)
                conn.commit()
                return result
        finally:
            conn.close()


# 初始化连接池
pool = MySQLPool(
    host='www.tianyunperfect.cn',
    port=3306,
    user='memos',
    password='s3ijTsaH5cciP8s8',
    database='memos'
)

base_dir = "/Users/alvin/Downloads/fcf7e810-2472-4a76-9d00-b2e58bf5cf20_Export-b76ceacb-9576-437c-9ad3-fb0cea7a6076/梦记 de2ddb5d286e4dffacb1eb26a4e074be"


def extract_date_and_process(text):
    lines = text.splitlines()

    # 移除第一行
    new_lines = lines[1:]

    # 提取第三行（原输入中的第三行，现在索引1）
    try:
        third_line = new_lines[1]
    except IndexError:
        return None, "输入内容不足，无法提取日期"

    # 解析日期
    if ':' in third_line:
        parts = third_line.split(':', 1)
        date_str = parts[1].strip()
    else:
        return None, "第三行格式不正确，缺少日期"

    # 重新组合字符串
    new_text = '\n'.join(new_lines)

    return date_str, new_text


for root, dirs, files in os.walk(base_dir):
    for file in files:
        if file.endswith(".md"):
            file_path = os.path.join(root, file)
            try:
                with open(file_path, "r", encoding="utf-8") as f:
                    content = f.read()

                    created_ts, full_content = extract_date_and_process(content)

                    full_content = "#梦记 " + full_content

                    # 生成 resource_name uuid 去除特殊字符
                    resource_name = str(uuid.uuid4()).replace('-', '')

                    # 构建 SQL 参数
                    params = (
                        resource_name,
                        1,  # creator_id
                        created_ts,  # created_ts
                        created_ts,  # updated_ts
                        'NORMAL',  # row_status
                        full_content,  # content
                        'PRIVATE'  # visibility
                    )

                    # 执行插入
                    sql = """
                    INSERT INTO memo 
                    (resource_name, creator_id, created_ts, updated_ts, row_status, content, visibility)
                    VALUES (%s, %s, %s, %s, %s, %s, %s)
                    """
                    # 打印 SQL 参数
                    print(f"SQL 参数: {params}")
                    result = pool.exec(sql, params)
                    print(f"成功插入: {file}，影响行数: {result}")

            except Exception as e:
                print(f"处理文件 {file} 时出错: {str(e)}")