doormile_backend/scratch/fix_miler_hubs.go

//go:build ignore

// Give every Available miler the hub it plainly belongs to.
//
// Why this is needed: assignment everywhere filters
// `hubid = ? AND availabilitystatus = 'Available'`, and that intersection was
// empty — all 15 Available milers had hubid NULL, while all 15 milers that
// had a hub were Offline or already busy. Two seed runs populated disjoint
// sets, so no rider was reachable by HubBatchAssign, HubAutoAssign, or the
// auto-assign retry loop.
//
// The hub is not guessed. Each miler is matched to the NEAREST ACTIVE HUB
// WITHIN ITS OWN applocationid — a rider is never handed a hub in another
// city, and within a city the closest one wins. The seeded GPS sits almost
// exactly on a hub in every case, so this reproduces the intended pairing
// rather than inventing one.
//
// Run:  go run scratch/fix_miler_hubs.go          (plan only, writes nothing)
//       go run scratch/fix_miler_hubs.go --apply  (writes, in one transaction)
package main

import (
	"fmt"
	"math"
	"os"
	"strings"

	"doormile/config"
	"doormile/db"

	"github.com/joho/godotenv"
)

type hub struct {
	Hubid         int
	Hubname       string
	Applocationid int
	Latitude      float64
	Longitude     float64
}

type miler struct {
	Userid           int
	Displayname      string
	Applocationid    int
	Currentlatitude  float64
	Currentlongitude float64
}

func haversineKM(lat1, lon1, lat2, lon2 float64) float64 {
	const R = 6371
	toRad := func(d float64) float64 { return d * math.Pi / 180 }
	dLat, dLon := toRad(lat2-lat1), toRad(lon2-lon1)
	a := math.Sin(dLat/2)*math.Sin(dLat/2) +
		math.Cos(toRad(lat1))*math.Cos(toRad(lat2))*math.Sin(dLon/2)*math.Sin(dLon/2)
	return R * 2 * math.Atan2(math.Sqrt(a), math.Sqrt(1-a))
}

func main() {
	apply := false
	for _, a := range os.Args[1:] {
		if a == "--apply" {
			apply = true
		}
	}

	_ = godotenv.Load()
	db.Connect(config.Load())
	if db.DB == nil {
		fmt.Println("no database connection")
		os.Exit(1)
	}

	var hubs []hub
	db.DB.Raw(`SELECT hubid, COALESCE(hubname,'') AS hubname,
	                  COALESCE(applocationid,0) AS applocationid,
	                  COALESCE(latitude,0) AS latitude, COALESCE(longitude,0) AS longitude
	             FROM hubs
	            WHERE deletedat IS NULL AND status = 'Active'
	              AND latitude IS NOT NULL AND longitude IS NOT NULL
	              AND latitude <> 0 AND longitude <> 0`).Scan(&hubs)

	var milers []miler
	db.DB.Raw(`SELECT userid, COALESCE(displayname,'') AS displayname,
	                  COALESCE(applocationid,0) AS applocationid,
	                  COALESCE(currentlatitude,0) AS currentlatitude,
	                  COALESCE(currentlongitude,0) AS currentlongitude
	             FROM milerprofiles
	            WHERE availabilitystatus = 'Available' AND hubid IS NULL
	            ORDER BY applocationid, userid`).Scan(&milers)

	fmt.Printf("candidate hubs: %d   milers to fix: %d\n\n", len(hubs), len(milers))

	type change struct {
		userid  int
		name    string
		hubid   int
		hubname string
		km      float64
	}
	var changes []change
	var skipped []string

	for _, m := range milers {
		best := -1
		bestKM := math.MaxFloat64
		for _, h := range hubs {
			// Never cross a city boundary, whatever the distance says.
			if h.Applocationid != m.Applocationid {
				continue
			}
			d := haversineKM(m.Currentlatitude, m.Currentlongitude, h.Latitude, h.Longitude)
			if d < bestKM {
				bestKM, best = d, h.Hubid
			}
		}
		if best == -1 {
			skipped = append(skipped, fmt.Sprintf("user=%d %s (no active hub at applocationid=%d)",
				m.Userid, m.Displayname, m.Applocationid))
			continue
		}
		name := ""
		for _, h := range hubs {
			if h.Hubid == best {
				name = h.Hubname
			}
		}
		changes = append(changes, change{m.Userid, m.Displayname, best, name, bestKM})
	}

	fmt.Println("PLAN — nearest active hub in the miler's own city:")
	for _, c := range changes {
		fmt.Printf("   user=%-5d %-28s -> hub %-4d %-30s (%.2f km)\n",
			c.userid, c.name, c.hubid, c.hubname, c.km)
	}
	if len(skipped) > 0 {
		fmt.Println("\nSKIPPED (left untouched):")
		for _, s := range skipped {
			fmt.Println("   " + s)
		}
	}

	// Rollback is trivial and worth printing either way: every row being
	// written currently holds NULL, so undoing this is one statement.
	ids := make([]string, 0, len(changes))
	for _, c := range changes {
		ids = append(ids, fmt.Sprint(c.userid))
	}
	fmt.Printf("\nROLLBACK:\n   UPDATE milerprofiles SET hubid = NULL WHERE userid IN (%s);\n",
		strings.Join(ids, ","))

	if !apply {
		fmt.Println("\n(plan only — nothing written. Re-run with --apply to commit.)")
		return
	}

	tx := db.DB.Begin()
	for _, c := range changes {
		// The hubid IS NULL guard makes this idempotent and means a concurrent
		// write cannot be clobbered: if someone set a hub in the meantime,
		// theirs stands.
		res := tx.Exec(`UPDATE milerprofiles SET hubid = ?, updatedat = NOW()
		                 WHERE userid = ? AND hubid IS NULL`, c.hubid, c.userid)
		if res.Error != nil {
			tx.Rollback()
			fmt.Println("FAILED, rolled back:", res.Error)
			os.Exit(1)
		}
	}
	if err := tx.Commit().Error; err != nil {
		fmt.Println("commit failed:", err)
		os.Exit(1)
	}
	fmt.Printf("\nAPPLIED: %d milers updated.\n", len(changes))

	var eligible int64
	db.DB.Raw(`SELECT COUNT(*) FROM milerprofiles
	            WHERE availabilitystatus = 'Available' AND hubid IS NOT NULL`).Scan(&eligible)
	fmt.Println("Available AND hubid set is now:", eligible)
}