package bootstrapimpl

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

type packGenerationConnectorBindingColumnShape struct {
	name       string
	columnType string
	collation  string
}

type packGenerationConnectorBindingTableShape struct {
	tableName string
	columns   []packGenerationConnectorBindingColumnShape
}

var packGenerationConnectorBindingTableShapes = []packGenerationConnectorBindingTableShape{
	{
		tableName: "_System_PackGenerationConnectorBindingDefinitionSet",
		columns: []packGenerationConnectorBindingColumnShape{
			{name: "PackGenerationId", columnType: "varchar(255)", collation: "utf8mb4_unicode_ci"},
			{name: "Protocol", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "DefinitionCount", columnType: "decimal(20,0)"},
			{name: "DefinitionSetDigest", columnType: "char(71)", collation: "utf8mb4_bin"},
			{name: "CreatedDate", columnType: "datetime(6)"},
			{name: "LastModifiedDate", columnType: "datetime(6)"},
		},
	},
	{
		tableName: "_System_PackGenerationConnectorBindingDefinition",
		columns: []packGenerationConnectorBindingColumnShape{
			{name: "Id", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "PackGenerationId", columnType: "varchar(255)", collation: "utf8mb4_unicode_ci"},
			{name: "PackInstallId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "DefinitionOrdinal", columnType: "decimal(20,0)"},
			{name: "Kind", columnType: "varchar(64)", collation: "utf8mb4_bin"},
			{name: "OwnerPack", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "PackPayloadDigest", columnType: "char(85)", collation: "utf8mb4_bin"},
			{name: "BusinessKey", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "DefinitionVersion", columnType: "decimal(20,0)"},
			{name: "DefinitionEnvelopeDigest", columnType: "char(71)", collation: "utf8mb4_bin"},
			{name: "DefinitionEnvelope", columnType: "mediumblob"},
			{name: "CreatedDate", columnType: "datetime(6)"},
			{name: "LastModifiedDate", columnType: "datetime(6)"},
		},
	},
}

type packGenerationConnectorBindingForeignKeyShape struct {
	tableName        string
	columnName       string
	referencedTable  string
	referencedColumn string
	deleteRule       string
	updateRule       string
}

var packGenerationConnectorBindingForeignKeyShapes = []packGenerationConnectorBindingForeignKeyShape{
	{
		tableName:        "_System_PackGenerationConnectorBindingDefinitionSet",
		columnName:       "PackGenerationId",
		referencedTable:  "_System_PackGeneration",
		referencedColumn: "Id",
		deleteRule:       "RESTRICT",
		updateRule:       "RESTRICT",
	},
	{
		tableName:        "_System_PackGenerationConnectorBindingDefinition",
		columnName:       "PackGenerationId",
		referencedTable:  "_System_PackGenerationConnectorBindingDefinitionSet",
		referencedColumn: "PackGenerationId",
		deleteRule:       "RESTRICT",
		updateRule:       "RESTRICT",
	},
}

func verifyPackGenerationConnectorBindingDefinitionAuthoritySchema(rawDB *sql.DB) error {
	if err := verifyPackGenerationConnectorBindingDefinitionColumns(rawDB); err != nil {
		return err
	}
	return verifyPackGenerationConnectorBindingDefinitionForeignKeys(rawDB)
}

func verifyPackGenerationConnectorBindingDefinitionColumns(rawDB *sql.DB) error {
	for _, requiredTable := range packGenerationConnectorBindingTableShapes {
		rows, err := rawDB.Query(`SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND LOWER(TABLE_NAME) = LOWER(?)
ORDER BY ORDINAL_POSITION`, requiredTable.tableName)
		if err != nil {
			return fmt.Errorf("verify connector binding authority table %s columns: %w", requiredTable.tableName, err)
		}

		actual := make(map[string]packGenerationConnectorBindingColumnShape)
		actualCount := 0
		for rows.Next() {
			var (
				name       string
				columnType string
				nullable   string
				collation  sql.NullString
			)
			if err := rows.Scan(&name, &columnType, &nullable, &collation); err != nil {
				_ = rows.Close()
				return fmt.Errorf("verify connector binding authority table %s columns: %w", requiredTable.tableName, err)
			}
			actualCount++
			actual[strings.ToLower(name)] = packGenerationConnectorBindingColumnShape{
				name:       name,
				columnType: strings.ToLower(columnType),
				collation:  strings.ToLower(collation.String),
			}
			if !strings.EqualFold(nullable, "NO") {
				_ = rows.Close()
				return fmt.Errorf(
					"schema readiness drift: %s.%s must be NOT NULL; run 'cmd/setup apply' to reconcile schema",
					requiredTable.tableName,
					name,
				)
			}
		}
		if err := rows.Err(); err != nil {
			_ = rows.Close()
			return fmt.Errorf("verify connector binding authority table %s columns: %w", requiredTable.tableName, err)
		}
		if err := rows.Close(); err != nil {
			return fmt.Errorf("verify connector binding authority table %s columns: %w", requiredTable.tableName, err)
		}
		if actualCount != len(requiredTable.columns) {
			return fmt.Errorf(
				"schema readiness drift: %s has %d columns, require exact immutable authority shape with %d; run 'cmd/setup apply' to reconcile schema",
				requiredTable.tableName,
				actualCount,
				len(requiredTable.columns),
			)
		}
		for _, required := range requiredTable.columns {
			column, found := actual[strings.ToLower(required.name)]
			if !found || column.columnType != required.columnType || column.collation != required.collation {
				return fmt.Errorf(
					"schema readiness drift: %s.%s must be %s collation=%q; run 'cmd/setup apply' to reconcile schema",
					requiredTable.tableName,
					required.name,
					required.columnType,
					required.collation,
				)
			}
		}
	}
	return nil
}

func verifyPackGenerationConnectorBindingDefinitionForeignKeys(rawDB *sql.DB) error {
	for _, required := range packGenerationConnectorBindingForeignKeyShapes {
		rows, err := rawDB.Query(`SELECT
k.CONSTRAINT_NAME, k.COLUMN_NAME, k.REFERENCED_TABLE_SCHEMA, DATABASE(), k.REFERENCED_TABLE_NAME,
k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS r
  ON r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
 AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME
 AND r.TABLE_NAME = k.TABLE_NAME
WHERE k.TABLE_SCHEMA = DATABASE()
  AND LOWER(k.TABLE_NAME) = LOWER(?)
  AND k.REFERENCED_TABLE_NAME IS NOT NULL`, required.tableName)
		if err != nil {
			return fmt.Errorf("verify connector binding authority foreign key on %s: %w", required.tableName, err)
		}
		matched := 0
		rowCount := 0
		for rows.Next() {
			var constraintName, columnName, referencedSchema, currentSchema, referencedTable, referencedColumn, deleteRule, updateRule string
			if err := rows.Scan(
				&constraintName,
				&columnName,
				&referencedSchema,
				&currentSchema,
				&referencedTable,
				&referencedColumn,
				&deleteRule,
				&updateRule,
			); err != nil {
				_ = rows.Close()
				return fmt.Errorf("verify connector binding authority foreign key on %s: %w", required.tableName, err)
			}
			_ = constraintName
			rowCount++
			if strings.EqualFold(columnName, required.columnName) &&
				referencedSchema == currentSchema &&
				strings.EqualFold(referencedTable, required.referencedTable) &&
				strings.EqualFold(referencedColumn, required.referencedColumn) &&
				packGenerationConnectorBindingForeignKeyRuleMatches(deleteRule, required.deleteRule) &&
				packGenerationConnectorBindingForeignKeyRuleMatches(updateRule, required.updateRule) {
				matched++
			}
		}
		if err := rows.Err(); err != nil {
			_ = rows.Close()
			return fmt.Errorf("verify connector binding authority foreign key on %s: %w", required.tableName, err)
		}
		if err := rows.Close(); err != nil {
			return fmt.Errorf("verify connector binding authority foreign key on %s: %w", required.tableName, err)
		}
		if rowCount != 1 || matched != 1 {
			return fmt.Errorf(
				"schema readiness drift: %s must have exactly one foreign key %s -> %s(%s) ON DELETE %s ON UPDATE %s; run 'cmd/setup apply' to reconcile schema",
				required.tableName,
				required.columnName,
				required.referencedTable,
				required.referencedColumn,
				required.deleteRule,
				required.updateRule,
			)
		}
	}
	return nil
}

func packGenerationConnectorBindingForeignKeyRuleMatches(actual, required string) bool {
	if strings.EqualFold(actual, required) {
		return true
	}
	// TiDB exposes the default immediate restrictive action as NO ACTION.
	// It has the same no-parent-mutation semantics as RESTRICT because TiDB
	// does not implement deferred foreign-key constraint checking.
	return strings.EqualFold(required, "RESTRICT") && strings.EqualFold(actual, "NO ACTION")
}