setScience("DeNormalizing", subProj="zipcodes")


wh <- "SCIENCE"
dbname <- "prod"
schema <- "zipcodes"

setSnowflake(wh=wh, dbname=dbname)

sfCreateSchema(schema=schema, dbname=dbname, showWarnings=FALSE, comment="small tables to help with zipcode geolocation")

f.florida <- ingest.p("zipcodes", "florida_zipcodes", ext="csv")
stopifnot(file.exists(f.florida))
DT.florida <- fread(f.florida)
cleanColNamesForSQL_(DT.florida)


ingestIntoSQL(DT.florida, schema=schema, dbname=dbname, datetime_type='TIMESTAMP_TZ', drop=TRUE)


-----------------------------------------------

## FUCK -  staging_raw_itunes is not in snowflake



PART 2

setDBall(cluster=4)
zipcodes_in_question <- DT.florida[, unique(zip_code)]
upcs_in_question <- c(884385828806, 827420050527, 827420050329, 184554150326, 827420150227, 884385828608, 827420051029, 827420050725, 827420050626, 884385828707, 827420050428, 184554150623, 884385048761, 184554150425, 885686038819, 887845961976, 884385241810, 884385241919, 884385242015, 884385242114, 884385242213, 884385242510, 884385242619, 884385242718, 884385242817, 884385243012, 842914004930, 842914004947, 884385243111, 884385243210, 884385243319, 884385240318, 884385240417, 884385240516, 801963872020, 884385240912, 884385241018, 884385241117, 884385239817, 884385241216, 884385241315, 884385240011, 842914004923, 884385241414, 884385241513, 884385241612, 884385240110, 884385241711, 754387801124, 827420050121, 827420050220)

cbind(qShowCols(tbl="staging_raw_itunes", schema="production"))

runQry(makeQry(tbl="staging_raw_itunes", schema="production", limit=99, upc=upcs_in_question, `SUBSTRING(postal_code, 1, 5)`=as.character(zipcodes_in_question), country_code='US', colsToPull=c("upc", "download_activity_date", "apple_id", "artist", "country_code", "postal_code")))

DT.sales_in_florida <- runQry(makeQry(tbl="staging_raw_itunes", schema="production", upc=upcs_in_question, , `SUBSTRING(postal_code, 1, 5)`=as.character(zipcodes_in_question), country_code='US', colsToPull=c("upc", "download_activity_date", "apple_id", "artist", "country_code", zipcode="SUBSTRING(postal_code, 1, 5)", anonymous_user_id="customer_identifier"  )))

DT.sales_in_florida[, upc := as.character(upc)]

setkey(DT.sales_in_florida, upc, download_activity_date)

DT.summary_by_upc <- DT.sales_in_florida[, list(first_appeared=min(download_activity_date), last_appeared=max(download_activity_date), total_appearances=.N, different_zipcodes=lunique(zipcode), number_of_users=lunique(anonymous_user_id)), keyby=upc]

f.out <- exportXLS.usingXLConnect(f.out=out.p("florida_upcs_on_itunes", ext="xlsx"),  list(sales_in_florida=DT.sales_in_florida, summary_by_upc=DT.summary_by_upc) )

email(to=getRS(), subject="florida zip coes", attach=f.out)