server/models/abuse/abuse.ts

   1 import { invert } from 'lodash'
   2 import { literal, Op, QueryTypes } from 'sequelize'
   3 import {
   4   AllowNull,
   5   BelongsTo,
   6   Column,
   7   CreatedAt,
   8   DataType,
   9   Default,
  10   ForeignKey,
  11   HasOne,
  12   Is,
  13   Model,
  14   Scopes,
  15   Table,
  16   UpdatedAt
  17 } from 'sequelize-typescript'
  18 import { isAbuseModerationCommentValid, isAbuseReasonValid, isAbuseStateValid } from '@server/helpers/custom-validators/abuses'
  19 import { AttributesOnly } from '@shared/core-utils'
  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<Partial<AttributesOnly<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   static loadByIdWithReporter (id: number): Promise<MAbuseReporter> {
 269     const query = {
 270       where: {
 271         id
 272       },
 273       include: [
 274         {
 275           model: AccountModel,
 276           as: 'ReporterAccount'
 277         }
 278       ]
 279     }
 280
 281     return AbuseModel.findOne(query)
 282   }
 283
 284   static loadFull (id: number): Promise<MAbuseFull> {
 285     const query = {
 286       where: {
 287         id
 288       },
 289       include: [
 290         {
 291           model: AccountModel.scope(AccountScopeNames.SUMMARY),
 292           required: false,
 293           as: 'ReporterAccount'
 294         },
 295         {
 296           model: AccountModel.scope(AccountScopeNames.SUMMARY),
 297           as: 'FlaggedAccount'
 298         },
 299         {
 300           model: VideoAbuseModel,
 301           required: false,
 302           include: [
 303             {
 304               model: VideoModel.scope([ VideoScopeNames.WITH_ACCOUNT_DETAILS ])
 305             }
 306           ]
 307         },
 308         {
 309           model: VideoCommentAbuseModel,
 310           required: false,
 311           include: [
 312             {
 313               model: VideoCommentModel.scope([
 314                 CommentScopeNames.WITH_ACCOUNT
 315               ]),
 316               include: [
 317                 {
 318                   model: VideoModel
 319                 }
 320               ]
 321             }
 322           ]
 323         }
 324       ]
 325     }
 326
 327     return AbuseModel.findOne(query)
 328   }
 329
 330   static async listForAdminApi (parameters: {
 331     start: number
 332     count: number
 333     sort: string
 334
 335     filter?: AbuseFilter
 336
 337     serverAccountId: number
 338     user?: MUserAccountId
 339
 340     id?: number
 341     predefinedReason?: AbusePredefinedReasonsString
 342     state?: AbuseState
 343     videoIs?: AbuseVideoIs
 344
 345     search?: string
 346     searchReporter?: string
 347     searchReportee?: string
 348     searchVideo?: string
 349     searchVideoChannel?: string
 350   }) {
 351     const {
 352       start,
 353       count,
 354       sort,
 355       search,
 356       user,
 357       serverAccountId,
 358       state,
 359       videoIs,
 360       predefinedReason,
 361       searchReportee,
 362       searchVideo,
 363       filter,
 364       searchVideoChannel,
 365       searchReporter,
 366       id
 367     } = parameters
 368
 369     const userAccountId = user ? user.Account.id : undefined
 370     const predefinedReasonId = predefinedReason ? abusePredefinedReasonsMap[predefinedReason] : undefined
 371
 372     const queryOptions: BuildAbusesQueryOptions = {
 373       start,
 374       count,
 375       sort,
 376       id,
 377       filter,
 378       predefinedReasonId,
 379       search,
 380       state,
 381       videoIs,
 382       searchReportee,
 383       searchVideo,
 384       searchVideoChannel,
 385       searchReporter,
 386       serverAccountId,
 387       userAccountId
 388     }
 389
 390     const [ total, data ] = await Promise.all([
 391       AbuseModel.internalCountForApi(queryOptions),
 392       AbuseModel.internalListForApi(queryOptions)
 393     ])
 394
 395     return { total, data }
 396   }
 397
 398   static async listForUserApi (parameters: {
 399     user: MUserAccountId
 400
 401     start: number
 402     count: number
 403     sort: string
 404
 405     id?: number
 406     search?: string
 407     state?: AbuseState
 408   }) {
 409     const {
 410       start,
 411       count,
 412       sort,
 413       search,
 414       user,
 415       state,
 416       id
 417     } = parameters
 418
 419     const queryOptions: BuildAbusesQueryOptions = {
 420       start,
 421       count,
 422       sort,
 423       id,
 424       search,
 425       state,
 426       reporterAccountId: user.Account.id
 427     }
 428
 429     const [ total, data ] = await Promise.all([
 430       AbuseModel.internalCountForApi(queryOptions),
 431       AbuseModel.internalListForApi(queryOptions)
 432     ])
 433
 434     return { total, data }
 435   }
 436
 437   buildBaseVideoCommentAbuse (this: MAbuseUserFormattable) {
 438     // Associated video comment could have been destroyed if the video has been deleted
 439     if (!this.VideoCommentAbuse || !this.VideoCommentAbuse.VideoComment) return null
 440
 441     const entity = this.VideoCommentAbuse.VideoComment
 442
 443     return {
 444       id: entity.id,
 445       threadId: entity.getThreadId(),
 446
 447       text: entity.text ?? '',
 448
 449       deleted: entity.isDeleted(),
 450
 451       video: {
 452         id: entity.Video.id,
 453         name: entity.Video.name,
 454         uuid: entity.Video.uuid
 455       }
 456     }
 457   }
 458
 459   buildBaseVideoAbuse (this: MAbuseUserFormattable): UserVideoAbuse {
 460     if (!this.VideoAbuse) return null
 461
 462     const abuseModel = this.VideoAbuse
 463     const entity = abuseModel.Video || abuseModel.deletedVideo
 464
 465     return {
 466       id: entity.id,
 467       uuid: entity.uuid,
 468       name: entity.name,
 469       nsfw: entity.nsfw,
 470
 471       startAt: abuseModel.startAt,
 472       endAt: abuseModel.endAt,
 473
 474       deleted: !abuseModel.Video,
 475       blacklisted: abuseModel.Video?.isBlacklisted() || false,
 476       thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
 477
 478       channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel
 479     }
 480   }
 481
 482   buildBaseAbuse (this: MAbuseUserFormattable, countMessages: number): UserAbuse {
 483     const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 484
 485     return {
 486       id: this.id,
 487       reason: this.reason,
 488       predefinedReasons,
 489
 490       flaggedAccount: this.FlaggedAccount
 491         ? this.FlaggedAccount.toFormattedJSON()
 492         : null,
 493
 494       state: {
 495         id: this.state,
 496         label: AbuseModel.getStateLabel(this.state)
 497       },
 498
 499       countMessages,
 500
 501       createdAt: this.createdAt,
 502       updatedAt: this.updatedAt
 503     }
 504   }
 505
 506   toFormattedAdminJSON (this: MAbuseAdminFormattable): AdminAbuse {
 507     const countReportsForVideo = this.get('countReportsForVideo') as number
 508     const nthReportForVideo = this.get('nthReportForVideo') as number
 509
 510     const countReportsForReporter = this.get('countReportsForReporter') as number
 511     const countReportsForReportee = this.get('countReportsForReportee') as number
 512
 513     const countMessages = this.get('countMessages') as number
 514
 515     const baseVideo = this.buildBaseVideoAbuse()
 516     const video: AdminVideoAbuse = baseVideo
 517       ? Object.assign(baseVideo, {
 518         countReports: countReportsForVideo,
 519         nthReport: nthReportForVideo
 520       })
 521       : null
 522
 523     const comment: AdminVideoCommentAbuse = this.buildBaseVideoCommentAbuse()
 524
 525     const abuse = this.buildBaseAbuse(countMessages || 0)
 526
 527     return Object.assign(abuse, {
 528       video,
 529       comment,
 530
 531       moderationComment: this.moderationComment,
 532
 533       reporterAccount: this.ReporterAccount
 534         ? this.ReporterAccount.toFormattedJSON()
 535         : null,
 536
 537       countReportsForReporter: (countReportsForReporter || 0),
 538       countReportsForReportee: (countReportsForReportee || 0)
 539     })
 540   }
 541
 542   toFormattedUserJSON (this: MAbuseUserFormattable): UserAbuse {
 543     const countMessages = this.get('countMessages') as number
 544
 545     const video = this.buildBaseVideoAbuse()
 546     const comment = this.buildBaseVideoCommentAbuse()
 547     const abuse = this.buildBaseAbuse(countMessages || 0)
 548
 549     return Object.assign(abuse, {
 550       video,
 551       comment
 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 async internalCountForApi (parameters: BuildAbusesQueryOptions) {
 577     const { query, replacements } = buildAbuseListQuery(parameters, 'count')
 578     const options = {
 579       type: QueryTypes.SELECT as QueryTypes.SELECT,
 580       replacements
 581     }
 582
 583     const [ { total } ] = await AbuseModel.sequelize.query<{ total: string }>(query, options)
 584     if (total === null) return 0
 585
 586     return parseInt(total, 10)
 587   }
 588
 589   private static async internalListForApi (parameters: BuildAbusesQueryOptions) {
 590     const { query, replacements } = buildAbuseListQuery(parameters, 'id')
 591     const options = {
 592       type: QueryTypes.SELECT as QueryTypes.SELECT,
 593       replacements
 594     }
 595
 596     const rows = await AbuseModel.sequelize.query<{ id: string }>(query, options)
 597     const ids = rows.map(r => r.id)
 598
 599     if (ids.length === 0) return []
 600
 601     return AbuseModel.scope(ScopeNames.FOR_API)
 602                      .findAll({
 603                        order: getSort(parameters.sort),
 604                        where: {
 605                          id: {
 606                            [Op.in]: ids
 607                          }
 608                        }
 609                      })
 610   }
 611
 612   private static getStateLabel (id: number) {
 613     return ABUSE_STATES[id] || 'Unknown'
 614   }
 615
 616   private static getPredefinedReasonsStrings (predefinedReasons: AbusePredefinedReasons[]): AbusePredefinedReasonsString[] {
 617     const invertedPredefinedReasons = invert(abusePredefinedReasonsMap)
 618
 619     return (predefinedReasons || [])
 620       .map(r => invertedPredefinedReasons[r] as AbusePredefinedReasonsString)
 621       .filter(v => !!v)
 622   }
 623 }