#SETTING UP DEPENDENCIES
library(readxl)
library(dplyr)
library(RMySQL)
source("Configurations.R")
source("Utilities.R")
Sys.setlocale("LC_ALL", "en_US.UTF8")

#READING FILES IN THE INPUT DIRECTORY
fileVector <- listFiles(inputFolder)

#ITERATING THROUGH THE SHEETS AND READING PROPERTY AND TAX INFO
for(file in fileVector){
  
  propertyInfoSheets <- readTables(filePath = file, sheetNames = propertyInfoSheetNames, 
                                   startRow = propertyInfoStartRow, 
                                   headerPresent = propertyInfoHeaderPresent)
  
  taxInfoSheets <- readTables(filePath = file, sheetNames = taxInfoSheetNames, 
                              startRow = taxInfoStartRow, headerPresent = taxInfoHeaderPresent)
}

#RETAINING ONLY THE REQUIRED COLUMNS
propertyInfoSheets <- dropColumns(tableList = propertyInfoSheets, 
                                  columnsToRetain = propertyInfoColumnsToRetain)

taxInfoSheets <- dropColumns(tableList = taxInfoSheets,
                             columnsToRetain = taxInfoColumnsToRetain)

#COLLATING THE PROPERTY AND TAX INFO TABLES
propertyInfoTable <- collateTables(tableList = propertyInfoSheets)
rm(propertyInfoSheets)
taxInfoTable <- collateTables(tableList = taxInfoSheets)
rm(taxInfoSheets)

#REMOVING DUPLICATES
propertyInfoTable = distinct(propertyInfoTable, PID, Owner_F_Name, Owner_M_Name, Owner_L_Name,
                             Address, Ward_No)
taxInfoTable = distinct(taxInfoTable, PID, Rvn_reference_no, AssessmentYear, Tax, Cess, Penalty, SolidCess, TotalAmount)

#ADDING THE SL_NO IDENTFIER TO PROPERTY TABLE
propertyInfoTable <- mutate(propertyInfoTable, sl_no = 1:nrow(propertyInfoTable))

#OBTAINING THE COMPLETE INFORMATION BY JOINING THE TABLES
totalInfoTable <- left_join(taxInfoTable, propertyInfoTable, by = "PID")

#CHECKING UNMATCHED VALUES AND WRITING THEM TO A LOG FILE
unmatchedRows <- filter(totalInfoTable, is.na(Owner_F_Name), is.na(Owner_M_Name), 
                                        is.na(Owner_L_Name), is.na(Address)) 
write.csv(unmatchedRows, "UnmatchedValuesLog.csv")

#CONVERTING TABLE TO REQUIRED FORM

totalInfoTable[, "AssessmentYear"] = substr(totalInfoTable[, "AssessmentYear"], 1, 4)

totalInfoTable <- totalInfoTable %>% 
                  mutate(app_no = paste(PID, Rvn_reference_no, AssessmentYear, sep = "_"),
                         owner_name = paste(Owner_F_Name, Owner_M_Name, Owner_L_Name, sep = " ")) %>%
                  select(sl_no, PID, owner_name, Address, Ward_No, AssessmentYear, app_no, 
                         Tax, Cess, Penalty, SolidCess,TotalAmount) %>%
                  rename(year = AssessmentYear, amount = TotalAmount, ward_no = Ward_No)

rm(propertyInfoTable)
rm(taxInfoTable)

#CONVERTING TO UTF 8
totalInfoTable[, "owner_name"] = iconv(totalInfoTable[, "owner_name"], to = "UTF-8")
totalInfoTable[, "Address"] = iconv(totalInfoTable[, "Address"], to = "UTF-8")

#REMOVING ANY DUPLICATIONS
totalInfoTableCopy = distinct(totalInfoTable, sl_no, PID, owner_name, Address, ward_no,
                              year, app_no, Tax, Cess, Penalty, SolidCess, amount)

#WRITING THE TABLE
write.csv(totalInfoTable, "TMK_NewData.csv", row.names = FALSE)
write.csv(arrange(totalInfoTable, PID, year)[1:150, ], "TMK_NewData_Sample.csv", row.names = FALSE)
#VALIDATING IF NON ENGLISH CHARACTERS ARE PROPERLY READ
print(totalInfoTable$owner_name[1])

#WRITING SAMPLE CSV 
sampleTable <- totalInfoTable[1:5, ]
sampleTable[, "owner_name"] = iconv(sampleTable[, "owner_name"], to = "UTF-8")
write.csv(sampleTable, "utf8sampleFile.csv", row.names = FALSE)

#CONNECTING TO THE MYSQL DB
sqlDB = dbConnect(MySQL(), user = "root", password = "gC6icstvvzzvk", dbname = "test_evs",
                  host = "14.139.134.252", port = 22)
#, port = 22
dbListTables(sqlDB)