• Para guardar os progressos dos perfis nos episódios vamos criar a nossa última tabela. Para isso, gere uma migration:

npx sequelize-cli migration:generate --name create-watch-times-table

• E então adicione o seu conteúdo:

// src/database/migrations/XXXXXXXXXXXXXX-create-watch-times-table

'use strict';

module.exports = {
  async up (queryInterface, Sequelize) {
    await queryInterface.createTable('watch_times', {
      seconds: {
        allowNull: false,
        type: Sequelize.DataTypes.INTEGER
      },
      user_id: {
        allowNull: false,
        type: Sequelize.DataTypes.INTEGER,
        references: { model: 'users', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      episode_id: {
        allowNull: false,
        type: Sequelize.DataTypes.INTEGER,
        references: { model: 'episodes', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      created_at: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updated_at: {
        allowNull: false,
        type: Sequelize.DATE
      }
    })
  },

  async down (queryInterface, Sequelize) {
    await queryInterface.dropTable('watch_times')
  }
};

• Com a migration terminada, execute-a:

npx sequelize-cli db:migrate

• Agora que já temos a tabela podemos criar um modelo;

// src/models/WatchTime.ts

import { DataTypes, Model } from "sequelize"
import { sequelize } from "../database"

export interface WatchTimeAttributes {
  seconds: number
  userId: number
  episodeId: number
}

export interface WatchTimeInstance extends Model<WatchTimeAttributes>, WatchTimeAttributes { }

export const WatchTime = sequelize.define<WatchTimeInstance, WatchTimeAttributes>('WatchTime', {
  seconds: {
    allowNull: false,
    type: DataTypes.INTEGER
  },
  userId: {
    allowNull: false,
    primaryKey: true,
    type: DataTypes.INTEGER,
    references: { model: 'users', key: 'id' },
    onUpdate: 'CASCADE',
    onDelete: 'CASCADE'
  },
  episodeId: {
    allowNull: false,
    primaryKey: true,
    type: DataTypes.INTEGER,
    references: { model: 'episodes', key: 'id' },
    onUpdate: 'CASCADE',
    onDelete: 'CASCADE'
  }
})

• E então incluir as associações desse modelo:

// src/models/index.ts

// ...
import { WatchTime } from './WatchTime'

// ...

Episode.belongsTo(Course)
Episode.belongsToMany(User, { through: WatchTime })

// ...

User.belongsToMany(Course, { through: Like })
User.belongsToMany(Episode, { through: WatchTime })
User.hasMany(Favorite, { as: 'favorites_courses', foreignKey: 'user_id' })

export {
  Category,
  Course,
  Episode,
  Favorite,
  User,
	WatchTime
}

• Agora vamos criar um serviço específico para o episódio e dentro dele dois métodos, um para criar um registro de progresso e outro para obter um registro:

// src/services/episodeService.ts

import { WatchTime } from "../models"

export const episodeService = {

	// ...

	  getWatchTime: async (userId: number, episodeId: number) => {
        const watchTime = await WatchTime.findOne({
            attributes: ['seconds'],
            where: {
                userId,
                episodeId
            }
        })

        return watchTime
    },

	  setWatchTime: async ({ userId, episodeId, seconds }: WatchTimeAttributes) => {
        const watchTimeAlreadyExists = await WatchTime.findOne({
            where: {
                userId,
                episodeId
            }
        })

        if (watchTimeAlreadyExists) {
            watchTimeAlreadyExists.seconds = seconds
            await watchTimeAlreadyExists.save()

            return watchTimeAlreadyExists
        } else {
            const watchTime = await WatchTime.create({
                userId,
                episodeId,
                seconds
            })
    
            return watchTime
        }
    }
}

• E depois criar os respectivos métodos no controlador para consumir esse serviço:

// src/controllers/episodes-controller.ts

import { Request, Response } from 'express'
import { RequestWithUser } from '../middlewares/auth'
import { episodeService } from '../services/episode-service'

export const episodesController = {

// ...

		// GET /episodes/:id/watchTime
    getWatchTime: async (req: AuthenticatedRequest, res: Response) => {
        const userId = req.user!.id
        const episodeId = req.params.id

        try {
            const watchTime = await episodeService.getWatchTime(userId, Number(episodeId))
            return res.json(watchTime)
        } catch (err) {
            if (err instanceof Error) {
                return res.status(400).json({ message: err.message })
            }
        }
    },

		// POST /episodes/:id/watchTime
    setWatchTime: async (req: AuthenticatedRequest, res: Response) => {
        const userId = req.user!.id
        const episodeId = Number(req.params.id)
        const { seconds } = req.body

        try {
            const watchTime = await episodeService.setWatchTime({
                episodeId,
                userId,
                seconds
            })
            return res.json(watchTime)
        } catch (err) {
            if (err instanceof Error) {
                return res.status(400).json({ message: err.message })
            }
        }
    }
}

• Agora só resta criar as rotas e testá-las:

// src/routes.ts

// ...

router.get('/episodes/stream', ensureAuthViaQuery, episodesController.stream)
router.get('/episodes/:id/watchTime', ensureAuth, episodesController.getWatchTime)
router.post('/episodes/:id/watchTime', ensureAuth, episodesController.setWatchTime)

// ...