server/models/account/user.ts

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