2 import { exists } from '@server/helpers/custom-validators/misc'
3 import { AbuseFilter, AbuseState, AbuseVideoIs } from '@shared/models'
4 import { buildBlockedAccountSQL, buildDirectionAndField } from '../utils'
6 export type BuildAbusesQueryOptions = {
13 searchReporter?: string
14 searchReportee?: string
18 searchVideoChannel?: string
19 videoIs?: AbuseVideoIs
23 predefinedReasonId?: number
29 serverAccountId?: number
30 userAccountId?: number
32 reporterAccountId?: number
35 function buildAbuseListQuery (options: BuildAbusesQueryOptions, type: 'count' | 'id') {
36 const whereAnd: string[] = []
37 const replacements: any = {}
40 'LEFT JOIN "videoAbuse" ON "videoAbuse"."abuseId" = "abuse"."id"',
41 'LEFT JOIN "video" ON "videoAbuse"."videoId" = "video"."id"',
42 'LEFT JOIN "videoBlacklist" ON "videoBlacklist"."videoId" = "video"."id"',
43 'LEFT JOIN "videoChannel" ON "video"."channelId" = "videoChannel"."id"',
44 'LEFT JOIN "account" "reporterAccount" ON "reporterAccount"."id" = "abuse"."reporterAccountId"',
45 'LEFT JOIN "account" "flaggedAccount" ON "flaggedAccount"."id" = "abuse"."flaggedAccountId"',
46 'LEFT JOIN "commentAbuse" ON "commentAbuse"."abuseId" = "abuse"."id"',
47 'LEFT JOIN "videoComment" ON "commentAbuse"."videoCommentId" = "videoComment"."id"'
50 if (options.serverAccountId || options.userAccountId) {
52 '"abuse"."reporterAccountId" IS NULL OR ' +
53 '"abuse"."reporterAccountId" NOT IN (' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')'
57 if (options.reporterAccountId) {
58 whereAnd.push('"abuse"."reporterAccountId" = :reporterAccountId')
59 replacements.reporterAccountId = options.reporterAccountId
63 const searchWhereOr = [
64 '"video"."name" ILIKE :search',
65 '"videoChannel"."name" ILIKE :search',
66 `"videoAbuse"."deletedVideo"->>'name' ILIKE :search`,
67 `"videoAbuse"."deletedVideo"->'channel'->>'displayName' ILIKE :search`,
68 '"reporterAccount"."name" ILIKE :search',
69 '"flaggedAccount"."name" ILIKE :search'
72 replacements.search = `%${options.search}%`
73 whereAnd.push('(' + searchWhereOr.join(' OR ') + ')')
76 if (options.searchVideo) {
77 whereAnd.push('"video"."name" ILIKE :searchVideo')
78 replacements.searchVideo = `%${options.searchVideo}%`
81 if (options.searchVideoChannel) {
82 whereAnd.push('"videoChannel"."name" ILIKE :searchVideoChannel')
83 replacements.searchVideoChannel = `%${options.searchVideoChannel}%`
87 whereAnd.push('"abuse"."id" = :id')
88 replacements.id = options.id
92 whereAnd.push('"abuse"."state" = :state')
93 replacements.state = options.state
96 if (options.videoIs === 'deleted') {
97 whereAnd.push('"videoAbuse"."deletedVideo" IS NOT NULL')
98 } else if (options.videoIs === 'blacklisted') {
99 whereAnd.push('"videoBlacklist"."id" IS NOT NULL')
102 if (options.predefinedReasonId) {
103 whereAnd.push(':predefinedReasonId = ANY("abuse"."predefinedReasons")')
104 replacements.predefinedReasonId = options.predefinedReasonId
107 if (options.filter === 'video') {
108 whereAnd.push('"videoAbuse"."id" IS NOT NULL')
109 } else if (options.filter === 'comment') {
110 whereAnd.push('"commentAbuse"."id" IS NOT NULL')
111 } else if (options.filter === 'account') {
112 whereAnd.push('"videoAbuse"."id" IS NULL AND "commentAbuse"."id" IS NULL')
115 if (options.searchReporter) {
116 whereAnd.push('"reporterAccount"."name" ILIKE :searchReporter')
117 replacements.searchReporter = `%${options.searchReporter}%`
120 if (options.searchReportee) {
121 whereAnd.push('"flaggedAccount"."name" ILIKE :searchReportee')
122 replacements.searchReportee = `%${options.searchReportee}%`
125 const prefix = type === 'count'
126 ? 'SELECT COUNT("abuse"."id") AS "total"'
127 : 'SELECT "abuse"."id" '
130 if (type !== 'count') {
133 const order = buildAbuseOrder(options.sort)
134 suffix += `${order} `
137 if (exists(options.count)) {
138 const count = parseInt(options.count + '', 10)
139 suffix += `LIMIT ${count} `
142 if (exists(options.start)) {
143 const start = parseInt(options.start + '', 10)
144 suffix += `OFFSET ${start} `
148 const where = whereAnd.length !== 0
149 ? `WHERE ${whereAnd.join(' AND ')}`
153 query: `${prefix} FROM "abuse" ${joins.join(' ')} ${where} ${suffix}`,
158 function buildAbuseOrder (value: string) {
159 const { direction, field } = buildDirectionAndField(value)
161 return `ORDER BY "abuse"."${field}" ${direction}`