server/models/video/video.ts

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