kennel (#13)

Reviewed-on: https://git.hatecomputers.club/hatecomputers/hatecomputers.club/pulls/13
Co-authored-by: Elizabeth Hunt <elizabeth@simponic.xyz>
Co-committed-by: Elizabeth Hunt <elizabeth@simponic.xyz>

2 files changed, 180 insertions, 0 deletions

diff --git a/database/kennel.go b/database/kennel.go
new file mode 100644
index 0000000..91525db
--- /dev/null
+++ b/database/kennel.go
@@ -0,0 +1,159 @@
+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
+}
diff --git a/database/migrate.go b/database/migrate.go
index e9e21b7..0c8318c 100644
--- a/database/migrate.go
+++ b/database/migrate.go
@@ -162,6 +162,26 @@ func MigrateProfiles(dbConn *sql.DB) (*sql.DB, error) {
 	return dbConn, nil
 }
 
+func MigrateKennel(dbConn *sql.DB) (*sql.DB, error) {
+	log.Println("migrating kennel tables")
+
+	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS kennel_cat (
+                id TEXT PRIMARY KEY,
+                name TEXT NOT NULL,
+		user_id INTEGER NOT NULL,
+                link TEXT NOT NULL,
+                description TEXT NOT NULL,
+                spritesheet TEXT NOT NULL,
+		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+		FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
+	);`)
+	if err != nil {
+		return dbConn, err
+	}
+
+	return dbConn, nil
+}
+
 func Migrate(dbConn *sql.DB) (*sql.DB, error) {
 	log.Println("migrating database")
 
@@ -173,6 +193,7 @@ func Migrate(dbConn *sql.DB) (*sql.DB, error) {
 		MigrateDNSRecords,
 		MigrateGuestBook,
 		MigrateProfiles,
+		MigrateKennel,
 	}
 
 	for _, migration := range migrations {