server/models/account/user.ts

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