package database

import (
	"cmp"
	"context"
	"embed"
	"errors"
	"fmt"
	"log/slog"
	"path"
	"slices"
	"strconv"
	"strings"

	"github.com/jackc/pgx/v5"
)

// Migration represents a database migration
type Migration struct {
	Version int
	Name    string
	SQL     string
}

// MigratorConfig configures a migrator instance.
// This allows external libraries (e.g., Enterprise extensions) to provide
// their own migrations while sharing the same schema_migrations table.
type MigratorConfig struct {
	// MigrationFiles is the embedded filesystem containing migration files.
	// The filesystem should contain a directory (named by MigrationDir) with .sql files
	// named using the pattern "NNN_description.sql" (e.g., "001_initial_schema.sql").
	MigrationFiles embed.FS
	// MigrationDir is the directory within MigrationFiles to read migrations from.
	// Defaults to "migrations" when empty.
	MigrationDir string
	// VersionOffset is added to all migration versions to avoid conflicts.
	// Set to 0 for OSS migrations, 500+ for extensions.
	// This allows multiple migration sources to avoid collisions.
	VersionOffset int
	// EnsureTable creates the schema_migrations table if it doesn't exist.
	// Set to true for OSS (creates table), false for extensions (assumes it exists from OSS).
	EnsureTable bool
}

// Migrator handles database migrations.
// It supports configurable migration sources and version offsets to allow
// multiple migration sets (e.g., OSS + extensions) to coexist.
type Migrator struct {
	conn   *pgx.Conn
	config MigratorConfig
	logger *slog.Logger
}

// NewMigrator creates a new migrator instance with the given configuration.
func NewMigrator(conn *pgx.Conn, config MigratorConfig) *Migrator {
	return &Migrator{
		conn:   conn,
		config: config,
		logger: slog.Default().With("component", "database.migrate"),
	}
}

// ensureMigrationsTable creates the migrations tracking table if it doesn't exist
func (m *Migrator) ensureMigrationsTable(ctx context.Context) error {
	query := `
		CREATE TABLE IF NOT EXISTS schema_migrations (
			version INTEGER PRIMARY KEY,
			name VARCHAR(255) NOT NULL,
			applied_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
		)
	`
	_, err := m.conn.Exec(ctx, query)
	return err
}

// getAppliedMigrations returns a map of already applied migration versions
func (m *Migrator) getAppliedMigrations(ctx context.Context) (map[int]struct{}, error) {
	query := "SELECT version FROM schema_migrations ORDER BY version"
	rows, err := m.conn.Query(ctx, query)
	if err != nil {
		return nil, fmt.Errorf("failed to query applied migrations: %w", err)
	}
	defer rows.Close()

	applied := make(map[int]struct{})
	for rows.Next() {
		var version int
		if err := rows.Scan(&version); err != nil {
			return nil, fmt.Errorf("failed to scan migration version: %w", err)
		}
		applied[version] = struct{}{}
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("failed to read migration rows: %w", err)
	}

	return applied, nil
}

// loadMigrations loads all migration files from the embedded filesystem
func (m *Migrator) loadMigrations() ([]Migration, error) {
	dir := cmp.Or(m.config.MigrationDir, "migrations")
	entries, err := m.config.MigrationFiles.ReadDir(dir)
	if err != nil {
		return nil, fmt.Errorf("failed to read migrations directory: %w", err)
	}

	var migrations []Migration
	for _, entry := range entries {
		if entry.IsDir() || !strings.HasSuffix(entry.Name(), ".sql") {
			continue
		}

		// Parse version from filename (e.g., "001_initial_schema.sql" -> version 1)
		name := entry.Name()
		parts := strings.SplitN(name, "_", 2)
		if len(parts) != 2 {
			m.logger.Error("skipping migration file with invalid name format", "name", name)
			continue
		}

		version, err := strconv.Atoi(parts[0])
		if err != nil {
			m.logger.Error("skipping migration file with invalid version", "name", name)
			continue
		}

		// Apply version offset
		offsetVersion := version + m.config.VersionOffset

		// Read the migration SQL
		content, err := m.config.MigrationFiles.ReadFile(path.Join(dir, name))
		if err != nil {
			return nil, fmt.Errorf("failed to read migration file %s: %w", name, err)
		}

		// Generate migration name with offset if offset is applied
		var migrationName string
		if m.config.VersionOffset > 0 {
			// Add offset to name to avoid conflicts with OSS migrations
			migrationName = fmt.Sprintf("%d_%s", offsetVersion, parts[1])
		} else {
			migrationName = name
		}

		migrations = append(migrations, Migration{
			Version: offsetVersion,
			Name:    strings.TrimSuffix(migrationName, ".sql"),
			SQL:     string(content),
		})
	}

	// Sort migrations by version
	slices.SortFunc(migrations, func(a, b Migration) int {
		return cmp.Compare(a.Version, b.Version)
	})

	return migrations, nil
}

// Migrate runs all pending migrations.
func (m *Migrator) Migrate(ctx context.Context) error {
	// Ensure the migrations table exists if configured
	if m.config.EnsureTable {
		if err := m.ensureMigrationsTable(ctx); err != nil {
			return fmt.Errorf("failed to create migrations table: %w", err)
		}
	}

	// Get applied migrations
	applied, err := m.getAppliedMigrations(ctx)
	if err != nil {
		return fmt.Errorf("failed to get applied migrations: %w", err)
	}

	// Load all migration files
	migrations, err := m.loadMigrations()
	if err != nil {
		return fmt.Errorf("failed to load migrations: %w", err)
	}

	// Find pending migrations
	var pending []Migration
	for _, migration := range migrations {
		if _, ok := applied[migration.Version]; !ok {
			pending = append(pending, migration)
		}
	}

	if len(pending) == 0 {
		m.logger.Info("no pending migrations")
		return nil
	}

	m.logger.Info("applying pending migrations", "count", len(pending))

	// Apply each pending migration in a transaction
	for _, migration := range pending {
		if err := m.applyMigration(ctx, migration); err != nil {
			return fmt.Errorf("failed to apply migration %s (v%d): %w", migration.Name, migration.Version, err)
		}
		m.logger.Info("applied migration", "version", migration.Version, "name", migration.Name)
	}

	m.logger.Info("all migrations applied successfully")
	return nil
}

// applyMigration applies a single migration in a transaction
func (m *Migrator) applyMigration(ctx context.Context, migration Migration) error {
	tx, err := m.conn.Begin(ctx)
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() {
		// Rollback is safe to be called after a transaction is committed, where it won't be rolled back (ErrTxClosed).
		if err := tx.Rollback(ctx); err != nil && !errors.Is(err, pgx.ErrTxClosed) {
			m.logger.Error("failed to rollback migration transaction", "error", err)
		}
	}()

	// Execute the migration SQL
	_, err = tx.Exec(ctx, migration.SQL)
	if err != nil {
		return fmt.Errorf("failed to execute migration SQL: %w", err)
	}

	// Record the migration as applied
	_, err = tx.Exec(ctx,
		"INSERT INTO schema_migrations (version, name) VALUES ($1, $2)",
		migration.Version, migration.Name)
	if err != nil {
		return fmt.Errorf("failed to record migration: %w", err)
	}

	return tx.Commit(ctx)
}
