/
db.ts
70 lines (56 loc) · 2.05 KB
/
db.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
import * as sdk from 'botpress/sdk'
import moment from 'moment'
import { isNullOrUndefined } from 'util'
export default class HistoryDb {
knex: any
constructor(private bp: typeof sdk) {
this.knex = bp.database
}
getDistinctConversations = async (botId: string, from?: number, to?: number) => {
const query = this.knex
.select()
.distinct('sessionId')
.from('events')
.whereNotNull('sessionId')
.andWhere({ botId })
if (from) {
const fromDate = moment.unix(from).toDate()
query.andWhere(this.knex.date.isBefore(fromDate, 'createdOn'))
}
if (to) {
const toDate = moment.unix(to).toDate()
query.andWhere(this.knex.date.isAfter(toDate, 'createdOn'))
}
const queryResults = await query
const uniqueConversations: string[] = queryResults.map(x => x.sessionId)
const buildConversationInfo = async c => ({ id: c, count: await this.getConversationMessageCount(c) })
return Promise.all(uniqueConversations.map(buildConversationInfo))
}
getMessagesOfConversation = async (sessionId, count, offset) => {
const incomingMessages: sdk.IO.Event[] = await this.knex
.select('event')
.orderBy('createdOn', 'desc')
.where({ sessionId, direction: 'incoming' })
.from('events')
.offset(offset)
.limit(count)
.then(rows => rows.map(r => this.knex.json.get(r.event)))
const messages = await this.knex('events')
.whereIn('incomingEventId', incomingMessages.map(x => x.id))
.then(rows => rows.map(r => this.knex.json.get(r.event)))
return messages
}
getConversationMessageCount = async (sessionId: string) => {
return this._getMessageCountWhere({ sessionId })
}
getConversationMessageGroupCount = async (sessionId: string) => {
return this._getMessageCountWhere({ sessionId, direction: 'incoming' })
}
private async _getMessageCountWhere(whereParams) {
const messageCountObject = await this.knex
.from('events')
.count()
.where(whereParams)
return messageCountObject.pop()['count(*)']
}
}