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

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

# Select contextual Ads (OnjectType=3)
# Warning: Takes a few minutes
trainSearchStreamContextual <- fetch(db, "select tss.HistCTR, tss.Position, si.IsUserLoggedOn, 
                                     si.CategoryID, tss.IsClick 
                                     from trainSearchStream tss 
                                     left join SearchInfo si on tss.SearchID = si.SearchID 
                                     where ObjectType=3", 2 * million)
m <- nrow(trainSearchStreamContextual)

# Create stratified sample 
sampleSize <- 0.01 * 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)

trainSearchStreamContextualTrainSample$IsClick <- factor(trainSearchStreamContextualTrainSample$IsClick)
trainSearchStreamContextualTrainSample$Position <- factor(trainSearchStreamContextualTrainSample$Position)
trainSearchStreamContextualTrainSample$IsUserLoggedOn <- factor(trainSearchStreamContextualTrainSample$IsUserLoggedOn)
#trainSearchStreamContextualTrainSample$LocationID <- factor(trainSearchStreamContextualTrainSample$LocationID)
trainSearchStreamContextualTrainSample$CategoryID <- factor(trainSearchStreamContextualTrainSample$CategoryID)

head(trainSearchStreamContextualTrainSample)
str(trainSearchStreamContextualTrainSample)

# Build a logistic regression ...
fitControl <- trainControl(method = "repeatedcv", number = 2, repeats = 2, classProbs=TRUE)
model <- train(IsClick ~., data = trainSearchStreamContextualTrainSample, method="svmRadial", trControl = fitControl, tuneLength = 8)
model
# Check that regression-coefficients have significant impact
summary(model)

trainSearchStreamContextualValidationSample$IsClick <- factor(trainSearchStreamContextualValidationSample$IsClick)
trainSearchStreamContextualValidationSample$Position <- factor(trainSearchStreamContextualValidationSample$Position)
trainSearchStreamContextualValidationSample$IsUserLoggedOn <- factor(trainSearchStreamContextualValidationSample$IsUserLoggedOn)
#trainSearchStreamContextualTrainSample$LocationID <- factor(trainSearchStreamContextualTrainSample$LocationID)
trainSearchStreamContextualValidationSample$CategoryID <- factor(trainSearchStreamContextualValidationSample$CategoryID)

# ... and predict data on validation data-set
prediction <- predict(model, trainSearchStreamContextualValidationSample)
head(prediction)
prediction <- as.numeric(prediction[,2)
trainSearchStreamContextualValidationSample$IsClick <- as.numeric(trainSearchStreamContextualValidationSample$IsClick)
print(logloss(trainSearchStreamContextualValidationSample$IsClick, prediction))

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

testSearchStreamContextual <- fetch(db, "select tss.TestId, tss.HistCTR, tss.Position, si.IsUserLoggedOn, 
                                    si.CategoryID
                                    from testSearchStream tss 
                                    left join SearchInfo si on tss.SearchID = si.SearchID 
                                    where ObjectType=3")
testSearchStreamContextual$Position <- factor(testSearchStreamContextual$Position)
testSearchStreamContextual$IsUserLoggedOn <- factor(testSearchStreamContextual$IsUserLoggedOn)
#testSearchStreamContextual$LocationID <- factor(testSearchStreamContextual$LocationID)
testSearchStreamContextual$CategoryID <- factor(testSearchStreamContextual$CategoryID)
head(testSearchStreamContextual)
str(testSearchStreamContextual)
prediction <- predict(model, testSearchStreamContextual, type="prob")

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

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

dbDisconnect(db)