server/models/account/user.ts

   1 import { col, FindOptions, fn, literal, Op, QueryTypes, where, 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, VideoAbuseState, VideoPlaylistType, VideoPrivacy } 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/types/models'
  72
  73 enum ScopeNames {
  74   FOR_ME_API = 'FOR_ME_API',
  75   WITH_VIDEOCHANNELS = 'WITH_VIDEOCHANNELS',
  76   WITH_STATS = 'WITH_STATS'
  77 }
  78
  79 @DefaultScope(() => ({
  80   include: [
  81     {
  82       model: AccountModel,
  83       required: true
  84     },
  85     {
  86       model: UserNotificationSettingModel,
  87       required: true
  88     }
  89   ]
  90 }))
  91 @Scopes(() => ({
  92   [ScopeNames.FOR_ME_API]: {
  93     include: [
  94       {
  95         model: AccountModel,
  96         include: [
  97           {
  98             model: VideoChannelModel
  99           },
 100           {
 101             attributes: [ 'id', 'name', 'type' ],
 102             model: VideoPlaylistModel.unscoped(),
 103             required: true,
 104             where: {
 105               type: {
 106                 [Op.ne]: VideoPlaylistType.REGULAR
 107               }
 108             }
 109           }
 110         ]
 111       },
 112       {
 113         model: UserNotificationSettingModel,
 114         required: true
 115       }
 116     ]
 117   },
 118   [ScopeNames.WITH_VIDEOCHANNELS]: {
 119     include: [
 120       {
 121         model: AccountModel,
 122         include: [
 123           {
 124             model: VideoChannelModel
 125           },
 126           {
 127             attributes: [ 'id', 'name', 'type' ],
 128             model: VideoPlaylistModel.unscoped(),
 129             required: true,
 130             where: {
 131               type: {
 132                 [Op.ne]: VideoPlaylistType.REGULAR
 133               }
 134             }
 135           }
 136         ]
 137       }
 138     ]
 139   },
 140   [ScopeNames.WITH_STATS]: {
 141     attributes: {
 142       include: [
 143         [
 144           literal(
 145             '(' +
 146               UserModel.generateUserQuotaBaseSQL({
 147                 withSelect: false,
 148                 whereUserId: '"UserModel"."id"'
 149               }) +
 150             ')'
 151           ),
 152           'videoQuotaUsed'
 153         ],
 154         [
 155           literal(
 156             '(' +
 157               'SELECT COUNT("video"."id") ' +
 158               'FROM "video" ' +
 159               'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 160               'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
 161               'WHERE "account"."userId" = "UserModel"."id"' +
 162             ')'
 163           ),
 164           'videosCount'
 165         ],
 166         [
 167           literal(
 168             '(' +
 169               `SELECT concat_ws(':', "abuses", "acceptedAbuses") ` +
 170               'FROM (' +
 171                 'SELECT COUNT("videoAbuse"."id") AS "abuses", ' +
 172                        `COUNT("videoAbuse"."id") FILTER (WHERE "videoAbuse"."state" = ${VideoAbuseState.ACCEPTED}) AS "acceptedAbuses" ` +
 173                 'FROM "videoAbuse" ' +
 174                 'INNER JOIN "video" ON "videoAbuse"."videoId" = "video"."id" ' +
 175                 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 176                 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
 177                 'WHERE "account"."userId" = "UserModel"."id"' +
 178               ') t' +
 179             ')'
 180           ),
 181           'videoAbusesCount'
 182         ],
 183         [
 184           literal(
 185             '(' +
 186               'SELECT COUNT("videoAbuse"."id") ' +
 187               'FROM "videoAbuse" ' +
 188               'INNER JOIN "account" ON "account"."id" = "videoAbuse"."reporterAccountId" ' +
 189               'WHERE "account"."userId" = "UserModel"."id"' +
 190             ')'
 191           ),
 192           'videoAbusesCreatedCount'
 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<UserModel> {
 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(true)
 357   @Default(null)
 358   @Column
 359   lastLoginDate: Date
 360
 361   @CreatedAt
 362   createdAt: Date
 363
 364   @UpdatedAt
 365   updatedAt: Date
 366
 367   @HasOne(() => AccountModel, {
 368     foreignKey: 'userId',
 369     onDelete: 'cascade',
 370     hooks: true
 371   })
 372   Account: AccountModel
 373
 374   @HasOne(() => UserNotificationSettingModel, {
 375     foreignKey: 'userId',
 376     onDelete: 'cascade',
 377     hooks: true
 378   })
 379   NotificationSetting: UserNotificationSettingModel
 380
 381   @HasMany(() => VideoImportModel, {
 382     foreignKey: 'userId',
 383     onDelete: 'cascade'
 384   })
 385   VideoImports: VideoImportModel[]
 386
 387   @HasMany(() => OAuthTokenModel, {
 388     foreignKey: 'userId',
 389     onDelete: 'cascade'
 390   })
 391   OAuthTokens: OAuthTokenModel[]
 392
 393   @BeforeCreate
 394   @BeforeUpdate
 395   static cryptPasswordIfNeeded (instance: UserModel) {
 396     if (instance.changed('password') && instance.password) {
 397       return cryptPassword(instance.password)
 398         .then(hash => {
 399           instance.password = hash
 400           return undefined
 401         })
 402     }
 403   }
 404
 405   @AfterUpdate
 406   @AfterDestroy
 407   static removeTokenCache (instance: UserModel) {
 408     return clearCacheByUserId(instance.id)
 409   }
 410
 411   static countTotal () {
 412     return this.count()
 413   }
 414
 415   static listForApi (parameters: {
 416     start: number
 417     count: number
 418     sort: string
 419     search?: string
 420     blocked?: boolean
 421   }) {
 422     const { start, count, sort, search, blocked } = parameters
 423     const where: WhereOptions = {}
 424
 425     if (search) {
 426       Object.assign(where, {
 427         [Op.or]: [
 428           {
 429             email: {
 430               [Op.iLike]: '%' + search + '%'
 431             }
 432           },
 433           {
 434             username: {
 435               [Op.iLike]: '%' + search + '%'
 436             }
 437           }
 438         ]
 439       })
 440     }
 441
 442     if (blocked !== undefined) {
 443       Object.assign(where, {
 444         blocked: blocked
 445       })
 446     }
 447
 448     const query: FindOptions = {
 449       attributes: {
 450         include: [
 451           [
 452             literal(
 453               '(' +
 454                 UserModel.generateUserQuotaBaseSQL({
 455                   withSelect: false,
 456                   whereUserId: '"UserModel"."id"'
 457                 }) +
 458               ')'
 459             ),
 460             'videoQuotaUsed'
 461           ] as any // FIXME: typings
 462         ]
 463       },
 464       offset: start,
 465       limit: count,
 466       order: getSort(sort),
 467       where
 468     }
 469
 470     return UserModel.findAndCountAll(query)
 471                     .then(({ rows, count }) => {
 472                       return {
 473                         data: rows,
 474                         total: count
 475                       }
 476                     })
 477   }
 478
 479   static listWithRight (right: UserRight): Bluebird<MUserDefault[]> {
 480     const roles = Object.keys(USER_ROLE_LABELS)
 481                         .map(k => parseInt(k, 10) as UserRole)
 482                         .filter(role => hasUserRight(role, right))
 483
 484     const query = {
 485       where: {
 486         role: {
 487           [Op.in]: roles
 488         }
 489       }
 490     }
 491
 492     return UserModel.findAll(query)
 493   }
 494
 495   static listUserSubscribersOf (actorId: number): Bluebird<MUserWithNotificationSetting[]> {
 496     const query = {
 497       include: [
 498         {
 499           model: UserNotificationSettingModel.unscoped(),
 500           required: true
 501         },
 502         {
 503           attributes: [ 'userId' ],
 504           model: AccountModel.unscoped(),
 505           required: true,
 506           include: [
 507             {
 508               attributes: [],
 509               model: ActorModel.unscoped(),
 510               required: true,
 511               where: {
 512                 serverId: null
 513               },
 514               include: [
 515                 {
 516                   attributes: [],
 517                   as: 'ActorFollowings',
 518                   model: ActorFollowModel.unscoped(),
 519                   required: true,
 520                   where: {
 521                     targetActorId: actorId
 522                   }
 523                 }
 524               ]
 525             }
 526           ]
 527         }
 528       ]
 529     }
 530
 531     return UserModel.unscoped().findAll(query)
 532   }
 533
 534   static listByUsernames (usernames: string[]): Bluebird<MUserDefault[]> {
 535     const query = {
 536       where: {
 537         username: usernames
 538       }
 539     }
 540
 541     return UserModel.findAll(query)
 542   }
 543
 544   static loadById (id: number, withStats = false): Bluebird<MUserDefault> {
 545     const scopes = [
 546       ScopeNames.WITH_VIDEOCHANNELS
 547     ]
 548
 549     if (withStats) scopes.push(ScopeNames.WITH_STATS)
 550
 551     return UserModel.scope(scopes).findByPk(id)
 552   }
 553
 554   static loadByUsername (username: string): Bluebird<MUserDefault> {
 555     const query = {
 556       where: {
 557         username: { [Op.iLike]: username }
 558       }
 559     }
 560
 561     return UserModel.findOne(query)
 562   }
 563
 564   static loadForMeAPI (username: string): Bluebird<MUserNotifSettingChannelDefault> {
 565     const query = {
 566       where: {
 567         username: { [Op.iLike]: username }
 568       }
 569     }
 570
 571     return UserModel.scope(ScopeNames.FOR_ME_API).findOne(query)
 572   }
 573
 574   static loadByEmail (email: string): Bluebird<MUserDefault> {
 575     const query = {
 576       where: {
 577         email
 578       }
 579     }
 580
 581     return UserModel.findOne(query)
 582   }
 583
 584   static loadByUsernameOrEmail (username: string, email?: string): Bluebird<MUserDefault> {
 585     if (!email) email = username
 586
 587     const query = {
 588       where: {
 589         [Op.or]: [
 590           where(fn('lower', col('username')), fn('lower', username)),
 591
 592           { email }
 593         ]
 594       }
 595     }
 596
 597     return UserModel.findOne(query)
 598   }
 599
 600   static loadByVideoId (videoId: number): Bluebird<MUserDefault> {
 601     const query = {
 602       include: [
 603         {
 604           required: true,
 605           attributes: [ 'id' ],
 606           model: AccountModel.unscoped(),
 607           include: [
 608             {
 609               required: true,
 610               attributes: [ 'id' ],
 611               model: VideoChannelModel.unscoped(),
 612               include: [
 613                 {
 614                   required: true,
 615                   attributes: [ 'id' ],
 616                   model: VideoModel.unscoped(),
 617                   where: {
 618                     id: videoId
 619                   }
 620                 }
 621               ]
 622             }
 623           ]
 624         }
 625       ]
 626     }
 627
 628     return UserModel.findOne(query)
 629   }
 630
 631   static loadByVideoImportId (videoImportId: number): Bluebird<MUserDefault> {
 632     const query = {
 633       include: [
 634         {
 635           required: true,
 636           attributes: [ 'id' ],
 637           model: VideoImportModel.unscoped(),
 638           where: {
 639             id: videoImportId
 640           }
 641         }
 642       ]
 643     }
 644
 645     return UserModel.findOne(query)
 646   }
 647
 648   static loadByChannelActorId (videoChannelActorId: number): Bluebird<MUserDefault> {
 649     const query = {
 650       include: [
 651         {
 652           required: true,
 653           attributes: [ 'id' ],
 654           model: AccountModel.unscoped(),
 655           include: [
 656             {
 657               required: true,
 658               attributes: [ 'id' ],
 659               model: VideoChannelModel.unscoped(),
 660               where: {
 661                 actorId: videoChannelActorId
 662               }
 663             }
 664           ]
 665         }
 666       ]
 667     }
 668
 669     return UserModel.findOne(query)
 670   }
 671
 672   static loadByAccountActorId (accountActorId: number): Bluebird<MUserDefault> {
 673     const query = {
 674       include: [
 675         {
 676           required: true,
 677           attributes: [ 'id' ],
 678           model: AccountModel.unscoped(),
 679           where: {
 680             actorId: accountActorId
 681           }
 682         }
 683       ]
 684     }
 685
 686     return UserModel.findOne(query)
 687   }
 688
 689   static getOriginalVideoFileTotalFromUser (user: MUserId) {
 690     // Don't use sequelize because we need to use a sub query
 691     const query = UserModel.generateUserQuotaBaseSQL({
 692       withSelect: true,
 693       whereUserId: '$userId'
 694     })
 695
 696     return UserModel.getTotalRawQuery(query, user.id)
 697   }
 698
 699   // Returns cumulative size of all video files uploaded in the last 24 hours.
 700   static getOriginalVideoFileTotalDailyFromUser (user: MUserId) {
 701     // Don't use sequelize because we need to use a sub query
 702     const query = UserModel.generateUserQuotaBaseSQL({
 703       withSelect: true,
 704       whereUserId: '$userId',
 705       where: '"video"."createdAt" > now() - interval \'24 hours\''
 706     })
 707
 708     return UserModel.getTotalRawQuery(query, user.id)
 709   }
 710
 711   static async getStats () {
 712     function getActiveUsers (days: number) {
 713       const query = {
 714         where: {
 715           [Op.and]: [
 716             literal(`"lastLoginDate" > NOW() - INTERVAL '${days}d'`)
 717           ]
 718         }
 719       }
 720
 721       return UserModel.count(query)
 722     }
 723
 724     const totalUsers = await UserModel.count()
 725     const totalDailyActiveUsers = await getActiveUsers(1)
 726     const totalWeeklyActiveUsers = await getActiveUsers(7)
 727     const totalMonthlyActiveUsers = await getActiveUsers(30)
 728
 729     return {
 730       totalUsers,
 731       totalDailyActiveUsers,
 732       totalWeeklyActiveUsers,
 733       totalMonthlyActiveUsers
 734     }
 735   }
 736
 737   static autoComplete (search: string) {
 738     const query = {
 739       where: {
 740         username: {
 741           [Op.like]: `%${search}%`
 742         }
 743       },
 744       limit: 10
 745     }
 746
 747     return UserModel.findAll(query)
 748                     .then(u => u.map(u => u.username))
 749   }
 750
 751   canGetVideo (video: MVideoFullLight) {
 752     const videoUserId = video.VideoChannel.Account.userId
 753
 754     if (video.isBlacklisted()) {
 755       return videoUserId === this.id || this.hasRight(UserRight.MANAGE_VIDEO_BLACKLIST)
 756     }
 757
 758     if (video.privacy === VideoPrivacy.PRIVATE) {
 759       return video.VideoChannel && videoUserId === this.id || this.hasRight(UserRight.MANAGE_VIDEO_BLACKLIST)
 760     }
 761
 762     if (video.privacy === VideoPrivacy.INTERNAL) return true
 763
 764     return false
 765   }
 766
 767   hasRight (right: UserRight) {
 768     return hasUserRight(this.role, right)
 769   }
 770
 771   hasAdminFlag (flag: UserAdminFlag) {
 772     return this.adminFlags & flag
 773   }
 774
 775   isPasswordMatch (password: string) {
 776     return comparePassword(password, this.password)
 777   }
 778
 779   toFormattedJSON (this: MUserFormattable, parameters: { withAdminFlags?: boolean } = {}): User {
 780     const videoQuotaUsed = this.get('videoQuotaUsed')
 781     const videoQuotaUsedDaily = this.get('videoQuotaUsedDaily')
 782     const videosCount = this.get('videosCount')
 783     const [ videoAbusesCount, videoAbusesAcceptedCount ] = (this.get('videoAbusesCount') as string || ':').split(':')
 784     const videoAbusesCreatedCount = this.get('videoAbusesCreatedCount')
 785     const videoCommentsCount = this.get('videoCommentsCount')
 786
 787     const json: User = {
 788       id: this.id,
 789       username: this.username,
 790       email: this.email,
 791       theme: getThemeOrDefault(this.theme, DEFAULT_USER_THEME_NAME),
 792
 793       pendingEmail: this.pendingEmail,
 794       emailVerified: this.emailVerified,
 795
 796       nsfwPolicy: this.nsfwPolicy,
 797       webTorrentEnabled: this.webTorrentEnabled,
 798       videosHistoryEnabled: this.videosHistoryEnabled,
 799       autoPlayVideo: this.autoPlayVideo,
 800       autoPlayNextVideo: this.autoPlayNextVideo,
 801       autoPlayNextVideoPlaylist: this.autoPlayNextVideoPlaylist,
 802       videoLanguages: this.videoLanguages,
 803
 804       role: this.role,
 805       roleLabel: USER_ROLE_LABELS[this.role],
 806
 807       videoQuota: this.videoQuota,
 808       videoQuotaDaily: this.videoQuotaDaily,
 809       videoQuotaUsed: videoQuotaUsed !== undefined
 810         ? parseInt(videoQuotaUsed + '', 10)
 811         : undefined,
 812       videoQuotaUsedDaily: videoQuotaUsedDaily !== undefined
 813         ? parseInt(videoQuotaUsedDaily + '', 10)
 814         : undefined,
 815       videosCount: videosCount !== undefined
 816         ? parseInt(videosCount + '', 10)
 817         : undefined,
 818       videoAbusesCount: videoAbusesCount
 819         ? parseInt(videoAbusesCount, 10)
 820         : undefined,
 821       videoAbusesAcceptedCount: videoAbusesAcceptedCount
 822         ? parseInt(videoAbusesAcceptedCount, 10)
 823         : undefined,
 824       videoAbusesCreatedCount: videoAbusesCreatedCount !== undefined
 825         ? parseInt(videoAbusesCreatedCount + '', 10)
 826         : undefined,
 827       videoCommentsCount: videoCommentsCount !== undefined
 828         ? parseInt(videoCommentsCount + '', 10)
 829         : undefined,
 830
 831       noInstanceConfigWarningModal: this.noInstanceConfigWarningModal,
 832       noWelcomeModal: this.noWelcomeModal,
 833
 834       blocked: this.blocked,
 835       blockedReason: this.blockedReason,
 836
 837       account: this.Account.toFormattedJSON(),
 838
 839       notificationSettings: this.NotificationSetting
 840         ? this.NotificationSetting.toFormattedJSON()
 841         : undefined,
 842
 843       videoChannels: [],
 844
 845       createdAt: this.createdAt,
 846
 847       pluginAuth: this.pluginAuth,
 848
 849       lastLoginDate: this.lastLoginDate
 850     }
 851
 852     if (parameters.withAdminFlags) {
 853       Object.assign(json, { adminFlags: this.adminFlags })
 854     }
 855
 856     if (Array.isArray(this.Account.VideoChannels) === true) {
 857       json.videoChannels = this.Account.VideoChannels
 858                                .map(c => c.toFormattedJSON())
 859                                .sort((v1, v2) => {
 860                                  if (v1.createdAt < v2.createdAt) return -1
 861                                  if (v1.createdAt === v2.createdAt) return 0
 862
 863                                  return 1
 864                                })
 865     }
 866
 867     return json
 868   }
 869
 870   toMeFormattedJSON (this: MMyUserFormattable): MyUser {
 871     const formatted = this.toFormattedJSON()
 872
 873     const specialPlaylists = this.Account.VideoPlaylists
 874                                  .map(p => ({ id: p.id, name: p.name, type: p.type }))
 875
 876     return Object.assign(formatted, { specialPlaylists })
 877   }
 878
 879   async isAbleToUploadVideo (videoFile: { size: number }) {
 880     if (this.videoQuota === -1 && this.videoQuotaDaily === -1) return Promise.resolve(true)
 881
 882     const [ totalBytes, totalBytesDaily ] = await Promise.all([
 883       UserModel.getOriginalVideoFileTotalFromUser(this),
 884       UserModel.getOriginalVideoFileTotalDailyFromUser(this)
 885     ])
 886
 887     const uploadedTotal = videoFile.size + totalBytes
 888     const uploadedDaily = videoFile.size + totalBytesDaily
 889
 890     if (this.videoQuotaDaily === -1) return uploadedTotal < this.videoQuota
 891     if (this.videoQuota === -1) return uploadedDaily < this.videoQuotaDaily
 892
 893     return uploadedTotal < this.videoQuota && uploadedDaily < this.videoQuotaDaily
 894   }
 895
 896   private static generateUserQuotaBaseSQL (options: {
 897     whereUserId: '$userId' | '"UserModel"."id"'
 898     withSelect: boolean
 899     where?: string
 900   }) {
 901     const andWhere = options.where
 902       ? 'AND ' + options.where
 903       : ''
 904
 905     const videoChannelJoin = 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 906       'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
 907       `WHERE "account"."userId" = ${options.whereUserId} ${andWhere}`
 908
 909     const webtorrentFiles = 'SELECT "videoFile"."size" AS "size", "video"."id" AS "videoId" FROM "videoFile" ' +
 910       'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
 911       videoChannelJoin
 912
 913     const hlsFiles = 'SELECT "videoFile"."size" AS "size", "video"."id" AS "videoId" FROM "videoFile" ' +
 914       'INNER JOIN "videoStreamingPlaylist" ON "videoFile"."videoStreamingPlaylistId" = "videoStreamingPlaylist".id ' +
 915       'INNER JOIN "video" ON "videoStreamingPlaylist"."videoId" = "video"."id" ' +
 916       videoChannelJoin
 917
 918     return 'SELECT COALESCE(SUM("size"), 0) AS "total" ' +
 919       'FROM (' +
 920         `SELECT MAX("t1"."size") AS "size" FROM (${webtorrentFiles} UNION ${hlsFiles}) t1 ` +
 921         'GROUP BY "t1"."videoId"' +
 922       ') t2'
 923   }
 924
 925   private static getTotalRawQuery (query: string, userId: number) {
 926     const options = {
 927       bind: { userId },
 928       type: QueryTypes.SELECT as QueryTypes.SELECT
 929     }
 930
 931     return UserModel.sequelize.query<{ total: string }>(query, options)
 932                     .then(([ { total } ]) => {
 933                       if (total === null) return 0
 934
 935                       return parseInt(total, 10)
 936                     })
 937   }
 938 }