1  import { $ } from "bun"
  2  import { Database } from "bun:sqlite"
  3  import * as fs from "node:fs/promises"
  4  
  5  // Production Queries
  6  const allQuestions = `select * from questions where channel_id != 157284360;`
  7  ,     allPurchases = `select * from purchases where channel_id != 157284360;`
  8  ,       allConfigs = `select * from configs   where channel_id != 157284360;`
  9  ,      allSessions = `select * from sessions  where channel_id != 157284360 order by open desc;`
 10  
 11  export function ProductionDB () {
 12    let productionDB = new Database("askebs.sql")
 13    
 14    const allQueries = new Map([
 15      ["configs", allConfigs],
 16      ["sessions", allSessions],
 17      ["purchases", allPurchases],
 18      ["questions", allQuestions],
 19    ])
 20  
 21    return {
 22      async getLatest() {
 23        productionDB.close()
 24        await fs.rm(    "askebs.sql", { force: true })
 25        await fs.rm("askebs.sql-wal", { force: true })
 26        await fs.rm("askebs.sql-shm", { force: true })
 27        const { stdout } =  await $`litestream restore "${process.env.LITESTREAM_PATH}"`
 28        productionDB = new Database("askebs.sql")
 29      },
 30      getAll(table) {
 31       const prepared = productionDB.query(allQueries.get(table))
 32       ,         data = prepared.all()  
 33  
 34        return data
 35      }
 36    }
 37  }
 38  
 39  // Support Queries
 40  const getVersion = `select version from support_version where id = 1;`
 41  ,     setVersion = `update support_version set version = $version where id = 1;`
 42  
 43  const makeLeadsTable = `\
 44  create table if not exists leads (
 45    channelId integer primary key,
 46    userName varchar(25),
 47    name text,
 48    content text,
 49    followers integer,
 50    averageViewers real,
 51    averageSampleLength real,
 52    averageBitsPerSample real,
 53    averageQuestionsPerMinute real,
 54    averageMessagesPerMinute real,
 55    averageChattersPerMinute real,
 56    email text,
 57    socials text,
 58    status integer
 59  );`
 60  
 61  const makeSamplesTable = `\
 62  create table if not exists samples (
 63    sampleId integer primary key,
 64    channelId integer,
 65    userName varchar(25),
 66    topic text,
 67    title text,
 68    duration integer,
 69    bits integer,
 70    messages integer,
 71    questions integer,
 72    chatters integer,
 73    averageViewers real
 74  );`
 75  
 76  const makeVODTable = `\
 77  create table if not exists vods (
 78    sessionId text primary key,
 79    userName varchar(25),
 80    vodLink text
 81  );
 82  `
 83  const getAllVODs = `select * from vods;`
 84  const insertVOD = `insert into vods values ($sessionId, $userName, $vodLink)`
 85  
 86  const updateLeadField = (field) => `update leads set ${field} = $value where channelId = $channelId;`
 87  
 88  const addStartColumnToSampleTable = `alter table samples add column start text;`
 89  
 90  const insertSample = `\
 91  insert into samples (channelId, userName, topic, title, duration, bits, messages, questions, chatters, averageViewers, start)
 92  values ($channelId, $userName, $topic, $title, $duration, $bits, $messages, $questions, $chatters, $averageViewers, $start);`
 93  
 94  const addWeekdayColumnToLeadTable = `alter table leads add column weekdays text;`
 95  const addActiveViewerPercentageToLeadTable = `alter table leads add column activeViewerPercentage real;`
 96  
 97  const insertLead = `\
 98  insert into leads
 99  values ($channelId, $userName, $name, $content, $followers,
100          $averageViewers, $averageSampleLength, $averageBitsPerSample,
101          $averageQuestionsPerMinute, $averageMessagesPerMinute, $averageChattersPerMinute,
102          $email, $socials, $status, $weekdays, $activeViewerPercentage);`
103  
104  const getAllSamplesForChannel = `select * from samples where userName = $userName;`
105  const getAllChannelsInLeads =  `select userName from leads;`
106  
107  export function SupportDB (path) {
108    const supportDB = new Database(path)
109  
110    const SupportVersionUpgrades = [
111      // v1 -> v2
112      (supportDB) => {
113        supportDB.query(makeLeadsTable).run()
114        supportDB.query(makeSamplesTable).run()
115        supportDB.query(setVersion).run({ $version: 2 })
116      },
117      // v2 -> v3
118      (supportDB) => {
119        supportDB.query(makeVODTable).run()
120        supportDB.query(setVersion).run({ $version: 3 })
121      },
122      // v3 -> v4
123      (supportDB) => {
124        supportDB.query(addStartColumnToSampleTable).run()
125        supportDB.query(setVersion).run({ $version: 4 })
126      },
127      // v4 -> v5
128      (supportDB) => {
129        supportDB.query(addWeekdayColumnToLeadTable).run()
130        supportDB.query(setVersion).run({ $version: 5 })
131      },
132      // v5 -> v6
133      (supportDB) => {
134        supportDB.query(addActiveViewerPercentageToLeadTable).run()
135        supportDB.query(setVersion).run({ $version: 6 })
136      },
137    ]
138  
139    const mutableLeadFields = new Set(["name", "content", "followers", "email", "status"])
140    
141    return {
142      getUserPasswordHash(user) {
143        const query = supportDB.query(`select password from users where username = $username;`)
144        return query.get({ $username: user })
145      },
146      upgradeIfNeeded() {
147        const versionQuery = supportDB.query(getVersion)
148        let { version } = versionQuery.get()
149  
150        for (let v = version-1; v < SupportVersionUpgrades.length; v++) {
151          SupportVersionUpgrades[v](supportDB)
152        }
153      },
154      getVersion() {
155        const versionQuery = supportDB.query(getVersion)
156        let { version } = versionQuery.get()
157  
158        return version
159      },
160      addSample(sample) {
161        const preparedSample = {
162          $channelId: sample.channelId,
163          $userName: sample.userName,
164          $topic: sample.topic,
165          $title: sample.title,
166          $duration: sample.duration,
167          $bits: sample.bits,
168          $messages: sample.messages,
169          $questions: sample.questions,
170          $chatters: sample.chatters,
171          $averageViewers: sample.averageViewers,
172          $start: sample.start,
173        }
174  
175        const sampleQuery = supportDB.query(insertSample)
176        sampleQuery.run(preparedSample)
177      },
178      addLead(lead) {
179        console.log(lead)
180        try {
181          const query = supportDB.query(insertLead)
182          query.run(lead)
183        } catch(e) {
184          console.log(e)
185        }
186      },
187      getAllLeads() {
188        const query = supportDB.query(`select * from leads;`)
189        return query.all() 
190      },
191      getSamples() {
192        const samplesQuery = supportDB.query(`select * from samples;`)
193        return samplesQuery.all()
194      },
195      getAllContentCategories() {
196        const contentQuery = supportDB.query(`select distinct content from leads;`)
197        return contentQuery.all().map(r => r.content)
198      },
199      updateLeadField(channelId, field, value) {
200        if (mutableLeadFields.has(field)) {
201          const fieldQuery = supportDB.query(updateLeadField(field))
202          fieldQuery.run({ $channelId: channelId, $value: value })
203        }
204      },
205      getVODs() { return supportDB.query(getAllVODs).all() },
206      addVOD({sessionId, userName, vodLink}) {
207        const insertVODQuery = supportDB.query(insertVOD)
208        return insertVODQuery.run({
209          $sessionId: sessionId,
210          $userName: userName,
211          $vodLink: vodLink,
212        })
213      },
214      getAllSamplesByChannel(userName) {
215        const query = supportDB.query(getAllSamplesForChannel)
216        return query.all({ $userName: userName })
217      },
218      getAllLeadChannels() {
219        const query = supportDB.query(getAllChannelsInLeads)
220        return query.all().map(r => r.userName)
221      }
222    }
223  }