package bootstrapimpl

import (
	"regexp"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/shared/pkg/constants"
)

func TestConnectedDeliveryManagedCatalog(t *testing.T) {
	defs := make(map[string]schema.TableDefinition)
	for _, def := range GetManagedTableDefinitions() {
		defs[def.TableName] = def
	}

	for _, tableName := range connectedDeliveryTableNames {
		def, ok := defs[tableName]
		if !ok {
			t.Fatalf("%s is missing from managed schema catalog", tableName)
		}
		if def.TableType != "system_core" || def.Category != "integration" {
			t.Fatalf("%s type/category = %q/%q, want system_core/integration", tableName, def.TableType, def.Category)
		}
		if def.RequiredReadPermission != "BLOCKED" || def.RequiredWritePermission != "BLOCKED" {
			t.Fatalf("%s permissions = %q/%q, want BLOCKED/BLOCKED", tableName, def.RequiredReadPermission, def.RequiredWritePermission)
		}
		for _, temporal := range []string{"CreatedDate", "LastModifiedDate"} {
			col, ok := connectedDeliveryColumn(def, temporal)
			if !ok {
				t.Fatalf("%s.%s is missing", tableName, temporal)
			}
			if col.Type != "DATETIME(6)" || col.Nullable || col.Default != "CURRENT_TIMESTAMP(6)" {
				t.Fatalf("%s.%s shape = type %q nullable=%v default=%q, want DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)", tableName, temporal, col.Type, col.Nullable, col.Default)
			}
		}
	}

	payload := defs[constants.TableConnectedDeliveryPayload]
	if _, ok := connectedDeliveryColumn(payload, "PayloadBase64"); !ok {
		t.Fatalf("%s.PayloadBase64 is missing", constants.TableConnectedDeliveryPayload)
	}
	if len(payload.ForeignKeys) != 0 {
		t.Fatalf("%s foreign keys = %v, want none for payload-before-job insertion", constants.TableConnectedDeliveryPayload, payload.ForeignKeys)
	}
}

func TestConnectedDeliveryColumnPrimaryIndex(t *testing.T) {
	want := map[string]string{
		constants.TableConnectedPrincipal:            "Id",
		constants.TableConnectedAuthorizationRequest: "Id",
		constants.TableConnectedPrincipalToken:       "ConnectedPrincipalId",
		constants.TableConnectedDeliveryJob:          "JobId",
		constants.TableConnectedDeliveryPayload:      "JobId",
	}
	for _, def := range GetManagedTableDefinitions() {
		column, ok := want[def.TableName]
		if !ok {
			continue
		}
		primary, ok := connectedDeliveryColumnPrimaryIndex(def)
		if !ok || !primary.Primary || len(primary.Columns) != 1 || primary.Columns[0] != column {
			t.Fatalf("connectedDeliveryColumnPrimaryIndex(%s) = %+v/%v, want PRIMARY(%s)", def.TableName, primary, ok, column)
		}
		delete(want, def.TableName)
	}
	if len(want) != 0 {
		t.Fatalf("connected delivery managed definitions missing: %v", want)
	}

	def := connectedDeliveryPayloadDefinition(t)
	def.Indices = append(def.Indices, schema.IndexDefinition{Columns: []string{"JobId"}, Primary: true})
	if _, ok := connectedDeliveryColumnPrimaryIndex(def); ok {
		t.Fatal("connectedDeliveryColumnPrimaryIndex() synthesized a duplicate explicit primary")
	}
	if _, ok := connectedDeliveryColumnPrimaryIndex(schema.TableDefinition{
		TableName: "_System_NonConnectedColumnPrimary",
		Columns:   []schema.ColumnDefinition{{Name: "Id", PrimaryKey: true}},
	}); ok {
		t.Fatal("connectedDeliveryColumnPrimaryIndex() adapted a non-connected table")
	}
}

func TestVerifyConnectedDeliverySchemaDetectsDrift(t *testing.T) {
	tests := []struct {
		name           string
		drift          connectedDeliveryTestDrift
		want           string
		wantGuidance   string
		rejectGuidance string
	}{
		{
			name:         "missing table",
			drift:        connectedDeliveryTestDrift{omitTable: constants.TableConnectedPrincipalToken},
			want:         constants.TableConnectedPrincipalToken + " table is missing",
			wantGuidance: "run 'cmd/setup apply'",
		},
		{
			name:         "missing payload temporal column",
			drift:        connectedDeliveryTestDrift{omitColumn: constants.TableConnectedDeliveryPayload + ".LastModifiedDate"},
			want:         constants.TableConnectedDeliveryPayload + ".LastModifiedDate is missing",
			wantGuidance: "run 'cmd/setup apply'",
		},
		{
			name:           "missing primary key",
			drift:          connectedDeliveryTestDrift{omitIndex: constants.TableConnectedDeliveryPayload + ".PRIMARY"},
			want:           constants.TableConnectedDeliveryPayload + " primary key is missing",
			wantGuidance:   "run 'cmd/setup apply'",
			rejectGuidance: "manual remediation",
		},
		{
			name: "primary key composition drift",
			drift: connectedDeliveryTestDrift{
				reshapeIndex:   constants.TableConnectedDeliveryPayload + ".PRIMARY",
				reshapeColumns: []string{"PayloadSha256"},
			},
			want:           constants.TableConnectedDeliveryPayload + " primary key composition/uniqueness does not match",
			wantGuidance:   "manual remediation",
			rejectGuidance: "run 'cmd/setup apply' for repairable",
		},
		{
			name: "unique index composition drift",
			drift: connectedDeliveryTestDrift{
				reshapeIndex:   constants.TableConnectedAuthorizationRequest + ".ux_connected_authorization_state_digest",
				reshapeColumns: []string{"StateDigest", "UserId"},
			},
			want:         constants.TableConnectedAuthorizationRequest + " index ux_connected_authorization_state_digest composition/uniqueness does not match",
			wantGuidance: "run 'cmd/setup apply'",
		},
		{
			name: "claim index uniqueness drift",
			drift: connectedDeliveryTestDrift{
				reshapeIndex:  constants.TableConnectedDeliveryJob + ".idx_connected_delivery_job_claim",
				reshapeUnique: true,
			},
			want:           constants.TableConnectedDeliveryJob + " index idx_connected_delivery_job_claim is unique but the managed catalog expects non-unique",
			wantGuidance:   "manual remediation",
			rejectGuidance: "run 'cmd/setup apply' for repairable",
		},
		{
			name: "foreign key target and delete rule drift",
			drift: connectedDeliveryTestDrift{
				reshapeForeignKey: constants.TableConnectedDeliveryJob + ".ConnectedPrincipalId",
				referencedTable:   "User",
				referencedColumn:  "Id",
				deleteRule:        "CASCADE",
			},
			want:         constants.TableConnectedDeliveryJob + ".ConnectedPrincipalId foreign key target/delete rule does not match",
			wantGuidance: "manual remediation",
		},
		{
			name:           "forbidden automatic update",
			drift:          connectedDeliveryTestDrift{onUpdateColumn: constants.TableConnectedDeliveryPayload + ".LastModifiedDate"},
			want:           constants.TableConnectedDeliveryPayload + ".LastModifiedDate has forbidden ON UPDATE behavior",
			wantGuidance:   "manual remediation",
			rejectGuidance: "run 'cmd/setup apply' for repairable",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			db, mock, err := sqlmock.New()
			if err != nil {
				t.Fatalf("sqlmock.New: %v", err)
			}
			defer db.Close()

			expectConnectedDeliveryPhysicalSchema(mock, tt.drift)
			err = verifyConnectedDeliverySchema(db)
			if err == nil {
				t.Fatal("verifyConnectedDeliverySchema() error = nil, want drift failure")
			}
			if !strings.Contains(err.Error(), tt.want) {
				t.Fatalf("error = %q, want %q", err.Error(), tt.want)
			}
			if !strings.Contains(err.Error(), tt.wantGuidance) {
				t.Fatalf("error = %q, want guidance %q", err.Error(), tt.wantGuidance)
			}
			if tt.rejectGuidance != "" && strings.Contains(err.Error(), tt.rejectGuidance) {
				t.Fatalf("error = %q, reject guidance %q", err.Error(), tt.rejectGuidance)
			}
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("sql expectations unmet: %v", err)
			}
		})
	}
}

type connectedDeliveryTestDrift struct {
	omitTable         string
	omitColumn        string
	omitIndex         string
	reshapeIndex      string
	reshapeColumns    []string
	reshapeUnique     bool
	reshapeForeignKey string
	referencedTable   string
	referencedColumn  string
	deleteRule        string
	onUpdateColumn    string
}

func expectConnectedDeliveryPhysicalSchema(mock sqlmock.Sqlmock, drift connectedDeliveryTestDrift) {
	columnRows := sqlmock.NewRows([]string{"TABLE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "COLLATION_NAME"})
	extraRows := sqlmock.NewRows([]string{"TABLE_NAME", "COLUMN_NAME", "EXTRA"})
	indexRows := sqlmock.NewRows([]string{"TABLE_NAME", "INDEX_NAME", "NON_UNIQUE", "COLUMNS"})
	foreignKeyRows := sqlmock.NewRows([]string{"TABLE_NAME", "COLUMN_NAME", "REFERENCED_TABLE_NAME", "REFERENCED_COLUMN_NAME", "DELETE_RULE"})
	for _, def := range GetManagedTableDefinitions() {
		if !isConnectedDeliveryTable(def.TableName) || def.TableName == drift.omitTable {
			continue
		}
		var primaryColumns []string
		for _, col := range def.Columns {
			columnKey := def.TableName + "." + col.Name
			if columnKey == drift.omitColumn {
				continue
			}
			var defaultValue any
			if col.Default != "" {
				defaultValue = col.Default
			}
			var collation any
			if col.CollationName != "" {
				collation = col.CollationName
			}
			columnRows.AddRow(def.TableName, col.Name, DesiredColumnType(col), nullableSQLValue(col.Nullable), defaultValue, collation)
			extra := ""
			if columnKey == drift.onUpdateColumn {
				extra = "DEFAULT_GENERATED on update CURRENT_TIMESTAMP(6)"
			}
			extraRows.AddRow(def.TableName, col.Name, extra)
			if col.PrimaryKey {
				primaryColumns = append(primaryColumns, col.Name)
			}
		}
		if indexKey := def.TableName + ".PRIMARY"; len(primaryColumns) > 0 && indexKey != drift.omitIndex {
			if indexKey == drift.reshapeIndex && len(drift.reshapeColumns) > 0 {
				primaryColumns = drift.reshapeColumns
			}
			indexRows.AddRow(def.TableName, "PRIMARY", 0, strings.Join(primaryColumns, ","))
		}
		for _, idx := range def.Indices {
			indexKey := def.TableName + "." + idx.Name
			if indexKey == drift.omitIndex {
				continue
			}
			columns := idx.Columns
			unique := idx.Unique
			if indexKey == drift.reshapeIndex {
				if len(drift.reshapeColumns) > 0 {
					columns = drift.reshapeColumns
				}
				if drift.reshapeUnique {
					unique = !unique
				}
			}
			nonUnique := 1
			if unique {
				nonUnique = 0
			}
			indexRows.AddRow(def.TableName, idx.Name, nonUnique, strings.Join(columns, ","))
		}
		for _, fk := range def.ForeignKeys {
			referencedTable, referencedColumn, err := parseConnectedDeliveryReference(fk.References)
			if err != nil {
				panic(err)
			}
			deleteRule := fk.OnDelete
			if def.TableName+"."+fk.Column == drift.reshapeForeignKey {
				referencedTable = drift.referencedTable
				referencedColumn = drift.referencedColumn
				deleteRule = drift.deleteRule
			}
			foreignKeyRows.AddRow(def.TableName, fk.Column, referencedTable, referencedColumn, deleteRule)
		}
	}

	mock.ExpectQuery(regexp.QuoteMeta("SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()")).
		WillReturnRows(columnRows)
	mock.ExpectQuery("SELECT TABLE_NAME, COLUMN_NAME, EXTRA FROM INFORMATION_SCHEMA.COLUMNS").WillReturnRows(extraRows)
	mock.ExpectQuery("SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, GROUP_CONCAT").WillReturnRows(indexRows)
	mock.ExpectQuery("SELECT k.TABLE_NAME, k.COLUMN_NAME, k.REFERENCED_TABLE_NAME").WillReturnRows(foreignKeyRows)
}

func connectedDeliveryColumn(def schema.TableDefinition, name string) (schema.ColumnDefinition, bool) {
	for _, col := range def.Columns {
		if col.Name == name {
			return col, true
		}
	}
	return schema.ColumnDefinition{}, false
}

func isConnectedDeliveryTable(tableName string) bool {
	for _, wanted := range connectedDeliveryTableNames {
		if tableName == wanted {
			return true
		}
	}
	return false
}

func nullableSQLValue(nullable bool) string {
	if nullable {
		return "YES"
	}
	return "NO"
}