package controlplane

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

	"github.com/DATA-DOG/go-sqlmock"
)

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

	expectMigrationApplied(mock, migrationIdListViewLegacyCutover, false)
	expectListViewLegacyCutoverColumnsReady(mock, 3)
	mock.ExpectExec(regexp.QuoteMeta(listViewBlankOwnerNormalizeSQL)).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec(regexp.QuoteMeta(listViewOrphanOwnerDeleteSQL)).
		WillReturnResult(sqlmock.NewResult(0, 2))
	expectListViewDanglingOwnerCount(mock, 0)
	expectListViewOwnerFKShape(mock, "", "")
	mock.ExpectExec(regexp.QuoteMeta(listViewOwnerAddForeignKeySQL)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(regexp.QuoteMeta(`INSERT INTO _System_Migration (Id, Version, Description, AppliedAt, CreatedDate, LastModifiedDate)
			VALUES (?, ?, ?, NOW(), NOW(), NOW())
			ON DUPLICATE KEY UPDATE LastModifiedDate = NOW()`)).
		WithArgs(migrationIdListViewLegacyCutover, 1, sqlmock.AnyArg()).
		WillReturnResult(sqlmock.NewResult(0, 1))

	touched, err := BackfillListViewLegacyCutover(context.Background(), db)
	if err != nil {
		t.Fatalf("BackfillListViewLegacyCutover: %v", err)
	}
	if touched != 3 {
		t.Fatalf("BackfillListViewLegacyCutover touched %d rows, want 3", touched)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	expectMigrationApplied(mock, migrationIdListViewLegacyCutover, true)
	expectListViewLegacyCutoverColumnsReady(mock, 3)
	expectListViewDanglingOwnerCount(mock, 0)
	expectListViewOwnerFKShape(mock, listViewOwnerForeignKeyName, "CASCADE")

	touched, err := BackfillListViewLegacyCutover(context.Background(), db)
	if err != nil {
		t.Fatalf("BackfillListViewLegacyCutover: %v", err)
	}
	if touched != 0 {
		t.Fatalf("BackfillListViewLegacyCutover touched %d rows, want 0", touched)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	expectMigrationApplied(mock, migrationIdListViewLegacyCutover, false)
	expectListViewLegacyCutoverColumnsReady(mock, 3)
	mock.ExpectExec(regexp.QuoteMeta(listViewBlankOwnerNormalizeSQL)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(regexp.QuoteMeta(listViewOrphanOwnerDeleteSQL)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectListViewDanglingOwnerCount(mock, 1)

	_, err = BackfillListViewLegacyCutover(context.Background(), db)
	if err == nil {
		t.Fatal("BackfillListViewLegacyCutover err = nil, want dangling-owner closure error")
	}
	if !strings.Contains(err.Error(), "dangling OwnerId") {
		t.Fatalf("BackfillListViewLegacyCutover err = %v, want dangling OwnerId context", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

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

	expectMigrationApplied(mock, migrationIdListViewLegacyCutover, false)
	expectListViewLegacyCutoverColumnsReady(mock, 3)
	mock.ExpectExec(regexp.QuoteMeta(listViewBlankOwnerNormalizeSQL)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(regexp.QuoteMeta(listViewOrphanOwnerDeleteSQL)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	expectListViewDanglingOwnerCount(mock, 0)
	expectListViewOwnerFKShape(mock, "old_fk", "RESTRICT")
	mock.ExpectExec(regexp.QuoteMeta("ALTER TABLE `_System_ListView` DROP FOREIGN KEY `old_fk`")).
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(regexp.QuoteMeta(listViewOwnerAddForeignKeySQL)).
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(regexp.QuoteMeta(`INSERT INTO _System_Migration (Id, Version, Description, AppliedAt, CreatedDate, LastModifiedDate)
			VALUES (?, ?, ?, NOW(), NOW(), NOW())
			ON DUPLICATE KEY UPDATE LastModifiedDate = NOW()`)).
		WithArgs(migrationIdListViewLegacyCutover, 1, sqlmock.AnyArg()).
		WillReturnResult(sqlmock.NewResult(0, 1))

	if _, err := BackfillListViewLegacyCutover(context.Background(), db); err != nil {
		t.Fatalf("BackfillListViewLegacyCutover: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestListViewLegacyCutoverWiring(t *testing.T) {
	raw, err := os.ReadFile("operations.go")
	if err != nil {
		t.Fatalf("read operations.go: %v", err)
	}
	source := string(raw)
	for _, want := range []string{
		`trackStepFatal("BackfillListViewLegacyCutover"`,
		"BackfillListViewLegacyCutover(ctx, db.DB())",
		"bootstrapStateStore.MarkListViewLegacyCutoverCompleted(db.DB())",
		`runLoggedControlPlanePhase("ApplyAll", "listview-legacy-cutover-final-gate"`,
		"bootstrapStateStore.IsListViewLegacyCutoverCompleted(markerDB)",
	} {
		if !strings.Contains(source, want) {
			t.Fatalf("operations.go missing list-view cutover wiring %q", want)
		}
	}
}

func expectListViewLegacyCutoverColumnsReady(mock sqlmock.Sqlmock, count int) {
	mock.ExpectQuery(regexp.QuoteMeta(`
SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = '_System_ListView'
   AND COLUMN_NAME IN ('Id', 'OwnerId', 'Visibility')`)).
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(count))
}

func expectListViewDanglingOwnerCount(mock sqlmock.Sqlmock, count int) {
	mock.ExpectQuery(regexp.QuoteMeta(`
SELECT COUNT(*)
  FROM _System_ListView
 WHERE NULLIF(TRIM(OwnerId), '') IS NOT NULL
   AND NOT EXISTS (
       SELECT 1
         FROM ` + "`User`" + ` u
        WHERE u.Id = _System_ListView.OwnerId
   )`)).
		WillReturnRows(sqlmock.NewRows([]string{"COUNT(*)"}).AddRow(count))
}

func expectListViewOwnerFKShape(mock sqlmock.Sqlmock, constraintName, deleteRule string) {
	q := mock.ExpectQuery(regexp.QuoteMeta(`
SELECT k.CONSTRAINT_NAME, COALESCE(rc.DELETE_RULE, '')
  FROM information_schema.KEY_COLUMN_USAGE k
  LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS rc
    ON rc.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
   AND rc.CONSTRAINT_NAME = k.CONSTRAINT_NAME
   AND rc.TABLE_NAME = k.TABLE_NAME
 WHERE k.TABLE_SCHEMA = DATABASE()
   AND k.TABLE_NAME = '_System_ListView'
   AND k.COLUMN_NAME = 'OwnerId'
   AND k.REFERENCED_TABLE_NAME = 'User'
   AND k.REFERENCED_COLUMN_NAME = 'Id'
 LIMIT 1`))
	if constraintName == "" {
		q.WillReturnRows(sqlmock.NewRows([]string{"CONSTRAINT_NAME", "DELETE_RULE"}))
		return
	}
	q.WillReturnRows(sqlmock.NewRows([]string{"CONSTRAINT_NAME", "DELETE_RULE"}).AddRow(constraintName, deleteRule))
}