# 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")
library("randomForest")

# ----- 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)
}

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

# Select contextual Ads (OnjectType=3)
# Warning: Takes a few minutes
trainSearchStreamContextual <- fetch(db, "SELECT IsClick, HistCTR, siCategoryID, CategoryID as aiCategoryID, Price, Position,IsUserLoggedOn
      FROM (SELECT IsClick, HistCTR,Position,IsUserLoggedOn,AdID, CategoryID as siCategoryID
            FROM trainSearchStream AS tss
            LEFT OUTER JOIN SearchInfo AS si
            ON tss.SearchID = si.SearchID
            WHERE tss.ObjectType= 3) AS sub
      LEFT OUTER JOIN AdsInfo AS ai
      ON sub.AdID = ai.AdID;", 10 * million)
m <- nrow(trainSearchStreamContextual)

trainSearchStreamContextual$Price = as.numeric(trainSearchStreamContextual$Price)
mean_price = mean(trainSearchStreamContextual$Price, na.rm=T)
trainSearchStreamContextual[is.na(trainSearchStreamContextual$Price), 'Price'] = mean_price
trainSearchStreamContextual[is.na(trainSearchStreamContextual$aiCategoryID), 'aiCategoryID'] = -1
trainSearchStreamContextual$Position = as.factor(trainSearchStreamContextual$Position)
trainSearchStreamContextual$IsUserLoggedOn = as.factor(trainSearchStreamContextual$IsUserLoggedOn)
trainSearchStreamContextual$aiCategoryID = as.factor(trainSearchStreamContextual$aiCategoryID)
trainSearchStreamContextual$siCategoryID = as.factor(trainSearchStreamContextual$siCategoryID)

# Create stratified sample 
sampleSize <- 1 * million
sampleRatio <- sampleSize / m
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 = .9, list = FALSE)

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

rm(trainSearchStreamContextual)

head(trainSearchStreamContextualTrainSample)
str(trainSearchStreamContextualTrainSample)

# Build a logistic regression ...
model <- randomForest(IsClick ~., data = trainSearchStreamContextualTrainSample, 
                                  ntree=40, do.trace=2, replace=F)

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

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

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

testSearchStreamContextual <- fetch(db, "SELECT TestID, HistCTR, siCategoryID, CategoryID as aiCategoryID, Price, Position,IsUserLoggedOn
      FROM (SELECT TestID, HistCTR,Position,IsUserLoggedOn,AdID, CategoryID as siCategoryID
            FROM trainSearchStream AS tss
            LEFT OUTER JOIN SearchInfo AS si
            ON tss.SearchID = si.SearchID
            WHERE tss.ObjectType= 3) AS sub
      LEFT OUTER JOIN AdsInfo AS ai
      ON sub.AdID = ai.AdID;")
      
      
testSearchStreamContextual$Price = as.numeric(testSearchStreamContextual$Price)
testSearchStreamContextual$aiCategoryID = testSearchStreamContextual$aiCategory
testSearchStreamContextual$aiCategory = NULL
testSearchStreamContextual[is.na(testSearchStreamContextual$aiCategory), 'aiCategoryID'] = -1
testSearchStreamContextual[is.na(testSearchStreamContextual$Price), 'Price'] = mean_price
testSearchStreamContextual$Position = as.factor(testSearchStreamContextual$Position)
testSearchStreamContextual$IsUserLoggedOn = as.factor(testSearchStreamContextual$IsUserLoggedOn)
testSearchStreamContextual$aiCategoryID = as.factor(testSearchStreamContextual$aiCategoryID)
testSearchStreamContextual$siCategoryID = as.factor(testSearchStreamContextual$siCategoryID)

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

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

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

dbDisconnect(db)