package persistence

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

	"github.com/nexuscrm/kernel/pkg/schema"
)

const managedPhysicalIndexInspectionSQL = `SELECT NON_UNIQUE, SEQ_IN_INDEX, COLUMN_NAME, SUB_PART
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND INDEX_NAME = ?
ORDER BY SEQ_IN_INDEX`

// reconcileManagedPhysicalSchemaEvolution owns narrowly scoped,
// catalog-driven migrations that CREATE TABLE IF NOT EXISTS cannot perform for
// an already-existing managed table. It is deliberately not a generic index
// reconciler: each migration names its exact old and new authority shapes.
func (r *SchemaRepository) reconcileManagedPhysicalSchemaEvolution(ctx context.Context, def schema.TableDefinition) error {
	if def.TableName == integrationOutboundCommandTable {
		return r.reconcileIntegrationOutboundDependencySchema(ctx, def)
	}
	if declaresWorkerQueueIndexes(def) {
		return r.reconcileWorkerQueueIndexes(ctx, def)
	}
	if declaresRuntimeOutboundIntentOwnerIndex(def) {
		return r.reconcileRuntimeOutboundIntentOwnerIndex(ctx)
	}
	if declaresPermissionAuthoringAuditDisplayCapacity(def) {
		return r.reconcilePermissionAuthoringAuditDisplayCapacity(ctx)
	}
	if declaresFileUploadLifecycleIndex(def) {
		return r.reconcileFileUploadLifecycleIndex(ctx)
	}
	if declaresRuntimePlatformCommandPartitionIndex(def) {
		return r.reconcileRuntimePlatformCommandPartitionIndex(ctx)
	}
	if declaresConnectorV2ReceiptTransportIdentity(def) {
		return r.reconcileConnectorV2ReceiptTransportIdentityIndex(ctx)
	}
	if declaresConnectorV2ReceiptAccountIndex(def) {
		if !declaresConnectorV2ReceiptProtocolWidth(def) {
			return fmt.Errorf("reconcile connector V2 receipt schema: catalog definition does not declare canonical ReceiptProtocolVersion VARCHAR(32)")
		}
		if err := r.reconcileConnectorV2ReceiptProtocolWidth(ctx); err != nil {
			return err
		}
		return r.reconcileConnectorV2ReceiptAccountIndex(ctx)
	}
	return nil
}

func inspectManagedPhysicalIndex(
	ctx context.Context,
	db *sql.DB,
	tableName string,
	indexName string,
) ([]string, bool, bool, error) {
	rows, err := db.QueryContext(
		ctx,
		managedPhysicalIndexInspectionSQL,
		tableName,
		indexName,
	)
	if err != nil {
		return nil, false, false, err
	}
	defer rows.Close()

	var (
		columns   []string
		unique    = true
		hasPrefix bool
	)
	for rows.Next() {
		var (
			nonUnique int
			position  int
			column    string
			subPart   sql.NullInt64
		)
		if err := rows.Scan(&nonUnique, &position, &column, &subPart); err != nil {
			return nil, false, false, err
		}
		if position != len(columns)+1 {
			return nil, false, false, fmt.Errorf("index column %s has position %d, want %d", column, position, len(columns)+1)
		}
		unique = unique && nonUnique == 0
		hasPrefix = hasPrefix || subPart.Valid
		columns = append(columns, column)
	}
	if err := rows.Err(); err != nil {
		return nil, false, false, err
	}
	return columns, unique, hasPrefix, nil
}

func equalExactColumnNames(left, right []string) bool {
	if len(left) != len(right) {
		return false
	}
	for i := range left {
		if !strings.EqualFold(left[i], right[i]) {
			return false
		}
	}
	return true
}