from typing import Optional

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

from database import get_db_connection
from schemas import (
    AssetProductBindBatch,
    ProductCreate,
    ProductMetricConfigBatch,
    ProductRecordCreate,
)

router = APIRouter()


@router.get("/api/products")
def get_products():
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    try:
        cursor.execute(
            """
            SELECT
                p.*,
                COUNT(DISTINCT apb.asset_id) AS asset_count,
                COUNT(DISTINCT pr.product_record_id) AS record_count
            FROM tb_product_def p
            LEFT JOIN tb_asset_product_bind apb ON p.product_id = apb.product_id
            LEFT JOIN tb_product_record pr ON p.product_id = pr.product_id
            GROUP BY p.product_id
            ORDER BY p.product_code, p.product_id
            """
        )
        return {"status": "success", "data": cursor.fetchall()}
    finally:
        cursor.close()
        conn.close()


@router.post("/api/products")
def create_product(item: ProductCreate):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            """
            INSERT INTO tb_product_def (product_code, product_name, product_detail)
            VALUES (%s, %s, %s)
            """,
            (item.product_code, item.product_name, item.product_detail),
        )
        conn.commit()
        return {"status": "success", "message": "产品已创建"}
    except Exception as e:
        conn.rollback()
        return {"status": "error", "message": str(e)}
    finally:
        cursor.close()
        conn.close()


@router.put("/api/products/{product_id}")
def update_product(product_id: int, item: ProductCreate):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            """
            UPDATE tb_product_def
            SET product_code = %s, product_name = %s, product_detail = %s
            WHERE product_id = %s
            """,
            (item.product_code, item.product_name, item.product_detail, product_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.delete("/api/products/{product_id}")
def delete_product(product_id: int):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM tb_product_def WHERE product_id = %s", (product_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}/products")
def get_asset_products(asset_id: int):
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    try:
        cursor.execute(
            """
            SELECT
                apb.bind_id,
                apb.asset_id,
                apb.product_id,
                apb.total_quantity,
                apb.process_note,
                p.product_code,
                p.product_name,
                p.product_detail,
                (
                    SELECT MAX(pr.created_at)
                    FROM tb_product_record pr
                    WHERE pr.asset_id = apb.asset_id AND pr.product_id = apb.product_id
                ) AS latest_record_at
            FROM tb_asset_product_bind apb
            JOIN tb_product_def p ON p.product_id = apb.product_id
            WHERE apb.asset_id = %s
            ORDER BY p.product_code, p.product_id
            """,
            (asset_id,),
        )
        return {"status": "success", "data": cursor.fetchall()}
    finally:
        cursor.close()
        conn.close()


@router.post("/api/assets/{asset_id}/products")
def save_asset_products(asset_id: int, item: AssetProductBindBatch):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM tb_asset_product_bind WHERE asset_id = %s", (asset_id,))
        for binding in item.bindings:
            cursor.execute(
                """
                INSERT INTO tb_asset_product_bind (asset_id, product_id, total_quantity, process_note)
                VALUES (%s, %s, %s, %s)
                """,
                (asset_id, binding.product_id, binding.total_quantity, binding.process_note),
            )
        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/products/{product_id}/metric-configs")
def get_product_metric_configs(product_id: int):
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    try:
        cursor.execute(
            """
            SELECT
                m.metric_id,
                m.metric_name,
                m.category_level_1,
                m.category_level_2,
                m.value_type,
                c.upper_limit,
	                c.lower_limit,
	                c.ideal_value,
	                c.extreme_value,
	                c.unit,
	                c.position_note
            FROM tb_product_metric_config c
            JOIN tb_metric_dict m ON c.metric_id = m.metric_id
            WHERE c.product_id = %s
            ORDER BY m.metric_name
            """,
            (product_id,),
        )
        return {"status": "success", "data": cursor.fetchall()}
    finally:
        cursor.close()
        conn.close()


@router.post("/api/products/{product_id}/metric-configs")
def save_product_metric_configs(product_id: int, item: ProductMetricConfigBatch):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM tb_product_metric_config WHERE product_id = %s", (product_id,))
        for metric in item.metrics:
            cursor.execute(
                """
	                INSERT INTO tb_product_metric_config (
	                    product_id, metric_id, upper_limit, lower_limit, ideal_value, extreme_value, unit, position_note
	                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                """,
                (
                    product_id,
                    metric.metric_id,
                    metric.upper_limit,
                    metric.lower_limit,
	                    metric.ideal_value,
	                    metric.extreme_value,
	                    metric.unit,
	                    metric.position_note,
	                ),
            )
        conn.commit()
        return {"status": "success", "message": "产品加工指标配置已保存"}
    except Exception as e:
        conn.rollback()
        return {"status": "error", "message": str(e)}
    finally:
        cursor.close()
        conn.close()


@router.post("/api/product-records")
def create_product_record(item: ProductRecordCreate):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        sample_count = max((len(metric.values) for metric in item.metric_samples), default=0)
        cursor.execute(
            """
            INSERT INTO tb_product_record (asset_id, product_id, batch_no, sample_count, production_quantity, created_at, remark)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
            RETURNING product_record_id
            """,
            (
                item.asset_id,
                item.product_id,
                item.batch_no,
                sample_count,
                item.production_quantity,
                item.record_date,
                item.remark,
            ),
        )
        record_id = cursor.fetchone()[0]

        for metric in item.metric_samples:
            for index, value in enumerate(metric.values, start=1):
                cursor.execute(
                    """
                    INSERT INTO tb_product_sample_value (record_id, metric_id, sample_no, value)
                    VALUES (%s, %s, %s, %s)
                    """,
                    (record_id, metric.metric_id, f"S{index}", value),
                )

        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/product-records")
def get_product_records(asset_id: int = Query(...), product_id: Optional[int] = Query(None)):
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    try:
        sql = """
            SELECT
                pr.product_record_id,
                pr.asset_id,
                pr.product_id,
                pr.batch_no,
                pr.sample_count,
                pr.production_quantity,
                pr.created_at,
                pr.remark,
                p.product_code,
                p.product_name
            FROM tb_product_record pr
            JOIN tb_product_def p ON p.product_id = pr.product_id
            WHERE pr.asset_id = %s
        """
        params = [asset_id]
        if product_id is not None:
            sql += " AND pr.product_id = %s"
            params.append(product_id)
        sql += " ORDER BY pr.created_at DESC, pr.product_record_id DESC"
        cursor.execute(sql, tuple(params))
        rows = cursor.fetchall()
        record_ids = [row["product_record_id"] for row in rows]
        if not record_ids:
            return {"status": "success", "data": rows}

        cursor.execute(
            """
            SELECT
                sv.record_id,
                sv.metric_id,
                sv.sample_no,
                sv.value,
                m.metric_name,
                COALESCE(c.unit, '') AS unit
            FROM tb_product_sample_value sv
            JOIN tb_metric_dict m ON m.metric_id = sv.metric_id
            LEFT JOIN tb_product_metric_config c
                ON c.product_id = (
                    SELECT pr.product_id
                    FROM tb_product_record pr
                    WHERE pr.product_record_id = sv.record_id
                )
                AND c.metric_id = sv.metric_id
            WHERE sv.record_id = ANY(%s)
            ORDER BY sv.record_id, m.metric_name, sv.sample_no
            """,
            (record_ids,),
        )
        sample_rows = cursor.fetchall()

        samples_by_record = {}
        for sample in sample_rows:
            record_id = sample["record_id"]
            metric_id = sample["metric_id"]
            record_metrics = samples_by_record.setdefault(record_id, {})
            metric = record_metrics.setdefault(
                metric_id,
                {
                    "metric_id": metric_id,
                    "metric_name": sample["metric_name"],
                    "unit": sample["unit"],
                    "samples": [],
                    "values": [],
                },
            )
            value = float(sample["value"]) if sample["value"] is not None else None
            metric["samples"].append({"sample_no": sample["sample_no"], "value": value})
            metric["values"].append(value)

        for row in rows:
            metrics = samples_by_record.get(row["product_record_id"], {})
            row["metric_samples"] = list(metrics.values())

        return {"status": "success", "data": rows}
    finally:
        cursor.close()
        conn.close()
