#############################################################################m#
#  Skript to generate Austrian experience mortality table objects          ####
#############################################################################m#
library(MortalityTables)
library(here)
library(readxl)
library(tidyverse)




############################################################################### #
# Bestandssterbetafel der Versucherungsunternehmen Österreichs, 2012-16      ----
############################################################################### #

VUGBfile = here::here("data-raw", "AT", "Experience", "2012-2016_VU-Gesamtbestand", "Gesamtbestandstafel.xlsx")
VUGB.detailfile = here::here("data-raw", "AT", "Experience", "2012-2016_VU-Gesamtbestand", "Gesamtbestandstafel_detail.xlsx")
VUGB.stornofile = here::here("data-raw", "AT", "Experience", "2012-2016_VU-Gesamtbestand", "Stornovektor_detail.xlsx")


#----------------------------------------------------#
## Details zu Sterblichkeiten und Storni          ####
#----------------------------------------------------#


read_VUGB = function(file, sheet, ...) {
  # Prepare column names for the actual data columns
  headers = readxl::read_excel(file, sheet = sheet, skip = 3,col_names = FALSE, n_max = 5)
  hh = headers %>%
    t %>%
    `colnames<-`(c("Tarif", "Wahrscheinlichkeit", "Prämie", "Geschlecht", "Typ")) %>%
    as_tibble()  %>%
    mutate(
      Tarif = recode(Tarif, "Alle Tarife" = "Alle"),
      Wahrscheinlichkeit = recode(Wahrscheinlichkeit, "Sterblichkeiten" = "qx", "Exposure" = NA_character_, "Stornowahrscheinlichkeit" = "sx"),
      Prämie = recode(Prämie, "Alle Zahlarten" = "Alle", "laufende Prämienzahlung" = "laufend", "Einmalerlag" = "Einmalerlag", "Praemienfrei" = "prämienfrei", "Prämienfrei" = "prämienfrei"),
      Geschlecht = recode(Geschlecht, "UNISEX" = "u", "Männer" = "m", "Frauen" = "w"),
      Typ = recode(Typ,
                   "qx roh" = "raw",
                   "qy roh" = "raw",
                   "qu roh" = "raw",
                   "qx" = "smooth",
                   "qy" = "smooth",
                   "qu" = "smooth",
                   "sx" = "raw",
                   "sy" = "raw",
                   "su" = "raw")) %>%
    fill(everything()) %>%
    mutate(varname = paste(Tarif, Wahrscheinlichkeit, Prämie, Geschlecht, Typ, sep = "."))

  headers = hh$varname
  headers[[1]] = "Alter"

  # Read in the actual data
  readxl::read_excel(file, sheet = sheet, skip = 8, col_names = headers) %>%
    gather("key", "Value", -Alter) %>%
    separate(col = "key", into = c("Tarif", "Wahrscheinlichkeit", "Prämie", "Geschlecht", "Typ"), sep = "\\.", remove = TRUE) %>%
    select("tariff" = "Tarif", "sex" = "Geschlecht", premium = Prämie, probability = Wahrscheinlichkeit, age = Alter, Typ, Value)
}

VUBestandstafel.data = bind_rows(
  read_VUGB(file = VUGB.detailfile, sheet = "Gesamtbestand"),
  read_VUGB(file = VUGB.detailfile, sheet = "KLV"),
  read_VUGB(file = VUGB.detailfile, sheet = "FLV"),
  read_VUGB(file = VUGB.detailfile, sheet = "Sonstige"),

  read_VUGB(file = VUGB.stornofile, sheet = "Stornovektor"),
  read_VUGB(file = VUGB.stornofile, sheet = "KLV"),
  read_VUGB(file = VUGB.stornofile, sheet = "FLV"),
  read_VUGB(file = VUGB.stornofile, sheet = "Sonstige")
) %>%
  acast(tariff ~ sex ~ premium ~ probability ~ age ~ Typ, value.var = "Value")


dmn = dimnames(VUBestandstafel.data)[1:4]
# Re-Order the dimension to the "intuitive" order:
dmn[[1]] = dmn[[1]][c(1,3,2,4)]
dmn[[2]] = dmn[[2]][c(2,1,3)]
dmn[[3]] = dmn[[3]][c(1,3,2,4)]

VU.Gesamtbestand.Detail = array(
  data = c(mortalityTable.NA),
  dim = c(dim(VUBestandstafel.data)[1:4], 2),
  dimnames = c(`names<-`(dmn, c("Tarif", "Geschlecht", "Prämie", "Wahrscheinlichkeit")), list(Typ = c("raw", "smooth")))
)

q = "qx"
ages = 0:99
ages.ch = as.character(ages)
for (t in dmn[[1]]) {
  for (s in dmn[[2]]) {
    for (p in dmn[[3]]) {
      VU.Gesamtbestand.Detail[[t, s, p, q, "smooth"]] = mortalityTable.period(
        name = sprintf("VU Gesamtbestand AT, %s, %s, %s, %s, geglättet", t, s, p, q),
        ages = ages,
        deathProbs = VUBestandstafel.data[t, s, p, q, ages.ch, "smooth"],
        exposures = VUBestandstafel.data[t, s, p, q, ages.ch, "Exposure"],
        baseYear = 2014,
        data = list(
          raw = VUBestandstafel.data[t, s, p, q, ages.ch, "raw"],
          dim = list(
            sex = s,
            year = "2012-2016",
            data = "smooth",
            tarif = t,
            table = "VU Gesamtbestand AT",
            zahlart = p,
            probability = q
          ))
      )
      VU.Gesamtbestand.Detail[[t, s, p, q, "raw"]] = mortalityTable.period(
        name = sprintf("VU Gesamtbestand AT, %s, %s, %s, %s, roh", t, s, p, q),
        ages = ages,
        deathProbs = VUBestandstafel.data[t, s, p, q, ages.ch, "raw"],
        exposures = VUBestandstafel.data[t, s, p, q, ages.ch, "Exposure"],
        baseYear = 2014,
        data = list(dim = list(
          sex = s,
          year = "2012-2016",
          data = "raw",
          tarif = t,
          table = "VU Gesamtbestand AT",
          zahlart = p,
          probability = q
        ))
      )
    }
  }
}
q = "sx"
ages = 0:40
ages.ch = as.character(ages)
for (t in dmn[[1]]) {
  for (s in dmn[[2]]) {
    for (p in dmn[[3]]) {
      VU.Gesamtbestand.Detail[[t, s, p, q, "raw"]] = mortalityTable.period(
        name = sprintf("VU Gesamtbestand AT, %s, %s, %s, %s, roh", t, s, p, q),
        ages = ages,
        deathProbs = VUBestandstafel.data[t, s, p, q, ages.ch, "raw"],
        exposures = VUBestandstafel.data[t, s, p, q, ages.ch, "Exposure"],
        baseYear = 2014,
        data = list(dim = list(
          sex = s,
          year = "2012-2016",
          data = "raw",
          tarif = t,
          table = "VU Gesamtbestand AT",
          zahlart = p,
          probability = q
        ))
      )
    }
  }
}

save(VU.Gesamtbestand.Detail, file = here::here("data", "VU.Gesamtbestand.Detail.RData"))



#----------------------------------------------------#
## Gesamtbestandssterblichkeit                    ####
#----------------------------------------------------#
sex = c("m", "w", "u")

VU.Gesamtbestand = array(
  data = c(mortalityTable.NA),
  dim = c(3),
  dimnames = list(sex = sex)
)

ages = as.integer(dimnames(VUBestandstafel.data)[[5]])

for (s in sex) {
  VU.Gesamtbestand[[s]] = mortalityTable.period(
    name = paste0("VU-Gesamtbestand 2012-2016, ", recode(s, "m" = "Männer", "f" = "Frauen", "u" = "Unisex")),
    ages = ages,
    deathProbs = VUBestandstafel.data["Alle", s, "laufend", "qx",,"smooth"],
    exposures = VUBestandstafel.data["Alle", s, "laufend", "qx",,"Exposure"],
    baseYear = 2014,
    data = list(
      dim = list(
        sex = s,
        year = "2012-2016",
        data = "smooth",
        tarif = "Gesamtbestand",
        table = "VU Gesamtbestand AT",
        probability = "qx",
        population = "Lebensversicherte",
        country = "AT",
        period = "2012-2016"
      )
    )
  )
}
save(VU.Gesamtbestand, file = here::here("data", "VU.Gesamtbestand.RData"))



#----------------------------------------------------#
## Gesamtbestandsstornovektor                     ####
#----------------------------------------------------#
tarif = c("KLV", "FLV", "Sonstige")

VU.Gesamtbestand.Storno = array(
  data = c(mortalityTable.NA),
  dim = c(3),
  dimnames = list(tarif = tarif)
)

lz = 0:40
for (t in tarif) {
  tt = recode(t, "Sonstige" = "Sonstige Tarife")
  VU.Gesamtbestand.Storno[[t]] = mortalityTable.period(
    name = paste0("VU-Gesamtbestand 2012-2016, Storno ", t),
    ages = lz,
    deathProbs = VUBestandstafel.data[tt, "u", "laufend", "qx", as.character(lz),"raw"],
    exposures = VUBestandstafel.data[tt, "u", "laufend", "qx", as.character(lz),"Exposure"],
    baseYear = 2014,
    data = list(
      dim = list(
        sex = "u",
        year = "2014",
        data = "raw",
        tarif = t,
        table = "VU Gesamtbestand AT",
        probability = "sx",
        population = "Lebensversicherte",
        country = "AT",
        period = "2012-2016"
      )
    )
  )
}
save(VU.Gesamtbestand.Storno, file = here::here("data", "VU.Gesamtbestand.Storno.RData"))

