package db

import (
	"context"
	"embed"
	"fmt"
	"sort"
	"strings"

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

//go:embed migrations/*.sql
var migrations embed.FS

// EmbeddedMigrations exposes the raw embedded FS for tests.
var EmbeddedMigrations = migrations

// ApplyMigrations runs every embedded migration that has not yet been
// recorded in projax.schema_migrations. Migrations are applied in lexicographic
// filename order; each successful apply inserts a row in schema_migrations so
// subsequent boots short-circuit without re-running. This lets later
// migrations destructively drop columns earlier ones created — historical
// idempotency-on-re-run is no longer required.
func ApplyMigrations(ctx context.Context, pool *pgxpool.Pool) error {
	// Bootstrap the tracker. Lives in the projax schema so it shares grants
	// with the rest of the surface. CREATE SCHEMA IF NOT EXISTS would fail
	// without database-level CREATE; the tracker only creates within a
	// schema we already own.
	if _, err := pool.Exec(ctx, `
		create table if not exists projax.schema_migrations (
			name        text primary key,
			applied_at  timestamptz not null default now()
		)`); err != nil {
		return fmt.Errorf("ensure schema_migrations: %w", err)
	}

	applied := map[string]struct{}{}
	rows, err := pool.Query(ctx, `select name from projax.schema_migrations`)
	if err != nil {
		return fmt.Errorf("read applied migrations: %w", err)
	}
	for rows.Next() {
		var n string
		if err := rows.Scan(&n); err != nil {
			rows.Close()
			return fmt.Errorf("scan applied: %w", err)
		}
		applied[n] = struct{}{}
	}
	rows.Close()

	entries, err := migrations.ReadDir("migrations")
	if err != nil {
		return fmt.Errorf("read migrations dir: %w", err)
	}
	names := make([]string, 0, len(entries))
	for _, e := range entries {
		if e.IsDir() || !strings.HasSuffix(e.Name(), ".sql") {
			continue
		}
		names = append(names, e.Name())
	}
	sort.Strings(names)

	for _, name := range names {
		if _, ok := applied[name]; ok {
			continue
		}
		body, err := migrations.ReadFile("migrations/" + name)
		if err != nil {
			return fmt.Errorf("read %s: %w", name, err)
		}
		if _, err := pool.Exec(ctx, string(body)); err != nil {
			return fmt.Errorf("apply %s: %w", name, err)
		}
		if _, err := pool.Exec(ctx,
			`insert into projax.schema_migrations (name) values ($1) on conflict (name) do nothing`,
			name,
		); err != nil {
			return fmt.Errorf("record applied %s: %w", name, err)
		}
	}
	return nil
}