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