package com.ai.trackwalker.service;

import com.ai.trackwalker.api.dto.Requests;
import com.ai.trackwalker.capability.uav.InspectionTaskWorkflow;
import com.ai.trackwalker.common.Ids;
import com.ai.trackwalker.common.Jsonb;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.kafka.core.KafkaTemplate;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.server.ResponseStatusException;

import java.sql.Timestamp;
import java.time.Instant;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class PlatformService {
    private final JdbcTemplate jdbc;
    private final AiClient aiClient;
    private final RuleEngine ruleEngine;
    private final KafkaTemplate<String, Object> kafkaTemplate;

    public PlatformService(JdbcTemplate jdbc, AiClient aiClient, RuleEngine ruleEngine, KafkaTemplate<String, Object> kafkaTemplate) {
        this.jdbc = jdbc;
        this.aiClient = aiClient;
        this.ruleEngine = ruleEngine;
        this.kafkaTemplate = kafkaTemplate;
    }

    @Transactional
    public Map<String, Object> createTask(Requests.CreateTaskRequest request) {
        String id = Ids.next("task");
        Instant now = Instant.now();
        jdbc.update(
                "insert into inspection_tasks(id, external_task_id, line_id, mileage_start, mileage_end, route_id, scene_set, priority, status, created_at, updated_at) values (?,?,?,?,?,?,?::jsonb,?,?,?,?)",
                id,
                request.externalTaskId(),
                request.lineId(),
                request.mileageStart(),
                request.mileageEnd(),
                request.routeId(),
                Jsonb.write(request.sceneSet()),
                request.priority() == null ? "normal" : request.priority(),
                "created",
                Timestamp.from(now),
                Timestamp.from(now)
        );
        publish("inspection.task.created", Map.of("task_id", id));
        return Map.of("task_id", id, "status", "created");
    }

    public List<Map<String, Object>> listTasks() {
        List<Map<String, Object>> rows = jdbc.queryForList("""
                select t.id as task_id, t.external_task_id, t.line_id, t.mileage_start, t.mileage_end,
                       t.route_id, r.name as route_name, r.status as route_status,
                       rv.status as route_version_status,
                       t.scene_set::text as scene_set, t.priority, t.status, t.plan_id,
                       p.name as plan_name, p.trigger_type, t.task_type, t.approval_status,
                       t.object_scope::text as object_scope, t.planned_start_at, t.created_by,
                       t.version_no, t.created_at, t.updated_at,
                       m.id as mission_id, m.status as mission_status, m.progress as mission_progress,
                       m.vendor_code, m.vendor_mission_id, m.version_no as mission_version,
                       m.error_code as mission_error_code, m.error_message as mission_error_message
                from inspection_tasks t
                left join inspection_plans p on p.id=t.plan_id
                left join routes r on r.id=t.route_id
                left join route_versions rv on rv.id=r.current_version_id
                left join lateral (
                    select fm.id, fm.status, fm.progress, fm.vendor_code, fm.vendor_mission_id,
                           fm.version_no, fm.error_code, fm.error_message
                    from flight_missions fm
                    where fm.task_id=t.id
                    order by fm.created_at desc
                    limit 1
                ) m on true
                order by t.created_at desc
                """);
        for (Map<String, Object> row : rows) {
            String blockReason = InspectionTaskWorkflow.dispatchBlockReason(
                    String.valueOf(row.get("status")),
                    String.valueOf(row.get("approval_status")),
                    row.get("route_id") != null,
                    "PUBLISHED".equals(row.get("route_status")) && "PUBLISHED".equals(row.get("route_version_status")),
                    row.get("mission_id") != null
            );
            row.put("can_dispatch", blockReason == null);
            row.put("dispatch_block_reason", blockReason);
            row.put("can_cancel", InspectionTaskWorkflow.canCancel(
                    String.valueOf(row.get("status")),
                    row.get("mission_id") != null
            ));
        }
        return rows;
    }

    @Transactional
    public Map<String, Object> cancelTask(String taskId) {
        List<Map<String, Object>> rows = jdbc.queryForList("""
                select t.status, exists(select 1 from flight_missions fm where fm.task_id=t.id) as has_mission
                from inspection_tasks t
                where t.id=?
                for update
                """, taskId);
        if (rows.isEmpty()) {
            throw new ResponseStatusException(HttpStatus.NOT_FOUND, "巡检任务不存在");
        }
        Map<String, Object> task = rows.get(0);
        if (!InspectionTaskWorkflow.canCancel(
                String.valueOf(task.get("status")),
                Boolean.TRUE.equals(task.get("has_mission"))
        )) {
            throw new ResponseStatusException(HttpStatus.CONFLICT, "只有尚未下发的待执行任务可以取消");
        }
        jdbc.update("update inspection_tasks set status='cancelled', updated_at=?, version_no=version_no+1 where id=?",
                Timestamp.from(Instant.now()), taskId);
        publish("inspection.task.cancelled", Map.of("task_id", taskId));
        return Map.of("task_id", taskId, "status", "cancelled");
    }

    @Transactional
    public Map<String, Object> updateMissionStatus(Requests.MissionStatusCallback callback) {
        String status = callback.status() == null || callback.status().isBlank() ? "flying" : callback.status();
        int updated = jdbc.update("update inspection_tasks set status=?, updated_at=? where id=?",
                status, Timestamp.from(Instant.now()), callback.taskId());
        if (updated == 0) {
            throw new ResponseStatusException(HttpStatus.NOT_FOUND, "巡检任务不存在");
        }
        publish("uav.mission.status", Map.of(
                "task_id", callback.taskId(),
                "status", status,
                "vendor", callback.vendor() == null ? "unknown" : callback.vendor(),
                "uav_id", callback.uavId() == null ? "" : callback.uavId()
        ));
        return Map.of("accepted", true, "task_id", callback.taskId(), "status", status);
    }

    public List<Map<String, Object>> listResources() {
        return jdbc.queryForList("""
                select r.id as resource_id, r.task_id, r.resource_type, r.storage_url, r.capture_time,
                       r.metadata::text as metadata, r.created_at, r.quality_status, r.sha256, r.size_bytes,
                       r.mime_type, t.line_id, t.mileage_start, t.mileage_end,
                       case when exists(select 1 from ai_results ar where ar.resource_id=r.id) then 'completed' else 'pending' end as analysis_status
                from inspection_resources r
                join inspection_tasks t on t.id=r.task_id
                order by r.created_at desc
                """);
    }

    @Transactional
    public Map<String, Object> completeResources(Requests.ResourceCompleteRequest request) {
        List<String> resourceIds = ingestResources(request);
        Map<String, Object> job = createAnalysisJob(new Requests.CreateAnalysisJobRequest(request.taskId(), resourceIds, null, "offline", "normal"));
        runAnalysis(String.valueOf(job.get("analysis_job_id")));
        return Map.of("accepted", resourceIds.size(), "analysis_job_id", job.get("analysis_job_id"));
    }

    @Transactional
    public List<String> ingestResources(Requests.ResourceCompleteRequest request) {
        List<String> resourceIds = request.resources().stream().map(resource -> resource.resourceId() == null ? Ids.next("res") : resource.resourceId()).toList();
        Instant now = Instant.now();
        for (int i = 0; i < request.resources().size(); i++) {
            Requests.ResourceItem item = request.resources().get(i);
            jdbc.update(
                    "insert into inspection_resources(id, task_id, resource_type, storage_url, capture_time, metadata, created_at) values (?,?,?,?,?,?::jsonb,?)",
                    resourceIds.get(i),
                    request.taskId(),
                    item.resourceType(),
                    item.storageUrl(),
                    Timestamp.from(now),
                    Jsonb.write(item.metadata() == null ? Map.of() : item.metadata()),
                    Timestamp.from(now)
            );
        }
        jdbc.update("update inspection_tasks set status='data_uploading', updated_at=? where id=?", Timestamp.from(now), request.taskId());
        return resourceIds;
    }

    @Transactional
    public Map<String, Object> createAnalysisJob(Requests.CreateAnalysisJobRequest request) {
        Map<String, Object> task = taskById(request.taskId());
        String id = Ids.next("job");
        String sceneSet = request.sceneSet() == null ? String.valueOf(task.get("scene_set_text")) : Jsonb.write(request.sceneSet());
        List<String> resources = request.resourceIds() == null ? jdbc.queryForList("select id from inspection_resources where task_id=?", String.class, request.taskId()) : request.resourceIds();
        jdbc.update(
                "insert into analysis_jobs(id, task_id, resource_ids, scene_set, analysis_mode, status, summary, created_at) values (?,?,?::jsonb,?::jsonb,?,?,?::jsonb,?)",
                id,
                request.taskId(),
                Jsonb.write(resources),
                sceneSet,
                request.analysisMode() == null ? "offline" : request.analysisMode(),
                "queued",
                "{}",
                Timestamp.from(Instant.now())
        );
        publish("analysis.job.created", Map.of("job_id", id));
        return Map.of("analysis_job_id", id, "status", "queued");
    }

    public List<Map<String, Object>> listAnalysisJobs() {
        return jdbc.queryForList("""
                select j.id as analysis_job_id, j.task_id, j.resource_ids::text as resource_ids,
                       j.scene_set::text as scene_set, j.analysis_mode, j.status, j.summary::text as summary,
                       j.created_at, j.completed_at, t.line_id
                from analysis_jobs j
                join inspection_tasks t on t.id=j.task_id
                order by j.created_at desc
                """);
    }

    public List<Map<String, Object>> listAnalysisResults() {
        return jdbc.queryForList("""
                select r.id as ai_result_id, r.job_id, r.task_id, r.resource_id, r.scene, r.category,
                       r.confidence, r.geometry::text as geometry, r.measurements::text as measurements,
                       r.attributes::text as attributes, r.model_name, r.model_version, r.created_at,
                       ir.resource_type, ir.storage_url, t.line_id
                from ai_results r
                join inspection_resources ir on ir.id=r.resource_id
                join inspection_tasks t on t.id=r.task_id
                order by r.created_at desc
                """);
    }

    @Transactional
    public Map<String, Object> runAnalysis(String jobId) {
        Map<String, Object> job = jdbc.queryForMap("select *, resource_ids::text as resource_ids_text, scene_set::text as scene_set_text from analysis_jobs where id=?", jobId);
        Map<String, Object> task = taskById(String.valueOf(job.get("task_id")));
        List<String> resourceIds = Jsonb.stringList(String.valueOf(job.get("resource_ids_text")));
        List<String> scenes = Jsonb.stringList(String.valueOf(job.get("scene_set_text")));
        int aiResults = 0;
        int alarms = 0;
        int suppressed = 0;
        jdbc.update("update analysis_jobs set status='running' where id=?", jobId);
        for (String resourceId : resourceIds) {
            Map<String, Object> resource = resourceById(resourceId);
            List<Map<String, Object>> results = aiClient.analyze(resource, scenes);
            for (Map<String, Object> result : results) {
                String resultId = insertAiResult(jobId, task, resource, result);
                result.put("id", resultId);
                RuleDecision decision = ruleEngine.evaluate(result, task);
                boolean created = insertAlarmAndMaybeWorkOrder(task, resource, result, decision);
                aiResults++;
                if (created) {
                    alarms++;
                } else {
                    suppressed++;
                }
            }
        }
        Map<String, Object> summary = Map.of("ai_results", aiResults, "alarms", alarms, "suppressed", suppressed);
        jdbc.update("update analysis_jobs set status='completed', summary=?::jsonb, completed_at=? where id=?", Jsonb.write(summary), Timestamp.from(Instant.now()), jobId);
        jdbc.update("update inspection_tasks set status='completed', updated_at=? where id=?", Timestamp.from(Instant.now()), task.get("id"));
        return Map.of("analysis_job_id", jobId, "status", "completed", "summary", summary);
    }

    public List<Map<String, Object>> listAlarms(boolean includeSuppressed) {
        String sql = "select id as alarm_id, task_id, result_id, scene, category, severity, confidence, location::text as location, evidence::text as evidence, rule_hits::text as rule_hits, status, suppressed, suppression_reason, created_at, updated_at from alarms";
        if (!includeSuppressed) {
            sql += " where suppressed=false";
        }
        sql += " order by created_at desc";
        return jdbc.queryForList(sql);
    }

    @Transactional
    public Map<String, Object> decideAlarm(String alarmId, Requests.AlarmDecisionRequest request) {
        String action = request.action() == null ? "confirm" : request.action();
        String status;
        boolean suppressed;
        String reason = request.reason();
        switch (action) {
            case "suppress" -> {
                status = "suppressed";
                suppressed = true;
            }
            case "reopen" -> {
                status = "pending";
                suppressed = false;
            }
            default -> {
                status = "confirmed";
                suppressed = false;
            }
        }
        int updated = jdbc.update("update alarms set status=?, suppressed=?, suppression_reason=?, updated_at=? where id=?",
                status, suppressed, suppressed ? reason : null, Timestamp.from(Instant.now()), alarmId);
        if (updated == 0) {
            throw new ResponseStatusException(HttpStatus.NOT_FOUND, "告警不存在");
        }
        publish("alarm.decision.updated", Map.of(
                "alarm_id", alarmId,
                "action", action,
                "operator", request.operator() == null ? "巡检平台" : request.operator()
        ));
        return Map.of("alarm_id", alarmId, "status", status, "suppressed", suppressed);
    }

    public List<Map<String, Object>> listWorkOrders() {
        return jdbc.queryForList("""
                select
                    wo.id as workorder_id,
                    wo.alarm_id,
                    wo.status,
                    wo.assignee,
                    wo.close_result,
                    wo.comment,
                    wo.created_at,
                    wo.updated_at,
                    a.task_id,
                    a.scene,
                    a.category,
                    a.severity,
                    a.location::text as location
                from work_orders wo
                join alarms a on a.id = wo.alarm_id
                order by wo.updated_at desc
                """);
    }

    @Transactional
    public Map<String, Object> workOrderCallback(Requests.WorkOrderCallbackRequest request) {
        Map<String, Object> workorder = request.workorderId() == null || request.workorderId().isBlank()
                ? jdbc.queryForMap("select id, alarm_id from work_orders where alarm_id=?", request.alarmId())
                : jdbc.queryForMap("select id, alarm_id from work_orders where id=?", request.workorderId());
        String workorderId = String.valueOf(workorder.get("id"));
        jdbc.update("update work_orders set status=?, close_result=?, comment=?, updated_at=? where alarm_id=?",
                request.status() == null ? "closed" : request.status(),
                request.result(),
                request.comment(),
                Timestamp.from(Instant.now()),
                request.alarmId());
        String alarmStatus = "closed".equals(request.status()) || request.status() == null ? "closed" : "processing";
        jdbc.update("update alarms set status=?, updated_at=? where id=?", alarmStatus, Timestamp.from(Instant.now()), request.alarmId());
        insertWorkOrderOperation(workorderId, "status_callback", request.operator(), request.result(), request.comment());
        publish("workorder.closed", Map.of("alarm_id", request.alarmId(), "result", request.result()));
        return Map.of("workorder_id", workorderId, "alarm_id", request.alarmId(), "status", request.status() == null ? "closed" : request.status());
    }

    @Transactional
    public Map<String, Object> reviewWorkOrder(String workorderId, Requests.WorkOrderReviewRequest request) {
        Map<String, Object> workorder = jdbc.queryForMap("select id, alarm_id, status from work_orders where id=?", workorderId);
        String action = request.action() == null ? "approve" : request.action();
        String targetStatus = "return".equals(action) ? "processing" : "closed";
        Instant now = Instant.now();
        jdbc.update("update work_orders set status=?, close_result=?, comment=?, updated_at=? where id=?",
                targetStatus, request.result(), request.comment(), Timestamp.from(now), workorderId);
        jdbc.update("update alarms set status=?, updated_at=? where id=?",
                "closed".equals(targetStatus) ? "closed" : "processing", Timestamp.from(now), workorder.get("alarm_id"));
        insertWorkOrderOperation(workorderId, "return".equals(action) ? "review_returned" : "review_approved",
                request.operator(), request.result(), request.comment());
        publish("workorder.reviewed", Map.of("workorder_id", workorderId, "status", targetStatus));
        return Map.of("workorder_id", workorderId, "alarm_id", workorder.get("alarm_id"), "status", targetStatus);
    }

    @Transactional
    public Map<String, Object> redispatchWorkOrder(String workorderId, String assignee, String reason, String operator) {
        String resolvedAssignee = assignee == null || assignee.isBlank() ? "专业复核人员" : assignee;
        int updated = jdbc.update("update work_orders set status='created', assignee=?, comment=?, updated_at=? where id=?",
                resolvedAssignee, reason, Timestamp.from(Instant.now()), workorderId);
        if (updated == 0) {
            throw new ResponseStatusException(HttpStatus.NOT_FOUND, "工单不存在");
        }
        insertWorkOrderOperation(workorderId, "redispatched", operator, "重新派发", reason);
        publish("workorder.redispatched", Map.of("workorder_id", workorderId, "assignee", resolvedAssignee));
        return Map.of("workorder_id", workorderId, "status", "created", "assignee", resolvedAssignee);
    }

    public Map<String, Object> resourcePreview(String resourceId) {
        Map<String, Object> row = new HashMap<>(jdbc.queryForMap("""
                select id as resource_id, task_id, resource_type, storage_url, capture_time,
                       metadata::text as metadata, created_at
                from inspection_resources where id=?
                """, resourceId));
        row.put("metadata", Jsonb.map(String.valueOf(row.get("metadata"))));
        row.put("preview_type", switch (String.valueOf(row.get("resource_type"))) {
            case "image" -> "image";
            case "thermal" -> "thermal";
            case "tif" -> "raster";
            case "pointcloud" -> "pointcloud";
            default -> "file";
        });
        return row;
    }

    public Map<String, Object> resultEvidence(String resultId) {
        Map<String, Object> row = new HashMap<>(jdbc.queryForMap("""
                select r.id as ai_result_id, r.job_id, r.task_id, r.resource_id, r.scene, r.category,
                       r.confidence, r.geometry::text as geometry, r.measurements::text as measurements,
                       r.attributes::text as attributes, r.model_name, r.model_version,
                       ir.resource_type, ir.storage_url
                from ai_results r
                join inspection_resources ir on ir.id=r.resource_id
                where r.id=?
                """, resultId));
        row.put("geometry", Jsonb.map(String.valueOf(row.get("geometry"))));
        row.put("measurements", Jsonb.map(String.valueOf(row.get("measurements"))));
        row.put("attributes", Jsonb.map(String.valueOf(row.get("attributes"))));
        return row;
    }

    public Map<String, Object> alarmEvidenceChain(String alarmId) {
        Map<String, Object> alarm = new HashMap<>(jdbc.queryForMap("""
                select id as alarm_id, task_id, result_id, scene, category, severity, confidence,
                       location::text as location, evidence::text as evidence, rule_hits::text as rule_hits,
                       status, suppressed, suppression_reason, created_at, updated_at
                from alarms where id=?
                """, alarmId));
        alarm.put("location", Jsonb.map(String.valueOf(alarm.get("location"))));
        alarm.put("evidence", Jsonb.map(String.valueOf(alarm.get("evidence"))));
        alarm.put("rule_hits", Jsonb.stringList(String.valueOf(alarm.get("rule_hits"))));
        Map<String, Object> result = resultEvidence(String.valueOf(alarm.get("result_id")));
        List<Map<String, Object>> relatedWorkorders = jdbc.queryForList("select id as workorder_id, status, assignee, close_result, comment, created_at, updated_at from work_orders where alarm_id=?", alarmId);
        return Map.of("alarm", alarm, "ai_result", result, "workorders", relatedWorkorders);
    }

    @Transactional
    public Map<String, Object> decideSample(String sampleId, Requests.SampleDecisionRequest request) {
        String decision = request.decision() == null ? "accepted" : request.decision();
        String labelingStatus = "excluded".equals(decision) ? "excluded" : "queued";
        int updated = jdbc.update("update sample_feedback_records set labeling_status=?, priority=coalesce(?, priority), updated_at=? where id=?",
                labelingStatus, request.priority(), Timestamp.from(Instant.now()), sampleId);
        if (updated == 0) {
            throw new ResponseStatusException(HttpStatus.NOT_FOUND, "样本不存在");
        }
        publish("sample.decision.updated", Map.of("sample_id", sampleId, "decision", decision));
        return Map.of("sample_id", sampleId, "decision", decision, "labeling_status", labelingStatus);
    }

    public List<Map<String, Object>> listSamples() {
        return jdbc.queryForList("""
                select s.id as sample_id, s.run_id, s.task_id, s.resource_id, s.ai_result_id, s.alarm_id,
                       s.workorder_id, s.feedback_reason, s.priority, s.labeling_status,
                       s.target_model_group, s.source_model_version, s.quality_status,
                       s.created_at, s.updated_at, t.line_id
                from sample_feedback_records s
                join inspection_tasks t on t.id=s.task_id
                order by s.updated_at desc
                """);
    }

    public List<Map<String, Object>> listPlatformEvents() {
        return jdbc.queryForList("select id as event_id, event_type, payload::text as payload, created_at from platform_events order by created_at desc limit 200");
    }

    public Map<String, Object> aiRuntimeStatus() {
        return aiClient.runtimeStatus();
    }

    @Transactional
    public Map<String, Object> createExemption(Requests.ExemptionRequest request) {
        String id = Ids.next("exemption");
        jdbc.update(
                "insert into exemption_areas(id, scene, line_id, geometry, valid_from, valid_to, reason, created_at) values (?,?,?,?::jsonb,?,?,?,?)",
                id,
                request.scene(),
                request.lineId(),
                Jsonb.write(request.geometry() == null ? Map.of() : request.geometry()),
                Timestamp.from(Instant.parse(request.validFrom() == null ? "2000-01-01T00:00:00Z" : request.validFrom())),
                Timestamp.from(Instant.parse(request.validTo() == null ? "2099-01-01T00:00:00Z" : request.validTo())),
                request.reason(),
                Timestamp.from(Instant.now())
        );
        return Map.of("exemption_id", id);
    }

    public List<Map<String, Object>> models() {
        return jdbc.queryForList("select id, model_group, version, status, scenes::text as scenes, metrics::text as metrics from model_versions order by model_group");
    }

    public Map<String, Object> overview() {
        return Map.of(
                "tasks", count("inspection_tasks"),
                "resources", count("inspection_resources"),
                "analysis_jobs", count("analysis_jobs"),
                "ai_results", count("ai_results"),
                "alarms", count("alarms where suppressed=false"),
                "suppressed", count("alarms where suppressed=true"),
                "workorders", count("work_orders"),
                "baselines", count("baseline_objects"),
                "exemptions", count("exemption_areas")
        );
    }

    public Map<String, Object> runDemo() {
        Map<String, Object> task = createTask(new Requests.CreateTaskRequest("DEMO-001", "line-demo", "K123+000", "K130+000", "route-demo", List.of("违法开挖", "塔吊", "人员入侵", "防洪点精密监测", "接触网异物（高危）", "杆上设备发热"), "high", null));
        completeResources(new Requests.ResourceCompleteRequest(String.valueOf(task.get("task_id")), List.of(
                new Requests.ResourceItem(null, "image", "s3://rail-inspection/demo/visible-001.jpg", null, Map.of("longitude", 116.1, "latitude", 39.1, "distance_to_track_m", 72.5)),
                new Requests.ResourceItem(null, "thermal", "s3://rail-inspection/demo/thermal-001.tiff", null, Map.of("temperature_c", 86.3)),
                new Requests.ResourceItem(null, "tif", "s3://rail-inspection/demo/flood-001.tif", null, Map.of("volume_change_m3", 1.8))
        )));
        return Map.of("task", task, "overview", overview());
    }

    private String insertAiResult(String jobId, Map<String, Object> task, Map<String, Object> resource, Map<String, Object> result) {
        String id = Ids.next("result");
        jdbc.update(
                "insert into ai_results(id, job_id, task_id, resource_id, scene, category, confidence, geometry, measurements, attributes, model_name, model_version, created_at) values (?,?,?,?,?,?,?,?::jsonb,?::jsonb,?::jsonb,?,?,?)",
                id,
                jobId,
                task.get("id"),
                resource.get("id"),
                result.get("scene"),
                result.get("category"),
                result.get("confidence"),
                Jsonb.write(result.getOrDefault("geometry", Map.of())),
                Jsonb.write(result.getOrDefault("measurements", Map.of())),
                Jsonb.write(result.getOrDefault("attributes", Map.of())),
                result.getOrDefault("model_name", "vision-detector"),
                result.getOrDefault("model_version", "v1.0.0"),
                Timestamp.from(Instant.now())
        );
        return id;
    }

    private boolean insertAlarmAndMaybeWorkOrder(Map<String, Object> task, Map<String, Object> resource, Map<String, Object> result, RuleDecision decision) {
        String alarmId = Ids.next("alarm");
        List<Map<String, Object>> matches = jdbc.queryForList("""
                select r.owner_org_id,r.assignee_user_id,u.display_name
                from responsibility_rules r left join platform_users u on u.id=r.assignee_user_id
                where r.status='ACTIVE' and (r.line_id is null or r.line_id=?)
                  and (r.scene is null or r.scene=?)
                order by r.priority limit 1
                """, task.get("line_id"), result.get("scene"));
        Map<String, Object> responsibility = matches.isEmpty() ? Map.of() : matches.get(0);
        String ownerOrgId = String.valueOf(responsibility.getOrDefault("owner_org_id", "org-railway"));
        String assigneeUserId = String.valueOf(responsibility.getOrDefault("assignee_user_id", "user-reviewer"));
        String assigneeName = String.valueOf(responsibility.getOrDefault("display_name", "专业复核人员"));
        Instant now = Instant.now();
        jdbc.update(
                "insert into alarms(id, task_id, result_id, scene, category, severity, confidence, location, evidence, rule_hits, status, suppressed, suppression_reason, owner_org_id, created_at, updated_at) values (?,?,?,?,?,?,?,?::jsonb,?::jsonb,?::jsonb,?,?,?,?,?,?)",
                alarmId,
                task.get("id"),
                result.get("id"),
                result.get("scene"),
                result.get("category"),
                decision.severity(),
                result.get("confidence"),
                Jsonb.write(decision.location()),
                Jsonb.write(Map.of("resource_id", resource.get("id"), "storage_url", resource.get("storage_url"))),
                Jsonb.write(decision.ruleHits()),
                decision.suppressed() ? "suppressed" : "pending",
                decision.suppressed(),
                decision.suppressionReason(),
                ownerOrgId,
                Timestamp.from(now),
                Timestamp.from(now)
        );
        if (!decision.suppressed()) {
            jdbc.update("insert into work_orders(id, alarm_id, status, assignee, owner_org_id, assignee_user_id, created_at, updated_at) values (?,?,?,?,?,?,?,?)",
                    Ids.next("wo"), alarmId, "created", assigneeName, ownerOrgId, assigneeUserId,
                    Timestamp.from(now), Timestamp.from(now));
            jdbc.update("""
                    insert into notification_deliveries(
                        id,alarm_id,channel,recipient,status,attempt_count,receipt,
                        sent_at,delivered_at,created_at,updated_at
                    ) values (?,?,'IN_APP',?,'DELIVERED',1,?::jsonb,?,?,?,?)
                    """, Ids.next("notification"), alarmId, assigneeUserId,
                    Jsonb.write(Map.of("recipient_name", assigneeName, "owner_org_id", ownerOrgId)),
                    Timestamp.from(now), Timestamp.from(now), Timestamp.from(now), Timestamp.from(now));
            publish("alarm.created", Map.of("alarm_id", alarmId, "scene", result.get("scene")));
            return true;
        }
        return false;
    }

    private Map<String, Object> taskById(String taskId) {
        return jdbc.queryForMap("select *, scene_set::text as scene_set_text from inspection_tasks where id=?", taskId);
    }

    private Map<String, Object> resourceById(String resourceId) {
        Map<String, Object> row = new HashMap<>(jdbc.queryForMap("select *, metadata::text as metadata from inspection_resources where id=?", resourceId));
        row.put("metadata", Jsonb.map(String.valueOf(row.get("metadata"))));
        return row;
    }

    private Integer count(String tableExpression) {
        return jdbc.queryForObject("select count(*) from " + tableExpression, Integer.class);
    }

    private void insertWorkOrderOperation(String workorderId, String operationType, String operator, String result, String comment) {
        jdbc.update("insert into work_order_operations(id, workorder_id, operation_type, operator_name, result, comment, created_at) values (?,?,?,?,?,?,?)",
                Ids.next("woo"), workorderId, operationType,
                operator == null || operator.isBlank() ? "巡检平台" : operator,
                result, comment, Timestamp.from(Instant.now()));
    }

    private void publish(String eventType, Map<String, Object> payload) {
        String id = Ids.next("event");
        jdbc.update("insert into platform_events(id, event_type, payload, created_at) values (?,?,?::jsonb,?)", id, eventType, Jsonb.write(payload), Timestamp.from(Instant.now()));
        kafkaTemplate.send("rail." + eventType, payload);
    }
}
