//go:build crm

package controlplane

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

	"github.com/nexuscrm/records"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/nexuscrm/shared/pkg/queryspec"
)

func TestActiveWriteMaterializedFormulaParity(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()

	r, err := NewRunner(conn)
	if err != nil {
		t.Fatalf("new runner: %v", err)
	}
	if err := r.RefreshCache(ctx); err != nil {
		t.Fatalf("refresh cache: %v", err)
	}
	// This test exercises Persistence + QuerySvc for record/query setup. Those
	// are exposed on the Runner's service graph (built via the public
	// BuildServiceGraph seam), so reuse it instead of importing the backend
	// composition root's application/services package.
	sm := r.SvcMgr
	admin := systemAdminSession()

	inserted, err := sm.Persistence().Insert(ctx, "WorkItem", models.SObject{
		"Name": "Alpha",
	}, admin)
	if err != nil {
		t.Fatalf("insert WorkItem with write_materialized active formula: %v", err)
	}
	insertedID, _ := inserted["Id"].(string)
	if insertedID == "" {
		t.Fatal("inserted WorkItem returned empty Id")
	}

	bulkResult, err := sm.Persistence().BulkInsert(ctx, "WorkItem", []models.SObject{{
		"Name": "Beta",
	}}, admin, records.BulkInsertOptions{})
	if err != nil {
		t.Fatalf("bulk insert WorkItem with write_materialized active formula: %v", err)
	}
	if bulkResult.SuccessCount != 1 || bulkResult.FailedCount != 0 {
		t.Fatalf("bulk insert result = %#v, want one success and zero failures", bulkResult)
	}

	detail, err := sm.Persistence().Retrieve(ctx, "WorkItem", insertedID, admin)
	if err != nil {
		t.Fatalf("detail retrieve WorkItem: %v", err)
	}

	queryResult, err := sm.QuerySvc().QueryWithSpec(ctx, queryspec.QuerySpec{
		ObjectAPIName: "WorkItem",
		SelectFields:  []string{"Id", "NameStored"},
		Filter: queryspec.CriteriaPredicate([]queryspec.QueryCriterion{{
			Field: "Id",
			Op:    "=",
			Val:   insertedID,
		}}),
		Limit: 1,
	}, admin)
	if err != nil {
		t.Fatalf("query WorkItem: %v", err)
	}
	if len(queryResult.Records) != 1 {
		t.Fatalf("query records = %#v, want exactly one record", queryResult.Records)
	}

	var rawInserted sql.NullString
	if err := conn.DB().QueryRowContext(ctx, "SELECT NameStored FROM WorkItem WHERE Id = ?", insertedID).Scan(&rawInserted); err != nil {
		t.Fatalf("raw SQL query for inserted WorkItem: %v", err)
	}
	var bulkID string
	var rawBulk sql.NullString
	if err := conn.DB().QueryRowContext(ctx, "SELECT Id, NameStored FROM WorkItem WHERE Name = ? LIMIT 1", "Beta").Scan(&bulkID, &rawBulk); err != nil {
		t.Fatalf("raw SQL query for bulk inserted WorkItem: %v", err)
	}

	if got := detail["NameStored"]; got != "Alpha" {
		t.Fatalf("detail NameStored = %#v, want Alpha", got)
	}
	if got := queryResult.Records[0]["NameStored"]; got != "Alpha" {
		t.Fatalf("query NameStored = %#v, want Alpha", got)
	}
	if !rawInserted.Valid || rawInserted.String != "Alpha" {
		t.Fatalf("raw SQL NameStored = %#v, want Alpha", rawInserted)
	}
	if bulkID == "" {
		t.Fatal("bulk inserted WorkItem raw SQL returned empty Id")
	}
	if !rawBulk.Valid || rawBulk.String != "Beta" {
		t.Fatalf("bulk raw SQL NameStored = %#v, want Beta", rawBulk)
	}

	var viewOnlyCount int
	if err := conn.DB().QueryRowContext(ctx, `
		SELECT COUNT(*)
		  FROM information_schema.COLUMNS
		 WHERE TABLE_SCHEMA = DATABASE()
		   AND TABLE_NAME = 'WorkItem'
		   AND COLUMN_NAME = 'NameEcho'`,
	).Scan(&viewOnlyCount); err != nil {
		t.Fatalf("query information_schema for NameEcho: %v", err)
	}
	if viewOnlyCount != 0 {
		t.Fatalf("physical column WorkItem.NameEcho count = %d, want 0 for view-only active formula", viewOnlyCount)
	}

	var storedCount int
	if err := conn.DB().QueryRowContext(ctx, `
		SELECT COUNT(*)
		  FROM information_schema.COLUMNS
		 WHERE TABLE_SCHEMA = DATABASE()
		   AND TABLE_NAME = 'WorkItem'
		   AND COLUMN_NAME = 'NameStored'`,
	).Scan(&storedCount); err != nil {
		t.Fatalf("query information_schema for NameStored: %v", err)
	}
	if storedCount != 1 {
		t.Fatalf("physical column WorkItem.NameStored count = %d, want 1", storedCount)
	}
}