server/models/video/video.ts

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