//go:build ignore

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")
	}

	fmt.Println("=== unique constraints on bookingno / trackingno ===")
	type c struct{ Conname, Def string }
	var cs []c
	db.DB.Raw(`SELECT c.conname, pg_get_constraintdef(c.oid) def
	  FROM pg_constraint c JOIN pg_class t ON t.oid=c.conrelid
	  WHERE t.relname IN ('pickupbookings','consignments') AND c.contype='u'`).Scan(&cs)
	if len(cs) == 0 {
		fmt.Println("  NONE — nothing stops a duplicate at the database level")
	}
	for _, x := range cs {
		fmt.Printf("  %s: %s\n", x.Conname, x.Def)
	}

	fmt.Println("\n=== duplicate booking numbers ===")
	type d struct {
		Bookingno string
		N         int64
	}
	var ds []d
	db.DB.Raw(`SELECT bookingno, count(*) n FROM pickupbookings
	  GROUP BY 1 HAVING count(*)>1 ORDER BY n DESC LIMIT 10`).Scan(&ds)
	fmt.Printf("  %d duplicated\n", len(ds))
	for _, x := range ds {
		fmt.Printf("    %s x%d\n", x.Bookingno, x.N)
	}

	fmt.Println("\n=== all-zero random part (would mean rand.Read failed) ===")
	var zeros int64
	db.DB.Raw(`SELECT count(*) FROM pickupbookings WHERE bookingno LIKE 'DM-BK-00000000-%'`).Scan(&zeros)
	fmt.Printf("  %d\n", zeros)

	fmt.Println("\n=== how many bookings share a timestamp suffix ===")
	type s struct {
		Suffix string
		N      int64
	}
	var ss []s
	db.DB.Raw(`SELECT split_part(bookingno,'-',4) suffix, count(*) n
	  FROM pickupbookings GROUP BY 1 ORDER BY n DESC LIMIT 5`).Scan(&ss)
	for _, x := range ss {
		fmt.Printf("  suffix %-8s %d bookings\n", x.Suffix, x.N)
	}

	fmt.Println("\n=== duplicate tracking numbers ===")
	var td []d
	db.DB.Raw(`SELECT trackingno bookingno, count(*) n FROM consignments
	  GROUP BY 1 HAVING count(*)>1 LIMIT 5`).Scan(&td)
	fmt.Printf("  %d duplicated\n", len(td))
}