const express = require('express')
const fs = require('fs')
const router = express.Router()

const MONTHS = ['JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC']

router.get('/populate-data', async (req, res) => {
  // Read offence data from file
  const data = fs.readFileSync('src/data/lga_reported_offenders_number.csv', { encoding: 'utf8' }).split('\r\n')

  // Parse out relivant data
  const headers = data[0].split(',').map(h => h.replace(/"/g, ''))
  const offences = headers.slice(4)
  const rows = data.slice(1).map(row => row.split(','))
  const areas = Array.from(new Set(rows.map(row => row[0]))).filter(i => i.length)
  const ages = Array.from(new Set(rows.map(row => row[2]))).filter(i => !!i)
  const genders = Array.from(new Set(rows.map(row => row[3]))).filter(i => !!i)

  // Purge old data
  await req.db('offences').delete().catch(error => res.status(500).json(error))
  await req.db('areas').delete().catch(error => res.status(500).json(error))
  await req.db('ages').delete().catch(error => res.status(500).json(error))
  await req.db('genders').delete().catch(error => res.status(500).json(error))
  await req.db('statistics').delete().catch(error => res.status(500).json(error))

  // Populate initial data
  await req.db('offences').insert(offences.map(i => ({ offence: i }))).catch(error => res.status(500).json(error))
  await req.db('areas').insert(areas.map(i => ({ area: i }))).catch(error => res.status(500).json(error))
  await req.db('ages').insert(ages.map(i => ({ age: i }))).catch(error => res.status(500).json(error))
  await req.db('genders').insert(genders.map(i => ({ gender: i }))).catch(error => res.status(500).json(error))

  // Query initial data
  const dbOffences = await req.db('offences').catch(error => res.status(500).json(error))
  const dbAreas = await req.db('areas').catch(error => res.status(500).json(error))
  const dbAges = await req.db('ages').catch(error => res.status(500).json(error))
  const dbGenders = await req.db('genders').catch(error => res.status(500).json(error))

  // Build statistic data
  const statistics = rows.filter(i => i.length>1).map(row => {
    return {
      offence: null,
      area: row[0],
      age: row[2],
      gender: row[3],
      year: `20${row[1].substring(3, 5)}`,
      month: MONTHS.indexOf(row[1].substring(0, 3)) + 1,
      value: null,
    }
  })

  res.json({ success: true, data: statistics[0], dbAges })
})

module.exports = router