backend_fiesta/scratch/termbackfill/main.go

// Backfills terminalid on bills that arrived before the posTerminalFor fix.
//
// Between the till and v1.3.96 the ingest wrote the fallback terminal code onto
// the batch while the row was built from the order, so bills landed with an
// empty terminalid and `byterminal` grouped them all under "". The code was
// never lost — it is the third segment of the invoice number the till printed,
// INV-2608-T5EDD-00116, minted at the same terminal in the same transaction.
// So this derives rather than guesses.
//
// Only rows whose terminalid is blank AND whose invoice number matches the
// exact shape are touched; anything else is left alone and reported.
//
//	go run ./scratch/termbackfill        # dry run — shows every change
//	go run ./scratch/termbackfill apply  # writes, then prints the undo
package main

import (
	"fmt"
	"log"
	"os"

	"github.com/joho/godotenv"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

// Anchored, and the terminal segment is required to be non-empty. A loose
// pattern here would write a blank over a blank and report it as a fix.
const invoiceShape = `^INV-[0-9]+-[A-Za-z0-9]+-[0-9]+$`

type row struct {
	Posorderid    int
	Invoicenumber string
	Locationid    int
	Derived       string
	Businessdate  string
}

func main() {
	apply := len(os.Args) > 1 && os.Args[1] == "apply"

	_ = godotenv.Load()
	dsn := fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=disable",
		os.Getenv("DB_HOST"), os.Getenv("DB_PORT"), os.Getenv("DB_USER"),
		os.Getenv("DB_PASSWORD"), os.Getenv("DB_NAME"))
	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{Logger: logger.Default.LogMode(logger.Silent)})
	if err != nil {
		log.Fatal(err)
	}

	var blank int
	db.Raw(`SELECT COUNT(*) FROM pos_orders WHERE COALESCE(terminalid,'') = ''`).Scan(&blank)

	var rows []row
	db.Raw(`SELECT posorderid, invoicenumber, locationid, businessdate,
	          split_part(invoicenumber, '-', 3) AS derived
	        FROM pos_orders
	        WHERE COALESCE(terminalid,'') = '' AND invoicenumber ~ ?
	        ORDER BY posorderid`, invoiceShape).Scan(&rows)

	fmt.Printf("bills with a blank terminalid: %d\n", blank)
	fmt.Printf("of those, recoverable from the invoice number: %d\n\n", len(rows))
	if len(rows) != blank {
		fmt.Printf("!! %d bill(s) cannot be recovered and will be left blank\n\n", blank-len(rows))
	}

	// A derived code must already be in use at the same outlet, or it is not a
	// terminal — it is a segment that happened to parse. A code that fails is
	// skipped, not fatal: it means one till's bills are *all* blank, which no
	// amount of cross-referencing can confirm from this table alone. Writing it
	// anyway would launder a guess into the ledger as though it were recovered.
	counts := map[string]int{}
	for _, r := range rows {
		counts[fmt.Sprintf("%d/%s", r.Locationid, r.Derived)]++
	}
	trusted := map[string]bool{}
	seen := map[string]bool{}

	fmt.Printf("%-10s %-9s %-6s %-12s %s\n", "location", "terminal", "bills", "date", "known at this outlet?")
	for _, r := range rows {
		key := fmt.Sprintf("%d/%s", r.Locationid, r.Derived)
		if seen[key] {
			continue
		}
		seen[key] = true

		var known int
		db.Raw(`SELECT COUNT(*) FROM pos_orders
		        WHERE locationid = ? AND terminalid = ?`, r.Locationid, r.Derived).Scan(&known)
		trusted[key] = known > 0

		verdict := fmt.Sprintf("yes — %d bill(s) already filed under it", known)
		if known == 0 {
			verdict = "NO — never seen here, SKIPPED"
		}
		fmt.Printf("%-10d %-9s %-6d %-12s %s\n", r.Locationid, r.Derived, counts[key], r.Businessdate, verdict)
	}

	ids := make([]int, 0, len(rows))
	skipped := 0
	for _, r := range rows {
		if trusted[fmt.Sprintf("%d/%s", r.Locationid, r.Derived)] {
			ids = append(ids, r.Posorderid)
			continue
		}
		skipped++
	}
	fmt.Printf("\nwill update %d bill(s); leaving %d blank\n", len(ids), skipped)

	if len(ids) == 0 {
		return
	}
	if !apply {
		fmt.Println("dry run — nothing written. re-run with `apply` to write.")
		return
	}

	res := db.Exec(`UPDATE pos_orders
	                SET terminalid = split_part(invoicenumber, '-', 3)
	                WHERE posorderid IN ? AND COALESCE(terminalid,'') = ''`, ids)
	if res.Error != nil {
		log.Fatal(res.Error)
	}
	fmt.Printf("\nupdated %d bill(s)\n", res.RowsAffected)

	var stillBlank int
	db.Raw(`SELECT COUNT(*) FROM pos_orders WHERE COALESCE(terminalid,'') = ''`).Scan(&stillBlank)
	fmt.Printf("bills still blank: %d\n", stillBlank)

	fmt.Printf("\nundo:\n  UPDATE pos_orders SET terminalid = '' WHERE posorderid IN (%v);\n", ids)
}