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 {
  21   AbuseFilter,
  22   AbuseObject,
  23   AbusePredefinedReasons,
  24   abusePredefinedReasonsMap,
  25   AbusePredefinedReasonsString,
  26   AbuseState,
  27   AbuseVideoIs,
  28   AdminVideoAbuse,
  29   AdminAbuse,
  30   AdminVideoCommentAbuse,
  31   UserAbuse,
  32   UserVideoAbuse
  33 } from '@shared/models'
  34 import { ABUSE_STATES, CONSTRAINTS_FIELDS } from '../../initializers/constants'
  35 import { MAbuse, MAbuseAdminFormattable, MAbuseAP, MUserAccountId, MAbuseUserFormattable } 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 { 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 { 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<MAbuse> {
 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       where: {
 284         id
 285       }
 286     }
 287     return AbuseModel.findOne(query)
 288   }
 289
 290   static loadById (id: number): Bluebird<MAbuse> {
 291     const query = {
 292       where: {
 293         id
 294       }
 295     }
 296
 297     return AbuseModel.findOne(query)
 298   }
 299
 300   static async listForAdminApi (parameters: {
 301     start: number
 302     count: number
 303     sort: string
 304
 305     filter?: AbuseFilter
 306
 307     serverAccountId: number
 308     user?: MUserAccountId
 309
 310     id?: number
 311     predefinedReason?: AbusePredefinedReasonsString
 312     state?: AbuseState
 313     videoIs?: AbuseVideoIs
 314
 315     search?: string
 316     searchReporter?: string
 317     searchReportee?: string
 318     searchVideo?: string
 319     searchVideoChannel?: string
 320   }) {
 321     const {
 322       start,
 323       count,
 324       sort,
 325       search,
 326       user,
 327       serverAccountId,
 328       state,
 329       videoIs,
 330       predefinedReason,
 331       searchReportee,
 332       searchVideo,
 333       filter,
 334       searchVideoChannel,
 335       searchReporter,
 336       id
 337     } = parameters
 338
 339     const userAccountId = user ? user.Account.id : undefined
 340     const predefinedReasonId = predefinedReason ? abusePredefinedReasonsMap[predefinedReason] : undefined
 341
 342     const queryOptions: BuildAbusesQueryOptions = {
 343       start,
 344       count,
 345       sort,
 346       id,
 347       filter,
 348       predefinedReasonId,
 349       search,
 350       state,
 351       videoIs,
 352       searchReportee,
 353       searchVideo,
 354       searchVideoChannel,
 355       searchReporter,
 356       serverAccountId,
 357       userAccountId
 358     }
 359
 360     const [ total, data ] = await Promise.all([
 361       AbuseModel.internalCountForApi(queryOptions),
 362       AbuseModel.internalListForApi(queryOptions)
 363     ])
 364
 365     return { total, data }
 366   }
 367
 368   static async listForUserApi (parameters: {
 369     user: MUserAccountId
 370
 371     start: number
 372     count: number
 373     sort: string
 374
 375     id?: number
 376     search?: string
 377     state?: AbuseState
 378   }) {
 379     const {
 380       start,
 381       count,
 382       sort,
 383       search,
 384       user,
 385       state,
 386       id
 387     } = parameters
 388
 389     const queryOptions: BuildAbusesQueryOptions = {
 390       start,
 391       count,
 392       sort,
 393       id,
 394       search,
 395       state,
 396       reporterAccountId: user.Account.id
 397     }
 398
 399     const [ total, data ] = await Promise.all([
 400       AbuseModel.internalCountForApi(queryOptions),
 401       AbuseModel.internalListForApi(queryOptions)
 402     ])
 403
 404     return { total, data }
 405   }
 406
 407   buildBaseVideoCommentAbuse (this: MAbuseUserFormattable) {
 408     if (!this.VideoCommentAbuse) return null
 409
 410     const abuseModel = this.VideoCommentAbuse
 411     const entity = abuseModel.VideoComment
 412
 413     return {
 414       id: entity.id,
 415       threadId: entity.getThreadId(),
 416
 417       text: entity.text ?? '',
 418
 419       deleted: entity.isDeleted(),
 420
 421       video: {
 422         id: entity.Video.id,
 423         name: entity.Video.name,
 424         uuid: entity.Video.uuid
 425       }
 426     }
 427   }
 428
 429   buildBaseVideoAbuse (this: MAbuseUserFormattable): UserVideoAbuse {
 430     if (!this.VideoAbuse) return null
 431
 432     const abuseModel = this.VideoAbuse
 433     const entity = abuseModel.Video || abuseModel.deletedVideo
 434
 435     return {
 436       id: entity.id,
 437       uuid: entity.uuid,
 438       name: entity.name,
 439       nsfw: entity.nsfw,
 440
 441       startAt: abuseModel.startAt,
 442       endAt: abuseModel.endAt,
 443
 444       deleted: !abuseModel.Video,
 445       blacklisted: abuseModel.Video?.isBlacklisted() || false,
 446       thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
 447
 448       channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel,
 449     }
 450   }
 451
 452   buildBaseAbuse (this: MAbuseUserFormattable, countMessages: number): UserAbuse {
 453     const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 454
 455     return {
 456       id: this.id,
 457       reason: this.reason,
 458       predefinedReasons,
 459
 460       flaggedAccount: this.FlaggedAccount
 461         ? this.FlaggedAccount.toFormattedJSON()
 462         : null,
 463
 464       state: {
 465         id: this.state,
 466         label: AbuseModel.getStateLabel(this.state)
 467       },
 468
 469       moderationComment: this.moderationComment,
 470
 471       countMessages,
 472
 473       createdAt: this.createdAt,
 474       updatedAt: this.updatedAt
 475     }
 476   }
 477
 478   toFormattedAdminJSON (this: MAbuseAdminFormattable): AdminAbuse {
 479     const countReportsForVideo = this.get('countReportsForVideo') as number
 480     const nthReportForVideo = this.get('nthReportForVideo') as number
 481
 482     const countReportsForReporter = this.get('countReportsForReporter') as number
 483     const countReportsForReportee = this.get('countReportsForReportee') as number
 484
 485     const countMessages = this.get('countMessages') as number
 486
 487     const baseVideo = this.buildBaseVideoAbuse()
 488     const video: AdminVideoAbuse = baseVideo
 489       ? Object.assign(baseVideo, {
 490         countReports: countReportsForVideo,
 491         nthReport: nthReportForVideo
 492       })
 493       : null
 494
 495     const comment: AdminVideoCommentAbuse = this.buildBaseVideoCommentAbuse()
 496
 497     const abuse = this.buildBaseAbuse(countMessages || 0)
 498
 499     return Object.assign(abuse, {
 500       video,
 501       comment,
 502
 503       reporterAccount: this.ReporterAccount
 504         ? this.ReporterAccount.toFormattedJSON()
 505         : null,
 506
 507       countReportsForReporter: (countReportsForReporter || 0),
 508       countReportsForReportee: (countReportsForReportee || 0),
 509
 510       // FIXME: deprecated in 2.3, remove this
 511       startAt: null,
 512       endAt: null,
 513       count: countReportsForVideo || 0,
 514       nth: nthReportForVideo || 0
 515     })
 516   }
 517
 518   toFormattedUserJSON (this: MAbuseUserFormattable): UserAbuse {
 519     const countMessages = this.get('countMessages') as number
 520
 521     const video = this.buildBaseVideoAbuse()
 522     const comment: AdminVideoCommentAbuse = this.buildBaseVideoCommentAbuse()
 523     const abuse = this.buildBaseAbuse(countMessages || 0)
 524
 525     return Object.assign(abuse, {
 526       video,
 527       comment
 528     })
 529   }
 530
 531   toActivityPubObject (this: MAbuseAP): AbuseObject {
 532     const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 533
 534     const object = this.VideoAbuse?.Video?.url || this.VideoCommentAbuse?.VideoComment?.url || this.FlaggedAccount.Actor.url
 535
 536     const startAt = this.VideoAbuse?.startAt
 537     const endAt = this.VideoAbuse?.endAt
 538
 539     return {
 540       type: 'Flag' as 'Flag',
 541       content: this.reason,
 542       object,
 543       tag: predefinedReasons.map(r => ({
 544         type: 'Hashtag' as 'Hashtag',
 545         name: r
 546       })),
 547       startAt,
 548       endAt
 549     }
 550   }
 551
 552   private static async internalCountForApi (parameters: BuildAbusesQueryOptions) {
 553     const { query, replacements } = buildAbuseListQuery(parameters, 'count')
 554     const options = {
 555       type: QueryTypes.SELECT as QueryTypes.SELECT,
 556       replacements
 557     }
 558
 559     const [ { total } ] = await AbuseModel.sequelize.query<{ total: string }>(query, options)
 560     if (total === null) return 0
 561
 562     return parseInt(total, 10)
 563   }
 564
 565   private static async internalListForApi (parameters: BuildAbusesQueryOptions) {
 566     const { query, replacements } = buildAbuseListQuery(parameters, 'id')
 567     const options = {
 568       type: QueryTypes.SELECT as QueryTypes.SELECT,
 569       replacements
 570     }
 571
 572     const rows = await AbuseModel.sequelize.query<{ id: string }>(query, options)
 573     const ids = rows.map(r => r.id)
 574
 575     if (ids.length === 0) return []
 576
 577     return AbuseModel.scope(ScopeNames.FOR_API)
 578                      .findAll({
 579                        order: getSort(parameters.sort),
 580                        where: {
 581                          id: {
 582                            [Op.in]: ids
 583                          }
 584                        }
 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 }