package automationintegration_test

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

	_ "github.com/go-sql-driver/mysql"
	automation "github.com/nexuscrm/automation"
	"github.com/nexuscrm/backend/internal/bootstrap"
	"github.com/pingcap-inc/nexuscrm-controlplane"
)

// openTiDB provisions a disposable TiDB schema for the relocated scheduled-run
// reservation integration tests. It mirrors the helper that lives in the
// features/automation package's own integration tests; that copy stays behind
// because those tests remain in-package, while these were relocated here to
// keep the automation package's *_test.go files free of backend/internal.
func openTiDB(t *testing.T) (*sql.DB, func()) {
	t.Helper()
	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"
	}
	pwd := os.Getenv("TIDB_PASSWORD")

	adminDSN := fmt.Sprintf("%s:%s@tcp(%s:%s)/?parseTime=true&loc=Local", user, pwd, 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_scheduled_run_reservation_%d", time.Now().UnixNano())
	if _, err := adminDB.Exec("CREATE DATABASE `" + dbName + "`"); err != nil {
		_ = adminDB.Close()
		t.Skipf("skipping integration test: cannot create temporary database: %v", err)
	}

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

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

func createScheduledRunReservationTable(t *testing.T, db *sql.DB) {
	t.Helper()
	_, err := db.Exec(`CREATE TABLE _System_ScheduledRunReservation (
		Id VARCHAR(255) PRIMARY KEY,
		OccurrenceIdentityDigest CHAR(64) COLLATE utf8mb4_bin NULL,
		FlowDefinitionId VARCHAR(191) NOT NULL,
		FlowVersionId VARCHAR(191) NOT NULL,
		ScheduleWindowKey VARCHAR(191) NOT NULL,
		WindowToken VARCHAR(191) NOT NULL,
		InputKey VARCHAR(191) NOT NULL,
		State VARCHAR(32) NOT NULL DEFAULT 'reserved',
		Status VARCHAR(32) NOT NULL,
		ReservedAt DATETIME NOT NULL,
		CompletedAt DATETIME NULL,
		LastError LONGTEXT NULL,
		CreatedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		LastModifiedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		UNIQUE KEY uq_scheduled_run_reservation_identity (OccurrenceIdentityDigest)
	)`)
	if err != nil {
		t.Fatalf("create _System_ScheduledRunReservation: %v", err)
	}
}

func createLegacyScheduledRunReservationTable(t *testing.T, db *sql.DB) {
	t.Helper()
	_, err := db.Exec(`CREATE TABLE _System_ScheduledRunReservation (
		Id VARCHAR(255) PRIMARY KEY,
		FlowDefinitionId VARCHAR(18) NOT NULL,
		FlowVersionId VARCHAR(18) NOT NULL,
		ScheduleWindowKey VARCHAR(128) NOT NULL,
		InputKey VARCHAR(128) NOT NULL,
		State VARCHAR(64) NOT NULL DEFAULT 'RESERVED',
		CompletedAt DATETIME NULL,
		CreatedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		LastModifiedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		UNIQUE KEY uk__System_ScheduledRunReservation_identity (FlowDefinitionId, FlowVersionId, ScheduleWindowKey, InputKey),
		KEY idx__System_ScheduledRunReservation_state (State)
	)`)
	if err != nil {
		t.Fatalf("create legacy _System_ScheduledRunReservation: %v", err)
	}
}

func createMigrationStampTable(t *testing.T, db *sql.DB) {
	t.Helper()
	_, err := db.Exec(`CREATE TABLE _System_Migration (
		Id VARCHAR(255) PRIMARY KEY,
		Version INT NOT NULL,
		Description LONGTEXT NOT NULL,
		AppliedAt DATETIME NOT NULL,
		CreatedDate DATETIME NOT NULL,
		LastModifiedDate DATETIME NOT NULL
	)`)
	if err != nil {
		t.Fatalf("create _System_Migration: %v", err)
	}
}

func countScheduledRunReservationRows(t *testing.T, db *sql.DB) int {
	t.Helper()
	var n int
	if err := db.QueryRow(`SELECT COUNT(*) FROM _System_ScheduledRunReservation`).Scan(&n); err != nil {
		t.Fatalf("count reservation rows: %v", err)
	}
	return n
}

func reservationRowStatus(t *testing.T, db *sql.DB, key automation.ScheduledRunReservationKey) (string, bool) {
	t.Helper()
	var status string
	err := db.QueryRow(
		`SELECT Status FROM _System_ScheduledRunReservation
		  WHERE FlowDefinitionId = ? AND FlowVersionId = ? AND WindowToken = ? AND InputKey = ?`,
		key.FlowDefinitionID, key.FlowVersionID, key.WindowToken, key.InputKey,
	).Scan(&status)
	if err == sql.ErrNoRows {
		return "", false
	}
	if err != nil {
		t.Fatalf("query reservation row: %v", err)
	}
	return status, true
}

func reservationRowTerminalState(t *testing.T, db *sql.DB, key automation.ScheduledRunReservationKey) (status string, lastError sql.NullString, ok bool) {
	t.Helper()
	err := db.QueryRow(
		`SELECT Status, LastError FROM _System_ScheduledRunReservation
		  WHERE FlowDefinitionId = ? AND FlowVersionId = ? AND WindowToken = ? AND InputKey = ?`,
		key.FlowDefinitionID, key.FlowVersionID, key.WindowToken, key.InputKey,
	).Scan(&status, &lastError)
	if err == sql.ErrNoRows {
		return "", sql.NullString{}, false
	}
	if err != nil {
		t.Fatalf("query reservation terminal state: %v", err)
	}
	return status, lastError, true
}

func reservationCompatibilityRow(t *testing.T, db *sql.DB, id string) (scheduleWindowKey, windowToken, status string, reservedAt, createdDate time.Time) {
	t.Helper()
	if err := db.QueryRow(
		`SELECT ScheduleWindowKey, WindowToken, Status, ReservedAt, CreatedDate
		  FROM _System_ScheduledRunReservation
		  WHERE Id = ?`,
		id,
	).Scan(&scheduleWindowKey, &windowToken, &status, &reservedAt, &createdDate); err != nil {
		t.Fatalf("query reservation compatibility row: %v", err)
	}
	return scheduleWindowKey, windowToken, status, reservedAt, createdDate
}

func TestScheduledRunReservationService_ConcurrentReserveSingleWinner(t *testing.T) {
	db, cleanup := openTiDB(t)
	defer cleanup()
	createScheduledRunReservationTable(t, db)

	svc := automation.NewScheduledRunReservationService(db)
	key, err := automation.NewScheduledRunReservationKey("def-1", "ver-1", "2026-05-13T10:00Z", "rec-1")
	if err != nil {
		t.Fatalf("automation.NewScheduledRunReservationKey: %v", err)
	}

	type outcome struct {
		result automation.ScheduledRunReserveResult
		err    error
	}
	start := make(chan struct{})
	results := make(chan outcome, 2)
	var wg sync.WaitGroup
	for i := 0; i < 2; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			<-start
			res, reserveErr := svc.Reserve(context.Background(), key)
			results <- outcome{result: res, err: reserveErr}
		}()
	}
	close(start)
	wg.Wait()
	close(results)

	var reserved, duplicate int
	for got := range results {
		if got.err != nil {
			t.Fatalf("Reserve err = %v", got.err)
		}
		switch got.result.Status {
		case automation.ScheduledRunReserveStatusReserved:
			reserved++
		case automation.ScheduledRunReserveStatusDuplicate:
			duplicate++
		default:
			t.Fatalf("unexpected reserve status %q", got.result.Status)
		}
	}
	if reserved != 1 || duplicate != 1 {
		t.Fatalf("reserve outcomes = reserved:%d duplicate:%d; want 1/1", reserved, duplicate)
	}
	if got := countScheduledRunReservationRows(t, db); got != 1 {
		t.Fatalf("reservation row count = %d; want 1", got)
	}
}

func TestScheduledRunReservationService_NewWindowAllowsReserve(t *testing.T) {
	db, cleanup := openTiDB(t)
	defer cleanup()
	createScheduledRunReservationTable(t, db)

	svc := automation.NewScheduledRunReservationService(db)
	windowA, err := automation.NewScheduledRunReservationKey("def-1", "ver-1", "2026-05-13T10:00Z", "rec-1")
	if err != nil {
		t.Fatalf("windowA key err = %v", err)
	}
	windowB, err := automation.NewScheduledRunReservationKey("def-1", "ver-1", "2026-05-13T11:00Z", "rec-1")
	if err != nil {
		t.Fatalf("windowB key err = %v", err)
	}

	first, err := svc.Reserve(context.Background(), windowA)
	if err != nil {
		t.Fatalf("first reserve err = %v", err)
	}
	if first.Status != automation.ScheduledRunReserveStatusReserved {
		t.Fatalf("first reserve status = %q, want %q", first.Status, automation.ScheduledRunReserveStatusReserved)
	}
	dup, err := svc.Reserve(context.Background(), windowA)
	if err != nil {
		t.Fatalf("duplicate reserve err = %v", err)
	}
	if dup.Status != automation.ScheduledRunReserveStatusDuplicate {
		t.Fatalf("duplicate reserve status = %q, want %q", dup.Status, automation.ScheduledRunReserveStatusDuplicate)
	}
	second, err := svc.Reserve(context.Background(), windowB)
	if err != nil {
		t.Fatalf("new-window reserve err = %v", err)
	}
	if second.Status != automation.ScheduledRunReserveStatusReserved {
		t.Fatalf("new-window reserve status = %q, want %q", second.Status, automation.ScheduledRunReserveStatusReserved)
	}
	if got := countScheduledRunReservationRows(t, db); got != 2 {
		t.Fatalf("reservation row count = %d; want 2", got)
	}
}

func TestScheduledRunReservationService_CompleteUpdatesWinnerOnly(t *testing.T) {
	db, cleanup := openTiDB(t)
	defer cleanup()
	createScheduledRunReservationTable(t, db)

	svc := automation.NewScheduledRunReservationService(db)
	key, err := automation.NewScheduledRunReservationKey("def-1", "ver-1", "2026-05-13T10:00Z", "rec-1")
	if err != nil {
		t.Fatalf("key err = %v", err)
	}

	first, err := svc.Reserve(context.Background(), key)
	if err != nil {
		t.Fatalf("first reserve err = %v", err)
	}
	if first.Status != automation.ScheduledRunReserveStatusReserved {
		t.Fatalf("first reserve status = %q, want %q", first.Status, automation.ScheduledRunReserveStatusReserved)
	}
	if err := svc.Complete(context.Background(), first.ReservationID, key, automation.ScheduledRunExecutionStatusCompleted, ""); err != nil {
		t.Fatalf("Complete err = %v", err)
	}
	status, ok := reservationRowStatus(t, db, key)
	if !ok {
		t.Fatal("reservation row missing after Complete")
	}
	if status != string(automation.ScheduledRunExecutionStatusCompleted) {
		t.Fatalf("row status = %q, want %q", status, automation.ScheduledRunExecutionStatusCompleted)
	}

	dup, err := svc.Reserve(context.Background(), key)
	if err != nil {
		t.Fatalf("duplicate reserve after complete err = %v", err)
	}
	if dup.Status != automation.ScheduledRunReserveStatusDuplicate {
		t.Fatalf("duplicate reserve after complete status = %q, want %q", dup.Status, automation.ScheduledRunReserveStatusDuplicate)
	}
	if got := countScheduledRunReservationRows(t, db); got != 1 {
		t.Fatalf("reservation row count = %d; want 1", got)
	}
}

func TestScheduledRunReservationService_CompleteRejectsWrongReservationID(t *testing.T) {
	db, cleanup := openTiDB(t)
	defer cleanup()
	createScheduledRunReservationTable(t, db)

	svc := automation.NewScheduledRunReservationService(db)
	key, err := automation.NewScheduledRunReservationKey("def-1", "ver-1", "2026-05-13T10:00Z", "rec-1")
	if err != nil {
		t.Fatalf("key err = %v", err)
	}

	first, err := svc.Reserve(context.Background(), key)
	if err != nil {
		t.Fatalf("first reserve err = %v", err)
	}
	if first.Status != automation.ScheduledRunReserveStatusReserved {
		t.Fatalf("first reserve status = %q, want %q", first.Status, automation.ScheduledRunReserveStatusReserved)
	}
	if err := svc.Complete(context.Background(), "wrong-id", key, automation.ScheduledRunExecutionStatusCompleted, ""); err == nil {
		t.Fatal("Complete with wrong reservation id = nil, want error")
	}
	status, ok := reservationRowStatus(t, db, key)
	if !ok {
		t.Fatal("reservation row missing after wrong-id Complete")
	}
	if status != string(automation.ScheduledRunExecutionStatusReserved) {
		t.Fatalf("row status after wrong-id Complete = %q, want %q", status, automation.ScheduledRunExecutionStatusReserved)
	}
}
func TestScheduledRunReservationService_CompleteRejectsSecondTerminalWrite(t *testing.T) {
	db, cleanup := openTiDB(t)
	defer cleanup()
	createScheduledRunReservationTable(t, db)

	svc := automation.NewScheduledRunReservationService(db)
	key, err := automation.NewScheduledRunReservationKey("def-1", "ver-1", "2026-05-13T10:00Z", "rec-1")
	if err != nil {
		t.Fatalf("key err = %v", err)
	}

	first, err := svc.Reserve(context.Background(), key)
	if err != nil {
		t.Fatalf("first reserve err = %v", err)
	}
	if first.Status != automation.ScheduledRunReserveStatusReserved {
		t.Fatalf("first reserve status = %q, want %q", first.Status, automation.ScheduledRunReserveStatusReserved)
	}
	if err := svc.Complete(context.Background(), first.ReservationID, key, automation.ScheduledRunExecutionStatusCompleted, ""); err != nil {
		t.Fatalf("first Complete err = %v", err)
	}

	if err := svc.Complete(context.Background(), first.ReservationID, key, automation.ScheduledRunExecutionStatusFailed, "boom"); err == nil {
		t.Fatal("second Complete on terminal reservation = nil, want error")
	}

	status, lastError, ok := reservationRowTerminalState(t, db, key)
	if !ok {
		t.Fatal("reservation row missing after second Complete attempt")
	}
	if status != string(automation.ScheduledRunExecutionStatusCompleted) {
		t.Fatalf("row status after second Complete = %q, want %q", status, automation.ScheduledRunExecutionStatusCompleted)
	}
	if lastError.Valid {
		t.Fatalf("row last error after second Complete = %q, want NULL", lastError.String)
	}
}

func TestScheduledRunReservationService_WarmUpgradeBackfillsLegacyRowsBeforeDuplicateCheck(t *testing.T) {
	db, cleanup := openTiDB(t)
	defer cleanup()
	createLegacyScheduledRunReservationTable(t, db)
	createMigrationStampTable(t, db)

	legacyCreatedAt := time.Date(2026, 5, 13, 10, 0, 0, 0, time.UTC)
	if _, err := db.Exec(
		`INSERT INTO _System_ScheduledRunReservation
			(Id, FlowDefinitionId, FlowVersionId, ScheduleWindowKey, InputKey, State, CompletedAt, CreatedDate, LastModifiedDate)
		  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		"legacy-row",
		"def-1",
		"ver-1",
		"2026-05-13T10:00Z",
		"rec-1",
		"COMPLETED",
		legacyCreatedAt.Add(5*time.Minute),
		legacyCreatedAt,
		legacyCreatedAt.Add(5*time.Minute),
	); err != nil {
		t.Fatalf("seed legacy reservation row: %v", err)
	}

	if err := bootstrap.ReconcileTableColumns(db, bootstrap.GetManagedTableDefinitions()); err != nil {
		t.Fatalf("ReconcileTableColumns: %v", err)
	}
	touched, err := controlplane.BackfillScheduledRunReservationCompatibility(context.Background(), db)
	if err != nil {
		t.Fatalf("BackfillScheduledRunReservationCompatibility: %v", err)
	}
	if touched != 1 {
		t.Fatalf("BackfillScheduledRunReservationCompatibility touched = %d, want 1", touched)
	}

	scheduleWindowKey, windowToken, status, reservedAt, createdDate := reservationCompatibilityRow(t, db, "legacy-row")
	if scheduleWindowKey != "2026-05-13T10:00Z" {
		t.Fatalf("legacy ScheduleWindowKey = %q, want original window", scheduleWindowKey)
	}
	if windowToken != scheduleWindowKey {
		t.Fatalf("legacy WindowToken = %q, want %q", windowToken, scheduleWindowKey)
	}
	if status != string(automation.ScheduledRunExecutionStatusCompleted) {
		t.Fatalf("legacy Status = %q, want %q", status, automation.ScheduledRunExecutionStatusCompleted)
	}
	if !reservedAt.Equal(createdDate) {
		t.Fatalf("legacy ReservedAt = %s, want CreatedDate %s", reservedAt.UTC().Format(time.RFC3339), createdDate.UTC().Format(time.RFC3339))
	}

	svc := automation.NewScheduledRunReservationService(db)
	sameWindow, err := automation.NewScheduledRunReservationKey("def-1", "ver-1", "2026-05-13T10:00Z", "rec-1")
	if err != nil {
		t.Fatalf("sameWindow key err = %v", err)
	}
	dup, err := svc.Reserve(context.Background(), sameWindow)
	if err != nil {
		t.Fatalf("same-window reserve after upgrade err = %v", err)
	}
	if dup.Status != automation.ScheduledRunReserveStatusDuplicate {
		t.Fatalf("same-window reserve after upgrade status = %q, want %q", dup.Status, automation.ScheduledRunReserveStatusDuplicate)
	}

	newWindow, err := automation.NewScheduledRunReservationKey("def-1", "ver-1", "2026-05-13T11:00Z", "rec-1")
	if err != nil {
		t.Fatalf("newWindow key err = %v", err)
	}
	reserved, err := svc.Reserve(context.Background(), newWindow)
	if err != nil {
		t.Fatalf("new-window reserve after upgrade err = %v", err)
	}
	if reserved.Status != automation.ScheduledRunReserveStatusReserved {
		t.Fatalf("new-window reserve after upgrade status = %q, want %q", reserved.Status, automation.ScheduledRunReserveStatusReserved)
	}
	if got := countScheduledRunReservationRows(t, db); got != 2 {
		t.Fatalf("reservation row count after upgrade flow = %d, want 2", got)
	}
}