pay/internal/model/schema_upgrade.go

package model

import (
	"fmt"

	"gorm.io/gorm"
)

// UpgradeWebhookDeliveryIndex is an idempotent, dialect-aware schema-upgrade
// step meant to run immediately after AutoMigrate (both in main.go's
// autoMigrate and in OpenTestDB).
//
// Why this exists: GORM's AutoMigrate only checks an index's EXISTENCE by
// name, never its column set. A P2-era database already has a 2-column
// uq_delivery(out_trade_no,event_type) unique index; when the model widened
// it to uq_delivery(out_trade_no,event_type,refund_id) (P4), AutoMigrate sees
// "uq_delivery exists" and leaves the stale 2-column index untouched. From
// then on store.EnqueueDelivery's 3-column `ON CONFLICT (out_trade_no,
// event_type,refund_id)` matches no real unique constraint and fails on
// EVERY call — including plain payment webhooks, not just refunds.
//
// This function explicitly detects that mismatch and repairs it:
//  1. If uq_delivery already covers refund_id, no-op (safe to call every
//     startup / every test DB open).
//  2. Otherwise: backfill any NULL refund_id to the empty string (pre-upgrade rows never
//     had the column, or had it added without a default), drop the stale
//     2-column index, and recreate it to match the current 3-column model.
func UpgradeWebhookDeliveryIndex(db *gorm.DB) error {
	covers, err := uqDeliveryCoversRefundID(db)
	if err != nil {
		return fmt.Errorf("model.UpgradeWebhookDeliveryIndex: detect: %w", err)
	}
	if covers {
		return nil
	}

	// Normalize before widening: rows created under the old 2-column index
	// may have NULL refund_id (payment events never set it, and a plain
	// AutoMigrate-added column can land NULL without an explicit default).
	// This also matches the RefundID `not null;default:''` column contract.
	if err := db.Exec(`UPDATE webhook_deliveries SET refund_id = '' WHERE refund_id IS NULL`).Error; err != nil {
		return fmt.Errorf("model.UpgradeWebhookDeliveryIndex: backfill refund_id: %w", err)
	}

	if db.Migrator().HasIndex(&WebhookDelivery{}, "uq_delivery") {
		if err := db.Migrator().DropIndex(&WebhookDelivery{}, "uq_delivery"); err != nil {
			return fmt.Errorf("model.UpgradeWebhookDeliveryIndex: drop stale uq_delivery: %w", err)
		}
	}
	if err := db.Migrator().CreateIndex(&WebhookDelivery{}, "uq_delivery"); err != nil {
		return fmt.Errorf("model.UpgradeWebhookDeliveryIndex: create widened uq_delivery: %w", err)
	}
	return nil
}

// uqDeliveryCoversRefundID reports whether the uq_delivery index on
// webhook_deliveries already includes refund_id as one of its columns.
// Dialect-aware: sqlite is queried via pragma_index_info (equivalent to
// `PRAGMA index_info(uq_delivery)`, exercised by the sqlite branch of the
// regression test); mysql via information_schema.statistics.
//
// If the index doesn't exist at all yet (fresh DB — AutoMigrate creates it
// straight from the current 3-column model tags), this reports covers=true
// so the caller treats it as nothing-to-widen; AutoMigrate already did the
// right thing in that case.
func uqDeliveryCoversRefundID(db *gorm.DB) (bool, error) {
	switch db.Dialector.Name() {
	case "sqlite":
		return sqliteUqDeliveryCoversRefundID(db)
	case "mysql":
		return mysqlUqDeliveryCoversRefundID(db)
	default:
		return false, fmt.Errorf("unsupported dialect %q for uq_delivery upgrade check", db.Dialector.Name())
	}
}

// sqliteUqDeliveryCoversRefundID — tested branch (glebarez/sqlite, used in
// all package tests via OpenTestDB).
func sqliteUqDeliveryCoversRefundID(db *gorm.DB) (bool, error) {
	var cols []struct {
		Name string `gorm:"column:name"`
	}
	if err := db.Raw(`SELECT name FROM pragma_index_info(?)`, "uq_delivery").Scan(&cols).Error; err != nil {
		return false, err
	}
	if len(cols) == 0 {
		// Index doesn't exist yet — AutoMigrate will create it fresh from
		// the current (3-column) model; nothing for us to widen.
		return true, nil
	}
	for _, c := range cols {
		if c.Name == "refund_id" {
			return true, nil
		}
	}
	return false, nil
}

// mysqlUqDeliveryCoversRefundID — reasoned, not exercised by tests (no mysql
// available in this environment; prod uses mysql, tests use glebarez/sqlite
// per repo convention). information_schema.statistics lists one row per
// (index_name, column) pair for the current database/table, which is the
// mysql analogue of sqlite's pragma_index_info.
func mysqlUqDeliveryCoversRefundID(db *gorm.DB) (bool, error) {
	var cols []struct {
		ColumnName string `gorm:"column:COLUMN_NAME"`
	}
	if err := db.Raw(
		`SELECT COLUMN_NAME FROM information_schema.statistics
		 WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ?`,
		"webhook_deliveries", "uq_delivery",
	).Scan(&cols).Error; err != nil {
		return false, err
	}
	if len(cols) == 0 {
		return true, nil
	}
	for _, c := range cols {
		if c.ColumnName == "refund_id" {
			return true, nil
		}
	}
	return false, nil
}