server/models/video/video-abuse.ts

   1 import {
   2   AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, Is, Model, Table, UpdatedAt, Scopes
   3 } from 'sequelize-typescript'
   4 import { VideoAbuseObject } from '../../../shared/models/activitypub/objects'
   5 import { VideoAbuse } from '../../../shared/models/videos'
   6 import {
   7   isVideoAbuseModerationCommentValid,
   8   isVideoAbuseReasonValid,
   9   isVideoAbuseStateValid
  10 } from '../../helpers/custom-validators/video-abuses'
  11 import { AccountModel } from '../account/account'
  12 import { buildBlockedAccountSQL, getSort, throwIfNotValid, searchAttribute, parseQueryStringFilter } from '../utils'
  13 import { VideoModel } from './video'
  14 import { VideoAbuseState, VideoDetails } from '../../../shared'
  15 import { CONSTRAINTS_FIELDS, VIDEO_ABUSE_STATES } from '../../initializers/constants'
  16 import { MUserAccountId, MVideoAbuse, MVideoAbuseFormattable, MVideoAbuseVideo } from '../../typings/models'
  17 import * as Bluebird from 'bluebird'
  18 import { literal, Op } from 'sequelize'
  19 import { ThumbnailModel } from './thumbnail'
  20 import { VideoBlacklistModel } from './video-blacklist'
  21 import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
  22
  23 export enum ScopeNames {
  24   FOR_API = 'FOR_API'
  25 }
  26
  27 @Scopes(() => ({
  28   [ScopeNames.FOR_API]: (options: {
  29     // search
  30     search?: string
  31     searchReporter?: string
  32     searchReportee?: string
  33     searchVideo?: string
  34     searchVideoChannel?: string
  35     // filters
  36     id?: number
  37     state?: VideoAbuseState
  38     is?: any
  39     // accountIds
  40     serverAccountId: number
  41     userAccountId: number
  42   }) => {
  43     let where = {
  44       reporterAccountId: {
  45         [Op.notIn]: literal('(' + buildBlockedAccountSQL(options.serverAccountId, options.userAccountId) + ')')
  46       }
  47     }
  48
  49     if (options.search) {
  50       where = Object.assign(where, {
  51         [Op.or]: [
  52           {
  53             [Op.and]: [
  54               { videoId: { [Op.not]: null } },
  55               searchAttribute(options.search, '$Video.name$')
  56             ]
  57           },
  58           {
  59             [Op.and]: [
  60               { videoId: { [Op.not]: null } },
  61               searchAttribute(options.search, '$Video.VideoChannel.name$')
  62             ]
  63           },
  64           {
  65             [Op.and]: [
  66               { deletedVideo: { [Op.not]: null } },
  67               { deletedVideo: searchAttribute(options.search, 'name') }
  68             ]
  69           },
  70           {
  71             [Op.and]: [
  72               { deletedVideo: { [Op.not]: null } },
  73               { deletedVideo: { channel: searchAttribute(options.search, 'displayName') } }
  74             ]
  75           },
  76           searchAttribute(options.search, '$Account.name$')
  77         ]
  78       })
  79     }
  80
  81     if (options.id) {
  82       where = Object.assign(where, {
  83         id: options.id
  84       })
  85     }
  86
  87     if (options.state) {
  88       where = Object.assign(where, {
  89         state: options.state
  90       })
  91     }
  92
  93     if (options.is) {
  94       where = Object.assign(where, {
  95         ...options.is
  96       })
  97     }
  98
  99     return {
 100       attributes: {
 101         include: [
 102           [
 103             // we don't care about this count for deleted videos, so there are not included
 104             literal(
 105               '(' +
 106                 'SELECT count(*) ' +
 107                 'FROM "videoAbuse" ' +
 108                 'WHERE "videoId" = "VideoAbuseModel"."videoId" ' +
 109               ')'
 110             ),
 111             'countReportsForVideo'
 112           ],
 113           [
 114             // we don't care about this count for deleted videos, so there are not included
 115             literal(
 116               '(' +
 117                 'SELECT t.nth ' +
 118                 'FROM ( ' +
 119                   'SELECT id, ' +
 120                          'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
 121                   'FROM "videoAbuse" ' +
 122                 ') t ' +
 123                 'WHERE t.id = "VideoAbuseModel".id ' +
 124               ')'
 125             ),
 126             'nthReportForVideo'
 127           ],
 128           [
 129             literal(
 130               '(' +
 131                 'SELECT count("videoAbuse"."id") ' +
 132                 'FROM "videoAbuse" ' +
 133                 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
 134                 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 135                 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
 136                 'WHERE "account"."id" = "VideoAbuseModel"."reporterAccountId" ' +
 137               ')'
 138             ),
 139             'countReportsForReporter__video'
 140           ],
 141           [
 142             literal(
 143               '(' +
 144                 'SELECT count(DISTINCT "videoAbuse"."id") ' +
 145                 'FROM "videoAbuse" ' +
 146                 `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "VideoAbuseModel"."reporterAccountId" ` +
 147               ')'
 148             ),
 149             'countReportsForReporter__deletedVideo'
 150           ],
 151           [
 152             literal(
 153               '(' +
 154                 'SELECT count(DISTINCT "videoAbuse"."id") ' +
 155                 'FROM "videoAbuse" ' +
 156                 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
 157                 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 158                 'INNER JOIN "account" ON ' +
 159                       '"videoChannel"."accountId" = "Video->VideoChannel"."accountId" ' +
 160                    `OR "videoChannel"."accountId" = CAST("VideoAbuseModel"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
 161               ')'
 162             ),
 163             'countReportsForReportee__video'
 164           ],
 165           [
 166             literal(
 167               '(' +
 168                 'SELECT count(DISTINCT "videoAbuse"."id") ' +
 169                 'FROM "videoAbuse" ' +
 170                 `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "Video->VideoChannel"."accountId" ` +
 171                    `OR CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = ` +
 172                       `CAST("VideoAbuseModel"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
 173               ')'
 174             ),
 175             'countReportsForReportee__deletedVideo'
 176           ]
 177         ]
 178       },
 179       include: [
 180         {
 181           model: AccountModel,
 182           required: true,
 183           where: searchAttribute(options.searchReporter, 'name')
 184         },
 185         {
 186           model: VideoModel,
 187           required: false,
 188           where: searchAttribute(options.searchVideo, 'name'),
 189           include: [
 190             {
 191               model: ThumbnailModel
 192             },
 193             {
 194               model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: true } as SummaryOptions ] }),
 195               where: searchAttribute(options.searchVideoChannel, 'name'),
 196               include: [
 197                 {
 198                   model: AccountModel,
 199                   where: searchAttribute(options.searchReportee, 'name')
 200                 }
 201               ]
 202             },
 203             {
 204               attributes: [ 'id', 'reason', 'unfederated' ],
 205               model: VideoBlacklistModel
 206             }
 207           ]
 208         }
 209       ],
 210       where
 211     }
 212   }
 213 }))
 214 @Table({
 215   tableName: 'videoAbuse',
 216   indexes: [
 217     {
 218       fields: [ 'videoId' ]
 219     },
 220     {
 221       fields: [ 'reporterAccountId' ]
 222     }
 223   ]
 224 })
 225 export class VideoAbuseModel extends Model<VideoAbuseModel> {
 226
 227   @AllowNull(false)
 228   @Default(null)
 229   @Is('VideoAbuseReason', value => throwIfNotValid(value, isVideoAbuseReasonValid, 'reason'))
 230   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_ABUSES.REASON.max))
 231   reason: string
 232
 233   @AllowNull(false)
 234   @Default(null)
 235   @Is('VideoAbuseState', value => throwIfNotValid(value, isVideoAbuseStateValid, 'state'))
 236   @Column
 237   state: VideoAbuseState
 238
 239   @AllowNull(true)
 240   @Default(null)
 241   @Is('VideoAbuseModerationComment', value => throwIfNotValid(value, isVideoAbuseModerationCommentValid, 'moderationComment', true))
 242   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_ABUSES.MODERATION_COMMENT.max))
 243   moderationComment: string
 244
 245   @AllowNull(true)
 246   @Default(null)
 247   @Column(DataType.JSONB)
 248   deletedVideo: VideoDetails
 249
 250   @CreatedAt
 251   createdAt: Date
 252
 253   @UpdatedAt
 254   updatedAt: Date
 255
 256   @ForeignKey(() => AccountModel)
 257   @Column
 258   reporterAccountId: number
 259
 260   @BelongsTo(() => AccountModel, {
 261     foreignKey: {
 262       allowNull: true
 263     },
 264     onDelete: 'set null'
 265   })
 266   Account: AccountModel
 267
 268   @ForeignKey(() => VideoModel)
 269   @Column
 270   videoId: number
 271
 272   @BelongsTo(() => VideoModel, {
 273     foreignKey: {
 274       allowNull: true
 275     },
 276     onDelete: 'set null'
 277   })
 278   Video: VideoModel
 279
 280   static loadByIdAndVideoId (id: number, videoId?: number, uuid?: string): Bluebird<MVideoAbuse> {
 281     const videoAttributes = {}
 282     if (videoId) videoAttributes['videoId'] = videoId
 283     if (uuid) videoAttributes['deletedVideo'] = { uuid }
 284
 285     const query = {
 286       where: {
 287         id,
 288         ...videoAttributes
 289       }
 290     }
 291     return VideoAbuseModel.findOne(query)
 292   }
 293
 294   static listForApi (parameters: {
 295     start: number
 296     count: number
 297     sort: string
 298     search?: string
 299     serverAccountId: number
 300     user?: MUserAccountId
 301   }) {
 302     const { start, count, sort, search, user, serverAccountId } = parameters
 303     const userAccountId = user ? user.Account.id : undefined
 304
 305     const query = {
 306       offset: start,
 307       limit: count,
 308       order: getSort(sort),
 309       col: 'VideoAbuseModel.id',
 310       distinct: true
 311     }
 312
 313     const filters = {
 314       ...parseQueryStringFilter(search, {
 315         id: {
 316           prefix: '#',
 317           handler: v => v
 318         },
 319         state: {
 320           prefix: 'state:',
 321           handler: v => {
 322             if (v === "accepted") return VideoAbuseState.ACCEPTED
 323             if (v === "pending") return VideoAbuseState.PENDING
 324             if (v === "rejected") return VideoAbuseState.REJECTED
 325             return undefined
 326           }
 327         },
 328         is: {
 329           prefix: 'is:',
 330           handler: v => {
 331             if (v === "deleted") return { deletedVideo: { [Op.not]: null } }
 332             return undefined
 333           }
 334         },
 335         searchReporter: {
 336           prefix: 'reporter:',
 337           handler: v => v
 338         },
 339         searchReportee: {
 340           prefix: 'reportee:',
 341           handler: v => v
 342         }
 343       }),
 344       serverAccountId,
 345       userAccountId
 346     }
 347
 348     return VideoAbuseModel
 349       .scope({ method: [ ScopeNames.FOR_API, filters ] })
 350       .findAndCountAll(query)
 351       .then(({ rows, count }) => {
 352         return { total: count, data: rows }
 353       })
 354   }
 355
 356   toFormattedJSON (this: MVideoAbuseFormattable): VideoAbuse {
 357     const countReportsForVideo = this.get('countReportsForVideo') as number
 358     const nthReportForVideo = this.get('nthReportForVideo') as number
 359     const countReportsForReporterVideo = this.get('countReportsForReporter__video') as number
 360     const countReportsForReporterDeletedVideo = this.get('countReportsForReporter__deletedVideo') as number
 361     const countReportsForReporteeVideo = this.get('countReportsForReportee__video') as number
 362     const countReportsForReporteeDeletedVideo = this.get('countReportsForReportee__deletedVideo') as number
 363
 364     const video = this.Video
 365       ? this.Video
 366       : this.deletedVideo
 367
 368     return {
 369       id: this.id,
 370       reason: this.reason,
 371       reporterAccount: this.Account.toFormattedJSON(),
 372       state: {
 373         id: this.state,
 374         label: VideoAbuseModel.getStateLabel(this.state)
 375       },
 376       moderationComment: this.moderationComment,
 377       video: {
 378         id: video.id,
 379         uuid: video.uuid,
 380         name: video.name,
 381         nsfw: video.nsfw,
 382         deleted: !this.Video,
 383         blacklisted: this.Video && this.Video.isBlacklisted(),
 384         thumbnailPath: this.Video?.getMiniatureStaticPath(),
 385         channel: this.Video?.VideoChannel.toFormattedJSON() || this.deletedVideo?.channel
 386       },
 387       createdAt: this.createdAt,
 388       updatedAt: this.updatedAt,
 389       count: countReportsForVideo || 0,
 390       nth: nthReportForVideo || 0,
 391       countReportsForReporter: (countReportsForReporterVideo || 0) + (countReportsForReporterDeletedVideo || 0),
 392       countReportsForReportee: (countReportsForReporteeVideo || 0) + (countReportsForReporteeDeletedVideo || 0)
 393     }
 394   }
 395
 396   toActivityPubObject (this: MVideoAbuseVideo): VideoAbuseObject {
 397     return {
 398       type: 'Flag' as 'Flag',
 399       content: this.reason,
 400       object: this.Video.url
 401     }
 402   }
 403
 404   private static getStateLabel (id: number) {
 405     return VIDEO_ABUSE_STATES[id] || 'Unknown'
 406   }
 407 }