from fastapi import APIRouter
from psycopg2.extras import RealDictCursor
import json
from database import get_db_connection
from schemas import RecordCreate

router = APIRouter()

@router.get("/api/assets/{asset_id}/metrics/detail")
def get_bound_metrics_detail(asset_id: int):
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    
    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
    """, (asset_id,))
    
    metrics = cursor.fetchall()
    cursor.close()
    conn.close()
    return {"status": "success", "data": metrics}

@router.post("/api/records")
def create_records(item: RecordCreate):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        for record in item.records:
            cursor.execute(
                "INSERT INTO tb_test_record (asset_id, metric_id, raw_data, created_at) VALUES (%s, %s, %s, %s)",
                (item.asset_id, record.metric_id, json.dumps(record.raw_data), item.record_date)
            )
        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/records")
def get_records(asset_id: int):
    """根据设备ID获取所有测试记录"""
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    try:
        # 联合指标与设备绑定配置，把展示单位一起查出来。
        cursor.execute("""
            SELECT
                r.record_id,
                r.asset_id,
                r.metric_id,
                r.raw_data,
                r.created_at,
                m.metric_name,
                m.value_type,
                b.unit
            FROM tb_test_record r
            JOIN tb_metric_dict m ON r.metric_id = m.metric_id
            LEFT JOIN tb_asset_metric_bind b ON b.asset_id = r.asset_id AND b.metric_id = r.metric_id
            WHERE r.asset_id = %s
            ORDER BY r.created_at DESC
        """, (asset_id,))
        records = cursor.fetchall()
        return {"status": "success", "data": records}
    finally:
        cursor.close()
        conn.close()


@router.put("/api/records/session")
def update_record_session(item: RecordCreate):
    """按同一次录入时间批量覆盖更新测试记录"""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            "DELETE FROM tb_test_record WHERE asset_id = %s AND created_at = %s",
            (item.asset_id, item.record_date)
        )
        for record in item.records:
            cursor.execute(
                "INSERT INTO tb_test_record (asset_id, metric_id, raw_data, created_at) VALUES (%s, %s, %s, %s)",
                (item.asset_id, record.metric_id, json.dumps(record.raw_data), item.record_date)
            )
        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/records/{record_id}")
def delete_record(record_id: int):
    """删除单条错误记录"""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM tb_test_record WHERE record_id = %s", (record_id,))
        conn.commit()
        return {"status": "success", "message": "记录已作废/删除！"}
    except Exception as e:
        conn.rollback()
        return {"status": "error", "message": str(e)}
    finally:
        cursor.close()
        conn.close()
