package persistence

import (
	"context"
	"database/sql"
	"errors"
	"strings"
	"sync"
	"testing"

	"github.com/nexuscrm/db"
	"github.com/nexuscrm/kernel/pkg/ports"
)

func TestHistoryTrackingTwoSessionOptimisticMatrix(t *testing.T) {
	db, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()
	db.SetMaxOpenConns(8)
	ctx := context.Background()
	for _, ddl := range []string{
		`CREATE TABLE _System_Field (
			Id VARCHAR(255) PRIMARY KEY,
			TrackHistory TINYINT(1) NOT NULL DEFAULT 0,
			HistoryTrackingRevision BIGINT NOT NULL DEFAULT 0,
			IsCustomized TINYINT(1) NOT NULL DEFAULT 0,
			IsDeleted TINYINT(1) NOT NULL DEFAULT 0,
			LastModifiedDate DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
		)`,
		`CREATE TABLE _System_FieldHistorySequence (
			FieldId VARCHAR(255) PRIMARY KEY,
			CurrentSequence BIGINT NOT NULL DEFAULT 0,
			CreatedDate DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
			LastModifiedDate DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
		)`,
		`CREATE TABLE _System_FieldHistoryTrackingInterval (
			Id VARCHAR(255) PRIMARY KEY,
			FieldId VARCHAR(255) NOT NULL,
			OpenFieldId VARCHAR(255) NULL,
			StartedSequence BIGINT NOT NULL,
			EndedSequence BIGINT NULL,
			StartedAt DATETIME(6) NOT NULL,
			EndedAt DATETIME(6) NULL,
			UNIQUE KEY uk_field_history_open (OpenFieldId),
			KEY idx_field_history_interval (FieldId, StartedSequence, EndedSequence)
		)`,
	} {
		if _, err := db.ExecContext(ctx, ddl); err != nil {
			t.Fatalf("create history tracking table: %v", err)
		}
	}

	t.Run("enable_enable_conflicts_and_fresh_retry_is_noop", func(t *testing.T) {
		resetHistoryTrackingField(t, db, false, 7)
		barrier := newHistoryCASBarrier(2)
		errs := runConcurrentHistoryAttempts(t, db, barrier, true, true)
		assertOneHistoryWinnerOneConflict(t, errs)
		assertHistoryTrackingFinalState(t, db, true, 8, 1, 1)

		tm := NewTransactionManager(database.NewConnectionFromDB(db))
		repo := NewSchemaRepository(db, nil)
		if err := tm.WithTransaction(ctx, func(txCtx context.Context) error {
			changed, err := repo.ApplyHistoryTrackingTransition(txCtx, "fld-history", true, tm.ExtractTx(txCtx), false, false)
			if err != nil {
				return err
			}
			if changed {
				t.Fatal("fresh loser retry changed already-enabled state")
			}
			return nil
		}); err != nil {
			t.Fatalf("fresh retry: %v", err)
		}
		assertHistoryTrackingFinalState(t, db, true, 8, 1, 1)
	})

	tests := []struct {
		name          string
		initial       bool
		desiredA      bool
		desiredB      bool
		wantTracked   bool
		wantRevision  int64
		wantIntervals int
		wantOpen      int
	}{
		{name: "enable_disable", initial: false, desiredA: true, desiredB: false, wantTracked: true, wantRevision: 8, wantIntervals: 1, wantOpen: 1},
		{name: "disable_reenable", initial: true, desiredA: false, desiredB: true, wantTracked: false, wantRevision: 8, wantIntervals: 1, wantOpen: 0},
		{name: "reapply_reapply", initial: true, desiredA: true, desiredB: true, wantTracked: true, wantRevision: 7, wantIntervals: 1, wantOpen: 1},
		{name: "reapply_toggle", initial: true, desiredA: true, desiredB: false, wantTracked: false, wantRevision: 8, wantIntervals: 1, wantOpen: 0},
	}
	for _, test := range tests {
		t.Run(test.name, func(t *testing.T) {
			resetHistoryTrackingField(t, db, test.initial, 7)
			errs := runConcurrentHistoryAttempts(t, db, nil, test.desiredA, test.desiredB)
			for _, err := range errs {
				if err != nil && !errors.Is(err, ports.ErrHistoryTrackingConflict) && !ports.IsHistorySerializationConflict(err) {
					t.Fatalf("attempt error = %v", err)
				}
			}
			assertHistoryTrackingFinalState(t, db, test.wantTracked, test.wantRevision, test.wantIntervals, test.wantOpen)
		})
	}
}

type historyCASBarrier struct {
	mu      sync.Mutex
	waiting int
	target  int
	release chan struct{}
}

func newHistoryCASBarrier(target int) *historyCASBarrier {
	return &historyCASBarrier{target: target, release: make(chan struct{})}
}

func (b *historyCASBarrier) wait() {
	b.mu.Lock()
	b.waiting++
	if b.waiting == b.target {
		close(b.release)
	}
	b.mu.Unlock()
	<-b.release
}

type historyBarrierExecutor struct {
	Executor
	barrier *historyCASBarrier
}

func (e *historyBarrierExecutor) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error) {
	if e.barrier != nil && strings.Contains(query, "INSERT INTO _System_FieldHistorySequence") {
		e.barrier.wait()
	}
	return e.Executor.ExecContext(ctx, query, args...)
}

func runConcurrentHistoryAttempts(t *testing.T, db *sql.DB, barrier *historyCASBarrier, desired ...bool) []error {
	t.Helper()
	errs := make([]error, len(desired))
	var wg sync.WaitGroup
	start := make(chan struct{})
	for i, target := range desired {
		wg.Add(1)
		go func(index int, want bool) {
			defer wg.Done()
			<-start
			tm := NewTransactionManager(database.NewConnectionFromDB(db))
			repo := NewSchemaRepository(db, nil)
			errs[index] = tm.WithTransaction(context.Background(), func(txCtx context.Context) error {
				exec, ok := tm.ExtractTx(txCtx).(*sql.Tx)
				if !ok || exec == nil {
					return errors.New("missing transaction executor")
				}
				_, err := repo.ApplyHistoryTrackingTransition(txCtx, "fld-history", want, &historyBarrierExecutor{Executor: exec, barrier: barrier}, false, false)
				return err
			})
		}(i, target)
	}
	close(start)
	wg.Wait()
	return errs
}

func resetHistoryTrackingField(t *testing.T, db *sql.DB, tracked bool, revision int64) {
	t.Helper()
	if _, err := db.Exec("DELETE FROM _System_FieldHistoryTrackingInterval"); err != nil {
		t.Fatalf("reset intervals: %v", err)
	}
	if _, err := db.Exec("DELETE FROM _System_FieldHistorySequence"); err != nil {
		t.Fatalf("reset sequences: %v", err)
	}
	if _, err := db.Exec("DELETE FROM _System_Field"); err != nil {
		t.Fatalf("reset field: %v", err)
	}
	if _, err := db.Exec(`INSERT INTO _System_Field (Id, TrackHistory, HistoryTrackingRevision, IsCustomized, IsDeleted) VALUES ('fld-history', ?, ?, 0, 0)`, tracked, revision); err != nil {
		t.Fatalf("insert field: %v", err)
	}
	if tracked {
		if _, err := db.Exec(`INSERT INTO _System_FieldHistorySequence (FieldId, CurrentSequence) VALUES ('fld-history', 1)`); err != nil {
			t.Fatalf("insert initial sequence: %v", err)
		}
		if _, err := db.Exec(`INSERT INTO _System_FieldHistoryTrackingInterval (Id, FieldId, OpenFieldId, StartedSequence, StartedAt) VALUES ('interval-initial', 'fld-history', 'fld-history', 1, UTC_TIMESTAMP(6) - INTERVAL 1 HOUR)`); err != nil {
			t.Fatalf("insert initial interval: %v", err)
		}
	}
}

func assertOneHistoryWinnerOneConflict(t *testing.T, errs []error) {
	t.Helper()
	winners, conflicts := 0, 0
	for _, err := range errs {
		switch {
		case err == nil:
			winners++
		case errors.Is(err, ports.ErrHistoryTrackingConflict), ports.IsHistorySerializationConflict(err):
			conflicts++
		default:
			t.Fatalf("unexpected attempt error: %v", err)
		}
	}
	if winners != 1 || conflicts != 1 {
		t.Fatalf("attempt outcomes: winners=%d conflicts=%d errors=%v", winners, conflicts, errs)
	}
}

func assertHistoryTrackingFinalState(t *testing.T, db *sql.DB, tracked bool, revision int64, intervals, open int) {
	t.Helper()
	var gotTracked bool
	var gotRevision int64
	if err := db.QueryRow(`SELECT TrackHistory, HistoryTrackingRevision FROM _System_Field WHERE Id = 'fld-history'`).Scan(&gotTracked, &gotRevision); err != nil {
		t.Fatalf("read final field state: %v", err)
	}
	var gotIntervals, gotOpen, overlaps int
	if err := db.QueryRow(`SELECT COUNT(*), COALESCE(SUM(CASE WHEN EndedSequence IS NULL AND OpenFieldId = FieldId THEN 1 ELSE 0 END), 0) FROM _System_FieldHistoryTrackingInterval WHERE FieldId = 'fld-history'`).Scan(&gotIntervals, &gotOpen); err != nil {
		t.Fatalf("read final interval state: %v", err)
	}
	if err := db.QueryRow(`SELECT COUNT(*) FROM _System_FieldHistoryTrackingInterval a JOIN _System_FieldHistoryTrackingInterval b ON a.FieldId = b.FieldId AND a.Id < b.Id AND a.StartedSequence < COALESCE(b.EndedSequence, 9223372036854775807) AND b.StartedSequence < COALESCE(a.EndedSequence, 9223372036854775807) WHERE a.FieldId = 'fld-history'`).Scan(&overlaps); err != nil {
		t.Fatalf("read interval overlaps: %v", err)
	}
	if gotTracked != tracked || gotRevision != revision || gotIntervals != intervals || gotOpen != open || overlaps != 0 {
		t.Fatalf("final state tracked=%t revision=%d intervals=%d open=%d overlaps=%d; want tracked=%t revision=%d intervals=%d open=%d overlaps=0", gotTracked, gotRevision, gotIntervals, gotOpen, overlaps, tracked, revision, intervals, open)
	}
}