From 4c09524e503d0e5648dcd46a8121360c927202d2 Mon Sep 17 00:00:00 2001
From: Jakub Czajka <jakub@ekhem.eu.org>
Date: Sun, 31 Dec 2023 12:30:44 +0100
Subject: [PATCH] Add files for creating and dropping the database.

---
 create.sql | 68 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
 drop.sql   | 38 ++++++++++++++++++++++++++++++
 2 files changed, 106 insertions(+)
 create mode 100644 create.sql
 create mode 100644 drop.sql

diff --git a/create.sql b/create.sql
new file mode 100644
index 0000000..3f28efb
--- /dev/null
+++ b/create.sql
@@ -0,0 +1,68 @@
+/* Copyright (c) 2023 Jakub Czajka <jakub@ekhem.eu.org>
+   License: GPL-3.0 or later. */
+
+CREATE TABLE IF NOT EXISTS shops (
+    id SERIAL PRIMARY KEY,
+    name VARCHAR(128) NOT NULL UNIQUE
+);
+
+CREATE TABLE IF NOT EXISTS products (
+    id SERIAL PRIMARY KEY,
+    name VARCHAR(128) NOT NULL UNIQUE
+);
+
+CREATE TABLE IF NOT EXISTS payment_methods (
+    id SERIAL PRIMARY KEY,
+    name VARCHAR(128) NOT NULL UNIQUE
+);
+
+CREATE TABLE IF NOT EXISTS currencies (
+    id SERIAL PRIMARY KEY,
+    name VARCHAR(128) NOT NULL UNIQUE
+);
+
+CREATE TABLE IF NOT EXISTS units (
+    id SERIAL PRIMARY KEY,
+    name VARCHAR(128) NOT NULL UNIQUE
+);
+
+CREATE TABLE IF NOT EXISTS purchases (
+    id SERIAL PRIMARY KEY,
+    shop_id INTEGER REFERENCES shops (id),
+    product_id INTEGER REFERENCES products (id),
+    payment_method_id INTEGER REFERENCES payment_methods (id),
+    date DATE DEFAULT CURRENT_DATE,
+    price DECIMAL(12,2) NOT NULL,
+    currency_id INTEGER REFERENCES currencies (id),
+    unit_id INTEGER REFERENCES units (id),
+    discount BOOLEAN DEFAULT FALSE
+);
+
+DO $$
+BEGIN
+    IF NOT EXISTS (SELECT * FROM pg_user WHERE usename = 'payments_tracker')
+    THEN
+        CREATE ROLE payments_tracker LOGIN;
+
+        GRANT SELECT
+        ON TABLE shops, products, payment_methods, currencies, units, purchases
+        TO payments_tracker;
+
+        GRANT INSERT
+        ON TABLE shops, products, payment_methods, currencies, units, purchases
+        TO payments_tracker;
+
+        GRANT UPDATE
+        ON TABLE shops, products, payment_methods, currencies, units, purchases
+        TO payments_tracker;
+
+        GRANT USAGE, SELECT
+        ON SEQUENCE shops_id_seq, products_id_seq, payment_methods_id_seq, currencies_id_seq , units_id_seq, purchases_id_seq
+        TO payments_tracker;
+
+        /* Execute for the current database. */
+        EXECUTE FORMAT('GRANT CONNECT
+                        ON DATABASE %I
+                        TO payments_tracker', current_database());
+    END IF;
+END$$;
diff --git a/drop.sql b/drop.sql
new file mode 100644
index 0000000..76029f7
--- /dev/null
+++ b/drop.sql
@@ -0,0 +1,38 @@
+/* Copyright (c) 2023 Jakub Czajka <jakub@ekhem.eu.org>
+   License: GPL-3.0 or later. */
+
+DO $$
+BEGIN
+    IF EXISTS (SELECT * FROM pg_user WHERE usename = 'payments_tracker')
+    THEN
+        REVOKE SELECT
+        ON TABLE shops, products, payment_methods, currencies, units, purchases
+        FROM payments_tracker;
+
+        REVOKE INSERT
+        ON TABLE shops, products, payment_methods, currencies, units, purchases
+        FROM payments_tracker;
+
+        REVOKE UPDATE
+        ON TABLE shops, products, payment_methods, currencies, units, purchases
+        FROM payments_tracker;
+
+        REVOKE USAGE, SELECT
+        ON SEQUENCE shops_id_seq, products_id_seq, payment_methods_id_seq, currencies_id_seq, units_id_seq, purchases_id_seq
+        FROM payments_tracker;
+
+        EXECUTE
+            FORMAT('REVOKE CONNECT
+                    ON DATABASE %I
+                    FROM payments_tracker;', current_database());
+
+        DROP ROLE payments_tracker;
+    END IF;
+END$$;
+
+DROP TABLE IF EXISTS purchases;
+DROP TABLE IF EXISTS shops;
+DROP TABLE IF EXISTS products;
+DROP TABLE IF EXISTS payment_methods;
+DROP TABLE IF EXISTS currencies;
+DROP TABLE IF EXISTS units;
-- 
2.39.5