package bootstrapimpl

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

	"github.com/nexuscrm/db"
	"github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/shared/pkg/constants"
)

var connectedDeliveryTableNames = []string{
	constants.TableConnectedPrincipal,
	constants.TableConnectedAuthorizationRequest,
	constants.TableConnectedPrincipalToken,
	constants.TableConnectedDeliveryJob,
	constants.TableConnectedDeliveryPayload,
}

func isConnectedDeliveryManagedTable(tableName string) bool {
	for _, connectedTableName := range connectedDeliveryTableNames {
		if strings.EqualFold(tableName, connectedTableName) {
			return true
		}
	}
	return false
}

type connectedDeliveryIndexShape struct {
	name    string
	columns string
	unique  bool
}

type connectedDeliveryForeignKeyShape struct {
	referencedTable  string
	referencedColumn string
	deleteRule       string
}

func connectedDeliveryColumnPrimaryIndex(def schema.TableDefinition) (schema.IndexDefinition, bool) {
	managed := false
	for _, tableName := range connectedDeliveryTableNames {
		if def.TableName == tableName {
			managed = true
			break
		}
	}
	if !managed {
		return schema.IndexDefinition{}, false
	}
	for _, idx := range def.Indices {
		if idx.Primary {
			return schema.IndexDefinition{}, false
		}
	}
	var columns []string
	for _, col := range def.Columns {
		if col.PrimaryKey {
			columns = append(columns, col.Name)
		}
	}
	if len(columns) == 0 {
		return schema.IndexDefinition{}, false
	}
	return schema.IndexDefinition{Columns: columns, Primary: true}, true
}

// VerifyConnectedDeliverySchema performs the operator-facing exact-convergence
// check for the dormant connected-delivery substrate. It intentionally stays
// out of pod readiness until a runtime consumer ships; completed-marker warm
// upgrades may skip schema apply and must remain restart-safe in the meantime.
func VerifyConnectedDeliverySchema(db *database.TiDBConnection) error {
	return verifyConnectedDeliverySchema(db.DB())
}

// verifyConnectedDeliverySchema fails closed when the connected-principal
// lifecycle substrate has not converged to the pinned kernel catalog.
func verifyConnectedDeliverySchema(rawDB *sql.DB) error {
	wanted := make(map[string]schema.TableDefinition, len(connectedDeliveryTableNames))
	for _, def := range GetManagedTableDefinitions() {
		for _, tableName := range connectedDeliveryTableNames {
			if def.TableName == tableName {
				wanted[tableName] = def
				break
			}
		}
	}

	physical, err := LoadPhysicalColumnShapes(rawDB)
	if err != nil {
		return fmt.Errorf("load connected delivery schema: %w", err)
	}
	extras, err := loadConnectedDeliveryColumnExtras(rawDB)
	if err != nil {
		return fmt.Errorf("load connected delivery column attributes: %w", err)
	}
	indices, err := loadConnectedDeliveryIndices(rawDB)
	if err != nil {
		return fmt.Errorf("load connected delivery indices: %w", err)
	}
	foreignKeys, err := loadConnectedDeliveryForeignKeys(rawDB)
	if err != nil {
		return fmt.Errorf("load connected delivery foreign keys: %w", err)
	}

	var drift []string
	var hasRepairableDrift, hasManualDrift bool
	for _, tableName := range connectedDeliveryTableNames {
		def, ok := wanted[tableName]
		if !ok {
			drift = append(drift, tableName+" catalog definition is missing")
			hasManualDrift = true
			continue
		}
		tableKey := strings.ToLower(tableName)
		columns, ok := physical[tableKey]
		if !ok {
			drift = append(drift, tableName+" table is missing")
			hasRepairableDrift = true
			continue
		}
		var primaryColumns []string
		for _, col := range def.Columns {
			actual, ok := columns[strings.ToLower(col.Name)]
			if !ok {
				drift = append(drift, tableName+"."+col.Name+" is missing")
				hasRepairableDrift = true
				continue
			}
			if !ColumnShapeMatchesForTable(tableName, actual, col) {
				drift = append(drift, tableName+"."+col.Name+" shape does not match the managed catalog")
				hasRepairableDrift = true
			}
			if col.PrimaryKey {
				primaryColumns = append(primaryColumns, col.Name)
			}
			if (col.Name == "CreatedDate" || col.Name == "LastModifiedDate") && strings.Contains(strings.ToLower(extras[tableKey][strings.ToLower(col.Name)]), "on update") {
				drift = append(drift, tableName+"."+col.Name+" has forbidden ON UPDATE behavior")
				hasManualDrift = true
			}
		}
		for _, idx := range def.Indices {
			if idx.Primary {
				primaryColumns = idx.Columns
				break
			}
		}
		if len(primaryColumns) > 0 {
			primary, ok := connectedDeliveryNamedIndex(indices[tableKey], "PRIMARY")
			if !ok {
				drift = append(drift, tableName+" primary key is missing")
				hasRepairableDrift = true
			} else if primary.columns != normalizeConnectedDeliveryColumns(primaryColumns) || !primary.unique {
				drift = append(drift, tableName+" primary key composition/uniqueness does not match the managed catalog")
				hasManualDrift = true
			}
		}
		for _, idx := range def.Indices {
			if idx.Primary || connectedDeliveryIndexMatches(indices[tableKey], "", idx.Columns, idx.Unique) {
				continue
			}
			if actual, ok := connectedDeliveryIndexByColumns(indices[tableKey], idx.Columns); ok && !idx.Unique && actual.unique {
				drift = append(drift, tableName+" index "+idx.Name+" is unique but the managed catalog expects non-unique")
				hasManualDrift = true
				continue
			}
			drift = append(drift, tableName+" index "+idx.Name+" composition/uniqueness does not match the managed catalog")
			hasRepairableDrift = true
		}
		for _, fk := range def.ForeignKeys {
			referencedTable, referencedColumn, err := parseConnectedDeliveryReference(fk.References)
			if err != nil {
				drift = append(drift, tableName+"."+fk.Column+" catalog foreign key reference is invalid")
				hasManualDrift = true
				continue
			}
			if !connectedDeliveryForeignKeyMatches(foreignKeys[tableKey][strings.ToLower(fk.Column)], referencedTable, referencedColumn, fk.OnDelete) {
				drift = append(drift, tableName+"."+fk.Column+" foreign key target/delete rule does not match the managed catalog")
				hasManualDrift = true
			}
		}
	}
	if len(drift) > 0 {
		var guidance []string
		if hasRepairableDrift {
			guidance = append(guidance, "run 'cmd/setup apply' for repairable table, column, missing-primary-key, and index drift")
		}
		if hasManualDrift {
			guidance = append(guidance, "manual remediation is required for catalog-definition, existing malformed primary-key composition, expected-non-unique/actual-unique index, foreign-key, and ON UPDATE drift because 'cmd/setup apply' does not repair them")
		}
		return fmt.Errorf("connected delivery schema drift: %s; %s", strings.Join(drift, ", "), strings.Join(guidance, "; "))
	}
	return nil
}

func loadConnectedDeliveryColumnExtras(rawDB *sql.DB) (map[string]map[string]string, error) {
	rows, err := rawDB.Query("SELECT TABLE_NAME, COLUMN_NAME, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	extras := make(map[string]map[string]string)
	for rows.Next() {
		var tableName, columnName, extra string
		if err := rows.Scan(&tableName, &columnName, &extra); err != nil {
			return nil, err
		}
		tableKey := strings.ToLower(tableName)
		if extras[tableKey] == nil {
			extras[tableKey] = make(map[string]string)
		}
		extras[tableKey][strings.ToLower(columnName)] = extra
	}
	return extras, rows.Err()
}

func loadConnectedDeliveryIndices(rawDB *sql.DB) (map[string][]connectedDeliveryIndexShape, error) {
	rows, err := rawDB.Query(`SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
		FROM INFORMATION_SCHEMA.STATISTICS
		WHERE TABLE_SCHEMA = DATABASE()
		GROUP BY TABLE_NAME, INDEX_NAME, NON_UNIQUE`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	indices := make(map[string][]connectedDeliveryIndexShape)
	for rows.Next() {
		var tableName, indexName, columns string
		var nonUnique int
		if err := rows.Scan(&tableName, &indexName, &nonUnique, &columns); err != nil {
			return nil, err
		}
		indices[strings.ToLower(tableName)] = append(indices[strings.ToLower(tableName)], connectedDeliveryIndexShape{
			name:    indexName,
			columns: normalizeConnectedDeliveryColumns(strings.Split(columns, ",")),
			unique:  nonUnique == 0,
		})
	}
	return indices, rows.Err()
}

func connectedDeliveryIndexMatches(actual []connectedDeliveryIndexShape, requiredName string, columns []string, unique bool) bool {
	wantedColumns := normalizeConnectedDeliveryColumns(columns)
	for _, shape := range actual {
		if requiredName != "" && !strings.EqualFold(shape.name, requiredName) {
			continue
		}
		if requiredName == "" && strings.EqualFold(shape.name, "PRIMARY") {
			continue
		}
		if shape.columns == wantedColumns && shape.unique == unique {
			return true
		}
	}
	return false
}

func connectedDeliveryNamedIndex(actual []connectedDeliveryIndexShape, name string) (connectedDeliveryIndexShape, bool) {
	for _, shape := range actual {
		if strings.EqualFold(shape.name, name) {
			return shape, true
		}
	}
	return connectedDeliveryIndexShape{}, false
}

func connectedDeliveryIndexByColumns(actual []connectedDeliveryIndexShape, columns []string) (connectedDeliveryIndexShape, bool) {
	wantedColumns := normalizeConnectedDeliveryColumns(columns)
	for _, shape := range actual {
		if !strings.EqualFold(shape.name, "PRIMARY") && shape.columns == wantedColumns {
			return shape, true
		}
	}
	return connectedDeliveryIndexShape{}, false
}

func normalizeConnectedDeliveryColumns(columns []string) string {
	normalized := make([]string, len(columns))
	for i, column := range columns {
		normalized[i] = strings.ToLower(strings.TrimSpace(column))
	}
	return strings.Join(normalized, ",")
}

func loadConnectedDeliveryForeignKeys(rawDB *sql.DB) (map[string]map[string][]connectedDeliveryForeignKeyShape, error) {
	rows, err := rawDB.Query(`SELECT k.TABLE_NAME, k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, rc.DELETE_RULE
		FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
		JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
		  ON rc.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
		 AND rc.TABLE_NAME = k.TABLE_NAME
		 AND rc.CONSTRAINT_NAME = k.CONSTRAINT_NAME
		WHERE k.TABLE_SCHEMA = DATABASE()
		  AND k.REFERENCED_TABLE_NAME IS NOT NULL`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	foreignKeys := make(map[string]map[string][]connectedDeliveryForeignKeyShape)
	for rows.Next() {
		var tableName, columnName string
		var shape connectedDeliveryForeignKeyShape
		if err := rows.Scan(&tableName, &columnName, &shape.referencedTable, &shape.referencedColumn, &shape.deleteRule); err != nil {
			return nil, err
		}
		tableKey := strings.ToLower(tableName)
		if foreignKeys[tableKey] == nil {
			foreignKeys[tableKey] = make(map[string][]connectedDeliveryForeignKeyShape)
		}
		columnKey := strings.ToLower(columnName)
		foreignKeys[tableKey][columnKey] = append(foreignKeys[tableKey][columnKey], shape)
	}
	return foreignKeys, rows.Err()
}

func connectedDeliveryForeignKeyMatches(actual []connectedDeliveryForeignKeyShape, referencedTable, referencedColumn, deleteRule string) bool {
	if len(actual) != 1 {
		return false
	}
	return strings.EqualFold(actual[0].referencedTable, referencedTable) &&
		strings.EqualFold(actual[0].referencedColumn, referencedColumn) &&
		strings.EqualFold(actual[0].deleteRule, deleteRule)
}

func parseConnectedDeliveryReference(reference string) (string, string, error) {
	open := strings.LastIndex(reference, "(")
	if open <= 0 || !strings.HasSuffix(reference, ")") {
		return "", "", fmt.Errorf("invalid reference %q", reference)
	}
	return reference[:open], reference[open+1 : len(reference)-1], nil
}