package ride

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"github.com/huandu/go-sqlbuilder"
	"github.com/lib/pq"
	uuid "github.com/satori/go.uuid"
	"github.com/yanishoss/germainion/internal/core"
	"time"
)

var (
	ErrRideDoesntExist = errors.New("there is no ride with these information")
)

type manager struct {
	db *sql.Tx
}

// Manager represents a user pool
type Manager interface {
	Create(ctx context.Context, driverID core.ID, ride Ride) (core.ID, error)
	UpdateByID(ctx context.Context, id core.ID, ride Ride) error
	DeleteByID(ctx context.Context, id core.ID) error
	DeleteAllByDriverID(ctx context.Context, driverID core.ID) error
	GetByID(ctx context.Context, id core.ID) (*Entry, error)
	GetAllByDriverID(ctx context.Context, driverID core.ID, limit int, offset int) (Entries, error)
	ExistsByID(ctx context.Context, id core.ID) (bool, error)
	ExistsByDriverID(ctx context.Context, driverID core.ID) (bool, error)
	ExistsByRideIDAndDriverID(ctx context.Context, rideID core.ID, driverID core.ID) (bool, error)
	FindBestMatch(ctx context.Context, startLocation, endLocation Location, startTime time.Time, seats, limit, offset int) (Entries, error)
	Expire(ctx context.Context) error
}

// New creates a new Manager (a singleton)
func New(tx *sql.Tx) Manager {
	m := &manager{
		db: tx,
	}

	m.init()

	return m
}

func (m manager) init() {
	db := m.db

	_, err := db.Exec(`CREATE EXTENSION IF NOT EXISTS postgis;`)

	if err != nil {
		panic(err)
	}

	// Got to remove the foreign key to the "user_pool" table
	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS ride (
		id UUID PRIMARY KEY NOT NULL,
		driver_id UUID NOT NULL,
		seats INT NOT NULL,
		start_location GEOGRAPHY NOT NULL,
		end_location GEOGRAPHY NOT NULL,
		start_time TIMESTAMP WITH TIME ZONE NOT NULL,
		expired BOOLEAN NOT NULL DEFAULT false,
		created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
		updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
	);`)

	if err != nil {
		panic(err)
	}
}

func (m manager) Create(ctx context.Context, driverID core.ID, ride Ride) (core.ID, error) {
	db := m.db

	id := uuid.NewV4().String()

	q := `INSERT INTO ride (id, driver_id, seats, start_location, end_location, start_time, expired) VALUES ($1, $2, $3, ST_MakePoint($4, $5)::GEOGRAPHY, ST_MakePoint($6, $7)::GEOGRAPHY, $8, $9)`

	_, err := db.ExecContext(
		ctx,
		q,
		id,
		driverID,
		*ride.Seats,
		ride.StartLocation[0],
		ride.StartLocation[1],
		ride.EndLocation[0],
		ride.EndLocation[1],
		pq.FormatTimestamp(*ride.StartTime),
		false)

	if err != nil {
		return "", err
	}

	return id, nil
}

func (m manager) UpdateByID(ctx context.Context, id core.ID, ride Ride) error {
	db := m.db

	oldRide, err := m.GetByID(ctx, id)

	if err != nil {
		return err
	}

	ub := sqlbuilder.NewUpdateBuilder()

	ub.
		Update("ride").
		Set("updated_at = now()")

	if ride.Seats != nil && (*ride.Seats != *oldRide.Seats) {
		ub.Set("seats = " + ub.Var(*ride.Seats))
	}

	if ride.StartLocation != nil && (ride.StartLocation[0] != 0 || ride.StartLocation[1] != 0) && (ride.StartLocation[0] != oldRide.StartLocation[0] || ride.StartLocation[1] != oldRide.StartLocation[1]) {
		ub.Set("start_location = " + ub.Var(*ride.StartLocation))
	}

	if ride.EndLocation != nil && (ride.EndLocation[0] != 0 || ride.EndLocation[1] != 0) && (ride.EndLocation[0] != oldRide.EndLocation[0] || ride.EndLocation[1] != oldRide.EndLocation[1]) {
		ub.Set("end_location = " + ub.Var(*ride.StartLocation))
	}

	if ride.StartTime != nil && !ride.StartTime.Equal(*oldRide.StartTime) {
		ub.Set("start_time = " + ub.Var(pq.FormatTimestamp(*ride.StartTime)))
	}

	if ride.Expired != nil && (*ride.Expired != *oldRide.Expired) {
		ub.Set("expired = " + ub.Var(*ride.Expired))
	}

	ub.Where("id = " + ub.Var(id))

	q, args := ub.BuildWithFlavor(sqlbuilder.PostgreSQL)

	_, err = db.ExecContext(ctx, q, args...)

	if err != nil {
		return err
	}

	return nil
}

func (m manager) GetByID(ctx context.Context, id core.ID) (*Entry, error) {
	db := m.db

	exists, err := m.ExistsByID(ctx, id)

	if err != nil {
		return nil, err
	}

	if !exists {
		return nil, ErrRideDoesntExist
	}

	q := `
	SELECT
		id,
		driver_id,
		seats,
		r.seats - COALESCE(b.total_seats, 0) AS seats_left,
		ST_X(start_location::GEOMETRY),
		ST_Y(start_location::GEOMETRY),
		ST_X(end_location::GEOMETRY),
		ST_Y(end_location::GEOMETRY),
		start_time,
		expired,
		created_at,
		updated_at
	FROM ride r
		LEFT JOIN (
			SELECT
				   b.ride_id,
				   SUM(b.seats) AS total_seats
			FROM booking b
			GROUP BY b.ride_id
	) b ON r.id = b.ride_id
	WHERE id = $1
	GROUP BY r.id, b.ride_id, b.total_seats
	`

	row := db.QueryRowContext(ctx, q, id)

	entry := NewEntry()

	if err := row.Scan(
		entry.ID,
		entry.DriverID,
		entry.Seats,
		entry.SeatsLeft,
		&entry.StartLocation[0],
		&entry.StartLocation[1],
		&entry.EndLocation[0],
		&entry.EndLocation[1],
		entry.StartTime,
		entry.Expired,
		entry.CreatedAt,
		entry.UpdatedAt,
	); err != nil {
		fmt.Println(err)
		return nil, err
	}

	return entry, nil
}

func (m manager) ExistsByID(ctx context.Context, id core.ID) (bool, error) {
	db := m.db

	sb := sqlbuilder.NewSelectBuilder()

	q, args := sb.
		Select("COUNT(*)").
		From("ride").
		Where("id = " + sb.Var(id)).
		Limit(1).
		BuildWithFlavor(sqlbuilder.PostgreSQL)

	count := 0

	row := db.QueryRowContext(ctx, q, args...)

	if err := row.Scan(&count); err != nil {
		return false, err
	}

	return count > 0, nil
}

func (m manager) ExistsByDriverID(ctx context.Context, driverID core.ID) (bool, error) {
	db := m.db

	sb := sqlbuilder.NewSelectBuilder()

	q, args := sb.
		Select("COUNT(*)").
		From("ride").
		Where("driver_id = " + sb.Var(driverID)).
		Limit(1).
		BuildWithFlavor(sqlbuilder.PostgreSQL)

	count := 0

	row := db.QueryRowContext(ctx, q, args...)

	if err := row.Scan(&count); err != nil {
		return false, err
	}

	return count > 0, nil
}

func (m manager) DeleteByID(ctx context.Context, id core.ID) error {
	db := m.db

	exists, err := m.ExistsByID(ctx, id)

	if err != nil {
		return err
	}

	if !exists {
		return ErrRideDoesntExist
	}

	delb := sqlbuilder.NewDeleteBuilder()

	q, args := delb.
		DeleteFrom("ride").
		Where("id = " + delb.Var(id)).
		BuildWithFlavor(sqlbuilder.PostgreSQL)

	_, err = db.ExecContext(ctx, q, args...)

	if err != nil {
		return err
	}

	return nil
}

func (m manager) GetAllByDriverID(ctx context.Context, driverID core.ID, limit, offset int) (Entries, error) {
	db := m.db

	exists, err := m.ExistsByDriverID(ctx, driverID)

	if err != nil {
		return nil, err
	}

	if !exists {
		return nil, ErrRideDoesntExist
	}

	q := `
	SELECT
		id,
		driver_id,
		seats,
		r.seats - COALESCE(b.total_seats, 0) AS seats_left,
		ST_X(start_location::GEOMETRY),
		ST_Y(start_location::GEOMETRY),
		ST_X(end_location::GEOMETRY),
		ST_Y(end_location::GEOMETRY),
		start_time,
		expired,
		created_at,
		updated_at
	FROM ride r
		LEFT JOIN (
			SELECT
				   b.ride_id,
				   SUM(b.seats) AS total_seats
			FROM booking b
			GROUP BY b.ride_id
	) b ON r.id = b.ride_id
	WHERE driver_id = $1
	GROUP BY r.id, b.ride_id, b.total_seats
	ORDER BY created_at DESC
	`

	if limit == core.DefaultLimit {
		q += "\nLIMIT ALL"
	} else {
		q += fmt.Sprintf("\nLIMIT %d", limit)
	}

	if offset == core.DefaultOffset {
		q += "\nOFFSET 0"
	} else {
		q += fmt.Sprintf("\nOFFSET %d", offset)
	}

	rows, err := db.QueryContext(ctx, q, driverID)

	if err != nil {
		return nil, err
	}

	defer rows.Close()

	entries := make(Entries, 0, core.DefaultScanAllocation)

	for rows.Next() {
		entry := NewEntry()

		if err := rows.Scan(
			entry.ID,
			entry.DriverID,
			entry.Seats,
			entry.SeatsLeft,
			&entry.StartLocation[0],
			&entry.StartLocation[1],
			&entry.EndLocation[0],
			&entry.EndLocation[1],
			entry.StartTime,
			entry.Expired,
			entry.CreatedAt,
			entry.UpdatedAt,
		); err != nil {
			return nil, err
		}

		entries = append(entries, entry)
	}

	return entries, nil
}

func (m manager) FindBestMatch(ctx context.Context, startLocation, endLocation Location, startTime time.Time, seats, limit, offset int) (Entries, error) {
	db := m.db

	q := `
	SELECT
		id,
		driver_id,
		seats,
		seats_left,
		ST_X(start_location::GEOMETRY),
		ST_Y(start_location::GEOMETRY),
		ST_X(end_location::GEOMETRY),
		ST_Y(end_location::GEOMETRY),
		start_time,
		expired,
		created_at,
		updated_at
	FROM (
		SELECT
		   *,
		   ST_DistanceSphere(r.start_location::GEOMETRY, ST_MakePoint($1, $2)) AS start_dist,
		   ST_DistanceSphere(r.end_location::GEOMETRY, ST_MakePoint($3, $4)) AS end_dist,
		   r.seats - COALESCE(b.total_seats, 0) AS seats_left
		FROM ride r
			LEFT JOIN (
				SELECT
					b.ride_id,
					SUM(b.seats) AS total_seats
				FROM booking b
				GROUP BY b.ride_id
			) b ON r.id = b.ride_id
		WHERE r.start_time >= $5::TIMESTAMPTZ
			AND r.start_time <= $5::TIMESTAMPTZ + '20 minute'::INTERVAL
			AND NOT r.expired
		GROUP BY r.id, b.ride_id, b.total_seats
	) AS q
	WHERE (start_dist + end_dist) <= 1000
		AND seats_left >= $6
	ORDER BY
		start_time ASC,
		(start_dist + end_dist) ASC,
		seats_left DESC
		`

	// Awkwardly try to handle null limit and offset
	if limit == core.DefaultLimit {
		q += "\nLIMIT ALL"
	} else {
		q += fmt.Sprintf("\nLIMIT %d", limit)
	}

	if offset == core.DefaultOffset {
		q += "\nOFFSET 0"
	} else {
		q += fmt.Sprintf("\nOFFSET %d", offset)
	}

	rows, err := db.QueryContext(
		ctx,
		q,
		startLocation[0],
		startLocation[1],
		endLocation[0],
		endLocation[1],
		startTime,
		seats,
	)

	if err != nil {
		return nil, err
	}

	defer rows.Close()

	entries := make(Entries, 0, core.DefaultScanAllocation)

	for rows.Next() {
		entry := NewEntry()

		if err := rows.Scan(
			entry.ID,
			entry.DriverID,
			entry.Seats,
			entry.SeatsLeft,
			&entry.StartLocation[0],
			&entry.StartLocation[1],
			&entry.EndLocation[0],
			&entry.EndLocation[1],
			entry.StartTime,
			entry.Expired,
			entry.CreatedAt,
			entry.UpdatedAt,
		); err != nil {
			return nil, err
		}

		entries = append(entries, entry)
	}

	return entries, nil
}

func (m manager) DeleteAllByDriverID(ctx context.Context, driverID core.ID) error {
	db := m.db

	exists, err := m.ExistsByDriverID(ctx, driverID)

	if err != nil {
		return err
	}

	if !exists {
		return ErrRideDoesntExist
	}

	delb := sqlbuilder.NewDeleteBuilder()

	q, args := delb.
		DeleteFrom("ride").
		Where("driver_id = " + delb.Var(driverID)).
		BuildWithFlavor(sqlbuilder.PostgreSQL)

	_, err = db.ExecContext(ctx, q, args...)

	if err != nil {
		return err
	}

	return nil
}

func (m manager) Expire(ctx context.Context) error {
	db := m.db

	q := `
	UPDATE ride
	SET expired = true
	WHERE
		start_time <= now()
		AND NOT expired;
	`

	_, err := db.ExecContext(ctx, q)

	if err != nil {
		return err
	}

	return nil
}

func (m manager) ExistsByRideIDAndDriverID(ctx context.Context, rideID core.ID, driverID core.ID) (bool, error) {
	db := m.db

	sb := sqlbuilder.NewSelectBuilder()

	q, args := sb.
		Select("COUNT(*)").
		From("ride").
		Where("driver_id = "+sb.Var(driverID), "id = "+sb.Var(rideID)).
		Limit(1).
		BuildWithFlavor(sqlbuilder.PostgreSQL)

	count := 0

	row := db.QueryRowContext(ctx, q, args...)

	if err := row.Scan(&count); err != nil {
		return false, err
	}

	return count > 0, nil
}