## remove (almost) everything in the working environment
rm(list = ls())

# packages
library(readxl)
library(dplyr)

# Set working directory based on this.path
if (!require(this.path)) {
  install.packages("this.path")
  require(this.path)
}
setwd(dirname(this.path()))

# Import data
profsgood <- as.data.frame(read_excel("siblings-extended-good.xlsx"))

############# CODE FOR WARS and OTHER EVENTS ###############

# Create place of birth frequency table
profsgood <- profsgood %>%
  mutate(BirthPlace_trimmed = ifelse(!is.na(BirthPlace),
                                     substr(BirthPlace, 1, nchar(BirthPlace) - 3),
                                     NA))

Birthplace_frequency <- profsgood %>%
  filter(!is.na(BirthPlace_trimmed), BirthPlace_trimmed != "") %>%
  count(BirthPlace_trimmed, sort = TRUE) %>%
  head(20)

print(Birthplace_frequency)

# Create event dummies
eventsgood <- profsgood[, c("PKey", "YearBorn", "BirthPlace_trimmed")]

### Stockholm ####
eventsgood$stockholm_bloodbath <- ifelse(eventsgood$YearBorn >= 1510 & eventsgood$YearBorn <= 1530 & eventsgood$BirthPlace_trimmed == "Stockholm", 1, 0)
eventsgood$stockholm_conquest <- ifelse(eventsgood$YearBorn >= 1513 & eventsgood$YearBorn <= 1533 & eventsgood$BirthPlace_trimmed == "Stockholm", 1, 0)
eventsgood$stockholm_firstgreatfire <- ifelse(eventsgood$YearBorn >= 1615 & eventsgood$YearBorn <= 1635 & eventsgood$BirthPlace_trimmed == "Stockholm", 1, 0)
eventsgood$stockholm_plague <- ifelse(eventsgood$YearBorn >= 1700 & eventsgood$YearBorn <= 1731 & eventsgood$BirthPlace_trimmed == "Stockholm", 1, 0)
eventsgood$stockholm_secondgreatfire <- ifelse(eventsgood$YearBorn >= 1749 & eventsgood$YearBorn <= 1769 & eventsgood$BirthPlace_trimmed == "Stockholm", 1, 0)

### Copenhagen ####
eventsgood$Kobenhavn_siegefeud <- ifelse(eventsgood$YearBorn >= 1524 & eventsgood$YearBorn <= 1546 & eventsgood$BirthPlace_trimmed == "Kobenhavn", 1, 0)
eventsgood$Kobenhavn_siegesecond <- ifelse(eventsgood$YearBorn >= 1648 & eventsgood$YearBorn <= 1670 & eventsgood$BirthPlace_trimmed == "Kobenhavn", 1, 0)
eventsgood$Kobenhavn_plague <- ifelse(eventsgood$YearBorn >= 1701 & eventsgood$YearBorn <= 1722 & eventsgood$BirthPlace_trimmed == "Kobenhavn", 1, 0)
eventsgood$Kobenhavn_firstfire <- ifelse(eventsgood$YearBorn >= 1718 & eventsgood$YearBorn <= 1738 & eventsgood$BirthPlace_trimmed == "Kobenhavn", 1, 0)
eventsgood$Kobenhavn_secondfire <- ifelse(eventsgood$YearBorn >= 1785 & eventsgood$YearBorn <= 1805 & eventsgood$BirthPlace_trimmed == "Kobenhavn", 1, 0)
eventsgood$Kobenhavn_napolean <- ifelse(eventsgood$YearBorn >= 1791 & eventsgood$BirthPlace_trimmed == "Kobenhavn", 1, 0)

### Edinburgh ####
eventsgood$Edinburgh_fire <- ifelse(eventsgood$YearBorn >= 1534 & eventsgood$YearBorn <= 1554 & eventsgood$BirthPlace_trimmed == "Edinburgh", 1, 0)
eventsgood$Edinburgh_langsiege <- ifelse(eventsgood$YearBorn >= 1561 & eventsgood$YearBorn <= 1583 & eventsgood$BirthPlace_trimmed == "Edinburgh", 1, 0)
eventsgood$Edinburgh_bishopsiege <- ifelse(eventsgood$YearBorn >= 1629 & eventsgood$YearBorn <= 1649 & eventsgood$BirthPlace_trimmed == "Edinburgh", 1, 0)
eventsgood$Edinburgh_cromwellsiege <- ifelse(eventsgood$YearBorn >= 1640 & eventsgood$YearBorn <= 1660 & eventsgood$BirthPlace_trimmed == "Edinburgh", 1, 0)
eventsgood$Edinburgh_plague <- ifelse(eventsgood$YearBorn >= 1634 & eventsgood$YearBorn <= 1655 & eventsgood$BirthPlace_trimmed == "Edinburgh", 1, 0)
eventsgood$Edinburgh_jacobitesiege1 <- ifelse(eventsgood$YearBorn >= 1679 & eventsgood$YearBorn <= 1699 & eventsgood$BirthPlace_trimmed == "Edinburgh", 1, 0)
eventsgood$Edinburgh_jacobitesiege2 <- ifelse(eventsgood$YearBorn >= 1705 & eventsgood$YearBorn <= 1725 & eventsgood$BirthPlace_trimmed == "Edinburgh", 1, 0)
eventsgood$Edinburgh_jacobitesiege3 <- ifelse(eventsgood$YearBorn >= 1735 & eventsgood$YearBorn <= 1755 & eventsgood$BirthPlace_trimmed == "Edinburgh", 1, 0)

### Rostock ####
eventsgood$rostock_occupation1 <- ifelse(eventsgood$YearBorn >= 1608 & eventsgood$YearBorn <= 1658 & eventsgood$BirthPlace_trimmed == "Rostock", 1, 0)
eventsgood$rostock_occupation2 <- ifelse(eventsgood$YearBorn >= 1690 & eventsgood$YearBorn <= 1731 & eventsgood$BirthPlace_trimmed == "Rostock", 1, 0)
eventsgood$rostock_fire <- ifelse(eventsgood$YearBorn >= 1667 & eventsgood$YearBorn <= 1687 & eventsgood$BirthPlace_trimmed == "Rostock", 1, 0)

### Uppsala ####
eventsgood$uppsala_conquest <- ifelse(eventsgood$YearBorn >= 1511 & eventsgood$YearBorn <= 1531 & eventsgood$BirthPlace_trimmed == "Uppsala", 1, 0)
eventsgood$uppsala_fire <- ifelse(eventsgood$YearBorn >= 1692 & eventsgood$YearBorn <= 1712 & eventsgood$BirthPlace_trimmed == "Uppsala", 1, 0)
eventsgood$uppsala_plague <- ifelse(eventsgood$YearBorn >= 1700 & eventsgood$YearBorn <= 1721 & eventsgood$BirthPlace_trimmed == "Uppsala", 1, 0)

### Amsterdam ####
eventsgood$amsterdam_fire1 <- ifelse(eventsgood$YearBorn >= 1411 & eventsgood$YearBorn <= 1431 & eventsgood$BirthPlace_trimmed == "Amsterdam", 1, 0)
eventsgood$amsterdam_fire2 <- ifelse(eventsgood$YearBorn >= 1442 & eventsgood$YearBorn <= 1462 & eventsgood$BirthPlace_trimmed == "Amsterdam", 1, 0)
eventsgood$amsterdam_siege <- ifelse(eventsgood$YearBorn >= 1623 & eventsgood$YearBorn <= 1644 & eventsgood$BirthPlace_trimmed == "Amsterdam", 1, 0)
eventsgood$amsterdam_plague <- ifelse(eventsgood$YearBorn >= 1625 & eventsgood$YearBorn <= 1647 & eventsgood$BirthPlace_trimmed == "Amsterdam", 1, 0)

### Leiden ####
eventsgood$leiden_siege <- ifelse(eventsgood$YearBorn >= 1563 & eventsgood$YearBorn <= 1584 & eventsgood$BirthPlace_trimmed == "Leiden", 1, 0)
eventsgood$leiden_plague <- ifelse(eventsgood$YearBorn >= 1625 & eventsgood$YearBorn <= 1646 & eventsgood$BirthPlace_trimmed == "Leiden", 1, 0)

### Lund ####
eventsgood$lund_battleoccupation <- ifelse(eventsgood$YearBorn >= 1666 & eventsgood$YearBorn <= 1689 & eventsgood$BirthPlace_trimmed == "Lund", 1, 0)
eventsgood$lund_plague <- ifelse(eventsgood$YearBorn >= 1644 & eventsgood$YearBorn <= 1665 & eventsgood$BirthPlace_trimmed == "Lund", 1, 0)

### Paris ####
# eventsgood$paris_siege <- ifelse(eventsgood$YearBorn >= 1405 & eventsgood$YearBorn <= 1439 & eventsgood$BirthPlace_trimmed == "Paris", 1, 0)
# eventsgood$paris_bartholomew <- ifelse(eventsgood$YearBorn >= 1562 & eventsgood$YearBorn <= 1582 & eventsgood$BirthPlace_trimmed == "Paris", 1, 0)
# eventsgood$paris_fronde <- ifelse(eventsgood$YearBorn >= 1638 & eventsgood$YearBorn <= 1663 & eventsgood$BirthPlace_trimmed == "Paris", 1, 0)
# eventsgood$paris_revolution <- ifelse(eventsgood$YearBorn >= 1779 & eventsgood$BirthPlace_trimmed == "Paris", 1, 0)

### Turku ####
eventsgood$turku_abosiege <- ifelse(eventsgood$YearBorn >= 1553 & eventsgood$YearBorn <= 1573 & eventsgood$BirthPlace_trimmed == "Turku", 1, 0)
eventsgood$turku_abobloodbath <- ifelse(eventsgood$YearBorn >= 1587 & eventsgood$YearBorn <= 1609 & eventsgood$BirthPlace_trimmed == "Turku", 1, 0)
eventsgood$turku_plague <- ifelse(eventsgood$YearBorn >= 1700 & eventsgood$YearBorn <= 1721 & eventsgood$BirthPlace_trimmed == "Turku", 1, 0)
eventsgood$turku_rswar <- ifelse(eventsgood$YearBorn >= 1731 & eventsgood$YearBorn <= 1753 & eventsgood$BirthPlace_trimmed == "Turku", 1, 0)

### Utrecht ####
eventsgood$utrecht_plague <- ifelse(eventsgood$YearBorn >= 1625 & eventsgood$YearBorn <= 1647 & eventsgood$BirthPlace_trimmed == "Utrecht", 1, 0)
eventsgood$utrecht_rampjaar <- ifelse(eventsgood$YearBorn >= 1731 & eventsgood$YearBorn <= 1753 & eventsgood$BirthPlace_trimmed == "Utrecht", 1, 0)

##### Generating the number of families concerned by events #####

event_dummies <- eventsgood[, !(names(eventsgood) %in% c("PKey", "YearBorn", "BirthPlace_trimmed"))]

frequency_events <- colSums(event_dummies == 1, na.rm = TRUE)

frequency_eventsdf <- data.frame(
  Variable = names(frequency_events),
  Frequency = as.numeric(frequency_events),
  stringsAsFactors = FALSE
)

print(frequency_eventsdf)

############################################################
########## BUILD TABLE OF EVENTS FREQUENCIES ###############
############################################################

# Extract births directly from Birthplace_frequency
births_lookup <- Birthplace_frequency %>%
  filter(BirthPlace_trimmed %in% c("Stockholm", "Kobenhavn", "Edinburgh", "Rostock", "Uppsala",
                                   "Amsterdam", "Leiden", "Lund", "Turku", "Utrecht")) %>%
  mutate(
    City = ifelse(BirthPlace_trimmed == "Kobenhavn", "Copenhagen", BirthPlace_trimmed),
    Births = n
  ) %>%
  select(City, Births)

# Metadata linking raw dummy names to the final table
table_meta <- data.frame(
  Variable = c(
    "stockholm_bloodbath",
    "stockholm_conquest",
    "stockholm_firstgreatfire",
    "stockholm_plague",
    "stockholm_secondgreatfire",
    
    "Kobenhavn_siegefeud",
    "Kobenhavn_siegesecond",
    "Kobenhavn_plague",
    "Kobenhavn_napolean",
    
    "Edinburgh_fire",
    "Edinburgh_langsiege",
    "Edinburgh_bishopsiege",
    "Edinburgh_cromwellsiege",
    "Edinburgh_plague",
    
    "rostock_occupation1",
    "rostock_occupation2",
    "rostock_fire",
    
    "uppsala_conquest",
    "uppsala_fire",
    "uppsala_plague",
    
    "amsterdam_fire1",
    "amsterdam_fire2",
    "amsterdam_siege",
    "amsterdam_plague",
    
    "leiden_siege",
    "leiden_plague",
    
    "lund_battleoccupation",
    "lund_plague",
    
    "turku_abosiege",
    "turku_abobloodbath",
    "turku_plague",
    "turku_rswar",
    
    "utrecht_plague",
    "utrecht_rampjaar"
  ),
  City = c(
    rep("Stockholm", 5),
    rep("Copenhagen", 4),
    rep("Edinburgh", 5),
    rep("Rostock", 3),
    rep("Uppsala", 3),
    rep("Amsterdam", 4),
    rep("Leiden", 2),
    rep("Lund", 2),
    rep("Turku", 4),
    rep("Utrecht", 2)
  ),
  Event = c(
    "Stockholm Blood Bath",
    "Conquest of Stockholm",
    "First Great Stockholm Fire",
    "Bubonic Plague",
    "Second Great Stockholm Fire",
    
    "Siege of the Count's Feud",
    "Siege of the Second Northern War",
    "Bubonic Plague",
    "1st and 2nd Battles of Copenhagen of the Napoleonic wars",
    
    "Burning of Edinburgh",
    "The Lang Siege during the Marian Civil War",
    "Siege of Edinburgh Castle during the Bishops' Wars",
    "Siege of Edinburgh Castle during Oliver Cromwell’s invasion of Scotland",
    "The Great Plague",
    
    "Occupation of Rostock during the 30 Years War",
    "Second occupation of Rostock",
    "Great Fire of Rostock",
    
    "Conquest of Uppsala during the Swedish War of Liberation",
    "Uppsala Fire",
    "Bubonic Plague",
    
    "First Great Fire of Amsterdam",
    "Second Great Fire of Amsterdam",
    "Siege of Amsterdam during the Ostend War",
    "Bubonic plague",
    
    "Siege of Leiden during the Eighty Years' War",
    "Bubonic plague pandemic",
    
    "Battle of Lund and Danish occupation during the Scanian War",
    "Bubonic plague",
    
    "Siege of Abo",
    "Siege of Turku Castle and Abo Blood Bath",
    "Bubonic Plague",
    "Russo-Swedish War",
    
    "Bubonic plague",
    "Rampjaar"
  ),
  Years = c(
    "1520",
    "1523",
    "1625",
    "1710-1711",
    "1759",
    
    "1534-1536",
    "1658-1660",
    "1711-1712",
    "1801 \\& 1807",
    
    "1544",
    "1571-1573",
    "1639",
    "1650",
    "1644-1645",
    
    "1618-1648",
    "1700-1721",
    "1677",
    
    "1521",
    "1702",
    "1710-1711",
    
    "1421",
    "1452",
    "1633-1634",
    "1635-1637",
    
    "1573-1574",
    "1635-1636",
    
    "1676-1679",
    "1654-1655",
    
    "1563",
    "1597-1599",
    "1710-1711",
    "1741-1743",
    
    "1635-1637",
    "1672"
  ),
  References = c(
    "",
    "\\citeN{heckscher1954}",
    "\\citeN{elgan2015}",
    "\\citeN{bain2014}",
    "",
    
    "",
    "\\citeN{jespersen2018}",
    "",
    "",
    
    "",
    "",
    "\\citeN{wormald2005}",
    "\\citeN{welsh2003}",
    "",
    
    "\\citeN{fulbrook2019}",
    "\\citeN{fulbrook2019}",
    "\\citeN{fulbrook2019}",
    
    "\\citeN{heckscher1954}",
    "\\citeN{elgan2015}",
    "\\citeN{bain2014}",
    
    "",
    "\\citeN{israel1995}",
    "\\citeN{persson2015}",
    "",
    
    "\\citeN{israel1995}",
    "\\citeN{persson2015}",
    
    "\\citeN{heckscher1954}, \\citeN{elgan2015}",
    "\\citeN{bain2014}",
    
    "",
    "\\citeN{singleton1998}",
    "\\citeN{meinander2020}",
    "",
    
    "\\citeN{israel1995}",
    "\\citeN{persson2015}"
  ),
  order_city = c(
    rep(1, 5),
    rep(2, 4),
    rep(3, 5),
    rep(4, 3),
    rep(5, 3),
    rep(6, 4),
    rep(7, 2),
    rep(8, 2),
    rep(9, 4),
    rep(10, 2)
  ),
  order_event = c(
    1:5, 1:4, 1:5, 1:3, 1:3, 1:4, 1:2, 1:2, 1:4, 1:2
  ),
  stringsAsFactors = FALSE
)

nice_table <- table_meta %>%
  left_join(frequency_eventsdf, by = "Variable") %>%
  left_join(births_lookup, by = "City") %>%
  mutate(Frequency = ifelse(is.na(Frequency), 0, Frequency))

jacobite_total <- sum(
  frequency_eventsdf$Frequency[
    frequency_eventsdf$Variable %in% c("Edinburgh_jacobitesiege1",
                                       "Edinburgh_jacobitesiege2",
                                       "Edinburgh_jacobitesiege3")
  ],
  na.rm = TRUE
)

jacobite_births <- births_lookup$Births[births_lookup$City == "Edinburgh"]

jacobite_row <- data.frame(
  Variable = "Edinburgh_jacobitesieges_combined",
  City = "Edinburgh",
  Event = "Jacobite Sieges",
  Years = "1689, 1715, 1745",
  References = "",
  order_city = 3,
  order_event = 6,
  Frequency = jacobite_total,
  Births = jacobite_births,
  stringsAsFactors = FALSE
)

copenhagen_fires_total <- sum(
  frequency_eventsdf$Frequency[
    frequency_eventsdf$Variable %in% c("Kobenhavn_firstfire", "Kobenhavn_secondfire")
  ],
  na.rm = TRUE
)

copenhagen_births <- births_lookup$Births[births_lookup$City == "Copenhagen"]

copenhagen_fires_row <- data.frame(
  Variable = "Kobenhavn_fires_combined",
  City = "Copenhagen",
  Event = "Copenhagen Fires",
  Years = "1728 \\& 1795",
  References = "\\citeN{bain2014}",
  order_city = 2,
  order_event = 4,
  Frequency = copenhagen_fires_total,
  Births = copenhagen_births,
  stringsAsFactors = FALSE
)

final_table <- nice_table %>%
  bind_rows(jacobite_row, copenhagen_fires_row) %>%
  arrange(order_city, order_event) %>%
  select(
    Variable,
    City,
    Births,
    Event,
    Years,
    References,
    Frequency,
    order_city,
    order_event
  )

final_table <- final_table %>%
  filter(!(Variable %in% c("Edinburgh_jacobitesiege1",
                           "Edinburgh_jacobitesiege2",
                           "Edinburgh_jacobitesiege3",
                           "Kobenhavn_firstfire",
                           "Kobenhavn_secondfire"))) %>%
  select(
    City,
    `# births` = Births,
    Event,
    Years,
    References,
    `# concerned` = Frequency
  )

print(final_table)

############################################################
################ EXPORT TO .txt #############################
############################################################

latex_rows <- final_table %>%
  group_by(City) %>%
  mutate(
    row_in_city = row_number(),
    n_rows_city = n()
  ) %>%
  ungroup() %>%
  mutate(
    City_col = ifelse(row_in_city == 1,
                      paste0("\\multirow{", n_rows_city, "}{*}{", City, "}"),
                      ""),
    Births_col = ifelse(row_in_city == 1,
                        paste0("\\multirow{", n_rows_city, "}{*}{", `# births`, "}"),
                        ""),
    latex_line = paste0(
      City_col, " & ",
      Births_col, " & ",
      Event, " & ",
      Years, " & ",
      References, " & ",
      `# concerned`, " \\\\"
    )
  )

# preserve the order already present in final_table
city_order <- unique(latex_rows$City)

latex_body <- unlist(lapply(city_order, function(cty) {
  df <- latex_rows[latex_rows$City == cty, ]
  c(df$latex_line, "\\midrule")
}))

latex_body <- latex_body[-length(latex_body)]

latex_full <- c(
  "\\begin{landscape}",
  "",
  "\\begin{table}[H]",
  "\\footnotesize \\centering",
  "\\scalebox{0.9}{\\begin{tabular}{",
  "    >{\\centering\\arraybackslash}m{2.5cm}",
  "    >{\\centering\\arraybackslash}m{1.5cm}",
  "    >{\\centering\\arraybackslash}m{7.5cm}",
  "    >{\\centering\\arraybackslash}m{3.5cm}",
  "    >{\\centering\\arraybackslash}m{4.5cm}",
  "    >{\\centering\\arraybackslash}m{2.5cm}",
  "}",
  "\\toprule",
  "\\textbf{City} & \\textbf{\\# births} & \\textbf{Event} & \\textbf{Years} & \\textbf{References} & \\textbf{\\# concerned} \\\\",
  "\\midrule",
  latex_body,
  "\\bottomrule",
  "\\end{tabular}}",
  "\\caption{Main events in the 10 most frequent birth places and number of professors' families affected. To be affected in their QQ-tradeoff, the event should occur in a time window of -10 years and +10 years around the birth of the professor.}",
  "\\label{tab:events}",
  "\\end{table}",
  "",
  "\\end{landscape}"
)

writeLines(latex_full, "events_table.txt")