1  import fs from 'fs';
  2  import sqlite3 from 'better-sqlite3';
  3  import { createHash } from 'crypto';
  4  import { ActivityType, CommandInteraction, StringSelectMenuInteraction } from 'discord.js';
  5  import { Locale, log } from './messages';
  6  
  7  export interface DBUser {
  8    userIDHash: string;
  9    autoRole: 1 | 0;
 10    language: Locale | 'none';
 11  }
 12  
 13  export interface DBGuild {
 14    guildID: string;
 15    language: Locale;
 16    requiredRoleID: string | null;
 17  }
 18  
 19  export interface DBActivityRole {
 20    guildID: string;
 21    activityName: string;
 22    roleID: string;
 23    exactActivityName: 1 | 0;
 24    live: 1 | 0;
 25  }
 26  
 27  export interface DBStatusRole {
 28    guildID: string;
 29    type: ActivityType;
 30    roleID: string;
 31  }
 32  
 33  export interface DBCurrentlyActiveActivity {
 34    userIDHash: string;
 35    guildID: string;
 36    activityName: string;
 37  }
 38  
 39  /// @deprecated use DBActivityStatus instead
 40  export interface DBActiveTemporaryRoles {
 41    userIDHash: string;
 42    guildId: string;
 43    roleID: string;
 44  }
 45  
 46  export interface DBActivityStats {
 47    guildID: string;
 48    activityName: string;
 49    count: number;
 50  }
 51  
 52  export interface DBVersion {
 53    version: number;
 54    enforcer: 0;
 55  }
 56  
 57  let db: sqlite3.Database;
 58  
 59  export const stats = {
 60    dbCalls: 0,
 61  }
 62  
 63  export function resetStats() {
 64    stats.dbCalls = 0;
 65  }
 66  
 67  export function prepare(query: string) {
 68    stats.dbCalls++;
 69    return db.prepare(query);
 70  }
 71  
 72  
 73  export function prepareDB() {
 74    if (!fs.existsSync('db')) fs.mkdirSync('db');
 75    db = new sqlite3('db/activity-roles.db');
 76  
 77    // `v1.9.1` live -> permanent: the database was not updated on purpose.
 78    // enforcer: see https://stackoverflow.com/a/3010975/16292720 (comment 4)
 79    prepare(
 80      'CREATE TABLE IF NOT EXISTS dbversion (version INT NOT NULL, enforcer INT DEFAULT 0 NOT NULL CHECK(enforcer == 0), UNIQUE (enforcer))'
 81    ).run();
 82    prepare(
 83      'CREATE TABLE IF NOT EXISTS users (userIDHash TEXT PRIMARY KEY, autoRole INTEGER, language TEXT)'
 84    ).run();
 85    prepare(
 86      'CREATE TABLE IF NOT EXISTS guilds (guildID TEXT PRIMARY KEY, language TEXT, requiredRoleID TEXT)'
 87    ).run();
 88    prepare(
 89      'CREATE TABLE IF NOT EXISTS activityRoles (guildID TEXT, activityName TEXT, roleID TEXT, exactActivityName INTEGER, live INTEGER, PRIMARY KEY (guildID, activityName, roleID))'
 90    ).run();
 91    prepare(
 92      'CREATE TABLE IF NOT EXISTS statusRoles (guildID TEXT, type INTEGER, roleID TEXT, PRIMARY KEY (guildID, type, roleID))'
 93    ).run();
 94    prepare(
 95      'CREATE TABLE IF NOT EXISTS currentlyActiveActivities (userIDHash TEXT, guildID TEXT, activityName TEXT, PRIMARY KEY (userIDHash, guildID, activityName))'
 96    ).run();
 97    prepare(
 98      'CREATE TABLE IF NOT EXISTS activeTemporaryRoles (userIDHash, guildID TEXT, roleID TEXT, PRIMARY KEY (userIDHash, guildID, roleID))'
 99    ).run();
100    prepare(
101      'CREATE TABLE IF NOT EXISTS activityStats (guildID TEXT, activityName TEXT, count INTEGER, PRIMARY KEY (guildID, activityName))'
102    ).run();
103  
104    const latestDBVersion = 3;
105    let dbVersion = latestDBVersion;
106  
107    if (!prepare('SELECT * FROM dbversion').get()) {
108      prepare('INSERT INTO dbversion (version) VALUES (?)').run(latestDBVersion);
109    } else {
110      dbVersion = (prepare('SELECT * FROM dbversion').get() as DBVersion).version;
111    }
112  
113    if (dbVersion === 1) {
114      // add status roles
115      // add activeTemporaryRoles
116      // fade out currentlyActiveActivites
117      prepare('UPDATE dbversion SET version = 2').run();
118      dbVersion = 2;
119    }
120    if (dbVersion === 2) {
121      prepare("DELETE FROM activityStats WHERE activityName = 'Custom Status'").run();
122      prepare('UPDATE dbversion SET version = 3').run();
123      dbVersion = 3;
124    }
125  
126    //TODO: add bot version?
127    if (dbVersion > latestDBVersion) {
128      log.warn(
129        `Database version: ${dbVersion}. The latest known database version is ${latestDBVersion}! Are you opening a database created with a newer version?`
130      );
131    } else {
132      log.info(`Database version: ${dbVersion}`);
133    }
134  }
135  
136  export function getUserConfig(userID: string): DBUser {
137    const userIDHash = createHash('sha256').update(userID).digest('base64');
138    const user = prepare('SELECT * FROM users WHERE userIDHash = ?').get(userIDHash) as DBUser;
139    if (user) return user;
140    prepare('INSERT INTO users VALUES (?, ?, ?)').run(userIDHash, 1, 'none');
141    return { userIDHash: userIDHash, autoRole: 1, language: 'none' };
142  }
143  
144  export function getGuildConfig(guildID: string): DBGuild {
145    const guild = prepare('SELECT * FROM guilds WHERE guildID = ?').get(guildID) as DBGuild;
146    if (guild) return guild;
147    prepare('INSERT INTO guilds VALUES (?, ?, NULL)').run(guildID, 'en-US');
148    return { guildID: guildID, language: 'en-US' as Locale, requiredRoleID: null };
149  }
150  
151  export function getActivityRoles(guildID: string): DBActivityRole[] {
152    return db
153      .prepare('SELECT * FROM activityRoles WHERE guildID = ?')
154      .all(guildID) as DBActivityRole[];
155  }
156  
157  export function getStatusRoles(guildID: string): DBStatusRole[] {
158    return prepare('SELECT * FROM statusRoles WHERE guildID = ?').all(guildID) as DBStatusRole[];
159  }
160  
161  export function getLang(interaction: CommandInteraction | StringSelectMenuInteraction): Locale {
162    const userLang = getUserConfig(interaction.user.id).language;
163    if (userLang !== 'none') return userLang;
164  
165    if (!interaction.guild) return 'en-US' as Locale;
166  
167    return getGuildConfig(interaction.guild.id).language;
168  }
169  
170  export async function addActivity(guildID: string, activityName: string) {
171    prepare(
172      'INSERT INTO activityStats VALUES (?, ?, ?) ON CONFLICT(guildID, activityName) DO UPDATE SET count = count + 1'
173    ).run(guildID, activityName, 1);
174  }
175  
176  export function getUserCount(): number {
177    return (prepare('SELECT COUNT(*) FROM users').get() as { 'COUNT(*)': number })['COUNT(*)'];
178  }
179  
180  export function getActivityRoleCount(): number {
181    return (prepare('SELECT COUNT(*) FROM activityRoles').get() as { 'COUNT(*)': number })['COUNT(*)'];
182  }
183  
184  export function getStatusRoleCount(): number {
185    return (prepare('SELECT COUNT(*) FROM statusRoles').get() as { 'COUNT(*)': number })['COUNT(*)'];
186  }
187  
188  export function getCurrentlyActiveActivityCount(): number {
189    return (prepare('SELECT COUNT(*) FROM currentlyActiveActivities').get() as { 'COUNT(*)': number })['COUNT(*)'];
190  }
191  
192  export function getTempRoleCount(): number {
193    return (prepare('SELECT COUNT(*) FROM activityRoles WHERE live = 1').get() as { 'COUNT(*)': number; })['COUNT(*)'];
194  }
195  
196  export function getPermRoleCount(): number {
197    return (prepare('SELECT COUNT(*) FROM activityRoles WHERE live = 0').get() as { 'COUNT(*)': number; })['COUNT(*)'];
198  }
199  
200  export function getRolesCount(): number {
201    return getActivityRoleCount() + getStatusRoleCount()
202  }