package persistence

import (
	"context"
	"database/sql"
	"fmt"
	"slices"
	"testing"
	"time"

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

func TestRollupRepositoryOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSessionRepositoryTiDB(t)
	defer cleanup()
	createRollupRepositoryTables(t, db)

	repo := NewRollupRepository(db)
	repo.SetSchemaProvider(integrationRollupSchemaProvider{schema: rollupRepositorySchema()})

	t.Run("aggregates use the authoritative source and exclude deleted rows", func(t *testing.T) {
		responded := "HasResponded = true"
		qualified := "EXISTS (SELECT 1 FROM RollupLine AS sibling WHERE sibling.ParentId = RollupLine.ParentId AND sibling.Status = 'Pending') AND RollupLine.HasResponded = true"
		for _, tc := range []struct {
			name, calc, field, parent string
			filter                    *string
			want                      string
		}{
			{name: "canonical count", calc: "count", parent: "parent-a", want: "2"},
			{name: "physical sum", calc: " sum ", field: "Amount", parent: "parent-a", want: "30"},
			{name: "computed sum", calc: "SUM", field: "ComputedAmount", parent: "parent-a", want: "60"},
			{name: "computed filter", calc: "SUM", field: "Amount", parent: "parent-a", filter: &responded, want: "10"},
			{name: "qualified view filter keeps nested scope", calc: "COUNT", parent: "parent-a", filter: &qualified, want: "1"},
			{name: "empty average", calc: "AVG", field: "Amount", parent: "missing", want: "0"},
			{name: "canonical date max", calc: "MAX", field: "OccurredOn", parent: "parent-a", want: "2026-01-03"},
		} {
			t.Run(tc.name, func(t *testing.T) {
				got, err := repo.CalculateRollup(t.Context(), tc.calc, "RollupLine", tc.field, "ParentId", tc.parent, tc.filter)
				if err != nil {
					t.Fatalf("CalculateRollup: %v", err)
				}
				if fmt.Sprint(got) != tc.want {
					t.Fatalf("CalculateRollup = %#v, want %s", got, tc.want)
				}
			})
		}

		blank := "   "
		if _, err := repo.CalculateRollup(t.Context(), "SUM", "RollupLine", "Amount", "ParentId", "parent-a", &blank); err == nil {
			t.Fatal("whitespace-only aggregate filter succeeded, want real TiDB syntax failure")
		}
	})

	t.Run("single and batched filter matches return record outcomes", func(t *testing.T) {
		physical := "Status = 'Responded'"
		computed := "HasResponded = true"
		physicalAlias := "`has_responded` = true"
		for _, tc := range []struct {
			name, record string
			filter       *string
			want         bool
		}{
			{name: "physical match", record: "line-1", filter: &physical, want: true},
			{name: "physical miss", record: "line-2", filter: &physical},
			{name: "computed match", record: "line-1", filter: &computed, want: true},
			{name: "computed physical alias", record: "line-1", filter: &physicalAlias, want: true},
			{name: "deleted record", record: "line-deleted", filter: &computed},
		} {
			t.Run(tc.name, func(t *testing.T) {
				got, err := repo.RecordMatchesRollupFilter(t.Context(), "RollupLine", tc.record, tc.filter)
				if err != nil || got != tc.want {
					t.Fatalf("RecordMatchesRollupFilter = %v, %v; want %v, nil", got, err, tc.want)
				}
			})
		}

		got, err := repo.RecordMatchesRollupFilters(t.Context(), "RollupLine", "line-1", []*string{nil, &physical, &computed, &physicalAlias})
		if err != nil {
			t.Fatalf("RecordMatchesRollupFilters: %v", err)
		}
		if want := []bool{true, true, true, true}; !slices.Equal(got, want) {
			t.Fatalf("RecordMatchesRollupFilters = %v, want %v", got, want)
		}

		blank := "   "
		if _, err := repo.RecordMatchesRollupFilter(t.Context(), "RollupLine", "line-1", &blank); err == nil {
			t.Fatal("whitespace-only rollup filter succeeded, want real TiDB syntax failure")
		}
	})

	t.Run("physical current read does not require the formula view", func(t *testing.T) {
		if _, err := db.ExecContext(t.Context(), "DROP VIEW v_rollupline"); err != nil {
			t.Fatal(err)
		}
		responded := "Status = 'Responded'"
		got, err := repo.CalculateRollupCurrentRead(t.Context(), "COUNT", "RollupLine", "", "ParentId", "parent-a", &responded)
		if err != nil || fmt.Sprint(got) != "1" {
			t.Fatalf("physical CalculateRollupCurrentRead without view = %#v, %v; want 1, nil", got, err)
		}
	})
}

func TestRollupRepositoryCurrentReadLockOutcomesOnTiDB(t *testing.T) {
	db, cleanup := openSessionRepositoryTiDB(t)
	defer cleanup()
	createRollupRepositoryTables(t, db)
	repo := NewRollupRepository(db)
	repo.SetSchemaProvider(integrationRollupSchemaProvider{schema: rollupRepositorySchema()})
	responded := "`status` = 'Responded'"

	t.Run("repeatable-read formula view locks the base child rows", func(t *testing.T) {
		tx, err := db.BeginTx(t.Context(), nil)
		if err != nil {
			t.Fatal(err)
		}
		defer tx.Rollback()
		txCtx := context.WithValue(t.Context(), txContextKey{}, tx)
		got, err := repo.CalculateRollupCurrentRead(txCtx, "SUM", "RollupLine", "ComputedAmount", "ParentId", "parent-a", &responded)
		if err != nil || fmt.Sprint(got) != "20" {
			t.Fatalf("CalculateRollupCurrentRead = %#v, %v; want 20, nil", got, err)
		}

		updateCtx, cancel := context.WithTimeout(t.Context(), 300*time.Millisecond)
		defer cancel()
		if _, err := db.ExecContext(updateCtx, "UPDATE RollupLine SET Amount = 11 WHERE Id = 'line-1'"); err == nil {
			t.Fatal("concurrent child update succeeded while current-read lock was held")
		}
	})

	t.Run("read-committed current read does not retain redundant child locks", func(t *testing.T) {
		tx, err := db.BeginTx(t.Context(), nil)
		if err != nil {
			t.Fatal(err)
		}
		defer tx.Rollback()
		txCtx := WithReadCommittedIsolation(context.WithValue(t.Context(), txContextKey{}, tx))
		got, err := repo.CalculateRollupCurrentRead(txCtx, "SUM", "RollupLine", "ComputedAmount", "ParentId", "parent-a", &responded)
		if err != nil || fmt.Sprint(got) != "20" {
			t.Fatalf("CalculateRollupCurrentRead = %#v, %v; want 20, nil", got, err)
		}

		updateCtx, cancel := context.WithTimeout(t.Context(), 2*time.Second)
		defer cancel()
		if _, err := db.ExecContext(updateCtx, "UPDATE RollupLine SET Amount = 11 WHERE Id = 'line-1'"); err != nil {
			t.Fatalf("read-committed child update remained blocked: %v", err)
		}
	})
}

func createRollupRepositoryTables(t *testing.T, db *sql.DB) {
	t.Helper()
	for _, statement := range []string{
		`CREATE TABLE RollupLine (
			Id VARCHAR(255) PRIMARY KEY,
			ParentId VARCHAR(255) NOT NULL,
			Amount DECIMAL(18,2) NOT NULL,
			Status VARCHAR(64) NOT NULL,
			OccurredOn DATE NOT NULL,
			IsDeleted BOOL NOT NULL DEFAULT 0
		)`,
		`INSERT INTO RollupLine (Id, ParentId, Amount, Status, OccurredOn, IsDeleted) VALUES
			('line-1', 'parent-a', 10, 'Responded', '2026-01-02', 0),
			('line-2', 'parent-a', 20, 'Pending', '2026-01-03', 0),
			('line-deleted', 'parent-a', 5, 'Responded', '2026-01-04', 1),
			('line-other', 'parent-b', 100, 'Responded', '2026-01-05', 0)`,
		`CREATE VIEW v_rollupline AS SELECT
			Id, ParentId, Amount, Status, OccurredOn, IsDeleted,
			Amount * 2 AS ComputedAmount,
			Status = 'Responded' AS HasResponded
		FROM RollupLine`,
	} {
		if _, err := db.ExecContext(t.Context(), statement); err != nil {
			t.Fatalf("prepare rollup repository fixture: %v\n%s", err, statement)
		}
	}
}

func rollupRepositorySchema() *models.ObjectMetadata {
	active := "active"
	computedAmount := "Amount * 2"
	hasResponded := `Status = "Responded"`
	dateReturn := models.FieldType(constants.FieldTypeDate)
	return &models.ObjectMetadata{
		APIName: "RollupLine",
		Fields: []models.FieldMetadata{
			{APIName: constants.FieldID, Type: constants.FieldTypeString, IsSystem: true},
			{APIName: "ParentId", Type: constants.FieldTypeReference},
			{APIName: "Amount", Type: constants.FieldTypeCurrency},
			{APIName: "Status", Type: constants.FieldTypeString},
			{APIName: "OccurredOn", Type: constants.FieldTypeDate, ReturnType: &dateReturn},
			{APIName: "ComputedAmount", Type: constants.FieldTypeFormula, Formula: &computedAmount, FormulaMode: &active},
			{APIName: "HasResponded", PhysicalName: "has_responded", Type: constants.FieldTypeFormula, Formula: &hasResponded, FormulaMode: &active},
			{APIName: constants.FieldIsDeleted, Type: constants.FieldTypeBoolean, IsSystem: true},
		},
	}
}

type integrationRollupSchemaProvider struct {
	schema *models.ObjectMetadata
}

func (s integrationRollupSchemaProvider) GetSchema(_ context.Context, _ string) *models.ObjectMetadata {
	return s.schema
}