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