From 95d4db4da725b74999524a1307e5e17b434dbfeb Mon Sep 17 00:00:00 2001
From: Joe Carstairs <me@joeac.net>
Date: Sat, 31 Aug 2024 22:43:00 +0100
Subject: Backend

---
 backend/migrations/.keep                           |  0
 .../2024-08-31-084439_initial_setup/down.sql       |  6 ++
 .../2024-08-31-084439_initial_setup/up.sql         | 66 ++++++++++++++++++++++
 3 files changed, 72 insertions(+)
 create mode 100644 backend/migrations/.keep
 create mode 100644 backend/migrations/2024-08-31-084439_initial_setup/down.sql
 create mode 100644 backend/migrations/2024-08-31-084439_initial_setup/up.sql

(limited to 'backend/migrations')

diff --git a/backend/migrations/.keep b/backend/migrations/.keep
new file mode 100644
index 0000000..e69de29
diff --git a/backend/migrations/2024-08-31-084439_initial_setup/down.sql b/backend/migrations/2024-08-31-084439_initial_setup/down.sql
new file mode 100644
index 0000000..5e68c99
--- /dev/null
+++ b/backend/migrations/2024-08-31-084439_initial_setup/down.sql
@@ -0,0 +1,6 @@
+DROP TABLE accounts;
+DROP TABLE budget_updates;
+DROP TABLE categories;
+DROP TABLE category_transactions;
+DROP TABLE category_transfers;
+DROP TABLE transactions;
diff --git a/backend/migrations/2024-08-31-084439_initial_setup/up.sql b/backend/migrations/2024-08-31-084439_initial_setup/up.sql
new file mode 100644
index 0000000..f2461a5
--- /dev/null
+++ b/backend/migrations/2024-08-31-084439_initial_setup/up.sql
@@ -0,0 +1,66 @@
+PRAGMA foreign_keys = ON;
+
+CREATE TABLE accounts(
+    id INTEGER NOT NULL PRIMARY KEY,
+    name TEXT NOT NULL,
+    opening_balance INTEGER NOT NULL,
+    opening_date TEXT NOT NULL
+);
+
+CREATE TABLE budget_updates(
+    id INTEGER NOT NULL PRIMARY KEY,
+    category_id INTEGER NOT NULL,
+    date TEXT NOT NULL,
+    new_budget INTEGER NOT NULL,
+    new_period INTEGER NOT NULL,
+    FOREIGN KEY (category_id)
+        REFERENCES categories (id)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT
+);
+
+CREATE TABLE categories(
+    id INTEGER NOT NULL PRIMARY KEY,
+    name TEXT NOT NULL,
+    initial_budget INTEGER NOT NULL,
+    initial_period INTEGER NOT NULL
+);
+
+CREATE TABLE category_transactions(
+    id INTEGER NOT NULL PRIMARY KEY,
+    description TEXT NOT NULL,
+    quantity INTEGER NOT NULL,
+    category_id INTEGER NOT NULL,
+    FOREIGN KEY (category_id)
+        REFERENCES categories (id)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT
+);
+
+CREATE TABLE category_transfers(
+    id INTEGER NOT NULL PRIMARY KEY,
+    description TEXT NOT NULL,
+    quantity INTEGER NOT NULL,
+    from_category_id INTEGER NOT NULL,
+    to_category_id INTEGER NOT NULL,
+    FOREIGN KEY (from_category_id)
+        REFERENCES categories (id)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT,
+    FOREIGN KEY (to_category_id)
+        REFERENCES categories (id)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT
+);
+
+CREATE TABLE transactions(
+    id INTEGER NOT NULL PRIMARY KEY,
+    description TEXT NOT NULL,
+    payee TEXT NOT NULL,
+    quantity INTEGER NOT NULL,
+    account_id INTEGER NOT NULL,
+    FOREIGN KEY (account_id)
+        REFERENCES accounts (id)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT
+);
-- 
cgit v1.2.3