# ------------------------------------------------------------------------------- #
#                                                                                 #
#   I'm not sure if the sale_return column of the itunes table                    #
#   has more than two values or not.                                              #
#   This queries the itunes table and finds out                                   #
#                                                                                 #
#   Also:  Are there any rows where units != 1 ?                                  #
#          Run another QRY to find out                                            #
#                                                                                 #
#                                                                                 #
# ------------------------------------------------------------------------------- #

##### RESULTS FROM BEING RAN: 
# 
#             Running query [Began at 05:20 AM]
#    -----------------------------------------------------------------------------------
# 
#    SELECT sale_return, units, artist, upc, order_id, customer_identifier, product_type_identifier
#      FROM production.staging_raw_itunes
#      WHERE sale_return <> 'S' AND sale_return <> 'R'
# 
#    -----------------------------------------------------------------------------------
#       Total time to execute query was 14.452 seconds
# 
# No non-sales / returns rows in itunes
# 
# 
#             Running query [Began at 05:20 AM]
#    -----------------------------------------------------------------------------------
# 
#    SELECT sale_return, units, artist, upc, order_id, customer_identifier, product_type_identifier
#      FROM production.staging_raw_itunes
#      WHERE sale_return <> 'S' AND sale_return <> 'R'
# 
#    -----------------------------------------------------------------------------------
#       Total time to execute query was 13.863 seconds
# 
# No non-unit units
# 
# 
#             Running query [Began at 05:20 AM]
#    -----------------------------------------------------------------------------------
# 
#    SELECT sale_return, units, artist, upc, order_id, customer_identifier, product_type_identifier
#      FROM production.staging_raw_itunes
#      WHERE sale_return <> 'S' AND sale_return <> 'R'
# 
#    -----------------------------------------------------------------------------------
#       Total time to execute query was 14.119 seconds
# 
# No negative units
#
#


filesForitunesCheck <- c()
# ------------------------------------------------------------------------------- #
{
  Q.check.sr <- 
  "SELECT sale_return, units, artist, upc, order_id, customer_identifier, product_type_identifier
  FROM production.staging_raw_itunes
  WHERE sale_return <> 'S' AND sale_return <> 'R'
  "

  try(dbDisconnect(con), silent=TRUE)
  con <- giveMeACon("PostgreSQL")
  DT_any_non_sales <- runQry(Q.check.sr, con)
  if (!nrow(DT_any_non_sales))
    message("No non-sales / returns rows in itunes")
  else {
    filesForitunesCheck["sr"] <- 
    jesus(DT_any_non_sales, dir=dataDir, sub=FALSE, stampFile=TRUE, stampDir=FALSE, summary=FALSE, verbose=FALSE)
    sr.unique <- DT_any_non_sales[, unique(sale_return)]
    message("SALES / RETURN COLUMN HAS ", 2+length(sr.unique), "VALUES:")
    cat("The following non-sales, non-returns values exist:\n\t", paste(sr.unique, collapse="\t"))
  }
}# ----------------------------------------------------------------------------- # 

# ------------------------------------------------------------------------------- #
{
  Q.check.units <- 
  "SELECT sale_return, units, artist, upc, order_id, customer_identifier, product_type_identifier
  FROM production.staging_raw_itunes
  WHERE units <> 1
  "

  try(dbDisconnect(con), silent=TRUE)
  con <- giveMeACon("PostgreSQL")
  DT_any_non_units <- runQry(Q.check.sr, con)
  if (!nrow(DT_any_non_units))
    message("No non-unit units")
  else {
    filesForitunesCheck["units"] <- 
    jesus(DT_any_non_units, dir=dataDir, sub=FALSE, stampFile=TRUE, stampDir=FALSE, summary=FALSE, verbose=FALSE)
    message("THERE *ARE* NON-UNIT UNITES")
    cat("The following rows contain non-unit units:\n")
    print(DT_any_non_units)
  }
}
# ----------------------------------------------------------------------------- # 



# ------------------------------------------------------------------------------- #
{
  Q.check.units.positivity <- 
  "SELECT artist, upc, order_id, customer_identifier, product_type_identifier, 
          SUM(  units *  ((-1) ^ ((sale_return = 'R') IS TRUE)::int)  )  AS netUnits
  FROM production.staging_raw_itunes
  WHERE download_date > '2013-06-01'
    AND netUnits < 0
  "

  try(dbDisconnect(con), silent=TRUE)
  con <- giveMeACon("PostgreSQL")
  DT_any_negative_units <- runQry(Q.check.sr, con)
  if (!nrow(DT_any_negative_units))
    message("No negative units")
  else {
    filesForitunesCheck["positivity"] <- 
    jesus(DT_any_negative_units, dir=dataDir, sub=FALSE, stampFile=TRUE, stampDir=FALSE, summary=FALSE, verbose=FALSE)
    message("THERE *ARE* NON-UNIT UNITES")
    cat("The following rows contain negative units:\n")
    print(DT_any_negative_units)
  }
}
# ----------------------------------------------------------------------------- #