//go:build ignore

// Read-only: why a customer-app booking may not reach the console.
//
//	go run scratch/check_customer_bookings.go
package main

import (
	"fmt"
	"log"

	"doormile/config"
	"doormile/db"

	"github.com/joho/godotenv"
)

func main() {
	_ = godotenv.Load()
	cfg := config.Load()
	db.Connect(cfg)
	if db.DB == nil {
		log.Fatal("no DB connection")
	}

	fmt.Println("=== 1. How many bookings exist, by source ===")
	type srcRow struct {
		Bookingsource string
		N             int64
	}
	var srcs []srcRow
	db.DB.Raw(`SELECT COALESCE(NULLIF(bookingsource,''),'(blank)') AS bookingsource, count(*) AS n
		FROM pickupbookings GROUP BY 1 ORDER BY n DESC`).Scan(&srcs)
	var total int64
	for _, s := range srcs {
		fmt.Printf("  %-16s %d\n", s.Bookingsource, s.N)
		total += s.N
	}
	fmt.Printf("  %-16s %d\n", "TOTAL", total)

	fmt.Println("\n=== 2. The 10 newest bookings ===")
	type bRow struct {
		Bookingid      int
		Bookingno      string
		Bookingsource  string
		Status         string
		Tenantid       *int
		Pickuplatitude float64
		Pickuppincode  string
		Createdat      string
	}
	var newest []bRow
	db.DB.Raw(`SELECT bookingid, bookingno, bookingsource, status, tenantid,
		       pickuplatitude, pickuppincode, createdat::text
		FROM pickupbookings ORDER BY bookingid DESC LIMIT 10`).Scan(&newest)
	for _, b := range newest {
		tenant := "NULL"
		if b.Tenantid != nil {
			tenant = fmt.Sprintf("%d", *b.Tenantid)
		}
		fmt.Printf("  #%-6d %-14s src=%-14s status=%-24s tenant=%-5s lat=%-10.4f pin=%-7s %s\n",
			b.Bookingid, b.Bookingno, b.Bookingsource, b.Status, tenant,
			b.Pickuplatitude, b.Pickuppincode, b.Createdat)
	}

	fmt.Println("\n=== 3. Customer-app bookings specifically ===")
	var appBookings []bRow
	db.DB.Raw(`SELECT bookingid, bookingno, bookingsource, status, tenantid,
		       pickuplatitude, pickuppincode, createdat::text
		FROM pickupbookings WHERE bookingsource = 'Customer_App'
		ORDER BY bookingid DESC LIMIT 10`).Scan(&appBookings)
	if len(appBookings) == 0 {
		fmt.Println("  none at all")
	}
	for _, b := range appBookings {
		fmt.Printf("  #%-6d %-14s status=%-24s lat=%-10.4f pin=%-7s %s\n",
			b.Bookingid, b.Bookingno, b.Status, b.Pickuplatitude, b.Pickuppincode, b.Createdat)
	}

	fmt.Println("\n=== 4. Customer-app bookings with no pickup coordinates ===")
	var noCoords int64
	db.DB.Raw(`SELECT count(*) FROM pickupbookings
		WHERE bookingsource = 'Customer_App'
		  AND (pickuplatitude = 0 OR pickuplongitude = 0
		       OR pickuplatitude IS NULL OR pickuplongitude IS NULL)`).Scan(&noCoords)
	fmt.Printf("  %d (these cannot be assigned a rider and fail the console's zone filter)\n", noCoords)

	fmt.Println("\n=== 5. What one page of the console's own query returns ===")
	// The console drains GET /admin/bookings, which runs no ORDER BY. This is
	// the same shape: LIMIT/OFFSET with no ordering.
	var page1, page1again []idRow
	db.DB.Raw(`SELECT bookingid FROM pickupbookings LIMIT 5 OFFSET 0`).Scan(&page1)
	db.DB.Raw(`SELECT bookingid FROM pickupbookings LIMIT 5 OFFSET 0`).Scan(&page1again)
	fmt.Printf("  unordered page 1, call A: %v\n", ids(page1))
	fmt.Printf("  unordered page 1, call B: %v\n", ids(page1again))

	var lastPage []idRow
	offset := total - 5
	if offset < 0 {
		offset = 0
	}
	db.DB.Raw(fmt.Sprintf(`SELECT bookingid FROM pickupbookings LIMIT 5 OFFSET %d`, offset)).Scan(&lastPage)
	fmt.Printf("  unordered LAST page:      %v\n", ids(lastPage))
	fmt.Println("  (if the newest ids appear only on the last page, a truncated drain never sees them)")
}

type idRow struct{ Bookingid int }

func ids(rows []idRow) []int {
	out := make([]int, 0, len(rows))
	for _, r := range rows {
		out = append(out, r.Bookingid)
	}
	return out
}