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