From 2a591695341590d5b593af468fcf4fcfaec828f7 Mon Sep 17 00:00:00 2001
From: Jakub Czajka <jakub@ekhem.eu.org>
Date: Sun, 14 Jan 2024 00:35:53 +0100
Subject: [PATCH] Track categories of products.

---
 create.sql  | 23 +++++++++++++++++++----
 drop.sql    | 16 +++++++++++-----
 payment.sh  | 11 ++++++++++-
 payments.sh |  6 ++++++
 4 files changed, 46 insertions(+), 10 deletions(-)

diff --git a/create.sql b/create.sql
index 9e1a462..2ee1738 100644
--- a/create.sql
+++ b/create.sql
@@ -11,6 +11,17 @@ CREATE TABLE IF NOT EXISTS products (
     name VARCHAR(128) NOT NULL UNIQUE
 );
 
+CREATE TABLE IF NOT EXISTS categories (
+    id SERIAL PRIMARY KEY,
+    name VARCHAR(128) NOT NULL UNIQUE
+);
+
+CREATE TABLE IF NOT EXISTS products_categories (
+    product_id INTEGER REFERENCES products (id),
+    category_id INTEGER REFERENCES categories (id),
+    PRIMARY KEY (product_id, category_id)
+);
+
 CREATE TABLE IF NOT EXISTS payment_methods (
     id SERIAL PRIMARY KEY,
     name VARCHAR(128) NOT NULL UNIQUE
@@ -39,19 +50,23 @@ BEGIN
         CREATE ROLE payments_tracker LOGIN;
 
         GRANT SELECT
-        ON TABLE shops, products, payment_methods, currencies, purchases
+        ON shops, products, categories, products_categories, payment_methods,
+            currencies, purchases
         TO payments_tracker;
 
         GRANT INSERT
-        ON TABLE shops, products, payment_methods, currencies, purchases
+        ON shops, products, categories, products_categories, payment_methods,
+            currencies, purchases
         TO payments_tracker;
 
         GRANT UPDATE
-        ON TABLE shops, products, payment_methods, currencies, purchases
+        ON shops, products, categories, products_categories, payment_methods,
+            currencies, purchases
         TO payments_tracker;
 
         GRANT USAGE, SELECT
-        ON SEQUENCE shops_id_seq, products_id_seq, payment_methods_id_seq, currencies_id_seq , purchases_id_seq
+        ON SEQUENCE shops_id_seq, products_id_seq, categories_id_seq,
+            payment_methods_id_seq, currencies_id_seq , purchases_id_seq
         TO payments_tracker;
 
         /* Execute for the current database. */
diff --git a/drop.sql b/drop.sql
index 339bbfe..9e2719f 100644
--- a/drop.sql
+++ b/drop.sql
@@ -6,23 +6,27 @@ BEGIN
     IF EXISTS (SELECT * FROM pg_user WHERE usename = 'payments_tracker')
     THEN
         REVOKE SELECT
-        ON TABLE shops, products, payment_methods, currencies, purchases
+        ON shops, products, categories, products_catagories, payment_methods,
+            currencies, purchases
         FROM payments_tracker;
 
         REVOKE INSERT
-        ON TABLE shops, products, payment_methods, currencies, purchases
+        ON shops, products, categories, products_catagories, payment_methods,
+            currencies, purchases
         FROM payments_tracker;
 
         REVOKE UPDATE
-        ON TABLE shops, products, payment_methods, currencies, purchases
+        ON shops, products, categories, products_catagories, payment_methods,
+            currencies, purchases
         FROM payments_tracker;
 
         REVOKE USAGE, SELECT
-        ON SEQUENCE shops_id_seq, products_id_seq, payment_methods_id_seq, currencies_id_seq, purchases_id_seq
+        ON SEQUENCE shops_id_seq, products_id_seq, categories_id_seq,
+            payment_methods_id_seq, currencies_id_seq , purchases_id_seq
         FROM payments_tracker;
 
         EXECUTE
-            FORMAT('REVOKE CONNECT
+                    FORMAT('REVOKE CONNECT
                     ON DATABASE %I
                     FROM payments_tracker;', current_database());
 
@@ -32,6 +36,8 @@ END$$;
 
 DROP TABLE IF EXISTS purchases;
 DROP TABLE IF EXISTS shops;
+DROP TABLE IF EXISTS products_categories;
+DROP TABLE IF EXISTS categories;
 DROP TABLE IF EXISTS products;
 DROP TABLE IF EXISTS payment_methods;
 DROP TABLE IF EXISTS currencies;
diff --git a/payment.sh b/payment.sh
index 0e5c2d1..da2cd51 100644
--- a/payment.sh
+++ b/payment.sh
@@ -37,6 +37,9 @@ insert_into() {
 shop=$(get_query_param "shop")
 shop_id=$(insert_into "shops" "${shop}")
 
+category=$(get_query_param "category")
+category_id=$(insert_into "categories" "${category}")
+
 product=$(get_query_param "product")
 product_id=$(insert_into "products" "${product}")
 
@@ -57,6 +60,11 @@ else
   discount="FALSE"
 fi
 
+query_db "\
+INSERT INTO products_categories (product_id, category_id)
+VALUES (${product_id}, ${category_id})
+ON CONFLICT DO NOTHING" > /dev/null
+
 purchase_id=$(query_db "\
 WITH new_purchase_id AS (
   INSERT INTO purchases (
@@ -96,7 +104,8 @@ PAGE="
   </style>
 </head>
 <body>
-Recorded new purchase (id=${purchase_id}) of ${product} \
+Associated ${product} (id=${product_id}) with category ${category} \
+(id=${category_id}). Recorded new purchase (id=${purchase_id}) of ${product} \
 (id=${product_id}) on ${purchase_date} in ${shop} (id=${shop_id}). Payed \
 ${price} ${currency} (id=${currency_id}) with ${payment_method} \
 (id=${payment_method_id}) (discount=${discount}).
diff --git a/payments.sh b/payments.sh
index 0a31e5c..2767388 100644
--- a/payments.sh
+++ b/payments.sh
@@ -51,6 +51,12 @@ PAGE="
         $(query_db "SELECT name FROM shops" | as_options)
       </datalist>
 
+      <input list='categories' id='category' name='category'
+        placeholder='Category' required />
+      <datalist id='categories'>
+        $(query_db "SELECT name FROM categories" | as_options)
+      </datalist>
+
       <input list='products' id='product' name='product' placeholder='Product'
         required />
       <datalist id='products'>
-- 
2.39.5