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