1 import { QueryTypes } from 'sequelize'
2 import { AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, HasMany, IsUUID, Model, Table } from 'sequelize-typescript'
3 import { STATS_TIMESERIE } from '@server/initializers/constants'
4 import { getActivityStreamDuration } from '@server/lib/activitypub/activity'
5 import { MLocalVideoViewer, MLocalVideoViewerWithWatchSections, MVideo } from '@server/types/models'
6 import { VideoStatsOverall, VideoStatsRetention, VideoStatsTimeserie, VideoStatsTimeserieMetric, WatchActionObject } from '@shared/models'
7 import { AttributesOnly } from '@shared/typescript-utils'
8 import { VideoModel } from '../video/video'
9 import { LocalVideoViewerWatchSectionModel } from './local-video-viewer-watch-section'
12 tableName: 'localVideoViewer',
20 export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVideoViewerModel>>> {
25 @Column(DataType.DATE)
29 @Column(DataType.DATE)
41 @Default(DataType.UUIDV4)
43 @Column(DataType.UUID)
50 @ForeignKey(() => VideoModel)
54 @BelongsTo(() => VideoModel, {
62 @HasMany(() => LocalVideoViewerWatchSectionModel, {
68 WatchSections: LocalVideoViewerWatchSectionModel[]
70 static loadByUrl (url: string): Promise<MLocalVideoViewer> {
78 static loadFullById (id: number): Promise<MLocalVideoViewerWithWatchSections> {
82 model: VideoModel.unscoped(),
86 model: LocalVideoViewerWatchSectionModel.unscoped(),
96 static async getOverallStats (video: MVideo): Promise<VideoStatsOverall> {
98 type: QueryTypes.SELECT as QueryTypes.SELECT,
99 replacements: { videoId: video.id }
102 const watchTimeQuery = `SELECT ` +
103 `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` +
104 `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
105 `FROM "localVideoViewer" ` +
106 `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` +
107 `WHERE "videoId" = :videoId`
109 const watchTimePromise = LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, options)
111 const watchPeakQuery = `WITH "watchPeakValues" AS (
112 SELECT "startDate" AS "dateBreakpoint", 1 AS "inc"
113 FROM "localVideoViewer"
114 WHERE "videoId" = :videoId
116 SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
117 FROM "localVideoViewer"
118 WHERE "videoId" = :videoId
120 SELECT "dateBreakpoint", "concurrent"
122 SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") AS "concurrent"
123 FROM "watchPeakValues"
124 GROUP BY "dateBreakpoint"
126 ORDER BY "concurrent" DESC
127 FETCH FIRST 1 ROW ONLY`
128 const watchPeakPromise = LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, options)
130 const commentsQuery = `SELECT COUNT(*) AS comments FROM "videoComment" WHERE "videoId" = :videoId`
131 const commentsPromise = LocalVideoViewerModel.sequelize.query<any>(commentsQuery, options)
133 const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
134 `FROM "localVideoViewer" ` +
135 `WHERE "videoId" = :videoId AND country IS NOT NULL ` +
136 `GROUP BY country ` +
137 `ORDER BY viewers DESC`
138 const countriesPromise = LocalVideoViewerModel.sequelize.query<any>(countriesQuery, options)
140 const [ rowsWatchTime, rowsWatchPeak, rowsComment, rowsCountries ] = await Promise.all([
148 totalWatchTime: rowsWatchTime.length !== 0
149 ? Math.round(rowsWatchTime[0].totalWatchTime) || 0
151 averageWatchTime: rowsWatchTime.length !== 0
152 ? Math.round(rowsWatchTime[0].averageWatchTime) || 0
155 viewersPeak: rowsWatchPeak.length !== 0
156 ? parseInt(rowsWatchPeak[0].concurrent) || 0
158 viewersPeakDate: rowsWatchPeak.length !== 0
159 ? rowsWatchPeak[0].dateBreakpoint || null
164 dislikes: video.dislikes,
166 comments: rowsComment.length !== 0
167 ? parseInt(rowsComment[0].comments) || 0
170 countries: rowsCountries.map(r => ({
177 static async getRetentionStats (video: MVideo): Promise<VideoStatsRetention> {
178 const step = Math.max(Math.round(video.duration / 100), 1)
180 const query = `WITH "total" AS (SELECT COUNT(*) AS viewers FROM "localVideoViewer" WHERE "videoId" = :videoId) ` +
181 `SELECT serie AS "second", ` +
182 `(COUNT("localVideoViewer".id)::float / (SELECT GREATEST("total"."viewers", 1) FROM "total")) AS "retention" ` +
183 `FROM generate_series(0, ${video.duration}, ${step}) serie ` +
184 `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` +
186 `SELECT 1 FROM "localVideoViewerWatchSection" ` +
187 `WHERE "localVideoViewer"."id" = "localVideoViewerWatchSection"."localVideoViewerId" ` +
188 `AND serie >= "localVideoViewerWatchSection"."watchStart" ` +
189 `AND serie <= "localVideoViewerWatchSection"."watchEnd"` +
194 const queryOptions = {
195 type: QueryTypes.SELECT as QueryTypes.SELECT,
196 replacements: { videoId: video.id }
199 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
202 data: rows.map(r => ({
204 retentionPercent: parseFloat(r.retention) * 100
209 static async getTimeserieStats (options: {
211 metric: VideoStatsTimeserieMetric
212 }): Promise<VideoStatsTimeserie> {
213 const { video, metric } = options
215 const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = {
216 viewers: 'COUNT("localVideoViewer"."id")',
217 aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")'
220 const query = `WITH days AS ( ` +
221 `SELECT (current_date::timestamp - (serie || ' days')::interval)::timestamptz AS day
222 FROM generate_series(0, ${STATS_TIMESERIE.MAX_DAYS - 1}) serie` +
224 `SELECT days.day AS date, COALESCE(${selectMetrics[metric]}, 0) AS value ` +
226 `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` +
227 `AND date_trunc('day', "localVideoViewer"."startDate") = date_trunc('day', days.day) ` +
231 const queryOptions = {
232 type: QueryTypes.SELECT as QueryTypes.SELECT,
233 replacements: { videoId: video.id }
236 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
239 data: rows.map(r => ({
241 value: parseInt(r.value)
246 toActivityPubObject (this: MLocalVideoViewerWithWatchSections): WatchActionObject {
247 const location = this.country
250 addressCountry: this.country
258 duration: getActivityStreamDuration(this.watchTime),
259 startTime: this.startDate.toISOString(),
260 endTime: this.endDate.toISOString(),
262 object: this.Video.url,
264 actionStatus: 'CompletedActionStatus',
266 watchSections: this.WatchSections.map(w => ({
267 startTimestamp: w.watchStart,
268 endTimestamp: w.watchEnd