backend_fiesta/scratch/cataloguefactsbackfill/main.go

// Backfills products.cataloguefacts for products imported before the column existed.
//
// The catalogue import copied eight of the catalogue's eighteen fields onto a
// tenant's product and left the other ten behind — the FSSAI licence, nutrients,
// highlights, providers, the typical price range, the variant key. The console
// covered for it by asking the catalogue again on every drawer open, and that
// stops working the moment a re-scrape retires the source row: a tenant's
// product is a SNAPSHOT and outlives it, so a licence number came off a product
// the shop was still selling with no way back.
//
// The import keeps them now. Every product imported BEFORE that does not have
// them, and no amount of new code fixes a row that was written last month — so
// this reads each one's catalogue entry while it is still there and stores it.
//
//	go run ./scratch/cataloguefactsbackfill        # dry run — shows every change
//	go run ./scratch/cataloguefactsbackfill apply  # writes, then prints the undo
//
// ── What it will and will not touch ─────────────────────────────────────────
//
// Only products with an `imageid` and a NULL `cataloguefacts`. That is the
// whole safety story:
//
//   - NULL means nothing was ever written. A product whose facts are already
//     stored — including one stored as `{}` because the catalogue genuinely had
//     nothing to say — is never overwritten, so re-running this is a no-op
//     rather than a second opinion.
//   - No `imageid` means it never came from the catalogue. Sheet-imported
//     products have no entry to read and are left alone.
//   - A catalogue row that has already been retired cannot be recovered by
//     anything, here or later. Those are counted and named rather than written
//     as empty, because `{}` would claim the catalogue said nothing when the
//     truth is that nobody asked in time.
//
// Brand tables are discovered rather than assumed, and their columns are
// checked one by one before being selected: the catalogue is another team's
// scrape, brands appear between runs, and a table missing `nutrients` is a
// perfectly good catalogue of products. Demanding the full column set is the
// exact mistake that once made 16 of 35 live brands invisible to this side.
package main

import (
	"encoding/json"
	"fmt"
	"log"
	"os"
	"sort"
	"strings"

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

	"nearle/models"
)

// The columns worth keeping, in the order the drawer reads them. Scalars and
// arrays are separated because an array comes back as a Postgres text[] literal
// and has to be parsed before it can be re-encoded as JSON.
var scalarFacts = []string{
	"title", "category", "variant_key", "sku_source",
	"price_range", "fssai_license", "search_query",
}

var arrayFacts = []string{"providers", "highlights", "nutrients"}

type product struct {
	Productid    int
	Productbrand string
	Imageid      string
	Productname  string
	Tenantid     int
}

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

	_ = godotenv.Load()

	main, err := open("DB_HOST", "DB_PORT", "DB_USER", "DB_PASSWORD", "DB_NAME")
	if err != nil {
		log.Fatal("nearledb: ", err)
	}
	cat, err := open("CATALOGUE_DB_HOST", "CATALOGUE_DB_PORT", "CATALOGUE_DB_USER",
		"CATALOGUE_DB_PASSWORD", "CATALOGUE_DB_NAME")
	if err != nil {
		log.Fatal("cataloguedb: ", err)
	}

	// The column has to exist before there is anything to fill. Checked rather
	// than assumed so this says so plainly instead of failing inside a query.
	var hasColumn int
	main.Raw(`SELECT COUNT(*) FROM information_schema.columns
	          WHERE table_name = 'products' AND column_name = 'cataloguefacts'`).Scan(&hasColumn)
	if hasColumn == 0 {
		log.Fatal("products.cataloguefacts does not exist — start the API once to run the migration, then re-run this")
	}

	var candidates []product
	main.Raw(`SELECT productid, tenantid, COALESCE(productbrand,'') AS productbrand,
	                 COALESCE(imageid,'') AS imageid, COALESCE(productname,'') AS productname
	          FROM products
	          WHERE COALESCE(imageid,'') <> '' AND cataloguefacts IS NULL
	          ORDER BY productbrand, productid`).Scan(&candidates)

	var (
		total       int
		alreadyDone int
		noImageid   int
	)
	main.Raw(`SELECT COUNT(*) FROM products`).Scan(&total)
	main.Raw(`SELECT COUNT(*) FROM products WHERE cataloguefacts IS NOT NULL`).Scan(&alreadyDone)
	main.Raw(`SELECT COUNT(*) FROM products WHERE COALESCE(imageid,'') = ''`).Scan(&noImageid)

	fmt.Printf("products on the platform            : %d\n", total)
	fmt.Printf("  never came from the catalogue     : %d  (no imageid — left alone)\n", noImageid)
	fmt.Printf("  facts already stored              : %d  (never overwritten)\n", alreadyDone)
	fmt.Printf("  to backfill                       : %d\n\n", len(candidates))

	if len(candidates) == 0 {
		fmt.Println("nothing to do.")
		return
	}

	// One column check per brand table, not per product: the shape is a
	// property of the table and a per-row check would be thousands of
	// information_schema reads to learn the same thing.
	columnsByTable := map[string][]string{}
	missingTable := map[string]bool{}

	type update struct {
		product product
		facts   string
	}
	var (
		updates   []update
		retired   []product
		unknown   []product
		emptyOnly []product
	)

	for _, p := range candidates {
		table := brandTable(p.Productbrand)
		if table == "" {
			unknown = append(unknown, p)
			continue
		}
		if missingTable[table] {
			retired = append(retired, p)
			continue
		}

		cols, known := columnsByTable[table]
		if !known {
			cols = factColumnsOf(cat, table)
			if cols == nil {
				missingTable[table] = true
				retired = append(retired, p)
				continue
			}
			columnsByTable[table] = cols
		}

		facts, found := factsFor(cat, table, cols, p.Imageid)
		if !found {
			retired = append(retired, p)
			continue
		}
		if len(facts) == 0 {
			// The row is there and had nothing in these columns. Worth writing
			// `{}` — it is the true answer and it stops the console asking the
			// catalogue again on every open.
			emptyOnly = append(emptyOnly, p)
		}

		encoded, err := json.Marshal(facts)
		if err != nil {
			log.Printf("could not encode facts for product %d: %v", p.Productid, err)
			continue
		}
		updates = append(updates, update{product: p, facts: string(encoded)})
	}

	fmt.Printf("%-9s %-14s %-22s %-34s %s\n", "product", "brand", "imageid", "name", "facts recovered")
	for _, u := range updates {
		var keys []string
		var got map[string]any
		_ = json.Unmarshal([]byte(u.facts), &got)
		for k := range got {
			keys = append(keys, k)
		}
		sort.Strings(keys)
		summary := strings.Join(keys, ",")
		if summary == "" {
			summary = "(catalogue row has none)"
		}
		fmt.Printf("%-9d %-14s %-22s %-34s %s\n",
			u.product.Productid, trim(u.product.Productbrand, 14), trim(u.product.Imageid, 22),
			trim(u.product.Productname, 34), summary)
	}

	if len(retired) > 0 {
		fmt.Printf("\n!! %d product(s) cannot be recovered — their catalogue row is gone:\n", len(retired))
		for _, p := range retired {
			fmt.Printf("   %-9d %-14s %-22s %s\n", p.Productid, trim(p.Productbrand, 14),
				trim(p.Imageid, 22), trim(p.Productname, 40))
		}
		fmt.Println("   These are left NULL. The console falls back to the live lookup for them,")
		fmt.Println("   which will also find nothing — the detail was lost before this ran.")
	}

	if len(unknown) > 0 {
		fmt.Printf("\n!! %d product(s) carry a brand with no table in the catalogue:\n", len(unknown))
		for _, p := range unknown {
			fmt.Printf("   %-9d %-14s %s\n", p.Productid, trim(p.Productbrand, 14), trim(p.Productname, 40))
		}
	}

	fmt.Printf("\nwill write %d product(s)", len(updates))
	if len(emptyOnly) > 0 {
		fmt.Printf(", %d of them as `{}` because the catalogue row carries none of these fields", len(emptyOnly))
	}
	fmt.Printf("; leaving %d NULL\n", len(retired)+len(unknown))

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

	// One row at a time, each guarded by `cataloguefacts IS NULL` again.
	// Between the read above and this write another import could have stored
	// the real thing, and this must never be the one that overwrites it.
	written := 0
	ids := make([]int, 0, len(updates))
	for _, u := range updates {
		res := main.Exec(`UPDATE products SET cataloguefacts = ?::jsonb
		                  WHERE productid = ? AND cataloguefacts IS NULL`,
			u.facts, u.product.Productid)
		if res.Error != nil {
			log.Printf("product %d: %v", u.product.Productid, res.Error)
			continue
		}
		if res.RowsAffected > 0 {
			written++
			ids = append(ids, u.product.Productid)
		}
	}

	fmt.Printf("\nwrote %d product(s)\n", written)

	var stillNull int
	main.Raw(`SELECT COUNT(*) FROM products
	          WHERE COALESCE(imageid,'') <> '' AND cataloguefacts IS NULL`).Scan(&stillNull)
	fmt.Printf("catalogue-linked products still without facts: %d\n", stillNull)

	if len(ids) > 0 {
		fmt.Printf("\nundo:\n  UPDATE products SET cataloguefacts = NULL WHERE productid IN (%s);\n",
			joinInts(ids))
	}
}

func open(hostKey, portKey, userKey, passKey, nameKey string) (*gorm.DB, error) {
	dsn := fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=disable",
		os.Getenv(hostKey), os.Getenv(portKey), os.Getenv(userKey),
		os.Getenv(passKey), os.Getenv(nameKey))
	return gorm.Open(postgres.Open(dsn), &gorm.Config{Logger: logger.Default.LogMode(logger.Silent)})
}

// brandTable mirrors the repository's rule: a brand IS a `brand_<name>` table.
//
// Lowercased and stripped of anything that is not a letter, digit or
// underscore. The table name cannot be parameterized in SQL, so this is the
// one place it is built and it refuses to build anything else.
func brandTable(brand string) string {
	cleaned := strings.Map(func(r rune) rune {
		switch {
		case r >= 'a' && r <= 'z', r >= '0' && r <= '9', r == '_':
			return r
		case r >= 'A' && r <= 'Z':
			return r + 32
		}
		return -1
	}, strings.TrimSpace(brand))

	if cleaned == "" {
		return ""
	}
	return "brand_" + cleaned
}

// factColumnsOf returns which of the fact columns this brand table actually
// has, or nil when the table is not there at all.
func factColumnsOf(db *gorm.DB, table string) []string {
	var have []string
	db.Raw(`SELECT column_name FROM information_schema.columns
	        WHERE table_schema = 'public' AND table_name = ?`, table).Scan(&have)
	if len(have) == 0 {
		return nil
	}

	present := map[string]bool{}
	for _, c := range have {
		present[c] = true
	}
	// image_id is how a product is found at all. Without it the table cannot
	// answer the question, whatever else it holds.
	if !present["image_id"] {
		return nil
	}

	var keep []string
	for _, c := range append(append([]string{}, scalarFacts...), arrayFacts...) {
		if present[c] {
			keep = append(keep, c)
		}
	}
	return keep
}

// factsFor reads one catalogue row and returns only what it actually stated.
//
// An empty field is omitted rather than stored as "" or [], so a reader can
// tell "the catalogue did not say" from "the catalogue said none" — the drawer
// prints a row per fact and an empty string would print an empty row.
func factsFor(db *gorm.DB, table string, cols []string, imageID string) (map[string]any, bool) {
	if len(cols) == 0 {
		return map[string]any{}, true
	}

	selects := make([]string, 0, len(cols))
	for _, c := range cols {
		if isArrayFact(c) {
			selects = append(selects, c+"::text AS "+c)
			continue
		}
		selects = append(selects, c)
	}

	row := map[string]any{}
	res := db.Raw(`SELECT `+strings.Join(selects, ", ")+` FROM `+table+
		` WHERE image_id = ? LIMIT 1`, imageID).Scan(&row)
	if res.Error != nil || res.RowsAffected == 0 {
		return nil, false
	}

	facts := map[string]any{}
	for _, c := range cols {
		raw, ok := row[c]
		if !ok || raw == nil {
			continue
		}
		text := strings.TrimSpace(fmt.Sprintf("%v", raw))
		if text == "" {
			continue
		}
		if isArrayFact(c) {
			values := models.ParsePGArray(text)
			kept := make([]string, 0, len(values))
			for _, v := range values {
				if t := strings.TrimSpace(v); t != "" {
					kept = append(kept, t)
				}
			}
			if len(kept) > 0 {
				facts[c] = kept
			}
			continue
		}
		facts[c] = text
	}
	return facts, true
}

func isArrayFact(name string) bool {
	for _, c := range arrayFacts {
		if c == name {
			return true
		}
	}
	return false
}

func trim(s string, n int) string {
	if len(s) <= n {
		return s
	}
	if n <= 1 {
		return s[:n]
	}
	return s[:n-1] + "…"
}

func joinInts(ids []int) string {
	parts := make([]string, len(ids))
	for i, id := range ids {
		parts[i] = fmt.Sprint(id)
	}
	return strings.Join(parts, ",")
}