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   AbuseFilter,
  23   AbuseObject,
  24   AbusePredefinedReasons,
  25   abusePredefinedReasonsMap,
  26   AbusePredefinedReasonsString,
  27   AbuseState,
  28   AbuseVideoIs,
  29   VideoAbuse,
  30   VideoCommentAbuse
  31 } from '@shared/models'
  32 import { ABUSE_STATES, CONSTRAINTS_FIELDS } from '../../initializers/constants'
  33 import { MAbuse, MAbuseAP, MAbuseFormattable, MUserAccountId } from '../../types/models'
  34 import { AccountModel, ScopeNames as AccountScopeNames, SummaryOptions as AccountSummaryOptions } from '../account/account'
  35 import { buildBlockedAccountSQL, getSort, searchAttribute, throwIfNotValid } from '../utils'
  36 import { ThumbnailModel } from '../video/thumbnail'
  37 import { VideoModel } from '../video/video'
  38 import { VideoBlacklistModel } from '../video/video-blacklist'
  39 import { ScopeNames as VideoChannelScopeNames, SummaryOptions as ChannelSummaryOptions, VideoChannelModel } from '../video/video-channel'
  40 import { VideoCommentModel } from '../video/video-comment'
  41 import { VideoAbuseModel } from './video-abuse'
  42 import { VideoCommentAbuseModel } from './video-comment-abuse'
  43
  44 export enum ScopeNames {
  45   FOR_API = 'FOR_API'
  46 }
  47
  48 @Scopes(() => ({
  49   [ScopeNames.FOR_API]: (options: {
  50     // search
  51     search?: string
  52     searchReporter?: string
  53     searchReportee?: string
  54
  55     // video releated
  56     searchVideo?: string
  57     searchVideoChannel?: string
  58     videoIs?: AbuseVideoIs
  59
  60     // filters
  61     id?: number
  62     predefinedReasonId?: number
  63     filter?: AbuseFilter
  64
  65     state?: AbuseState
  66
  67     // accountIds
  68     serverAccountId: number
  69     userAccountId: number
  70   }) => {
  71     const whereAnd: WhereOptions[] = []
  72
  73     whereAnd.push({
  74       reporterAccountId: {
  75         [Op.notIn]: literal('(' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')')
  76       }
  77     })
  78
  79     if (options.search) {
  80       const escapedSearch = AbuseModel.sequelize.escape('%' + options.search + '%')
  81
  82       whereAnd.push({
  83         [Op.or]: [
  84           {
  85             [Op.and]: [
  86               { '$VideoAbuse.videoId$': { [Op.not]: null } },
  87               searchAttribute(options.search, '$VideoAbuse.Video.name$')
  88             ]
  89           },
  90           {
  91             [Op.and]: [
  92               { '$VideoAbuse.videoId$': { [Op.not]: null } },
  93               searchAttribute(options.search, '$VideoAbuse.Video.VideoChannel.name$')
  94             ]
  95           },
  96           {
  97             [Op.and]: [
  98               { '$VideoAbuse.deletedVideo$': { [Op.not]: null } },
  99               literal(`"VideoAbuse"."deletedVideo"->>'name' ILIKE ${escapedSearch}`)
 100             ]
 101           },
 102           {
 103             [Op.and]: [
 104               { '$VideoAbuse.deletedVideo$': { [Op.not]: null } },
 105               literal(`"VideoAbuse"."deletedVideo"->'channel'->>'displayName' ILIKE ${escapedSearch}`)
 106             ]
 107           },
 108           searchAttribute(options.search, '$ReporterAccount.name$'),
 109           searchAttribute(options.search, '$FlaggedAccount.name$')
 110         ]
 111       })
 112     }
 113
 114     if (options.id) whereAnd.push({ id: options.id })
 115     if (options.state) whereAnd.push({ state: options.state })
 116
 117     if (options.videoIs === 'deleted') {
 118       whereAnd.push({
 119         '$VideoAbuse.deletedVideo$': {
 120           [Op.not]: null
 121         }
 122       })
 123     }
 124
 125     if (options.predefinedReasonId) {
 126       whereAnd.push({
 127         predefinedReasons: {
 128           [Op.contains]: [ options.predefinedReasonId ]
 129         }
 130       })
 131     }
 132
 133     if (options.filter === 'account') {
 134       whereAnd.push({
 135         videoId: null,
 136         commentId: null
 137       })
 138     }
 139
 140     const onlyBlacklisted = options.videoIs === 'blacklisted'
 141     const videoRequired = !!(onlyBlacklisted || options.searchVideo || options.searchVideoChannel)
 142
 143     return {
 144       attributes: {
 145         include: [
 146           [
 147             // we don't care about this count for deleted videos, so there are not included
 148             literal(
 149               '(' +
 150                 'SELECT count(*) ' +
 151                 'FROM "videoAbuse" ' +
 152                 'WHERE "videoId" = "VideoAbuse"."videoId" AND "videoId" IS NOT NULL' +
 153               ')'
 154             ),
 155             'countReportsForVideo'
 156           ],
 157           [
 158             // we don't care about this count for deleted videos, so there are not included
 159             literal(
 160               '(' +
 161                 'SELECT t.nth ' +
 162                 'FROM ( ' +
 163                   'SELECT id, ' +
 164                          'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
 165                   'FROM "videoAbuse" ' +
 166                 ') t ' +
 167                 'WHERE t.id = "VideoAbuse".id AND t.id IS NOT NULL' +
 168               ')'
 169             ),
 170             'nthReportForVideo'
 171           ],
 172           [
 173             literal(
 174               '(' +
 175                 'SELECT count("abuse"."id") ' +
 176                 'FROM "abuse" ' +
 177                 'WHERE "abuse"."reporterAccountId" = "AbuseModel"."reporterAccountId"' +
 178               ')'
 179             ),
 180             'countReportsForReporter'
 181           ],
 182           [
 183             literal(
 184               '(' +
 185                 'SELECT count("abuse"."id") ' +
 186                 'FROM "abuse" ' +
 187                 'WHERE "abuse"."flaggedAccountId" = "AbuseModel"."flaggedAccountId"' +
 188               ')'
 189             ),
 190             'countReportsForReportee'
 191           ]
 192         ]
 193       },
 194       include: [
 195         {
 196           model: AccountModel.scope(AccountScopeNames.SUMMARY),
 197           as: 'ReporterAccount',
 198           required: !!options.searchReporter,
 199           where: searchAttribute(options.searchReporter, 'name')
 200         },
 201         {
 202           model: AccountModel.scope({
 203             method: [
 204               AccountScopeNames.SUMMARY,
 205               { actorRequired: false } as AccountSummaryOptions
 206             ]
 207           }),
 208           as: 'FlaggedAccount',
 209           required: !!options.searchReportee,
 210           where: searchAttribute(options.searchReportee, 'name')
 211         },
 212         {
 213           model: VideoCommentAbuseModel.unscoped(),
 214           required: options.filter === 'comment',
 215           include: [
 216             {
 217               model: VideoCommentModel.unscoped(),
 218               required: false,
 219               include: [
 220                 {
 221                   model: VideoModel.unscoped(),
 222                   attributes: [ 'name', 'id', 'uuid' ]
 223                 }
 224               ]
 225             }
 226           ]
 227         },
 228         {
 229           model: VideoAbuseModel.unscoped(),
 230           required: options.filter === 'video' || !!options.videoIs || videoRequired,
 231           include: [
 232             {
 233               attributes: [ 'id', 'uuid', 'name', 'nsfw' ],
 234               model: VideoModel.unscoped(),
 235               required: videoRequired,
 236               where: searchAttribute(options.searchVideo, 'name'),
 237               include: [
 238                 {
 239                   attributes: [ 'filename', 'fileUrl' ],
 240                   model: ThumbnailModel
 241                 },
 242                 {
 243                   model: VideoChannelModel.scope({
 244                     method: [
 245                       VideoChannelScopeNames.SUMMARY,
 246                       { withAccount: false, actorRequired: false } as ChannelSummaryOptions
 247                     ]
 248                   }),
 249
 250                   where: searchAttribute(options.searchVideoChannel, 'name'),
 251                   required: !!options.searchVideoChannel
 252                 },
 253                 {
 254                   attributes: [ 'id', 'reason', 'unfederated' ],
 255                   model: VideoBlacklistModel,
 256                   required: onlyBlacklisted
 257                 }
 258               ]
 259             }
 260           ]
 261         }
 262       ],
 263       where: {
 264         [Op.and]: whereAnd
 265       }
 266     }
 267   }
 268 }))
 269 @Table({
 270   tableName: 'abuse',
 271   indexes: [
 272     {
 273       fields: [ 'reporterAccountId' ]
 274     },
 275     {
 276       fields: [ 'flaggedAccountId' ]
 277     }
 278   ]
 279 })
 280 export class AbuseModel extends Model<AbuseModel> {
 281
 282   @AllowNull(false)
 283   @Default(null)
 284   @Is('AbuseReason', value => throwIfNotValid(value, isAbuseReasonValid, 'reason'))
 285   @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.REASON.max))
 286   reason: string
 287
 288   @AllowNull(false)
 289   @Default(null)
 290   @Is('AbuseState', value => throwIfNotValid(value, isAbuseStateValid, 'state'))
 291   @Column
 292   state: AbuseState
 293
 294   @AllowNull(true)
 295   @Default(null)
 296   @Is('AbuseModerationComment', value => throwIfNotValid(value, isAbuseModerationCommentValid, 'moderationComment', true))
 297   @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.MODERATION_COMMENT.max))
 298   moderationComment: string
 299
 300   @AllowNull(true)
 301   @Default(null)
 302   @Column(DataType.ARRAY(DataType.INTEGER))
 303   predefinedReasons: AbusePredefinedReasons[]
 304
 305   @CreatedAt
 306   createdAt: Date
 307
 308   @UpdatedAt
 309   updatedAt: Date
 310
 311   @ForeignKey(() => AccountModel)
 312   @Column
 313   reporterAccountId: number
 314
 315   @BelongsTo(() => AccountModel, {
 316     foreignKey: {
 317       name: 'reporterAccountId',
 318       allowNull: true
 319     },
 320     as: 'ReporterAccount',
 321     onDelete: 'set null'
 322   })
 323   ReporterAccount: AccountModel
 324
 325   @ForeignKey(() => AccountModel)
 326   @Column
 327   flaggedAccountId: number
 328
 329   @BelongsTo(() => AccountModel, {
 330     foreignKey: {
 331       name: 'flaggedAccountId',
 332       allowNull: true
 333     },
 334     as: 'FlaggedAccount',
 335     onDelete: 'set null'
 336   })
 337   FlaggedAccount: AccountModel
 338
 339   @HasOne(() => VideoCommentAbuseModel, {
 340     foreignKey: {
 341       name: 'abuseId',
 342       allowNull: false
 343     },
 344     onDelete: 'cascade'
 345   })
 346   VideoCommentAbuse: VideoCommentAbuseModel
 347
 348   @HasOne(() => VideoAbuseModel, {
 349     foreignKey: {
 350       name: 'abuseId',
 351       allowNull: false
 352     },
 353     onDelete: 'cascade'
 354   })
 355   VideoAbuse: VideoAbuseModel
 356
 357   // FIXME: deprecated in 2.3. Remove these validators
 358   static loadByIdAndVideoId (id: number, videoId?: number, uuid?: string): Bluebird<MAbuse> {
 359     const videoWhere: WhereOptions = {}
 360
 361     if (videoId) videoWhere.videoId = videoId
 362     if (uuid) videoWhere.deletedVideo = { uuid }
 363
 364     const query = {
 365       include: [
 366         {
 367           model: VideoAbuseModel,
 368           required: true,
 369           where: videoWhere
 370         }
 371       ],
 372       where: {
 373         id
 374       }
 375     }
 376     return AbuseModel.findOne(query)
 377   }
 378
 379   static loadById (id: number): Bluebird<MAbuse> {
 380     const query = {
 381       where: {
 382         id
 383       }
 384     }
 385
 386     return AbuseModel.findOne(query)
 387   }
 388
 389   static listForApi (parameters: {
 390     start: number
 391     count: number
 392     sort: string
 393
 394     filter?: AbuseFilter
 395
 396     serverAccountId: number
 397     user?: MUserAccountId
 398
 399     id?: number
 400     predefinedReason?: AbusePredefinedReasonsString
 401     state?: AbuseState
 402     videoIs?: AbuseVideoIs
 403
 404     search?: string
 405     searchReporter?: string
 406     searchReportee?: string
 407     searchVideo?: string
 408     searchVideoChannel?: string
 409   }) {
 410     const {
 411       start,
 412       count,
 413       sort,
 414       search,
 415       user,
 416       serverAccountId,
 417       state,
 418       videoIs,
 419       predefinedReason,
 420       searchReportee,
 421       searchVideo,
 422       filter,
 423       searchVideoChannel,
 424       searchReporter,
 425       id
 426     } = parameters
 427
 428     const userAccountId = user ? user.Account.id : undefined
 429     const predefinedReasonId = predefinedReason ? abusePredefinedReasonsMap[predefinedReason] : undefined
 430
 431     const query = {
 432       offset: start,
 433       limit: count,
 434       order: getSort(sort),
 435       col: 'AbuseModel.id',
 436       distinct: true
 437     }
 438
 439     const filters = {
 440       id,
 441       filter,
 442       predefinedReasonId,
 443       search,
 444       state,
 445       videoIs,
 446       searchReportee,
 447       searchVideo,
 448       searchVideoChannel,
 449       searchReporter,
 450       serverAccountId,
 451       userAccountId
 452     }
 453
 454     return AbuseModel
 455       .scope([
 456         { method: [ ScopeNames.FOR_API, filters ] }
 457       ])
 458       .findAndCountAll(query)
 459       .then(({ rows, count }) => {
 460         return { total: count, data: rows }
 461       })
 462   }
 463
 464   toFormattedJSON (this: MAbuseFormattable): Abuse {
 465     const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 466
 467     const countReportsForVideo = this.get('countReportsForVideo') as number
 468     const nthReportForVideo = this.get('nthReportForVideo') as number
 469
 470     const countReportsForReporter = this.get('countReportsForReporter') as number
 471     const countReportsForReportee = this.get('countReportsForReportee') as number
 472
 473     let video: VideoAbuse
 474     let comment: VideoCommentAbuse
 475
 476     if (this.VideoAbuse) {
 477       const abuseModel = this.VideoAbuse
 478       const entity = abuseModel.Video || abuseModel.deletedVideo
 479
 480       video = {
 481         id: entity.id,
 482         uuid: entity.uuid,
 483         name: entity.name,
 484         nsfw: entity.nsfw,
 485
 486         startAt: abuseModel.startAt,
 487         endAt: abuseModel.endAt,
 488
 489         deleted: !abuseModel.Video,
 490         blacklisted: abuseModel.Video?.isBlacklisted() || false,
 491         thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
 492
 493         channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel,
 494
 495         countReports: countReportsForVideo,
 496         nthReport: nthReportForVideo
 497       }
 498     }
 499
 500     if (this.VideoCommentAbuse) {
 501       const abuseModel = this.VideoCommentAbuse
 502       const entity = abuseModel.VideoComment || abuseModel.deletedComment
 503
 504       comment = {
 505         id: entity.id,
 506         text: entity.text,
 507
 508         deleted: !abuseModel.VideoComment,
 509
 510         video: {
 511           id: entity.Video.id,
 512           name: entity.Video.name,
 513           uuid: entity.Video.uuid
 514         }
 515       }
 516     }
 517
 518     return {
 519       id: this.id,
 520       reason: this.reason,
 521       predefinedReasons,
 522
 523       reporterAccount: this.ReporterAccount
 524         ? this.ReporterAccount.toFormattedJSON()
 525         : null,
 526
 527       flaggedAccount: this.FlaggedAccount
 528         ? this.FlaggedAccount.toFormattedJSON()
 529         : null,
 530
 531       state: {
 532         id: this.state,
 533         label: AbuseModel.getStateLabel(this.state)
 534       },
 535
 536       moderationComment: this.moderationComment,
 537
 538       video,
 539       comment,
 540
 541       createdAt: this.createdAt,
 542       updatedAt: this.updatedAt,
 543
 544       countReportsForReporter: (countReportsForReporter || 0),
 545       countReportsForReportee: (countReportsForReportee || 0),
 546
 547       // FIXME: deprecated in 2.3, remove this
 548       startAt: null,
 549       endAt: null,
 550       count: countReportsForVideo || 0,
 551       nth: nthReportForVideo || 0
 552     }
 553   }
 554
 555   toActivityPubObject (this: MAbuseAP): AbuseObject {
 556     const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 557
 558     const object = this.VideoAbuse?.Video?.url || this.VideoCommentAbuse?.VideoComment?.url || this.FlaggedAccount.Actor.url
 559
 560     const startAt = this.VideoAbuse?.startAt
 561     const endAt = this.VideoAbuse?.endAt
 562
 563     return {
 564       type: 'Flag' as 'Flag',
 565       content: this.reason,
 566       object,
 567       tag: predefinedReasons.map(r => ({
 568         type: 'Hashtag' as 'Hashtag',
 569         name: r
 570       })),
 571       startAt,
 572       endAt
 573     }
 574   }
 575
 576   private static getStateLabel (id: number) {
 577     return ABUSE_STATES[id] || 'Unknown'
 578   }
 579
 580   private static getPredefinedReasonsStrings (predefinedReasons: AbusePredefinedReasons[]): AbusePredefinedReasonsString[] {
 581     return (predefinedReasons || [])
 582       .filter(r => r in AbusePredefinedReasons)
 583       .map(r => invert(abusePredefinedReasonsMap)[r] as AbusePredefinedReasonsString)
 584   }
 585 }