package schema

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

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

// TestSuiteIntegration 测试套件：验证所有表的DDL在真实数据库中的执行
type TestSuiteIntegration struct {
	dbSQLite   *sql.DB
	dbMySQL    *sql.DB
	mysqlDSN   string
	skipMySQL  bool
	tablesDefs []func() *TableBuilder
	tableNames []string
}

// setupIntegrationTest 设置集成测试环境
func setupIntegrationTest(t *testing.T) *TestSuiteIntegration {
	suite := &TestSuiteIntegration{
		tablesDefs: []func() *TableBuilder{
			DefineChannelsTable,
			DefineAPIKeysTable,
			DefineChannelModelsTable,
			DefineAuthTokensTable,
			DefineSystemSettingsTable,
			DefineAdminSessionsTable,
			DefineLogsTable,
		},
		tableNames: []string{
			"channels",
			"api_keys",
			"channel_models",
			"auth_tokens",
			"system_settings",
			"admin_sessions",
			"logs",
		},
	}

	// 1. 设置SQLite内存数据库
	sqliteDB, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		t.Fatalf("Failed to open SQLite: %v", err)
	}
	suite.dbSQLite = sqliteDB

	// 2. 设置MySQL数据库（可选）
	suite.mysqlDSN = os.Getenv("CCLOAD_TEST_MYSQL_DSN")
	if suite.mysqlDSN == "" {
		t.Logf("MySQL DSN not set, skipping MySQL tests")
		suite.skipMySQL = true
	} else {
		mysqlDB, err := sql.Open("mysql", suite.mysqlDSN)
		if err != nil {
			t.Logf("Failed to open MySQL: %v, skipping MySQL tests", err)
			suite.skipMySQL = true
		} else {
			suite.dbMySQL = mysqlDB
		}
	}

	return suite
}

// teardownIntegrationTest 清理测试环境
func teardownIntegrationTest(suite *TestSuiteIntegration, _ *testing.T) {
	if suite.dbSQLite != nil {
		_ = suite.dbSQLite.Close()
	}
	if suite.dbMySQL != nil && !suite.skipMySQL {
		_ = suite.dbMySQL.Close()
	}
}

// TestAllTablesSQLiteIntegration 测试所有表在SQLite中的创建
func TestAllTablesSQLiteIntegration(t *testing.T) {
	suite := setupIntegrationTest(t)
	defer teardownIntegrationTest(suite, t)

	ctx := context.Background()

	// 为每个表测试DDL生成和执行
	for i, tableDef := range suite.tablesDefs {
		tableName := suite.tableNames[i]
		t.Run(tableName, func(t *testing.T) {
			// 1. 生成SQLite DDL
			builder := tableDef()
			sqliteDDL := builder.BuildSQLite()
			t.Logf("SQLite DDL for %s:\n%s", tableName, sqliteDDL)

			// 2. 执行DDL
			_, err := suite.dbSQLite.ExecContext(ctx, sqliteDDL)
			if err != nil {
				t.Fatalf("Failed to create table %s: %v", tableName, err)
			}

			// 3. 验证表是否存在
			verifyTableExists(t, suite.dbSQLite, tableName, "SQLite")

			// 4. 验证表结构（仅验证列类型，不验证约束）
			verifyTableStructure(t, suite.dbSQLite, tableName, builder, "SQLite")

			// 5. 创建索引
			indexes := builder.GetIndexesSQLite()
			for _, idx := range indexes {
				_, err := suite.dbSQLite.ExecContext(ctx, idx.SQL)
				if err != nil {
					t.Fatalf("Failed to create index %s: %v", idx.Name, err)
				}
			}

			// 6. 验证索引创建
			t.Logf("Attempting to verify indexes for %s...", tableName)
			verifyIndexesCreated(t, suite.dbSQLite, tableName, indexes, "SQLite")

			// 7. 测试插入基本数据
			testBasicInsert(t, suite.dbSQLite, tableName)
		})
	}

	// 8. 测试表间关联（如果存在外键）
	t.Run("TableRelationships", func(t *testing.T) {
		testTableRelationships(t, suite.dbSQLite)
	})
}

// TestAllTablesMySQLIntegration 测试所有表在MySQL中的创建
func TestAllTablesMySQLIntegration(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping MySQL integration test in short mode")
	}

	suite := setupIntegrationTest(t)
	defer teardownIntegrationTest(suite, t)

	if suite.skipMySQL {
		t.Skip("MySQL tests skipped")
	}

	ctx := context.Background()

	// 为每个表测试DDL生成和执行
	for i, tableDef := range suite.tablesDefs {
		tableName := suite.tableNames[i]
		t.Run(tableName, func(t *testing.T) {
			// 1. 生成MySQL DDL
			builder := tableDef()
			mysqlDDL := builder.BuildMySQL()
			t.Logf("MySQL DDL for %s:\n%s", tableName, mysqlDDL)

			// 2. 执行DDL
			_, err := suite.dbMySQL.ExecContext(ctx, mysqlDDL)
			if err != nil {
				t.Fatalf("Failed to create table %s: %v", tableName, err)
			}

			// 3. 验证表是否存在
			verifyTableExists(t, suite.dbMySQL, tableName, "MySQL")

			// 4. 验证表结构
			verifyTableStructure(t, suite.dbMySQL, tableName, builder, "MySQL")

			// 5. 验证索引创建（MySQL 5.6兼容）
			verifyIndexesCreated(t, suite.dbMySQL, tableName, builder.GetIndexesMySQL(), "MySQL")

			// 6. 测试插入基本数据
			testBasicInsert(t, suite.dbMySQL, tableName)
		})
	}

	// 7. 测试表间关联
	t.Run("TableRelationships", func(t *testing.T) {
		testTableRelationships(t, suite.dbMySQL)
	})
}

// verifyTableExists 验证表是否存在
func verifyTableExists(t *testing.T, db *sql.DB, tableName, dbType string) {
	var exists bool
	var query string
	var args []any

	switch dbType {
	case "SQLite":
		query = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?"
		args = []any{tableName}
	case "MySQL":
		query = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name=?"
		args = []any{tableName}
	}

	err := db.QueryRow(query, args...).Scan(&exists)
	if err != nil {
		t.Fatalf("Failed to check if table %s exists: %v", tableName, err)
	}
	if !exists {
		t.Errorf("Table %s was not created", tableName)
	}
}

// verifyTableStructure 验证表结构是否符合预期
func verifyTableStructure(t *testing.T, db *sql.DB, tableName string, _ *TableBuilder, dbType string) {
	var query string
	switch dbType {
	case "SQLite":
		query = fmt.Sprintf("PRAGMA table_info(%s)", tableName)
	case "MySQL":
		query = fmt.Sprintf("DESCRIBE %s", tableName)
	}

	rows, err := db.Query(query)
	if err != nil {
		t.Fatalf("Failed to get table structure for %s: %v", tableName, err)
	}
	defer func() { _ = rows.Close() }()

	// 验证实际存在的列
	var actualColumns []string
	for rows.Next() {
		var colName, colType, nullable, key, defaultValue, extra string

		switch dbType {
		case "SQLite":
			var cid int
			var dfltValue any
			err := rows.Scan(&cid, &colName, &colType, &nullable, &dfltValue, &extra)
			if err != nil {
				t.Errorf("Failed to scan column info: %v", err)
				continue
			}
			actualColumns = append(actualColumns, fmt.Sprintf("%s %s", colName, colType))
		case "MySQL":
			err := rows.Scan(&colName, &colType, &nullable, &key, &defaultValue, &extra)
			if err != nil {
				t.Errorf("Failed to scan column info: %v", err)
				continue
			}
			actualColumns = append(actualColumns, fmt.Sprintf("%s %s", colName, colType))
		}
	}

	t.Logf("Table %s structure (%s):", tableName, dbType)
	for _, col := range actualColumns {
		t.Logf("  - %s", col)
	}

	// 基本验证：确保有预期的列数
	if len(actualColumns) == 0 {
		t.Errorf("No columns found in table %s", tableName)
	}
}

// verifyIndexesCreated 验证索引是否创建成功（宽容模式）
func verifyIndexesCreated(t *testing.T, db *sql.DB, tableName string, indexes []IndexDef, dbType string) {
	for _, idx := range indexes {
		t.Logf("Verifying index: %s", idx.SQL)

		var query string
		var result any

		switch dbType {
		case "SQLite":
			// 使用PRAGMA indexes获取索引信息
			query = fmt.Sprintf("SELECT name FROM pragma_index_list('%s') WHERE name='%s'", tableName, idx.Name)
			err := db.QueryRow(query).Scan(&result)
			if err != nil {
				// [FIX] P3: 索引验证失败应该报错，而不是只记录日志
				t.Errorf("Index %s not found in SQLite: %v", idx.Name, err)
				continue
			}
			t.Logf("Index %s found in SQLite", idx.Name)
		case "MySQL":
			// MySQL 5.6兼容：检查索引是否存在
			query = fmt.Sprintf("SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='%s' AND index_name='%s'", tableName, idx.Name)
			var count int
			err := db.QueryRow(query).Scan(&count)
			if err != nil {
				t.Errorf("Index %s verification failed in MySQL: %v", idx.Name, err)
				continue
			}
			if count == 0 {
				// [FIX] P3: 索引未创建应该报错
				t.Errorf("Index %s not found in MySQL", idx.Name)
			} else {
				t.Logf("Index %s verified successfully in MySQL", idx.Name)
			}
		}
	}
}

// testBasicInsert 测试基本插入操作
func testBasicInsert(t *testing.T, db *sql.DB, tableName string) {
	// 根据不同表执行基本插入测试
	switch tableName {
	case "channels":
		// 注意：models 和 model_redirects 字段已迁移到 channel_models 表
		_, err := db.Exec("INSERT INTO channels (name, url, channel_type, created_at, updated_at) VALUES (?, ?, ?, ?, ?)",
			"test-channel", "http://example.com", "anthropic", 1234567890, 1234567890)
		if err != nil {
			t.Logf("Warning: Failed to insert test data into %s: %v", tableName, err)
		}
	case "auth_tokens":
		_, err := db.Exec("INSERT INTO auth_tokens (token, description, created_at, is_active) VALUES (?, ?, ?, ?)",
			"test-token", "Test Token", 1234567890, 1)
		if err != nil {
			t.Logf("Warning: Failed to insert test data into %s: %v", tableName, err)
		}
	case "system_settings":
		_, err := db.Exec("INSERT OR IGNORE INTO system_settings (key, value, value_type, description, default_value, updated_at) VALUES (?, ?, ?, ?, ?, ?)",
			"test_setting", "test_value", "string", "Test Setting", "test_value", 1234567890)
		if err != nil {
			t.Logf("Warning: Failed to insert test data into %s: %v", tableName, err)
		}
	default:
		// 其他表暂时跳过插入测试
		t.Logf("Skipping insert test for %s (foreign key dependencies)", tableName)
	}
}

// testTableRelationships 测试表间关联关系
func testTableRelationships(t *testing.T, db *sql.DB) {
	// 测试外键约束（如果数据库支持）
	// 检查是否支持外键约束
	var foreignKeysSupported bool
	err := db.QueryRow("PRAGMA foreign_keys").Scan(&foreignKeysSupported)
	if err == nil && foreignKeysSupported {
		t.Log("Testing foreign key constraints...")

		// 1. 插入channel
		// 注意：models 和 model_redirects 字段已迁移到 channel_models 表
		result, err := db.Exec("INSERT INTO channels (name, url, channel_type, created_at, updated_at) VALUES (?, ?, ?, ?, ?)",
			"test-channel-rel", "http://example.com", "anthropic", 1234567890, 1234567890)
		if err != nil {
			t.Fatalf("Failed to insert test channel: %v", err)
		}

		channelID, _ := result.LastInsertId()

		// 2. 尝试插入关联的api_key
		_, err = db.Exec("INSERT INTO api_keys (channel_id, key_index, api_key, created_at, updated_at) VALUES (?, ?, ?, ?, ?)",
			channelID, 0, "test-api-key", 1234567890, 1234567890)
		if err != nil {
			t.Fatalf("Failed to insert related api_key: %v", err)
		}

		// 3. 尝试插入不存在的channel_id（应该失败）
		_, err = db.Exec("INSERT INTO api_keys (channel_id, key_index, api_key, created_at, updated_at) VALUES (?, ?, ?, ?, ?)",
			99999, 0, "test-api-key-invalid", 1234567890, 1234567890)
		if err == nil {
			t.Error("Expected foreign key constraint violation for invalid channel_id")
		}

		t.Log("Foreign key constraints working correctly")
	} else {
		t.Log("Foreign key constraints not supported or disabled")
	}
}

// TestTypeConversionCorrectness 测试类型转换的正确性
func TestTypeConversionCorrectness(t *testing.T) {
	testCases := []struct {
		mysqlCol       string
		expectedSQLite string
		description    string
	}{
		{"INT PRIMARY KEY AUTO_INCREMENT", "INTEGER PRIMARY KEY AUTOINCREMENT", "Auto increment primary key"},
		{"INT NOT NULL", "INTEGER NOT NULL", "Integer column"},
		{"BIGINT NOT NULL", "INTEGER NOT NULL", "Big integer column"}, // [FIX] P3: BIGINT应转换为INTEGER
		{"VARCHAR(191) NOT NULL", "TEXT NOT NULL", "Varchar column"},
		{"TEXT NOT NULL", "TEXT NOT NULL", "Text column (unchanged)"},
		{"TINYINT NOT NULL DEFAULT 1", "INTEGER NOT NULL DEFAULT 1", "Tinyint column"},
		{"DOUBLE NOT NULL DEFAULT 0.0", "REAL NOT NULL DEFAULT 0.0", "Double column"},
		{"VARCHAR(255) UNIQUE", "TEXT UNIQUE", "Varchar with unique constraint"},
		{"INT PRIMARY KEY", "INTEGER PRIMARY KEY", "Primary key without auto increment"},
	}

	for _, tc := range testCases {
		t.Run(tc.description, func(t *testing.T) {
			// 模拟TableBuilder
			builder := NewTable("test").
				Column(tc.mysqlCol)

			sqliteDDL := builder.BuildSQLite()

			// 验证转换结果
			if !strings.Contains(sqliteDDL, tc.expectedSQLite) {
				t.Errorf("Expected %s in SQLite DDL, but got:\n%s", tc.expectedSQLite, sqliteDDL)
			}

			// 确保原始类型不存在（除非预期保持不变）
			if tc.expectedSQLite != tc.mysqlCol && strings.Contains(sqliteDDL, tc.mysqlCol) {
				t.Errorf("Original MySQL type %s should not appear in SQLite DDL", tc.mysqlCol)
			}
		})
	}
}

// TestIndexGeneration 测试索引生成的正确性
func TestIndexGeneration(t *testing.T) {
	builder := NewTable("test_table").
		Column("id INT PRIMARY KEY AUTO_INCREMENT").
		Column("name VARCHAR(191) NOT NULL").
		Column("created_at BIGINT NOT NULL").
		Index("idx_name", "name").
		Index("idx_created_at", "created_at DESC")

	// 测试MySQL索引
	mysqlIndexes := builder.GetIndexesMySQL()
	if len(mysqlIndexes) != 2 {
		t.Errorf("Expected 2 MySQL indexes, got %d", len(mysqlIndexes))
	}

	// 验证MySQL索引内容
	for _, idx := range mysqlIndexes {
		if !strings.Contains(idx.SQL, "CREATE INDEX") {
			t.Errorf("MySQL index should contain 'CREATE INDEX': %s", idx.SQL)
		}
		if strings.Contains(idx.SQL, "IF NOT EXISTS") {
			t.Errorf("MySQL index should not contain 'IF NOT EXISTS': %s", idx.SQL)
		}
	}

	// 测试SQLite索引
	sqliteIndexes := builder.GetIndexesSQLite()
	if len(sqliteIndexes) != 2 {
		t.Errorf("Expected 2 SQLite indexes, got %d", len(sqliteIndexes))
	}

	// 验证SQLite索引内容
	for _, idx := range sqliteIndexes {
		if !strings.Contains(idx.SQL, "CREATE INDEX IF NOT EXISTS") {
			t.Errorf("SQLite index should contain 'CREATE INDEX IF NOT EXISTS': %s", idx.SQL)
		}
	}
}

// TestBuilderChain 验证Builder链式调用
func TestBuilderChain(t *testing.T) {
	builder := NewTable("test").
		Column("id INT PRIMARY KEY AUTO_INCREMENT").
		Column("name VARCHAR(100) NOT NULL").
		Index("idx_name", "name")

	if builder.name != "test" {
		t.Errorf("Table name not set correctly")
	}

	if len(builder.columns) != 2 {
		t.Errorf("Expected 2 columns, got %d", len(builder.columns))
	}

	if len(builder.indexes) != 1 {
		t.Errorf("Expected 1 index, got %d", len(builder.indexes))
	}

	// 测试MySQL DDL
	mysqlDDL := builder.BuildMySQL()
	if !strings.Contains(mysqlDDL, "CREATE TABLE IF NOT EXISTS test") {
		t.Errorf("MySQL DDL should contain table creation statement")
	}

	// 测试SQLite DDL
	sqliteDDL := builder.BuildSQLite()
	if !strings.Contains(sqliteDDL, "CREATE TABLE IF NOT EXISTS test") {
		t.Errorf("SQLite DDL should contain table creation statement")
	}
}