package db

import (
	"git.ssns.se/git/frozendragon/pokemon-go-trade/internal/model"
	"github.com/jmoiron/sqlx"
	"github.com/pkg/errors"
)

type Package struct {
	Db *sqlx.DB
}

type Interface interface {
	GetPokemons() ([]Pokemon, error)
	StoreHavePokemons(havePokemons []HavePokemon) error
	GetHavePokemons() ([]Pokemon, error)
	CreateWantGroup(wantGroup model.WantGroup) error
	GetWantGroups() ([]model.WantGroup, error)
	DeleteWantGroup(id int) error
	CreateWantPokemon(wantPokemon model.WantPokemon) error
	GetWantPokemonByWantGroup(wantGroupID int) ([]Pokemon, error)
}

type Pokemon struct {
	ID             int    `db:"id"`
	DexNumber      int    `db:"dex_number"`
	Region         string `db:"region"`
	Name           string `db:"name"`
	ShinyEnabled   bool   `db:"shiny_enabled"`
	ShinyImageName string `db:"shiny_image_name"`
	ImageName      string `db:"image_name"`
}

type HavePokemon struct {
	ID        int  `db:"id"`
	PokemonID int  `db:"pokemon_id"`
	Shiny     bool `db:"shiny"`
}

func (p *Package) GetPokemons() ([]Pokemon, error) {
	var pokemons []Pokemon
	err := p.Db.Select(&pokemons, "SELECT * FROM pokemon")
	if err != nil {
		return nil, errors.WithStack(err)
	}
	return pokemons, nil
}

func (p *Package) GetHavePokemons() ([]Pokemon, error) {
	var pokemons []Pokemon
	err := p.Db.Select(&pokemons, "SELECT pokemon.id, pokemon.dex_number, pokemon.region, "+
		"have_pokemon.shiny as shiny_enabled, pokemon.name, pokemon.shiny_image_name, pokemon.image_name "+
		"FROM pokemon JOIN have_pokemon ON have_pokemon.pokemon_id = pokemon.id")
	if err != nil {
		return nil, errors.WithStack(err)
	}
	return pokemons, nil
}

func (p *Package) GetWantGroups() ([]model.WantGroup, error) {
	var wantGroups []model.WantGroup
	err := p.Db.Select(&wantGroups, "SELECT * FROM want_group_pokemon")
	if err != nil {
		return nil, errors.WithStack(err)
	}
	return wantGroups, nil
}

func (p *Package) GetWantPokemonByWantGroup(wantGroupID int) ([]Pokemon, error) {
	pokemons := []Pokemon{}
	err := p.Db.Select(&pokemons, "SELECT pokemon.* FROM want_pokemon JOIN pokemon ON want_pokemon.pokemon_id = pokemon.id AND want_pokemon.want_group_id = $1", wantGroupID)
	if err != nil {
		return nil, errors.WithStack(err)
	}
	return pokemons, nil
}

func (p *Package) CreateWantGroup(wantGroup model.WantGroup) error {
	insertQuery := `INSERT INTO want_group_pokemon(group_name) VALUES (:group_name)`
	_, err := p.Db.NamedExec(insertQuery, wantGroup)
	if err != nil {
		return errors.WithStack(err)
	}
	return nil
}

func (p *Package) CreateWantPokemon(wantPokemon model.WantPokemon) error {
	insertQuery := `INSERT INTO want_pokemon(pokemon_id, want_group_id, shiny) VALUES (:pokemon_id, :want_group_id, :shiny)`
	_, err := p.Db.NamedExec(insertQuery, wantPokemon)
	if err != nil {
		return errors.WithStack(err)
	}
	return nil
}

func (p *Package) DeleteWantGroup(id int) error {
	insertQuery := "DELETE FROM want_group_pokemon WHERE id = $1"
	_, err := p.Db.Exec(insertQuery, id)
	if err != nil {
		return errors.WithStack(err)
	}
	return nil
}

func (p *Package) StoreHavePokemons(havePokemons []HavePokemon) error {
	insertQuery := `INSERT INTO have_pokemon (pokemon_id, shiny) VALUES (:pokemon_id, :shiny)`
	for _, havePokemon := range havePokemons {
		_, err := p.Db.NamedExec(insertQuery, havePokemon)
		if err != nil {
			return errors.WithStack(err)
		}
	}
	return nil
}