package bootstrapimpl

import (
	"os"
	"regexp"
	"strings"
	"testing"

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

func TestSchemaReconciliationRunsListViewDeveloperNameDedupBeforeIndexReconciliation(t *testing.T) {
	data, err := os.ReadFile("schema.go")
	if err != nil {
		t.Fatalf("read schema.go: %v", err)
	}
	source := string(data)
	dedup := strings.Index(source, "backfillListViewDeveloperNamesBeforeIndex(rawDB)")
	reconcile := strings.Index(source, "reconcileSystemTableIndices(rawDB, tableDefs)")
	if dedup < 0 {
		t.Fatal("schema reconciliation must run _System_ListView DeveloperName dedup before adding unique indexes")
	}
	if reconcile < 0 {
		t.Fatal("schema reconciliation index reconciler call not found")
	}
	if dedup > reconcile {
		t.Fatal("_System_ListView DeveloperName dedup must run before reconcileSystemTableIndices")
	}
}

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

	expectListViewDeveloperNameDedupColumnQuery(mock).WillReturnRows(sqlmock.NewRows([]string{"COLUMN_NAME"}).
		AddRow(constants.FieldID).
		AddRow(constants.FieldObjectAPIName).
		AddRow(constants.FieldSysListView_DeveloperName))

	if err := backfillListViewDeveloperNamesBeforeIndex(db); err != nil {
		t.Fatalf("backfillListViewDeveloperNamesBeforeIndex: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

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

	expectListViewDeveloperNameDedupColumnQuery(mock).WillReturnRows(sqlmock.NewRows([]string{"COLUMN_NAME"}).
		AddRow(constants.FieldID).
		AddRow(constants.FieldObjectAPIName).
		AddRow(constants.FieldSysListView_DeveloperName).
		AddRow(constants.FieldSysListView_CreatedDate).
		AddRow(constants.FieldSysListView_LastModifiedDate))
	expectListViewDeveloperNameRowsQuery(mock).WillReturnRows(sqlmock.NewRows([]string{
		constants.FieldID,
		constants.FieldObjectAPIName,
		constants.FieldSysListView_DeveloperName,
	}).
		AddRow("lv-keep", "Account", "Shared").
		AddRow("lv-loser-1", "Account", "Shared").
		AddRow("lv-loser-2", "Account", "Shared"))
	expectListViewDeveloperNameDedupUpdate(mock, "ListView_dedup_lv_loser_1", "lv-loser-1", 1)
	expectListViewDeveloperNameDedupUpdate(mock, "ListView_dedup_lv_loser_2", "lv-loser-2", 1)

	if err := backfillListViewDeveloperNamesBeforeIndex(db); err != nil {
		t.Fatalf("backfillListViewDeveloperNamesBeforeIndex: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

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

	expectListViewDeveloperNameDedupColumnQuery(mock).WillReturnRows(sqlmock.NewRows([]string{"COLUMN_NAME"}).
		AddRow(constants.FieldID).
		AddRow(constants.FieldObjectAPIName).
		AddRow(constants.FieldSysListView_DeveloperName).
		AddRow(constants.FieldSysListView_CreatedDate).
		AddRow(constants.FieldSysListView_LastModifiedDate))
	expectListViewDeveloperNameRowsQuery(mock).WillReturnRows(sqlmock.NewRows([]string{
		constants.FieldID,
		constants.FieldObjectAPIName,
		constants.FieldSysListView_DeveloperName,
	}).
		AddRow("lv-existing", "Account", "ListView_dedup_lv_dup").
		AddRow("lv-keep", "Account", "Shared").
		AddRow("lv-dup", "Account", "Shared"))
	expectListViewDeveloperNameDedupUpdate(mock, "ListView_dedup_lv_dup_2", "lv-dup", 1)

	if err := backfillListViewDeveloperNamesBeforeIndex(db); err != nil {
		t.Fatalf("backfillListViewDeveloperNamesBeforeIndex: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet sql expectations: %v", err)
	}
}

func expectListViewDeveloperNameDedupColumnQuery(mock sqlmock.Sqlmock) *sqlmock.ExpectedQuery {
	return mock.ExpectQuery(regexp.QuoteMeta("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME IN (?, ?, ?, ?, ?)")).
		WithArgs(
			constants.TableListView,
			constants.FieldID,
			constants.FieldObjectAPIName,
			constants.FieldSysListView_DeveloperName,
			constants.FieldSysListView_CreatedDate,
			constants.FieldSysListView_LastModifiedDate,
		)
}

func expectListViewDeveloperNameRowsQuery(mock sqlmock.Sqlmock) *sqlmock.ExpectedQuery {
	return mock.ExpectQuery("SELECT `" + constants.FieldID + "`, `" + constants.FieldObjectAPIName + "`, `" + constants.FieldSysListView_DeveloperName + "`[\\s\\S]*FROM `" + constants.TableListView + "`[\\s\\S]*ORDER BY LOWER\\(TRIM\\(`" + constants.FieldObjectAPIName + "`\\)\\), LOWER\\(TRIM\\(`" + constants.FieldSysListView_DeveloperName + "`\\)\\), `" + constants.FieldSysListView_LastModifiedDate + "` DESC")
}

func expectListViewDeveloperNameDedupUpdate(mock sqlmock.Sqlmock, developerName string, id string, affected int64) {
	mock.ExpectExec(regexp.QuoteMeta("UPDATE `"+constants.TableListView+"` SET `"+constants.FieldSysListView_DeveloperName+"` = ?, `"+constants.FieldSysListView_LastModifiedDate+"` = NOW() WHERE `"+constants.FieldID+"` = ?")).
		WithArgs(developerName, id).
		WillReturnResult(sqlmock.NewResult(0, affected))
}