//go:build integration

package analytics

import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"strconv"
	"testing"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/nexuscrm/persistence"
	"github.com/nexuscrm/shared/pkg/models"
)

func TestFormulaViewRollupReferenceSoftDeleteParityWithRollupRepository(t *testing.T) {
	ctx := context.Background()
	db, cleanup := openFormulaViewAggregateParityDB(t)
	defer cleanup()

	mustExec(t, db, "CREATE TABLE `ParentRecord` (`Id` VARCHAR(64) NOT NULL PRIMARY KEY, `IsDeleted` TINYINT(1) NOT NULL DEFAULT 0, `SumAmount__c` DOUBLE NULL, `CountAmount__c` BIGINT NULL, `AvgAmount__c` DOUBLE NULL, `MinAmount__c` BIGINT NULL, `MaxAmount__c` BIGINT NULL)")
	mustExec(t, db, "CREATE TABLE `ChildRecord` (`Id` VARCHAR(64) NOT NULL PRIMARY KEY, `ParentId` VARCHAR(64) NOT NULL, `Amount` INT NOT NULL, `IsDeleted` TINYINT(1) NOT NULL DEFAULT 0)")

	const parentID = "parent-001"
	mustExec(t, db, "INSERT INTO `ChildRecord` (`Id`, `ParentId`, `Amount`, `IsDeleted`) VALUES (?, ?, ?, ?)", "child-live-1", parentID, 10, 0)
	mustExec(t, db, "INSERT INTO `ChildRecord` (`Id`, `ParentId`, `Amount`, `IsDeleted`) VALUES (?, ?, ?, ?)", "child-live-2", parentID, 20, 0)
	mustExec(t, db, "INSERT INTO `ChildRecord` (`Id`, `ParentId`, `Amount`, `IsDeleted`) VALUES (?, ?, ?, ?)", "child-deleted-min", parentID, 1, 1)
	mustExec(t, db, "INSERT INTO `ChildRecord` (`Id`, `ParentId`, `Amount`, `IsDeleted`) VALUES (?, ?, ?, ?)", "child-deleted-max", parentID, 999, 1)

	meta := newFakeMetadata(map[string]*models.ObjectMetadata{
		"ChildRecord": {APIName: "ChildRecord"},
	})
	rollups := persistence.NewRollupRepository(db)
	rollups.SetSchemaProvider(meta)

	sumValue, err := rollups.CalculateRollup(ctx, persistence.RollupTypeSum, "ChildRecord", "Amount", "ParentId", parentID, nil)
	if err != nil {
		t.Fatalf("CalculateRollup(SUM): %v", err)
	}
	countValue, err := rollups.CalculateRollup(ctx, persistence.RollupTypeCount, "ChildRecord", "Amount", "ParentId", parentID, nil)
	if err != nil {
		t.Fatalf("CalculateRollup(COUNT): %v", err)
	}
	avgValue, err := rollups.CalculateRollup(ctx, persistence.RollupTypeAvg, "ChildRecord", "Amount", "ParentId", parentID, nil)
	if err != nil {
		t.Fatalf("CalculateRollup(AVG): %v", err)
	}
	minValue, err := rollups.CalculateRollup(ctx, persistence.RollupTypeMin, "ChildRecord", "Amount", "ParentId", parentID, nil)
	if err != nil {
		t.Fatalf("CalculateRollup(MIN): %v", err)
	}
	maxValue, err := rollups.CalculateRollup(ctx, persistence.RollupTypeMax, "ChildRecord", "Amount", "ParentId", parentID, nil)
	if err != nil {
		t.Fatalf("CalculateRollup(MAX): %v", err)
	}

	mustExec(t, db, "INSERT INTO `ParentRecord` (`Id`, `IsDeleted`, `SumAmount__c`, `CountAmount__c`, `AvgAmount__c`, `MinAmount__c`, `MaxAmount__c`) VALUES (?, 0, ?, ?, ?, ?, ?)",
		parentID,
		float64FromRollup(t, sumValue),
		int64FromRollup(t, countValue),
		float64FromRollup(t, avgValue),
		int64FromRollup(t, minValue),
		int64FromRollup(t, maxValue),
	)

	ddl, err := NewDSLViewBuilder().BuildViewDDL(ViewBuildConfig{
		ObjectName:    "ParentRecord",
		PhysicalTable: "ParentRecord",
		TableAlias:    "p",
		BaseColumns:   []string{"Id", "SumAmount__c", "CountAmount__c", "AvgAmount__c", "MinAmount__c", "MaxAmount__c"},
		Formulas: []FormulaDefinition{
			{Name: "ProjectedSumAmount__c", Expression: "SumAmount__c", ReturnType: "Number"},
			{Name: "ProjectedCountAmount__c", Expression: "CountAmount__c", ReturnType: "Number"},
			{Name: "ProjectedAvgAmount__c", Expression: "AvgAmount__c", ReturnType: "Number"},
			{Name: "ProjectedMinAmount__c", Expression: "MinAmount__c", ReturnType: "Number"},
			{Name: "ProjectedMaxAmount__c", Expression: "MaxAmount__c", ReturnType: "Number"},
		},
	})
	if err != nil {
		t.Fatalf("BuildViewDDL() error = %v", err)
	}
	mustExec(t, db, ddl)

	var viewSum, viewAvg float64
	var viewCount, viewMin, viewMax int64
	row := db.QueryRowContext(ctx, "SELECT `ProjectedSumAmount__c`, `ProjectedCountAmount__c`, `ProjectedAvgAmount__c`, `ProjectedMinAmount__c`, `ProjectedMaxAmount__c` FROM `v_parentrecord` WHERE `Id` = ?", parentID)
	if err := row.Scan(&viewSum, &viewCount, &viewAvg, &viewMin, &viewMax); err != nil {
		t.Fatalf("query formula view parity row: %v", err)
	}

	if viewSum != float64FromRollup(t, sumValue) {
		t.Fatalf("SUM parity mismatch: view=%v rollup=%v", viewSum, sumValue)
	}
	if viewCount != int64FromRollup(t, countValue) {
		t.Fatalf("COUNT parity mismatch: view=%v rollup=%v", viewCount, countValue)
	}
	if viewAvg != float64FromRollup(t, avgValue) {
		t.Fatalf("AVG parity mismatch: view=%v rollup=%v", viewAvg, avgValue)
	}
	if viewMin != int64FromRollup(t, minValue) {
		t.Fatalf("MIN parity mismatch: view=%v rollup=%v", viewMin, minValue)
	}
	if viewMax != int64FromRollup(t, maxValue) {
		t.Fatalf("MAX parity mismatch: view=%v rollup=%v", viewMax, maxValue)
	}

	if viewSum != 30 || viewCount != 2 || viewAvg != 15 || viewMin != 10 || viewMax != 20 {
		t.Fatalf("formula view returned wrong live-only aggregate set: sum=%v count=%v avg=%v min=%v max=%v", viewSum, viewCount, viewAvg, viewMin, viewMax)
	}
}

func openFormulaViewAggregateParityDB(t *testing.T) (*sql.DB, func()) {
	t.Helper()

	host := getenvDefault("TIDB_HOST", "127.0.0.1")
	port := getenvDefault("TIDB_PORT", "4001")
	user := getenvDefault("TIDB_USER", "root")
	password := os.Getenv("TIDB_PASSWORD")

	adminDSN := fmt.Sprintf("%s:%s@tcp(%s:%s)/?charset=utf8mb4&parseTime=True&loc=Local", user, password, host, port)
	adminDB, err := sql.Open("mysql", adminDSN)
	if err != nil {
		t.Skipf("skipping integration test: open admin TiDB: %v", err)
	}
	if err := adminDB.Ping(); err != nil {
		_ = adminDB.Close()
		t.Skipf("skipping integration test: TiDB not reachable at %s:%s: %v", host, port, err)
	}

	dbName := fmt.Sprintf("nexuscrm_formula_view_rollup_parity_%d", time.Now().UnixNano())
	if _, err := adminDB.Exec("CREATE DATABASE `" + dbName + "` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"); err != nil {
		_ = adminDB.Close()
		t.Skipf("skipping integration test: cannot create temporary database %s: %v", dbName, err)
	}

	testDSN := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local", user, password, host, port, dbName)
	db, err := sql.Open("mysql", testDSN)
	if err != nil {
		_, _ = adminDB.Exec("DROP DATABASE IF EXISTS `" + dbName + "`")
		_ = adminDB.Close()
		t.Fatalf("open test TiDB database %s: %v", dbName, err)
	}
	if err := db.Ping(); err != nil {
		_ = db.Close()
		_, _ = adminDB.Exec("DROP DATABASE IF EXISTS `" + dbName + "`")
		_ = adminDB.Close()
		t.Fatalf("ping test TiDB database %s: %v", dbName, err)
	}

	cleanup := func() {
		_ = db.Close()
		_, _ = adminDB.Exec("DROP DATABASE IF EXISTS `" + dbName + "`")
		_ = adminDB.Close()
	}
	return db, cleanup
}

func mustExec(t *testing.T, db *sql.DB, sqlText string, args ...any) {
	t.Helper()
	if _, err := db.Exec(sqlText, args...); err != nil {
		t.Fatalf("exec failed for %q: %v", sqlText, err)
	}
}

func getenvDefault(key, fallback string) string {
	if v := os.Getenv(key); v != "" {
		return v
	}
	return fallback
}

func float64FromRollup(t *testing.T, value any) float64 {
	t.Helper()
	switch v := value.(type) {
	case float64:
		return v
	case float32:
		return float64(v)
	case int64:
		return float64(v)
	case int:
		return float64(v)
	case []byte:
		f, err := strconv.ParseFloat(string(v), 64)
		if err != nil {
			t.Fatalf("parse rollup float %q: %v", string(v), err)
		}
		return f
	case string:
		f, err := strconv.ParseFloat(v, 64)
		if err != nil {
			t.Fatalf("parse rollup float %q: %v", v, err)
		}
		return f
	default:
		t.Fatalf("unsupported rollup float type %T (%v)", value, value)
		return 0
	}
}

func int64FromRollup(t *testing.T, value any) int64 {
	t.Helper()
	switch v := value.(type) {
	case int64:
		return v
	case int:
		return int64(v)
	case float64:
		return int64(v)
	case []byte:
		n, err := strconv.ParseInt(string(v), 10, 64)
		if err != nil {
			t.Fatalf("parse rollup int %q: %v", string(v), err)
		}
		return n
	case string:
		n, err := strconv.ParseInt(v, 10, 64)
		if err != nil {
			t.Fatalf("parse rollup int %q: %v", v, err)
		}
		return n
	default:
		t.Fatalf("unsupported rollup int type %T (%v)", value, value)
		return 0
	}
}