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