//go:build integration

package persistence

import (
	"context"
	"fmt"
	"testing"
	"time"

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

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

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

	// 1. Create table
	_, err := repo.db.Exec(fmt.Sprintf("CREATE TABLE `%s` (`id` VARCHAR(36) PRIMARY KEY)", tableName))
	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 api_name = ?", tableName)
		_, _ = repo.db.Exec("DELETE FROM _System_Field WHERE object_id IN (SELECT id FROM _System_Object WHERE api_name = ?)", tableName)
	}()

	desc := "Integration Test"
	objMeta := &models.ObjectMetadata{
		APIName:     tableName,
		Label:       "Unique Test",
		PluralLabel: "Unique Tests",
		Description: &desc,
		IsCustom:    true,
	}
	err = repo.SaveObjectMetadata(context.Background(), objMeta, nil)
	require.NoError(t, err)

	// 2. Add Unique Column
	fields := []models.FieldMetadata{
		{
			APIName:  "UniqueCol",
			Type:     constants.FieldTypeString,
			IsUnique: true,
		},
	}

	err = repo.BatchAddColumns(tableName, fields)
	assert.NoError(t, err, "BatchAddColumns should succeed with Unique constraint")

	// 3. Verify Constraint Enforcement
	// Insert Record 1
	_, err = repo.db.Exec(fmt.Sprintf("INSERT INTO `%s` (`id`, `UniqueCol`) VALUES ('1', 'A')", tableName))
	assert.NoError(t, err, "First insert should succeed")

	// Insert Record 2 (Duplicate Unique Col)
	_, err = repo.db.Exec(fmt.Sprintf("INSERT INTO `%s` (`id`, `UniqueCol`) VALUES ('2', 'A')", tableName))
	assert.Error(t, err, "Duplicate insert should fail")
	// Verify error message if possible (DB dependent, usually "Duplicate entry")
	// assert.Contains(t, err.Error(), "Duplicate entry")

	// Insert Record 3 (Different Unique Col)
	_, err = repo.db.Exec(fmt.Sprintf("INSERT INTO `%s` (`id`, `UniqueCol`) VALUES ('3', 'B')", tableName))
	assert.NoError(t, err, "Distinct insert should succeed")
}

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

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

	// 1. Create table with INT non-unique column
	_, err := repo.db.Exec(fmt.Sprintf("CREATE TABLE `%s` (`id` VARCHAR(36) PRIMARY KEY, `StartCol` INT)", tableName))
	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 api_name = ?", tableName)
		_, _ = repo.db.Exec("DELETE FROM _System_Field WHERE object_id IN (SELECT id FROM _System_Object WHERE api_name = ?)", tableName)
	}()

	desc := "Mod Test"
	objMeta := &models.ObjectMetadata{
		APIName:     tableName,
		Label:       "Mod Test",
		PluralLabel: "Mod Tests",
		Description: &desc,
		IsCustom:    true,
	}
	err = repo.SaveObjectMetadata(context.Background(), objMeta, nil)
	require.NoError(t, err)

	// 2. BatchAddColumns with TEXT + Unique (Trigger Modify + Unique Add)
	fields := []models.FieldMetadata{
		{
			APIName:  "StartCol",
			Type:     constants.FieldTypeString, // Change INT -> TEXT
			IsUnique: true,                      // Add Unique
		},
	}

	err = repo.BatchAddColumns(tableName, fields)
	assert.NoError(t, err, "BatchAddColumns should succeed (Modify + Unique)")

	// 3. Verify Uniqueness (and implicitly type change, allowing 'A')
	_, err = repo.db.Exec(fmt.Sprintf("INSERT INTO `%s` (`id`, `StartCol`) VALUES ('1', 'A')", tableName))
	assert.NoError(t, err, "Insert string 'A' should succeed (Type changed to TEXT)")

	_, err = repo.db.Exec(fmt.Sprintf("INSERT INTO `%s` (`id`, `StartCol`) VALUES ('2', 'A')", tableName))
	assert.Error(t, err, "Duplicate insert should fail after Modify")
}