package official_climb

import (
	"database/sql"
	"fmt"
	"time"

	"github.com/martinlehoux/biking_home/climb"
)

// Attempt is one recorded passage of an official climb during one ride,
// identified by (rideID, climbID, occurrence). Scalar stats are snapshotted at
// recording time so climb listings never re-parse ride files.
type Attempt struct {
	RideID             int64
	ClimbID            int64
	Occurrence         int
	DistanceM          float64
	ElevationGainM     float64
	SlopePercent       float64
	Cotacol            float64
	CotacolAlgoVersion string
	StartedAt          time.Time
	EndedAt            time.Time
	CreatedAt          time.Time
}

const attemptColumns = "a.ride_id, a.climb_id, a.occurrence, a.distance_m, a.elevation_gain_m, a.slope_percent, a.cotacol, a.cotacol_algo_version, a.started_at, a.ended_at, a.created_at"

// AttemptsForClimb returns every recorded attempt of one official climb,
// oldest ride first.
func AttemptsForClimb(db *sql.DB, climbID int64) ([]Attempt, error) {
	return listAttempts(db, "climb_id = ?", climbID, "r.start_date, a.ride_id, a.occurrence")
}

// AttemptsForRide returns every recorded attempt within one ride, in route
// order.
func AttemptsForRide(db *sql.DB, rideID int64) ([]Attempt, error) {
	return listAttempts(db, "ride_id = ?", rideID, "a.occurrence")
}

func listAttempts(db *sql.DB, where string, arg any, order string) ([]Attempt, error) {
	rows, err := db.Query(`
		SELECT `+attemptColumns+`
		FROM ride_official_climb_attempts a
		JOIN rides r ON r.id = a.ride_id
		WHERE `+where+`
		ORDER BY `+order, arg)
	if err != nil {
		return nil, fmt.Errorf("list official climb attempts: %w", err)
	}
	defer rows.Close()
	attempts := make([]Attempt, 0)
	for rows.Next() {
		attempt, err := scanAttempt(rows)
		if err != nil {
			return nil, err
		}
		attempts = append(attempts, attempt)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate official climb attempts: %w", err)
	}
	return attempts, nil
}

// ReplaceRideAttempts atomically rewrites every attempt row of one ride,
// dropping stale matches and inserting the freshly analyzed ones.
func ReplaceRideAttempts(db *sql.DB, rideID int64, attempts []Attempt) error {
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("replace ride attempts: %w", err)
	}
	defer func() { _ = tx.Rollback() }()
	if _, err := tx.Exec("DELETE FROM ride_official_climb_attempts WHERE ride_id = ?", rideID); err != nil {
		return fmt.Errorf("delete ride attempts: %w", err)
	}
	for _, attempt := range attempts {
		if attempt.RideID != rideID {
			return fmt.Errorf("replace ride attempts: attempt references ride %d, expected %d", attempt.RideID, rideID)
		}
		if _, err := tx.Exec(`
			INSERT INTO ride_official_climb_attempts (ride_id, climb_id, occurrence, distance_m, elevation_gain_m, slope_percent, cotacol, cotacol_algo_version, started_at, ended_at)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
		`, attempt.RideID, attempt.ClimbID, attempt.Occurrence, attempt.DistanceM, attempt.ElevationGainM, attempt.SlopePercent, attempt.Cotacol, attempt.CotacolAlgoVersion,
			attempt.StartedAt.UTC().Format(time.RFC3339), attempt.EndedAt.UTC().Format(time.RFC3339)); err != nil {
			return fmt.Errorf("insert ride attempt: %w", err)
		}
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit ride attempts: %w", err)
	}
	return nil
}

type attemptScanner interface {
	Scan(dest ...any) error
}

func scanAttempt(s attemptScanner) (Attempt, error) {
	var (
		attempt            Attempt
		startedAt, endedAt string
		createdAt          sql.NullString
	)
	if err := s.Scan(&attempt.RideID, &attempt.ClimbID, &attempt.Occurrence, &attempt.DistanceM, &attempt.ElevationGainM, &attempt.SlopePercent, &attempt.Cotacol, &attempt.CotacolAlgoVersion, &startedAt, &endedAt, &createdAt); err != nil {
		return Attempt{}, err
	}
	var err error
	if attempt.StartedAt, err = time.Parse(time.RFC3339, startedAt); err != nil {
		return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.started_at: %w", err)
	}
	if attempt.EndedAt, err = time.Parse(time.RFC3339, endedAt); err != nil {
		return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.ended_at: %w", err)
	}
	if createdAt.Valid {
		if attempt.CreatedAt, err = time.Parse(time.RFC3339, createdAt.String); err != nil {
			return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.created_at: %w", err)
		}
	}
	return attempt, nil
}

// AttemptStats carries the analyzed values snapshotted into an attempt row.
type AttemptStats struct {
	OfficialClimbID int64
	DistanceM       float64
	ElevationGainM  float64
	SlopePercent    float64
	Cotacol         float64
	StartIndex      int
	EndIndex        int
}

// AttemptFromStats builds the persisted snapshot of one analyzed climb
// occurrence. Timestamps come from the ride's recorded points bounding the
// climb interval.
func AttemptFromStats(rideID int64, occurrence int, stats AttemptStats, timestamps []time.Time) Attempt {
	startedAt, endedAt := time.Time{}, time.Time{}
	if stats.StartIndex < len(timestamps) {
		startedAt = timestamps[stats.StartIndex]
	}
	if stats.EndIndex < len(timestamps) {
		endedAt = timestamps[stats.EndIndex]
	}
	return Attempt{
		RideID:             rideID,
		ClimbID:            stats.OfficialClimbID,
		Occurrence:         occurrence,
		DistanceM:          stats.DistanceM,
		ElevationGainM:     stats.ElevationGainM,
		SlopePercent:       stats.SlopePercent,
		Cotacol:            stats.Cotacol,
		CotacolAlgoVersion: climb.CotacolAlgorithmVersion,
		StartedAt:          startedAt,
		EndedAt:            endedAt,
	}
}