abuse.ts 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624
  1. import { invert } from 'lodash'
  2. import { literal, Op, QueryTypes } from 'sequelize'
  3. import {
  4. AllowNull,
  5. BelongsTo,
  6. Column,
  7. CreatedAt,
  8. DataType,
  9. Default,
  10. ForeignKey,
  11. HasOne,
  12. Is,
  13. Model,
  14. Scopes,
  15. Table,
  16. UpdatedAt
  17. } from 'sequelize-typescript'
  18. import { isAbuseModerationCommentValid, isAbuseReasonValid, isAbuseStateValid } from '@server/helpers/custom-validators/abuses'
  19. import { abusePredefinedReasonsMap } from '@shared/core-utils'
  20. import {
  21. AbuseFilter,
  22. AbuseObject,
  23. AbusePredefinedReasons,
  24. AbusePredefinedReasonsString,
  25. AbuseState,
  26. AbuseVideoIs,
  27. AdminAbuse,
  28. AdminVideoAbuse,
  29. AdminVideoCommentAbuse,
  30. UserAbuse,
  31. UserVideoAbuse
  32. } from '@shared/models'
  33. import { AttributesOnly } from '@shared/typescript-utils'
  34. import { ABUSE_STATES, CONSTRAINTS_FIELDS } from '../../initializers/constants'
  35. import { MAbuseAdminFormattable, MAbuseAP, MAbuseFull, MAbuseReporter, MAbuseUserFormattable, MUserAccountId } from '../../types/models'
  36. import { AccountModel, ScopeNames as AccountScopeNames, SummaryOptions as AccountSummaryOptions } from '../account/account'
  37. import { getSort, throwIfNotValid } from '../utils'
  38. import { ThumbnailModel } from '../video/thumbnail'
  39. import { ScopeNames as VideoScopeNames, VideoModel } from '../video/video'
  40. import { VideoBlacklistModel } from '../video/video-blacklist'
  41. import { ScopeNames as VideoChannelScopeNames, SummaryOptions as ChannelSummaryOptions, VideoChannelModel } from '../video/video-channel'
  42. import { ScopeNames as CommentScopeNames, VideoCommentModel } from '../video/video-comment'
  43. import { buildAbuseListQuery, BuildAbusesQueryOptions } from './abuse-query-builder'
  44. import { VideoAbuseModel } from './video-abuse'
  45. import { VideoCommentAbuseModel } from './video-comment-abuse'
  46. export enum ScopeNames {
  47. FOR_API = 'FOR_API'
  48. }
  49. @Scopes(() => ({
  50. [ScopeNames.FOR_API]: () => {
  51. return {
  52. attributes: {
  53. include: [
  54. [
  55. literal(
  56. '(' +
  57. 'SELECT count(*) ' +
  58. 'FROM "abuseMessage" ' +
  59. 'WHERE "abuseId" = "AbuseModel"."id"' +
  60. ')'
  61. ),
  62. 'countMessages'
  63. ],
  64. [
  65. // we don't care about this count for deleted videos, so there are not included
  66. literal(
  67. '(' +
  68. 'SELECT count(*) ' +
  69. 'FROM "videoAbuse" ' +
  70. 'WHERE "videoId" = "VideoAbuse"."videoId" AND "videoId" IS NOT NULL' +
  71. ')'
  72. ),
  73. 'countReportsForVideo'
  74. ],
  75. [
  76. // we don't care about this count for deleted videos, so there are not included
  77. literal(
  78. '(' +
  79. 'SELECT t.nth ' +
  80. 'FROM ( ' +
  81. 'SELECT id, ' +
  82. 'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
  83. 'FROM "videoAbuse" ' +
  84. ') t ' +
  85. 'WHERE t.id = "VideoAbuse".id AND t.id IS NOT NULL' +
  86. ')'
  87. ),
  88. 'nthReportForVideo'
  89. ],
  90. [
  91. literal(
  92. '(' +
  93. 'SELECT count("abuse"."id") ' +
  94. 'FROM "abuse" ' +
  95. 'WHERE "abuse"."reporterAccountId" = "AbuseModel"."reporterAccountId"' +
  96. ')'
  97. ),
  98. 'countReportsForReporter'
  99. ],
  100. [
  101. literal(
  102. '(' +
  103. 'SELECT count("abuse"."id") ' +
  104. 'FROM "abuse" ' +
  105. 'WHERE "abuse"."flaggedAccountId" = "AbuseModel"."flaggedAccountId"' +
  106. ')'
  107. ),
  108. 'countReportsForReportee'
  109. ]
  110. ]
  111. },
  112. include: [
  113. {
  114. model: AccountModel.scope({
  115. method: [
  116. AccountScopeNames.SUMMARY,
  117. { actorRequired: false } as AccountSummaryOptions
  118. ]
  119. }),
  120. as: 'ReporterAccount'
  121. },
  122. {
  123. model: AccountModel.scope({
  124. method: [
  125. AccountScopeNames.SUMMARY,
  126. { actorRequired: false } as AccountSummaryOptions
  127. ]
  128. }),
  129. as: 'FlaggedAccount'
  130. },
  131. {
  132. model: VideoCommentAbuseModel.unscoped(),
  133. include: [
  134. {
  135. model: VideoCommentModel.unscoped(),
  136. include: [
  137. {
  138. model: VideoModel.unscoped(),
  139. attributes: [ 'name', 'id', 'uuid' ]
  140. }
  141. ]
  142. }
  143. ]
  144. },
  145. {
  146. model: VideoAbuseModel.unscoped(),
  147. include: [
  148. {
  149. attributes: [ 'id', 'uuid', 'name', 'nsfw' ],
  150. model: VideoModel.unscoped(),
  151. include: [
  152. {
  153. attributes: [ 'filename', 'fileUrl', 'type' ],
  154. model: ThumbnailModel
  155. },
  156. {
  157. model: VideoChannelModel.scope({
  158. method: [
  159. VideoChannelScopeNames.SUMMARY,
  160. { withAccount: false, actorRequired: false } as ChannelSummaryOptions
  161. ]
  162. }),
  163. required: false
  164. },
  165. {
  166. attributes: [ 'id', 'reason', 'unfederated' ],
  167. required: false,
  168. model: VideoBlacklistModel
  169. }
  170. ]
  171. }
  172. ]
  173. }
  174. ]
  175. }
  176. }
  177. }))
  178. @Table({
  179. tableName: 'abuse',
  180. indexes: [
  181. {
  182. fields: [ 'reporterAccountId' ]
  183. },
  184. {
  185. fields: [ 'flaggedAccountId' ]
  186. }
  187. ]
  188. })
  189. export class AbuseModel extends Model<Partial<AttributesOnly<AbuseModel>>> {
  190. @AllowNull(false)
  191. @Default(null)
  192. @Is('AbuseReason', value => throwIfNotValid(value, isAbuseReasonValid, 'reason'))
  193. @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.REASON.max))
  194. reason: string
  195. @AllowNull(false)
  196. @Default(null)
  197. @Is('AbuseState', value => throwIfNotValid(value, isAbuseStateValid, 'state'))
  198. @Column
  199. state: AbuseState
  200. @AllowNull(true)
  201. @Default(null)
  202. @Is('AbuseModerationComment', value => throwIfNotValid(value, isAbuseModerationCommentValid, 'moderationComment', true))
  203. @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.MODERATION_COMMENT.max))
  204. moderationComment: string
  205. @AllowNull(true)
  206. @Default(null)
  207. @Column(DataType.ARRAY(DataType.INTEGER))
  208. predefinedReasons: AbusePredefinedReasons[]
  209. @CreatedAt
  210. createdAt: Date
  211. @UpdatedAt
  212. updatedAt: Date
  213. @ForeignKey(() => AccountModel)
  214. @Column
  215. reporterAccountId: number
  216. @BelongsTo(() => AccountModel, {
  217. foreignKey: {
  218. name: 'reporterAccountId',
  219. allowNull: true
  220. },
  221. as: 'ReporterAccount',
  222. onDelete: 'set null'
  223. })
  224. ReporterAccount: AccountModel
  225. @ForeignKey(() => AccountModel)
  226. @Column
  227. flaggedAccountId: number
  228. @BelongsTo(() => AccountModel, {
  229. foreignKey: {
  230. name: 'flaggedAccountId',
  231. allowNull: true
  232. },
  233. as: 'FlaggedAccount',
  234. onDelete: 'set null'
  235. })
  236. FlaggedAccount: AccountModel
  237. @HasOne(() => VideoCommentAbuseModel, {
  238. foreignKey: {
  239. name: 'abuseId',
  240. allowNull: false
  241. },
  242. onDelete: 'cascade'
  243. })
  244. VideoCommentAbuse: VideoCommentAbuseModel
  245. @HasOne(() => VideoAbuseModel, {
  246. foreignKey: {
  247. name: 'abuseId',
  248. allowNull: false
  249. },
  250. onDelete: 'cascade'
  251. })
  252. VideoAbuse: VideoAbuseModel
  253. static loadByIdWithReporter (id: number): Promise<MAbuseReporter> {
  254. const query = {
  255. where: {
  256. id
  257. },
  258. include: [
  259. {
  260. model: AccountModel,
  261. as: 'ReporterAccount'
  262. }
  263. ]
  264. }
  265. return AbuseModel.findOne(query)
  266. }
  267. static loadFull (id: number): Promise<MAbuseFull> {
  268. const query = {
  269. where: {
  270. id
  271. },
  272. include: [
  273. {
  274. model: AccountModel.scope(AccountScopeNames.SUMMARY),
  275. required: false,
  276. as: 'ReporterAccount'
  277. },
  278. {
  279. model: AccountModel.scope(AccountScopeNames.SUMMARY),
  280. as: 'FlaggedAccount'
  281. },
  282. {
  283. model: VideoAbuseModel,
  284. required: false,
  285. include: [
  286. {
  287. model: VideoModel.scope([ VideoScopeNames.WITH_ACCOUNT_DETAILS ])
  288. }
  289. ]
  290. },
  291. {
  292. model: VideoCommentAbuseModel,
  293. required: false,
  294. include: [
  295. {
  296. model: VideoCommentModel.scope([
  297. CommentScopeNames.WITH_ACCOUNT
  298. ]),
  299. include: [
  300. {
  301. model: VideoModel
  302. }
  303. ]
  304. }
  305. ]
  306. }
  307. ]
  308. }
  309. return AbuseModel.findOne(query)
  310. }
  311. static async listForAdminApi (parameters: {
  312. start: number
  313. count: number
  314. sort: string
  315. filter?: AbuseFilter
  316. serverAccountId: number
  317. user?: MUserAccountId
  318. id?: number
  319. predefinedReason?: AbusePredefinedReasonsString
  320. state?: AbuseState
  321. videoIs?: AbuseVideoIs
  322. search?: string
  323. searchReporter?: string
  324. searchReportee?: string
  325. searchVideo?: string
  326. searchVideoChannel?: string
  327. }) {
  328. const {
  329. start,
  330. count,
  331. sort,
  332. search,
  333. user,
  334. serverAccountId,
  335. state,
  336. videoIs,
  337. predefinedReason,
  338. searchReportee,
  339. searchVideo,
  340. filter,
  341. searchVideoChannel,
  342. searchReporter,
  343. id
  344. } = parameters
  345. const userAccountId = user ? user.Account.id : undefined
  346. const predefinedReasonId = predefinedReason ? abusePredefinedReasonsMap[predefinedReason] : undefined
  347. const queryOptions: BuildAbusesQueryOptions = {
  348. start,
  349. count,
  350. sort,
  351. id,
  352. filter,
  353. predefinedReasonId,
  354. search,
  355. state,
  356. videoIs,
  357. searchReportee,
  358. searchVideo,
  359. searchVideoChannel,
  360. searchReporter,
  361. serverAccountId,
  362. userAccountId
  363. }
  364. const [ total, data ] = await Promise.all([
  365. AbuseModel.internalCountForApi(queryOptions),
  366. AbuseModel.internalListForApi(queryOptions)
  367. ])
  368. return { total, data }
  369. }
  370. static async listForUserApi (parameters: {
  371. user: MUserAccountId
  372. start: number
  373. count: number
  374. sort: string
  375. id?: number
  376. search?: string
  377. state?: AbuseState
  378. }) {
  379. const {
  380. start,
  381. count,
  382. sort,
  383. search,
  384. user,
  385. state,
  386. id
  387. } = parameters
  388. const queryOptions: BuildAbusesQueryOptions = {
  389. start,
  390. count,
  391. sort,
  392. id,
  393. search,
  394. state,
  395. reporterAccountId: user.Account.id
  396. }
  397. const [ total, data ] = await Promise.all([
  398. AbuseModel.internalCountForApi(queryOptions),
  399. AbuseModel.internalListForApi(queryOptions)
  400. ])
  401. return { total, data }
  402. }
  403. buildBaseVideoCommentAbuse (this: MAbuseUserFormattable) {
  404. // Associated video comment could have been destroyed if the video has been deleted
  405. if (!this.VideoCommentAbuse || !this.VideoCommentAbuse.VideoComment) return null
  406. const entity = this.VideoCommentAbuse.VideoComment
  407. return {
  408. id: entity.id,
  409. threadId: entity.getThreadId(),
  410. text: entity.text ?? '',
  411. deleted: entity.isDeleted(),
  412. video: {
  413. id: entity.Video.id,
  414. name: entity.Video.name,
  415. uuid: entity.Video.uuid
  416. }
  417. }
  418. }
  419. buildBaseVideoAbuse (this: MAbuseUserFormattable): UserVideoAbuse {
  420. if (!this.VideoAbuse) return null
  421. const abuseModel = this.VideoAbuse
  422. const entity = abuseModel.Video || abuseModel.deletedVideo
  423. return {
  424. id: entity.id,
  425. uuid: entity.uuid,
  426. name: entity.name,
  427. nsfw: entity.nsfw,
  428. startAt: abuseModel.startAt,
  429. endAt: abuseModel.endAt,
  430. deleted: !abuseModel.Video,
  431. blacklisted: abuseModel.Video?.isBlacklisted() || false,
  432. thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
  433. channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel
  434. }
  435. }
  436. buildBaseAbuse (this: MAbuseUserFormattable, countMessages: number): UserAbuse {
  437. const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
  438. return {
  439. id: this.id,
  440. reason: this.reason,
  441. predefinedReasons,
  442. flaggedAccount: this.FlaggedAccount
  443. ? this.FlaggedAccount.toFormattedJSON()
  444. : null,
  445. state: {
  446. id: this.state,
  447. label: AbuseModel.getStateLabel(this.state)
  448. },
  449. countMessages,
  450. createdAt: this.createdAt,
  451. updatedAt: this.updatedAt
  452. }
  453. }
  454. toFormattedAdminJSON (this: MAbuseAdminFormattable): AdminAbuse {
  455. const countReportsForVideo = this.get('countReportsForVideo') as number
  456. const nthReportForVideo = this.get('nthReportForVideo') as number
  457. const countReportsForReporter = this.get('countReportsForReporter') as number
  458. const countReportsForReportee = this.get('countReportsForReportee') as number
  459. const countMessages = this.get('countMessages') as number
  460. const baseVideo = this.buildBaseVideoAbuse()
  461. const video: AdminVideoAbuse = baseVideo
  462. ? Object.assign(baseVideo, {
  463. countReports: countReportsForVideo,
  464. nthReport: nthReportForVideo
  465. })
  466. : null
  467. const comment: AdminVideoCommentAbuse = this.buildBaseVideoCommentAbuse()
  468. const abuse = this.buildBaseAbuse(countMessages || 0)
  469. return Object.assign(abuse, {
  470. video,
  471. comment,
  472. moderationComment: this.moderationComment,
  473. reporterAccount: this.ReporterAccount
  474. ? this.ReporterAccount.toFormattedJSON()
  475. : null,
  476. countReportsForReporter: (countReportsForReporter || 0),
  477. countReportsForReportee: (countReportsForReportee || 0)
  478. })
  479. }
  480. toFormattedUserJSON (this: MAbuseUserFormattable): UserAbuse {
  481. const countMessages = this.get('countMessages') as number
  482. const video = this.buildBaseVideoAbuse()
  483. const comment = this.buildBaseVideoCommentAbuse()
  484. const abuse = this.buildBaseAbuse(countMessages || 0)
  485. return Object.assign(abuse, {
  486. video,
  487. comment
  488. })
  489. }
  490. toActivityPubObject (this: MAbuseAP): AbuseObject {
  491. const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
  492. const object = this.VideoAbuse?.Video?.url || this.VideoCommentAbuse?.VideoComment?.url || this.FlaggedAccount.Actor.url
  493. const startAt = this.VideoAbuse?.startAt
  494. const endAt = this.VideoAbuse?.endAt
  495. return {
  496. type: 'Flag' as 'Flag',
  497. content: this.reason,
  498. mediaType: 'text/markdown',
  499. object,
  500. tag: predefinedReasons.map(r => ({
  501. type: 'Hashtag' as 'Hashtag',
  502. name: r
  503. })),
  504. startAt,
  505. endAt
  506. }
  507. }
  508. private static async internalCountForApi (parameters: BuildAbusesQueryOptions) {
  509. const { query, replacements } = buildAbuseListQuery(parameters, 'count')
  510. const options = {
  511. type: QueryTypes.SELECT as QueryTypes.SELECT,
  512. replacements
  513. }
  514. const [ { total } ] = await AbuseModel.sequelize.query<{ total: string }>(query, options)
  515. if (total === null) return 0
  516. return parseInt(total, 10)
  517. }
  518. private static async internalListForApi (parameters: BuildAbusesQueryOptions) {
  519. const { query, replacements } = buildAbuseListQuery(parameters, 'id')
  520. const options = {
  521. type: QueryTypes.SELECT as QueryTypes.SELECT,
  522. replacements
  523. }
  524. const rows = await AbuseModel.sequelize.query<{ id: string }>(query, options)
  525. const ids = rows.map(r => r.id)
  526. if (ids.length === 0) return []
  527. return AbuseModel.scope(ScopeNames.FOR_API)
  528. .findAll({
  529. order: getSort(parameters.sort),
  530. where: {
  531. id: {
  532. [Op.in]: ids
  533. }
  534. }
  535. })
  536. }
  537. private static getStateLabel (id: number) {
  538. return ABUSE_STATES[id] || 'Unknown'
  539. }
  540. private static getPredefinedReasonsStrings (predefinedReasons: AbusePredefinedReasons[]): AbusePredefinedReasonsString[] {
  541. const invertedPredefinedReasons = invert(abusePredefinedReasonsMap)
  542. return (predefinedReasons || [])
  543. .map(r => invertedPredefinedReasons[r] as AbusePredefinedReasonsString)
  544. .filter(v => !!v)
  545. }
  546. }