From b629b237da6c7618ae7e679a2c9b879c6019662d Mon Sep 17 00:00:00 2001
From: Cara Salter <cara@devcara.com>
Date: Sun, 23 Apr 2023 15:04:34 -0400
Subject: sql: Implement bot state

Allows for persistent, dynamic storage of configuration options outside
of the config.json file.

The bot_state table is, essentially, a key-value store for config
options. There are two new prepared statements, `state` and
`update_state`:

- `state` will return the value for the provided key
- `update_state` will create the key or update an existing key
---
 include/142bot/bot.hpp               |  1 +
 migrations/0002-state.sql            |  5 +++++
 migrations/0003-unique-state-key.sql |  2 ++
 modules/spotify/spotify.cpp          | 23 +++++++++++++++++++++--
 src/bot.cpp                          | 11 +++++++++++
 5 files changed, 40 insertions(+), 2 deletions(-)
 create mode 100644 migrations/0002-state.sql
 create mode 100644 migrations/0003-unique-state-key.sql

diff --git a/include/142bot/bot.hpp b/include/142bot/bot.hpp
index 342f32e..1fdb042 100644
--- a/include/142bot/bot.hpp
+++ b/include/142bot/bot.hpp
@@ -14,6 +14,7 @@ class Bot {
     dpp::snowflake owner_id;
 private:
 	bool run_database_migrations();
+	void create_queries();
 public:
     char prefix;
 	pqxx::connection conn;
diff --git a/migrations/0002-state.sql b/migrations/0002-state.sql
new file mode 100644
index 0000000..2465d30
--- /dev/null
+++ b/migrations/0002-state.sql
@@ -0,0 +1,5 @@
+CREATE TABLE IF NOT EXISTS bot_state (
+    id SERIAL PRIMARY KEY,
+    setting TEXT NOT NULL,
+    value TEXT NOT NULL
+);
\ No newline at end of file
diff --git a/migrations/0003-unique-state-key.sql b/migrations/0003-unique-state-key.sql
new file mode 100644
index 0000000..1d19219
--- /dev/null
+++ b/migrations/0003-unique-state-key.sql
@@ -0,0 +1,2 @@
+ALTER TABLE bot_state DROP CONSTRAINT IF EXISTS setting_unique;
+ALTER TABLE bot_state ADD CONSTRAINT setting_unique UNIQUE (setting);
\ No newline at end of file
diff --git a/modules/spotify/spotify.cpp b/modules/spotify/spotify.cpp
index e00a83b..5533a9a 100644
--- a/modules/spotify/spotify.cpp
+++ b/modules/spotify/spotify.cpp
@@ -38,9 +38,24 @@ public:
         ml->attach({I_OnMessage, I_OnCommand}, this);
 
         this->spotifyRegex = "^https:\/\/open.spotify.com\/track\/([a-zA-Z0-9]+)(.*)$";
-        this->defaultSpotifyAccount = "1";
+
+        pqxx::work tx(creator->conn);
+        try {
+            auto rs = tx.exec_prepared1("state", "default_spotify_account");
+            this->defaultSpotifyAccount = rs[0].as<std::string>();
+        } catch (std::exception &e) {
+            creator->core->log(dpp::ll_warning, "Couldn't find default_spotify_account in state, creating");
+            tx.exec_prepared("update_state", "default_spotify_account", "1");
+        } 
         this->spotifyBaseUrl = "https://api.spotify.com/v1";
-        this->spotifyDefaultDevice = "";
+        try {
+            auto rs = tx.exec_prepared1("state", "default_spotify_device");
+            this->spotifyDefaultDevice = rs[0].as<std::string>();
+        } catch (std::exception &e) {
+            creator->core->log(dpp::ll_warning, "Couldn't find default_spotify_device in state, creating");
+            tx.exec_prepared("update_state", "default_spotify_device", "");
+        }
+        tx.commit();
     }
 
     virtual std::string version() {
@@ -233,6 +248,7 @@ public:
                     auto res = tx.exec_params1("SELECT id FROM spotify WHERE id=$1", params[2]);
 
                     this->defaultSpotifyAccount = params[2];
+                    tx.exec_prepared("update_state", "default_spotify_account", params[2]);
 
                     tx.commit();
                 } catch (std::exception &e) {
@@ -273,6 +289,9 @@ public:
                 for (int i = 0; i < devices.size(); i++) {
                     if (devices[i]["id"] == params[2]) {
                         this->spotifyDefaultDevice = params[2];
+                        pqxx::work tx(bot->conn);
+                        tx.exec_prepared("update_state", "default_spotify_device", params[2]);
+                        tx.commit();
                         EmbedSuccess("Changed default spotify device", message.msg.channel_id);
                         return true;
                     }
diff --git a/src/bot.cpp b/src/bot.cpp
index 2bc6a6b..b1d530c 100644
--- a/src/bot.cpp
+++ b/src/bot.cpp
@@ -67,9 +67,20 @@ bool Bot::run_database_migrations() {
 	w.commit();
 	this->core->log(dpp::ll_info, "Done.");
 
+	this->core->log(dpp::ll_info, "Preparing statements...");
+	create_queries();
+	this->core->log(dpp::ll_info, "Done");
+
 	return true;
 }
 
+void Bot::create_queries() {
+	this->core->log(dpp::ll_trace, "Preparing state query");
+	this->conn.prepare("state", "SELECT value FROM bot_state WHERE setting=$1");	
+	this->core->log(dpp::ll_trace, "Preparing update state query");
+	this->conn.prepare("update_state", "INSERT INTO bot_state (setting,value) VALUES ($1, $2) ON CONFLICT (setting) DO UPDATE SET value=EXCLUDED.value;");
+}
+
 bool Bot::isDevMode() {
     return dev;
 }
-- 
cgit v1.2.3