1 import * as Bluebird from 'bluebird'
2 import { literal, Op } from 'sequelize'
16 } from 'sequelize-typescript'
17 import { VideoAbuseVideoIs } from '@shared/models/videos/abuse/video-abuse-video-is.type'
18 import { VideoAbuseState, VideoDetails } from '../../../shared'
19 import { VideoAbuseObject } from '../../../shared/models/activitypub/objects'
20 import { VideoAbuse } from '../../../shared/models/videos'
22 isVideoAbuseModerationCommentValid,
23 isVideoAbuseReasonValid,
24 isVideoAbuseStateValid
25 } from '../../helpers/custom-validators/video-abuses'
26 import { CONSTRAINTS_FIELDS, VIDEO_ABUSE_STATES } from '../../initializers/constants'
27 import { MUserAccountId, MVideoAbuse, MVideoAbuseFormattable, MVideoAbuseVideo } from '../../typings/models'
28 import { AccountModel } from '../account/account'
29 import { buildBlockedAccountSQL, getSort, searchAttribute, throwIfNotValid } from '../utils'
30 import { ThumbnailModel } from './thumbnail'
31 import { VideoModel } from './video'
32 import { VideoBlacklistModel } from './video-blacklist'
33 import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
35 export enum ScopeNames {
40 [ScopeNames.FOR_API]: (options: {
43 searchReporter?: string
44 searchReportee?: string
46 searchVideoChannel?: string
51 state?: VideoAbuseState
52 videoIs?: VideoAbuseVideoIs
55 serverAccountId: number
60 [Op.notIn]: literal('(' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')')
65 Object.assign(where, {
69 { videoId: { [Op.not]: null } },
70 searchAttribute(options.search, '$Video.name$')
75 { videoId: { [Op.not]: null } },
76 searchAttribute(options.search, '$Video.VideoChannel.name$')
81 { deletedVideo: { [Op.not]: null } },
82 { deletedVideo: searchAttribute(options.search, 'name') }
87 { deletedVideo: { [Op.not]: null } },
88 { deletedVideo: { channel: searchAttribute(options.search, 'displayName') } }
91 searchAttribute(options.search, '$Account.name$')
96 if (options.id) Object.assign(where, { id: options.id })
97 if (options.state) Object.assign(where, { state: options.state })
99 if (options.videoIs === 'deleted') {
100 Object.assign(where, {
107 const onlyBlacklisted = options.videoIs === 'blacklisted'
113 // we don't care about this count for deleted videos, so there are not included
117 'FROM "videoAbuse" ' +
118 'WHERE "videoId" = "VideoAbuseModel"."videoId" ' +
121 'countReportsForVideo'
124 // we don't care about this count for deleted videos, so there are not included
130 'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
131 'FROM "videoAbuse" ' +
133 'WHERE t.id = "VideoAbuseModel".id ' +
141 'SELECT count("videoAbuse"."id") ' +
142 'FROM "videoAbuse" ' +
143 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
144 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
145 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
146 'WHERE "account"."id" = "VideoAbuseModel"."reporterAccountId" ' +
149 'countReportsForReporter__video'
154 'SELECT count(DISTINCT "videoAbuse"."id") ' +
155 'FROM "videoAbuse" ' +
156 `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "VideoAbuseModel"."reporterAccountId" ` +
159 'countReportsForReporter__deletedVideo'
164 'SELECT count(DISTINCT "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 ' +
169 '"videoChannel"."accountId" = "Video->VideoChannel"."accountId" ' +
170 `OR "videoChannel"."accountId" = CAST("VideoAbuseModel"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
173 'countReportsForReportee__video'
178 'SELECT count(DISTINCT "videoAbuse"."id") ' +
179 'FROM "videoAbuse" ' +
180 `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "Video->VideoChannel"."accountId" ` +
181 `OR CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = ` +
182 `CAST("VideoAbuseModel"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
185 'countReportsForReportee__deletedVideo'
193 where: searchAttribute(options.searchReporter, 'name')
197 required: !!(onlyBlacklisted || options.searchVideo || options.searchReportee || options.searchVideoChannel),
198 where: searchAttribute(options.searchVideo, 'name'),
201 model: ThumbnailModel
204 model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: true } as SummaryOptions ] }),
205 where: searchAttribute(options.searchVideoChannel, 'name'),
209 where: searchAttribute(options.searchReportee, 'name')
214 attributes: [ 'id', 'reason', 'unfederated' ],
215 model: VideoBlacklistModel,
216 required: onlyBlacklisted
226 tableName: 'videoAbuse',
229 fields: [ 'videoId' ]
232 fields: [ 'reporterAccountId' ]
236 export class VideoAbuseModel extends Model<VideoAbuseModel> {
240 @Is('VideoAbuseReason', value => throwIfNotValid(value, isVideoAbuseReasonValid, 'reason'))
241 @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_ABUSES.REASON.max))
246 @Is('VideoAbuseState', value => throwIfNotValid(value, isVideoAbuseStateValid, 'state'))
248 state: VideoAbuseState
252 @Is('VideoAbuseModerationComment', value => throwIfNotValid(value, isVideoAbuseModerationCommentValid, 'moderationComment', true))
253 @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_ABUSES.MODERATION_COMMENT.max))
254 moderationComment: string
258 @Column(DataType.JSONB)
259 deletedVideo: VideoDetails
267 @ForeignKey(() => AccountModel)
269 reporterAccountId: number
271 @BelongsTo(() => AccountModel, {
277 Account: AccountModel
279 @ForeignKey(() => VideoModel)
283 @BelongsTo(() => VideoModel, {
291 static loadByIdAndVideoId (id: number, videoId?: number, uuid?: string): Bluebird<MVideoAbuse> {
292 const videoAttributes = {}
293 if (videoId) videoAttributes['videoId'] = videoId
294 if (uuid) videoAttributes['deletedVideo'] = { uuid }
302 return VideoAbuseModel.findOne(query)
305 static listForApi (parameters: {
310 serverAccountId: number
311 user?: MUserAccountId
314 state?: VideoAbuseState
315 videoIs?: VideoAbuseVideoIs
318 searchReporter?: string
319 searchReportee?: string
321 searchVideoChannel?: string
339 const userAccountId = user ? user.Account.id : undefined
344 order: getSort(sort),
345 col: 'VideoAbuseModel.id',
362 return VideoAbuseModel
363 .scope({ method: [ ScopeNames.FOR_API, filters ] })
364 .findAndCountAll(query)
365 .then(({ rows, count }) => {
366 return { total: count, data: rows }
370 toFormattedJSON (this: MVideoAbuseFormattable): VideoAbuse {
371 const countReportsForVideo = this.get('countReportsForVideo') as number
372 const nthReportForVideo = this.get('nthReportForVideo') as number
373 const countReportsForReporterVideo = this.get('countReportsForReporter__video') as number
374 const countReportsForReporterDeletedVideo = this.get('countReportsForReporter__deletedVideo') as number
375 const countReportsForReporteeVideo = this.get('countReportsForReportee__video') as number
376 const countReportsForReporteeDeletedVideo = this.get('countReportsForReportee__deletedVideo') as number
378 const video = this.Video
385 reporterAccount: this.Account.toFormattedJSON(),
388 label: VideoAbuseModel.getStateLabel(this.state)
390 moderationComment: this.moderationComment,
396 deleted: !this.Video,
397 blacklisted: this.Video && this.Video.isBlacklisted(),
398 thumbnailPath: this.Video?.getMiniatureStaticPath(),
399 channel: this.Video?.VideoChannel.toFormattedJSON() || this.deletedVideo?.channel
401 createdAt: this.createdAt,
402 updatedAt: this.updatedAt,
403 count: countReportsForVideo || 0,
404 nth: nthReportForVideo || 0,
405 countReportsForReporter: (countReportsForReporterVideo || 0) + (countReportsForReporterDeletedVideo || 0),
406 countReportsForReportee: (countReportsForReporteeVideo || 0) + (countReportsForReporteeDeletedVideo || 0)
410 toActivityPubObject (this: MVideoAbuseVideo): VideoAbuseObject {
412 type: 'Flag' as 'Flag',
413 content: this.reason,
414 object: this.Video.url
418 private static getStateLabel (id: number) {
419 return VIDEO_ABUSE_STATES[id] || 'Unknown'