from fastapi import APIRouter, Depends, Request
from ...core.database import get_db, get_system_db, _has_split_databases
from ...core.permission import require_login
from ...core.config import settings, get_upload_dir
from ...services.document_generator import (
    generate_document,
    match_templates,
    extract_template_placeholders,
)
import os
import json
import logging
import shutil
import time
from datetime import datetime
from ...utils.request_utils import safe_json

logger = logging.getLogger(__name__)

# ===== 29个实际占位符的字段定义（来自字段对照映射表V3）=====
# 注意：key 不含花括号，与 docxtpl/jinja2 模板变量名一致
PLACEHOLDER_FIELD_DEF = {
    # A组：案号信息（2个）
    "案号_地区简称": {"label": "案号-地区简称", "db_field": "case_number_region", "group": "案件基础信息"},
    "案号_案件号": {"label": "案号-案件号", "db_field": "case_number_seq", "group": "案件基础信息"},
    # B组：执法单位信息（4个）
    "执法单位名称": {"label": "执法单位名称", "db_field": "org_name", "group": "执法单位信息"},
    "单位联系电话": {"label": "单位联系电话", "db_field": "org_phone", "group": "执法单位信息"},
    "单位联系地址": {"label": "单位联系地址", "db_field": "org_address", "group": "执法单位信息"},
    "执法单位地址": {"label": "执法单位地址", "db_field": "org_address", "group": "执法单位信息"},
    # C组：当事人信息（11个）
    "当事人名称": {"label": "当事人名称", "db_field": "party_name", "group": "当事人信息"},
    "当事人姓名": {"label": "当事人姓名", "db_field": "party_name", "group": "当事人信息"},
    "字号名称": {"label": "字号名称", "db_field": "party_name", "group": "当事人信息"},
    "当事人住所": {"label": "当事人住所", "db_field": "address", "group": "当事人信息"},
    "当事人住所地址": {"label": "当事人住所地址", "db_field": "address", "group": "当事人信息"},
    "当事人联系电话": {"label": "当事人联系电话", "db_field": "phone", "group": "当事人信息"},
    "统一社会信用代码": {"label": "统一社会信用代码", "db_field": "credit_code", "group": "当事人信息"},
    "个体统一社会信用代码": {"label": "个体统一社会信用代码", "db_field": "credit_code", "group": "当事人信息"},
    "法定代表人": {"label": "法定代表人", "db_field": "legal_representative", "group": "当事人信息"},
    "身份证件号码": {"label": "身份证件号码", "db_field": "party_id_number", "group": "当事人信息"},
    "法人身份证号码": {"label": "法人身份证号码", "db_field": "party_id_number", "group": "当事人信息"},
    # D组：执法人员信息（4个，JSON字段）
    "主办": {"label": "主办人员姓名", "db_field": "main_officer", "json_key": "name", "group": "执法人员信息"},
    "协办": {"label": "协办人员姓名", "db_field": "assistant_officer", "json_key": "name", "group": "执法人员信息"},
    "主办证号": {"label": "主办人员证号", "db_field": "main_officer", "json_key": "badge_number", "group": "执法人员信息"},
    "协办证号": {"label": "协办人员证号", "db_field": "assistant_officer", "json_key": "badge_number", "group": "执法人员信息"},
    # E组：案由与法律依据（5个）
    "案由": {"label": "案由", "db_field": "causes_merged", "group": "案由与法律依据"},
    "案由_法律依据_违法条款": {"label": "违法条款", "db_field": "violation_laws_full", "group": "案由与法律依据"},
    "案由_法律依据_处罚条款": {"label": "处罚条款", "db_field": "penalty_laws_clause", "group": "案由与法律依据"},
    "案由_法律规定_处罚条款": {"label": "法律规定-处罚条款", "db_field": "penalty_laws_clause", "group": "案由与法律依据"},
    "案由_法律依据_处罚_详细内容": {"label": "处罚详细内容", "db_field": "penalty_content_detail", "group": "案由与法律依据"},
    # F组：其他（3个）
    "案件描述": {"label": "案件描述", "db_field": "description", "group": "其他"},
    "接收材料日期": {"label": "接收材料日期", "db_field": "receive_date", "group": "其他"},
    "案件名称": {"label": "案件名称", "db_field": "title", "group": "其他"},
}

router = APIRouter(tags=["document-generation"])


async def _get_db_templates(cur, category=None, keyword=None):
    conditions = ["is_deleted = 0", "is_active = 1"]
    params = []
    if category:
        conditions.append("template_category = %s")
        params.append(category)
    if keyword:
        conditions.append("(template_name LIKE %s OR template_code LIKE %s)")
        params.extend([f"%{keyword}%", f"%{keyword}%"])
    where = f"WHERE {' AND '.join(conditions)}"
    await cur.execute(
        f"SELECT id, template_code, template_name, template_category, "
        f"template_file_path, schema_TEXT, layout_type, description "
        f"FROM document_templates {where} ORDER BY template_category, id",
        params,
    )
    rows = [dict(row) for row in (await cur.fetchall())]
    for row in rows:
        if row.get("schema_TEXT") and isinstance(row["schema_TEXT"], str):
            try:
                row["schema_TEXT"] = json.loads(row["schema_TEXT"])
            except (json.JSONDecodeError, TypeError):
                pass
        row["name"] = row.get("template_name", "")
        row["category"] = row.get("template_category", "")
    return rows


def _build_user_doc_path(upload_dir: str, user_id: int, module: str, filename: str) -> str:
    today = datetime.now().strftime("%Y%m%d")
    dir_path = os.path.join(upload_dir, str(user_id), module, today)
    os.makedirs(dir_path, exist_ok=True)

    base_name, ext = os.path.splitext(filename)
    final_path = os.path.join(dir_path, filename)
    if os.path.exists(final_path):
        counter = 2
        while os.path.exists(os.path.join(dir_path, f"{base_name}_{counter}{ext}")):
            counter += 1
        final_path = os.path.join(dir_path, f"{base_name}_{counter}{ext}")
    return final_path


@router.get("/template-list")
async def get_templates(
    request: Request,
    user: dict = Depends(require_login),
    db=Depends(get_db),
):
    # document_templates 在 system.db 中
    _sys_conn, sys_cur, _sys_pool = await _get_system_cur()
    try:
        query_cur = sys_cur or db[1]
        templates = await _get_db_templates(query_cur)
        templates_light = []
        for t in templates:
            templates_light.append({
                "id": t["id"],
                "template_code": t.get("template_code", ""),
                "name": t["template_name"],
                "category": t["template_category"],
                "description": t.get("description", ""),
            })
        return {"templates": templates_light, "total": len(templates_light)}
    finally:
        await _release_system_conn(_sys_conn, _sys_pool)


@router.post("/match-template")
async def match_template_endpoint(
    request: Request,
    user: dict = Depends(require_login),
    db=Depends(get_db),
):
    # document_templates 在 system.db 中
    _sys_conn, sys_cur, _sys_pool = await _get_system_cur()
    try:
        query_cur = sys_cur or db[1]
        body = await safe_json(request)
        context = body.get("context", body.get("intent", ""))
        templates = await _get_db_templates(query_cur)
        matched = match_templates(context, templates)
        return {"templates": matched, "total": len(matched)}
    finally:
        await _release_system_conn(_sys_conn, _sys_pool)


# ===== 零AI文书生成核心逻辑 =====

def _json_get(json_str, key):
    """安全地从JSON字符串中提取字段"""
    try:
        d = json.loads(json_str) if json_str else {}
        return d.get(key, '')
    except Exception:
        return ''


def _parse_case_number(case_number: str) -> tuple:
    """从完整案号字符串中解析出（地区简称, 案件序号）

    规则说明：
      - 完整案号格式: 一师市监处罚〔2026〕123号
      - 案号_地区简称 = 区域+单位简称（不含文书简称），如 "一师市监"、"一师农"
      - 案号_案件号   = 年号+序号部分，如 "〔2026〕123号"
      - 文书简称（如"处罚"、"物移"、"处字"等）由模板预设，不需要截取

    支持格式：
      - 新格式: 一师市监处罚〔2026〕123号  → ('一师市监', '〔2026〕123号')
      - 新格式: 一师农物移〔2026〕456号     → ('一师农', '〔2026〕456号')
      - 旧格式: 市监处字[2026]第xxx号       → ('市监', '[2026]第xxx')
    """
    if not case_number:
        return '', ''
    cn = case_number.strip()

    # 已知文书简称白名单（市场监管处罚文书模板中使用的）
    # 来源: 立案(立)、调查取证、强制措施(物移/强措)、责改(责改/通知)、决定(处罚/处字)、执行、结案
    DOC_ABBR_SET = {
        '处罚', '处字',   # 决定类
        '物移', '强措',   # 强制措施类
        '责改', '通知',   # 责令改正/通知类
        '退赔',           # 退款通知书
        '当罚',           # 当场处罚
        '立',              # 立案类
        '询',              # 询问/讯问
        '审',              # 审核/听证
        '告',              # 告知
        '决',              # 决定
        '执',              # 执行
        '结',              # 结案
        '案件源',          # 案件来源登记表
        '抽样',            # 抽样
        '检验',            # 检验/检测
        '送达',            # 送达
        '回执',            # 回执
        '扣留',            # 扣留
        '封存',            # 封存/解除
        '解封',            # 解除
        '延期',            # 延期
        '复核',            # 复核
        '移送',            # 移送
        '结案',            # 结案报告
        '销案',            # 销案
        '撤案',            # 撤案
        'XX',              # 测试占位符（生产数据不会出现）
    }

    # 新格式：含 〔 〕
    if '〔' in cn and '〕' in cn:
        bracket_start = cn.index('〔')
        bracket_end = cn.index('〕') + 1
        prefix = cn[:bracket_start].strip()   # 〔 之前的部分，如 "一师市监处罚"
        year_part = cn[bracket_start:bracket_end]  # 〔2026〕
        tail = cn[bracket_end:].strip()         # 123号

        # 从 prefix 中剥离文书简称（匹配最长的已知简称）
        region = prefix
        for abbr in sorted(DOC_ABBR_SET, key=len, reverse=True):
            if prefix.endswith(abbr) and len(prefix) > len(abbr):
                region = prefix[:-len(abbr)].strip()
                break

        # 拼接案件序号
        tail_clean = tail[:-1].strip() if tail.endswith('号') else tail
        seq = f"{year_part}{tail_clean}号" if tail_clean else year_part
        return region, seq

    # 旧格式：含 [ ]
    if '[' in cn:
        idx = cn.index('[')
        prefix = cn[:idx].strip()

        # 从 prefix 中剥离文书简称
        region = prefix
        for abbr in sorted(DOC_ABBR_SET, key=len, reverse=True):
            if prefix.endswith(abbr) and len(prefix) > len(abbr):
                region = prefix[:-len(abbr)].strip()
                break

        return region, cn[idx:]

    # 无法解析的格式：整个作为地区简称
    return cn, ''


def _build_case_render_context(case_data: dict, causes_data: list) -> dict:
    """从案件数据构建29个占位符的渲染上下文"""
    receive_date = case_data.get('receive_date')
    if receive_date:
        try:
            if isinstance(receive_date, datetime):
                receive_date = receive_date.strftime('%Y-%m-%d')
            else:
                receive_date = str(receive_date)[:10]
        except Exception:
            receive_date = ''

    cause_names = [c.get('cause_name', '') for c in causes_data if c.get('cause_name')]
    causes_merged = '、'.join(cause_names)

    violation_parts = []
    penalty_clause_parts = []
    penalty_detail_parts = []
    for c in causes_data:
        cname = c.get('cause_name', '')
        laws = c.get('_laws', [])
        v_laws = [l for l in laws if l.get('law_type') == 'violation']
        p_laws = [l for l in laws if l.get('law_type') == 'penalty']
        if v_laws and cname:
            law_refs = '，'.join([f"{l.get('law_name', '')} {l.get('article_number', '')}" for l in v_laws if l.get('law_name')])
            violation_parts.append(f"{cname}\n  {law_refs}")
        for l in p_laws:
            clause = l.get('article_number', '')
            detail = l.get('content_detail', '')
            if clause:
                penalty_clause_parts.append(f"{l.get('law_name', '')}{clause}")
            if detail:
                penalty_detail_parts.append(detail)

    # 解析案号：从完整 case_number 字符串中提取地区简称和案件序号
    # 数据库 cases 表只有 case_number 字段（如"一师市监XX〔2026〕123号"），
    # 没有 case_number_region / case_number_seq 列
    cn_region, cn_seq = _parse_case_number(case_data.get('case_number') or '')

    return {
        '案件名称': case_data.get('title') or '',
        '案号_地区简称': cn_region,
        '案号_案件号': cn_seq,
        '案由': causes_merged,
        '接收材料日期': receive_date,
        '案件描述': case_data.get('description') or '',
        '执法单位名称': case_data.get('org_name') or '',
        '单位联系电话': case_data.get('org_phone') or case_data.get('unit_contact_phone') or '',
        '单位联系地址': case_data.get('org_address') or case_data.get('unit_contact_address') or '',
        '执法单位地址': case_data.get('org_address') or case_data.get('unit_contact_address') or '',
        '当事人名称': case_data.get('party_name') or '',
        '当事人姓名': case_data.get('party_name') or '',
        '字号名称': case_data.get('party_name') or '',
        '当事人住所': case_data.get('address') or '',
        '当事人住所地址': case_data.get('address') or '',
        '当事人联系电话': case_data.get('phone') or '',
        '统一社会信用代码': case_data.get('credit_code') or '',
        '个体统一社会信用代码': case_data.get('credit_code') or '',
        '法定代表人': case_data.get('legal_representative') or '',
        '身份证件号码': case_data.get('party_id_number') or '',
        '法人身份证号码': case_data.get('party_id_number') or '',
        '主办': _json_get(case_data.get('main_officer'), 'name'),
        '协办': _json_get(case_data.get('assistant_officer'), 'name'),
        '主办证号': _json_get(case_data.get('main_officer'), 'badge_number'),
        '协办证号': _json_get(case_data.get('assistant_officer'), 'badge_number'),
        '案由_法律依据_违法条款': '\n'.join(violation_parts),
        '案由_法律依据_处罚条款': '；'.join(penalty_clause_parts) if penalty_clause_parts else '',
        '案由_法律规定_处罚条款': '；'.join(penalty_clause_parts) if penalty_clause_parts else '',
        '案由_法律依据_处罚_详细内容': '\n\n'.join(penalty_detail_parts),
    }


async def _get_system_cur():
    """获取 system.db 的游标（用于查询 document_templates 等系统表）

    返回 (conn, cur, pool) 三元组，使用后需调用 _release_system_conn() 释放连接。
    兼容 SQLCipher 加密连接（sync cursor）和 aiosqlite 连接（Result 上下文管理器）。
    """
    if not _has_split_databases():
        return None, None, None
    from ...core.database import get_system_pool, _SQLiteCursorWrapper
    pool = await get_system_pool()
    conn = await pool.acquire_read()
    raw_cur = conn.cursor()
    # aiosqlite 0.22+: conn.cursor() 返回 Result 上下文管理器，需要 __aenter__ 获取真正的 Cursor
    if hasattr(raw_cur, '__aenter__'):
        real_cur = await raw_cur.__aenter__()
    else:
        real_cur = raw_cur
    cur = _SQLiteCursorWrapper(real_cur, pool)
    return conn, cur, pool


async def _release_system_conn(conn, pool):
    """释放 system.db 读连接"""
    if conn and pool:
        await pool.release_read(conn)


async def _fetch_template_and_placeholders(cur, template_id, sys_cur=None):
    """获取模板信息及其占位符列表

    Args:
        cur: user.db 游标（未使用，保留兼容）
        template_id: 模板ID或模板编码
        sys_cur: system.db 游标（用于查询 document_templates）
    """
    # 优先使用 sys_cur（system.db），回退到 cur（兼容旧单库模式）
    query_cur = sys_cur or cur
    try:
        tid = int(template_id)
        await query_cur.execute(
            "SELECT id, template_code, template_name, template_category, "
            "template_file_path, schema_TEXT FROM document_templates WHERE id = ? AND is_deleted = 0 AND is_active = 1",
            (tid,),
        )
    except (ValueError, TypeError):
        await query_cur.execute(
            "SELECT id, template_code, template_name, template_category, "
            "template_file_path, schema_TEXT FROM document_templates WHERE template_code = ? AND is_deleted = 0 AND is_active = 1",
            (template_id,),
        )
    row = await query_cur.fetchone()
    if not row:
        return None, []
    template = dict(row)
    tpl_path = template.get("template_file_path", "")
    placeholders = []
    if tpl_path and os.path.exists(tpl_path):
        placeholders = extract_template_placeholders(tpl_path)

    # 文件不存在、提取失败、或仅返回兜底占位符时，从数据库 schema_TEXT（placeholder_mapping）回退读取
    if not placeholders or placeholders == ["通用文档模板（无特殊字段）"]:
        schema_text = template.get("schema_TEXT") or ""
        if schema_text:
            try:
                mapping = json.loads(schema_text)
                if isinstance(mapping, dict):
                    # placeholder_mapping 的 key 格式为 "{{占位符名}}"，去掉花括号
                    placeholders = [
                        k.replace("{{", "").replace("}}", "") if k.startswith("{{") else k
                        for k in mapping.keys()
                    ]
            except (json.JSONDecodeError, TypeError):
                pass

    return template, placeholders


async def _fetch_case_with_causes(cur, case_id):
    """获取案件数据及案由法条"""
    await cur.execute("SELECT * FROM cases WHERE id = %s AND is_deleted = 0", (case_id,))
    case_row = await cur.fetchone()
    if not case_row:
        return None, []
    case_data = dict(case_row)

    created_by = case_data.get("created_by")
    if created_by:
        # 优先从 user_memories（个人中心）获取执法单位信息
        try:
            await cur.execute(
                "SELECT content FROM user_memories WHERE user_id = %s AND memory_type = 'user_profile'",
                (created_by,),
            )
            profile_row = await cur.fetchone()
            if profile_row and dict(profile_row).get("content"):
                import json as _json
                saved = _json.loads(dict(profile_row)["content"])
                basic_info = saved.get("basic_info", {})
                if basic_info.get("org_name") and not case_data.get("org_name"):
                    case_data["org_name"] = basic_info["org_name"]
                if basic_info.get("org_phone") and not case_data.get("org_phone"):
                    case_data["org_phone"] = basic_info["org_phone"]
                if basic_info.get("org_address") and not case_data.get("org_address"):
                    case_data["org_address"] = basic_info["org_address"]
                # unit_contact_* 字段也从个人中心获取（与 org_* 同值）
                if basic_info.get("org_phone") and not case_data.get("unit_contact_phone"):
                    case_data["unit_contact_phone"] = basic_info["org_phone"]
                if basic_info.get("org_address") and not case_data.get("unit_contact_address"):
                    case_data["unit_contact_address"] = basic_info["org_address"]
        except Exception:
            pass

        # 兜底：从 users 表获取
        if not case_data.get("org_name"):
            await cur.execute(
                "SELECT org_name, unit_contact_phone, unit_contact_address FROM users WHERE id = %s",
                (created_by,),
            )
            creator_row = await cur.fetchone()
            creator = dict(creator_row) if creator_row else None
            if creator:
                if creator.get("org_name") and not case_data.get("org_name"):
                    case_data["org_name"] = creator["org_name"]
                if creator.get("unit_contact_phone") and not case_data.get("org_phone"):
                    case_data["org_phone"] = creator["unit_contact_phone"]
                if creator.get("unit_contact_address") and not case_data.get("org_address"):
                    case_data["org_address"] = creator["unit_contact_address"]

    await cur.execute(
        """SELECT id, cause_name, cause_code, sort_order
           FROM case_causes_detail WHERE case_id = %s ORDER BY sort_order ASC, id ASC""",
        (case_id,),
    )
    causes = [dict(row) for row in (await cur.fetchall())]
    for cause in causes:
        await cur.execute(
            """SELECT law_type, law_name, article_number, content_detail
               FROM case_cause_laws WHERE cause_detail_id = ? ORDER BY law_type, sort_order""",
            (cause["id"],),
        )
        rows = await cur.fetchall()
        cause["_laws"] = [dict(r) for r in rows]

    return case_data, causes


@router.post("/check-fields")
async def check_template_fields(
    request: Request,
    user: dict = Depends(require_login),
    db=Depends(get_db),
):
    """检查模板所需字段与案件数据的匹配情况，返回缺失字段列表供用户补充"""
    conn, cur = db
    body = await safe_json(request)
    case_id = body.get("case_id")
    template_id = body.get("template_id")
    if not case_id or not template_id:
        return {"error": "缺少参数", "detail": "case_id and template_id required"}

    # 获取 system.db 游标用于查询 document_templates
    _sys_conn, sys_cur, _sys_pool = await _get_system_cur()
    try:
        template, tpl_placeholders = await _fetch_template_and_placeholders(cur, template_id, sys_cur=sys_cur)
        if not template:
            return {"error": "模板不存在", "detail": "template not found"}

        logger.info(f"[check-fields] template_id={template_id}, name={template.get('template_name')}, "
                    f"tpl_path={template.get('template_file_path')}, "
                    f"placeholders={tpl_placeholders}, count={len(tpl_placeholders)}, "
                    f"schema_TEXT={'有' if template.get('schema_TEXT') else '无'}")

        case_data, causes = await _fetch_case_with_causes(cur, case_id)
        if not case_data:
            return {"error": "案件不存在", "detail": "case not found"}

        context = _build_case_render_context(case_data, causes)

        filled_fields = []
        missing_fields = []
        known_placeholders = set(PLACEHOLDER_FIELD_DEF.keys())
        for ph in tpl_placeholders:
            ph_key = ph  # 占位符名称不含花括号，与 docxtpl/jinja2 变量名一致
            if ph_key not in known_placeholders:
                continue
            field_def = PLACEHOLDER_FIELD_DEF[ph_key]
            value = context.get(ph_key, '')
            is_filled = bool(value and str(value).strip())
            field_info = {
                "placeholder": ph,
                "placeholder_key": ph_key,
                "label": field_def["label"],
                "group": field_def["group"],
                "current_value": value if is_filled else "",
                "is_filled": is_filled,
            }
            if is_filled:
                filled_fields.append(field_info)
            else:
                missing_fields.append(field_info)

        result = {
            "template_id": template["id"],
            "template_name": template["template_name"],
            "template_category": template.get("template_category", ""),
            "total_placeholders": len(tpl_placeholders),
            "known_placeholders_count": len(filled_fields) + len(missing_fields),
            "filled_count": len(filled_fields),
            "missing_count": len(missing_fields),
            "filled_fields": filled_fields,
            "missing_fields": missing_fields,
            "render_context": context,
        }
        logger.info(f"[check-fields] result: total={result['total_placeholders']}, "
                    f"filled={result['filled_count']}, missing={result['missing_count']}")
        return result
    finally:
        await _release_system_conn(_sys_conn, _sys_pool)


@router.post("/generate-direct")
async def generate_document_direct(
    request: Request,
    user: dict = Depends(require_login),
    db=Depends(get_db),
):
    """零AI文书生成：直接用案件数据+用户可选补充填充模板生成docx"""
    conn, cur = db
    body = await safe_json(request)
    case_id = body.get("case_id")
    template_id = body.get("template_id")
    field_overrides = body.get("field_overrides")

    if not case_id or not template_id:
        return {"error": "缺少参数", "detail": "case_id and template_id required"}

    user_id = int(user["userId"]) if str(user["userId"]).isdigit() else 1

    # 获取 system.db 游标用于查询 document_templates
    _sys_conn, sys_cur, _sys_pool = await _get_system_cur()
    try:
        template, _ = await _fetch_template_and_placeholders(cur, template_id, sys_cur=sys_cur)
        if not template:
            return {"error": "模板不存在", "detail": "template not found"}
    finally:
        await _release_system_conn(_sys_conn, _sys_pool)

    template_file_path = template.get("template_file_path", "")
    if not template_file_path or not os.path.exists(template_file_path):
        return {"error": "模板文件不存在", "detail": f"template docx not found: {template_file_path}"}

    case_data, causes = await _fetch_case_with_causes(cur, case_id)
    if not case_data:
        return {"error": "案件不存在", "detail": "case not found"}

    render_context = _build_case_render_context(case_data, causes)

    if field_overrides and isinstance(field_overrides, dict):
        for ph_key, value in field_overrides.items():
            if value is not None and str(value).strip():
                render_context[ph_key] = str(value).strip()

    try:
        temp_output = generate_document(template_file_path, render_context)
    except Exception as e:
        logger.error(f"docxtpl render failed: {e}")
        return {"error": "文档渲染失败", "detail": str(e)}

    upload_dir = await get_upload_dir(db)
    docx_filename = f"{template['template_name']}.docx"
    final_path = _build_user_doc_path(upload_dir, user_id, "cases", docx_filename)

    shutil.move(temp_output, final_path)
    file_size = os.path.getsize(final_path)

    latest_version = 1
    await cur.execute(
        "SELECT COALESCE(MAX(version), 0) as max_ver FROM case_documents "
        "WHERE case_id = %s AND template_id = %s AND is_deleted = 0",
        (case_id, template["id"]),
    )
    ver_row = await cur.fetchone()
    if ver_row:
        latest_version = ver_row["max_ver"] + 1

    template_name = template["template_name"]
    document_name = f"{template_name}-v{latest_version}"

    await cur.execute(
        "INSERT INTO case_documents (case_id, template_id, template_name, document_name, "
        "version, status, content_docx_path, basic_info, generated_content, generation_method, created_by) "
        "VALUES (%s, %s, %s, %s, %s, 'draft', %s, %s, %s, 'direct_fill', %s)",
        (
            case_id, template["id"], template_name, document_name,
            latest_version, final_path,
            json.dumps(render_context, ensure_ascii=False, default=str),
            json.dumps({"overrides": field_overrides or {}}, ensure_ascii=False),
            user_id,
        ),
    )
    doc_id = cur.lastrowid

    await cur.execute(
        "UPDATE cases SET document_count = COALESCE(document_count, 0) + 1, updated_at = NOW() WHERE id = %s",
        (case_id,),
    )

    doc_key = f"{doc_id}-{int(time.time() * 1000)}"

    return {
        "success": True,
        "data": {
            "document_id": doc_id,
            "template_name": template_name,
            "document_name": document_name,
            "version": latest_version,
            "status": "draft",
            "generation_method": "direct_fill",
            "file_size": file_size,
            "document_url": f"{settings.NEXT_PUBLIC_API_URL}/api/user/cases/{case_id}/documents/{doc_id}/download",
            "document_key": doc_key,
        },
    }
