/*
Copyright (c) 2006-2009, Tom Thielicke IT Solutions

SPDX-License-Identifier: GPL-2.0-only
*/

/****************************************************************
**
** Implementation of the StartSql class
** File name: startsql.cpp
**
****************************************************************/

#include <QApplication>
#include <QChar>
#include <QDateTime>
#include <QEventLoop>
#include <QHash>
#include <QHashIterator>
#include <QIcon>
#include <QListWidgetItem>
#include <QSqlQuery>
#include <QVariant>

#include "startsql.h"

// Konstruktor
StartSql::StartSql() { }

int StartSql::fillLessonList(QListWidget* listLesson,
    QList<QString>* arrayTraining, QString languageLesson)
{
    // Clear the list first because this function is called not only once in
    // the contructor but also after a successful online update
    listLesson->clear();
    arrayTraining->clear();
    // Fill lesson list from SQLite lesson table
    QSqlQuery query;
    QIcon lessonIcon;
    int lessonCounter = 0;
    // SQL: all lessons sorted by id and a left joint to the number of
    // lessons done by the user
    QString sqlString
        = "SELECT lesson_list.lesson_id, "
          "lesson_list.lesson_name, lesson_list.lesson_description, "
          "COUNT(user_lesson_list.user_lesson_lesson) FROM lesson_list "
          "LEFT JOIN user_lesson_list ON lesson_list.lesson_id = "
          "user_lesson_list.user_lesson_lesson AND "
          "user_lesson_list.user_lesson_type = 0 "
          "WHERE lesson_list.lesson_language = '"
        + languageLesson + "' ";
    sqlString = sqlString.append("GROUP BY lesson_list.lesson_id;");
    if (!query.exec(sqlString)) {
        return -1;
    }
    // Read all datasets to list items
    while (query.next()) {
        // ID of the lesson
        QString lessonId = query.value(0).toString();
        // Name of the lesson
        QString lessonName = query.value(1).toString();
        // Maybe a additional description
        QString lessonDescription = query.value(2).toString();
        // Maybe a additional description -> show it in brackets
        if (query.value(2).toString() != "") {
            lessonName.append(" (" + lessonDescription + ")");
        }
        // Show different icons depending on number of lessons done
        if (query.value(3).toInt() > 2) {
            // Lesson done multiple
            lessonIcon = QIcon(":/img/lesson_done_three.png");
        } else {
            if (query.value(3).toInt() == 2) {
                // Lesson done multiple
                lessonIcon = QIcon(":/img/lesson_done_two.png");
            } else {
                if (query.value(3).toInt() == 1) {
                    // Lesson done once
                    lessonIcon = QIcon(":/img/lesson_done_one.png");
                } else {
                    // Lesson never done
                    lessonIcon = QIcon(":/img/lesson_done_none.png");
                }
            }
        }
        // Add Item to the list
        auto* currentItem
            = new QListWidgetItem(lessonIcon, lessonName, listLesson);
        currentItem->setToolTip(lessonDescription);
        arrayTraining->append(lessonId);
        lessonCounter++;
    }
    return lessonCounter;
}

// TODO: decide what to do with unused variable languageLesson
int StartSql::fillOpenList(QListWidget* listOpen, QList<QString>* arrayOpen,
    QString themeId, [[maybe_unused]] QString languageLesson)
{
    // Clear the list first because this function is called not only once in
    // the contructor but also after a successful online update
    listOpen->clear();
    arrayOpen->clear();
    // Fill lesson list from SQLite lesson table
    QSqlQuery query;
    QIcon openIcon;
    int lessonCounter = 0;
    QString themeAll = "WHERE open_list.open_theme = " + themeId + " ";
    if (themeId == "0" || themeId == "") {
        themeAll = "";
    }
    // SQL: all lessons sorted by id and a left joint to the number of
    // lessons done by the user
    if (!query.exec("SELECT open_list.open_id, open_list.open_name, "
                    "open_list.open_description, "
                    "COUNT(user_lesson_list.user_lesson_lesson) FROM open_list "
                    "LEFT JOIN user_lesson_list ON open_list.open_id = "
                    "user_lesson_list.user_lesson_lesson AND "
                    "user_lesson_list.user_lesson_type = 1 "
            + themeAll + "GROUP BY open_list.open_name;")) {
        return -1;
    }
    // Read all datasets to list items
    while (query.next()) {
        // ID of the lesson
        QString lessonId = query.value(0).toString();
        // Name of the lesson
        QString lessonName = query.value(1).toString();
        // Maybe a additional description
        QString lessonDescription = query.value(2).toString();
        // Show different icons depending on number of lessons done
        if (query.value(3).toInt() > 2) {
            // Lesson done multiple
            openIcon = QIcon(":/img/lesson_done_three.png");
        } else {
            if (query.value(3).toInt() == 2) {
                // Lesson done multiple
                openIcon = QIcon(":/img/lesson_done_two.png");
            } else {
                if (query.value(3).toInt() == 1) {
                    // Lesson done once
                    openIcon = QIcon(":/img/lesson_done_one.png");
                } else {
                    // Lesson never done
                    openIcon = QIcon(":/img/lesson_done_none.png");
                }
            }
        }
        // Add Item to the list
        auto* currentItem = new QListWidgetItem(openIcon, lessonName, listOpen);
        currentItem->setToolTip(lessonDescription);
        arrayOpen->append(lessonId);
        lessonCounter++;
    }
    return lessonCounter;
}

int StartSql::fillOwnList(QListWidget* listOwn, QList<QString>* arrayOwn)
{
    // Clear the list first because this function is called not only once in
    // the contructor but also after a successful online update
    listOwn->clear();
    arrayOwn->clear();
    // Fill lesson list from SQLite lesson table
    QSqlQuery query;
    QIcon ownIcon;
    int lessonCounter = 0;
    // SQL: all lessons sorted by id and a left joint to the number of
    // lessons done by the user
    if (!query.exec("SELECT own_list.own_id, own_list.own_name, "
                    "own_list.own_description, "
                    "COUNT(user_lesson_list.user_lesson_lesson) FROM own_list "
                    "LEFT JOIN user_lesson_list ON own_list.own_id = "
                    "user_lesson_list.user_lesson_lesson AND "
                    "user_lesson_list.user_lesson_type = 2 "
                    "GROUP BY own_list.own_name;")) {
        return -1;
    }
    // Read all datasets to list items
    while (query.next()) {
        // ID of the lesson
        QString lessonId = query.value(0).toString();
        // Name of the lesson
        QString lessonName = query.value(1).toString();
        // Maybe a additional description
        QString lessonDescription = query.value(2).toString();
        // Show different icons depending on number of lessons done
        if (query.value(3).toInt() > 2) {
            // Lesson done multiple
            ownIcon = QIcon(":/img/lesson_done_three.png");
        } else {
            if (query.value(3).toInt() == 2) {
                // Lesson done multiple
                ownIcon = QIcon(":/img/lesson_done_two.png");
            } else {
                if (query.value(3).toInt() == 1) {
                    // Lesson done once
                    ownIcon = QIcon(":/img/lesson_done_one.png");
                } else {
                    // Lesson never done
                    ownIcon = QIcon(":/img/lesson_done_none.png");
                }
            }
        }
        // Add Item to the list
        auto* currentItem = new QListWidgetItem(ownIcon, lessonName, listOwn);
        currentItem->setToolTip(lessonDescription);
        arrayOwn->append(lessonId);
        lessonCounter++;
    }
    return lessonCounter;
}

// TODO: decide what to do with unused variable languageLesson
int StartSql::fillThemes(QComboBox* comboTheme, QList<QString>* arrayThemes,
    [[maybe_unused]] QString languageLesson, QString textAll)
{
    // Clear the list first because this function is called not only once in
    // the contructor but also after a successful online update
    comboTheme->clear();
    arrayThemes->clear();
    // Fill lesson list from SQLite lesson table
    QSqlQuery query;
    QString themeName;
    QString themeId;
    int themeCounter = 1;
    // SQL: all lessons sorted by id and a left joint to the number of
    // lessons done by the user
    if (!query.exec("SELECT open_themes.theme_id, open_themes.theme_name, "
                    "open_themes.theme_description FROM open_themes "
                    "GROUP BY open_themes.theme_id;")) {
        return -1;
    }
    comboTheme->insertItem(0, textAll);
    arrayThemes->append(nullptr);
    // Read all datasets to list items
    while (query.next()) {
        // ID of the lesson
        themeId = query.value(0).toString();
        // Name of the lesson
        themeName = query.value(1).toString();
        // Add Item to the list
        comboTheme->insertItem(themeCounter, themeName);
        arrayThemes->append(themeId);
        themeCounter++;
    }
    return themeCounter;
}

bool StartSql::deleteUserLessonList()
{
    QSqlQuery query;
    return query.exec("DELETE FROM user_lesson_list;");
}

bool StartSql::deleteUserChars()
{
    QSqlQuery query;
    return query.exec("DELETE FROM user_chars;");
}

bool StartSql::deleteOwnLesson(QString lessonnumber)
{
    QSqlQuery query;
    if (!query.exec("DELETE FROM user_lesson_list WHERE user_lesson_lesson = "
            + lessonnumber + " AND user_lesson_type = 2;")) {
        return false;
    }
    if (!query.exec("DELETE FROM own_content WHERE content_lesson = "
            + lessonnumber + ";")) {
        return false;
    }
    if (!query.exec(
            "DELETE FROM own_list WHERE own_id = " + lessonnumber + ";")) {
        return false;
    }
    return true;
}

bool StartSql::updateOwnLesson(QString lessonnumber, QString lessonname,
    QString description, QStringList content, int unit)
{
    QVariant lastLessonId;
    QSqlQuery query;
    QString lessonid = "0";
    QString simplifiedContent = "";

    if (content.empty())
        return true;

    if (lessonnumber != "-1" && lessonnumber != "-2") {
        // Update existing lesson
        // ----------------------
        // First, delete all content of the lesson
        if (!query.exec("DELETE FROM own_content WHERE content_lesson = "
                + lessonnumber + ";")) {
            return false;
        }
        lessonid = lessonnumber;
        // Insert content name in the database
        if (!query.exec("UPDATE own_list SET own_name = '"
                + lessonname.replace(QChar(0x27), "''", Qt::CaseSensitive)
                + "', "
                  "own_description = '"
                + description.replace(QChar(0x27), "''", Qt::CaseSensitive)
                + "', "
                  "own_unit = "
                + QString::number(unit) + " WHERE own_id = " + lessonnumber
                + ";")) {
            return false;
        }
    } else {
        // Create new lesson
        // -----------------
        // Insert content name in the database
        if (!query.exec("INSERT INTO own_list VALUES(NULL,'"
                + lessonname.replace(QChar(0x27), "''", Qt::CaseSensitive)
                + "','"
                + description.replace(QChar(0x27), "''", Qt::CaseSensitive)
                + "', " + QString::number(unit) + ");")) {
            return false;
        }
        lastLessonId = query.lastInsertId();
        lessonid = QString::number(lastLessonId.toInt());
    }
    // Write every line of lesson content to database
    for (int i = 0; i < content.size(); i++) {
        // simplifiedContent = QString::QString(
        //	content.at(i)).replace(QChar(0x27), "''",
        // Qt::CaseSensitive).simplified();
        simplifiedContent = QString(content.at(i))
                                .replace(QChar(0x27), "''", Qt::CaseSensitive)
                                .trimmed();

        if (!query.exec("INSERT INTO own_content VALUES(NULL,'"
                + simplifiedContent + "'," + lessonid + ");")) {
            return false;
        }
    }

    return true;
}

bool StartSql::ownLessonExist(QString lessonname)
{
    QSqlQuery query;
    if (!query.exec("SELECT own_name "
                    "FROM own_list "
                    "WHERE own_name = '"
            + lessonname + "';")) {
        return false;
    }
    return query.first();
}

bool StartSql::getOwnLesson(QString lessonnumber, QLineEdit* lineLessonName,
    QLineEdit* lineLessonDescription, QTextEdit* lineLessonContent,
    QRadioButton* radioUnitSentence, QRadioButton* radioUnitWord)
{

    QSqlQuery query;
    QString lessonName = "";
    QString lessonDescription = "";
    QString lessonContent = "";
    int lessonUnit = 0;

    if (!query.exec("SELECT own_name, own_description, own_unit "
                    "FROM own_list "
                    "WHERE own_id = "
            + lessonnumber + ";")) {
        return false;
    }
    if (query.first()) {
        lessonName = query.value(0).toString();
        lessonDescription = query.value(1).toString();
        lessonUnit = query.value(2).toInt();
    }

    if (!query.exec("SELECT content_text "
                    "FROM own_content "
                    "WHERE content_lesson = "
            + lessonnumber
            + " "
              "ORDER BY content_id;")) {
        return false;
    }
    // Read all datasets to list items
    while (query.next()) {
        // ID of the lesson
        lessonContent.append(query.value(0).toString() + "\n");
    }

    // Fill out text lines
    lineLessonName->setText(lessonName);
    lineLessonDescription->setText(lessonDescription);
    lineLessonContent->setText(lessonContent);
    if (lessonUnit == 0) {
        radioUnitSentence->setChecked(true);
        radioUnitWord->setChecked(false);
    } else {
        radioUnitSentence->setChecked(false);
        radioUnitWord->setChecked(true);
    }
    return true;
}

bool StartSql::analyzeOwnLessons() { return analyzeLessons("own"); }

bool StartSql::analyzeLessons(QString lessonType)
{
    QSqlQuery mainQuery;
    QSqlQuery subQuery;
    QHash<int, int> unicodeErrorHash;
    QHash<int, int> unicodeErrorHashClean;

    if (!mainQuery.exec("SELECT char_unicode FROM lesson_chars;")) {
        // Error message
        return false;
    }

    while (mainQuery.next()) {
        unicodeErrorHashClean[mainQuery.value(0).toInt()] = 0;
    }

    // 1. Delete current lessonanalysis table
    // (no error message, table could not exist)
    mainQuery.exec("DROP TABLE " + lessonType + "_analysis;");

    // 2a. Create new analysis table query with new error definitions as columns
    QString queryString = "CREATE TABLE " + lessonType + "_analysis "
        + "(analysis_content INTEGER primary key";
    QString indexString = "CREATE INDEX " + lessonType + "_analysis_index ON "
        + lessonType + "_analysis (analysis_content";

    QHashIterator<int, int> hashIteratorClean(unicodeErrorHashClean);
    while (hashIteratorClean.hasNext()) {
        hashIteratorClean.next();
        queryString += ", analysis_char_"
            + QString::number(hashIteratorClean.key()) + " INTEGER";
        indexString
            += ", analysis_char_" + QString::number(hashIteratorClean.key());
        // cout << i.key() << ": " << i.value() << endl;
    }

    queryString += ");";
    indexString += ");";

    // 2b. Execute new analysis table query
    if (!mainQuery.exec(queryString)) {
        return false;
    }

    // 2c. Execute new analysis table query
    if (!mainQuery.exec(indexString)) {
        return false;
    }

    // 3. Count number of lesson text
    if (!mainQuery.exec(
            "SELECT COUNT(content_id) FROM " + lessonType + "_content;")) {
        return false;
    }
    mainQuery.first();
    mainQuery.value(0).toInt();

    // 4. Query all lesson text and analyze it
    if (!mainQuery.exec("SELECT content_id, content_text FROM " + lessonType
            + "_content ORDER BY content_id;")) {
        return false;
    }

    while (mainQuery.next()) {
        unicodeErrorHash = unicodeErrorHashClean;
        QString lessonString = mainQuery.value(1).toString();
        auto lessonLen = static_cast<double>(lessonString.length());

        // Now count all error chars and put them into the hash table
        for (const auto& character : lessonString) {
            if (unicodeErrorHash.contains(character.unicode())) {
                unicodeErrorHash[character.unicode()]++;
            }
        }

        queryString = "INSERT INTO " + lessonType + "_analysis VALUES(";
        queryString += mainQuery.value(0).toString();
        QHashIterator<int, int> hashIterator(unicodeErrorHash);
        // hashIterator.toFront();
        while (hashIterator.hasNext()) {
            hashIterator.next();
            // Weighted number of chars (char ratio) to SQL string
            queryString += ", "
                + QString::number(
                    (static_cast<double>(hashIterator.value()) / lessonLen)
                        * 100.0,
                    'f', 2);
        }
        queryString += ");";

        // Execute new analysis table query
        if (!subQuery.exec(queryString)) {
            return false;
        }
    }

    return true;
}

void StartSql::fillLanguage(QComboBox* combo, QString table, QString field)
{

    combo->clear();

    QSqlQuery query;
    if (!query.exec("SELECT " + table + "." + field
            + "_key, "
              ""
            + table + "." + field
            + "_label "
              "FROM "
            + table
            + " "
              "GROUP BY "
            + table + "." + field + "_id;")) {
        return;
    }

    int counter = 0;
    while (query.next()) {
        combo->insertItem(counter, query.value(1).toString(),
            QVariant(query.value(0).toString()));
        counter++;
    }
}