server/models/video/video-abuse.ts

   1 import * as Bluebird from 'bluebird'
   2 import { literal, Op } from 'sequelize'
   3 import {
   4   AllowNull,
   5   BelongsTo,
   6   Column,
   7   CreatedAt,
   8   DataType,
   9   Default,
  10   ForeignKey,
  11   Is,
  12   Model,
  13   Scopes,
  14   Table,
  15   UpdatedAt
  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'
  21 import {
  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'
  34
  35 export enum ScopeNames {
  36   FOR_API = 'FOR_API'
  37 }
  38
  39 @Scopes(() => ({
  40   [ScopeNames.FOR_API]: (options: {
  41     // search
  42     search?: string
  43     searchReporter?: string
  44     searchReportee?: string
  45     searchVideo?: string
  46     searchVideoChannel?: string
  47
  48     // filters
  49     id?: number
  50
  51     state?: VideoAbuseState
  52     videoIs?: VideoAbuseVideoIs
  53
  54     // accountIds
  55     serverAccountId: number
  56     userAccountId: number
  57   }) => {
  58     const where = {
  59       reporterAccountId: {
  60         [Op.notIn]: literal('(' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')')
  61       }
  62     }
  63
  64     if (options.search) {
  65       Object.assign(where, {
  66         [Op.or]: [
  67           {
  68             [Op.and]: [
  69               { videoId: { [Op.not]: null } },
  70               searchAttribute(options.search, '$Video.name$')
  71             ]
  72           },
  73           {
  74             [Op.and]: [
  75               { videoId: { [Op.not]: null } },
  76               searchAttribute(options.search, '$Video.VideoChannel.name$')
  77             ]
  78           },
  79           {
  80             [Op.and]: [
  81               { deletedVideo: { [Op.not]: null } },
  82               { deletedVideo: searchAttribute(options.search, 'name') }
  83             ]
  84           },
  85           {
  86             [Op.and]: [
  87               { deletedVideo: { [Op.not]: null } },
  88               { deletedVideo: { channel: searchAttribute(options.search, 'displayName') } }
  89             ]
  90           },
  91           searchAttribute(options.search, '$Account.name$')
  92         ]
  93       })
  94     }
  95
  96     if (options.id) Object.assign(where, { id: options.id })
  97     if (options.state) Object.assign(where, { state: options.state })
  98
  99     if (options.videoIs === 'deleted') {
 100       Object.assign(where, {
 101         deletedVideo: {
 102           [Op.not]: null
 103         }
 104       })
 105     }
 106
 107     const onlyBlacklisted = options.videoIs === 'blacklisted'
 108
 109     return {
 110       attributes: {
 111         include: [
 112           [
 113             // we don't care about this count for deleted videos, so there are not included
 114             literal(
 115               '(' +
 116                 'SELECT count(*) ' +
 117                 'FROM "videoAbuse" ' +
 118                 'WHERE "videoId" = "VideoAbuseModel"."videoId" ' +
 119               ')'
 120             ),
 121             'countReportsForVideo'
 122           ],
 123           [
 124             // we don't care about this count for deleted videos, so there are not included
 125             literal(
 126               '(' +
 127                 'SELECT t.nth ' +
 128                 'FROM ( ' +
 129                   'SELECT id, ' +
 130                          'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
 131                   'FROM "videoAbuse" ' +
 132                 ') t ' +
 133                 'WHERE t.id = "VideoAbuseModel".id ' +
 134               ')'
 135             ),
 136             'nthReportForVideo'
 137           ],
 138           [
 139             literal(
 140               '(' +
 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" ' +
 147               ')'
 148             ),
 149             'countReportsForReporter__video'
 150           ],
 151           [
 152             literal(
 153               '(' +
 154                 'SELECT count(DISTINCT "videoAbuse"."id") ' +
 155                 'FROM "videoAbuse" ' +
 156                 `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "VideoAbuseModel"."reporterAccountId" ` +
 157               ')'
 158             ),
 159             'countReportsForReporter__deletedVideo'
 160           ],
 161           [
 162             literal(
 163               '(' +
 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) ` +
 171               ')'
 172             ),
 173             'countReportsForReportee__video'
 174           ],
 175           [
 176             literal(
 177               '(' +
 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) ` +
 183               ')'
 184             ),
 185             'countReportsForReportee__deletedVideo'
 186           ]
 187         ]
 188       },
 189       include: [
 190         {
 191           model: AccountModel,
 192           required: true,
 193           where: searchAttribute(options.searchReporter, 'name')
 194         },
 195         {
 196           model: VideoModel,
 197           required: !!(onlyBlacklisted || options.searchVideo || options.searchReportee || options.searchVideoChannel),
 198           where: searchAttribute(options.searchVideo, 'name'),
 199           include: [
 200             {
 201               model: ThumbnailModel
 202             },
 203             {
 204               model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: true } as SummaryOptions ] }),
 205               where: searchAttribute(options.searchVideoChannel, 'name'),
 206               include: [
 207                 {
 208                   model: AccountModel,
 209                   where: searchAttribute(options.searchReportee, 'name')
 210                 }
 211               ]
 212             },
 213             {
 214               attributes: [ 'id', 'reason', 'unfederated' ],
 215               model: VideoBlacklistModel,
 216               required: onlyBlacklisted
 217             }
 218           ]
 219         }
 220       ],
 221       where
 222     }
 223   }
 224 }))
 225 @Table({
 226   tableName: 'videoAbuse',
 227   indexes: [
 228     {
 229       fields: [ 'videoId' ]
 230     },
 231     {
 232       fields: [ 'reporterAccountId' ]
 233     }
 234   ]
 235 })
 236 export class VideoAbuseModel extends Model<VideoAbuseModel> {
 237
 238   @AllowNull(false)
 239   @Default(null)
 240   @Is('VideoAbuseReason', value => throwIfNotValid(value, isVideoAbuseReasonValid, 'reason'))
 241   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_ABUSES.REASON.max))
 242   reason: string
 243
 244   @AllowNull(false)
 245   @Default(null)
 246   @Is('VideoAbuseState', value => throwIfNotValid(value, isVideoAbuseStateValid, 'state'))
 247   @Column
 248   state: VideoAbuseState
 249
 250   @AllowNull(true)
 251   @Default(null)
 252   @Is('VideoAbuseModerationComment', value => throwIfNotValid(value, isVideoAbuseModerationCommentValid, 'moderationComment', true))
 253   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_ABUSES.MODERATION_COMMENT.max))
 254   moderationComment: string
 255
 256   @AllowNull(true)
 257   @Default(null)
 258   @Column(DataType.JSONB)
 259   deletedVideo: VideoDetails
 260
 261   @CreatedAt
 262   createdAt: Date
 263
 264   @UpdatedAt
 265   updatedAt: Date
 266
 267   @ForeignKey(() => AccountModel)
 268   @Column
 269   reporterAccountId: number
 270
 271   @BelongsTo(() => AccountModel, {
 272     foreignKey: {
 273       allowNull: true
 274     },
 275     onDelete: 'set null'
 276   })
 277   Account: AccountModel
 278
 279   @ForeignKey(() => VideoModel)
 280   @Column
 281   videoId: number
 282
 283   @BelongsTo(() => VideoModel, {
 284     foreignKey: {
 285       allowNull: true
 286     },
 287     onDelete: 'set null'
 288   })
 289   Video: VideoModel
 290
 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 }
 295
 296     const query = {
 297       where: {
 298         id,
 299         ...videoAttributes
 300       }
 301     }
 302     return VideoAbuseModel.findOne(query)
 303   }
 304
 305   static listForApi (parameters: {
 306     start: number
 307     count: number
 308     sort: string
 309
 310     serverAccountId: number
 311     user?: MUserAccountId
 312
 313     id?: number
 314     state?: VideoAbuseState
 315     videoIs?: VideoAbuseVideoIs
 316
 317     search?: string
 318     searchReporter?: string
 319     searchReportee?: string
 320     searchVideo?: string
 321     searchVideoChannel?: string
 322   }) {
 323     const {
 324       start,
 325       count,
 326       sort,
 327       search,
 328       user,
 329       serverAccountId,
 330       state,
 331       videoIs,
 332       searchReportee,
 333       searchVideo,
 334       searchVideoChannel,
 335       searchReporter,
 336       id
 337     } = parameters
 338
 339     const userAccountId = user ? user.Account.id : undefined
 340
 341     const query = {
 342       offset: start,
 343       limit: count,
 344       order: getSort(sort),
 345       col: 'VideoAbuseModel.id',
 346       distinct: true
 347     }
 348
 349     const filters = {
 350       id,
 351       search,
 352       state,
 353       videoIs,
 354       searchReportee,
 355       searchVideo,
 356       searchVideoChannel,
 357       searchReporter,
 358       serverAccountId,
 359       userAccountId
 360     }
 361
 362     return VideoAbuseModel
 363       .scope({ method: [ ScopeNames.FOR_API, filters ] })
 364       .findAndCountAll(query)
 365       .then(({ rows, count }) => {
 366         return { total: count, data: rows }
 367       })
 368   }
 369
 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
 377
 378     const video = this.Video
 379       ? this.Video
 380       : this.deletedVideo
 381
 382     return {
 383       id: this.id,
 384       reason: this.reason,
 385       reporterAccount: this.Account.toFormattedJSON(),
 386       state: {
 387         id: this.state,
 388         label: VideoAbuseModel.getStateLabel(this.state)
 389       },
 390       moderationComment: this.moderationComment,
 391       video: {
 392         id: video.id,
 393         uuid: video.uuid,
 394         name: video.name,
 395         nsfw: video.nsfw,
 396         deleted: !this.Video,
 397         blacklisted: this.Video?.isBlacklisted() || false,
 398         thumbnailPath: this.Video?.getMiniatureStaticPath(),
 399         channel: this.Video?.VideoChannel.toFormattedJSON() || this.deletedVideo?.channel
 400       },
 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)
 407     }
 408   }
 409
 410   toActivityPubObject (this: MVideoAbuseVideo): VideoAbuseObject {
 411     return {
 412       type: 'Flag' as 'Flag',
 413       content: this.reason,
 414       object: this.Video.url
 415     }
 416   }
 417
 418   private static getStateLabel (id: number) {
 419     return VIDEO_ABUSE_STATES[id] || 'Unknown'
 420   }
 421 }