recycle-docker/backend/routers/estimate.py

from fastapi import APIRouter, Request, Depends, Form
from fastapi.responses import HTMLResponse
from sqlalchemy.orm import Session
from sqlalchemy import text
from database import SessionLocal, redis_client
from services.template_service import build_template
from services.price_service import apply_adjust
from fastapi.templating import Jinja2Templates
import json
import uuid

router = APIRouter(prefix="/estimate", tags=["estimate"])
templates = Jinja2Templates(directory="templates")

# ================= DB =================
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# ================= 获取所有机型 =================
@router.get("/simulate", response_class=HTMLResponse)
def simulate(db: Session = Depends(get_db)):
    # 获取所有有模板的机型
    machines = db.execute(text("""
      SELECT machine_id, name
      FROM t_machine
      WHERE machine_id IN (SELECT DISTINCT machine_id FROM machine_temp)
    """)).fetchall()

    html = "<h2>估价模拟</h2>"
    html += '<form method="get" action="/estimate/simulate_one">'

    # 展示机型选择
    html += '<label>选择机型:</label><br>'
    html += '<select name="machine_id">'
    for machine in machines:
        html += f'<option value="{machine.machine_id}">{machine.name}</option>'
    html += '</select><br>'

    html += '<button type="submit">选择机型</button></form>'

    return html


# ================= 获取机型模板和选项 =================
@router.get("/simulate_one_del", response_class=HTMLResponse)
def simulate(
    machine_id: int, 
    db: Session = Depends(get_db)
):
    # 获取机型对应的模板
    row = db.execute(text("""
      SELECT estimate_packet
      FROM machine_temp
      WHERE machine_id=:mid
    """), {"mid": machine_id}).fetchone()

    tpl = json.loads(row.estimate_packet)

    html = f"<h2>估价模拟 - {tpl['templateId']}</h2>"

    html += '<form method="post" action="/estimate/simulate">'

    for step in tpl["template"]:
        html += f"<h3>{step['stepName']}</h3>"

        for p in step["properties"]:
            html += f"<b>{p['name']}</b><br>"

            for v in p["values"]:
                html += f"""
                <label>
                  <input type="checkbox" name="option_ids" value="{v['valueId']}">
                  {v['valueText']}
                </label><br>
                """

    html += f"""
      <input type="hidden" name="machine_id" value="{machine_id}">
      <br>
      <button type="submit">开始估价</button>
    </form>
    """

    return html
@router.get("/simulate_one", response_class=HTMLResponse)
def simulate_one_del2(
    request: Request,
    machine_id: int,
    db: Session = Depends(get_db)
):

    row = db.execute(text("""
        SELECT m.name AS machine_name,
               t.estimate_packet
        FROM machine_temp t
        JOIN t_machine m ON t.machine_id = m.machine_id
        WHERE t.machine_id = :mid
    """), {"mid": machine_id}).fetchone()

    if not row:
        return HTMLResponse("未找到机型模板", status_code=404)
    
    # print("*************row****************")
    # print(row)
    tpl = json.loads(row.estimate_packet)

    return templates.TemplateResponse(
        "simulate_one.html",
        {
            "request": request,
            "machine_id": machine_id,
            "machine_name": row.machine_name,
            "tpl": tpl
        }
    )



# ================= 提交估价并计算价格 =================
@router.post("/simulate_del", response_class=HTMLResponse)
def simulate_calc(
    machine_id: int = Form(...),
    option_ids: list[str] = Form([]),
    db: Session = Depends(get_db)
):
    # 获取基准价格
    base_price_row = db.execute(text("""
      SELECT base_price 
      FROM machine_base_price 
      WHERE machine_id=:mid
    """), {"mid": machine_id}).fetchone()

    # base_price = base_price_row.base_price
    base_price = base_price_row.base_price if base_price_row else 1000.0
    # 获取选项因素
    factors = db.execute(text("""
      SELECT option_id, factor, absolute_deduct 
      FROM price_option_factor 
      WHERE option_id IN :ids
    """), {"ids": tuple(option_ids)}).fetchall()

    # 计算价格
    price = base_price

    for f in factors:
        if f.factor:
            price *= (1-float(f.factor))
        if f.absolute_deduct:
            price -= float(f.absolute_deduct)

    # 逐步应用调节因子

    # 1. 获取全局调节因子
    global_adjust = db.execute(text("""
      SELECT factor FROM price_adjust_factor
      WHERE level='global'
    """)).fetchone()

    if global_adjust:
        price *= float(global_adjust.factor)

    # 2. 获取品牌调节因子
    brand_adjust = db.execute(text("""
      SELECT factor FROM price_adjust_factor
      WHERE level='brand'
        AND ref_id=(
          SELECT brand_id FROM t_machine WHERE machine_id=:mid
        )
    """), {"mid": machine_id}).fetchone()

    if brand_adjust:
        price *= float(brand_adjust.factor)

    # 3. 获取机型调节因子
    machine_adjust = db.execute(text("""
      SELECT factor FROM price_adjust_factor
      WHERE level='machine'
        AND ref_id=:mid
    """), {"mid": machine_id}).fetchone()

    if machine_adjust:
        price *= float(machine_adjust.factor)

    html = f"""
    <h2>估价结果</h2>
    选择项：{",".join(option_ids)}<br>
    估价：{round(price, 2)} 元<br>
    <a href="/simulate?machine_id={machine_id}">返回</a>
    """

    return html
@router.post("/simulate_de3", response_class=HTMLResponse)
def simulate_calc(
    request: Request,
    machine_id: int = Form(...),
    option_ids: list[str] = Form([]),
    db: Session = Depends(get_db)
):

    # 机型名称
    machine = db.execute(text("""
        SELECT name, brand_name
        FROM t_machine
        WHERE machine_id=:mid
    """), {"mid": machine_id}).fetchone()

    # 模板
    row = db.execute(text("""
        SELECT estimate_packet
        FROM machine_temp
        WHERE machine_id=:mid
    """), {"mid": machine_id}).fetchone()

    tpl = json.loads(row.estimate_packet)

    # ========== 基准价 ==========
    base_price_row = db.execute(text("""
      SELECT base_price 
      FROM machine_base_price 
      WHERE machine_id=:mid
      LIMIT 1
    """), {"mid": machine_id}).fetchone()

    base_price = float(base_price_row.base_price) if base_price_row else 1000.0

    price = base_price

    detail_rows = []

    # ========== 选项扣减 ==========
    if option_ids:
        factors = db.execute(text("""
          SELECT 
            pf.option_id,
            pf.factor,
            pf.absolute_deduct,
            ro.option_name
          FROM price_option_factor pf
          LEFT JOIN release_option ro
            ON pf.option_id = ro.option_id
          WHERE pf.option_id IN :ids
        """), {"ids": tuple(option_ids)}).fetchall()
    else:
        factors = []

    for f in factors:
        before = price

        if f.factor is not None:
            price *= float(f.factor)

        if f.absolute_deduct is not None:
            price -= float(f.absolute_deduct)

        detail_rows.append({
            "type": "option",
            "name": f.option_name or str(f.option_id),
            "option_id": f.option_id,
            "factor": f.factor,
            "absolute_deduct": f.absolute_deduct,
            "before": round(before, 2),
            "after": round(price, 2)
        })

    # ========== 全局调节 ==========
    global_adjust = db.execute(text("""
        SELECT factor
        FROM price_adjust_factor
        WHERE level='global'
        LIMIT 1
    """)).fetchone()

    if global_adjust:
        before = price
        price *= float(global_adjust.factor)

        detail_rows.append({
            "type": "adjust",
            "name": "全局调节",
            "factor": global_adjust.factor,
            "before": round(before, 2),
            "after": round(price, 2)
        })

    # ========== 品牌调节 ==========
    brand_adjust = db.execute(text("""
        SELECT factor
        FROM price_adjust_factor
        WHERE level='brand'
          AND ref_id=(
            SELECT brand_id FROM t_machine WHERE machine_id=:mid
          )
        LIMIT 1
    """), {"mid": machine_id}).fetchone()

    if brand_adjust:
        before = price
        price *= float(brand_adjust.factor)

        detail_rows.append({
            "type": "adjust",
            "name": "品牌调节",
            "factor": brand_adjust.factor,
            "before": round(before, 2),
            "after": round(price, 2)
        })

    # ========== 机型调节 ==========
    machine_adjust = db.execute(text("""
        SELECT factor
        FROM price_adjust_factor
        WHERE level='machine'
          AND ref_id=:mid
        LIMIT 1
    """), {"mid": machine_id}).fetchone()

    if machine_adjust:
        before = price
        price *= float(machine_adjust.factor)

        detail_rows.append({
            "type": "adjust",
            "name": "机型调节",
            "factor": machine_adjust.factor,
            "before": round(before, 2),
            "after": round(price, 2)
        })

    final_price = round(price, 2)

    return templates.TemplateResponse(
        "simulate_one.html",
        {
            "request": request,
            "tpl": tpl,
            "machine": machine,
            "machine_id": machine_id,
            "selected_option_ids": option_ids,
            "base_price": round(base_price, 2),
            "detail_rows": detail_rows,
            "final_price": final_price
        }
    )

@router.get("/simulate_one", response_class=HTMLResponse)
def simulate_one(
    request: Request,
    machine_id: int,
    db: Session = Depends(get_db)
):
    row = db.execute(text("""
        SELECT m.name,
               t.estimate_packet
        FROM machine_temp t
        JOIN t_machine m ON t.machine_id = m.machine_id
        WHERE t.machine_id = :mid
    """), {"mid": machine_id}).fetchone()

    tpl = json.loads(row.estimate_packet)

    return templates.TemplateResponse(
        "simulate_one.html",
        {
            "request": request,
            "machine_id": machine_id,
            "machine_name": row.name,
            "tpl": tpl,
            "result": None
        }
    )


# ================= 估价 =================
@router.post("/simulate666", response_class=HTMLResponse)
async def simulate_calc(
    request: Request,
    machine_id: int = Form(...),
    db: Session = Depends(get_db)
):
    form = await request.form()

    # ---------------- 收集 option ----------------
    option_ids = []
    for k, v in form.multi_items():
        if k.startswith("option_"):
            option_ids.append(str(v))

    # ---------------- 重新取模板与机型名 ----------------
    row = db.execute(text("""
        SELECT m.name,
               t.estimate_packet
        FROM machine_temp t
        JOIN t_machine m ON t.machine_id = m.machine_id
        WHERE t.machine_id = :mid
    """), {"mid": machine_id}).fetchone()

    if not row:
        return HTMLResponse("机型模板不存在")

    tpl = json.loads(row.estimate_packet)

    # ---------------- 构造 valueId -> valueText 映射 ----------------
    value_name_map = {}

    for step in tpl["template"]:
        for p in step["properties"]:
            for v in p["values"]:
                value_name_map[str(v["valueId"])] = v["valueText"]

    # ---------------- 基准价 ----------------
    base_row = db.execute(text("""
        SELECT base_price
        FROM machine_base_price
        WHERE machine_id=:mid
        LIMIT 1
    """), {"mid": machine_id}).fetchone()

    base_price = float(base_row.base_price) if base_row else 1000.0

    # ---------------- 选项因子 ----------------
    detail_rows = []

    if option_ids:
        rows = db.execute(text("""
            SELECT option_id, factor, absolute_deduct
            FROM price_option_factor
            WHERE option_id IN :ids
        """), {"ids": tuple(option_ids)}).fetchall()
    else:
        rows = []

    price = base_price

    for r in rows:
        before = price

        if r.factor is not None:
            price = price * float(r.factor)

        if r.absolute_deduct is not None:
            price = price - float(r.absolute_deduct)

        detail_rows.append({
            "option_id": r.option_id,
            "option_name": value_name_map.get(str(r.option_id), str(r.option_id)),
            "factor": r.factor,
            "absolute": r.absolute_deduct,
            "before": round(before, 2),
            "after": round(price, 2)
        })

    # ---------------- 调节因子 ----------------

    # 全局
    g = db.execute(text("""
        SELECT factor FROM price_adjust_factor
        WHERE level='global'
        LIMIT 1
    """)).fetchone()

    if g:
        before = price
        price = price * float(g.factor)

        detail_rows.append({
            "option_id": "GLOBAL",
            "option_name": "全局调节",
            "factor": g.factor,
            "absolute": None,
            "before": round(before, 2),
            "after": round(price, 2)
        })

    # 品牌
    b = db.execute(text("""
        SELECT factor FROM price_adjust_factor
        WHERE level='brand'
          AND ref_id=(
            SELECT brand_id
            FROM t_machine
            WHERE machine_id=:mid
          )
        LIMIT 1
    """), {"mid": machine_id}).fetchone()

    if b:
        before = price
        price = price * float(b.factor)

        detail_rows.append({
            "option_id": "BRAND",
            "option_name": "品牌调节",
            "factor": b.factor,
            "absolute": None,
            "before": round(before, 2),
            "after": round(price, 2)
        })

    # 机型
    m = db.execute(text("""
        SELECT factor FROM price_adjust_factor
        WHERE level='machine'
          AND ref_id=:mid
        LIMIT 1
    """), {"mid": machine_id}).fetchone()

    if m:
        before = price
        price = price * float(m.factor)

        detail_rows.append({
            "option_id": "MACHINE",
            "option_name": "机型调节",
            "factor": m.factor,
            "absolute": None,
            "before": round(before, 2),
            "after": round(price, 2)
        })

    return templates.TemplateResponse(
        "simulate_one.html",
        {
            "request": request,
            "machine_id": machine_id,
            "machine_name": row.name,
            "tpl": tpl,
            "result": {
                "base_price": round(base_price, 2),
                "final_price": round(price, 2),
                "details": detail_rows,
                "selected": option_ids
            }
        }
    )

@router.post("/simulate", response_class=HTMLResponse)
async def simulate_calc(
    request: Request,
    machine_id: int = Form(...),
    db: Session = Depends(get_db)
):
    form = await request.form()

    # 收集所有 option
    option_ids = []
    for k, v in form.multi_items():
        if k.startswith("option_"):
            option_ids.append(str(v))

    # ---------------- 重新取模板与机型名 ----------------
    row = db.execute(text("""
        SELECT m.name,
               t.estimate_packet
        FROM machine_temp t
        JOIN t_machine m ON t.machine_id = m.machine_id
        WHERE t.machine_id = :mid
    """), {"mid": machine_id}).fetchone()

    if not row:
        return HTMLResponse("机型模板不存在")
    tpl = json.loads(row.estimate_packet)
    # ---------------- 构造 valueId -> valueText 映射 ----------------
    value_name_map = {}

    for step in tpl["template"]:
        for p in step["properties"]:
            for v in p["values"]:
                value_name_map[str(v["valueId"])] = v["valueText"]

    # ---------------- 基准价 ----------------
    base_row = db.execute(text("""
        SELECT base_price
        FROM machine_base_price
        WHERE machine_id=:mid
        LIMIT 1
    """), {"mid": machine_id}).fetchone()

    base_price_input = form.get("base_price")

    if base_price_input and str(base_price_input).strip():
        base_price = float(base_price_input)
    else:
        base_price = float(base_row.base_price) if base_row else 1

    # ---------------- 选项因子 ----------------
    detail_rows = []

    if option_ids:
        rows = db.execute(text("""
            SELECT option_id, factor, absolute_deduct
            FROM price_option_factor
            WHERE option_id IN :ids
        """), {"ids": tuple(option_ids)}).fetchall()
    else:
        rows = []

    price = base_price

    total_deduct_rate = 0
    detail_rows.append({
            "option_id": "GLOBAL",
            "option_name": "--   以下是扣减比例,累加",
            "factor": 0,
            "absolute": None,
            "before": None,
            "after": None
    })
    for r in rows:
        before = price

        if r.factor:
            total_deduct_rate += float(r.factor)
            # price = price * (1-float(r.factor))

        # if r.absolute_deduct:
        #     price = price - float(r.absolute_deduct)

        detail_rows.append({
            "option_id": r.option_id,
            "option_name": value_name_map.get(str(r.option_id), str(r.option_id)),
            "factor": r.factor,
            "absolute": r.absolute_deduct,
            "before": round(before, 2),
            "after": round(price, 2)
        })
    if total_deduct_rate:
        price = price * (1 - total_deduct_rate)
    price = max(price, 0.0)
    # ---------------- 调节因子 ----------------

    detail_rows.append({
            "option_id": "GLOBAL",
            "option_name": "--   以下是调节因子,累乘",
            "factor": 0,
            "absolute": None,
            "before": None,
            "after": None
    })

    # 全局
    g = db.execute(text("""
        SELECT factor FROM price_adjust_factor
        WHERE level='global'
        LIMIT 1
    """)).fetchone()

    if g:
        before = price
        price = price * float(g.factor)
        detail_rows.append({
            "option_id": "GLOBAL",
            "option_name": "全局调节系数",
            "factor": g.factor,
            "absolute": None,
            "before": round(before, 2),
            "after": round(price, 2)
        })

    # 品牌
    b = db.execute(text("""
        SELECT factor FROM price_adjust_factor
        WHERE level='brand'
          AND ref_id=(
            SELECT brand_id
            FROM t_machine
            WHERE machine_id=:mid
          )
        LIMIT 1
    """), {"mid": machine_id}).fetchone()

    if b:
        before = price
        price = price * float(b.factor)
        detail_rows.append({
            "option_id": "BRAND",
            "option_name": "品牌调节系数",
            "factor": b.factor,
            "absolute": None,
            "before": round(before, 2),
            "after": round(price, 2)
        })

    # 机型
    m = db.execute(text("""
        SELECT factor FROM price_adjust_factor
        WHERE level='machine'
          AND ref_id=:mid
        LIMIT 1
    """), {"mid": machine_id}).fetchone()

    if m:
        before = price
        price = price * float(m.factor)
        detail_rows.append({
            "option_id": "MACHINE",
            "option_name": "机型调节系数",
            "factor": m.factor,
            "absolute": None,
            "before": round(before, 2),
            "after": round(price, 2)
        })

    # 重新取模板与机型名
    row = db.execute(text("""
        SELECT m.name,
               t.estimate_packet
        FROM machine_temp t
        JOIN t_machine m ON t.machine_id = m.machine_id
        WHERE t.machine_id = :mid
    """), {"mid": machine_id}).fetchone()

    tpl = json.loads(row.estimate_packet)

    return templates.TemplateResponse(
        "simulate_one.html",
        {
            "request": request,
            "machine_id": machine_id,
            "machine_name": row.name,
            "tpl": tpl,
            "result": {
                "base_price": round(base_price, 2),
                "final_price": round(price, 2),
                "details": detail_rows,
                "selected": option_ids
            }
        }
    )


@router.post("/estimate2", response_class=HTMLResponse)
def estimate_submit(
    request: Request,
    machine_id: int = Form(...),
    capacity: str = Form(...),
    option_ids: list[int] = Form([]),
    db: Session = Depends(get_db)
):
    # ---------- 基础价格 ----------
    base_price = db.execute(
        text("""
            SELECT base_price
            FROM machine_base_price
            WHERE machine_id=:mid AND capacity=:cap
        """),
        {"mid": machine_id, "cap": capacity}
    ).scalar() or 0

    price = float(base_price)

    # ---------- 扣减项 ----------
    for oid in option_ids:
        r = db.execute(
            text("""
                SELECT factor, absolute_deduct
                FROM price_option_factor
                WHERE option_id=:oid
            """),
            {"oid": oid}
        ).fetchone()

        if r:
            price = price * float(r.factor) - float(r.absolute_deduct)

    # ---------- 调节系数 ----------
    brand_id = db.execute(
        text("""
            SELECT brand_id
            FROM t_machine
            WHERE machine_id=:mid
        """),
        {"mid": machine_id}
    ).scalar()

    price = apply_adjust(db, machine_id, brand_id, price)
    price = round(max(price, 0), 2)

    # ---------- 估价版本 ----------
    version_no = str(uuid.uuid4())[:8]

    db.execute(
        text("""
            INSERT INTO estimate_record
            (machine_id, capacity, option_ids, final_price, version_no)
            VALUES (:m, :c, :o, :p, :v)
        """),
        {
            "m": machine_id,
            "c": capacity,
            "o": ",".join(map(str, option_ids)),
            "p": price,
            "v": version_no
        }
    )
    db.commit()

    # ---------- 模板（Redis 缓存） ----------
    cache_key = f"template:{machine_id}"
    cached = redis_client.get(cache_key) if redis_client else None

    if cached:
        template = json.loads(cached)
    else:
        rows = db.execute(text("SELECT * FROM release_option")).fetchall()
        template = build_template(rows)
        if redis_client:
            redis_client.set(cache_key, json.dumps(template), ex=3600)

    # ---------- 重新加载页面 ----------
    machines = db.execute(
        text("""
            SELECT machine_id, name
            FROM t_machine
            ORDER BY brand_name, name
        """)
    ).fetchall()

    capacities = db.execute(
        text("""
            SELECT capacity
            FROM machine_base_price
            WHERE machine_id=:mid
        """),
        {"mid": machine_id}
    ).fetchall()

    return request.app.state.templates.TemplateResponse(
        "estimate.html",
        {
            "request": request,
            "machines": machines,
            "capacities": capacities,
            "template": template,
            "price": price,
            "version": version_no,
            "selected_machine": machine_id,
            "selected_capacity": capacity
        }
    )