#!/usr/bin/env python3
"""
文章编写系统 - Article Writer System
功能：主题管理、素材收集、Prompt编辑、大模型生成文章、分类与标签
"""

import os
import json
import uuid
import time
import sqlite3
import requests
from datetime import datetime
from flask import Flask, render_template, request, jsonify, redirect, url_for, send_from_directory
from werkzeug.utils import secure_filename

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
UPLOAD_DIR = os.path.join(BASE_DIR, 'static', 'uploads')
DB_PATH = os.path.join(BASE_DIR, 'articles.db')
ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg', 'gif', 'webp', 'svg', 'bmp'}

# LLM配置（通过环境变量读取，避免密钥泄露；运行时可从数据库覆盖）
LLM_URL = os.environ.get('LLM_URL', 'https://www.autodl.art/api/v1/chat/completions')
LLM_API_KEY = os.environ.get('LLM_API_KEY', '')
LLM_MODEL = os.environ.get('LLM_MODEL', 'qwen3.6-plus')

app = Flask(__name__)
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 16MB


def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    return conn


def init_db():
    conn = get_db()
    conn.executescript('''
        CREATE TABLE IF NOT EXISTS topics (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            description TEXT DEFAULT '',
            core_idea TEXT DEFAULT '',
            prompt TEXT DEFAULT '',
            created_at TEXT DEFAULT (datetime('now','localtime')),
            updated_at TEXT DEFAULT (datetime('now','localtime'))
        );
        CREATE TABLE IF NOT EXISTS materials (
            id TEXT PRIMARY KEY,
            topic_id TEXT NOT NULL,
            type TEXT NOT NULL DEFAULT 'text',
            content TEXT DEFAULT '',
            file_path TEXT DEFAULT '',
            analysis TEXT DEFAULT '',
            sort_order INTEGER DEFAULT 0,
            created_at TEXT DEFAULT (datetime('now','localtime')),
            FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
        );
        CREATE TABLE IF NOT EXISTS articles (
            id TEXT PRIMARY KEY,
            topic_id TEXT NOT NULL,
            content TEXT DEFAULT '',
            prompt_used TEXT DEFAULT '',
            model TEXT DEFAULT '',
            llm_url TEXT DEFAULT '',
            created_at TEXT DEFAULT (datetime('now','localtime')),
            FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
        );
        CREATE TABLE IF NOT EXISTS llm_config (
            id INTEGER PRIMARY KEY CHECK (id = 1),
            url TEXT DEFAULT '',
            api_key TEXT DEFAULT '',
            model TEXT DEFAULT '',
            updated_at TEXT DEFAULT (datetime('now','localtime'))
        );
        CREATE TABLE IF NOT EXISTS prompt_history (
            id TEXT PRIMARY KEY,
            topic_id TEXT NOT NULL,
            prompt TEXT NOT NULL DEFAULT '',
            model TEXT DEFAULT '',
            created_at TEXT DEFAULT (datetime('now','localtime')),
            FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
        );
        CREATE TABLE IF NOT EXISTS categories (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL UNIQUE,
            sort_order INTEGER DEFAULT 0,
            created_at TEXT DEFAULT (datetime('now','localtime'))
        );
        CREATE TABLE IF NOT EXISTS tags (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL UNIQUE,
            created_at TEXT DEFAULT (datetime('now','localtime'))
        );
        CREATE TABLE IF NOT EXISTS topic_categories (
            topic_id TEXT NOT NULL,
            category_id TEXT NOT NULL,
            PRIMARY KEY (topic_id, category_id),
            FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE,
            FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
        );
        CREATE TABLE IF NOT EXISTS prompt_templates (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            prompt TEXT NOT NULL DEFAULT '',
            is_default INTEGER DEFAULT 0,
            created_at TEXT DEFAULT (datetime('now','localtime')),
            updated_at TEXT DEFAULT (datetime('now','localtime'))
        );
        CREATE TABLE IF NOT EXISTS topic_tags (
            topic_id TEXT NOT NULL,
            tag_id TEXT NOT NULL,
            PRIMARY KEY (topic_id, tag_id),
            FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE,
            FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
        );
        CREATE TABLE IF NOT EXISTS search_config (
            id INTEGER PRIMARY KEY CHECK (id = 1),
            provider TEXT DEFAULT 'tavily',
            api_key TEXT DEFAULT '',
            updated_at TEXT DEFAULT (datetime('now','localtime'))
        );
        CREATE TABLE IF NOT EXISTS search_results (
            id TEXT PRIMARY KEY,
            topic_id TEXT NOT NULL,
            query TEXT NOT NULL,
            title TEXT DEFAULT '',
            url TEXT DEFAULT '',
            snippet TEXT DEFAULT '',
            raw_content TEXT DEFAULT '',
            score REAL DEFAULT 0,
            search_depth TEXT DEFAULT 'basic',
            max_results INTEGER DEFAULT 10,
            created_at TEXT DEFAULT (datetime('now','localtime')),
            FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
        );
    ''')
    # 初始化默认Prompt模板
    default_templates = [
        ('通用写作', '请根据以下素材撰写一篇结构清晰、内容丰富的文章：'),
        ('深度分析', '请根据以下素材撰写一篇深度分析文章，要求：\n1. 深入挖掘素材背后的逻辑和趋势\n2. 提供独到的观点和见解\n3. 论证充分，数据支撑\n4. 结构严谨，层次分明'),
        ('新闻报道', '请根据以下素材撰写一篇新闻报道风格的文章，要求：\n1. 标题醒目，吸引读者\n2. 导语概括核心信息\n3. 正文详实客观\n4. 语言简洁流畅'),
        ('技术解读', '请根据以下素材撰写一篇技术解读文章，要求：\n1. 技术概念清晰解释\n2. 结合实际应用场景\n3. 对比分析优劣势\n4. 展望未来发展趋势'),
        ('自媒体爆款', '请根据以下素材撰写一篇适合自媒体发布的爆款文章，要求：\n1. 标题吸睛，引发好奇\n2. 开头设置悬念或冲突\n3. 内容有干货，有情绪共鸣\n4. 结尾引导互动，促转发'),
    ]
    for tname, tprompt in default_templates:
        conn.execute('INSERT OR IGNORE INTO prompt_templates (id, name, prompt, is_default) VALUES (?, ?, ?, 1)',
                     (str(uuid.uuid4())[:8], tname, tprompt))
    # 初始化默认配置
    conn.execute('INSERT OR IGNORE INTO llm_config (id, url, api_key, model) VALUES (1, ?, ?, ?)',
                 (LLM_URL, LLM_API_KEY, LLM_MODEL))
    conn.commit()
    # 兼容旧库：如 topics 缺少 core_idea 列则补上
    try:
        conn.execute('ALTER TABLE topics ADD COLUMN core_idea TEXT DEFAULT ""')
    except Exception:
        pass
    # 兼容旧库：如 materials 缺少 analysis/selected 列则补上
    try:
        conn.execute('ALTER TABLE materials ADD COLUMN analysis TEXT DEFAULT ""')
    except Exception:
        pass
    try:
        conn.execute('ALTER TABLE materials ADD COLUMN selected INTEGER DEFAULT 1')
    except Exception:
        pass
    # 初始化默认搜索配置
    conn.execute('INSERT OR IGNORE INTO search_config (id, provider, api_key) VALUES (1, ?, ?)',
                 ('tavily', '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'))
    conn.close()


def get_llm_config():
    """从数据库读取LLM配置，如无则用环境变量默认值"""
    conn = get_db()
    row = conn.execute('SELECT * FROM llm_config WHERE id=1').fetchone()
    conn.close()
    if row and row['url']:
        return {'url': row['url'], 'api_key': row['api_key'], 'model': row['model'] or LLM_MODEL}
    return {'url': LLM_URL, 'api_key': LLM_API_KEY, 'model': LLM_MODEL}


def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS


def _topic_with_tags(conn, topic_row):
    """给 topic row 附上分类和标签信息"""
    t = dict(topic_row)
    cats = conn.execute(
        'SELECT c.* FROM categories c JOIN topic_categories tc ON c.id=tc.category_id WHERE tc.topic_id=? ORDER BY c.sort_order',
        (t['id'],)
    ).fetchall()
    t['categories'] = [dict(c) for c in cats]
    tags = conn.execute(
        'SELECT tg.* FROM tags tg JOIN topic_tags tt ON tg.id=tt.tag_id WHERE tt.topic_id=? ORDER BY tg.name',
        (t['id'],)
    ).fetchall()
    t['tags'] = [dict(tg) for tg in tags]
    return t


# ==================== 页面路由 ====================

@app.route('/')
def index():
    conn = get_db()
    topics = conn.execute(
        'SELECT t.*, (SELECT COUNT(*) FROM materials WHERE topic_id=t.id) as mat_count '
        'FROM topics t ORDER BY updated_at DESC'
    ).fetchall()
    categories = conn.execute('SELECT * FROM categories ORDER BY sort_order, name').fetchall()
    tags = conn.execute('SELECT * FROM tags ORDER BY name').fetchall()
    # 附上分类和标签
    topic_list = []
    for t in topics:
        topic_list.append(_topic_with_tags(conn, t))
    conn.close()
    return render_template('index.html', topics=topic_list, categories=categories, tags=tags)


@app.route('/topic/<topic_id>')
def topic_detail(topic_id):
    conn = get_db()
    topic = conn.execute('SELECT * FROM topics WHERE id=?', (topic_id,)).fetchone()
    if not topic:
        conn.close()
        return redirect(url_for('index'))
    topic = _topic_with_tags(conn, topic)
    materials = conn.execute(
        'SELECT * FROM materials WHERE topic_id=? ORDER BY sort_order, created_at',
        (topic_id,)
    ).fetchall()
    articles = conn.execute(
        'SELECT * FROM articles WHERE topic_id=? ORDER BY created_at DESC',
        (topic_id,)
    ).fetchall()
    categories = conn.execute('SELECT * FROM categories ORDER BY sort_order, name').fetchall()
    tags = conn.execute('SELECT * FROM tags ORDER BY name').fetchall()
    conn.close()
    return render_template('topic.html', topic=topic, materials=materials, articles=articles,
                           categories=categories, tags=tags)


# ==================== 主题API ====================

@app.route('/api/topics', methods=['POST'])
def create_topic():
    data = request.get_json(force=True)
    name = data.get('name', '').strip()
    if not name:
        return jsonify({'error': '主题名不能为空'}), 400
    conn = get_db()
    tid = str(uuid.uuid4())[:8]
    now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    conn.execute(
        'INSERT INTO topics (id, name, description, core_idea, prompt, created_at, updated_at) VALUES (?,?,?,?,?,?,?)',
        (tid, name, data.get('description', ''), data.get('core_idea', ''), data.get('prompt', ''), now, now)
    )
    # 关联分类
    for cat_id in data.get('category_ids', []):
        conn.execute('INSERT OR IGNORE INTO topic_categories (topic_id, category_id) VALUES (?,?)', (tid, cat_id))
    # 关联标签（如果传了已有tag id）
    for tag_id in data.get('tag_ids', []):
        conn.execute('INSERT OR IGNORE INTO topic_tags (topic_id, tag_id) VALUES (?,?)', (tid, tag_id))
    # 处理新标签（按名称创建）
    for tag_name in data.get('new_tags', []):
        tag_name = tag_name.strip()
        if not tag_name:
            continue
        existing = conn.execute('SELECT id FROM tags WHERE name=?', (tag_name,)).fetchone()
        if existing:
            conn.execute('INSERT OR IGNORE INTO topic_tags (topic_id, tag_id) VALUES (?,?)', (tid, existing['id']))
        else:
            tag_id = str(uuid.uuid4())[:8]
            conn.execute('INSERT INTO tags (id, name, created_at) VALUES (?,?,?)', (tag_id, tag_name, now))
            conn.execute('INSERT OR IGNORE INTO topic_tags (topic_id, tag_id) VALUES (?,?)', (tid, tag_id))
    conn.commit()
    topic = conn.execute('SELECT * FROM topics WHERE id=?', (tid,)).fetchone()
    result = _topic_with_tags(conn, topic)
    conn.close()
    return jsonify(result)


@app.route('/api/topics/<topic_id>', methods=['PUT'])
def update_topic(topic_id):
    data = request.get_json(force=True)
    conn = get_db()
    now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    fields = []
    values = []
    for key in ['name', 'description', 'core_idea', 'prompt']:
        if key in data:
            fields.append(f'{key}=?')
            values.append(data[key])
    if fields:
        fields.append('updated_at=?')
        values.append(now)
        values.append(topic_id)
        conn.execute(f'UPDATE topics SET {",".join(fields)} WHERE id=?', values)
        conn.commit()

    # 更新分类关联
    if 'category_ids' in data:
        conn.execute('DELETE FROM topic_categories WHERE topic_id=?', (topic_id,))
        for cat_id in data['category_ids']:
            conn.execute('INSERT OR IGNORE INTO topic_categories (topic_id, category_id) VALUES (?,?)', (topic_id, cat_id))

    # 更新标签关联
    if 'tag_ids' in data or 'new_tags' in data:
        conn.execute('DELETE FROM topic_tags WHERE topic_id=?', (topic_id,))
        for tag_id in data.get('tag_ids', []):
            conn.execute('INSERT OR IGNORE INTO topic_tags (topic_id, tag_id) VALUES (?,?)', (topic_id, tag_id))
        for tag_name in data.get('new_tags', []):
            tag_name = tag_name.strip()
            if not tag_name:
                continue
            existing = conn.execute('SELECT id FROM tags WHERE name=?', (tag_name,)).fetchone()
            if existing:
                conn.execute('INSERT OR IGNORE INTO topic_tags (topic_id, tag_id) VALUES (?,?)', (topic_id, existing['id']))
            else:
                tag_id = str(uuid.uuid4())[:8]
                conn.execute('INSERT INTO tags (id, name, created_at) VALUES (?,?,?)', (tag_id, tag_name, now))
                conn.execute('INSERT OR IGNORE INTO topic_tags (topic_id, tag_id) VALUES (?,?)', (topic_id, tag_id))
        # 清理无引用的孤立标签
        conn.execute('DELETE FROM tags WHERE id NOT IN (SELECT tag_id FROM topic_tags)')

    conn.commit()
    topic = conn.execute('SELECT * FROM topics WHERE id=?', (topic_id,)).fetchone()
    result = _topic_with_tags(conn, topic)
    conn.close()
    return jsonify(result)


@app.route('/api/topics/<topic_id>', methods=['DELETE'])
def delete_topic(topic_id):
    conn = get_db()
    # 删除关联的素材文件
    materials = conn.execute('SELECT file_path FROM materials WHERE topic_id=? AND type="image"', (topic_id,)).fetchall()
    for m in materials:
        if m['file_path']:
            fp = os.path.join(BASE_DIR, 'static', m['file_path'])
            if os.path.exists(fp):
                os.remove(fp)
    conn.execute('DELETE FROM topics WHERE id=?', (topic_id,))
    # 清理孤立标签
    conn.execute('DELETE FROM tags WHERE id NOT IN (SELECT tag_id FROM topic_tags)')
    conn.commit()
    conn.close()
    return jsonify({'ok': True})


# ==================== Prompt模板API ====================

@app.route('/api/prompt-templates', methods=['GET'])
def list_prompt_templates():
    conn = get_db()
    rows = conn.execute('SELECT * FROM prompt_templates ORDER BY is_default DESC, name').fetchall()
    conn.close()
    return jsonify([dict(r) for r in rows])


@app.route('/api/prompt-templates', methods=['POST'])
def create_prompt_template():
    data = request.get_json(force=True)
    name = data.get('name', '').strip()
    prompt = data.get('prompt', '').strip()
    if not name:
        return jsonify({'error': '模板名称不能为空'}), 400
    if not prompt:
        return jsonify({'error': '模板内容不能为空'}), 400
    conn = get_db()
    tid = str(uuid.uuid4())[:8]
    now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    conn.execute('INSERT INTO prompt_templates (id, name, prompt, is_default, created_at, updated_at) VALUES (?,?,?,?,?,?)',
                 (tid, name, prompt, 0, now, now))
    conn.commit()
    tmpl = conn.execute('SELECT * FROM prompt_templates WHERE id=?', (tid,)).fetchone()
    conn.close()
    return jsonify(dict(tmpl))


@app.route('/api/prompt-templates/<template_id>', methods=['PUT'])
def update_prompt_template(template_id):
    data = request.get_json(force=True)
    conn = get_db()
    now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    fields = []
    values = []
    for key in ['name', 'prompt']:
        if key in data:
            fields.append(f'{key}=?')
            values.append(data[key])
    if fields:
        fields.append('updated_at=?')
        values.append(now)
        values.append(template_id)
        conn.execute(f'UPDATE prompt_templates SET {",".join(fields)} WHERE id=?', values)
        conn.commit()
    tmpl = conn.execute('SELECT * FROM prompt_templates WHERE id=?', (template_id,)).fetchone()
    conn.close()
    if tmpl:
        return jsonify(dict(tmpl))
    return jsonify({'error': '未找到'}), 404


@app.route('/api/prompt-templates/<template_id>', methods=['DELETE'])
def delete_prompt_template(template_id):
    conn = get_db()
    conn.execute('DELETE FROM prompt_templates WHERE id=?', (template_id,))
    conn.commit()
    conn.close()
    return jsonify({'ok': True})


# ==================== 分类API ====================

@app.route('/api/categories', methods=['GET'])
def list_categories():
    conn = get_db()
    rows = conn.execute(
        'SELECT c.*, (SELECT COUNT(*) FROM topic_categories WHERE category_id=c.id) as topic_count '
        'FROM categories c ORDER BY c.sort_order, c.name'
    ).fetchall()
    conn.close()
    return jsonify([dict(r) for r in rows])


@app.route('/api/categories', methods=['POST'])
def create_category():
    data = request.get_json(force=True)
    name = data.get('name', '').strip()
    if not name:
        return jsonify({'error': '分类名不能为空'}), 400
    conn = get_db()
    existing = conn.execute('SELECT * FROM categories WHERE name=?', (name,)).fetchone()
    if existing:
        conn.close()
        return jsonify({'error': '分类已存在'}), 400
    cid = str(uuid.uuid4())[:8]
    now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    conn.execute('INSERT INTO categories (id, name, sort_order, created_at) VALUES (?,?,?,?)',
                 (cid, name, data.get('sort_order', 0), now))
    conn.commit()
    cat = conn.execute('SELECT * FROM categories WHERE id=?', (cid,)).fetchone()
    conn.close()
    return jsonify(dict(cat))


@app.route('/api/categories/<category_id>', methods=['PUT'])
def update_category(category_id):
    data = request.get_json(force=True)
    conn = get_db()
    fields = []
    values = []
    for key in ['name', 'sort_order']:
        if key in data:
            fields.append(f'{key}=?')
            values.append(data[key])
    if fields:
        values.append(category_id)
        conn.execute(f'UPDATE categories SET {",".join(fields)} WHERE id=?', values)
        conn.commit()
    cat = conn.execute('SELECT * FROM categories WHERE id=?', (category_id,)).fetchone()
    conn.close()
    if cat:
        return jsonify(dict(cat))
    return jsonify({'error': '未找到'}), 404


@app.route('/api/categories/<category_id>', methods=['DELETE'])
def delete_category(category_id):
    conn = get_db()
    conn.execute('DELETE FROM categories WHERE id=?', (category_id,))
    conn.commit()
    conn.close()
    return jsonify({'ok': True})


# ==================== 标签API ====================

@app.route('/api/tags', methods=['GET'])
def list_tags():
    conn = get_db()
    rows = conn.execute(
        'SELECT tg.*, (SELECT COUNT(*) FROM topic_tags WHERE tag_id=tg.id) as topic_count '
        'FROM tags tg ORDER BY tg.name'
    ).fetchall()
    conn.close()
    return jsonify([dict(r) for r in rows])


@app.route('/api/tags', methods=['POST'])
def create_tag():
    data = request.get_json(force=True)
    name = data.get('name', '').strip()
    if not name:
        return jsonify({'error': '标签名不能为空'}), 400
    conn = get_db()
    existing = conn.execute('SELECT * FROM tags WHERE name=?', (name,)).fetchone()
    if existing:
        conn.close()
        return jsonify(dict(existing))  # 已存在直接返回
    tid = str(uuid.uuid4())[:8]
    now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    conn.execute('INSERT INTO tags (id, name, created_at) VALUES (?,?,?)', (tid, name, now))
    conn.commit()
    tag = conn.execute('SELECT * FROM tags WHERE id=?', (tid,)).fetchone()
    conn.close()
    return jsonify(dict(tag))


@app.route('/api/tags/<tag_id>', methods=['PUT'])
def update_tag(tag_id):
    data = request.get_json(force=True)
    conn = get_db()
    tag = conn.execute('SELECT * FROM tags WHERE id=?', (tag_id,)).fetchone()
    if not tag:
        conn.close()
        return jsonify({'error': '标签不存在'}), 404
    name = data.get('name', '').strip()
    if not name:
        conn.close()
        return jsonify({'error': '标签名不能为空'}), 400
    # 检查重名（排除自身）
    existing = conn.execute('SELECT id FROM tags WHERE name=? AND id!=?', (name, tag_id)).fetchone()
    if existing:
        conn.close()
        return jsonify({'error': '标签名已存在'}), 400
    conn.execute('UPDATE tags SET name=? WHERE id=?', (name, tag_id))
    conn.commit()
    tag = conn.execute('SELECT * FROM tags WHERE id=?', (tag_id,)).fetchone()
    conn.close()
    return jsonify(dict(tag))


@app.route('/api/tags/<tag_id>', methods=['DELETE'])
def delete_tag(tag_id):
    conn = get_db()
    conn.execute('DELETE FROM tags WHERE id=?', (tag_id,))
    conn.commit()
    conn.close()
    return jsonify({'ok': True})


# ==================== 素材API ====================

@app.route('/api/topics/<topic_id>/materials', methods=['POST'])
def add_material(topic_id):
    conn = get_db()
    # 检查主题存在
    if not conn.execute('SELECT id FROM topics WHERE id=?', (topic_id,)).fetchone():
        conn.close()
        return jsonify({'error': '主题不存在'}), 404

    mtype = request.form.get('type', 'text')
    mid = str(uuid.uuid4())[:8]
    now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    if mtype == 'image':
        file = request.files.get('file')
        if not file or not allowed_file(file.filename):
            conn.close()
            return jsonify({'error': '不支持的图片格式'}), 400
        ext = secure_filename(file.filename).rsplit('.', 1)[-1].lower()
        filename = f"{topic_id}_{mid}_{int(time.time())}.{ext}"
        filepath = os.path.join(UPLOAD_DIR, filename)
        file.save(filepath)
        conn.execute(
            'INSERT INTO materials (id, topic_id, type, content, file_path, created_at) VALUES (?,?,?,?,?,?)',
            (mid, topic_id, 'image', request.form.get('content', ''), f'uploads/{filename}', now)
        )
    else:
        content = request.form.get('content', '').strip()
        if not content:
            conn.close()
            return jsonify({'error': '文本内容不能为空'}), 400
        conn.execute(
            'INSERT INTO materials (id, topic_id, type, content, created_at) VALUES (?,?,?,?,?)',
            (mid, topic_id, 'text', content, now)
        )

    conn.execute('UPDATE topics SET updated_at=? WHERE id=?', (now, topic_id))
    conn.commit()
    material = conn.execute('SELECT * FROM materials WHERE id=?', (mid,)).fetchone()
    conn.close()
    return jsonify(dict(material))


@app.route('/api/materials/<material_id>', methods=['PUT'])
def update_material(material_id):
    data = request.get_json(force=True)
    conn = get_db()
    fields = []
    values = []
    for key in ['content', 'sort_order', 'analysis', 'selected']:
        if key in data:
            fields.append(f'{key}=?')
            values.append(data[key])
    if fields:
        values.append(material_id)
        conn.execute(f'UPDATE materials SET {",".join(fields)} WHERE id=?', values)
        conn.commit()
    material = conn.execute('SELECT * FROM materials WHERE id=?', (material_id,)).fetchone()
    conn.close()
    if material:
        return jsonify(dict(material))
    return jsonify({'error': '未找到'}), 404


@app.route('/api/materials/<material_id>', methods=['DELETE'])
def delete_material(material_id):
    conn = get_db()
    mat = conn.execute('SELECT * FROM materials WHERE id=?', (material_id,)).fetchone()
    if mat and mat['file_path']:
        fp = os.path.join(BASE_DIR, 'static', mat['file_path'])
        if os.path.exists(fp):
            os.remove(fp)
    conn.execute('DELETE FROM materials WHERE id=?', (material_id,))
    conn.commit()
    conn.close()
    return jsonify({'ok': True})


@app.route('/api/topics/<topic_id>/materials/select', methods=['POST'])
def batch_update_material_selection(topic_id):
    """批量更新素材选中状态"""
    data = request.get_json(force=True)
    conn = get_db()
    now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    # data 格式: {"materials": [{"id": "xxx", "selected": 1/0}, ...]}
    # 或: {"select_all": true/false}
    if 'select_all' in data:
        selected = 1 if data['select_all'] else 0
        conn.execute('UPDATE materials SET selected=? WHERE topic_id=?', (selected, topic_id))
    elif 'materials' in data:
        for item in data['materials']:
            conn.execute('UPDATE materials SET selected=? WHERE id=? AND topic_id=?',
                         (1 if item.get('selected') else 0, item['id'], topic_id))
    conn.execute('UPDATE topics SET updated_at=? WHERE id=?', (now, topic_id))
    conn.commit()
    conn.close()
    return jsonify({'ok': True})


@app.route('/api/materials/<material_id>/analyze', methods=['POST'])
def analyze_material(material_id):
    """大模型分析单个素材，返回分析结果并保存"""
    conn = get_db()
    mat = conn.execute('SELECT * FROM materials WHERE id=?', (material_id,)).fetchone()
    if not mat:
        conn.close()
        return jsonify({'error': '素材不存在'}), 404

    mat_dict = dict(mat)
    # 获取主题核心思路
    topic = conn.execute('SELECT core_idea FROM topics WHERE id=?', (mat_dict['topic_id'],)).fetchone()
    core_idea = dict(topic)['core_idea'] if topic else ''
    conn.close()

    llm_config = get_llm_config()
    if not llm_config['api_key']:
        return jsonify({'error': '请先配置LLM API Key'}), 400

    # 构建核心思路引导语
    core_idea_hint = ''
    if core_idea:
        core_idea_hint = f'\n\n=== 主题核心思路 ===\n{core_idea}\n请围绕上述核心思路进行分析，确保分析结果与文章整体方向一致。'

    # 构建分析prompt
    if mat_dict['type'] == 'text':
        user_content = f"""请对以下文本素材进行深度分析，要求：
1. 概括核心观点和关键信息
2. 分析文本的逻辑结构和论证方式
3. 识别潜在的写作价值和可延伸方向
4. 指出可能的不足或需要补充的信息
{core_idea_hint}

=== 文本素材 ===
{mat_dict['content']}

请直接给出分析结果，不要输出思考过程。"""
    else:
        # 图片素材：发送图片给多模态模型
        note = mat_dict['content'] or '无备注'
        img_path = os.path.join(BASE_DIR, 'static', mat_dict['file_path']) if mat_dict['file_path'] else None

        # 尝试读取图片并编码为base64
        image_content = None
        if img_path and os.path.exists(img_path):
            import base64
            with open(img_path, 'rb') as f:
                img_data = base64.b64encode(f.read()).decode('utf-8')
            ext = mat_dict['file_path'].rsplit('.', 1)[-1].lower()
            mime_map = {'png': 'image/png', 'jpg': 'image/jpeg', 'jpeg': 'image/jpeg',
                        'gif': 'image/gif', 'webp': 'image/webp', 'svg': 'image/svg+xml', 'bmp': 'image/bmp'}
            mime = mime_map.get(ext, 'image/png')
            image_content = f'data:{mime};base64,{img_data}'

        if image_content:
            user_content = [
                {'type': 'text', 'text': f"""请对以下图片素材进行深度分析，要求：
1. 详细描述图片内容（场景、人物、物体、文字等）
2. 分析图片的核心信息和表达意图
3. 识别图片在文章写作中的潜在用途
4. 如有备注，结合备注分析：{note}
{core_idea_hint}

请直接给出分析结果，不要输出思考过程。"""},
                {'type': 'image_url', 'image_url': {'url': image_content}}
            ]
        else:
            user_content = f"""请对以下图片素材备注进行分析（图片文件不可读，仅基于备注）：
备注：{note}
{core_idea_hint}

请尝试基于备注信息给出分析，并建议补充更多图片细节。"""

    try:
        req_body = {
            'model': llm_config['model'],
            'messages': [{'role': 'user', 'content': user_content}],
            'temperature': 0.5,
            'max_tokens': 2048
        }
        resp = requests.post(
            llm_config['url'],
            headers={'Authorization': f"Bearer {llm_config['api_key']}", 'Content-Type': 'application/json'},
            json=req_body,
            timeout=60
        )
        resp.raise_for_status()
        result = resp.json()
        analysis_text = result['choices'][0]['message']['content']
    except Exception as e:
        return jsonify({'error': f'分析失败: {str(e)}'}), 500

    # 保存分析结果
    conn = get_db()
    conn.execute('UPDATE materials SET analysis=? WHERE id=?', (analysis_text, material_id))
    conn.commit()
    conn.close()

    return jsonify({'analysis': analysis_text})


# ==================== 搜索配置与搜索API ====================

def get_search_config():
    """从数据库读取搜索配置"""
    conn = get_db()
    row = conn.execute('SELECT * FROM search_config WHERE id=1').fetchone()
    conn.close()
    if row and row['api_key']:
        return {'provider': row['provider'], 'api_key': row['api_key']}
    return {'provider': 'tavily', 'api_key': ''}


@app.route('/api/search-config', methods=['GET'])
def get_search_config_api():
    config = get_search_config()
    key = config['api_key']
    masked = key[:8] + '***' + key[-4:] if len(key) > 12 else ('***' if key else '')
    return jsonify({
        'provider': config['provider'],
        'api_key_masked': masked,
        'api_key_set': bool(key)
    })


@app.route('/api/search-config', methods=['PUT'])
def update_search_config():
    data = request.get_json(force=True)
    conn = get_db()
    now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    current = conn.execute('SELECT * FROM search_config WHERE id=1').fetchone()
    provider = data.get('provider', current['provider'] if current else 'tavily')
    api_key = data.get('api_key', '') or (current['api_key'] if current else '')
    conn.execute('''
        INSERT INTO search_config (id, provider, api_key, updated_at) VALUES (1, ?, ?, ?)
        ON CONFLICT(id) DO UPDATE SET provider=excluded.provider, api_key=excluded.api_key, updated_at=excluded.updated_at
    ''', (provider, api_key, now))
    conn.commit()
    conn.close()
    return jsonify({'ok': True})


@app.route('/api/search', methods=['POST'])
def search_web():
    """调用 Tavily Search API 进行搜索，结果保存到数据库"""
    data = request.get_json(force=True)
    query = data.get('query', '').strip()
    topic_id = data.get('topic_id', '').strip()
    if not query:
        return jsonify({'error': '搜索关键词不能为空'}), 400

    search_config = get_search_config()
    if not search_config['api_key']:
        return jsonify({'error': '请先配置搜索API Key（首页右上角设置）'}), 400

    max_results = data.get('max_results', 10)
    search_depth = data.get('search_depth', 'basic')
    time_range = data.get('time_range', None)

    try:
        req_body = {
            'query': query,
            'max_results': max_results,
            'search_depth': search_depth,
            'include_raw_content': False,
        }
        if time_range:
            req_body['time_range'] = time_range

        resp = requests.post(
            'https://api.tavily.com/search',
            headers={
                'Authorization': f"Bearer {search_config['api_key']}",
                'Content-Type': 'application/json'
            },
            json=req_body,
            timeout=30
        )
        resp.raise_for_status()
        result = resp.json()
    except requests.exceptions.HTTPError as e:
        return jsonify({'error': f'搜索请求失败: HTTP {e.response.status_code}'}), 502
    except Exception as e:
        return jsonify({'error': f'搜索失败: {str(e)}'}), 500

    # 保存搜索结果到数据库
    if topic_id:
        conn = get_db()
        now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        saved_results = []
        for r in result.get('results', []):
            rid = str(uuid.uuid4())[:8]
            conn.execute(
                'INSERT INTO search_results (id, topic_id, query, title, url, snippet, score, search_depth, max_results, created_at) VALUES (?,?,?,?,?,?,?,?,?,?)',
                (rid, topic_id, query, r.get('title', ''), r.get('url', ''), r.get('content', ''), r.get('score', 0), search_depth, max_results, now)
            )
            saved_results.append({
                'id': rid,
                'title': r.get('title', ''),
                'url': r.get('url', ''),
                'content': r.get('content', ''),
                'score': r.get('score', 0),
            })
        conn.commit()
        conn.close()
        result['saved_results'] = saved_results

    return jsonify(result)


@app.route('/api/search/detail', methods=['POST'])
def search_detail():
    """获取搜索结果详细内容，并更新数据库中的 raw_content"""
    data = request.get_json(force=True)
    query = data.get('query', '').strip()
    url = data.get('url', '').strip()
    result_id = data.get('result_id', '').strip()
    if not query:
        return jsonify({'error': '搜索关键词不能为空'}), 400

    search_config = get_search_config()
    if not search_config['api_key']:
        return jsonify({'error': '请先配置搜索API Key'}), 400

    try:
        req_body = {
            'query': query,
            'max_results': 3,
            'search_depth': 'advanced',
            'include_raw_content': True,
        }
        if url:
            req_body['include_domains'] = [url.split('/')[2]] if '/' in url else [url]

        resp = requests.post(
            'https://api.tavily.com/search',
            headers={
                'Authorization': f"Bearer {search_config['api_key']}",
                'Content-Type': 'application/json'
            },
            json=req_body,
            timeout=45
        )
        resp.raise_for_status()
        result = resp.json()
        # 如果指定了URL，过滤只返回该URL的结果
        if url:
            result['results'] = [r for r in result.get('results', []) if r.get('url') == url]

        # 更新数据库中的 raw_content
        if result_id and result.get('results'):
            raw_content = result['results'][0].get('raw_content', '') or result['results'][0].get('content', '')
            conn = get_db()
            conn.execute('UPDATE search_results SET raw_content=? WHERE id=?', (raw_content, result_id))
            conn.commit()
            conn.close()

        return jsonify(result)
    except Exception as e:
        return jsonify({'error': f'获取详情失败: {str(e)}'}), 500


@app.route('/api/topics/<topic_id>/search-results', methods=['GET'])
def get_topic_search_results(topic_id):
    """获取主题的所有搜索结果"""
    conn = get_db()
    results = conn.execute(
        'SELECT * FROM search_results WHERE topic_id=? ORDER BY created_at DESC',
        (topic_id,)
    ).fetchall()
    conn.close()
    return jsonify([dict(r) for r in results])


@app.route('/api/search-results/<result_id>', methods=['DELETE'])
def delete_search_result(result_id):
    """删除单条搜索结果"""
    conn = get_db()
    conn.execute('DELETE FROM search_results WHERE id=?', (result_id,))
    conn.commit()
    conn.close()
    return jsonify({'ok': True})


@app.route('/api/topics/<topic_id>/search-results', methods=['DELETE'])
def clear_topic_search_results(topic_id):
    """清空主题的所有搜索结果"""
    conn = get_db()
    conn.execute('DELETE FROM search_results WHERE topic_id=?', (topic_id,))
    conn.commit()
    conn.close()
    return jsonify({'ok': True})


# ==================== LLM配置API ====================

@app.route('/api/llm-config', methods=['GET'])
def get_llm_config_api():
    config = get_llm_config()
    # 隐藏部分API Key
    key = config['api_key']
    masked = key[:8] + '***' + key[-4:] if len(key) > 12 else '***'
    return jsonify({
        'url': config['url'],
        'api_key_masked': masked,
        'api_key_set': bool(key),
        'model': config['model']
    })


@app.route('/api/llm-config', methods=['PUT'])
def update_llm_config():
    data = request.get_json(force=True)
    conn = get_db()
    now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    # 读取当前配置
    current = conn.execute('SELECT * FROM llm_config WHERE id=1').fetchone()

    url = data.get('url', current['url'] if current else LLM_URL)
    # 如果前端传了空key则保留旧key
    api_key = data.get('api_key', '') or (current['api_key'] if current else LLM_API_KEY)
    model = data.get('model', current['model'] if current else LLM_MODEL)

    conn.execute('''
        INSERT INTO llm_config (id, url, api_key, model, updated_at) VALUES (1, ?, ?, ?, ?)
        ON CONFLICT(id) DO UPDATE SET url=excluded.url, api_key=excluded.api_key, model=excluded.model, updated_at=excluded.updated_at
    ''', (url, api_key, model, now))
    conn.commit()
    conn.close()
    return jsonify({'ok': True})


# ==================== Prompt历史API ====================

@app.route('/api/topics/<topic_id>/prompt-history', methods=['GET'])
def get_prompt_history(topic_id):
    conn = get_db()
    history = conn.execute(
        'SELECT * FROM prompt_history WHERE topic_id=? ORDER BY created_at DESC LIMIT 20',
        (topic_id,)
    ).fetchall()
    conn.close()
    return jsonify([dict(h) for h in history])


# ==================== 文章生成API ====================

@app.route('/api/topics/<topic_id>/generate', methods=['POST'])
def generate_article(topic_id):
    conn = get_db()
    topic = conn.execute('SELECT * FROM topics WHERE id=?', (topic_id,)).fetchone()
    if not topic:
        conn.close()
        return jsonify({'error': '主题不存在'}), 404

    materials = conn.execute(
        'SELECT * FROM materials WHERE topic_id=? AND selected=1 ORDER BY sort_order, created_at',
        (topic_id,)
    ).fetchall()
    conn.close()

    # 构建素材文本
    material_parts = []
    for m in materials:
        if m['type'] == 'text':
            material_parts.append(f"【文本素材】\n{m['content']}")
        elif m['type'] == 'image':
            desc = m['content'] or '（图片）'
            material_parts.append(f"【图片素材】{desc}")

    materials_text = '\n\n'.join(material_parts) if material_parts else '（暂无素材）'

    # 构建prompt
    custom_prompt = topic['prompt'] or '请根据以下素材撰写一篇结构清晰、内容丰富的文章：'

    # 获取LLM配置
    llm_config = get_llm_config()
    llm_url = llm_config['url']
    llm_key = llm_config['api_key']
    llm_model = llm_config['model']

    if not llm_key:
        return jsonify({'error': '请先配置LLM API Key（首页右上角设置）'}), 400

    user_message = f"""{custom_prompt}

主题：{topic['name']}
{f'主题说明：{topic["description"]}' if topic['description'] else ''}
{f'核心思路：{topic["core_idea"]}' if topic['core_idea'] else ''}

=== 素材 ===
{materials_text}

=== 要求 ===
1. 充分利用提供的素材
2. 文章结构完整，层次分明
3. 语言流畅，逻辑清晰
4. 紧扣核心思路进行创作，保持文章方向与风格一致
5. 不要输出思考过程，直接给出文章内容"""

    # 调用大模型
    try:
        resp = requests.post(
            llm_url,
            headers={
                'Authorization': f'Bearer {llm_key}',
                'Content-Type': 'application/json'
            },
            json={
                'model': llm_model,
                'messages': [
                    {'role': 'user', 'content': user_message}
                ],
                'temperature': 0.7,
                'max_tokens': 8192
            },
            timeout=120
        )
        resp.raise_for_status()
        result = resp.json()
        article_content = result['choices'][0]['message']['content']
    except Exception as e:
        return jsonify({'error': f'生成失败: {str(e)}'}), 500

    # 保存prompt历史
    phid = str(uuid.uuid4())[:8]
    now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    conn = get_db()
    conn.execute(
        'INSERT INTO prompt_history (id, topic_id, prompt, model, created_at) VALUES (?,?,?,?,?)',
        (phid, topic_id, custom_prompt, llm_model, now)
    )

    # 保存文章
    aid = str(uuid.uuid4())[:8]
    conn.execute(
        'INSERT INTO articles (id, topic_id, content, prompt_used, model, llm_url, created_at) VALUES (?,?,?,?,?,?,?)',
        (aid, topic_id, article_content, custom_prompt, llm_model, llm_url, now)
    )
    conn.execute('UPDATE topics SET updated_at=? WHERE id=?', (now, topic_id))
    conn.commit()
    article = conn.execute('SELECT * FROM articles WHERE id=?', (aid,)).fetchone()
    conn.close()
    return jsonify(dict(article))


@app.route('/api/articles/<article_id>', methods=['DELETE'])
def delete_article(article_id):
    conn = get_db()
    conn.execute('DELETE FROM articles WHERE id=?', (article_id,))
    conn.commit()
    conn.close()
    return jsonify({'ok': True})


@app.route('/api/articles/<article_id>', methods=['PUT'])
def update_article(article_id):
    data = request.get_json(force=True)
    conn = get_db()
    conn.execute('UPDATE articles SET content=? WHERE id=?', (data.get('content', ''), article_id))
    conn.commit()
    conn.close()
    return jsonify({'ok': True})


# ==================== 启动 ====================

if __name__ == '__main__':
    os.makedirs(UPLOAD_DIR, exist_ok=True)
    init_db()
    print('=' * 50)
    print('  文章编写系统启动')
    print('  访问地址: http://localhost:19200')
    print('=' * 50)
    app.run(host='0.0.0.0', port=19200, debug=False)