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, MVideoImmutable,
 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 import { ModelCache } from '@server/models/model-cache'
 136
 137 export enum ScopeNames {
 138   AVAILABLE_FOR_LIST_IDS = 'AVAILABLE_FOR_LIST_IDS',
 139   FOR_API = 'FOR_API',
 140   WITH_ACCOUNT_DETAILS = 'WITH_ACCOUNT_DETAILS',
 141   WITH_TAGS = 'WITH_TAGS',
 142   WITH_WEBTORRENT_FILES = 'WITH_WEBTORRENT_FILES',
 143   WITH_SCHEDULED_UPDATE = 'WITH_SCHEDULED_UPDATE',
 144   WITH_BLACKLISTED = 'WITH_BLACKLISTED',
 145   WITH_USER_HISTORY = 'WITH_USER_HISTORY',
 146   WITH_STREAMING_PLAYLISTS = 'WITH_STREAMING_PLAYLISTS',
 147   WITH_USER_ID = 'WITH_USER_ID',
 148   WITH_THUMBNAILS = 'WITH_THUMBNAILS'
 149 }
 150
 151 export type ForAPIOptions = {
 152   ids?: number[]
 153
 154   videoPlaylistId?: number
 155
 156   withFiles?: boolean
 157
 158   withAccountBlockerIds?: number[]
 159 }
 160
 161 export type AvailableForListIDsOptions = {
 162   serverAccountId: number
 163   followerActorId: number
 164   includeLocalVideos: boolean
 165
 166   attributesType?: 'none' | 'id' | 'all'
 167
 168   filter?: VideoFilter
 169   categoryOneOf?: number[]
 170   nsfw?: boolean
 171   licenceOneOf?: number[]
 172   languageOneOf?: string[]
 173   tagsOneOf?: string[]
 174   tagsAllOf?: string[]
 175
 176   withFiles?: boolean
 177
 178   accountId?: number
 179   videoChannelId?: number
 180
 181   videoPlaylistId?: number
 182
 183   trendingDays?: number
 184   user?: MUserAccountId
 185   historyOfUser?: MUserId
 186
 187   baseWhere?: WhereOptions[]
 188 }
 189
 190 @Scopes(() => ({
 191   [ScopeNames.FOR_API]: (options: ForAPIOptions) => {
 192     const query: FindOptions = {
 193       include: [
 194         {
 195           model: VideoChannelModel.scope({
 196             method: [
 197               VideoChannelScopeNames.SUMMARY, {
 198                 withAccount: true,
 199                 withAccountBlockerIds: options.withAccountBlockerIds
 200               } as SummaryOptions
 201             ]
 202           }),
 203           required: true
 204         },
 205         {
 206           attributes: [ 'type', 'filename' ],
 207           model: ThumbnailModel,
 208           required: false
 209         }
 210       ]
 211     }
 212
 213     if (options.ids) {
 214       query.where = {
 215         id: {
 216           [Op.in]: options.ids
 217         }
 218       }
 219     }
 220
 221     if (options.withFiles === true) {
 222       query.include.push({
 223         model: VideoFileModel.unscoped(),
 224         required: true
 225       })
 226     }
 227
 228     if (options.videoPlaylistId) {
 229       query.include.push({
 230         model: VideoPlaylistElementModel.unscoped(),
 231         required: true,
 232         where: {
 233           videoPlaylistId: options.videoPlaylistId
 234         }
 235       })
 236     }
 237
 238     return query
 239   },
 240   [ScopeNames.AVAILABLE_FOR_LIST_IDS]: (options: AvailableForListIDsOptions) => {
 241     const whereAnd = options.baseWhere ? [].concat(options.baseWhere) : []
 242
 243     const query: FindOptions = {
 244       raw: true,
 245       include: []
 246     }
 247
 248     const attributesType = options.attributesType || 'id'
 249
 250     if (attributesType === 'id') query.attributes = [ 'id' ]
 251     else if (attributesType === 'none') query.attributes = []
 252
 253     whereAnd.push({
 254       id: {
 255         [Op.notIn]: Sequelize.literal(
 256           '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
 257         )
 258       }
 259     })
 260
 261     if (options.serverAccountId) {
 262       whereAnd.push({
 263         channelId: {
 264           [Op.notIn]: Sequelize.literal(
 265             '(' +
 266               'SELECT id FROM "videoChannel" WHERE "accountId" IN (' +
 267                 buildBlockedAccountSQL(options.serverAccountId, options.user ? options.user.Account.id : undefined) +
 268               ')' +
 269             ')'
 270           )
 271         }
 272       })
 273     }
 274
 275     // Only list public/published videos
 276     if (!options.filter || options.filter !== 'all-local') {
 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   @BeforeDestroy
1079   static invalidateCache (instance: VideoModel) {
1080     ModelCache.Instance.invalidateCache('video', instance.id)
1081   }
1082
1083   static listLocal (): Bluebird<MVideoWithAllFiles[]> {
1084     const query = {
1085       where: {
1086         remote: false
1087       }
1088     }
1089
1090     return VideoModel.scope([
1091       ScopeNames.WITH_WEBTORRENT_FILES,
1092       ScopeNames.WITH_STREAMING_PLAYLISTS,
1093       ScopeNames.WITH_THUMBNAILS
1094     ]).findAll(query)
1095   }
1096
1097   static listAllAndSharedByActorForOutbox (actorId: number, start: number, count: number) {
1098     function getRawQuery (select: string) {
1099       const queryVideo = 'SELECT ' + select + ' FROM "video" AS "Video" ' +
1100         'INNER JOIN "videoChannel" AS "VideoChannel" ON "VideoChannel"."id" = "Video"."channelId" ' +
1101         'INNER JOIN "account" AS "Account" ON "Account"."id" = "VideoChannel"."accountId" ' +
1102         'WHERE "Account"."actorId" = ' + actorId
1103       const queryVideoShare = 'SELECT ' + select + ' FROM "videoShare" AS "VideoShare" ' +
1104         'INNER JOIN "video" AS "Video" ON "Video"."id" = "VideoShare"."videoId" ' +
1105         'WHERE "VideoShare"."actorId" = ' + actorId
1106
1107       return `(${queryVideo}) UNION (${queryVideoShare})`
1108     }
1109
1110     const rawQuery = getRawQuery('"Video"."id"')
1111     const rawCountQuery = getRawQuery('COUNT("Video"."id") as "total"')
1112
1113     const query = {
1114       distinct: true,
1115       offset: start,
1116       limit: count,
1117       order: getVideoSort('createdAt', [ 'Tags', 'name', 'ASC' ] as any), // FIXME: sequelize typings
1118       where: {
1119         id: {
1120           [Op.in]: Sequelize.literal('(' + rawQuery + ')')
1121         },
1122         [Op.or]: [
1123           { privacy: VideoPrivacy.PUBLIC },
1124           { privacy: VideoPrivacy.UNLISTED }
1125         ]
1126       },
1127       include: [
1128         {
1129           attributes: [ 'language', 'fileUrl' ],
1130           model: VideoCaptionModel.unscoped(),
1131           required: false
1132         },
1133         {
1134           attributes: [ 'id', 'url' ],
1135           model: VideoShareModel.unscoped(),
1136           required: false,
1137           // We only want videos shared by this actor
1138           where: {
1139             [Op.and]: [
1140               {
1141                 id: {
1142                   [Op.not]: null
1143                 }
1144               },
1145               {
1146                 actorId
1147               }
1148             ]
1149           },
1150           include: [
1151             {
1152               attributes: [ 'id', 'url' ],
1153               model: ActorModel.unscoped()
1154             }
1155           ]
1156         },
1157         {
1158           model: VideoChannelModel.unscoped(),
1159           required: true,
1160           include: [
1161             {
1162               attributes: [ 'name' ],
1163               model: AccountModel.unscoped(),
1164               required: true,
1165               include: [
1166                 {
1167                   attributes: [ 'id', 'url', 'followersUrl' ],
1168                   model: ActorModel.unscoped(),
1169                   required: true
1170                 }
1171               ]
1172             },
1173             {
1174               attributes: [ 'id', 'url', 'followersUrl' ],
1175               model: ActorModel.unscoped(),
1176               required: true
1177             }
1178           ]
1179         },
1180         VideoFileModel,
1181         TagModel
1182       ]
1183     }
1184
1185     return Bluebird.all([
1186       VideoModel.scope(ScopeNames.WITH_THUMBNAILS).findAll(query),
1187       VideoModel.sequelize.query<{ total: string }>(rawCountQuery, { type: QueryTypes.SELECT })
1188     ]).then(([ rows, totals ]) => {
1189       // totals: totalVideos + totalVideoShares
1190       let totalVideos = 0
1191       let totalVideoShares = 0
1192       if (totals[0]) totalVideos = parseInt(totals[0].total, 10)
1193       if (totals[1]) totalVideoShares = parseInt(totals[1].total, 10)
1194
1195       const total = totalVideos + totalVideoShares
1196       return {
1197         data: rows,
1198         total: total
1199       }
1200     })
1201   }
1202
1203   static listUserVideosForApi (
1204     accountId: number,
1205     start: number,
1206     count: number,
1207     sort: string,
1208     search?: string
1209   ) {
1210     function buildBaseQuery (): FindOptions {
1211       let baseQuery = {
1212         offset: start,
1213         limit: count,
1214         order: getVideoSort(sort),
1215         include: [
1216           {
1217             model: VideoChannelModel,
1218             required: true,
1219             include: [
1220               {
1221                 model: AccountModel,
1222                 where: {
1223                   id: accountId
1224                 },
1225                 required: true
1226               }
1227             ]
1228           }
1229         ]
1230       }
1231
1232       if (search) {
1233         baseQuery = Object.assign(baseQuery, {
1234           where: {
1235             name: {
1236               [Op.iLike]: '%' + search + '%'
1237             }
1238           }
1239         })
1240       }
1241
1242       return baseQuery
1243     }
1244
1245     const countQuery = buildBaseQuery()
1246     const findQuery = buildBaseQuery()
1247
1248     const findScopes: (string | ScopeOptions)[] = [
1249       ScopeNames.WITH_SCHEDULED_UPDATE,
1250       ScopeNames.WITH_BLACKLISTED,
1251       ScopeNames.WITH_THUMBNAILS
1252     ]
1253
1254     return Promise.all([
1255       VideoModel.count(countQuery),
1256       VideoModel.scope(findScopes).findAll<MVideoForUser>(findQuery)
1257     ]).then(([ count, rows ]) => {
1258       return {
1259         data: rows,
1260         total: count
1261       }
1262     })
1263   }
1264
1265   static async listForApi (options: {
1266     start: number
1267     count: number
1268     sort: string
1269     nsfw: boolean
1270     includeLocalVideos: boolean
1271     withFiles: boolean
1272     categoryOneOf?: number[]
1273     licenceOneOf?: number[]
1274     languageOneOf?: string[]
1275     tagsOneOf?: string[]
1276     tagsAllOf?: string[]
1277     filter?: VideoFilter
1278     accountId?: number
1279     videoChannelId?: number
1280     followerActorId?: number
1281     videoPlaylistId?: number
1282     trendingDays?: number
1283     user?: MUserAccountId
1284     historyOfUser?: MUserId
1285     countVideos?: boolean
1286   }) {
1287     if (options.filter && options.filter === 'all-local' && !options.user.hasRight(UserRight.SEE_ALL_VIDEOS)) {
1288       throw new Error('Try to filter all-local but no user has not the see all videos right')
1289     }
1290
1291     const query: FindOptions & { where?: null } = {
1292       offset: options.start,
1293       limit: options.count,
1294       order: getVideoSort(options.sort)
1295     }
1296
1297     let trendingDays: number
1298     if (options.sort.endsWith('trending')) {
1299       trendingDays = CONFIG.TRENDING.VIDEOS.INTERVAL_DAYS
1300
1301       query.group = 'VideoModel.id'
1302     }
1303
1304     const serverActor = await getServerActor()
1305
1306     // followerActorId === null has a meaning, so just check undefined
1307     const followerActorId = options.followerActorId !== undefined ? options.followerActorId : serverActor.id
1308
1309     const queryOptions = {
1310       followerActorId,
1311       serverAccountId: serverActor.Account.id,
1312       nsfw: options.nsfw,
1313       categoryOneOf: options.categoryOneOf,
1314       licenceOneOf: options.licenceOneOf,
1315       languageOneOf: options.languageOneOf,
1316       tagsOneOf: options.tagsOneOf,
1317       tagsAllOf: options.tagsAllOf,
1318       filter: options.filter,
1319       withFiles: options.withFiles,
1320       accountId: options.accountId,
1321       videoChannelId: options.videoChannelId,
1322       videoPlaylistId: options.videoPlaylistId,
1323       includeLocalVideos: options.includeLocalVideos,
1324       user: options.user,
1325       historyOfUser: options.historyOfUser,
1326       trendingDays
1327     }
1328
1329     return VideoModel.getAvailableForApi(query, queryOptions, options.countVideos)
1330   }
1331
1332   static async searchAndPopulateAccountAndServer (options: {
1333     includeLocalVideos: boolean
1334     search?: string
1335     start?: number
1336     count?: number
1337     sort?: string
1338     startDate?: string // ISO 8601
1339     endDate?: string // ISO 8601
1340     originallyPublishedStartDate?: string
1341     originallyPublishedEndDate?: string
1342     nsfw?: boolean
1343     categoryOneOf?: number[]
1344     licenceOneOf?: number[]
1345     languageOneOf?: string[]
1346     tagsOneOf?: string[]
1347     tagsAllOf?: string[]
1348     durationMin?: number // seconds
1349     durationMax?: number // seconds
1350     user?: MUserAccountId
1351     filter?: VideoFilter
1352   }) {
1353     const whereAnd = []
1354
1355     if (options.startDate || options.endDate) {
1356       const publishedAtRange = {}
1357
1358       if (options.startDate) publishedAtRange[Op.gte] = options.startDate
1359       if (options.endDate) publishedAtRange[Op.lte] = options.endDate
1360
1361       whereAnd.push({ publishedAt: publishedAtRange })
1362     }
1363
1364     if (options.originallyPublishedStartDate || options.originallyPublishedEndDate) {
1365       const originallyPublishedAtRange = {}
1366
1367       if (options.originallyPublishedStartDate) originallyPublishedAtRange[Op.gte] = options.originallyPublishedStartDate
1368       if (options.originallyPublishedEndDate) originallyPublishedAtRange[Op.lte] = options.originallyPublishedEndDate
1369
1370       whereAnd.push({ originallyPublishedAt: originallyPublishedAtRange })
1371     }
1372
1373     if (options.durationMin || options.durationMax) {
1374       const durationRange = {}
1375
1376       if (options.durationMin) durationRange[Op.gte] = options.durationMin
1377       if (options.durationMax) durationRange[Op.lte] = options.durationMax
1378
1379       whereAnd.push({ duration: durationRange })
1380     }
1381
1382     const attributesInclude = []
1383     const escapedSearch = VideoModel.sequelize.escape(options.search)
1384     const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
1385     if (options.search) {
1386       const trigramSearch = {
1387         id: {
1388           [Op.in]: Sequelize.literal(
1389             '(' +
1390             'SELECT "video"."id" FROM "video" ' +
1391             'WHERE ' +
1392             'lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' +
1393             'lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' +
1394             'UNION ALL ' +
1395             'SELECT "video"."id" FROM "video" LEFT JOIN "videoTag" ON "videoTag"."videoId" = "video"."id" ' +
1396             'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
1397             'WHERE lower("tag"."name") = lower(' + escapedSearch + ')' +
1398             ')'
1399           )
1400         }
1401       }
1402
1403       if (validator.isUUID(options.search)) {
1404         whereAnd.push({
1405           [Op.or]: [
1406             trigramSearch,
1407             {
1408               uuid: options.search
1409             }
1410           ]
1411         })
1412       } else {
1413         whereAnd.push(trigramSearch)
1414       }
1415
1416       attributesInclude.push(createSimilarityAttribute('VideoModel.name', options.search))
1417     }
1418
1419     // Cannot search on similarity if we don't have a search
1420     if (!options.search) {
1421       attributesInclude.push(
1422         Sequelize.literal('0 as similarity')
1423       )
1424     }
1425
1426     const query = {
1427       attributes: {
1428         include: attributesInclude
1429       },
1430       offset: options.start,
1431       limit: options.count,
1432       order: getVideoSort(options.sort)
1433     }
1434
1435     const serverActor = await getServerActor()
1436     const queryOptions = {
1437       followerActorId: serverActor.id,
1438       serverAccountId: serverActor.Account.id,
1439       includeLocalVideos: options.includeLocalVideos,
1440       nsfw: options.nsfw,
1441       categoryOneOf: options.categoryOneOf,
1442       licenceOneOf: options.licenceOneOf,
1443       languageOneOf: options.languageOneOf,
1444       tagsOneOf: options.tagsOneOf,
1445       tagsAllOf: options.tagsAllOf,
1446       user: options.user,
1447       filter: options.filter,
1448       baseWhere: whereAnd
1449     }
1450
1451     return VideoModel.getAvailableForApi(query, queryOptions)
1452   }
1453
1454   static load (id: number | string, t?: Transaction): Bluebird<MVideoThumbnail> {
1455     const where = buildWhereIdOrUUID(id)
1456     const options = {
1457       where,
1458       transaction: t
1459     }
1460
1461     return VideoModel.scope(ScopeNames.WITH_THUMBNAILS).findOne(options)
1462   }
1463
1464   static loadWithBlacklist (id: number | string, t?: Transaction): Bluebird<MVideoThumbnailBlacklist> {
1465     const where = buildWhereIdOrUUID(id)
1466     const options = {
1467       where,
1468       transaction: t
1469     }
1470
1471     return VideoModel.scope([
1472       ScopeNames.WITH_THUMBNAILS,
1473       ScopeNames.WITH_BLACKLISTED
1474     ]).findOne(options)
1475   }
1476
1477   static loadImmutableAttributes (id: number | string, t?: Transaction): Bluebird<MVideoImmutable> {
1478     const fun = () => {
1479       const where = buildWhereIdOrUUID(id)
1480       const options = {
1481         attributes: [
1482           'id', 'url', 'uuid'
1483         ],
1484         where,
1485         transaction: t
1486       }
1487
1488       return VideoModel.unscoped().findOne(options)
1489     }
1490
1491     return ModelCache.Instance.doCache({
1492       cacheType: 'video-immutable',
1493       key: '' + id,
1494       deleteKey: 'video',
1495       fun
1496     })
1497   }
1498
1499   static loadWithRights (id: number | string, t?: Transaction): Bluebird<MVideoWithRights> {
1500     const where = buildWhereIdOrUUID(id)
1501     const options = {
1502       where,
1503       transaction: t
1504     }
1505
1506     return VideoModel.scope([
1507       ScopeNames.WITH_BLACKLISTED,
1508       ScopeNames.WITH_USER_ID,
1509       ScopeNames.WITH_THUMBNAILS
1510     ]).findOne(options)
1511   }
1512
1513   static loadOnlyId (id: number | string, t?: Transaction): Bluebird<MVideoIdThumbnail> {
1514     const where = buildWhereIdOrUUID(id)
1515
1516     const options = {
1517       attributes: [ 'id' ],
1518       where,
1519       transaction: t
1520     }
1521
1522     return VideoModel.scope(ScopeNames.WITH_THUMBNAILS).findOne(options)
1523   }
1524
1525   static loadWithFiles (id: number | string, t?: Transaction, logging?: boolean): Bluebird<MVideoWithAllFiles> {
1526     const where = buildWhereIdOrUUID(id)
1527
1528     const query = {
1529       where,
1530       transaction: t,
1531       logging
1532     }
1533
1534     return VideoModel.scope([
1535       ScopeNames.WITH_WEBTORRENT_FILES,
1536       ScopeNames.WITH_STREAMING_PLAYLISTS,
1537       ScopeNames.WITH_THUMBNAILS
1538     ]).findOne(query)
1539   }
1540
1541   static loadByUUID (uuid: string): Bluebird<MVideoThumbnail> {
1542     const options = {
1543       where: {
1544         uuid
1545       }
1546     }
1547
1548     return VideoModel.scope(ScopeNames.WITH_THUMBNAILS).findOne(options)
1549   }
1550
1551   static loadByUrl (url: string, transaction?: Transaction): Bluebird<MVideoThumbnail> {
1552     const query: FindOptions = {
1553       where: {
1554         url
1555       },
1556       transaction
1557     }
1558
1559     return VideoModel.scope(ScopeNames.WITH_THUMBNAILS).findOne(query)
1560   }
1561
1562   static loadByUrlAndPopulateAccount (url: string, transaction?: Transaction): Bluebird<MVideoAccountLightBlacklistAllFiles> {
1563     const query: FindOptions = {
1564       where: {
1565         url
1566       },
1567       transaction
1568     }
1569
1570     return VideoModel.scope([
1571       ScopeNames.WITH_ACCOUNT_DETAILS,
1572       ScopeNames.WITH_WEBTORRENT_FILES,
1573       ScopeNames.WITH_STREAMING_PLAYLISTS,
1574       ScopeNames.WITH_THUMBNAILS,
1575       ScopeNames.WITH_BLACKLISTED
1576     ]).findOne(query)
1577   }
1578
1579   static loadAndPopulateAccountAndServerAndTags (id: number | string, t?: Transaction, userId?: number): Bluebird<MVideoFullLight> {
1580     const where = buildWhereIdOrUUID(id)
1581
1582     const options = {
1583       order: [ [ 'Tags', 'name', 'ASC' ] ] as any,
1584       where,
1585       transaction: t
1586     }
1587
1588     const scopes: (string | ScopeOptions)[] = [
1589       ScopeNames.WITH_TAGS,
1590       ScopeNames.WITH_BLACKLISTED,
1591       ScopeNames.WITH_ACCOUNT_DETAILS,
1592       ScopeNames.WITH_SCHEDULED_UPDATE,
1593       ScopeNames.WITH_WEBTORRENT_FILES,
1594       ScopeNames.WITH_STREAMING_PLAYLISTS,
1595       ScopeNames.WITH_THUMBNAILS
1596     ]
1597
1598     if (userId) {
1599       scopes.push({ method: [ ScopeNames.WITH_USER_HISTORY, userId ] })
1600     }
1601
1602     return VideoModel
1603       .scope(scopes)
1604       .findOne(options)
1605   }
1606
1607   static loadForGetAPI (parameters: {
1608     id: number | string
1609     t?: Transaction
1610     userId?: number
1611   }): Bluebird<MVideoDetails> {
1612     const { id, t, userId } = parameters
1613     const where = buildWhereIdOrUUID(id)
1614
1615     const options = {
1616       order: [ [ 'Tags', 'name', 'ASC' ] ] as any, // FIXME: sequelize typings
1617       where,
1618       transaction: t
1619     }
1620
1621     const scopes: (string | ScopeOptions)[] = [
1622       ScopeNames.WITH_TAGS,
1623       ScopeNames.WITH_BLACKLISTED,
1624       ScopeNames.WITH_ACCOUNT_DETAILS,
1625       ScopeNames.WITH_SCHEDULED_UPDATE,
1626       ScopeNames.WITH_THUMBNAILS,
1627       { method: [ ScopeNames.WITH_WEBTORRENT_FILES, true ] },
1628       { method: [ ScopeNames.WITH_STREAMING_PLAYLISTS, true ] }
1629     ]
1630
1631     if (userId) {
1632       scopes.push({ method: [ ScopeNames.WITH_USER_HISTORY, userId ] })
1633     }
1634
1635     return VideoModel
1636       .scope(scopes)
1637       .findOne(options)
1638   }
1639
1640   static async getStats () {
1641     const totalLocalVideos = await VideoModel.count({
1642       where: {
1643         remote: false
1644       }
1645     })
1646     const totalVideos = await VideoModel.count()
1647
1648     let totalLocalVideoViews = await VideoModel.sum('views', {
1649       where: {
1650         remote: false
1651       }
1652     })
1653     // Sequelize could return null...
1654     if (!totalLocalVideoViews) totalLocalVideoViews = 0
1655
1656     return {
1657       totalLocalVideos,
1658       totalLocalVideoViews,
1659       totalVideos
1660     }
1661   }
1662
1663   static incrementViews (id: number, views: number) {
1664     return VideoModel.increment('views', {
1665       by: views,
1666       where: {
1667         id
1668       }
1669     })
1670   }
1671
1672   static checkVideoHasInstanceFollow (videoId: number, followerActorId: number) {
1673     // Instances only share videos
1674     const query = 'SELECT 1 FROM "videoShare" ' +
1675       'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
1676       'WHERE "actorFollow"."actorId" = $followerActorId AND "videoShare"."videoId" = $videoId ' +
1677       'LIMIT 1'
1678
1679     const options = {
1680       type: QueryTypes.SELECT as QueryTypes.SELECT,
1681       bind: { followerActorId, videoId },
1682       raw: true
1683     }
1684
1685     return VideoModel.sequelize.query(query, options)
1686                      .then(results => results.length === 1)
1687   }
1688
1689   static bulkUpdateSupportField (videoChannel: MChannel, t: Transaction) {
1690     const options = {
1691       where: {
1692         channelId: videoChannel.id
1693       },
1694       transaction: t
1695     }
1696
1697     return VideoModel.update({ support: videoChannel.support }, options)
1698   }
1699
1700   static getAllIdsFromChannel (videoChannel: MChannelId): Bluebird<number[]> {
1701     const query = {
1702       attributes: [ 'id' ],
1703       where: {
1704         channelId: videoChannel.id
1705       }
1706     }
1707
1708     return VideoModel.findAll(query)
1709                      .then(videos => videos.map(v => v.id))
1710   }
1711
1712   // threshold corresponds to how many video the field should have to be returned
1713   static async getRandomFieldSamples (field: 'category' | 'channelId', threshold: number, count: number) {
1714     const serverActor = await getServerActor()
1715     const followerActorId = serverActor.id
1716
1717     const scopeOptions: AvailableForListIDsOptions = {
1718       serverAccountId: serverActor.Account.id,
1719       followerActorId,
1720       includeLocalVideos: true,
1721       attributesType: 'none' // Don't break aggregation
1722     }
1723
1724     const query: FindOptions = {
1725       attributes: [ field ],
1726       limit: count,
1727       group: field,
1728       having: Sequelize.where(
1729         Sequelize.fn('COUNT', Sequelize.col(field)), { [Op.gte]: threshold }
1730       ),
1731       order: [ (this.sequelize as any).random() ]
1732     }
1733
1734     return VideoModel.scope({ method: [ ScopeNames.AVAILABLE_FOR_LIST_IDS, scopeOptions ] })
1735                      .findAll(query)
1736                      .then(rows => rows.map(r => r[field]))
1737   }
1738
1739   static buildTrendingQuery (trendingDays: number) {
1740     return {
1741       attributes: [],
1742       subQuery: false,
1743       model: VideoViewModel,
1744       required: false,
1745       where: {
1746         startDate: {
1747           [Op.gte]: new Date(new Date().getTime() - (24 * 3600 * 1000) * trendingDays)
1748         }
1749       }
1750     }
1751   }
1752
1753   private static async getAvailableForApi (
1754     query: FindOptions & { where?: null }, // Forbid where field in query
1755     options: AvailableForListIDsOptions,
1756     countVideos = true
1757   ) {
1758     const idsScope: ScopeOptions = {
1759       method: [
1760         ScopeNames.AVAILABLE_FOR_LIST_IDS, options
1761       ]
1762     }
1763
1764     // Remove trending sort on count, because it uses a group by
1765     const countOptions = Object.assign({}, options, { trendingDays: undefined })
1766     const countQuery: CountOptions = Object.assign({}, query, { attributes: undefined, group: undefined })
1767     const countScope: ScopeOptions = {
1768       method: [
1769         ScopeNames.AVAILABLE_FOR_LIST_IDS, countOptions
1770       ]
1771     }
1772
1773     const [ count, rows ] = await Promise.all([
1774       countVideos
1775         ? VideoModel.scope(countScope).count(countQuery)
1776         : Promise.resolve<number>(undefined),
1777
1778       VideoModel.scope(idsScope)
1779                 .findAll(Object.assign({}, query, { raw: true }))
1780                 .then(rows => rows.map(r => r.id))
1781                 .then(ids => VideoModel.loadCompleteVideosForApi(ids, query, options))
1782     ])
1783
1784     return {
1785       data: rows,
1786       total: count
1787     }
1788   }
1789
1790   private static loadCompleteVideosForApi (ids: number[], query: FindOptions, options: AvailableForListIDsOptions) {
1791     if (ids.length === 0) return []
1792
1793     const secondQuery: FindOptions = {
1794       offset: 0,
1795       limit: query.limit,
1796       attributes: query.attributes,
1797       order: [ // Keep original order
1798         Sequelize.literal(
1799           ids.map(id => `"VideoModel".id = ${id} DESC`).join(', ')
1800         )
1801       ]
1802     }
1803
1804     const apiScope: (string | ScopeOptions)[] = []
1805
1806     if (options.user) {
1807       apiScope.push({ method: [ ScopeNames.WITH_USER_HISTORY, options.user.id ] })
1808     }
1809
1810     apiScope.push({
1811       method: [
1812         ScopeNames.FOR_API, {
1813           ids,
1814           withFiles: options.withFiles,
1815           videoPlaylistId: options.videoPlaylistId
1816         } as ForAPIOptions
1817       ]
1818     })
1819
1820     return VideoModel.scope(apiScope).findAll(secondQuery)
1821   }
1822
1823   private static isPrivacyForFederation (privacy: VideoPrivacy) {
1824     const castedPrivacy = parseInt(privacy + '', 10)
1825
1826     return castedPrivacy === VideoPrivacy.PUBLIC || castedPrivacy === VideoPrivacy.UNLISTED
1827   }
1828
1829   static getCategoryLabel (id: number) {
1830     return VIDEO_CATEGORIES[id] || 'Misc'
1831   }
1832
1833   static getLicenceLabel (id: number) {
1834     return VIDEO_LICENCES[id] || 'Unknown'
1835   }
1836
1837   static getLanguageLabel (id: string) {
1838     return VIDEO_LANGUAGES[id] || 'Unknown'
1839   }
1840
1841   static getPrivacyLabel (id: number) {
1842     return VIDEO_PRIVACIES[id] || 'Unknown'
1843   }
1844
1845   static getStateLabel (id: number) {
1846     return VIDEO_STATES[id] || 'Unknown'
1847   }
1848
1849   isBlacklisted () {
1850     return !!this.VideoBlacklist
1851   }
1852
1853   isBlocked () {
1854     return (this.VideoChannel.Account.Actor.Server && this.VideoChannel.Account.Actor.Server.isBlocked()) ||
1855       this.VideoChannel.Account.isBlocked()
1856   }
1857
1858   getQualityFileBy<T extends MVideoWithFile> (this: T, fun: (files: MVideoFile[], it: (file: MVideoFile) => number) => MVideoFile) {
1859     if (Array.isArray(this.VideoFiles) && this.VideoFiles.length !== 0) {
1860       const file = fun(this.VideoFiles, file => file.resolution)
1861
1862       return Object.assign(file, { Video: this })
1863     }
1864
1865     // No webtorrent files, try with streaming playlist files
1866     if (Array.isArray(this.VideoStreamingPlaylists) && this.VideoStreamingPlaylists.length !== 0) {
1867       const streamingPlaylistWithVideo = Object.assign(this.VideoStreamingPlaylists[0], { Video: this })
1868
1869       const file = fun(streamingPlaylistWithVideo.VideoFiles, file => file.resolution)
1870       return Object.assign(file, { VideoStreamingPlaylist: streamingPlaylistWithVideo })
1871     }
1872
1873     return undefined
1874   }
1875
1876   getMaxQualityFile<T extends MVideoWithFile> (this: T): MVideoFileVideo | MVideoFileStreamingPlaylistVideo {
1877     return this.getQualityFileBy(maxBy)
1878   }
1879
1880   getMinQualityFile<T extends MVideoWithFile> (this: T): MVideoFileVideo | MVideoFileStreamingPlaylistVideo {
1881     return this.getQualityFileBy(minBy)
1882   }
1883
1884   getWebTorrentFile<T extends MVideoWithFile> (this: T, resolution: number): MVideoFileVideo {
1885     if (Array.isArray(this.VideoFiles) === false) return undefined
1886
1887     const file = this.VideoFiles.find(f => f.resolution === resolution)
1888     if (!file) return undefined
1889
1890     return Object.assign(file, { Video: this })
1891   }
1892
1893   async addAndSaveThumbnail (thumbnail: MThumbnail, transaction: Transaction) {
1894     thumbnail.videoId = this.id
1895
1896     const savedThumbnail = await thumbnail.save({ transaction })
1897
1898     if (Array.isArray(this.Thumbnails) === false) this.Thumbnails = []
1899
1900     // Already have this thumbnail, skip
1901     if (this.Thumbnails.find(t => t.id === savedThumbnail.id)) return
1902
1903     this.Thumbnails.push(savedThumbnail)
1904   }
1905
1906   generateThumbnailName () {
1907     return this.uuid + '.jpg'
1908   }
1909
1910   getMiniature () {
1911     if (Array.isArray(this.Thumbnails) === false) return undefined
1912
1913     return this.Thumbnails.find(t => t.type === ThumbnailType.MINIATURE)
1914   }
1915
1916   generatePreviewName () {
1917     return this.uuid + '.jpg'
1918   }
1919
1920   getPreview () {
1921     if (Array.isArray(this.Thumbnails) === false) return undefined
1922
1923     return this.Thumbnails.find(t => t.type === ThumbnailType.PREVIEW)
1924   }
1925
1926   isOwned () {
1927     return this.remote === false
1928   }
1929
1930   getWatchStaticPath () {
1931     return '/videos/watch/' + this.uuid
1932   }
1933
1934   getEmbedStaticPath () {
1935     return '/videos/embed/' + this.uuid
1936   }
1937
1938   getMiniatureStaticPath () {
1939     const thumbnail = this.getMiniature()
1940     if (!thumbnail) return null
1941
1942     return join(STATIC_PATHS.THUMBNAILS, thumbnail.filename)
1943   }
1944
1945   getPreviewStaticPath () {
1946     const preview = this.getPreview()
1947     if (!preview) return null
1948
1949     // We use a local cache, so specify our cache endpoint instead of potential remote URL
1950     return join(LAZY_STATIC_PATHS.PREVIEWS, preview.filename)
1951   }
1952
1953   toFormattedJSON (this: MVideoFormattable, options?: VideoFormattingJSONOptions): Video {
1954     return videoModelToFormattedJSON(this, options)
1955   }
1956
1957   toFormattedDetailsJSON (this: MVideoFormattableDetails): VideoDetails {
1958     return videoModelToFormattedDetailsJSON(this)
1959   }
1960
1961   getFormattedVideoFilesJSON (): VideoFile[] {
1962     const { baseUrlHttp, baseUrlWs } = this.getBaseUrls()
1963     return videoFilesModelToFormattedJSON(this, baseUrlHttp, baseUrlWs, this.VideoFiles)
1964   }
1965
1966   toActivityPubObject (this: MVideoAP): VideoTorrentObject {
1967     return videoModelToActivityPubObject(this)
1968   }
1969
1970   getTruncatedDescription () {
1971     if (!this.description) return null
1972
1973     const maxLength = CONSTRAINTS_FIELDS.VIDEOS.TRUNCATED_DESCRIPTION.max
1974     return peertubeTruncate(this.description, { length: maxLength })
1975   }
1976
1977   getMaxQualityResolution () {
1978     const file = this.getMaxQualityFile()
1979     const videoOrPlaylist = file.getVideoOrStreamingPlaylist()
1980     const originalFilePath = getVideoFilePath(videoOrPlaylist, file)
1981
1982     return getVideoFileResolution(originalFilePath)
1983   }
1984
1985   getDescriptionAPIPath () {
1986     return `/api/${API_VERSION}/videos/${this.uuid}/description`
1987   }
1988
1989   getHLSPlaylist (): MStreamingPlaylistFilesVideo {
1990     if (!this.VideoStreamingPlaylists) return undefined
1991
1992     const playlist = this.VideoStreamingPlaylists.find(p => p.type === VideoStreamingPlaylistType.HLS)
1993     playlist.Video = this
1994
1995     return playlist
1996   }
1997
1998   setHLSPlaylist (playlist: MStreamingPlaylist) {
1999     const toAdd = [ playlist ] as [ VideoStreamingPlaylistModel ]
2000
2001     if (Array.isArray(this.VideoStreamingPlaylists) === false || this.VideoStreamingPlaylists.length === 0) {
2002       this.VideoStreamingPlaylists = toAdd
2003       return
2004     }
2005
2006     this.VideoStreamingPlaylists = this.VideoStreamingPlaylists
2007                                        .filter(s => s.type !== VideoStreamingPlaylistType.HLS)
2008                                        .concat(toAdd)
2009   }
2010
2011   removeFile (videoFile: MVideoFile, isRedundancy = false) {
2012     const filePath = getVideoFilePath(this, videoFile, isRedundancy)
2013     return remove(filePath)
2014       .catch(err => logger.warn('Cannot delete file %s.', filePath, { err }))
2015   }
2016
2017   removeTorrent (videoFile: MVideoFile) {
2018     const torrentPath = getTorrentFilePath(this, videoFile)
2019     return remove(torrentPath)
2020       .catch(err => logger.warn('Cannot delete torrent %s.', torrentPath, { err }))
2021   }
2022
2023   async removeStreamingPlaylistFiles (streamingPlaylist: MStreamingPlaylist, isRedundancy = false) {
2024     const directoryPath = getHLSDirectory(this, isRedundancy)
2025
2026     await remove(directoryPath)
2027
2028     if (isRedundancy !== true) {
2029       const streamingPlaylistWithFiles = streamingPlaylist as MStreamingPlaylistFilesVideo
2030       streamingPlaylistWithFiles.Video = this
2031
2032       if (!Array.isArray(streamingPlaylistWithFiles.VideoFiles)) {
2033         streamingPlaylistWithFiles.VideoFiles = await streamingPlaylistWithFiles.$get('VideoFiles')
2034       }
2035
2036       // Remove physical files and torrents
2037       await Promise.all(
2038         streamingPlaylistWithFiles.VideoFiles.map(file => streamingPlaylistWithFiles.removeTorrent(file))
2039       )
2040     }
2041   }
2042
2043   isOutdated () {
2044     if (this.isOwned()) return false
2045
2046     return isOutdated(this, ACTIVITY_PUB.VIDEO_REFRESH_INTERVAL)
2047   }
2048
2049   hasPrivacyForFederation () {
2050     return VideoModel.isPrivacyForFederation(this.privacy)
2051   }
2052
2053   isNewVideo (newPrivacy: VideoPrivacy) {
2054     return this.hasPrivacyForFederation() === false && VideoModel.isPrivacyForFederation(newPrivacy) === true
2055   }
2056
2057   setAsRefreshed () {
2058     this.changed('updatedAt', true)
2059
2060     return this.save()
2061   }
2062
2063   requiresAuth () {
2064     return this.privacy === VideoPrivacy.PRIVATE || this.privacy === VideoPrivacy.INTERNAL || !!this.VideoBlacklist
2065   }
2066
2067   setPrivacy (newPrivacy: VideoPrivacy) {
2068     if (this.privacy === VideoPrivacy.PRIVATE && newPrivacy !== VideoPrivacy.PRIVATE) {
2069       this.publishedAt = new Date()
2070     }
2071
2072     this.privacy = newPrivacy
2073   }
2074
2075   isConfidential () {
2076     return this.privacy === VideoPrivacy.PRIVATE ||
2077       this.privacy === VideoPrivacy.UNLISTED ||
2078       this.privacy === VideoPrivacy.INTERNAL
2079   }
2080
2081   async publishIfNeededAndSave (t: Transaction) {
2082     if (this.state !== VideoState.PUBLISHED) {
2083       this.state = VideoState.PUBLISHED
2084       this.publishedAt = new Date()
2085       await this.save({ transaction: t })
2086
2087       return true
2088     }
2089
2090     return false
2091   }
2092
2093   getBaseUrls () {
2094     if (this.isOwned()) {
2095       return {
2096         baseUrlHttp: WEBSERVER.URL,
2097         baseUrlWs: WEBSERVER.WS + '://' + WEBSERVER.HOSTNAME + ':' + WEBSERVER.PORT
2098       }
2099     }
2100
2101     return {
2102       baseUrlHttp: REMOTE_SCHEME.HTTP + '://' + this.VideoChannel.Account.Actor.Server.host,
2103       baseUrlWs: REMOTE_SCHEME.WS + '://' + this.VideoChannel.Account.Actor.Server.host
2104     }
2105   }
2106
2107   getTrackerUrls (baseUrlHttp: string, baseUrlWs: string) {
2108     return [ baseUrlWs + '/tracker/socket', baseUrlHttp + '/tracker/announce' ]
2109   }
2110
2111   getTorrentUrl (videoFile: MVideoFile, baseUrlHttp: string) {
2112     return baseUrlHttp + STATIC_PATHS.TORRENTS + getTorrentFileName(this, videoFile)
2113   }
2114
2115   getTorrentDownloadUrl (videoFile: MVideoFile, baseUrlHttp: string) {
2116     return baseUrlHttp + STATIC_DOWNLOAD_PATHS.TORRENTS + getTorrentFileName(this, videoFile)
2117   }
2118
2119   getVideoFileUrl (videoFile: MVideoFile, baseUrlHttp: string) {
2120     return baseUrlHttp + STATIC_PATHS.WEBSEED + getVideoFilename(this, videoFile)
2121   }
2122
2123   getVideoRedundancyUrl (videoFile: MVideoFile, baseUrlHttp: string) {
2124     return baseUrlHttp + STATIC_PATHS.REDUNDANCY + getVideoFilename(this, videoFile)
2125   }
2126
2127   getVideoFileDownloadUrl (videoFile: MVideoFile, baseUrlHttp: string) {
2128     return baseUrlHttp + STATIC_DOWNLOAD_PATHS.VIDEOS + getVideoFilename(this, videoFile)
2129   }
2130
2131   getBandwidthBits (videoFile: MVideoFile) {
2132     return Math.ceil((videoFile.size * 8) / this.duration)
2133   }
2134 }