'use strict'

// Dependencies

const bodyParser = require('body-parser')
const compression = require('compression')
const express = require('express')
var fileUpload = require('express-fileupload')
var mysql = require('mysql')
var session = require('express-session')

// Configuration

process.env.NODE_ENV = 'production'

const app = express()
app.set('view engine', 'ejs')
app.use(bodyParser.urlencoded({ extended: false })) // parse application/x-www-form-urlencoded
app.use(compression())
app.use(express.static(__dirname + '/email'))
app.use(express.static(__dirname + '/public'))
app.use(fileUpload())
app.use(session({
  secret: 'NnnstJ4#qzRn9*saaZ5a*@#^3rxPdF',
  resave: false,
  saveUninitialized: true
}))

if (app.get('env') === 'production') {
  app.set('trust proxy', 1) // trust first proxy
  // session.cookie.secure = true // serve secure cookies
}

// Database connection

var connection = mysql.createConnection({
  host: 'localhost',
  user: 'kuntokes_energy',
  password: '2VPy9cp6bSNN78UU%hzr*wr5Mt!n%q',
  database: 'kuntokes_energy'
})

// Global functions

function checkIfBranchExists (res, branch, callback) {
  connection.query('SELECT nimi_ilman_skandeja FROM branches WHERE nimi_ilman_skandeja = "' + branch + '"', function (error, results, fields) {
    if (error) throw error
    if (typeof results[0] === 'undefined') {
      res.status(404).render('404.ejs')
    } else {
      callback()
    }
  })
}

// Routes

app.get('/', function (req, res) {
  var backgroundImage = ''
  var navigationData = []
  var notification = ''
  var partners = ''

  var inEnglish = false
  if (req.query.lang === 'en') {
    inEnglish = true
  }

  fetchInformationForNavigation()

  function fetchInformationForNavigation () {
    connection.query('SELECT nimi, nimi_ilman_skandeja FROM branches', function (error, results, fields) {
      if (error) throw error
      navigationData = results
      fetchNotification()
    })
  }

  function fetchNotification () {
    connection.query('SELECT internet_osoite FROM frontpage WHERE tyyppi = "frontpage-notification" OR tyyppi = "frontpage-notification-eng"', function (error, results, fields) {
      if (error) throw error
      if (inEnglish) {
        notification = results[1].internet_osoite
      } else {
        notification = results[0].internet_osoite
      }
      fetchPartners()
    })
  }

  function fetchPartners () {
    connection.query('SELECT internet_osoite, kuva FROM frontpage WHERE tyyppi = "frontpage-partner"', function (error, results, fields) {
      if (error) throw error
      partners = results
      fetchFrontPageImage()
    })
  }

  function fetchFrontPageImage () {
    connection.query('SELECT kuva FROM frontpage WHERE tyyppi = "frontpage-image"', function (error, results, fields) {
      if (error) throw error
      backgroundImage = results[0].kuva
      renderView()
    })
  }

  function renderView () {
    res.render('frontpage.ejs', {
      backgroundImage: backgroundImage,
      inEnglish: inEnglish,
      navigationData: navigationData,
      notification: notification,
      partners: partners
    })
  }
})

app.get('/fysioterapia/:branch', function (req, res) {
  // Check if branch exists before proceeding
  var branch = req.params.branch
  checkIfBranchExists(res, branch, fetchInformationForNavigation)

  // Variables
  var contactInfo = ''
  var galleryItems = ''
  var heading = ''
  var inEnglish = false
  var latitude = ''
  var location = ''
  var longitude = ''
  var navigationData = []
  var openingTimes = ''
  var physiotherapyServices = ''
  var staffMembers = ''
  var subHeading = ''
  var urlEncodedName = ''

  if (req.query.lang === 'en') {
    inEnglish = true
  }

  function fetchInformationForNavigation () {
    connection.query('SELECT nimi, nimi_ilman_skandeja FROM branches', function (error, results, fields) {
      if (error) throw error
      navigationData = results
      fetchBranchInformation()
    })
  }

  function fetchBranchInformation () {
    connection.query('SELECT nimi_ilman_skandeja, fysioterapia_otsikko, fysioterapia_otsikko_eng, fysioterapia_alaotsikko, fysioterapia_alaotsikko_eng, koordinaatti_leveys, koordinaatti_pituus, aukioloajat, aukioloajat_eng, yhteystiedot, yhteystiedot_eng, sijainti, sijainti_eng FROM branches WHERE nimi_ilman_skandeja = "' + branch + '"', function (error, results, fields) {
      if (error) throw error
      contactInfo = inEnglish ? results[0].yhteystiedot_eng : results[0].yhteystiedot
      heading = inEnglish ? results[0].fysioterapia_otsikko_eng : results[0].fysioterapia_otsikko
      latitude = results[0].koordinaatti_leveys
      location = inEnglish ? results[0].sijainti_eng : results[0].sijainti
      longitude = results[0].koordinaatti_pituus
      openingTimes = inEnglish ? results[0].aukioloajat_eng : results[0].aukioloajat
      subHeading = inEnglish ? results[0].fysioterapia_alaotsikko_eng : results[0].fysioterapia_alaotsikko
      urlEncodedName = results[0].nimi_ilman_skandeja
      fetchServiceDescriptions()
    })
  }

  function fetchServiceDescriptions () {
    connection.query('SELECT kuva, palvelun_nimi, palvelun_nimi_eng, palvelun_kuvaus, palvelun_kuvaus_eng, videon_osoite FROM physiotherapy_services WHERE ' + branch + ' = "kyllä" ORDER by jarjestys', function (error, results, fields) {
      if (error) throw error
      physiotherapyServices = results
      fetchStaffMembers()
    })
  }

  function fetchStaffMembers () {
    connection.query('SELECT kuvausteksti, kuva, nimi, titteli, titteli_eng, ajanvarauskalenterin_osoite FROM staff WHERE ' + urlEncodedName + ' = "kyllä" AND titteli LIKE "%Fysiotera%" ORDER BY rand()', function (error, results, fields) {
      if (error) throw error
      staffMembers = results
      renderView()
    })
  }

  function renderView () {
    res.render('physiotherapy.ejs', {
      branch: branch,
      contactInfo: contactInfo,
      galleryItems: galleryItems,
      heading: heading,
      heroButtonText: '',
      heroButtonURL: '',
      inEnglish: inEnglish,
      latitude: latitude,
      location: location,
      longitude: longitude,
      navigationData: navigationData,
      openingTimes: openingTimes,
      physiotherapyServices: physiotherapyServices,
      staffMembers: staffMembers,
      subHeading: subHeading
    })
  }
})

app.get('/maps', function (req, res) {
  res.render('partials/google-maps.ejs', {
    longitude: req.query.screencenter.longitude,
    latitude: req.query.screencenter.latitude
  })
})

app.get('/hieronta/:branch', function (req, res) {
  // Check if branch exists before proceeding
  var branch = req.params.branch
  var inEnglish = false
  if (req.query.lang === 'en') {
    inEnglish = true
  }
  checkIfBranchExists(res, branch, fetchInformationForNavigation)

  // Variables
  var contactInfo = ''
  var galleryItems = ''
  var heading = ''
  var latitude = ''
  var location = ''
  var longitude = ''
  var massageServices = ''
  var navigationData = []
  var openingTimes = ''
  var staffMembers = ''
  var subHeading = ''
  var urlEncodedName = ''

  function fetchInformationForNavigation () {
    connection.query('SELECT nimi, nimi_ilman_skandeja FROM branches', function (error, results, fields) {
      if (error) throw error
      navigationData = results
      fetchBranchInformation()
    })
  }

  function fetchBranchInformation () {
    connection.query('SELECT nimi_ilman_skandeja, hieronta_otsikko, hieronta_otsikko_eng, hieronta_alaotsikko, hieronta_alaotsikko_eng, koordinaatti_leveys, koordinaatti_pituus, aukioloajat, aukioloajat_eng, yhteystiedot, yhteystiedot_eng, sijainti, sijainti_eng FROM branches WHERE nimi_ilman_skandeja = "' + branch + '"', function (error, results, fields) {
      if (error) throw error
      contactInfo = inEnglish ? results[0].yhteystiedot_eng : results[0].yhteystiedot
      heading = inEnglish ? results[0].hieronta_otsikko_eng : results[0].hieronta_otsikko
      latitude = results[0].koordinaatti_leveys
      location = inEnglish ? results[0].sijainti_eng : results[0].sijainti
      longitude = results[0].koordinaatti_pituus
      openingTimes = inEnglish ? results[0].aukioloajat_eng : results[0].aukioloajat
      subHeading = inEnglish ? results[0].hieronta_alaotsikko_eng : results[0].hieronta_alaotsikko
      urlEncodedName = results[0].nimi_ilman_skandeja
      fetchServiceDescriptions()
    })
  }

  function fetchServiceDescriptions () {
    connection.query('SELECT kuva, palvelun_nimi, palvelun_nimi_eng, palvelun_kuvaus, palvelun_kuvaus_eng, videon_osoite FROM massage_services WHERE ' + branch + ' = "kyllä" ORDER by jarjestys', function (error, results, fields) {
      if (error) throw error
      massageServices = results
      fetchStaffMembers()
    })
  }

  function fetchStaffMembers () {
    connection.query('SELECT kuvausteksti, kuva, nimi, titteli, titteli_eng, ajanvarauskalenterin_osoite FROM staff WHERE ' + urlEncodedName + ' = "kyllä" AND titteli LIKE "%Hieroja%" ORDER BY rand()', function (error, results, fields) {
      if (error) throw error
      staffMembers = results
      renderView()
    })
  }

  function renderView () {
    res.render('massage.ejs', {
      branch: branch,
      contactInfo: contactInfo,
      galleryItems: galleryItems,
      heading: heading,
      heroButtonText: '',
      heroButtonURL: '',
      inEnglish: inEnglish,
      latitude: latitude,
      location: location,
      longitude: longitude,
      massageServices: massageServices,
      navigationData: navigationData,
      openingTimes: openingTimes,
      staffMembers: staffMembers,
      subHeading: subHeading
    })
  }
})

app.get('/hinnasto', function (req, res) {
  var contracts = ''
  var contractsInfo = ''
  var gymServices = ''
  var gymServicesInfo = ''
  var massageServices = ''
  var massageServicesInfo = ''
  var navigationData = []
  var physiotherapyServices = ''
  var physiotherapyServicesInfo = ''

  var inEnglish = false
  if (req.query.lang === 'en') {
    inEnglish = true
  }

  fetchInformationForNavigation()

  function fetchInformationForNavigation () {
    connection.query('SELECT nimi, nimi_ilman_skandeja FROM branches', function (error, results, fields) {
      if (error) throw error
      navigationData = results
      fetchContracts()
    })
  }

  function fetchContracts () {
    connection.query('SELECT product, product_eng, price1, price2, price1_eng, price2_eng, description, description_eng FROM prices WHERE section = "sopimukset" ORDER BY jarjestys', function (error, results, fields) {
      if (error) throw error
      contracts = results
      fetchContractInfo()
    })
  }

  function fetchContractInfo () {
    connection.query('SELECT description, description_eng FROM prices WHERE section = "sopimukset_lisatiedot"', function (error, results, fields) {
      if (error) throw error
      contractsInfo = results[0]
      fetchGymServices()
    })
  }

  function fetchGymServices () {
    connection.query('SELECT product, product_eng, price1, price2, price1_eng, price2_eng, description, description_eng, jarjestys FROM prices WHERE section = "kuntosalipalvelut" ORDER BY jarjestys', function (error, results, fields) {
      if (error) throw error
      gymServices = results
      fetchGymServicesInfo()
    })
  }

  function fetchGymServicesInfo () {
    connection.query('SELECT description, description_eng FROM prices WHERE section = "kuntosalipalvelut_lisatiedot"', function (error, results, fields) {
      if (error) throw error
      gymServicesInfo = results[0]
      fetchPhysiotherapyServices()
    })
  }

  function fetchPhysiotherapyServices () {
    connection.query('SELECT product, product_eng, price1, price2, price1_eng, price2_eng, description, description_eng FROM prices WHERE section = "fysioterapiapalvelut" ORDER BY jarjestys', function (error, results, fields) {
      if (error) throw error
      physiotherapyServices = results
      fetchPhysiotherapyServicesInfo()
    })
  }

  function fetchPhysiotherapyServicesInfo () {
    connection.query('SELECT description, description_eng FROM prices WHERE section = "fysioterapiapalvelut_lisatiedot"', function (error, results, fields) {
      if (error) throw error
      physiotherapyServicesInfo = results[0]
      fetchMassageServices()
    })
  }

  function fetchMassageServices () {
    connection.query('SELECT product, product_eng, price1, price2, price1_eng, price2_eng, description, description_eng FROM prices WHERE section = "hierontapalvelut" ORDER BY jarjestys', function (error, results, fields) {
      if (error) throw error
      massageServices = results
      fetchMassageServicesInfo()
    })
  }

  function fetchMassageServicesInfo () {
    connection.query('SELECT description, description_eng FROM prices WHERE section = "hierontapalvelut_lisatiedot"', function (error, results, fields) {
      if (error) throw error
      massageServicesInfo = results[0]
      renderView()
    })
  }

  function renderView () {
    res.render('hinnasto.ejs', {
      contracts: contracts,
      contractsInfo: contractsInfo,
      gymServices: gymServices,
      gymServicesInfo: gymServicesInfo,
      inEnglish: inEnglish,
      massageServices: massageServices,
      massageServicesInfo: massageServicesInfo,
      navigationData: navigationData,
      physiotherapyServices: physiotherapyServices,
      physiotherapyServicesInfo: physiotherapyServicesInfo
    })
  }
})

app.get('/kuntosali/:branch', function (req, res) {
  // Check if branch exists before proceeding
  var branch = req.params.branch
  var inEnglish = false
  if (req.query.lang === 'en') {
    inEnglish = true
  }
  checkIfBranchExists(res, branch, fetchInformationForNavigation)

  // Variables
  var branchName = ''
  var contactInfo = ''
  var galleryItems = ''
  var gymServices = ''
  var heroImage = ''
  var latitude = ''
  var location = ''
  var longitude = ''
  var navigationData = ''
  var openingTimes = ''
  var staffMembers = ''
  var subHeading = ''
  var urlEncodedName = ''
  var videoURL = ''

  function fetchInformationForNavigation () {
    connection.query('SELECT nimi, nimi_ilman_skandeja FROM branches', function (error, results, fields) {
      if (error) throw error
      navigationData = results
      fetchBranchInformation()
    })
  }

  function fetchBranchInformation () {
    connection.query('SELECT nimi, nimi_ilman_skandeja, nimi_ilman_skandeja, kuva, kuntosali_alaotsikko, kuntosali_alaotsikko_eng, videon_osoite, koordinaatti_leveys, koordinaatti_pituus, aukioloajat, aukioloajat_eng, yhteystiedot, yhteystiedot_eng, sijainti, sijainti_eng FROM branches WHERE nimi_ilman_skandeja = "' + branch + '"', function (error, results, fields) {
      if (error) throw error
      branchName = results[0].nimi
      contactInfo = inEnglish ? results[0].yhteystiedot_eng : results[0].yhteystiedot
      heroImage = results[0].kuva
      latitude = results[0].koordinaatti_leveys
      location = inEnglish ? results[0].sijainti_eng : results[0].sijainti
      longitude = results[0].koordinaatti_pituus
      openingTimes = inEnglish ? results[0].aukioloajat_eng : results[0].aukioloajat
      subHeading = inEnglish ? results[0].kuntosali_alaotsikko_eng : results[0].kuntosali_alaotsikko
      urlEncodedName = results[0].nimi_ilman_skandeja
      videoURL = results[0].videon_osoite
      fetchgalleryItems()
    })
  }

  function fetchgalleryItems () {
    connection.query('SELECT toimipiste, kuvateksti, kuvateksti_eng, kuva FROM gym_gallery WHERE toimipiste = "' + branch + '" ORDER by jarjestys', function (error, results, fields) {
      if (error) throw error
      galleryItems = results
      fetchServiceDescriptions()
    })
  }

  function fetchServiceDescriptions () {
    connection.query('SELECT kuva, palvelun_nimi, palvelun_nimi_eng, palvelun_kuvaus, palvelun_kuvaus_eng, videon_osoite FROM gym_services WHERE ' + branch + ' = "kyllä" ORDER by jarjestys', function (error, results, fields) {
      if (error) throw error
      gymServices = results
      fetchStaffMembers()
    })
  }

  function fetchStaffMembers () {
    connection.query('SELECT kuvausteksti, kuva, nimi, titteli, titteli_eng, ajanvarauskalenterin_osoite FROM staff WHERE ' + urlEncodedName + ' = "kyllä" AND titteli LIKE "%Personal%" ORDER BY rand()', function (error, results, fields) {
      if (error) throw error
      staffMembers = results
      renderView()
    })
  }

  function renderView () {
    res.render('gym.ejs', {
      branch: branch,
      contactInfo: contactInfo,
      galleryItems: galleryItems,
      gymServices: gymServices,
      heading: branchName,
      heroButtonText: '',
      heroButtonURL: '',
      heroImage: heroImage,
      inEnglish: inEnglish,
      latitude: latitude,
      location: location,
      longitude: longitude,
      navigationData: navigationData,
      openingTimes: openingTimes,
      staffMembers: staffMembers,
      subHeading: subHeading,
      videoURL: videoURL
    })
  }
})

app.get('/ryhmaliikunta/:branch', function (req, res) {
  // Check if branch exists before proceeding
  var branch = req.params.branch
  var inEnglish = false
  if (req.query.lang === 'en') {
    inEnglish = true
  }
  checkIfBranchExists(res, branch, fetchInformationForNavigation)

  // Variables
  var branchName = ''
  var calendarBackgrounds = []
  var classDescriptions = []
  var contactInfo = ''
  var groupExerciseCalendarItemsRaw = []
  var groupExerciseItems = []
  var heading = ''
  var latitude = ''
  var location = ''
  var longitude = ''
  var navigationData = []
  var openingTimes = ''
  var spinningCalendarItemsRaw = []
  var spinningItems = []
  var staffMembers = []
  var subHeading = ''
  var urlEncodedName = ''

  function fetchInformationForNavigation () {
    connection.query('SELECT nimi, nimi_ilman_skandeja FROM branches', function (error, results, fields) {
      if (error) throw error
      navigationData = results
      fetchBranchInformation()
    })
  }

  function fetchBranchInformation () {
    connection.query('SELECT nimi, nimi_ilman_skandeja, ryhmaliikunta_otsikko, ryhmaliikunta_otsikko_eng, ryhmaliikunta_alaotsikko, ryhmaliikunta_alaotsikko_eng, koordinaatti_leveys, koordinaatti_pituus, aukioloajat, aukioloajat_eng, yhteystiedot, yhteystiedot_eng, sijainti, sijainti_eng FROM branches WHERE nimi_ilman_skandeja = "' + branch + '"', function (error, results, fields) {
      if (error) throw error
      branchName = results[0].nimi
      contactInfo = inEnglish ? results[0].yhteystiedot_eng : results[0].yhteystiedot
      heading = inEnglish ? results[0].ryhmaliikunta_otsikko_eng : results[0].ryhmaliikunta_otsikko
      latitude = results[0].koordinaatti_leveys
      location = inEnglish ? results[0].sijainti_eng : results[0].sijainti
      longitude = results[0].koordinaatti_pituus
      openingTimes = inEnglish ? results[0].aukioloajat_eng : results[0].aukioloajat
      subHeading = inEnglish ? results[0].ryhmaliikunta_alaotsikko_eng : results[0].ryhmaliikunta_alaotsikko
      urlEncodedName = results[0].nimi_ilman_skandeja
      fetchGroupExerciseCalendarBackgrounds()
    })
  }

  function fetchGroupExerciseCalendarBackgrounds () {
    connection.query('SELECT kuva, tyyppi FROM group_exercise_calendar_backgrounds WHERE toimipiste = "' + branch + '"', function (error, results, fields) {
      if (error) throw error
      for (var i = 0; i < results.length; i++) {
        if (typeof results[i] !== 'undefined' && results[i].tyyppi === 'ryhmaliikunta') {
          calendarBackgrounds.ryhmaliikunta = results[i].kuva
        }
        if (typeof results[i] !== 'undefined' && results[i].tyyppi === 'spinning') {
          calendarBackgrounds.spinning = results[i].kuva
        }
      }
      fetchGroupExerciseCalendarItems()
    })
  }

  function fetchGroupExerciseCalendarItems () {
    connection.query('SELECT paiva, tunnin_aika_nimi_ja_ohjaaja FROM group_exercise_calendar WHERE toimipiste = "' + branch + '" AND tyyppi = "ryhmaliikunta"', function (error, results, fields) {
      if (error) throw error
      groupExerciseCalendarItemsRaw = results
      fetchSpinningCalendarItems()
    })
  }

  function fetchSpinningCalendarItems () {
    connection.query('SELECT paiva, tunnin_aika_nimi_ja_ohjaaja FROM group_exercise_calendar WHERE toimipiste = "' + branch + '" AND tyyppi = "spinning"', function (error, results, fields) {
      if (error) throw error
      spinningCalendarItemsRaw = results
      fetchClassDescriptions()
    })
  }

  function fetchClassDescriptions () {
    connection.query('SELECT kuva, tunnin_nimi, tunnin_kuvaus, tunnin_kuvaus_eng, videon_osoite, centrum, rantakyla, ylamylly FROM group_exercise_classes WHERE ' + branch + ' = "kyllä" ORDER BY tunnin_nimi', function (error, results, fields) {
      if (error) throw error
      classDescriptions = results
      fetchStaffMembers()
    })
  }

  function fetchStaffMembers () {
    connection.query('SELECT kuvausteksti, kuva, nimi, titteli, titteli_eng, ajanvarauskalenterin_osoite FROM staff WHERE ' + urlEncodedName + ' = "kyllä" AND titteli LIKE "%Ryhmäliikun%" ORDER BY rand()', function (error, results, fields) {
      if (error) throw error
      staffMembers = results
      organizeCalendarItems(groupExerciseCalendarItemsRaw, groupExerciseItems)
      organizeCalendarItems(spinningCalendarItemsRaw, spinningItems)
      renderView()
    })
  }

  function organizeCalendarItems (calendarItemsRaw, calendarItems) {
    var daysOfTheWeek = ['maanantai', 'tiistai', 'keskiviikko', 'torstai', 'perjantai', 'lauantai', 'sunnuntai']
    var daysOfTheWeekLANG = []
    if (inEnglish) {
      daysOfTheWeekLANG = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
    } else {
      daysOfTheWeekLANG = ['Maanantai', 'Tiistai', 'Keskiviikko', 'Torstai', 'Perjantai', 'Lauantai', 'Sunnuntai']
    }

    // Create arrays to hold items for each day and give each day a heading
    for (var i = 0; i < daysOfTheWeekLANG.length; i++) {
      calendarItems.push(new Array(daysOfTheWeekLANG[i]))
    }

    // Populate the arrays with classes for each day
    for (i = 0; i < calendarItemsRaw.length; i++) {
      for (var ii = 0; ii < daysOfTheWeek.length; ii++) {
        if (calendarItemsRaw[i].paiva === daysOfTheWeek[ii]) {
          calendarItems[ii].push(calendarItemsRaw[i].tunnin_aika_nimi_ja_ohjaaja)
        }
      }
    }

    // Check which days do not have classes
    var daysToBeRemoved = []
    for (i = 0; i < calendarItems.length; i++) {
      if (calendarItems[i].length === 1) {
        daysToBeRemoved.push(i)
      }
    }

    // Remove days from the array that do not have any classes
    for (i = daysToBeRemoved.length - 1; i >= 0; i--) {
      calendarItems.splice(daysToBeRemoved[i], 1)
    }
  }

  function renderView () {
    res.render('group-exercise.ejs', {
      branch: branch,
      branchName: branchName,
      calendarBackgrounds: calendarBackgrounds,
      classDescriptions: classDescriptions,
      contactInfo: contactInfo,
      groupExerciseItems: groupExerciseItems,
      heading: heading,
      inEnglish: inEnglish,
      latitude: latitude,
      location: location,
      longitude: longitude,
      navigationData: navigationData,
      openingTimes: openingTimes,
      spinningItems: spinningItems,
      staffMembers: staffMembers,
      subHeading: subHeading
    })
  }
})

app.get('/tulosta/:typeOfGroupExercise/:branch', function (req, res) {
  // Check if branch exists before proceeding
  var bottomBarText = ''
  var branch = req.params.branch
  var branchName = ''
  var calendarBackgrounds = []
  var groupExerciseCalendarItemsRaw = []
  var groupExerciseItems = []
  var spinningCalendarItemsRaw = []
  var spinningItems = []
  var typeOfGroupExercise = req.params.typeOfGroupExercise

  checkIfBranchExists(res, branch, fetchBranchInformation)

  function fetchBranchInformation () {
    connection.query('SELECT nimi FROM branches WHERE nimi_ilman_skandeja = "' + branch + '"', function (error, results, fields) {
      if (error) throw error
      branchName = results[0].nimi
      fetchGroupExerciseCalendarBackgrounds()
    })
  }

  function fetchGroupExerciseCalendarBackgrounds () {
    connection.query('SELECT kuva, tyyppi FROM group_exercise_calendar_backgrounds WHERE toimipiste = "' + branch + '"', function (error, results, fields) {
      if (error) throw error
      for (var i = 0; i < results.length; i++) {
        if (typeof results[i] !== 'undefined' && results[i].tyyppi === 'ryhmaliikunta') {
          calendarBackgrounds.ryhmaliikunta = results[i].kuva
        }
        if (typeof results[i] !== 'undefined' && results[i].tyyppi === 'spinning') {
          calendarBackgrounds.spinning = results[i].kuva
        }
      }
      checkTypeOfGroupExercise()
    })
  }

  function checkTypeOfGroupExercise () {
    if (typeOfGroupExercise === 'ryhmaliikunta') {
      fetchGroupExerciseCalendarItems()
    } else if (typeOfGroupExercise === 'spinning') {
      fetchSpinningCalendarItems()
    }
  }

  function fetchGroupExerciseCalendarItems () {
    connection.query('SELECT paiva, tunnin_aika_nimi_ja_ohjaaja FROM group_exercise_calendar WHERE toimipiste = "' + branch + '" AND tyyppi = "ryhmaliikunta"', function (error, results, fields) {
      if (error) throw error
      groupExerciseCalendarItemsRaw = results
      organizeCalendarItems(groupExerciseCalendarItemsRaw, groupExerciseItems)
    })
  }

  function fetchSpinningCalendarItems () {
    connection.query('SELECT paiva, tunnin_aika_nimi_ja_ohjaaja FROM group_exercise_calendar WHERE toimipiste = "' + branch + '" AND tyyppi = "spinning"', function (error, results, fields) {
      if (error) throw error
      spinningCalendarItemsRaw = results
      organizeCalendarItems(spinningCalendarItemsRaw, spinningItems)
    })
  }

  function organizeCalendarItems (calendarItemsRaw, calendarItems) {
    var daysOfTheWeek = ['maanantai', 'tiistai', 'keskiviikko', 'torstai', 'perjantai', 'lauantai', 'sunnuntai']
    var daysOfTheWeekLANG = ['Maanantai', 'Tiistai', 'Keskiviikko', 'Torstai', 'Perjantai', 'Lauantai', 'Sunnuntai']

    // Create arrays to hold items for each day and give each day a heading
    for (var i = 0; i < daysOfTheWeekLANG.length; i++) {
      calendarItems.push(new Array(daysOfTheWeekLANG[i]))
    }

    // Populate the arrays with classes for each day
    for (i = 0; i < calendarItemsRaw.length; i++) {
      for (var ii = 0; ii < daysOfTheWeek.length; ii++) {
        if (calendarItemsRaw[i].paiva === daysOfTheWeek[ii]) {
          calendarItems[ii].push(calendarItemsRaw[i].tunnin_aika_nimi_ja_ohjaaja)
        }
      }
    }

    // Check which days do not have classes
    var daysToBeRemoved = []
    for (i = 0; i < calendarItems.length; i++) {
      if (calendarItems[i].length === 1) {
        daysToBeRemoved.push(i)
      }
    }

    // Remove days from the array that do not have any classes
    for (i = daysToBeRemoved.length - 1; i >= 0; i--) {
      calendarItems.splice(daysToBeRemoved[i], 1)
    }
    fetchSpinningCalendarBottomBarText()
  }

  function fetchSpinningCalendarBottomBarText () {
    connection.query('SELECT alapalkin_teksti FROM group_exercise_calendar_print WHERE toimipiste = "' + branch + '" AND tyyppi = "' + typeOfGroupExercise + '"', function (error, results, fields) {
      if (error) throw error
      bottomBarText = results
      renderView()
    })
  }

  function renderView () {
    res.render('group-exercise-print.ejs', {
      bottomBarText: bottomBarText,
      branchName: branchName,
      calendarBackgrounds: calendarBackgrounds,
      groupExerciseItems: groupExerciseItems,
      spinningItems: spinningItems
    })
  }
})

/*****************************/
/* Content management routes */
/*****************************/

app.get('/kirjaudu', function (req, res) {
  res.render('login.ejs', {
    username: ''
  })
})

app.post('/kirjaudu', function (req, res) {
  var username = req.body.username || ''
  var password = req.body.password || ''

  if ((username === 'Ryhmäliikunta' && password === 'g2VcKTNfxA3z') || (username === 'Ylläpitäjä' && password === 'E9KB3mNsDX72')) {
    req.session.loggedIn = true
    req.session.username = username
    res.redirect('yllapito')
  } else {
    res.render('login.ejs', {
      username: username
    })
  }
})

app.get('/kirjaudu-ulos', function (req, res) {
  req.session.loggedIn = false
  req.session.username = ''
  res.redirect('/kirjaudu')
})

app.get('/yllapito', function (req, res) {
  var data = []
  var namesOfAllBranches = []
  var tableNames = []
  var username = req.session.username
  if (req.session.loggedIn === true) {
    fetchNamesOfEachBranch()
  } else {
    res.redirect('kirjaudu')
  }

  function fetchNamesOfEachBranch () {
    connection.query('SELECT nimi_ilman_skandeja FROM branches', function (error, results, fields) {
      if (error) throw error
      namesOfAllBranches = results
      fetchTableNames()
    })
  }

  function fetchTableNames () {
    connection.query('SELECT table_name FROM information_schema.tables WHERE table_schema = "kuntokes_energy"', function (error, results, fields) {
      if (error) throw error
      tableNames = results
      fetchDataFromTable(tableNames[0].table_name, 0)
    })
  }

  // Iterate using a recursive function because a for loop does not work with the asynchronous 'connection.query' function
  function fetchDataFromTable (tableName, i) {
    if (i < tableNames.length - 1) {
      connection.query('SELECT * FROM ' + tableName, function (error, results, fields) {
        if (error) throw error
        data.push(results)
        i++
        fetchDataFromTable(tableNames[i].table_name, i)
      })
    } else {
      connection.query('SELECT * FROM ' + tableName, function (error, results, fields) {
        if (error) throw error
        data.push(results)
        renderView()
      })
    }
  }

  function renderView () {
    res.render('manage-content.ejs', {
      data: data,
      namesOfAllBranches: namesOfAllBranches,
      username: username
    })
  }
})

app.post('/update/:table', function (req, res) {
  var queryParameters = req.query
  var table = mysql.escape(req.params.table).slice(1, -1)
  var SQLQuery = 'UPDATE ' + table + ' SET '

  if (req.session.loggedIn === true) {
    checkIfTableExists()
  } else {
    res.redirect('kirjaudu')
  }

  function checkIfTableExists () {
    connection.query('SELECT id FROM ' + table + ' LIMIT 1', function (error, results, fields) {
      if (!error) {
        updateRecord()
      } else {
        console.log('Cannot update the record because table ' + table + ' was not found.')
      }
    })
  }

  function updateRecord () {
    // Get the name of the last property in the queryParameters object
    var lastProperty
    for (lastProperty in queryParameters);
    // Form the SQL query string
    for (var key in queryParameters) {
      if (key === 'id') {
        // Do nothing if id
      } else if (key === lastProperty) {
        // Omit the last comma for the last property
        SQLQuery += key + '=' + mysql.escape(queryParameters[key]) + ' '
      } else {
        SQLQuery += key + '=' + mysql.escape(queryParameters[key]) + ', '
      }
    }
    SQLQuery += 'WHERE id=' + mysql.escape(queryParameters.id)
    performDatabaseQuery()
  }
  function performDatabaseQuery () {
    connection.query(SQLQuery, function (error, results, fields) {
      if (error) throw error
      res.redirect('/yllapito')
    })
  }
})

app.post('/insert/:table', function (req, res) {
  var queryParameters = req.query
  var table = mysql.escape(req.params.table).slice(1, -1)
  var SQLQuery = 'INSERT INTO ' + table + ' ('

  if (req.session.loggedIn === true) {
    checkIfTableExists()
  } else {
    res.redirect('kirjaudu')
  }

  function checkIfTableExists () {
    connection.query('SELECT id FROM ' + table + ' LIMIT 1', function (error, results, fields) {
      if (!error) {
        insertRecord()
      } else {
        console.log('Cannot update the record because table was not found.')
      }
    })
  }

  function insertRecord () {
    // Get the name of the last property in the queryParameters object
    var lastProperty
    for (lastProperty in queryParameters);
    // Form the SQL query string
    for (var key in queryParameters) {
      if (key === 'id') {
        // Do nothing if id
      } else if (key === lastProperty) {
        // Add closing parenthesis for the last property
        SQLQuery += '' + key + ') VALUES ('
      } else {
        SQLQuery += '' + key + ', '
      }
    }
    for (key in queryParameters) {
      if (key === 'id') {
        // Do nothing if id
      } else if (key === lastProperty) {
        // Add closing parenthesis for the last property
        SQLQuery += mysql.escape(queryParameters[key]) + ')'
      } else {
        SQLQuery += mysql.escape(queryParameters[key]) + ', '
      }
    }
    performDatabaseQuery()
  }
  function performDatabaseQuery () {
    connection.query(SQLQuery, function (error, results, fields) {
      if (error) throw error
      res.redirect('/yllapito')
    })
  }
})

app.post('/insert-branch/:table', function (req, res) {
  var queryParameters = req.query
  var table = mysql.escape(req.params.table).slice(1, -1)
  var SQLQuery = 'INSERT INTO ' + table + ' ('
  if (req.session.loggedIn === true) {
    checkIfTableExists()
  } else {
    res.redirect('kirjaudu')
  }

  function checkIfTableExists () {
    connection.query('SELECT id FROM ' + table + ' LIMIT 1', function (error, results, fields) {
      if (!error) {
        insertRecord()
      } else {
        console.log('Cannot update the record because table was not found.')
      }
    })
  }

  function insertRecord () {
    // Get the name of the last property in the queryParameters object
    var lastProperty
    for (lastProperty in queryParameters);
    // Form the SQL query string
    for (var key in queryParameters) {
      if (key === 'id') {
        // Do nothing if id
      } else if (key === lastProperty) {
        // Add closing parenthesis for the last property
        SQLQuery += '' + key + ') VALUES ('
      } else {
        SQLQuery += '' + key + ', '
      }
    }
    for (key in queryParameters) {
      if (key === 'id') {
        // Do nothing if id
      } else if (key === lastProperty) {
        // Add closing parenthesis for the last property
        SQLQuery += mysql.escape(queryParameters[key]) + ')'
      } else {
        SQLQuery += mysql.escape(queryParameters[key]) + ', '
      }
    }
    insertColumnsToGroupExerciseClassesTable()
  }

  function insertColumnsToGroupExerciseClassesTable () {
    connection.query('ALTER TABLE group_exercise_classes ADD COLUMN ' + queryParameters.nimi_ilman_skandeja + ' VARCHAR(45) NULL', function (error, results, fields) {
      if (error) throw error
      insertColumnsToGymServicesTable()
    })
  }

  function insertColumnsToGymServicesTable () {
    connection.query('ALTER TABLE gym_services ADD COLUMN ' + queryParameters.nimi_ilman_skandeja + ' VARCHAR(45) NULL', function (error, results, fields) {
      if (error) throw error
      insertColumnsToMassageServicesTable()
    })
  }

  function insertColumnsToMassageServicesTable () {
    connection.query('ALTER TABLE massage_services ADD COLUMN ' + queryParameters.nimi_ilman_skandeja + ' VARCHAR(45) NULL', function (error, results, fields) {
      if (error) throw error
      insertColumnsToPhysiotherapyServicesTable()
    })
  }

  function insertColumnsToPhysiotherapyServicesTable () {
    connection.query('ALTER TABLE physiotherapy_services ADD COLUMN ' + queryParameters.nimi_ilman_skandeja + ' VARCHAR(45) NULL', function (error, results, fields) {
      if (error) throw error
      insertColumnsToStaffTable()
    })
  }

  function insertColumnsToStaffTable () {
    connection.query('ALTER TABLE staff ADD COLUMN ' + queryParameters.nimi_ilman_skandeja + ' VARCHAR(45) NULL', function (error, results, fields) {
      if (error) throw error
      performDatabaseQuery()
    })
  }

  function performDatabaseQuery () {
    connection.query(SQLQuery, function (error, results, fields) {
      if (error) throw error
      res.redirect('/yllapito')
    })
  }
})

app.post('/delete/:table', function (req, res) {
  var queryParameters = req.query
  var table = mysql.escape(req.params.table).slice(1, -1)
  var SQLQuery = 'DELETE FROM ' + table + ' WHERE id=' + mysql.escape(queryParameters.id)

  if (req.session.loggedIn === true) {
    checkIfTableExists()
  } else {
    res.redirect('kirjaudu')
  }

  function checkIfTableExists () {
    connection.query('SELECT id FROM ' + table + ' LIMIT 1', function (error, results, fields) {
      if (!error) {
        performDatabaseQuery()
      } else {
        console.log('Cannot update the record because table was not found.')
      }
    })
  }

  function performDatabaseQuery () {
    connection.query(SQLQuery, function (error, results, fields) {
      if (error) throw error
      res.redirect('/yllapito')
    })
  }
})

app.post('/delete-branch/:table', function (req, res) {
  var queryParameters = req.query
  var table = mysql.escape(req.params.table).slice(1, -1)
  var SQLQuery = 'DELETE FROM ' + table + ' WHERE id=' + mysql.escape(queryParameters.id)

  if (req.session.loggedIn === true) {
    checkIfTableExists()
  } else {
    res.redirect('kirjaudu')
  }

  function checkIfTableExists () {
    connection.query('SELECT id FROM ' + table + ' LIMIT 1', function (error, results, fields) {
      if (!error) {
        performDatabaseQuery()
      } else {
        console.log('Cannot update the record because table was not found.')
      }
    })
  }

  function performDatabaseQuery () {
    connection.query(SQLQuery, function (error, results, fields) {
      if (error) throw error
      deleteGroupExerciseClassesTableColumn()
    })
  }

  function deleteGroupExerciseClassesTableColumn () {
    connection.query('ALTER TABLE group_exercise_classes DROP COLUMN ' + queryParameters.branch, function (error, results, fields) {
      if (error) throw error
      deleteGymServicesTableColumn()
    })
  }

  function deleteGymServicesTableColumn () {
    connection.query('ALTER TABLE gym_services DROP COLUMN ' + queryParameters.branch, function (error, results, fields) {
      if (error) throw error
      deleteMassageServicesTableColumn()
    })
  }

  function deleteMassageServicesTableColumn () {
    connection.query('ALTER TABLE massage_services DROP COLUMN ' + queryParameters.branch, function (error, results, fields) {
      if (error) throw error
      deletePhysiotherapyServicesTableColumn()
    })
  }

  function deletePhysiotherapyServicesTableColumn () {
    connection.query('ALTER TABLE physiotherapy_services DROP COLUMN ' + queryParameters.branch, function (error, results, fields) {
      if (error) throw error
      deleteStaffTableColumn()
    })
  }

  function deleteStaffTableColumn () {
    connection.query('ALTER TABLE staff DROP COLUMN ' + queryParameters.branch, function (error, results, fields) {
      if (error) throw error
      res.redirect('/yllapito')
    })
  }
})

app.post('/upload', function (req, res) {
  if (req.session.loggedIn === true) {
    if (!req.files) return res.status(400).send('No files were uploaded.')
    var uploadedFile = req.files.image
    var uploadPath = req.query.uploadpath
    // Move the file to the correct directory
    uploadedFile.mv(__dirname + '/public/img/' + uploadPath + uploadedFile.name, function (err) {
      if (err) {
        return res.status(500).send(err)
      }
      res.send('File uploaded!')
    })
  } else {
    res.redirect('kirjaudu')
  }
})

// Error pages

app.get('*', function (req, res) {
  res.status(404).render('404.ejs')
})

// Server
app.listen(8000, function () {
  console.log('Server started on http://localhost:8000/')
})