# Kaggle-competititon "Avito Context Ad Clicks"
# See https://www.kaggle.com/c/avito-context-ad-clicks

# In order to run this script on Kaggle-scripts I had to limit the number of entries to read
# from the database as well as to decrease the sample-size. With the full dataset from the database as well
# as a sample of 20 millions entries

library("data.table")
library("RSQLite")
library("caret")

# ----- Prepare database -------------------------------------------------------

db <- dbConnect(SQLite(), dbname="../input/database.sqlite")
dbListTables(db)

# ----- Utitlies ---------------------------------------------------------------

# Define constants to improve readability of large number
thousand <- 1000
million  <- thousand * thousand 
billion  <- thousand * million

# Runs the query, fetches the given number of entries and returns a
# data.table
fetch  <- function(db, query, n = -1) {
  result <- dbSendQuery(db, query)
  data <- dbFetch(result, n)
  dbClearResult(result)
  return(as.data.table(data))
}

# Loss-function to evaluate result
# See https://www.kaggle.com/c/avito-context-ad-clicks/details/evaluation
logloss <- function(y, yHat){
  
  threshold <- 10^(-15)
  yHat <- pmax(pmin(yHat, 1-threshold), threshold)
  
  loss <- -mean(y*log(yHat) + (1-y)*log(1-yHat))
  
  return(loss)
}

# ----- Machine Learning ------------------------------------------------

# Select contextual Ads (OnjectType=3)
# Warning: Takes a few minutes
trainSearchStreamContextual <- fetch(db, "select tss.HistCTR, tss.Position, si.IsUserLoggedOn, 
si.CategoryID, ai.CategoryID as AdCategoryID, csi.ParentCategoryID, cai.ParentCategoryID as AdParentCategoryID, tss.IsClick
from trainSearchStream tss 
left outer join SearchInfo si on tss.SearchID = si.SearchID 
left outer join AdsInfo ai on tss.AdID = ai.AdID
left outer join Category cai on ai.CategoryID = cai.CategoryID
left outer join Category csi on si.CategoryID = csi.CategoryID
where ObjectType=3", 1 * million)
m <- nrow(trainSearchStreamContextual)

# Add category comparison
trainSearchStreamContextual$IsParentCategorySame <- 0
trainSearchStreamContextual$IsParentCategorySame[trainSearchStreamContextual$ParentCategoryID==trainSearchStreamContextual$AdParentCategoryID] <- 1
head(trainSearchStreamContextual[trainSearchStreamContextual$IsClick==1], 10)
head(trainSearchStreamContextual[trainSearchStreamContextual$IsClick==0], 10)

# Create stratified sample 
sampleSize <- 200 * thousand #100 * million
sampleRatio <- sampleSize / m
str(trainSearchStreamContextual)

set.seed(5)
sampleIndex <- createDataPartition(trainSearchStreamContextual$IsClick, p = sampleRatio, list=FALSE)
trainSearchStreamContextualSample <- trainSearchStreamContextual[as.vector(sampleIndex), ]

# Compare click-ratio in full set and sample to verify stratification
print(paste("Clickratio full dataset:", sum(trainSearchStreamContextual$IsClick)/m))
print(paste("Clickratio sample:", sum(trainSearchStreamContextualSample$IsClick)/sampleSize))

# Create stratified random split ...
trainSampleIndex <- createDataPartition(y = trainSearchStreamContextualSample$IsClick, p = .8, list = FALSE)

# ... and partition data-set into train- and validation-set
trainSearchStreamContextualTrainSample <- trainSearchStreamContextualSample[as.vector(trainSampleIndex),]
trainSearchStreamContextualValidationSample <- trainSearchStreamContextualSample[-as.vector(trainSampleIndex),]

# Build a logistic regression ...
model <- glm(IsClick ~ HistCTR + Position + IsUserLoggedOn + IsParentCategorySame, data = trainSearchStreamContextualTrainSample, family="binomial")

# Check that regression-coefficients have significant impact
summary(model)

# ... and predict data on validation data-set
prediction <- predict(model, trainSearchStreamContextualValidationSample, type="response")
print(logloss(trainSearchStreamContextualValidationSample$IsClick, prediction))

# Predict population
prediction2 <- predict(model, trainSearchStreamContextual, type="response")
print(logloss(trainSearchStreamContextual$IsClick, prediction2))

# ----- Predict submission dataset ---------------------------------------------

testQuery <-
"select tss.TestID, tss.HistCTR, tss.Position, si.IsUserLoggedOn, 
si.CategoryID, ai.CategoryID as AdCategoryID, csi.ParentCategoryID, cai.ParentCategoryID as AdParentCategoryID
from testSearchStream tss 
left outer join SearchInfo si on tss.SearchID = si.SearchID 
left outer join AdsInfo ai on tss.AdID = ai.AdID
left outer join Category cai on ai.CategoryID = cai.CategoryID
left outer join Category csi on si.CategoryID = csi.CategoryID
where ObjectType=3
"
testSearchStreamContextual <- fetch(db, testQuery)
testSearchStreamContextual$IsParentCategorySame <- 0
testSearchStreamContextual$IsParentCategorySame[testSearchStreamContextual$ParentCategoryID==testSearchStreamContextual$AdParentCategoryID] <- 1

prediction <- predict(model, testSearchStreamContextual, type="response")

submissionData <- data.frame(ID=testSearchStreamContextual$TestId, IsClick=prediction)
submissionFile <- paste0("glm", format(Sys.time(), "%Y-%m-%d-%H-%M-%S"), ".csv")
write.csv(submissionData, submissionFile, row.names=FALSE)
m <- nrow(submissionData)
print(head(submissionData, 10))
print(m)

# ----- Clean up ---------------------------------------------------------------

dbDisconnect(db)