server/models/video/video.ts

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