package db

import (
	"context"
	"fmt"
	"strconv"
	"time"

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

// PoolOptions tunes pgxpool for API/worker processes.
//
// Sizing guidance (multi-instance): MaxConns ≈ (Postgres max_connections − reserved) / instance_count.
// Typical per-process MaxConns is 20–50; MinConns ≈ 10–30% of MaxConns (warm floor).
// Always set MaxConnLifetimeJitter (~10–20% of MaxConnLifetime) to avoid thundering-herd reconnects.
// Defaults match historical NewPool hardcodes, plus idle recycle + statement timeout for multi-tenant churn.
type PoolOptions struct {
	MaxConns        int32
	MinConns        int32
	MaxConnLifetime time.Duration
	// MaxConnLifetimeJitter adds random extra lifetime per connection (pgxpool); 0 disables.
	MaxConnLifetimeJitter time.Duration
	MaxConnIdleTime       time.Duration
	HealthCheckPeriod     time.Duration
	// StatementTimeout sets Postgres statement_timeout on each connection (0 disables).
	StatementTimeout time.Duration
}

// DefaultPoolOptions returns safe production-oriented defaults used when config omits overrides.
func DefaultPoolOptions() PoolOptions {
	return PoolOptions{
		MaxConns:              20,
		MinConns:              2,
		MaxConnLifetime:       time.Hour,
		MaxConnLifetimeJitter: 6 * time.Minute, // ~10% of lifetime
		MaxConnIdleTime:       5 * time.Minute,
		HealthCheckPeriod:     time.Minute,
		StatementTimeout:      30 * time.Second,
	}
}

// NewPool opens a pgx pool with sizing/timeouts from opts (see DefaultPoolOptions).
func NewPool(ctx context.Context, databaseURL string, opts PoolOptions) (*pgxpool.Pool, error) {
	cfg, err := ParsePoolConfig(databaseURL, opts)
	if err != nil {
		return nil, err
	}

	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	if err != nil {
		return nil, fmt.Errorf("connect database: %w", err)
	}
	if err := pool.Ping(ctx); err != nil {
		pool.Close()
		return nil, fmt.Errorf("ping database: %w", err)
	}
	return pool, nil
}

// ParsePoolConfig builds a pgxpool.Config without connecting (unit-testable).
func ParsePoolConfig(databaseURL string, opts PoolOptions) (*pgxpool.Config, error) {
	cfg, err := pgxpool.ParseConfig(databaseURL)
	if err != nil {
		return nil, fmt.Errorf("parse database url: %w", err)
	}
	applyPoolOptions(cfg, opts)
	return cfg, nil
}

func applyPoolOptions(cfg *pgxpool.Config, opts PoolOptions) {
	cfg.MaxConns = opts.MaxConns
	cfg.MinConns = opts.MinConns
	cfg.MaxConnLifetime = opts.MaxConnLifetime
	cfg.MaxConnLifetimeJitter = opts.MaxConnLifetimeJitter
	cfg.MaxConnIdleTime = opts.MaxConnIdleTime
	cfg.HealthCheckPeriod = opts.HealthCheckPeriod

	if opts.StatementTimeout > 0 {
		if cfg.ConnConfig.RuntimeParams == nil {
			cfg.ConnConfig.RuntimeParams = map[string]string{}
		}
		// Postgres accepts integer milliseconds for statement_timeout.
		cfg.ConnConfig.RuntimeParams["statement_timeout"] = strconv.FormatInt(opts.StatementTimeout.Milliseconds(), 10)
	}
}