From 4ebd772e57469345120187fe01d956a3295d1468 Mon Sep 17 00:00:00 2001
From: René 'Necoro' Neumann <necoro@necoro.eu>
Date: Sun, 25 Feb 2024 21:15:40 +0100
Subject: Small model changes / Reformatting

---
 main.go           |  2 +-
 model/models.go   |  7 +++---
 sql/ddl/pgsql.sql | 74 +++++++++++++++++++++++++------------------------------
 3 files changed, 38 insertions(+), 45 deletions(-)

diff --git a/main.go b/main.go
index 8ed57bf..118262b 100644
--- a/main.go
+++ b/main.go
@@ -82,6 +82,6 @@ func indexPage() http.HandlerFunc {
 	return func(w http.ResponseWriter, r *http.Request) {
 		uid := userId(r)
 		u, _ := Q.GetUserById(r.Context(), uid)
-		showTemplate(w, "index", u.Name)
+		showTemplate(w, "content", u.Name)
 	}
 }
diff --git a/model/models.go b/model/models.go
index eba1207..2ac5a4b 100644
--- a/model/models.go
+++ b/model/models.go
@@ -19,7 +19,7 @@ type ConstExpense struct {
 	ID          int32
 	Description pgtype.Text
 	Expense     pgtype.Numeric
-	Months      int16
+	Duration    pgtype.Interval
 	Start       pgtype.Date
 	End         pgtype.Date
 	PrevID      pgtype.Int4
@@ -31,9 +31,8 @@ type SingleExpense struct {
 	ID          int64
 	Description pgtype.Text
 	Expense     pgtype.Numeric
-	Year        int16
-	Month       int16
-	Day         int16
+	Date        pgtype.Date
+	CorrMonth   pgtype.Date
 	CategoryID  int32
 	UserID      int32
 }
diff --git a/sql/ddl/pgsql.sql b/sql/ddl/pgsql.sql
index 18c0661..09c7133 100644
--- a/sql/ddl/pgsql.sql
+++ b/sql/ddl/pgsql.sql
@@ -1,51 +1,45 @@
-create table users
-(
-    id          serial primary key,
-    name        text not null,
-    pwd         text not null,
-    description text
+CREATE TABLE users (
+    id          SERIAL PRIMARY KEY,
+    name        TEXT NOT NULL UNIQUE,
+    pwd         TEXT NOT NULL,
+    description TEXT
 );
 
-create unique index users_name ON users(name);
-
-create table categories
-(
-    id        serial primary key,
-    name      text not null,
-    parent_id integer references categories,
-    user_id   integer not null references users
+CREATE TABLE categories (
+    id        SERIAL PRIMARY KEY,
+    name      TEXT    NOT NULL,
+    parent_id INTEGER REFERENCES categories,
+    user_id   INTEGER NOT NULL REFERENCES users
 );
 
-create index parent_id
-    on categories (parent_id);
+CREATE INDEX parent_id
+    ON categories (parent_id);
 
-create index user_id
-    on categories (user_id);
+CREATE INDEX user_id
+    ON categories (user_id);
 
 
-create table const_expenses
-(
-    id          serial primary key,
-    description text,
-    expense     numeric(10, 2) not null,
-    months      smallint not null,
-    start       date not null,
-    "end"       date not null,
-    prev_id     integer references const_expenses,
-    category_id integer not null references categories,
-    user_id     integer not null references users
+CREATE TABLE const_expenses (
+    id          SERIAL PRIMARY KEY,
+    description TEXT,
+    expense     NUMERIC(10, 2) NOT NULL,
+    duration    INTERVAL       NOT NULL,
+    start       DATE           NOT NULL,
+    "end"       DATE           NOT NULL,
+    prev_id     INTEGER REFERENCES const_expenses,
+    category_id INTEGER        NOT NULL REFERENCES categories,
+    user_id     INTEGER        NOT NULL REFERENCES users
 );
 
-create table single_expenses
-(
-    id          bigserial primary key,
-    description text,
-    expense     numeric(10, 2) not null,
-    year        smallint not null,
-    month       smallint not null,
-    day         smallint not null,
-    category_id integer not null references categories,
-    user_id     integer not null references users
+CREATE TABLE single_expenses (
+    id          BIGSERIAL PRIMARY KEY,
+    description TEXT,
+    expense     NUMERIC(10, 2) NOT NULL,
+    date        DATE           NOT NULL,
+    -- we need the cast to timestamp, because it is only considered immutable then
+    corr_month  DATE GENERATED ALWAYS AS (DATE_TRUNC('month', date::timestamp)) STORED,
+    category_id INTEGER        NOT NULL REFERENCES categories,
+    user_id     INTEGER        NOT NULL REFERENCES users
 );
 
-CREATE INDEX idx_single_date ON single_expenses(user_id, year, month);
\ No newline at end of file
+CREATE INDEX idx_single_month ON single_expenses (user_id, corr_month);
\ No newline at end of file
-- 
cgit v1.2.3-70-g09d2