package services

import (
	"context"
	"fmt"
	"sort"
	"time"

	"github.com/danielrhuynh/busybar/internal/models"
	"github.com/danielrhuynh/busybar/pkg/database"
	"github.com/jftuga/geodist"
)

func GetBusiestDays() ([]models.BusiestDay, error) {
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()
	withClause := database.PSQL.
		Select(
			"bar_id",
			"EXTRACT(DOW FROM report_time AT TIME ZONE 'UTC') AS day_of_week",
			"COUNT(*) as report_count",
		).
		From("reports").
		GroupBy("bar_id", "day_of_week")

	sqlWithClause, argsWithClause, err := withClause.ToSql()
	if err != nil {
		fmt.Println("error generating WITH clause SQL: ", err)
		return nil, err
	}

	query := database.PSQL.
		Select(
			"b.bar_id",
			"b.name",
			"dc.day_of_week",
			"dc.report_count",
		).
		From("bars b").
		Join("(SELECT bar_id, day_of_week, report_count FROM day_counts dc1 WHERE report_count = (SELECT MAX(report_count) FROM day_counts dc2 WHERE dc1.bar_id = dc2.bar_id)) dc ON dc.bar_id = b.bar_id").
		OrderBy("b.bar_id").
		Prefix(
			fmt.Sprintf("WITH day_counts AS (%s)", sqlWithClause),
		)

	sqlStr, args, err := query.ToSql()
	if err != nil {
		fmt.Println("error generating SQL: ", err)
		return nil, err
	}

	allArgs := append(argsWithClause, args...)

	rows, err := database.DB.Query(ctx, sqlStr, allArgs...)
	if err != nil {
		fmt.Println("error executing query: ", err)
		return nil, err
	}
	defer rows.Close()

	var results []models.BusiestDay
	for rows.Next() {
		var bd models.BusiestDay
		var dayNum float64
		err := rows.Scan(&bd.BarID, &bd.BarName, &dayNum, &bd.ReportCount)
		if err != nil {
			fmt.Println("error scanning row: ", err)
			return nil, err
		}

		// because of weird bug
		adjustedDay := int(dayNum)
		if adjustedDay == 0 {
			adjustedDay = 6
		} else {
			adjustedDay--
		}

		bd.DayOfWeek = getDayName(adjustedDay)
		results = append(results, bd)
	}

	return results, nil
}

func getDayName(day int) string {
	days := []string{"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"}
	return days[day]
}

func GetBusiestBar() (*models.BusiestBar, error) {
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	query := database.PSQL.
		Select(
			"b.bar_id",
			"b.name",
			"AVG(r.busyness)::numeric(10,2) as avg_busyness",
			"COUNT(r.report_id) as report_count",
		).
		From("bars b").
		Join("reports r ON b.bar_id = r.bar_id").
		Where("r.report_time >= NOW() - INTERVAL '3 hours'").
		GroupBy("b.bar_id", "b.name").
		OrderBy("avg_busyness DESC").
		Limit(1)

	sqlStr, args, err := query.ToSql()
	if err != nil {
		return nil, fmt.Errorf("error generating SQL: %v", err)
	}

	var result models.BusiestBar
	err = database.DB.QueryRow(ctx, sqlStr, args...).Scan(
		&result.BarID,
		&result.BarName,
		&result.AverageBusyness,
		&result.ReportCount,
	)

	if err != nil {
		if err.Error() == "no rows in result set" {
			return nil, nil
		}
		return nil, fmt.Errorf("error executing query: %v", err)
	}

	return &result, nil
}

func GetTrendingBars() ([]models.TrendingBar, error) {
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	query := database.PSQL.
		Select(
			"b.bar_id",
			"b.name",
			"COUNT(r.report_id) as report_count",
			"AVG(r.busyness)::numeric(10,2) as avg_busyness",
			"AVG(r.wait_time)::numeric(10,2) as avg_wait_time",
		).
		From("bars b").
		Join("reports r ON b.bar_id = r.bar_id").
		Where("r.report_time >= NOW() - INTERVAL '3 hours'").
		GroupBy("b.bar_id", "b.name").
		Having("COUNT(r.report_id) > 0").
		OrderBy("report_count DESC, avg_busyness DESC")

	sqlStr, args, err := query.ToSql()
	if err != nil {
		return nil, fmt.Errorf("error generating SQL: %v", err)
	}

	rows, err := database.DB.Query(ctx, sqlStr, args...)
	if err != nil {
		return nil, fmt.Errorf("error executing query: %v", err)
	}
	defer rows.Close()

	var results []models.TrendingBar
	for rows.Next() {
		var bar models.TrendingBar
		err := rows.Scan(
			&bar.BarID,
			&bar.BarName,
			&bar.ReportCount,
			&bar.AverageBusyness,
			&bar.AverageWaitTime,
		)
		if err != nil {
			return nil, fmt.Errorf("error scanning row: %v", err)
		}
		results = append(results, bar)
	}

	if err = rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating rows: %v", err)
	}

	return results, nil
}

func GetShortestWaitBar() (*models.WaitTimeBar, error) {
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	query := database.PSQL.
		Select(
			"b.bar_id",
			"b.name",
			"AVG(r.wait_time)::numeric(10,2) as avg_wait_time",
			"COUNT(r.report_id) as report_count",
			"AVG(r.busyness)::numeric(10,2) as avg_busyness",
		).
		From("bars b").
		Join("reports r ON b.bar_id = r.bar_id").
		Where("r.report_time >= NOW() - INTERVAL '3 hours'").
		GroupBy("b.bar_id", "b.name").
		Having("COUNT(r.report_id) > 0").
		OrderBy("avg_wait_time ASC").
		Limit(1)

	sqlStr, args, err := query.ToSql()
	if err != nil {
		return nil, fmt.Errorf("error generating SQL: %v", err)
	}

	var result models.WaitTimeBar
	err = database.DB.QueryRow(ctx, sqlStr, args...).Scan(
		&result.BarID,
		&result.BarName,
		&result.AverageWaitTime,
		&result.ReportCount,
		&result.AverageBusyness,
	)

	if err != nil {
		if err.Error() == "no rows in result set" {
			return nil, nil
		}
		return nil, fmt.Errorf("error executing query: %v", err)
	}

	return &result, nil
}

func GetAllBars(userLat, userLong float64) ([]models.Bar, error) {
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	query := database.PSQL.
		Select(
			"bar_id",
			"name",
			"description",
			"latitude",
			"longitude",
			"is_active",
		).
		From("bars").
		Where("is_active = true")

	sqlStr, args, err := query.ToSql()
	if err != nil {
		return nil, fmt.Errorf("error generating SQL: %v", err)
	}

	rows, err := database.DB.Query(ctx, sqlStr, args...)
	if err != nil {
		return nil, fmt.Errorf("error querying bars: %v", err)
	}
	defer rows.Close()

	userCoord := geodist.Coord{Lat: userLat, Lon: userLong}
	var bars []models.Bar

	for rows.Next() {
		var bar models.Bar
		err := rows.Scan(
			&bar.BarID,
			&bar.BarName,
			&bar.BarDescription,
			&bar.BarLatitude,
			&bar.BarLongitude,
			&bar.IsActive,
		)
		if err != nil {
			return nil, fmt.Errorf("error scanning bar row: %v", err)
		}

		barCoord := geodist.Coord{Lat: bar.BarLatitude, Lon: bar.BarLongitude}
		_, distanceKm, err := geodist.VincentyDistance(userCoord, barCoord)
		if err != nil {
			_, distanceKm = geodist.HaversineDistance(userCoord, barCoord)
		}

		bar.Distance = distanceKm * 1000

		bars = append(bars, bar)
	}

	if err = rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating bar rows: %v", err)
	}

	sort.Slice(bars, func(i, j int) bool {
		return bars[i].Distance < bars[j].Distance
	})

	return bars, nil
}