import json
from datetime import datetime, timedelta

from database import ensure_metric_bind_columns, ensure_product_tables, get_db_connection


# 案例数据：200 天标准化检测，t1-t10 每 20 天一次。
CASE_START_TIME = datetime(2025, 1, 1, 9, 0, 0)
CASE_STEP_DAYS = 20

WHOLE_CRITERION_WEIGHTS = {
    "几何精度": 0.35,
    "位置精度": 0.40,
    "加工精度": 0.25,
}

WHOLE_SERIES = {
    "X轴直线度": [5.11, 5.23, 5.40, 5.61, 5.88, 6.18, 6.54, 6.96, 7.42, 7.95],
    "X/Y轴垂直度": [6.12, 6.31, 6.55, 6.84, 7.18, 7.58, 8.04, 8.56, 9.15, 9.80],
    "X轴定位精度": [8.25, 8.65, 9.12, 9.72, 10.45, 11.31, 12.30, 13.45, 14.75, 16.20],
    "X轴重复定位精度": [3.08, 3.19, 3.32, 3.48, 3.68, 3.91, 4.19, 4.51, 4.88, 5.32],
    "Y轴定位精度": [7.85, 8.10, 8.42, 8.85, 9.31, 9.92, 10.58, 11.31, 12.08, 12.92],
    "Y轴重复定位精度": [2.96, 3.08, 3.23, 3.40, 3.61, 3.86, 4.15, 4.48, 4.82, 5.18],
    "上表面粗糙度": [0.40, 0.42, 0.45, 0.49, 0.54, 0.60, 0.67, 0.75, 0.84, 0.95],
    "外圆圆度": [8.10, 8.35, 8.72, 9.16, 9.68, 10.30, 11.02, 11.85, 12.78, 13.86],
    "基准面平行度": [6.05, 6.22, 6.44, 6.70, 7.02, 7.40, 7.84, 8.35, 8.93, 9.58],
    "安装孔位置度": [10.10, 10.42, 10.86, 11.38, 12.02, 12.76, 13.62, 14.60, 15.72, 16.96],
    "端面圆跳动": [5.10, 5.24, 5.43, 5.68, 5.99, 6.36, 6.80, 7.31, 7.90, 8.56],
}

WHOLE_METRICS = [
    {
        "code": "C11",
        "category_level_1": "几何精度",
        "category_level_2": "直线度",
        "metric_name": "X轴直线度",
        "value_type": "单值",
        "description": "整机精度保持性测试案例指标 C11",
        "bind": {
            "unit": "μm",
            "baseline_value": 5.0,
            "failure_value": 15.0,
            "criterion_weight": WHOLE_CRITERION_WEIGHTS["几何精度"],
            "indicator_weight": 0.5,
        },
    },
    {
        "code": "C13",
        "category_level_1": "几何精度",
        "category_level_2": "垂直度",
        "metric_name": "X/Y轴垂直度",
        "value_type": "单值",
        "description": "整机精度保持性测试案例指标 C13",
        "bind": {
            "unit": "μm/300mm",
            "baseline_value": 6.0,
            "failure_value": 18.0,
            "criterion_weight": WHOLE_CRITERION_WEIGHTS["几何精度"],
            "indicator_weight": 0.5,
        },
    },
    {
        "code": "C21",
        "category_level_1": "位置精度",
        "category_level_2": "定位精度",
        "metric_name": "X轴定位精度",
        "value_type": "单值",
        "description": "整机精度保持性测试案例指标 C21",
        "bind": {
            "unit": "μm",
            "baseline_value": 8.0,
            "failure_value": 25.0,
            "criterion_weight": WHOLE_CRITERION_WEIGHTS["位置精度"],
            "indicator_weight": 0.25,
        },
    },
    {
        "code": "C22",
        "category_level_1": "位置精度",
        "category_level_2": "重复定位精度",
        "metric_name": "X轴重复定位精度",
        "value_type": "单值",
        "description": "整机精度保持性测试案例指标 C22",
        "bind": {
            "unit": "μm",
            "baseline_value": 3.0,
            "failure_value": 10.0,
            "criterion_weight": WHOLE_CRITERION_WEIGHTS["位置精度"],
            "indicator_weight": 0.25,
        },
    },
    {
        "code": "C23",
        "category_level_1": "位置精度",
        "category_level_2": "定位精度",
        "metric_name": "Y轴定位精度",
        "value_type": "单值",
        "description": "整机精度保持性测试案例指标 C23",
        "bind": {
            "unit": "μm",
            "baseline_value": 8.0,
            "failure_value": 25.0,
            "criterion_weight": WHOLE_CRITERION_WEIGHTS["位置精度"],
            "indicator_weight": 0.25,
        },
    },
    {
        "code": "C24",
        "category_level_1": "位置精度",
        "category_level_2": "重复定位精度",
        "metric_name": "Y轴重复定位精度",
        "value_type": "单值",
        "description": "整机精度保持性测试案例指标 C24",
        "bind": {
            "unit": "μm",
            "baseline_value": 3.0,
            "failure_value": 10.0,
            "criterion_weight": WHOLE_CRITERION_WEIGHTS["位置精度"],
            "indicator_weight": 0.25,
        },
    },
    {
        "code": "C31",
        "category_level_1": "加工精度",
        "category_level_2": "粗糙度",
        "metric_name": "上表面粗糙度",
        "value_type": "抽样数组",
        "description": "整机精度保持性测试案例指标 C31",
        "bind": {
            "unit": "μm",
            "baseline_value": 0.4,
            "failure_value": 1.6,
            "lower_limit": 0.4,
            "upper_limit": 1.6,
            "criterion_weight": WHOLE_CRITERION_WEIGHTS["加工精度"],
            "indicator_weight": 0.2,
        },
    },
    {
        "code": "C32",
        "category_level_1": "加工精度",
        "category_level_2": "圆度",
        "metric_name": "外圆圆度",
        "value_type": "抽样数组",
        "description": "整机精度保持性测试案例指标 C32",
        "bind": {
            "unit": "μm",
            "baseline_value": 8.0,
            "failure_value": 25.0,
            "lower_limit": 8.0,
            "upper_limit": 25.0,
            "criterion_weight": WHOLE_CRITERION_WEIGHTS["加工精度"],
            "indicator_weight": 0.2,
        },
    },
    {
        "code": "C33",
        "category_level_1": "加工精度",
        "category_level_2": "平行度",
        "metric_name": "基准面平行度",
        "value_type": "抽样数组",
        "description": "整机精度保持性测试案例指标 C33",
        "bind": {
            "unit": "μm",
            "baseline_value": 6.0,
            "failure_value": 18.0,
            "lower_limit": 6.0,
            "upper_limit": 18.0,
            "criterion_weight": WHOLE_CRITERION_WEIGHTS["加工精度"],
            "indicator_weight": 0.2,
        },
    },
    {
        "code": "C34",
        "category_level_1": "加工精度",
        "category_level_2": "位置度",
        "metric_name": "安装孔位置度",
        "value_type": "抽样数组",
        "description": "整机精度保持性测试案例指标 C34",
        "bind": {
            "unit": "μm",
            "baseline_value": 10.0,
            "failure_value": 30.0,
            "lower_limit": 10.0,
            "upper_limit": 30.0,
            "criterion_weight": WHOLE_CRITERION_WEIGHTS["加工精度"],
            "indicator_weight": 0.2,
        },
    },
    {
        "code": "C35",
        "category_level_1": "加工精度",
        "category_level_2": "圆跳动",
        "metric_name": "端面圆跳动",
        "value_type": "抽样数组",
        "description": "整机精度保持性测试案例指标 C35",
        "bind": {
            "unit": "μm",
            "baseline_value": 5.0,
            "failure_value": 15.0,
            "lower_limit": 5.0,
            "upper_limit": 15.0,
            "criterion_weight": WHOLE_CRITERION_WEIGHTS["加工精度"],
            "indicator_weight": 0.2,
        },
    },
]

WHOLE_PRODUCTS = [
    {
        "product_code": "CASE-PROD-001",
        "product_name": "案例测试试件",
        "product_detail": "用于多加工精度指标评价的案例试件数据",
        "total_quantity": 10,
        "process_note": "t1-t10 批次测试，样本均值对应批次实测值",
        "metric_names": ["上表面粗糙度", "外圆圆度", "基准面平行度", "安装孔位置度", "端面圆跳动"],
    }
]

PART_ASSETS = [
    {
        "asset_name": "案例主轴部件-未失效",
        "asset_type": "主轴部件",
        "asset_code": "CASE-PART-SPINDLE-OK",
        "metrics": [
            {
                "category_level_1": "几何精度",
                "category_level_2": "径向跳动",
                "metric_name": "主轴径向跳动",
                "value_type": "单值",
                "description": "部件保持度评价案例指标",
                "bind": {"unit": "μm", "baseline_value": 3.0, "failure_value": 10.0, "cycle_hours": 12.0},
                "series": [3.2, 3.5, 3.9, 4.4, 4.8, 5.3],
            },
            {
                "category_level_1": "位置精度",
                "category_level_2": "轴向窜动",
                "metric_name": "主轴轴向窜动",
                "value_type": "单值",
                "description": "部件保持度评价案例指标",
                "bind": {"unit": "μm", "baseline_value": 2.0, "failure_value": 8.0, "cycle_hours": 12.0},
                "series": [2.1, 2.3, 2.7, 3.0, 3.4, 3.9],
            },
        ],
        "record_interval_hours": 12,
    },
    {
        "asset_name": "案例刀架部件-已失效",
        "asset_type": "刀架部件",
        "asset_code": "CASE-PART-TURRET-FAIL",
        "metrics": [
            {
                "category_level_1": "几何精度",
                "category_level_2": "重复定位精度",
                "metric_name": "刀架重复定位误差",
                "value_type": "单值",
                "description": "部件保持时间评价案例指标",
                "bind": {"unit": "μm", "baseline_value": 4.0, "failure_value": 12.0, "cycle_hours": 10.0},
                "series": [4.2, 5.1, 6.4, 8.8, 12.6, 14.1],
            },
            {
                "category_level_1": "位置精度",
                "category_level_2": "间隙",
                "metric_name": "刀架锁紧间隙",
                "value_type": "单值",
                "description": "部件保持时间评价案例指标",
                "bind": {"unit": "μm", "baseline_value": 5.0, "failure_value": 15.0, "cycle_hours": 10.0},
                "series": [5.2, 6.0, 7.1, 8.3, 9.6, 10.8],
            },
        ],
        "record_interval_hours": 10,
    },
]


def case_time(index):
    return CASE_START_TIME + timedelta(days=index * CASE_STEP_DAYS)


def symmetric_samples(mean_value, std_value):
    # 5 个样本，样本标准差等于 std_value，均值等于 mean_value。
    return [
        round(mean_value - std_value, 6),
        round(mean_value - std_value, 6),
        round(mean_value, 6),
        round(mean_value + std_value, 6),
        round(mean_value + std_value, 6),
    ]


def product_samples(metric_name, index):
    mean_value = WHOLE_SERIES[metric_name][index]
    std_map = {
        "上表面粗糙度": 0.08,
        "外圆圆度": 0.65,
        "基准面平行度": 0.45,
        "安装孔位置度": 0.80,
        "端面圆跳动": 0.36,
    }
    std_value = std_map.get(metric_name, 0.3)
    return symmetric_samples(mean_value, std_value)


def insert_metric(cursor, metric):
    cursor.execute(
        """
        INSERT INTO tb_metric_dict (category_level_1, category_level_2, metric_name, value_type, description)
        VALUES (%s, %s, %s, %s, %s)
        RETURNING metric_id
        """,
        (
            metric["category_level_1"],
            metric["category_level_2"],
            metric["metric_name"],
            metric["value_type"],
            metric["description"],
        ),
    )
    return cursor.fetchone()[0]


def bind_metric(cursor, asset_id, metric_id, bind):
    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, is_active
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, TRUE)
        """,
        (
            asset_id,
            metric_id,
            bind.get("upper_limit"),
            bind.get("lower_limit"),
            bind.get("baseline_value"),
            bind.get("failure_value"),
            bind.get("ideal_value"),
            bind.get("extreme_value"),
            bind.get("criterion_weight"),
            bind.get("indicator_weight"),
            bind.get("cycle_hours"),
            bind.get("unit"),
        ),
    )


def insert_record(cursor, asset_id, metric_id, created_at, value_type, payload):
    raw_data = {"val": payload} if value_type == "单值" else {"samples": payload}
    cursor.execute(
        """
        INSERT INTO tb_test_record (asset_id, metric_id, test_time, raw_data, created_at)
        VALUES (%s, %s, %s, %s::jsonb, %s)
        """,
        (asset_id, metric_id, created_at, json.dumps(raw_data, ensure_ascii=False), created_at),
    )


def insert_product(cursor, product):
    cursor.execute(
        """
        INSERT INTO tb_product_def (product_code, product_name, product_detail)
        VALUES (%s, %s, %s)
        RETURNING product_id
        """,
        (product["product_code"], product["product_name"], product["product_detail"]),
    )
    return cursor.fetchone()[0]


def bind_product(cursor, asset_id, product_id, product):
    cursor.execute(
        """
        INSERT INTO tb_asset_product_bind (asset_id, product_id, total_quantity, process_note)
        VALUES (%s, %s, %s, %s)
        """,
        (asset_id, product_id, product.get("total_quantity"), product.get("process_note")),
    )


def config_product_metric(cursor, product_id, metric_id, bind):
    cursor.execute(
        """
        INSERT INTO tb_product_metric_config (
            product_id, metric_id, upper_limit, lower_limit, ideal_value, extreme_value, unit
        ) VALUES (%s, %s, %s, %s, %s, %s, %s)
        """,
        (
            product_id,
            metric_id,
            bind.get("upper_limit"),
            bind.get("lower_limit"),
            bind.get("ideal_value"),
            bind.get("extreme_value"),
            bind.get("unit"),
        ),
    )


def insert_product_record(cursor, asset_id, product_id, batch_no, created_at, production_quantity, remark):
    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
        """,
        (asset_id, product_id, batch_no, 5, production_quantity, created_at, remark),
    )
    return cursor.fetchone()[0]


def insert_product_samples(cursor, record_id, metric_id, samples):
    for index, value in enumerate(samples, 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_id, f"S{index}", value),
        )


def main():
    ensure_metric_bind_columns()
    ensure_product_tables()
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM tb_product_sample_value")
        cursor.execute("DELETE FROM tb_product_record")
        cursor.execute("DELETE FROM tb_product_metric_config")
        cursor.execute("DELETE FROM tb_asset_product_bind")
        cursor.execute("DELETE FROM tb_product_def")
        cursor.execute("DELETE FROM tb_test_record")
        cursor.execute("DELETE FROM tb_asset_metric_bind")
        cursor.execute("DELETE FROM tb_asset")
        cursor.execute("DELETE FROM tb_metric_dict")
        cursor.execute("DELETE FROM tb_project")
        cursor.execute("ALTER SEQUENCE IF EXISTS tb_product_sample_value_sample_id_seq RESTART WITH 1")
        cursor.execute("ALTER SEQUENCE IF EXISTS tb_product_record_product_record_id_seq RESTART WITH 1")
        cursor.execute("ALTER SEQUENCE IF EXISTS tb_product_metric_config_config_id_seq RESTART WITH 1")
        cursor.execute("ALTER SEQUENCE IF EXISTS tb_asset_product_bind_bind_id_seq RESTART WITH 1")
        cursor.execute("ALTER SEQUENCE IF EXISTS tb_product_def_product_id_seq RESTART WITH 1")
        cursor.execute("ALTER SEQUENCE IF EXISTS tb_test_record_record_id_seq RESTART WITH 1")
        cursor.execute("ALTER SEQUENCE IF EXISTS tb_asset_metric_bind_bind_id_seq RESTART WITH 1")
        cursor.execute("ALTER SEQUENCE IF EXISTS tb_asset_asset_id_seq RESTART WITH 1")
        cursor.execute("ALTER SEQUENCE IF EXISTS tb_metric_dict_metric_id_seq RESTART WITH 1")
        cursor.execute("ALTER SEQUENCE IF EXISTS tb_project_project_id_seq RESTART WITH 1")

        cursor.execute(
            "INSERT INTO tb_project (project_name, eval_mode) VALUES (%s, %s) RETURNING project_id",
            ("整机精度保持性案例项目", "整机跟踪,部件试验"),
        )
        project_id = cursor.fetchone()[0]

        cursor.execute(
            """
            INSERT INTO tb_asset (project_id, asset_type, asset_code, asset_category, asset_name)
            VALUES (%s, %s, %s, %s, %s)
            RETURNING asset_id
            """,
            (project_id, "立式加工中心", "CASE-WHOLE-MC-001", "整机", "案例立式加工中心"),
        )
        whole_asset_id = cursor.fetchone()[0]

        whole_metric_ids = {}
        for metric in WHOLE_METRICS:
            metric_id = insert_metric(cursor, metric)
            whole_metric_ids[metric["metric_name"]] = metric_id
            bind_metric(cursor, whole_asset_id, metric_id, metric["bind"])
            if metric["category_level_1"] != "加工精度":
                for index, payload in enumerate(WHOLE_SERIES[metric["metric_name"]]):
                    insert_record(cursor, whole_asset_id, metric_id, case_time(index), metric["value_type"], payload)

        for product in WHOLE_PRODUCTS:
            product_id = insert_product(cursor, product)
            bind_product(cursor, whole_asset_id, product_id, product)
            for metric_name in product["metric_names"]:
                metric = next(item for item in WHOLE_METRICS if item["metric_name"] == metric_name)
                config_product_metric(cursor, product_id, whole_metric_ids[metric_name], metric["bind"])

            for index in range(10):
                created_at = case_time(index)
                record_id = insert_product_record(
                    cursor,
                    whole_asset_id,
                    product_id,
                    f"T{index + 1}-{created_at.strftime('%Y%m%d')}",
                    created_at,
                    product["total_quantity"],
                    product["process_note"],
                )
                for metric_name in product["metric_names"]:
                    insert_product_samples(
                        cursor,
                        record_id,
                        whole_metric_ids[metric_name],
                        product_samples(metric_name, index),
                    )

        for offset, part_asset in enumerate(PART_ASSETS):
            cursor.execute(
                """
                INSERT INTO tb_asset (project_id, asset_type, asset_code, asset_category, asset_name)
                VALUES (%s, %s, %s, %s, %s)
                RETURNING asset_id
                """,
                (project_id, part_asset["asset_type"], part_asset["asset_code"], "部件", part_asset["asset_name"]),
            )
            part_asset_id = cursor.fetchone()[0]
            part_start = datetime(2025, 8, 1 + offset, 8, 0, 0)
            for metric in part_asset["metrics"]:
                metric_id = insert_metric(cursor, metric)
                bind_metric(cursor, part_asset_id, metric_id, metric["bind"])
                for index, payload in enumerate(metric["series"]):
                    created_at = part_start + timedelta(hours=index * part_asset["record_interval_hours"])
                    insert_record(cursor, part_asset_id, metric_id, created_at, metric["value_type"], payload)

        conn.commit()
        print("✅ 已清除旧记录，并写入整机精度保持性案例数据。")
        print(f"整机资产ID: {whole_asset_id}；采样日期: {CASE_START_TIME.date()} 起，每 {CASE_STEP_DAYS} 天一次，共 10 次。")
    except Exception:
        conn.rollback()
        raise
    finally:
        cursor.close()
        conn.close()


if __name__ == "__main__":
    main()
