populate uex_items

4 files changed, 122 insertions, 15 deletions

diff --git a/174bg/discord-bot/commands/_index.js b/174bg/discord-bot/commands/_index.js
index 2421b4d..77452ff 100644
--- a/174bg/discord-bot/commands/_index.js
+++ b/174bg/discord-bot/commands/_index.js
@@ -1,7 +1,8 @@
 import requisition from "./requisition.js";
 import closeTicket from "./close-ticket.js";
 import oncallCheck from "./oncall-check.js";
+import pocketbase from "./pocketbase.js";
 
-export { requisition, closeTicket, oncallCheck };
+export { requisition, closeTicket, oncallCheck, pocketbase };
 
-export default [requisition, closeTicket, oncallCheck];
+export default [requisition, closeTicket, oncallCheck, pocketbase];
diff --git a/174bg/discord-bot/commands/pocketbase.js b/174bg/discord-bot/commands/pocketbase.js
new file mode 100644
index 0000000..8b97822
--- /dev/null
+++ b/174bg/discord-bot/commands/pocketbase.js
@@ -0,0 +1,91 @@
+import { SlashCommandBuilder, PermissionFlagsBits } from "discord.js";
+import PocketBase from "pocketbase";
+import uex from "../.cache/uex.json" with { type: "json" };
+
+export default {
+  data: new SlashCommandBuilder()
+    .setName("pocketbase")
+    .setDescription("Runs various PocketBase commands.")
+    .setDefaultMemberPermissions(PermissionFlagsBits.Administrator)
+    .addSubcommandGroup((group) =>
+      group
+        .setName("populate")
+        .setDescription("Populates the PocketBase database with data.")
+        .addSubcommand((subcommand) =>
+          subcommand
+            .setName("uex_items")
+            .setDescription("Populates the uex_items collection with data from the bot's UEX cache.")
+        )
+    ),
+  execute: async (interaction) => {
+    // only zuedev can run any of these commands
+    if (interaction.member.id !== "723361818940276736") return interaction.reply({
+      content: "You do not have permission to use this command.",
+      ephemeral: true,
+    });
+
+    const subcommandGroup = interaction.options.getSubcommandGroup();
+    const subcommand = interaction.options.getSubcommand();
+
+    if (subcommandGroup === "populate") {
+      if (subcommand === "uex_items") {
+        const pb = new PocketBase(process.env.POCKETBASE_URL);
+        pb.authStore.save(process.env.POCKETBASE_AUTH_TOKEN, null);
+
+        interaction.reply({
+          content: `Populating uex_items collection with ${uex.items.length} items...`,
+          ephemeral: true,
+        });
+
+        let ops = 0;
+        let failed = 0;
+        let updates = 0;
+        let creates = 0;
+
+        for (const item of uex.items) {
+          try {
+            // does it already exist?
+            const existingItem = await pb.collection("uex_items").getFullList({
+              filter: `id="${item.id}"`,
+            });
+
+            if (existingItem.length > 0) {
+              // compare times to see if we need to update
+              // example pocketbase: "2026-07-18 12:30:02.405Z"
+              // example uex: 1746219762
+              if (new Date(existingItem[0].updated).getTime() < item.updated * 1000) {
+                await pb.collection("uex_items").update(existingItem[0].id, {
+                  name: item.name,
+                });
+                updates++;
+              }
+            } else {
+              await pb.collection("uex_items").create({
+                id: item.id,
+                name: item.name,
+              });
+              creates++;
+            }
+            ops++;
+
+            // update the interaction every 1000 operations to avoid timeout
+            if (ops % 1000 === 0) {
+              interaction.editReply({
+                content: `Populating uex_items collection with ${uex.items.length} items... (${ops} processed, ${updates} updated, ${creates} created, ${failed} failed)`,
+                ephemeral: true,
+              });
+            }
+          } catch (error) {
+            console.error(`Failed to create item ${item.name}:`, error);
+            failed++;
+          }
+        }
+
+        interaction.editReply({
+          content: `Successfully populated uex_items collection with ${uex.items.length} items. (${ops} processed, ${updates} updated, ${creates} created, ${failed} failed)`,
+          ephemeral: true,
+        });
+      }
+    }
+  },
+};
diff --git a/174bg/discord-bot/main.js b/174bg/discord-bot/main.js
index 02400d6..8a83bd9 100644
--- a/174bg/discord-bot/main.js
+++ b/174bg/discord-bot/main.js
@@ -1,3 +1,4 @@
+import fs from "fs";
 import {
   Client,
   Events,
@@ -11,11 +12,9 @@ import {
   ButtonBuilder,
   ButtonStyle,
 } from "discord.js";
-
+import getUrl from "./utilities/getUrl.js";
 import commands from "./commands/_index.js";
 
-import fs from "fs";
-
 const client = new Client({
   intents: [GatewayIntentBits.Guilds, GatewayIntentBits.GuildMembers],
 });
@@ -82,12 +81,6 @@ async function registerCommands() {
   }
 }
 
-async function getUrl(url) {
-  const response = await fetch(url);
-  if (!response.ok) return null;
-  return await response.json();
-}
-
 async function buildCache() {
   if (!fs.existsSync("./.cache")) fs.mkdirSync("./.cache");
 
@@ -96,19 +89,32 @@ async function buildCache() {
   uex.categories = (await getUrl("https://api.uexcorp.uk/2.0/categories"))?.data;
 
   for (const category of uex.categories) {
+    uex.items = uex.items || [];
     if (category.type === "item") {
-      if (!uex.items) uex.items = {};
-      if (!uex.items[category.section]) uex.items[category.section] = {};
-      uex.items[category.section][category.name] = (await getUrl(`https://api.uexcorp.uk/2.0/items?id_category=${category.id}`))?.data;
+      uex.items = uex.items.concat((await getUrl(`https://api.uexcorp.uk/2.0/items?id_category=${category.id}`))?.data || []);
     }
   }
 
+  uex.space_stations = (await getUrl("https://api.uexcorp.uk/2.0/space_stations"))?.data;
+  uex.cities = (await getUrl("https://api.uexcorp.uk/2.0/cities"))?.data;
+  uex.outposts = (await getUrl("https://api.uexcorp.uk/2.0/outposts"))?.data;
+  uex.points_of_interest = (await getUrl("https://api.uexcorp.uk/2.0/poi"))?.data;
+  uex.terminals = (await getUrl("https://api.uexcorp.uk/2.0/terminals"))?.data;
+  uex.vehicles = (await getUrl("https://api.uexcorp.uk/2.0/vehicles"))?.data;
+  uex.vehicles_pledge_prices = (await getUrl("https://api.uexcorp.uk/2.0/vehicles_prices"))?.data;
+  uex.vehicles_buy_prices = (await getUrl("https://api.uexcorp.uk/2.0/vehicles_purchases_prices"))?.data;
+  uex.vehicles_rent_prices = (await getUrl("https://api.uexcorp.uk/2.0/vehicles_rentals_prices"))?.data;
+
   // is there a difference between the current cache and the new cache?
   if (fs.existsSync("./.cache/uex.json")) {
     const currentCache = JSON.parse(fs.readFileSync("./.cache/uex.json", "utf-8"));
 
     if (JSON.stringify(currentCache) === JSON.stringify(uex)) {
       console.log("No changes detected in UEX cache.");
+      console.log("If you want to force a cache update, delete the .cache/uex.json file and restart the bot.");
+      for (const [key, value] of Object.entries(uex)) {
+        console.log(`Found ${value.length} ${key.replace(/_/g, " ")}.`);
+      }
       return;
     }
 
@@ -123,8 +129,12 @@ async function buildCache() {
     console.log("No existing UEX cache found. Creating new cache.");
   }
 
-  fs.writeFileSync(
+  await fs.writeFileSync(
     "./.cache/uex.json",
     JSON.stringify(uex, null, 2),
   );
+
+  for (const [key, value] of Object.entries(uex)) {
+    console.log(`Cached ${value.length} ${key.replace(/_/g, " ")}.`);
+  }
 }
\ No newline at end of file
diff --git a/174bg/discord-bot/utilities/getUrl.js b/174bg/discord-bot/utilities/getUrl.js
new file mode 100644
index 0000000..49342d9
--- /dev/null
+++ b/174bg/discord-bot/utilities/getUrl.js
@@ -0,0 +1,5 @@
+export default async (url) => {
+    const response = await fetch(url);
+    if (!response.ok) return null;
+    return await response.json();
+};