genealogy-app/app.py

import os
import pymysql
import requests
import json
import re
import threading
import urllib3
from flask import Flask, render_template, request, redirect, url_for, session, flash, jsonify, Response, stream_with_context
from werkzeug.utils import secure_filename
from oss_utils import upload_to_oss
from ocr_utils import extract_page_number
import time
from datetime import datetime

# Suppress InsecureRequestWarning
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

app = Flask(__name__)
app.secret_key = 'genealogy_secret_key'
app.config['UPLOAD_FOLDER'] = 'uploads'
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)

# 数据库配置
DB_CONFIG = {
    "host": "rm-f8ze60yirdj8786u2.mysql.rds.aliyuncs.com",
    "port": 3306,
    "user": "root",
    "password": "csqz@20255",
    "db": "csqz-client",
    "charset": "utf8mb4",
    "cursorclass": pymysql.cursors.DictCursor
}

def get_db_connection():
    return pymysql.connect(**DB_CONFIG)

def format_timestamp(ts):
    if not ts: return '未知'
    try:
        # 兼容秒和毫秒
        if ts > 10000000000: # 超过2286年的秒数，通常认为是毫秒
            ts = ts / 1000
        return time.strftime('%Y-%m-%d', time.localtime(ts))
    except:
        return '未知'

def manual_simplify(text):
    """
    Simple fallback for common Traditional to Simplified conversion 
    if AI fails to convert specific characters.
    """
    if not text: return text
    mapping = {
        '學': '学', '國': '国', '萬': '万', '寶': '宝', '興': '兴', 
        '華': '华', '會': '会', '葉': '叶', '藝': '艺', '號': '号',
        '處': '处', '見': '见', '視': '视', '言': '言', '語': '语',
        '貝': '贝', '車': '车', '長': '长', '門': '门', '韋': '韦',
        '頁': '页', '風': '风', '飛': '飞', '食': '食', '馬': '马',
        '魚': '鱼', '鳥': '鸟', '麥': '麦', '黃': '黄', '齊': '齐',
        '齒': '齿', '龍': '龙', '龜': '龟', '壽': '寿', '榮': '荣',
        '愛': '爱', '慶': '庆', '衛': '卫', '賢': '贤', '義': '义',
        '禮': '礼', '樂': '乐', '靈': '灵', '滅': '灭', '氣': '气',
        '智': '智', '信': '信', '仁': '仁', '勇': '勇', '嚴': '严',
        '銳': '锐', '優': '优', '楊': '杨', '吳': '吴', '銀': '银'
    }
    
    result = ""
    for char in text:
        result += mapping.get(char, char)
    return result

def clean_name(name):
    """
    Clean name according to Liu family genealogy rules:
    1. If name is '学公' or '留学公', keep 'Gong' (exception).
    2. Otherwise, if name ends with '公', remove '公'.
    3. If name does not start with '留', prepend '留'.
    """
    if not name: return name
    name = name.strip()
    
    # Pre-process: Ensure Simplified Chinese for specific chars
    name = manual_simplify(name)
    
    # 1. Check exceptions (names that SHOULD keep 'Gong')
    exceptions = ['学公', '留学公']
    
    if name in exceptions:
        if not name.startswith('留'):
            name = '留' + name
        return name
        
    # 2. General Rule: Remove 'Gong' suffix
    if name.endswith('公'):
        name = name[:-1]
        
    # 3. Ensure 'Liu' surname
    if not name.startswith('留'):
        name = '留' + name
        
    return name

def process_ai_task(record_id, image_url):
    """Background task to process image with AI and store result."""
    print(f"[AI Task] Starting task for record {record_id}...")
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("UPDATE genealogy_records SET ai_status = 1 WHERE id = %s", (record_id,))
        conn.commit()
        print(f"[AI Task] Status updated to 'Processing' for record {record_id}")

        api_key = "a1800657-9212-4afe-9b7c-b49f015c54d3"
        api_url = "https://ark.cn-beijing.volces.com/api/v3/responses"
        
        prompt = """
        请分析这张家谱图片，提取其中关于人物的信息。
        请务必将繁体字转换为简体字（original_name 字段除外）。
        特别注意：'name' 字段必须是纯简体中文，不能包含繁体字（例如：'學'应转换为'学'，'劉'应转换为'刘'，'萬'应转换为'万'）。
        请提取以下字段（如果存在）：
        - original_name: 原始姓名（严格保持图片上的繁体字，不做任何修改或转换）
        - name: 简体姓名（必须转换为简体中文，去除不需要的敬称）
        - sex: 性别（男/女）
        - birthday: 出生日期（尝试转换为YYYY-MM-DD格式，如果无法确定年份可只填月日）
        - father_name: 父亲姓名
        - spouse_name: 配偶姓名
        - generation: 第几世/代数
        - name_word: 字辈（例如名字为“学勤公”，“学”为字辈；提取名字中的字辈信息）
        - education: 学历/功名
        - title: 官职/称号
        
        请严格以JSON列表格式返回，不要包含Markdown代码块标记（如 ```json ... ```），直接返回JSON数组。
        如果包含多个人物，请都提取出来。
        Do not output any reasoning or explanation, just the JSON.
        """

        payload = {
            "model": "doubao-seed-1-8-251228",
            "stream": True,  # Streaming for robust handling
            "input": [
                {
                    "role": "user",
                    "content": [
                        {"type": "input_image", "image_url": image_url},
                        {"type": "input_text", "text": prompt}
                    ]
                }
            ]
        }
        
        headers = {
            "Authorization": f"Bearer {api_key}",
            "Content-Type": "application/json"
        }
        
        max_retries = 3
        last_exception = None
        
        for attempt in range(max_retries):
            try:
                print(f"[AI Task] Attempt {attempt+1}/{max_retries} connecting to API for record {record_id}...")
                response = requests.post(
                    api_url, 
                    json=payload, 
                    headers=headers, 
                    timeout=1200, 
                    stream=True,
                    verify=False,
                    proxies={"http": None, "https": None}
                )
                
                if response.status_code == 200:
                    print(f"[AI Task] Connection established for record {record_id}, receiving stream...")
                    full_content = ""
                    
                    for line in response.iter_lines():
                        if not line: continue
                        line_str = line.decode('utf-8')
                        
                        # Debug: Print full line to understand event flow
                        print(f"[AI Task Debug] Raw Line: {line_str[:500]}") # Truncate very long lines

                        if line_str.startswith('data: '):
                            json_str = line_str[6:]
                            if json_str.strip() == '[DONE]':
                                print("[AI Task Debug] Received [DONE]")
                                break
                            try:
                                chunk = json.loads(json_str)
                                chunk_type = chunk.get('type')
                                
                                # Standard OpenAI format (choices)
                                if 'choices' in chunk and len(chunk['choices']) > 0:
                                    delta = chunk['choices'][0].get('delta', {})
                                    if 'content' in delta:
                                        full_content += delta['content']
                                
                                # Doubao/Volcengine specific formats (delta)
                                elif chunk_type == 'response.text.delta':
                                    full_content += chunk.get('delta', '')
                                
                                # Check response.completed if empty
                                elif chunk_type == 'response.completed' and not full_content:
                                    output = chunk.get('response', {}).get('output', [])
                                    for item in output:
                                        # Also extract from reasoning if it contains JSON-like text
                                        if item.get('type') == 'reasoning':
                                            summary = item.get('summary', [])
                                            for sum_item in summary:
                                                if sum_item.get('type') == 'summary_text':
                                                    full_content += sum_item.get('text', '')
                                        
                                        elif item.get('type') == 'message':
                                            content = item.get('content')
                                            if isinstance(content, str):
                                                full_content += content
                                            elif isinstance(content, list):
                                                for part in content:
                                                    if isinstance(part, dict) and part.get('type') == 'text':
                                                        full_content += part.get('text', '')
                                
                                # Fallback: output_item.added
                                elif chunk_type == 'response.output_item.added':
                                    item = chunk.get('item', {})
                                    if item.get('role') == 'assistant':
                                        content_field = item.get('content', [])
                                        if isinstance(content_field, str):
                                            full_content += content_field
                                        elif isinstance(content_field, list):
                                            for part in content_field:
                                                if isinstance(part, dict) and part.get('type') == 'text':
                                                    full_content += part.get('text', '')

                            except Exception as e:
                                print(f"[AI Task] Chunk parse error: {e}")
                        else:
                             # Fallback for non-SSE
                             try:
                                chunk = json.loads(line_str)
                                if 'choices' in chunk and len(chunk['choices']) > 0:
                                    content = chunk['choices'][0]['message']['content']
                                    full_content += content
                             except:
                                pass
                    
                    print(f"[AI Task] Stream finished. Content length: {len(full_content)}")
                    if len(full_content) == 0:
                         print(f"[AI Task] WARNING: No content received from AI stream.")
                         # Continue to JSON parse to fail gracefully
                    
                    # Clean JSON
                    try:
                        # 1. Try finding [...] array
                        start = full_content.find('[')
                        end = full_content.rfind(']')
                        
                        # 2. If not found, try finding {...} object and wrap it
                        is_single_object = False
                        if start == -1 or end == -1 or end <= start:
                            start = full_content.find('{')
                            end = full_content.rfind('}')
                            is_single_object = True

                        if start != -1 and end != -1 and end > start:
                            content_clean = full_content[start:end+1]
                        else:
                            # Fallback to regex or raw
                            content_clean = re.sub(r'^```json\s*', '', full_content)
                            content_clean = re.sub(r'```$', '', content_clean)

                        parsed = json.loads(content_clean)
                        
                        # Normalize single object to list
                        if is_single_object and isinstance(parsed, dict):
                            parsed = [parsed]
                            content_clean = json.dumps(parsed, ensure_ascii=False)
                        elif isinstance(parsed, dict) and not isinstance(parsed, list):
                             # Just in case json.loads parsed a dict even if we looked for []
                             parsed = [parsed]
                             content_clean = json.dumps(parsed, ensure_ascii=False)

                        # Clean names in parsed content
                        if isinstance(parsed, list):
                            for person in parsed:
                                # Process Name: 'name' is Simplified from AI, 'original_name' is Traditional/Raw from AI
                                simplified_name = person.get('name', '')
                                original_name = person.get('original_name', '')
                                
                                # Apply clean logic to simplified name（本人生效：拼接“留”姓、处理“公”）
                                cleaned_simplified = clean_name(simplified_name)
                                person['simplified_name'] = cleaned_simplified
                                
                                # Store raw name in 'name' field (as requested)
                                if original_name:
                                    person['name'] = original_name
                                else:
                                    # Fallback: if no original_name returned, use the uncleaned name as 'name'
                                    # or keep existing logic. But user wants raw in 'name'.
                                    # If AI didn't return original_name, 'name' is likely simplified.
                                    pass # Keep 'name' as is (which is Simplified) if original_name missing

                                # Father name：同族，需要按“留”姓规则清洗
                                if 'father_name' in person and person['father_name']:
                                    person['father_name'] = clean_name(person['father_name'])

                                # Spouse name：只做繁转简，不拼接“留”姓，也不去“公”
                                if 'spouse_name' in person and person['spouse_name']:
                                    person['spouse_name'] = manual_simplify(person['spouse_name'])
                        
                        # Re-serialize
                        content_clean = json.dumps(parsed, ensure_ascii=False)

                        with conn.cursor() as cursor:
                            cursor.execute("UPDATE genealogy_records SET ai_status = 2, ai_content = %s WHERE id = %s", (content_clean, record_id))
                        conn.commit()
                        print(f"[AI Task] SUCCESS: Record {record_id} processed and saved.")
                        return # Success
                    except json.JSONDecodeError:
                        print(f"[AI Task] WARNING: JSON Parse Error for record {record_id}. Saving raw content.")
                        with conn.cursor() as cursor:
                            cursor.execute("UPDATE genealogy_records SET ai_status = 3, ai_content = %s WHERE id = %s", (f"JSON Parse Error. Raw: {full_content}", record_id))
                        conn.commit()
                        return # Success (technically API worked, just bad content)
                else:
                    print(f"[AI Task] API Error {response.status_code} for record {record_id}: {response.text[:100]}...")
                    if response.status_code >= 500:
                        raise Exception(f"Server Error {response.status_code}")
                    
                    with conn.cursor() as cursor:
                        cursor.execute("UPDATE genealogy_records SET ai_status = 3, ai_content = %s WHERE id = %s", (f"API Error: {response.text}", record_id))
                    conn.commit()
                    return

            except Exception as e:
                print(f"[AI Task] Attempt {attempt+1} failed for record {record_id}: {e}")
                last_exception = e
                if attempt < max_retries - 1:
                    wait_time = 2 * (attempt + 1)
                    print(f"[AI Task] Waiting {wait_time}s before retry...")
                    time.sleep(wait_time)
        
        raise last_exception or Exception("Unknown error")
            
    except Exception as e:
        print(f"[AI Task] FINAL FAILURE for record {record_id}: {e}")
        try:
            with conn.cursor() as cursor:
                cursor.execute("UPDATE genealogy_records SET ai_status = 3, ai_content = %s WHERE id = %s", (f"Max Retries Exceeded. Error: {str(e)}", record_id))
            conn.commit()
        except:
            pass
    finally:
        conn.close()
        print(f"[AI Task] Task finished for record {record_id}")

@app.route('/manager/')
def index():
    if 'user_id' not in session:
        return redirect(url_for('login'))
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 获取家谱图片记录 (上传管理)
            cursor.execute("SELECT * FROM genealogy_records ORDER BY upload_time DESC")
            records = cursor.fetchall()
    finally:
        conn.close()
    
    return render_template('index.html', records=records)

@app.route('/manager/members')
def members():
    if 'user_id' not in session:
        return redirect(url_for('login'))
    
    search_name = request.args.get('name', '').strip()
    page = request.args.get('page', 1, type=int)
    per_page = 10
    offset = (page - 1) * per_page
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 1. Get total count
            if search_name:
                cursor.execute("SELECT COUNT(*) as count FROM family_member_info WHERE name LIKE %s", (f"%{search_name}%",))
            else:
                cursor.execute("SELECT COUNT(*) as count FROM family_member_info")
            
            result = cursor.fetchone()
            total = result['count'] if result else 0
            total_pages = (total + per_page - 1) // per_page
            
            # 2. Get paginated results, ordered by modified_time DESC (or create_time if modified is null/same)
            # Using COALESCE to ensure sort works even if modified_time is NULL
            order_clause = "ORDER BY COALESCE(modified_time, create_time) DESC"
            
            if search_name:
                sql = f"SELECT * FROM family_member_info WHERE name LIKE %s {order_clause} LIMIT %s OFFSET %s"
                cursor.execute(sql, (f"%{search_name}%", per_page, offset))
            else:
                sql = f"SELECT * FROM family_member_info {order_clause} LIMIT %s OFFSET %s"
                cursor.execute(sql, (per_page, offset))
            
            members = cursor.fetchall()
            
            # 格式化日期
            for m in members:
                m['birthday_str'] = format_timestamp(m.get('birthday'))
                # 格式化创建时间 (针对 TIMESTAMP 字段)
                if m.get('create_time'):
                    m['create_time_str'] = m['create_time'].strftime('%Y-%m-%d')
                if m.get('modified_time'):
                    m['modified_time_str'] = m['modified_time'].strftime('%Y-%m-%d %H:%M')
                    
    finally:
        conn.close()
    
    return render_template('members.html', members=members, search_name=search_name, page=page, total_pages=total_pages, total=total)

@app.route('/manager/tree')
def tree():
    if 'user_id' not in session:
        return redirect(url_for('login'))
    return render_template('tree.html')

@app.route('/manager/api/tree_data')
def tree_data():
    if 'user_id' not in session:
        return jsonify({"error": "Unauthorized"}), 401
        
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 获取所有成员
            cursor.execute("SELECT id, name, simplified_name, sex FROM family_member_info")
            members = cursor.fetchall()
            # 获取所有关系 (1:父子 2:母子 10:夫妻 11:兄弟 12:姐妹)
            cursor.execute("SELECT parent_mid, child_mid, relation_type FROM family_relation_info")
            relations = cursor.fetchall()
            
            return jsonify({"members": members, "relations": relations})
    finally:
        conn.close()

@app.route('/manager/api/save_relation', methods=['POST'])
def save_relation():
    if 'user_id' not in session:
        return jsonify({"success": False, "message": "Unauthorized"}), 401
    
    data = request.json
    source_mid = data.get('source_mid') # The member being dragged
    target_mid = data.get('target_mid') # The member being dropped onto
    rel_type = int(data.get('relation_type'))
    sub_rel_type = int(data.get('sub_relation_type', 0))
    
    if not source_mid or not target_mid or not rel_type:
        return jsonify({"success": False, "message": "参数不完整"}), 400

    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 简单处理：如果是父子/母子关系
            # target_mid 是父辈，source_mid 是子辈
            parent_mid = target_mid
            child_mid = source_mid
            gen_diff = 1
            
            if rel_type == 10: # 夫妻
                # 夫妻关系中，我们通常把关联人设为 parent_mid
                parent_mid = target_mid
                child_mid = source_mid
                gen_diff = 0
            elif rel_type in [11, 12]: # 兄弟姐妹
                # 这里逻辑上比较复杂，通常兄弟姐妹有共同父母。
                # 简化处理：暂时存为同级关系 (gen_diff=0)
                parent_mid = target_mid
                child_mid = source_mid
                gen_diff = 0
            
            # 删除旧关系
            cursor.execute("DELETE FROM family_relation_info WHERE source_mid = %s", (source_mid,))
            
            # 插入新关系
            sql = """
                INSERT INTO family_relation_info 
                (parent_mid, child_mid, relation_type, sub_relation_type, source_mid, generation_diff) 
                VALUES (%s, %s, %s, %s, %s, %s)
            """
            cursor.execute(sql, (parent_mid, child_mid, rel_type, sub_rel_type, source_mid, gen_diff))
            conn.commit()
            return jsonify({"success": True, "message": "关系已保存"})
    except Exception as e:
        return jsonify({"success": False, "message": str(e)}), 500
    finally:
        conn.close()

@app.route('/manager/api/check_relations', methods=['POST'])
def check_relations():
    if 'user_id' not in session:
        return jsonify({"success": False, "message": "Unauthorized"}), 401
    
    data = request.json
    people = data.get('people', [])
    if not people:
        return jsonify({"success": False, "matches": {}})

    conn = get_db_connection()
    matches = {}
    
    try:
        with conn.cursor() as cursor:
            # Collect all father names and spouse names to query
            names_to_check = set()
            for p in people:
                if p.get('father_name'): names_to_check.add(p['father_name'])
                if p.get('spouse_name'): names_to_check.add(p['spouse_name'])
            
            if not names_to_check:
                return jsonify({"success": True, "matches": {}})

            # Query DB
            format_strings = ','.join(['%s'] * len(names_to_check))
            if names_to_check:
                sql = "SELECT id, name, simplified_name, sex, birthday FROM family_member_info WHERE name IN (%s) OR simplified_name IN (%s)" % (format_strings, format_strings)
                cursor.execute(sql, tuple(names_to_check) * 2)
                results = cursor.fetchall()
            else:
                results = []
            
            # Organize by name
            db_map = {} # name -> [list of members]
            for r in results:
                # Add under 'name' (Traditional/Old Simplified)
                if r['name'] not in db_map: db_map[r['name']] = []
                db_map[r['name']].append(r)
                
                # Add under 'simplified_name' if exists
                if r.get('simplified_name'):
                     sname = r['simplified_name']
                     if sname not in db_map: db_map[sname] = []
                     # Avoid duplicates if simplified_name is same as name?
                     # The list might contain same object reference, which is fine.
                     if sname != r['name']:
                         db_map[sname].append(r)
            
            # Build matches for each input person
            for index, p in enumerate(people):
                p_match = {}
                
                # Check Father
                fname = p.get('father_name')
                if fname and fname in db_map:
                    candidates = db_map[fname]
                    # Filter: Father should be Male usually, and older than child (if birthday available)
                    valid_fathers = [c for c in candidates if c['sex'] == 1]
                    if valid_fathers:
                        p_match['father'] = valid_fathers # Return all candidates
                
                # Check Spouse
                sname = p.get('spouse_name')
                if sname and sname in db_map:
                    candidates = db_map[sname]
                    # Filter: Spouse usually opposite sex
                    target_sex = 1 if p.get('sex') == '女' else 2
                    valid_spouses = [c for c in candidates if c['sex'] == target_sex]
                    if valid_spouses:
                        p_match['spouse'] = valid_spouses

                if p_match:
                    matches[index] = p_match

        return jsonify({"success": True, "matches": matches})
    finally:
        conn.close()

@app.route('/manager/add_member', methods=['GET', 'POST'])
def add_member():
    if 'user_id' not in session:
        return redirect(url_for('login'))
        
    conn = get_db_connection()
    try:
        # Check for source_record_id (from GET or POST)
        source_record_id = request.args.get('record_id') or request.form.get('source_record_id')
        prefilled_content = None
        source_oss_url = None
        
        if source_record_id:
            with conn.cursor() as cursor:
                cursor.execute("SELECT oss_url, ai_content, ai_status FROM genealogy_records WHERE id = %s", (source_record_id,))
                rec = cursor.fetchone()
                if rec:
                    source_oss_url = rec['oss_url']
                    # Check ai_status (2 = success)
                    if rec['ai_status'] == 2 and rec['ai_content']:
                        prefilled_content = rec['ai_content']

        if request.method == 'POST':
            # 处理生日转换为 Unix 时间戳
            birthday_str = request.form.get('birthday')
            birthday_ts = 0
            if birthday_str:
                try:
                    birthday_ts = int(datetime.strptime(birthday_str, '%Y-%m-%d').timestamp())
                except ValueError:
                    birthday_ts = 0

            # 关系数据
            related_mid = request.form.get('related_mid')
            relation_type = request.form.get('relation_type')
            sub_relation_type = request.form.get('sub_relation_type', 0)
            
            # 年龄校验逻辑
            if related_mid and relation_type in ['1', '2']: # 1:父子 2:母子
                with conn.cursor() as cursor:
                    cursor.execute("SELECT name, birthday FROM family_member_info WHERE id = %s", (related_mid,))
                    parent = cursor.fetchone()
                    if parent and parent['birthday'] > 0 and birthday_ts > 0:
                        if birthday_ts < parent['birthday']:
                            error_msg = f"数据冲突：成员年龄不能比其父亲/母亲（{parent['name']}）大，请检查并修正出生日期。"
                            flash(error_msg)
                            
                            # Re-fetch data for rendering
                            cursor.execute("SELECT id, name FROM family_member_info ORDER BY name")
                            all_members = cursor.fetchall()
                            cursor.execute("SELECT * FROM genealogy_records ORDER BY page_number ASC")
                            images = cursor.fetchall()

                            if request.headers.get('X-Requested-With') == 'XMLHttpRequest' or request.is_json:
                                return jsonify({
                                    "success": False, 
                                    "message": error_msg
                                }), 400
                            
                            return render_template('add_member.html', all_members=all_members, images=images, 
                                                 prefilled_content=prefilled_content, source_oss_url=source_oss_url, source_record_id=source_record_id)

            # 获取表单数据
            data = {
                'name': request.form['name'],
                'simplified_name': request.form.get('simplified_name'),
                'former_name': request.form.get('former_name'),
                'childhood_name': request.form.get('childhood_name'),
                'name_word': request.form.get('name_word'),
                'name_word_generation': request.form.get('name_word_generation'),
                'name_title': request.form.get('name_title'),
                'sex': request.form['sex'],
                'birthday': birthday_ts,
                'is_pass_away': request.form.get('is_pass_away', 0),
                'marital_status': request.form.get('marital_status', 0),
                'birth_place': request.form.get('birth_place'),
                'branch_family_hall': request.form.get('branch_family_hall'),
                'cluster_place': request.form.get('cluster_place'),
                'nation': request.form.get('nation'),
                'residential_address': request.form.get('residential_address'),
                'phone': request.form.get('phone'),
                'mail': request.form.get('mail'),
                'wechat_account': request.form.get('wechat_account'),
                'id_number': request.form.get('id_number'),
                'occupation': request.form.get('occupation'),
                'educational': request.form.get('educational'),
                'blood_type': request.form.get('blood_type'),
                'religion': request.form.get('religion'),
                'hobbies': request.form.get('hobbies'),
                'personal_achievements': request.form.get('personal_achievements'),
                'family_rank': request.form.get('family_rank'),
                'tags': request.form.get('tags'),
                'notes': request.form.get('notes'),
                'source_record_id': request.form.get('source_record_id') or None  # Save source record ID
            }
            
            # ... (rest of logic) ...
            
            with conn.cursor() as cursor:
                fields = ", ".join(data.keys())
                placeholders = ", ".join(["%s"] * len(data))
                sql = f"INSERT INTO family_member_info ({fields}) VALUES ({placeholders})"
                cursor.execute(sql, list(data.values()))
                member_id = cursor.lastrowid
                
                # 录入关系
                if related_mid and relation_type:
                    rel_type = int(relation_type)
                    parent_mid = int(related_mid)
                    child_mid = member_id
                    gen_diff = 1 if rel_type in [1, 2] else 0
                        
                    sql_relation = """
                        INSERT INTO family_relation_info 
                        (parent_mid, child_mid, relation_type, sub_relation_type, source_mid, generation_diff) 
                        VALUES (%s, %s, %s, %s, %s, %s)
                    """
                    cursor.execute(sql_relation, (parent_mid, child_mid, rel_type, sub_relation_type, member_id, gen_diff))
                
                # Update AI Record Status if applicable
                source_record_id = data.get('source_record_id')
                source_index = request.form.get('source_index')
                
                if source_record_id and source_index and source_index.isdigit():
                    try:
                        idx = int(source_index)
                        cursor.execute("SELECT ai_content FROM genealogy_records WHERE id = %s FOR UPDATE", (source_record_id,))
                        rec = cursor.fetchone()
                        if rec and rec['ai_content']:
                            import json
                            content = json.loads(rec['ai_content'])
                            # Ensure content is a list (it might be a dict if single object, though we try to normalize)
                            if isinstance(content, dict):
                                content = [content]
                                
                            if isinstance(content, list):
                                updated = False
                                if 0 <= idx < len(content):
                                    if not content[idx].get('is_imported'): # Avoid redundant updates
                                        content[idx]['is_imported'] = True
                                        content[idx]['imported_member_id'] = member_id
                                        updated = True
                                
                                if updated:
                                    new_content = json.dumps(content, ensure_ascii=False)
                                    cursor.execute("UPDATE genealogy_records SET ai_content = %s WHERE id = %s", (new_content, source_record_id))
                    except Exception as e:
                        print(f"Error updating AI content status: {e}")

                conn.commit()
                
                if request.headers.get('X-Requested-With') == 'XMLHttpRequest' or request.is_json:
                    return jsonify({"success": True, "message": "成员录入成功", "member_id": member_id})

                flash('成员录入成功')
                return redirect(url_for('members'))
        
        with conn.cursor() as cursor:
            cursor.execute("SELECT id, name FROM family_member_info ORDER BY name")
            all_members = cursor.fetchall()
            cursor.execute("SELECT * FROM genealogy_records ORDER BY page_number ASC")
            images = cursor.fetchall()
            
    except Exception as e:
        flash(f'发生错误: {e}')
        all_members = []
        images = []
    finally:
        conn.close()
        
    return render_template('add_member.html', all_members=all_members, images=images, 
                         prefilled_content=prefilled_content, source_oss_url=source_oss_url, source_record_id=source_record_id)

@app.route('/manager/edit_member/<int:member_id>', methods=['GET', 'POST'])
def edit_member(member_id):
    if 'user_id' not in session:
        return redirect(url_for('login'))
        
    conn = get_db_connection()
    try:
        if request.method == 'POST':
            birthday_str = request.form.get('birthday')
            birthday_ts = 0
            if birthday_str:
                try:
                    birthday_ts = int(datetime.strptime(birthday_str, '%Y-%m-%d').timestamp())
                except ValueError:
                    birthday_ts = 0

            # 关系数据
            related_mid = request.form.get('related_mid')
            relation_type = request.form.get('relation_type')
            sub_relation_type = request.form.get('sub_relation_type', 0)

            # 年龄校验逻辑
            if related_mid and relation_type in ['1', '2']:
                with conn.cursor() as cursor:
                    cursor.execute("SELECT name, birthday FROM family_member_info WHERE id = %s", (related_mid,))
                    parent = cursor.fetchone()
                    if parent and parent['birthday'] > 0 and birthday_ts > 0:
                        if birthday_ts < parent['birthday']:
                            flash(f"数据冲突：成员年龄不能比其父亲/母亲（{parent['name']}）大，请检查并修正出生日期。")
                            # 重新加载编辑页所需数据
                            cursor.execute("SELECT * FROM family_member_info WHERE id = %s", (member_id,))
                            member = cursor.fetchone()
                            member['birthday_date'] = birthday_str # 保持用户输入
                            cursor.execute("SELECT id, name FROM family_member_info WHERE id != %s ORDER BY name", (member_id,))
                            all_members = cursor.fetchall()
                            cursor.execute("SELECT * FROM genealogy_records ORDER BY page_number ASC")
                            images = cursor.fetchall()
                            
                            if request.headers.get('X-Requested-With') == 'XMLHttpRequest' or request.is_json:
                                return jsonify({
                                    "success": False, 
                                    "message": f"数据冲突：成员年龄不能比其父亲/母亲（{parent['name']}）大，请检查并修正出生日期。"
                                }), 400
                            
                            return render_template('add_member.html', member=member, images=images, all_members=all_members)

            data = {
                'name': request.form['name'],
                'simplified_name': request.form.get('simplified_name'),
                'former_name': request.form.get('former_name'),
                'childhood_name': request.form.get('childhood_name'),
                'name_word': request.form.get('name_word'),
                'name_word_generation': request.form.get('name_word_generation'),
                'name_title': request.form.get('name_title'),
                'sex': request.form['sex'],
                'birthday': birthday_ts,
                'is_pass_away': request.form.get('is_pass_away', 0),
                'marital_status': request.form.get('marital_status', 0),
                'birth_place': request.form.get('birth_place'),
                'branch_family_hall': request.form.get('branch_family_hall'),
                'cluster_place': request.form.get('cluster_place'),
                'nation': request.form.get('nation'),
                'residential_address': request.form.get('residential_address'),
                'phone': request.form.get('phone'),
                'mail': request.form.get('mail'),
                'wechat_account': request.form.get('wechat_account'),
                'id_number': request.form.get('id_number'),
                'occupation': request.form.get('occupation'),
                'educational': request.form.get('educational'),
                'blood_type': request.form.get('blood_type'),
                'religion': request.form.get('religion'),
                'hobbies': request.form.get('hobbies'),
                'personal_achievements': request.form.get('personal_achievements'),
                'family_rank': request.form.get('family_rank'),
                'tags': request.form.get('tags'),
                'notes': request.form.get('notes'),
                'source_record_id': request.form.get('source_record_id') or None
            }
            
            # 关系数据
            related_mid = request.form.get('related_mid')
            relation_type = request.form.get('relation_type')
            sub_relation_type = request.form.get('sub_relation_type', 0)
            
            with conn.cursor() as cursor:
                update_parts = [f"{k} = %s" for k in data.keys()]
                sql = f"UPDATE family_member_info SET {', '.join(update_parts)} WHERE id = %s"
                cursor.execute(sql, list(data.values()) + [member_id])
                
                # 更新关系
                if related_mid and relation_type:
                    rel_type = int(relation_type)
                    cursor.execute("DELETE FROM family_relation_info WHERE source_mid = %s", (member_id,))
                    
                    parent_mid = int(related_mid)
                    child_mid = member_id
                    gen_diff = 1 if rel_type in [1, 2] else 0
                    
                    sql_relation = """
                        INSERT INTO family_relation_info 
                        (parent_mid, child_mid, relation_type, sub_relation_type, source_mid, generation_diff) 
                        VALUES (%s, %s, %s, %s, %s, %s)
                    """
                    cursor.execute(sql_relation, (parent_mid, child_mid, rel_type, sub_relation_type, member_id, gen_diff))
                
                # Update AI Record Status if applicable
                source_record_id = data.get('source_record_id')
                source_index = request.form.get('source_index')
                
                if source_record_id and source_index and source_index.isdigit():
                    try:
                        idx = int(source_index)
                        cursor.execute("SELECT ai_content FROM genealogy_records WHERE id = %s FOR UPDATE", (source_record_id,))
                        rec = cursor.fetchone()
                        if rec and rec['ai_content']:
                            import json
                            content = json.loads(rec['ai_content'])
                            if isinstance(content, dict):
                                content = [content]
                                
                            if isinstance(content, list):
                                updated = False
                                if 0 <= idx < len(content):
                                    if not content[idx].get('is_imported'): # Avoid redundant updates
                                        content[idx]['is_imported'] = True
                                        content[idx]['imported_member_id'] = member_id
                                        updated = True
                                
                                if updated:
                                    new_content = json.dumps(content, ensure_ascii=False)
                                    cursor.execute("UPDATE genealogy_records SET ai_content = %s WHERE id = %s", (new_content, source_record_id))
                    except Exception as e:
                        print(f"Error updating AI content status: {e}")

                conn.commit()
                if request.headers.get('X-Requested-With') == 'XMLHttpRequest' or request.is_json:
                    return jsonify({"success": True, "message": "成员信息更新成功"})
                
                flash('成员信息更新成功')
                return redirect(url_for('members'))
        
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM family_member_info WHERE id = %s", (member_id,))
            member = cursor.fetchone()
            if not member:
                flash('成员不存在')
                return redirect(url_for('members'))
            
            # 格式化日期供显示
            if member.get('birthday'):
                member['birthday_date'] = format_timestamp(member['birthday'])
            
            # 获取现有关系
            cursor.execute("SELECT * FROM family_relation_info WHERE source_mid = %s LIMIT 1", (member_id,))
            current_relation = cursor.fetchone()
            
            cursor.execute("SELECT id, name FROM family_member_info WHERE id != %s ORDER BY name", (member_id,))
            all_members = cursor.fetchall()
            
            cursor.execute("SELECT * FROM genealogy_records ORDER BY page_number ASC")
            images = cursor.fetchall()
    finally:
        conn.close()
        
    return render_template('add_member.html', member=member, images=images, all_members=all_members, current_relation=current_relation)

@app.route('/manager/member_detail/<int:member_id>')
def member_detail(member_id):
    if 'user_id' not in session:
        return redirect(url_for('login'))
        
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # Join with genealogy_records to get source image info
            sql = """
                SELECT m.*, r.oss_url as source_image_url, r.page_number as source_page
                FROM family_member_info m
                LEFT JOIN genealogy_records r ON m.source_record_id = r.id
                WHERE m.id = %s
            """
            cursor.execute(sql, (member_id,))
            member = cursor.fetchone()
            if not member:
                flash('成员不存在')
                return redirect(url_for('members'))
            
            member['birthday_str'] = format_timestamp(member.get('birthday'))
            
            # 获取关系
            cursor.execute("""
                SELECT m.id, m.name, r.relation_type 
                FROM family_relation_info r 
                JOIN family_member_info m ON r.parent_mid = m.id 
                WHERE r.child_mid = %s
            """, (member_id,))
            parents = cursor.fetchall()
            
            cursor.execute("""
                SELECT m.id, m.name, r.relation_type 
                FROM family_relation_info r 
                JOIN family_member_info m ON r.child_mid = m.id 
                WHERE r.parent_mid = %s
            """, (member_id,))
            children = cursor.fetchall()
    finally:
        conn.close()
        
    return render_template('member_detail.html', member=member, parents=parents, children=children)

@app.route('/manager/delete_member/<int:member_id>', methods=['POST'])
def delete_member(member_id):
    if 'user_id' not in session:
        return jsonify({"success": False, "message": "Unauthorized"}), 401
        
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 1. 删除关系表中关联该成员的所有记录
            cursor.execute("DELETE FROM family_relation_info WHERE parent_mid = %s OR child_mid = %s OR source_mid = %s", 
                         (member_id, member_id, member_id))
            
            # 2. 删除成员本身
            cursor.execute("DELETE FROM family_member_info WHERE id = %s", (member_id,))
            
            conn.commit()
            flash('成员及其关系已成功删除')
            return redirect(url_for('members'))
    except Exception as e:
        conn.rollback()
        flash(f'删除失败: {e}')
        return redirect(url_for('members'))
    finally:
        conn.close()

@app.route('/manager/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        
        try:
            conn = get_db_connection()
            try:
                with conn.cursor() as cursor:
                    cursor.execute("SELECT * FROM users WHERE username=%s AND password=%s", (username, password))
                    user = cursor.fetchone()
                    if user:
                        session['user_id'] = user['id']
                        session['username'] = user['username']
                        return redirect(url_for('index'))
                    else:
                        flash('用户名或密码错误')
            finally:
                conn.close()
        except Exception as e:
            flash(f'数据库连接错误: {str(e)}')
            print(f'Login error: {str(e)}')
            
    return render_template('login.html')

@app.route('/manager/logout')
def logout():
    session.clear()
    return redirect(url_for('login'))

import requests
import json
import re

@app.route('/manager/api/recognize_image', methods=['POST'])
def recognize_image():
    if 'user_id' not in session:
        return jsonify({"success": False, "message": "Unauthorized"}), 401
    
    data = request.json
    image_url = data.get('image_url')
    if not image_url:
        return jsonify({"success": False, "message": "No image URL provided"}), 400

    api_key = "a1800657-9212-4afe-9b7c-b49f015c54d3"
    api_url = "https://ark.cn-beijing.volces.com/api/v3/responses"
    
    prompt = """
    请分析这张家谱图片，提取其中关于人物的信息。
    请务必将繁体字转换为简体字（original_name 字段除外）。
    特别注意：'name' 字段必须是纯简体中文，不能包含繁体字（例如：'學'应转换为'学'，'劉'应转换为'刘'，'萬'应转换为'万'）。
    请提取以下字段（如果存在）：
    - original_name: 原始姓名（严格保持图片上的繁体字，不做任何修改或转换）
    - name: 简体姓名（必须转换为简体中文，去除不需要的敬称）
    - sex: 性别（男/女）
    - birthday: 出生日期（尝试转换为YYYY-MM-DD格式，如果无法确定年份可只填月日）
    - father_name: 父亲姓名
    - spouse_name: 配偶姓名
    - generation: 第几世/代数
    - name_word: 字辈（例如名字为“学勤公”，“学”为字辈；提取名字中的字辈信息）
    - education: 学历/功名
    - title: 官职/称号
    
    请严格以JSON列表格式返回，不要包含Markdown代码块标记（如 ```json ... ```），直接返回JSON数组。
    如果包含多个人物，请都提取出来。
    """

    payload = {
        "model": "doubao-seed-1-8-251228",
        "stream": True,
        "input": [
            {
                "role": "user",
                "content": [
                    {
                        "type": "input_image",
                        "image_url": image_url
                    },
                    {
                        "type": "input_text",
                        "text": prompt
                    }
                ]
            }
        ]
    }
    
    headers = {
        "Authorization": f"Bearer {api_key}",
        "Content-Type": "application/json"
    }
    
    def generate():
        yield "正在连接 AI 服务...\n"
        try:
            # 使用 stream=True, timeout=120
            # 增加 verify=False 以防 SSL 问题（开发环境）
            # 增加 proxies=None 以防本地代理干扰
            with requests.post(
                api_url, 
                json=payload, 
                headers=headers, 
                stream=True, 
                timeout=1200, 
                verify=False,
                proxies={"http": None, "https": None}
            ) as r:
                if r.status_code != 200:
                    yield f"Error: API returned status code {r.status_code}. Response: {r.text}"
                    return

                yield "连接成功，正在等待 AI 响应...\n"
                
                full_reasoning = ""
                
                json_started = False
                
                for line in r.iter_lines():
                    if line:
                        line_str = line.decode('utf-8')
                        if line_str.startswith('data: '):
                            json_str = line_str[6:]
                            if json_str.strip() == '[DONE]':
                                break
                            try:
                                chunk = json.loads(json_str)
                                
                                # 处理 standard OpenAI choices format (content)
                                if 'choices' in chunk and len(chunk['choices']) > 0:
                                    delta = chunk['choices'][0].get('delta', {})
                                    if 'content' in delta:
                                        if not json_started:
                                            yield "|||JSON_START|||"
                                            json_started = True
                                        yield delta['content']
                                    
                                    # 处理 standard OpenAI choices format (reasoning_content) if any
                                    if 'reasoning_content' in delta:
                                        yield f"\n[推理]: {delta['reasoning_content']}"

                                # 处理 Doubao/Volcano specific formats
                                # Type: response.reasoning_summary_text.delta
                                if chunk.get('type') == 'response.reasoning_summary_text.delta':
                                    if 'delta' in chunk:
                                        yield chunk['delta']
                                
                                # Type: response.text.delta
                                if chunk.get('type') == 'response.text.delta':
                                    if 'delta' in chunk:
                                        if not json_started:
                                            yield "|||JSON_START|||"
                                            json_started = True
                                        yield chunk['delta']
                                        
                                # Type: response.output_item.added (May contain initial content or status)
                                # Type: response.reasoning_summary_part.added
                                
                            except Exception as e:
                                print(f"Chunk parse error: {e}")
                        else:
                            # 尝试直接解析非 data: 开头的行
                            try:
                                chunk = json.loads(line_str)
                                if 'choices' in chunk and len(chunk['choices']) > 0:
                                    content = chunk['choices'][0]['message']['content']
                                    yield content
                            except:
                                pass
        except Exception as e:
            yield f"\n[Error: {str(e)}]"

    return Response(stream_with_context(generate()), mimetype='text/plain')

@app.route('/manager/api/start_analysis/<int:record_id>', methods=['POST'])
def start_analysis(record_id):
    if 'user_id' not in session:
        return jsonify({"success": False, "message": "Unauthorized"}), 401
        
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # Check if record exists
            cursor.execute("SELECT oss_url, ai_status FROM genealogy_records WHERE id = %s", (record_id,))
            record = cursor.fetchone()
            
            if not record:
                return jsonify({"success": False, "message": "Record not found"}), 404
            
            # Update status to processing (1)
            cursor.execute("UPDATE genealogy_records SET ai_status = 1 WHERE id = %s", (record_id,))
            conn.commit()
            
            # Start background task
            threading.Thread(target=process_ai_task, args=(record_id, record['oss_url'])).start()
            
            return jsonify({"success": True, "message": "Analysis started"})
    except Exception as e:
        return jsonify({"success": False, "message": str(e)}), 500
    finally:
        conn.close()

@app.route('/manager/upload', methods=['GET', 'POST'])
def upload():
    if 'user_id' not in session:
        return redirect(url_for('login'))
        
    # 获取建议页码 (当前最大页码 + 1)
    conn = get_db_connection()
    suggested_page = 1
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT MAX(page_number) as max_p FROM genealogy_records")
            result = cursor.fetchone()
            if result and result['max_p']:
                suggested_page = result['max_p'] + 1
    finally:
        conn.close()

    if request.method == 'POST':
        if 'file' not in request.files:
            flash('未选择文件')
            return redirect(request.url)
        
        file = request.files['file']
        if file.filename == '':
            flash('未选择文件')
            return redirect(request.url)
            
        # 允许用户在上传时直接指定页码，或者由 OCR 识别
        manual_page = request.form.get('manual_page')
        
        if file:
            filename = secure_filename(file.filename)
            file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
            file.save(file_path)
            
            # 1. 尝试 OCR 提取页码
            page_num = extract_page_number(file_path)
            
            # 如果 OCR 没识别到，且用户也没手动输入，则跳转到“补录页码”页面
            if not page_num and not manual_page:
                # 先把文件传到 OSS，拿到 URL，方便后续补录
                oss_url = upload_to_oss(file_path)
                if oss_url:
                    # 暂时存入 session 或者跳转带参数
                    return render_template('confirm_page.html', 
                                         filename=filename, 
                                         oss_url=oss_url, 
                                         suggested_page=suggested_page)
                else:
                    flash('上传 OSS 失败')
                    return redirect(url_for('upload'))

            # 使用识别到的或手动输入的页码
            final_page = manual_page if manual_page else page_num
            oss_url = upload_to_oss(file_path)
            
            if oss_url:
                conn = get_db_connection()
                try:
                    with conn.cursor() as cursor:
                        sql = "INSERT INTO genealogy_records (file_name, oss_url, page_number, ai_status) VALUES (%s, %s, %s, 1)"
                        cursor.execute(sql, (filename, oss_url, final_page))
                        record_id = cursor.lastrowid
                    conn.commit()
                    
                    # Start AI Task
                    threading.Thread(target=process_ai_task, args=(record_id, oss_url)).start()
                    
                    flash(f'上传成功！页码：{final_page}，已加入解析队列')
                except Exception as e:
                    flash(f'数据库错误：{e}')
                finally:
                    conn.close()
                return redirect(url_for('index'))
            else:
                flash('上传 OSS 失败')
                
    return render_template('upload.html', suggested_page=suggested_page)

@app.route('/manager/save_upload', methods=['POST'])
def save_upload():
    if 'user_id' not in session: return redirect(url_for('login'))
    
    filename = request.form.get('filename')
    oss_url = request.form.get('oss_url')
    page_number = request.form.get('page_number')
    
    if not oss_url or not page_number:
        flash('页码不能为空')
        return redirect(url_for('upload'))
        
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            sql = "INSERT INTO genealogy_records (file_name, oss_url, page_number, ai_status) VALUES (%s, %s, %s, 1)"
            cursor.execute(sql, (filename, oss_url, page_number))
            record_id = cursor.lastrowid
        conn.commit()
        
        # Start AI Task
        threading.Thread(target=process_ai_task, args=(record_id, oss_url)).start()
        
        flash(f'记录已保存，页码：{page_number}，已加入解析队列')
    except Exception as e:
        flash(f'保存失败: {e}')
    finally:
        conn.close()
    return redirect(url_for('index'))

@app.route('/manager/delete_upload/<int:record_id>', methods=['POST'])
def delete_upload(record_id):
    if 'user_id' not in session:
        return jsonify({"success": False, "message": "Unauthorized"}), 401
        
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 删除记录
            cursor.execute("DELETE FROM genealogy_records WHERE id = %s", (record_id,))
            conn.commit()
            flash('文件记录已成功删除')
            return redirect(url_for('index'))
    except Exception as e:
        conn.rollback()
        flash(f'删除失败: {e}')
        return redirect(url_for('index'))
    finally:
        conn.close()

if __name__ == '__main__':
    app.run(debug=True, port=5001)