dobotai-platform/db/migrations/000005_answer_feedback_contract_test.go

// Package migrations_test contains source-level contract tests for the
// 000005_answer_feedback migration. No real database connection is used.
package migrations_test

import (
	"os"
	"path/filepath"
	"regexp"
	"strings"
	"testing"
)

func read005UpMigration(t *testing.T) string {
	t.Helper()
	data, err := os.ReadFile(filepath.Join(".", "000005_answer_feedback.up.sql"))
	if err != nil {
		t.Fatalf("read up migration: %v", err)
	}
	return string(data)
}

func read005DownMigration(t *testing.T) string {
	t.Helper()
	data, err := os.ReadFile(filepath.Join(".", "000005_answer_feedback.down.sql"))
	if err != nil {
		t.Fatalf("read down migration: %v", err)
	}
	return string(data)
}

// --- RED: These tests will fail until the migration files exist ---

// TestUp005CreatesAnswerFeedbackTable ensures the table is created with IF NOT EXISTS.
func TestUp005CreatesAnswerFeedbackTable(t *testing.T) {
	up := read005UpMigration(t)
	upper := strings.ToUpper(up)

	if !strings.Contains(upper, "CREATE TABLE IF NOT EXISTS ANSWER_FEEDBACK") {
		t.Error("up migration must create answer_feedback table with IF NOT EXISTS")
	}
}

// TestUp005AnswerFeedbackColumns ensures all required columns are present.
func TestUp005AnswerFeedbackColumns(t *testing.T) {
	up := read005UpMigration(t)
	upper := strings.ToUpper(up)

	required := []string{
		"ID TEXT PRIMARY KEY",
		"TENANT_KEY TEXT NOT NULL",
		"CHANNEL TEXT NOT NULL",
		"ANSWER_MESSAGE_ID TEXT NOT NULL",
		"TURN_ID TEXT NOT NULL",
		"ACTOR_ID TEXT NOT NULL",
		"VALUE TEXT NOT NULL",
		"REASON_CODE TEXT NOT NULL DEFAULT ''",
		"SOURCE TEXT NOT NULL",
		"CREATED_AT TIMESTAMPTZ NOT NULL DEFAULT NOW()",
		"UPDATED_AT TIMESTAMPTZ NOT NULL DEFAULT NOW()",
	}
	for _, col := range required {
		if !strings.Contains(upper, col) {
			t.Errorf("up migration must contain column %q", col)
		}
	}
}

// TestUp005ValueCheck ensures value is constrained to 'up' or 'down'.
func TestUp005ValueCheck(t *testing.T) {
	up := read005UpMigration(t)
	upper := strings.ToUpper(up)

	if !strings.Contains(upper, "VALUE IN ('UP'") || !strings.Contains(upper, "'DOWN'") {
		t.Error("value must be CHECK constrained to 'up' and 'down'")
	}
}

// TestUp005ReasonCodeCheck ensures reason_code is constrained to allowed values.
func TestUp005ReasonCodeCheck(t *testing.T) {
	up := read005UpMigration(t)
	upper := strings.ToUpper(up)

	allowed := []string{"''", "INCORRECT", "NOT_RESOLVED", "IRRELEVANT", "UNCLEAR", "OUTDATED", "OTHER"}
	for _, r := range allowed {
		if !strings.Contains(upper, r) {
			t.Errorf("reason_code CHECK must include %q", r)
		}
	}
}

// TestUp005SourceCheck ensures source is constrained to 'dingtalk_card'.
func TestUp005SourceCheck(t *testing.T) {
	up := read005UpMigration(t)
	upper := strings.ToUpper(up)

	if !strings.Contains(upper, "DINGTALK_CARD") {
		t.Error("source must be CHECK constrained to 'dingtalk_card'")
	}
}

// TestUp005UpValueMustHaveNoReason ensures CHECK that value='up' implies reason_code=”.
func TestUp005UpValueMustHaveNoReason(t *testing.T) {
	up := read005UpMigration(t)
	upper := strings.ToUpper(up)

	// Must have a CHECK that when value='up', reason_code must be empty
	hasCheck := strings.Contains(upper, "VALUE = 'UP'") && strings.Contains(upper, "REASON_CODE = ''")
	// Also check for the inverse: "VALUE != 'UP' OR REASON_CODE = ''"
	hasOr := strings.Contains(upper, "OR") && strings.Contains(upper, "REASON_CODE = ''")
	if !hasCheck && !hasOr {
		t.Error("must have CHECK ensuring value='up' implies reason_code=''")
	}
}

// TestUp005ForeignKeyConstraints ensures FK constraints on answer_message_id, turn_id, actor_id.
func TestUp005ForeignKeyConstraints(t *testing.T) {
	up := read005UpMigration(t)
	upper := strings.ToUpper(up)

	// Must reference messages(id), turns(id), channel_actors(id)
	if !strings.Contains(upper, "REFERENCES MESSAGES(ID)") {
		t.Error("answer_message_id must have FK REFERENCES messages(id)")
	}
	if !strings.Contains(upper, "REFERENCES TURNS(ID)") {
		t.Error("turn_id must have FK REFERENCES turns(id)")
	}
	if !strings.Contains(upper, "REFERENCES CHANNEL_ACTORS(ID)") {
		t.Error("actor_id must have FK REFERENCES channel_actors(id)")
	}
}

// TestUp005UniqueConstraint ensures UNIQUE on (tenant_key, channel, answer_message_id, actor_id).
func TestUp005UniqueConstraint(t *testing.T) {
	up := read005UpMigration(t)
	upper := strings.ToUpper(up)

	hasUnique := strings.Contains(upper, "UNIQUE") &&
		strings.Contains(upper, "TENANT_KEY") &&
		strings.Contains(upper, "CHANNEL") &&
		strings.Contains(upper, "ANSWER_MESSAGE_ID") &&
		strings.Contains(upper, "ACTOR_ID")
	if !hasUnique {
		t.Error("must have UNIQUE constraint on (tenant_key, channel, answer_message_id, actor_id)")
	}
}

// TestUp005Indexes ensures required indexes are created.
func TestUp005Indexes(t *testing.T) {
	up := read005UpMigration(t)
	upper := strings.ToUpper(up)

	// Index on (tenant_key, value, updated_at DESC)
	hasValueIdx := strings.Contains(upper, "TENANT_KEY") &&
		strings.Contains(upper, "VALUE") &&
		strings.Contains(upper, "UPDATED_AT") &&
		strings.Contains(upper, "DESC")
	if !hasValueIdx {
		t.Error("must have index on (tenant_key, value, updated_at DESC)")
	}

	// Index on (tenant_key, reason_code, updated_at DESC)
	hasReasonIdx := strings.Contains(upper, "TENANT_KEY") &&
		strings.Contains(upper, "REASON_CODE") &&
		strings.Contains(upper, "UPDATED_AT") &&
		strings.Contains(upper, "DESC")
	if !hasReasonIdx {
		t.Error("must have index on (tenant_key, reason_code, updated_at DESC)")
	}

	// Must use CREATE INDEX IF NOT EXISTS
	if got := countMatchesRe(up, `(?i)CREATE\s+INDEX\s+IF\s+NOT\s+EXISTS`); got < 2 {
		t.Errorf("expected at least 2 CREATE INDEX IF NOT EXISTS, got %d", got)
	}
}

// TestUp005UpMigrationUsesIfNotExists ensures idempotency.
func TestUp005UpMigrationUsesIfNotExists(t *testing.T) {
	up := read005UpMigration(t)
	upper := strings.ToUpper(up)

	if strings.Contains(upper, "CREATE TABLE") && !strings.Contains(upper, "CREATE TABLE IF NOT EXISTS") {
		t.Error("CREATE TABLE must use IF NOT EXISTS for idempotency")
	}

	// Must have exactly 1 CREATE TABLE IF NOT EXISTS
	if got := countMatchesRe(up, `(?i)CREATE\s+TABLE\s+IF\s+NOT\s+EXISTS`); got != 1 {
		t.Errorf("expected 1 CREATE TABLE IF NOT EXISTS, got %d", got)
	}
}

func TestUp005CreatesUniqueCardProviderIndex(t *testing.T) {
	up := strings.ToUpper(read005UpMigration(t))
	for _, fragment := range []string{
		"CREATE UNIQUE INDEX IF NOT EXISTS IDX_MESSAGES_CARD_PROVIDER_UNIQUE",
		"ON MESSAGES (TENANT_KEY, CHANNEL, PROVIDER_MESSAGE_ID)",
		"PROVIDER_MESSAGE_ID != ''",
		"DELIVERY_METHOD = 'CARD'",
	} {
		if !strings.Contains(up, fragment) {
			t.Errorf("provider binding index missing %q", fragment)
		}
	}
	down := strings.ToUpper(read005DownMigration(t))
	if !strings.Contains(down, "DROP INDEX IF EXISTS IDX_MESSAGES_CARD_PROVIDER_UNIQUE") {
		t.Error("down migration must remove the B2 provider binding index")
	}
}

// TestUp005NoForbiddenColumns ensures no secrets/protocol columns.
func TestUp005NoForbiddenColumns(t *testing.T) {
	up := read005UpMigration(t)
	upper := strings.ToUpper(up)

	forbidden := []string{
		"WEBHOOK", "TOKEN", "RAW_CALLBACK", "RAW_PAYLOAD",
		"CONTENT", "CALLBACK", "EXTENSION", "CORP_ID",
		"SESSIONWEBHOOK", "DOWNLOAD_CODE", "DATAURI", "DATA_URI",
		"RAW_RESPONSE", "RAW_ERROR", "PROVIDER_RESPONSE", "API_RESPONSE",
	}
	for _, col := range forbidden {
		if strings.Contains(upper, col) {
			t.Errorf("migration must not contain forbidden column %q", col)
		}
	}
}

// TestUp005DoesNotModifyBaselineTables ensures migration only touches answer_feedback.
func TestUp005DoesNotModifyBaselineTables(t *testing.T) {
	up := read005UpMigration(t)
	upper := strings.ToUpper(up)

	// Must not ALTER other tables
	baseline := []string{"MESSAGES", "TURNS", "CHANNEL_ACTORS", "CHANNEL_CONVERSATIONS", "SESSION_MAPPINGS"}
	for _, tbl := range baseline {
		if strings.Contains(upper, "ALTER TABLE "+tbl) {
			t.Errorf("migration must not ALTER TABLE %s", tbl)
		}
	}
}

// TestDown005DropsAnswerFeedbackTable ensures down drops only the new table and indexes.
func TestDown005DropsAnswerFeedbackTable(t *testing.T) {
	down := read005DownMigration(t)
	upper := strings.ToUpper(down)

	if !strings.Contains(upper, "DROP TABLE IF EXISTS ANSWER_FEEDBACK") {
		t.Error("down migration must drop answer_feedback with IF EXISTS")
	}
}

// TestDown005DoesNotDropBaselineTables ensures down does not touch B1 tables.
func TestDown005DoesNotDropBaselineTables(t *testing.T) {
	down := read005DownMigration(t)
	upper := strings.ToUpper(down)

	baseline := []string{"MESSAGES", "TURNS", "CHANNEL_ACTORS", "CHANNEL_CONVERSATIONS", "SESSION_MAPPINGS"}
	for _, tbl := range baseline {
		if strings.Contains(upper, "DROP TABLE IF EXISTS "+tbl) {
			t.Errorf("down migration must not drop baseline table %s", tbl)
		}
	}
}

// TestDown005DoesNotDropBaselineColumns ensures down does not drop baseline columns.
func TestDown005DoesNotDropBaselineColumns(t *testing.T) {
	down := read005DownMigration(t)
	upper := strings.ToUpper(down)

	if strings.Contains(upper, "DROP COLUMN") || strings.Contains(upper, "ALTER TABLE") {
		t.Error("down migration must not drop/alter columns — only drop the answer_feedback table and indexes")
	}
}

// TestUp005NoReservedWords ensures no bare reserved words as column names.
func TestUp005NoReservedWords(t *testing.T) {
	up := read005UpMigration(t)

	// Check for bare "references" as column name (not in FK context)
	bareRefs := regexp.MustCompile(`(?im)^\s*REFERENCES\s+`)
	if bareRefs.MatchString(up) {
		lines := strings.Split(up, "\n")
		for _, line := range lines {
			trimmed := strings.TrimSpace(line)
			upperLine := strings.ToUpper(trimmed)
			if strings.HasPrefix(upperLine, "REFERENCES ") && !strings.Contains(upperLine, "FOREIGN KEY") {
				t.Errorf("reserved word 'references' used as column name: %s", trimmed)
			}
		}
	}
}

// --- Helpers ---

func countMatchesRe(haystack, pattern string) int {
	return len(regexp.MustCompile(pattern).FindAllString(haystack, -1))
}