Couse.js

'use strict'

const Database = use('Database')
const Model = use('Model')
const Env = use('Env')

class Course extends Model {
  static get computed () {
    return ['thumbnailUrl']
  }

  static scopeOnlyAvailable (query, user_id, type) {
    return query.whereHas('products.students', builder => {
      builder
        .whereRaw('batches.starts_at <= current_timestamp')
        .whereRaw('((current_date between purchases.start_date and purchases.end_date) or purchases.end_date is null)')
        .where('users.id', user_id)
        .where('products.type', type)
    })
  }

  static scopeFromUser (query, user_id, type) {
    return query
      .innerJoin('batches', 'batches.course_id', 'courses.id')
      .innerJoin('products', 'products.id', 'batches.product_id')
      .joinRaw(
        'join purchases on purchases.product_id = products.id and purchases.user_id = ?',
        [user_id]
      )
      .select(
        'courses.*',
        'batches.starts_at',
        Database.raw('batches.starts_at <= current_timestamp as available')
      )
      .where('products.type', type)
      .whereRaw('((current_date between purchases.start_date and purchases.end_date) or purchases.end_date is null)')
  }

  static scopeWithLessons (query, user_id) {
    return query.with('modules', builder => {
      builder
        .whereExists(function () {
          this.select(1)
            .from('batches')
            .innerJoin('products', 'products.id', 'batches.product_id')
            .joinRaw(
              'join purchases on purchases.product_id = products.id and purchases.user_id = ?',
              [user_id]
            )
            .joinRaw(
              'left join modules previous_modules on previous_modules.course_id = modules.course_id and previous_modules."order" = modules."order" - 1'
            )
            // .joinRaw(
            //   'left join challenge_deliveries on challenge_deliveries.module_id = previous_modules.id and challenge_deliveries.user_id = ?',
            //   [user_id]
            // )
            .whereRaw('batches.course_id = modules.course_id')
            .whereRaw(
              `((batches.release_all = true and batches.starts_at <= current_timestamp) or (batches.starts_at + (modules."order" * batches.release_frequency * interval '1 day') <= current_timestamp))`
            )
        })
        .orderBy('order')
        .with('lessons', builder => {
          builder.orderBy('order').with('progress', builder => {
            builder.where('progress.user_id', user_id)
          })
        })
    })
  }

  products () {
    return this.belongsToMany('App/Models/Product')
      .withPivot(['release_frequency', 'release_all', 'starts_at'])
      .pivotModel('App/Models/Batch')
  }

  modules () {
    return this.hasMany('App/Models/Module')
  }

  getThumbnailUrl ({ thumbnail }) {
    const image = thumbnail || 'no-image.svg'
    return `${Env.get('APP_URL')}/files/${image}`
  }
}

module.exports = Course