1 files changed, 118 insertions, 58 deletions

diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index e10adcb3a..642e129ff 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -30,7 +30,7 @@ import { buildServerIdsFollowedBy, buildTrigramSearchIndex, createSimilarityAttr
 import { VideoModel } from './video'
 import { CONSTRAINTS_FIELDS, WEBSERVER } from '../../initializers/constants'
 import { ServerModel } from '../server/server'
-import { FindOptions, ModelIndexesOptions, Op } from 'sequelize'
+import { FindOptions, Op, literal, ScopeOptions } from 'sequelize'
 import { AvatarModel } from '../avatar/avatar'
 import { VideoPlaylistModel } from './video-playlist'
 import * as Bluebird from 'bluebird'
@@ -43,30 +43,23 @@ import {
   MChannelSummaryFormattable
 } from '../../typings/models/video'
 
-// FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation
-const indexes: ModelIndexesOptions[] = [
-  buildTrigramSearchIndex('video_channel_name_trigram', 'name'),
-
-  {
-    fields: [ 'accountId' ]
-  },
-  {
-    fields: [ 'actorId' ]
-  }
-]
-
 export enum ScopeNames {
   FOR_API = 'FOR_API',
+  SUMMARY = 'SUMMARY',
   WITH_ACCOUNT = 'WITH_ACCOUNT',
   WITH_ACTOR = 'WITH_ACTOR',
   WITH_VIDEOS = 'WITH_VIDEOS',
-  SUMMARY = 'SUMMARY'
+  WITH_STATS = 'WITH_STATS'
 }
 
 type AvailableForListOptions = {
   actorId: number
 }
 
+type AvailableWithStatsOptions = {
+  daysPrior: number
+}
+
 export type SummaryOptions = {
   withAccount?: boolean // Default: false
   withAccountBlockerIds?: number[]
@@ -81,40 +74,6 @@ export type SummaryOptions = {
   ]
 }))
 @Scopes(() => ({
-  [ScopeNames.SUMMARY]: (options: SummaryOptions = {}) => {
-    const base: FindOptions = {
-      attributes: [ 'id', 'name', 'description', 'actorId' ],
-      include: [
-        {
-          attributes: [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ],
-          model: ActorModel.unscoped(),
-          required: true,
-          include: [
-            {
-              attributes: [ 'host' ],
-              model: ServerModel.unscoped(),
-              required: false
-            },
-            {
-              model: AvatarModel.unscoped(),
-              required: false
-            }
-          ]
-        }
-      ]
-    }
-
-    if (options.withAccount === true) {
-      base.include.push({
-        model: AccountModel.scope({
-          method: [ AccountModelScopeNames.SUMMARY, { withAccountBlockerIds: options.withAccountBlockerIds } as AccountSummaryOptions ]
-        }),
-        required: true
-      })
-    }
-
-    return base
-  },
   [ScopeNames.FOR_API]: (options: AvailableForListOptions) => {
     // Only list local channels OR channels that are on an instance followed by actorId
     const inQueryInstanceFollow = buildServerIdsFollowedBy(options.actorId)
@@ -133,7 +92,7 @@ export type SummaryOptions = {
               },
               {
                 serverId: {
-                  [ Op.in ]: Sequelize.literal(inQueryInstanceFollow)
+                  [Op.in]: Sequelize.literal(inQueryInstanceFollow)
                 }
               }
             ]
@@ -155,6 +114,40 @@ export type SummaryOptions = {
       ]
     }
   },
+  [ScopeNames.SUMMARY]: (options: SummaryOptions = {}) => {
+    const base: FindOptions = {
+      attributes: [ 'id', 'name', 'description', 'actorId' ],
+      include: [
+        {
+          attributes: [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ],
+          model: ActorModel.unscoped(),
+          required: true,
+          include: [
+            {
+              attributes: [ 'host' ],
+              model: ServerModel.unscoped(),
+              required: false
+            },
+            {
+              model: AvatarModel.unscoped(),
+              required: false
+            }
+          ]
+        }
+      ]
+    }
+
+    if (options.withAccount === true) {
+      base.include.push({
+        model: AccountModel.scope({
+          method: [ AccountModelScopeNames.SUMMARY, { withAccountBlockerIds: options.withAccountBlockerIds } as AccountSummaryOptions ]
+        }),
+        required: true
+      })
+    }
+
+    return base
+  },
   [ScopeNames.WITH_ACCOUNT]: {
     include: [
       {
@@ -163,20 +156,66 @@ export type SummaryOptions = {
       }
     ]
   },
-  [ScopeNames.WITH_VIDEOS]: {
+  [ScopeNames.WITH_ACTOR]: {
     include: [
-      VideoModel
+      ActorModel
     ]
   },
-  [ScopeNames.WITH_ACTOR]: {
+  [ScopeNames.WITH_VIDEOS]: {
     include: [
-      ActorModel
+      VideoModel
     ]
+  },
+  [ScopeNames.WITH_STATS]: (options: AvailableWithStatsOptions = { daysPrior: 30 }) => {
+    const daysPrior = parseInt(options.daysPrior + '', 10)
+
+    return {
+      attributes: {
+        include: [
+          [
+            literal(
+              '(' +
+              `SELECT string_agg(concat_ws('|', t.day, t.views), ',') ` +
+              'FROM ( ' +
+                'WITH ' +
+                  'days AS ( ' +
+                    `SELECT generate_series(date_trunc('day', now()) - '${daysPrior} day'::interval, ` +
+                          `date_trunc('day', now()), '1 day'::interval) AS day ` +
+                  '), ' +
+                  'views AS ( ' +
+                    'SELECT v.* ' +
+                    'FROM "videoView" AS v ' +
+                    'INNER JOIN "video" ON "video"."id" = v."videoId" ' +
+                    'WHERE "video"."channelId" = "VideoChannelModel"."id" ' +
+                  ') ' +
+                'SELECT days.day AS day, ' +
+                      'COALESCE(SUM(views.views), 0) AS views ' +
+                'FROM days ' +
+                `LEFT JOIN views ON date_trunc('day', "views"."startDate") = date_trunc('day', days.day) ` +
+                'GROUP BY day ' +
+                'ORDER BY day ' +
+              ') t' +
+              ')'
+            ),
+            'viewsPerDay'
+          ]
+        ]
+      }
+    }
   }
 }))
 @Table({
   tableName: 'videoChannel',
-  indexes
+  indexes: [
+    buildTrigramSearchIndex('video_channel_name_trigram', 'name'),
+
+    {
+      fields: [ 'accountId' ]
+    },
+    {
+      fields: [ 'actorId' ]
+    }
+  ]
 })
 export class VideoChannelModel extends Model<VideoChannelModel> {
 
@@ -351,10 +390,11 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
   }
 
   static listByAccount (options: {
-    accountId: number,
-    start: number,
-    count: number,
+    accountId: number
+    start: number
+    count: number
     sort: string
+    withStats?: boolean
   }) {
     const query = {
       offset: options.start,
@@ -371,7 +411,16 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
       ]
     }
 
+    const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR ]
+
+    if (options.withStats) {
+      scopes.push({
+        method: [ ScopeNames.WITH_STATS, { daysPrior: 30 } as AvailableWithStatsOptions ]
+      })
+    }
+
     return VideoChannelModel
+      .scope(scopes)
       .findAndCountAll(query)
       .then(({ rows, count }) => {
         return { total: count, data: rows }
@@ -499,6 +548,8 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
   }
 
   toFormattedJSON (this: MChannelFormattable): VideoChannel {
+    const viewsPerDay = this.get('viewsPerDay') as string
+
     const actor = this.Actor.toFormattedJSON()
     const videoChannel = {
       id: this.id,
@@ -508,7 +559,16 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
       isLocal: this.Actor.isOwned(),
       createdAt: this.createdAt,
       updatedAt: this.updatedAt,
-      ownerAccount: undefined
+      ownerAccount: undefined,
+      viewsPerDay: viewsPerDay !== undefined
+        ? viewsPerDay.split(',').map(v => {
+          const o = v.split('|')
+          return {
+            date: new Date(o[0]),
+            views: +o[1]
+          }
+        })
+        : undefined
     }
 
     if (this.Account) videoChannel.ownerAccount = this.Account.toFormattedJSON()