package automationintegration_test

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

	"github.com/go-sql-driver/mysql"
	automation "github.com/nexuscrm/automation"
	"github.com/nexuscrm/backend/internal/application/services"
	"github.com/nexuscrm/backend/internal/bootstrap"
	database "github.com/nexuscrm/db"
	appErrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/pingcap-inc/nexuscrm-controlplane"
)

func TestApprovalService_Submit_CanonicalTargetIDWithSkipEntryCriteria_Integration(t *testing.T) {
	ctx := constants.WithSecurityContext(context.Background(), constants.SystemContext, "issue-351 integration skip-entry canonical id")
	conn, sm := setupApprovalSubmitRuntime(t, ctx)

	installActiveApprovalProcess(t, ctx, conn, "Lead", "Status equals Qualified")
	leadID := insertLeadForApproval(t, ctx, sm, "Qualified")

	rawID := " " + leadID + " "
	result, err := sm.Approval.Submit(ctx, automation.SubmitRequest{
		RecordID:          rawID,
		SkipEntryCriteria: true,
	}, systemAdminSession())
	if err != nil {
		t.Fatalf("Submit() error = %v", err)
	}
	if got, _ := result["TargetObjectId"].(string); got != leadID {
		t.Fatalf("response TargetObjectId = %q, want %q", got, leadID)
	}
	assertApprovalRowsCanonicalOnly(t, conn.DB(), leadID, rawID)
}

func TestApprovalService_Submit_CanonicalTargetIDWithEmptyEntryFilter_Integration(t *testing.T) {
	ctx := constants.WithSecurityContext(context.Background(), constants.SystemContext, "issue-351 integration empty entryfilter canonical id")
	conn, sm := setupApprovalSubmitRuntime(t, ctx)

	installActiveApprovalProcess(t, ctx, conn, "Lead", "")
	leadID := insertLeadForApproval(t, ctx, sm, "Qualified")

	rawID := " " + leadID + " "
	result, err := sm.Approval.Submit(ctx, automation.SubmitRequest{
		RecordID: rawID,
	}, systemAdminSession())
	if err != nil {
		t.Fatalf("Submit() error = %v", err)
	}
	if got, _ := result["TargetObjectId"].(string); got != leadID {
		t.Fatalf("response TargetObjectId = %q, want %q", got, leadID)
	}
	assertApprovalRowsCanonicalOnly(t, conn.DB(), leadID, rawID)
}

func TestApprovalService_Submit_NonexistentTargetSkipCriteriaNoMutation_Integration(t *testing.T) {
	ctx := constants.WithSecurityContext(context.Background(), constants.SystemContext, "issue-351 integration nonexistent skip-entry")
	conn, sm := setupApprovalSubmitRuntime(t, ctx)

	installActiveApprovalProcess(t, ctx, conn, "Lead", "Status equals Qualified")
	fakeID := fakeLeadID(t, ctx, sm)

	_, err := sm.Approval.Submit(ctx, automation.SubmitRequest{
		RecordID:          fakeID,
		SkipEntryCriteria: true,
	}, systemAdminSession())
	if err == nil {
		t.Fatal("Submit() error = nil, want not-found")
	}
	if !appErrors.IsNotFound(err) {
		t.Fatalf("Submit() error = %T %v, want not-found", err, err)
	}
	assertNoApprovalRowsForTarget(t, conn.DB(), strings.TrimSpace(fakeID))
}

func TestApprovalService_Submit_NonexistentTargetEmptyEntryFilterNoMutation_Integration(t *testing.T) {
	ctx := constants.WithSecurityContext(context.Background(), constants.SystemContext, "issue-351 integration nonexistent empty entryfilter")
	conn, sm := setupApprovalSubmitRuntime(t, ctx)

	installActiveApprovalProcess(t, ctx, conn, "Lead", "")
	fakeID := fakeLeadID(t, ctx, sm)

	_, err := sm.Approval.Submit(ctx, automation.SubmitRequest{
		RecordID: fakeID,
	}, systemAdminSession())
	if err == nil {
		t.Fatal("Submit() error = nil, want not-found")
	}
	if !appErrors.IsNotFound(err) {
		t.Fatalf("Submit() error = %T %v, want not-found", err, err)
	}
	assertNoApprovalRowsForTarget(t, conn.DB(), strings.TrimSpace(fakeID))
}

func TestApprovalService_Submit_DeletedTargetSkipCriteriaNoMutation_Integration(t *testing.T) {
	ctx := constants.WithSecurityContext(context.Background(), constants.SystemContext, "issue-351 integration deleted skip-entry")
	conn, sm := setupApprovalSubmitRuntime(t, ctx)

	installActiveApprovalProcess(t, ctx, conn, "Lead", "Status equals Qualified")
	leadID := insertLeadForApproval(t, ctx, sm, "Qualified")
	if err := sm.Persistence.Delete(ctx, "Lead", leadID, systemAdminSession()); err != nil {
		t.Fatalf("soft-delete Lead: %v", err)
	}

	_, err := sm.Approval.Submit(ctx, automation.SubmitRequest{
		RecordID:          leadID,
		SkipEntryCriteria: true,
	}, systemAdminSession())
	if err == nil {
		t.Fatal("Submit() error = nil, want not-found")
	}
	if !appErrors.IsNotFound(err) {
		t.Fatalf("Submit() error = %T %v, want not-found", err, err)
	}
	assertNoApprovalRowsForTarget(t, conn.DB(), leadID)
}

func TestApprovalService_Submit_DeletedTargetEmptyEntryFilterNoMutation_Integration(t *testing.T) {
	ctx := constants.WithSecurityContext(context.Background(), constants.SystemContext, "issue-351 integration deleted empty entryfilter")
	conn, sm := setupApprovalSubmitRuntime(t, ctx)

	installActiveApprovalProcess(t, ctx, conn, "Lead", "")
	leadID := insertLeadForApproval(t, ctx, sm, "Qualified")
	if err := sm.Persistence.Delete(ctx, "Lead", leadID, systemAdminSession()); err != nil {
		t.Fatalf("soft-delete Lead: %v", err)
	}

	_, err := sm.Approval.Submit(ctx, automation.SubmitRequest{
		RecordID: leadID,
	}, systemAdminSession())
	if err == nil {
		t.Fatal("Submit() error = nil, want not-found")
	}
	if !appErrors.IsNotFound(err) {
		t.Fatalf("Submit() error = %T %v, want not-found", err, err)
	}
	assertNoApprovalRowsForTarget(t, conn.DB(), leadID)
}

func TestApprovalService_Submit_CriteriaFalseNoMutation_Integration(t *testing.T) {
	ctx := constants.WithSecurityContext(context.Background(), constants.SystemContext, "issue-351 integration criteria false")
	conn, sm := setupApprovalSubmitRuntime(t, ctx)

	installActiveApprovalProcess(t, ctx, conn, "Lead", "Status equals Qualified")
	leadID := insertLeadForApproval(t, ctx, sm, "Open - Not Contacted")

	_, err := sm.Approval.Submit(ctx, automation.SubmitRequest{
		RecordID: leadID,
	}, systemAdminSession())
	if err == nil {
		t.Fatal("Submit() error = nil, want entry criteria failure")
	}
	if !errors.Is(err, automation.ErrEntryCriteriaNotMet) && !appErrors.IsValidation(err) {
		t.Fatalf("Submit() error = %T %v, want entry criteria validation", err, err)
	}
	assertNoApprovalRowsForTarget(t, conn.DB(), leadID)

	var status string
	if err := conn.DB().QueryRowContext(ctx, "SELECT `Status` FROM `Lead` WHERE `Id` = ?", leadID).Scan(&status); err != nil {
		t.Fatalf("query Lead.Status: %v", err)
	}
	if status != "Open - Not Contacted" {
		t.Fatalf("Lead.Status after rejected submit = %q, want unchanged %q", status, "Open - Not Contacted")
	}
}

func TestApprovalService_Submit_PendingConflictSurvivesEntryCriteriaDrift_Integration(t *testing.T) {
	ctx := constants.WithSecurityContext(context.Background(), constants.SystemContext, "approval submit pending conflict after criteria drift")
	st := setupSubmitForApprovalRuntime(t)
	db := st.conn.DB()

	seedSFAScenario(t, ctx, st, sfaScenarioSeed{
		objectAPIName: "XSFADirectDrift__c",
		idSuffix:      "direct-drift",
		keyPrefix:     "x0e",
		entryFilter:   `{"criteriaItems":[{"field":"Status__c","operation":"equals","value":"draft"}]}`,
		pdID:          "pd-xsfa-direct-drift",
		pdName:        "XSFA_Direct_Drift_Approval",
		pdMetadata:    "{}",
		stampOnSubmit: true,
		grantPerms:    true,
	})

	userCtx := constants.WithSecurityContext(context.Background(), constants.UserContext, "create and submit direct-drift record as regular user")
	record, err := st.sm.Persistence.Insert(userCtx, "XSFADirectDrift__c", models.SObject{
		"Name":      "XSFA Direct Drift",
		"Status__c": "draft",
	}, st.user)
	if err != nil {
		t.Fatalf("insert direct-drift record: %v", err)
	}
	recordID, _ := record[constants.FieldID].(string)
	mustDrainSFAOutbox(t, ctx, st)

	request := automation.SubmitRequest{ObjectAPIName: "XSFADirectDrift__c", RecordID: recordID}
	if _, err := st.sm.Approval.Submit(userCtx, request, st.user); err != nil {
		t.Fatalf("first Submit(direct-drift): %v", err)
	}
	mustDrainSFAOutbox(t, ctx, st)

	if got := queryString(t, db, `SELECT COALESCE(Status__c, '') FROM XSFADirectDrift__c WHERE Id = ?`, recordID); got != "submitted" {
		t.Fatalf("Status__c after first submit = %q, want %q", got, "submitted")
	}

	_, err = st.sm.Approval.Submit(
		constants.WithSecurityContext(context.Background(), constants.UserContext, "duplicate direct-drift submit as the same user"),
		request,
		st.user,
	)
	if !errors.Is(err, automation.ErrRecordAlreadyPending) {
		t.Fatalf("second Submit(direct-drift) error = %T %v, want ErrRecordAlreadyPending", err, err)
	}

	instances := sfaInstanceRows(t, db, recordID)
	if len(instances) != 1 || instances[0].status != constants.ApprovalStatusPending {
		t.Fatalf("ProcessInstance rows after duplicate submit = %#v, want one Pending instance", instances)
	}
}

var (
	approvalSubmitRuntimeOnce sync.Once
	approvalSubmitRuntime     approvalSubmitRuntimeState
)

type approvalSubmitRuntimeState struct {
	conn       *database.TiDBConnection
	sm         *services.ServiceManager
	cleanup    func()
	skipReason string
	err        error
}

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

	approvalSubmitRuntimeOnce.Do(func() {
		approvalSubmitRuntime = resolveApprovalSubmitRuntime(ctx)
	})

	if approvalSubmitRuntime.skipReason != "" {
		t.Skipf("%s", approvalSubmitRuntime.skipReason)
	}
	if approvalSubmitRuntime.err != nil {
		t.Fatal(approvalSubmitRuntime.err)
	}
	if approvalSubmitRuntime.conn == nil || approvalSubmitRuntime.sm == nil {
		t.Fatal("approval submit runtime setup returned nil handles")
	}

	t.Cleanup(func() {
		if approvalSubmitRuntime.cleanup != nil {
			// Intentionally held until process exit; per-test cleanup would
			// invalidate the shared runtime for later integration cases.
		}
	})
	return approvalSubmitRuntime.conn, approvalSubmitRuntime.sm
}

func resolveApprovalSubmitRuntime(ctx context.Context) approvalSubmitRuntimeState {
	cfg, cleanup, skipReason, err := newApprovalSubmitTemporaryIntegrationDatabase()
	if skipReason != "" {
		return approvalSubmitRuntimeState{skipReason: skipReason}
	}
	if err != nil {
		return approvalSubmitRuntimeState{err: err}
	}

	state := approvalSubmitRuntimeState{cleanup: cleanup}
	fail := func(err error, conn *database.TiDBConnection) approvalSubmitRuntimeState {
		if conn != nil {
			_ = conn.Close()
		}
		if cleanup != nil {
			cleanup()
		}
		return approvalSubmitRuntimeState{err: err}
	}

	if err := setApprovalSubmitDatabaseEnvFromConfig(cfg); err != nil {
		return fail(err, nil)
	}

	conn, err := openApprovalSubmitIntegrationConnection(cfg)
	if err != nil {
		return fail(err, nil)
	}
	if err := controlplane.InitL0(ctx, conn); err != nil {
		return fail(fmt.Errorf("InitL0: %w", err), conn)
	}
	_ = conn.Close()

	conn, err = openApprovalSubmitIntegrationConnection(cfg)
	if err != nil {
		return fail(err, nil)
	}
	runner, err := controlplane.NewRunner(conn)
	if err != nil {
		return fail(fmt.Errorf("NewRunner: %w", err), conn)
	}
	if err := runner.RefreshCache(ctx); err != nil {
		return fail(fmt.Errorf("RefreshCache: %w", err), conn)
	}
	if err := controlplane.ApplyPacks(ctx, runner, false); err != nil {
		return fail(fmt.Errorf("ApplyPacks: %w", err), conn)
	}
	if err := ensureOfficialPackRootForAutomationProcess(); err != nil {
		return fail(fmt.Errorf("set official pack root: %w", err), conn)
	}
	registerCRMDistributionHostPrimitives()
	if err := controlplane.InstallPackFromSource(ctx, runner, "official_repo:salesforce-crm-standard"); err != nil {
		return fail(fmt.Errorf("InstallPackFromSource(salesforce-crm-standard): %w", err), conn)
	}

	sm, err := services.NewServiceManager(services.BuildInfrastructureDeps(conn))
	if err != nil {
		return fail(fmt.Errorf("NewServiceManager: %w", err), conn)
	}
	if err := sm.RefreshMetadataCache(ctx); err != nil {
		return fail(fmt.Errorf("RefreshMetadataCache: %w", err), conn)
	}
	if err := ensureApprovalSystemKeyPrefixes(ctx, conn.DB()); err != nil {
		return fail(fmt.Errorf("ensure approval system key prefixes: %w", err), conn)
	}
	if err := sm.RefreshMetadataCache(ctx); err != nil {
		return fail(fmt.Errorf("RefreshMetadataCache after approval key prefixes: %w", err), conn)
	}

	state.conn = conn
	state.sm = sm
	return state
}

func newApprovalSubmitTemporaryIntegrationDatabase() (*mysql.Config, func(), string, error) {
	host := os.Getenv("TIDB_HOST")
	if host == "" {
		host = "127.0.0.1"
	}
	port := os.Getenv("TIDB_PORT")
	if port == "" {
		port = "4001"
	}
	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_issue351_%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 {
		return nil, nil, "", fmt.Errorf("open admin TiDB connection: %w", err)
	}
	if err := adminDB.Ping(); err != nil {
		_ = adminDB.Close()
		return nil, nil, fmt.Sprintf("skipping integration test: TiDB is not reachable at %s:%s: %v", host, port, err), nil
	}

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

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

func openApprovalSubmitIntegrationConnection(cfg *mysql.Config) (*database.TiDBConnection, error) {
	rawDB, err := sql.Open("mysql", cfg.FormatDSN())
	if err != nil {
		return nil, fmt.Errorf("open TiDB test connection: %w", err)
	}
	rawDB.SetMaxOpenConns(20)
	rawDB.SetMaxIdleConns(20)
	rawDB.SetConnMaxLifetime(5 * time.Minute)
	if err := rawDB.Ping(); err != nil {
		_ = rawDB.Close()
		return nil, fmt.Errorf("ping TiDB test connection: %w", err)
	}
	return database.NewConnectionFromDB(rawDB), nil
}

func setApprovalSubmitDatabaseEnvFromConfig(cfg *mysql.Config) error {
	host, port, err := net.SplitHostPort(cfg.Addr)
	if err != nil {
		return fmt.Errorf("split TiDB address %q: %w", cfg.Addr, err)
	}
	if err := os.Setenv("TIDB_HOST", host); err != nil {
		return fmt.Errorf("set TIDB_HOST: %w", err)
	}
	if err := os.Setenv("TIDB_PORT", port); err != nil {
		return fmt.Errorf("set TIDB_PORT: %w", err)
	}
	if err := os.Setenv("TIDB_USER", cfg.User); err != nil {
		return fmt.Errorf("set TIDB_USER: %w", err)
	}
	if err := os.Setenv("TIDB_PASSWORD", cfg.Passwd); err != nil {
		return fmt.Errorf("set TIDB_PASSWORD: %w", err)
	}
	if err := os.Setenv("TIDB_DATABASE", cfg.DBName); err != nil {
		return fmt.Errorf("set TIDB_DATABASE: %w", err)
	}
	return nil
}

func ensureApprovalSystemKeyPrefixes(ctx context.Context, db *sql.DB) error {
	approvalTables := map[string]struct{}{
		constants.TableProcessDefinition:       {},
		constants.TableProcessNode:             {},
		constants.TableProcessInstance:         {},
		constants.TableProcessInstanceHistory:  {},
		constants.TableProcessInstanceNode:     {},
		constants.TableProcessInstanceWorkitem: {},
		constants.TableProcessAction:           {},
	}
	updates := map[string]string{}
	for _, def := range bootstrap.GetSystemTableDefinitions() {
		if _, ok := approvalTables[def.TableName]; !ok || strings.TrimSpace(def.KeyPrefix) == "" {
			continue
		}
		updates[def.TableName] = def.KeyPrefix
	}
	for apiName, keyPrefix := range updates {
		if _, err := db.ExecContext(ctx, `UPDATE _System_Object SET KeyPrefix = ? WHERE ApiName = ?`, keyPrefix, apiName); err != nil {
			return err
		}
	}
	return nil
}

func installActiveApprovalProcess(t *testing.T, ctx context.Context, conn *database.TiDBConnection, objectName, entryFilter string) string {
	t.Helper()

	developerName := fmt.Sprintf("Issue351_%d", time.Now().UnixNano())
	processID := fmt.Sprintf("pd-%d", time.Now().UnixNano())
	if _, err := conn.DB().ExecContext(ctx, `
		INSERT INTO ProcessDefinition
			(Id, Name, DeveloperName, State, TableEnumOrId, EntryFilter, IsActive, Metadata, ProcessOrder, CreatedDate, LastModifiedDate)
		VALUES (?, ?, ?, 'Active', ?, ?, 1, '{}', 1, NOW(), NOW())`,
		processID,
		developerName,
		developerName,
		objectName,
		entryFilter,
	); err != nil {
		t.Fatalf("insert ProcessDefinition via SQL: %v", err)
	}
	t.Cleanup(func() {
		_, _ = conn.DB().ExecContext(ctx, `UPDATE ProcessDefinition SET State = 'Inactive', IsActive = 0, LastModifiedDate = NOW() WHERE Id = ?`, processID)
	})
	return processID
}

func insertLeadForApproval(t *testing.T, ctx context.Context, sm *services.ServiceManager, status string) string {
	t.Helper()

	record, err := sm.Persistence.Insert(ctx, "Lead", models.SObject{
		"FirstName": "Issue351",
		"LastName":  fmt.Sprintf("Submit-%d", time.Now().UnixNano()),
		"Company":   fmt.Sprintf("Issue351 Co %d", time.Now().UnixNano()),
		"Status":    status,
	}, systemAdminSession())
	if err != nil {
		t.Fatalf("insert Lead: %v", err)
	}
	leadID, _ := record[constants.FieldID].(string)
	if leadID == "" {
		t.Fatal("insert Lead returned empty Id")
	}
	return leadID
}

func fakeLeadID(t *testing.T, ctx context.Context, sm *services.ServiceManager) string {
	t.Helper()

	prefix := mustObjectKeyPrefix(t, ctx, sm, "Lead")
	return prefix + fmt.Sprintf("issue351%09d", time.Now().UnixNano()%1000000000)
}

func assertApprovalRowsCanonicalOnly(t *testing.T, db *sql.DB, canonicalID, rawID string) {
	t.Helper()

	var canonicalCount int
	if err := db.QueryRow("SELECT COUNT(*) FROM `ProcessInstance` WHERE `TargetObjectId` = ?", canonicalID).Scan(&canonicalCount); err != nil {
		t.Fatalf("count canonical ProcessInstance rows: %v", err)
	}
	if canonicalCount == 0 {
		t.Fatalf("canonical ProcessInstance rows = %d, want >= 1 for %q", canonicalCount, canonicalID)
	}

	var rawCount int
	if err := db.QueryRow("SELECT COUNT(*) FROM `ProcessInstance` WHERE `TargetObjectId` = ?", rawID).Scan(&rawCount); err != nil {
		t.Fatalf("count raw-id ProcessInstance rows: %v", err)
	}
	if rawCount != 0 {
		t.Fatalf("raw-id ProcessInstance rows = %d, want 0 for %q", rawCount, rawID)
	}
}

func assertNoApprovalRowsForTarget(t *testing.T, db *sql.DB, targetID string) {
	t.Helper()

	var count int
	if err := db.QueryRow("SELECT COUNT(*) FROM `ProcessInstance` WHERE `TargetObjectId` = ?", targetID).Scan(&count); err != nil {
		t.Fatalf("count ProcessInstance rows: %v", err)
	}
	if count != 0 {
		t.Fatalf("ProcessInstance rows for %q = %d, want 0", targetID, count)
	}
}