1 files changed, 70 insertions, 0 deletions
diff --git a/pkg/service/places.go b/pkg/service/places.go
new file mode 100644
index 0000000..d088836
--- /dev/null
+++ b/pkg/service/places.go
@@ -0,0 +1,70 @@
+package service
+
+import (
+	"context"
+	"database/sql"
+
+	"git.neonxp.ru/neonxp/guessr/pkg/model"
+	"github.com/uptrace/bun"
+)
+
+type Places struct {
+	db *bun.DB
+}
+
+func New(db *bun.DB) *Places {
+	return &Places{db: db}
+}
+
+func (p *Places) GetNext(ctx context.Context) (*model.Place, error) {
+	r := new(model.Place)
+	btx, err := p.db.BeginTx(ctx, &sql.TxOptions{})
+	if err != nil {
+		return nil, err
+	}
+
+	err = btx.NewSelect().
+		ColumnExpr(`p.guid, p.img`).
+		Model(r).
+		Where(`p.count = (SELECT MIN(pl.count) FROM places pl WHERE pl.deleted_at IS NULL)`).
+		OrderExpr(`RANDOM()`).
+		Limit(1).
+		Scan(ctx, r)
+	if err != nil {
+		return nil, err
+	}
+	_, err = btx.NewUpdate().
+		Model(r).
+		Set(`count = count + 1`).
+		WherePK("guid").
+		Exec(ctx)
+	if err != nil {
+		return nil, err
+	}
+
+	return r, btx.Commit()
+}
+
+func (p *Places) Guess(ctx context.Context, guid string, lat, lon float32) (*GuessResult, error) {
+	r := &GuessResult{}
+	err := p.db.NewSelect().
+		Model(&model.Place{GUID: guid}).
+		WherePK("guid").
+		ColumnExpr(`p.name, p.guid, p.img,
+			ST_Distance(ST_MakePoint(?, ?)::geography, p.position::geography)::int AS distance,
+			ST_AsGeoJSON(ST_MakeLine(
+				ST_SetSRID(ST_MakePoint(?, ?), 4326),
+				ST_SetSRID(p.position, 4326)
+			)) AS geojson`, lon, lat, lon, lat).
+		Scan(ctx, r)
+
+	return r, err
+}
+
+type GuessResult struct {
+	GUID     string `json:"guid"`
+	Img      string `json:"img"`
+	Name     string `json:"name"`
+	Geojson  any    `json:"geojson"`
+	Distance int    `json:"distance"`
+}