package qc import ( "encoding/json" "fmt" "net/http" "strings" "verofy-backend/models" "github.com/gin-gonic/gin" "gorm.io/gorm" ) type ZoneContainmentResult struct { ElementID int `json:"element_id"` ElementType string `json:"element_type"` // "segment", "site", "pole", "access_point" ElementName string `json:"element_name,omitempty"` AssignedZone *string `json:"assigned_zone"` ActualZones []string `json:"actual_zones,omitempty"` IsValid bool `json:"is_valid"` ErrorMessage string `json:"error_message,omitempty"` Geometry map[string]interface{} `json:"geometry,omitempty"` } type ZoneContainmentSummary struct { TotalElements int `json:"total_elements"` ValidElements int `json:"valid_elements"` InvalidElements int `json:"invalid_elements"` PassRate float64 `json:"pass_rate"` ByType map[string]TypeSummary `json:"by_type"` Results []ZoneContainmentResult `json:"results"` } type TypeSummary struct { Total int `json:"total"` Valid int `json:"valid"` Invalid int `json:"invalid"` } func ZoneContainmentRoute(router *gin.Engine, db *gorm.DB, schema, segmentTable, mapIDCol, zoneCol, idCol, qcFlagCol string) { // Full zone containment summary endpoint router.GET("/api/qc/zone-containment", func(c *gin.Context) { mapID := c.Query("map_id") zone := c.Query("zone") if mapID == "" || zone == "" { c.JSON(http.StatusBadRequest, gin.H{"error": "map_id and zone are required"}) return } summary, err := CheckZoneContainment(db, mapID, zone, schema, segmentTable, mapIDCol, zoneCol, idCol, qcFlagCol) if err != nil { c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) return } c.JSON(http.StatusOK, summary) }) // Invalid elements only endpoint router.GET("/api/qc/zone-containment/invalid", func(c *gin.Context) { mapID := c.Query("map_id") zone := c.Query("zone") if mapID == "" || zone == "" { c.JSON(http.StatusBadRequest, gin.H{"error": "map_id and zone are required"}) return } invalid, err := GetInvalidZoneContainment(db, mapID, zone, schema, segmentTable, mapIDCol, zoneCol, idCol, qcFlagCol) if err != nil { c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) return } c.JSON(http.StatusOK, gin.H{ "invalid_elements": invalid, "count": len(invalid), }) }) // Update QC flags endpoint (for segments) router.POST("/api/qc/zone-containment/update-flags", func(c *gin.Context) { var request struct { SegmentIDs []int `json:"segment_ids"` MapID string `json:"map_id"` Zone string `json:"zone"` } if err := c.ShouldBindJSON(&request); err != nil { c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()}) return } err := UpdateZoneContainmentFlags(db, request.SegmentIDs, schema, segmentTable, idCol, qcFlagCol) if err != nil { c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) return } c.JSON(http.StatusOK, gin.H{"message": fmt.Sprintf("Updated QC flags for %d segments", len(request.SegmentIDs))}) }) } // CheckZoneContainment validates that network elements are within their assigned zones func CheckZoneContainment(db *gorm.DB, mapID, zone, schema, segmentTable, mapIDCol, zoneCol, idCol, qcFlagCol string) (*ZoneContainmentSummary, error) { summary := &ZoneContainmentSummary{ Results: make([]ZoneContainmentResult, 0), ByType: map[string]TypeSummary{ "segment": {Total: 0, Valid: 0, Invalid: 0}, "site": {Total: 0, Valid: 0, Invalid: 0}, "pole": {Total: 0, Valid: 0, Invalid: 0}, "access_point": {Total: 0, Valid: 0, Invalid: 0}, }, } // Get all zone polygons zones, err := getZonePolygons(db, schema) if err != nil { return nil, fmt.Errorf("failed to fetch zone polygons: %w", err) } // Check segments segmentResults, err := checkSegmentZones(db, mapID, zone, schema, segmentTable, mapIDCol, zoneCol, idCol, zones) if err != nil { return nil, fmt.Errorf("failed to check segments: %w", err) } summary.Results = append(summary.Results, segmentResults...) // Check sites siteResults, err := checkSiteZones(db, mapID, schema, zones) if err != nil { return nil, fmt.Errorf("failed to check sites: %w", err) } summary.Results = append(summary.Results, siteResults...) // Check poles poleResults, err := checkPoleZones(db, mapID, schema, zones) if err != nil { return nil, fmt.Errorf("failed to check poles: %w", err) } summary.Results = append(summary.Results, poleResults...) // Check access points accessPointResults, err := checkAccessPointZones(db, mapID, schema, zones) if err != nil { return nil, fmt.Errorf("failed to check access points: %w", err) } summary.Results = append(summary.Results, accessPointResults...) // Calculate summary statistics for _, result := range summary.Results { typeSummary := summary.ByType[result.ElementType] typeSummary.Total++ if result.IsValid { typeSummary.Valid++ summary.ValidElements++ } else { typeSummary.Invalid++ summary.InvalidElements++ } summary.ByType[result.ElementType] = typeSummary summary.TotalElements++ } // Calculate pass rate if summary.TotalElements > 0 { summary.PassRate = float64(summary.ValidElements) / float64(summary.TotalElements) * 100 } return summary, nil } // getZonePolygons fetches all zone polygons from the info table func getZonePolygons(db *gorm.DB, schema string) ([]models.InfoGeoJSON, error) { var zones []models.InfoGeoJSON table := fmt.Sprintf("%s.info", schema) err := db.Table(table). Select("id, name, group_1, ST_AsGeoJSON(geom)::json AS geometry"). Find(&zones).Error if err != nil { return nil, err } return zones, nil } // checkSegmentZones validates segments against their assigned zones using PostGIS func checkSegmentZones(db *gorm.DB, mapID, zone, schema, segmentTable, mapIDCol, zoneCol, idCol string, zones []models.InfoGeoJSON) ([]ZoneContainmentResult, error) { // Use PostGIS to check intersection directly in the database type SegmentZoneCheck struct { ID int `gorm:"column:id"` SegmentType string `gorm:"column:segment_type"` AssignedZone *string `gorm:"column:assigned_zone"` ActualZones string `gorm:"column:actual_zones"` Geometry json.RawMessage `gorm:"column:geometry"` } var results []SegmentZoneCheck table := fmt.Sprintf("%s.%s", schema, segmentTable) infoTable := fmt.Sprintf("%s.info", schema) query := fmt.Sprintf(` SELECT s.%s as id, s.segment_type, s."%s" as assigned_zone, STRING_AGG(i.group_1, ',') as actual_zones, ST_AsGeoJSON(ST_Transform(s.geom, 4326))::json AS geometry FROM %s s LEFT JOIN %s i ON ST_Intersects(ST_Transform(s.geom, 4326), i.geom) WHERE s.%s = ? AND s."%s" = ? GROUP BY s.%s, s.segment_type, s."%s", s.geom `, idCol, zoneCol, table, infoTable, mapIDCol, zoneCol, idCol, zoneCol) err := db.Raw(query, mapID, zone).Scan(&results).Error if err != nil { return nil, err } qcResults := make([]ZoneContainmentResult, 0, len(results)) for _, seg := range results { result := ZoneContainmentResult{ ElementID: seg.ID, ElementType: "segment", ElementName: seg.SegmentType, AssignedZone: seg.AssignedZone, Geometry: parseGeometryToMap(seg.Geometry), } // Parse actual zones if seg.ActualZones != "" { result.ActualZones = splitZones(seg.ActualZones) } else { result.ActualZones = []string{} } // Check validity if seg.AssignedZone == nil || *seg.AssignedZone == "" { result.IsValid = false result.ErrorMessage = "Element has no assigned zone (NULL or blank)" } else if len(result.ActualZones) == 0 { result.IsValid = false result.ErrorMessage = fmt.Sprintf("Element assigned to '%s' but not found in any zone", *seg.AssignedZone) } else { // Check if assigned zone is in actual zones result.IsValid = false for _, actualZone := range result.ActualZones { if actualZone == *seg.AssignedZone { result.IsValid = true break } } if !result.IsValid { result.ErrorMessage = fmt.Sprintf("Element assigned to '%s' but found in: %v", *seg.AssignedZone, result.ActualZones) } } qcResults = append(qcResults, result) } return qcResults, nil } // Helper function to split comma-separated zones func splitZones(zones string) []string { if zones == "" { return []string{} } parts := []string{} for _, z := range strings.Split(zones, ",") { z = strings.TrimSpace(z) if z != "" { parts = append(parts, z) } } return parts } // Helper to parse geometry JSON to map func parseGeometryToMap(geomJSON json.RawMessage) map[string]interface{} { var geomMap map[string]interface{} if err := json.Unmarshal(geomJSON, &geomMap); err != nil { return nil } return geomMap } // checkSiteZones validates sites against their assigned zones using PostGIS func checkSiteZones(db *gorm.DB, mapID, schema string, zones []models.InfoGeoJSON) ([]ZoneContainmentResult, error) { type SiteZoneCheck struct { ID int `gorm:"column:id"` Name *string `gorm:"column:name"` AssignedZone *string `gorm:"column:assigned_zone"` ActualZones string `gorm:"column:actual_zones"` Geometry json.RawMessage `gorm:"column:geometry"` } var results []SiteZoneCheck table := fmt.Sprintf("%s.sites", schema) infoTable := fmt.Sprintf("%s.info", schema) query := fmt.Sprintf(` SELECT COALESCE(s.id, s.gid) as id, s."Name" as name, s."Group 1" as assigned_zone, STRING_AGG(i.group_1, ',') as actual_zones, ST_AsGeoJSON(s.geometry)::json AS geometry FROM %s s LEFT JOIN %s i ON ST_Within(s.geometry, i.geom) WHERE s."MapProjectID" = ? GROUP BY s.gid, s.id, s."Name", s."Group 1", s.geometry `, table, infoTable) err := db.Raw(query, mapID).Scan(&results).Error if err != nil { return nil, err } qcResults := make([]ZoneContainmentResult, 0, len(results)) for _, site := range results { result := ZoneContainmentResult{ ElementID: site.ID, ElementType: "site", ElementName: "", AssignedZone: site.AssignedZone, Geometry: parseGeometryToMap(site.Geometry), } if site.Name != nil { result.ElementName = *site.Name } // Parse actual zones if site.ActualZones != "" { result.ActualZones = splitZones(site.ActualZones) } else { result.ActualZones = []string{} } // Check validity if site.AssignedZone == nil || *site.AssignedZone == "" { result.IsValid = false result.ErrorMessage = "Element has no assigned zone (NULL or blank)" } else if len(result.ActualZones) == 0 { result.IsValid = false result.ErrorMessage = fmt.Sprintf("Element assigned to '%s' but not found in any zone", *site.AssignedZone) } else { // Check if assigned zone is in actual zones result.IsValid = false for _, actualZone := range result.ActualZones { if actualZone == *site.AssignedZone { result.IsValid = true break } } if !result.IsValid { result.ErrorMessage = fmt.Sprintf("Element assigned to '%s' but found in: %v", *site.AssignedZone, result.ActualZones) } } qcResults = append(qcResults, result) } return qcResults, nil } // checkPoleZones validates poles against their assigned zones using PostGIS func checkPoleZones(db *gorm.DB, mapID, schema string, zones []models.InfoGeoJSON) ([]ZoneContainmentResult, error) { type PoleZoneCheck struct { ID int `gorm:"column:id"` Name *string `gorm:"column:name"` AssignedZone *string `gorm:"column:assigned_zone"` ActualZones string `gorm:"column:actual_zones"` Geometry json.RawMessage `gorm:"column:geometry"` } var results []PoleZoneCheck table := fmt.Sprintf("%s.poles", schema) infoTable := fmt.Sprintf("%s.info", schema) query := fmt.Sprintf(` SELECT COALESCE(p.id, p.gid) as id, p.name, p.group1 as assigned_zone, STRING_AGG(i.group_1, ',') as actual_zones, ST_AsGeoJSON(ST_Transform(p.geom, 4326))::json AS geometry FROM %s p LEFT JOIN %s i ON ST_Within(ST_Transform(p.geom, 4326), i.geom) WHERE p.mapprojectid = ? GROUP BY p.gid, p.id, p.name, p.group1, p.geom `, table, infoTable) err := db.Raw(query, mapID).Scan(&results).Error if err != nil { return nil, err } qcResults := make([]ZoneContainmentResult, 0, len(results)) for _, pole := range results { result := ZoneContainmentResult{ ElementID: pole.ID, ElementType: "pole", ElementName: "", AssignedZone: pole.AssignedZone, Geometry: parseGeometryToMap(pole.Geometry), } if pole.Name != nil { result.ElementName = *pole.Name } // Parse actual zones if pole.ActualZones != "" { result.ActualZones = splitZones(pole.ActualZones) } else { result.ActualZones = []string{} } // Check validity if pole.AssignedZone == nil || *pole.AssignedZone == "" { result.IsValid = false result.ErrorMessage = "Element has no assigned zone (NULL or blank)" } else if len(result.ActualZones) == 0 { result.IsValid = false result.ErrorMessage = fmt.Sprintf("Element assigned to '%s' but not found in any zone", *pole.AssignedZone) } else { // Check if assigned zone is in actual zones result.IsValid = false for _, actualZone := range result.ActualZones { if actualZone == *pole.AssignedZone { result.IsValid = true break } } if !result.IsValid { result.ErrorMessage = fmt.Sprintf("Element assigned to '%s' but found in: %v", *pole.AssignedZone, result.ActualZones) } } qcResults = append(qcResults, result) } return qcResults, nil } // checkAccessPointZones validates access points against their assigned zones using PostGIS func checkAccessPointZones(db *gorm.DB, mapID, schema string, zones []models.InfoGeoJSON) ([]ZoneContainmentResult, error) { type AccessPointZoneCheck struct { ID int `gorm:"column:id"` Name *string `gorm:"column:name"` AssignedZone *string `gorm:"column:assigned_zone"` ActualZones string `gorm:"column:actual_zones"` Geometry json.RawMessage `gorm:"column:geometry"` } var results []AccessPointZoneCheck table := fmt.Sprintf("%s.access_points", schema) infoTable := fmt.Sprintf("%s.info", schema) query := fmt.Sprintf(` SELECT COALESCE(ap.id, ap.gid) as id, ap.name, ap.group1 as assigned_zone, STRING_AGG(i.group_1, ',') as actual_zones, ST_AsGeoJSON(ST_Transform(ap.geom, 4326))::json AS geometry FROM %s ap LEFT JOIN %s i ON ST_Within(ST_Transform(ap.geom, 4326), i.geom) WHERE ap.mapprojectid = ? GROUP BY ap.gid, ap.id, ap.name, ap.group1, ap.geom `, table, infoTable) err := db.Raw(query, mapID).Scan(&results).Error if err != nil { return nil, err } qcResults := make([]ZoneContainmentResult, 0, len(results)) for _, ap := range results { result := ZoneContainmentResult{ ElementID: ap.ID, ElementType: "access_point", ElementName: "", AssignedZone: ap.AssignedZone, Geometry: parseGeometryToMap(ap.Geometry), } if ap.Name != nil { result.ElementName = *ap.Name } // Parse actual zones if ap.ActualZones != "" { result.ActualZones = splitZones(ap.ActualZones) } else { result.ActualZones = []string{} } // Check validity if ap.AssignedZone == nil || *ap.AssignedZone == "" { result.IsValid = false result.ErrorMessage = "Element has no assigned zone (NULL or blank)" } else if len(result.ActualZones) == 0 { result.IsValid = false result.ErrorMessage = fmt.Sprintf("Element assigned to '%s' but not found in any zone", *ap.AssignedZone) } else { // Check if assigned zone is in actual zones result.IsValid = false for _, actualZone := range result.ActualZones { if actualZone == *ap.AssignedZone { result.IsValid = true break } } if !result.IsValid { result.ErrorMessage = fmt.Sprintf("Element assigned to '%s' but found in: %v", *ap.AssignedZone, result.ActualZones) } } qcResults = append(qcResults, result) } return qcResults, nil } // validateElementZone checks if an element is within its assigned zone // For segments: isLineString=true, allows partial intersection // For points: isLineString=false, requires point to be within zone func validateElementZone(elementID int, elementType, elementName string, assignedZone *string, geometry json.RawMessage, zones []models.InfoGeoJSON, isLineString bool) ZoneContainmentResult { result := ZoneContainmentResult{ ElementID: elementID, ElementType: elementType, ElementName: elementName, AssignedZone: assignedZone, IsValid: false, ActualZones: []string{}, } // Parse geometry var geomMap map[string]interface{} if err := json.Unmarshal(geometry, &geomMap); err != nil { result.ErrorMessage = "Failed to parse geometry" return result } result.Geometry = geomMap // Check if assigned zone is NULL or empty - this is INVALID if assignedZone == nil || *assignedZone == "" { result.ErrorMessage = "Element has no assigned zone (NULL or blank)" return result } // Find which zones contain this element for _, zone := range zones { if zone.Group1 == nil { continue } if isLineString { // For segments (LineStrings): check if ANY part intersects with the zone if geometryIntersectsZone(geomMap, zone.Geometry) { result.ActualZones = append(result.ActualZones, *zone.Group1) } } else { // For points: check if point is within the zone if pointWithinZone(geomMap, zone.Geometry) { result.ActualZones = append(result.ActualZones, *zone.Group1) } } } // Validate: assigned zone must be in the list of actual zones for _, actualZone := range result.ActualZones { if actualZone == *assignedZone { result.IsValid = true return result } } // Element is not in its assigned zone if len(result.ActualZones) == 0 { result.ErrorMessage = fmt.Sprintf("Element assigned to '%s' but not found in any zone", *assignedZone) } else { result.ErrorMessage = fmt.Sprintf("Element assigned to '%s' but found in: %v", *assignedZone, result.ActualZones) } return result } // geometryIntersectsZone checks if a LineString geometry intersects with a zone polygon func geometryIntersectsZone(lineGeom map[string]interface{}, zoneGeometry json.RawMessage) bool { var zonePoly map[string]interface{} if err := json.Unmarshal(zoneGeometry, &zonePoly); err != nil { return false } // Get line coordinates lineCoords, ok := lineGeom["coordinates"].([]interface{}) if !ok || len(lineCoords) == 0 { return false } // Get polygon coordinates (first ring is outer boundary) polyCoords, ok := zonePoly["coordinates"].([]interface{}) if !ok || len(polyCoords) == 0 { return false } outerRing, ok := polyCoords[0].([]interface{}) if !ok || len(outerRing) == 0 { return false } // Check if ANY point of the line is within the polygon for _, coordInterface := range lineCoords { coord, ok := coordInterface.([]interface{}) if !ok || len(coord) < 2 { continue } lng, ok1 := coord[0].(float64) lat, ok2 := coord[1].(float64) if !ok1 || !ok2 { continue } if pointInPolygon(lng, lat, outerRing) { return true } } return false } // pointWithinZone checks if a Point geometry is within a zone polygon func pointWithinZone(pointGeom map[string]interface{}, zoneGeometry json.RawMessage) bool { var zonePoly map[string]interface{} if err := json.Unmarshal(zoneGeometry, &zonePoly); err != nil { return false } // Get point coordinates pointCoords, ok := pointGeom["coordinates"].([]interface{}) if !ok || len(pointCoords) < 2 { return false } lng, ok1 := pointCoords[0].(float64) lat, ok2 := pointCoords[1].(float64) if !ok1 || !ok2 { return false } // Get polygon coordinates polyCoords, ok := zonePoly["coordinates"].([]interface{}) if !ok || len(polyCoords) == 0 { return false } outerRing, ok := polyCoords[0].([]interface{}) if !ok || len(outerRing) == 0 { return false } return pointInPolygon(lng, lat, outerRing) } // pointInPolygon uses ray casting algorithm to determine if point is inside polygon func pointInPolygon(lng, lat float64, ring []interface{}) bool { inside := false j := len(ring) - 1 for i := 0; i < len(ring); i++ { coord, ok := ring[i].([]interface{}) if !ok || len(coord) < 2 { continue } coordJ, ok := ring[j].([]interface{}) if !ok || len(coordJ) < 2 { continue } xi, ok1 := coord[0].(float64) yi, ok2 := coord[1].(float64) xj, ok3 := coordJ[0].(float64) yj, ok4 := coordJ[1].(float64) if !ok1 || !ok2 || !ok3 || !ok4 { continue } intersect := ((yi > lat) != (yj > lat)) && (lng < (xj-xi)*(lat-yi)/(yj-yi)+xi) if intersect { inside = !inside } j = i } return inside } // GetInvalidZoneContainment returns only elements that failed the check func GetInvalidZoneContainment(db *gorm.DB, mapID, zone, schema, segmentTable, mapIDCol, zoneCol, idCol, qcFlagCol string) ([]ZoneContainmentResult, error) { summary, err := CheckZoneContainment(db, mapID, zone, schema, segmentTable, mapIDCol, zoneCol, idCol, qcFlagCol) if err != nil { return nil, err } var invalid []ZoneContainmentResult for _, result := range summary.Results { if !result.IsValid { invalid = append(invalid, result) } } return invalid, nil } // UpdateZoneContainmentFlags updates QC flags for invalid segments func UpdateZoneContainmentFlags(db *gorm.DB, segmentIDs []int, schema, segmentTable, idCol, qcFlagCol string) error { if len(segmentIDs) == 0 { return nil } table := fmt.Sprintf("%s.%s", schema, segmentTable) return db.Table(table). Where(fmt.Sprintf("%s IN ?", idCol), segmentIDs). Update(qcFlagCol, "zone_containment_invalid").Error } // Helper function to get string pointer func getStringPointer(s string) *string { return &s }