package controlplane

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

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

const migrationIDDropConnectorEventLegacyRetirementIndex = "20260723_drop_connector_event_legacy_retirement_index"

const (
	connectorEventTableName                 = "_System_ConnectorEvent"
	connectorEventLegacyRetirementIndexName = "idx_connector_event_legacy_retire"
)

// DropConnectorEventLegacyRetirementIndex removes the obsolete index used by
// the retired Event-owned execution compatibility path. New catalogs omit the
// index; this stamp-guarded migration converges warm-upgraded databases.
func DropConnectorEventLegacyRetirementIndex(ctx context.Context, db *sql.DB) error {
	if db == nil {
		return fmt.Errorf("DropConnectorEventLegacyRetirementIndex requires a database")
	}

	applied, err := bootstate.MigrationApplied(ctx, db, migrationIDDropConnectorEventLegacyRetirementIndex)
	if err != nil {
		return err
	}
	if applied {
		log.Printf("   DropConnectorEventLegacyRetirementIndex: already applied; skipping")
		return nil
	}

	const lookupSQL = `
		SELECT 1
		FROM INFORMATION_SCHEMA.STATISTICS
		WHERE TABLE_SCHEMA = DATABASE()
		  AND TABLE_NAME = ?
		  AND INDEX_NAME = ?
		LIMIT 1`

	var present int
	err = db.QueryRowContext(ctx, lookupSQL, connectorEventTableName, connectorEventLegacyRetirementIndexName).Scan(&present)
	indexAlreadyAbsent := err == sql.ErrNoRows
	if err != nil && !indexAlreadyAbsent {
		return fmt.Errorf("DropConnectorEventLegacyRetirementIndex lookup: %w", err)
	}

	if indexAlreadyAbsent {
		log.Printf("   DropConnectorEventLegacyRetirementIndex: index already absent; stamping")
	} else {
		const dropSQL = "ALTER TABLE `_System_ConnectorEvent` DROP INDEX `idx_connector_event_legacy_retire`"
		if _, err := db.ExecContext(ctx, dropSQL); err != nil {
			return fmt.Errorf("DropConnectorEventLegacyRetirementIndex: %w (stmt=%s)", err, dropSQL)
		}
		log.Printf("   DropConnectorEventLegacyRetirementIndex: dropped %s.%s", connectorEventTableName, connectorEventLegacyRetirementIndexName)
	}

	if err := bootstate.RecordMigrationApplied(
		ctx,
		db,
		migrationIDDropConnectorEventLegacyRetirementIndex,
		1,
		"Drop obsolete _System_ConnectorEvent idx_connector_event_legacy_retire after Event-owned execution retirement (NexusCRM #3980).",
	); err != nil {
		return fmt.Errorf("DropConnectorEventLegacyRetirementIndex stamp: %w", err)
	}
	log.Printf("   DropConnectorEventLegacyRetirementIndex: migration stamped")
	return nil
}