package packs

import (
	"context"
	"regexp"
	"slices"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/kernel/pkg/utils"
	cp "github.com/pingcap-inc/nexuscrm-contract/pkg/controlplane"
)

type countingComponentColumnCatalog struct {
	columns map[string][]string
	calls   map[string]int
}

func (c *countingComponentColumnCatalog) Columns(_ context.Context, table string) ([]string, error) {
	c.calls[table]++
	return append([]string(nil), c.columns[table]...), nil
}

func TestSQLComponentColumnCatalogReadsLiveColumnsAndRejectsUnsafeTables(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { _ = db.Close() })
	catalog := sqlComponentColumnCatalog{db: db}
	query := regexp.QuoteMeta("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? ORDER BY ORDINAL_POSITION")

	mock.ExpectQuery(query).WithArgs("ProcessDefinition").WillReturnRows(
		sqlmock.NewRows([]string{"COLUMN_NAME"}).AddRow("Id").AddRow("DeveloperName"),
	)
	columns, err := catalog.Columns(context.Background(), "ProcessDefinition")
	if err != nil || !slices.Equal(columns, []string{"Id", "DeveloperName"}) {
		t.Fatalf("Columns() = %v, %v", columns, err)
	}
	if _, err := catalog.Columns(context.Background(), "bad-table"); err == nil || !strings.Contains(err.Error(), "invalid SQL identifier") {
		t.Fatalf("unsafe table error = %v", err)
	}

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

func TestManagedComponentRowReaderIsBoundedAndReadOnly(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { _ = db.Close() })
	catalog := testComponentColumnCatalog()
	reader, err := newManagedComponentRowReader(db, catalog, testComponentWriteSet())
	if err != nil {
		t.Fatal(err)
	}
	if _, ok := reader.(cp.ComponentMutationStore); ok {
		t.Fatal("read-only adapter exposes mutation capability")
	}

	_, err = reader.ReadRows(context.Background(), cp.ComponentRowQuery{Table: "ComponentRow", Columns: []string{"Id"}})
	if err == nil || !strings.Contains(err.Error(), "equal predicates are required") {
		t.Fatalf("unbounded read error = %v", err)
	}
	_, err = reader.ReadRows(context.Background(), cp.ComponentRowQuery{
		Table: "OtherRow", Columns: []string{"Id"}, Equal: cp.ComponentRow{"Owner": "pack"},
	})
	if err == nil || !strings.Contains(err.Error(), "component write set") {
		t.Fatalf("unauthorized table error = %v", err)
	}
	_, err = reader.ReadRows(context.Background(), cp.ComponentRowQuery{
		Table: "ComponentRow", Columns: []string{"Secret"}, Equal: cp.ComponentRow{"Owner": "pack"},
	})
	if err == nil || !strings.Contains(err.Error(), "not present in the live schema") {
		t.Fatalf("unknown column error = %v", err)
	}
	_, err = reader.ReadRows(context.Background(), cp.ComponentRowQuery{
		Table: "ComponentRow", Columns: []string{"Id; DROP"}, Equal: cp.ComponentRow{"Owner": "pack"},
	})
	if err == nil || !strings.Contains(err.Error(), "invalid SQL identifier") {
		t.Fatalf("unsafe identifier error = %v", err)
	}

	mock.ExpectQuery(regexp.QuoteMeta("SELECT `Id`, `Label` FROM `ComponentRow` WHERE `Nullable` IS NULL AND `Owner` = ?")).
		WithArgs("pack").WillReturnRows(sqlmock.NewRows([]string{"Id", "Label"}).AddRow([]byte("row-1"), []byte("First")))
	rows, err := reader.ReadRows(context.Background(), cp.ComponentRowQuery{
		Table: "ComponentRow", Columns: []string{"Id", "Label"}, Equal: cp.ComponentRow{"Owner": "pack", "Nullable": nil},
	})
	if err != nil {
		t.Fatal(err)
	}
	if len(rows) != 1 || rows[0]["Id"] != "row-1" || rows[0]["Label"] != "First" {
		t.Fatalf("rows = %#v", rows)
	}
	if catalog.calls["ComponentRow"] != 1 {
		t.Fatalf("column snapshots = %d, want 1", catalog.calls["ComponentRow"])
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

func TestManagedComponentMutationStoreOperations(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { _ = db.Close() })
	catalog := testComponentColumnCatalog()
	mock.ExpectBegin()
	tx, err := db.BeginTx(context.Background(), nil)
	if err != nil {
		t.Fatal(err)
	}
	store, err := newManagedComponentMutationStore(tx, catalog, testComponentWriteSet())
	if err != nil {
		t.Fatal(err)
	}
	if id := store.NewID(); !utils.IsValidUUID(id) {
		t.Fatalf("NewID() = %q", id)
	}

	mock.ExpectQuery(regexp.QuoteMeta("SELECT `Id` FROM `ComponentRow` WHERE `Owner` = ? FOR UPDATE")).
		WithArgs("pack").WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow("row-1"))
	locked, err := store.LockRows(context.Background(), cp.ComponentRowQuery{
		Table: "ComponentRow", Columns: []string{"Id"}, Equal: cp.ComponentRow{"Owner": "pack"},
	})
	if err != nil || len(locked) != 1 || locked[0]["Id"] != "row-1" {
		t.Fatalf("LockRows() = %#v, %v", locked, err)
	}

	mock.ExpectExec(regexp.QuoteMeta("INSERT INTO `ComponentRow` (`Id`, `Owner`, `CreatedAt`) VALUES (?, ?, CURRENT_TIMESTAMP)")).
		WithArgs("row-2", "pack").WillReturnResult(sqlmock.NewResult(1, 1))
	if err := store.InsertRow(context.Background(), cp.ComponentInsert{
		Table: "ComponentRow", Values: cp.ComponentRow{"Owner": "pack", "Id": "row-2"}, CurrentTimestampColumns: []string{"CreatedAt"},
	}); err != nil {
		t.Fatal(err)
	}

	mock.ExpectExec(regexp.QuoteMeta("UPDATE `ComponentRow` SET `Label` = ?, `UpdatedAt` = CURRENT_TIMESTAMP WHERE `DeletedAt` IS NULL AND `Id` = ?")).
		WithArgs("Second", "row-2").WillReturnResult(sqlmock.NewResult(0, 2))
	updated, err := store.UpdateRows(context.Background(), cp.ComponentUpdate{
		Table: "ComponentRow", Match: cp.ComponentRow{"Id": "row-2", "DeletedAt": nil},
		Values: cp.ComponentRow{"Label": "Second"}, CurrentTimestampColumns: []string{"UpdatedAt"},
	})
	if err != nil || updated != 2 {
		t.Fatalf("UpdateRows() = %d, %v", updated, err)
	}

	mock.ExpectExec(regexp.QuoteMeta("DELETE FROM `ComponentRow` WHERE `Id` = ?")).
		WithArgs("row-2").WillReturnResult(sqlmock.NewResult(0, 1))
	deleted, err := store.DeleteRows(context.Background(), cp.ComponentDelete{
		Table: "ComponentRow", Match: cp.ComponentRow{"Id": "row-2"},
	})
	if err != nil || deleted != 1 {
		t.Fatalf("DeleteRows() = %d, %v", deleted, err)
	}
	if catalog.calls["ComponentRow"] != 1 {
		t.Fatalf("column snapshots = %d, want 1", catalog.calls["ComponentRow"])
	}
	mock.ExpectCommit()
	if err := tx.Commit(); err != nil {
		t.Fatal(err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

func TestTransactionComponentMutationStoreUsesCallerOwnedTransaction(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { _ = db.Close() })
	if _, err := NewTransactionComponentMutationStore(nil, db, testComponentWriteSet()); err == nil || !strings.Contains(err.Error(), "transaction is required") {
		t.Fatalf("missing transaction error = %v", err)
	}

	mock.ExpectBegin()
	tx, err := db.BeginTx(context.Background(), nil)
	if err != nil {
		t.Fatal(err)
	}
	if _, err := NewTransactionComponentMutationStore(tx, nil, testComponentWriteSet()); err == nil || !strings.Contains(err.Error(), "catalog database is required") {
		t.Fatalf("missing database error = %v", err)
	}
	store, err := NewTransactionComponentMutationStore(tx, db, testComponentWriteSet())
	if err != nil {
		t.Fatal(err)
	}

	columnsQuery := regexp.QuoteMeta("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? ORDER BY ORDINAL_POSITION")
	mock.ExpectQuery(columnsQuery).WithArgs("ComponentRow").WillReturnRows(
		sqlmock.NewRows([]string{"COLUMN_NAME"}).AddRow("Id").AddRow("Owner"),
	)
	mock.ExpectQuery(regexp.QuoteMeta("SELECT `Id` FROM `ComponentRow` WHERE `Owner` = ? FOR UPDATE")).
		WithArgs("pack").WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	if _, err := store.LockRows(context.Background(), cp.ComponentRowQuery{
		Table: "ComponentRow", Columns: []string{"Id"}, Equal: cp.ComponentRow{"Owner": "pack"},
	}); err != nil {
		t.Fatal(err)
	}

	mock.ExpectRollback()
	if err := tx.Rollback(); err != nil {
		t.Fatal(err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

func TestManagedComponentRowStoreSnapshotsAreStoreScoped(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { _ = db.Close() })
	catalog := testComponentColumnCatalog()
	for range 2 {
		reader, err := newManagedComponentRowReader(db, catalog, testComponentWriteSet())
		if err != nil {
			t.Fatal(err)
		}
		mock.ExpectQuery(regexp.QuoteMeta("SELECT `Id` FROM `ComponentRow` WHERE `Owner` = ?")).
			WithArgs("pack").WillReturnRows(sqlmock.NewRows([]string{"Id"}))
		if _, err := reader.ReadRows(context.Background(), cp.ComponentRowQuery{
			Table: "ComponentRow", Columns: []string{"Id"}, Equal: cp.ComponentRow{"Owner": "pack"},
		}); err != nil {
			t.Fatal(err)
		}
	}
	if catalog.calls["ComponentRow"] != 2 {
		t.Fatalf("column snapshots = %d, want one per store", catalog.calls["ComponentRow"])
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

func TestManagedComponentRowStoreValidatesWriteSet(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { _ = db.Close() })
	if _, err := newManagedComponentMutationStore(nil, testComponentColumnCatalog(), testComponentWriteSet()); err == nil {
		t.Fatal("expected missing transaction to fail")
	}
	mock.ExpectBegin()
	tx, err := db.Begin()
	if err != nil {
		t.Fatal(err)
	}
	for name, writeSet := range map[string][]cp.ComponentWriteSurface{
		"missing": nil,
		"unsafe":  {{Table: "ComponentRow; DROP", Fact: "prose is not authority"}},
	} {
		t.Run(name, func(t *testing.T) {
			if _, err := newManagedComponentMutationStore(tx, testComponentColumnCatalog(), writeSet); err == nil {
				t.Fatal("expected invalid write set to fail")
			}
		})
	}
	mock.ExpectRollback()
	if err := tx.Rollback(); err != nil {
		t.Fatal(err)
	}
}

func testComponentColumnCatalog() *countingComponentColumnCatalog {
	return &countingComponentColumnCatalog{
		columns: map[string][]string{"ComponentRow": {"Id", "Owner", "Label", "Nullable", "CreatedAt", "UpdatedAt", "DeletedAt"}},
		calls:   make(map[string]int),
	}
}

func testComponentWriteSet() []cp.ComponentWriteSurface {
	return []cp.ComponentWriteSurface{{Table: "ComponentRow", Fact: "logical component rows"}}
}