1 import * as Bluebird from 'bluebird'
2 import { invert } from 'lodash'
3 import { literal, Op, WhereOptions } from 'sequelize'
18 } from 'sequelize-typescript'
19 import { isAbuseModerationCommentValid, isAbuseReasonValid, isAbuseStateValid } from '@server/helpers/custom-validators/abuses'
23 AbusePredefinedReasons,
24 abusePredefinedReasonsMap,
25 AbusePredefinedReasonsString,
29 } from '@shared/models'
30 import { AbuseFilter } from '@shared/models/moderation/abuse/abuse-filter'
31 import { CONSTRAINTS_FIELDS, ABUSE_STATES } from '../../initializers/constants'
32 import { MAbuse, MAbuseAP, MAbuseFormattable, MUserAccountId } from '../../types/models'
33 import { AccountModel, ScopeNames as AccountScopeNames } from '../account/account'
34 import { buildBlockedAccountSQL, getSort, searchAttribute, throwIfNotValid } from '../utils'
35 import { ThumbnailModel } from '../video/thumbnail'
36 import { VideoModel } from '../video/video'
37 import { VideoBlacklistModel } from '../video/video-blacklist'
38 import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from '../video/video-channel'
39 import { VideoAbuseModel } from './video-abuse'
40 import { VideoCommentAbuseModel } from './video-comment-abuse'
42 export enum ScopeNames {
47 [ScopeNames.FOR_API]: (options: {
50 searchReporter?: string
51 searchReportee?: string
55 searchVideoChannel?: string
56 videoIs?: AbuseVideoIs
60 predefinedReasonId?: number
66 serverAccountId: number
69 const onlyBlacklisted = options.videoIs === 'blacklisted'
70 const videoRequired = !!(onlyBlacklisted || options.searchVideo || options.searchVideoChannel)
74 [Op.notIn]: literal('(' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')')
79 const escapedSearch = AbuseModel.sequelize.escape('%' + options.search + '%')
81 Object.assign(where, {
85 { '$VideoAbuse.videoId$': { [Op.not]: null } },
86 searchAttribute(options.search, '$VideoAbuse.Video.name$')
91 { '$VideoAbuse.videoId$': { [Op.not]: null } },
92 searchAttribute(options.search, '$VideoAbuse.Video.VideoChannel.name$')
97 { '$VideoAbuse.deletedVideo$': { [Op.not]: null } },
98 literal(`"VideoAbuse"."deletedVideo"->>'name' ILIKE ${escapedSearch}`)
103 { '$VideoAbuse.deletedVideo$': { [Op.not]: null } },
104 literal(`"VideoAbuse"."deletedVideo"->'channel'->>'displayName' ILIKE ${escapedSearch}`)
107 searchAttribute(options.search, '$ReporterAccount.name$'),
108 searchAttribute(options.search, '$FlaggedAccount.name$')
113 if (options.id) Object.assign(where, { id: options.id })
114 if (options.state) Object.assign(where, { state: options.state })
116 if (options.videoIs === 'deleted') {
117 Object.assign(where, {
118 '$VideoAbuse.deletedVideo$': {
124 if (options.predefinedReasonId) {
125 Object.assign(where, {
127 [Op.contains]: [ options.predefinedReasonId ]
136 // we don't care about this count for deleted videos, so there are not included
140 'FROM "videoAbuse" ' +
141 'WHERE "videoId" = "VideoAbuse"."videoId" ' +
144 'countReportsForVideo'
147 // we don't care about this count for deleted videos, so there are not included
153 'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
154 'FROM "videoAbuse" ' +
156 'WHERE t.id = "VideoAbuse".id' +
164 'SELECT count("videoAbuse"."id") ' +
165 'FROM "videoAbuse" ' +
166 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
167 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
168 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
169 'WHERE "account"."id" = "AbuseModel"."reporterAccountId" ' +
172 'countReportsForReporter__video'
177 'SELECT count(DISTINCT "videoAbuse"."id") ' +
178 'FROM "videoAbuse" ' +
179 `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "AbuseModel"."reporterAccountId" ` +
182 'countReportsForReporter__deletedVideo'
187 'SELECT count(DISTINCT "videoAbuse"."id") ' +
188 'FROM "videoAbuse" ' +
189 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
190 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
191 'INNER JOIN "account" ON ' +
192 '"videoChannel"."accountId" = "VideoAbuse->Video->VideoChannel"."accountId" ' +
193 `OR "videoChannel"."accountId" = CAST("VideoAbuse"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
196 'countReportsForReportee__video'
201 'SELECT count(DISTINCT "videoAbuse"."id") ' +
202 'FROM "videoAbuse" ' +
203 `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "VideoAbuse->Video->VideoChannel"."accountId" ` +
204 `OR CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = ` +
205 `CAST("VideoAbuse"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
208 'countReportsForReportee__deletedVideo'
214 model: AccountModel.scope(AccountScopeNames.SUMMARY),
215 as: 'ReporterAccount',
217 where: searchAttribute(options.searchReporter, 'name')
220 model: AccountModel.scope(AccountScopeNames.SUMMARY),
221 as: 'FlaggedAccount',
223 where: searchAttribute(options.searchReportee, 'name')
226 model: VideoAbuseModel,
227 required: options.filter === 'video' || !!options.videoIs || videoRequired,
231 required: videoRequired,
232 where: searchAttribute(options.searchVideo, 'name'),
235 model: ThumbnailModel
238 model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: false } as SummaryOptions ] }),
239 where: searchAttribute(options.searchVideoChannel, 'name'),
243 model: AccountModel.scope(AccountScopeNames.SUMMARY),
245 where: searchAttribute(options.searchReportee, 'name')
250 attributes: [ 'id', 'reason', 'unfederated' ],
251 model: VideoBlacklistModel,
252 required: onlyBlacklisted
267 fields: [ 'reporterAccountId' ]
270 fields: [ 'flaggedAccountId' ]
274 export class AbuseModel extends Model<AbuseModel> {
278 @Is('VideoAbuseReason', value => throwIfNotValid(value, isAbuseReasonValid, 'reason'))
279 @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.REASON.max))
284 @Is('VideoAbuseState', value => throwIfNotValid(value, isAbuseStateValid, 'state'))
290 @Is('VideoAbuseModerationComment', value => throwIfNotValid(value, isAbuseModerationCommentValid, 'moderationComment', true))
291 @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.MODERATION_COMMENT.max))
292 moderationComment: string
296 @Column(DataType.ARRAY(DataType.INTEGER))
297 predefinedReasons: AbusePredefinedReasons[]
305 @ForeignKey(() => AccountModel)
307 reporterAccountId: number
309 @BelongsTo(() => AccountModel, {
311 name: 'reporterAccountId',
314 as: 'ReporterAccount',
317 ReporterAccount: AccountModel
319 @ForeignKey(() => AccountModel)
321 flaggedAccountId: number
323 @BelongsTo(() => AccountModel, {
325 name: 'flaggedAccountId',
328 as: 'FlaggedAccount',
331 FlaggedAccount: AccountModel
333 @HasOne(() => VideoCommentAbuseModel, {
340 VideoCommentAbuse: VideoCommentAbuseModel
342 @HasOne(() => VideoAbuseModel, {
349 VideoAbuse: VideoAbuseModel
351 static loadByIdAndVideoId (id: number, videoId?: number, uuid?: string): Bluebird<MAbuse> {
352 const videoWhere: WhereOptions = {}
354 if (videoId) videoWhere.videoId = videoId
355 if (uuid) videoWhere.deletedVideo = { uuid }
360 model: VideoAbuseModel,
369 return AbuseModel.findOne(query)
372 static listForApi (parameters: {
379 serverAccountId: number
380 user?: MUserAccountId
383 predefinedReason?: AbusePredefinedReasonsString
385 videoIs?: AbuseVideoIs
388 searchReporter?: string
389 searchReportee?: string
391 searchVideoChannel?: string
411 const userAccountId = user ? user.Account.id : undefined
412 const predefinedReasonId = predefinedReason ? abusePredefinedReasonsMap[predefinedReason] : undefined
417 order: getSort(sort),
418 col: 'AbuseModel.id',
439 { method: [ ScopeNames.FOR_API, filters ] }
441 .findAndCountAll(query)
442 .then(({ rows, count }) => {
443 return { total: count, data: rows }
447 toFormattedJSON (this: MAbuseFormattable): Abuse {
448 const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
449 const countReportsForVideo = this.get('countReportsForVideo') as number
450 const nthReportForVideo = this.get('nthReportForVideo') as number
451 const countReportsForReporterVideo = this.get('countReportsForReporter__video') as number
452 const countReportsForReporterDeletedVideo = this.get('countReportsForReporter__deletedVideo') as number
453 const countReportsForReporteeVideo = this.get('countReportsForReportee__video') as number
454 const countReportsForReporteeDeletedVideo = this.get('countReportsForReportee__deletedVideo') as number
456 let video: VideoAbuse
458 if (this.VideoAbuse) {
459 const abuseModel = this.VideoAbuse
460 const entity = abuseModel.Video || abuseModel.deletedVideo
468 startAt: abuseModel.startAt,
469 endAt: abuseModel.endAt,
471 deleted: !abuseModel.Video,
472 blacklisted: abuseModel.Video?.isBlacklisted() || false,
473 thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
474 channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel
483 reporterAccount: this.ReporterAccount.toFormattedJSON(),
487 label: AbuseModel.getStateLabel(this.state)
490 moderationComment: this.moderationComment,
495 createdAt: this.createdAt,
496 updatedAt: this.updatedAt,
497 count: countReportsForVideo || 0,
498 nth: nthReportForVideo || 0,
499 countReportsForReporter: (countReportsForReporterVideo || 0) + (countReportsForReporterDeletedVideo || 0),
500 countReportsForReportee: (countReportsForReporteeVideo || 0) + (countReportsForReporteeDeletedVideo || 0),
502 // FIXME: deprecated in 2.3, remove this
508 toActivityPubObject (this: MAbuseAP): AbuseObject {
509 const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
511 const object = this.VideoAbuse?.Video?.url || this.VideoCommentAbuse?.VideoComment?.url || this.FlaggedAccount.Actor.url
513 const startAt = this.VideoAbuse?.startAt
514 const endAt = this.VideoAbuse?.endAt
517 type: 'Flag' as 'Flag',
518 content: this.reason,
520 tag: predefinedReasons.map(r => ({
521 type: 'Hashtag' as 'Hashtag',
529 private static getStateLabel (id: number) {
530 return ABUSE_STATES[id] || 'Unknown'
533 private static getPredefinedReasonsStrings (predefinedReasons: AbusePredefinedReasons[]): AbusePredefinedReasonsString[] {
534 return (predefinedReasons || [])
535 .filter(r => r in AbusePredefinedReasons)
536 .map(r => invert(abusePredefinedReasonsMap)[r] as AbusePredefinedReasonsString)