package migrate_record

import (
	"database/sql"
	"strings"

	"github.com/pingcap/dbaas/billing2/db/entity"
	"gorm.io/gorm"
)

// 2026-08-04-stripe-invoice-paying-schema-fix
//
// Adds stripe_invoice_paying + stripe_invoice_push_carryover tables, two new
// fields on billing2_invoices, and finalises the stripe_invoice_paying
// schema to match docs/stripe-invoice-auto-paying-design.md.
//
// Schema enforced here (warm-DB fixes; fresh DBs get the same shape from the
// entity tag's full DDL via InitDB AutoMigrate):
//
//   - active_flag: STORED GENERATED column derived from status. NULL when
//     status is completed/failed, 1 when status is pending/running. The
//     (month, active_flag) unique index enforces "at most one active
//     paying per month"; NULL values do not collide in MySQL.
//   - idx_pending (status, created_at): composite index supporting the
//     worker's claim query
//     `UPDATE ... WHERE status='pending' ORDER BY created_at ASC LIMIT 1`.
//   - Drop the legacy idx_status_created single-column index from the
//     previous entity tag — it is a redundant prefix of idx_pending.
//
// Idempotency: every DDL step is guarded by an information_schema lookup so
// the migration is safe to re-run on partially-migrated DBs and on warm
// DBs where the table was created by the legacy struct definition.
var _ = AddMigrateRecord(
	"2026-08-04-stripe-invoice-paying",
	func(db *gorm.DB) error {
		// AutoMigrate brings the table shape (and the inline-DDL
		// active_flag column) up to the entity definition on both
		// fresh and warm DBs. On warm DBs this is a no-op for
		// existing tables; on fresh DBs it emits the full DDL
		// including the GENERATED ALWAYS clause because the type tag
		// preserves it verbatim.
		if err := db.AutoMigrate(entity.Models...); err != nil {
			return err
		}

		// 1. active_flag must be STORED GENERATED. On warm DBs the
		//    column may currently be a plain TINYINT (the original
		//    struct wrote active_flag from the application side), so
		//    drop and re-add it. Dropping the column also drops the
		//    unique index uk_active_paying transitively; we re-create
		//    it below.
		var genExpr sql.NullString
		if err := db.Raw(
			"SELECT generation_expression FROM information_schema.columns "+
				"WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?",
			"stripe_invoice_paying", "active_flag",
		).Scan(&genExpr).Error; err != nil {
			return err
		}
		if !genExpr.Valid || genExpr.String == "" {
			// Drop the column if it exists. If the column is missing
			// (fresh DB without active_flag), the DROP errors with
			// MySQL 1091 — ignore that case and rely on the ADD
			// COLUMN below.
			if err := db.Exec(
				"ALTER TABLE stripe_invoice_paying DROP COLUMN active_flag",
			).Error; err != nil && !isMySQLMissingColumnErr(err) {
				return err
			}
			if err := db.Exec(
				"ALTER TABLE stripe_invoice_paying ADD COLUMN active_flag TINYINT " +
					"GENERATED ALWAYS AS (CASE WHEN status IN ('pending','running') THEN 1 ELSE NULL END) STORED",
			).Error; err != nil {
				return err
			}
		}

		// 2. uk_active_paying(month, active_flag). Idempotent guard
		//    via information_schema.statistics.
		var ukCount int
		if err := db.Raw(
			"SELECT COUNT(*) FROM information_schema.statistics "+
				"WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ?",
			"stripe_invoice_paying", "uk_active_paying",
		).Scan(&ukCount).Error; err != nil {
			return err
		}
		if ukCount == 0 {
			if err := db.Exec(
				"CREATE UNIQUE INDEX `uk_active_paying` " +
					"ON `stripe_invoice_paying` (`month`, `active_flag`)",
			).Error; err != nil {
				return err
			}
		}

		// 3. Drop legacy single-column idx_status_created if it
		//    exists (it was created by the previous entity tag, and
		//    is redundant with idx_pending's leading column).
		var legacyCount int
		if err := db.Raw(
			"SELECT COUNT(*) FROM information_schema.statistics "+
				"WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ?",
			"stripe_invoice_paying", "idx_status_created",
		).Scan(&legacyCount).Error; err != nil {
			return err
		}
		if legacyCount > 0 {
			if err := db.Exec(
				"ALTER TABLE stripe_invoice_paying DROP INDEX `idx_status_created`",
			).Error; err != nil {
				return err
			}
		}

		// 4. idx_pending(status, created_at). AutoMigrate handles
		//    fresh DBs via the entity tag, but warm DBs need an
		//    explicit CREATE INDEX guarded by information_schema.
		var idxCount int
		if err := db.Raw(
			"SELECT COUNT(*) FROM information_schema.statistics "+
				"WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ?",
			"stripe_invoice_paying", "idx_pending",
		).Scan(&idxCount).Error; err != nil {
			return err
		}
		if idxCount == 0 {
			if err := db.Exec(
				"CREATE INDEX `idx_pending` " +
					"ON `stripe_invoice_paying` (`status`, `created_at`)",
			).Error; err != nil {
				return err
			}
		}

		// ANALYZE so the optimizer picks up idx_pending's
		// cardinality on first use; without it the new index can be
		// ignored in favour of a table scan.
		if err := db.Exec("ANALYZE TABLE stripe_invoice_paying").Error; err != nil {
			return err
		}
		return nil
	},
)

// isMySQLMissingColumnErr matches the MySQL error raised when ALTER TABLE
// tries to drop a column that does not exist ("check that column/key
// exists" / error code 1091).
func isMySQLMissingColumnErr(err error) bool {
	if err == nil {
		return false
	}
	msg := err.Error()
	return strings.Contains(msg, "Error 1091") ||
		strings.Contains(msg, "check that column/key exists")
}