package database

import (
	"context"
	"fmt"
	"os"
	"path/filepath"
	"testing"

	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
	"gorm.io/gorm"

	"github.com/gochat/gochat/internal/repository"
)

func TestInboxAssignmentPolicyProductionMigration(t *testing.T) {
	db, dbURL := openUploadMigrationPostgres(t)
	migrations := productionMigrationsPath(t)
	require.NoError(t, MigrateSteps(dbURL, migrations, 85))
	assert.False(t, db.Migrator().HasTable("inbox_assignment_policies"))

	var accountID, inboxID, policyID uint
	require.NoError(t, db.Raw("INSERT INTO accounts(name) VALUES (?) RETURNING id", "assignment migration account").Scan(&accountID).Error)
	require.NoError(t, db.Raw(`INSERT INTO inboxes(account_id, name, channel_type, channel_id)
		VALUES (?, 'assignment migration inbox', 'web_widget', 1) RETURNING id`, accountID).Scan(&inboxID).Error)
	require.NoError(t, db.Raw(`INSERT INTO assignment_policies(account_id, inbox_id, policy_type)
		VALUES (?, ?, 'round_robin') RETURNING id`, accountID, inboxID).Scan(&policyID).Error)

	require.NoError(t, MigrateSteps(dbURL, migrations, 1))
	assert.True(t, db.Migrator().HasTable("inbox_assignment_policies"))
	assert.True(t, db.Migrator().HasIndex("assignment_policies", "idx_account_policy_name"))
	assert.True(t, db.Migrator().HasIndex("inbox_assignment_policies", "idx_inbox_assignment_policies_inbox_id"))
	assert.True(t, db.Migrator().HasIndex("inbox_assignment_policies", "idx_inbox_assignment_policies_assignment_policy_id"))

	policy, err := repository.NewInboxAssignmentPolicyRepo(db).FindPolicyByInbox(context.Background(), accountID, inboxID)
	require.NoError(t, err)
	assert.Equal(t, policyID, policy.ID)
	assert.Equal(t, "round_robin "+fmt.Sprint(policyID), policy.Name)

	var newInboxID, newPolicyID uint
	require.NoError(t, db.Raw(`INSERT INTO inboxes(account_id, name, channel_type, channel_id)
		VALUES (?, 'post-up inbox', 'web_widget', 2) RETURNING id`, accountID).Scan(&newInboxID).Error)
	require.NoError(t, db.Raw(`INSERT INTO assignment_policies(account_id, name, description)
		VALUES (?, 'post-up policy', 'must survive rollback') RETURNING id`, accountID).Scan(&newPolicyID).Error)
	require.NoError(t, db.Exec("INSERT INTO inbox_assignment_policies(inbox_id, assignment_policy_id) VALUES (?, ?)", newInboxID, newPolicyID).Error)

	down, err := os.ReadFile(filepath.Join("..", "..", "migrations", "000086_repair_assignment_policy_schema.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 000086 is irreversible")
	assert.True(t, db.Migrator().HasTable("inbox_assignment_policies"))
	assert.True(t, db.Migrator().HasColumn("assignment_policies", "description"))

	var description string
	require.NoError(t, db.Raw("SELECT description FROM assignment_policies WHERE id = ?", newPolicyID).Scan(&description).Error)
	assert.Equal(t, "must survive rollback", description)
	var links int64
	require.NoError(t, db.Table("inbox_assignment_policies").Where("inbox_id = ? AND assignment_policy_id = ?", newInboxID, newPolicyID).Count(&links).Error)
	assert.Equal(t, int64(1), links)
}

func TestInboxAssignmentPolicyMigrationRejectsDuplicateLegacyBindings(t *testing.T) {
	db, dbURL := openUploadMigrationPostgres(t)
	migrations := productionMigrationsPath(t)
	require.NoError(t, MigrateSteps(dbURL, migrations, 85))

	var accountID, inboxID uint
	require.NoError(t, db.Raw("INSERT INTO accounts(name) VALUES (?) RETURNING id", "duplicate migration account").Scan(&accountID).Error)
	require.NoError(t, db.Raw(`INSERT INTO inboxes(account_id, name, channel_type, channel_id)
		VALUES (?, 'duplicate migration inbox', 'web_widget', 1) RETURNING id`, accountID).Scan(&inboxID).Error)
	for _, policyType := range []string{"round_robin", "balanced"} {
		require.NoError(t, db.Exec(`INSERT INTO assignment_policies(account_id, inbox_id, policy_type)
			VALUES (?, ?, ?)`, accountID, inboxID, policyType).Error)
	}

	err := MigrateSteps(dbURL, migrations, 1)
	require.ErrorContains(t, err, "multiple legacy assignment policies target one inbox")
}