server/models/abuse/abuse.ts

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