1 import { FindOptions, Op, Order, QueryTypes, Sequelize, Transaction } from 'sequelize'
15 } from 'sequelize-typescript'
16 import { getServerActor } from '@server/models/application/application'
17 import { MAccount, MAccountId, MUserAccountId } from '@server/types/models'
18 import { pick, uniqify } from '@shared/core-utils'
19 import { AttributesOnly } from '@shared/typescript-utils'
20 import { ActivityTagObject, ActivityTombstoneObject } from '../../../shared/models/activitypub/objects/common-objects'
21 import { VideoCommentObject } from '../../../shared/models/activitypub/objects/video-comment-object'
22 import { VideoComment, VideoCommentAdmin } from '../../../shared/models/videos/comment/video-comment.model'
23 import { actorNameAlphabet } from '../../helpers/custom-validators/activitypub/actor'
24 import { isActivityPubUrlValid } from '../../helpers/custom-validators/activitypub/misc'
25 import { regexpCapture } from '../../helpers/regexp'
26 import { CONSTRAINTS_FIELDS, WEBSERVER } from '../../initializers/constants'
29 MCommentAdminFormattable,
34 MCommentOwnerReplyVideoLight,
36 MCommentOwnerVideoFeed,
37 MCommentOwnerVideoReply,
39 } from '../../types/models/video'
40 import { VideoCommentAbuseModel } from '../abuse/video-comment-abuse'
41 import { AccountModel } from '../account/account'
42 import { ActorModel } from '../actor/actor'
43 import { buildLocalAccountIdsIn, throwIfNotValid } from '../utils'
44 import { ListVideoCommentsOptions, VideoCommentListQueryBuilder } from './sql/comment/video-comment-list-query-builder'
45 import { VideoModel } from './video'
46 import { VideoChannelModel } from './video-channel'
48 export enum ScopeNames {
49 WITH_ACCOUNT = 'WITH_ACCOUNT',
50 WITH_IN_REPLY_TO = 'WITH_IN_REPLY_TO',
51 WITH_VIDEO = 'WITH_VIDEO'
55 [ScopeNames.WITH_ACCOUNT]: {
62 [ScopeNames.WITH_IN_REPLY_TO]: {
65 model: VideoCommentModel,
66 as: 'InReplyToVideoComment'
70 [ScopeNames.WITH_VIDEO]: {
77 model: VideoChannelModel,
92 tableName: 'videoComment',
98 fields: [ 'videoId', 'originCommentId' ]
105 fields: [ 'accountId' ]
109 { name: 'createdAt', order: 'DESC' }
114 export class VideoCommentModel extends Model<Partial<AttributesOnly<VideoCommentModel>>> {
122 @Column(DataType.DATE)
126 @Is('VideoCommentUrl', value => throwIfNotValid(value, isActivityPubUrlValid, 'url'))
127 @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEOS.URL.max))
131 @Column(DataType.TEXT)
134 @ForeignKey(() => VideoCommentModel)
136 originCommentId: number
138 @BelongsTo(() => VideoCommentModel, {
140 name: 'originCommentId',
143 as: 'OriginVideoComment',
146 OriginVideoComment: VideoCommentModel
148 @ForeignKey(() => VideoCommentModel)
150 inReplyToCommentId: number
152 @BelongsTo(() => VideoCommentModel, {
154 name: 'inReplyToCommentId',
157 as: 'InReplyToVideoComment',
160 InReplyToVideoComment: VideoCommentModel | null
162 @ForeignKey(() => VideoModel)
166 @BelongsTo(() => VideoModel, {
174 @ForeignKey(() => AccountModel)
178 @BelongsTo(() => AccountModel, {
184 Account: AccountModel
186 @HasMany(() => VideoCommentAbuseModel, {
188 name: 'videoCommentId',
193 CommentAbuses: VideoCommentAbuseModel[]
195 static loadById (id: number, t?: Transaction): Promise<MComment> {
196 const query: FindOptions = {
202 if (t !== undefined) query.transaction = t
204 return VideoCommentModel.findOne(query)
207 static loadByIdAndPopulateVideoAndAccountAndReply (id: number, t?: Transaction): Promise<MCommentOwnerVideoReply> {
208 const query: FindOptions = {
214 if (t !== undefined) query.transaction = t
216 return VideoCommentModel
217 .scope([ ScopeNames.WITH_VIDEO, ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_IN_REPLY_TO ])
221 static loadByUrlAndPopulateAccountAndVideo (url: string, t?: Transaction): Promise<MCommentOwnerVideo> {
222 const query: FindOptions = {
228 if (t !== undefined) query.transaction = t
230 return VideoCommentModel.scope([ ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_VIDEO ]).findOne(query)
233 static loadByUrlAndPopulateReplyAndVideoUrlAndAccount (url: string, t?: Transaction): Promise<MCommentOwnerReplyVideoLight> {
234 const query: FindOptions = {
240 attributes: [ 'id', 'url' ],
241 model: VideoModel.unscoped()
246 if (t !== undefined) query.transaction = t
248 return VideoCommentModel.scope([ ScopeNames.WITH_IN_REPLY_TO, ScopeNames.WITH_ACCOUNT ]).findOne(query)
251 static listCommentsForApi (parameters: {
256 onLocalVideo?: boolean
259 searchAccount?: string
262 const queryOptions: ListVideoCommentsOptions = {
263 ...pick(parameters, [ 'start', 'count', 'sort', 'isLocal', 'search', 'searchVideo', 'searchAccount', 'onLocalVideo' ]),
270 new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MCommentAdminFormattable>(),
271 new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments()
272 ]).then(([ rows, count ]) => {
273 return { total: count, data: rows }
277 static async listThreadsForApi (parameters: {
279 isVideoOwned: boolean
283 user?: MUserAccountId
285 const { videoId, user } = parameters
287 const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user })
289 const commonOptions: ListVideoCommentsOptions = {
295 const listOptions: ListVideoCommentsOptions = {
297 ...pick(parameters, [ 'sort', 'start', 'count' ]),
300 includeReplyCounters: true
303 const countOptions: ListVideoCommentsOptions = {
309 const notDeletedCountOptions: ListVideoCommentsOptions = {
316 new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, listOptions).listComments<MCommentAdminFormattable>(),
317 new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, countOptions).countComments(),
318 new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, notDeletedCountOptions).countComments()
319 ]).then(([ rows, count, totalNotDeletedComments ]) => {
320 return { total: count, data: rows, totalNotDeletedComments }
324 static async listThreadCommentsForApi (parameters: {
327 user?: MUserAccountId
329 const { user } = parameters
331 const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user })
333 const queryOptions: ListVideoCommentsOptions = {
334 ...pick(parameters, [ 'videoId', 'threadId' ]),
340 includeReplyCounters: true
344 new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MCommentAdminFormattable>(),
345 new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments()
346 ]).then(([ rows, count ]) => {
347 return { total: count, data: rows }
351 static listThreadParentComments (comment: MCommentId, t: Transaction, order: 'ASC' | 'DESC' = 'ASC'): Promise<MCommentOwner[]> {
353 order: [ [ 'createdAt', order ] ] as Order,
356 [Op.in]: Sequelize.literal('(' +
357 'WITH RECURSIVE children (id, "inReplyToCommentId") AS ( ' +
358 `SELECT id, "inReplyToCommentId" FROM "videoComment" WHERE id = ${comment.id} ` +
360 'SELECT "parent"."id", "parent"."inReplyToCommentId" FROM "videoComment" "parent" ' +
361 'INNER JOIN "children" ON "children"."inReplyToCommentId" = "parent"."id"' +
363 'SELECT id FROM children' +
371 return VideoCommentModel
372 .scope([ ScopeNames.WITH_ACCOUNT ])
376 static async listAndCountByVideoForAP (parameters: {
377 video: MVideoImmutable
381 const { video } = parameters
383 const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user: null })
385 const queryOptions: ListVideoCommentsOptions = {
386 ...pick(parameters, [ 'start', 'count' ]),
388 selectType: 'comment-only',
396 new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MComment>(),
397 new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments()
398 ]).then(([ rows, count ]) => {
399 return { total: count, data: rows }
403 static async listForFeed (parameters: {
408 videoChannelId?: number
410 const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user: null })
412 const queryOptions: ListVideoCommentsOptions = {
413 ...pick(parameters, [ 'start', 'count', 'accountId', 'videoId', 'videoChannelId' ]),
424 return new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MCommentOwnerVideoFeed>()
427 static listForBulkDelete (ofAccount: MAccount, filter: { onVideosOfAccount?: MAccountId } = {}) {
428 const queryOptions: ListVideoCommentsOptions = {
429 selectType: 'comment-only',
431 accountId: ofAccount.id,
432 videoAccountOwnerId: filter.onVideosOfAccount?.id,
438 return new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MComment>()
441 static async getStats () {
442 const totalLocalVideoComments = await VideoCommentModel.count({
445 model: AccountModel.unscoped(),
449 model: ActorModel.unscoped(),
459 const totalVideoComments = await VideoCommentModel.count()
462 totalLocalVideoComments,
467 static listRemoteCommentUrlsOfLocalVideos () {
468 const query = `SELECT "videoComment".url FROM "videoComment" ` +
469 `INNER JOIN account ON account.id = "videoComment"."accountId" ` +
470 `INNER JOIN actor ON actor.id = "account"."actorId" AND actor."serverId" IS NOT NULL ` +
471 `INNER JOIN video ON video.id = "videoComment"."videoId" AND video.remote IS FALSE`
473 return VideoCommentModel.sequelize.query<{ url: string }>(query, {
474 type: QueryTypes.SELECT,
476 }).then(rows => rows.map(r => r.url))
479 static cleanOldCommentsOf (videoId: number, beforeUpdatedAt: Date) {
483 [Op.lt]: beforeUpdatedAt
487 [Op.notIn]: buildLocalAccountIdsIn()
489 // Do not delete Tombstones
494 return VideoCommentModel.destroy(query)
497 getCommentStaticPath () {
498 return this.Video.getWatchStaticPath() + ';threadId=' + this.getThreadId()
501 getThreadId (): number {
502 return this.originCommentId || this.id
506 if (!this.Account) return false
508 return this.Account.isOwned()
513 this.deletedAt = new Date()
514 this.accountId = null
518 return this.deletedAt !== null
522 let result: string[] = []
524 const localMention = `@(${actorNameAlphabet}+)`
525 const remoteMention = `${localMention}@${WEBSERVER.HOST}`
527 const mentionRegex = this.isOwned()
528 ? '(?:(?:' + remoteMention + ')|(?:' + localMention + '))' // Include local mentions?
529 : '(?:' + remoteMention + ')'
531 const firstMentionRegex = new RegExp(`^${mentionRegex} `, 'g')
532 const endMentionRegex = new RegExp(` ${mentionRegex}$`, 'g')
533 const remoteMentionsRegex = new RegExp(' ' + remoteMention + ' ', 'g')
535 result = result.concat(
536 regexpCapture(this.text, firstMentionRegex)
537 .map(([ , username1, username2 ]) => username1 || username2),
539 regexpCapture(this.text, endMentionRegex)
540 .map(([ , username1, username2 ]) => username1 || username2),
542 regexpCapture(this.text, remoteMentionsRegex)
543 .map(([ , username ]) => username)
546 // Include local mentions
547 if (this.isOwned()) {
548 const localMentionsRegex = new RegExp(' ' + localMention + ' ', 'g')
550 result = result.concat(
551 regexpCapture(this.text, localMentionsRegex)
552 .map(([ , username ]) => username)
556 return uniqify(result)
559 toFormattedJSON (this: MCommentFormattable) {
565 threadId: this.getThreadId(),
566 inReplyToCommentId: this.inReplyToCommentId || null,
567 videoId: this.videoId,
569 createdAt: this.createdAt,
570 updatedAt: this.updatedAt,
571 deletedAt: this.deletedAt,
573 isDeleted: this.isDeleted(),
575 totalRepliesFromVideoAuthor: this.get('totalRepliesFromVideoAuthor') || 0,
576 totalReplies: this.get('totalReplies') || 0,
578 account: this.Account
579 ? this.Account.toFormattedJSON()
584 toFormattedAdminJSON (this: MCommentAdminFormattable) {
590 threadId: this.getThreadId(),
591 inReplyToCommentId: this.inReplyToCommentId || null,
592 videoId: this.videoId,
594 createdAt: this.createdAt,
595 updatedAt: this.updatedAt,
599 uuid: this.Video.uuid,
600 name: this.Video.name
603 account: this.Account
604 ? this.Account.toFormattedJSON()
606 } as VideoCommentAdmin
609 toActivityPubObject (this: MCommentAP, threadParentComments: MCommentOwner[]): VideoCommentObject | ActivityTombstoneObject {
610 let inReplyTo: string
611 // New thread, so in AS we reply to the video
612 if (this.inReplyToCommentId === null) {
613 inReplyTo = this.Video.url
615 inReplyTo = this.InReplyToVideoComment.url
618 if (this.isDeleted()) {
624 published: this.createdAt.toISOString(),
625 updated: this.updatedAt.toISOString(),
626 deleted: this.deletedAt.toISOString()
630 const tag: ActivityTagObject[] = []
631 for (const parentComment of threadParentComments) {
632 if (!parentComment.Account) continue
634 const actor = parentComment.Account.Actor
639 name: `@${actor.preferredUsername}@${actor.getHost()}`
644 type: 'Note' as 'Note',
648 mediaType: 'text/markdown',
651 updated: this.updatedAt.toISOString(),
652 published: this.createdAt.toISOString(),
654 attributedTo: this.Account.Actor.url,
659 private static async buildBlockerAccountIds (options: {
661 }): Promise<number[]> {
662 const { user } = options
664 const serverActor = await getServerActor()
665 const blockerAccountIds = [ serverActor.Account.id ]
667 if (user) blockerAccountIds.push(user.Account.id)
669 return blockerAccountIds