thomasm291678
VetCopilot-LCPS: LCPS 2.0 本地推理引擎替换 DeepSeek API
793e953 3 months ago
66.2 kB
"""
数据库连接管理模块
使用 SQLAlchemy 2.0 异步引擎，MVP 阶段使用 SQLite。
"""

from sqlalchemy import create_engine, event, text
from sqlalchemy.orm import DeclarativeBase, sessionmaker

from app.config import settings


# SQLAlchemy 引擎
engine = create_engine(
    settings.effective_database_url,
    echo=settings.DEBUG,
    # SQLite 需要启用 connect_args 以支持多线程
    connect_args={"check_same_thread": False} if "sqlite" in settings.effective_database_url else {},
)

# 会话工厂
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


# SQLite 外键支持
@event.listens_for(engine, "connect")
def _set_sqlite_pragma(dbapi_connection, connection_record):
    """启用 SQLite 外键约束"""
    if "sqlite" in settings.effective_database_url:
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA foreign_keys=ON")
        cursor.close()


class Base(DeclarativeBase):
    """SQLAlchemy ORM 基类"""
    pass


def get_db():
    """
    获取数据库会话的依赖注入函数。
    用法：
        @router.get("/items")
        def list_items(db: Session = Depends(get_db)):
            ...
    """
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def _seed_drugs():
    """种子常用兽药数据（仅在 drugs 表为空时执行）"""
    from app.models.drug import Drug

    # 使用原始连接检查，避免 session 问题
    from sqlalchemy import inspect
    inspector = inspect(engine)
    if "drugs" not in inspector.get_table_names():
        # 表还不存在，等 create_all 创建
        return

    db = SessionLocal()
    try:
        existing_count = db.query(Drug).count()
        if existing_count > 0:
            return

        import uuid
        seed_drugs = [
            {
                "id": str(uuid.uuid4()),
                "name": "阿莫西林",
                "generic_name": "Amoxicillin",
                "category": "抗生素",
                "specification": "250mg/片",
                "unit": "mg",
                "route": "口服",
                "default_dosage": "10-20 mg/kg",
                "min_dosage_per_kg": 10.0,
                "max_dosage_per_kg": 20.0,
                "frequency": "每日2次",
                "duration": "5-7天",
                "precautions": "青霉素过敏禁用；肾功能不全需调整剂量",
            },
            {
                "id": str(uuid.uuid4()),
                "name": "头孢氨苄",
                "generic_name": "Cephalexin",
                "category": "抗生素",
                "specification": "300mg/片",
                "unit": "mg",
                "route": "口服",
                "default_dosage": "15-30 mg/kg",
                "min_dosage_per_kg": 15.0,
                "max_dosage_per_kg": 30.0,
                "frequency": "每日2次",
                "duration": "7-14天",
                "precautions": "头孢类过敏禁用；肾功能不全需调整剂量",
            },
            {
                "id": str(uuid.uuid4()),
                "name": "恩诺沙星",
                "generic_name": "Enrofloxacin",
                "category": "抗生素",
                "specification": "50mg/片",
                "unit": "mg",
                "route": "口服/注射",
                "default_dosage": "2.5-5 mg/kg",
                "min_dosage_per_kg": 2.5,
                "max_dosage_per_kg": 5.0,
                "frequency": "每日1-2次",
                "duration": "5-7天",
                "precautions": "幼龄动物慎用（影响软骨发育）；猫慎用高剂量（视网膜毒性）",
            },
            {
                "id": str(uuid.uuid4()),
                "name": "多西环素",
                "generic_name": "Doxycycline",
                "category": "抗生素",
                "specification": "100mg/片",
                "unit": "mg",
                "route": "口服",
                "default_dosage": "5-10 mg/kg",
                "min_dosage_per_kg": 5.0,
                "max_dosage_per_kg": 10.0,
                "frequency": "每日1次",
                "duration": "7-14天",
                "precautions": "食道炎风险，需随水/食物服用；妊娠动物慎用",
            },
            {
                "id": str(uuid.uuid4()),
                "name": "甲硝唑",
                "generic_name": "Metronidazole",
                "category": "抗生素",
                "specification": "200mg/片",
                "unit": "mg",
                "route": "口服",
                "default_dosage": "10-25 mg/kg",
                "min_dosage_per_kg": 10.0,
                "max_dosage_per_kg": 25.0,
                "frequency": "每日2次",
                "duration": "5-7天",
                "precautions": "肝病动物禁用；可能引起神经系统症状",
            },
            {
                "id": str(uuid.uuid4()),
                "name": "吡喹酮",
                "generic_name": "Praziquantel",
                "category": "驱虫",
                "specification": "50mg/片",
                "unit": "mg",
                "route": "口服",
                "default_dosage": "5-10 mg/kg",
                "min_dosage_per_kg": 5.0,
                "max_dosage_per_kg": 10.0,
                "frequency": "单次给药",
                "duration": "单次",
                "precautions": "一般安全，少数可能出现呕吐、嗜睡",
            },
            {
                "id": str(uuid.uuid4()),
                "name": "芬苯达唑",
                "generic_name": "Fenbendazole",
                "category": "驱虫",
                "specification": "100mg/片",
                "unit": "mg",
                "route": "口服",
                "default_dosage": "50 mg/kg",
                "min_dosage_per_kg": 50.0,
                "max_dosage_per_kg": 50.0,
                "frequency": "每日1次",
                "duration": "3天",
                "precautions": "广谱驱虫，对贾第虫也有效",
            },
            {
                "id": str(uuid.uuid4()),
                "name": "伊维菌素",
                "generic_name": "Ivermectin",
                "category": "驱虫",
                "specification": "6mg/片",
                "unit": "mg",
                "route": "口服/注射",
                "default_dosage": "0.2-0.3 mg/kg",
                "min_dosage_per_kg": 0.2,
                "max_dosage_per_kg": 0.3,
                "frequency": "单次给药",
                "duration": "每1-3个月重复",
                "precautions": "柯利犬种（牧羊犬等）禁用（MDR1基因突变）；幼龄动物慎用",
            },
            {
                "id": str(uuid.uuid4()),
                "name": "泼尼松龙",
                "generic_name": "Prednisolone",
                "category": "激素",
                "specification": "5mg/片",
                "unit": "mg",
                "route": "口服",
                "default_dosage": "0.5-2 mg/kg",
                "min_dosage_per_kg": 0.5,
                "max_dosage_per_kg": 2.0,
                "frequency": "每日1-2次",
                "duration": "按需，逐渐减量",
                "precautions": "禁用于全身真菌感染；长期使用需逐渐减量停药；消化道溃疡禁用",
            },
            {
                "id": str(uuid.uuid4()),
                "name": "美洛昔康",
                "generic_name": "Meloxicam",
                "category": "止痛",
                "specification": "7.5mg/片",
                "unit": "mg",
                "route": "口服",
                "default_dosage": "0.1-0.2 mg/kg",
                "min_dosage_per_kg": 0.1,
                "max_dosage_per_kg": 0.2,
                "frequency": "每日1次",
                "duration": "不超过5-7天",
                "precautions": "禁用于脱水、低血压、肾功能不全动物；禁与糖皮质激素联用",
            },
            {
                "id": str(uuid.uuid4()),
                "name": "赛拉嗪",
                "generic_name": "Xylazine",
                "category": "镇静",
                "specification": "20mg/ml 注射液",
                "unit": "mg",
                "route": "注射",
                "default_dosage": "0.5-1 mg/kg (犬) / 1-2 mg/kg (猫)",
                "min_dosage_per_kg": 0.5,
                "max_dosage_per_kg": 2.0,
                "frequency": "单次给药",
                "duration": "单次",
                "precautions": "心肺功能不全动物慎用；可用育亨宾拮抗",
            },
            {
                "id": str(uuid.uuid4()),
                "name": "妙三多",
                "generic_name": "FVRCP",
                "category": "疫苗",
                "specification": "1头份/支",
                "unit": "头份",
                "route": "皮下注射",
                "default_dosage": "1头份",
                "min_dosage_per_kg": None,
                "max_dosage_per_kg": None,
                "frequency": "首次两针间隔3-4周",
                "duration": "每年加强免疫",
                "precautions": "仅用于健康猫；免疫后可能出现一过性嗜睡、食欲减退",
            },
            {
                "id": str(uuid.uuid4()),
                "name": "卫佳伍",
                "generic_name": "DHPPi",
                "category": "疫苗",
                "specification": "1头份/支",
                "unit": "头份",
                "route": "皮下注射",
                "default_dosage": "1头份",
                "min_dosage_per_kg": None,
                "max_dosage_per_kg": None,
                "frequency": "首次两针间隔3-4周",
                "duration": "每年加强免疫",
                "precautions": "仅用于健康犬；免疫后可能出现一过性嗜睡、食欲减退",
            },
            {
                "id": str(uuid.uuid4()),
                "name": "复合维生素B",
                "generic_name": "Vitamin B Complex",
                "category": "营养",
                "specification": "2ml/支 注射液",
                "unit": "ml",
                "route": "注射/口服",
                "default_dosage": "0.5-2 ml/只",
                "min_dosage_per_kg": None,
                "max_dosage_per_kg": None,
                "frequency": "每日1次",
                "duration": "3-5天",
                "precautions": "一般安全，过敏体质慎用",
            },
        ]

        db.execute(Drug.__table__.insert(), seed_drugs)
        db.commit()

        import logging
        logging.getLogger("vetcopilot").info(
            f"种子药品数据已插入: {len(seed_drugs)} 条记录"
        )
    except Exception as e:
        db.rollback()
        import logging
        logging.getLogger("vetcopilot").warning(
            f"种子药品数据插入失败（可能表尚未创建）: {e}"
        )
    finally:
        db.close()


def _seed_lab_panel_references():
    """种子实验室参考范围数据（仅在 lab_panel_references 表为空时执行）"""
    import uuid
    from datetime import datetime
    from app.models.lab_panel import LabPanelReference
    from sqlalchemy import inspect

    inspector = inspect(engine)
    if "lab_panel_references" not in inspector.get_table_names():
        return

    db = SessionLocal()
    try:
        existing_count = db.query(LabPanelReference).count()
        if existing_count > 0:
            return

        refs = []
        now_dt = datetime(2026, 1, 1, 0, 0, 0)

        # ================================================================
        # 犬 CBC（血常规）- 完整版含分类
        # ================================================================
        dog_cbc = [
            ("犬", "haematology", "WBC", "10^9/L", "6.0", "17.0", "6.0-17.0"),
            ("犬", "haematology", "RBC", "10^12/L", "5.5", "8.5", "5.5-8.5"),
            ("犬", "haematology", "HGB", "g/dL", "12.0", "18.0", "12.0-18.0"),
            ("犬", "haematology", "HCT", "%", "37", "55", "37-55"),
            ("犬", "haematology", "MCV", "fL", "60", "77", "60-77"),
            ("犬", "haematology", "MCH", "pg", "19.5", "24.5", "19.5-24.5"),
            ("犬", "haematology", "MCHC", "g/dL", "32.0", "36.0", "32.0-36.0"),
            ("犬", "haematology", "RDW", "%", "12.0", "17.0", "12.0-17.0"),
            ("犬", "haematology", "PLT", "10^9/L", "200", "500", "200-500"),
            ("犬", "haematology", "MPV", "fL", "7.0", "12.0", "7.0-12.0"),
            ("犬", "haematology", "PCT", "%", "0.14", "0.46", "0.14-0.46"),
            ("犬", "haematology", "PDW", "%", "15.0", "17.0", "15.0-17.0"),
            ("犬", "haematology", "RETIC", "K/uL", "10.0", "110.0", "10.0-110.0"),
            ("犬", "haematology", "%RETIC", "%", "0.1", "1.6", "0.1-1.6"),
            # 白细胞分类-百分比
            ("犬", "haematology", "%NEU", "%", "60.0", "77.0", "60-77"),
            ("犬", "haematology", "%LYM", "%", "12.0", "30.0", "12-30"),
            ("犬", "haematology", "%MONO", "%", "3.0", "10.0", "3-10"),
            ("犬", "haematology", "%EOS", "%", "1.0", "10.0", "1-10"),
            ("犬", "haematology", "%BASO", "%", "0.0", "1.0", "0-1"),
            # 白细胞分类-绝对值
            ("犬", "haematology", "NEU", "10^9/L", "3.0", "11.5", "3.0-11.5"),
            ("犬", "haematology", "LYM", "10^9/L", "1.0", "4.8", "1.0-4.8"),
            ("犬", "haematology", "MONO", "10^9/L", "0.15", "1.35", "0.15-1.35"),
            ("犬", "haematology", "EOS", "10^9/L", "0.1", "1.25", "0.1-1.25"),
            ("犬", "haematology", "BASO", "10^9/L", "0.0", "0.1", "0.0-0.1"),
        ]

        # 猫 CBC（血常规）- 完整版含分类
        cat_cbc = [
            ("猫", "haematology", "WBC", "10^9/L", "5.5", "19.5", "5.5-19.5"),
            ("猫", "haematology", "RBC", "10^12/L", "5.0", "10.0", "5.0-10.0"),
            ("猫", "haematology", "HGB", "g/dL", "8.0", "15.0", "8.0-15.0"),
            ("猫", "haematology", "HCT", "%", "24", "45", "24-45"),
            ("猫", "haematology", "MCV", "fL", "39", "55", "39-55"),
            ("猫", "haematology", "MCH", "pg", "13.0", "17.0", "13.0-17.0"),
            ("猫", "haematology", "MCHC", "g/dL", "30.0", "36.0", "30.0-36.0"),
            ("猫", "haematology", "RDW", "%", "12.0", "17.0", "12.0-17.0"),
            ("猫", "haematology", "PLT", "10^9/L", "300", "800", "300-800"),
            ("猫", "haematology", "MPV", "fL", "7.0", "12.0", "7.0-12.0"),
            ("猫", "haematology", "PCT", "%", "0.11", "0.42", "0.11-0.42"),
            ("猫", "haematology", "PDW", "%", "15.0", "17.0", "15.0-17.0"),
            ("猫", "haematology", "RETIC", "K/uL", "10.0", "80.0", "10.0-80.0"),
            ("猫", "haematology", "%RETIC", "%", "0.1", "1.4", "0.1-1.4"),
            ("猫", "haematology", "%NEU", "%", "35.0", "75.0", "35-75"),
            ("猫", "haematology", "%LYM", "%", "20.0", "55.0", "20-55"),
            ("猫", "haematology", "%MONO", "%", "1.0", "4.0", "1-4"),
            ("猫", "haematology", "%EOS", "%", "1.0", "12.0", "1-12"),
            ("猫", "haematology", "%BASO", "%", "0.0", "1.0", "0-1"),
            ("猫", "haematology", "NEU", "10^9/L", "2.5", "12.5", "2.5-12.5"),
            ("猫", "haematology", "LYM", "10^9/L", "1.5", "7.0", "1.5-7.0"),
            ("猫", "haematology", "MONO", "10^9/L", "0.0", "0.85", "0.0-0.85"),
            ("猫", "haematology", "EOS", "10^9/L", "0.0", "1.5", "0.0-1.5"),
            ("猫", "haematology", "BASO", "10^9/L", "0.0", "0.1", "0.0-0.1"),
        ]

        # ================================================================
        # 犬生化 - 完整版（基于真实检测报告格式）
        # ================================================================
        dog_biochem = [
            ("犬", "biochemistry", "TP", "g/L", "52", "82", "52-82"),
            ("犬", "biochemistry", "ALB", "g/L", "23", "40", "23-40"),
            ("犬", "biochemistry", "GLOB", "g/L", "25", "45", "25-45"),
            ("犬", "biochemistry", "A/G", "", "0.7", "1.4", "0.7-1.4"),
            ("犬", "biochemistry", "ALT", "U/L", "10", "125", "10-125"),
            ("犬", "biochemistry", "AST", "U/L", "0", "50", "0-50"),
            ("犬", "biochemistry", "ALP", "U/L", "23", "212", "23-212"),
            ("犬", "biochemistry", "GGT", "U/L", "0", "11", "0-11"),
            ("犬", "biochemistry", "TBIL", "umol/L", "0", "15", "0-15"),
            ("犬", "biochemistry", "BUN", "mmol/L", "2.5", "9.6", "2.5-9.6"),
            ("犬", "biochemistry", "CREA", "umol/L", "44", "159", "44-159"),
            ("犬", "biochemistry", "BUN/CREA", "", "", "", "20-30"),
            ("犬", "biochemistry", "GLU", "mmol/L", "4.11", "7.95", "4.11-7.95"),
            ("犬", "biochemistry", "CHOL", "mmol/L", "2.84", "8.26", "2.84-8.26"),
            ("犬", "biochemistry", "AMYL", "U/L", "500", "1500", "500-1500"),
            ("犬", "biochemistry", "LIPA", "U/L", "200", "1800", "200-1800"),
            ("犬", "biochemistry", "Na", "mmol/L", "144", "160", "144-160"),
            ("犬", "biochemistry", "K", "mmol/L", "3.5", "5.8", "3.5-5.8"),
            ("犬", "biochemistry", "Cl", "mmol/L", "109", "122", "109-122"),
            ("犬", "biochemistry", "Ca", "mmol/L", "1.98", "3.00", "1.98-3.00"),
            ("犬", "biochemistry", "P", "mmol/L", "0.81", "2.20", "0.81-2.20"),
            ("犬", "biochemistry", "SDMA", "ug/dL", "0", "14", "0-14"),
            ("犬", "biochemistry", "CK", "U/L", "10", "200", "10-200"),
            ("犬", "biochemistry", "Osm Calc", "mmol/kg", "280", "310", "280-310"),
        ]

        # 猫生化 - 完整版
        cat_biochem = [
            ("猫", "biochemistry", "TP", "g/L", "57", "89", "57-89"),
            ("猫", "biochemistry", "ALB", "g/L", "22", "40", "22-40"),
            ("猫", "biochemistry", "GLOB", "g/L", "28", "51", "28-51"),
            ("猫", "biochemistry", "A/G", "", "0.45", "1.19", "0.45-1.19"),
            ("猫", "biochemistry", "ALT", "U/L", "10", "100", "10-100"),
            ("猫", "biochemistry", "AST", "U/L", "0", "48", "0-48"),
            ("猫", "biochemistry", "ALP", "U/L", "14", "111", "14-111"),
            ("猫", "biochemistry", "GGT", "U/L", "0", "1", "0-1"),
            ("猫", "biochemistry", "TBIL", "umol/L", "0", "15", "0-15"),
            ("猫", "biochemistry", "BUN", "mmol/L", "5.7", "12.9", "5.7-12.9"),
            ("猫", "biochemistry", "CREA", "umol/L", "71", "212", "71-212"),
            ("猫", "biochemistry", "BUN/CREA", "", "", "", "15-25"),
            ("猫", "biochemistry", "GLU", "mmol/L", "3.9", "8.3", "3.9-8.3"),
            ("猫", "biochemistry", "CHOL", "mmol/L", "2.46", "8.00", "2.46-8.00"),
            ("猫", "biochemistry", "AMYL", "U/L", "500", "1500", "500-1500"),
            ("猫", "biochemistry", "LIPA", "U/L", "100", "1400", "100-1400"),
            ("猫", "biochemistry", "Na", "mmol/L", "150", "165", "150-165"),
            ("猫", "biochemistry", "K", "mmol/L", "3.5", "5.8", "3.5-5.8"),
            ("猫", "biochemistry", "Cl", "mmol/L", "112", "129", "112-129"),
            ("猫", "biochemistry", "Ca", "mmol/L", "1.55", "2.85", "1.55-2.85"),
            ("猫", "biochemistry", "P", "mmol/L", "1.00", "2.42", "1.00-2.42"),
            ("猫", "biochemistry", "SDMA", "ug/dL", "0", "14", "0-14"),
            ("猫", "biochemistry", "CK", "U/L", "10", "200", "10-200"),
            ("猫", "biochemistry", "Osm Calc", "mmol/kg", "290", "320", "290-320"),
        ]

        # ================================================================
        # 电解质（犬猫通用但有差异的值分开）
        # ================================================================
        dog_electrolytes = [
            ("犬", "electrolytes", "Na", "mmol/L", "144", "160", "144-160"),
            ("犬", "electrolytes", "K", "mmol/L", "3.5", "5.8", "3.5-5.8"),
            ("犬", "electrolytes", "Na/K", "", "27", "46", "27-46"),
            ("犬", "electrolytes", "Cl", "mmol/L", "109", "122", "109-122"),
            ("犬", "electrolytes", "Ca", "mmol/L", "1.98", "3.00", "1.98-3.00"),
            ("犬", "electrolytes", "P", "mmol/L", "0.81", "2.20", "0.81-2.20"),
            ("犬", "electrolytes", "Mg", "mmol/L", "0.74", "1.21", "0.74-1.21"),
        ]

        cat_electrolytes = [
            ("猫", "electrolytes", "Na", "mmol/L", "150", "165", "150-165"),
            ("猫", "electrolytes", "K", "mmol/L", "3.5", "5.8", "3.5-5.8"),
            ("猫", "electrolytes", "Na/K", "", "26", "47", "26-47"),
            ("猫", "electrolytes", "Cl", "mmol/L", "112", "129", "112-129"),
            ("猫", "electrolytes", "Ca", "mmol/L", "1.55", "2.85", "1.55-2.85"),
            ("猫", "electrolytes", "P", "mmol/L", "1.00", "2.42", "1.00-2.42"),
            ("猫", "electrolytes", "Mg", "mmol/L", "0.74", "1.21", "0.74-1.21"),
        ]

        # ================================================================
        # 血气
        # ================================================================
        dog_blood_gas = [
            ("犬", "blood_gas", "pH", "", "7.31", "7.42", "7.31-7.42"),
            ("犬", "blood_gas", "pCO2", "mmHg", "29", "42", "29-42"),
            ("犬", "blood_gas", "pO2", "mmHg", "80", "100", "80-100"),
            ("犬", "blood_gas", "HCO3", "mmol/L", "17", "24", "17-24"),
            ("犬", "blood_gas", "BE", "mmol/L", "-4", "+4", "-4~+4"),
            ("犬", "blood_gas", "TCO2", "mmol/L", "18", "26", "18-26"),
            ("犬", "blood_gas", "SO2", "%", "95", "100", "95-100"),
            ("犬", "blood_gas", "Lac", "mmol/L", "0.5", "2.5", "0.5-2.5"),
        ]

        cat_blood_gas = [
            ("猫", "blood_gas", "pH", "", "7.24", "7.40", "7.24-7.40"),
            ("猫", "blood_gas", "pCO2", "mmHg", "29", "42", "29-42"),
            ("猫", "blood_gas", "pO2", "mmHg", "80", "100", "80-100"),
            ("猫", "blood_gas", "HCO3", "mmol/L", "17", "24", "17-24"),
            ("猫", "blood_gas", "BE", "mmol/L", "-4", "+4", "-4~+4"),
            ("猫", "blood_gas", "TCO2", "mmol/L", "18", "24", "18-24"),
            ("猫", "blood_gas", "SO2", "%", "95", "100", "95-100"),
            ("猫", "blood_gas", "Lac", "mmol/L", "0.5", "2.5", "0.5-2.5"),
        ]

        # ================================================================
        # 尿检（犬猫通用，差异较小）
        # ================================================================
        dog_urinalysis = [
            ("犬", "urinalysis", "SG", "", "1.015", "1.045", "1.015-1.045"),
            ("犬", "urinalysis", "USG", "", "1.015", "1.045", "1.015-1.045"),
            ("犬", "urinalysis", "pH", "", "5.5", "7.5", "5.5-7.5"),
            ("犬", "urinalysis", "PRO", "", "", "", "阴性"),
            ("犬", "urinalysis", "GLU", "", "", "", "阴性"),
            ("犬", "urinalysis", "KET", "", "", "", "阴性"),
            ("犬", "urinalysis", "BIL", "", "", "", "阴性至微量"),
            ("犬", "urinalysis", "UBG", "", "", "", "正常"),
            ("犬", "urinalysis", "BLD", "", "", "", "阴性"),
            ("犬", "urinalysis", "NIT", "", "", "", "阴性"),
            ("犬", "urinalysis", "LEU", "", "", "", "阴性"),
        ]

        cat_urinalysis = [
            ("猫", "urinalysis", "SG", "", "1.020", "1.040", "1.020-1.040"),
            ("猫", "urinalysis", "USG", "", "1.020", "1.040", "1.020-1.040"),
            ("猫", "urinalysis", "pH", "", "6.0", "7.0", "6.0-7.0"),
            ("猫", "urinalysis", "PRO", "", "", "", "阴性"),
            ("猫", "urinalysis", "GLU", "", "", "", "阴性"),
            ("猫", "urinalysis", "KET", "", "", "", "阴性"),
            ("猫", "urinalysis", "BIL", "", "", "", "阴性至微量"),
            ("猫", "urinalysis", "UBG", "", "", "", "正常"),
            ("猫", "urinalysis", "BLD", "", "", "", "阴性"),
            ("猫", "urinalysis", "NIT", "", "", "", "阴性"),
            ("猫", "urinalysis", "LEU", "", "", "", "阴性"),
        ]

        all_refs = (
            dog_cbc + cat_cbc
            + dog_biochem + cat_biochem
            + dog_electrolytes + cat_electrolytes
            + dog_blood_gas + cat_blood_gas
            + dog_urinalysis + cat_urinalysis
        )

        for (species, category, parameter, unit, low, high, text) in all_refs:
            refs.append({
                "id": str(uuid.uuid4()),
                "species": species,
                "category": category,
                "parameter": parameter,
                "unit": unit,
                "ref_range_low": low,
                "ref_range_high": high,
                "ref_range_text": text,
                "created_at": now_dt,
                "updated_at": now_dt,
            })

        db.execute(LabPanelReference.__table__.insert(), refs)
        db.commit()

        import logging
        logging.getLogger("vetcopilot").info(
            f"种子实验室参考范围数据已插入: {len(refs)} 条记录"
        )
    except Exception as e:
        db.rollback()
        import logging
        logging.getLogger("vetcopilot").warning(
            f"种子实验室参考范围数据插入失败（可能表尚未创建）: {e}"
        )
    finally:
        db.close()


def _seed_templates():
    """种子预设模板数据（仅在 soap_templates 表无数据时执行）"""
    import json
    import uuid
    from datetime import datetime
    from app.models.soap_template import SoapTemplate
    from sqlalchemy import inspect

    inspector = inspect(engine)
    if "soap_templates" not in inspector.get_table_names():
        return

    db = SessionLocal()
    try:
        existing_count = db.query(SoapTemplate).count()
        if existing_count > 0:
            return

        # 系统用户 ID（用于系统预置模板）
        system_user_id = "00000000-0000-0000-0000-000000000000"
        now_dt = datetime(2026, 1, 1, 0, 0, 0)

        # 确保系统用户存在
        from app.models.user import User
        sys_user = db.query(User).filter(User.id == system_user_id).first()
        if not sys_user:
            db.execute(
                User.__table__.insert(),
                [{
                    "id": system_user_id,
                    "phone": "00000000000",
                    "nickname": "系统",
                    "role": "admin",
                    "password_hash": "system",
                    "is_active": False,
                    "created_at": now_dt,
                    "updated_at": now_dt,
                }]
            )
            db.commit()

        # 新增字段公共默认值
        _extra_template_fields = {
            "subjective": "",
            "objective": "",
            "assessment": "",
            "plan": "",
            "clinical_reasoning": "",
            "client_communication": "",
            "problem_list": "",
            "discharge_instructions": "",
            "referral_letter": "",
            "history_gaps": "",
            "differential_diagnosis": "",
        }

        templates = []

        # ---- SOAP 模板（3个） ----
        templates.append({
            "id": str(uuid.uuid4()),
            "name": "初诊test1",
            "template_type": "soap",
            "category": "日常体检",
            "species": "通用",
            "description": "通用初诊 SOAP 模板",
            "subjective": "主诉：\n既往病史：\n饮食/饮水量：\n排便/排尿：\n精神状态：",
            "objective": "体温：\n心率：\n呼吸：\n体重：\n体格检查：",
            "assessment": "主要问题：\n鉴别诊断：\n严重程度：",
            "plan": "检查建议：\n治疗方案：\n用药：\n复查时间：",
            "clinical_reasoning": "",
            "client_communication": "",
            "is_public": True,
            "usage_count": 0,
            "user_id": system_user_id,
            "created_at": now_dt,
            "updated_at": now_dt,
        })
        templates.append({
            "id": str(uuid.uuid4()),
            "name": "年度疫苗体检",
            "template_type": "soap",
            "category": "疫苗",
            "species": "通用",
            "description": "年度疫苗接种健康检查模板",
            "subjective": "主诉：年度体检/疫苗接种\n上次疫苗时间：\n近期有无异常：\n食欲/精神：正常",
            "objective": "体温：\n心率：\n呼吸：\n体重：\n体格检查：\n淋巴结触诊：\n口腔检查：\n耳道检查：",
            "assessment": "健康状况：良好，适合接种\n既往免疫史：完整",
            "plan": "疫苗接种方案：\n- 核心疫苗：\n- 非核心疫苗：\n下次加强免疫时间：\n健康建议：",
            "clinical_reasoning": "",
            "client_communication": "",
            "is_public": True,
            "usage_count": 0,
            "user_id": system_user_id,
            "created_at": now_dt,
            "updated_at": now_dt,
        })
        templates.append({
            "id": str(uuid.uuid4()),
            "name": "皮肤病问诊",
            "template_type": "soap",
            "category": "皮肤病",
            "species": "通用",
            "description": "皮肤科问诊 SOAP 模板",
            "subjective": "主诉：皮肤问题\n瘙痒程度（1-10）：\n发病时间：\n病灶部位：\n是否季节性：\n饮食情况：\n驱虫情况：\n同住动物是否有类似症状：",
            "objective": "体温：\n心率：\n呼吸：\n体重：\n皮肤检查：\n- 脱毛/红斑/丘疹/脓疱/结痂：\n- 分布：局部/全身\n耳道检查：\n伍德氏灯：",
            "assessment": "主要问题：皮肤病\n鉴别诊断：\n- 过敏性皮炎\n- 寄生虫（跳蚤/螨虫）\n- 真菌感染\n- 细菌感染",
            "plan": "检查建议：皮肤刮片/细胞学/真菌培养\n治疗方案：\n- 驱虫：\n- 抗炎：\n- 抗感染：\n- 药浴：\n复查时间：",
            "clinical_reasoning": "",
            "client_communication": "",
            "is_public": True,
            "usage_count": 0,
            "user_id": system_user_id,
            "created_at": now_dt,
            "updated_at": now_dt,
        })

        # ---- 实验室模板（6个）- 基于真实检测报告格式 ----
        lab_cbc_items = [
            {"parameter": "RBC", "unit": "×10¹²/L", "refRange": "5.83-9.01 (犬) / 5.0-10.0 (猫)"},
            {"parameter": "HCT", "unit": "%", "refRange": "36.6-54.5 (犬) / 24-45 (猫)"},
            {"parameter": "HGB", "unit": "g/dL", "refRange": "12.2-18.4 (犬) / 8.0-15.0 (猫)"},
            {"parameter": "MCV", "unit": "fL", "refRange": "55.8-71.6 (犬) / 39-55 (猫)"},
            {"parameter": "MCH", "unit": "pg", "refRange": "17.8-28.8 (犬) / 13.0-17.0 (猫)"},
            {"parameter": "MCHC", "unit": "g/dL", "refRange": "30.9-38.6 (犬) / 30.0-36.0 (猫)"},
            {"parameter": "RDW", "unit": "%", "refRange": "14.7-17.9 (犬) / 12.0-17.0 (猫)"},
            {"parameter": "%RETIC", "unit": "%", "refRange": "0.1-1.6 (犬) / 0.1-1.4 (猫)"},
            {"parameter": "RETIC", "unit": "K/μL", "refRange": "10.0-110.0 (犬) / 10.0-80.0 (猫)"},
            {"parameter": "WBC", "unit": "×10⁹/L", "refRange": "5.50-16.90 (犬) / 5.5-19.5 (猫)"},
            {"parameter": "%NEU", "unit": "%", "refRange": "60-77 (犬) / 35-75 (猫)"},
            {"parameter": "%LYM", "unit": "%", "refRange": "12-30 (犬) / 20-55 (猫)"},
            {"parameter": "%MONO", "unit": "%", "refRange": "3-10 (犬) / 1-4 (猫)"},
            {"parameter": "%EOS", "unit": "%", "refRange": "1-10 (犬) / 1-12 (猫)"},
            {"parameter": "%BASO", "unit": "%", "refRange": "0-1 (犬) / 0-1 (猫)"},
            {"parameter": "NEU", "unit": "×10⁹/L", "refRange": "2.00-12.00 (犬) / 2.50-12.50 (猫)"},
            {"parameter": "LYM", "unit": "×10⁹/L", "refRange": "0.50-4.90 (犬) / 1.50-7.00 (猫)"},
            {"parameter": "MONO", "unit": "×10⁹/L", "refRange": "0.30-2.00 (犬) / 0.00-0.85 (猫)"},
            {"parameter": "EOS", "unit": "×10⁹/L", "refRange": "0.10-1.49 (犬) / 0.00-1.50 (猫)"},
            {"parameter": "BASO", "unit": "×10⁹/L", "refRange": "0.00-0.10 (犬) / 0.00-0.10 (猫)"},
            {"parameter": "PLT", "unit": "K/μL", "refRange": "175-500 (犬) / 300-800 (猫)"},
            {"parameter": "MPV", "unit": "fL", "refRange": "7.0-12.0"},
            {"parameter": "PDW", "unit": "%", "refRange": "15.0-17.0"},
            {"parameter": "PCT", "unit": "%", "refRange": "0.14-0.46 (犬) / 0.11-0.42 (猫)"},
        ]
        templates.append({
            "id": str(uuid.uuid4()),
            "name": "血常规 (CBC)",
            "template_type": "lab_result",
            "category": "血液学",
            "species": "通用",
            "description": "犬猫血常规检查模板，含白细胞、红细胞、血小板等指标",
            "objective": json.dumps(lab_cbc_items, ensure_ascii=False),
            "is_public": True,
            "usage_count": 0,
            "user_id": system_user_id,
            "created_at": now_dt,
            "updated_at": now_dt,
        })

        lab_biochem_items = [
            {"parameter": "TP", "unit": "g/L", "refRange": "52-82 (犬) / 57-89 (猫)"},
            {"parameter": "ALB", "unit": "g/L", "refRange": "23-40 (犬) / 22-40 (猫)"},
            {"parameter": "GLOB", "unit": "g/L", "refRange": "25-45 (犬) / 28-51 (猫)"},
            {"parameter": "A/G", "unit": "", "refRange": "0.7-1.4 (犬) / 0.45-1.19 (猫)"},
            {"parameter": "ALT", "unit": "U/L", "refRange": "10-125 (犬) / 10-100 (猫)"},
            {"parameter": "AST", "unit": "U/L", "refRange": "0-50 (犬) / 0-48 (猫)"},
            {"parameter": "ALP", "unit": "U/L", "refRange": "23-212 (犬) / 14-111 (猫)"},
            {"parameter": "GGT", "unit": "U/L", "refRange": "0-11 (犬) / 0-1 (猫)"},
            {"parameter": "TBIL", "unit": "μmol/L", "refRange": "0-15 (犬) / 0-15 (猫)"},
            {"parameter": "BUN", "unit": "mmol/L", "refRange": "2.5-9.6 (犬) / 5.7-12.9 (猫)"},
            {"parameter": "CREA", "unit": "μmol/L", "refRange": "44-159 (犬) / 71-212 (猫)"},
            {"parameter": "BUN/CREA", "unit": "", "refRange": "20-30 (犬) / 15-25 (猫)"},
            {"parameter": "GLU", "unit": "mmol/L", "refRange": "4.11-7.95 (犬) / 3.9-8.3 (猫)"},
            {"parameter": "CHOL", "unit": "mmol/L", "refRange": "2.84-8.26 (犬) / 2.46-8.00 (猫)"},
            {"parameter": "AMYL", "unit": "U/L", "refRange": "500-1500"},
            {"parameter": "LIPA", "unit": "U/L", "refRange": "200-1800 (犬) / 100-1400 (猫)"},
            {"parameter": "SDMA", "unit": "μg/dL", "refRange": "0-14"},
            {"parameter": "CK", "unit": "U/L", "refRange": "10-200"},
            {"parameter": "Na", "unit": "mmol/L", "refRange": "144-160 (犬) / 150-165 (猫)"},
            {"parameter": "K", "unit": "mmol/L", "refRange": "3.5-5.8"},
            {"parameter": "Na/K", "unit": "", "refRange": "27-46 (犬) / 26-47 (猫)"},
            {"parameter": "Cl", "unit": "mmol/L", "refRange": "109-122 (犬) / 112-129 (猫)"},
            {"parameter": "Ca", "unit": "mmol/L", "refRange": "1.98-3.00 (犬) / 1.55-2.85 (猫)"},
            {"parameter": "P", "unit": "mmol/L", "refRange": "0.81-2.20 (犬) / 1.00-2.42 (猫)"},
            {"parameter": "Osm Calc", "unit": "mmol/kg", "refRange": "280-310 (犬) / 290-320 (猫)"},
        ]
        templates.append({
            "id": str(uuid.uuid4()),
            "name": "生化全项 (Biochemistry)",
            "template_type": "lab_result",
            "category": "生化",
            "species": "通用",
            "description": "犬猫生化全项检查模板，含肝功、肾功、血糖、电解质等指标",
            "objective": json.dumps(lab_biochem_items, ensure_ascii=False),
            "is_public": True,
            "usage_count": 0,
            "user_id": system_user_id,
            "created_at": now_dt,
            "updated_at": now_dt,
        })

        lab_elec_items = [
            {"parameter": "Na", "unit": "mmol/L", "refRange": "144-160 (犬) / 150-165 (猫)"},
            {"parameter": "K", "unit": "mmol/L", "refRange": "3.5-5.8 (犬) / 3.5-5.8 (猫)"},
            {"parameter": "Cl", "unit": "mmol/L", "refRange": "109-122 (犬) / 112-129 (猫)"},
            {"parameter": "Ca", "unit": "mmol/L", "refRange": "1.98-3.00 (犬) / 1.55-2.85 (猫)"},
            {"parameter": "P", "unit": "mmol/L", "refRange": "0.81-2.19 (犬) / 1.00-2.42 (猫)"},
            {"parameter": "Mg", "unit": "mmol/L", "refRange": "0.74-1.21"},
        ]
        templates.append({
            "id": str(uuid.uuid4()),
            "name": "电解质 (Electrolytes)",
            "template_type": "lab_result",
            "category": "生化",
            "species": "通用",
            "description": "犬猫电解质检查模板，含钠、钾、氯、钙、磷、镁",
            "objective": json.dumps(lab_elec_items, ensure_ascii=False),
            "is_public": True,
            "usage_count": 0,
            "user_id": system_user_id,
            "created_at": now_dt,
            "updated_at": now_dt,
        })

        lab_ua_items = [
            {"parameter": "SG", "unit": "", "refRange": "1.015-1.045 (犬) / 1.020-1.040 (猫)"},
            {"parameter": "pH", "unit": "", "refRange": "5.5-7.5 (犬) / 6.0-7.0 (猫)"},
            {"parameter": "PRO", "unit": "", "refRange": "阴性"},
            {"parameter": "GLU", "unit": "", "refRange": "阴性"},
            {"parameter": "KET", "unit": "", "refRange": "阴性"},
            {"parameter": "BIL", "unit": "", "refRange": "阴性至微量"},
            {"parameter": "UBG", "unit": "", "refRange": "正常"},
            {"parameter": "BLD", "unit": "", "refRange": "阴性"},
            {"parameter": "NIT", "unit": "", "refRange": "阴性"},
            {"parameter": "LEU", "unit": "", "refRange": "阴性"},
        ]
        templates.append({
            "id": str(uuid.uuid4()),
            "name": "尿检 (Urinalysis)",
            "template_type": "lab_result",
            "category": "尿液分析",
            "species": "通用",
            "description": "犬猫尿液分析模板，含比重、pH、蛋白、糖等指标",
            "objective": json.dumps(lab_ua_items, ensure_ascii=False),
            "is_public": True,
            "usage_count": 0,
            "user_id": system_user_id,
            "created_at": now_dt,
            "updated_at": now_dt,
        })

        # ---- 血常规+生化综合模板 ----
        lab_combined_items = [
            # 生化部分
            {"section": "生化检测", "parameter": "GLU", "unit": "mmol/L", "refRange": "4.11-7.95 (犬) / 3.9-8.3 (猫)"},
            {"section": "生化检测", "parameter": "SDMA", "unit": "μg/dL", "refRange": "0-14"},
            {"section": "生化检测", "parameter": "CREA", "unit": "μmol/L", "refRange": "44-159 (犬) / 71-212 (猫)"},
            {"section": "生化检测", "parameter": "BUN", "unit": "mmol/L", "refRange": "2.5-9.6 (犬) / 5.7-12.9 (猫)"},
            {"section": "生化检测", "parameter": "BUN/CREA", "unit": "", "refRange": "—"},
            {"section": "生化检测", "parameter": "P", "unit": "mmol/L", "refRange": "0.81-2.20 (犬) / 1.00-2.42 (猫)"},
            {"section": "生化检测", "parameter": "Ca", "unit": "mmol/L", "refRange": "1.98-3.00 (犬) / 1.55-2.85 (猫)"},
            {"section": "生化检测", "parameter": "TP", "unit": "g/L", "refRange": "52-82 (犬) / 57-89 (猫)"},
            {"section": "生化检测", "parameter": "ALB", "unit": "g/L", "refRange": "23-40 (犬) / 22-40 (猫)"},
            {"section": "生化检测", "parameter": "GLOB", "unit": "g/L", "refRange": "25-45 (犬) / 28-51 (猫)"},
            {"section": "生化检测", "parameter": "ALB/GLOB", "unit": "", "refRange": "—"},
            {"section": "生化检测", "parameter": "ALT", "unit": "U/L", "refRange": "10-125 (犬) / 10-100 (猫)"},
            {"section": "生化检测", "parameter": "ALP", "unit": "U/L", "refRange": "23-212 (犬) / 14-111 (猫)"},
            {"section": "生化检测", "parameter": "GGT", "unit": "U/L", "refRange": "0-11 (犬) / 0-1 (猫)"},
            {"section": "生化检测", "parameter": "TBIL", "unit": "μmol/L", "refRange": "0-15"},
            {"section": "生化检测", "parameter": "CHOL", "unit": "mmol/L", "refRange": "2.84-8.26 (犬) / 2.46-8.00 (猫)"},
            {"section": "生化检测", "parameter": "AMYL", "unit": "U/L", "refRange": "500-1500"},
            {"section": "生化检测", "parameter": "LIPA", "unit": "U/L", "refRange": "200-1800 (犬) / 100-1400 (猫)"},
            {"section": "电解质", "parameter": "Na", "unit": "mmol/L", "refRange": "144-160 (犬) / 150-165 (猫)"},
            {"section": "电解质", "parameter": "K", "unit": "mmol/L", "refRange": "3.5-5.8"},
            {"section": "电解质", "parameter": "Na/K", "unit": "", "refRange": "—"},
            {"section": "电解质", "parameter": "Cl", "unit": "mmol/L", "refRange": "109-122 (犬) / 112-129 (猫)"},
            {"section": "电解质", "parameter": "Osm Calc", "unit": "mmol/kg", "refRange": "280-310 (犬) / 290-320 (猫)"},
            {"section": "特殊检测", "parameter": "TT4", "unit": "nmol/L", "refRange": "13-51 (犬) / 10-60 (猫)"},
            # 血常规部分
            {"section": "血常规", "parameter": "RBC", "unit": "×10¹²/L", "refRange": "5.83-9.01 (犬) / 5.0-10.0 (猫)"},
            {"section": "血常规", "parameter": "HCT", "unit": "%", "refRange": "36.6-54.5 (犬) / 24-45 (猫)"},
            {"section": "血常规", "parameter": "HGB", "unit": "g/dL", "refRange": "12.2-18.4 (犬) / 8.0-15.0 (猫)"},
            {"section": "血常规", "parameter": "MCV", "unit": "fL", "refRange": "55.8-71.6 (犬) / 39-55 (猫)"},
            {"section": "血常规", "parameter": "MCH", "unit": "pg", "refRange": "17.8-28.8 (犬) / 13.0-17.0 (猫)"},
            {"section": "血常规", "parameter": "MCHC", "unit": "g/dL", "refRange": "30.9-38.6 (犬) / 30.0-36.0 (猫)"},
            {"section": "血常规", "parameter": "RDW", "unit": "%", "refRange": "14.7-17.9 (犬) / 12.0-17.0 (猫)"},
            {"section": "血常规", "parameter": "%RETIC", "unit": "%", "refRange": "—"},
            {"section": "血常规", "parameter": "RETIC", "unit": "K/μL", "refRange": "10.0-110.0 (犬) / 10.0-80.0 (猫)"},
            {"section": "血常规", "parameter": "WBC", "unit": "×10⁹/L", "refRange": "5.50-16.90 (犬) / 5.5-19.5 (猫)"},
            {"section": "白细胞分类", "parameter": "%NEU", "unit": "%", "refRange": "—"},
            {"section": "白细胞分类", "parameter": "%LYM", "unit": "%", "refRange": "—"},
            {"section": "白细胞分类", "parameter": "%MONO", "unit": "%", "refRange": "—"},
            {"section": "白细胞分类", "parameter": "%EOS", "unit": "%", "refRange": "—"},
            {"section": "白细胞分类", "parameter": "%BASO", "unit": "%", "refRange": "—"},
            {"section": "白细胞分类", "parameter": "NEU", "unit": "×10⁹/L", "refRange": "2.00-12.00 (犬) / 2.50-12.50 (猫)"},
            {"section": "白细胞分类", "parameter": "LYM", "unit": "×10⁹/L", "refRange": "0.50-4.90 (犬) / 1.50-7.00 (猫)"},
            {"section": "白细胞分类", "parameter": "MONO", "unit": "×10⁹/L", "refRange": "0.30-2.00 (犬) / 0.00-0.85 (猫)"},
            {"section": "白细胞分类", "parameter": "EOS", "unit": "×10⁹/L", "refRange": "0.10-1.49 (犬) / 0.00-1.50 (猫)"},
            {"section": "白细胞分类", "parameter": "BASO", "unit": "×10⁹/L", "refRange": "0.00-0.10"},
            {"section": "血小板", "parameter": "PLT", "unit": "K/μL", "refRange": "175-500 (犬) / 300-800 (猫)"},
            {"section": "血小板", "parameter": "MPV", "unit": "fL", "refRange": "7.0-12.0"},
            {"section": "血小板", "parameter": "PDW", "unit": "%", "refRange": "15.0-17.0"},
            {"section": "血小板", "parameter": "PCT", "unit": "%", "refRange": "0.14-0.46 (犬) / 0.11-0.42 (猫)"},
        ]
        templates.append({
            "id": str(uuid.uuid4()),
            "name": "血常规+生化综合 (CBC+Biochem)",
            "template_type": "lab_result",
            "category": "综合检查",
            "species": "通用",
            "description": "血常规+生化+电解质+特殊检测综合模板，参考LaserCyte全自动血球仪+生化分析仪报告格式",
            "objective": json.dumps(lab_combined_items, ensure_ascii=False),
            "is_public": True,
            "usage_count": 0,
            "user_id": system_user_id,
            "created_at": now_dt,
            "updated_at": now_dt,
        })

        # ---- 出院说明模板（2个） ----
        templates.append({
            "id": str(uuid.uuid4()),
            "name": "常规术后出院说明",
            "template_type": "discharge_instructions",
            "category": "术后",
            "species": "通用",
            "description": "常规手术后出院护理指导",
            "discharge_instructions": (
                "## 出院说明\n\n"
                "### 伤口护理\n- 保持伤口清洁干燥，术后7-10天拆线\n"
                "- 避免舔舐伤口，建议佩戴伊丽莎白圈\n"
                "- 观察伤口有无红肿、渗液、裂开等异常\n\n"
                "### 用药指导\n- 请严格按照处方用药，不可自行增减剂量\n"
                "- 抗生素需服完整个疗程\n"
                "- 止痛药按需使用，观察有无呕吐腹泻\n\n"
                "### 饮食管理\n- 术后24小时内少量多次喂食\n"
                "- 建议易消化食物\n"
                "- 保证充足饮水\n\n"
                "### 活动限制\n- 术后7-10天限制剧烈运动\n"
                "- 避免跳跃、爬楼梯等\n"
                "- 使用牵引绳外出\n\n"
                "### 复诊时间\n- 拆线：术后____天\n"
                "- 复查：____年____月____日\n\n"
                "### 紧急情况\n如出现以下情况请立即就诊：\n"
                "- 伤口大量出血或裂开\n"
                "- 持续呕吐、腹泻\n"
                "- 精神萎靡、食欲废绝\n"
                "- 体温异常（>39.5℃或<37.5℃）"
            ),
            "is_public": True,
            "usage_count": 0,
            "user_id": system_user_id,
            "created_at": now_dt,
            "updated_at": now_dt,
        })
        templates.append({
            "id": str(uuid.uuid4()),
            "name": "内科住院出院说明",
            "template_type": "discharge_instructions",
            "category": "内科",
            "species": "通用",
            "description": "内科疾病住院后出院指导",
            "discharge_instructions": (
                "## 出院说明\n\n"
                "### 饮食管理\n- 建议处方粮：____________________\n"
                "- 喂食频率：每日____次，每次____g\n"
                "- 饮水：保证充足新鲜饮水\n"
                "- 禁忌食物：____________________\n\n"
                "### 用药指导\n- 请严格按照处方用药\n"
                "- 药物列表：\n"
                "  1. ____________________\n"
                "  2. ____________________\n"
                "  3. ____________________\n\n"
                "### 居家观察\n- 每日监测精神状态、食欲\n"
                "- 记录体重变化（每周称重）\n"
                "- 注意排便排尿情况\n"
                "- 观察有无呕吐、腹泻、咳嗽等\n\n"
                "### 复诊安排\n- 下次复诊：____年____月____日\n"
                "- 复查项目：____________________\n"
                "- 需带物品：病历、化验单\n\n"
                "### 注意事项\n- 避免应激，保持环境安静舒适\n"
                "- 如出现症状反复请及时就诊\n"
                "- 遵医嘱调整用药，不可自行停药"
            ),
            "is_public": True,
            "usage_count": 0,
            "user_id": system_user_id,
            "created_at": now_dt,
            "updated_at": now_dt,
        })

        # ---- 转诊信模板（1个） ----
        templates.append({
            "id": str(uuid.uuid4()),
            "name": "标准转诊信",
            "template_type": "referral_letter",
            "category": "转诊",
            "species": "通用",
            "description": "标准兽医转诊信模板",
            "referral_letter": (
                "## 转诊信\n\n"
                "致：____________________ 医生\n"
                "转诊医院/科室：____________________\n\n"
                "### 转诊理由\n"
                "尊敬的____________________医生：\n\n"
                "兹转诊患宠____________________至贵院进行进一步诊疗。\n\n"
                "### 患宠信息\n"
                "- 动物种类：____________________\n"
                "- 品种：____________________\n"
                "- 性别：____________________\n"
                "- 年龄：____________________\n"
                "- 体重：____________________\n"
                "- 宠主姓名：____________________\n\n"
                "### 主诉\n____________________\n\n"
                "### 病史摘要\n____________________\n\n"
                "### 体格检查\n____________________\n\n"
                "### 已完成的检查\n____________________\n\n"
                "### 诊断（初步）\n____________________\n\n"
                "### 已进行的治疗\n____________________\n\n"
                "### 转诊目的\n____________________\n\n"
                "### 随附材料\n- 病历摘要\n- 实验室检查报告\n- 影像学资料\n- 其他：____________________\n\n"
                "转诊医师：____________________\n"
                "联系电话：____________________\n"
                "日期：____________________"
            ),
            "is_public": True,
            "usage_count": 0,
            "user_id": system_user_id,
            "created_at": now_dt,
            "updated_at": now_dt,
        })

        # ---- 全科转专科转诊信模板 ----
        templates.append({
            "id": str(uuid.uuid4()),
            "name": "全科医院转专科医院转诊信",
            "template_type": "referral_letter",
            "category": "转诊",
            "species": "通用",
            "description": "全科动物医院转诊至专科医院的标准化转诊信模板",
            "referral_letter": (
                "## 转诊信（全科→专科）\n\n"
                "致：____________________ 专科医院/医生\n"
                "转诊科室：____________________\n"
                "转诊日期：____________________\n\n"
                "尊敬的____________________医生：\n\n"
                "我院现转诊一例患宠至贵院进行进一步专科诊疗，相关病情摘要如下：\n\n"
                "### 患宠基本信息\n"
                "- 宠物姓名：____________________\n"
                "- 动物种类：□犬 □猫 □其他________\n"
                "- 品种：____________________\n"
                "- 性别：□公 □母 □已绝育\n"
                "- 年龄：____________________\n"
                "- 体重：________ kg\n"
                "- 宠主姓名：____________________\n"
                "- 宠主电话：____________________\n\n"
                "### 主诉与现病史\n"
                "主诉：____________________\n\n"
                "现病史（发病时间、症状演变、就诊前处理）：\n"
                "____________________\n\n"
                "### 既往病史\n"
                "- 既往疾病史：____________________\n"
                "- 手术史：____________________\n"
                "- 过敏史：____________________\n"
                "- 用药史：____________________\n\n"
                "### 体格检查\n"
                "一般状态：____________________\n"
                "体温：________ ℃  心率：________ 次/分  呼吸：________ 次/分\n"
                "BCS：________ / 9\n"
                "系统检查异常发现：\n"
                "____________________\n\n"
                "### 实验室及辅助检查\n"
                "1. 血常规：____________________\n"
                "2. 生化：____________________\n"
                "3. 影像学（X光/B超/CT/MR）：____________________\n"
                "4. 其他检查：____________________\n\n"
                "### 初步诊断\n"
                "1. ____________________\n"
                "2. ____________________\n"
                "3. ____________________\n\n"
                "鉴别诊断考虑：____________________\n\n"
                "### 已进行的治疗\n"
                "1. ____________________\n"
                "2. ____________________\n"
                "3. ____________________\n\n"
                "治疗反应：____________________\n\n"
                "### 当前用药\n"
                "| 药物名称 | 剂量 | 用法 | 频次 |\n"
                "|----------|------|------|------|\n"
                "| ____________________ | ____ | ____ | ____ |\n"
                "| ____________________ | ____ | ____ | ____ |\n\n"
                "### 转诊目的与需求\n"
                "转诊原因：□进一步诊断 □专科手术治疗 □疑难病例会诊 □其他________\n\n"
                "希望专科医院协助：\n"
                "____________________\n\n"
                "### 随附材料清单\n"
                "- □ 病历摘要\n"
                "- □ 实验室检查报告原件/复印件\n"
                "- □ 影像学资料（胶片/CD）\n"
                "- □ 病理报告\n"
                "- □ 用药记录\n"
                "- □ 其他：____________________\n\n"
                "### 转诊方信息\n"
                "转诊医院：____________________\n"
                "转诊医师：____________________\n"
                "执业证号：____________________\n"
                "联系电话：____________________\n"
                "电子邮箱：____________________\n\n"
                "此致\n\n"
                "敬礼！\n\n"
                "（转诊医院盖章）\n"
                "日期：____________________"
            ),
            "is_public": True,
            "usage_count": 0,
            "user_id": system_user_id,
            "created_at": now_dt,
            "updated_at": now_dt,
        })

        # 补全所有模板缺失的新增字段
        for t in templates:
            for k, v in _extra_template_fields.items():
                if k not in t:
                    t[k] = v

        # 批量插入
        db.execute(SoapTemplate.__table__.insert(), templates)
        db.commit()

        import logging
        logging.getLogger("vetcopilot").info(
            f"种子模板数据已插入: {len(templates)} 条记录"
        )
    except Exception as e:
        db.rollback()
        import logging
        logging.getLogger("vetcopilot").warning(
            f"种子模板数据插入失败（可能表尚未创建）: {e}"
        )
    finally:
        db.close()


def _seed_notification_templates():
    """种子预设通知模板数据（仅在 notification_templates 表为空时执行）"""
    import uuid
    from datetime import datetime
    from app.models.notification_template import NotificationTemplate
    from sqlalchemy import inspect

    inspector = inspect(engine)
    if "notification_templates" not in inspector.get_table_names():
        return

    db = SessionLocal()
    try:
        existing_count = db.query(NotificationTemplate).count()
        if existing_count > 0:
            return

        now_dt = datetime(2026, 1, 1, 0, 0, 0)
        templates = [
            {
                "id": str(uuid.uuid4()),
                "name": "appointment_reminder",
                "display_name": "预约提醒",
                "channel": "both",
                "subject": "预约提醒 - {hospital_name}",
                "sms_content": "{owner_name}您好，{pet_name}的预约时间为{date} {time}，请准时到院。{hospital_name}",
                "email_content": "<h3>预约提醒</h3><p>{owner_name}您好：</p><p>您为<b>{pet_name}</b>预约了 <b>{date} {time}</b> 的<em>{appointment_type}</em>。</p><p>请准时到院，如有变动请提前联系。</p><hr/><p>此致<br/>{hospital_name}</p>",
                "is_active": True,
                "variables": "owner_name,pet_name,date,time,appointment_type,hospital_name",
                "created_at": now_dt,
                "updated_at": now_dt,
            },
            {
                "id": str(uuid.uuid4()),
                "name": "appointment_confirmation",
                "display_name": "预约确认",
                "channel": "both",
                "subject": "预约确认 - {hospital_name}",
                "sms_content": "已为您预约{date} {time}就诊，宠物：{pet_name}。{hospital_name}",
                "email_content": "<h3>预约确认</h3><p>{owner_name}您好：</p><p>已确认以下预约：</p><ul><li>宠物：<b>{pet_name}</b></li><li>日期：{date}</li><li>时间：{time}</li><li>类型：{appointment_type}</li></ul><p>请准时带宠物到院。</p><hr/><p>{hospital_name}</p>",
                "is_active": True,
                "variables": "owner_name,pet_name,date,time,appointment_type,hospital_name",
                "created_at": now_dt,
                "updated_at": now_dt,
            },
            {
                "id": str(uuid.uuid4()),
                "name": "vaccine_reminder",
                "display_name": "疫苗到期提醒",
                "channel": "both",
                "subject": "疫苗到期提醒 - {hospital_name}",
                "sms_content": "{pet_name}的疫苗将于{date}到期，请及时来院接种。{hospital_name}",
                "email_content": "<h3>疫苗到期提醒</h3><p>{owner_name}您好：</p><p>您的宠物<b>{pet_name}</b>的 <b>{vaccine_name}</b> 将于 <b>{date}</b> 到期。</p><p>请及时带宠到院接种，保护宠物健康。</p><hr/><p>{hospital_name}</p>",
                "is_active": True,
                "variables": "owner_name,pet_name,vaccine_name,date,hospital_name",
                "created_at": now_dt,
                "updated_at": now_dt,
            },
            {
                "id": str(uuid.uuid4()),
                "name": "followup_reminder",
                "display_name": "回访提醒",
                "channel": "both",
                "subject": "复诊回访提醒 - {hospital_name}",
                "sms_content": "{owner_name}您好，{pet_name}建议于{date}到院复诊。{hospital_name}",
                "email_content": "<h3>复诊回访提醒</h3><p>{owner_name}您好：</p><p>您的宠物<b>{pet_name}</b>建议于 <b>{date}</b> 到院复诊，请提前预约。</p><p>如有任何问题请随时联系我们。</p><hr/><p>{hospital_name}</p>",
                "is_active": True,
                "variables": "owner_name,pet_name,date,hospital_name",
                "created_at": now_dt,
                "updated_at": now_dt,
            },
            {
                "id": str(uuid.uuid4()),
                "name": "bill_ready",
                "display_name": "账单通知",
                "channel": "both",
                "subject": "账单通知 - {hospital_name}",
                "sms_content": "{owner_name}您好，您在{hospital_name}有一笔账单：{amount}元（{description}），请及时结算。",
                "email_content": "<h3>账单通知</h3><p>{owner_name}您好：</p><p>您在<b>{hospital_name}</b>有一笔待结算账单：</p><ul><li>金额：<b>{amount}元</b></li><li>项目：{description}</li></ul><p>请及时结算，谢谢！</p><hr/><p>{hospital_name}</p>",
                "is_active": True,
                "variables": "owner_name,amount,description,hospital_name",
                "created_at": now_dt,
                "updated_at": now_dt,
            },
        ]

        db.execute(NotificationTemplate.__table__.insert(), templates)
        db.commit()

        import logging
        logging.getLogger("vetcopilot").info(
            f"种子通知模板数据已插入: {len(templates)} 条记录"
        )
    except Exception as e:
        db.rollback()
        import logging
        logging.getLogger("vetcopilot").warning(
            f"种子通知模板数据插入失败（可能表尚未创建）: {e}"
        )
    finally:
        db.close()


def init_db():
    """
    初始化数据库：创建所有表。
    在应用启动时调用，确保表结构存在。
    """
    from app.models import consultation, transcript, soap_record, soap_template, user, user_settings, patient, drug, prescription, followup, workflow_log, push_subscription, knowledge, inventory, weight_history, comment  # noqa: F401 确保模型被注册
    from app.models import client_instruction, instruction_template, referral_letter  # noqa: F401 新模型
    from app.models import lab_result, lab_panel  # noqa: F401 实验室模型
    from app.models import audit_log, hospital  # noqa: F401 审计日志 + 医院模型
    from app.models import owner  # noqa: F401 宠主模型
    from app.models import appointment, doctor_schedule  # noqa: F401 预约排班模型
    from app.models import notification  # noqa: F401 通知模型
    from app.models import notification_template  # noqa: F401 通知模板模型
    from app.models import in_app_notification  # noqa: F401 站内通知模型
    from app.models import notification_setting  # noqa: F401 通知设置模型
    from app.models import bill  # noqa: F401 账单收费模型
    from app.models import backup  # noqa: F401 备份日志模型

    Base.metadata.create_all(bind=engine)

    # ---- 种子药品数据 ----
    _seed_drugs()

    # ---- 种子模板数据 ----
    _seed_templates()

    # ---- 种子实验室参考范围数据 ----
    _seed_lab_panel_references()

    # ---- 种子通知模板数据 ----
    _seed_notification_templates()

    # ---- FTS5 全文搜索初始化 ----
    if "sqlite" in settings.effective_database_url:
        try:
            with engine.connect() as conn:
                # 1. 创建 FTS5 虚拟表（external content 模式，数据存储在 consultations 中）
                conn.execute(text(
                    "CREATE VIRTUAL TABLE IF NOT EXISTS consultations_fts "
                    "USING fts5("
                    "    animal_name, species, breed, owner_name, doctor_name, "
                    "    content='consultations', content_rowid='rowid'"
                    ")"
                ))

                # 2. 创建触发器：INSERT 时同步到 FTS 索引
                conn.execute(text(
                    "CREATE TRIGGER IF NOT EXISTS consultations_fts_ai "
                    "AFTER INSERT ON consultations BEGIN "
                    "    INSERT INTO consultations_fts(rowid, animal_name, species, breed, owner_name, doctor_name) "
                    "    VALUES (new.rowid, new.animal_name, new.species, new.breed, new.owner_name, new.doctor_name); "
                    "END"
                ))

                # 3. 创建触发器：DELETE 时从 FTS 索引移除
                conn.execute(text(
                    "CREATE TRIGGER IF NOT EXISTS consultations_fts_ad "
                    "AFTER DELETE ON consultations BEGIN "
                    "    INSERT INTO consultations_fts(consultations_fts, rowid, animal_name, species, breed, owner_name, doctor_name) "
                    "    VALUES ('delete', old.rowid, old.animal_name, old.species, old.breed, old.owner_name, old.doctor_name); "
                    "END"
                ))

                # 4. 创建触发器：UPDATE 时更新 FTS 索引（先删后插）
                conn.execute(text(
                    "CREATE TRIGGER IF NOT EXISTS consultations_fts_au "
                    "AFTER UPDATE ON consultations BEGIN "
                    "    INSERT INTO consultations_fts(consultations_fts, rowid, animal_name, species, breed, owner_name, doctor_name) "
                    "    VALUES ('delete', old.rowid, old.animal_name, old.species, old.breed, old.owner_name, old.doctor_name); "
                    "    INSERT INTO consultations_fts(rowid, animal_name, species, breed, owner_name, doctor_name) "
                    "    VALUES (new.rowid, new.animal_name, new.species, new.breed, new.owner_name, new.doctor_name); "
                    "END"
                ))

                # 5. 填充已有数据到 FTS 索引（幂等，已存在则跳过）
                conn.execute(text(
                    "INSERT INTO consultations_fts(rowid, animal_name, species, breed, owner_name, doctor_name) "
                    "SELECT rowid, animal_name, species, breed, owner_name, doctor_name "
                    "FROM consultations "
                    "WHERE rowid NOT IN (SELECT rowid FROM consultations_fts)"
                ))

                conn.commit()
        except Exception as e:
            # FTS5 可能在某些 SQLite 编译版本中不可用，降级处理
            import logging
            logging.getLogger("vetcopilot").warning(
                f"FTS5 全文搜索初始化失败，将回退到 LIKE 搜索: {e}"
            )