db.go

package database

import (
	"context"
	"fmt"
	"log"
	"os"
	"runtime"
	"sync"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
	_ "github.com/joho/godotenv/autoload"
)

type Service interface {
	Health() map[string]string
	Close() error
	GetPool() *pgxpool.Pool
}

type service struct {
	db *pgxpool.Pool
}

var (
	dbInstance *service
	once       sync.Once

	database = os.Getenv("DB_DATABASE")
	password = os.Getenv("DB_PASSWORD")
	username = os.Getenv("DB_USERNAME")
	port     = os.Getenv("DB_PORT")
	host     = os.Getenv("DB_HOST")
	schema   = os.Getenv("DB_SCHEMA")
)

func New() (Service, error) {
	var initErr error

	once.Do(func() {
		if database == "" || password == "" || username == "" || port == "" || host == "" {
			initErr = fmt.Errorf("database configuration is incomplete")
			return
		}

		connStr := fmt.Sprintf(
			"postgres://%s:%s@%s:%s/%s?sslmode=disable&search_path=%s",
			username, password, host, port, database, schema,
		)

		config, err := pgxpool.ParseConfig(connStr)
		if err != nil {
			initErr = fmt.Errorf("error parsing connection string: %w", err)
			return
		}

		// Configure pool based on workload
		maxConns := runtime.NumCPU() * 4 // More aggressive for web apps
		if maxConns < 8 {
			maxConns = 8 // Minimum for production
		}
		
		config.MaxConns = int32(maxConns)
		config.MinConns = int32(maxConns / 4)           // Pre-warm 25% of pool
		config.MaxConnLifetime = time.Hour              // Recycle old connections
		config.MaxConnIdleTime = 15 * time.Minute       // Close idle connections
		config.HealthCheckPeriod = time.Minute          // Periodic health checks

		ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
		defer cancel()

		pool, err := pgxpool.NewWithConfig(ctx, config)
		if err != nil {
			initErr = fmt.Errorf("failed to connect to DB: %w", err)
			return
		}

		// Ping check
		if err := pool.Ping(ctx); err != nil {
			pool.Close()
			initErr = fmt.Errorf("db ping failed: %w", err)
			return
		}

		dbInstance = &service{db: pool}
		log.Printf("✅ Connected to PostgreSQL: %s (MaxConns: %d, MinConns: %d)", 
			database, maxConns, maxConns/4)
	})

	return dbInstance, initErr
}

func (s *service) Health() map[string]string {
	stats := make(map[string]string)

	ctx, cancel := context.WithTimeout(context.Background(), 1*time.Second)
	defer cancel()
	
	if err := s.db.Ping(ctx); err != nil {
		stats["status"] = "down"
		stats["error"] = err.Error()
		return stats
	}

	p := s.db.Stat()
	stats["status"] = "up"
	stats["total_connections"] = fmt.Sprint(p.TotalConns())
	stats["acquired"] = fmt.Sprint(p.AcquiredConns())
	stats["idle"] = fmt.Sprint(p.IdleConns())
	stats["max"] = fmt.Sprint(p.MaxConns())
	stats["acquire_count"] = fmt.Sprint(p.AcquireCount())
	stats["acquire_wait"] = p.AcquireDuration().String()
	stats["empty_acquire"] = fmt.Sprint(p.EmptyAcquireCount())
	stats["canceled_acquire"] = fmt.Sprint(p.CanceledAcquireCount())

	// Better health logic
	stats["message"] = "healthy"
	
	// Check utilization percentage
	utilization := float64(p.AcquiredConns()) / float64(p.MaxConns()) * 100
	if utilization > 80 {
		stats["message"] = fmt.Sprintf("High utilization: %.1f%% of connections in use", utilization)
		stats["warning"] = "consider increasing pool size"
	}

	// Check for connection starvation
	if p.EmptyAcquireCount() > 0 {
		stats["message"] = "Connection pool exhaustion detected"
		stats["warning"] = "requests waiting for connections"
	}

	// Check for request cancellations
	if p.CanceledAcquireCount() > 0 {
		stats["message"] = "Acquire timeouts detected"
		stats["warning"] = "requests timing out waiting for connections"
	}

	return stats
}

func (s *service) Close() error {
	log.Printf("Disconnecting from PostgreSQL: %s", database)
	s.db.Close()
	return nil
}

func (s *service) GetPool() *pgxpool.Pool {
	return s.db
}