package controlplane

import (
	"context"
	"crypto/tls"
	"database/sql"
	"fmt"
	"net"
	"os"
	"testing"
	"time"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/go-sql-driver/mysql"
	"github.com/nexuscrm/analytics"
	"github.com/nexuscrm/db"
	"github.com/nexuscrm/kernel/pkg/logging"
	"github.com/nexuscrm/metadata"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/pingcap-inc/nexuscrm-controlplane/packs"
)

const requireActiveFormulaSmokeTiDBEnv = "NEXUSCRM_REQUIRE_ACTIVE_FORMULA_SMOKE_TIDB"

// TestActiveFormulaIssue770HermeticMaterializeCompileRebuild is the stable,
// always-on acceptance gate for issue #770. It covers the
// materialize → compile → rebuild leg of the active-formula contract using
// sqlmock to stub the DB surface — no TiDB connection required.
//
// Coverage contract (hermetic, always-on):
//   - fixture loads and validates as an installable source-backed pack
//     (ValidateInstallablePackSource confirms the fixture authors the
//     active-formula field metadata that the discovery/rebuild path depends on)
//   - materializeSourceBackedPackDerived: RefreshCache → touchedObjects
//     discovery (SELECT _System_Field WHERE FormulaMode='active') →
//     RebuildView (CREATE OR REPLACE) → RefreshViewCache → verifySourceBackedDerivedArtifacts
//   - verifySourceBackedDerivedArtifactsWithCompileProof: re-discovers
//     touched objects → confirms view exists → PreviewDDL (in-memory
//     formula compile via FormulaViewService) succeeds without DB
//
// What this test does NOT cover — and why:
//
//	The import/apply leg (InstallPackFromSource → installPackFromSourceUnlocked →
//	applyObjectSpecs → BatchSaveFieldMetadata) writes FormulaMode=active into
//	_System_Field via real DDL and multi-table SQL UPSERTs that depend on
//	bootstrap.VerifySchemaReadiness and a live SchemaRepository. That path
//	cannot be exercised with sqlmock without rewriting ~15 interface methods,
//	all of which require TiDB-specific DDL execution.
//
//	The import/apply leg is covered by TestActiveFormulaSmokeFixtureInstallsCompilesAndRebuilds
//	(gated by NEXUSCRM_REQUIRE_ACTIVE_FORMULA_SMOKE_TIDB=1), which calls
//	InstallPackFromSource against a real TiDB instance and verifies both the
//	installed pack metadata and the formula view.
//
//	A regression in InstallPackFromSource that stops authoring active-formula
//	metadata would NOT be caught by this hermetic test — it would be caught
//	by the TiDB-gated test.
//
// This test must pass under the normal package acceptance command:
//
//	(cd backend && go test ./internal/controlplane/... ./internal/features/analytics/...)
func TestActiveFormulaIssue770HermeticMaterializeCompileRebuild(t *testing.T) {
	root := activeFormulaSmokePackSourceDirForRequiredGate(t)
	report := packs.ValidateInstallablePackSource("local_path:" + root)
	if report.HasErrors() || report.Pack == nil {
		t.Fatalf("active-formula-smoke fixture is not installable:\n%s", report.Error())
	}

	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	schema := activeFormulaSmokeWorkItemSchemaForTest(t, report.Pack)
	metaRepo := &activeFormulaSmokeMetadataRepo{schemas: []*models.ObjectMetadata{schema}}
	metaSvc := metadata.NewMetadataService(metaRepo, nil, nil)
	fvSvc := analytics.NewFormulaViewService(db, metaSvc, logging.Default)
	runner := &Runner{
		DB: database.NewConnectionFromDB(db),
		SvcMgr: newTestServiceGraph(testServiceGraphConfig{
			Metadata:    metaSvc,
			FormulaView: fvSvc,
		}),
	}

	// materializeSourceBackedPackDerived sequence:
	//   1. touchedObjects (RefreshCache uses fake repo — no DB call)
	//   2. touchedObjects SELECT
	//   3. INFORMATION_SCHEMA.COLUMNS physical-column probe
	//   4. CREATE OR REPLACE VIEW
	//   5. RefreshViewCache SELECT TABLE_NAME
	//   6. touchedObjects SELECT (verifySourceBackedDerivedArtifacts)
	//   7. formulaViewExists SELECT COUNT(*)

	expectTouchedActiveFormulaObjects(mock, "workitem", "WorkItem")
	expectFormulaViewPhysicalColumnsForSchema(mock, schema)
	mock.ExpectExec("CREATE OR REPLACE SQL SECURITY INVOKER VIEW `v_workitem`").
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectQuery("SELECT TABLE_NAME FROM information_schema\\.VIEWS WHERE TABLE_SCHEMA = DATABASE\\(\\) AND TABLE_NAME LIKE 'v\\\\_%'").
		WillReturnRows(sqlmock.NewRows([]string{"TABLE_NAME"}).AddRow("v_workitem"))
	expectTouchedActiveFormulaObjects(mock, "workitem", "WorkItem")
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)[\\s\\S]*information_schema\\.VIEWS").
		WithArgs("v_workitem").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))
	// View-usability probe (issue #1592), per verifySourceBackedDerivedArtifacts.
	mock.ExpectQuery("SELECT 1 FROM .v_workitem. LIMIT 1").
		WillReturnRows(sqlmock.NewRows([]string{"1"}))

	if err := materializeSourceBackedPackDerived(context.Background(), runner, report.Pack, false); err != nil {
		t.Fatalf("materializeSourceBackedPackDerived: %v", err)
	}

	// compile proof: verifySourceBackedDerivedArtifactsWithCompileProof using
	// formulaViewCompileProofForRunner (PreviewDDL) reuses the same physical
	// column probe as rebuild so probe failures fail closed.
	//   8. touchedObjects SELECT (touched-objects discovery)
	//   9. formulaViewExists SELECT COUNT(*) (view exists)
	//   10. view-usability SELECT
	//   11. INFORMATION_SCHEMA.COLUMNS physical-column probe

	expectTouchedActiveFormulaObjects(mock, "workitem", "WorkItem")
	mock.ExpectQuery("SELECT COUNT\\(\\*\\)[\\s\\S]*information_schema\\.VIEWS").
		WithArgs("v_workitem").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))
	// View-usability probe (issue #1592), per verifySourceBackedDerivedArtifacts.
	mock.ExpectQuery("SELECT 1 FROM .v_workitem. LIMIT 1").
		WillReturnRows(sqlmock.NewRows([]string{"1"}))
	expectFormulaViewPhysicalColumnsForSchema(mock, schema)

	if err := verifySourceBackedDerivedArtifactsWithCompileProof(
		context.Background(),
		db,
		report.Pack,
		formulaViewCompileProofForRunner(runner),
	); err != nil {
		t.Fatalf("verifySourceBackedDerivedArtifactsWithCompileProof: %v", err)
	}

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestActiveFormulaSmokeFixtureInstallsCompilesAndRebuilds(t *testing.T) {
	if os.Getenv(requireActiveFormulaSmokeTiDBEnv) != "1" {
		t.Skipf("skipping live active-formula smoke; set %s=1 in a TiDB-backed gate to require it", requireActiveFormulaSmokeTiDBEnv)
	}

	ctx := context.Background()
	conn := installActiveFormulaSmokeFixture(t, ctx)
	defer conn.Close()

	if err := VerifyInstalledPackByName(ctx, conn.DB(), "active-formula-smoke", true); err != nil {
		t.Fatalf("verify installed active-formula-smoke pack: %v", err)
	}

	var viewCount int
	if err := conn.DB().QueryRowContext(ctx, `
		SELECT COUNT(*)
		  FROM information_schema.VIEWS
		 WHERE TABLE_SCHEMA = DATABASE()
		   AND TABLE_NAME = 'v_workitem'`,
	).Scan(&viewCount); err != nil {
		t.Fatalf("query active formula smoke view: %v", err)
	}
	if viewCount != 1 {
		t.Fatalf("active formula smoke view count = %d, want 1", viewCount)
	}
}

func TestActiveFormulaIsblankNonTextSmokeTiDB(t *testing.T) {
	if os.Getenv(requireActiveFormulaSmokeTiDBEnv) != "1" {
		t.Skipf("skipping live active-formula smoke; set %s=1 in a TiDB-backed gate to require it", requireActiveFormulaSmokeTiDBEnv)
	}

	ctx := context.Background()
	cfg, cleanup := newTemporaryActiveFormulaSmokeDatabase(t)
	defer cleanup()

	conn := openActiveFormulaSmokeConnection(t, cfg)
	defer conn.Close()

	if _, err := conn.DB().ExecContext(ctx,
		"CREATE TABLE `synthetic_formula` ("+
			"`Id` VARCHAR(18) PRIMARY KEY, "+
			"`Date_Field__c` DATE NULL, "+
			"`Months_Field__c` DECIMAL(18,2) NULL"+
			")",
	); err != nil {
		t.Fatalf("create synthetic_formula table: %v", err)
	}
	if _, err := conn.DB().ExecContext(ctx,
		"INSERT INTO `synthetic_formula` (`Id`, `Date_Field__c`, `Months_Field__c`) "+
			"VALUES ('row-1', '2026-05-01', 12)",
	); err != nil {
		t.Fatalf("insert synthetic formula row: %v", err)
	}

	ddl, err := analytics.NewDSLViewBuilder().BuildViewDDL(analytics.ViewBuildConfig{
		ObjectName:    "SyntheticFormula",
		PhysicalTable: "synthetic_formula",
		TableAlias:    "t",
		BaseColumns:   []string{"Id", "Date_Field__c", "Months_Field__c"},
		FieldTypes: map[string]string{
			"Date_Field__c":   "date",
			"Months_Field__c": "double",
		},
		Formulas: []analytics.FormulaDefinition{
			{
				Name:       "Derived_End_Date__c",
				Expression: "IF(AND(NOT(ISBLANK(Date_Field__c)), NOT(ISBLANK(Months_Field__c))), __DATE_ADD_MONTHS__(Date_Field__c, ROUND(Months_Field__c, 0)) - 1, null)",
				ReturnType: "date",
			},
		},
	})
	if err != nil {
		t.Fatalf("build synthetic formula view DDL: %v", err)
	}
	if _, err := conn.DB().ExecContext(ctx, ddl); err != nil {
		t.Fatalf("create synthetic formula view: %v\nDDL:\n%s", err, ddl)
	}

	var got sql.NullString
	if err := conn.DB().QueryRowContext(ctx,
		"SELECT CAST(`Derived_End_Date__c` AS CHAR) "+
			"FROM `v_syntheticformula` "+
			"WHERE `Id` = 'row-1'",
	).Scan(&got); err != nil {
		t.Fatalf("query synthetic formula view: %v", err)
	}
	if !got.Valid || got.String != "2027-04-30" {
		t.Fatalf("Derived_End_Date__c = %q valid=%v, want 2027-04-30", got.String, got.Valid)
	}
}

func installActiveFormulaSmokeFixture(t *testing.T, ctx context.Context) *database.TiDBConnection {
	t.Helper()

	cfg, cleanup := newTemporaryActiveFormulaSmokeDatabase(t)
	t.Cleanup(cleanup)
	setActiveFormulaSmokeDatabaseEnv(t, cfg)

	conn := openActiveFormulaSmokeConnection(t, cfg)
	if err := InitL0(ctx, conn); err != nil {
		_ = conn.Close()
		t.Fatalf("init-l0: %v", err)
	}
	_ = conn.Close()

	conn = openActiveFormulaSmokeConnection(t, cfg)
	r, err := NewRunner(conn)
	if err != nil {
		_ = conn.Close()
		t.Fatalf("new runner: %v", err)
	}
	if err := r.RefreshCache(ctx); err != nil {
		_ = conn.Close()
		t.Fatalf("initial cache refresh: %v", err)
	}
	if err := InstallPackFromSource(ctx, r, "local_path:"+activeFormulaSmokePackSourceDirForRequiredGate(t)); err != nil {
		_ = conn.Close()
		t.Fatalf("install active-formula-smoke source-backed pack: %v", err)
	}
	return conn
}

func newTemporaryActiveFormulaSmokeDatabase(t *testing.T) (*mysql.Config, func()) {
	t.Helper()

	host := os.Getenv("TIDB_HOST")
	if host == "" {
		host = "127.0.0.1"
	}
	port := os.Getenv("TIDB_PORT")
	if port == "" {
		port = "4000"
	}
	user := os.Getenv("TIDB_USER")
	if user == "" {
		user = "root"
	}

	cfg := &mysql.Config{
		User:                 user,
		Passwd:               os.Getenv("TIDB_PASSWORD"),
		Net:                  "tcp",
		Addr:                 host + ":" + port,
		Params:               map[string]string{"charset": "utf8mb4", "collation": "utf8mb4_unicode_ci"},
		ParseTime:            true,
		Loc:                  time.Local,
		AllowNativePasswords: true,
	}
	if host != "127.0.0.1" && host != "localhost" {
		tlsName := fmt.Sprintf("tidb_active_formula_smoke_%d", time.Now().UnixNano())
		if err := mysql.RegisterTLSConfig(tlsName, &tls.Config{
			MinVersion: tls.VersionTLS12,
			ServerName: host,
		}); err == nil {
			cfg.TLSConfig = tlsName
		}
	}

	adminDB, err := sql.Open("mysql", cfg.FormatDSN())
	if err != nil {
		t.Fatalf("open admin TiDB connection: %v", err)
	}
	if err := adminDB.Ping(); err != nil {
		_ = adminDB.Close()
		t.Fatalf("%s=1 requires TiDB at %s:%s: %v", requireActiveFormulaSmokeTiDBEnv, host, port, err)
	}

	dbName := fmt.Sprintf("nexuscrm_active_formula_smoke_%d", time.Now().UnixNano())
	if _, err := adminDB.Exec("CREATE DATABASE `" + dbName + "` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"); err != nil {
		_ = adminDB.Close()
		t.Fatalf("create temporary active formula smoke database %s: %v", dbName, err)
	}

	testCfg := *cfg
	testCfg.DBName = dbName
	cleanup := func() {
		_, _ = adminDB.Exec("DROP DATABASE IF EXISTS `" + dbName + "`")
		_ = adminDB.Close()
	}
	return &testCfg, cleanup
}

func openActiveFormulaSmokeConnection(t *testing.T, cfg *mysql.Config) *database.TiDBConnection {
	t.Helper()
	rawDB, err := sql.Open("mysql", cfg.FormatDSN())
	if err != nil {
		t.Fatalf("open TiDB test connection: %v", err)
	}
	rawDB.SetMaxOpenConns(20)
	rawDB.SetMaxIdleConns(20)
	rawDB.SetConnMaxLifetime(5 * time.Minute)
	if err := rawDB.Ping(); err != nil {
		_ = rawDB.Close()
		t.Fatalf("ping TiDB test connection: %v", err)
	}
	return database.NewConnectionFromDB(rawDB)
}

func setActiveFormulaSmokeDatabaseEnv(t *testing.T, cfg *mysql.Config) {
	t.Helper()
	host, port, err := net.SplitHostPort(cfg.Addr)
	if err != nil {
		t.Fatalf("split TiDB address %q: %v", cfg.Addr, err)
	}
	t.Setenv("TIDB_HOST", host)
	t.Setenv("TIDB_PORT", port)
	t.Setenv("TIDB_USER", cfg.User)
	t.Setenv("TIDB_PASSWORD", cfg.Passwd)
	t.Setenv("TIDB_DATABASE", cfg.DBName)
}

func activeFormulaSmokePackSourceDirForRequiredGate(t *testing.T) string {
	t.Helper()

	return controlplaneTestPackFixtureDir(t, "active-formula-smoke")
}