# Code to plot annual_radiance of  projects
# abandoned/completed between 2015 - 2018
# Author: Sayan Dasgupta
# Loading required libraries (#2307)
library(stringr)
library(dplyr)
library(tidyr)
library(lubridate)
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"))

# Reading the data
proj_radiance <- read.csv("../DATA/largest_tpp_manual_annual_radiance_3km.csv",check.names=FALSE)

# Getting the data ready for Plotting
melt_proj_radiance <- melt(proj_radiance, id.vars = c("project_name", "project_number","company","company_code"), 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"),variable.name = "date", value.name = "Radiance")
melt_proj_radiance$date <- as.Date(melt_proj_radiance$date)

# Getting data for "Date of announcement: and "Date of completion" or 
# "Abandoned on" or "Sheved on"
project_events <- dbGetQuery(mydb,"select PROJECT_NAME,PROJECT_NUMBER,COMPANY,
                                   COMPANY_CODE,EVENT,EVENT_DATE
                                   from capex_all_project_events")
colnames(project_events) <- tolower(colnames(project_events))
project_events$event_date <- as.Date(project_events$event_date,
                                         format = "%d %B %Y")
req_proj <- left_join(melt_proj_radiance,
                      project_events,by = c("project_name","project_number",
                                            "company","company_code"))
date_of_announcement <- req_proj %>% filter(event == "Date of announcement")
names(date_of_announcement)[names(date_of_announcement) == 'event_date'] <- 'date_of_announcement'
date_of_abandon <- req_proj %>% filter(event == "Abandoned on" | event == "Shelved on")
names(date_of_abandon)[names(date_of_abandon) == 'event_date'] <- 'date_of_abandon'
date_of_completion <- req_proj %>% filter(event == "Completed")
names(date_of_completion)[names(date_of_completion) == 'event_date'] <- 'date_of_completion'
date_of_announcement  <- date_of_announcement %>%
    distinct(project_name,project_number,company,company_code,
             date_of_announcement,Radiance,date)
date_of_abandon  <- date_of_abandon %>%
    distinct(project_name,project_number,company,company_code,date_of_abandon)
date_of_completion  <- date_of_completion %>%
    distinct(project_name,project_number,company,company_code,date_of_completion)
announce_abandon <- left_join(date_of_announcement,
                              date_of_abandon ,
                              by = c("project_name","project_number",
                                     "company","company_code"))
announce_abandon_complete <- left_join(announce_abandon,date_of_completion,
                                       by = c("project_name","project_number",
                                              "company","company_code"))
products <- dbGetQuery(mydb,"select COMPANY,COMPANY_CODE,
                                    PROJECT_NAME,PROJECT_NUMBER,PRODUCT from
                                    capex_all_products_and_capacity")
colnames(products) <- tolower(colnames(products))
complete_products <- left_join(announce_abandon_complete,products,
                               by = c("project_name","project_number",
                                      "company","company_code"))
complete_products <- complete_products %>%
    group_by(project_name,project_number,
             company,company_code) %>%
    summarise(products  = paste(product, collapse =",")) 
complete_products <- as.data.frame(complete_products)
complete_products_date <- left_join(announce_abandon_complete,complete_products,
                            by = c("project_name","project_number",
                                   "company","company_code"))
complete_products_date <- complete_products_date %>% arrange(date_of_completion)
# Plotting
pdf("../RESULTS/plot_largest_tpp_manual_annual_radiance_3km.pdf",onefile = TRUE)
for(i in unique(complete_products_date$project_name)) {
    selected_project <- i
    proj_select <- complete_products_date[which(complete_products_date$project_name == selected_project),]
    date_of_announcement <- proj_select$date_of_anouncement[1]
    date_of_abandon <- proj_select$date_of_abandon[1]
    date_of_completion <- proj_select$date_of_completion[1]
    plots <- ggplot(proj_select, aes(x = date ,y = Radiance,group = 1)) +
        geom_line() +
        theme(legend.position="bottom") +
        ylab(TeX('Radiance ($nW cm^{-2}$ $sr^{-1})$')) +
        scale_x_date(limits = as.Date(c("2012-01-01","2020-01-01"))) +
        ggtitle(selected_project) +
        xlab("") +
        geom_vline(aes(xintercept = date_of_announcement),colour = "#BB0000",linetype = "dashed") +
        geom_text(aes(x = date_of_announcement, label="\nDate of Announcement", y = median(Radiance,na.rm = TRUE)), colour="blue", angle=90, text=element_text(size=11)) +
        geom_vline(aes(xintercept = date_of_abandon),colour = "#BB0000",linetype = "dashed") +
        geom_text(aes(x = date_of_abandon, label="\nAbandoned on", y = median(Radiance,na.rm = TRUE)), colour="blue", angle=90, text=element_text(size=11)) +
        geom_vline(aes(xintercept = date_of_completion),colour = "#BB0000",linetype = "dashed") +
        geom_text(aes(x = date_of_completion, label="\nCompleted on", y = median(Radiance,na.rm = TRUE)), colour="blue", angle=90, text=element_text(size=11))
    print(plots)
}
dev.off()