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   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, SummaryOptions as AccountSummaryOptions } from '../account/account'
  35 import { getSort, 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 as ChannelSummaryOptions, VideoChannelModel } from '../video/video-channel'
  40 import { VideoCommentModel } from '../video/video-comment'
  41 import { buildAbuseListQuery, BuildAbusesQueryOptions } from './abuse-query-builder'
  42 import { VideoAbuseModel } from './video-abuse'
  43 import { VideoCommentAbuseModel } from './video-comment-abuse'
  44
  45 export enum ScopeNames {
  46   FOR_API = 'FOR_API'
  47 }
  48
  49 @Scopes(() => ({
  50   [ScopeNames.FOR_API]: () => {
  51     return {
  52       attributes: {
  53         include: [
  54           [
  55             // we don't care about this count for deleted videos, so there are not included
  56             literal(
  57               '(' +
  58                 'SELECT count(*) ' +
  59                 'FROM "videoAbuse" ' +
  60                 'WHERE "videoId" = "VideoAbuse"."videoId" AND "videoId" IS NOT NULL' +
  61               ')'
  62             ),
  63             'countReportsForVideo'
  64           ],
  65           [
  66             // we don't care about this count for deleted videos, so there are not included
  67             literal(
  68               '(' +
  69                 'SELECT t.nth ' +
  70                 'FROM ( ' +
  71                   'SELECT id, ' +
  72                          'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
  73                   'FROM "videoAbuse" ' +
  74                 ') t ' +
  75                 'WHERE t.id = "VideoAbuse".id AND t.id IS NOT NULL' +
  76               ')'
  77             ),
  78             'nthReportForVideo'
  79           ],
  80           [
  81             literal(
  82               '(' +
  83                 'SELECT count("abuse"."id") ' +
  84                 'FROM "abuse" ' +
  85                 'WHERE "abuse"."reporterAccountId" = "AbuseModel"."reporterAccountId"' +
  86               ')'
  87             ),
  88             'countReportsForReporter'
  89           ],
  90           [
  91             literal(
  92               '(' +
  93                 'SELECT count("abuse"."id") ' +
  94                 'FROM "abuse" ' +
  95                 'WHERE "abuse"."flaggedAccountId" = "AbuseModel"."flaggedAccountId"' +
  96               ')'
  97             ),
  98             'countReportsForReportee'
  99           ]
 100         ]
 101       },
 102       include: [
 103         {
 104           model: AccountModel.scope({
 105             method: [
 106               AccountScopeNames.SUMMARY,
 107               { actorRequired: false } as AccountSummaryOptions
 108             ]
 109           }),
 110           as: 'ReporterAccount'
 111         },
 112         {
 113           model: AccountModel.scope({
 114             method: [
 115               AccountScopeNames.SUMMARY,
 116               { actorRequired: false } as AccountSummaryOptions
 117             ]
 118           }),
 119           as: 'FlaggedAccount'
 120         },
 121         {
 122           model: VideoCommentAbuseModel.unscoped(),
 123           include: [
 124             {
 125               model: VideoCommentModel.unscoped(),
 126               include: [
 127                 {
 128                   model: VideoModel.unscoped(),
 129                   attributes: [ 'name', 'id', 'uuid' ]
 130                 }
 131               ]
 132             }
 133           ]
 134         },
 135         {
 136           model: VideoAbuseModel.unscoped(),
 137           include: [
 138             {
 139               attributes: [ 'id', 'uuid', 'name', 'nsfw' ],
 140               model: VideoModel.unscoped(),
 141               include: [
 142                 {
 143                   attributes: [ 'filename', 'fileUrl' ],
 144                   model: ThumbnailModel
 145                 },
 146                 {
 147                   model: VideoChannelModel.scope({
 148                     method: [
 149                       VideoChannelScopeNames.SUMMARY,
 150                       { withAccount: false, actorRequired: false } as ChannelSummaryOptions
 151                     ]
 152                   }),
 153                   required: false
 154                 },
 155                 {
 156                   attributes: [ 'id', 'reason', 'unfederated' ],
 157                   required: false,
 158                   model: VideoBlacklistModel
 159                 }
 160               ]
 161             }
 162           ]
 163         }
 164       ]
 165     }
 166   }
 167 }))
 168 @Table({
 169   tableName: 'abuse',
 170   indexes: [
 171     {
 172       fields: [ 'reporterAccountId' ]
 173     },
 174     {
 175       fields: [ 'flaggedAccountId' ]
 176     }
 177   ]
 178 })
 179 export class AbuseModel extends Model<AbuseModel> {
 180
 181   @AllowNull(false)
 182   @Default(null)
 183   @Is('AbuseReason', value => throwIfNotValid(value, isAbuseReasonValid, 'reason'))
 184   @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.REASON.max))
 185   reason: string
 186
 187   @AllowNull(false)
 188   @Default(null)
 189   @Is('AbuseState', value => throwIfNotValid(value, isAbuseStateValid, 'state'))
 190   @Column
 191   state: AbuseState
 192
 193   @AllowNull(true)
 194   @Default(null)
 195   @Is('AbuseModerationComment', value => throwIfNotValid(value, isAbuseModerationCommentValid, 'moderationComment', true))
 196   @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.MODERATION_COMMENT.max))
 197   moderationComment: string
 198
 199   @AllowNull(true)
 200   @Default(null)
 201   @Column(DataType.ARRAY(DataType.INTEGER))
 202   predefinedReasons: AbusePredefinedReasons[]
 203
 204   @CreatedAt
 205   createdAt: Date
 206
 207   @UpdatedAt
 208   updatedAt: Date
 209
 210   @ForeignKey(() => AccountModel)
 211   @Column
 212   reporterAccountId: number
 213
 214   @BelongsTo(() => AccountModel, {
 215     foreignKey: {
 216       name: 'reporterAccountId',
 217       allowNull: true
 218     },
 219     as: 'ReporterAccount',
 220     onDelete: 'set null'
 221   })
 222   ReporterAccount: AccountModel
 223
 224   @ForeignKey(() => AccountModel)
 225   @Column
 226   flaggedAccountId: number
 227
 228   @BelongsTo(() => AccountModel, {
 229     foreignKey: {
 230       name: 'flaggedAccountId',
 231       allowNull: true
 232     },
 233     as: 'FlaggedAccount',
 234     onDelete: 'set null'
 235   })
 236   FlaggedAccount: AccountModel
 237
 238   @HasOne(() => VideoCommentAbuseModel, {
 239     foreignKey: {
 240       name: 'abuseId',
 241       allowNull: false
 242     },
 243     onDelete: 'cascade'
 244   })
 245   VideoCommentAbuse: VideoCommentAbuseModel
 246
 247   @HasOne(() => VideoAbuseModel, {
 248     foreignKey: {
 249       name: 'abuseId',
 250       allowNull: false
 251     },
 252     onDelete: 'cascade'
 253   })
 254   VideoAbuse: VideoAbuseModel
 255
 256   // FIXME: deprecated in 2.3. Remove these validators
 257   static loadByIdAndVideoId (id: number, videoId?: number, uuid?: string): Bluebird<MAbuse> {
 258     const videoWhere: WhereOptions = {}
 259
 260     if (videoId) videoWhere.videoId = videoId
 261     if (uuid) videoWhere.deletedVideo = { uuid }
 262
 263     const query = {
 264       include: [
 265         {
 266           model: VideoAbuseModel,
 267           required: true,
 268           where: videoWhere
 269         }
 270       ],
 271       where: {
 272         id
 273       }
 274     }
 275     return AbuseModel.findOne(query)
 276   }
 277
 278   static loadById (id: number): Bluebird<MAbuse> {
 279     const query = {
 280       where: {
 281         id
 282       }
 283     }
 284
 285     return AbuseModel.findOne(query)
 286   }
 287
 288   static async listForApi (parameters: {
 289     start: number
 290     count: number
 291     sort: string
 292
 293     filter?: AbuseFilter
 294
 295     serverAccountId: number
 296     user?: MUserAccountId
 297
 298     id?: number
 299     predefinedReason?: AbusePredefinedReasonsString
 300     state?: AbuseState
 301     videoIs?: AbuseVideoIs
 302
 303     search?: string
 304     searchReporter?: string
 305     searchReportee?: string
 306     searchVideo?: string
 307     searchVideoChannel?: string
 308   }) {
 309     const {
 310       start,
 311       count,
 312       sort,
 313       search,
 314       user,
 315       serverAccountId,
 316       state,
 317       videoIs,
 318       predefinedReason,
 319       searchReportee,
 320       searchVideo,
 321       filter,
 322       searchVideoChannel,
 323       searchReporter,
 324       id
 325     } = parameters
 326
 327     const userAccountId = user ? user.Account.id : undefined
 328     const predefinedReasonId = predefinedReason ? abusePredefinedReasonsMap[predefinedReason] : undefined
 329
 330     const queryOptions: BuildAbusesQueryOptions = {
 331       start,
 332       count,
 333       sort,
 334       id,
 335       filter,
 336       predefinedReasonId,
 337       search,
 338       state,
 339       videoIs,
 340       searchReportee,
 341       searchVideo,
 342       searchVideoChannel,
 343       searchReporter,
 344       serverAccountId,
 345       userAccountId
 346     }
 347
 348     const [ total, data ] = await Promise.all([
 349       AbuseModel.internalCountForApi(queryOptions),
 350       AbuseModel.internalListForApi(queryOptions)
 351     ])
 352
 353     return { total, data }
 354   }
 355
 356   toFormattedJSON (this: MAbuseFormattable): Abuse {
 357     const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 358
 359     const countReportsForVideo = this.get('countReportsForVideo') as number
 360     const nthReportForVideo = this.get('nthReportForVideo') as number
 361
 362     const countReportsForReporter = this.get('countReportsForReporter') as number
 363     const countReportsForReportee = this.get('countReportsForReportee') as number
 364
 365     let video: VideoAbuse = null
 366     let comment: VideoCommentAbuse = null
 367
 368     if (this.VideoAbuse) {
 369       const abuseModel = this.VideoAbuse
 370       const entity = abuseModel.Video || abuseModel.deletedVideo
 371
 372       video = {
 373         id: entity.id,
 374         uuid: entity.uuid,
 375         name: entity.name,
 376         nsfw: entity.nsfw,
 377
 378         startAt: abuseModel.startAt,
 379         endAt: abuseModel.endAt,
 380
 381         deleted: !abuseModel.Video,
 382         blacklisted: abuseModel.Video?.isBlacklisted() || false,
 383         thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
 384
 385         channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel,
 386
 387         countReports: countReportsForVideo,
 388         nthReport: nthReportForVideo
 389       }
 390     }
 391
 392     if (this.VideoCommentAbuse) {
 393       const abuseModel = this.VideoCommentAbuse
 394       const entity = abuseModel.VideoComment
 395
 396       comment = {
 397         id: entity.id,
 398         text: entity.text ?? '',
 399
 400         deleted: entity.isDeleted(),
 401
 402         video: {
 403           id: entity.Video.id,
 404           name: entity.Video.name,
 405           uuid: entity.Video.uuid
 406         }
 407       }
 408     }
 409
 410     return {
 411       id: this.id,
 412       reason: this.reason,
 413       predefinedReasons,
 414
 415       reporterAccount: this.ReporterAccount
 416         ? this.ReporterAccount.toFormattedJSON()
 417         : null,
 418
 419       flaggedAccount: this.FlaggedAccount
 420         ? this.FlaggedAccount.toFormattedJSON()
 421         : null,
 422
 423       state: {
 424         id: this.state,
 425         label: AbuseModel.getStateLabel(this.state)
 426       },
 427
 428       moderationComment: this.moderationComment,
 429
 430       video,
 431       comment,
 432
 433       createdAt: this.createdAt,
 434       updatedAt: this.updatedAt,
 435
 436       countReportsForReporter: (countReportsForReporter || 0),
 437       countReportsForReportee: (countReportsForReportee || 0),
 438
 439       // FIXME: deprecated in 2.3, remove this
 440       startAt: null,
 441       endAt: null,
 442       count: countReportsForVideo || 0,
 443       nth: nthReportForVideo || 0
 444     }
 445   }
 446
 447   toActivityPubObject (this: MAbuseAP): AbuseObject {
 448     const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 449
 450     const object = this.VideoAbuse?.Video?.url || this.VideoCommentAbuse?.VideoComment?.url || this.FlaggedAccount.Actor.url
 451
 452     const startAt = this.VideoAbuse?.startAt
 453     const endAt = this.VideoAbuse?.endAt
 454
 455     return {
 456       type: 'Flag' as 'Flag',
 457       content: this.reason,
 458       object,
 459       tag: predefinedReasons.map(r => ({
 460         type: 'Hashtag' as 'Hashtag',
 461         name: r
 462       })),
 463       startAt,
 464       endAt
 465     }
 466   }
 467
 468   private static async internalCountForApi (parameters: BuildAbusesQueryOptions) {
 469     const { query, replacements } = buildAbuseListQuery(parameters, 'count')
 470     const options = {
 471       type: QueryTypes.SELECT as QueryTypes.SELECT,
 472       replacements
 473     }
 474
 475     const [ { total } ] = await AbuseModel.sequelize.query<{ total: string }>(query, options)
 476     if (total === null) return 0
 477
 478     return parseInt(total, 10)
 479   }
 480
 481   private static async internalListForApi (parameters: BuildAbusesQueryOptions) {
 482     const { query, replacements } = buildAbuseListQuery(parameters, 'id')
 483     const options = {
 484       type: QueryTypes.SELECT as QueryTypes.SELECT,
 485       replacements
 486     }
 487
 488     const rows = await AbuseModel.sequelize.query<{ id: string }>(query, options)
 489     const ids = rows.map(r => r.id)
 490
 491     if (ids.length === 0) return []
 492
 493     return AbuseModel.scope(ScopeNames.FOR_API)
 494                      .findAll({
 495                        order: getSort(parameters.sort),
 496                        where: {
 497                          id: {
 498                            [Op.in]: ids
 499                          }
 500                        }
 501                      })
 502   }
 503
 504   private static getStateLabel (id: number) {
 505     return ABUSE_STATES[id] || 'Unknown'
 506   }
 507
 508   private static getPredefinedReasonsStrings (predefinedReasons: AbusePredefinedReasons[]): AbusePredefinedReasonsString[] {
 509     return (predefinedReasons || [])
 510       .filter(r => r in AbusePredefinedReasons)
 511       .map(r => invert(abusePredefinedReasonsMap)[r] as AbusePredefinedReasonsString)
 512   }
 513 }