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 } 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?: string
  30     searchReporter?: string
  31     searchVideo?: string
  32     searchVideoChannel?: string
  33     serverAccountId: number
  34     userAccountId: any
  35   }) => {
  36     let where = {
  37       reporterAccountId: {
  38         [Op.notIn]: literal('(' + buildBlockedAccountSQL(options.serverAccountId, options.userAccountId) + ')')
  39       }
  40     }
  41
  42     if (options.search) {
  43       where = Object.assign(where, {
  44         [Op.or]: [
  45           {
  46             [Op.and]: [
  47               { videoId: { [Op.not]: null } },
  48               { '$Video.name$': { [Op.iLike]: `%${options.search}%` } }
  49             ]
  50           },
  51           {
  52             [Op.and]: [
  53               { videoId: { [Op.not]: null } },
  54               { '$Video.VideoChannel.name$': { [Op.iLike]: `%${options.search}%` } }
  55             ]
  56           },
  57           {
  58             [Op.and]: [
  59               { deletedVideo: { [Op.not]: null } },
  60               { deletedVideo: { name: { [Op.iLike]: `%${options.search}%` } } }
  61             ]
  62           },
  63           {
  64             [Op.and]: [
  65               { deletedVideo: { [Op.not]: null } },
  66               { deletedVideo: { channel: { displayName: { [Op.iLike]: `%${options.search}%` } } } }
  67             ]
  68           },
  69           { '$Account.name$': { [Op.iLike]: `%${options.search}%` } }
  70         ]
  71       })
  72     }
  73
  74     return {
  75       attributes: {
  76         include: [
  77           [
  78             literal(
  79               '(' +
  80                 'SELECT t.count ' +
  81                 'FROM ( ' +
  82                   'SELECT id, ' +
  83                          'count(id) OVER (PARTITION BY "videoId") ' +
  84                   'FROM "videoAbuse" ' +
  85                 ') t ' +
  86                 'WHERE t.id = "VideoAbuseModel".id ' +
  87               ')'
  88             ),
  89             'countReportsForVideo'
  90           ],
  91           [
  92             literal(
  93               '(' +
  94                 'SELECT t.nth ' +
  95                 'FROM ( ' +
  96                   'SELECT id, ' +
  97                          'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
  98                   'FROM "videoAbuse" ' +
  99                 ') t ' +
 100                 'WHERE t.id = "VideoAbuseModel".id ' +
 101               ')'
 102             ),
 103             'nthReportForVideo'
 104           ],
 105           [
 106             literal(
 107               '(' +
 108                 'SELECT count("videoAbuse"."id") ' +
 109                 'FROM "videoAbuse" ' +
 110                 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
 111                 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 112                 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
 113                 'WHERE "account"."id" = "VideoAbuseModel"."reporterAccountId" ' +
 114               ')'
 115             ),
 116             'countReportsForReporter'
 117           ],
 118           [
 119             literal(
 120               '(' +
 121                 'WITH ' +
 122                   'ids AS ( ' +
 123                     'SELECT "account"."id" ' +
 124                     'FROM "account" ' +
 125                     'INNER JOIN "videoChannel" ON "videoChannel"."accountId" = "account"."id" ' +
 126                     'INNER JOIN "video" ON "video"."channelId" = "videoChannel"."id" ' +
 127                     'WHERE "video"."id" = "VideoAbuseModel"."videoId" ' +
 128                   ') ' +
 129                 'SELECT count("videoAbuse"."id") ' +
 130                 'FROM "videoAbuse" ' +
 131                 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
 132                 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 133                 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
 134                 'INNER JOIN ids ON "account"."id" = ids.id ' +
 135               ')'
 136             ),
 137             'countReportsForReportee'
 138           ]
 139         ]
 140       },
 141       include: [
 142         {
 143           model: AccountModel,
 144           required: true,
 145           where: { ...searchAttribute(options.searchReporter, 'name') }
 146         },
 147         {
 148           model: VideoModel,
 149           required: false,
 150           where: { ...searchAttribute(options.searchVideo, 'name') },
 151           include: [
 152             {
 153               model: ThumbnailModel
 154             },
 155             {
 156               model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: true } as SummaryOptions ] }),
 157               where: { ...searchAttribute(options.searchVideoChannel, 'name') }
 158             },
 159             {
 160               attributes: [ 'id', 'reason', 'unfederated' ],
 161               model: VideoBlacklistModel
 162             }
 163           ]
 164         }
 165       ],
 166       where
 167     }
 168   }
 169 }))
 170 @Table({
 171   tableName: 'videoAbuse',
 172   indexes: [
 173     {
 174       fields: [ 'videoId' ]
 175     },
 176     {
 177       fields: [ 'reporterAccountId' ]
 178     }
 179   ]
 180 })
 181 export class VideoAbuseModel extends Model<VideoAbuseModel> {
 182
 183   @AllowNull(false)
 184   @Default(null)
 185   @Is('VideoAbuseReason', value => throwIfNotValid(value, isVideoAbuseReasonValid, 'reason'))
 186   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_ABUSES.REASON.max))
 187   reason: string
 188
 189   @AllowNull(false)
 190   @Default(null)
 191   @Is('VideoAbuseState', value => throwIfNotValid(value, isVideoAbuseStateValid, 'state'))
 192   @Column
 193   state: VideoAbuseState
 194
 195   @AllowNull(true)
 196   @Default(null)
 197   @Is('VideoAbuseModerationComment', value => throwIfNotValid(value, isVideoAbuseModerationCommentValid, 'moderationComment', true))
 198   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_ABUSES.MODERATION_COMMENT.max))
 199   moderationComment: string
 200
 201   @AllowNull(true)
 202   @Default(null)
 203   @Column(DataType.JSONB)
 204   deletedVideo: VideoDetails
 205
 206   @CreatedAt
 207   createdAt: Date
 208
 209   @UpdatedAt
 210   updatedAt: Date
 211
 212   @ForeignKey(() => AccountModel)
 213   @Column
 214   reporterAccountId: number
 215
 216   @BelongsTo(() => AccountModel, {
 217     foreignKey: {
 218       allowNull: true
 219     },
 220     onDelete: 'set null'
 221   })
 222   Account: AccountModel
 223
 224   @ForeignKey(() => VideoModel)
 225   @Column
 226   videoId: number
 227
 228   @BelongsTo(() => VideoModel, {
 229     foreignKey: {
 230       allowNull: true
 231     },
 232     onDelete: 'set null'
 233   })
 234   Video: VideoModel
 235
 236   static loadByIdAndVideoId (id: number, videoId?: number, uuid?: string): Bluebird<MVideoAbuse> {
 237     const videoAttributes = {}
 238     if (videoId) videoAttributes['videoId'] = videoId
 239     if (uuid) videoAttributes['deletedVideo'] = { uuid }
 240
 241     const query = {
 242       where: {
 243         id,
 244         ...videoAttributes
 245       }
 246     }
 247     return VideoAbuseModel.findOne(query)
 248   }
 249
 250   static listForApi (parameters: {
 251     start: number
 252     count: number
 253     sort: string
 254     search?: string
 255     serverAccountId: number
 256     user?: MUserAccountId
 257   }) {
 258     const { start, count, sort, search, user, serverAccountId } = parameters
 259     const userAccountId = user ? user.Account.id : undefined
 260
 261     const query = {
 262       offset: start,
 263       limit: count,
 264       order: getSort(sort),
 265       col: 'VideoAbuseModel.id',
 266       distinct: true
 267     }
 268
 269     const filters = {
 270       search,
 271       serverAccountId,
 272       userAccountId
 273     }
 274
 275     return VideoAbuseModel
 276       .scope({ method: [ ScopeNames.FOR_API, filters ] })
 277       .findAndCountAll(query)
 278       .then(({ rows, count }) => {
 279         return { total: count, data: rows }
 280       })
 281   }
 282
 283   toFormattedJSON (this: MVideoAbuseFormattable): VideoAbuse {
 284     const countReportsForVideo = this.get('countReportsForVideo') as number
 285     const nthReportForVideo = this.get('nthReportForVideo') as number
 286     const countReportsForReporter = this.get('countReportsForReporter') as number
 287     const countReportsForReportee = this.get('countReportsForReportee') as number
 288
 289     const video = this.Video
 290       ? this.Video
 291       : this.deletedVideo
 292
 293     return {
 294       id: this.id,
 295       reason: this.reason,
 296       reporterAccount: this.Account.toFormattedJSON(),
 297       state: {
 298         id: this.state,
 299         label: VideoAbuseModel.getStateLabel(this.state)
 300       },
 301       moderationComment: this.moderationComment,
 302       video: {
 303         id: video.id,
 304         uuid: video.uuid,
 305         name: video.name,
 306         nsfw: video.nsfw,
 307         deleted: !this.Video,
 308         blacklisted: this.Video && this.Video.isBlacklisted(),
 309         thumbnailPath: this.Video?.getMiniatureStaticPath(),
 310         channel: this.Video?.VideoChannel.toFormattedJSON() || this.deletedVideo?.channel
 311       },
 312       createdAt: this.createdAt,
 313       updatedAt: this.updatedAt,
 314       count: countReportsForVideo || 0,
 315       nth: nthReportForVideo || 0,
 316       countReportsForReporter: countReportsForReporter || 0,
 317       countReportsForReportee: countReportsForReportee || 0
 318     }
 319   }
 320
 321   toActivityPubObject (this: MVideoAbuseVideo): VideoAbuseObject {
 322     return {
 323       type: 'Flag' as 'Flag',
 324       content: this.reason,
 325       object: this.Video.url
 326     }
 327   }
 328
 329   private static getStateLabel (id: number) {
 330     return VIDEO_ABUSE_STATES[id] || 'Unknown'
 331   }
 332 }