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