import hashlib
import io
import json
import re
from datetime import date, datetime, time
from typing import Optional
from urllib.parse import quote

from fastapi import APIRouter, File, HTTPException, Query, UploadFile
from fastapi.responses import StreamingResponse
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Font, PatternFill
from psycopg2.extras import RealDictCursor

from database import get_db_connection

router = APIRouter(prefix="/api/excel-import", tags=["Excel导入"])

FIELDS = {
    "项目": [("项目名称", "project_name", True, "text"), ("评价模式", "eval_mode", True, "text")],
    "精度指标": [("一级分类", "category_level_1", True, "text"), ("二级类型", "category_level_2", True, "text"), ("指标名称", "metric_name", True, "text"), ("录入方式", "value_type", True, "option"), ("说明", "description", False, "text")],
    "资产台账": [("项目名称", "project_name", False, "text"), ("资产类别", "asset_category", True, "option"), ("资产代码", "asset_code", True, "code"), ("资产名称", "asset_name", True, "text"), ("资产类型", "asset_type", True, "text")],
    "资产指标绑定": [("资产代码", "asset_code", True, "code"), ("指标名称", "metric_name", True, "text"), ("上限", "upper_limit", False, "float"), ("下限", "lower_limit", False, "float"), ("出厂精度", "baseline_value", False, "float"), ("失效精度", "failure_value", False, "float"), ("理想值", "ideal_value", False, "float"), ("极限值", "extreme_value", False, "float"), ("准则层权重", "criterion_weight", False, "float"), ("指标层权重", "indicator_weight", False, "float"), ("单循环小时", "cycle_hours", False, "float"), ("单位", "unit", False, "text")],
    "制品": [("制品代码", "product_code", True, "code"), ("制品名称", "product_name", True, "text"), ("制品说明", "product_detail", False, "text")],
    "资产制品绑定": [("资产代码", "asset_code", True, "code"), ("制品代码", "product_code", True, "code"), ("计划数量", "total_quantity", False, "integer"), ("加工说明", "process_note", False, "text")],
    "制品指标配置": [("制品代码", "product_code", True, "code"), ("指标名称", "metric_name", True, "text"), ("失效精度", "upper_limit", False, "float"), ("出厂精度", "lower_limit", False, "float"), ("理想值", "ideal_value", False, "float"), ("极限值", "extreme_value", False, "float"), ("单位", "unit", False, "text"), ("作用部位或备注", "position_note", False, "text")],
    "精度测量记录": [("资产代码", "asset_code", True, "code"), ("测定时间", "record_time", True, "datetime"), ("指标名称", "metric_name", True, "text"), ("测定值", "value", True, "float"), ("样本序号", "sample_no", False, "text")],
    "制品测量记录": [("资产代码", "asset_code", True, "code"), ("制品代码", "product_code", True, "code"), ("生产批次号", "batch_no", True, "text"), ("测定时间", "record_time", True, "datetime"), ("生产数量", "production_quantity", False, "integer"), ("备注", "remark", False, "text"), ("指标名称", "metric_name", True, "text"), ("样本序号", "sample_no", True, "text"), ("测定值", "value", True, "float")],
}
ORDER = list(FIELDS)
SCOPES = {"项目配置": ["项目"], "精度指标": ["精度指标"], "资产台账": ["资产台账", "资产指标绑定", "资产制品绑定"], "制品配置": ["制品", "制品指标配置"], "精度测量记录": ["精度测量记录"], "制品测量记录": ["制品测量记录"]}
CODE_RE = re.compile(r"^[A-Za-z0-9][A-Za-z0-9_.-]*$")

def ensure_tables(cursor):
    cursor.execute("""CREATE TABLE IF NOT EXISTS tb_excel_import_batch (batch_id SERIAL PRIMARY KEY,file_name VARCHAR(255),file_hash VARCHAR(64),status VARCHAR(30),sheet_count INTEGER DEFAULT 0,row_count INTEGER DEFAULT 0,error_count INTEGER DEFAULT 0,warning_count INTEGER DEFAULT 0,imported_count INTEGER DEFAULT 0,skipped_count INTEGER DEFAULT 0,payload JSONB,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,imported_at TIMESTAMP)""")
    cursor.execute("""CREATE TABLE IF NOT EXISTS tb_excel_import_issue (issue_id SERIAL PRIMARY KEY,batch_id INTEGER REFERENCES tb_excel_import_batch(batch_id) ON DELETE CASCADE,severity VARCHAR(20),sheet_name VARCHAR(100),row_number INTEGER,column_name VARCHAR(100),error_code VARCHAR(50),message TEXT,cell_value TEXT)""")

def scope_sheets(scope_key: Optional[str]):
    if not scope_key: return ORDER
    if scope_key not in SCOPES: raise HTTPException(400, "该页面未配置 Excel 导入范围")
    return SCOPES[scope_key]

def build_template(sheets):
    wb=Workbook(); wb.remove(wb.active)
    for name in sheets:
        ws=wb.create_sheet(name); ws.append([f[0] for f in FIELDS[name]]); ws.freeze_panes="A2"; ws.auto_filter.ref=ws.dimensions
        for i,cell in enumerate(ws[1],1): cell.font=Font(bold=True,color="FFFFFF"); cell.fill=PatternFill("solid",fgColor="5B87C5"); cell.alignment=Alignment(horizontal="center"); ws.column_dimensions[cell.column_letter].width=max(14,min(28,len(cell.value)*2+8))
    notes=wb.create_sheet("填写说明"); notes.append(["填写规则"]); notes.append(["请使用中文表头；代码只使用字母、数字、点、下划线和短横线；时间格式为 yyyy-mm-dd hh:mm:ss；空值保持单元格空白。"]); notes.column_dimensions["A"].width=100
    return wb

def issue(items,sheet,row,col,code,message,value=None,severity="error"):
    items.append({"severity":severity,"sheet_name":sheet,"row_number":row,"column_name":col,"error_code":code,"message":message,"cell_value":None if value is None else str(value)[:500]})

def normalize(value,kind,items,sheet,row,col):
    if value is None or (isinstance(value,str) and not value.strip()): return None
    if isinstance(value,str) and value.startswith("="): issue(items,sheet,row,col,"FORMULA","数据区域不允许使用公式",value); return None
    if kind in {"text","option","code"}:
        if not isinstance(value,str): issue(items,sheet,row,col,"TYPE","该字段必须按文本填写",value); return None
        value=value.strip()
        if kind=="code" and not CODE_RE.fullmatch(value): issue(items,sheet,row,col,"CODE_FORMAT","代码只能包含字母、数字、点、下划线和短横线",value)
        return value
    if kind in {"float","integer"}:
        try: number=float(value)
        except Exception: issue(items,sheet,row,col,"TYPE","必须填写数值",value); return None
        if kind=="integer" and not number.is_integer(): issue(items,sheet,row,col,"TYPE","必须填写整数",value); return None
        return int(number) if kind=="integer" else number
    if kind=="datetime":
        if isinstance(value,datetime): return value.isoformat(sep=" ")
        if isinstance(value,date): return datetime.combine(value,time.min).isoformat(sep=" ")
        try: return datetime.strptime(str(value).strip(),"%Y-%m-%d %H:%M:%S").isoformat(sep=" ")
        except Exception: issue(items,sheet,row,col,"TYPE","时间格式必须为 yyyy-mm-dd hh:mm:ss",value); return None
    return value

def parse(content,sheets):
    issues=[]; payload={name:[] for name in ORDER}; summary={"sheets":{}}
    try: wb=load_workbook(io.BytesIO(content),data_only=False)
    except Exception: issue(issues,"工作簿",None,None,"FILE","无法读取 Excel 文件"); return payload,issues,summary
    allowed=set(sheets)|{"填写说明"}
    for name in wb.sheetnames:
        if name not in allowed: issue(issues,name,1,None,"UNKNOWN_SHEET","当前入口不接受此工作表")
    for name in sheets:
        if name not in wb.sheetnames: continue
        ws=wb[name]; specs=FIELDS[name]; headers=[str(c.value).strip() if c.value is not None else None for c in ws[1]]; expected=[f[0] for f in specs]
        for h in expected:
            if h not in headers: issue(issues,name,1,h,"HEADER_MISSING","缺少规定列")
        for h in [x for x in headers if x]:
            if h not in expected: issue(issues,name,1,h,"HEADER_UNKNOWN","存在未定义列")
        positions={h:i+1 for i,h in enumerate(headers) if h}; count=0
        for rn in range(2,ws.max_row+1):
            if all(ws.cell(rn,c).value in (None,"") for c in range(1,ws.max_column+1)): continue
            count+=1; row={"_row":rn}
            for header,key,required,kind in specs:
                raw=ws.cell(rn,positions[header]).value if header in positions else None; val=normalize(raw,kind,issues,name,rn,header)
                if required and val is None: issue(issues,name,rn,header,"REQUIRED","该字段为必填项",raw)
                row[key]=val
            payload[name].append(row)
        summary["sheets"][name]={"rows":count}
    summary["sheet_count"]=sum(1 for n in sheets if n in wb.sheetnames); summary["row_count"]=sum(v["rows"] for v in summary["sheets"].values())
    if not summary["sheet_count"]: issue(issues,"工作簿",None,None,"NO_DATA_SHEET","没有可导入的数据工作表")
    elif not summary["row_count"]: issue(issues,"工作簿",None,None,"NO_DATA","工作表中没有可导入的数据行")
    return payload,issues,summary

def context(cursor):
    cursor.execute("SELECT project_name,project_id FROM tb_project")
    projects = {row["project_name"]: row["project_id"] for row in cursor.fetchall()}
    cursor.execute("SELECT metric_name,metric_id,value_type,category_level_1 FROM tb_metric_dict")
    metrics = {
        row["metric_name"]: (row["metric_id"], row["value_type"], row["category_level_1"])
        for row in cursor.fetchall()
    }
    cursor.execute("SELECT asset_code,asset_id FROM tb_asset")
    assets = {row["asset_code"]: row["asset_id"] for row in cursor.fetchall()}
    cursor.execute("SELECT product_code,product_id FROM tb_product_def")
    products = {row["product_code"]: row["product_id"] for row in cursor.fetchall()}
    return projects,metrics,assets,products

def validate_relations(cursor,payload,issues):
    projects,metrics,assets,products=context(cursor)
    projects.update({r["project_name"]:None for r in payload["项目"]}); metrics.update({r["metric_name"]:(None,r["value_type"],r["category_level_1"]) for r in payload["精度指标"]}); assets.update({r["asset_code"]:None for r in payload["资产台账"]}); products.update({r["product_code"]:None for r in payload["制品"]})
    for r in payload["精度指标"]:
        if r["category_level_1"] not in {"几何精度","位置精度","加工精度"}: issue(issues,"精度指标",r["_row"],"一级分类","TYPE","只能填写几何精度、位置精度或加工精度")
        if r["value_type"] not in {"单值","抽样数组"}: issue(issues,"精度指标",r["_row"],"录入方式","TYPE","只能填写单值或抽样数组")
    for r in payload["资产台账"]:
        if r["asset_category"] not in {"整机","部件"}: issue(issues,"资产台账",r["_row"],"资产类别","TYPE","只能填写整机或部件")
        if r.get("project_name") and r["project_name"] not in projects: issue(issues,"资产台账",r["_row"],"项目名称","REFERENCE","项目不存在")
    checks=[("资产指标绑定","asset_code",assets,"资产代码"),("资产指标绑定","metric_name",metrics,"指标名称"),("资产制品绑定","asset_code",assets,"资产代码"),("资产制品绑定","product_code",products,"制品代码"),("制品指标配置","product_code",products,"制品代码"),("制品指标配置","metric_name",metrics,"指标名称"),("精度测量记录","asset_code",assets,"资产代码"),("精度测量记录","metric_name",metrics,"指标名称"),("制品测量记录","asset_code",assets,"资产代码"),("制品测量记录","product_code",products,"制品代码"),("制品测量记录","metric_name",metrics,"指标名称")]
    for sheet,key,mapping,col in checks:
        for r in payload[sheet]:
            if r.get(key) not in mapping: issue(issues,sheet,r["_row"],col,"REFERENCE",f"{col}不存在",r.get(key))
    unique_keys = {
        "项目": ("project_name",),
        "精度指标": ("metric_name",),
        "资产台账": ("asset_code",),
        "资产指标绑定": ("asset_code", "metric_name"),
        "制品": ("product_code",),
        "资产制品绑定": ("asset_code", "product_code"),
        "制品指标配置": ("product_code", "metric_name"),
        "精度测量记录": ("asset_code", "record_time", "metric_name", "sample_no"),
        "制品测量记录": ("asset_code", "product_code", "batch_no", "record_time", "metric_name", "sample_no"),
    }
    for sheet, keys in unique_keys.items():
        seen = {}
        for row in payload[sheet]:
            identity = tuple(row.get(key) for key in keys)
            if identity in seen:
                issue(issues, sheet, row["_row"], None, "DUPLICATE_ROW", f"与第 {seen[identity]} 行重复")
            else:
                seen[identity] = row["_row"]

    for row in payload["资产指标绑定"]:
        for key, column in (("criterion_weight", "准则层权重"), ("indicator_weight", "指标层权重")):
            value = row.get(key)
            if value is not None and not 0 <= value <= 1:
                issue(issues, "资产指标绑定", row["_row"], column, "RANGE", "权重必须在 0 到 1 之间", value)
    for sheet, key, column in (("资产制品绑定", "total_quantity", "计划数量"), ("制品测量记录", "production_quantity", "生产数量")):
        for row in payload[sheet]:
            if row.get(key) is not None and row[key] < 0:
                issue(issues, sheet, row["_row"], column, "RANGE", "数量不能为负数", row[key])

    cursor.execute("SELECT a.asset_code,m.metric_name FROM tb_asset_metric_bind b JOIN tb_asset a ON a.asset_id=b.asset_id JOIN tb_metric_dict m ON m.metric_id=b.metric_id")
    asset_metric = {(row["asset_code"], row["metric_name"]) for row in cursor.fetchall()}
    asset_metric.update((row["asset_code"], row["metric_name"]) for row in payload["资产指标绑定"])
    for row in payload["精度测量记录"]:
        if row.get("asset_code") in assets and row.get("metric_name") in metrics and (row["asset_code"], row["metric_name"]) not in asset_metric:
            issue(issues, "精度测量记录", row["_row"], "指标名称", "NOT_BOUND", "该指标未绑定到当前资产", row["metric_name"])

    cursor.execute("SELECT a.asset_code,p.product_code FROM tb_asset_product_bind b JOIN tb_asset a ON a.asset_id=b.asset_id JOIN tb_product_def p ON p.product_id=b.product_id")
    asset_product = {(row["asset_code"], row["product_code"]) for row in cursor.fetchall()}
    asset_product.update((row["asset_code"], row["product_code"]) for row in payload["资产制品绑定"])
    cursor.execute("SELECT p.product_code,m.metric_name FROM tb_product_metric_config c JOIN tb_product_def p ON p.product_id=c.product_id JOIN tb_metric_dict m ON m.metric_id=c.metric_id")
    product_metric = {(row["product_code"], row["metric_name"]) for row in cursor.fetchall()}
    product_metric.update((row["product_code"], row["metric_name"]) for row in payload["制品指标配置"])
    for row in payload["制品测量记录"]:
        if row.get("asset_code") in assets and row.get("product_code") in products and (row["asset_code"], row["product_code"]) not in asset_product:
            issue(issues, "制品测量记录", row["_row"], "制品代码", "NOT_BOUND", "该制品未绑定到当前资产", row["product_code"])
        if row.get("product_code") in products and row.get("metric_name") in metrics and (row["product_code"], row["metric_name"]) not in product_metric:
            issue(issues, "制品测量记录", row["_row"], "指标名称", "NOT_CONFIGURED", "该指标未配置到当前制品", row["metric_name"])

def save_batch(cursor,file_name,content,payload,issues,summary):
    status="validated" if not any(i["severity"]=="error" for i in issues) else "validation_failed"
    cursor.execute("INSERT INTO tb_excel_import_batch(file_name,file_hash,status,sheet_count,row_count,error_count,warning_count,payload) VALUES(%s,%s,%s,%s,%s,%s,%s,%s::jsonb) RETURNING batch_id,created_at",(file_name,hashlib.sha256(content).hexdigest(),status,summary.get("sheet_count",0),summary.get("row_count",0),sum(i["severity"]=="error" for i in issues),sum(i["severity"]!="error" for i in issues),json.dumps(payload,ensure_ascii=False)))
    created_row = cursor.fetchone()
    batch_id, created = created_row["batch_id"], created_row["created_at"]
    for i in issues: cursor.execute("INSERT INTO tb_excel_import_issue(batch_id,severity,sheet_name,row_number,column_name,error_code,message,cell_value) VALUES(%s,%s,%s,%s,%s,%s,%s,%s)",(batch_id,i["severity"],i["sheet_name"],i["row_number"],i["column_name"],i["error_code"],i["message"],i["cell_value"]))
    return batch_id,status,created

def batch_data(cursor,batch_id,with_issues=True):
    cursor.execute("SELECT * FROM tb_excel_import_batch WHERE batch_id=%s",(batch_id,)); row=cursor.fetchone()
    if not row: raise HTTPException(404,"导入批次不存在")
    data=dict(row)
    if with_issues: cursor.execute("SELECT severity,sheet_name,row_number,column_name,error_code,message,cell_value FROM tb_excel_import_issue WHERE batch_id=%s ORDER BY issue_id",(batch_id,)); data["issues"]=[dict(x) for x in cursor.fetchall()]
    return data

@router.get("/scope")
def get_scope(): return {"status":"success","data":{"sheets":ORDER,"scopes":SCOPES}}

@router.get("/template")
def template(scope_key:Optional[str]=Query(None)):
    wb=build_template(scope_sheets(scope_key)); out=io.BytesIO(); wb.save(out); out.seek(0); name=quote(f"精度保持性系统_{scope_key or '整体'}_导入模板.xlsx")
    return StreamingResponse(out,media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",headers={"Content-Disposition":f"attachment; filename*=UTF-8''{name}"})

@router.post("/precheck")
async def precheck(file:UploadFile=File(...),scope_key:Optional[str]=Query(None)):
    content=await file.read()
    if not (file.filename or "").lower().endswith(".xlsx"): raise HTTPException(400,"只支持 .xlsx 文件")
    if len(content)>20*1024*1024: raise HTTPException(400,"文件不能超过 20 MB")
    payload,issues,summary=parse(content,scope_sheets(scope_key)); conn=get_db_connection(); cursor=conn.cursor(cursor_factory=RealDictCursor)
    try: ensure_tables(cursor); validate_relations(cursor,payload,issues); bid,status,created=save_batch(cursor,file.filename or "未命名.xlsx",content,payload,issues,summary); conn.commit(); data=batch_data(cursor,bid); return {"status":"success","message":"预检通过" if status=="validated" else "预检发现问题","data":data}
    finally: cursor.close(); conn.close()

def import_payload(cursor,p):
    imported=skipped=0
    for r in p["项目"]: cursor.execute("INSERT INTO tb_project(project_name,eval_mode) SELECT %s,%s WHERE NOT EXISTS(SELECT 1 FROM tb_project WHERE project_name=%s)",(r["project_name"],r["eval_mode"],r["project_name"])); imported+=cursor.rowcount; skipped+=not cursor.rowcount
    for r in p["精度指标"]: cursor.execute("INSERT INTO tb_metric_dict(category_level_1,category_level_2,metric_name,value_type,description) SELECT %s,%s,%s,%s,%s WHERE NOT EXISTS(SELECT 1 FROM tb_metric_dict WHERE metric_name=%s)",(r["category_level_1"],r["category_level_2"],r["metric_name"],r["value_type"],r.get("description"),r["metric_name"])); imported+=cursor.rowcount; skipped+=not cursor.rowcount
    projects,metrics,assets,products=context(cursor)
    for r in p["资产台账"]: cursor.execute("INSERT INTO tb_asset(project_id,asset_category,asset_code,asset_name,asset_type) SELECT %s,%s,%s,%s,%s WHERE NOT EXISTS(SELECT 1 FROM tb_asset WHERE asset_code=%s)",(projects.get(r.get("project_name")),r["asset_category"],r["asset_code"],r["asset_name"],r["asset_type"],r["asset_code"])); imported+=cursor.rowcount; skipped+=not cursor.rowcount
    for r in p["制品"]: cursor.execute("INSERT INTO tb_product_def(product_code,product_name,product_detail) SELECT %s,%s,%s WHERE NOT EXISTS(SELECT 1 FROM tb_product_def WHERE product_code=%s)",(r["product_code"],r["product_name"],r.get("product_detail"),r["product_code"])); imported+=cursor.rowcount; skipped+=not cursor.rowcount
    projects,metrics,assets,products=context(cursor)
    for r in p["资产指标绑定"]:
        aid=assets[r["asset_code"]]; mid=metrics[r["metric_name"]][0]; cursor.execute("INSERT INTO tb_asset_metric_bind(asset_id,metric_id,upper_limit,lower_limit,baseline_value,failure_value,ideal_value,extreme_value,criterion_weight,indicator_weight,cycle_hours,unit,is_active) SELECT %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,TRUE WHERE NOT EXISTS(SELECT 1 FROM tb_asset_metric_bind WHERE asset_id=%s AND metric_id=%s)",(aid,mid,r.get("upper_limit"),r.get("lower_limit"),r.get("baseline_value"),r.get("failure_value"),r.get("ideal_value"),r.get("extreme_value"),r.get("criterion_weight"),r.get("indicator_weight"),r.get("cycle_hours"),r.get("unit"),aid,mid)); imported+=cursor.rowcount; skipped+=not cursor.rowcount
    for r in p["资产制品绑定"]:
        aid=assets[r["asset_code"]]; pid=products[r["product_code"]]; cursor.execute("INSERT INTO tb_asset_product_bind(asset_id,product_id,total_quantity,process_note) SELECT %s,%s,%s,%s WHERE NOT EXISTS(SELECT 1 FROM tb_asset_product_bind WHERE asset_id=%s AND product_id=%s)",(aid,pid,r.get("total_quantity"),r.get("process_note"),aid,pid)); imported+=cursor.rowcount; skipped+=not cursor.rowcount
    for r in p["制品指标配置"]:
        pid=products[r["product_code"]]; mid=metrics[r["metric_name"]][0]; cursor.execute("INSERT INTO tb_product_metric_config(product_id,metric_id,upper_limit,lower_limit,ideal_value,extreme_value,unit,position_note) SELECT %s,%s,%s,%s,%s,%s,%s,%s WHERE NOT EXISTS(SELECT 1 FROM tb_product_metric_config WHERE product_id=%s AND metric_id=%s)",(pid,mid,r.get("upper_limit"),r.get("lower_limit"),r.get("ideal_value"),r.get("extreme_value"),r.get("unit"),r.get("position_note"),pid,mid)); imported+=cursor.rowcount; skipped+=not cursor.rowcount
    grouped={}
    for r in p["精度测量记录"]: grouped.setdefault((r["asset_code"],r["record_time"],r["metric_name"]),[]).append(r["value"])
    for (ac,rt,mn),vals in grouped.items():
        aid=assets[ac]; mid,value_type,_=metrics[mn]; raw={"val":vals[0]} if value_type=="单值" and len(vals)==1 else {"samples":vals}; cursor.execute("SELECT 1 FROM tb_test_record WHERE asset_id=%s AND metric_id=%s AND created_at=%s",(aid,mid,rt))
        if cursor.fetchone(): skipped+=1
        else: cursor.execute("INSERT INTO tb_test_record(asset_id,metric_id,test_time,raw_data,created_at) VALUES(%s,%s,%s,%s::jsonb,%s)",(aid,mid,rt,json.dumps(raw),rt)); imported+=1
    prod_groups={}
    for r in p["制品测量记录"]: prod_groups.setdefault((r["asset_code"],r["product_code"],r["batch_no"],r["record_time"],r.get("production_quantity"),r.get("remark")),[]).append(r)
    for (ac,pc,bn,rt,qty,remark),rows in prod_groups.items():
        aid=assets[ac];pid=products[pc];cursor.execute("SELECT product_record_id FROM tb_product_record WHERE asset_id=%s AND product_id=%s AND batch_no=%s AND created_at=%s",(aid,pid,bn,rt)); existing=cursor.fetchone()
        if existing: skipped+=len(rows);continue
        cursor.execute("INSERT INTO tb_product_record(asset_id,product_id,batch_no,sample_count,production_quantity,created_at,remark) VALUES(%s,%s,%s,%s,%s,%s,%s) RETURNING product_record_id",(aid,pid,bn,len({r["sample_no"] for r in rows}),qty,rt,remark));rid=cursor.fetchone()["product_record_id"];imported+=1
        for r in rows: cursor.execute("INSERT INTO tb_product_sample_value(record_id,metric_id,sample_no,value) VALUES(%s,%s,%s,%s)",(rid,metrics[r["metric_name"]][0],r["sample_no"],r["value"]));imported+=1
    return int(imported),int(skipped)

@router.post("/{batch_id}/commit")
def commit(batch_id:int):
    conn=get_db_connection();cursor=conn.cursor(cursor_factory=RealDictCursor)
    try:
        ensure_tables(cursor);data=batch_data(cursor,batch_id,False)
        if data["status"]!="validated" or data["error_count"]: raise HTTPException(400,"只有预检通过的批次可以导入")
        imported,skipped=import_payload(cursor,data["payload"]);cursor.execute("UPDATE tb_excel_import_batch SET status='imported',imported_count=%s,skipped_count=%s,imported_at=CURRENT_TIMESTAMP WHERE batch_id=%s",(imported,skipped,batch_id));conn.commit();return {"status":"success","message":"整批导入成功","data":{"batch_id":batch_id,"imported_count":imported,"skipped_count":skipped}}
    except HTTPException: conn.rollback();raise
    except Exception as exc: conn.rollback();raise HTTPException(500,f"导入失败，整批数据已回滚：{str(exc)[:160]}")
    finally: cursor.close();conn.close()

@router.get("/batches")
def batches(page:int=1,page_size:int=20):
    conn=get_db_connection();cursor=conn.cursor(cursor_factory=RealDictCursor)
    try: ensure_tables(cursor);cursor.execute("SELECT COUNT(*) AS total FROM tb_excel_import_batch");total=cursor.fetchone()["total"];cursor.execute("SELECT * FROM tb_excel_import_batch ORDER BY batch_id DESC OFFSET %s LIMIT %s",((max(page,1)-1)*page_size,min(page_size,100)));return {"status":"success","data":{"total":total,"items":[dict(x) for x in cursor.fetchall()]}}
    finally: conn.commit();cursor.close();conn.close()

@router.get("/batches/{batch_id}")
def batch(batch_id:int):
    conn=get_db_connection();cursor=conn.cursor(cursor_factory=RealDictCursor)
    try: ensure_tables(cursor);conn.commit();return {"status":"success","data":batch_data(cursor,batch_id)}
    finally: cursor.close();conn.close()

@router.get("/batches/{batch_id}/error-report")
def report(batch_id:int):
    conn=get_db_connection();cursor=conn.cursor(cursor_factory=RealDictCursor)
    try: data=batch_data(cursor,batch_id);wb=Workbook();ws=wb.active;ws.title="预检问题清单";ws.append(["级别","工作表","行号","列名","错误代码","问题说明","原始值"]);[ws.append([i["severity"],i["sheet_name"],i["row_number"],i["column_name"],i["error_code"],i["message"],i["cell_value"]]) for i in data["issues"]];out=io.BytesIO();wb.save(out);out.seek(0);return StreamingResponse(out,media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",headers={"Content-Disposition":f"attachment; filename*=UTF-8''{quote(f'精度导入批次{batch_id}_错误报告.xlsx')}"})
    finally: cursor.close();conn.close()
