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', 'type' ],
 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         threadId: entity.getThreadId(),
 399
 400         text: entity.text ?? '',
 401
 402         deleted: entity.isDeleted(),
 403
 404         video: {
 405           id: entity.Video.id,
 406           name: entity.Video.name,
 407           uuid: entity.Video.uuid
 408         }
 409       }
 410     }
 411
 412     return {
 413       id: this.id,
 414       reason: this.reason,
 415       predefinedReasons,
 416
 417       reporterAccount: this.ReporterAccount
 418         ? this.ReporterAccount.toFormattedJSON()
 419         : null,
 420
 421       flaggedAccount: this.FlaggedAccount
 422         ? this.FlaggedAccount.toFormattedJSON()
 423         : null,
 424
 425       state: {
 426         id: this.state,
 427         label: AbuseModel.getStateLabel(this.state)
 428       },
 429
 430       moderationComment: this.moderationComment,
 431
 432       video,
 433       comment,
 434
 435       createdAt: this.createdAt,
 436       updatedAt: this.updatedAt,
 437
 438       countReportsForReporter: (countReportsForReporter || 0),
 439       countReportsForReportee: (countReportsForReportee || 0),
 440
 441       // FIXME: deprecated in 2.3, remove this
 442       startAt: null,
 443       endAt: null,
 444       count: countReportsForVideo || 0,
 445       nth: nthReportForVideo || 0
 446     }
 447   }
 448
 449   toActivityPubObject (this: MAbuseAP): AbuseObject {
 450     const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 451
 452     const object = this.VideoAbuse?.Video?.url || this.VideoCommentAbuse?.VideoComment?.url || this.FlaggedAccount.Actor.url
 453
 454     const startAt = this.VideoAbuse?.startAt
 455     const endAt = this.VideoAbuse?.endAt
 456
 457     return {
 458       type: 'Flag' as 'Flag',
 459       content: this.reason,
 460       object,
 461       tag: predefinedReasons.map(r => ({
 462         type: 'Hashtag' as 'Hashtag',
 463         name: r
 464       })),
 465       startAt,
 466       endAt
 467     }
 468   }
 469
 470   private static async internalCountForApi (parameters: BuildAbusesQueryOptions) {
 471     const { query, replacements } = buildAbuseListQuery(parameters, 'count')
 472     const options = {
 473       type: QueryTypes.SELECT as QueryTypes.SELECT,
 474       replacements
 475     }
 476
 477     const [ { total } ] = await AbuseModel.sequelize.query<{ total: string }>(query, options)
 478     if (total === null) return 0
 479
 480     return parseInt(total, 10)
 481   }
 482
 483   private static async internalListForApi (parameters: BuildAbusesQueryOptions) {
 484     const { query, replacements } = buildAbuseListQuery(parameters, 'id')
 485     const options = {
 486       type: QueryTypes.SELECT as QueryTypes.SELECT,
 487       replacements
 488     }
 489
 490     const rows = await AbuseModel.sequelize.query<{ id: string }>(query, options)
 491     const ids = rows.map(r => r.id)
 492
 493     if (ids.length === 0) return []
 494
 495     return AbuseModel.scope(ScopeNames.FOR_API)
 496                      .findAll({
 497                        order: getSort(parameters.sort),
 498                        where: {
 499                          id: {
 500                            [Op.in]: ids
 501                          }
 502                        }
 503                      })
 504   }
 505
 506   private static getStateLabel (id: number) {
 507     return ABUSE_STATES[id] || 'Unknown'
 508   }
 509
 510   private static getPredefinedReasonsStrings (predefinedReasons: AbusePredefinedReasons[]): AbusePredefinedReasonsString[] {
 511     return (predefinedReasons || [])
 512       .filter(r => r in AbusePredefinedReasons)
 513       .map(r => invert(abusePredefinedReasonsMap)[r] as AbusePredefinedReasonsString)
 514   }
 515 }