## Setup working environment
setScience("Looker", subProj="compare", subl=FALSE)


## Which files to examine
pat.files_using <- "bi.acc.*20160707"

## Extract the files
files.orig <- extractIngestFiles(pat=pat.files_using, sub="ORIG", ext="lookml")
files.new  <- extractIngestFiles(pat=pat.files_using, sub="NEW",  ext="lookml")

## Confirm there is at least one file, and that they have the same file name in NEW and ORIG
stopifnot(length(names(files.new)) >= 1, identical(names(files.new), names(files.orig)))


## These are the files names that will be used to dump out the lookml into a TSV
## Use names same as files.orig/.new  but replacing the ext with TSV
tsv_full_file_path.orig <- gsub(".view.lookml$", '.tsv', files.orig)
tsv_full_file_path.new  <- gsub(".view.lookml$", '.tsv', files.new)


## ---------------------------------------------------------------- ##
## Read the files into (a list of) data.tables
## First the 'Orig' then the 'New'
## ---------------------------------------------------------------- ##
  ll_DT.org <- emptylist(files.orig)
  for (nm in names(files.orig)) {
    tbl <- strsplit(nm, "\\.")[[c(1, 2)]]
    catheader("Proceesing: ", tbl)
    lookml_to_csv(files.orig[[nm]], full_file_path=tsv_full_file_path.orig[[nm]], schema="bi", tbl=tbl, include_previous_version=FALSE, allow_double_quotes=TRUE)
    ll_DT.org[[nm]] <- fread(tsv_full_file_path.orig[[nm]], colClasses="character")
  }

  ll_DT.new <- emptylist(files.new)
  for (nm in names(files.new)) {
    tbl <- strsplit(nm, "\\.")[[c(1, 2)]]
    lookml_to_csv(files.new[[nm]], full_file_path=tsv_full_file_path.new[[nm]], schema="bi", tbl=tbl, include_previous_version=FALSE)
    ll_DT.new[[nm]] <- fread(tsv_full_file_path.new[[nm]], colClasses="character")
  }

  ## Confirm all the names are the same
  stopifnot(names(ll_DT.new) == names(ll_DT.org))
## ---------------------------------------------------------------- ##


## ---------------------------------------------------------------- ##
##  NEW JULY 4th 2016  ##
## ---------------------------------------------------------------- ##
nm <- names(files.new) [[1]]
DT.org <- copy(ll_DT.org[[nm]])
DT.new  <- copy(ll_DT.new[[nm]])

if ("value_format" %in% names(DT.org))
  DT.org[value_format == '[>=1000000]$ #,##0.0,,"" M""; [>=100 OR =0]$ #,##0; [>=.0000001]$ #,##0.0000;', value_format := '[>=1000000]$ #,##0.0,,"" M""; [>=10 OR =0]$ #,##0; [>=.0000001]$ #,##0.0000;']

## ------------------------------
## is_same_to_other
## ------------------------------
##    1 :=  IDENTICAL
##    0 :=  Not Present in Other
##   -1 :=  Different from Other
## ------------------------------

## all columns
all_columns <- unique(c(names(DT.org), names(DT.new), "required_fields"))

addColumnIfNotExist(DT.org, cols=all_columns, fill_with=list(NA_character_))
addColumnIfNotExist(DT.new,  cols=all_columns, fill_with=list(NA_character_))

## Have the key column of each table be the lookml field;  The field can be one of these columns
cols_for_key_candidate <- c("dimension", "dimension_group", "measure", "filter")
DT.org[, .key := apply(.SD, 1, function(x) if (length(removeNA(x)) != 1) stop ("Error for x = \n\t", pasteC(x, C="\n\t")) else removeText(removeNA(x), pat="\\s*#.*")), .SDcols=cols_for_key_candidate]
DT.new[, .key := apply(.SD, 1, function(x) if (length(removeNA(x)) != 1) stop ("Error for x = \n\t", pasteC(x, C="\n\t")) else removeText(removeNA(x), pat="\\s*#.*")), .SDcols=cols_for_key_candidate]

## Identify all lookml fields
all_lookml_fields <- unique(c(DT.org$.key, DT.new$.key)) %>% selfname_

## Check that there are no duplicated fileds in the lookml
## If this fails, check for duplicate keys
stopifnot(lunique(DT.org$.key) == nrow(DT.org), lunique(DT.new$.key) == nrow(DT.new))

## Remove the comment 'manually edited' 
DT.org <- DT.org[, lapply(.SD, removeText, pat="\\s*## MANUALLY EDITED FIELD.*$", ignore.case=TRUE)]
DT.new <- DT.new[, lapply(.SD, removeText, pat="\\s*## MANUALLY EDITED FIELD.*$", ignore.case=TRUE)]

## set col order and key
setcolorderpt(DT.org, names(DT.new))
setcolorderpt(DT.new, names(DT.org))
setkeyIfNot(DT.org, .key, organize=TRUE, verbose=FALSE)
setkeyIfNot(DT.new,  .key, organize=TRUE, verbose=FALSE)

DT.descriptions.org <- DT.org[.(all_lookml_fields)][!is.na(description)][description != "''", list(description), keyby=.key]
DT.descriptions.new <- DT.new[.(all_lookml_fields)][!is.na(description)][description != "''", list(description), keyby=.key]

OK       <- "<ALL OK>"
HIDDEN   <- "<JUST HIDDEN>"
ALIAS    <- "<JUST ALIAS>"
VF       <- "<JUST VALUE_FORMAT>"
VF_ALIAS <- "<JUST VALUE_FORMAT & ALIAS>"

DT.compare <- columnWiseEqual(DT.new[.(all_lookml_fields)], DT.org[.(all_lookml_fields)]) %>% 
                  apply(1, function(X) 
                    ifelse(all(X), OK, 
                      ifelse(identical(nwhich(!X), "hidden"), yes=HIDDEN,
                        ifelse(identical(nwhich(!X), "alias"), yes=ALIAS,
                          ifelse(identical(nwhich(!X), "value_format"), yes=VF,
                            ifelse(identical(sort(nwhich(!X)), c("alias", "value_format")), yes=VF_ALIAS,
                               no=pasteC(nwhich(!X), C="|")
                    )))))
                  ) %>% 
                  # apply(1, function(X) colnames(DT.org)[!X] %>% {browser(); ifelse(!length(.) | is.na(.), OK, pasteC(., C="|"))}) %>%
                  data.table(.key=all_lookml_fields, diff=., key=".key")

DT.compare[, missing_from_orig := .key %ni% DT.org$.key]
DT.compare[, missing_from_new  := .key %ni% DT.new$.key]
DT.compare[, just_hidden       := (diff == HIDDEN)]
DT.compare[, just_alias        := (diff == ALIAS)]
DT.compare[, just_vf           := (diff == VF)]
DT.compare[, just_vf_alias     := (diff == VF_ALIAS)]
DT.compare[, different_fields  := (diff %ni% c(OK, HIDDEN, ALIAS, VF, VF_ALIAS)) & (!missing_from_new) & (!missing_from_orig)]


old_lookml_fields_removed <- DT.compare[(missing_from_new),  .key]
new_lookml_fields_added   <- DT.compare[(missing_from_orig), .key]
different_fields          <- DT.compare[(different_fields),  .key]

.k <- "track_id"
DT.new[.k]
DT.org[.k]
DT.compare[.k]

catnn("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
catnn("There are ", length(different_fields), " that are different in an unknown fashion")
catnn("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")

## SHOW DIFFERENT
{
  catnn("HEREHER")
for (.k in different_fields) {
  .org_header <- "\n\n    ------------- [ORIGINAL] ------------- \n"
  .new_header <- "\n\n    ------------- [NEW]      ------------- \n"

  cols <- DT.compare[.k, strsplit(diff, "\\|")] %>% unlist

  if (length(cols) == 1 && cols == "description")
    next;

     ##  if (length(cols) == 1) {
     ##     .org_txt <- DT.org[.(.k)][[cols]]
     ##     .new_txt <- DT.new[.(.k)][[cols]]
     ## 
     ##                     
     ## 
     ##     cat("Field '", .k, "' is different only in  '", cols, "':"
     ##           , ifelse(are_two_strings_same(s1=.org_txt, s2=.new_txt, len_verbose=121, trim_pre_linebreak=TRUE, verbose="auto")
     ##             , yes = " <essentially the same though, except for maybe whitespace>"
     ##             , no  = paste0(
     ##                         .org_header,   gsub("\\\\n", "\n", .org_txt)  
     ##                       , .new_header,   gsub("\\\\n", "\n", DT.new[.(.k)][[cols]] ) )
     ##                     )
     ##         , "\n", sep="")
     ## 
     ##   } else {

    tmp.out <- cbind(
                  JUST_WS = NA,
                  NEW = t(DT.new [.k, cols, with=FALSE]),
                  OLD = t(DT.org [.k, cols, with=FALSE])
                )
    colnames(tmp.out) <- c("JUST WS  ", "NEW", "PREVIOUS/MANUAL")
    
    ## check if the field is only different in whitespace
    tmp.out[, "JUST WS  "] <- {apply(tmp.out, 1, function(r) are_two_strings_same(s1=removeText(pat="\\|(\\\\)+n\\s*", r[[2]]), s2=removeText(pat="\\|(\\\\)+n\\s*", r[[3]]), len_verbose=121, trim_pre_linebreak=TRUE, verbose=FALSE)) %>% cbind}
    tmp.out[, 2] %<>% {ifelse(is.na(.), ., paste0(substr(., 1, 88), ifelse(nchar(.) > 88, " ...  ", no="  ")))}
    tmp.out[, 3] %<>% {ifelse(is.na(.), ., paste0(substr(., 1, 88), ifelse(nchar(.) > 88, " ...  ", no="  ")))}
    {
        catheader(.k, endl=0)
        # print(apply(tmp.out, 2, function(x) ifelse(is.na(x), x, paste0(substr(x, 1, 88), ifelse(nchar(x) > 88, " ...  ", no="  ")))), quote=FALSE)
        print(tmp.out, quote=FALSE)
    }

  # readline("\n<press any key> ... ")
  catn("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
}
}

{
  catn("\n\n===============================\n\n")
  catnn("OLD FIELDS REMOVED:", "--------------------------", old_lookml_fields_removed, if (!length(old_lookml_fields_removed)) "\n   <None, great job!>\n", "--------------------------", "")
  catnn("NEW FIELDS ADDED:",   "--------------------------", new_lookml_fields_added, "--------------------------", "")

  catnn(pasteR(75), "   :: Note ::  You want the 'OLD' to be empty.\n               It's okay for 'NEW' to have items\n               You also want to check the different items", pasteR(75))
}

stop("\n\nbreak point for auto run --- \n\n do the rest manually", call.=FALSE)

# WRONG:  f.manual_fields <- writeDT(DT=DT.org[.(old_lookml_fields_removed)][, -".key", with=FALSE], sep="\t", ext="tsv", base.file.name = files.orig[[nm]] %>% basename %>% removeText(pat=".view.lookml") %>% paste0("__manual_pieces_to_add") )

catnn("copy them to ~/git/orch/src/Looker/additional_lookml_pieces/<schema>.<table>.additional_lookml.csv ---------- \n\n\t!!!!!!! wait (the above is slightly wrong) !!!!!!  \n\tThe columns and column order")

## Read in the current and combine
f.current <- "~/git/orch/src/Looker/additional_lookml_pieces/bi.accounting.additional_lookml.csv"

DT.current <- fread(f.current, colClasses="character")

zArchive(f.current, addTimeStamp=TRUE)

DT.manual_fields <- rbind(DT.current, DT.org[.(old_lookml_fields_removed)][, -".key", with=FALSE], fill=TRUE, use.names=TRUE)
write.table(DT.manual_fields, sep="\t", file=f.current, qmethod="escape", row.names=FALSE, col.names=TRUE)
subl(f.current)

setdiff(names(DT.org), names(DT.current))



# &&& LEFT OFF HERE --- check them 
##     
##     OLD FIELDS REMOVED:
##     --------------------------
##     artist_country_code
##     broad_region_group
##     count_distinct_track_uniques
##     country_is_cis
##     fees_cloud_publishing_ccur_dimension
##     fees_cloud_publishing_usd_dimension
##     label_country_code
##     label_is_orchard_test_label
##     label_status
##     label_type
##     main_isrc
##     p_line
##     release_avg_number_of_tracks_per_cd
##     release_avg_number_of_tracks_per_cd_dimension
##     release_months_since_release_dimension
##     release_number_of_cds
##     release_number_of_cds_sum
##     store_is_primarily_video_products
##     third_party_publisher
##     track_might_be_error
##     track_might_be_error_dimension
##     track_unique_id
##     track_version
##     track_videoresolution
##     --------------------------
##     
##     
##     NEW FIELDS ADDED:
##     --------------------------
##     account_breakdown_store_name
##     chadleys_favorite_trans_types
##     client_net_receipt_usd_cat
##     dbo_gross_sales_usd
##     effective_units_calc
##     est_plus_vod_volume
##     five_years
##     is_in_sme_label_country
##     ivod_volume
##     label_is_red_or_odd
##     netflix_tl
##     red_percentage
##     red_revenue
##     sd_or_hd
##     sme_affiliate_label_country_groupings
##     spotify_or_other
##     top_10_labels_in_2014
##     top_10_labels_in_2015
##     top_10_releases_in_2014
##     top_10_releases_in_2015
##     top_5_labels_in_2014
##     top_5_labels_in_2015
##     top_5_releases_in_2014
##     top_5_releases_in_2015
##     us_cananda_bo_gross_sales_usd
##     video_store_name_mgmt
##     vod_volume
##     --------------------------