//go:build integration

package persistence

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

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
)

// setupTestDB ensures we have a valid repository connected to the test DB
func setupTestDB(t *testing.T) *SchemaRepository {
	// Assume .env is loaded by init() in init_test.go
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		os.Getenv("TIDB_USER"),
		os.Getenv("TIDB_PASSWORD"),
		os.Getenv("TIDB_HOST"),
		os.Getenv("TIDB_PORT"),
		os.Getenv("TIDB_DATABASE"),
	)
	db, err := sql.Open("mysql", dsn)
	require.NoError(t, err, "failed to connect to DB")

	// Ping to verify
	err = db.Ping()
	// If ping fails, skip test (avoid failing build if no local DB)
	if err != nil {
		t.Skipf("Skipping integration test: database not available: %v", err)
	}

	// Ensure Core Schema exists (Robustness for parallel tests)
	// We must use a single connection to ensure SET FOREIGN_KEY_CHECKS applies to subsequent statements
	ctx := context.Background()
	conn, err := db.Conn(ctx)
	if err != nil {
		t.Fatalf("Failed to open connection for setup: %v", err)
	}
	defer conn.Close()

	conn.ExecContext(ctx, "SET FOREIGN_KEY_CHECKS = 0")

	// Dependencies: Find all tables referencing _System_Object and DROP them
	// Also find any table with constraint 'fk_1' because constraint names are global/unique
	// and a previous schema used the generic constraint name 'fk_1'.
	dropDependents := func(refTable string, constraintName string) {
		var rows *sql.Rows
		var err error
		if refTable != "" {
			rows, err = conn.QueryContext(ctx, "SELECT TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = ? AND TABLE_SCHEMA = DATABASE()", refTable)
		} else if constraintName != "" {
			rows, err = conn.QueryContext(ctx, "SELECT TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME = ? AND TABLE_SCHEMA = DATABASE()", constraintName)
		}

		if err != nil {
			t.Logf("Failed to query cleanup targets: %v", err)
			return
		}
		var tablesToDrop []string
		for rows.Next() {
			var tName string
			if err := rows.Scan(&tName); err == nil {
				tablesToDrop = append(tablesToDrop, tName)
			}
		}
		rows.Close()

		for _, tbl := range tablesToDrop {
			conn.ExecContext(ctx, fmt.Sprintf("DROP TABLE IF EXISTS %s", tbl))
		}
	}

	dropDependents("", "fk_1") // Nuke all tables using fk_1 generic name
	dropDependents("_System_Object", "")
	dropDependents("_System_Field", "")
	dropDependents("_System_Table", "") // Just in case

	// Also drop explicit blockers discovered earlier
	conn.ExecContext(ctx, "DROP TABLE IF EXISTS _System_Table")
	conn.ExecContext(ctx, "DROP TABLE IF EXISTS _System_Field")
	conn.ExecContext(ctx, "DROP TABLE IF EXISTS _System_Object")

	_, err = conn.ExecContext(ctx, `CREATE TABLE _System_Object (
		Id VARCHAR(255) PRIMARY KEY,
		ApiName VARCHAR(255),
		Label VARCHAR(255),
		PluralLabel VARCHAR(255),
		KeyPrefix VARCHAR(3),
		IsCustom TINYINT DEFAULT 1,
		Searchable TINYINT DEFAULT 1,
		Description TEXT,
		SharingModel VARCHAR(50),
		IsCustomized TINYINT DEFAULT 0,
		EnableHierarchySharing TINYINT(1) DEFAULT 0,
		RequiredWritePermission VARCHAR(255),
		RequiredReadPermission VARCHAR(255),
		AccessPolicy JSON,
		ThemeColor VARCHAR(50),
		IconName VARCHAR(50),
		PathField VARCHAR(255),
		TableType VARCHAR(50),
		Category VARCHAR(50),
		AppId VARCHAR(255),
		Icon VARCHAR(255),
		ManageableState VARCHAR(50),
		NamespacePrefix VARCHAR(50),
		ListFields TEXT,
		ChildCloneRules JSON,
		SearchLayout TEXT,
		CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
		IsDeleted TINYINT DEFAULT 0,
		OwnerId VARCHAR(255),
		CreatedById VARCHAR(255),
		LastModifiedById VARCHAR(255),
		LastModifiedDate DATETIME DEFAULT CURRENT_TIMESTAMP
	)`)
	if err != nil {
		t.Logf("Failed to recreate _System_Object: %v", err)
	}

	_, err = conn.ExecContext(ctx, `CREATE TABLE _System_Field (
		Id VARCHAR(255) PRIMARY KEY,
		ObjectId VARCHAR(255),
		ApiName VARCHAR(255),
		Label VARCHAR(255),
		Type VARCHAR(255),
		Required TINYINT DEFAULT 0,
		IsUnique TINYINT DEFAULT 0,
		DefaultValue TEXT,
		HelpText TEXT,
		Description TEXT,
		IsSystem TINYINT DEFAULT 0,
		IsNameField TINYINT DEFAULT 0,
		Createable TINYINT DEFAULT 1,
		Updateable TINYINT DEFAULT 1,
		Indexed TINYINT DEFAULT 0,
		Options TEXT,
		MinLength BIGINT,
		MaxLength BIGINT,
		Regex TEXT,
		RegexMessage TEXT,
		Validator TEXT,
		ReferenceTo TEXT,
		Formula TEXT,
		ReturnType VARCHAR(50),
		FormulaMode VARCHAR(50),
		RollupConfig TEXT,
		PicklistDependency TEXT,
		TrackHistory TINYINT DEFAULT 0,
		`+"`MinValue`"+` DECIMAL(18,4),
		`+"`MaxValue`"+` DECIMAL(18,4),
		IsMasterDetail TINYINT DEFAULT 0,
		IsPolymorphic TINYINT DEFAULT 0,
		DeleteRule VARCHAR(50),
		RelationshipName VARCHAR(255),
		ControllingField VARCHAR(255),
		PhysicalName VARCHAR(255),
		CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
		IsDeleted TINYINT DEFAULT 0,
		IsCustomized TINYINT DEFAULT 0,
		OwnerId VARCHAR(255),
		CreatedById VARCHAR(255),
		LastModifiedById VARCHAR(255),
		LastModifiedDate DATETIME DEFAULT CURRENT_TIMESTAMP
	)`)
	if err != nil {
		t.Logf("Failed to recreate _System_Field: %v", err)
	}
	conn.ExecContext(ctx, "SET FOREIGN_KEY_CHECKS = 1")

	// Restore Report if it was nuked (needed for Dashboard tests)

	// Force cleanup of Report to ensure schema is fresh (avoid stale schema from passing checks)
	conn.ExecContext(ctx, "DROP TABLE IF EXISTS Report")
	_, err = conn.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS Report (
		Id VARCHAR(255) PRIMARY KEY,
		OwnerId VARCHAR(255),
		Name VARCHAR(255),
		DeveloperName VARCHAR(255),
		SourceObject VARCHAR(255),
		ReportType VARCHAR(50),
		Description TEXT,
		Query JSON,
		ChartSettings JSON,
		FolderId VARCHAR(255),
		CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
		LastModifiedDate DATETIME DEFAULT CURRENT_TIMESTAMP
	)`)
	if err != nil {
		t.Logf("Failed to recreate Report: %v", err)
	}

	// Restore _System_Table (needed for MetadataLifecycle tests)

	// Force cleanup
	conn.ExecContext(ctx, "DROP TABLE IF EXISTS _System_Table")
	_, err = conn.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS _System_Table (
		Id VARCHAR(255) PRIMARY KEY,
		TableName VARCHAR(255) UNIQUE,
		TableType VARCHAR(50) DEFAULT 'custom_object',
		Category VARCHAR(50),
		Description TEXT,
		IsManaged TINYINT DEFAULT 1,
		SchemaVersion VARCHAR(50) DEFAULT '1.0.0',
		CreatedBy VARCHAR(255) DEFAULT 'bootstrap',
		CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
		IsDeleted TINYINT DEFAULT 0,
		LastModifiedDate DATETIME DEFAULT CURRENT_TIMESTAMP
	)`)
	if err != nil {
		t.Logf("Failed to recreate _System_Table: %v", err)
	}

	// Ensure _System_Object has DeleteMode column (added by this PR; CI DB may lack it)
	conn.ExecContext(ctx, "ALTER TABLE `_System_Object` ADD COLUMN IF NOT EXISTS `DeleteMode` VARCHAR(50) NULL")

	return NewSchemaRepository(db, nil)
}

func TestBatchAddColumns_Integration(t *testing.T) {
	if testing.Short() {
		t.Skip("skipping integration test in short mode")
	}

	repo := setupTestDB(t)
	tableName := fmt.Sprintf("test_batch_cols_%d", time.Now().UnixNano())

	// 1. Create a dummy table first
	createTableSQL := fmt.Sprintf("CREATE TABLE `%s` (`%s` VARCHAR(36) PRIMARY KEY, `created_date` DATETIME, `last_modified_date` DATETIME)", tableName, constants.ColID)
	_, err := repo.db.Exec(createTableSQL)
	require.NoError(t, err)
	defer func() {
		// Cleanup
		_, _ = repo.db.Exec(fmt.Sprintf("DROP TABLE IF EXISTS `%s`", tableName))
		// Clean metadata
		_, _ = repo.db.Exec("DELETE FROM _System_Object WHERE ApiName = ?", tableName)
		_, _ = repo.db.Exec("DELETE FROM _System_Field WHERE ObjectId IN (SELECT Id FROM _System_Object WHERE ApiName = ?)", tableName)
	}()

	// Register Object Metadata so BatchAddColumns can resolve ObjectID
	objMeta := &models.ObjectMetadata{
		APIName:     tableName,
		Label:       "Test Batch Object",
		PluralLabel: "Test Batch Objects",
		Description: stringPtr("A test object for batch DDL"),
		IsCustom:    true,
	}
	err = repo.SaveObjectMetadata(context.Background(), objMeta, nil)
	require.NoError(t, err, "failed to register object metadata")

	// 2. Define Batch Fields
	fields := []models.FieldMetadata{
		{
			APIName:  "BatchCol1",
			Label:    "Batch Col 1",
			Type:     constants.FieldTypeString,
			Required: false,
		},
		{
			APIName:      "BatchCol2",
			Label:        "Batch Col 2",
			Type:         constants.FieldTypeDouble, // DECIMAL(18,6)
			Required:     false,
			DefaultValue: stringPtr("100"),
		},
		{
			APIName:  "BatchCol3",
			Label:    "Batch Col 3",
			Type:     constants.FieldTypeBoolean,
			Required: false,
		},
	}

	// 3. Execute BatchAddColumns
	err = repo.BatchAddColumns(tableName, fields)
	assert.NoError(t, err, "BatchAddColumns should succeed")

	// 4. Verify Physical Columns
	for _, f := range fields {
		exists, err := repo.checkColumnExists(tableName, f.APIName)
		assert.NoError(t, err)
		assert.True(t, exists, "Column %s should exist in DB", f.APIName)
	}

	// 5. Verify Metadata
	// Query _System_Field
	// We need to fetch the ObjectID first
	var objectID string
	// Use ColID explicitly when querying _System_Object manually
	err = repo.db.QueryRow(fmt.Sprintf("SELECT %s FROM _System_Object WHERE ApiName = ?", constants.ColID), tableName).Scan(&objectID)
	require.NoError(t, err)

	for _, f := range fields {
		var count int
		err := repo.db.QueryRow("SELECT COUNT(*) FROM _System_Field WHERE ObjectId = ? AND ApiName = ?", objectID, f.APIName).Scan(&count)
		assert.NoError(t, err)
		assert.Equal(t, 1, count, "Field metadata for %s should exist", f.APIName)
	}
}

func TestBatchAddColumns_StrictValidation(t *testing.T) {
	if testing.Short() {
		t.Skip("skipping integration test in short mode")
	}

	repo := setupTestDB(t)
	tableName := fmt.Sprintf("test_strict_%d", time.Now().UnixNano())

	// Create table
	createTableSQL := fmt.Sprintf("CREATE TABLE `%s` (`id` VARCHAR(36) PRIMARY KEY)", tableName)
	_, err := repo.db.Exec(createTableSQL)
	require.NoError(t, err)
	defer func() {
		_, _ = repo.db.Exec(fmt.Sprintf("DROP TABLE IF EXISTS `%s`", tableName))
		_, _ = repo.db.Exec("DELETE FROM _System_Object WHERE ApiName = ?", tableName)
	}()

	// Register metadata (required for BatchAddColumns happy path)
	objMeta := &models.ObjectMetadata{
		APIName:     tableName,
		Label:       "Test Strict",
		PluralLabel: "Test Stricts",
		Description: stringPtr("Strict test"),
		IsCustom:    true,
	}
	err = repo.SaveObjectMetadata(context.Background(), objMeta, nil)
	require.NoError(t, err)

	// Case 1: Invalid Column Name
	t.Run("InvalidColumnName", func(t *testing.T) {
		fields := []models.FieldMetadata{
			{APIName: "Invalid-Name!", Type: constants.FieldTypeString},
		}
		err := repo.BatchAddColumns(tableName, fields)
		assert.Error(t, err)
		assert.Contains(t, err.Error(), "invalid column name")
	})

	// Case 2: SQL Injection in Default Value
	t.Run("SQLInjectionInDefault", func(t *testing.T) {
		maliciousDefault := "'); DROP TABLE Students; --"
		fields := []models.FieldMetadata{
			{
				APIName:      "SafeColInjection",
				Type:         constants.FieldTypeString,
				DefaultValue: &maliciousDefault,
			},
		}
		err := repo.BatchAddColumns(tableName, fields)
		assert.NoError(t, err, "Should sanitize and succeed")

		// Verify it was escaped properly
		// In MySQL/TiDB, we can check COLUMN_DEFAULT in information_schema
		// Defaults are stored as strings (sometimes with quotes, sometimes without)
		// But simpler: insert a row and check the value.
		_, err = repo.db.Exec(fmt.Sprintf("INSERT INTO `%s` (`id`) VALUES ('test_id_1')", tableName))
		assert.NoError(t, err)

		var val string
		err = repo.db.QueryRow(fmt.Sprintf("SELECT `SafeColInjection` FROM `%s` WHERE `id`='test_id_1'", tableName)).Scan(&val)
		assert.NoError(t, err)
		assert.Equal(t, maliciousDefault, val, "Default value should match literal string (escaped correctly)")
	})
}

func stringPtr(s string) *string {
	return &s
}