Use 3 tables to represent abuses

2 files changed, 42 insertions, 521 deletions

diff --git a/server/models/video/video-abuse.ts b/server/models/video/video-abuse.ts
deleted file mode 100644
index 1319332f0..000000000
--- a/server/models/video/video-abuse.ts
+++ /dev/null
@@ -1,479 +0,0 @@
-import * as Bluebird from 'bluebird'
-import { literal, Op } from 'sequelize'
-import {
-  AllowNull,
-  BelongsTo,
-  Column,
-  CreatedAt,
-  DataType,
-  Default,
-  ForeignKey,
-  Is,
-  Model,
-  Scopes,
-  Table,
-  UpdatedAt
-} from 'sequelize-typescript'
-import { VideoAbuseVideoIs } from '@shared/models/videos/abuse/video-abuse-video-is.type'
-import {
-  VideoAbuseState,
-  VideoDetails,
-  VideoAbusePredefinedReasons,
-  VideoAbusePredefinedReasonsString,
-  videoAbusePredefinedReasonsMap
-} from '../../../shared'
-import { VideoAbuseObject } from '../../../shared/models/activitypub/objects'
-import { VideoAbuse } from '../../../shared/models/videos'
-import {
-  isVideoAbuseModerationCommentValid,
-  isVideoAbuseReasonValid,
-  isVideoAbuseStateValid
-} from '../../helpers/custom-validators/video-abuses'
-import { CONSTRAINTS_FIELDS, VIDEO_ABUSE_STATES } from '../../initializers/constants'
-import { MUserAccountId, MVideoAbuse, MVideoAbuseFormattable, MVideoAbuseVideo } from '../../types/models'
-import { AccountModel } from '../account/account'
-import { buildBlockedAccountSQL, getSort, searchAttribute, throwIfNotValid } from '../utils'
-import { ThumbnailModel } from './thumbnail'
-import { VideoModel } from './video'
-import { VideoBlacklistModel } from './video-blacklist'
-import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
-import { invert } from 'lodash'
-
-export enum ScopeNames {
-  FOR_API = 'FOR_API'
-}
-
-@Scopes(() => ({
-  [ScopeNames.FOR_API]: (options: {
-    // search
-    search?: string
-    searchReporter?: string
-    searchReportee?: string
-    searchVideo?: string
-    searchVideoChannel?: string
-
-    // filters
-    id?: number
-    predefinedReasonId?: number
-
-    state?: VideoAbuseState
-    videoIs?: VideoAbuseVideoIs
-
-    // accountIds
-    serverAccountId: number
-    userAccountId: number
-  }) => {
-    const where = {
-      reporterAccountId: {
-        [Op.notIn]: literal('(' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')')
-      }
-    }
-
-    if (options.search) {
-      Object.assign(where, {
-        [Op.or]: [
-          {
-            [Op.and]: [
-              { videoId: { [Op.not]: null } },
-              searchAttribute(options.search, '$Video.name$')
-            ]
-          },
-          {
-            [Op.and]: [
-              { videoId: { [Op.not]: null } },
-              searchAttribute(options.search, '$Video.VideoChannel.name$')
-            ]
-          },
-          {
-            [Op.and]: [
-              { deletedVideo: { [Op.not]: null } },
-              { deletedVideo: searchAttribute(options.search, 'name') }
-            ]
-          },
-          {
-            [Op.and]: [
-              { deletedVideo: { [Op.not]: null } },
-              { deletedVideo: { channel: searchAttribute(options.search, 'displayName') } }
-            ]
-          },
-          searchAttribute(options.search, '$Account.name$')
-        ]
-      })
-    }
-
-    if (options.id) Object.assign(where, { id: options.id })
-    if (options.state) Object.assign(where, { state: options.state })
-
-    if (options.videoIs === 'deleted') {
-      Object.assign(where, {
-        deletedVideo: {
-          [Op.not]: null
-        }
-      })
-    }
-
-    if (options.predefinedReasonId) {
-      Object.assign(where, {
-        predefinedReasons: {
-          [Op.contains]: [ options.predefinedReasonId ]
-        }
-      })
-    }
-
-    const onlyBlacklisted = options.videoIs === 'blacklisted'
-
-    return {
-      attributes: {
-        include: [
-          [
-            // we don't care about this count for deleted videos, so there are not included
-            literal(
-              '(' +
-                'SELECT count(*) ' +
-                'FROM "videoAbuse" ' +
-                'WHERE "videoId" = "VideoAbuseModel"."videoId" ' +
-              ')'
-            ),
-            'countReportsForVideo'
-          ],
-          [
-            // we don't care about this count for deleted videos, so there are not included
-            literal(
-              '(' +
-                'SELECT t.nth ' +
-                'FROM ( ' +
-                  'SELECT id, ' +
-                         'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
-                  'FROM "videoAbuse" ' +
-                ') t ' +
-                'WHERE t.id = "VideoAbuseModel".id ' +
-              ')'
-            ),
-            'nthReportForVideo'
-          ],
-          [
-            literal(
-              '(' +
-                'SELECT count("videoAbuse"."id") ' +
-                'FROM "videoAbuse" ' +
-                'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
-                'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
-                'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
-                'WHERE "account"."id" = "VideoAbuseModel"."reporterAccountId" ' +
-              ')'
-            ),
-            'countReportsForReporter__video'
-          ],
-          [
-            literal(
-              '(' +
-                'SELECT count(DISTINCT "videoAbuse"."id") ' +
-                'FROM "videoAbuse" ' +
-                `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "VideoAbuseModel"."reporterAccountId" ` +
-              ')'
-            ),
-            'countReportsForReporter__deletedVideo'
-          ],
-          [
-            literal(
-              '(' +
-                'SELECT count(DISTINCT "videoAbuse"."id") ' +
-                'FROM "videoAbuse" ' +
-                'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
-                'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
-                'INNER JOIN "account" ON ' +
-                      '"videoChannel"."accountId" = "Video->VideoChannel"."accountId" ' +
-                   `OR "videoChannel"."accountId" = CAST("VideoAbuseModel"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
-              ')'
-            ),
-            'countReportsForReportee__video'
-          ],
-          [
-            literal(
-              '(' +
-                'SELECT count(DISTINCT "videoAbuse"."id") ' +
-                'FROM "videoAbuse" ' +
-                `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "Video->VideoChannel"."accountId" ` +
-                   `OR CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = ` +
-                      `CAST("VideoAbuseModel"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
-              ')'
-            ),
-            'countReportsForReportee__deletedVideo'
-          ]
-        ]
-      },
-      include: [
-        {
-          model: AccountModel,
-          required: true,
-          where: searchAttribute(options.searchReporter, 'name')
-        },
-        {
-          model: VideoModel,
-          required: !!(onlyBlacklisted || options.searchVideo || options.searchReportee || options.searchVideoChannel),
-          where: searchAttribute(options.searchVideo, 'name'),
-          include: [
-            {
-              model: ThumbnailModel
-            },
-            {
-              model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: true } as SummaryOptions ] }),
-              where: searchAttribute(options.searchVideoChannel, 'name'),
-              include: [
-                {
-                  model: AccountModel,
-                  where: searchAttribute(options.searchReportee, 'name')
-                }
-              ]
-            },
-            {
-              attributes: [ 'id', 'reason', 'unfederated' ],
-              model: VideoBlacklistModel,
-              required: onlyBlacklisted
-            }
-          ]
-        }
-      ],
-      where
-    }
-  }
-}))
-@Table({
-  tableName: 'videoAbuse',
-  indexes: [
-    {
-      fields: [ 'videoId' ]
-    },
-    {
-      fields: [ 'reporterAccountId' ]
-    }
-  ]
-})
-export class VideoAbuseModel extends Model<VideoAbuseModel> {
-
-  @AllowNull(false)
-  @Default(null)
-  @Is('VideoAbuseReason', value => throwIfNotValid(value, isVideoAbuseReasonValid, 'reason'))
-  @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_ABUSES.REASON.max))
-  reason: string
-
-  @AllowNull(false)
-  @Default(null)
-  @Is('VideoAbuseState', value => throwIfNotValid(value, isVideoAbuseStateValid, 'state'))
-  @Column
-  state: VideoAbuseState
-
-  @AllowNull(true)
-  @Default(null)
-  @Is('VideoAbuseModerationComment', value => throwIfNotValid(value, isVideoAbuseModerationCommentValid, 'moderationComment', true))
-  @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_ABUSES.MODERATION_COMMENT.max))
-  moderationComment: string
-
-  @AllowNull(true)
-  @Default(null)
-  @Column(DataType.JSONB)
-  deletedVideo: VideoDetails
-
-  @AllowNull(true)
-  @Default(null)
-  @Column(DataType.ARRAY(DataType.INTEGER))
-  predefinedReasons: VideoAbusePredefinedReasons[]
-
-  @AllowNull(true)
-  @Default(null)
-  @Column
-  startAt: number
-
-  @AllowNull(true)
-  @Default(null)
-  @Column
-  endAt: number
-
-  @CreatedAt
-  createdAt: Date
-
-  @UpdatedAt
-  updatedAt: Date
-
-  @ForeignKey(() => AccountModel)
-  @Column
-  reporterAccountId: number
-
-  @BelongsTo(() => AccountModel, {
-    foreignKey: {
-      allowNull: true
-    },
-    onDelete: 'set null'
-  })
-  Account: AccountModel
-
-  @ForeignKey(() => VideoModel)
-  @Column
-  videoId: number
-
-  @BelongsTo(() => VideoModel, {
-    foreignKey: {
-      allowNull: true
-    },
-    onDelete: 'set null'
-  })
-  Video: VideoModel
-
-  static loadByIdAndVideoId (id: number, videoId?: number, uuid?: string): Bluebird<MVideoAbuse> {
-    const videoAttributes = {}
-    if (videoId) videoAttributes['videoId'] = videoId
-    if (uuid) videoAttributes['deletedVideo'] = { uuid }
-
-    const query = {
-      where: {
-        id,
-        ...videoAttributes
-      }
-    }
-    return VideoAbuseModel.findOne(query)
-  }
-
-  static listForApi (parameters: {
-    start: number
-    count: number
-    sort: string
-
-    serverAccountId: number
-    user?: MUserAccountId
-
-    id?: number
-    predefinedReason?: VideoAbusePredefinedReasonsString
-    state?: VideoAbuseState
-    videoIs?: VideoAbuseVideoIs
-
-    search?: string
-    searchReporter?: string
-    searchReportee?: string
-    searchVideo?: string
-    searchVideoChannel?: string
-  }) {
-    const {
-      start,
-      count,
-      sort,
-      search,
-      user,
-      serverAccountId,
-      state,
-      videoIs,
-      predefinedReason,
-      searchReportee,
-      searchVideo,
-      searchVideoChannel,
-      searchReporter,
-      id
-    } = parameters
-
-    const userAccountId = user ? user.Account.id : undefined
-    const predefinedReasonId = predefinedReason ? videoAbusePredefinedReasonsMap[predefinedReason] : undefined
-
-    const query = {
-      offset: start,
-      limit: count,
-      order: getSort(sort),
-      col: 'VideoAbuseModel.id',
-      distinct: true
-    }
-
-    const filters = {
-      id,
-      predefinedReasonId,
-      search,
-      state,
-      videoIs,
-      searchReportee,
-      searchVideo,
-      searchVideoChannel,
-      searchReporter,
-      serverAccountId,
-      userAccountId
-    }
-
-    return VideoAbuseModel
-      .scope([
-        { method: [ ScopeNames.FOR_API, filters ] }
-      ])
-      .findAndCountAll(query)
-      .then(({ rows, count }) => {
-        return { total: count, data: rows }
-      })
-  }
-
-  toFormattedJSON (this: MVideoAbuseFormattable): VideoAbuse {
-    const predefinedReasons = VideoAbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
-    const countReportsForVideo = this.get('countReportsForVideo') as number
-    const nthReportForVideo = this.get('nthReportForVideo') as number
-    const countReportsForReporterVideo = this.get('countReportsForReporter__video') as number
-    const countReportsForReporterDeletedVideo = this.get('countReportsForReporter__deletedVideo') as number
-    const countReportsForReporteeVideo = this.get('countReportsForReportee__video') as number
-    const countReportsForReporteeDeletedVideo = this.get('countReportsForReportee__deletedVideo') as number
-
-    const video = this.Video
-      ? this.Video
-      : this.deletedVideo
-
-    return {
-      id: this.id,
-      reason: this.reason,
-      predefinedReasons,
-      reporterAccount: this.Account.toFormattedJSON(),
-      state: {
-        id: this.state,
-        label: VideoAbuseModel.getStateLabel(this.state)
-      },
-      moderationComment: this.moderationComment,
-      video: {
-        id: video.id,
-        uuid: video.uuid,
-        name: video.name,
-        nsfw: video.nsfw,
-        deleted: !this.Video,
-        blacklisted: this.Video?.isBlacklisted() || false,
-        thumbnailPath: this.Video?.getMiniatureStaticPath(),
-        channel: this.Video?.VideoChannel.toFormattedJSON() || this.deletedVideo?.channel
-      },
-      createdAt: this.createdAt,
-      updatedAt: this.updatedAt,
-      startAt: this.startAt,
-      endAt: this.endAt,
-      count: countReportsForVideo || 0,
-      nth: nthReportForVideo || 0,
-      countReportsForReporter: (countReportsForReporterVideo || 0) + (countReportsForReporterDeletedVideo || 0),
-      countReportsForReportee: (countReportsForReporteeVideo || 0) + (countReportsForReporteeDeletedVideo || 0)
-    }
-  }
-
-  toActivityPubObject (this: MVideoAbuseVideo): VideoAbuseObject {
-    const predefinedReasons = VideoAbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
-
-    const startAt = this.startAt
-    const endAt = this.endAt
-
-    return {
-      type: 'Flag' as 'Flag',
-      content: this.reason,
-      object: this.Video.url,
-      tag: predefinedReasons.map(r => ({
-        type: 'Hashtag' as 'Hashtag',
-        name: r
-      })),
-      startAt,
-      endAt
-    }
-  }
-
-  private static getStateLabel (id: number) {
-    return VIDEO_ABUSE_STATES[id] || 'Unknown'
-  }
-
-  private static getPredefinedReasonsStrings (predefinedReasons: VideoAbusePredefinedReasons[]): VideoAbusePredefinedReasonsString[] {
-    return (predefinedReasons || [])
-      .filter(r => r in VideoAbusePredefinedReasons)
-      .map(r => invert(videoAbusePredefinedReasonsMap)[r] as VideoAbusePredefinedReasonsString)
-  }
-}
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index e2718300e..272bba0e1 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -1,4 +1,5 @@
 import * as Bluebird from 'bluebird'
+import { remove } from 'fs-extra'
 import { maxBy, minBy, pick } from 'lodash'
 import { join } from 'path'
 import { FindOptions, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize'
@@ -23,10 +24,18 @@ import {
   Table,
   UpdatedAt
 } from 'sequelize-typescript'
-import { UserRight, VideoPrivacy, VideoState, ResultList } from '../../../shared'
+import { buildNSFWFilter } from '@server/helpers/express-utils'
+import { getPrivaciesForFederation, isPrivacyForFederation } from '@server/helpers/video'
+import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths'
+import { getServerActor } from '@server/models/application/application'
+import { ModelCache } from '@server/models/model-cache'
+import { VideoFile } from '@shared/models/videos/video-file.model'
+import { ResultList, UserRight, VideoPrivacy, VideoState } from '../../../shared'
 import { VideoTorrentObject } from '../../../shared/models/activitypub/objects'
 import { Video, VideoDetails } from '../../../shared/models/videos'
+import { ThumbnailType } from '../../../shared/models/videos/thumbnail.type'
 import { VideoFilter } from '../../../shared/models/videos/video-query.type'
+import { VideoStreamingPlaylistType } from '../../../shared/models/videos/video-streaming-playlist.type'
 import { peertubeTruncate } from '../../helpers/core-utils'
 import { isActivityPubUrlValid } from '../../helpers/custom-validators/activitypub/misc'
 import { isBooleanValid } from '../../helpers/custom-validators/misc'
@@ -43,6 +52,7 @@ import {
 } from '../../helpers/custom-validators/videos'
 import { getVideoFileResolution } from '../../helpers/ffmpeg-utils'
 import { logger } from '../../helpers/logger'
+import { CONFIG } from '../../initializers/config'
 import {
   ACTIVITY_PUB,
   API_VERSION,
@@ -59,40 +69,6 @@ import {
   WEBSERVER
 } from '../../initializers/constants'
 import { sendDeleteVideo } from '../../lib/activitypub/send'
-import { AccountModel } from '../account/account'
-import { AccountVideoRateModel } from '../account/account-video-rate'
-import { ActorModel } from '../activitypub/actor'
-import { AvatarModel } from '../avatar/avatar'
-import { ServerModel } from '../server/server'
-import { buildTrigramSearchIndex, buildWhereIdOrUUID, getVideoSort, isOutdated, throwIfNotValid } from '../utils'
-import { TagModel } from './tag'
-import { VideoAbuseModel } from './video-abuse'
-import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
-import { VideoCommentModel } from './video-comment'
-import { VideoFileModel } from './video-file'
-import { VideoShareModel } from './video-share'
-import { VideoTagModel } from './video-tag'
-import { ScheduleVideoUpdateModel } from './schedule-video-update'
-import { VideoCaptionModel } from './video-caption'
-import { VideoBlacklistModel } from './video-blacklist'
-import { remove } from 'fs-extra'
-import { VideoViewModel } from './video-view'
-import { VideoRedundancyModel } from '../redundancy/video-redundancy'
-import {
-  videoFilesModelToFormattedJSON,
-  VideoFormattingJSONOptions,
-  videoModelToActivityPubObject,
-  videoModelToFormattedDetailsJSON,
-  videoModelToFormattedJSON
-} from './video-format-utils'
-import { UserVideoHistoryModel } from '../account/user-video-history'
-import { VideoImportModel } from './video-import'
-import { VideoStreamingPlaylistModel } from './video-streaming-playlist'
-import { VideoPlaylistElementModel } from './video-playlist-element'
-import { CONFIG } from '../../initializers/config'
-import { ThumbnailModel } from './thumbnail'
-import { ThumbnailType } from '../../../shared/models/videos/thumbnail.type'
-import { VideoStreamingPlaylistType } from '../../../shared/models/videos/video-streaming-playlist.type'
 import {
   MChannel,
   MChannelAccountDefault,
@@ -118,15 +94,39 @@ import {
   MVideoWithFile,
   MVideoWithRights
 } from '../../types/models'
-import { MVideoFile, MVideoFileStreamingPlaylistVideo } from '../../types/models/video/video-file'
 import { MThumbnail } from '../../types/models/video/thumbnail'
-import { VideoFile } from '@shared/models/videos/video-file.model'
-import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths'
-import { ModelCache } from '@server/models/model-cache'
+import { MVideoFile, MVideoFileStreamingPlaylistVideo } from '../../types/models/video/video-file'
+import { VideoAbuseModel } from '../abuse/video-abuse'
+import { AccountModel } from '../account/account'
+import { AccountVideoRateModel } from '../account/account-video-rate'
+import { UserVideoHistoryModel } from '../account/user-video-history'
+import { ActorModel } from '../activitypub/actor'
+import { AvatarModel } from '../avatar/avatar'
+import { VideoRedundancyModel } from '../redundancy/video-redundancy'
+import { ServerModel } from '../server/server'
+import { buildTrigramSearchIndex, buildWhereIdOrUUID, getVideoSort, isOutdated, throwIfNotValid } from '../utils'
+import { ScheduleVideoUpdateModel } from './schedule-video-update'
+import { TagModel } from './tag'
+import { ThumbnailModel } from './thumbnail'
+import { VideoBlacklistModel } from './video-blacklist'
+import { VideoCaptionModel } from './video-caption'
+import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
+import { VideoCommentModel } from './video-comment'
+import { VideoFileModel } from './video-file'
+import {
+  videoFilesModelToFormattedJSON,
+  VideoFormattingJSONOptions,
+  videoModelToActivityPubObject,
+  videoModelToFormattedDetailsJSON,
+  videoModelToFormattedJSON
+} from './video-format-utils'
+import { VideoImportModel } from './video-import'
+import { VideoPlaylistElementModel } from './video-playlist-element'
 import { buildListQuery, BuildVideosQueryOptions, wrapForAPIResults } from './video-query-builder'
-import { buildNSFWFilter } from '@server/helpers/express-utils'
-import { getServerActor } from '@server/models/application/application'
-import { getPrivaciesForFederation, isPrivacyForFederation } from "@server/helpers/video"
+import { VideoShareModel } from './video-share'
+import { VideoStreamingPlaylistModel } from './video-streaming-playlist'
+import { VideoTagModel } from './video-tag'
+import { VideoViewModel } from './video-view'
 
 export enum ScopeNames {
   AVAILABLE_FOR_LIST_IDS = 'AVAILABLE_FOR_LIST_IDS',