package credentialgeneration

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

const (
	dispatchGateTable = "_System_ConnectorCredentialGenerationDispatchGate"

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

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

	inspectDispatchGateForeignKeysSQL = `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`
)

var dispatchGateColumns = []expectedCredentialGenerationColumn{
	{name: "CredentialGenerationId", columnType: "varchar(255)", collation: "utf8mb4_bin"},
	{name: "Mode", columnType: "varchar(32)", collation: "utf8mb4_bin", hasDefault: true, columnDefault: "enabled"},
	{name: "FenceToken", columnType: "bigint", hasDefault: true, columnDefault: "0"},
	{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 dispatchGateIndexes = []expectedCredentialGenerationIndex{
	{name: "PRIMARY", unique: true, columns: []string{"CredentialGenerationId"}},
}

type dispatchGateSchemaQueryer interface {
	QueryContext(context.Context, string, ...any) (*sql.Rows, error)
}

func verifyDispatchGateSchema(ctx context.Context, queryer dispatchGateSchemaQueryer) error {
	if ctx == nil || queryer == nil {
		return fmt.Errorf("%w: dispatch gate database or context is unavailable", ErrSchemaUnavailable)
	}
	checks := []struct {
		name string
		run  func() error
	}{
		{name: "columns", run: func() error { return verifyDispatchGateColumns(ctx, queryer) }},
		{name: "indexes", run: func() error { return verifyDispatchGateIndexes(ctx, queryer) }},
		{name: "foreign key", run: func() error { return verifyDispatchGateForeignKey(ctx, queryer) }},
	}
	for _, check := range checks {
		if err := check.run(); err != nil {
			return fmt.Errorf("%w: verify %s %s: %w", ErrSchemaUnavailable, dispatchGateTable, check.name, err)
		}
	}
	return nil
}

func verifyDispatchGateColumns(ctx context.Context, queryer dispatchGateSchemaQueryer) error {
	rows, err := queryer.QueryContext(ctx, inspectDispatchGateColumnsSQL, dispatchGateTable)
	if err != nil {
		return err
	}
	defer rows.Close()

	expected := make(map[string]expectedCredentialGenerationColumn, len(dispatchGateColumns))
	for _, column := range dispatchGateColumns {
		expected[column.name] = column
	}
	found := make(map[string]struct{}, len(dispatchGateColumns))
	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{}{}
		if !dispatchGateColumnTypeMatches(columnType, want.columnType) || nullable != "NO" ||
			collation.Valid != (want.collation != "") ||
			(collation.Valid && collation.String != want.collation) ||
			!credentialGenerationColumnDefaultMatches(columnDefault, want.hasDefault, want.columnDefault) ||
			!credentialGenerationColumnExtraMatches(extra, want.allowDefaultGenerated) {
			return fmt.Errorf(
				"%s is type=%s nullable=%s collation=%v default=%v extra=%q, want type=%s nullable=NO collation=%q default=%q",
				name, columnType, nullable, collation, columnDefault, extra,
				want.columnType, want.collation, want.columnDefault,
			)
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}
	for _, column := range dispatchGateColumns {
		if _, ok := found[column.name]; !ok {
			return fmt.Errorf("missing column %s", column.name)
		}
	}
	return nil
}

func dispatchGateColumnTypeMatches(actual, expected string) bool {
	if expected != "bigint" {
		return actual == expected
	}
	return actual == "bigint" || actual == "bigint(20)"
}

func verifyDispatchGateIndexes(ctx context.Context, queryer dispatchGateSchemaQueryer) error {
	rows, err := queryer.QueryContext(ctx, inspectDispatchGateIndexesSQL, dispatchGateTable)
	if err != nil {
		return err
	}
	defer rows.Close()

	type observedIndex struct {
		unique  bool
		set     bool
		columns []string
	}
	observed := make(map[string]observedIndex, len(dispatchGateIndexes))
	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 name != "PRIMARY" {
			return fmt.Errorf("unexpected secondary index %s", name)
		}
		shape := observed[name]
		if nonUnique != 0 || (shape.set && !shape.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 = true
		shape.columns = append(shape.columns, column.String)
		observed[name] = shape
	}
	if err := rows.Err(); err != nil {
		return err
	}
	primary, ok := observed["PRIMARY"]
	if !ok || !primary.set || !equalCredentialGenerationColumns(primary.columns, dispatchGateIndexes[0].columns) {
		return fmt.Errorf("index PRIMARY is columns=%v unique=%t, want columns=%v unique=true", primary.columns, primary.unique, dispatchGateIndexes[0].columns)
	}
	return nil
}

func verifyDispatchGateForeignKey(ctx context.Context, queryer dispatchGateSchemaQueryer) error {
	rows, err := queryer.QueryContext(ctx, inspectDispatchGateForeignKeysSQL, dispatchGateTable)
	if err != nil {
		return err
	}
	defer rows.Close()

	count := 0
	for rows.Next() {
		count++
		var constraint, column, referencedSchema, referencedTable, referencedColumn string
		var currentDatabase, updateRule, deleteRule string
		if err := rows.Scan(
			&constraint,
			&column,
			&referencedSchema,
			&referencedTable,
			&referencedColumn,
			&currentDatabase,
			&updateRule,
			&deleteRule,
		); err != nil {
			return err
		}
		if count > 1 {
			return fmt.Errorf("unexpected additional foreign key %s", constraint)
		}
		if column != "CredentialGenerationId" ||
			referencedSchema != currentDatabase ||
			referencedTable != credentialGenerationTable ||
			referencedColumn != "Id" ||
			!dispatchGateRestrictiveRule(updateRule) ||
			!dispatchGateRestrictiveRule(deleteRule) {
			return fmt.Errorf(
				"foreign key %s is %s -> %s.%s(%s) current=%s update=%s delete=%s, want same-database exact generation with restrictive rules",
				constraint, column, referencedSchema, referencedTable, referencedColumn,
				currentDatabase, updateRule, deleteRule,
			)
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}
	if count != 1 {
		return fmt.Errorf("foreign key count = %d, want 1", count)
	}
	return nil
}

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