//go:build integration
// +build integration

package automation

// Integration test for the Wait element / PausedUntil schema migration.
//
// Run with:
//   TIDB_HOST=gateway01.us-east-1.prod.aws.tidbcloud.com \
//   TIDB_PORT=4000 \
//   TIDB_USER=4Qbn3F3nyaU9DLk.root \
//   TIDB_PASSWORD=$WAIT_INTEGRATION_DB_PASSWORD \
//   TIDB_DATABASE=nexuscrm \
//   go test -tags=integration -run Integration ./internal/features/automation/...
//
// The test:
//  1. Verifies that the FlowInterview table has the new PausedUntil column
//     and the idx_flow_interview_paused_scan composite index.
//  2. Round-trips a paused interview row: INSERT a Paused row with
//     PausedUntil in the past, confirm the scheduler query finds it,
//     then UPDATE Running + PausedUntil=NULL and confirm the query no
//     longer matches.
//
// We run this behind a build tag so `go test ./...` in CI does not
// require the live DB. The /review gate runs it explicitly.

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

	_ "github.com/go-sql-driver/mysql"
	"github.com/stretchr/testify/require"
)

func openWaitIntegrationDB(t *testing.T) *sql.DB {
	host := os.Getenv("TIDB_HOST")
	if host == "" {
		t.Skip("TIDB_HOST not set; skipping integration test")
	}
	user := os.Getenv("TIDB_USER")
	pass := os.Getenv("TIDB_PASSWORD")
	port := os.Getenv("TIDB_PORT")
	if port == "" {
		port = "4000"
	}
	db := os.Getenv("TIDB_DATABASE")
	if db == "" {
		db = "nexuscrm"
	}

	tls := ""
	if host != "127.0.0.1" && host != "localhost" {
		tls = "?tls=true"
	}
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s%s", user, pass, host, port, db, tls)
	handle, err := sql.Open("mysql", dsn)
	require.NoError(t, err)
	handle.SetConnMaxLifetime(30 * time.Second)
	require.NoError(t, handle.PingContext(context.Background()))
	return handle
}

// TestIntegration_FlowInterviewSchema_HasPausedUntilAndIndex asserts the
// schema migration executed by `cmd/setup apply` added the PausedUntil
// column and the composite (InterviewStatus, PausedUntil) index.
func TestIntegration_FlowInterviewSchema_HasPausedUntilAndIndex(t *testing.T) {
	db := openWaitIntegrationDB(t)
	defer db.Close()

	// Column exists with TIMESTAMP NULL. Scope by TABLE_SCHEMA =
	// DATABASE() — a shared TiDB/MySQL host can hold multiple schemas
	// with the same table name, and an unscoped INFORMATION_SCHEMA
	// query would let the test pass by reading another schema's row.
	var name, isNullable, colType string
	row := db.QueryRow(`
		SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_SCHEMA = DATABASE()
		  AND TABLE_NAME = 'FlowInterview'
		  AND COLUMN_NAME = 'PausedUntil'
	`)
	err := row.Scan(&name, &isNullable, &colType)
	require.NoError(t, err, "FlowInterview.PausedUntil column must exist — ran setup apply?")
	require.Equal(t, "PausedUntil", name)
	require.Equal(t, "YES", isNullable)
	require.Equal(t, "timestamp", colType)

	// Composite index exists and leads with (InterviewStatus, PausedUntil).
	// TiDB / MySQL versions vary in how many columns SHOW INDEX emits (15–17),
	// so we parse INFORMATION_SCHEMA.STATISTICS instead, which is stable.
	// Same TABLE_SCHEMA scoping for the same reason as above.
	idxRows, err := db.Query(`
		SELECT COLUMN_NAME
		FROM INFORMATION_SCHEMA.STATISTICS
		WHERE TABLE_SCHEMA = DATABASE()
		  AND TABLE_NAME = 'FlowInterview'
		  AND INDEX_NAME = 'idx_flow_interview_paused_scan'
		ORDER BY SEQ_IN_INDEX
	`)
	require.NoError(t, err)
	defer idxRows.Close()

	var cols []string
	for idxRows.Next() {
		var col string
		require.NoError(t, idxRows.Scan(&col))
		cols = append(cols, col)
	}
	require.NoError(t, idxRows.Err())
	require.GreaterOrEqual(t, len(cols), 2, "index idx_flow_interview_paused_scan must exist")
	require.Equal(t, "InterviewStatus", cols[0])
	require.Equal(t, "PausedUntil", cols[1])
}

// TestIntegration_PausedScanQuery_MatchesAndClears exercises the exact
// query shape the scheduler uses. We insert a synthetic Paused row with
// PausedUntil in the past, confirm it shows up, then flip to Running
// with PausedUntil=NULL and confirm it disappears.
func TestIntegration_PausedScanQuery_MatchesAndClears(t *testing.T) {
	db := openWaitIntegrationDB(t)
	defer db.Close()

	ctx := context.Background()
	id := fmt.Sprintf("test-wait-%d", time.Now().UnixNano())
	flowID := fmt.Sprintf("test-ver-%d", time.Now().UnixNano())
	defID := fmt.Sprintf("test-def-%d", time.Now().UnixNano())
	past := time.Now().Add(-time.Hour).UTC().Format("2006-01-02 15:04:05")

	// Clean up any previous run, and prepare FK parent rows.
	_, _ = db.ExecContext(ctx, "DELETE FROM FlowInterview WHERE Id = ?", id)
	_, _ = db.ExecContext(ctx, "DELETE FROM Flow WHERE Id = ?", flowID)
	_, _ = db.ExecContext(ctx, "DELETE FROM FlowDefinition WHERE Id = ?", defID)

	// Insert FlowDefinition + Flow (parent rows for FlowInterview.FlowVersionId FK).
	_, err := db.ExecContext(ctx, `
		INSERT INTO FlowDefinition (Id, DeveloperName, MasterLabel, ProcessType)
		VALUES (?, ?, 'WaitIntegration', 'AutoLaunchedFlow')
	`, defID, "WaitIntegrationTest_"+defID)
	require.NoError(t, err)
	defer db.ExecContext(ctx, "DELETE FROM FlowDefinition WHERE Id = ?", defID)

	_, err = db.ExecContext(ctx, `
		INSERT INTO Flow (Id, DefinitionId, MasterLabel, VersionNumber, Status, ProcessType, Metadata)
		VALUES (?, ?, 'WaitIntegration v1', 1, 'Active', 'AutoLaunchedFlow', '{}')
	`, flowID, defID)
	require.NoError(t, err)
	defer db.ExecContext(ctx, "DELETE FROM Flow WHERE Id = ?", flowID)

	// Insert a Paused row with PausedUntil in the past.
	_, err = db.ExecContext(ctx, `
		INSERT INTO FlowInterview (Id, FlowVersionId, InterviewLabel, InterviewStatus, CurrentElement, PausedUntil)
		VALUES (?, ?, 'wait-integration', 'Paused', 'Wait_Test', ?)
	`, id, flowID, past)
	require.NoError(t, err)
	defer db.ExecContext(ctx, "DELETE FROM FlowInterview WHERE Id = ?", id)

	// Scheduler's due-scan query must see it.
	var count int
	err = db.QueryRowContext(ctx, `
		SELECT COUNT(*) FROM FlowInterview
		WHERE InterviewStatus = 'Paused' AND PausedUntil <= NOW() AND Id = ?
	`, id).Scan(&count)
	require.NoError(t, err)
	require.Equal(t, 1, count, "due paused row must match scheduler query")

	// Flip to Running + NULL (ResumeRunning semantics).
	_, err = db.ExecContext(ctx, `
		UPDATE FlowInterview SET InterviewStatus = 'Running', PausedUntil = NULL WHERE Id = ?
	`, id)
	require.NoError(t, err)

	// Must no longer be picked up by the scheduler.
	err = db.QueryRowContext(ctx, `
		SELECT COUNT(*) FROM FlowInterview
		WHERE InterviewStatus = 'Paused' AND PausedUntil <= NOW() AND Id = ?
	`, id).Scan(&count)
	require.NoError(t, err)
	require.Equal(t, 0, count, "row must no longer be due after ResumeRunning")
}