##  OLD VERSION 
##
##  This .R file would expect the large .txt file to be chopped into 
##		 individual .tsv reports and would then parse those
##  The newer version just reads in the raw .txt file and chops it automagically
##
## This file still has good output, which we need to copy away. 

library(XLConnect)
library(bit)
library(bit64)
library(data.table)

.us()
setScience("Spotify Data", subl=FALSE)


## File where to save results
f.out <- as.path(outDir, "Spotify_New_Data_Fields.xlsx")
f.out.data <- as.path(outDir, "Spotify_New_Data.xlsx")


## Folder containing the input TSV files
folder <- as.path(dataDir, "Spotify - Monthly additional data 2014-02-01 2014-02-28")

## Load them in
files <- dir(folder, full=TRUE)
setattr(files, "names", basename(files))
raw <- lapply(files, fread, header=TRUE)


DataPoints <- lapply(raw, function(D)  unique(D[["Data point"]]) )

## each raw file should have only one Data Point.  If they do not, that means we did not chop them up properly
DataPoints.cnt <- sapply(DataPoints, length)
if (any(DataPoints.cnt != 1))
	warning ("More than one Data Point for\n     ", pasteQand(nwhich(DataPoints.cnt != 1)))


## Have Data point be the name of the DT
setattr(raw, "names", paste0(DataPoints, " (", names(DataPoints), ")"))


## Clean up each data set
Fields <- 
	lapply(raw, function(D) {
		DP <- D[["Data point"]][[1]]

		cols <- desc(D, quiet=TRUE)
		cols[, values := ""]
		numbs <- c("integer", "numeric", "integer64")

		cols[.(numbs), values := '[Number Value]']
		cols[!.(numbs), values := sapply(Columns, function(C)    
				# as.data.table(table(D[[C]]))[,  paste_l(sprintf("%s (%i)", V1, count), cols=max(3, min(10, 200/max(5+nchar(V1)))), spacer=",  ")]
				as.data.table(table(D[[C]]))[,  paste_l(sprintf("%s (%i)", V1, count), cols=10, spacer=",  ")]
		)]

		cols[!.(numbs), valuecount := sapply(Columns, function(C) lunique(D[[C]]) )]

		cols[, list('Report'=DP, 'Field'=Columns, 'Values (Count per value)'=values, 'Number of Distinct Values' = valuecount)]
	})


# ----------------------------------------------------------------------------


		FF <-rbindlist(Fields)[Field != "Data point"]
		saveImageTo()

    wb    <- loadWorkbook(f.out, create=TRUE)
    sheet <- "Spotify New Data from Feb 2014"

    createSheet(wb, name=sheet)
     
    # Write built-in data set 'CO2' to the worksheet created above;
    # offset from the top left corner and with default header = TRUE
    writeWorksheet(wb, FF, sheet=sheet)

    ## Width units are 1/256th of a character
    for (i in seq_along(FF))
	    setColumnWidth(wb, sheet=sheet, column=i, width=min(255, max(nchar(unlist(strsplit(as.character(FF[[i]]), "\\n")))+1)) * 256)

    # Save workbook (this actually writes the file to disk)
    saveWorkbook(wb)
    message("Done writing to XLS.")


    .o(f.out)


# ----------------------------------------------------------------------------
		countrygrps <- fread(as.path(dataDir, "Country Groups.csv"))


		## Clean Names
		# setnames(countrygrps, gsub("COUNTRY", "Country", names(countrygrps)))
		setnames(countrygrps, gsub(" ", "_", topropper(names(countrygrps))))
		setkey(countrygrps, Country_Code)

		countrygrps[, table(region_group)]

		raw <- lapply(raw, function(D) {
								setkey(D, Country)[countrygrps, `:=`(region_group=i.region_group, Country_Broad_Group=i.Country_Broad_Group)]
						})

		raw
# ----------------------------------------------------------------------------


    folder.out <- as.path(outDir, "CSVs")
		dir.create(folder.out, showWarnings=FALSE)


		for (nm in names(raw)) {
			DD <- raw[[nm]]
			nm2 <- gsub(".*\\((.*).tsv\\)", "\\1.csv", nm)

			write.table(DD[, !"Data point", with=FALSE], file=as.path(folder.out, nm2), sep=",", row.names=FALSE, col.names=TRUE, fileEncoding="UTF-8")
		}
		.o(folder.out)


# ----------------------------------------------------------------------------