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