server/models/video/video.ts

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