package serviceconfiguration

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

const (
	serviceConfigurationGenerationTable          = "_System_ConnectorServiceConfigurationGeneration"
	serviceConfigurationAccountTable             = "_System_ConnectorServiceConfigurationAccount"
	serviceConfigurationBindingTable             = "_System_ConnectorServiceConfigurationBinding"
	serviceConfigurationCredentialSelectionTable = "_System_ConnectorServiceConfigurationCredentialSelection"

	inspectServiceConfigurationColumnsSQL = `SELECT COLUMN_NAME, LOWER(COLUMN_TYPE), IS_NULLABLE, COLLATION_NAME, COLUMN_DEFAULT, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
ORDER BY ORDINAL_POSITION`

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

	inspectServiceConfigurationForeignKeysSQL = `SELECT kcu.CONSTRAINT_NAME, kcu.COLUMN_NAME,
       kcu.REFERENCED_TABLE_SCHEMA, kcu.REFERENCED_TABLE_NAME,
       kcu.REFERENCED_COLUMN_NAME, DATABASE(),
       rc.UPDATE_RULE, rc.DELETE_RULE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
  ON rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
 AND rc.TABLE_NAME = kcu.TABLE_NAME
 AND rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE kcu.CONSTRAINT_SCHEMA = DATABASE()
  AND kcu.TABLE_SCHEMA = DATABASE()
  AND kcu.TABLE_NAME = ?
  AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION`
)

type expectedServiceConfigurationColumn struct {
	name                  string
	columnType            string
	nullable              bool
	collation             string
	hasDefault            bool
	columnDefault         string
	allowDefaultGenerated bool
}

type expectedServiceConfigurationIndex struct {
	name    string
	unique  bool
	columns []string
}

type expectedServiceConfigurationForeignKey struct {
	column           string
	referencedTable  string
	referencedColumn string
}

type serviceConfigurationTableSchema struct {
	name        string
	columns     []expectedServiceConfigurationColumn
	indexes     []expectedServiceConfigurationIndex
	foreignKeys []expectedServiceConfigurationForeignKey
}

var serviceConfigurationSchemas = []serviceConfigurationTableSchema{
	{
		name: serviceConfigurationGenerationTable,
		columns: []expectedServiceConfigurationColumn{
			{name: "Id", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "GenerationProtocolVersion", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "OrgId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "AppInstallationId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "AuthorizedWorkloadId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "CatalogRevision", columnType: "decimal(20,0)"},
			{name: "SchemaGeneration", columnType: "decimal(20,0)"},
			{name: "EngineGeneration", columnType: "decimal(20,0)"},
			{name: "SourceSnapshotDigest", columnType: "char(71)", collation: "utf8mb4_bin"},
			{name: "CatalogViewDigest", columnType: "char(71)", collation: "utf8mb4_bin"},
			{name: "CatalogReceiptProtocolVersion", columnType: "varchar(32)", collation: "utf8mb4_bin"},
			{name: "CatalogReceiptCapabilityVersion", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "CatalogSecurityEpoch", columnType: "decimal(20,0)"},
			{name: "ConfigurationOwnerRevisionId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "ConfigurationOwnerRevisionDigest", columnType: "char(71)", collation: "utf8mb4_bin"},
			{name: "GenerationDigest", columnType: "char(71)", collation: "utf8mb4_bin"},
			{name: "ReceiptProtocolVersion", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "OwnerReceipt", columnType: "mediumblob"},
			{name: "OwnerReceiptDigest", columnType: "char(71)", collation: "utf8mb4_bin"},
			{name: "AccountCount", columnType: "bigint"},
			{name: "BindingCount", columnType: "bigint"},
			{name: "CredentialSelectionCount", columnType: "bigint"},
			{name: "CreatedDate", columnType: "datetime(6)", hasDefault: true, columnDefault: "CURRENT_TIMESTAMP(6)", allowDefaultGenerated: true},
			{name: "LastModifiedDate", columnType: "datetime(6)", hasDefault: true, columnDefault: "CURRENT_TIMESTAMP(6)", allowDefaultGenerated: true},
		},
		indexes: []expectedServiceConfigurationIndex{
			{name: "PRIMARY", unique: true, columns: []string{"Id"}},
			{name: "idx_connector_service_configuration_catalog_view", columns: []string{"CatalogViewDigest", "Id"}},
		},
	},
	{
		name: serviceConfigurationAccountTable,
		columns: []expectedServiceConfigurationColumn{
			{name: "Id", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "ServiceConfigurationGenerationId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "AccountOrdinal", columnType: "int"},
			{name: "ConnectorAccountId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "ProviderScopeId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "AccountRevisionId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "AccountRevisionDigest", columnType: "char(71)", collation: "utf8mb4_bin"},
			{name: "ProviderRevisionId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "ProviderRevisionDigest", columnType: "char(71)", collation: "utf8mb4_bin"},
			{name: "CreatedDate", columnType: "datetime(6)", hasDefault: true, columnDefault: "CURRENT_TIMESTAMP(6)", allowDefaultGenerated: true},
			{name: "LastModifiedDate", columnType: "datetime(6)", hasDefault: true, columnDefault: "CURRENT_TIMESTAMP(6)", allowDefaultGenerated: true},
		},
		indexes: []expectedServiceConfigurationIndex{
			{name: "PRIMARY", unique: true, columns: []string{"Id"}},
			{name: "ux_connector_service_configuration_account_ordinal", unique: true, columns: []string{"ServiceConfigurationGenerationId", "AccountOrdinal"}},
			{name: "ux_connector_service_configuration_account_identity", unique: true, columns: []string{"ServiceConfigurationGenerationId", "ConnectorAccountId"}},
		},
		foreignKeys: []expectedServiceConfigurationForeignKey{
			{column: "ServiceConfigurationGenerationId", referencedTable: serviceConfigurationGenerationTable, referencedColumn: "Id"},
		},
	},
	{
		name: serviceConfigurationBindingTable,
		columns: []expectedServiceConfigurationColumn{
			{name: "Id", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "AccountConfigurationId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "CatalogBindingOrdinal", columnType: "int"},
			{name: "ConfigurationRevisionId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "ConfigurationRevisionDigest", columnType: "char(71)", collation: "utf8mb4_bin"},
			{name: "ConfigurationFormatVersion", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "CanonicalConfiguration", columnType: "mediumblob"},
			{name: "CreatedDate", columnType: "datetime(6)", hasDefault: true, columnDefault: "CURRENT_TIMESTAMP(6)", allowDefaultGenerated: true},
			{name: "LastModifiedDate", columnType: "datetime(6)", hasDefault: true, columnDefault: "CURRENT_TIMESTAMP(6)", allowDefaultGenerated: true},
		},
		indexes: []expectedServiceConfigurationIndex{
			{name: "PRIMARY", unique: true, columns: []string{"Id"}},
			{name: "ux_connector_service_configuration_binding_ordinal", unique: true, columns: []string{"AccountConfigurationId", "CatalogBindingOrdinal"}},
		},
		foreignKeys: []expectedServiceConfigurationForeignKey{
			{column: "AccountConfigurationId", referencedTable: serviceConfigurationAccountTable, referencedColumn: "Id"},
		},
	},
	{
		name: serviceConfigurationCredentialSelectionTable,
		columns: []expectedServiceConfigurationColumn{
			{name: "Id", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "BindingConfigurationId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "CredentialOrdinal", columnType: "int"},
			{name: "CredentialRef", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "CredentialGenerationId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
			{name: "CreatedDate", columnType: "datetime(6)", hasDefault: true, columnDefault: "CURRENT_TIMESTAMP(6)", allowDefaultGenerated: true},
			{name: "LastModifiedDate", columnType: "datetime(6)", hasDefault: true, columnDefault: "CURRENT_TIMESTAMP(6)", allowDefaultGenerated: true},
		},
		indexes: []expectedServiceConfigurationIndex{
			{name: "PRIMARY", unique: true, columns: []string{"Id"}},
			{name: "ux_connector_service_configuration_credential_ordinal", unique: true, columns: []string{"BindingConfigurationId", "CredentialOrdinal"}},
			{name: "ux_connector_service_configuration_credential_ref", unique: true, columns: []string{"BindingConfigurationId", "CredentialRef"}},
			{name: "ux_connector_service_configuration_credential_generation", unique: true, columns: []string{"BindingConfigurationId", "CredentialGenerationId"}},
		},
		foreignKeys: []expectedServiceConfigurationForeignKey{
			{column: "BindingConfigurationId", referencedTable: serviceConfigurationBindingTable, referencedColumn: "Id"},
			{column: "CredentialGenerationId", referencedTable: "_System_ConnectorCredentialGeneration", referencedColumn: "Id"},
		},
	},
}

// verifyServiceConfigurationSchema is a readiness check only. NexusCRM owns
// the physical tables and Controlplane converges them; Connector neither
// creates nor repairs an absent, partial, or compatibility schema.
func verifyServiceConfigurationSchema(ctx context.Context, db *sql.DB) error {
	if ctx == nil || db == nil {
		return fmt.Errorf("%w: database or context is unavailable", ErrSchemaUnavailable)
	}
	for _, table := range serviceConfigurationSchemas {
		if err := verifyServiceConfigurationColumns(ctx, db, table); err != nil {
			return fmt.Errorf("%w: verify %s columns: %w", ErrSchemaUnavailable, table.name, err)
		}
	}
	for _, table := range serviceConfigurationSchemas {
		if err := verifyServiceConfigurationIndexes(ctx, db, table); err != nil {
			return fmt.Errorf("%w: verify %s indexes: %w", ErrSchemaUnavailable, table.name, err)
		}
	}
	for _, table := range serviceConfigurationSchemas {
		if err := verifyServiceConfigurationForeignKeys(ctx, db, table); err != nil {
			return fmt.Errorf("%w: verify %s foreign keys: %w", ErrSchemaUnavailable, table.name, err)
		}
	}
	return nil
}

func verifyServiceConfigurationColumns(
	ctx context.Context,
	db *sql.DB,
	table serviceConfigurationTableSchema,
) error {
	rows, err := db.QueryContext(ctx, inspectServiceConfigurationColumnsSQL, table.name)
	if err != nil {
		return err
	}
	defer rows.Close()

	expected := make(map[string]expectedServiceConfigurationColumn, len(table.columns))
	for _, column := range table.columns {
		expected[column.name] = column
	}
	found := make(map[string]struct{}, len(table.columns))
	for rows.Next() {
		var (
			name, columnType, nullable, extra string
			collation, columnDefault          sql.NullString
		)
		if err := rows.Scan(&name, &columnType, &nullable, &collation, &columnDefault, &extra); err != nil {
			return err
		}
		want, ok := expected[name]
		if !ok {
			return fmt.Errorf("unexpected column %s", name)
		}
		if _, duplicate := found[name]; duplicate {
			return fmt.Errorf("column %s reported more than once", name)
		}
		found[name] = struct{}{}

		wantNullable := "NO"
		if want.nullable {
			wantNullable = "YES"
		}
		if !serviceConfigurationColumnTypeMatches(columnType, want.columnType) ||
			nullable != wantNullable ||
			collation.Valid != (want.collation != "") ||
			(collation.Valid && collation.String != want.collation) ||
			!serviceConfigurationColumnDefaultMatches(columnDefault, want.hasDefault, want.columnDefault) ||
			!serviceConfigurationColumnExtraMatches(extra, want.allowDefaultGenerated) {
			actualCollation := "NULL"
			if collation.Valid {
				actualCollation = collation.String
			}
			wantCollation := "NULL"
			if want.collation != "" {
				wantCollation = want.collation
			}
			actualDefault := "NULL"
			if columnDefault.Valid {
				actualDefault = columnDefault.String
			}
			wantDefault := "NULL"
			if want.hasDefault {
				wantDefault = want.columnDefault
			}
			wantExtra := "empty"
			if want.allowDefaultGenerated {
				wantExtra = "empty or DEFAULT_GENERATED"
			}
			return fmt.Errorf(
				"%s is type=%s nullable=%s collation=%s default=%s extra=%q, want type=%s nullable=%s collation=%s default=%s extra=%s",
				name, columnType, nullable, actualCollation, actualDefault, extra,
				want.columnType, wantNullable, wantCollation, wantDefault, wantExtra,
			)
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}
	for _, column := range table.columns {
		if _, ok := found[column.name]; !ok {
			return fmt.Errorf("missing column %s", column.name)
		}
	}
	return nil
}

func serviceConfigurationColumnTypeMatches(actual, expected string) bool {
	switch expected {
	case "int":
		return actual == "int" || actual == "int(11)"
	case "bigint":
		return actual == "bigint" || actual == "bigint(20)"
	default:
		return actual == expected
	}
}

func serviceConfigurationColumnDefaultMatches(actual sql.NullString, hasExpected bool, expected string) bool {
	if !hasExpected {
		return !actual.Valid
	}
	if !actual.Valid {
		return false
	}
	if expected == "CURRENT_TIMESTAMP(6)" {
		return strings.EqualFold(actual.String, expected)
	}
	return actual.String == expected
}

func serviceConfigurationColumnExtraMatches(actual string, allowDefaultGenerated bool) bool {
	if actual == "" {
		return true
	}
	return allowDefaultGenerated && strings.EqualFold(actual, "DEFAULT_GENERATED")
}

func verifyServiceConfigurationIndexes(
	ctx context.Context,
	db *sql.DB,
	table serviceConfigurationTableSchema,
) error {
	rows, err := db.QueryContext(ctx, inspectServiceConfigurationIndexesSQL, table.name)
	if err != nil {
		return err
	}
	defer rows.Close()

	expected := make(map[string]expectedServiceConfigurationIndex, len(table.indexes))
	for _, index := range table.indexes {
		expected[index.name] = index
	}
	type observedIndex struct {
		unique  bool
		set     bool
		columns []string
	}
	observed := make(map[string]observedIndex, len(table.indexes))
	for rows.Next() {
		var (
			name      string
			nonUnique int
			sequence  int
			column    sql.NullString
			subPart   sql.NullInt64
		)
		if err := rows.Scan(&name, &nonUnique, &sequence, &column, &subPart); err != nil {
			return err
		}
		if nonUnique != 0 && nonUnique != 1 {
			return fmt.Errorf("index %s has invalid NON_UNIQUE=%d", name, nonUnique)
		}
		if _, required := expected[name]; !required {
			if nonUnique == 0 {
				return fmt.Errorf("unexpected unique index %s", name)
			}
			continue
		}
		shape := observed[name]
		unique := nonUnique == 0
		if (shape.set && shape.unique != unique) || sequence != len(shape.columns)+1 ||
			!column.Valid || subPart.Valid {
			return fmt.Errorf("index %s has unsafe sequence, column, uniqueness, or prefix", name)
		}
		shape.set = true
		shape.unique = unique
		shape.columns = append(shape.columns, column.String)
		observed[name] = shape
	}
	if err := rows.Err(); err != nil {
		return err
	}
	for _, want := range table.indexes {
		got, ok := observed[want.name]
		if !ok || !got.set || got.unique != want.unique || !equalServiceConfigurationColumns(got.columns, want.columns) {
			return fmt.Errorf(
				"index %s is columns=%v unique=%t, want columns=%v unique=%t",
				want.name, got.columns, got.unique, want.columns, want.unique,
			)
		}
	}
	return nil
}

func verifyServiceConfigurationForeignKeys(
	ctx context.Context,
	db *sql.DB,
	table serviceConfigurationTableSchema,
) error {
	rows, err := db.QueryContext(ctx, inspectServiceConfigurationForeignKeysSQL, table.name)
	if err != nil {
		return err
	}
	defer rows.Close()

	expected := make(map[string]expectedServiceConfigurationForeignKey, len(table.foreignKeys))
	for _, foreignKey := range table.foreignKeys {
		expected[foreignKey.column] = foreignKey
	}
	found := make(map[string]struct{}, len(table.foreignKeys))
	for rows.Next() {
		var (
			constraint, column, referencedSchema, referencedTable   string
			referencedColumn, currentSchema, updateRule, deleteRule string
		)
		if err := rows.Scan(
			&constraint,
			&column,
			&referencedSchema,
			&referencedTable,
			&referencedColumn,
			&currentSchema,
			&updateRule,
			&deleteRule,
		); err != nil {
			return err
		}
		want, ok := expected[column]
		if !ok {
			return fmt.Errorf("unexpected foreign key %s on column %s", constraint, column)
		}
		if _, duplicate := found[column]; duplicate {
			return fmt.Errorf("foreign-key column %s reported more than once", column)
		}
		found[column] = struct{}{}
		if referencedSchema != currentSchema ||
			referencedTable != want.referencedTable ||
			referencedColumn != want.referencedColumn ||
			!serviceConfigurationRestrictiveRule(updateRule) ||
			!serviceConfigurationRestrictiveRule(deleteRule) {
			return fmt.Errorf(
				"foreign key %s is %s -> %s.%s(%s) current=%s update=%s delete=%s, want same-database %s(%s) with restrictive rules",
				constraint, column, referencedSchema, referencedTable, referencedColumn,
				currentSchema, updateRule, deleteRule, want.referencedTable, want.referencedColumn,
			)
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}
	for _, foreignKey := range table.foreignKeys {
		if _, ok := found[foreignKey.column]; !ok {
			return fmt.Errorf("missing foreign key on column %s", foreignKey.column)
		}
	}
	return nil
}

func serviceConfigurationRestrictiveRule(rule string) bool {
	return strings.EqualFold(rule, "RESTRICT") || strings.EqualFold(rule, "NO ACTION")
}

func equalServiceConfigurationColumns(first, second []string) bool {
	if len(first) != len(second) {
		return false
	}
	for index := range first {
		if first[index] != second[index] {
			return false
		}
	}
	return true
}