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