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