const config = require('../../../knexfile').extweb
const knex = require('knex')(config)
const documentTypeEnum = require('../../constants/document-type-enum')

module.exports = function (claimId) {
  return knex('Claim')
    .join('Eligibility', 'Claim.Reference', '=', 'Eligibility.Reference')
    .join('Visitor', 'Eligibility.Reference', '=', 'Visitor.Reference')
    .join('Prisoner', 'Eligibility.Reference', '=', 'Prisoner.Reference')
    .where('Claim.ClaimId', claimId)
    .first(
      'Eligibility.Reference',
      'Claim.DateSubmitted',
      'Claim.DateOfJourney',
      'Visitor.FirstName',
      'Visitor.LastName',
      'Visitor.Benefit',
      'Prisoner.FirstName AS PrisonerFirstName',
      'Prisoner.LastName AS PrisonerLastName',
      'Prisoner.DateOfBirth AS PrisonerDateOfBirth',
      'Prisoner.PrisonNumber',
      'Prisoner.NameOfPrison'
    )
    .then(function (claim) {
      return knex('ClaimDocument')
        .join('Claim', 'ClaimDocument.ClaimId', '=', 'Claim.ClaimId')
        .where({ 'ClaimDocument.DocumentType': documentTypeEnum.VISIT_CONFIRMATION, 'Claim.ClaimId': claimId })
        .first('ClaimDocument.DocumentStatus', 'ClaimDocument.DocumentType')
        .then(function (visitConfirmationDocumentStatus) {
          return knex('Claim')
            .join('ClaimExpense', 'Claim.ClaimId', '=', 'ClaimExpense.ClaimId')
            .where({ 'Claim.ClaimId': claimId, 'ClaimExpense.IsEnabled': true })
            .select(
              'ClaimExpense.ClaimExpenseId',
              'ClaimExpense.ExpenseType',
              'ClaimExpense.Cost',
              'ClaimExpense.To',
              'ClaimExpense.From',
              'ClaimExpense.IsReturn',
              'ClaimExpense.TravelTime',
              'ClaimExpense.DurationOfTravel',
              'ClaimExpense.TicketType',
              'ClaimExpense.IsChild'
            )
            .orderBy('ClaimExpense.ClaimExpenseId')
            .then(function (claimExpenses) {
              claimExpenses.forEach(function (expense) {
                expense.Cost = Number(expense.Cost).toFixed(2)
              })
              claim.visitConfirmation = visitConfirmationDocumentStatus
              return claimExpenses
            })
        })
        .then(function (claimExpenses) {
          return knex('Claim')
            .join('ClaimChild', 'Claim.ClaimId', '=', 'ClaimChild.ClaimId')
            .where({ 'Claim.ClaimId': claimId, 'ClaimChild.IsEnabled': true })
            .select(
              'ClaimChild.ClaimChildId',
              'ClaimChild.Name',
              'ClaimChild.DateOfBirth',
              'ClaimChild.Relationship'
            )
            .orderBy('ClaimChild.Name')
            .then(function (claimChild) {
              return {
                claim: claim,
                claimExpenses: claimExpenses,
                claimChild: claimChild
              }
            })
        })
    })
}

@@ -1464,416 +1464,8 @@
 ect(
-%0A              'ClaimExpense.ClaimExpenseId',%0A              'ClaimExpense.ExpenseType',%0A              'ClaimExpense.Cost',%0A              'ClaimExpense.To',%0A              'ClaimExpense.From',%0A              'ClaimExpense.IsReturn',%0A              'ClaimExpense.TravelTime',%0A              'ClaimExpense.DurationOfTravel',%0A              'ClaimExpense.TicketType',%0A              'ClaimExpense.IsChild'%0A            
 )%0A  
@@ -2080,175 +2080,8 @@
 ect(
-%0A              'ClaimChild.ClaimChildId',%0A              'ClaimChild.Name',%0A              'ClaimChild.DateOfBirth',%0A              'ClaimChild.Relationship'%0A            
 )%0A  

Simplified query. (#160)