server/models/abuse/abuse.ts

   1 import * as Bluebird from 'bluebird'
   2 import { invert } from 'lodash'
   3 import { literal, Op, WhereOptions } from 'sequelize'
   4 import {
   5   AllowNull,
   6   BelongsTo,
   7   Column,
   8   CreatedAt,
   9   DataType,
  10   Default,
  11   ForeignKey,
  12   HasOne,
  13   Is,
  14   Model,
  15   Scopes,
  16   Table,
  17   UpdatedAt
  18 } from 'sequelize-typescript'
  19 import { isAbuseModerationCommentValid, isAbuseReasonValid, isAbuseStateValid } from '@server/helpers/custom-validators/abuses'
  20 import {
  21   Abuse,
  22   AbuseObject,
  23   AbusePredefinedReasons,
  24   abusePredefinedReasonsMap,
  25   AbusePredefinedReasonsString,
  26   AbuseState,
  27   AbuseVideoIs,
  28   VideoAbuse
  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'
  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
  53     // video releated
  54     searchVideo?: string
  55     searchVideoChannel?: string
  56     videoIs?: AbuseVideoIs
  57
  58     // filters
  59     id?: number
  60     predefinedReasonId?: number
  61     filter?: AbuseFilter
  62
  63     state?: AbuseState
  64
  65     // accountIds
  66     serverAccountId: number
  67     userAccountId: number
  68   }) => {
  69     const onlyBlacklisted = options.videoIs === 'blacklisted'
  70     const videoRequired = !!(onlyBlacklisted || options.searchVideo || options.searchVideoChannel)
  71
  72     const where = {
  73       reporterAccountId: {
  74         [Op.notIn]: literal('(' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')')
  75       }
  76     }
  77
  78     if (options.search) {
  79       const escapedSearch = AbuseModel.sequelize.escape('%' + options.search + '%')
  80
  81       Object.assign(where, {
  82         [Op.or]: [
  83           {
  84             [Op.and]: [
  85               { '$VideoAbuse.videoId$': { [Op.not]: null } },
  86               searchAttribute(options.search, '$VideoAbuse.Video.name$')
  87             ]
  88           },
  89           {
  90             [Op.and]: [
  91               { '$VideoAbuse.videoId$': { [Op.not]: null } },
  92               searchAttribute(options.search, '$VideoAbuse.Video.VideoChannel.name$')
  93             ]
  94           },
  95           {
  96             [Op.and]: [
  97               { '$VideoAbuse.deletedVideo$': { [Op.not]: null } },
  98               literal(`"VideoAbuse"."deletedVideo"->>'name' ILIKE ${escapedSearch}`)
  99             ]
 100           },
 101           {
 102             [Op.and]: [
 103               { '$VideoAbuse.deletedVideo$': { [Op.not]: null } },
 104               literal(`"VideoAbuse"."deletedVideo"->'channel'->>'displayName' ILIKE ${escapedSearch}`)
 105             ]
 106           },
 107           searchAttribute(options.search, '$ReporterAccount.name$'),
 108           searchAttribute(options.search, '$FlaggedAccount.name$')
 109         ]
 110       })
 111     }
 112
 113     if (options.id) Object.assign(where, { id: options.id })
 114     if (options.state) Object.assign(where, { state: options.state })
 115
 116     if (options.videoIs === 'deleted') {
 117       Object.assign(where, {
 118         '$VideoAbuse.deletedVideo$': {
 119           [Op.not]: null
 120         }
 121       })
 122     }
 123
 124     if (options.predefinedReasonId) {
 125       Object.assign(where, {
 126         predefinedReasons: {
 127           [Op.contains]: [ options.predefinedReasonId ]
 128         }
 129       })
 130     }
 131
 132     return {
 133       attributes: {
 134         include: [
 135           [
 136             // we don't care about this count for deleted videos, so there are not included
 137             literal(
 138               '(' +
 139                 'SELECT count(*) ' +
 140                 'FROM "videoAbuse" ' +
 141                 'WHERE "videoId" = "VideoAbuse"."videoId" ' +
 142               ')'
 143             ),
 144             'countReportsForVideo'
 145           ],
 146           [
 147             // we don't care about this count for deleted videos, so there are not included
 148             literal(
 149               '(' +
 150                 'SELECT t.nth ' +
 151                 'FROM ( ' +
 152                   'SELECT id, ' +
 153                          'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
 154                   'FROM "videoAbuse" ' +
 155                 ') t ' +
 156                 'WHERE t.id = "VideoAbuse".id' +
 157               ')'
 158             ),
 159             'nthReportForVideo'
 160           ],
 161           [
 162             literal(
 163               '(' +
 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" ' +
 170               ')'
 171             ),
 172             'countReportsForReporter__video'
 173           ],
 174           [
 175             literal(
 176               '(' +
 177                 'SELECT count(DISTINCT "videoAbuse"."id") ' +
 178                 'FROM "videoAbuse" ' +
 179                 `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "AbuseModel"."reporterAccountId" ` +
 180               ')'
 181             ),
 182             'countReportsForReporter__deletedVideo'
 183           ],
 184           [
 185             literal(
 186               '(' +
 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) ` +
 194               ')'
 195             ),
 196             'countReportsForReportee__video'
 197           ],
 198           [
 199             literal(
 200               '(' +
 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) ` +
 206               ')'
 207             ),
 208             'countReportsForReportee__deletedVideo'
 209           ]
 210         ]
 211       },
 212       include: [
 213         {
 214           model: AccountModel.scope(AccountScopeNames.SUMMARY),
 215           as: 'ReporterAccount',
 216           required: true,
 217           where: searchAttribute(options.searchReporter, 'name')
 218         },
 219         {
 220           model: AccountModel.scope(AccountScopeNames.SUMMARY),
 221           as: 'FlaggedAccount',
 222           required: true,
 223           where: searchAttribute(options.searchReportee, 'name')
 224         },
 225         {
 226           model: VideoAbuseModel,
 227           required: options.filter === 'video' || !!options.videoIs || videoRequired,
 228           include: [
 229             {
 230               model: VideoModel,
 231               required: videoRequired,
 232               where: searchAttribute(options.searchVideo, 'name'),
 233               include: [
 234                 {
 235                   model: ThumbnailModel
 236                 },
 237                 {
 238                   model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: false } as SummaryOptions ] }),
 239                   where: searchAttribute(options.searchVideoChannel, 'name'),
 240                   required: true,
 241                   include: [
 242                     {
 243                       model: AccountModel.scope(AccountScopeNames.SUMMARY),
 244                       required: true,
 245                       where: searchAttribute(options.searchReportee, 'name')
 246                     }
 247                   ]
 248                 },
 249                 {
 250                   attributes: [ 'id', 'reason', 'unfederated' ],
 251                   model: VideoBlacklistModel,
 252                   required: onlyBlacklisted
 253                 }
 254               ]
 255             }
 256           ]
 257         }
 258       ],
 259       where
 260     }
 261   }
 262 }))
 263 @Table({
 264   tableName: 'abuse',
 265   indexes: [
 266     {
 267       fields: [ 'reporterAccountId' ]
 268     },
 269     {
 270       fields: [ 'flaggedAccountId' ]
 271     }
 272   ]
 273 })
 274 export class AbuseModel extends Model<AbuseModel> {
 275
 276   @AllowNull(false)
 277   @Default(null)
 278   @Is('VideoAbuseReason', value => throwIfNotValid(value, isAbuseReasonValid, 'reason'))
 279   @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.REASON.max))
 280   reason: string
 281
 282   @AllowNull(false)
 283   @Default(null)
 284   @Is('VideoAbuseState', value => throwIfNotValid(value, isAbuseStateValid, 'state'))
 285   @Column
 286   state: AbuseState
 287
 288   @AllowNull(true)
 289   @Default(null)
 290   @Is('VideoAbuseModerationComment', value => throwIfNotValid(value, isAbuseModerationCommentValid, 'moderationComment', true))
 291   @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.MODERATION_COMMENT.max))
 292   moderationComment: string
 293
 294   @AllowNull(true)
 295   @Default(null)
 296   @Column(DataType.ARRAY(DataType.INTEGER))
 297   predefinedReasons: AbusePredefinedReasons[]
 298
 299   @CreatedAt
 300   createdAt: Date
 301
 302   @UpdatedAt
 303   updatedAt: Date
 304
 305   @ForeignKey(() => AccountModel)
 306   @Column
 307   reporterAccountId: number
 308
 309   @BelongsTo(() => AccountModel, {
 310     foreignKey: {
 311       name: 'reporterAccountId',
 312       allowNull: true
 313     },
 314     as: 'ReporterAccount',
 315     onDelete: 'set null'
 316   })
 317   ReporterAccount: AccountModel
 318
 319   @ForeignKey(() => AccountModel)
 320   @Column
 321   flaggedAccountId: number
 322
 323   @BelongsTo(() => AccountModel, {
 324     foreignKey: {
 325       name: 'flaggedAccountId',
 326       allowNull: true
 327     },
 328     as: 'FlaggedAccount',
 329     onDelete: 'set null'
 330   })
 331   FlaggedAccount: AccountModel
 332
 333   @HasOne(() => VideoCommentAbuseModel, {
 334     foreignKey: {
 335       name: 'abuseId',
 336       allowNull: false
 337     },
 338     onDelete: 'cascade'
 339   })
 340   VideoCommentAbuse: VideoCommentAbuseModel
 341
 342   @HasOne(() => VideoAbuseModel, {
 343     foreignKey: {
 344       name: 'abuseId',
 345       allowNull: false
 346     },
 347     onDelete: 'cascade'
 348   })
 349   VideoAbuse: VideoAbuseModel
 350
 351   static loadByIdAndVideoId (id: number, videoId?: number, uuid?: string): Bluebird<MAbuse> {
 352     const videoWhere: WhereOptions = {}
 353
 354     if (videoId) videoWhere.videoId = videoId
 355     if (uuid) videoWhere.deletedVideo = { uuid }
 356
 357     const query = {
 358       include: [
 359         {
 360           model: VideoAbuseModel,
 361           required: true,
 362           where: videoWhere
 363         }
 364       ],
 365       where: {
 366         id
 367       }
 368     }
 369     return AbuseModel.findOne(query)
 370   }
 371
 372   static listForApi (parameters: {
 373     start: number
 374     count: number
 375     sort: string
 376
 377     filter?: AbuseFilter
 378
 379     serverAccountId: number
 380     user?: MUserAccountId
 381
 382     id?: number
 383     predefinedReason?: AbusePredefinedReasonsString
 384     state?: AbuseState
 385     videoIs?: AbuseVideoIs
 386
 387     search?: string
 388     searchReporter?: string
 389     searchReportee?: string
 390     searchVideo?: string
 391     searchVideoChannel?: string
 392   }) {
 393     const {
 394       start,
 395       count,
 396       sort,
 397       search,
 398       user,
 399       serverAccountId,
 400       state,
 401       videoIs,
 402       predefinedReason,
 403       searchReportee,
 404       searchVideo,
 405       filter,
 406       searchVideoChannel,
 407       searchReporter,
 408       id
 409     } = parameters
 410
 411     const userAccountId = user ? user.Account.id : undefined
 412     const predefinedReasonId = predefinedReason ? abusePredefinedReasonsMap[predefinedReason] : undefined
 413
 414     const query = {
 415       offset: start,
 416       limit: count,
 417       order: getSort(sort),
 418       col: 'AbuseModel.id',
 419       distinct: true
 420     }
 421
 422     const filters = {
 423       id,
 424       filter,
 425       predefinedReasonId,
 426       search,
 427       state,
 428       videoIs,
 429       searchReportee,
 430       searchVideo,
 431       searchVideoChannel,
 432       searchReporter,
 433       serverAccountId,
 434       userAccountId
 435     }
 436
 437     return AbuseModel
 438       .scope([
 439         { method: [ ScopeNames.FOR_API, filters ] }
 440       ])
 441       .findAndCountAll(query)
 442       .then(({ rows, count }) => {
 443         return { total: count, data: rows }
 444       })
 445   }
 446
 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
 455
 456     let video: VideoAbuse
 457
 458     if (this.VideoAbuse) {
 459       const abuseModel = this.VideoAbuse
 460       const entity = abuseModel.Video || abuseModel.deletedVideo
 461
 462       video = {
 463         id: entity.id,
 464         uuid: entity.uuid,
 465         name: entity.name,
 466         nsfw: entity.nsfw,
 467
 468         startAt: abuseModel.startAt,
 469         endAt: abuseModel.endAt,
 470
 471         deleted: !abuseModel.Video,
 472         blacklisted: abuseModel.Video?.isBlacklisted() || false,
 473         thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
 474         channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel
 475       }
 476     }
 477
 478     return {
 479       id: this.id,
 480       reason: this.reason,
 481       predefinedReasons,
 482
 483       reporterAccount: this.ReporterAccount.toFormattedJSON(),
 484
 485       state: {
 486         id: this.state,
 487         label: AbuseModel.getStateLabel(this.state)
 488       },
 489
 490       moderationComment: this.moderationComment,
 491
 492       video,
 493       comment: null,
 494
 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),
 501
 502       // FIXME: deprecated in 2.3, remove this
 503       startAt: null,
 504       endAt: null
 505     }
 506   }
 507
 508   toActivityPubObject (this: MAbuseAP): AbuseObject {
 509     const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 510
 511     const object = this.VideoAbuse?.Video?.url || this.VideoCommentAbuse?.VideoComment?.url || this.FlaggedAccount.Actor.url
 512
 513     const startAt = this.VideoAbuse?.startAt
 514     const endAt = this.VideoAbuse?.endAt
 515
 516     return {
 517       type: 'Flag' as 'Flag',
 518       content: this.reason,
 519       object,
 520       tag: predefinedReasons.map(r => ({
 521         type: 'Hashtag' as 'Hashtag',
 522         name: r
 523       })),
 524       startAt,
 525       endAt
 526     }
 527   }
 528
 529   private static getStateLabel (id: number) {
 530     return ABUSE_STATES[id] || 'Unknown'
 531   }
 532
 533   private static getPredefinedReasonsStrings (predefinedReasons: AbusePredefinedReasons[]): AbusePredefinedReasonsString[] {
 534     return (predefinedReasons || [])
 535       .filter(r => r in AbusePredefinedReasons)
 536       .map(r => invert(abusePredefinedReasonsMap)[r] as AbusePredefinedReasonsString)
 537   }
 538 }