"""库存预警管理 API（Admin）"""
from fastapi import APIRouter, Depends, HTTPException
from pydantic import BaseModel
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import text

from app.api.deps import get_db, get_admin_user
from app.core.models.user import User


class AlertSetBody(BaseModel):
    product_id: int
    threshold: int

router = APIRouter(prefix="/admin/inventory-alerts", tags=["库存预警"])


@router.get("")
async def list_inventory_alerts(
    db: AsyncSession = Depends(get_db),
    admin: User = Depends(get_admin_user),
):
    """列出当前租户的所有库存预警设置"""
    result = await db.execute(
        text("""
            SELECT ia.id, ia.tenant_id, ia.product_id, ia.variant_id,
                   ia.threshold, ia.current_stock, ia.alert_sent,
                   ia.last_checked, ia.created_at, ia.updated_at,
                   p.name AS product_name, pv.sku
            FROM inventory_alerts ia
            JOIN products p ON p.id = ia.product_id
            LEFT JOIN product_variants pv ON pv.id = ia.variant_id
            WHERE ia.tenant_id = :tenant_id
            ORDER BY ia.current_stock ASC, ia.id DESC
        """),
        {"tenant_id": admin.tenant_id}
    )
    rows = result.fetchall()
    return [
        {
            "id": r[0],
            "tenant_id": r[1],
            "product_id": r[2],
            "variant_id": r[3],
            "threshold": r[4],
            "current_stock": r[5],
            "alert_sent": bool(r[6]),
            "last_checked": r[7],
            "created_at": r[8],
            "updated_at": r[9],
            "product_name": r[10],
            "sku": r[11],
        }
        for r in rows
    ]


@router.post("")
async def set_inventory_alert(
    body: AlertSetBody,
    db: AsyncSession = Depends(get_db),
    admin: User = Depends(get_admin_user),
):
    """为任意商品设置（或更新）预警阈值"""
    row = (await db.execute(
        text("SELECT stock_qty FROM products WHERE id = :id AND tenant_id = :tid"),
        {"id": body.product_id, "tid": admin.tenant_id},
    )).fetchone()
    if not row:
        raise HTTPException(status_code=404, detail="商品不存在")

    current_stock = row[0] or 0
    await db.execute(
        text("UPDATE products SET low_stock_threshold = :t WHERE id = :id AND tenant_id = :tid"),
        {"t": body.threshold, "id": body.product_id, "tid": admin.tenant_id},
    )
    await db.execute(
        text("""
            INSERT INTO inventory_alerts
                (tenant_id, product_id, variant_id, threshold, current_stock, alert_sent, last_checked)
            VALUES (:tid, :pid, NULL, :t, :cs, 0, NOW(3))
            ON DUPLICATE KEY UPDATE
                threshold = VALUES(threshold),
                current_stock = VALUES(current_stock),
                alert_sent = 0,
                last_checked = NOW(3)
        """),
        {"tid": admin.tenant_id, "pid": body.product_id, "t": body.threshold, "cs": current_stock},
    )
    await db.commit()
    return {"ok": True, "product_id": body.product_id, "threshold": body.threshold, "current_stock": current_stock}


@router.put("/{alert_id}")
async def update_inventory_alert(
    alert_id: int,
    threshold: int = 10,
    db: AsyncSession = Depends(get_db),
    admin: User = Depends(get_admin_user),
):
    """更新预警阈值"""
    await db.execute(
        text("UPDATE inventory_alerts SET threshold = :threshold WHERE id = :id AND tenant_id = :tid"),
        {"threshold": threshold, "id": alert_id, "tid": admin.tenant_id},
    )
    await db.commit()
    return {"ok": True}


@router.delete("/{alert_id}")
async def delete_inventory_alert(
    alert_id: int,
    db: AsyncSession = Depends(get_db),
    admin: User = Depends(get_admin_user),
):
    """取消商品库存预警"""
    row = (await db.execute(
        text("SELECT product_id FROM inventory_alerts WHERE id = :id AND tenant_id = :tid"),
        {"id": alert_id, "tid": admin.tenant_id},
    )).fetchone()
    if row:
        await db.execute(
            text("UPDATE products SET low_stock_threshold = NULL WHERE id = :id AND tenant_id = :tid"),
            {"id": row[0], "tid": admin.tenant_id},
        )
        await db.execute(
            text("DELETE FROM inventory_alerts WHERE id = :id AND tenant_id = :tid"),
            {"id": alert_id, "tid": admin.tenant_id},
        )
        await db.commit()
    return {"ok": True}