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 } 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, VideoChannelModel } from '../video/video-channel'
  40 import { VideoAbuseModel } from './video-abuse'
  41 import { VideoCommentAbuseModel } from './video-comment-abuse'
  42 import { VideoCommentModel } from '../video/video-comment'
  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" ' +
 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' +
 168               ')'
 169             ),
 170             'nthReportForVideo'
 171           ],
 172           [
 173             literal(
 174               '(' +
 175                 'SELECT count("videoAbuse"."id") ' +
 176                 'FROM "videoAbuse" ' +
 177                 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
 178                 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 179                 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
 180                 'WHERE "account"."id" = "AbuseModel"."reporterAccountId" ' +
 181               ')'
 182             ),
 183             'countReportsForReporter__video'
 184           ],
 185           [
 186             literal(
 187               '(' +
 188                 'SELECT count(DISTINCT "videoAbuse"."id") ' +
 189                 'FROM "videoAbuse" ' +
 190                 `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "AbuseModel"."reporterAccountId" ` +
 191               ')'
 192             ),
 193             'countReportsForReporter__deletedVideo'
 194           ],
 195           [
 196             literal(
 197               '(' +
 198                 'SELECT count(DISTINCT "videoAbuse"."id") ' +
 199                 'FROM "videoAbuse" ' +
 200                 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
 201                 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 202                 'INNER JOIN "account" ON ' +
 203                       '"videoChannel"."accountId" = "VideoAbuse->Video->VideoChannel"."accountId" ' +
 204                    `OR "videoChannel"."accountId" = CAST("VideoAbuse"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
 205               ')'
 206             ),
 207             'countReportsForReportee__video'
 208           ],
 209           [
 210             literal(
 211               '(' +
 212                 'SELECT count(DISTINCT "videoAbuse"."id") ' +
 213                 'FROM "videoAbuse" ' +
 214                 `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "VideoAbuse->Video->VideoChannel"."accountId" ` +
 215                    `OR CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = ` +
 216                       `CAST("VideoAbuse"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
 217               ')'
 218             ),
 219             'countReportsForReportee__deletedVideo'
 220           ]
 221         ]
 222       },
 223       include: [
 224         {
 225           model: AccountModel.scope(AccountScopeNames.SUMMARY),
 226           as: 'ReporterAccount',
 227           required: true,
 228           where: searchAttribute(options.searchReporter, 'name')
 229         },
 230         {
 231           model: AccountModel.scope(AccountScopeNames.SUMMARY),
 232           as: 'FlaggedAccount',
 233           required: true,
 234           where: searchAttribute(options.searchReportee, 'name')
 235         },
 236         {
 237           model: VideoCommentAbuseModel.unscoped(),
 238           required: options.filter === 'comment',
 239           include: [
 240             {
 241               model: VideoCommentModel.unscoped(),
 242               required: false,
 243               include: [
 244                 {
 245                   model: VideoModel.unscoped(),
 246                   attributes: [ 'name', 'id', 'uuid' ],
 247                   required: true
 248                 }
 249               ]
 250             }
 251           ]
 252         },
 253         {
 254           model: VideoAbuseModel,
 255           required: options.filter === 'video' || !!options.videoIs || videoRequired,
 256           include: [
 257             {
 258               model: VideoModel,
 259               required: videoRequired,
 260               where: searchAttribute(options.searchVideo, 'name'),
 261               include: [
 262                 {
 263                   model: ThumbnailModel
 264                 },
 265                 {
 266                   model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: false } as SummaryOptions ] }),
 267                   where: searchAttribute(options.searchVideoChannel, 'name'),
 268                   required: true,
 269                   include: [
 270                     {
 271                       model: AccountModel.scope(AccountScopeNames.SUMMARY),
 272                       required: true
 273                     }
 274                   ]
 275                 },
 276                 {
 277                   attributes: [ 'id', 'reason', 'unfederated' ],
 278                   model: VideoBlacklistModel,
 279                   required: onlyBlacklisted
 280                 }
 281               ]
 282             }
 283           ]
 284         }
 285       ],
 286       where: {
 287         [Op.and]: whereAnd
 288       }
 289     }
 290   }
 291 }))
 292 @Table({
 293   tableName: 'abuse',
 294   indexes: [
 295     {
 296       fields: [ 'reporterAccountId' ]
 297     },
 298     {
 299       fields: [ 'flaggedAccountId' ]
 300     }
 301   ]
 302 })
 303 export class AbuseModel extends Model<AbuseModel> {
 304
 305   @AllowNull(false)
 306   @Default(null)
 307   @Is('VideoAbuseReason', value => throwIfNotValid(value, isAbuseReasonValid, 'reason'))
 308   @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.REASON.max))
 309   reason: string
 310
 311   @AllowNull(false)
 312   @Default(null)
 313   @Is('VideoAbuseState', value => throwIfNotValid(value, isAbuseStateValid, 'state'))
 314   @Column
 315   state: AbuseState
 316
 317   @AllowNull(true)
 318   @Default(null)
 319   @Is('VideoAbuseModerationComment', value => throwIfNotValid(value, isAbuseModerationCommentValid, 'moderationComment', true))
 320   @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.MODERATION_COMMENT.max))
 321   moderationComment: string
 322
 323   @AllowNull(true)
 324   @Default(null)
 325   @Column(DataType.ARRAY(DataType.INTEGER))
 326   predefinedReasons: AbusePredefinedReasons[]
 327
 328   @CreatedAt
 329   createdAt: Date
 330
 331   @UpdatedAt
 332   updatedAt: Date
 333
 334   @ForeignKey(() => AccountModel)
 335   @Column
 336   reporterAccountId: number
 337
 338   @BelongsTo(() => AccountModel, {
 339     foreignKey: {
 340       name: 'reporterAccountId',
 341       allowNull: true
 342     },
 343     as: 'ReporterAccount',
 344     onDelete: 'set null'
 345   })
 346   ReporterAccount: AccountModel
 347
 348   @ForeignKey(() => AccountModel)
 349   @Column
 350   flaggedAccountId: number
 351
 352   @BelongsTo(() => AccountModel, {
 353     foreignKey: {
 354       name: 'flaggedAccountId',
 355       allowNull: true
 356     },
 357     as: 'FlaggedAccount',
 358     onDelete: 'set null'
 359   })
 360   FlaggedAccount: AccountModel
 361
 362   @HasOne(() => VideoCommentAbuseModel, {
 363     foreignKey: {
 364       name: 'abuseId',
 365       allowNull: false
 366     },
 367     onDelete: 'cascade'
 368   })
 369   VideoCommentAbuse: VideoCommentAbuseModel
 370
 371   @HasOne(() => VideoAbuseModel, {
 372     foreignKey: {
 373       name: 'abuseId',
 374       allowNull: false
 375     },
 376     onDelete: 'cascade'
 377   })
 378   VideoAbuse: VideoAbuseModel
 379
 380   // FIXME: deprecated in 2.3. Remove these validators
 381   static loadByIdAndVideoId (id: number, videoId?: number, uuid?: string): Bluebird<MAbuse> {
 382     const videoWhere: WhereOptions = {}
 383
 384     if (videoId) videoWhere.videoId = videoId
 385     if (uuid) videoWhere.deletedVideo = { uuid }
 386
 387     const query = {
 388       include: [
 389         {
 390           model: VideoAbuseModel,
 391           required: true,
 392           where: videoWhere
 393         }
 394       ],
 395       where: {
 396         id
 397       }
 398     }
 399     return AbuseModel.findOne(query)
 400   }
 401
 402   static loadById (id: number): Bluebird<MAbuse> {
 403     const query = {
 404       where: {
 405         id
 406       }
 407     }
 408
 409     return AbuseModel.findOne(query)
 410   }
 411
 412   static listForApi (parameters: {
 413     start: number
 414     count: number
 415     sort: string
 416
 417     filter?: AbuseFilter
 418
 419     serverAccountId: number
 420     user?: MUserAccountId
 421
 422     id?: number
 423     predefinedReason?: AbusePredefinedReasonsString
 424     state?: AbuseState
 425     videoIs?: AbuseVideoIs
 426
 427     search?: string
 428     searchReporter?: string
 429     searchReportee?: string
 430     searchVideo?: string
 431     searchVideoChannel?: string
 432   }) {
 433     const {
 434       start,
 435       count,
 436       sort,
 437       search,
 438       user,
 439       serverAccountId,
 440       state,
 441       videoIs,
 442       predefinedReason,
 443       searchReportee,
 444       searchVideo,
 445       filter,
 446       searchVideoChannel,
 447       searchReporter,
 448       id
 449     } = parameters
 450
 451     const userAccountId = user ? user.Account.id : undefined
 452     const predefinedReasonId = predefinedReason ? abusePredefinedReasonsMap[predefinedReason] : undefined
 453
 454     const query = {
 455       offset: start,
 456       limit: count,
 457       order: getSort(sort),
 458       col: 'AbuseModel.id',
 459       distinct: true
 460     }
 461
 462     const filters = {
 463       id,
 464       filter,
 465       predefinedReasonId,
 466       search,
 467       state,
 468       videoIs,
 469       searchReportee,
 470       searchVideo,
 471       searchVideoChannel,
 472       searchReporter,
 473       serverAccountId,
 474       userAccountId
 475     }
 476
 477     return AbuseModel
 478       .scope([
 479         { method: [ ScopeNames.FOR_API, filters ] }
 480       ])
 481       .findAndCountAll(query)
 482       .then(({ rows, count }) => {
 483         return { total: count, data: rows }
 484       })
 485   }
 486
 487   toFormattedJSON (this: MAbuseFormattable): Abuse {
 488     const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 489     const countReportsForVideo = this.get('countReportsForVideo') as number
 490     const nthReportForVideo = this.get('nthReportForVideo') as number
 491     const countReportsForReporterVideo = this.get('countReportsForReporter__video') as number
 492     const countReportsForReporterDeletedVideo = this.get('countReportsForReporter__deletedVideo') as number
 493     const countReportsForReporteeVideo = this.get('countReportsForReportee__video') as number
 494     const countReportsForReporteeDeletedVideo = this.get('countReportsForReportee__deletedVideo') as number
 495
 496     let video: VideoAbuse
 497     let comment: VideoCommentAbuse
 498
 499     if (this.VideoAbuse) {
 500       const abuseModel = this.VideoAbuse
 501       const entity = abuseModel.Video || abuseModel.deletedVideo
 502
 503       video = {
 504         id: entity.id,
 505         uuid: entity.uuid,
 506         name: entity.name,
 507         nsfw: entity.nsfw,
 508
 509         startAt: abuseModel.startAt,
 510         endAt: abuseModel.endAt,
 511
 512         deleted: !abuseModel.Video,
 513         blacklisted: abuseModel.Video?.isBlacklisted() || false,
 514         thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
 515         channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel
 516       }
 517     }
 518
 519     if (this.VideoCommentAbuse) {
 520       const abuseModel = this.VideoCommentAbuse
 521       const entity = abuseModel.VideoComment || abuseModel.deletedComment
 522
 523       comment = {
 524         id: entity.id,
 525         text: entity.text,
 526
 527         deleted: !abuseModel.VideoComment,
 528
 529         video: {
 530           id: entity.Video.id,
 531           name: entity.Video.name,
 532           uuid: entity.Video.uuid
 533         }
 534       }
 535     }
 536
 537     return {
 538       id: this.id,
 539       reason: this.reason,
 540       predefinedReasons,
 541
 542       reporterAccount: this.ReporterAccount.toFormattedJSON(),
 543
 544       state: {
 545         id: this.state,
 546         label: AbuseModel.getStateLabel(this.state)
 547       },
 548
 549       moderationComment: this.moderationComment,
 550
 551       video,
 552       comment,
 553
 554       createdAt: this.createdAt,
 555       updatedAt: this.updatedAt,
 556       count: countReportsForVideo || 0,
 557       nth: nthReportForVideo || 0,
 558       countReportsForReporter: (countReportsForReporterVideo || 0) + (countReportsForReporterDeletedVideo || 0),
 559       countReportsForReportee: (countReportsForReporteeVideo || 0) + (countReportsForReporteeDeletedVideo || 0),
 560
 561       // FIXME: deprecated in 2.3, remove this
 562       startAt: null,
 563       endAt: null
 564     }
 565   }
 566
 567   toActivityPubObject (this: MAbuseAP): AbuseObject {
 568     const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 569
 570     const object = this.VideoAbuse?.Video?.url || this.VideoCommentAbuse?.VideoComment?.url || this.FlaggedAccount.Actor.url
 571
 572     const startAt = this.VideoAbuse?.startAt
 573     const endAt = this.VideoAbuse?.endAt
 574
 575     return {
 576       type: 'Flag' as 'Flag',
 577       content: this.reason,
 578       object,
 579       tag: predefinedReasons.map(r => ({
 580         type: 'Hashtag' as 'Hashtag',
 581         name: r
 582       })),
 583       startAt,
 584       endAt
 585     }
 586   }
 587
 588   private static getStateLabel (id: number) {
 589     return ABUSE_STATES[id] || 'Unknown'
 590   }
 591
 592   private static getPredefinedReasonsStrings (predefinedReasons: AbusePredefinedReasons[]): AbusePredefinedReasonsString[] {
 593     return (predefinedReasons || [])
 594       .filter(r => r in AbusePredefinedReasons)
 595       .map(r => invert(abusePredefinedReasonsMap)[r] as AbusePredefinedReasonsString)
 596   }
 597 }