hatecomputers.club/database/kennel.go

package database

import (
	"database/sql"
	_ "github.com/mattn/go-sqlite3"
	"log"
	"time"
)

type KennelCat struct {
	ID          string    `json:"id"`
	UserID      string    `json:"user_id"`
	Name        string    `json:"name"`
	Link        string    `json:"link"`
	Description string    `json:"description"`
	Spritesheet string    `json:"spritesheet"`
	CreatedAt   time.Time `json:"created_at"`
}

type KennelState struct {
	At           time.Time `json:"at"`
	EncodedState string    `json:"state"`
}

func CountUserKennelCats(db *sql.DB, userID string) (int, error) {
	log.Println("counting kennel cats for user", userID)

	row := db.QueryRow("SELECT COUNT(*) FROM kennel_cat WHERE user_id = ?", userID)
	var count int
	err := row.Scan(&count)
	if err != nil {
		return 0, err
	}
	return count, nil
}

func GetUserKennelCats(db *sql.DB, userID string) ([]KennelCat, error) {
	log.Println("getting kennel cats for user", userID)

	rows, err := db.Query("SELECT * FROM kennel_cat WHERE user_id = ?", userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var cats []KennelCat
	for rows.Next() {
		var cat KennelCat
		err := rows.Scan(&cat.ID, &cat.Name, &cat.UserID, &cat.Link, &cat.Description, &cat.Spritesheet, &cat.CreatedAt)
		if err != nil {
			return nil, err
		}
		cats = append(cats, cat)
	}

	return cats, nil
}

func SaveKennelCat(db *sql.DB, cat *KennelCat) (*KennelCat, error) {
	log.Println("saving kennel cat", cat.ID)

	if (cat.CreatedAt == time.Time{}) {
		cat.CreatedAt = time.Now()
	}

	_, err := db.Exec("INSERT OR REPLACE INTO kennel_cat (id, user_id, name, link, description, spritesheet, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)", cat.ID, cat.UserID, cat.Name, cat.Link, cat.Description, cat.Spritesheet, cat.CreatedAt)

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

func GetKennelCat(db *sql.DB, catID string) (*KennelCat, error) {
	log.Println("getting kennel cat", catID)

	row := db.QueryRow("SELECT * FROM kennel_cat WHERE id = ?", catID)
	var cat KennelCat
	err := row.Scan(&cat.ID, &cat.Name, &cat.UserID, &cat.Link, &cat.Description, &cat.Spritesheet, &cat.CreatedAt)
	if err != nil {
		return nil, err
	}
	return &cat, nil
}

func DeleteKennelCat(db *sql.DB, catID string) error {
	log.Println("deleting kennel cat", catID)

	_, err := db.Exec("DELETE FROM kennel_cat WHERE id = ?", catID)
	if err != nil {
		return err
	}
	return nil
}

func GetRandomKennelCat(dbConn *sql.DB) (*KennelCat, error) {
	log.Println("getting random kennel cat")

	row := dbConn.QueryRow("SELECT * FROM kennel_cat ORDER BY RANDOM() LIMIT 1")
	var cat KennelCat
	err := row.Scan(&cat.ID, &cat.Name, &cat.UserID, &cat.Link, &cat.Description, &cat.Spritesheet, &cat.CreatedAt)
	if err != nil {
		return nil, err
	}
	return &cat, nil
}

func GetNextKennelCat(dbConn *sql.DB, lastID string, next bool) (*KennelCat, error) {
	log.Println("getting next kennel cat")

	operation := ">"
	sorted := "ASC"
	if !next {
		operation = "<"
		sorted = "DESC"
	}

	row := dbConn.QueryRow("SELECT * FROM kennel_cat WHERE id "+operation+" ? ORDER BY id "+sorted+" LIMIT 1", lastID)

	var cat KennelCat
	err := row.Scan(&cat.ID, &cat.Name, &cat.UserID, &cat.Link, &cat.Description, &cat.Spritesheet, &cat.CreatedAt)
	if err != nil {
		if next {
			// loop "back" to the first in the ring
			row = dbConn.QueryRow("SELECT * FROM kennel_cat ORDER BY id ASC LIMIT 1")
		} else {
			// loop "forward" to the first in the ring
			row = dbConn.QueryRow("SELECT * FROM kennel_cat ORDER BY id DESC LIMIT 1")
		}
		err = row.Scan(&cat.ID, &cat.Name, &cat.UserID, &cat.Link, &cat.Description, &cat.Spritesheet, &cat.CreatedAt)
	}
	if err != nil {
		return nil, err
	}

	return &cat, nil
}

func GetKennel(dbConn *sql.DB) ([]KennelCat, error) {
	log.Println("getting kennel")

	rows, err := dbConn.Query("SELECT * FROM kennel_cat")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var cats []KennelCat
	for rows.Next() {
		var cat KennelCat
		err := rows.Scan(&cat.ID, &cat.Name, &cat.UserID, &cat.Link, &cat.Description, &cat.Spritesheet, &cat.CreatedAt)
		if err != nil {
			return nil, err
		}
		cats = append(cats, cat)
	}

	return cats, nil
}