server/models/user/user.ts

   1 import { values } from 'lodash'
   2 import { col, FindOptions, fn, literal, Op, QueryTypes, where, WhereOptions } from 'sequelize'
   3 import {
   4   AfterDestroy,
   5   AfterUpdate,
   6   AllowNull,
   7   BeforeCreate,
   8   BeforeUpdate,
   9   Column,
  10   CreatedAt,
  11   DataType,
  12   Default,
  13   DefaultScope,
  14   HasMany,
  15   HasOne,
  16   Is,
  17   IsEmail,
  18   IsUUID,
  19   Model,
  20   Scopes,
  21   Table,
  22   UpdatedAt
  23 } from 'sequelize-typescript'
  24 import { TokensCache } from '@server/lib/auth/tokens-cache'
  25 import { LiveQuotaStore } from '@server/lib/live'
  26 import {
  27   MMyUserFormattable,
  28   MUser,
  29   MUserDefault,
  30   MUserFormattable,
  31   MUserNotifSettingChannelDefault,
  32   MUserWithNotificationSetting
  33 } from '@server/types/models'
  34 import { AttributesOnly } from '@shared/typescript-utils'
  35 import { hasUserRight, USER_ROLE_LABELS } from '../../../shared/core-utils/users'
  36 import { AbuseState, MyUser, UserRight, VideoPlaylistType } from '../../../shared/models'
  37 import { User, UserRole } from '../../../shared/models/users'
  38 import { UserAdminFlag } from '../../../shared/models/users/user-flag.model'
  39 import { NSFWPolicyType } from '../../../shared/models/videos/nsfw-policy.type'
  40 import { isThemeNameValid } from '../../helpers/custom-validators/plugins'
  41 import {
  42   isUserAdminFlagsValid,
  43   isUserAutoPlayNextVideoPlaylistValid,
  44   isUserAutoPlayNextVideoValid,
  45   isUserAutoPlayVideoValid,
  46   isUserBlockedReasonValid,
  47   isUserBlockedValid,
  48   isUserEmailVerifiedValid,
  49   isUserNoModal,
  50   isUserNSFWPolicyValid,
  51   isUserP2PEnabledValid,
  52   isUserPasswordValid,
  53   isUserRoleValid,
  54   isUserUsernameValid,
  55   isUserVideoLanguages,
  56   isUserVideoQuotaDailyValid,
  57   isUserVideoQuotaValid,
  58   isUserVideosHistoryEnabledValid
  59 } from '../../helpers/custom-validators/users'
  60 import { comparePassword, cryptPassword } from '../../helpers/peertube-crypto'
  61 import { DEFAULT_USER_THEME_NAME, NSFW_POLICY_TYPES } from '../../initializers/constants'
  62 import { getThemeOrDefault } from '../../lib/plugins/theme-utils'
  63 import { AccountModel } from '../account/account'
  64 import { ActorModel } from '../actor/actor'
  65 import { ActorFollowModel } from '../actor/actor-follow'
  66 import { ActorImageModel } from '../actor/actor-image'
  67 import { OAuthTokenModel } from '../oauth/oauth-token'
  68 import { getAdminUsersSort, throwIfNotValid } from '../utils'
  69 import { VideoModel } from '../video/video'
  70 import { VideoChannelModel } from '../video/video-channel'
  71 import { VideoImportModel } from '../video/video-import'
  72 import { VideoLiveModel } from '../video/video-live'
  73 import { VideoPlaylistModel } from '../video/video-playlist'
  74 import { UserNotificationSettingModel } from './user-notification-setting'
  75
  76 enum ScopeNames {
  77   FOR_ME_API = 'FOR_ME_API',
  78   WITH_VIDEOCHANNELS = 'WITH_VIDEOCHANNELS',
  79   WITH_QUOTA = 'WITH_QUOTA',
  80   WITH_STATS = 'WITH_STATS'
  81 }
  82
  83 @DefaultScope(() => ({
  84   include: [
  85     {
  86       model: AccountModel,
  87       required: true
  88     },
  89     {
  90       model: UserNotificationSettingModel,
  91       required: true
  92     }
  93   ]
  94 }))
  95 @Scopes(() => ({
  96   [ScopeNames.FOR_ME_API]: {
  97     include: [
  98       {
  99         model: AccountModel,
 100         include: [
 101           {
 102             model: VideoChannelModel.unscoped(),
 103             include: [
 104               {
 105                 model: ActorModel,
 106                 required: true,
 107                 include: [
 108                   {
 109                     model: ActorImageModel,
 110                     as: 'Banners',
 111                     required: false
 112                   }
 113                 ]
 114               }
 115             ]
 116           },
 117           {
 118             attributes: [ 'id', 'name', 'type' ],
 119             model: VideoPlaylistModel.unscoped(),
 120             required: true,
 121             where: {
 122               type: {
 123                 [Op.ne]: VideoPlaylistType.REGULAR
 124               }
 125             }
 126           }
 127         ]
 128       },
 129       {
 130         model: UserNotificationSettingModel,
 131         required: true
 132       }
 133     ]
 134   },
 135   [ScopeNames.WITH_VIDEOCHANNELS]: {
 136     include: [
 137       {
 138         model: AccountModel,
 139         include: [
 140           {
 141             model: VideoChannelModel
 142           },
 143           {
 144             attributes: [ 'id', 'name', 'type' ],
 145             model: VideoPlaylistModel.unscoped(),
 146             required: true,
 147             where: {
 148               type: {
 149                 [Op.ne]: VideoPlaylistType.REGULAR
 150               }
 151             }
 152           }
 153         ]
 154       }
 155     ]
 156   },
 157   [ScopeNames.WITH_QUOTA]: {
 158     attributes: {
 159       include: [
 160         [
 161           literal(
 162             '(' +
 163               UserModel.generateUserQuotaBaseSQL({
 164                 withSelect: false,
 165                 whereUserId: '"UserModel"."id"',
 166                 daily: false
 167               }) +
 168             ')'
 169           ),
 170           'videoQuotaUsed'
 171         ],
 172         [
 173           literal(
 174             '(' +
 175               UserModel.generateUserQuotaBaseSQL({
 176                 withSelect: false,
 177                 whereUserId: '"UserModel"."id"',
 178                 daily: true
 179               }) +
 180             ')'
 181           ),
 182           'videoQuotaUsedDaily'
 183         ]
 184       ]
 185     }
 186   },
 187   [ScopeNames.WITH_STATS]: {
 188     attributes: {
 189       include: [
 190         [
 191           literal(
 192             '(' +
 193               'SELECT COUNT("video"."id") ' +
 194               'FROM "video" ' +
 195               'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 196               'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
 197               'WHERE "account"."userId" = "UserModel"."id"' +
 198             ')'
 199           ),
 200           'videosCount'
 201         ],
 202         [
 203           literal(
 204             '(' +
 205               `SELECT concat_ws(':', "abuses", "acceptedAbuses") ` +
 206               'FROM (' +
 207                 'SELECT COUNT("abuse"."id") AS "abuses", ' +
 208                        `COUNT("abuse"."id") FILTER (WHERE "abuse"."state" = ${AbuseState.ACCEPTED}) AS "acceptedAbuses" ` +
 209                 'FROM "abuse" ' +
 210                 'INNER JOIN "account" ON "account"."id" = "abuse"."flaggedAccountId" ' +
 211                 'WHERE "account"."userId" = "UserModel"."id"' +
 212               ') t' +
 213             ')'
 214           ),
 215           'abusesCount'
 216         ],
 217         [
 218           literal(
 219             '(' +
 220               'SELECT COUNT("abuse"."id") ' +
 221               'FROM "abuse" ' +
 222               'INNER JOIN "account" ON "account"."id" = "abuse"."reporterAccountId" ' +
 223               'WHERE "account"."userId" = "UserModel"."id"' +
 224             ')'
 225           ),
 226           'abusesCreatedCount'
 227         ],
 228         [
 229           literal(
 230             '(' +
 231               'SELECT COUNT("videoComment"."id") ' +
 232               'FROM "videoComment" ' +
 233               'INNER JOIN "account" ON "account"."id" = "videoComment"."accountId" ' +
 234               'WHERE "account"."userId" = "UserModel"."id"' +
 235             ')'
 236           ),
 237           'videoCommentsCount'
 238         ]
 239       ]
 240     }
 241   }
 242 }))
 243 @Table({
 244   tableName: 'user',
 245   indexes: [
 246     {
 247       fields: [ 'username' ],
 248       unique: true
 249     },
 250     {
 251       fields: [ 'email' ],
 252       unique: true
 253     }
 254   ]
 255 })
 256 export class UserModel extends Model<Partial<AttributesOnly<UserModel>>> {
 257
 258   @AllowNull(true)
 259   @Is('UserPassword', value => throwIfNotValid(value, isUserPasswordValid, 'user password', true))
 260   @Column
 261   password: string
 262
 263   @AllowNull(false)
 264   @Is('UserUsername', value => throwIfNotValid(value, isUserUsernameValid, 'user name'))
 265   @Column
 266   username: string
 267
 268   @AllowNull(false)
 269   @IsEmail
 270   @Column(DataType.STRING(400))
 271   email: string
 272
 273   @AllowNull(true)
 274   @IsEmail
 275   @Column(DataType.STRING(400))
 276   pendingEmail: string
 277
 278   @AllowNull(true)
 279   @Default(null)
 280   @Is('UserEmailVerified', value => throwIfNotValid(value, isUserEmailVerifiedValid, 'email verified boolean', true))
 281   @Column
 282   emailVerified: boolean
 283
 284   @AllowNull(false)
 285   @Is('UserNSFWPolicy', value => throwIfNotValid(value, isUserNSFWPolicyValid, 'NSFW policy'))
 286   @Column(DataType.ENUM(...values(NSFW_POLICY_TYPES)))
 287   nsfwPolicy: NSFWPolicyType
 288
 289   @AllowNull(false)
 290   @Is('p2pEnabled', value => throwIfNotValid(value, isUserP2PEnabledValid, 'P2P enabled'))
 291   @Column
 292   p2pEnabled: boolean
 293
 294   @AllowNull(false)
 295   @Default(true)
 296   @Is('UserVideosHistoryEnabled', value => throwIfNotValid(value, isUserVideosHistoryEnabledValid, 'Videos history enabled'))
 297   @Column
 298   videosHistoryEnabled: boolean
 299
 300   @AllowNull(false)
 301   @Default(true)
 302   @Is('UserAutoPlayVideo', value => throwIfNotValid(value, isUserAutoPlayVideoValid, 'auto play video boolean'))
 303   @Column
 304   autoPlayVideo: boolean
 305
 306   @AllowNull(false)
 307   @Default(false)
 308   @Is('UserAutoPlayNextVideo', value => throwIfNotValid(value, isUserAutoPlayNextVideoValid, 'auto play next video boolean'))
 309   @Column
 310   autoPlayNextVideo: boolean
 311
 312   @AllowNull(false)
 313   @Default(true)
 314   @Is(
 315     'UserAutoPlayNextVideoPlaylist',
 316     value => throwIfNotValid(value, isUserAutoPlayNextVideoPlaylistValid, 'auto play next video for playlists boolean')
 317   )
 318   @Column
 319   autoPlayNextVideoPlaylist: boolean
 320
 321   @AllowNull(true)
 322   @Default(null)
 323   @Is('UserVideoLanguages', value => throwIfNotValid(value, isUserVideoLanguages, 'video languages'))
 324   @Column(DataType.ARRAY(DataType.STRING))
 325   videoLanguages: string[]
 326
 327   @AllowNull(false)
 328   @Default(UserAdminFlag.NONE)
 329   @Is('UserAdminFlags', value => throwIfNotValid(value, isUserAdminFlagsValid, 'user admin flags'))
 330   @Column
 331   adminFlags?: UserAdminFlag
 332
 333   @AllowNull(false)
 334   @Default(false)
 335   @Is('UserBlocked', value => throwIfNotValid(value, isUserBlockedValid, 'blocked boolean'))
 336   @Column
 337   blocked: boolean
 338
 339   @AllowNull(true)
 340   @Default(null)
 341   @Is('UserBlockedReason', value => throwIfNotValid(value, isUserBlockedReasonValid, 'blocked reason', true))
 342   @Column
 343   blockedReason: string
 344
 345   @AllowNull(false)
 346   @Is('UserRole', value => throwIfNotValid(value, isUserRoleValid, 'role'))
 347   @Column
 348   role: number
 349
 350   @AllowNull(false)
 351   @Is('UserVideoQuota', value => throwIfNotValid(value, isUserVideoQuotaValid, 'video quota'))
 352   @Column(DataType.BIGINT)
 353   videoQuota: number
 354
 355   @AllowNull(false)
 356   @Is('UserVideoQuotaDaily', value => throwIfNotValid(value, isUserVideoQuotaDailyValid, 'video quota daily'))
 357   @Column(DataType.BIGINT)
 358   videoQuotaDaily: number
 359
 360   @AllowNull(false)
 361   @Default(DEFAULT_USER_THEME_NAME)
 362   @Is('UserTheme', value => throwIfNotValid(value, isThemeNameValid, 'theme'))
 363   @Column
 364   theme: string
 365
 366   @AllowNull(false)
 367   @Default(false)
 368   @Is(
 369     'UserNoInstanceConfigWarningModal',
 370     value => throwIfNotValid(value, isUserNoModal, 'no instance config warning modal')
 371   )
 372   @Column
 373   noInstanceConfigWarningModal: boolean
 374
 375   @AllowNull(false)
 376   @Default(false)
 377   @Is(
 378     'UserNoWelcomeModal',
 379     value => throwIfNotValid(value, isUserNoModal, 'no welcome modal')
 380   )
 381   @Column
 382   noWelcomeModal: boolean
 383
 384   @AllowNull(false)
 385   @Default(false)
 386   @Is(
 387     'UserNoAccountSetupWarningModal',
 388     value => throwIfNotValid(value, isUserNoModal, 'no account setup warning modal')
 389   )
 390   @Column
 391   noAccountSetupWarningModal: boolean
 392
 393   @AllowNull(true)
 394   @Default(null)
 395   @Column
 396   pluginAuth: string
 397
 398   @AllowNull(false)
 399   @Default(DataType.UUIDV4)
 400   @IsUUID(4)
 401   @Column(DataType.UUID)
 402   feedToken: string
 403
 404   @AllowNull(true)
 405   @Default(null)
 406   @Column
 407   lastLoginDate: Date
 408
 409   @CreatedAt
 410   createdAt: Date
 411
 412   @UpdatedAt
 413   updatedAt: Date
 414
 415   @HasOne(() => AccountModel, {
 416     foreignKey: 'userId',
 417     onDelete: 'cascade',
 418     hooks: true
 419   })
 420   Account: AccountModel
 421
 422   @HasOne(() => UserNotificationSettingModel, {
 423     foreignKey: 'userId',
 424     onDelete: 'cascade',
 425     hooks: true
 426   })
 427   NotificationSetting: UserNotificationSettingModel
 428
 429   @HasMany(() => VideoImportModel, {
 430     foreignKey: 'userId',
 431     onDelete: 'cascade'
 432   })
 433   VideoImports: VideoImportModel[]
 434
 435   @HasMany(() => OAuthTokenModel, {
 436     foreignKey: 'userId',
 437     onDelete: 'cascade'
 438   })
 439   OAuthTokens: OAuthTokenModel[]
 440
 441   @BeforeCreate
 442   @BeforeUpdate
 443   static cryptPasswordIfNeeded (instance: UserModel) {
 444     if (instance.changed('password') && instance.password) {
 445       return cryptPassword(instance.password)
 446         .then(hash => {
 447           instance.password = hash
 448           return undefined
 449         })
 450     }
 451   }
 452
 453   @AfterUpdate
 454   @AfterDestroy
 455   static removeTokenCache (instance: UserModel) {
 456     return TokensCache.Instance.clearCacheByUserId(instance.id)
 457   }
 458
 459   static countTotal () {
 460     return this.count()
 461   }
 462
 463   static listForAdminApi (parameters: {
 464     start: number
 465     count: number
 466     sort: string
 467     search?: string
 468     blocked?: boolean
 469   }) {
 470     const { start, count, sort, search, blocked } = parameters
 471     const where: WhereOptions = {}
 472
 473     if (search) {
 474       Object.assign(where, {
 475         [Op.or]: [
 476           {
 477             email: {
 478               [Op.iLike]: '%' + search + '%'
 479             }
 480           },
 481           {
 482             username: {
 483               [Op.iLike]: '%' + search + '%'
 484             }
 485           }
 486         ]
 487       })
 488     }
 489
 490     if (blocked !== undefined) {
 491       Object.assign(where, { blocked })
 492     }
 493
 494     const query: FindOptions = {
 495       offset: start,
 496       limit: count,
 497       order: getAdminUsersSort(sort),
 498       where
 499     }
 500
 501     return Promise.all([
 502       UserModel.unscoped().count(query),
 503       UserModel.scope([ 'defaultScope', ScopeNames.WITH_QUOTA ]).findAll(query)
 504     ]).then(([ total, data ]) => ({ total, data }))
 505   }
 506
 507   static listWithRight (right: UserRight): Promise<MUserDefault[]> {
 508     const roles = Object.keys(USER_ROLE_LABELS)
 509                         .map(k => parseInt(k, 10) as UserRole)
 510                         .filter(role => hasUserRight(role, right))
 511
 512     const query = {
 513       where: {
 514         role: {
 515           [Op.in]: roles
 516         }
 517       }
 518     }
 519
 520     return UserModel.findAll(query)
 521   }
 522
 523   static listUserSubscribersOf (actorId: number): Promise<MUserWithNotificationSetting[]> {
 524     const query = {
 525       include: [
 526         {
 527           model: UserNotificationSettingModel.unscoped(),
 528           required: true
 529         },
 530         {
 531           attributes: [ 'userId' ],
 532           model: AccountModel.unscoped(),
 533           required: true,
 534           include: [
 535             {
 536               attributes: [],
 537               model: ActorModel.unscoped(),
 538               required: true,
 539               where: {
 540                 serverId: null
 541               },
 542               include: [
 543                 {
 544                   attributes: [],
 545                   as: 'ActorFollowings',
 546                   model: ActorFollowModel.unscoped(),
 547                   required: true,
 548                   where: {
 549                     targetActorId: actorId
 550                   }
 551                 }
 552               ]
 553             }
 554           ]
 555         }
 556       ]
 557     }
 558
 559     return UserModel.unscoped().findAll(query)
 560   }
 561
 562   static listByUsernames (usernames: string[]): Promise<MUserDefault[]> {
 563     const query = {
 564       where: {
 565         username: usernames
 566       }
 567     }
 568
 569     return UserModel.findAll(query)
 570   }
 571
 572   static loadById (id: number): Promise<MUser> {
 573     return UserModel.unscoped().findByPk(id)
 574   }
 575
 576   static loadByIdFull (id: number): Promise<MUserDefault> {
 577     return UserModel.findByPk(id)
 578   }
 579
 580   static loadByIdWithChannels (id: number, withStats = false): Promise<MUserDefault> {
 581     const scopes = [
 582       ScopeNames.WITH_VIDEOCHANNELS
 583     ]
 584
 585     if (withStats) {
 586       scopes.push(ScopeNames.WITH_QUOTA)
 587       scopes.push(ScopeNames.WITH_STATS)
 588     }
 589
 590     return UserModel.scope(scopes).findByPk(id)
 591   }
 592
 593   static loadByUsername (username: string): Promise<MUserDefault> {
 594     const query = {
 595       where: {
 596         username
 597       }
 598     }
 599
 600     return UserModel.findOne(query)
 601   }
 602
 603   static loadForMeAPI (id: number): Promise<MUserNotifSettingChannelDefault> {
 604     const query = {
 605       where: {
 606         id
 607       }
 608     }
 609
 610     return UserModel.scope(ScopeNames.FOR_ME_API).findOne(query)
 611   }
 612
 613   static loadByEmail (email: string): Promise<MUserDefault> {
 614     const query = {
 615       where: {
 616         email
 617       }
 618     }
 619
 620     return UserModel.findOne(query)
 621   }
 622
 623   static loadByUsernameOrEmail (username: string, email?: string): Promise<MUserDefault> {
 624     if (!email) email = username
 625
 626     const query = {
 627       where: {
 628         [Op.or]: [
 629           where(fn('lower', col('username')), fn('lower', username) as any),
 630
 631           { email }
 632         ]
 633       }
 634     }
 635
 636     return UserModel.findOne(query)
 637   }
 638
 639   static loadByVideoId (videoId: number): Promise<MUserDefault> {
 640     const query = {
 641       include: [
 642         {
 643           required: true,
 644           attributes: [ 'id' ],
 645           model: AccountModel.unscoped(),
 646           include: [
 647             {
 648               required: true,
 649               attributes: [ 'id' ],
 650               model: VideoChannelModel.unscoped(),
 651               include: [
 652                 {
 653                   required: true,
 654                   attributes: [ 'id' ],
 655                   model: VideoModel.unscoped(),
 656                   where: {
 657                     id: videoId
 658                   }
 659                 }
 660               ]
 661             }
 662           ]
 663         }
 664       ]
 665     }
 666
 667     return UserModel.findOne(query)
 668   }
 669
 670   static loadByVideoImportId (videoImportId: number): Promise<MUserDefault> {
 671     const query = {
 672       include: [
 673         {
 674           required: true,
 675           attributes: [ 'id' ],
 676           model: VideoImportModel.unscoped(),
 677           where: {
 678             id: videoImportId
 679           }
 680         }
 681       ]
 682     }
 683
 684     return UserModel.findOne(query)
 685   }
 686
 687   static loadByChannelActorId (videoChannelActorId: number): Promise<MUserDefault> {
 688     const query = {
 689       include: [
 690         {
 691           required: true,
 692           attributes: [ 'id' ],
 693           model: AccountModel.unscoped(),
 694           include: [
 695             {
 696               required: true,
 697               attributes: [ 'id' ],
 698               model: VideoChannelModel.unscoped(),
 699               where: {
 700                 actorId: videoChannelActorId
 701               }
 702             }
 703           ]
 704         }
 705       ]
 706     }
 707
 708     return UserModel.findOne(query)
 709   }
 710
 711   static loadByAccountActorId (accountActorId: number): Promise<MUserDefault> {
 712     const query = {
 713       include: [
 714         {
 715           required: true,
 716           attributes: [ 'id' ],
 717           model: AccountModel.unscoped(),
 718           where: {
 719             actorId: accountActorId
 720           }
 721         }
 722       ]
 723     }
 724
 725     return UserModel.findOne(query)
 726   }
 727
 728   static loadByLiveId (liveId: number): Promise<MUser> {
 729     const query = {
 730       include: [
 731         {
 732           attributes: [ 'id' ],
 733           model: AccountModel.unscoped(),
 734           required: true,
 735           include: [
 736             {
 737               attributes: [ 'id' ],
 738               model: VideoChannelModel.unscoped(),
 739               required: true,
 740               include: [
 741                 {
 742                   attributes: [ 'id' ],
 743                   model: VideoModel.unscoped(),
 744                   required: true,
 745                   include: [
 746                     {
 747                       attributes: [],
 748                       model: VideoLiveModel.unscoped(),
 749                       required: true,
 750                       where: {
 751                         id: liveId
 752                       }
 753                     }
 754                   ]
 755                 }
 756               ]
 757             }
 758           ]
 759         }
 760       ]
 761     }
 762
 763     return UserModel.unscoped().findOne(query)
 764   }
 765
 766   static generateUserQuotaBaseSQL (options: {
 767     whereUserId: '$userId' | '"UserModel"."id"'
 768     withSelect: boolean
 769     daily: boolean
 770   }) {
 771     const andWhere = options.daily === true
 772       ? 'AND "video"."createdAt" > now() - interval \'24 hours\''
 773       : ''
 774
 775     const videoChannelJoin = 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 776       'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
 777       `WHERE "account"."userId" = ${options.whereUserId} ${andWhere}`
 778
 779     const webtorrentFiles = 'SELECT "videoFile"."size" AS "size", "video"."id" AS "videoId" FROM "videoFile" ' +
 780       'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
 781       videoChannelJoin
 782
 783     const hlsFiles = 'SELECT "videoFile"."size" AS "size", "video"."id" AS "videoId" FROM "videoFile" ' +
 784       'INNER JOIN "videoStreamingPlaylist" ON "videoFile"."videoStreamingPlaylistId" = "videoStreamingPlaylist".id ' +
 785       'INNER JOIN "video" ON "videoStreamingPlaylist"."videoId" = "video"."id" ' +
 786       videoChannelJoin
 787
 788     return 'SELECT COALESCE(SUM("size"), 0) AS "total" ' +
 789       'FROM (' +
 790         `SELECT MAX("t1"."size") AS "size" FROM (${webtorrentFiles} UNION ${hlsFiles}) t1 ` +
 791         'GROUP BY "t1"."videoId"' +
 792       ') t2'
 793   }
 794
 795   static getTotalRawQuery (query: string, userId: number) {
 796     const options = {
 797       bind: { userId },
 798       type: QueryTypes.SELECT as QueryTypes.SELECT
 799     }
 800
 801     return UserModel.sequelize.query<{ total: string }>(query, options)
 802                     .then(([ { total } ]) => {
 803                       if (total === null) return 0
 804
 805                       return parseInt(total, 10)
 806                     })
 807   }
 808
 809   static async getStats () {
 810     function getActiveUsers (days: number) {
 811       const query = {
 812         where: {
 813           [Op.and]: [
 814             literal(`"lastLoginDate" > NOW() - INTERVAL '${days}d'`)
 815           ]
 816         }
 817       }
 818
 819       return UserModel.unscoped().count(query)
 820     }
 821
 822     const totalUsers = await UserModel.unscoped().count()
 823     const totalDailyActiveUsers = await getActiveUsers(1)
 824     const totalWeeklyActiveUsers = await getActiveUsers(7)
 825     const totalMonthlyActiveUsers = await getActiveUsers(30)
 826     const totalHalfYearActiveUsers = await getActiveUsers(180)
 827
 828     return {
 829       totalUsers,
 830       totalDailyActiveUsers,
 831       totalWeeklyActiveUsers,
 832       totalMonthlyActiveUsers,
 833       totalHalfYearActiveUsers
 834     }
 835   }
 836
 837   static autoComplete (search: string) {
 838     const query = {
 839       where: {
 840         username: {
 841           [Op.like]: `%${search}%`
 842         }
 843       },
 844       limit: 10
 845     }
 846
 847     return UserModel.findAll(query)
 848                     .then(u => u.map(u => u.username))
 849   }
 850
 851   hasRight (right: UserRight) {
 852     return hasUserRight(this.role, right)
 853   }
 854
 855   hasAdminFlag (flag: UserAdminFlag) {
 856     return this.adminFlags & flag
 857   }
 858
 859   isPasswordMatch (password: string) {
 860     return comparePassword(password, this.password)
 861   }
 862
 863   toFormattedJSON (this: MUserFormattable, parameters: { withAdminFlags?: boolean } = {}): User {
 864     const videoQuotaUsed = this.get('videoQuotaUsed')
 865     const videoQuotaUsedDaily = this.get('videoQuotaUsedDaily')
 866     const videosCount = this.get('videosCount')
 867     const [ abusesCount, abusesAcceptedCount ] = (this.get('abusesCount') as string || ':').split(':')
 868     const abusesCreatedCount = this.get('abusesCreatedCount')
 869     const videoCommentsCount = this.get('videoCommentsCount')
 870
 871     const json: User = {
 872       id: this.id,
 873       username: this.username,
 874       email: this.email,
 875       theme: getThemeOrDefault(this.theme, DEFAULT_USER_THEME_NAME),
 876
 877       pendingEmail: this.pendingEmail,
 878       emailVerified: this.emailVerified,
 879
 880       nsfwPolicy: this.nsfwPolicy,
 881
 882       // FIXME: deprecated in 4.1
 883       webTorrentEnabled: this.p2pEnabled,
 884       p2pEnabled: this.p2pEnabled,
 885
 886       videosHistoryEnabled: this.videosHistoryEnabled,
 887       autoPlayVideo: this.autoPlayVideo,
 888       autoPlayNextVideo: this.autoPlayNextVideo,
 889       autoPlayNextVideoPlaylist: this.autoPlayNextVideoPlaylist,
 890       videoLanguages: this.videoLanguages,
 891
 892       role: this.role,
 893       roleLabel: USER_ROLE_LABELS[this.role],
 894
 895       videoQuota: this.videoQuota,
 896       videoQuotaDaily: this.videoQuotaDaily,
 897
 898       videoQuotaUsed: videoQuotaUsed !== undefined
 899         ? parseInt(videoQuotaUsed + '', 10) + LiveQuotaStore.Instance.getLiveQuotaOf(this.id)
 900         : undefined,
 901
 902       videoQuotaUsedDaily: videoQuotaUsedDaily !== undefined
 903         ? parseInt(videoQuotaUsedDaily + '', 10) + LiveQuotaStore.Instance.getLiveQuotaOf(this.id)
 904         : undefined,
 905
 906       videosCount: videosCount !== undefined
 907         ? parseInt(videosCount + '', 10)
 908         : undefined,
 909       abusesCount: abusesCount
 910         ? parseInt(abusesCount, 10)
 911         : undefined,
 912       abusesAcceptedCount: abusesAcceptedCount
 913         ? parseInt(abusesAcceptedCount, 10)
 914         : undefined,
 915       abusesCreatedCount: abusesCreatedCount !== undefined
 916         ? parseInt(abusesCreatedCount + '', 10)
 917         : undefined,
 918       videoCommentsCount: videoCommentsCount !== undefined
 919         ? parseInt(videoCommentsCount + '', 10)
 920         : undefined,
 921
 922       noInstanceConfigWarningModal: this.noInstanceConfigWarningModal,
 923       noWelcomeModal: this.noWelcomeModal,
 924       noAccountSetupWarningModal: this.noAccountSetupWarningModal,
 925
 926       blocked: this.blocked,
 927       blockedReason: this.blockedReason,
 928
 929       account: this.Account.toFormattedJSON(),
 930
 931       notificationSettings: this.NotificationSetting
 932         ? this.NotificationSetting.toFormattedJSON()
 933         : undefined,
 934
 935       videoChannels: [],
 936
 937       createdAt: this.createdAt,
 938
 939       pluginAuth: this.pluginAuth,
 940
 941       lastLoginDate: this.lastLoginDate
 942     }
 943
 944     if (parameters.withAdminFlags) {
 945       Object.assign(json, { adminFlags: this.adminFlags })
 946     }
 947
 948     if (Array.isArray(this.Account.VideoChannels) === true) {
 949       json.videoChannels = this.Account.VideoChannels
 950                                .map(c => c.toFormattedJSON())
 951                                .sort((v1, v2) => {
 952                                  if (v1.createdAt < v2.createdAt) return -1
 953                                  if (v1.createdAt === v2.createdAt) return 0
 954
 955                                  return 1
 956                                })
 957     }
 958
 959     return json
 960   }
 961
 962   toMeFormattedJSON (this: MMyUserFormattable): MyUser {
 963     const formatted = this.toFormattedJSON({ withAdminFlags: true })
 964
 965     const specialPlaylists = this.Account.VideoPlaylists
 966                                  .map(p => ({ id: p.id, name: p.name, type: p.type }))
 967
 968     return Object.assign(formatted, { specialPlaylists })
 969   }
 970 }