create extension if not exists pgcrypto;

create table if not exists model_groups (
    id varchar(64) primary key,
    code varchar(128) not null unique,
    name varchar(128) not null,
    model_type varchar(32) not null,
    description text not null,
    input_modalities jsonb not null default '[]'::jsonb,
    output_contract_version varchar(32) not null default '1.0',
    owner_team varchar(128) not null,
    enabled boolean not null default true,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

alter table model_versions
    add column if not exists display_name varchar(128),
    add column if not exists release_notes text,
    add column if not exists dataset_version varchar(64),
    add column if not exists runtime_requirements jsonb not null default '{}'::jsonb,
    add column if not exists created_by varchar(128) not null default '算法平台';

create unique index if not exists uk_model_versions_group_version
    on model_versions(model_group, version);

create table if not exists model_artifacts (
    id varchar(64) primary key,
    model_version_id varchar(64) not null references model_versions(id),
    artifact_type varchar(32) not null,
    object_key text not null,
    sha256 char(64) not null,
    size_bytes bigint not null,
    runtime_constraints jsonb not null default '{}'::jsonb,
    validation_status varchar(32) not null,
    validation_report jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now()
);

create table if not exists model_parameter_schemas (
    id varchar(64) primary key,
    model_group_id varchar(64) not null references model_groups(id),
    model_version_id varchar(64) references model_versions(id),
    schema_version varchar(32) not null,
    json_schema jsonb not null,
    ui_schema jsonb not null,
    active boolean not null default true,
    created_at timestamptz not null default now()
);

create unique index if not exists uk_parameter_schema_active
    on model_parameter_schemas(model_group_id, schema_version);

create table if not exists model_parameter_profiles (
    id varchar(64) primary key,
    model_group_id varchar(64) not null references model_groups(id),
    model_version_id varchar(64) not null references model_versions(id),
    name varchar(128) not null,
    revision int not null,
    scope_type varchar(32) not null,
    scope_value jsonb not null default '{}'::jsonb,
    parameters jsonb not null,
    based_on_id varchar(64) references model_parameter_profiles(id),
    status varchar(32) not null,
    change_reason text,
    created_by varchar(128) not null,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create unique index if not exists uk_parameter_profile_revision
    on model_parameter_profiles(model_group_id, name, revision);

create table if not exists model_samples (
    id varchar(64) primary key,
    name varchar(256) not null,
    model_group_id varchar(64) not null references model_groups(id),
    modality varchar(32) not null,
    storage_url text not null,
    thumbnail_url text,
    line_id varchar(64),
    mileage varchar(64),
    scene varchar(128) not null,
    source varchar(64) not null,
    label_status varchar(32) not null,
    split_type varchar(32) not null,
    quality_status varchar(32) not null,
    difficulty_tags jsonb not null default '[]'::jsonb,
    metadata jsonb not null default '{}'::jsonb,
    ground_truth jsonb not null default '{}'::jsonb,
    sha256 char(64) not null,
    created_at timestamptz not null default now()
);

create index if not exists idx_model_samples_search
    on model_samples(model_group_id, modality, label_status, quality_status);

create table if not exists test_upload_sessions (
    id varchar(64) primary key,
    model_group_id varchar(64) not null references model_groups(id),
    status varchar(32) not null,
    file_count int not null default 0,
    total_size_bytes bigint not null default 0,
    object_prefix text not null,
    created_by varchar(128) not null,
    expires_at timestamptz not null,
    completed_at timestamptz,
    cleanup_status varchar(32) not null default 'pending',
    created_at timestamptz not null default now()
);

create table if not exists test_upload_files (
    id varchar(64) primary key,
    session_id varchar(64) not null references test_upload_sessions(id) on delete cascade,
    file_name varchar(512) not null,
    content_type varchar(128),
    modality varchar(32) not null,
    size_bytes bigint not null,
    sha256 char(64) not null,
    object_key text not null,
    storage_url text not null,
    metadata jsonb not null default '{}'::jsonb,
    validation_status varchar(32) not null,
    validation_messages jsonb not null default '[]'::jsonb,
    created_at timestamptz not null default now()
);

create index if not exists idx_test_upload_files_session
    on test_upload_files(session_id, created_at);

create table if not exists model_test_tasks (
    id varchar(64) primary key,
    task_no varchar(64) not null unique,
    name varchar(256) not null,
    test_type varchar(32) not null,
    model_group_id varchar(64) not null references model_groups(id),
    status varchar(32) not null,
    current_step varchar(64),
    input_snapshot jsonb not null,
    matrix_snapshot jsonb not null,
    options jsonb not null default '{}'::jsonb,
    acceptance_rule_version varchar(32),
    progress numeric(5,2) not null default 0,
    trace_id varchar(64) not null,
    cancel_requested boolean not null default false,
    error_message text,
    created_by varchar(128) not null,
    created_at timestamptz not null default now(),
    started_at timestamptz,
    finished_at timestamptz
);

create index if not exists idx_model_test_tasks_query
    on model_test_tasks(model_group_id, status, created_at desc);

create table if not exists model_test_inputs (
    id varchar(64) primary key,
    test_task_id varchar(64) not null references model_test_tasks(id) on delete cascade,
    source_type varchar(32) not null,
    sample_id varchar(64),
    upload_file_id varchar(64),
    name varchar(512) not null,
    storage_url text not null,
    sha256 char(64) not null,
    modality varchar(32) not null,
    metadata_snapshot jsonb not null default '{}'::jsonb,
    ground_truth_snapshot jsonb not null default '{}'::jsonb,
    validation_status varchar(32) not null,
    validation_messages jsonb not null default '[]'::jsonb,
    created_at timestamptz not null default now()
);

create index if not exists idx_model_test_inputs_task
    on model_test_inputs(test_task_id, created_at);

create table if not exists model_test_result_items (
    id varchar(64) primary key,
    test_task_id varchar(64) not null references model_test_tasks(id) on delete cascade,
    test_input_id varchar(64) not null references model_test_inputs(id),
    model_version_id varchar(64) not null references model_versions(id),
    parameter_profile_id varchar(64),
    parameter_snapshot jsonb not null,
    status varchar(32) not null,
    prediction jsonb not null default '{}'::jsonb,
    metrics jsonb not null default '{}'::jsonb,
    artifacts jsonb not null default '{}'::jsonb,
    runtime_snapshot jsonb not null default '{}'::jsonb,
    timings jsonb not null default '{}'::jsonb,
    error_code varchar(64),
    error_detail jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now(),
    completed_at timestamptz
);

create unique index if not exists uk_model_test_result_combination
    on model_test_result_items(test_task_id, test_input_id, model_version_id, coalesce(parameter_profile_id, ''));

create index if not exists idx_model_test_result_task
    on model_test_result_items(test_task_id, status, created_at);

create table if not exists model_test_summaries (
    id varchar(64) primary key,
    test_task_id varchar(64) not null references model_test_tasks(id) on delete cascade,
    model_version_id varchar(64) not null references model_versions(id),
    parameter_profile_id varchar(64),
    metrics jsonb not null,
    class_metrics jsonb not null default '{}'::jsonb,
    baseline_delta jsonb not null default '{}'::jsonb,
    acceptance_status varchar(32) not null,
    acceptance_details jsonb not null default '[]'::jsonb,
    conclusion text,
    report_object_key text,
    metric_calculator_version varchar(32) not null,
    created_at timestamptz not null default now()
);

create index if not exists idx_model_test_summaries_task
    on model_test_summaries(test_task_id, created_at);

create table if not exists model_test_events (
    id varchar(64) primary key,
    test_task_id varchar(64) not null references model_test_tasks(id) on delete cascade,
    sequence_no bigint not null,
    event_type varchar(64) not null,
    step_key varchar(64),
    status varchar(32) not null,
    progress numeric(5,2) not null,
    message text not null,
    payload jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now()
);

create unique index if not exists uk_model_test_event_sequence
    on model_test_events(test_task_id, sequence_no);

create table if not exists model_acceptance_rules (
    id varchar(64) primary key,
    model_group_id varchar(64) not null references model_groups(id),
    version varchar(32) not null,
    rules jsonb not null,
    active boolean not null default true,
    created_at timestamptz not null default now()
);

create unique index if not exists uk_model_acceptance_rule_version
    on model_acceptance_rules(model_group_id, version);

create table if not exists model_release_records (
    id varchar(64) primary key,
    release_no varchar(64) not null unique,
    model_group_id varchar(64) not null references model_groups(id),
    model_version_id varchar(64) not null references model_versions(id),
    parameter_profile_id varchar(64) not null references model_parameter_profiles(id),
    evaluation_task_id varchar(64) references model_test_tasks(id),
    status varchar(32) not null,
    gray_strategy jsonb not null default '{}'::jsonb,
    gray_percent int not null default 0,
    applicant varchar(128) not null,
    approver varchar(128),
    approval_comment text,
    rollback_version_id varchar(64),
    rollback_profile_id varchar(64),
    created_at timestamptz not null default now(),
    approved_at timestamptz,
    effective_at timestamptz,
    updated_at timestamptz not null default now()
);

create index if not exists idx_model_release_query
    on model_release_records(model_group_id, created_at desc);

create table if not exists model_release_events (
    id varchar(64) primary key,
    release_id varchar(64) not null references model_release_records(id) on delete cascade,
    action varchar(64) not null,
    from_status varchar(32),
    to_status varchar(32) not null,
    operator_name varchar(128) not null,
    comment text,
    payload jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now()
);

create table if not exists model_test_feedback (
    id varchar(64) primary key,
    result_item_id varchar(64) not null references model_test_result_items(id),
    feedback_type varchar(64) not null,
    comment text,
    archive_as_sample boolean not null default false,
    created_by varchar(128) not null,
    created_at timestamptz not null default now()
);

create table if not exists model_audit_logs (
    id varchar(64) primary key,
    entity_type varchar(64) not null,
    entity_id varchar(64) not null,
    action varchar(64) not null,
    operator_name varchar(128) not null,
    before_snapshot jsonb not null default '{}'::jsonb,
    after_snapshot jsonb not null default '{}'::jsonb,
    reason text,
    created_at timestamptz not null default now()
);

insert into model_groups(id, code, name, model_type, description, input_modalities, owner_team) values
('group-vision-detector', 'vision-detector', '可见光目标检测', 'detection', '识别轨道沿线异物、人员、设备缺陷和施工风险目标', '["image","video"]', '视觉算法组'),
('group-vision-segmenter', 'vision-segmenter', '场景语义分割', 'segmentation', '提取裂缝、水毁、堆土、边坡和沟渠等像素级区域', '["image","video"]', '视觉算法组'),
('group-thermal-analyzer', 'thermal-analyzer', '红外温度分析', 'thermal', '完成电力设备热点检测、温度补偿和相对温差分析', '["thermal"]', '多模态算法组'),
('group-pointcloud-analyzer', 'pointcloud-analyzer', '点云形变分析', 'pointcloud', '执行点云滤波、配准、空间测量和形变量计算', '["pointcloud"]', '三维算法组'),
('group-change-detector', 'change-detector', '栅格变化检测', 'change', '对齐多时相影像与高程数据并识别新增和变化区域', '["image","raster"]', '遥感算法组')
on conflict (id) do update set
    name=excluded.name,
    model_type=excluded.model_type,
    description=excluded.description,
    input_modalities=excluded.input_modalities,
    owner_team=excluded.owner_team,
    updated_at=now();

update model_versions set
    display_name=case model_group
        when 'vision-detector' then '可见光检测生产基线'
        when 'vision-segmenter' then '场景分割生产基线'
        when 'thermal-analyzer' then '红外分析生产基线'
        when 'pointcloud-analyzer' then '点云分析生产基线'
        when 'change-detector' then '变化检测生产基线'
        else model_group || ' ' || version end,
    release_notes=coalesce(release_notes, '首个稳定生产版本'),
    dataset_version=coalesce(dataset_version, 'rail-dataset-v1.0'),
    runtime_requirements='{"python":"3.12","runtime":"ONNX Runtime / TensorRT","cuda":">=12.0","memory_gb":8}'::jsonb
where display_name is null;

insert into model_versions(id, model_group, version, status, scenes, metrics, display_name, release_notes, dataset_version, runtime_requirements, created_by) values
('model-vision-detector-v110', 'vision-detector', 'v1.1.0', 'candidate', '["违法开挖","塔吊","人员入侵","动物防控","鸟巢","信号机破损倾斜"]', '{"precision":0.932,"recall":0.901,"f1":0.916,"map50":0.925}', '可见光检测候选版本', '补充夜间与雨雾难例，优化小目标召回', 'rail-visible-v1.4', '{"python":"3.12","runtime":"TensorRT","cuda":">=12.0","memory_gb":8}', '视觉算法组'),
('model-vision-segmenter-v110', 'vision-segmenter', 'v1.1.0', 'candidate', '["深基坑隐患","违规堆土","裂缝识别","水毁核查","沟渠疏通"]', '{"miou":0.824,"dice":0.861,"boundary_f1":0.806}', '场景分割候选版本', '优化细长裂缝边界和积水区域完整性', 'rail-segment-v1.3', '{"python":"3.12","runtime":"TensorRT","cuda":">=12.0","memory_gb":10}', '视觉算法组'),
('model-thermal-v110', 'thermal-analyzer', 'v1.1.0', 'candidate', '["接头严重发热","杆上设备发热"]', '{"temperature_error_c":1.4,"hotspot_recall":0.94}', '红外分析候选版本', '增加环境补偿参数并校准热点聚合', 'rail-thermal-v1.2', '{"python":"3.12","runtime":"ONNX Runtime","cuda":">=12.0","memory_gb":6}', '多模态算法组'),
('model-pointcloud-v110', 'pointcloud-analyzer', 'v1.1.0', 'candidate', '["防洪点精密监测","危树侵限"]', '{"registration_error_m":0.045,"anomaly_recall":0.91}', '点云分析候选版本', '优化稀疏点云配准与轨旁目标距离测量', 'rail-pointcloud-v1.3', '{"python":"3.12","runtime":"Open3D/PCL","cuda":">=12.0","memory_gb":16}', '三维算法组'),
('model-change-v110', 'change-detector', 'v1.1.0', 'candidate', '["彩钢板清理","塔吊","防洪点精密监测"]', '{"precision":0.913,"recall":0.889,"iou":0.847}', '变化检测候选版本', '提升多时相影像配准稳定性与小区域过滤', 'rail-change-v1.2', '{"python":"3.12","runtime":"ONNX Runtime/GDAL","cuda":">=12.0","memory_gb":12}', '遥感算法组')
on conflict (id) do nothing;

insert into model_artifacts(id, model_version_id, artifact_type, object_key, sha256, size_bytes, runtime_constraints, validation_status, validation_report)
select 'artifact-' || id, id,
       case when model_group='pointcloud-analyzer' then 'Open3D' else case when status='production' then 'ONNX' else 'TensorRT' end end,
       'models/' || model_group || '/' || version || '/model.bin',
       encode(sha256((model_group || ':' || version)::bytea), 'hex'),
       case when model_group='pointcloud-analyzer' then 188743680 else 94371840 end,
       runtime_requirements,
       'passed',
       '{"checksum":true,"contract":true,"runtime":true,"smoke_test":true}'::jsonb
from model_versions
on conflict (id) do nothing;

insert into model_parameter_schemas(id, model_group_id, schema_version, json_schema, ui_schema) values
('schema-vision-detector-v1', 'group-vision-detector', '1.0',
 '{"type":"object","properties":{"confidence_threshold":{"type":"number","title":"置信度阈值","minimum":0.05,"maximum":0.99,"multipleOf":0.01,"default":0.55},"nms_iou_threshold":{"type":"number","title":"NMS IoU 阈值","minimum":0.1,"maximum":0.9,"multipleOf":0.01,"default":0.45},"max_detections":{"type":"integer","title":"最大检测数量","minimum":1,"maximum":500,"default":100},"input_size":{"type":"integer","title":"输入尺寸","enum":[640,960,1280],"default":960},"precision":{"type":"string","title":"计算精度","enum":["FP32","FP16","INT8"],"default":"FP16"},"enable_tiling":{"type":"boolean","title":"启用切片推理","default":true},"tile_overlap":{"type":"number","title":"切片重叠率","minimum":0.05,"maximum":0.5,"multipleOf":0.01,"default":0.2},"save_intermediate":{"type":"boolean","title":"保存中间产物","default":false}},"required":["confidence_threshold","nms_iou_threshold","input_size","precision"]}',
 '{"groups":[{"title":"检测控制","fields":["confidence_threshold","nms_iou_threshold","max_detections"]},{"title":"输入与性能","fields":["input_size","precision","enable_tiling","tile_overlap","save_intermediate"]}]}'::jsonb),
('schema-vision-segmenter-v1', 'group-vision-segmenter', '1.0',
 '{"type":"object","properties":{"mask_threshold":{"type":"number","title":"掩码阈值","minimum":0.05,"maximum":0.95,"multipleOf":0.01,"default":0.5},"minimum_area":{"type":"integer","title":"最小区域面积","minimum":4,"maximum":10000,"default":64},"fill_holes":{"type":"boolean","title":"填充区域孔洞","default":true},"contour_simplification":{"type":"number","title":"轮廓简化系数","minimum":0,"maximum":5,"multipleOf":0.1,"default":0.8},"input_size":{"type":"integer","title":"输入尺寸","enum":[640,1024,1280],"default":1024},"precision":{"type":"string","title":"计算精度","enum":["FP32","FP16"],"default":"FP16"},"save_intermediate":{"type":"boolean","title":"保存中间产物","default":true}},"required":["mask_threshold","minimum_area","input_size","precision"]}',
 '{"groups":[{"title":"分割后处理","fields":["mask_threshold","minimum_area","fill_holes","contour_simplification"]},{"title":"输入与性能","fields":["input_size","precision","save_intermediate"]}]}'::jsonb),
('schema-thermal-v1', 'group-thermal-analyzer', '1.0',
 '{"type":"object","properties":{"emissivity":{"type":"number","title":"发射率","minimum":0.1,"maximum":1,"multipleOf":0.01,"default":0.95},"ambient_temperature_c":{"type":"number","title":"环境温度","minimum":-50,"maximum":80,"multipleOf":0.1,"default":25},"reflected_temperature_c":{"type":"number","title":"反射温度","minimum":-50,"maximum":100,"multipleOf":0.1,"default":25},"distance_m":{"type":"number","title":"拍摄距离","minimum":0.5,"maximum":500,"multipleOf":0.5,"default":30},"humidity_percent":{"type":"integer","title":"相对湿度","minimum":0,"maximum":100,"default":60},"hotspot_threshold_c":{"type":"number","title":"热点温度阈值","minimum":20,"maximum":200,"multipleOf":0.5,"default":70},"relative_rise_threshold_c":{"type":"number","title":"相对温升阈值","minimum":1,"maximum":100,"multipleOf":0.5,"default":20}},"required":["emissivity","ambient_temperature_c","distance_m","hotspot_threshold_c"]}',
 '{"groups":[{"title":"辐射补偿","fields":["emissivity","ambient_temperature_c","reflected_temperature_c","distance_m","humidity_percent"]},{"title":"热点规则","fields":["hotspot_threshold_c","relative_rise_threshold_c"]}]}'::jsonb),
('schema-pointcloud-v1', 'group-pointcloud-analyzer', '1.0',
 '{"type":"object","properties":{"voxel_size_m":{"type":"number","title":"体素尺寸","minimum":0.01,"maximum":1,"multipleOf":0.01,"default":0.08},"outlier_neighbors":{"type":"integer","title":"离群点邻域数量","minimum":4,"maximum":100,"default":20},"outlier_std_ratio":{"type":"number","title":"离群点标准差倍数","minimum":0.5,"maximum":5,"multipleOf":0.1,"default":2},"icp_max_distance_m":{"type":"number","title":"ICP 最大匹配距离","minimum":0.05,"maximum":5,"multipleOf":0.05,"default":0.5},"icp_iterations":{"type":"integer","title":"ICP 迭代次数","minimum":10,"maximum":500,"default":80},"registration_error_limit_m":{"type":"number","title":"配准误差上限","minimum":0.01,"maximum":1,"multipleOf":0.01,"default":0.1},"color_mode":{"type":"string","title":"默认着色方式","enum":["height","intensity","classification","distance"],"default":"distance"}},"required":["voxel_size_m","icp_max_distance_m","icp_iterations","registration_error_limit_m"]}',
 '{"groups":[{"title":"滤波处理","fields":["voxel_size_m","outlier_neighbors","outlier_std_ratio"]},{"title":"点云配准","fields":["icp_max_distance_m","icp_iterations","registration_error_limit_m"]},{"title":"结果查看","fields":["color_mode"]}]}'::jsonb),
('schema-change-v1', 'group-change-detector', '1.0',
 '{"type":"object","properties":{"target_resolution_m":{"type":"number","title":"目标分辨率","minimum":0.01,"maximum":2,"multipleOf":0.01,"default":0.1},"registration_precision_m":{"type":"number","title":"配准精度要求","minimum":0.01,"maximum":2,"multipleOf":0.01,"default":0.2},"change_threshold":{"type":"number","title":"变化阈值","minimum":0.01,"maximum":1,"multipleOf":0.01,"default":0.25},"minimum_area_m2":{"type":"number","title":"最小变化面积","minimum":0.1,"maximum":1000,"multipleOf":0.1,"default":2},"morphology_kernel":{"type":"integer","title":"形态学核尺寸","enum":[1,3,5,7,9],"default":3},"precision":{"type":"string","title":"计算精度","enum":["FP32","FP16"],"default":"FP16"},"save_intermediate":{"type":"boolean","title":"保存差分栅格","default":true}},"required":["target_resolution_m","registration_precision_m","change_threshold","minimum_area_m2"]}',
 '{"groups":[{"title":"对齐与差分","fields":["target_resolution_m","registration_precision_m","change_threshold"]},{"title":"区域过滤","fields":["minimum_area_m2","morphology_kernel"]},{"title":"性能与产物","fields":["precision","save_intermediate"]}]}'::jsonb)
on conflict (id) do nothing;

insert into model_parameter_profiles(id, model_group_id, model_version_id, name, revision, scope_type, scope_value, parameters, status, change_reason, created_by)
select 'profile-' || mv.model_group || '-' || replace(mv.version, '.', ''),
       mg.id,
       mv.id,
       case when mv.status='production' then '生产基线' else '候选评估' end,
       1,
       case when mv.status='production' then 'production_snapshot' else 'test' end,
       '{}'::jsonb,
       case mv.model_group
           when 'vision-detector' then '{"confidence_threshold":0.55,"nms_iou_threshold":0.45,"max_detections":100,"input_size":960,"precision":"FP16","enable_tiling":true,"tile_overlap":0.2,"save_intermediate":false}'::jsonb
           when 'vision-segmenter' then '{"mask_threshold":0.5,"minimum_area":64,"fill_holes":true,"contour_simplification":0.8,"input_size":1024,"precision":"FP16","save_intermediate":true}'::jsonb
           when 'thermal-analyzer' then '{"emissivity":0.95,"ambient_temperature_c":25,"reflected_temperature_c":25,"distance_m":30,"humidity_percent":60,"hotspot_threshold_c":70,"relative_rise_threshold_c":20}'::jsonb
           when 'pointcloud-analyzer' then '{"voxel_size_m":0.08,"outlier_neighbors":20,"outlier_std_ratio":2.0,"icp_max_distance_m":0.5,"icp_iterations":80,"registration_error_limit_m":0.1,"color_mode":"distance"}'::jsonb
           else '{"target_resolution_m":0.1,"registration_precision_m":0.2,"change_threshold":0.25,"minimum_area_m2":2.0,"morphology_kernel":3,"precision":"FP16","save_intermediate":true}'::jsonb
       end,
       case when mv.status='production' then 'locked' else 'active' end,
       case when mv.status='production' then '随生产版本固化' else '候选版本回归配置' end,
       mv.created_by
from model_versions mv
join model_groups mg on mg.code=mv.model_group
on conflict (id) do nothing;

insert into model_samples(id, name, model_group_id, modality, storage_url, line_id, mileage, scene, source, label_status, split_type, quality_status, difficulty_tags, metadata, ground_truth, sha256) values
('ms-visible-001', '轨道沿线塔吊雨天样本', 'group-vision-detector', 'image', 's3://rail-inspection/model-samples/visible/tower-crane-rain.jpg', '京广线', 'K123+450', '塔吊', '巡检回流', 'reviewed', 'test', 'qualified', '["雨天","小目标"]', '{"width":1920,"height":1080,"weather":"rain","longitude":116.101,"latitude":39.101,"distance_to_track_m":82.4}', '{"objects":[{"category":"塔吊","bbox":[0.58,0.18,0.24,0.62]}]}', repeat('1',64)),
('ms-visible-002', '接触网异物夜间样本', 'group-vision-detector', 'image', 's3://rail-inspection/model-samples/visible/contact-object-night.jpg', '京沪线', 'K087+120', '接触网异物', '告警回流', 'reviewed', 'test', 'qualified', '["夜间","低对比度"]', '{"width":2560,"height":1440,"weather":"clear","longitude":116.112,"latitude":39.108,"distance_to_track_m":3.2}', '{"objects":[{"category":"塑料薄膜","bbox":[0.42,0.31,0.18,0.16]}]}', repeat('2',64)),
('ms-visible-003', '轨旁人员入侵视频片段', 'group-vision-detector', 'video', 's3://rail-inspection/model-samples/video/person-intrusion.mp4', '京广线', 'K126+800', '人员入侵', '工单回流', 'reviewed', 'test', 'qualified', '["遮挡","运动模糊"]', '{"duration_s":18,"fps":25,"width":1920,"height":1080,"longitude":116.124,"latitude":39.115}', '{"events":[{"category":"人员","start_s":4.2,"end_s":13.6}]}', repeat('3',64)),
('ms-segment-001', '桥梁裂缝近景样本', 'group-vision-segmenter', 'image', 's3://rail-inspection/model-samples/segment/bridge-crack.jpg', '京沪线', 'K091+650', '裂缝识别', '巡检回流', 'reviewed', 'test', 'qualified', '["细长目标","复杂纹理"]', '{"width":2048,"height":1536,"pixel_scale_mm":0.32,"longitude":116.132,"latitude":39.121}', '{"masks":[{"category":"裂缝","area_px":18420,"width_cm":3.1,"length_cm":61.5}]}', repeat('4',64)),
('ms-segment-002', '路基水毁区域样本', 'group-vision-segmenter', 'image', 's3://rail-inspection/model-samples/segment/flood-damage.jpg', '京广线', 'K130+050', '水毁核查', '人工导入', 'reviewed', 'test', 'qualified', '["反光","边界模糊"]', '{"width":4096,"height":3072,"longitude":116.144,"latitude":39.127}', '{"masks":[{"category":"路基漫水","area_px":208320}]}', repeat('5',64)),
('ms-thermal-001', '接头高温辐射图', 'group-thermal-analyzer', 'thermal', 's3://rail-inspection/model-samples/thermal/hot-joint.tiff', '京沪线', 'K095+230', '接头严重发热', '告警回流', 'reviewed', 'test', 'qualified', '["强热源"]', '{"radiometric":true,"camera":"H20T","ambient_temperature_c":27.2,"distance_m":32,"temperature_c":86.3,"longitude":116.151,"latitude":39.134}', '{"hotspots":[{"x":0.61,"y":0.42,"temperature_c":85.1}]}', repeat('6',64)),
('ms-thermal-002', '杆上设备温差样本', 'group-thermal-analyzer', 'thermal', 's3://rail-inspection/model-samples/thermal/pole-device.tiff', '京广线', 'K136+780', '杆上设备发热', '巡检回流', 'reviewed', 'test', 'qualified', '["远距离"]', '{"radiometric":true,"camera":"M30T","ambient_temperature_c":24.5,"distance_m":48,"temperature_c":78.8,"longitude":116.163,"latitude":39.141}', '{"hotspots":[{"x":0.48,"y":0.36,"temperature_c":79.4}]}', repeat('7',64)),
('ms-point-001', '防洪重点点云当前期', 'group-pointcloud-analyzer', 'pointcloud', 's3://rail-inspection/model-samples/pointcloud/flood-current.laz', '京广线', 'K141+500', '防洪点精密监测', '人工导入', 'reviewed', 'test', 'qualified', '["稀疏边缘"]', '{"point_count":1840000,"crs":"EPSG:4547","unit":"m","volume_change_m3":1.82,"max_height_diff_m":0.31,"longitude":116.174,"latitude":39.148}', '{"registration_error_m":0.05,"volume_change_m3":1.75}', repeat('8',64)),
('ms-point-002', '危树侵限点云样本', 'group-pointcloud-analyzer', 'pointcloud', 's3://rail-inspection/model-samples/pointcloud/tree-clearance.pcd', '京沪线', 'K102+310', '危树侵限', '工单回流', 'reviewed', 'test', 'qualified', '["植被密集"]', '{"point_count":920000,"crs":"EPSG:4547","unit":"m","distance_to_contact_network_m":3.4,"longitude":116.182,"latitude":39.155}', '{"distance_to_contact_network_m":3.5,"tree_height_m":12.1}', repeat('9',64)),
('ms-change-001', '防洪点多时相高程差分', 'group-change-detector', 'raster', 's3://rail-inspection/model-samples/raster/flood-diff.tif', '京广线', 'K141+500', '防洪点精密监测', '人工导入', 'reviewed', 'test', 'qualified', '["季节变化"]', '{"width":4096,"height":4096,"crs":"EPSG:4547","resolution_m":0.1,"baseline":"2026-06-15","longitude":116.174,"latitude":39.148}', '{"regions":[{"category":"体积变化","area_m2":32.4}]}', repeat('a',64)),
('ms-change-002', '彩钢板新增区域影像', 'group-change-detector', 'image', 's3://rail-inspection/model-samples/change/new-sheeting.jpg', '京沪线', 'K108+920', '彩钢板清理', '巡检回流', 'reviewed', 'test', 'qualified', '["轻微视角差"]', '{"width":3840,"height":2160,"baseline":"2026-07-01","longitude":116.193,"latitude":39.162}', '{"regions":[{"category":"彩钢板","polygon":[[0.22,0.46],[0.41,0.43],[0.44,0.62],[0.24,0.65]]}]}', repeat('b',64)),
('ms-change-003', '塔吊建设变化样本', 'group-change-detector', 'image', 's3://rail-inspection/model-samples/change/tower-change.jpg', '京广线', 'K149+050', '塔吊', '告警回流', 'reviewed', 'test', 'qualified', '["小区域","阴影"]', '{"width":4096,"height":3072,"baseline":"2026-06-20","longitude":116.205,"latitude":39.169}', '{"regions":[{"category":"塔吊","area_m2":18.6}]}', repeat('c',64))
on conflict (id) do nothing;

insert into model_acceptance_rules(id, model_group_id, version, rules)
select 'rule-' || code || '-v1', id, '1.0',
       case model_type
           when 'detection' then '{"precision":{"min":0.88},"recall":{"min":0.85},"f1":{"min":0.86},"p95_latency_ms":{"max":800},"failure_rate":{"max":0.02}}'::jsonb
           when 'segmentation' then '{"miou":{"min":0.76},"dice":{"min":0.80},"p95_latency_ms":{"max":1200},"failure_rate":{"max":0.02}}'::jsonb
           when 'thermal' then '{"temperature_error_c":{"max":2.5},"hotspot_recall":{"min":0.88},"p95_latency_ms":{"max":900},"failure_rate":{"max":0.02}}'::jsonb
           when 'pointcloud' then '{"registration_error_m":{"max":0.10},"anomaly_recall":{"min":0.85},"p95_latency_ms":{"max":5000},"failure_rate":{"max":0.05}}'::jsonb
           else '{"precision":{"min":0.85},"recall":{"min":0.82},"iou":{"min":0.78},"p95_latency_ms":{"max":3000},"failure_rate":{"max":0.03}}'::jsonb
       end
from model_groups
on conflict (id) do nothing;

insert into model_release_records(id, release_no, model_group_id, model_version_id, parameter_profile_id, status, gray_strategy, gray_percent, applicant, approver, approval_comment, created_at, approved_at, effective_at, updated_at)
select 'release-' || mg.code || '-v100',
       'REL-' || upper(replace(mg.code, '-', '')) || '-100',
       mg.id,
       mv.id,
       mp.id,
       'production',
       '{"type":"traffic","scope":"all"}'::jsonb,
       100,
       mv.created_by,
       '发布审批人',
       '生产基线版本',
       mv.created_at,
       mv.created_at,
       mv.created_at,
       mv.created_at
from model_groups mg
join model_versions mv on mv.model_group=mg.code and mv.status='production'
join model_parameter_profiles mp on mp.model_version_id=mv.id and mp.scope_type='production_snapshot'
on conflict (id) do nothing;
