# eventstudies for construction date of projects as defined by CMIE
# with 10 km radius nighttimelights for only manufacturing projects completed between 2015-2019
# Author: Sayan Dasgupta
# Loading required librarie
library(zoo)
library(tidyverse)
library(devtools)
library(eventstudies)
library(lubridate)
library(stringr)
library(dplyr)
library(tidyr)
library(RMySQL)
library(data.table)
library(latex2exp)
library(ggplot2)
# Connecting to the database
mydb <- dbConnect(dbDriver("MySQL"), host = "data.mayin.org",db="fld",
                  user=Sys.getenv("HHD_AND_FLD_USER"),
                  password=Sys.getenv("HHD_AND_FLD_PASSWORD"))

proj_radiance.csv <- as.data.frame(read.csv("./DATA/29_thermal_2015_2018_annual_radiance_10km.csv",check.names=FALSE))

proj_radiance.csv$project_company <- paste(proj_radiance.csv$project_name, "(", proj_radiance.csv$company, ")")

melt_proj_radiance.csv <- as.data.frame(melt(proj_radiance.csv, id.vars = c("project_company"), measure.vars = c("2012-01-01","2013-01-01","2014-01-01","2015-01-01","2016-01-01","2017-01-01","2018-01-01","2019-01-01","2020-01-01","2021-01-01"),variable.name = "date", value.name = "Radiance"))
melt_proj_radiance.csv <- melt_proj_radiance.csv %>%
    group_by(project_company) %>% mutate(normalised_radiance = Radiance/max(Radiance)) %>%
    ungroup()
melt_proj_radiance <- melt_proj_radiance.csv %>%
    distinct(project_company,date,normalised_radiance) %>%
    as.data.frame()
melt_proj_radiance_pivot <- melt_proj_radiance %>%
    tidyr::spread(key = project_company, value = normalised_radiance)
melt_proj_radiance_pivot <- as.data.frame(melt_proj_radiance_pivot)
proj_radiance_zoo <- read.zoo(melt_proj_radiance_pivot, drop = FALSE)

# I need construction starting  dates for all these projects
proj_events <- dbGetQuery(mydb,"select PROJECT_NAME,PROJECT_NUMBER,COMPANY,COMPANY_CODE,EVENT,EVENT_DATE from capex_all_project_events")
colnames(proj_events) <- tolower(colnames(proj_events))
proj_events <- proj_events %>% filter(event == "Construction commenced" | event == "Implementation started")
proj_events$project_company <- paste(proj_events$project_name, "(", proj_events$company, ")")
### Creating the events table
event_dates <- left_join(proj_radiance.csv,proj_events, by = c("project_company"))
event_dates$event_date <- as.Date(event_dates$event_date,
                                  format = "%d %B %Y")
event_dates$event_date<- lubridate::ymd(event_dates$event_date, truncated = 2L)
colnames(event_dates)[which(names(event_dates) == "event_date")] <- "when"
colnames(event_dates)[which(names(event_dates) == "project_company")] <- "name"
### Keeping only one event for each project
event_dates <- event_dates[order(event_dates$when, decreasing=TRUE),]
event_dates <- event_dates %>% distinct(name,.keep_all = TRUE)


## Doing the Event studies 
announce_10km <- eventstudy(firm.returns = proj_radiance_zoo,
                 event.list = event_dates,
                 event.window = 3,
                 type = "None",
                 to.remap = FALSE,
                 is.levels = TRUE, 
                 inference = TRUE,
                 inference.strategy = "bootstrap")
pdf("eventstudy_29_thermal_2015_2018_10km_construction.pdf")
plot(announce_10km)
dev.off()