package bootstrap

import (
	"encoding/json"
	"os"
	"path/filepath"
	"strconv"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
)

// TestPackTableDescriptors_MatchSystemTablesJSON is the Phase-1B drift
// guard. Each PackTable declared in pack_reconcile_tables.go must key by
// the EXACT columns that system_tables.json declares in the corresponding
// `uq_*` unique index. If a DDL refactor reorders or renames a unique-key
// column without updating the descriptor, this test fails at CI — same
// shape as the Phase-1A `actionInputGuardID` index-keyed bug, caught
// earlier. Prevents the reconcile pass from silently mis-keying.
//
// The drift-guard reads system_tables.json at test time (not via a generated
// constants table) so changes to the DDL that forget to regenerate
// constants still trip the guard.
func TestPackTableDescriptors_MatchSystemTablesJSON(t *testing.T) {
	path := filepath.Join("system_tables.json")
	raw, err := os.ReadFile(path)
	require.NoError(t, err, "must be able to read system_tables.json from package dir")

	var tables []struct {
		TableName string `json:"tableName"`
		Indices   []struct {
			Name    string   `json:"name"`
			Unique  bool     `json:"unique"`
			Columns []string `json:"columns"`
		} `json:"indices"`
	}
	require.NoError(t, json.Unmarshal(raw, &tables))

	// Build a (tableName → uq-index columns) map. Only unique indices count.
	uqByTable := make(map[string][]string, len(tables))
	for _, t := range tables {
		for _, idx := range t.Indices {
			if idx.Unique {
				uqByTable[t.TableName] = idx.Columns
				break // descriptors expect exactly one uq_* per satellite
			}
		}
	}

	cases := []struct {
		label  string
		table  PackTable
		wantUq []string
	}{
		{"_System_StandardAction", packTableStandardAction, uqByTable[tableStandardAction]},
		{"_System_ActionSchema", packTableActionSchema, uqByTable[tableActionSchema]},
		{"_System_ActionFieldDefault", packTableActionFieldDefault, uqByTable[tableActionFieldDefault]},
		{"_System_ActionInputGuard", packTableActionInputGuard, uqByTable[tableActionInputGuard]},
		{"_System_StandardActionInternalAccessScope", packTableStandardActionInternalAccessScope, uqByTable[tableStandardActionInternalAccessScope]},
	}
	for _, c := range cases {
		t.Run(c.label, func(t *testing.T) {
			require.NotEmpty(t, c.wantUq,
				"system_tables.json must declare a unique index on %s (the reconcile pass relies on it to pick the right `keep` key)",
				c.label)
			assert.Equal(t, c.wantUq, c.table.KeyColumns,
				"PackTable.KeyColumns for %s drifted from the `uq_*` index declared in system_tables.json — update pack_reconcile_tables.go AND the Id-derivation function in the same commit",
				c.label)
		})
	}
}

func TestActionInputGuardUniqueIndexFitsTiDBKeyLimit(t *testing.T) {
	path := filepath.Join("system_tables.json")
	raw, err := os.ReadFile(path)
	require.NoError(t, err, "must be able to read system_tables.json from package dir")

	var tables []struct {
		TableName string `json:"tableName"`
		Columns   []struct {
			Name string `json:"name"`
			Type string `json:"type"`
		} `json:"columns"`
		Indices []struct {
			Name    string   `json:"name"`
			Unique  bool     `json:"unique"`
			Columns []string `json:"columns"`
		} `json:"indices"`
	}
	require.NoError(t, json.Unmarshal(raw, &tables))

	const (
		tableName   = "_System_ActionInputGuard"
		indexName   = "uq_aig_rule"
		maxKeyBytes = 3072
		utf8mb4Byte = 4
	)

	for _, table := range tables {
		if table.TableName != tableName {
			continue
		}
		columnTypes := make(map[string]string, len(table.Columns))
		for _, column := range table.Columns {
			columnTypes[column.Name] = column.Type
		}
		for _, idx := range table.Indices {
			if idx.Name != indexName {
				continue
			}
			require.True(t, idx.Unique, "%s.%s must stay unique for metadata pack reconciliation", tableName, indexName)
			keyBytes := 0
			for _, column := range idx.Columns {
				keyBytes += varcharCharLength(t, columnTypes[column]) * utf8mb4Byte
			}
			require.LessOrEqual(t, keyBytes, maxKeyBytes,
				"%s.%s exceeds TiDB/MySQL max key length under utf8mb4", tableName, indexName)
			return
		}
		t.Fatalf("%s missing %s unique index", tableName, indexName)
	}
	t.Fatalf("%s missing from system_tables.json", tableName)
}

func varcharCharLength(t *testing.T, typ string) int {
	t.Helper()
	upper := strings.ToUpper(strings.TrimSpace(typ))
	require.True(t, strings.HasPrefix(upper, "VARCHAR("), "expected VARCHAR type, got %q", typ)
	end := strings.IndexByte(upper, ')')
	require.NotEqual(t, -1, end, "expected VARCHAR(n) type, got %q", typ)
	n, err := strconv.Atoi(upper[len("VARCHAR("):end])
	require.NoError(t, err, "expected VARCHAR(n) type, got %q", typ)
	return n
}

// TestInitializeStandardActions_PackHashFastPath pins the warm-install
// fast-path: when the last successful seed's sha256 matches the shipped
// pack, the seeder SHORT-CIRCUITS after one SELECT on _System_BootstrapState
// — no upserts, no reconcile.
//
// Without this gate, every boot re-runs ~3N queries per action even when
// nothing changed.
func TestInitializeStandardActions_PackHashFastPath(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	// Seed a minimal pack + stamp the hash as "applied" so the fast-path
	// fires on the second boot. We use the real standard_actions_pack key
	// and the real seeded payload hash so the equality check inside
	// isPackHashApplied passes.
	original := standardActionsJSON
	standardActionsJSON = []byte(`[{"name":"x","label":"l","flowDeveloperName":"f"}]`)
	defer func() { standardActionsJSON = original }()

	wantHash := standardActionsPackHash()

	mock.ExpectQuery(`SELECT .Phase., .Status. FROM .*BootstrapState`).
		WithArgs("standard_actions_pack").
		WillReturnRows(sqlmock.NewRows([]string{"Phase", "Status"}).AddRow(wantHash, "applied"))

	// NO upsert, NO reconcile, NO stamp. If the fast-path bug-regresses
	// and anything else runs, sqlmock fails the test with an unexpected-call
	// error.

	require.NoError(t, InitializeStandardActions(db))
	require.NoError(t, mock.ExpectationsWereMet())
}

// TestInitializeStandardActions_PackHashMismatchTriggersFullRun pins the
// complement of the fast-path: a stale hash forces the full seed + reconcile
// pass, and the new hash gets stamped on success. Catches a regression where
// the gate is checked but the comparison is off (e.g. comparing against a
// constant or caching wrong).
func TestInitializeStandardActions_PackHashMismatchTriggersFullRun(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	original := standardActionsJSON
	standardActionsJSON = []byte(`[{"name":"x","label":"l","flowDeveloperName":"f"}]`)
	defer func() { standardActionsJSON = original }()

	// Gate row exists but with a DIFFERENT hash → full seed must run.
	mock.ExpectQuery(`SELECT .Phase., .Status. FROM .*BootstrapState`).
		WithArgs("standard_actions_pack").
		WillReturnRows(sqlmock.NewRows([]string{"Phase", "Status"}).AddRow("stalehash", "applied"))

	// Parent upsert + 4 satellite reconcile SELECTs (no satellite pack rows).
	mock.ExpectExec(`INSERT INTO _System_StandardAction`).
		WillReturnResult(sqlmock.NewResult(1, 1))
	expectPersistedStandardActionID(mock, "x", "sa_x")
	mock.ExpectQuery(`SELECT Id FROM .*ActionSchema`).
		WithArgs("sa_x").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*ActionFieldDefault`).
		WithArgs("sa_x").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*ActionInputGuard`).
		WithArgs("sa_x").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*StandardActionInternalAccessScope`).
		WithArgs("sa_x").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	// Parent reconcile: SELECT persisted Id for the pack's action Name, then
	// SELECT live non-customized rows globally (empty result here means no
	// orphans to deactivate).
	mock.ExpectQuery(`SELECT Id FROM .*_System_StandardAction.* WHERE Name IN \(\?\) AND IsDeleted = 0`).
		WithArgs("x").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow("sa_x"))
	mock.ExpectQuery(`SELECT Id FROM .*_System_StandardAction.* WHERE IsCustomized = 0 AND IsDeleted = 0`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow("sa_x"))
	// Fresh hash gets stamped on success — this is the critical invariant.
	mock.ExpectExec(`INSERT INTO .*BootstrapState.*ON DUPLICATE KEY UPDATE`).
		WillReturnResult(sqlmock.NewResult(1, 1))

	require.NoError(t, InitializeStandardActions(db))
	require.NoError(t, mock.ExpectationsWereMet())
}

// TestInitializeStandardActions_ParentOrphanDeactivated pins the new
// _System_StandardAction parent reconcile: an action present in a prior
// pack but absent from the current pack must be soft-deleted (IsActive=0,
// IsDeleted=1) so the dispatcher stops discovering and dispatching it.
//
// This is the P2 gap that led to orphaned-action routing: before the fix,
// InitializeStandardActions only iterated current defs and never pruned
// the parent table.
func TestInitializeStandardActions_ParentOrphanDeactivated(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	original := standardActionsJSON
	standardActionsJSON = []byte(`[{"name":"keep","label":"Keep","flowDeveloperName":"f"}]`)
	defer func() { standardActionsJSON = original }()

	mock.ExpectQuery(`SELECT .Phase., .Status. FROM .*BootstrapState`).
		WithArgs("standard_actions_pack").
		WillReturnRows(sqlmock.NewRows([]string{"Phase", "Status"}))

	mock.ExpectExec(`INSERT INTO _System_StandardAction`).
		WillReturnResult(sqlmock.NewResult(1, 1))
	expectPersistedStandardActionID(mock, "keep", "sa_keep")
	mock.ExpectQuery(`SELECT Id FROM .*ActionSchema`).
		WithArgs("sa_keep").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*ActionFieldDefault`).
		WithArgs("sa_keep").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*ActionInputGuard`).
		WithArgs("sa_keep").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*StandardActionInternalAccessScope`).
		WithArgs("sa_keep").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))

	// Parent reconcile: SELECT persisted Ids for pack Names, then SELECT
	// live non-customized rows — one orphan ("sa_gone" from a prior pack)
	// must be soft-deleted.
	mock.ExpectQuery(`SELECT Id FROM .*_System_StandardAction.* WHERE Name IN \(\?\) AND IsDeleted = 0`).
		WithArgs("keep").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow("sa_keep"))
	mock.ExpectQuery(`SELECT Id FROM .*_System_StandardAction.* WHERE IsCustomized = 0 AND IsDeleted = 0`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).
			AddRow("sa_keep").
			AddRow("sa_gone"))
	mock.ExpectExec(`UPDATE .*_System_StandardAction.* SET IsActive=0, IsDeleted=1, LastModifiedDate=\? WHERE Id IN \(\?\)`).
		WillReturnResult(sqlmock.NewResult(0, 1))

	mock.ExpectExec(`INSERT INTO .*BootstrapState.*ON DUPLICATE KEY UPDATE`).
		WillReturnResult(sqlmock.NewResult(1, 1))

	require.NoError(t, InitializeStandardActions(db))
	require.NoError(t, mock.ExpectationsWereMet())
}

// TestInitializeStandardActions_ParentCustomizedOrphanPreserved pins the
// IsCustomized=1 guard on the parent reconcile: an admin-edited action
// row (IsCustomized=1) that disappears from the pack must STAY ACTIVE.
// The reconcile's SELECT filters IsCustomized=1 at the DB level, so the
// row never becomes a deactivation candidate.
func TestInitializeStandardActions_ParentCustomizedOrphanPreserved(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	original := standardActionsJSON
	standardActionsJSON = []byte(`[{"name":"keep","label":"Keep","flowDeveloperName":"f"}]`)
	defer func() { standardActionsJSON = original }()

	mock.ExpectQuery(`SELECT .Phase., .Status. FROM .*BootstrapState`).
		WithArgs("standard_actions_pack").
		WillReturnRows(sqlmock.NewRows([]string{"Phase", "Status"}))

	mock.ExpectExec(`INSERT INTO _System_StandardAction`).
		WillReturnResult(sqlmock.NewResult(1, 1))
	expectPersistedStandardActionID(mock, "keep", "sa_keep")
	mock.ExpectQuery(`SELECT Id FROM .*ActionSchema`).
		WithArgs("sa_keep").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*ActionFieldDefault`).
		WithArgs("sa_keep").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*ActionInputGuard`).
		WithArgs("sa_keep").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*StandardActionInternalAccessScope`).
		WithArgs("sa_keep").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))

	// Parent reconcile: only the non-customized "sa_keep" row is visible
	// to the IsCustomized=0 filter. The customized orphan is invisible
	// to the SELECT and therefore never deactivated — no UPDATE fires.
	mock.ExpectQuery(`SELECT Id FROM .*_System_StandardAction.* WHERE Name IN \(\?\) AND IsDeleted = 0`).
		WithArgs("keep").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow("sa_keep"))
	mock.ExpectQuery(`SELECT Id FROM .*_System_StandardAction.* WHERE IsCustomized = 0 AND IsDeleted = 0`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow("sa_keep"))

	mock.ExpectExec(`INSERT INTO .*BootstrapState.*ON DUPLICATE KEY UPDATE`).
		WillReturnResult(sqlmock.NewResult(1, 1))

	require.NoError(t, InitializeStandardActions(db))
	require.NoError(t, mock.ExpectationsWereMet())
}

// TestInitializeStandardActions_ParentReconcileUsesPersistedId pins the
// review correction: reconcile `keep` must come from the actually-
// persisted row Id (SELECT by Name), NOT from StandardActionID(def.Name).
// If a legacy deployment carries the same Name under a non-canonical Id
// (e.g. "custom_convertLead" loaded via manual SQL or migration), ON
// DUPLICATE KEY UPDATE on the unique Name index preserves that Id — and
// the reconcile MUST see the real Id in keep, or it would wrongly soft-
// delete the live row.
func TestInitializeStandardActions_ParentReconcileUsesPersistedId(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	original := standardActionsJSON
	standardActionsJSON = []byte(`[{"name":"convertLead","label":"Convert","flowDeveloperName":"f"}]`)
	defer func() { standardActionsJSON = original }()

	mock.ExpectQuery(`SELECT .Phase., .Status. FROM .*BootstrapState`).
		WithArgs("standard_actions_pack").
		WillReturnRows(sqlmock.NewRows([]string{"Phase", "Status"}))

	mock.ExpectExec(`INSERT INTO _System_StandardAction`).
		WillReturnResult(sqlmock.NewResult(1, 1))
	expectPersistedStandardActionID(mock, "convertLead", "custom_convertLead")
	mock.ExpectQuery(`SELECT Id FROM .*ActionSchema`).
		WithArgs("custom_convertLead").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*ActionFieldDefault`).
		WithArgs("custom_convertLead").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*ActionInputGuard`).
		WithArgs("custom_convertLead").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))
	mock.ExpectQuery(`SELECT Id FROM .*StandardActionInternalAccessScope`).
		WithArgs("custom_convertLead").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}))

	// Parent reconcile: SELECT-by-Name returns the legacy Id. The live
	// scan returns that SAME legacy Id. keep contains the legacy Id, so
	// NO UPDATE fires. If the code regressed to building keep from
	// StandardActionID(def.Name), keep would contain "sa_convertlead"
	// and an UPDATE would soft-delete "custom_convertLead" — the test
	// fails because no ExpectExec(UPDATE...) is registered.
	mock.ExpectQuery(`SELECT Id FROM .*_System_StandardAction.* WHERE Name IN \(\?\) AND IsDeleted = 0`).
		WithArgs("convertLead").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow("custom_convertLead"))
	mock.ExpectQuery(`SELECT Id FROM .*_System_StandardAction.* WHERE IsCustomized = 0 AND IsDeleted = 0`).
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow("custom_convertLead"))

	mock.ExpectExec(`INSERT INTO .*BootstrapState.*ON DUPLICATE KEY UPDATE`).
		WillReturnResult(sqlmock.NewResult(1, 1))

	require.NoError(t, InitializeStandardActions(db))
	require.NoError(t, mock.ExpectationsWereMet())
}