#include "databaseerasmusapp.h"

DatabaseErasmusApp::DatabaseErasmusApp( QObject *parent ) : QObject( parent )
{
  openDatabase();
}

DatabaseErasmusApp::~DatabaseErasmusApp()
{
  closeDatabase();
}

void DatabaseErasmusApp::openDatabase( void )
{
  db = QSqlDatabase::addDatabase( "QSQLITE" );
  db.setDatabaseName( "./databaseErasmusApp" );
  if( !db.open() ) qDebug() << "DB Verbingung könnte nicht geöffnet werden!";
}

void DatabaseErasmusApp::closeDatabase( void )
{
  db.close();
}

//----------------------------------------------------------
//Settings
void DatabaseErasmusApp::setCurrentCountryCode( QString countryCode )
{
  QSqlQuery query( "UPDATE settings SET current_country_code = '" + countryCode + "'" );
}

QString DatabaseErasmusApp::getCurrentCountryCode( void )
{
  QSqlQuery query( "SELECT current_country_code FROM settings" );
  query.first();
  return query.value( 0 ).toString();
}

void DatabaseErasmusApp::setCurrentLanguageId( int langId )
{
  QSqlQuery query( "UPDATE settings SET current_lang_id = " + QString::number( langId ) );
}

int DatabaseErasmusApp::getCurrentLanguageId( void )
{
  QSqlQuery query( "SELECT current_lang_id FROM settings" );
  query.first();
  return query.value( 0 ).toInt();
}

//QuizSettings
void DatabaseErasmusApp::setCurrentCategoryIds( QList< int > categoryIds )
{
  QSqlQuery deleteIds( "DELETE FROM quiz_settings_current_category_id" );
  for( int i = 0; i < categoryIds.length(); i++ )
  {
    QSqlQuery query;
    query.prepare( "INSERT INTO quiz_settings_current_category_id VALUES"
                   "( :current_category_id )" );
    query.bindValue( ":current_category_id", categoryIds[ i ] );
    query.exec();
  }
}

QList< int > DatabaseErasmusApp::getCurrentCategoryIds( void )
{
  QSqlQuery query( "SELECT current_category_id FROM quiz_settings_current_category_id" );
  QList< int > currentCategoryIds;
  while( query.next() )
  {
    currentCategoryIds << query.value( 0 ).toInt();
  }
  return currentCategoryIds;
}

void DatabaseErasmusApp::setNumberOfQuestions( int qNumber )
{
  QSqlQuery query( "UPDATE quiz_settings SET question_number = " + QString::number( qNumber ) );
}

int DatabaseErasmusApp::getNumberOfQuestions( void )
{
  QSqlQuery query( "SELECT question_number FROM quiz_settings" );
  query.first();
  return query.value( 0 ).toInt();
}

void DatabaseErasmusApp::setTimePerQuestion( int time )
{
  QSqlQuery query( "UPDATE quiz_settings SET time_per_question = " + QString::number( time ) );
}

int DatabaseErasmusApp::getTimePerQuestion( void )
{
  QSqlQuery query( "SELECT time_per_question FROM quiz_settings" );
  query.first();
  return query.value( 0 ).toInt();
}

//Category
QList< int > DatabaseErasmusApp::getAllCategoryIds( void )
{
  QList< int > categoryIds;
  QSqlQuery query( "SELECT category_id"
                   "    FROM category"
                   "    ORDER BY category_id ASC" );
  while( query.next() )
  {
    categoryIds << query.value( 0 ).toInt();
  }
  return categoryIds;
}

int DatabaseErasmusApp::getCategoryIdForQuestionId( int questionId )
{
  QSqlQuery query( "SELECT category_id FROM question WHERE question_id = " + QString::number( questionId ) );
  query.first();
  return query.value( 0 ).toInt();
}

QString DatabaseErasmusApp::getCategoryNameAt( int categoryId )
{
  QSqlQuery query( "SELECT category_text"
                   "    FROM category_lang"
                   "    WHERE lang_id = " + QString::number( getCurrentLanguageId() ) +
                   "        AND category_id = " + QString::number( categoryId ) );
  query.first();
  return query.value( 0 ).toString();
}

QList< QString > DatabaseErasmusApp::getCategoryNamesAt( int categoryId )
{
  QSqlQuery query( "SELECT category_text"
                   "    FROM category_lang"
                   "        WHERE category_id = " + QString::number( categoryId ) );
  QList< QString > categoryNames;
  while( query.next() )
    categoryNames << query.value( 0 ).toString();
  return categoryNames;
}

QString DatabaseErasmusApp::getCategoryNameForQuestionId( int questionId )
{
  QSqlQuery query( "SELECT category_id"
                   "    FROM question"
                   "    WHERE question_id = " + QString::number( questionId ) );
  query.first();
  return getCategoryNameAt( query.value( 0 ).toInt() );
}

int DatabaseErasmusApp::addCategory( void )
{
  QSqlQuery query;
  query.prepare( "INSERT INTO category( category_id ) "
                 "VALUES( :category_id )" );
  int categoryId =  getFreeCategoryId();
  query.bindValue( ":category_id", categoryId );
  query.exec();
  return categoryId;
}

void DatabaseErasmusApp::deleteCategory( int categoryId )
{
  QSqlQuery deleteCategory( "DELETE FROM category WHERE category_id = " + QString::number( categoryId ) );
  QSqlQuery deleteCategoryLang( "DELETE FROM category_lang WHERE category_id = " + QString::number( categoryId ) );
}

int DatabaseErasmusApp::getFreeCategoryId( void )
{
  return getFreeId( getAllCategoryIds() );
}

int DatabaseErasmusApp::getMaxCategoryId( void )
{
  QSqlQuery query( "SELECT MAX( category_id ) FROM category" );
  query.first();
  return query.value( 0 ).toInt();
}

void DatabaseErasmusApp::setCategoryText( int langId, int categoryId, QString categoryText )
{
  QSqlQuery query;
  query.prepare( "INSERT INTO category_lang( category_id, lang_id, category_text ) "
                 "VALUES( :category_id, :lang_id, :category_text )" );
  query.bindValue( ":category_id", categoryId );
  query.bindValue( ":lang_id", langId );
  query.bindValue( ":category_text", categoryText );
  query.exec();
}

//Question
QList< int > DatabaseErasmusApp::getAllQuestionIds( void )
{
  QList< int > questionIds;
  QSqlQuery query( "SELECT question_id"
                   "    FROM question"
                   "    ORDER BY question_id ASC" );
  while( query.next() )
  {
    questionIds << query.value( 0 ).toInt();
  }
  return questionIds;
}

QList< int > DatabaseErasmusApp::getQuestionIdsToCategoryIds( QList< int > categoryIds )
{
  QList< int > questionIds;
  for( int i = 0; i < categoryIds.length(); i++ )
  {
    QSqlQuery query( "SELECT question_id FROM question WHERE category_id = " + QString::number( categoryIds[ i ] ) );
    while( query.next() )
    {
      questionIds << query.value( 0 ).toInt();
    }
  }
  return questionIds;
}

QString DatabaseErasmusApp::getQuestionTextAt( int questionId )
{
  QSqlQuery query( "SELECT question_text"
                   "    FROM question_lang"
                   "    WHERE lang_id = " + QString::number( getCurrentLanguageId() ) +
                   "        AND question_id = " + QString::number( questionId ) );
  query.first();
  return query.value( 0 ).toString();
}

QList< QString > DatabaseErasmusApp::getQuestionTextsAt( int questionId )
{
  QSqlQuery query( "SELECT question_text"
                   "    FROM question_lang"
                   "        WHERE question_id = " + QString::number( questionId ) );
  QList< QString > questionTexts;
  while( query.next() )
    questionTexts << query.value( 0 ).toString();
  return questionTexts;
}

int DatabaseErasmusApp::addQuestion( int categoryId )
{
  QSqlQuery query;
  query.prepare( "INSERT INTO question( question_id, category_id ) "
                 "VALUES( :question_id , :category_id )" );
  int questionId =  getFreeQuestionId();
  query.bindValue( ":question_id", questionId );
  query.bindValue( ":category_id", categoryId );
  query.exec();
  return questionId;
}

void DatabaseErasmusApp::deleteQuestion( int questionId )
{
  QSqlQuery deleteQuestion( "DELETE FROM question WHERE question_id = " + QString::number( questionId ) );
  QSqlQuery deleteQuestionLang( "DELETE FROM question_lang WHERE question_id = " + QString::number( questionId ) );
  deleteQuestionAnswer( questionId );
}

int DatabaseErasmusApp::getFreeQuestionId( void )
{
  return getFreeId( getAllQuestionIds() );
}

int DatabaseErasmusApp::getMaxQuestionId( void )
{
  QSqlQuery query( "SELECT MAX( question_id ) FROM question" );
  query.first();
  return query.value( 0 ).toInt();
}

void DatabaseErasmusApp::setQuestionText( int langId, int questionId, QString questionText )
{
  QSqlQuery query;
  query.prepare( "INSERT INTO question_lang( question_id, lang_id, question_text ) "
                 "VALUES( :question_id, :lang_id, :question_text )" );
  query.bindValue( ":question_id", questionId );
  query.bindValue( ":lang_id", langId );
  query.bindValue( ":question_text", questionText );
  query.exec();
}

//Answer
QList<int> DatabaseErasmusApp::getAllAnswerIds( void )
{
  QList< int > answerIds;
  QSqlQuery query( "SELECT answer_id"
                   "    FROM answer"
                   "    ORDER BY answer_id ASC" );
  while( query.next() )
  {
    answerIds << query.value( 0 ).toInt();
  }
  return answerIds;
}

QList< QString > DatabaseErasmusApp::getTrueAnswerTextsToQuestionId( int questionId )
{
  QList< QString > trueAnswerTexts;
  QSqlQuery query( "SELECT answer_text"
                   "    FROM answer_lang, question_answer"
                   "    WHERE lang_id = " + QString::number( getCurrentLanguageId() ) +
                   "        AND question_id = " + QString::number( questionId ) +
                   "        AND is_True = 'true'"
                   "        AND answer_lang.answer_id = question_answer.answer_id");
  while( query.next() )
  {
    trueAnswerTexts << query.value( 0 ).toString();
  }
  return trueAnswerTexts;
}

QList< int > DatabaseErasmusApp::getTrueAnswerIdsToQuestionId( int questionId )
{
  QList< int > trueAnswerIds;
  QSqlQuery query( "SELECT answer_id"
                   "    FROM question_answer"
                   "    WHERE question_id = " + QString::number( questionId ) +
                   "        AND is_True = 'true'");
  while( query.next() )
  {
    trueAnswerIds << query.value( 0 ).toInt();
  }
  return trueAnswerIds;
}

QList< QString > DatabaseErasmusApp::getFalseAnswerTextsToQuestionId( int questionId )
{
  QList< QString > falseAnswerTexts;
  QSqlQuery query( "SELECT answer_text"
                   "    FROM answer_lang, question_answer"
                   "    WHERE lang_id = " + QString::number( getCurrentLanguageId() ) +
                   "        AND question_id = " + QString::number( questionId ) +
                   "        AND is_True = 'false'"
                   "        AND answer_lang.answer_id = question_answer.answer_id" );
  while( query.next() )
  {
    falseAnswerTexts << query.value( 0 ).toString();
  }
  return falseAnswerTexts;
}

QList< int > DatabaseErasmusApp::getFalseAnswerIdsToQuestionId( int questionId )
{
  QList< int > falseAnswerIds;
  QSqlQuery query( "SELECT answer_id"
                   "    FROM question_answer"
                   "    WHERE question_id = " + QString::number( questionId ) +
                   "        AND is_True = 'false'");
  while( query.next() )
  {
    falseAnswerIds << query.value( 0 ).toInt();
  }
  return falseAnswerIds;
}

QList< QString > DatabaseErasmusApp::getAnswerTextsAt( int answerId )
{
  QList< QString > answerTexts;
  QSqlQuery query( "SELECT answer_text"
                   "    FROM answer_lang"
                   "        WHERE answer_id = " + QString::number( answerId ) );
  while( query.next() )
    answerTexts << query.value( 0 ).toString();
  qDebug() << answerTexts;
  return answerTexts;
}

QString DatabaseErasmusApp::getExplanationTextToAnswerId( int answerId )
{
  QSqlQuery query( "SELECT explanation_text"
                   "    FROM answer_lang"
                   "    WHERE lang_id = " + QString::number( getCurrentLanguageId() ) +
                   "        AND answer_id = " + QString::number( answerId ) );
  query.first();
  return query.value( 0 ).toString();
}

QList< QString > DatabaseErasmusApp::getExplanationTextsToAnswerId( int answerId )
{
  QList< QString > explanationTexts;
  QSqlQuery query( "SELECT explanation_text"
                   "    FROM answer_lang"
                   "        WHERE answer_id = " + QString::number( answerId ) );
  while( query.next() )
    explanationTexts << query.value( 0 ).toString();
  return explanationTexts;
}

int DatabaseErasmusApp::addAnswer( void )
{
  QSqlQuery query;
  query.prepare( "INSERT INTO answer( answer_id ) "
                 "VALUES( :answer_id )" );
  int answerId = getFreeAnswerId();
  query.bindValue( ":answer_id", answerId );
  query.exec();
  return answerId;
}

void DatabaseErasmusApp::deleteAnswer( int answerId )
{
  QSqlQuery deleteAnswer( "DELETE FROM answer WHERE answer_id = " + QString::number( answerId ) );
  QSqlQuery deleteAnswerLang( "DELETE FROM answer_lang WHERE answer_id = " + QString::number( answerId ) );

}

int DatabaseErasmusApp::getFreeAnswerId( void )
{
  return getFreeId( getAllAnswerIds() );
}

int DatabaseErasmusApp::getMaxAnswerId( void )
{
  QSqlQuery query( "SELECT MAX( answer_id ) FROM answer" );
  query.first();
  return query.value( 0 ).toInt();
}

void DatabaseErasmusApp::setAnswerText( int langId, int answerId, QString answerText, QString explanationText )
{
  QSqlQuery query;
  query.prepare( "INSERT INTO answer_lang( answer_id, lang_id, answer_text, explanation_text ) "
                 "VALUES( :answer_id, :lang_id, :answer_text, :explanation_text )" );
  query.bindValue( ":answer_id", answerId );
  query.bindValue( ":lang_id", langId );
  query.bindValue( ":answer_text", answerText );
  query.bindValue( ":explanation_text", explanationText );
  query.exec();
}

void DatabaseErasmusApp::setQuestionAnswer( int answerId, int questionId, bool isAnswerTrue )
{
  QSqlQuery query;
  query.prepare( "INSERT INTO question_answer( answer_id, question_id, is_True ) "
                 "VALUES( :answer_id, :question_id, :is_True )" );
  query.bindValue( ":answer_id", answerId );
  query.bindValue( ":question_id", questionId );
  query.bindValue( ":is_True", isAnswerTrue ? "true" : "false" );
  query.exec();
}

void DatabaseErasmusApp::deleteQuestionAnswer( int questionId )
{
  QSqlQuery query( "DELETE FROM question_answer WHERE question_id = " + QString::number( questionId ) );
}

int DatabaseErasmusApp::getFreeId( QList< int > idList )
{
  QList< int > ids = idList;
  for( int i = 0; i < ids.length(); i++ )
    if( ids[ i ] != i + 1)
      return i + 1;
  return ids.length() + 1;
}
