package official_climb

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

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

// SortColumn is a climbs-list ordering key.
type SortColumn string

const (
	SortName     SortColumn = "name"
	SortAttempts SortColumn = "attempts"
	SortDistance SortColumn = "distance"
	SortSlope    SortColumn = "slope"
	SortCotacol  SortColumn = "cotacol"
	SortBestTime SortColumn = "best_time"
)

// ListedClimb is one climbs-list row: official climb identity plus aggregates
// over its recorded attempts. Medians are computed in Go from the attempt
// rows; best time is the minimum attempt duration.
type ListedClimb struct {
	ID             int64
	Name           string
	Attempts       int
	DistanceM      float64
	ElevationGainM float64
	SlopePercent   float64
	Cotacol        float64
	BestDurationS  float64
	HasBest        bool
}

func (c ListedClimb) Category() string {
	return climb.CategoryOf(c.Cotacol).String()
}

// ListWithAggregates returns every official climb with aggregates over its
// attempts. Attempts default to zero for climbs never ridden; climbs without
// attempts show no distance/slope stats.
func ListWithAggregates(db *sql.DB) ([]ListedClimb, error) {
	climbRows, err := db.Query("SELECT id, name FROM official_climbs ORDER BY name ASC")
	if err != nil {
		return nil, fmt.Errorf("list climbs: %w", err)
	}
	defer climbRows.Close()
	climbs := make([]ListedClimb, 0)
	index := make(map[int64]int)
	for climbRows.Next() {
		var row ListedClimb
		if err := climbRows.Scan(&row.ID, &row.Name); err != nil {
			return nil, err
		}
		index[row.ID] = len(climbs)
		climbs = append(climbs, row)
	}
	if err := climbRows.Err(); err != nil {
		return nil, fmt.Errorf("iterate climbs: %w", err)
	}

	attemptRows, err := db.Query(`
		SELECT climb_id, distance_m, elevation_gain_m, slope_percent, cotacol, started_at, ended_at
		FROM ride_official_climb_attempts`)
	if err != nil {
		return nil, fmt.Errorf("list climb attempts: %w", err)
	}
	defer attemptRows.Close()
	samples := make(map[int64]*attemptSamples)
	for attemptRows.Next() {
		var (
			climbID   int64
			distanceM float64
			gainM     float64
			slope     float64
			cotacol   float64
			startedAt string
			endedAt   string
		)
		if err := attemptRows.Scan(&climbID, &distanceM, &gainM, &slope, &cotacol, &startedAt, &endedAt); err != nil {
			return nil, err
		}
		sample, known := samples[climbID]
		if !known {
			sample = &attemptSamples{}
			samples[climbID] = sample
		}
		sample.distances = append(sample.distances, distanceM)
		sample.gains = append(sample.gains, gainM)
		sample.slopes = append(sample.slopes, slope)
		sample.cotacols = append(sample.cotacols, cotacol)
		start, err := time.Parse(time.RFC3339, startedAt)
		if err != nil {
			return nil, fmt.Errorf("invalid ride_official_climb_attempts.started_at: %w", err)
		}
		end, err := time.Parse(time.RFC3339, endedAt)
		if err != nil {
			return nil, fmt.Errorf("invalid ride_official_climb_attempts.ended_at: %w", err)
		}
		durationS := end.Sub(start).Seconds()
		if durationS > 0 && (sample.bestDurationS == 0 || durationS < sample.bestDurationS) {
			sample.bestDurationS = durationS
		}
	}
	if err := attemptRows.Err(); err != nil {
		return nil, fmt.Errorf("iterate climb attempts: %w", err)
	}

	for id, sample := range samples {
		row := &climbs[index[id]]
		row.Attempts = len(sample.distances)
		row.DistanceM = Median(sample.distances)
		row.ElevationGainM = Median(sample.gains)
		row.SlopePercent = Median(sample.slopes)
		row.Cotacol = Median(sample.cotacols)
		if sample.bestDurationS > 0 {
			row.BestDurationS = sample.bestDurationS
			row.HasBest = true
		}
	}
	return climbs, nil
}

type attemptSamples struct {
	distances     []float64
	gains         []float64
	slopes        []float64
	cotacols      []float64
	bestDurationS float64
}