dobotai-platform/db/migrations/000007_coverage_decisions_contract_test.go

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

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

func read007Migration(t *testing.T, direction string) string {
	t.Helper()
	data, err := os.ReadFile(filepath.Join(".", "000007_coverage_decisions."+direction+".sql"))
	if err != nil {
		t.Fatalf("read 000007 %s migration: %v", direction, err)
	}
	return string(data)
}

func TestUp007CreatesCoverageDecisionsTable(t *testing.T) {
	up := strings.ToUpper(read007Migration(t, "up"))
	required := []string{
		"CREATE TABLE IF NOT EXISTS COVERAGE_DECISIONS",
		"ID", "TENANT_KEY", "CHANNEL", "TURN_ID",
		"EVALUATOR_VERSION", "VERDICT", "REASON_CODE", "REFERENCE_COUNT", "TOP_SCORE",
		"CREATED_AT", "UPDATED_AT",
	}
	for _, fragment := range required {
		if !strings.Contains(up, fragment) {
			t.Errorf("up migration missing %q", fragment)
		}
	}
}

func TestUp007CoverageDecisionsConstraints(t *testing.T) {
	up := strings.ToUpper(read007Migration(t, "up"))
	// REFERENCES turns(id)
	if !strings.Contains(up, "REFERENCES TURNS(ID)") {
		t.Error("coverage_decisions.turn_id must reference turns(id)")
	}
	// UNIQUE (tenant_key, channel, turn_id)
	if !strings.Contains(up, "UNIQUE (TENANT_KEY, CHANNEL, TURN_ID)") {
		t.Error("coverage_decisions must have UNIQUE (tenant_key, channel, turn_id)")
	}
	// verdict CHECK
	if !strings.Contains(up, "COVERED") || !strings.Contains(up, "UNCOVERED") {
		t.Error("verdict CHECK must include 'covered' and 'uncovered'")
	}
	// reason_code CHECK
	for _, code := range []string{"COVERED_HAS_REFERENCES", "UNCOVERED_NO_REFERENCES", "UNCOVERED_EXPLICIT_NO_ANSWER"} {
		if !strings.Contains(up, code) {
			t.Errorf("reason_code CHECK missing %q", code)
		}
	}
}

func TestUp007CreatesSupportCasesTable(t *testing.T) {
	up := strings.ToUpper(read007Migration(t, "up"))
	required := []string{
		"CREATE TABLE IF NOT EXISTS SUPPORT_CASES",
		"ID", "TENANT_KEY", "CHANNEL", "TURN_ID",
		"COVERAGE_DECISION_ID", "STATUS", "CREATED_AT", "UPDATED_AT",
	}
	for _, fragment := range required {
		if !strings.Contains(up, fragment) {
			t.Errorf("up migration missing %q", fragment)
		}
	}
	// Notification fields must be present
	notificationFields := []string{
		"NOTIFICATION_ATTEMPTS", "LAST_NOTIFICATION_FAILURE_CODE",
		"LAST_NOTIFICATION_AT", "NOTIFIED_AT",
	}
	for _, fragment := range notificationFields {
		if !strings.Contains(up, fragment) {
			t.Errorf("up migration missing notification field %q", fragment)
		}
	}
}

func TestUp007SupportCasesConstraints(t *testing.T) {
	up := strings.ToUpper(read007Migration(t, "up"))
	// FK to coverage_decisions
	if !strings.Contains(up, "REFERENCES COVERAGE_DECISIONS(ID)") {
		t.Error("support_cases.coverage_decision_id must reference coverage_decisions(id)")
	}
	// FK to turns
	if !strings.Contains(up, "REFERENCES TURNS(ID)") {
		t.Error("support_cases.turn_id must reference turns(id)")
	}
	// UNIQUE (tenant_key, channel, turn_id)
	if !strings.Contains(up, "UNIQUE (TENANT_KEY, CHANNEL, TURN_ID)") {
		t.Error("support_cases must have UNIQUE (tenant_key, channel, turn_id)")
	}
	// status CHECK
	for _, status := range []string{"PENDING", "NOTIFYING", "NOTIFICATION_FAILED", "NOTIFIED", "RESOLVED"} {
		if !strings.Contains(up, status) {
			t.Errorf("status CHECK must include %q", status)
		}
	}
}

func TestUp007CreatesQueryIndexes(t *testing.T) {
	up := strings.ToUpper(read007Migration(t, "up"))
	// Pending/retry query index and stale notifying index
	if !strings.Contains(up, "IDX_SUPPORT_CASES_NOTIFICATION_PENDING") ||
		!strings.Contains(up, "WHERE STATUS IN ('PENDING', 'NOTIFICATION_FAILED')") {
		t.Error("up migration must index pending and notification_failed support_cases")
	}
	if !strings.Contains(up, "IDX_SUPPORT_CASES_NOTIFYING_STALE") ||
		!strings.Contains(up, "WHERE STATUS = 'NOTIFYING'") {
		t.Error("up migration must index stale notifying support_cases")
	}
	// Coverage decisions listing index
	if !strings.Contains(up, "IDX_COVERAGE_DECISIONS_TENANT_CHANNEL") {
		t.Error("up migration must create index on coverage_decisions (tenant_key, channel, created_at DESC)")
	}
	// Uncovered verdict index
	if !strings.Contains(up, "WHERE VERDICT = 'UNCOVERED'") {
		t.Error("up migration must create partial index on uncovered coverage_decisions")
	}
}

func TestUp007CoverageNoReservedWords(t *testing.T) {
	up := read007Migration(t, "up")
	// Check for bare "references" as column name in CREATE TABLE statements
	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, "REFERENCES TURNS") && !strings.Contains(upperLine, "REFERENCES COVERAGE") {
			t.Errorf("reserved word 'references' used as column name in: %s", trimmed)
		}
	}
}

func TestUp007NoForbiddenOperations(t *testing.T) {
	up := strings.ToUpper(read007Migration(t, "up"))
	for _, forbidden := range []string{
		"DROP TABLE", "DROP COLUMN", "TRUNCATE", "DELETE FROM",
		"ALTER TABLE", "RAW_PAYLOAD", "SESSIONWEBHOOK", "TOKEN",
		"RAW_RESPONSE", "RAW_ERROR",
	} {
		if strings.Contains(up, forbidden) {
			t.Errorf("up migration contains forbidden operation %q", forbidden)
		}
	}
}

func TestUp007NoQuestionAnswerBodies(t *testing.T) {
	up := strings.ToUpper(read007Migration(t, "up"))
	for _, forbidden := range []string{"QUESTION", "ANSWER_TEXT", "REFERENCE_CONTENT", "ANSWER_CONTENT"} {
		if strings.Contains(up, forbidden) {
			t.Errorf("coverage/support tables must not duplicate question/answer/reference bodies: found %q", forbidden)
		}
	}
}

func TestDown007DropsInSafeOrder(t *testing.T) {
	down := strings.ToUpper(read007Migration(t, "down"))
	supportAt := strings.Index(down, "DROP TABLE IF EXISTS SUPPORT_CASES")
	coverageAt := strings.Index(down, "DROP TABLE IF EXISTS COVERAGE_DECISIONS")
	if supportAt < 0 || coverageAt < 0 {
		t.Fatal("down migration must drop both tables")
	}
	if supportAt > coverageAt {
		t.Fatal("down migration must drop support_cases (child) before coverage_decisions (parent)")
	}
}

func TestDown007DoesNotDropBaselineTables(t *testing.T) {
	down := strings.ToUpper(read007Migration(t, "down"))
	for _, table := range []string{"TURNS", "MESSAGES", "SESSION_MAPPINGS", "CHANNEL_ACTORS", "CHANNEL_CONVERSATIONS"} {
		if strings.Contains(down, "DROP TABLE IF EXISTS "+table) {
			t.Errorf("down migration must not drop baseline table %q", table)
		}
	}
}