package credentialgeneration

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

const (
	credentialGenerationTable = "_System_ConnectorCredentialGeneration"

	inspectCredentialGenerationColumnsSQL = `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`

	inspectCredentialGenerationIndexesSQL = `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`

	inspectCredentialGenerationForeignKeysSQL = `SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = ?
  AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY CONSTRAINT_NAME, ORDINAL_POSITION`
)

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

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

var credentialGenerationColumns = []expectedCredentialGenerationColumn{
	{name: "Id", columnType: "varchar(255)", collation: "utf8mb4_bin"},
	{name: "StableCredentialIdentity", columnType: "varchar(255)", collation: "utf8mb4_bin"},
	{name: "ProviderScopeId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
	{name: "ConnectorAccountId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
	{name: "SecretBackendProtocol", columnType: "varchar(255)", collation: "utf8mb4_bin"},
	{name: "SecretBackendVersionRef", columnType: "varchar(255)", collation: "utf8mb4_bin"},
	{name: "TransportConfiguration", columnType: "mediumblob"},
	{name: "TransportConfigurationDigest", columnType: "char(71)", collation: "utf8mb4_bin"},
	{name: "ReceiptProtocolVersion", columnType: "varchar(32)", collation: "utf8mb4_bin"},
	{name: "OwnerReceipt", columnType: "mediumblob"},
	{name: "OwnerReceiptDigest", 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},
}

var credentialGenerationIndexes = []expectedCredentialGenerationIndex{
	{name: "PRIMARY", unique: true, columns: []string{"Id"}},
	{name: "idx_connector_credential_generation_identity", columns: []string{"StableCredentialIdentity", "Id"}},
	{name: "idx_connector_credential_generation_scope", columns: []string{"ProviderScopeId", "ConnectorAccountId"}},
}

// verifyCredentialGenerationSchema is a readiness check only. The physical
// schema is owned by the NexusCRM catalog and converged by Controlplane;
// Connector must never create, alter, or tolerate a compatibility shape here.
func verifyCredentialGenerationSchema(ctx context.Context, db *sql.DB) error {
	if ctx == nil || db == nil {
		return fmt.Errorf("%w: database or context is unavailable", ErrSchemaUnavailable)
	}
	checks := []struct {
		name string
		run  func() error
	}{
		{name: "columns", run: func() error {
			return verifyCredentialGenerationColumns(ctx, db)
		}},
		{name: "indexes", run: func() error {
			return verifyCredentialGenerationIndexes(ctx, db)
		}},
		{name: "foreign keys", run: func() error {
			return verifyCredentialGenerationForeignKeys(ctx, db)
		}},
	}
	for _, check := range checks {
		if err := check.run(); err != nil {
			return fmt.Errorf("%w: verify %s %s: %w", ErrSchemaUnavailable, credentialGenerationTable, check.name, err)
		}
	}
	return nil
}

func verifyCredentialGenerationColumns(ctx context.Context, db *sql.DB) error {
	rows, err := db.QueryContext(ctx, inspectCredentialGenerationColumnsSQL, credentialGenerationTable)
	if err != nil {
		return err
	}
	defer rows.Close()

	expectedByName := make(map[string]expectedCredentialGenerationColumn, len(credentialGenerationColumns))
	for _, column := range credentialGenerationColumns {
		expectedByName[column.name] = column
	}
	found := make(map[string]struct{}, len(credentialGenerationColumns))
	for rows.Next() {
		var (
			name          string
			columnType    string
			nullable      string
			collation     sql.NullString
			columnDefault sql.NullString
			extra         string
		)
		if err := rows.Scan(&name, &columnType, &nullable, &collation, &columnDefault, &extra); err != nil {
			return err
		}
		want, ok := expectedByName[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 columnType != want.columnType || nullable != wantNullable ||
			collation.Valid != (want.collation != "") ||
			(collation.Valid && collation.String != want.collation) ||
			!credentialGenerationColumnDefaultMatches(columnDefault, want.hasDefault, want.columnDefault) ||
			!credentialGenerationColumnExtraMatches(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 credentialGenerationColumns {
		if _, ok := found[column.name]; !ok {
			return fmt.Errorf("missing column %s", column.name)
		}
	}
	return nil
}

func credentialGenerationColumnDefaultMatches(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 credentialGenerationColumnExtraMatches(actual string, allowDefaultGenerated bool) bool {
	if actual == "" {
		return true
	}
	return allowDefaultGenerated && strings.EqualFold(actual, "DEFAULT_GENERATED")
}

func verifyCredentialGenerationIndexes(ctx context.Context, db *sql.DB) error {
	rows, err := db.QueryContext(ctx, inspectCredentialGenerationIndexesSQL, credentialGenerationTable)
	if err != nil {
		return err
	}
	defer rows.Close()

	expectedByName := make(map[string]expectedCredentialGenerationIndex, len(credentialGenerationIndexes))
	for _, index := range credentialGenerationIndexes {
		expectedByName[index.name] = index
	}
	type observedIndex struct {
		unique  bool
		set     bool
		columns []string
	}
	observed := make(map[string]observedIndex, len(credentialGenerationIndexes))
	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 := expectedByName[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 credentialGenerationIndexes {
		got, ok := observed[want.name]
		if !ok || !got.set || got.unique != want.unique || !equalCredentialGenerationColumns(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 verifyCredentialGenerationForeignKeys(ctx context.Context, db *sql.DB) error {
	rows, err := db.QueryContext(ctx, inspectCredentialGenerationForeignKeysSQL, credentialGenerationTable)
	if err != nil {
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var constraint, column, referencedSchema, referencedTable, referencedColumn string
		if err := rows.Scan(&constraint, &column, &referencedSchema, &referencedTable, &referencedColumn); err != nil {
			return err
		}
		return fmt.Errorf(
			"unexpected foreign key %s: %s -> %s.%s.%s",
			constraint, column, referencedSchema, referencedTable, referencedColumn,
		)
	}
	return rows.Err()
}

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