package profile

import (
	"testing"

	"github.com/stretchr/testify/require"
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"

	domainProfile "base/internal/domain/profile"
)

// setupTestDB creates an in-memory SQLite database for testing
func setupTestDB(t *testing.T) *gorm.DB {
	db, err := gorm.Open(sqlite.Open(":memory:"), &gorm.Config{
		DisableForeignKeyConstraintWhenMigrating: true,
	})
	require.NoError(t, err)

	// Create tables manually with SQLite-compatible syntax
	// This avoids PostgreSQL-specific syntax like gen_random_uuid() and timestamptz

	createProfilesTable := `
		CREATE TABLE IF NOT EXISTS profiles (
			id TEXT PRIMARY KEY,
			user_id TEXT,
			handle TEXT NOT NULL,
			role_id TEXT,
			role_name TEXT,
			first_name TEXT,
			last_name TEXT,
			company TEXT,
			short_description TEXT,
			resume_link TEXT,
			cta_enabled INTEGER NOT NULL DEFAULT 0,
			avatar TEXT,
			profile_picture TEXT,
			about TEXT,
			email TEXT,
			phone TEXT,
			visibility_level TEXT NOT NULL DEFAULT 'public',
			page_section_order TEXT,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			deleted_at DATETIME,
			UNIQUE(handle)
		)
	`
	require.NoError(t, db.Exec(createProfilesTable).Error)
	require.NoError(t, db.Exec("CREATE INDEX IF NOT EXISTS profiles_user_id_idx ON profiles(user_id)").Error)
	require.NoError(t, db.Exec("CREATE INDEX IF NOT EXISTS profiles_role_id_idx ON profiles(role_id)").Error)
	require.NoError(t, db.Exec("CREATE INDEX IF NOT EXISTS profiles_name_idx ON profiles(first_name, last_name)").Error)
	require.NoError(t, db.Exec("CREATE INDEX IF NOT EXISTS profiles_company_idx ON profiles(company)").Error)
	require.NoError(t, db.Exec("CREATE INDEX IF NOT EXISTS profiles_email_idx ON profiles(email)").Error)

	createProfileSkillsTable := `
		CREATE TABLE IF NOT EXISTS profile_skills (
			id TEXT PRIMARY KEY,
			profile_id TEXT NOT NULL,
			skill_name TEXT NOT NULL,
			level TEXT NOT NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			deleted_at DATETIME
		)
	`
	require.NoError(t, db.Exec(createProfileSkillsTable).Error)
	require.NoError(t, db.Exec("CREATE INDEX IF NOT EXISTS skills_profile_id_idx ON profile_skills(profile_id)").Error)
	require.NoError(t, db.Exec("CREATE INDEX IF NOT EXISTS skills_name_idx ON profile_skills(skill_name)").Error)

	createProfileSocialLinksTable := `
		CREATE TABLE IF NOT EXISTS profile_social_links (
			id TEXT PRIMARY KEY,
			profile_id TEXT NOT NULL,
			link_type TEXT NOT NULL,
			link TEXT NOT NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			deleted_at DATETIME
		)
	`
	require.NoError(t, db.Exec(createProfileSocialLinksTable).Error)
	require.NoError(t, db.Exec("CREATE INDEX IF NOT EXISTS social_links_profile_id_idx ON profile_social_links(profile_id)").Error)

	createProfileAchievementsTable := `
		CREATE TABLE IF NOT EXISTS profile_achievements (
			id TEXT PRIMARY KEY,
			profile_id TEXT NOT NULL,
			title TEXT NOT NULL,
			value TEXT NOT NULL,
			enabled INTEGER NOT NULL DEFAULT 1,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			deleted_at DATETIME
		)
	`
	require.NoError(t, db.Exec(createProfileAchievementsTable).Error)
	require.NoError(t, db.Exec("CREATE INDEX IF NOT EXISTS achievements_profile_id_idx ON profile_achievements(profile_id)").Error)

	return db
}

// createTestProfileRepository creates a profile repository for testing
func createTestProfileRepository(db *gorm.DB) domainProfile.Repository {
	return &profileRepository{db: db}
}