package persistence

import (
	"context"
	"database/sql"
	"testing"

	"github.com/nexuscrm/db"
	"github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/kernel/pkg/schemacatalog"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

func TestHistorySequenceSurvivesTrackedAndDisabledFieldRecreateLifecycle(t *testing.T) {
	rawDB, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()
	prepareHistoryOwnerCatalog(t, rawDB)
	prepareHistoryLifecycleAuditCatalog(t, rawDB)
	if _, err := rawDB.Exec(`ALTER TABLE Account ADD COLUMN Id VARCHAR(255)`); err != nil {
		t.Fatalf("add lifecycle account id: %v", err)
	}

	ctx := context.Background()
	repo := NewSchemaRepository(rawDB, nil)
	tm := NewTransactionManager(database.NewConnectionFromDB(rawDB))
	fieldID := schema.GenerateFieldID("Account", "Name")
	candidate := historyLifecycleFieldCandidate(fieldID, false)
	if err := repo.EnsurePackOwnedField(ctx, "Account", candidate); err != nil {
		t.Fatalf("create initial field: %v", err)
	}

	transition := func(desired bool) {
		t.Helper()
		err := tm.WithTransaction(ctx, func(txCtx context.Context) error {
			field := historyLifecycleFieldCandidate(fieldID, desired)
			return repo.BatchSaveFieldMetadataWithCustomizationPolicy(txCtx, []schema.FieldWithContext{field}, tm.ExtractTx(txCtx), false)
		})
		if err != nil {
			t.Fatalf("transition TrackHistory=%t: %v", desired, err)
		}
	}
	writeFact := func(id string) int64 {
		t.Helper()
		var sequence int64
		err := tm.WithTransaction(ctx, func(txCtx context.Context) error {
			reserved, err := ReserveHistorySequences(txCtx, tm.ExtractTx(txCtx), []string{fieldID})
			if err != nil {
				return err
			}
			sequence = reserved[fieldID]
			_, err = tm.ExtractTx(txCtx).(Executor).ExecContext(txCtx, `
				INSERT INTO _System_AuditLog (Id, ObjectApiName, RecordId, FieldName, ChangedById, HistorySequence)
				VALUES (?, 'Account', 'record-1', 'Name', 'user-1', ?)`, id, sequence)
			return err
		})
		if err != nil {
			t.Fatalf("write fact %s: %v", id, err)
		}
		return sequence
	}
	eligible := func(id string) int {
		t.Helper()
		var count int
		if err := rawDB.QueryRow(`
			SELECT COUNT(*)
			FROM _System_AuditLog fact
			JOIN _System_FieldHistoryTrackingInterval interval_row
			  ON interval_row.FieldId = ?
			WHERE fact.Id = ?
			  AND fact.HistorySequence IS NOT NULL
			  AND fact.HistorySequence > interval_row.StartedSequence
			  AND (interval_row.EndedSequence IS NULL OR fact.HistorySequence < interval_row.EndedSequence)`, fieldID, id).Scan(&count); err != nil {
			t.Fatalf("query fact %s: %v", id, err)
		}
		return count
	}
	dropAndRecreate := func() {
		t.Helper()
		if err := repo.DropColumnPhase1("Account", "Name", "history-pack"); err != nil {
			t.Fatalf("drop field phase 1: %v", err)
		}
		if err := repo.DropColumnPhase2("Account", "Name", "history-pack"); err != nil {
			t.Fatalf("drop field phase 2: %v", err)
		}
		var sequenceRows int
		if err := rawDB.QueryRow(`SELECT COUNT(*) FROM _System_FieldHistorySequence WHERE FieldId = ?`, fieldID).Scan(&sequenceRows); err != nil || sequenceRows != 1 {
			t.Fatalf("durable sequence rows after delete = %d, err = %v", sequenceRows, err)
		}
		if err := repo.EnsurePackOwnedField(ctx, "Account", historyLifecycleFieldCandidate(fieldID, false)); err != nil {
			t.Fatalf("same-name deterministic recreate: %v", err)
		}
		var recreatedID string
		var tracked bool
		if err := rawDB.QueryRow(`SELECT Id, TrackHistory FROM _System_Field WHERE ObjectId = 'obj-account' AND ApiName = 'Name'`).Scan(&recreatedID, &tracked); err != nil {
			t.Fatalf("read recreated field: %v", err)
		}
		if recreatedID != fieldID || tracked {
			t.Fatalf("recreated field = %s tracked=%t, want deterministic id %s disabled", recreatedID, tracked, fieldID)
		}
	}

	transition(true)
	if got := writeFact("before-tracked-delete"); got != 2 {
		t.Fatalf("tracked fact sequence = %d, want 2", got)
	}
	dropAndRecreate()
	if got := writeFact("disabled-after-tracked-recreate"); got != 4 {
		t.Fatalf("disabled recreated fact sequence = %d, want 4", got)
	}
	if got := eligible("before-tracked-delete"); got != 1 {
		t.Fatalf("closed pre-delete history eligible = %d, want 1", got)
	}
	if got := eligible("disabled-after-tracked-recreate"); got != 0 {
		t.Fatalf("disabled recreated history eligible = %d, want 0", got)
	}
	transition(true)
	if got := writeFact("after-explicit-reenable"); got != 6 {
		t.Fatalf("reenabled fact sequence = %d, want 6", got)
	}
	if got := eligible("after-explicit-reenable"); got != 1 {
		t.Fatalf("reenabled history eligible = %d, want 1", got)
	}

	transition(false)
	dropAndRecreate()
	if got := writeFact("disabled-after-disabled-recreate"); got != 8 {
		t.Fatalf("disabled-delete recreated fact sequence = %d, want 8", got)
	}
	if got := eligible("disabled-after-disabled-recreate"); got != 0 {
		t.Fatalf("disabled-delete recreated history eligible = %d, want 0", got)
	}
	transition(true)
	if got := writeFact("after-second-explicit-reenable"); got != 10 {
		t.Fatalf("second reenabled fact sequence = %d, want 10", got)
	}
	if got := eligible("after-second-explicit-reenable"); got != 1 {
		t.Fatalf("second reenabled history eligible = %d, want 1", got)
	}
}

func TestLegacyTrackedFieldWithoutIntervalCanDisableAndDelete(t *testing.T) {
	rawDB, cleanup := openSchemaDDLTiDB(t)
	defer cleanup()
	prepareHistoryOwnerCatalog(t, rawDB)
	if _, err := rawDB.Exec(`ALTER TABLE Account ADD COLUMN Id VARCHAR(255)`); err != nil {
		t.Fatalf("add lifecycle account id: %v", err)
	}

	ctx := context.Background()
	repo := NewSchemaRepository(rawDB, nil)
	tm := NewTransactionManager(database.NewConnectionFromDB(rawDB))
	fieldID := schema.GenerateFieldID("Account", "Name")
	if err := repo.EnsurePackOwnedField(ctx, "Account", historyLifecycleFieldCandidate(fieldID, false)); err != nil {
		t.Fatalf("create field: %v", err)
	}
	setLegacyTracked := func() {
		t.Helper()
		if _, err := rawDB.Exec(`UPDATE _System_Field SET TrackHistory = 1 WHERE Id = ?`, fieldID); err != nil {
			t.Fatalf("stage legacy tracked field: %v", err)
		}
	}
	setLegacyTracked()
	if err := tm.WithTransaction(ctx, func(txCtx context.Context) error {
		_, err := repo.ApplyHistoryTrackingTransition(txCtx, fieldID, false, tm.ExtractTx(txCtx), false, false)
		return err
	}); err != nil {
		t.Fatalf("disable legacy tracked field: %v", err)
	}
	var tracked bool
	var intervals int
	if err := rawDB.QueryRow(`SELECT TrackHistory FROM _System_Field WHERE Id = ?`, fieldID).Scan(&tracked); err != nil || tracked {
		t.Fatalf("legacy disable tracked=%t err=%v, want false", tracked, err)
	}
	if err := rawDB.QueryRow(`SELECT COUNT(*) FROM _System_FieldHistoryTrackingInterval WHERE FieldId = ?`, fieldID).Scan(&intervals); err != nil || intervals != 0 {
		t.Fatalf("legacy disable intervals=%d err=%v, want 0", intervals, err)
	}

	setLegacyTracked()
	if err := repo.DropColumnPhase1("Account", "Name", "history-pack"); err != nil {
		t.Fatalf("drop legacy field phase 1: %v", err)
	}
	if err := repo.DropColumnPhase2("Account", "Name", "history-pack"); err != nil {
		t.Fatalf("drop legacy field phase 2: %v", err)
	}
	var fields, sequenceRows int
	if err := rawDB.QueryRow(`SELECT COUNT(*) FROM _System_Field WHERE Id = ?`, fieldID).Scan(&fields); err != nil || fields != 0 {
		t.Fatalf("legacy deleted fields=%d err=%v, want 0", fields, err)
	}
	if err := rawDB.QueryRow(`SELECT COUNT(*) FROM _System_FieldHistorySequence WHERE FieldId = ?`, fieldID).Scan(&sequenceRows); err != nil || sequenceRows != 1 {
		t.Fatalf("legacy delete sequence rows=%d err=%v, want 1", sequenceRows, err)
	}
}

func prepareHistoryLifecycleAuditCatalog(t *testing.T, rawDB *sql.DB) {
	t.Helper()
	definitions, err := schemacatalog.SystemTableDefinitions()
	if err != nil {
		t.Fatalf("SystemTableDefinitions(): %v", err)
	}
	for _, definition := range definitions {
		if definition.TableName == constants.TableAuditLog {
			if err := NewSchemaRepository(rawDB, nil).CreatePhysicalTable(context.Background(), definition); err != nil {
				t.Fatalf("create audit catalog: %v", err)
			}
			return
		}
	}
	t.Fatalf("missing canonical %s", constants.TableAuditLog)
}

func historyLifecycleFieldCandidate(fieldID string, tracked bool) schema.FieldWithContext {
	ownerPack := "history-pack"
	return schema.FieldWithContext{
		TableName: "Account", ObjectID: "obj-account", FieldID: fieldID,
		OwnerKind: constants.FieldOwnerKindPack, OwnerPack: ownerPack,
		ColumnDefinition: schema.ColumnDefinition{Name: "Name", Type: "VARCHAR(255)", Nullable: true},
		Field: &models.FieldMetadata{
			ID: fieldID, APIName: "Name", Label: "Name", Type: constants.FieldTypeString,
			OwnerKind: constants.FieldOwnerKindPack, OwnerPack: &ownerPack, TrackHistory: tracked,
		},
	}
}