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