package db

import (
	"context"
	"fmt"
	"log/slog"
	"os"
	"path/filepath"
	"sort"

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

func NewPool(ctx context.Context, databaseURL string) (*pgxpool.Pool, error) {
	config, err := pgxpool.ParseConfig(databaseURL)
	if err != nil {
		return nil, fmt.Errorf("parse db config: %w", err)
	}
	config.MaxConns = 10
	return pgxpool.NewWithConfig(ctx, config)
}

func RunMigrations(ctx context.Context, pool *pgxpool.Pool) error {
	// Acquire a cluster-wide advisory lock so concurrent replicas don't race.
	if _, err := pool.Exec(ctx, "SELECT pg_advisory_lock(8675309)"); err != nil {
		return fmt.Errorf("acquire advisory lock: %w", err)
	}
	defer pool.Exec(ctx, "SELECT pg_advisory_unlock(8675309)") //nolint:errcheck

	_, err := pool.Exec(ctx, `
		CREATE TABLE IF NOT EXISTS _schema_migrations (
			name       TEXT        PRIMARY KEY,
			applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
		)
	`)
	if err != nil {
		return fmt.Errorf("create migrations table: %w", err)
	}

	files, err := filepath.Glob("./migrations/*.sql")
	if err != nil {
		return fmt.Errorf("glob migrations: %w", err)
	}
	sort.Strings(files)

	for _, f := range files {
		name := filepath.Base(f)

		var count int
		_ = pool.QueryRow(ctx,
			"SELECT COUNT(*) FROM _schema_migrations WHERE name = $1", name,
		).Scan(&count)
		if count > 0 {
			continue
		}

		sql, err := os.ReadFile(f)
		if err != nil {
			return fmt.Errorf("read migration %s: %w", name, err)
		}
		if _, err := pool.Exec(ctx, string(sql)); err != nil {
			return fmt.Errorf("run migration %s: %w", name, err)
		}
		if _, err := pool.Exec(ctx,
			"INSERT INTO _schema_migrations (name) VALUES ($1)", name,
		); err != nil {
			return fmt.Errorf("record migration %s: %w", name, err)
		}

		slog.Info("applied migration", "name", name)
	}

	return nil
}
