Add migrations

5 files changed, 85 insertions, 97 deletions

diff --git a/server/models/abuse/abuse.ts b/server/models/abuse/abuse.ts
index 087c77bd3..9c17c4d51 100644
--- a/server/models/abuse/abuse.ts
+++ b/server/models/abuse/abuse.ts
@@ -31,15 +31,15 @@ import {
 } from '@shared/models'
 import { ABUSE_STATES, CONSTRAINTS_FIELDS } from '../../initializers/constants'
 import { MAbuse, MAbuseAP, MAbuseFormattable, MUserAccountId } from '../../types/models'
-import { AccountModel, ScopeNames as AccountScopeNames } from '../account/account'
+import { AccountModel, ScopeNames as AccountScopeNames, SummaryOptions as AccountSummaryOptions } from '../account/account'
 import { buildBlockedAccountSQL, getSort, searchAttribute, throwIfNotValid } from '../utils'
 import { ThumbnailModel } from '../video/thumbnail'
 import { VideoModel } from '../video/video'
 import { VideoBlacklistModel } from '../video/video-blacklist'
-import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from '../video/video-channel'
+import { ScopeNames as VideoChannelScopeNames, SummaryOptions as ChannelSummaryOptions, VideoChannelModel } from '../video/video-channel'
+import { VideoCommentModel } from '../video/video-comment'
 import { VideoAbuseModel } from './video-abuse'
 import { VideoCommentAbuseModel } from './video-comment-abuse'
-import { VideoCommentModel } from '../video/video-comment'
 
 export enum ScopeNames {
   FOR_API = 'FOR_API'
@@ -149,7 +149,7 @@ export enum ScopeNames {
               '(' +
                 'SELECT count(*) ' +
                 'FROM "videoAbuse" ' +
-                'WHERE "videoId" = "VideoAbuse"."videoId" ' +
+                'WHERE "videoId" = "VideoAbuse"."videoId" AND "videoId" IS NOT NULL' +
               ')'
             ),
             'countReportsForVideo'
@@ -164,7 +164,7 @@ export enum ScopeNames {
                          'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
                   'FROM "videoAbuse" ' +
                 ') t ' +
-                'WHERE t.id = "VideoAbuse".id' +
+                'WHERE t.id = "VideoAbuse".id AND t.id IS NOT NULL' +
               ')'
             ),
             'nthReportForVideo'
@@ -172,51 +172,22 @@ export enum ScopeNames {
           [
             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" = "AbuseModel"."reporterAccountId" ' +
-              ')'
-            ),
-            'countReportsForReporter__video'
-          ],
-          [
-            literal(
-              '(' +
-                'SELECT count(DISTINCT "videoAbuse"."id") ' +
-                'FROM "videoAbuse" ' +
-                `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "AbuseModel"."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" = "VideoAbuse->Video->VideoChannel"."accountId" ' +
-                   `OR "videoChannel"."accountId" = CAST("VideoAbuse"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
+                'SELECT count("abuse"."id") ' +
+                'FROM "abuse" ' +
+                'WHERE "abuse"."reporterAccountId" = "AbuseModel"."reporterAccountId"' +
               ')'
             ),
-            'countReportsForReportee__video'
+            'countReportsForReporter'
           ],
           [
             literal(
               '(' +
-                'SELECT count(DISTINCT "videoAbuse"."id") ' +
-                'FROM "videoAbuse" ' +
-                `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "VideoAbuse->Video->VideoChannel"."accountId" ` +
-                   `OR CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = ` +
-                      `CAST("VideoAbuse"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
+                'SELECT count("abuse"."id") ' +
+                'FROM "abuse" ' +
+                'WHERE "abuse"."flaggedAccountId" = "AbuseModel"."flaggedAccountId"' +
               ')'
             ),
-            'countReportsForReportee__deletedVideo'
+            'countReportsForReportee'
           ]
         ]
       },
@@ -224,13 +195,18 @@ export enum ScopeNames {
         {
           model: AccountModel.scope(AccountScopeNames.SUMMARY),
           as: 'ReporterAccount',
-          required: true,
+          required: !!options.searchReporter,
           where: searchAttribute(options.searchReporter, 'name')
         },
         {
-          model: AccountModel.scope(AccountScopeNames.SUMMARY),
+          model: AccountModel.scope({
+            method: [
+              AccountScopeNames.SUMMARY,
+              { actorRequired: false } as AccountSummaryOptions
+            ]
+          }),
           as: 'FlaggedAccount',
-          required: true,
+          required: !!options.searchReportee,
           where: searchAttribute(options.searchReportee, 'name')
         },
         {
@@ -243,35 +219,36 @@ export enum ScopeNames {
               include: [
                 {
                   model: VideoModel.unscoped(),
-                  attributes: [ 'name', 'id', 'uuid' ],
-                  required: true
+                  attributes: [ 'name', 'id', 'uuid' ]
                 }
               ]
             }
           ]
         },
         {
-          model: VideoAbuseModel,
+          model: VideoAbuseModel.unscoped(),
           required: options.filter === 'video' || !!options.videoIs || videoRequired,
           include: [
             {
-              model: VideoModel,
+              attributes: [ 'id', 'uuid', 'name', 'nsfw' ],
+              model: VideoModel.unscoped(),
               required: videoRequired,
               where: searchAttribute(options.searchVideo, 'name'),
               include: [
                 {
+                  attributes: [ 'filename', 'fileUrl' ],
                   model: ThumbnailModel
                 },
                 {
-                  model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: false } as SummaryOptions ] }),
+                  model: VideoChannelModel.scope({
+                    method: [
+                      VideoChannelScopeNames.SUMMARY,
+                      { withAccount: false, actorRequired: false } as ChannelSummaryOptions
+                    ]
+                  }),
+
                   where: searchAttribute(options.searchVideoChannel, 'name'),
-                  required: true,
-                  include: [
-                    {
-                      model: AccountModel.scope(AccountScopeNames.SUMMARY),
-                      required: true
-                    }
-                  ]
+                  required: !!options.searchVideoChannel
                 },
                 {
                   attributes: [ 'id', 'reason', 'unfederated' ],
@@ -304,19 +281,19 @@ export class AbuseModel extends Model<AbuseModel> {
 
   @AllowNull(false)
   @Default(null)
-  @Is('VideoAbuseReason', value => throwIfNotValid(value, isAbuseReasonValid, 'reason'))
+  @Is('AbuseReason', value => throwIfNotValid(value, isAbuseReasonValid, 'reason'))
   @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.REASON.max))
   reason: string
 
   @AllowNull(false)
   @Default(null)
-  @Is('VideoAbuseState', value => throwIfNotValid(value, isAbuseStateValid, 'state'))
+  @Is('AbuseState', value => throwIfNotValid(value, isAbuseStateValid, 'state'))
   @Column
   state: AbuseState
 
   @AllowNull(true)
   @Default(null)
-  @Is('VideoAbuseModerationComment', value => throwIfNotValid(value, isAbuseModerationCommentValid, 'moderationComment', true))
+  @Is('AbuseModerationComment', value => throwIfNotValid(value, isAbuseModerationCommentValid, 'moderationComment', true))
   @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.MODERATION_COMMENT.max))
   moderationComment: string
 
@@ -486,12 +463,12 @@ export class AbuseModel extends Model<AbuseModel> {
 
   toFormattedJSON (this: MAbuseFormattable): Abuse {
     const predefinedReasons = AbuseModel.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 countReportsForReporter = this.get('countReportsForReporter') as number
+    const countReportsForReportee = this.get('countReportsForReportee') as number
 
     let video: VideoAbuse
     let comment: VideoCommentAbuse
@@ -512,7 +489,11 @@ export class AbuseModel extends Model<AbuseModel> {
         deleted: !abuseModel.Video,
         blacklisted: abuseModel.Video?.isBlacklisted() || false,
         thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
-        channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel
+
+        channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel,
+
+        countReports: countReportsForVideo,
+        nthReport: nthReportForVideo
       }
     }
 
@@ -539,7 +520,13 @@ export class AbuseModel extends Model<AbuseModel> {
       reason: this.reason,
       predefinedReasons,
 
-      reporterAccount: this.ReporterAccount.toFormattedJSON(),
+      reporterAccount: this.ReporterAccount
+        ? this.ReporterAccount.toFormattedJSON()
+        : null,
+
+      flaggedAccount: this.FlaggedAccount
+        ? this.FlaggedAccount.toFormattedJSON()
+        : null,
 
       state: {
         id: this.state,
@@ -553,14 +540,15 @@ export class AbuseModel extends Model<AbuseModel> {
 
       createdAt: this.createdAt,
       updatedAt: this.updatedAt,
-      count: countReportsForVideo || 0,
-      nth: nthReportForVideo || 0,
-      countReportsForReporter: (countReportsForReporterVideo || 0) + (countReportsForReporterDeletedVideo || 0),
-      countReportsForReportee: (countReportsForReporteeVideo || 0) + (countReportsForReporteeDeletedVideo || 0),
+
+      countReportsForReporter: (countReportsForReporter || 0),
+      countReportsForReportee: (countReportsForReportee || 0),
 
       // FIXME: deprecated in 2.3, remove this
       startAt: null,
-      endAt: null
+      endAt: null,
+      count: countReportsForVideo || 0,
+      nth: nthReportForVideo || 0
     }
   }
 
diff --git a/server/models/account/account.ts b/server/models/account/account.ts
index 466d6258e..f97519b14 100644
--- a/server/models/account/account.ts
+++ b/server/models/account/account.ts
@@ -42,6 +42,7 @@ export enum ScopeNames {
 }
 
 export type SummaryOptions = {
+  actorRequired?: boolean // Default: true
   whereActor?: WhereOptions
   withAccountBlockerIds?: number[]
 }
@@ -65,12 +66,12 @@ export type SummaryOptions = {
     }
 
     const query: FindOptions = {
-      attributes: [ 'id', 'name' ],
+      attributes: [ 'id', 'name', 'actorId' ],
       include: [
         {
           attributes: [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ],
           model: ActorModel.unscoped(),
-          required: true,
+          required: options.actorRequired ?? true,
           where: whereActor,
           include: [
             serverInclude,
diff --git a/server/models/account/user-notification-setting.ts b/server/models/account/user-notification-setting.ts
index b69b47265..d8f3f13da 100644
--- a/server/models/account/user-notification-setting.ts
+++ b/server/models/account/user-notification-setting.ts
@@ -51,11 +51,11 @@ export class UserNotificationSettingModel extends Model<UserNotificationSettingM
   @AllowNull(false)
   @Default(null)
   @Is(
-    'UserNotificationSettingVideoAbuseAsModerator',
-    value => throwIfNotValid(value, isUserNotificationSettingValid, 'videoAbuseAsModerator')
+    'UserNotificationSettingAbuseAsModerator',
+    value => throwIfNotValid(value, isUserNotificationSettingValid, 'abuseAsModerator')
   )
   @Column
-  videoAbuseAsModerator: UserNotificationSettingValue
+  abuseAsModerator: UserNotificationSettingValue
 
   @AllowNull(false)
   @Default(null)
@@ -166,7 +166,7 @@ export class UserNotificationSettingModel extends Model<UserNotificationSettingM
     return {
       newCommentOnMyVideo: this.newCommentOnMyVideo,
       newVideoFromSubscription: this.newVideoFromSubscription,
-      videoAbuseAsModerator: this.videoAbuseAsModerator,
+      abuseAsModerator: this.abuseAsModerator,
       videoAutoBlacklistAsModerator: this.videoAutoBlacklistAsModerator,
       blacklistOnMyVideo: this.blacklistOnMyVideo,
       myVideoPublished: this.myVideoPublished,
diff --git a/server/models/account/user.ts b/server/models/account/user.ts
index f21eff04b..5f45f8e7c 100644
--- a/server/models/account/user.ts
+++ b/server/models/account/user.ts
@@ -168,28 +168,26 @@ enum ScopeNames {
             '(' +
               `SELECT concat_ws(':', "abuses", "acceptedAbuses") ` +
               'FROM (' +
-                'SELECT COUNT("videoAbuse"."id") AS "abuses", ' +
-                       `COUNT("videoAbuse"."id") FILTER (WHERE "videoAbuse"."state" = ${AbuseState.ACCEPTED}) AS "acceptedAbuses" ` +
-                'FROM "videoAbuse" ' +
-                'INNER JOIN "video" ON "videoAbuse"."videoId" = "video"."id" ' +
-                'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
-                'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
+                'SELECT COUNT("abuse"."id") AS "abuses", ' +
+                       `COUNT("abuse"."id") FILTER (WHERE "abuse"."state" = ${AbuseState.ACCEPTED}) AS "acceptedAbuses" ` +
+                'FROM "abuse" ' +
+                'INNER JOIN "account" ON "account"."id" = "abuse"."flaggedAccountId" ' +
                 'WHERE "account"."userId" = "UserModel"."id"' +
               ') t' +
             ')'
           ),
-          'videoAbusesCount'
+          'abusesCount'
         ],
         [
           literal(
             '(' +
-              'SELECT COUNT("videoAbuse"."id") ' +
-              'FROM "videoAbuse" ' +
-              'INNER JOIN "account" ON "account"."id" = "videoAbuse"."reporterAccountId" ' +
+              'SELECT COUNT("abuse"."id") ' +
+              'FROM "abuse" ' +
+              'INNER JOIN "account" ON "account"."id" = "abuse"."reporterAccountId" ' +
               'WHERE "account"."userId" = "UserModel"."id"' +
             ')'
           ),
-          'videoAbusesCreatedCount'
+          'abusesCreatedCount'
         ],
         [
           literal(
@@ -780,8 +778,8 @@ export class UserModel extends Model<UserModel> {
     const videoQuotaUsed = this.get('videoQuotaUsed')
     const videoQuotaUsedDaily = this.get('videoQuotaUsedDaily')
     const videosCount = this.get('videosCount')
-    const [ videoAbusesCount, videoAbusesAcceptedCount ] = (this.get('videoAbusesCount') as string || ':').split(':')
-    const videoAbusesCreatedCount = this.get('videoAbusesCreatedCount')
+    const [ abusesCount, abusesAcceptedCount ] = (this.get('abusesCount') as string || ':').split(':')
+    const abusesCreatedCount = this.get('abusesCreatedCount')
     const videoCommentsCount = this.get('videoCommentsCount')
 
     const json: User = {
@@ -815,14 +813,14 @@ export class UserModel extends Model<UserModel> {
       videosCount: videosCount !== undefined
         ? parseInt(videosCount + '', 10)
         : undefined,
-      videoAbusesCount: videoAbusesCount
-        ? parseInt(videoAbusesCount, 10)
+      abusesCount: abusesCount
+        ? parseInt(abusesCount, 10)
         : undefined,
-      videoAbusesAcceptedCount: videoAbusesAcceptedCount
-        ? parseInt(videoAbusesAcceptedCount, 10)
+      abusesAcceptedCount: abusesAcceptedCount
+        ? parseInt(abusesAcceptedCount, 10)
         : undefined,
-      videoAbusesCreatedCount: videoAbusesCreatedCount !== undefined
-        ? parseInt(videoAbusesCreatedCount + '', 10)
+      abusesCreatedCount: abusesCreatedCount !== undefined
+        ? parseInt(abusesCreatedCount + '', 10)
         : undefined,
       videoCommentsCount: videoCommentsCount !== undefined
         ? parseInt(videoCommentsCount + '', 10)
diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 9cee64229..03a3cdf81 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -61,6 +61,7 @@ type AvailableWithStatsOptions = {
 }
 
 export type SummaryOptions = {
+  actorRequired?: boolean // Default: true
   withAccount?: boolean // Default: false
   withAccountBlockerIds?: number[]
 }
@@ -121,7 +122,7 @@ export type SummaryOptions = {
         {
           attributes: [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ],
           model: ActorModel.unscoped(),
-          required: true,
+          required: options.actorRequired ?? true,
           include: [
             {
               attributes: [ 'host' ],