server/models/video/video.ts

   1 import * as Bluebird from 'bluebird'
   2 import { maxBy } from 'lodash'
   3 import * as magnetUtil from 'magnet-uri'
   4 import * as parseTorrent from 'parse-torrent'
   5 import { join } from 'path'
   6 import * as Sequelize from 'sequelize'
   7 import {
   8   AllowNull,
   9   BeforeDestroy,
  10   BelongsTo,
  11   BelongsToMany,
  12   Column,
  13   CreatedAt,
  14   DataType,
  15   Default,
  16   ForeignKey,
  17   HasMany,
  18   HasOne,
  19   IFindOptions,
  20   IIncludeOptions,
  21   Is,
  22   IsInt,
  23   IsUUID,
  24   Min,
  25   Model,
  26   Scopes,
  27   Table,
  28   UpdatedAt
  29 } from 'sequelize-typescript'
  30 import { UserRight, VideoPrivacy, VideoState } from '../../../shared'
  31 import { VideoTorrentObject } from '../../../shared/models/activitypub/objects'
  32 import { Video, VideoDetails, VideoFile } from '../../../shared/models/videos'
  33 import { VideoFilter } from '../../../shared/models/videos/video-query.type'
  34 import { createTorrentPromise, peertubeTruncate } from '../../helpers/core-utils'
  35 import { isActivityPubUrlValid } from '../../helpers/custom-validators/activitypub/misc'
  36 import { isArray, isBooleanValid } from '../../helpers/custom-validators/misc'
  37 import {
  38   isVideoCategoryValid,
  39   isVideoDescriptionValid,
  40   isVideoDurationValid,
  41   isVideoLanguageValid,
  42   isVideoLicenceValid,
  43   isVideoNameValid,
  44   isVideoPrivacyValid,
  45   isVideoStateValid,
  46   isVideoSupportValid
  47 } from '../../helpers/custom-validators/videos'
  48 import { generateImageFromVideoFile, getVideoFileResolution } from '../../helpers/ffmpeg-utils'
  49 import { logger } from '../../helpers/logger'
  50 import { getServerActor } from '../../helpers/utils'
  51 import {
  52   ACTIVITY_PUB,
  53   API_VERSION,
  54   CONSTRAINTS_FIELDS,
  55   HLS_REDUNDANCY_DIRECTORY,
  56   HLS_STREAMING_PLAYLIST_DIRECTORY,
  57   PREVIEWS_SIZE,
  58   REMOTE_SCHEME,
  59   STATIC_DOWNLOAD_PATHS,
  60   STATIC_PATHS,
  61   THUMBNAILS_SIZE,
  62   VIDEO_CATEGORIES,
  63   VIDEO_LANGUAGES,
  64   VIDEO_LICENCES,
  65   VIDEO_PRIVACIES,
  66   VIDEO_STATES,
  67   WEBSERVER
  68 } from '../../initializers/constants'
  69 import { sendDeleteVideo } from '../../lib/activitypub/send'
  70 import { AccountModel } from '../account/account'
  71 import { AccountVideoRateModel } from '../account/account-video-rate'
  72 import { ActorModel } from '../activitypub/actor'
  73 import { AvatarModel } from '../avatar/avatar'
  74 import { ServerModel } from '../server/server'
  75 import {
  76   buildBlockedAccountSQL,
  77   buildTrigramSearchIndex,
  78   buildWhereIdOrUUID,
  79   createSimilarityAttribute,
  80   getVideoSort,
  81   isOutdated,
  82   throwIfNotValid
  83 } from '../utils'
  84 import { TagModel } from './tag'
  85 import { VideoAbuseModel } from './video-abuse'
  86 import { ScopeNames as VideoChannelScopeNames, VideoChannelModel } from './video-channel'
  87 import { VideoCommentModel } from './video-comment'
  88 import { VideoFileModel } from './video-file'
  89 import { VideoShareModel } from './video-share'
  90 import { VideoTagModel } from './video-tag'
  91 import { ScheduleVideoUpdateModel } from './schedule-video-update'
  92 import { VideoCaptionModel } from './video-caption'
  93 import { VideoBlacklistModel } from './video-blacklist'
  94 import { remove, writeFile } from 'fs-extra'
  95 import { VideoViewModel } from './video-views'
  96 import { VideoRedundancyModel } from '../redundancy/video-redundancy'
  97 import {
  98   videoFilesModelToFormattedJSON,
  99   VideoFormattingJSONOptions,
 100   videoModelToActivityPubObject,
 101   videoModelToFormattedDetailsJSON,
 102   videoModelToFormattedJSON
 103 } from './video-format-utils'
 104 import { UserVideoHistoryModel } from '../account/user-video-history'
 105 import { UserModel } from '../account/user'
 106 import { VideoImportModel } from './video-import'
 107 import { VideoStreamingPlaylistModel } from './video-streaming-playlist'
 108 import { VideoPlaylistElementModel } from './video-playlist-element'
 109 import { CONFIG } from '../../initializers/config'
 110
 111 // FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation
 112 const indexes: Sequelize.DefineIndexesOptions[] = [
 113   buildTrigramSearchIndex('video_name_trigram', 'name'),
 114
 115   { fields: [ 'createdAt' ] },
 116   { fields: [ 'publishedAt' ] },
 117   { fields: [ 'duration' ] },
 118   { fields: [ 'views' ] },
 119   { fields: [ 'channelId' ] },
 120   {
 121     fields: [ 'originallyPublishedAt' ],
 122     where: {
 123       originallyPublishedAt: {
 124         [Sequelize.Op.ne]: null
 125       }
 126     }
 127   },
 128   {
 129     fields: [ 'category' ], // We don't care videos with an unknown category
 130     where: {
 131       category: {
 132         [Sequelize.Op.ne]: null
 133       }
 134     }
 135   },
 136   {
 137     fields: [ 'licence' ], // We don't care videos with an unknown licence
 138     where: {
 139       licence: {
 140         [Sequelize.Op.ne]: null
 141       }
 142     }
 143   },
 144   {
 145     fields: [ 'language' ], // We don't care videos with an unknown language
 146     where: {
 147       language: {
 148         [Sequelize.Op.ne]: null
 149       }
 150     }
 151   },
 152   {
 153     fields: [ 'nsfw' ], // Most of the videos are not NSFW
 154     where: {
 155       nsfw: true
 156     }
 157   },
 158   {
 159     fields: [ 'remote' ], // Only index local videos
 160     where: {
 161       remote: false
 162     }
 163   },
 164   {
 165     fields: [ 'uuid' ],
 166     unique: true
 167   },
 168   {
 169     fields: [ 'url' ],
 170     unique: true
 171   }
 172 ]
 173
 174 export enum ScopeNames {
 175   AVAILABLE_FOR_LIST_IDS = 'AVAILABLE_FOR_LIST_IDS',
 176   FOR_API = 'FOR_API',
 177   WITH_ACCOUNT_DETAILS = 'WITH_ACCOUNT_DETAILS',
 178   WITH_TAGS = 'WITH_TAGS',
 179   WITH_FILES = 'WITH_FILES',
 180   WITH_SCHEDULED_UPDATE = 'WITH_SCHEDULED_UPDATE',
 181   WITH_BLACKLISTED = 'WITH_BLACKLISTED',
 182   WITH_USER_HISTORY = 'WITH_USER_HISTORY',
 183   WITH_STREAMING_PLAYLISTS = 'WITH_STREAMING_PLAYLISTS',
 184   WITH_USER_ID = 'WITH_USER_ID'
 185 }
 186
 187 type ForAPIOptions = {
 188   ids: number[]
 189
 190   videoPlaylistId?: number
 191
 192   withFiles?: boolean
 193 }
 194
 195 type AvailableForListIDsOptions = {
 196   serverAccountId: number
 197   followerActorId: number
 198   includeLocalVideos: boolean
 199
 200   filter?: VideoFilter
 201   categoryOneOf?: number[]
 202   nsfw?: boolean
 203   licenceOneOf?: number[]
 204   languageOneOf?: string[]
 205   tagsOneOf?: string[]
 206   tagsAllOf?: string[]
 207
 208   withFiles?: boolean
 209
 210   accountId?: number
 211   videoChannelId?: number
 212
 213   videoPlaylistId?: number
 214
 215   trendingDays?: number
 216   user?: UserModel,
 217   historyOfUser?: UserModel
 218 }
 219
 220 @Scopes({
 221   [ ScopeNames.FOR_API ]: (options: ForAPIOptions) => {
 222     const query: IFindOptions<VideoModel> = {
 223       where: {
 224         id: {
 225           [ Sequelize.Op.any ]: options.ids
 226         }
 227       },
 228       include: [
 229         {
 230           model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, true ] }),
 231           required: true
 232         }
 233       ]
 234     }
 235
 236     if (options.withFiles === true) {
 237       query.include.push({
 238         model: VideoFileModel.unscoped(),
 239         required: true
 240       })
 241     }
 242
 243     if (options.videoPlaylistId) {
 244       query.include.push({
 245         model: VideoPlaylistElementModel.unscoped(),
 246         required: true,
 247         where: {
 248           videoPlaylistId: options.videoPlaylistId
 249         }
 250       })
 251     }
 252
 253     return query
 254   },
 255   [ ScopeNames.AVAILABLE_FOR_LIST_IDS ]: (options: AvailableForListIDsOptions) => {
 256     const query: IFindOptions<VideoModel> = {
 257       raw: true,
 258       attributes: [ 'id' ],
 259       where: {
 260         id: {
 261           [ Sequelize.Op.and ]: [
 262             {
 263               [ Sequelize.Op.notIn ]: Sequelize.literal(
 264                 '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
 265               )
 266             }
 267           ]
 268         },
 269         channelId: {
 270           [ Sequelize.Op.notIn ]: Sequelize.literal(
 271             '(' +
 272               'SELECT id FROM "videoChannel" WHERE "accountId" IN (' +
 273                 buildBlockedAccountSQL(options.serverAccountId, options.user ? options.user.Account.id : undefined) +
 274               ')' +
 275             ')'
 276           )
 277         }
 278       },
 279       include: []
 280     }
 281
 282     // Only list public/published videos
 283     if (!options.filter || options.filter !== 'all-local') {
 284       const privacyWhere = {
 285         // Always list public videos
 286         privacy: VideoPrivacy.PUBLIC,
 287         // Always list published videos, or videos that are being transcoded but on which we don't want to wait for transcoding
 288         [ Sequelize.Op.or ]: [
 289           {
 290             state: VideoState.PUBLISHED
 291           },
 292           {
 293             [ Sequelize.Op.and ]: {
 294               state: VideoState.TO_TRANSCODE,
 295               waitTranscoding: false
 296             }
 297           }
 298         ]
 299       }
 300
 301       Object.assign(query.where, privacyWhere)
 302     }
 303
 304     if (options.videoPlaylistId) {
 305       query.include.push({
 306         attributes: [],
 307         model: VideoPlaylistElementModel.unscoped(),
 308         required: true,
 309         where: {
 310           videoPlaylistId: options.videoPlaylistId
 311         }
 312       })
 313
 314       query.subQuery = false
 315     }
 316
 317     if (options.filter || options.accountId || options.videoChannelId) {
 318       const videoChannelInclude: IIncludeOptions = {
 319         attributes: [],
 320         model: VideoChannelModel.unscoped(),
 321         required: true
 322       }
 323
 324       if (options.videoChannelId) {
 325         videoChannelInclude.where = {
 326           id: options.videoChannelId
 327         }
 328       }
 329
 330       if (options.filter || options.accountId) {
 331         const accountInclude: IIncludeOptions = {
 332           attributes: [],
 333           model: AccountModel.unscoped(),
 334           required: true
 335         }
 336
 337         if (options.filter) {
 338           accountInclude.include = [
 339             {
 340               attributes: [],
 341               model: ActorModel.unscoped(),
 342               required: true,
 343               where: VideoModel.buildActorWhereWithFilter(options.filter)
 344             }
 345           ]
 346         }
 347
 348         if (options.accountId) {
 349           accountInclude.where = { id: options.accountId }
 350         }
 351
 352         videoChannelInclude.include = [ accountInclude ]
 353       }
 354
 355       query.include.push(videoChannelInclude)
 356     }
 357
 358     if (options.followerActorId) {
 359       let localVideosReq = ''
 360       if (options.includeLocalVideos === true) {
 361         localVideosReq = ' UNION ALL ' +
 362           'SELECT "video"."id" AS "id" FROM "video" ' +
 363           'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 364           'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
 365           'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
 366           'WHERE "actor"."serverId" IS NULL'
 367       }
 368
 369       // Force actorId to be a number to avoid SQL injections
 370       const actorIdNumber = parseInt(options.followerActorId.toString(), 10)
 371       query.where[ 'id' ][ Sequelize.Op.and ].push({
 372         [ Sequelize.Op.in ]: Sequelize.literal(
 373           '(' +
 374           'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
 375           'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
 376           'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
 377           ' UNION ALL ' +
 378           'SELECT "video"."id" AS "id" FROM "video" ' +
 379           'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 380           'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
 381           'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
 382           'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
 383           'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
 384           localVideosReq +
 385           ')'
 386         )
 387       })
 388     }
 389
 390     if (options.withFiles === true) {
 391       query.where[ 'id' ][ Sequelize.Op.and ].push({
 392         [ Sequelize.Op.in ]: Sequelize.literal(
 393           '(SELECT "videoId" FROM "videoFile")'
 394         )
 395       })
 396     }
 397
 398     // FIXME: issues with sequelize count when making a join on n:m relation, so we just make a IN()
 399     if (options.tagsAllOf || options.tagsOneOf) {
 400       const createTagsIn = (tags: string[]) => {
 401         return tags.map(t => VideoModel.sequelize.escape(t))
 402                    .join(', ')
 403       }
 404
 405       if (options.tagsOneOf) {
 406         query.where[ 'id' ][ Sequelize.Op.and ].push({
 407           [ Sequelize.Op.in ]: Sequelize.literal(
 408             '(' +
 409             'SELECT "videoId" FROM "videoTag" ' +
 410             'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
 411             'WHERE "tag"."name" IN (' + createTagsIn(options.tagsOneOf) + ')' +
 412             ')'
 413           )
 414         })
 415       }
 416
 417       if (options.tagsAllOf) {
 418         query.where[ 'id' ][ Sequelize.Op.and ].push({
 419           [ Sequelize.Op.in ]: Sequelize.literal(
 420             '(' +
 421             'SELECT "videoId" FROM "videoTag" ' +
 422             'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
 423             'WHERE "tag"."name" IN (' + createTagsIn(options.tagsAllOf) + ')' +
 424             'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + options.tagsAllOf.length +
 425             ')'
 426           )
 427         })
 428       }
 429     }
 430
 431     if (options.nsfw === true || options.nsfw === false) {
 432       query.where[ 'nsfw' ] = options.nsfw
 433     }
 434
 435     if (options.categoryOneOf) {
 436       query.where[ 'category' ] = {
 437         [ Sequelize.Op.or ]: options.categoryOneOf
 438       }
 439     }
 440
 441     if (options.licenceOneOf) {
 442       query.where[ 'licence' ] = {
 443         [ Sequelize.Op.or ]: options.licenceOneOf
 444       }
 445     }
 446
 447     if (options.languageOneOf) {
 448       query.where[ 'language' ] = {
 449         [ Sequelize.Op.or ]: options.languageOneOf
 450       }
 451     }
 452
 453     if (options.trendingDays) {
 454       query.include.push(VideoModel.buildTrendingQuery(options.trendingDays))
 455
 456       query.subQuery = false
 457     }
 458
 459     if (options.historyOfUser) {
 460       query.include.push({
 461         model: UserVideoHistoryModel,
 462         required: true,
 463         where: {
 464           userId: options.historyOfUser.id
 465         }
 466       })
 467
 468       // Even if the relation is n:m, we know that a user only have 0..1 video history
 469       // So we won't have multiple rows for the same video
 470       // Without this, we would not be able to sort on "updatedAt" column of UserVideoHistoryModel
 471       query.subQuery = false
 472     }
 473
 474     return query
 475   },
 476   [ ScopeNames.WITH_USER_ID ]: {
 477     include: [
 478       {
 479         attributes: [ 'accountId' ],
 480         model: () => VideoChannelModel.unscoped(),
 481         required: true,
 482         include: [
 483           {
 484             attributes: [ 'userId' ],
 485             model: () => AccountModel.unscoped(),
 486             required: true
 487           }
 488         ]
 489       }
 490     ]
 491   },
 492   [ ScopeNames.WITH_ACCOUNT_DETAILS ]: {
 493     include: [
 494       {
 495         model: () => VideoChannelModel.unscoped(),
 496         required: true,
 497         include: [
 498           {
 499             attributes: {
 500               exclude: [ 'privateKey', 'publicKey' ]
 501             },
 502             model: () => ActorModel.unscoped(),
 503             required: true,
 504             include: [
 505               {
 506                 attributes: [ 'host' ],
 507                 model: () => ServerModel.unscoped(),
 508                 required: false
 509               },
 510               {
 511                 model: () => AvatarModel.unscoped(),
 512                 required: false
 513               }
 514             ]
 515           },
 516           {
 517             model: () => AccountModel.unscoped(),
 518             required: true,
 519             include: [
 520               {
 521                 model: () => ActorModel.unscoped(),
 522                 attributes: {
 523                   exclude: [ 'privateKey', 'publicKey' ]
 524                 },
 525                 required: true,
 526                 include: [
 527                   {
 528                     attributes: [ 'host' ],
 529                     model: () => ServerModel.unscoped(),
 530                     required: false
 531                   },
 532                   {
 533                     model: () => AvatarModel.unscoped(),
 534                     required: false
 535                   }
 536                 ]
 537               }
 538             ]
 539           }
 540         ]
 541       }
 542     ]
 543   },
 544   [ ScopeNames.WITH_TAGS ]: {
 545     include: [ () => TagModel ]
 546   },
 547   [ ScopeNames.WITH_BLACKLISTED ]: {
 548     include: [
 549       {
 550         attributes: [ 'id', 'reason' ],
 551         model: () => VideoBlacklistModel,
 552         required: false
 553       }
 554     ]
 555   },
 556   [ ScopeNames.WITH_FILES ]: (withRedundancies = false) => {
 557     let subInclude: any[] = []
 558
 559     if (withRedundancies === true) {
 560       subInclude = [
 561         {
 562           attributes: [ 'fileUrl' ],
 563           model: VideoRedundancyModel.unscoped(),
 564           required: false
 565         }
 566       ]
 567     }
 568
 569     return {
 570       include: [
 571         {
 572           model: VideoFileModel.unscoped(),
 573           // FIXME: typings
 574           [ 'separate' as any ]: true, // We may have multiple files, having multiple redundancies so let's separate this join
 575           required: false,
 576           include: subInclude
 577         }
 578       ]
 579     }
 580   },
 581   [ ScopeNames.WITH_STREAMING_PLAYLISTS ]: (withRedundancies = false) => {
 582     let subInclude: any[] = []
 583
 584     if (withRedundancies === true) {
 585       subInclude = [
 586         {
 587           attributes: [ 'fileUrl' ],
 588           model: VideoRedundancyModel.unscoped(),
 589           required: false
 590         }
 591       ]
 592     }
 593
 594     return {
 595       include: [
 596         {
 597           model: VideoStreamingPlaylistModel.unscoped(),
 598           // FIXME: typings
 599           [ 'separate' as any ]: true, // We may have multiple streaming playlists, having multiple redundancies so let's separate this join
 600           required: false,
 601           include: subInclude
 602         }
 603       ]
 604     }
 605   },
 606   [ ScopeNames.WITH_SCHEDULED_UPDATE ]: {
 607     include: [
 608       {
 609         model: () => ScheduleVideoUpdateModel.unscoped(),
 610         required: false
 611       }
 612     ]
 613   },
 614   [ ScopeNames.WITH_USER_HISTORY ]: (userId: number) => {
 615     return {
 616       include: [
 617         {
 618           attributes: [ 'currentTime' ],
 619           model: UserVideoHistoryModel.unscoped(),
 620           required: false,
 621           where: {
 622             userId
 623           }
 624         }
 625       ]
 626     }
 627   }
 628 })
 629 @Table({
 630   tableName: 'video',
 631   indexes
 632 })
 633 export class VideoModel extends Model<VideoModel> {
 634
 635   @AllowNull(false)
 636   @Default(DataType.UUIDV4)
 637   @IsUUID(4)
 638   @Column(DataType.UUID)
 639   uuid: string
 640
 641   @AllowNull(false)
 642   @Is('VideoName', value => throwIfNotValid(value, isVideoNameValid, 'name'))
 643   @Column
 644   name: string
 645
 646   @AllowNull(true)
 647   @Default(null)
 648   @Is('VideoCategory', value => throwIfNotValid(value, isVideoCategoryValid, 'category'))
 649   @Column
 650   category: number
 651
 652   @AllowNull(true)
 653   @Default(null)
 654   @Is('VideoLicence', value => throwIfNotValid(value, isVideoLicenceValid, 'licence'))
 655   @Column
 656   licence: number
 657
 658   @AllowNull(true)
 659   @Default(null)
 660   @Is('VideoLanguage', value => throwIfNotValid(value, isVideoLanguageValid, 'language'))
 661   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEOS.LANGUAGE.max))
 662   language: string
 663
 664   @AllowNull(false)
 665   @Is('VideoPrivacy', value => throwIfNotValid(value, isVideoPrivacyValid, 'privacy'))
 666   @Column
 667   privacy: number
 668
 669   @AllowNull(false)
 670   @Is('VideoNSFW', value => throwIfNotValid(value, isBooleanValid, 'NSFW boolean'))
 671   @Column
 672   nsfw: boolean
 673
 674   @AllowNull(true)
 675   @Default(null)
 676   @Is('VideoDescription', value => throwIfNotValid(value, isVideoDescriptionValid, 'description'))
 677   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEOS.DESCRIPTION.max))
 678   description: string
 679
 680   @AllowNull(true)
 681   @Default(null)
 682   @Is('VideoSupport', value => throwIfNotValid(value, isVideoSupportValid, 'support'))
 683   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEOS.SUPPORT.max))
 684   support: string
 685
 686   @AllowNull(false)
 687   @Is('VideoDuration', value => throwIfNotValid(value, isVideoDurationValid, 'duration'))
 688   @Column
 689   duration: number
 690
 691   @AllowNull(false)
 692   @Default(0)
 693   @IsInt
 694   @Min(0)
 695   @Column
 696   views: number
 697
 698   @AllowNull(false)
 699   @Default(0)
 700   @IsInt
 701   @Min(0)
 702   @Column
 703   likes: number
 704
 705   @AllowNull(false)
 706   @Default(0)
 707   @IsInt
 708   @Min(0)
 709   @Column
 710   dislikes: number
 711
 712   @AllowNull(false)
 713   @Column
 714   remote: boolean
 715
 716   @AllowNull(false)
 717   @Is('VideoUrl', value => throwIfNotValid(value, isActivityPubUrlValid, 'url'))
 718   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEOS.URL.max))
 719   url: string
 720
 721   @AllowNull(false)
 722   @Column
 723   commentsEnabled: boolean
 724
 725   @AllowNull(false)
 726   @Column
 727   downloadEnabled: boolean
 728
 729   @AllowNull(false)
 730   @Column
 731   waitTranscoding: boolean
 732
 733   @AllowNull(false)
 734   @Default(null)
 735   @Is('VideoState', value => throwIfNotValid(value, isVideoStateValid, 'state'))
 736   @Column
 737   state: VideoState
 738
 739   @CreatedAt
 740   createdAt: Date
 741
 742   @UpdatedAt
 743   updatedAt: Date
 744
 745   @AllowNull(false)
 746   @Default(Sequelize.NOW)
 747   @Column
 748   publishedAt: Date
 749
 750   @AllowNull(true)
 751   @Default(null)
 752   @Column
 753   originallyPublishedAt: Date
 754
 755   @ForeignKey(() => VideoChannelModel)
 756   @Column
 757   channelId: number
 758
 759   @BelongsTo(() => VideoChannelModel, {
 760     foreignKey: {
 761       allowNull: true
 762     },
 763     hooks: true
 764   })
 765   VideoChannel: VideoChannelModel
 766
 767   @BelongsToMany(() => TagModel, {
 768     foreignKey: 'videoId',
 769     through: () => VideoTagModel,
 770     onDelete: 'CASCADE'
 771   })
 772   Tags: TagModel[]
 773
 774   @HasMany(() => VideoPlaylistElementModel, {
 775     foreignKey: {
 776       name: 'videoId',
 777       allowNull: false
 778     },
 779     onDelete: 'cascade'
 780   })
 781   VideoPlaylistElements: VideoPlaylistElementModel[]
 782
 783   @HasMany(() => VideoAbuseModel, {
 784     foreignKey: {
 785       name: 'videoId',
 786       allowNull: false
 787     },
 788     onDelete: 'cascade'
 789   })
 790   VideoAbuses: VideoAbuseModel[]
 791
 792   @HasMany(() => VideoFileModel, {
 793     foreignKey: {
 794       name: 'videoId',
 795       allowNull: false
 796     },
 797     hooks: true,
 798     onDelete: 'cascade'
 799   })
 800   VideoFiles: VideoFileModel[]
 801
 802   @HasMany(() => VideoStreamingPlaylistModel, {
 803     foreignKey: {
 804       name: 'videoId',
 805       allowNull: false
 806     },
 807     hooks: true,
 808     onDelete: 'cascade'
 809   })
 810   VideoStreamingPlaylists: VideoStreamingPlaylistModel[]
 811
 812   @HasMany(() => VideoShareModel, {
 813     foreignKey: {
 814       name: 'videoId',
 815       allowNull: false
 816     },
 817     onDelete: 'cascade'
 818   })
 819   VideoShares: VideoShareModel[]
 820
 821   @HasMany(() => AccountVideoRateModel, {
 822     foreignKey: {
 823       name: 'videoId',
 824       allowNull: false
 825     },
 826     onDelete: 'cascade'
 827   })
 828   AccountVideoRates: AccountVideoRateModel[]
 829
 830   @HasMany(() => VideoCommentModel, {
 831     foreignKey: {
 832       name: 'videoId',
 833       allowNull: false
 834     },
 835     onDelete: 'cascade',
 836     hooks: true
 837   })
 838   VideoComments: VideoCommentModel[]
 839
 840   @HasMany(() => VideoViewModel, {
 841     foreignKey: {
 842       name: 'videoId',
 843       allowNull: false
 844     },
 845     onDelete: 'cascade'
 846   })
 847   VideoViews: VideoViewModel[]
 848
 849   @HasMany(() => UserVideoHistoryModel, {
 850     foreignKey: {
 851       name: 'videoId',
 852       allowNull: false
 853     },
 854     onDelete: 'cascade'
 855   })
 856   UserVideoHistories: UserVideoHistoryModel[]
 857
 858   @HasOne(() => ScheduleVideoUpdateModel, {
 859     foreignKey: {
 860       name: 'videoId',
 861       allowNull: false
 862     },
 863     onDelete: 'cascade'
 864   })
 865   ScheduleVideoUpdate: ScheduleVideoUpdateModel
 866
 867   @HasOne(() => VideoBlacklistModel, {
 868     foreignKey: {
 869       name: 'videoId',
 870       allowNull: false
 871     },
 872     onDelete: 'cascade'
 873   })
 874   VideoBlacklist: VideoBlacklistModel
 875
 876   @HasOne(() => VideoImportModel, {
 877     foreignKey: {
 878       name: 'videoId',
 879       allowNull: true
 880     },
 881     onDelete: 'set null'
 882   })
 883   VideoImport: VideoImportModel
 884
 885   @HasMany(() => VideoCaptionModel, {
 886     foreignKey: {
 887       name: 'videoId',
 888       allowNull: false
 889     },
 890     onDelete: 'cascade',
 891     hooks: true,
 892     [ 'separate' as any ]: true
 893   })
 894   VideoCaptions: VideoCaptionModel[]
 895
 896   @BeforeDestroy
 897   static async sendDelete (instance: VideoModel, options) {
 898     if (instance.isOwned()) {
 899       if (!instance.VideoChannel) {
 900         instance.VideoChannel = await instance.$get('VideoChannel', {
 901           include: [
 902             {
 903               model: AccountModel,
 904               include: [ ActorModel ]
 905             }
 906           ],
 907           transaction: options.transaction
 908         }) as VideoChannelModel
 909       }
 910
 911       return sendDeleteVideo(instance, options.transaction)
 912     }
 913
 914     return undefined
 915   }
 916
 917   @BeforeDestroy
 918   static async removeFiles (instance: VideoModel) {
 919     const tasks: Promise<any>[] = []
 920
 921     logger.info('Removing files of video %s.', instance.url)
 922
 923     tasks.push(instance.removeThumbnail())
 924
 925     if (instance.isOwned()) {
 926       if (!Array.isArray(instance.VideoFiles)) {
 927         instance.VideoFiles = await instance.$get('VideoFiles') as VideoFileModel[]
 928       }
 929
 930       tasks.push(instance.removePreview())
 931
 932       // Remove physical files and torrents
 933       instance.VideoFiles.forEach(file => {
 934         tasks.push(instance.removeFile(file))
 935         tasks.push(instance.removeTorrent(file))
 936       })
 937
 938       // Remove playlists file
 939       tasks.push(instance.removeStreamingPlaylist())
 940     }
 941
 942     // Do not wait video deletion because we could be in a transaction
 943     Promise.all(tasks)
 944            .catch(err => {
 945              logger.error('Some errors when removing files of video %s in before destroy hook.', instance.uuid, { err })
 946            })
 947
 948     return undefined
 949   }
 950
 951   static listLocal () {
 952     const query = {
 953       where: {
 954         remote: false
 955       }
 956     }
 957
 958     return VideoModel.scope([ ScopeNames.WITH_FILES, ScopeNames.WITH_STREAMING_PLAYLISTS ]).findAll(query)
 959   }
 960
 961   static listAllAndSharedByActorForOutbox (actorId: number, start: number, count: number) {
 962     function getRawQuery (select: string) {
 963       const queryVideo = 'SELECT ' + select + ' FROM "video" AS "Video" ' +
 964         'INNER JOIN "videoChannel" AS "VideoChannel" ON "VideoChannel"."id" = "Video"."channelId" ' +
 965         'INNER JOIN "account" AS "Account" ON "Account"."id" = "VideoChannel"."accountId" ' +
 966         'WHERE "Account"."actorId" = ' + actorId
 967       const queryVideoShare = 'SELECT ' + select + ' FROM "videoShare" AS "VideoShare" ' +
 968         'INNER JOIN "video" AS "Video" ON "Video"."id" = "VideoShare"."videoId" ' +
 969         'WHERE "VideoShare"."actorId" = ' + actorId
 970
 971       return `(${queryVideo}) UNION (${queryVideoShare})`
 972     }
 973
 974     const rawQuery = getRawQuery('"Video"."id"')
 975     const rawCountQuery = getRawQuery('COUNT("Video"."id") as "total"')
 976
 977     const query = {
 978       distinct: true,
 979       offset: start,
 980       limit: count,
 981       order: getVideoSort('createdAt', [ 'Tags', 'name', 'ASC' ]),
 982       where: {
 983         id: {
 984           [ Sequelize.Op.in ]: Sequelize.literal('(' + rawQuery + ')')
 985         },
 986         [ Sequelize.Op.or ]: [
 987           { privacy: VideoPrivacy.PUBLIC },
 988           { privacy: VideoPrivacy.UNLISTED }
 989         ]
 990       },
 991       include: [
 992         {
 993           attributes: [ 'language' ],
 994           model: VideoCaptionModel.unscoped(),
 995           required: false
 996         },
 997         {
 998           attributes: [ 'id', 'url' ],
 999           model: VideoShareModel.unscoped(),
1000           required: false,
1001           // We only want videos shared by this actor
1002           where: {
1003             [ Sequelize.Op.and ]: [
1004               {
1005                 id: {
1006                   [ Sequelize.Op.not ]: null
1007                 }
1008               },
1009               {
1010                 actorId
1011               }
1012             ]
1013           },
1014           include: [
1015             {
1016               attributes: [ 'id', 'url' ],
1017               model: ActorModel.unscoped()
1018             }
1019           ]
1020         },
1021         {
1022           model: VideoChannelModel.unscoped(),
1023           required: true,
1024           include: [
1025             {
1026               attributes: [ 'name' ],
1027               model: AccountModel.unscoped(),
1028               required: true,
1029               include: [
1030                 {
1031                   attributes: [ 'id', 'url', 'followersUrl' ],
1032                   model: ActorModel.unscoped(),
1033                   required: true
1034                 }
1035               ]
1036             },
1037             {
1038               attributes: [ 'id', 'url', 'followersUrl' ],
1039               model: ActorModel.unscoped(),
1040               required: true
1041             }
1042           ]
1043         },
1044         VideoFileModel,
1045         TagModel
1046       ]
1047     }
1048
1049     return Bluebird.all([
1050       // FIXME: typing issue
1051       VideoModel.findAll(query as any),
1052       VideoModel.sequelize.query(rawCountQuery, { type: Sequelize.QueryTypes.SELECT })
1053     ]).then(([ rows, totals ]) => {
1054       // totals: totalVideos + totalVideoShares
1055       let totalVideos = 0
1056       let totalVideoShares = 0
1057       if (totals[ 0 ]) totalVideos = parseInt(totals[ 0 ].total, 10)
1058       if (totals[ 1 ]) totalVideoShares = parseInt(totals[ 1 ].total, 10)
1059
1060       const total = totalVideos + totalVideoShares
1061       return {
1062         data: rows,
1063         total: total
1064       }
1065     })
1066   }
1067
1068   static listUserVideosForApi (accountId: number, start: number, count: number, sort: string, withFiles = false) {
1069     const query: IFindOptions<VideoModel> = {
1070       offset: start,
1071       limit: count,
1072       order: getVideoSort(sort),
1073       include: [
1074         {
1075           model: VideoChannelModel,
1076           required: true,
1077           include: [
1078             {
1079               model: AccountModel,
1080               where: {
1081                 id: accountId
1082               },
1083               required: true
1084             }
1085           ]
1086         },
1087         {
1088           model: ScheduleVideoUpdateModel,
1089           required: false
1090         },
1091         {
1092           model: VideoBlacklistModel,
1093           required: false
1094         }
1095       ]
1096     }
1097
1098     if (withFiles === true) {
1099       query.include.push({
1100         model: VideoFileModel.unscoped(),
1101         required: true
1102       })
1103     }
1104
1105     return VideoModel.findAndCountAll(query).then(({ rows, count }) => {
1106       return {
1107         data: rows,
1108         total: count
1109       }
1110     })
1111   }
1112
1113   static async listForApi (options: {
1114     start: number,
1115     count: number,
1116     sort: string,
1117     nsfw: boolean,
1118     includeLocalVideos: boolean,
1119     withFiles: boolean,
1120     categoryOneOf?: number[],
1121     licenceOneOf?: number[],
1122     languageOneOf?: string[],
1123     tagsOneOf?: string[],
1124     tagsAllOf?: string[],
1125     filter?: VideoFilter,
1126     accountId?: number,
1127     videoChannelId?: number,
1128     followerActorId?: number
1129     videoPlaylistId?: number,
1130     trendingDays?: number,
1131     user?: UserModel,
1132     historyOfUser?: UserModel
1133   }, countVideos = true) {
1134     if (options.filter && options.filter === 'all-local' && !options.user.hasRight(UserRight.SEE_ALL_VIDEOS)) {
1135       throw new Error('Try to filter all-local but no user has not the see all videos right')
1136     }
1137
1138     const query: IFindOptions<VideoModel> = {
1139       offset: options.start,
1140       limit: options.count,
1141       order: getVideoSort(options.sort)
1142     }
1143
1144     let trendingDays: number
1145     if (options.sort.endsWith('trending')) {
1146       trendingDays = CONFIG.TRENDING.VIDEOS.INTERVAL_DAYS
1147
1148       query.group = 'VideoModel.id'
1149     }
1150
1151     const serverActor = await getServerActor()
1152
1153     // followerActorId === null has a meaning, so just check undefined
1154     const followerActorId = options.followerActorId !== undefined ? options.followerActorId : serverActor.id
1155
1156     const queryOptions = {
1157       followerActorId,
1158       serverAccountId: serverActor.Account.id,
1159       nsfw: options.nsfw,
1160       categoryOneOf: options.categoryOneOf,
1161       licenceOneOf: options.licenceOneOf,
1162       languageOneOf: options.languageOneOf,
1163       tagsOneOf: options.tagsOneOf,
1164       tagsAllOf: options.tagsAllOf,
1165       filter: options.filter,
1166       withFiles: options.withFiles,
1167       accountId: options.accountId,
1168       videoChannelId: options.videoChannelId,
1169       videoPlaylistId: options.videoPlaylistId,
1170       includeLocalVideos: options.includeLocalVideos,
1171       user: options.user,
1172       historyOfUser: options.historyOfUser,
1173       trendingDays
1174     }
1175
1176     return VideoModel.getAvailableForApi(query, queryOptions, countVideos)
1177   }
1178
1179   static async searchAndPopulateAccountAndServer (options: {
1180     includeLocalVideos: boolean
1181     search?: string
1182     start?: number
1183     count?: number
1184     sort?: string
1185     startDate?: string // ISO 8601
1186     endDate?: string // ISO 8601
1187     originallyPublishedStartDate?: string
1188     originallyPublishedEndDate?: string
1189     nsfw?: boolean
1190     categoryOneOf?: number[]
1191     licenceOneOf?: number[]
1192     languageOneOf?: string[]
1193     tagsOneOf?: string[]
1194     tagsAllOf?: string[]
1195     durationMin?: number // seconds
1196     durationMax?: number // seconds
1197     user?: UserModel,
1198     filter?: VideoFilter
1199   }) {
1200     const whereAnd = []
1201
1202     if (options.startDate || options.endDate) {
1203       const publishedAtRange = {}
1204
1205       if (options.startDate) publishedAtRange[ Sequelize.Op.gte ] = options.startDate
1206       if (options.endDate) publishedAtRange[ Sequelize.Op.lte ] = options.endDate
1207
1208       whereAnd.push({ publishedAt: publishedAtRange })
1209     }
1210
1211     if (options.originallyPublishedStartDate || options.originallyPublishedEndDate) {
1212       const originallyPublishedAtRange = {}
1213
1214       if (options.originallyPublishedStartDate) originallyPublishedAtRange[ Sequelize.Op.gte ] = options.originallyPublishedStartDate
1215       if (options.originallyPublishedEndDate) originallyPublishedAtRange[ Sequelize.Op.lte ] = options.originallyPublishedEndDate
1216
1217       whereAnd.push({ originallyPublishedAt: originallyPublishedAtRange })
1218     }
1219
1220     if (options.durationMin || options.durationMax) {
1221       const durationRange = {}
1222
1223       if (options.durationMin) durationRange[ Sequelize.Op.gte ] = options.durationMin
1224       if (options.durationMax) durationRange[ Sequelize.Op.lte ] = options.durationMax
1225
1226       whereAnd.push({ duration: durationRange })
1227     }
1228
1229     const attributesInclude = []
1230     const escapedSearch = VideoModel.sequelize.escape(options.search)
1231     const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
1232     if (options.search) {
1233       whereAnd.push(
1234         {
1235           id: {
1236             [ Sequelize.Op.in ]: Sequelize.literal(
1237               '(' +
1238               'SELECT "video"."id" FROM "video" ' +
1239               'WHERE ' +
1240               'lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' +
1241               'lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' +
1242               'UNION ALL ' +
1243               'SELECT "video"."id" FROM "video" LEFT JOIN "videoTag" ON "videoTag"."videoId" = "video"."id" ' +
1244               'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
1245               'WHERE "tag"."name" = ' + escapedSearch +
1246               ')'
1247             )
1248           }
1249         }
1250       )
1251
1252       attributesInclude.push(createSimilarityAttribute('VideoModel.name', options.search))
1253     }
1254
1255     // Cannot search on similarity if we don't have a search
1256     if (!options.search) {
1257       attributesInclude.push(
1258         Sequelize.literal('0 as similarity')
1259       )
1260     }
1261
1262     const query: IFindOptions<VideoModel> = {
1263       attributes: {
1264         include: attributesInclude
1265       },
1266       offset: options.start,
1267       limit: options.count,
1268       order: getVideoSort(options.sort),
1269       where: {
1270         [ Sequelize.Op.and ]: whereAnd
1271       }
1272     }
1273
1274     const serverActor = await getServerActor()
1275     const queryOptions = {
1276       followerActorId: serverActor.id,
1277       serverAccountId: serverActor.Account.id,
1278       includeLocalVideos: options.includeLocalVideos,
1279       nsfw: options.nsfw,
1280       categoryOneOf: options.categoryOneOf,
1281       licenceOneOf: options.licenceOneOf,
1282       languageOneOf: options.languageOneOf,
1283       tagsOneOf: options.tagsOneOf,
1284       tagsAllOf: options.tagsAllOf,
1285       user: options.user,
1286       filter: options.filter
1287     }
1288
1289     return VideoModel.getAvailableForApi(query, queryOptions)
1290   }
1291
1292   static load (id: number | string, t?: Sequelize.Transaction) {
1293     const where = buildWhereIdOrUUID(id)
1294     const options = {
1295       where,
1296       transaction: t
1297     }
1298
1299     return VideoModel.findOne(options)
1300   }
1301
1302   static loadWithRights (id: number | string, t?: Sequelize.Transaction) {
1303     const where = buildWhereIdOrUUID(id)
1304     const options = {
1305       where,
1306       transaction: t
1307     }
1308
1309     return VideoModel.scope([ ScopeNames.WITH_BLACKLISTED, ScopeNames.WITH_USER_ID ]).findOne(options)
1310   }
1311
1312   static loadOnlyId (id: number | string, t?: Sequelize.Transaction) {
1313     const where = buildWhereIdOrUUID(id)
1314
1315     const options = {
1316       attributes: [ 'id' ],
1317       where,
1318       transaction: t
1319     }
1320
1321     return VideoModel.findOne(options)
1322   }
1323
1324   static loadWithFiles (id: number, t?: Sequelize.Transaction, logging?: boolean) {
1325     return VideoModel.scope([ ScopeNames.WITH_FILES, ScopeNames.WITH_STREAMING_PLAYLISTS ])
1326                      .findByPk(id, { transaction: t, logging })
1327   }
1328
1329   static loadByUUIDWithFile (uuid: string) {
1330     const options = {
1331       where: {
1332         uuid
1333       }
1334     }
1335
1336     return VideoModel.findOne(options)
1337   }
1338
1339   static loadByUrl (url: string, transaction?: Sequelize.Transaction) {
1340     const query: IFindOptions<VideoModel> = {
1341       where: {
1342         url
1343       },
1344       transaction
1345     }
1346
1347     return VideoModel.findOne(query)
1348   }
1349
1350   static loadByUrlAndPopulateAccount (url: string, transaction?: Sequelize.Transaction) {
1351     const query: IFindOptions<VideoModel> = {
1352       where: {
1353         url
1354       },
1355       transaction
1356     }
1357
1358     return VideoModel.scope([
1359       ScopeNames.WITH_ACCOUNT_DETAILS,
1360       ScopeNames.WITH_FILES,
1361       ScopeNames.WITH_STREAMING_PLAYLISTS
1362     ]).findOne(query)
1363   }
1364
1365   static loadAndPopulateAccountAndServerAndTags (id: number | string, t?: Sequelize.Transaction, userId?: number) {
1366     const where = buildWhereIdOrUUID(id)
1367
1368     const options = {
1369       order: [ [ 'Tags', 'name', 'ASC' ] ],
1370       where,
1371       transaction: t
1372     }
1373
1374     const scopes = [
1375       ScopeNames.WITH_TAGS,
1376       ScopeNames.WITH_BLACKLISTED,
1377       ScopeNames.WITH_ACCOUNT_DETAILS,
1378       ScopeNames.WITH_SCHEDULED_UPDATE,
1379       ScopeNames.WITH_FILES,
1380       ScopeNames.WITH_STREAMING_PLAYLISTS
1381     ]
1382
1383     if (userId) {
1384       scopes.push({ method: [ ScopeNames.WITH_USER_HISTORY, userId ] } as any) // FIXME: typings
1385     }
1386
1387     return VideoModel
1388       .scope(scopes)
1389       .findOne(options)
1390   }
1391
1392   static loadForGetAPI (id: number | string, t?: Sequelize.Transaction, userId?: number) {
1393     const where = buildWhereIdOrUUID(id)
1394
1395     const options = {
1396       order: [ [ 'Tags', 'name', 'ASC' ] ],
1397       where,
1398       transaction: t
1399     }
1400
1401     const scopes = [
1402       ScopeNames.WITH_TAGS,
1403       ScopeNames.WITH_BLACKLISTED,
1404       ScopeNames.WITH_ACCOUNT_DETAILS,
1405       ScopeNames.WITH_SCHEDULED_UPDATE,
1406       { method: [ ScopeNames.WITH_FILES, true ] } as any, // FIXME: typings
1407       { method: [ ScopeNames.WITH_STREAMING_PLAYLISTS, true ] } as any // FIXME: typings
1408     ]
1409
1410     if (userId) {
1411       scopes.push({ method: [ ScopeNames.WITH_USER_HISTORY, userId ] } as any) // FIXME: typings
1412     }
1413
1414     return VideoModel
1415       .scope(scopes)
1416       .findOne(options)
1417   }
1418
1419   static async getStats () {
1420     const totalLocalVideos = await VideoModel.count({
1421       where: {
1422         remote: false
1423       }
1424     })
1425     const totalVideos = await VideoModel.count()
1426
1427     let totalLocalVideoViews = await VideoModel.sum('views', {
1428       where: {
1429         remote: false
1430       }
1431     })
1432     // Sequelize could return null...
1433     if (!totalLocalVideoViews) totalLocalVideoViews = 0
1434
1435     return {
1436       totalLocalVideos,
1437       totalLocalVideoViews,
1438       totalVideos
1439     }
1440   }
1441
1442   static incrementViews (id: number, views: number) {
1443     return VideoModel.increment('views', {
1444       by: views,
1445       where: {
1446         id
1447       }
1448     })
1449   }
1450
1451   static checkVideoHasInstanceFollow (videoId: number, followerActorId: number) {
1452     // Instances only share videos
1453     const query = 'SELECT 1 FROM "videoShare" ' +
1454     'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
1455     'WHERE "actorFollow"."actorId" = $followerActorId AND "videoShare"."videoId" = $videoId ' +
1456     'LIMIT 1'
1457
1458     const options = {
1459       type: Sequelize.QueryTypes.SELECT,
1460       bind: { followerActorId, videoId },
1461       raw: true
1462     }
1463
1464     return VideoModel.sequelize.query(query, options)
1465                      .then(results => results.length === 1)
1466   }
1467
1468   // threshold corresponds to how many video the field should have to be returned
1469   static async getRandomFieldSamples (field: 'category' | 'channelId', threshold: number, count: number) {
1470     const serverActor = await getServerActor()
1471     const followerActorId = serverActor.id
1472
1473     const scopeOptions: AvailableForListIDsOptions = {
1474       serverAccountId: serverActor.Account.id,
1475       followerActorId,
1476       includeLocalVideos: true
1477     }
1478
1479     const query: IFindOptions<VideoModel> = {
1480       attributes: [ field ],
1481       limit: count,
1482       group: field,
1483       having: Sequelize.where(Sequelize.fn('COUNT', Sequelize.col(field)), {
1484         [ Sequelize.Op.gte ]: threshold
1485       }) as any, // FIXME: typings
1486       order: [ this.sequelize.random() ]
1487     }
1488
1489     return VideoModel.scope({ method: [ ScopeNames.AVAILABLE_FOR_LIST_IDS, scopeOptions ] })
1490                      .findAll(query)
1491                      .then(rows => rows.map(r => r[ field ]))
1492   }
1493
1494   static buildTrendingQuery (trendingDays: number) {
1495     return {
1496       attributes: [],
1497       subQuery: false,
1498       model: VideoViewModel,
1499       required: false,
1500       where: {
1501         startDate: {
1502           [ Sequelize.Op.gte ]: new Date(new Date().getTime() - (24 * 3600 * 1000) * trendingDays)
1503         }
1504       }
1505     }
1506   }
1507
1508   private static buildActorWhereWithFilter (filter?: VideoFilter) {
1509     if (filter && (filter === 'local' || filter === 'all-local')) {
1510       return {
1511         serverId: null
1512       }
1513     }
1514
1515     return {}
1516   }
1517
1518   private static async getAvailableForApi (
1519     query: IFindOptions<VideoModel>,
1520     options: AvailableForListIDsOptions,
1521     countVideos = true
1522   ) {
1523     const idsScope = {
1524       method: [
1525         ScopeNames.AVAILABLE_FOR_LIST_IDS, options
1526       ]
1527     }
1528
1529     // Remove trending sort on count, because it uses a group by
1530     const countOptions = Object.assign({}, options, { trendingDays: undefined })
1531     const countQuery = Object.assign({}, query, { attributes: undefined, group: undefined })
1532     const countScope = {
1533       method: [
1534         ScopeNames.AVAILABLE_FOR_LIST_IDS, countOptions
1535       ]
1536     }
1537
1538     const [ count, rowsId ] = await Promise.all([
1539       countVideos ? VideoModel.scope(countScope).count(countQuery) : Promise.resolve<number>(undefined),
1540       VideoModel.scope(idsScope).findAll(query)
1541     ])
1542     const ids = rowsId.map(r => r.id)
1543
1544     if (ids.length === 0) return { data: [], total: count }
1545
1546     const secondQuery: IFindOptions<VideoModel> = {
1547       offset: 0,
1548       limit: query.limit,
1549       attributes: query.attributes,
1550       order: [ // Keep original order
1551         Sequelize.literal(
1552           ids.map(id => `"VideoModel".id = ${id} DESC`).join(', ')
1553         )
1554       ]
1555     }
1556
1557     // FIXME: typing
1558     const apiScope: any[] = []
1559
1560     if (options.user) {
1561       apiScope.push({ method: [ ScopeNames.WITH_USER_HISTORY, options.user.id ] })
1562
1563       // Even if the relation is n:m, we know that a user only have 0..1 video history
1564       // So we won't have multiple rows for the same video
1565       // A subquery adds some bugs in our query so disable it
1566       secondQuery.subQuery = false
1567     }
1568
1569     apiScope.push({
1570       method: [
1571         ScopeNames.FOR_API, {
1572           ids,
1573           withFiles: options.withFiles,
1574           videoPlaylistId: options.videoPlaylistId
1575         } as ForAPIOptions
1576       ]
1577     })
1578
1579     const rows = await VideoModel.scope(apiScope).findAll(secondQuery)
1580
1581     return {
1582       data: rows,
1583       total: count
1584     }
1585   }
1586
1587   static getCategoryLabel (id: number) {
1588     return VIDEO_CATEGORIES[ id ] || 'Misc'
1589   }
1590
1591   static getLicenceLabel (id: number) {
1592     return VIDEO_LICENCES[ id ] || 'Unknown'
1593   }
1594
1595   static getLanguageLabel (id: string) {
1596     return VIDEO_LANGUAGES[ id ] || 'Unknown'
1597   }
1598
1599   static getPrivacyLabel (id: number) {
1600     return VIDEO_PRIVACIES[ id ] || 'Unknown'
1601   }
1602
1603   static getStateLabel (id: number) {
1604     return VIDEO_STATES[ id ] || 'Unknown'
1605   }
1606
1607   getOriginalFile () {
1608     if (Array.isArray(this.VideoFiles) === false) return undefined
1609
1610     // The original file is the file that have the higher resolution
1611     return maxBy(this.VideoFiles, file => file.resolution)
1612   }
1613
1614   getVideoFilename (videoFile: VideoFileModel) {
1615     return this.uuid + '-' + videoFile.resolution + videoFile.extname
1616   }
1617
1618   getThumbnailName () {
1619     const extension = '.jpg'
1620     return this.uuid + extension
1621   }
1622
1623   getPreviewName () {
1624     const extension = '.jpg'
1625     return this.uuid + extension
1626   }
1627
1628   getTorrentFileName (videoFile: VideoFileModel) {
1629     const extension = '.torrent'
1630     return this.uuid + '-' + videoFile.resolution + extension
1631   }
1632
1633   isOwned () {
1634     return this.remote === false
1635   }
1636
1637   createPreview (videoFile: VideoFileModel) {
1638     return generateImageFromVideoFile(
1639       this.getVideoFilePath(videoFile),
1640       CONFIG.STORAGE.PREVIEWS_DIR,
1641       this.getPreviewName(),
1642       PREVIEWS_SIZE
1643     )
1644   }
1645
1646   createThumbnail (videoFile: VideoFileModel) {
1647     return generateImageFromVideoFile(
1648       this.getVideoFilePath(videoFile),
1649       CONFIG.STORAGE.THUMBNAILS_DIR,
1650       this.getThumbnailName(),
1651       THUMBNAILS_SIZE
1652     )
1653   }
1654
1655   getTorrentFilePath (videoFile: VideoFileModel) {
1656     return join(CONFIG.STORAGE.TORRENTS_DIR, this.getTorrentFileName(videoFile))
1657   }
1658
1659   getVideoFilePath (videoFile: VideoFileModel) {
1660     return join(CONFIG.STORAGE.VIDEOS_DIR, this.getVideoFilename(videoFile))
1661   }
1662
1663   async createTorrentAndSetInfoHash (videoFile: VideoFileModel) {
1664     const options = {
1665       // Keep the extname, it's used by the client to stream the file inside a web browser
1666       name: `${this.name} ${videoFile.resolution}p${videoFile.extname}`,
1667       createdBy: 'PeerTube',
1668       announceList: [
1669         [ WEBSERVER.WS + '://' + WEBSERVER.HOSTNAME + ':' + WEBSERVER.PORT + '/tracker/socket' ],
1670         [ WEBSERVER.URL + '/tracker/announce' ]
1671       ],
1672       urlList: [ WEBSERVER.URL + STATIC_PATHS.WEBSEED + this.getVideoFilename(videoFile) ]
1673     }
1674
1675     const torrent = await createTorrentPromise(this.getVideoFilePath(videoFile), options)
1676
1677     const filePath = join(CONFIG.STORAGE.TORRENTS_DIR, this.getTorrentFileName(videoFile))
1678     logger.info('Creating torrent %s.', filePath)
1679
1680     await writeFile(filePath, torrent)
1681
1682     const parsedTorrent = parseTorrent(torrent)
1683     videoFile.infoHash = parsedTorrent.infoHash
1684   }
1685
1686   getWatchStaticPath () {
1687     return '/videos/watch/' + this.uuid
1688   }
1689
1690   getEmbedStaticPath () {
1691     return '/videos/embed/' + this.uuid
1692   }
1693
1694   getThumbnailStaticPath () {
1695     return join(STATIC_PATHS.THUMBNAILS, this.getThumbnailName())
1696   }
1697
1698   getPreviewStaticPath () {
1699     return join(STATIC_PATHS.PREVIEWS, this.getPreviewName())
1700   }
1701
1702   toFormattedJSON (options?: VideoFormattingJSONOptions): Video {
1703     return videoModelToFormattedJSON(this, options)
1704   }
1705
1706   toFormattedDetailsJSON (): VideoDetails {
1707     return videoModelToFormattedDetailsJSON(this)
1708   }
1709
1710   getFormattedVideoFilesJSON (): VideoFile[] {
1711     return videoFilesModelToFormattedJSON(this, this.VideoFiles)
1712   }
1713
1714   toActivityPubObject (): VideoTorrentObject {
1715     return videoModelToActivityPubObject(this)
1716   }
1717
1718   getTruncatedDescription () {
1719     if (!this.description) return null
1720
1721     const maxLength = CONSTRAINTS_FIELDS.VIDEOS.TRUNCATED_DESCRIPTION.max
1722     return peertubeTruncate(this.description, maxLength)
1723   }
1724
1725   getOriginalFileResolution () {
1726     const originalFilePath = this.getVideoFilePath(this.getOriginalFile())
1727
1728     return getVideoFileResolution(originalFilePath)
1729   }
1730
1731   getDescriptionAPIPath () {
1732     return `/api/${API_VERSION}/videos/${this.uuid}/description`
1733   }
1734
1735   removeThumbnail () {
1736     const thumbnailPath = join(CONFIG.STORAGE.THUMBNAILS_DIR, this.getThumbnailName())
1737     return remove(thumbnailPath)
1738       .catch(err => logger.warn('Cannot delete thumbnail %s.', thumbnailPath, { err }))
1739   }
1740
1741   removePreview () {
1742     const previewPath = join(CONFIG.STORAGE.PREVIEWS_DIR + this.getPreviewName())
1743     return remove(previewPath)
1744       .catch(err => logger.warn('Cannot delete preview %s.', previewPath, { err }))
1745   }
1746
1747   removeFile (videoFile: VideoFileModel, isRedundancy = false) {
1748     const baseDir = isRedundancy ? CONFIG.STORAGE.REDUNDANCY_DIR : CONFIG.STORAGE.VIDEOS_DIR
1749
1750     const filePath = join(baseDir, this.getVideoFilename(videoFile))
1751     return remove(filePath)
1752       .catch(err => logger.warn('Cannot delete file %s.', filePath, { err }))
1753   }
1754
1755   removeTorrent (videoFile: VideoFileModel) {
1756     const torrentPath = join(CONFIG.STORAGE.TORRENTS_DIR, this.getTorrentFileName(videoFile))
1757     return remove(torrentPath)
1758       .catch(err => logger.warn('Cannot delete torrent %s.', torrentPath, { err }))
1759   }
1760
1761   removeStreamingPlaylist (isRedundancy = false) {
1762     const baseDir = isRedundancy ? HLS_REDUNDANCY_DIRECTORY : HLS_STREAMING_PLAYLIST_DIRECTORY
1763
1764     const filePath = join(baseDir, this.uuid)
1765     return remove(filePath)
1766       .catch(err => logger.warn('Cannot delete playlist directory %s.', filePath, { err }))
1767   }
1768
1769   isOutdated () {
1770     if (this.isOwned()) return false
1771
1772     return isOutdated(this, ACTIVITY_PUB.VIDEO_REFRESH_INTERVAL)
1773   }
1774
1775   setAsRefreshed () {
1776     this.changed('updatedAt', true)
1777
1778     return this.save()
1779   }
1780
1781   getBaseUrls () {
1782     let baseUrlHttp
1783     let baseUrlWs
1784
1785     if (this.isOwned()) {
1786       baseUrlHttp = WEBSERVER.URL
1787       baseUrlWs = WEBSERVER.WS + '://' + WEBSERVER.HOSTNAME + ':' + WEBSERVER.PORT
1788     } else {
1789       baseUrlHttp = REMOTE_SCHEME.HTTP + '://' + this.VideoChannel.Account.Actor.Server.host
1790       baseUrlWs = REMOTE_SCHEME.WS + '://' + this.VideoChannel.Account.Actor.Server.host
1791     }
1792
1793     return { baseUrlHttp, baseUrlWs }
1794   }
1795
1796   generateMagnetUri (videoFile: VideoFileModel, baseUrlHttp: string, baseUrlWs: string) {
1797     const xs = this.getTorrentUrl(videoFile, baseUrlHttp)
1798     const announce = this.getTrackerUrls(baseUrlHttp, baseUrlWs)
1799     let urlList = [ this.getVideoFileUrl(videoFile, baseUrlHttp) ]
1800
1801     const redundancies = videoFile.RedundancyVideos
1802     if (isArray(redundancies)) urlList = urlList.concat(redundancies.map(r => r.fileUrl))
1803
1804     const magnetHash = {
1805       xs,
1806       announce,
1807       urlList,
1808       infoHash: videoFile.infoHash,
1809       name: this.name
1810     }
1811
1812     return magnetUtil.encode(magnetHash)
1813   }
1814
1815   getTrackerUrls (baseUrlHttp: string, baseUrlWs: string) {
1816     return [ baseUrlWs + '/tracker/socket', baseUrlHttp + '/tracker/announce' ]
1817   }
1818
1819   getThumbnailUrl (baseUrlHttp: string) {
1820     return baseUrlHttp + STATIC_PATHS.THUMBNAILS + this.getThumbnailName()
1821   }
1822
1823   getTorrentUrl (videoFile: VideoFileModel, baseUrlHttp: string) {
1824     return baseUrlHttp + STATIC_PATHS.TORRENTS + this.getTorrentFileName(videoFile)
1825   }
1826
1827   getTorrentDownloadUrl (videoFile: VideoFileModel, baseUrlHttp: string) {
1828     return baseUrlHttp + STATIC_DOWNLOAD_PATHS.TORRENTS + this.getTorrentFileName(videoFile)
1829   }
1830
1831   getVideoFileUrl (videoFile: VideoFileModel, baseUrlHttp: string) {
1832     return baseUrlHttp + STATIC_PATHS.WEBSEED + this.getVideoFilename(videoFile)
1833   }
1834
1835   getVideoRedundancyUrl (videoFile: VideoFileModel, baseUrlHttp: string) {
1836     return baseUrlHttp + STATIC_PATHS.REDUNDANCY + this.getVideoFilename(videoFile)
1837   }
1838
1839   getVideoFileDownloadUrl (videoFile: VideoFileModel, baseUrlHttp: string) {
1840     return baseUrlHttp + STATIC_DOWNLOAD_PATHS.VIDEOS + this.getVideoFilename(videoFile)
1841   }
1842
1843   getBandwidthBits (videoFile: VideoFileModel) {
1844     return Math.ceil((videoFile.size * 8) / this.duration)
1845   }
1846 }