"""库存预警服务"""
import json
from datetime import datetime
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession


async def check_low_stock(db: AsyncSession, tenant_id: int) -> list[dict]:
    """
    检查所有 is_active=1 且 stock_qty <= threshold 的 ProductVariant，
    发送预警（目前写日志），返回预警列表。
    """
    rows = await db.execute(
        text("""
            SELECT pv.id, pv.product_id, pv.sku, pv.stock_qty,
                   p.name AS product_name, p.low_stock_threshold
            FROM product_variants pv
            JOIN products p ON p.id = pv.product_id
            WHERE pv.tenant_id = :tenant_id
              AND pv.is_active = 1
              AND p.is_active = 1
              AND pv.stock_qty <= IFNULL(p.low_stock_threshold, 10)
            ORDER BY pv.stock_qty ASC
        """),
        {"tenant_id": tenant_id}
    )
    alerts = []
    for row in rows:
        alerts.append({
            "variant_id": row[0],
            "product_id": row[1],
            "sku": row[2],
            "stock_qty": row[3],
            "product_name": row[4],
            "threshold": row[5] or 10,
        })
        print(f"[ALERT] Low stock: {row[4]} (SKU:{row[2]}) stock={row[3]}")
    return alerts


async def upsert_alert(
    db: AsyncSession,
    tenant_id: int,
    product_id: int,
    variant_id: int | None,
    threshold: int,
    current_stock: int,
) -> None:
    """更新或创建库存预警记录"""
    await db.execute(
        text("""
            INSERT INTO inventory_alerts (tenant_id, product_id, variant_id, threshold, current_stock, alert_sent, last_checked)
            VALUES (:tenant_id, :product_id, :variant_id, :threshold, :current_stock, 0, NOW(3))
            ON DUPLICATE KEY UPDATE
                threshold = VALUES(threshold),
                current_stock = VALUES(current_stock),
                alert_sent = 0,
                last_checked = NOW(3)
        """),
        {
            "tenant_id": tenant_id,
            "product_id": product_id,
            "variant_id": variant_id,
            "threshold": threshold,
            "current_stock": current_stock,
        }
    )
    await db.commit()