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