from typing import Optional

from fastapi import APIRouter, Query
from psycopg2.extras import RealDictCursor

from database import get_db_connection
from schemas import ProjectCreate, AssetCreate, BindMetrics

router = APIRouter()


def ensure_default_project(cursor):
    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
        )
        """,
        ('默认设备池', '整机跟踪,部件试验', '默认设备池')
    )
    cursor.execute(
        "SELECT project_id FROM tb_project WHERE project_name = %s ORDER BY project_id ASC LIMIT 1",
        ('默认设备池',)
    )
    row = cursor.fetchone()
    return row[0] if row else None


@router.get("/api/projects")
def get_projects():
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    try:
        ensure_default_project(cursor)
        conn.commit()
        cursor.execute(
            "SELECT project_id, project_name, eval_mode, created_at FROM tb_project ORDER BY created_at DESC, project_id DESC"
        )
        projects = cursor.fetchall()
        return {"status": "success", "data": projects}
    finally:
        cursor.close()
        conn.close()


@router.post("/api/projects")
def create_project(item: ProjectCreate):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            "INSERT INTO tb_project (project_name, eval_mode) VALUES (%s, %s)",
            (item.project_name, item.eval_mode),
        )
        conn.commit()
        return {"status": "success", "message": "项目创建成功！"}
    finally:
        cursor.close()
        conn.close()


@router.put("/api/projects/{project_id}")
def update_project(project_id: int, item: ProjectCreate):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            "UPDATE tb_project SET project_name = %s, eval_mode = %s WHERE project_id = %s",
            (item.project_name, item.eval_mode, project_id),
        )
        conn.commit()
        return {"status": "success", "message": "项目修改成功！"}
    finally:
        cursor.close()
        conn.close()


@router.delete("/api/projects/{project_id}")
def delete_project(project_id: int):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        default_project_id = ensure_default_project(cursor)
        if project_id == default_project_id:
            return {"status": "error", "message": "默认设备池不能删除"}

        cursor.execute(
            "UPDATE tb_asset SET project_id = %s WHERE project_id = %s",
            (default_project_id, project_id),
        )
        cursor.execute("DELETE FROM tb_project WHERE project_id = %s", (project_id,))
        conn.commit()
        return {"status": "success", "message": "项目删除成功，相关设备已归档到默认设备池。"}
    except Exception as e:
        conn.rollback()
        return {"status": "error", "message": str(e)}
    finally:
        cursor.close()
        conn.close()


@router.get("/api/projects/{project_id}/assets")
def get_assets_by_project(project_id: int):
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    try:
        cursor.execute(
            """
            SELECT a.*, p.project_name
            FROM tb_asset a
            LEFT JOIN tb_project p ON a.project_id = p.project_id
            WHERE a.project_id = %s
            ORDER BY a.asset_category, a.asset_code, a.asset_id
            """,
            (project_id,),
        )
        assets = cursor.fetchall()
        return {"status": "success", "data": assets}
    finally:
        cursor.close()
        conn.close()


@router.get("/api/assets")
def get_assets(asset_category: Optional[str] = Query(None)):
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    try:
        base_sql = """
            SELECT a.*, p.project_name
            FROM tb_asset a
            LEFT JOIN tb_project p ON a.project_id = p.project_id
        """
        params = []
        if asset_category:
            base_sql += " WHERE a.asset_category = %s"
            params.append(asset_category)
        base_sql += " ORDER BY a.asset_category, a.asset_code, a.asset_id"
        cursor.execute(base_sql, tuple(params))
        return {"status": "success", "data": cursor.fetchall()}
    finally:
        cursor.close()
        conn.close()


@router.get("/api/assets/{asset_id}")
def get_asset_detail(asset_id: int):
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    try:
        cursor.execute(
            """
            SELECT a.*, p.project_name
            FROM tb_asset a
            LEFT JOIN tb_project p ON a.project_id = p.project_id
            WHERE a.asset_id = %s
            """,
            (asset_id,),
        )
        asset = cursor.fetchone()
        if not asset:
            return {"status": "error", "message": "设备不存在"}

        cursor.execute(
            """
            SELECT COUNT(*) AS metric_count
            FROM tb_asset_metric_bind
            WHERE asset_id = %s
            """,
            (asset_id,),
        )
        metric_count = cursor.fetchone()["metric_count"]

        cursor.execute(
            """
            SELECT COUNT(*) AS record_count, MAX(created_at) AS latest_record_at
            FROM tb_test_record
            WHERE asset_id = %s
            """,
            (asset_id,),
        )
        record_stat = cursor.fetchone()

        cursor.execute(
            """
            SELECT COUNT(*) AS product_count
            FROM tb_asset_product_bind
            WHERE asset_id = %s
            """,
            (asset_id,),
        )
        product_count = cursor.fetchone()["product_count"]

        cursor.execute(
            """
            SELECT MAX(created_at) AS latest_product_record_at
            FROM tb_product_record
            WHERE asset_id = %s
            """,
            (asset_id,),
        )
        product_record_stat = cursor.fetchone()

        cursor.execute(
            """
            SELECT r.record_id, r.created_at, m.metric_name, m.value_type, r.raw_data
            FROM tb_test_record r
            JOIN tb_metric_dict m ON r.metric_id = m.metric_id
            WHERE r.asset_id = %s
            ORDER BY r.created_at DESC
            LIMIT 12
            """,
            (asset_id,),
        )
        recent_records = cursor.fetchall()

        cursor.execute(
            """
            SELECT
                m.metric_id,
                m.metric_name,
                m.category_level_1,
                m.category_level_2,
                m.value_type,
                b.upper_limit,
                b.lower_limit,
                b.baseline_value,
                b.failure_value,
                b.ideal_value,
                b.extreme_value,
                b.criterion_weight,
                b.indicator_weight,
                b.cycle_hours,
                b.unit
            FROM tb_asset_metric_bind b
            JOIN tb_metric_dict m ON b.metric_id = m.metric_id
            WHERE b.asset_id = %s
            ORDER BY m.category_level_1, m.category_level_2, m.metric_name
            """,
            (asset_id,),
        )
        metrics = cursor.fetchall()

        cursor.execute(
            """
            SELECT
                apb.product_id,
                apb.total_quantity,
                apb.process_note,
                p.product_code,
                p.product_name,
                p.product_detail
            FROM tb_asset_product_bind apb
            JOIN tb_product_def p ON apb.product_id = p.product_id
            WHERE apb.asset_id = %s
            ORDER BY p.product_code, p.product_id
            """,
            (asset_id,),
        )
        products = cursor.fetchall()

        return {
            "status": "success",
            "data": {
                **asset,
                "metric_count": metric_count,
                "record_count": record_stat["record_count"],
                "latest_record_at": record_stat["latest_record_at"],
                "product_count": product_count,
                "latest_product_record_at": product_record_stat["latest_product_record_at"],
                "recent_records": recent_records,
                "metrics": metrics,
                "products": products,
            },
        }
    finally:
        cursor.close()
        conn.close()


@router.post("/api/assets")
def create_asset(item: AssetCreate):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        default_project_id = ensure_default_project(cursor)
        project_id = item.project_id or default_project_id
        cursor.execute(
            """
            INSERT INTO tb_asset (project_id, asset_category, asset_name, asset_type, asset_code)
            VALUES (%s, %s, %s, %s, %s)
            """,
            (project_id, item.asset_category, item.asset_name, item.asset_type, item.asset_code),
        )
        conn.commit()
        return {"status": "success", "message": f"{item.asset_category} 添加成功！"}
    except Exception as e:
        conn.rollback()
        return {"status": "error", "message": str(e)}
    finally:
        cursor.close()
        conn.close()


@router.put("/api/assets/{asset_id}")
def update_asset(asset_id: int, item: AssetCreate):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            """
            UPDATE tb_asset
            SET asset_category = %s,
                asset_name = %s,
                asset_type = %s,
                asset_code = %s
            WHERE asset_id = %s
            """,
            (item.asset_category, item.asset_name, item.asset_type, item.asset_code, asset_id),
        )
        conn.commit()
        return {"status": "success", "message": "设备信息更新成功！"}
    except Exception as e:
        conn.rollback()
        return {"status": "error", "message": str(e)}
    finally:
        cursor.close()
        conn.close()


@router.get("/api/assets/{asset_id}/metrics")
def get_bound_metrics(asset_id: int):
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    try:
        cursor.execute(
            """
            SELECT
                metric_id,
                upper_limit,
                lower_limit,
                baseline_value,
                failure_value,
                ideal_value,
                extreme_value,
                criterion_weight,
                indicator_weight,
                cycle_hours,
                unit
            FROM tb_asset_metric_bind
            WHERE asset_id = %s
            """,
            (asset_id,),
        )
        bindings = cursor.fetchall()
        return {"status": "success", "data": bindings}
    finally:
        cursor.close()
        conn.close()


@router.post("/api/assets/{asset_id}/metrics")
def bind_metrics_to_asset(asset_id: int, item: BindMetrics):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM tb_asset_metric_bind WHERE asset_id = %s", (asset_id,))
        for m in item.metrics:
            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
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """,
                (
                    asset_id,
                    m.metric_id,
                    m.upper_limit,
                    m.lower_limit,
                    m.baseline_value,
                    m.failure_value,
                    m.ideal_value,
                    m.extreme_value,
                    m.criterion_weight,
                    m.indicator_weight,
                    m.cycle_hours,
                    m.unit,
                ),
            )
        conn.commit()
        return {"status": "success", "message": "配置已保存！"}
    except Exception as e:
        conn.rollback()
        return {"status": "error", "message": str(e)}
    finally:
        cursor.close()
        conn.close()


@router.delete("/api/assets/{asset_id}")
def delete_asset(asset_id: int):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM tb_asset_metric_bind WHERE asset_id = %s", (asset_id,))
        cursor.execute("DELETE FROM tb_test_record WHERE asset_id = %s", (asset_id,))
        cursor.execute("DELETE FROM tb_asset WHERE asset_id = %s", (asset_id,))
        conn.commit()
        return {"status": "success", "message": "设备及其关联数据已彻底清除！"}
    except Exception as e:
        conn.rollback()
        return {"status": "error", "message": f"删除失败：{str(e)}"}
    finally:
        cursor.close()
        conn.close()
