Add calendar support

6 files changed, 141 insertions, 41 deletions

diff --git a/pkg/database/migrations/0001_initial.sql b/pkg/database/migrations/0001_initial.sql
index eea0a73..a1f5fda 100644
--- a/pkg/database/migrations/0001_initial.sql
+++ b/pkg/database/migrations/0001_initial.sql
@@ -1,8 +1,8 @@
 -- +goose Up
 CREATE TABLE users (
     id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
-    username varchar(20) UNIQUE NOT NULL,
-    password char(60) NOT NULL
+    username varchar(20) UNIQUE NOT NULL CONSTRAINT non_blank_username CHECK(length(username) > 0),
+    password text NOT NULL CONSTRAINT valid_hash CHECK (length(password) = 60)
 );
 
 CREATE TABLE favourites (
@@ -11,7 +11,16 @@ CREATE TABLE favourites (
     event_guid uuid,
     event_id int,
     UNIQUE(user_id, event_guid, event_id),
-    CONSTRAINT chk_favourites CHECK (event_guid IS NOT NULL OR event_id IS NOT NULL),
-    FOREIGN KEY (user_id) REFERENCES users(id)
+    CONSTRAINT require_event_detail CHECK (event_guid IS NOT NULL OR event_id IS NOT NULL),
+    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
 );
 
+CREATE TABLE calendars (
+    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
+    user_id int NOT NULL,
+    name text NOT NULL CONSTRAINT non_blank_name CHECK(length(name) > 0),
+    key text NOT NULL,
+    UNIQUE(user_id),
+    UNIQUE(name),
+    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
+);
diff --git a/pkg/database/query/calendars.sql b/pkg/database/query/calendars.sql
new file mode 100644
index 0000000..490475d
--- /dev/null
+++ b/pkg/database/query/calendars.sql
@@ -0,0 +1,23 @@
+-- name: CreateCalendar :one
+INSERT INTO calendars (
+  user_id, name, key
+) VALUES (
+  $1, $2, $3
+)
+RETURNING *;
+
+-- name: GetCalendarForUser :one
+SELECT * FROM calendars
+WHERE user_id = $1 LIMIT 1;
+
+-- name: GetCalendarByName :one
+SELECT * FROM calendars
+WHERE name = $1 LIMIT 1;
+
+-- name: DeleteCalendar :execrows
+DELETE FROM calendars
+WHERE user_id = $1;
+
+-- name: DeleteCalendarByName :execrows
+DELETE FROM calendars
+WHERE name = $1;
diff --git a/pkg/database/query/favourites.sql b/pkg/database/query/favourites.sql
index 0661daa..94e914c 100644
--- a/pkg/database/query/favourites.sql
+++ b/pkg/database/query/favourites.sql
@@ -16,4 +16,4 @@ WHERE id = $1;
 
 -- name: DeleteFavouriteByEventDetails :execrows
 DELETE FROM favourites
-WHERE (event_guid = $1 OR event_id = $2) AND user_id = $3;
\ No newline at end of file
+WHERE (event_guid = $1 OR event_id = $2) AND user_id = $3;
diff --git a/pkg/database/sqlc/calendars.sql.go b/pkg/database/sqlc/calendars.sql.go
new file mode 100644
index 0000000..47ae37f
--- /dev/null
+++ b/pkg/database/sqlc/calendars.sql.go
@@ -0,0 +1,97 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.27.0
+// source: calendars.sql
+
+package sqlc
+
+import (
+	"context"
+)
+
+const createCalendar = `-- name: CreateCalendar :one
+INSERT INTO calendars (
+  user_id, name, key
+) VALUES (
+  $1, $2, $3
+)
+RETURNING id, user_id, name, key
+`
+
+type CreateCalendarParams struct {
+	UserID int32  `json:"user_id"`
+	Name   string `json:"name"`
+	Key    string `json:"key"`
+}
+
+func (q *Queries) CreateCalendar(ctx context.Context, arg CreateCalendarParams) (Calendar, error) {
+	row := q.db.QueryRow(ctx, createCalendar, arg.UserID, arg.Name, arg.Key)
+	var i Calendar
+	err := row.Scan(
+		&i.ID,
+		&i.UserID,
+		&i.Name,
+		&i.Key,
+	)
+	return i, err
+}
+
+const deleteCalendar = `-- name: DeleteCalendar :execrows
+DELETE FROM calendars
+WHERE user_id = $1
+`
+
+func (q *Queries) DeleteCalendar(ctx context.Context, userID int32) (int64, error) {
+	result, err := q.db.Exec(ctx, deleteCalendar, userID)
+	if err != nil {
+		return 0, err
+	}
+	return result.RowsAffected(), nil
+}
+
+const deleteCalendarByName = `-- name: DeleteCalendarByName :execrows
+DELETE FROM calendars
+WHERE name = $1
+`
+
+func (q *Queries) DeleteCalendarByName(ctx context.Context, name string) (int64, error) {
+	result, err := q.db.Exec(ctx, deleteCalendarByName, name)
+	if err != nil {
+		return 0, err
+	}
+	return result.RowsAffected(), nil
+}
+
+const getCalendarByName = `-- name: GetCalendarByName :one
+SELECT id, user_id, name, key FROM calendars
+WHERE name = $1 LIMIT 1
+`
+
+func (q *Queries) GetCalendarByName(ctx context.Context, name string) (Calendar, error) {
+	row := q.db.QueryRow(ctx, getCalendarByName, name)
+	var i Calendar
+	err := row.Scan(
+		&i.ID,
+		&i.UserID,
+		&i.Name,
+		&i.Key,
+	)
+	return i, err
+}
+
+const getCalendarForUser = `-- name: GetCalendarForUser :one
+SELECT id, user_id, name, key FROM calendars
+WHERE user_id = $1 LIMIT 1
+`
+
+func (q *Queries) GetCalendarForUser(ctx context.Context, userID int32) (Calendar, error) {
+	row := q.db.QueryRow(ctx, getCalendarForUser, userID)
+	var i Calendar
+	err := row.Scan(
+		&i.ID,
+		&i.UserID,
+		&i.Name,
+		&i.Key,
+	)
+	return i, err
+}
diff --git a/pkg/database/sqlc/favourites.sql.go b/pkg/database/sqlc/favourites.sql.go
index 3bf7c06..359ae9d 100644
--- a/pkg/database/sqlc/favourites.sql.go
+++ b/pkg/database/sqlc/favourites.sql.go
@@ -67,42 +67,6 @@ func (q *Queries) DeleteFavouriteByEventDetails(ctx context.Context, arg DeleteF
 	return result.RowsAffected(), nil
 }
 
-const deleteFavouriteByEventGuid = `-- name: DeleteFavouriteByEventGuid :execrows
-DELETE FROM favourites
-WHERE event_guid = $1 AND user_id = $2
-`
-
-type DeleteFavouriteByEventGuidParams struct {
-	EventGuid pgtype.UUID `json:"event_guid"`
-	UserID    int32       `json:"user_id"`
-}
-
-func (q *Queries) DeleteFavouriteByEventGuid(ctx context.Context, arg DeleteFavouriteByEventGuidParams) (int64, error) {
-	result, err := q.db.Exec(ctx, deleteFavouriteByEventGuid, arg.EventGuid, arg.UserID)
-	if err != nil {
-		return 0, err
-	}
-	return result.RowsAffected(), nil
-}
-
-const deleteFavouriteByEventId = `-- name: DeleteFavouriteByEventId :execrows
-DELETE FROM favourites
-WHERE event_id = $1 AND user_id = $2
-`
-
-type DeleteFavouriteByEventIdParams struct {
-	EventID pgtype.Int4 `json:"event_id"`
-	UserID  int32       `json:"user_id"`
-}
-
-func (q *Queries) DeleteFavouriteByEventId(ctx context.Context, arg DeleteFavouriteByEventIdParams) (int64, error) {
-	result, err := q.db.Exec(ctx, deleteFavouriteByEventId, arg.EventID, arg.UserID)
-	if err != nil {
-		return 0, err
-	}
-	return result.RowsAffected(), nil
-}
-
 const getFavouritesForUser = `-- name: GetFavouritesForUser :many
 SELECT id, user_id, event_guid, event_id FROM favourites
 WHERE user_id = $1
diff --git a/pkg/database/sqlc/models.go b/pkg/database/sqlc/models.go
index 09208aa..e38851a 100644
--- a/pkg/database/sqlc/models.go
+++ b/pkg/database/sqlc/models.go
@@ -8,6 +8,13 @@ import (
 	"github.com/jackc/pgx/v5/pgtype"
 )
 
+type Calendar struct {
+	ID     int32  `json:"id"`
+	UserID int32  `json:"user_id"`
+	Name   string `json:"name"`
+	Key    string `json:"key"`
+}
+
 type Favourite struct {
 	ID        int32       `json:"id"`
 	UserID    int32       `json:"user_id"`