package migrate

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

	"github.com/mem9-ai/drive9/pkg/testtidb"
)

// restorePlainSemanticSchema recreates the plain (app-managed) semantic
// table. ResetDB only deletes rows, so the generated-column variants created
// by these tests are load-bearing cleanups: without an explicit restore the
// modified schema would leak into other tests sharing the database.
func restorePlainSemanticSchema(t *testing.T, db *sql.DB) {
	t.Helper()
	if _, err := db.Exec(`DROP TABLE IF EXISTS semantic`); err != nil {
		t.Fatalf("drop semantic: %v", err)
	}
	if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS semantic (
		inode_id                           VARCHAR(64) PRIMARY KEY,
		content_text                       LONGTEXT,
		description                        LONGTEXT,
		embedding                          LONGTEXT,
		embedding_revision                 BIGINT,
		description_embedding              LONGTEXT,
		description_embedding_revision     BIGINT
	)`); err != nil {
		t.Fatalf("restore plain semantic: %v", err)
	}
}

// TestSplitTablesMigratorSkipGeneratedColumns verifies that when the semantic
// table has GENERATED columns (TiDB auto-embedding mode), the migration omits
// those columns from the INSERT column list (GENERATED ALWAYS columns are not
// insertable) and lets the database recompute them from
// content_text/description.
func TestSplitTablesMigratorSkipGeneratedColumns(t *testing.T) {
	db, err := sql.Open("mysql", testDSN)
	if err != nil {
		t.Fatalf("open db: %v", err)
	}
	ctx := t.Context()
	testtidb.ResetDB(t, db)

	// Insert a file with content_text.
	if _, err := db.Exec(`
		INSERT INTO files (file_id, storage_type, storage_ref, status, content_text, description, embedding)
		VALUES ('file1', 'db9', 'ref1', 'CONFIRMED', 'hello', 'world', 'should_be_ignored')
	`); err != nil {
		t.Fatalf("insert file: %v", err)
	}

	// Recreate semantic table with generated columns (simulating TiDB auto-embedding).
	if _, err := db.Exec(`DROP TABLE IF EXISTS semantic`); err != nil {
		t.Fatalf("drop semantic: %v", err)
	}
	if _, err := db.Exec(`
		CREATE TABLE semantic (
			inode_id                           VARCHAR(64) PRIMARY KEY,
			content_text                       LONGTEXT,
			description                        LONGTEXT,
			embedding                          VARCHAR(255) GENERATED ALWAYS AS (UPPER(content_text)) STORED,
			embedding_revision                 BIGINT,
			description_embedding              VARCHAR(255) GENERATED ALWAYS AS (UPPER(description)) STORED,
			description_embedding_revision     BIGINT
		)
	`); err != nil {
		t.Fatalf("create semantic with generated columns: %v", err)
	}
	t.Cleanup(func() {
		restorePlainSemanticSchema(t, db)
		_ = db.Close()
	})

	m := NewSplitTablesMigrator(db)
	res, err := m.Run(ctx)
	if err != nil {
		t.Fatalf("migration failed: %v", err)
	}
	if res.SemanticMigrated != 1 {
		t.Errorf("semantic migrated = %d, want 1", res.SemanticMigrated)
	}

	// Verify that embedding was auto-generated from content_text,
	// not copied from files.embedding (which was 'should_be_ignored').
	var embedding, descEmbedding string
	if err := db.QueryRow(`
		SELECT embedding, description_embedding FROM semantic WHERE inode_id = 'file1'
	`).Scan(&embedding, &descEmbedding); err != nil {
		t.Fatalf("select semantic: %v", err)
	}
	if embedding != "HELLO" {
		t.Errorf("embedding = %q, want HELLO (should be generated, not copied)", embedding)
	}
	if descEmbedding != "WORLD" {
		t.Errorf("description_embedding = %q, want WORLD (should be generated, not copied)", descEmbedding)
	}
}

// TestSplitTablesMigratorGeneratedColumnRerun pins the two behaviors that
// ended the 2026-09-03 dat9 incident loop. The semantic table is recreated
// with a STORED generated column that fails to evaluate for
// content_text='boom', standing in for TiDB auto-embedding inference: the
// expression is evaluated for every row the semantic INSERT ... SELECT
// returns, even rows INSERT IGNORE would discard.
func TestSplitTablesMigratorGeneratedColumnRerun(t *testing.T) {
	db, err := sql.Open("mysql", testDSN)
	if err != nil {
		t.Fatalf("open db: %v", err)
	}
	ctx := t.Context()
	testtidb.ResetDB(t, db)

	// Recreate semantic with a generated column that explodes on 'boom'. The
	// error class matters: Error 3140 (invalid JSON) is a statement-level
	// error that INSERT IGNORE does not demote to a warning — unlike data
	// conversion errors such as division by zero — so it faithfully stands in
	// for TiDB Cloud inference failures (Error 1105 from EMBED_TEXT).
	if _, err := db.Exec(`DROP TABLE IF EXISTS semantic`); err != nil {
		t.Fatalf("drop semantic: %v", err)
	}
	if _, err := db.Exec(`
		CREATE TABLE semantic (
			inode_id                           VARCHAR(64) PRIMARY KEY,
			content_text                       LONGTEXT,
			description                        LONGTEXT,
			embedding                          VARCHAR(255) GENERATED ALWAYS AS (IF(content_text = 'boom', JSON_EXTRACT('notjson', '$'), UPPER(content_text))) STORED,
			embedding_revision                 BIGINT,
			description_embedding              LONGTEXT,
			description_embedding_revision     BIGINT
		)
	`); err != nil {
		t.Fatalf("create semantic with probe column: %v", err)
	}
	t.Cleanup(func() {
		restorePlainSemanticSchema(t, db)
		_ = db.Close()
	})

	insertFile := func(id, text string) {
		t.Helper()
		if _, err := db.Exec(`
			INSERT INTO files (file_id, storage_type, storage_ref, status, content_text)
			VALUES (?, 'db9', ?, 'CONFIRMED', ?)
		`, id, "ref-"+id, text); err != nil {
			t.Fatalf("insert file %s: %v", id, err)
		}
	}
	insertDentry := func(id string) {
		t.Helper()
		if _, err := db.Exec(`
			INSERT INTO file_nodes (node_id, path, path_hash, parent_path, parent_path_hash, name, is_directory, file_id, inode_id)
			VALUES (?, ?, LOWER(SHA2(?, 256)), '/', LOWER(SHA2('/', 256)), ?, 0, ?, NULL)
		`, "node-"+id, "/"+id+".txt", "/"+id+".txt", id+".txt", id); err != nil {
			t.Fatalf("insert dentry %s: %v", id, err)
		}
	}
	dentryInodeID := func(id string) sql.NullString {
		t.Helper()
		var inodeID sql.NullString
		if err := db.QueryRow(`SELECT inode_id FROM file_nodes WHERE file_id = ?`, id).Scan(&inodeID); err != nil {
			t.Fatalf("read dentry %s inode_id: %v", id, err)
		}
		return inodeID
	}

	m := NewSplitTablesMigrator(db)

	// Initial migration of a benign row: the generated column evaluates fine.
	insertFile("file1", "ok")
	res, err := m.Run(ctx)
	if err != nil {
		t.Fatalf("initial migration failed: %v", err)
	}
	if res.SemanticMigrated != 1 {
		t.Fatalf("semantic migrated = %d, want 1", res.SemanticMigrated)
	}

	// Case 1 (filter pin): poison the legacy row after migration; the migrated
	// semantic row stays benign. A rerun triggered only by an unbackfilled
	// dentry must succeed — if the unmigrated-rows filter were removed, the
	// unfiltered SELECT would return file1 and the generated column would
	// fail the whole Run.
	if _, err := db.Exec(`UPDATE files SET content_text = 'boom' WHERE file_id = 'file1'`); err != nil {
		t.Fatalf("poison file1: %v", err)
	}
	insertDentry("file1")
	if _, err := m.Run(ctx); err != nil {
		t.Fatalf("repair rerun must not re-evaluate migrated rows: %v", err)
	}
	if inodeID := dentryInodeID("file1"); !inodeID.Valid || inodeID.String != "file1" {
		t.Errorf("file1 dentry inode_id = %v, want file1", inodeID)
	}

	// Case 2 (reorder pin): a genuinely missing semantic row that fails
	// inference must fail Run at the semantic step, but only after the cheap
	// local repair steps (here: dentry backfill) have committed.
	insertFile("file2", "boom")
	insertDentry("file2")
	if _, err := m.Run(ctx); err == nil || !strings.Contains(err.Error(), "migrate semantic") {
		t.Fatalf("Run error = %v, want semantic failure for poisoned unmigrated row", err)
	}
	if inodeID := dentryInodeID("file2"); !inodeID.Valid || inodeID.String != "file2" {
		t.Errorf("reorder violated: file2 dentry inode_id = %v, want file2 (backfill must commit before the semantic step)", inodeID)
	}
}

// TestSemanticHasGeneratedColumns verifies the detection logic directly.
func TestSemanticHasGeneratedColumns(t *testing.T) {
	db, err := sql.Open("mysql", testDSN)
	if err != nil {
		t.Fatalf("open db: %v", err)
	}
	ctx := t.Context()
	testtidb.ResetDB(t, db)

	// Ensure plain semantic table (previous tests may have left a generated-column version).
	restorePlainSemanticSchema(t, db)
	t.Cleanup(func() {
		restorePlainSemanticSchema(t, db)
		_ = db.Close()
	})

	// Plain semantic table (no generated columns).
	m := NewSplitTablesMigrator(db)
	gen, err := m.semanticHasGeneratedColumns(ctx)
	if err != nil {
		t.Fatalf("detect generated columns on plain table: %v", err)
	}
	if gen {
		t.Error("expected false for plain semantic table")
	}

	// Recreate with generated columns.
	if _, err := db.Exec(`DROP TABLE IF EXISTS semantic`); err != nil {
		t.Fatalf("drop semantic: %v", err)
	}
	if _, err := db.Exec(`
		CREATE TABLE semantic (
			inode_id     VARCHAR(64) PRIMARY KEY,
			content_text LONGTEXT,
			embedding    VARCHAR(255) GENERATED ALWAYS AS (UPPER(content_text)) STORED
		)
	`); err != nil {
		t.Fatalf("create semantic with generated column: %v", err)
	}
	gen, err = m.semanticHasGeneratedColumns(ctx)
	if err != nil {
		t.Fatalf("detect generated columns on generated table: %v", err)
	}
	if !gen {
		t.Error("expected true for semantic table with generated column")
	}
}