package bootstrapimpl

import (
	"regexp"
	"testing"

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

const schedulerLeaseTableForPrimaryTest = "_System_ConnectorSchedulerLease"

func TestReconcileSystemTableIndicesAddsPrimaryKeyAsPrimary(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectExistingTableSet(mock, schedulerLeaseTableForPrimaryTest)
	expectExistingIndexSet(mock)
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `" + schedulerLeaseTableForPrimaryTest + "` ADD PRIMARY KEY (`InstalledPackId`, `Generation`)")).
		WillReturnResult(sqlmock.NewResult(0, 1))

	if err := reconcileSystemTableIndices(db, []schema.TableDefinition{schedulerLeasePrimaryKeyDef()}); err != nil {
		t.Fatalf("reconcileSystemTableIndices: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

func TestReconcileSystemTableIndicesTreatsExistingPrimaryAsSatisfied(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectExistingTableSet(mock, schedulerLeaseTableForPrimaryTest)
	expectExistingIndexSet(mock,
		driverIndexRow{tableName: schedulerLeaseTableForPrimaryTest, indexName: "PRIMARY", nonUnique: 0, columns: "InstalledPackId,Generation"},
	)

	if err := reconcileSystemTableIndices(db, []schema.TableDefinition{schedulerLeasePrimaryKeyDef()}); err != nil {
		t.Fatalf("reconcileSystemTableIndices: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

func TestReconcileSystemTableIndicesDoesNotTreatUniqueIndexAsPrimary(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectExistingTableSet(mock, schedulerLeaseTableForPrimaryTest)
	expectExistingIndexSet(mock,
		driverIndexRow{tableName: schedulerLeaseTableForPrimaryTest, indexName: "uq_scheduler", nonUnique: 0, columns: "InstalledPackId,Generation"},
	)
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `" + schedulerLeaseTableForPrimaryTest + "` ADD PRIMARY KEY (`InstalledPackId`, `Generation`)")).
		WillReturnResult(sqlmock.NewResult(0, 1))

	if err := reconcileSystemTableIndices(db, []schema.TableDefinition{schedulerLeasePrimaryKeyDef()}); err != nil {
		t.Fatalf("reconcileSystemTableIndices: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

func TestReconcileSystemTableIndicesFailsOnPrimaryKeyColumnDrift(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	expectExistingTableSet(mock, schedulerLeaseTableForPrimaryTest)
	expectExistingIndexSet(mock,
		driverIndexRow{tableName: schedulerLeaseTableForPrimaryTest, indexName: "PRIMARY", nonUnique: 0, columns: "InstalledPackId"},
	)

	err = reconcileSystemTableIndices(db, []schema.TableDefinition{schedulerLeasePrimaryKeyDef()})
	if err == nil {
		t.Fatal("reconcileSystemTableIndices() error = nil, want primary key drift")
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

func TestReconcileSystemTableIndicesAddsConnectedDeliveryColumnPrimaryKey(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	def := connectedDeliveryPayloadDefinition(t)
	expectExistingTableSet(mock, def.TableName)
	expectExistingIndexSet(mock, connectedDeliveryPayloadCreatedIndex())
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `" + def.TableName + "` ADD PRIMARY KEY (`JobId`)")).
		WillReturnResult(sqlmock.NewResult(0, 1))

	if err := reconcileSystemTableIndices(db, []schema.TableDefinition{def}); err != nil {
		t.Fatalf("reconcileSystemTableIndices: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

func TestReconcileSystemTableIndicesTreatsConnectedDeliveryColumnPrimaryAsSatisfied(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	def := connectedDeliveryPayloadDefinition(t)
	expectExistingTableSet(mock, def.TableName)
	expectExistingIndexSet(mock,
		connectedDeliveryPayloadCreatedIndex(),
		driverIndexRow{tableName: def.TableName, indexName: "PRIMARY", nonUnique: 0, columns: "JobId"},
	)

	if err := reconcileSystemTableIndices(db, []schema.TableDefinition{def}); err != nil {
		t.Fatalf("reconcileSystemTableIndices: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

func TestReconcileSystemTableIndicesDoesNotReplaceConnectedDeliveryColumnPrimary(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	def := connectedDeliveryPayloadDefinition(t)
	expectExistingTableSet(mock, def.TableName)
	expectExistingIndexSet(mock,
		connectedDeliveryPayloadCreatedIndex(),
		driverIndexRow{tableName: def.TableName, indexName: "PRIMARY", nonUnique: 0, columns: "PayloadSha256"},
	)

	err = reconcileSystemTableIndices(db, []schema.TableDefinition{def})
	if err == nil {
		t.Fatal("reconcileSystemTableIndices() error = nil, want primary key drift")
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

func TestReconcileSystemTableIndicesDoesNotAdaptNonConnectedColumnPrimary(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	const tableName = "_System_NonConnectedColumnPrimary"
	expectExistingTableSet(mock, tableName)
	expectExistingIndexSet(mock)

	def := schema.TableDefinition{
		TableName: tableName,
		Columns: []schema.ColumnDefinition{{
			Name:       "Id",
			PrimaryKey: true,
		}},
	}
	if err := reconcileSystemTableIndices(db, []schema.TableDefinition{def}); err != nil {
		t.Fatalf("reconcileSystemTableIndices: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

type driverIndexRow struct {
	tableName string
	indexName string
	nonUnique int
	columns   string
}

func expectExistingTableSet(mock sqlmock.Sqlmock, tables ...string) {
	rows := sqlmock.NewRows([]string{"TABLE_NAME"})
	for _, table := range tables {
		rows.AddRow(table)
	}
	mock.ExpectQuery(regexp.QuoteMeta("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE()")).
		WillReturnRows(rows)
}

func expectExistingIndexSet(mock sqlmock.Sqlmock, indexes ...driverIndexRow) {
	rows := sqlmock.NewRows([]string{"TABLE_NAME", "INDEX_NAME", "NON_UNIQUE", "GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)"})
	for _, idx := range indexes {
		rows.AddRow(idx.tableName, idx.indexName, idx.nonUnique, idx.columns)
	}
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
		 FROM INFORMATION_SCHEMA.STATISTICS
		 WHERE TABLE_SCHEMA = DATABASE()
		 GROUP BY TABLE_NAME, INDEX_NAME, NON_UNIQUE`)).
		WillReturnRows(rows)
}

func connectedDeliveryPayloadDefinition(t *testing.T) schema.TableDefinition {
	t.Helper()
	for _, def := range GetManagedTableDefinitions() {
		if def.TableName == constants.TableConnectedDeliveryPayload {
			return def
		}
	}
	t.Fatalf("managed definition for %s is missing", constants.TableConnectedDeliveryPayload)
	return schema.TableDefinition{}
}

func connectedDeliveryPayloadCreatedIndex() driverIndexRow {
	return driverIndexRow{
		tableName: constants.TableConnectedDeliveryPayload,
		indexName: "idx_connected_delivery_payload_created",
		nonUnique: 1,
		columns:   "CreatedDate,JobId",
	}
}

func schedulerLeasePrimaryKeyDef() schema.TableDefinition {
	return schema.TableDefinition{
		TableName: schedulerLeaseTableForPrimaryTest,
		Indices: []schema.IndexDefinition{{
			Columns: []string{"InstalledPackId", "Generation"},
			Primary: true,
		}},
	}
}