vent_ai_platform/app/db/seed.py

from __future__ import annotations

from datetime import datetime, timedelta, timezone

from sqlalchemy import select
from sqlalchemy.orm import Session

from app.db.base import new_id
from app.db.models.diagnosis import DiagnosisRule
from app.db.models.document import BusinessDocument, DocumentExtraction
from app.db.models.evaluation import EvaluationIndicator
from app.db.models.knowledge import KnowledgeItem
from app.db.models.mine import Mine, MineArea, Roadway, WorkFace
from app.db.models.monitoring import (
    DataSource,
    Sensor,
    SensorReading,
    VentilationFacility,
)
from app.db.models.work_order import Alarm, WorkOrder, WorkOrderEvent


def seed_demo_data(db: Session) -> None:
    if db.scalar(select(Mine).where(Mine.code == "shangwan")):
        return

    now = datetime.now(timezone.utc)
    mine = Mine(
        id=new_id(),
        code="shangwan",
        name="上湾煤矿",
        short_name="上湾煤矿",
        province="内蒙古自治区",
        city="鄂尔多斯市",
        status="active",
        production_capacity=1600,
        design_capacity=1600,
        ventilation_method="中央并列式",
        description="通防智能管控平台示范矿井",
        settings={"coordinate_system": "CGCS2000"},
        is_default=True,
    )
    area = MineArea(
        id=new_id(),
        mine_id=mine.id,
        code="N",
        name="北翼采区",
        area_type="采区",
        depth=420,
        status="active",
    )
    face = WorkFace(
        id=new_id(),
        mine_id=mine.id,
        area_id=area.id,
        code="N1202",
        name="N1202 综采工作面",
        face_type="采煤工作面",
        status="active",
        planned_air_volume=1404,
        actual_air_volume=1286,
        gas_limit=0.8,
        dust_limit=4.0,
        location={"x": 1202, "y": 386, "z": -420},
    )
    return_roadway = Roadway(
        id=new_id(),
        mine_id=mine.id,
        area_id=area.id,
        code="N1202-R",
        name="N1202 回风巷",
        roadway_type="回风巷",
        start_node="N1202",
        end_node="北翼回风",
        length=1260,
        cross_section_area=18.6,
        resistance=2860,
        airflow=1286,
        status="warning",
        properties={"support_type": "锚网索"},
    )

    gas_source = DataSource(
        id=new_id(),
        mine_id=mine.id,
        code="KJ-SAFETY",
        name="KJ 安全监控系统",
        source_type="monitoring",
        protocol="OPC UA",
        endpoint="opc.tcp://10.20.16.28:4840",
        status="online",
        last_sync_at=now,
        point_count=1286,
        latency_ms=2400,
        config={},
    )
    video_source = DataSource(
        id=new_id(),
        mine_id=mine.id,
        code="VIDEO",
        name="工业视频平台",
        source_type="video",
        protocol="GB/T 28181",
        endpoint="10.20.18.10:5060",
        status="online",
        last_sync_at=now,
        point_count=218,
        latency_ms=820,
        config={"channels": 218},
    )
    gas_sensor = Sensor(
        id=new_id(),
        mine_id=mine.id,
        area_id=area.id,
        data_source_id=gas_source.id,
        code="CH4-N1202-01",
        name="N1202 回风巷瓦斯",
        sensor_type="gas",
        unit="%CH4",
        location="N1202 回风巷",
        status="online",
        warning_threshold=0.6,
        alarm_threshold=0.8,
        upper_bound=4,
        lower_bound=0,
        last_value=0.86,
        last_seen_at=now,
        properties={},
    )
    airflow_sensor = Sensor(
        id=new_id(),
        mine_id=mine.id,
        area_id=area.id,
        data_source_id=gas_source.id,
        code="AF-N1202-01",
        name="N1202 工作面风量",
        sensor_type="airflow",
        unit="m³/min",
        location="N1202 工作面",
        status="online",
        warning_threshold=1300,
        alarm_threshold=1200,
        upper_bound=1800,
        lower_bound=0,
        last_value=1286,
        last_seen_at=now,
        properties={},
    )
    dust_sensor = Sensor(
        id=new_id(),
        mine_id=mine.id,
        area_id=area.id,
        data_source_id=gas_source.id,
        code="DUST-S1302-01",
        name="S1302 粉尘",
        sensor_type="dust",
        unit="mg/m³",
        location="S1302 回风巷",
        status="online",
        warning_threshold=3.5,
        alarm_threshold=4.0,
        upper_bound=10,
        lower_bound=0,
        last_value=4.2,
        last_seen_at=now,
        properties={},
    )
    gate = VentilationFacility(
        id=new_id(),
        mine_id=mine.id,
        area_id=area.id,
        roadway_id=return_roadway.id,
        code="FG-002",
        name="2# 调节风门",
        facility_type="风门",
        location="北翼回风巷",
        status="warning",
        open_state="open",
        sensor_value=42,
        video_confidence=0.91,
        last_checked_at=now,
        properties={},
    )

    db.add_all(
        [
            mine,
            area,
            face,
            return_roadway,
            gas_source,
            video_source,
            gas_sensor,
            airflow_sensor,
            dust_sensor,
            gate,
        ]
    )
    db.flush()

    readings = []
    for sensor, base in (
        (gas_sensor, 0.46),
        (airflow_sensor, 1380),
        (dust_sensor, 3.1),
    ):
        for index in range(24):
            value = base + (index % 6) * (0.03 if sensor.sensor_type == "gas" else 8)
            readings.append(
                SensorReading(
                    id=new_id(),
                    sensor_id=sensor.id,
                    measured_at=now - timedelta(hours=23 - index),
                    value=round(value, 2),
                    quality=100,
                    is_alarm=sensor.sensor_type == "gas" and value >= 0.8,
                    source="seed",
                    meta={},
                )
            )
    db.add_all(readings)

    documents = [
        BusinessDocument(
            id=new_id(),
            mine_id=mine.id,
            code="TF-CS-2026-086",
            title="N1202 工作面通风安全技术措施",
            category="通风专业",
            subcategory="安全技术措施",
            file_name="N1202通风安全技术措施.pdf",
            file_path="data/uploads/N1202通风安全技术措施.pdf",
            file_ext=".pdf",
            file_size=2_936_012,
            page_count=42,
            owner_name="王强",
            department="通防科",
            version="V1.0",
            parse_status="completed",
            parse_progress=100,
            tags=["通风", "N1202", "安全措施"],
            summary="N1202 工作面通风方式、需风量和瓦斯管理要求。",
            metadata_json={},
        ),
        BusinessDocument(
            id=new_id(),
            mine_id=mine.id,
            code="TF-BB-2026-091",
            title="2026 年 9 月通风阻力测定报告",
            category="通风专业",
            subcategory="测定报告",
            file_name="通风阻力测定报告.docx",
            file_path="data/uploads/通风阻力测定报告.docx",
            file_ext=".docx",
            file_size=1_678_592,
            page_count=28,
            owner_name="赵磊",
            department="通防科",
            parse_status="completed",
            parse_progress=100,
            tags=["通风阻力", "月度报告"],
            summary="北翼回风系统阻力和主通风机工况分析。",
            metadata_json={},
        ),
        BusinessDocument(
            id=new_id(),
            mine_id=mine.id,
            code="TF-BB-2026-092",
            title="矿井测风记录日报表",
            category="监测监控",
            subcategory="台账报表",
            file_name="测风记录日报表.xlsx",
            file_path="data/uploads/测风记录日报表.xlsx",
            file_ext=".xlsx",
            file_size=876_544,
            row_count=1240,
            owner_name="刘伟",
            department="通防科",
            parse_status="processing",
            parse_progress=76,
            tags=["测风", "日报"],
            summary="各测风站风量和风速记录。",
            metadata_json={},
        ),
        BusinessDocument(
            id=new_id(),
            mine_id=mine.id,
            code="TF-SJ-2026-034",
            title="S1302 工作面综合防尘设计",
            category="粉尘防治",
            subcategory="专项设计",
            file_name="S1302综合防尘设计.pdf",
            file_path="data/uploads/S1302综合防尘设计.pdf",
            file_ext=".pdf",
            file_size=3_250_176,
            page_count=36,
            owner_name="陈晨",
            department="通防科",
            parse_status="pending",
            parse_progress=0,
            tags=["粉尘", "S1302"],
            summary="综合防尘设施和喷雾降尘设计。",
            metadata_json={},
        ),
    ]
    db.add_all(documents)
    db.flush()
    db.add_all(
        [
            DocumentExtraction(
                id=new_id(),
                document_id=documents[0].id,
                field_key="ventilation_method",
                field_name="通风方式",
                field_value="U 型通风",
                normalized_value="U型通风",
                confidence=0.968,
                page_no=8,
                verified=True,
                metadata_json={},
            ),
            DocumentExtraction(
                id=new_id(),
                document_id=documents[0].id,
                field_key="planned_air_volume",
                field_name="设计需风量",
                field_value="1404 m³/min",
                normalized_value="1404",
                unit="m³/min",
                confidence=0.942,
                page_no=12,
                verified=True,
                metadata_json={},
            ),
        ]
    )

    db.add_all(
        [
            KnowledgeItem(
                id=new_id(),
                mine_id=mine.id,
                document_id=documents[0].id,
                code="K-REG-001",
                title="瓦斯超限处置要求",
                knowledge_type="规程",
                category="瓦斯防治",
                source_type="document",
                source_ref="N1202 工作面通风安全技术措施",
                content="瓦斯浓度超限后应立即停止作业、切断电源、撤出人员，并核查局部通风和回风系统。",
                summary="明确瓦斯超限后的撤人、断电、检查和复测闭环要求。",
                tags=["瓦斯", "超限", "应急处置"],
                is_verified=True,
                verified_by="通防专家",
                metadata_json={},
            ),
            KnowledgeItem(
                id=new_id(),
                mine_id=mine.id,
                code="K-CASE-001",
                title="调节风门状态异常处置案例",
                knowledge_type="事故案例",
                category="通风专业",
                source_type="manual",
                source_ref="2024-026",
                content="视频判断风门未闭合但传感器反馈正常时，应优先现场复核闭锁装置、密封状态和传感器安装位置。",
                summary="说明视频与传感器冲突时的联合核查方法。",
                tags=["风门", "漏风", "视频识别"],
                is_verified=True,
                verified_by="通防专家",
                metadata_json={},
            ),
            KnowledgeItem(
                id=new_id(),
                mine_id=mine.id,
                code="K-RULE-001",
                title="局部通风机循环风判定要点",
                knowledge_type="专家经验",
                category="通风专业",
                source_type="expert",
                source_ref="专家规则 R-021",
                content="判定循环风应结合吸风口瓦斯浓度、风筒出口距离、巷道风流方向和风量变化综合判断。",
                summary="局部通风机循环风多因素判定方法。",
                tags=["局部通风", "循环风"],
                is_verified=True,
                verified_by="通防专家",
                metadata_json={},
            ),
        ]
    )

    db.add_all(
        [
            DiagnosisRule(
                id=new_id(),
                mine_id=mine.id,
                code="R-GAS-001",
                name="瓦斯浓度超限",
                scene="灾害防治",
                defect_type="瓦斯超限",
                severity="high",
                condition={"sensor_type": "gas", "operator": ">=", "field": "alarm_threshold"},
                threshold={"value": 0.8, "unit": "%CH4"},
                output_template="监测点瓦斯浓度超过报警阈值。",
                recommendation="停止作业、切断电源、撤出人员并核查通风系统。",
                knowledge_refs=["K-REG-001"],
            ),
            DiagnosisRule(
                id=new_id(),
                mine_id=mine.id,
                code="R-AF-001",
                name="工作面风量不足",
                scene="风量分配",
                defect_type="风量不足",
                severity="medium",
                condition={"actual_ratio": "<", "value": 0.9},
                threshold={"ratio": 0.9},
                output_template="实际风量低于计划风量的 90%。",
                recommendation="核查风窗、漏风点和巷道阻力，调整后复测。",
            ),
            DiagnosisRule(
                id=new_id(),
                mine_id=mine.id,
                code="R-FAC-001",
                name="通风设施状态异常",
                scene="通风设施",
                defect_type="设施状态异常",
                severity="medium",
                condition={"facility_status": "!=", "value": "normal"},
                threshold={},
                output_template="设施传感器或视频状态异常。",
                recommendation="安排现场复核设施开闭状态和传感器工作状态。",
            ),
        ]
    )

    indicator_rows = [
        ("SAFETY", "安全性", "安全性", 0.3, 1),
        ("RELIABILITY", "可靠性", "可靠性", 0.2, 2),
        ("ECONOMY", "经济性", "经济性", 0.15, 3),
        ("MANAGEMENT", "管理合规", "管理合规", 0.2, 4),
        ("DATA_QUALITY", "数据质量", "数据质量", 0.15, 5),
    ]
    for code, name, dimension, weight, sort_order in indicator_rows:
        db.add(
            EvaluationIndicator(
                id=new_id(),
                mine_id=mine.id,
                code=code,
                name=name,
                dimension=dimension,
                indicator_type="composite",
                weight=weight,
                weight_method="static",
                enabled=True,
                sort_order=sort_order,
                config={},
            )
        )

    alarm = Alarm(
        id=new_id(),
        mine_id=mine.id,
        sensor_id=gas_sensor.id,
        alarm_no="AL-20261008-001",
        alarm_type="gas_over_limit",
        level="high",
        title="N1202 回风巷瓦斯浓度超限",
        content="瓦斯浓度达到 0.86%，超过 0.80% 报警阈值。",
        value=0.86,
        threshold=0.8,
        status="open",
        occurred_at=now - timedelta(minutes=12),
        metadata_json={},
    )
    db.add(alarm)
    db.flush()
    order = WorkOrder(
        id=new_id(),
        mine_id=mine.id,
        alarm_id=alarm.id,
        order_no="WO-20261008-001",
        title="N1202 回风巷瓦斯超限处置",
        content="核查局部通风机、风筒和回风系统运行状态，完成复测后闭环。",
        priority="high",
        status="processing",
        assignee_name="王强",
        department="通防科",
        due_at=now + timedelta(hours=2),
        attachments=[],
        metadata_json={},
    )
    db.add(order)
    db.flush()
    db.add(
        WorkOrderEvent(
            id=new_id(),
            work_order_id=order.id,
            event_type="created",
            to_status="processing",
            operator_name="系统",
            comment="根据瓦斯超限告警自动创建",
            attachments=[],
            created_at=now - timedelta(minutes=10),
        )
    )
    db.commit()