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