package database

import (
	"database/sql"
	"fmt"

	"forge.cadoles.com/LaCanne/realZ/internal/config"
	_ "github.com/lib/pq" // Driver PostgreSQL
)

// Service holds the database connection pool.
type Service struct {
	db *sql.DB
}

// NewService initializes and returns a new database service.
func NewService(cfg *config.DatabaseConfig) (*Service, error) {
	connStr := fmt.Sprintf("user=%s password=%s dbname=%s host=%s port=%d sslmode=%s",
		cfg.User, cfg.Password, cfg.DBName,
		cfg.Host, cfg.Port, cfg.SSLMode,
	)

	db, err := sql.Open("postgres", connStr)
	if err != nil {
		return nil, fmt.Errorf("could not open database connection: %w", err)
	}

	if err = db.Ping(); err != nil {
		db.Close() // Close connection on ping failure
		return nil, fmt.Errorf("could not ping database: %w", err)
	}

	return &Service{db: db}, nil
}

// Close closes the database connection.
func (s *Service) Close() error {
	return s.db.Close()
}

// QueryOrthometricCorrection queries the database for the orthometric correction for given coordinates.
func (s *Service) QueryOrthometricCorrection(lat, lon float64, srcProj, dstProj int) (float64, error) {
	var correction float64

	query := `
		SELECT ST_Value(rast, ST_Transform(ST_SetSRID(ST_MakePoint($2, $1), $3::integer), $4::integer)) AS pixel_value
		FROM raf20lamber93
		WHERE ST_Intersects(rast, ST_Transform(ST_SetSRID(ST_MakePoint($2, $1), $3::integer), $4::integer));
	`

	// QueryRow is ideal as we expect at most one row.
	err := s.db.QueryRow(query, lat, lon, srcProj, dstProj).Scan(&correction)
	if err != nil {
		// If no rows are found, Scan will return sql.ErrNoRows.
		return 0, err
	}
	return correction, nil
}