server/models/user/user.ts

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