115 lines
3.4 KiB
Go
115 lines
3.4 KiB
Go
package db
|
|
|
|
import (
|
|
"fmt"
|
|
"time"
|
|
|
|
"charm.land/ssh"
|
|
"github.com/google/uuid"
|
|
"github.com/jmoiron/sqlx"
|
|
"golang.org/x/crypto/bcrypt"
|
|
gossh "golang.org/x/crypto/ssh"
|
|
"jp.wtf/go-fishing/internal/models"
|
|
)
|
|
|
|
type Database struct {
|
|
Conn *sqlx.DB
|
|
}
|
|
|
|
func (db *Database) GetAnglerByID(angler_id uuid.UUID) (models.Angler, error) {
|
|
var angler models.Angler
|
|
err := db.Conn.Get(&angler, "SELECT angler_id, angler_username, angler_email FROM anglers WHERE angler_id = $1", angler_id)
|
|
|
|
return angler, err
|
|
}
|
|
|
|
func (db *Database) GetAnglerByKey(ssh_key ssh.PublicKey) (models.Angler, error) {
|
|
var angler models.Angler
|
|
err := db.Conn.Get(&angler, "SELECT angler_id, angler_username, angler_email FROM ssh_keys INNER JOIN anglers USING (angler_id) WHERE key_data = $1", ssh_key.Marshal())
|
|
|
|
return angler, err
|
|
}
|
|
|
|
func (db *Database) GetRawAnglerByUsername(username string) (models.AnglerDB, error) {
|
|
var angler models.AnglerDB
|
|
err := db.Conn.Get(&angler, "SELECT angler_id, angler_username, angler_email, angler_password FROM anglers WHERE angler_username = $1", username)
|
|
|
|
return angler, err
|
|
}
|
|
|
|
func (db *Database) CreateNewAngler(username string, hashed_password string) (models.Angler, error) {
|
|
var new_angler models.Angler
|
|
err := db.Conn.Get(&new_angler, `
|
|
INSERT INTO public.anglers (angler_username, angler_password)
|
|
VALUES ($1, $2)
|
|
RETURNING angler_id, angler_username, angler_email
|
|
`, username, hashed_password)
|
|
|
|
return new_angler, err
|
|
}
|
|
|
|
func (db *Database) CreateNewAnglerFromKey(ssh_key ssh.PublicKey) (models.Angler, error) {
|
|
bytes, err := bcrypt.GenerateFromPassword([]byte(uuid.New().String()), bcrypt.DefaultCost)
|
|
|
|
if err != nil {
|
|
return models.Angler{}, err
|
|
}
|
|
|
|
fingerprint := gossh.FingerprintSHA256(ssh_key)
|
|
|
|
angler, err := db.CreateNewAngler(fingerprint, string(bytes))
|
|
if err != nil {
|
|
return models.Angler{}, err
|
|
}
|
|
|
|
if _, err := db.AttachKeyToAngler(angler.ID, ssh_key); err != nil {
|
|
return models.Angler{}, err
|
|
}
|
|
|
|
return angler, err
|
|
}
|
|
|
|
func (db *Database) AttachKeyToAngler(angler_id uuid.UUID, ssh_key ssh.PublicKey) (models.SSHKey, error) {
|
|
var new_key models.SSHKey
|
|
err := db.Conn.Get(&new_key, `
|
|
INSERT INTO public.ssh_keys (key_type, key_data, key_fingerprint, angler_id)
|
|
VALUES ($1, $2, $3, $4)
|
|
RETURNING key_id, key_type, key_data, key_fingerprint
|
|
`, ssh_key.Type(), ssh_key.Marshal(), gossh.FingerprintSHA256(ssh_key), angler_id)
|
|
|
|
return new_key, err
|
|
}
|
|
|
|
func (db *Database) IsCasting(angler_id uuid.UUID) (bool, error) {
|
|
var exists bool
|
|
err := db.Conn.Get(&exists, "SELECT EXISTS( SELECT 1 FROM public.casts WHERE angler_id = $1 AND cast_ended_at IS NULL )", angler_id)
|
|
|
|
if err != nil {
|
|
fmt.Print(err.Error())
|
|
return false, err
|
|
}
|
|
|
|
return exists, err
|
|
}
|
|
|
|
func (db *Database) GetMostRecentCast(angler_id uuid.UUID) (models.Cast, error) {
|
|
var cast models.Cast
|
|
err := db.Conn.Get(&cast, "SELECT cast_id, cast_started_at, cast_ended_at, catch_id FROM public.casts WHERE angler_id =! ORDER cast_started_at DESC LIMIT 1", angler_id)
|
|
|
|
return cast, err
|
|
}
|
|
|
|
func (db *Database) CreateNewCast(angler_id uuid.UUID) (models.Cast, error) {
|
|
var cast models.Cast
|
|
err := db.Conn.Get(&cast, "INSERT INTO public.casts (angler_id) VALUES ($1) RETURNING *", angler_id)
|
|
|
|
return cast, err
|
|
}
|
|
|
|
func (db *Database) EndCastByID(cast_id uuid.UUID) (models.Cast, error) {
|
|
var cast models.Cast
|
|
err := db.Conn.Get(&cast, "UPDATE public.casts SET cast_ended_at = $1 WHERE cast_id = $2 RETURNING *", time.Now(), cast_id)
|
|
|
|
return cast, err
|
|
}
|