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 {
  19   VideoAbuseState,
  20   VideoDetails,
  21   VideoAbusePredefinedReasons,
  22   VideoAbusePredefinedReasonsString,
  23   videoAbusePredefinedReasonsMap
  24 } from '../../../shared'
  25 import { VideoAbuseObject } from '../../../shared/models/activitypub/objects'
  26 import { VideoAbuse } from '../../../shared/models/videos'
  27 import {
  28   isVideoAbuseModerationCommentValid,
  29   isVideoAbuseReasonValid,
  30   isVideoAbuseStateValid
  31 } from '../../helpers/custom-validators/video-abuses'
  32 import { CONSTRAINTS_FIELDS, VIDEO_ABUSE_STATES } from '../../initializers/constants'
  33 import { MUserAccountId, MVideoAbuse, MVideoAbuseFormattable, MVideoAbuseVideo } from '../../types/models'
  34 import { AccountModel } from '../account/account'
  35 import { buildBlockedAccountSQL, getSort, searchAttribute, throwIfNotValid } from '../utils'
  36 import { ThumbnailModel } from './thumbnail'
  37 import { VideoModel } from './video'
  38 import { VideoBlacklistModel } from './video-blacklist'
  39 import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
  40 import { invert } from 'lodash'
  41
  42 export enum ScopeNames {
  43   FOR_API = 'FOR_API'
  44 }
  45
  46 @Scopes(() => ({
  47   [ScopeNames.FOR_API]: (options: {
  48     // search
  49     search?: string
  50     searchReporter?: string
  51     searchReportee?: string
  52     searchVideo?: string
  53     searchVideoChannel?: string
  54
  55     // filters
  56     id?: number
  57     predefinedReasonId?: number
  58
  59     state?: VideoAbuseState
  60     videoIs?: VideoAbuseVideoIs
  61
  62     // accountIds
  63     serverAccountId: number
  64     userAccountId: number
  65   }) => {
  66     const where = {
  67       reporterAccountId: {
  68         [Op.notIn]: literal('(' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')')
  69       }
  70     }
  71
  72     if (options.search) {
  73       Object.assign(where, {
  74         [Op.or]: [
  75           {
  76             [Op.and]: [
  77               { videoId: { [Op.not]: null } },
  78               searchAttribute(options.search, '$Video.name$')
  79             ]
  80           },
  81           {
  82             [Op.and]: [
  83               { videoId: { [Op.not]: null } },
  84               searchAttribute(options.search, '$Video.VideoChannel.name$')
  85             ]
  86           },
  87           {
  88             [Op.and]: [
  89               { deletedVideo: { [Op.not]: null } },
  90               { deletedVideo: searchAttribute(options.search, 'name') }
  91             ]
  92           },
  93           {
  94             [Op.and]: [
  95               { deletedVideo: { [Op.not]: null } },
  96               { deletedVideo: { channel: searchAttribute(options.search, 'displayName') } }
  97             ]
  98           },
  99           searchAttribute(options.search, '$Account.name$')
 100         ]
 101       })
 102     }
 103
 104     if (options.id) Object.assign(where, { id: options.id })
 105     if (options.state) Object.assign(where, { state: options.state })
 106
 107     if (options.videoIs === 'deleted') {
 108       Object.assign(where, {
 109         deletedVideo: {
 110           [Op.not]: null
 111         }
 112       })
 113     }
 114
 115     if (options.predefinedReasonId) {
 116       Object.assign(where, {
 117         predefinedReasons: {
 118           [Op.contains]: [ options.predefinedReasonId ]
 119         }
 120       })
 121     }
 122
 123     const onlyBlacklisted = options.videoIs === 'blacklisted'
 124
 125     return {
 126       attributes: {
 127         include: [
 128           [
 129             // we don't care about this count for deleted videos, so there are not included
 130             literal(
 131               '(' +
 132                 'SELECT count(*) ' +
 133                 'FROM "videoAbuse" ' +
 134                 'WHERE "videoId" = "VideoAbuseModel"."videoId" ' +
 135               ')'
 136             ),
 137             'countReportsForVideo'
 138           ],
 139           [
 140             // we don't care about this count for deleted videos, so there are not included
 141             literal(
 142               '(' +
 143                 'SELECT t.nth ' +
 144                 'FROM ( ' +
 145                   'SELECT id, ' +
 146                          'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
 147                   'FROM "videoAbuse" ' +
 148                 ') t ' +
 149                 'WHERE t.id = "VideoAbuseModel".id ' +
 150               ')'
 151             ),
 152             'nthReportForVideo'
 153           ],
 154           [
 155             literal(
 156               '(' +
 157                 'SELECT count("videoAbuse"."id") ' +
 158                 'FROM "videoAbuse" ' +
 159                 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
 160                 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 161                 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
 162                 'WHERE "account"."id" = "VideoAbuseModel"."reporterAccountId" ' +
 163               ')'
 164             ),
 165             'countReportsForReporter__video'
 166           ],
 167           [
 168             literal(
 169               '(' +
 170                 'SELECT count(DISTINCT "videoAbuse"."id") ' +
 171                 'FROM "videoAbuse" ' +
 172                 `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "VideoAbuseModel"."reporterAccountId" ` +
 173               ')'
 174             ),
 175             'countReportsForReporter__deletedVideo'
 176           ],
 177           [
 178             literal(
 179               '(' +
 180                 'SELECT count(DISTINCT "videoAbuse"."id") ' +
 181                 'FROM "videoAbuse" ' +
 182                 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
 183                 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 184                 'INNER JOIN "account" ON ' +
 185                       '"videoChannel"."accountId" = "Video->VideoChannel"."accountId" ' +
 186                    `OR "videoChannel"."accountId" = CAST("VideoAbuseModel"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
 187               ')'
 188             ),
 189             'countReportsForReportee__video'
 190           ],
 191           [
 192             literal(
 193               '(' +
 194                 'SELECT count(DISTINCT "videoAbuse"."id") ' +
 195                 'FROM "videoAbuse" ' +
 196                 `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "Video->VideoChannel"."accountId" ` +
 197                    `OR CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = ` +
 198                       `CAST("VideoAbuseModel"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
 199               ')'
 200             ),
 201             'countReportsForReportee__deletedVideo'
 202           ]
 203         ]
 204       },
 205       include: [
 206         {
 207           model: AccountModel,
 208           required: true,
 209           where: searchAttribute(options.searchReporter, 'name')
 210         },
 211         {
 212           model: VideoModel,
 213           required: !!(onlyBlacklisted || options.searchVideo || options.searchReportee || options.searchVideoChannel),
 214           where: searchAttribute(options.searchVideo, 'name'),
 215           include: [
 216             {
 217               model: ThumbnailModel
 218             },
 219             {
 220               model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: true } as SummaryOptions ] }),
 221               where: searchAttribute(options.searchVideoChannel, 'name'),
 222               include: [
 223                 {
 224                   model: AccountModel,
 225                   where: searchAttribute(options.searchReportee, 'name')
 226                 }
 227               ]
 228             },
 229             {
 230               attributes: [ 'id', 'reason', 'unfederated' ],
 231               model: VideoBlacklistModel,
 232               required: onlyBlacklisted
 233             }
 234           ]
 235         }
 236       ],
 237       where
 238     }
 239   }
 240 }))
 241 @Table({
 242   tableName: 'videoAbuse',
 243   indexes: [
 244     {
 245       fields: [ 'videoId' ]
 246     },
 247     {
 248       fields: [ 'reporterAccountId' ]
 249     }
 250   ]
 251 })
 252 export class VideoAbuseModel extends Model<VideoAbuseModel> {
 253
 254   @AllowNull(false)
 255   @Default(null)
 256   @Is('VideoAbuseReason', value => throwIfNotValid(value, isVideoAbuseReasonValid, 'reason'))
 257   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_ABUSES.REASON.max))
 258   reason: string
 259
 260   @AllowNull(false)
 261   @Default(null)
 262   @Is('VideoAbuseState', value => throwIfNotValid(value, isVideoAbuseStateValid, 'state'))
 263   @Column
 264   state: VideoAbuseState
 265
 266   @AllowNull(true)
 267   @Default(null)
 268   @Is('VideoAbuseModerationComment', value => throwIfNotValid(value, isVideoAbuseModerationCommentValid, 'moderationComment', true))
 269   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_ABUSES.MODERATION_COMMENT.max))
 270   moderationComment: string
 271
 272   @AllowNull(true)
 273   @Default(null)
 274   @Column(DataType.JSONB)
 275   deletedVideo: VideoDetails
 276
 277   @AllowNull(true)
 278   @Default(null)
 279   @Column(DataType.ARRAY(DataType.INTEGER))
 280   predefinedReasons: VideoAbusePredefinedReasons[]
 281
 282   @AllowNull(true)
 283   @Default(null)
 284   @Column
 285   startAt: number
 286
 287   @AllowNull(true)
 288   @Default(null)
 289   @Column
 290   endAt: number
 291
 292   @CreatedAt
 293   createdAt: Date
 294
 295   @UpdatedAt
 296   updatedAt: Date
 297
 298   @ForeignKey(() => AccountModel)
 299   @Column
 300   reporterAccountId: number
 301
 302   @BelongsTo(() => AccountModel, {
 303     foreignKey: {
 304       allowNull: true
 305     },
 306     onDelete: 'set null'
 307   })
 308   Account: AccountModel
 309
 310   @ForeignKey(() => VideoModel)
 311   @Column
 312   videoId: number
 313
 314   @BelongsTo(() => VideoModel, {
 315     foreignKey: {
 316       allowNull: true
 317     },
 318     onDelete: 'set null'
 319   })
 320   Video: VideoModel
 321
 322   static loadByIdAndVideoId (id: number, videoId?: number, uuid?: string): Bluebird<MVideoAbuse> {
 323     const videoAttributes = {}
 324     if (videoId) videoAttributes['videoId'] = videoId
 325     if (uuid) videoAttributes['deletedVideo'] = { uuid }
 326
 327     const query = {
 328       where: {
 329         id,
 330         ...videoAttributes
 331       }
 332     }
 333     return VideoAbuseModel.findOne(query)
 334   }
 335
 336   static listForApi (parameters: {
 337     start: number
 338     count: number
 339     sort: string
 340
 341     serverAccountId: number
 342     user?: MUserAccountId
 343
 344     id?: number
 345     predefinedReason?: VideoAbusePredefinedReasonsString
 346     state?: VideoAbuseState
 347     videoIs?: VideoAbuseVideoIs
 348
 349     search?: string
 350     searchReporter?: string
 351     searchReportee?: string
 352     searchVideo?: string
 353     searchVideoChannel?: string
 354   }) {
 355     const {
 356       start,
 357       count,
 358       sort,
 359       search,
 360       user,
 361       serverAccountId,
 362       state,
 363       videoIs,
 364       predefinedReason,
 365       searchReportee,
 366       searchVideo,
 367       searchVideoChannel,
 368       searchReporter,
 369       id
 370     } = parameters
 371
 372     const userAccountId = user ? user.Account.id : undefined
 373     const predefinedReasonId = predefinedReason ? videoAbusePredefinedReasonsMap[predefinedReason] : undefined
 374
 375     const query = {
 376       offset: start,
 377       limit: count,
 378       order: getSort(sort),
 379       col: 'VideoAbuseModel.id',
 380       distinct: true
 381     }
 382
 383     const filters = {
 384       id,
 385       predefinedReasonId,
 386       search,
 387       state,
 388       videoIs,
 389       searchReportee,
 390       searchVideo,
 391       searchVideoChannel,
 392       searchReporter,
 393       serverAccountId,
 394       userAccountId
 395     }
 396
 397     return VideoAbuseModel
 398       .scope([
 399         { method: [ ScopeNames.FOR_API, filters ] }
 400       ])
 401       .findAndCountAll(query)
 402       .then(({ rows, count }) => {
 403         return { total: count, data: rows }
 404       })
 405   }
 406
 407   toFormattedJSON (this: MVideoAbuseFormattable): VideoAbuse {
 408     const predefinedReasons = VideoAbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 409     const countReportsForVideo = this.get('countReportsForVideo') as number
 410     const nthReportForVideo = this.get('nthReportForVideo') as number
 411     const countReportsForReporterVideo = this.get('countReportsForReporter__video') as number
 412     const countReportsForReporterDeletedVideo = this.get('countReportsForReporter__deletedVideo') as number
 413     const countReportsForReporteeVideo = this.get('countReportsForReportee__video') as number
 414     const countReportsForReporteeDeletedVideo = this.get('countReportsForReportee__deletedVideo') as number
 415
 416     const video = this.Video
 417       ? this.Video
 418       : this.deletedVideo
 419
 420     return {
 421       id: this.id,
 422       reason: this.reason,
 423       predefinedReasons,
 424       reporterAccount: this.Account.toFormattedJSON(),
 425       state: {
 426         id: this.state,
 427         label: VideoAbuseModel.getStateLabel(this.state)
 428       },
 429       moderationComment: this.moderationComment,
 430       video: {
 431         id: video.id,
 432         uuid: video.uuid,
 433         name: video.name,
 434         nsfw: video.nsfw,
 435         deleted: !this.Video,
 436         blacklisted: this.Video?.isBlacklisted() || false,
 437         thumbnailPath: this.Video?.getMiniatureStaticPath(),
 438         channel: this.Video?.VideoChannel.toFormattedJSON() || this.deletedVideo?.channel
 439       },
 440       createdAt: this.createdAt,
 441       updatedAt: this.updatedAt,
 442       startAt: this.startAt,
 443       endAt: this.endAt,
 444       count: countReportsForVideo || 0,
 445       nth: nthReportForVideo || 0,
 446       countReportsForReporter: (countReportsForReporterVideo || 0) + (countReportsForReporterDeletedVideo || 0),
 447       countReportsForReportee: (countReportsForReporteeVideo || 0) + (countReportsForReporteeDeletedVideo || 0)
 448     }
 449   }
 450
 451   toActivityPubObject (this: MVideoAbuseVideo): VideoAbuseObject {
 452     const predefinedReasons = VideoAbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 453
 454     const startAt = this.startAt
 455     const endAt = this.endAt
 456
 457     return {
 458       type: 'Flag' as 'Flag',
 459       content: this.reason,
 460       object: this.Video.url,
 461       tag: predefinedReasons.map(r => ({
 462         type: 'Hashtag' as 'Hashtag',
 463         name: r
 464       })),
 465       startAt,
 466       endAt
 467     }
 468   }
 469
 470   private static getStateLabel (id: number) {
 471     return VIDEO_ABUSE_STATES[id] || 'Unknown'
 472   }
 473
 474   private static getPredefinedReasonsStrings (predefinedReasons: VideoAbusePredefinedReasons[]): VideoAbusePredefinedReasonsString[] {
 475     return (predefinedReasons || [])
 476       .filter(r => r in VideoAbusePredefinedReasons)
 477       .map(r => invert(videoAbusePredefinedReasonsMap)[r] as VideoAbusePredefinedReasonsString)
 478   }
 479 }