//go:build integration

package bootstrap

import (
	"database/sql"
	"os"
	"strings"
	"testing"

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

// TestReconcileTableColumns_AutoNumberV2 verifies — against a real TiDB/MySQL-
// compatible database — that the Wave 1 Autonumber Engine v2 schema changes
// land correctly after ReconcileTableColumns runs:
//
//   - _System_Field gains the 6 auto_number_* columns (PRD §4.3)
//   - _System_RecordType gains RecordTypeCode (PRD §4.5, Q11 Approach α / F7)
//   - _System_AutoNumberCounter exists with the v2 composite uniqueness pattern
//     (PRD §4.4)
//
// The test is skipped unless TIDB_TEST_DSN is set (DSN for an already-provisioned
// test database — the suggested value in project memory is the local/branch
// cluster described in project_local_test_db.md).
//
// IMPORTANT: this test does NOT seed any rows. It only verifies DDL reconciliation
// outcomes — the table + column existence checks are the contract.
//
// Expected initial state (on a DB where these changes haven't shipped yet): FAIL.
// That's the point — the test is the acceptance criterion for Wave 1.
func TestReconcileTableColumns_AutoNumberV2(t *testing.T) {
	dsn := os.Getenv("TIDB_TEST_DSN")
	if dsn == "" {
		t.Skip("TIDB_TEST_DSN not set; skipping autonumber v2 schema integration test")
	}

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		t.Fatalf("sql.Open: %v", err)
	}
	defer db.Close()

	if err := db.Ping(); err != nil {
		t.Skipf("TIDB_TEST_DSN set but DB unreachable (%v); skipping", err)
	}

	defs := GetSystemTableDefinitions()
	if err := ReconcileTableColumns(db, defs); err != nil {
		t.Fatalf("ReconcileTableColumns returned error: %v", err)
	}

	t.Run("_System_Field has v2 autonumber columns", func(t *testing.T) {
		want := []string{
			"AutoNumberFormat",
			"AutoNumberScope",
			"AutoNumberStart",
			"AutoNumberTokenRefs",
			"AutoNumberCanonicalHash",
			"AutoNumberCounterResetPolicy",
		}
		for _, col := range want {
			if !columnExists(t, db, "_System_Field", col) {
				t.Errorf("_System_Field.%s missing", col)
			}
		}
	})

	t.Run("_System_RecordType has RecordTypeCode", func(t *testing.T) {
		if !columnExists(t, db, "_System_RecordType", "RecordTypeCode") {
			t.Error("_System_RecordType.RecordTypeCode missing")
		}
		// v2 fixpack F7: must be NULL-able on creation; V6 verify will enforce
		// non-empty at rollout time. Here we only assert presence.
	})

	t.Run("_System_AutoNumberCounter table exists with v2 columns", func(t *testing.T) {
		if !tableExists(t, db, "_System_AutoNumberCounter") {
			t.Fatal("_System_AutoNumberCounter table missing; ReconcileTableColumns does not create new tables — run full bootstrap/CreatePhysicalTable path first")
		}
		want := []string{"Id", "FieldId", "ScopeKey", "NextValue", "CreatedDate", "LastModifiedDate"}
		for _, col := range want {
			if !columnExists(t, db, "_System_AutoNumberCounter", col) {
				t.Errorf("_System_AutoNumberCounter.%s missing", col)
			}
		}
	})
}

func tableExists(t *testing.T, db *sql.DB, table string) bool {
	t.Helper()
	var n int
	err := db.QueryRow(
		"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?",
		table,
	).Scan(&n)
	if err != nil {
		t.Fatalf("query INFORMATION_SCHEMA.TABLES for %s: %v", table, err)
	}
	return n > 0
}

func columnExists(t *testing.T, db *sql.DB, table, column string) bool {
	t.Helper()
	var n int
	err := db.QueryRow(
		"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?",
		table, column,
	).Scan(&n)
	if err != nil {
		// case-insensitive fallback in case of collation oddities
		if strings.Contains(err.Error(), "collat") {
			return false
		}
		t.Fatalf("query INFORMATION_SCHEMA.COLUMNS for %s.%s: %v", table, column, err)
	}
	return n > 0
}