package database

import (
	"os"
	"path/filepath"
	"testing"
	"time"

	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

func TestCaptainBindingMigrationDeduplicatesAndRejectsDown(t *testing.T) {
	if os.Getenv("GOCHAT_TEST_DB") == "sqlite" {
		t.Skip("requires PostgreSQL migration semantics")
	}
	dsn := os.Getenv("GOCHAT_TEST_DB_URL")
	if dsn == "" {
		dsn = "host=localhost port=5432 user=postgres password=postgres dbname=gochat_test sslmode=disable"
	}
	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{Logger: logger.Default.LogMode(logger.Silent)})
	require.NoError(t, err)
	sqlDB, err := db.DB()
	require.NoError(t, err)
	sqlDB.SetMaxOpenConns(1)
	t.Cleanup(func() { _ = sqlDB.Close() })

	schema := "captain_migration_" + time.Now().Format("20060102150405000000000")
	require.NoError(t, db.Exec("CREATE SCHEMA "+schema).Error)
	t.Cleanup(func() { _ = db.Exec("DROP SCHEMA " + schema + " CASCADE").Error })
	require.NoError(t, db.Exec("SET search_path TO "+schema).Error)
	require.NoError(t, db.Exec(`
		CREATE TABLE agent_bots (id BIGINT PRIMARY KEY, account_id BIGINT, bot_type TEXT, config JSONB);
		CREATE TABLE agent_bot_inboxes (id BIGINT PRIMARY KEY, agent_bot_id BIGINT, inbox_id BIGINT);
		CREATE TABLE captain_inboxes (id BIGINT PRIMARY KEY, inbox_id BIGINT, deleted_at TIMESTAMPTZ);
		CREATE TABLE conversations (id BIGINT PRIMARY KEY, assignee_agent_bot_id BIGINT);
		CREATE TABLE agent_bot_presence_events (id BIGINT PRIMARY KEY, agent_bot_id BIGINT);
		CREATE TABLE bot_rules (id BIGINT PRIMARY KEY, agent_bot_id BIGINT);
		CREATE TABLE bot_trigger_configs (id BIGINT PRIMARY KEY, agent_bot_id BIGINT);
		INSERT INTO agent_bots VALUES
			(10, 1, 'captain', '{"assistant_id":7}'),
			(11, 1, 'captain', '{"assistant_id":7}');
		INSERT INTO agent_bot_inboxes VALUES (20, 10, 42), (21, 11, 42);
		INSERT INTO captain_inboxes VALUES (30, 42, NULL), (31, 42, NULL);
		INSERT INTO conversations VALUES (40, 11);
		INSERT INTO agent_bot_presence_events VALUES (50, 11);
		INSERT INTO bot_rules VALUES (60, 11);
		INSERT INTO bot_trigger_configs VALUES (70, 11);
	`).Error)

	up, err := os.ReadFile(filepath.Join("..", "..", "migrations", "000079_make_captain_bindings_unique.up.sql"))
	require.NoError(t, err)
	require.NoError(t, db.Transaction(func(tx *gorm.DB) error { return tx.Exec(string(up)).Error }))

	for _, table := range []string{"agent_bots", "agent_bot_inboxes", "captain_inboxes"} {
		var count int64
		require.NoError(t, db.Table(table).Count(&count).Error)
		assert.Equal(t, int64(1), count, table)
	}
	for table, column := range map[string]string{
		"agent_bot_inboxes":         "agent_bot_id",
		"conversations":             "assignee_agent_bot_id",
		"agent_bot_presence_events": "agent_bot_id",
		"bot_rules":                 "agent_bot_id",
		"bot_trigger_configs":       "agent_bot_id",
	} {
		var botID int64
		require.NoError(t, db.Table(table).Select(column).Scan(&botID).Error)
		assert.Equal(t, int64(10), botID, table)
	}
	for _, index := range []string{
		"idx_agent_bots_captain_assistant",
		"idx_agent_bot_inboxes_bot_inbox",
		"idx_captain_inboxes_active_inbox",
	} {
		var unique bool
		require.NoError(t, db.Raw("SELECT indisunique FROM pg_index WHERE indexrelid = to_regclass(?)", index).Scan(&unique).Error)
		assert.True(t, unique, index)
	}

	down, err := os.ReadFile(filepath.Join("..", "..", "migrations", "000079_make_captain_bindings_unique.down.sql"))
	require.NoError(t, err)
	err = db.Transaction(func(tx *gorm.DB) error { return tx.Exec(string(down)).Error })
	require.ErrorContains(t, err, "migration 000079 is irreversible")
	assert.True(t, db.Migrator().HasColumn("agent_bots", "captain_assistant_id"))
}