package controlplane

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	bootstate "github.com/nexuscrm/db/pkg/bootstate"
	"github.com/nexuscrm/kernel/pkg/utils"
)

const migrationIdPackInstallIdBackfill = "20260528_pack_install_id_backfill"

// MigratePackInstallIdBackfill fills _System_InstalledPack.PackInstallId for
// any row that still carries the upgrade-time default (empty string or NULL).
// Each affected row gets a fresh UUID — that becomes its per-install identity
// from this point forward. Subsequent installs/rebirths mint new UUIDs via
// markInstalledPackInstalling / markInstalledPackFailed.
//
// Rationale: source-backed connector packs are not auto-reapplied during
// ApplyAll/reconcile, so an upgraded org can keep PackInstallId=” on its
// existing rows indefinitely. Downstream substrate (NCRM-INT-05 connector
// event queue, PR #1498) keys per-incarnation state on PackInstallId; blank
// values collapse all incarnations to one scope. Backfilling once on first
// startup after this migration closes that gap.
//
// Idempotent: stamp-guarded by _System_Migration. The WHERE predicate also
// skips rows that already have an identity. Safe to re-run.
func MigratePackInstallIdBackfill(ctx context.Context, db *sql.DB) error {
	if db == nil {
		return fmt.Errorf("MigratePackInstallIdBackfill requires a database")
	}
	applied, err := bootstate.MigrationApplied(ctx, db, migrationIdPackInstallIdBackfill)
	if err != nil {
		return err
	}
	if applied {
		log.Println("   ⏭  MigratePackInstallIdBackfill: already applied; skipping")
		return nil
	}

	n, err := backfillBlankPackInstallIds(ctx, db)
	if err != nil {
		return fmt.Errorf("MigratePackInstallIdBackfill: %w", err)
	}

	if err := bootstate.RecordMigrationApplied(
		ctx,
		db,
		migrationIdPackInstallIdBackfill,
		1,
		"Backfill blank PackInstallId values on _System_InstalledPack rows upgraded before this column was introduced.",
	); err != nil {
		return err
	}

	if n > 0 {
		log.Printf("   🧼 MigratePackInstallIdBackfill: assigned fresh PackInstallId to %d row(s)", n)
	}
	return nil
}

// backfillBlankPackInstallIds is the idempotent inner workhorse (not stamp-guarded).
// Exported for direct use in tests.
func backfillBlankPackInstallIds(ctx context.Context, db *sql.DB) (int, error) {
	rows, err := db.QueryContext(ctx,
		`SELECT Id FROM _System_InstalledPack WHERE PackInstallId = '' OR PackInstallId IS NULL`)
	if err != nil {
		return 0, fmt.Errorf("query _System_InstalledPack for PackInstallId backfill: %w", err)
	}
	defer rows.Close()

	var ids []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return 0, fmt.Errorf("scan _System_InstalledPack.Id for PackInstallId backfill: %w", err)
		}
		ids = append(ids, id)
	}
	if err := rows.Err(); err != nil {
		return 0, fmt.Errorf("iterate _System_InstalledPack rows for PackInstallId backfill: %w", err)
	}

	updated := 0
	for _, id := range ids {
		res, err := db.ExecContext(ctx,
			`UPDATE _System_InstalledPack SET PackInstallId = ? WHERE Id = ? AND (PackInstallId = '' OR PackInstallId IS NULL)`,
			utils.GenerateID(), id)
		if err != nil {
			return updated, fmt.Errorf("backfill PackInstallId for installed pack %s: %w", id, err)
		}
		if n, _ := res.RowsAffected(); n > 0 {
			updated++
		}
	}
	return updated, nil
}