//
// Syntilista - velkalistasovellus Kampus-kahvilaan
// Programmed and designed by Matti Hämäläinen <ccr@tnsp.org>
// (C) Copyright 2017 Tecnic Software productions (TNSP)
//
#include <QApplication>
#include <QMessageBox>
#include <QSettings>
#include "main.h"
#include "ui_mainwindow.h"
#include "ui_editperson.h"

static QString query_Person =
    "SELECT id,last_name,first_name,"
    "(SELECT SUM(value) FROM transactions WHERE transactions.person=people.id) AS balance,"
    "updated FROM people";



void appError(QString title, QString msg)
{
    QMessageBox::critical(0, title, msg, QMessageBox::Ok);
    QApplication::exit();
}


double moneyStrToValue(const QString &str)
{
    QString str2 = str;
    return str2.replace(",", ".").toDouble();
}


QString moneyValueToStr(double val)
{
    return QStringLiteral("%1").arg(val, 1, 'f', 2);
}


QString cleanupStr(const QString &str)
{
    return str.simplified().trimmed();
}


const QString dateTimeToStr(const QDateTime &val)
{
    QDateTime tmp = val;
    tmp.setOffsetFromUtc(0);
    return tmp.toLocalTime().toString(QStringLiteral("yyyy-MM-dd hh:mm"));
}


bool checkAndReportSQLError(const QString where, const QSqlError &err)
{
    if (err.isValid())
    {
        printf("SQL Error in %s: %s\n",
            where.toUtf8().constData(),
            err.text().toUtf8().constData());
        return true;
    }
    else
        return false;
}


int main(int argc, char *argv[])
{
    QApplication sapp(argc, argv);

    //
    // Initialize / open SQL database connection
    //
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(qApp->applicationDirPath() + QDir::separator() + "syntilista.sqlite3");

    if (!db.open())
    {
        appError(
            "Cannot open database",
            "Unable to establish a database connection.\n"
            "This example needs SQLite support. Please read "
            "the Qt SQL driver documentation for information how "
            "to build it."
            );
        return false;
    }

    QSqlQuery query;
    query.exec(
        "CREATE TABLE people (id INTEGER PRIMARY KEY, "
        "first_name VARCHAR(128) NOT NULL, "
        "last_name VARCHAR(128) NOT NULL, "
        "extra_info VARCHAR(2048), "
        "added DATETIME NOT NULL, "
        "updated DATETIME NOT NULL)");

    checkAndReportSQLError("CREATE TABLE people", query.lastError());

    query.exec(
        "CREATE TABLE transactions ("
        "id INTEGER PRIMARY KEY, "
        "person INT NOT NULL, "
        "value REAL, "
        "added DATETIME NOT NULL)");

    checkAndReportSQLError("CREATE TABLE transactions", query.lastError());

    SyntilistaMainWindow swin;
    swin.show();
    return sapp.exec();
}


//
// Main application window code
//
SyntilistaMainWindow::SyntilistaMainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::SyntilistaMainWindow)
{
    ui->setupUi(this);
    ui->edit_Amount->setValidator(new QDoubleValidator(0, 1000, 2, this));

    readSettings();

    peopleSortIndex = 1;
    peopleSortOrder = Qt::AscendingOrder;
    peopleFilter = "";

    model_People = new PersonSQLModel();
    updatePersonList();

    ui->tableview_People->setModel(model_People);
    ui->tableview_People->setColumnHidden(0, true);
    ui->tableview_People->setItemDelegate(new QSqlRelationalDelegate(ui->tableview_People));
    ui->tableview_People->verticalHeader()->setVisible(false);
    ui->tableview_People->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tableview_People->setSortingEnabled(true);

    connect(ui->tableview_People->selectionModel(),
        SIGNAL(currentChanged(const QModelIndex &, const QModelIndex &)),
        this, SLOT(selectedPersonChanged(const QModelIndex &, const QModelIndex &)));

    connect(ui->tableview_People->horizontalHeader(),
        SIGNAL(sortIndicatorChanged(int, Qt::SortOrder)),
        this,
        SLOT(updateSortOrder(int, Qt::SortOrder)));

    ui->tableview_People->horizontalHeader()->setSortIndicator(1, Qt::AscendingOrder);

    model_Latest = new TransactionSQLModel();
    ui->tableview_Latest->setModel(model_Latest);
    ui->tableview_Latest->setItemDelegate(new QSqlRelationalDelegate(ui->tableview_Latest));
    ui->tableview_Latest->verticalHeader()->setVisible(false);
    ui->tableview_Latest->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);

    setActivePerson(-1);
}


SyntilistaMainWindow::~SyntilistaMainWindow()
{
    printf("QUITTAUS\n");

    saveSettings();

    delete ui;
    delete model_People;
    delete model_Latest;
}


void SyntilistaMainWindow::statusMsg(const QString &msg)
{
    ui->statusbar->showMessage(msg);
}


void SyntilistaMainWindow::readSettings()
{
    QSettings settings(APP_VENDOR, APP_NAME);
    move(settings.value("pos", QPoint(100, 100)).toPoint());
    resize(settings.value("size", QSize(1000, 600)).toSize());
}


void SyntilistaMainWindow::saveSettings()
{
    QSettings settings(APP_VENDOR, APP_NAME);
    settings.setValue("pos", pos());
    settings.setValue("size", size());
}


void SyntilistaMainWindow::selectedPersonChanged(const QModelIndex &curr, const QModelIndex &prev)
{
    (void) prev;
    int row = curr.row();
    if (row >= 0)
    {
        const QAbstractItemModel *model = curr.model();
        setActivePerson(model->data(model->index(row, 0)).toInt());
    }
    else
        setActivePerson(-1);
}


void SyntilistaMainWindow::updateSortOrder(int index, Qt::SortOrder order)
{
    peopleSortIndex = index;
    peopleSortOrder = order;
    updatePersonList();
}


void SyntilistaMainWindow::setActivePerson(qint64 id)
{
    personID = id;

    if (id >= 0)
    {
        QSqlQuery person;
        person.prepare(query_Person +" WHERE id=?");
        person.addBindValue(id);
        person.exec();
        checkAndReportSQLError("SELECT in setActivePerson()", person.lastError());

        if (!person.next())
        {
            statusMsg(tr("ERROR! No person with ID #%1").arg(id));
        }
        else
        {
            ui->personGB->setEnabled(true);
            ui->label_PersonName->setText(person.value(1).toString() +", "+ person.value(2).toString());

            double balance = person.value(3).toDouble();
            ui->label_BalanceValue->setText(moneyValueToStr(balance));
            ui->label_BalanceValue->setStyleSheet(balance < 0 ? "color: red;" : "color: green;");

            QSqlQuery query;
            query.prepare("SELECT id,value,added FROM transactions WHERE person=? ORDER BY added DESC LIMIT 5");
            query.addBindValue(id);
            query.exec();
            checkAndReportSQLError("SELECT transactions for tableview_Latest", query.lastError());

            model_Latest->setQuery(query);

            model_Latest->setHeaderData(0, Qt::Horizontal, "ID");
            model_Latest->setHeaderData(1, Qt::Horizontal, "Summa");
            model_Latest->setHeaderData(2, Qt::Horizontal, "Aika");

            ui->tableview_Latest->setModel(model_Latest);
            ui->tableview_Latest->setColumnHidden(0, true);
            ui->tableview_Latest->verticalHeader()->setVisible(false);
            ui->tableview_Latest->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);

            return; // Ugly
        }
    }

    // In case of id < 0 or errors ..
    ui->personGB->setEnabled(false);
    ui->edit_Amount->clear();
    ui->label_BalanceValue->setText("--");
    ui->label_BalanceValue->setStyleSheet(NULL);
    ui->label_PersonName->setText("???");
    ui->tableview_Latest->setModel(NULL);
}


//
// Widget slot handlers
//
void SyntilistaMainWindow::on_button_Quit_clicked()
{
    close();
}


void SyntilistaMainWindow::on_button_AddPerson_clicked()
{
    EditPerson *person = new EditPerson(this);
    person->setPerson(-1);
}


void SyntilistaMainWindow::on_button_EditPerson_clicked()
{
    EditPerson *person = new EditPerson(this);
    person->setPerson(personID);
}


void SyntilistaMainWindow::on_tableview_People_doubleClicked(const QModelIndex &curr)
{
    int row = curr.row();
    if (row >= 0)
    {
        const QAbstractItemModel *model = curr.model();
        setActivePerson(model->data(model->index(row, 0)).toInt());

        EditPerson *person = new EditPerson(this);
        person->setPerson(personID);
    }
    else
        setActivePerson(-1);
}


void SyntilistaMainWindow::on_button_ClearFilter_clicked()
{
    ui->edit_PersonFilter->clear();
}


void SyntilistaMainWindow::updatePersonData(qint64 id)
{
    printf("updatePersonData(%lld)\n", id);
    if (id == personID)
        setActivePerson(id);
    
    model_People->updateModel();
}


void SyntilistaMainWindow::updatePersonList()
{
    QSqlQuery query;
    QString queryDir, querySort = QStringLiteral("");

    if (peopleSortOrder == Qt::AscendingOrder)
        queryDir = QStringLiteral("ASC");
    else
        queryDir = QStringLiteral("DESC");

    switch (peopleSortIndex)
    {
        case 1:
        case 2:
            querySort = QStringLiteral(" ORDER BY last_name ") + queryDir + QStringLiteral(",first_name ") + queryDir;
            break;
        
        case 3:
            querySort = QStringLiteral(" ORDER BY balance ") + queryDir;
            break;

        case 4:
            querySort = QStringLiteral(" ORDER BY updated ") + queryDir;
            break;
    }

    if (peopleFilter != "")
    {
        QString tmp = "%"+ peopleFilter +"%";
        query.prepare(query_Person +" WHERE first_name LIKE ? OR last_name LIKE ?" + querySort);

        query.addBindValue(tmp);
        query.addBindValue(tmp);
    }
    else
    {
        query.prepare(query_Person + querySort);
    }

    checkAndReportSQLError("updatePersonList() before exec", query.lastError());
    query.exec();
    checkAndReportSQLError("updatePersonList() after exec", query.lastError());

    model_People->setQuery(query);

    model_People->setHeaderData(0, Qt::Horizontal, "ID");
    model_People->setHeaderData(1, Qt::Horizontal, "Sukunimi");
    model_People->setHeaderData(2, Qt::Horizontal, "Etunimi");
    model_People->setHeaderData(3, Qt::Horizontal, "Tase");
    model_People->setHeaderData(4, Qt::Horizontal, "Muutettu");
}


void SyntilistaMainWindow::on_edit_PersonFilter_textChanged(const QString &str)
{
    peopleFilter = cleanupStr(str);
    updatePersonList();
}


void SyntilistaMainWindow::on_button_XXX_clicked()
{
//    printf("XXX-namiskaa painettu!\n");
}


bool SyntilistaMainWindow::addTransaction(bool debt, double value)
{
    if (personID <= 0)
        return false;

    QSqlQuery person;
    person.prepare("SELECT * FROM people WHERE id=?");
    person.addBindValue(personID);
    person.exec();
    person.next();

    if (value != 0)
    {
        QSqlQuery query;
        query.prepare("INSERT INTO transactions (person,value,added) VALUES (?,?,?)");
        query.addBindValue(personID);
        query.addBindValue(debt ? -value : value);
        query.addBindValue(QDateTime::currentDateTimeUtc());
        query.exec();
        checkAndReportSQLError("addTransaction()", query.lastError());

        query.prepare("UPDATE people SET updated=? WHERE id=?");
        query.addBindValue(QDateTime::currentDateTimeUtc());
        query.addBindValue(personID);
        query.exec();
        checkAndReportSQLError("addTransaction update timestamp", query.lastError());

        QSqlDatabase::database().commit();

        ui->edit_Amount->clear();
        updatePersonData(personID);

        QString str;
        if (debt)
        {
            str = tr("Lisättiin velkaa %1 EUR henkilölle '%2 %3' (#%4).").
                arg(value, 1, 'f', 2).
                arg(person.value(1).toString()).
                arg(person.value(2).toString()).
                arg(person.value(0).toInt());
        }
        else
        {
            str = tr("Vähennettiin velkaa %1 EUR henkilöltä '%2 %3' (#%4).").
                arg(value, 1, 'f', 2).
                arg(person.value(1).toString()).
                arg(person.value(2).toString()).
                arg(person.value(0).toInt());
        }


        statusMsg(str);
        return true;
    }
    else
    {
        QString tmp = (debt ? "lisätty" : "vähennetty");
        statusMsg("Velkaa ei "+ tmp +" koska summaa ei määritetty.");
        return false;
    }
}


void SyntilistaMainWindow::on_button_AddDebt_clicked()
{
    addTransaction(true, moneyStrToValue(ui->edit_Amount->text()));
}


void SyntilistaMainWindow::on_button_SubDebt_clicked()
{
    addTransaction(false, moneyStrToValue(ui->edit_Amount->text()));
}


//
// Edit person dialog
//
EditPerson::EditPerson(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::EditPerson)
{
    ui->setupUi(this);

    setModal(true);
    setAttribute(Qt::WA_DeleteOnClose);
    show();
    activateWindow();
    raise();
    setFocus();

    model_Transactions = new TransactionSQLModel();
    ui->tableview_Transactions->setModel(model_Transactions);
    ui->tableview_Transactions->setItemDelegate(new QSqlRelationalDelegate(ui->tableview_Transactions));
    ui->tableview_Transactions->verticalHeader()->setVisible(false);
    ui->tableview_Transactions->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);

    validateForm();
}


EditPerson::~EditPerson()
{
    delete ui;
    delete model_Transactions;
}


void EditPerson::statusMsg(const QString &msg)
{
    dynamic_cast<SyntilistaMainWindow *>(parent())->statusMsg(msg);
}


bool EditPerson::validateForm(PersonInfo &info)
{
    info.firstName = cleanupStr(ui->edit_FirstName->text());
    info.lastName = cleanupStr(ui->edit_LastName->text());

    ui->edit_FirstName->setStyleSheet(info.firstName == "" ? "background-color: red;" : NULL);
    ui->edit_LastName->setStyleSheet(info.lastName == "" ? "background-color: red;" : NULL);

    return info.firstName != "" && info.lastName != "";
}


bool EditPerson::validateForm()
{
    PersonInfo info;
    return validateForm(info);
}


void EditPerson::on_button_Cancel_clicked()
{
    close();
}


void EditPerson::on_button_OK_clicked()
{
    PersonInfo info;
    info.id = personID;
    info.extraInfo = ui->textedit_ExtraInfo->document()->toPlainText();

    if (!validateForm(info))
        return;

    if (info.id >= 0)
    {
        QSqlQuery person;
        person.prepare("SELECT * FROM people WHERE id <> ? AND first_name=? AND last_name=?");
        person.addBindValue(info.id);
        person.addBindValue(info.firstName);
        person.addBindValue(info.lastName);
        person.exec();

        checkAndReportSQLError("SELECT check for existing person by same name (UPDATE)", person.lastError());
        
        if (person.next())
        {
            statusMsg(tr("Ei pysty! Samalla nimellä on jo henkilö!"));
            return;
        }

        dynamic_cast<SyntilistaMainWindow *>(parent())->model_People->updatePerson(QModelIndex(), info);

        statusMsg(tr("Päivitettiin henkilö '%1 %2' (#%3).").
            arg(info.firstName).arg(info.lastName).arg(info.id));
    }
    else
    {
        QSqlQuery person;
        person.prepare("SELECT * FROM people WHERE first_name=? AND last_name=?");
        person.addBindValue(info.firstName);
        person.addBindValue(info.lastName);
        person.exec();

        checkAndReportSQLError("SELECT check for existing person by same name (ADD)", person.lastError());

        if (person.next())
        {
            statusMsg(tr("Ei pysty! Samalla nimellä on jo henkilö!"));
            return;
        }

        dynamic_cast<SyntilistaMainWindow *>(parent())->model_People->addPerson(info);
        dynamic_cast<SyntilistaMainWindow *>(parent())->updatePersonList();

        statusMsg(tr("Lisättiin uusi henkilö '%1 %2'.").
            arg(info.firstName).arg(info.lastName));
    }

    close();
}


void EditPerson::on_edit_FirstName_textChanged(const QString &arg1)
{
    (void) arg1;
    validateForm();
}


void EditPerson::on_edit_LastName_textChanged(const QString &arg1)
{
    (void) arg1;
    validateForm();
}


void EditPerson::clearForm()
{
    ui->edit_FirstName->clear();
    ui->edit_LastName->clear();
    ui->textedit_ExtraInfo->document()->clear();
    ui->edit_FirstName->setFocus();
}


void EditPerson::setPerson(qint64 id)
{
    personID = id;

    if (id >= 0)
    {
        QSqlQuery person;
        person.prepare("SELECT * FROM people WHERE id=?");
        person.addBindValue(id);
        person.exec();
        checkAndReportSQLError("SELECT in EditPerson::setPerson()", person.lastError());

        if (!person.next())
        {
            statusMsg(tr("ERROR! No person with ID #%1").arg(id));
        }
        else
        {
            ui->edit_FirstName->setText(person.value(1).toString());
            ui->edit_LastName->setText(person.value(2).toString());
            ui->textedit_ExtraInfo->document()->setPlainText(person.value(3).toString());
            
            QSqlQuery query;
            query.prepare("SELECT id,value,added FROM transactions WHERE person=? ORDER BY added DESC");
            query.addBindValue(id);
            query.exec();
            checkAndReportSQLError("SELECT transactions for tableview_Transactions", query.lastError());

            model_Transactions->setQuery(query);

            model_Transactions->setHeaderData(0, Qt::Horizontal, "ID");
            model_Transactions->setHeaderData(1, Qt::Horizontal, "Summa");
            model_Transactions->setHeaderData(2, Qt::Horizontal, "Aika");

            ui->tableview_Transactions->setModel(model_Transactions);
            ui->tableview_Transactions->setColumnHidden(0, true);

            return; // Ugly
        }
    }

    // In case of id < 0 or errors ..
    clearForm();
    ui->tableview_Transactions->setModel(NULL);
}


//
// Custom SQL models
//
PersonSQLModel::PersonSQLModel(QObject *parent) : QSqlQueryModel(parent)
{
}


QVariant PersonSQLModel::data(const QModelIndex &index, int role) const
{
    QVariant value = QSqlQueryModel::data(index, role);

    if (value.isValid() && role == Qt::DisplayRole)
    {
        switch (index.column())
        {
            case 3:
                return moneyValueToStr(value.toDouble());

            case 4:
                return dateTimeToStr(value.toDateTime());
        }
    }

    if (index.column() == 3 && role == Qt::ForegroundRole)
    {
        double val = QSqlQueryModel::data(index, Qt::DisplayRole).toDouble();
        if (val < 0)
            return QVariant::fromValue(QColor(Qt::red));
        else
            return QVariant::fromValue(QColor(Qt::green));
    }

    return value;
}


void PersonSQLModel::updatePerson(const QModelIndex &item, const PersonInfo &person)
{
    QSqlQuery query;
    query.prepare("UPDATE people SET first_name=?,last_name=?,extra_info=?,updated=? WHERE id=?");
    query.addBindValue(person.firstName);
    query.addBindValue(person.lastName);
    query.addBindValue(person.extraInfo);
    query.addBindValue(QDateTime::currentDateTimeUtc());
    query.addBindValue(person.id);
    query.exec();

    checkAndReportSQLError("PersonSQLModel::updatePerson()", query.lastError());
    QSqlDatabase::database().commit();
    
    updateModel();
}


void PersonSQLModel::addPerson(const PersonInfo &person)
{
//    beginInsertRows(QModelIndex(), rowCount(), rowCount());

    QSqlQuery np;
    np.prepare("INSERT INTO people (first_name,last_name,extra_info,added,updated) VALUES (?,?,?,?,?)");
    np.addBindValue(person.firstName);
    np.addBindValue(person.lastName);
    np.addBindValue(person.extraInfo);
    np.addBindValue(QDateTime::currentDateTimeUtc());
    np.addBindValue(QDateTime::currentDateTimeUtc());
    np.exec();

    checkAndReportSQLError("PersonSQLModel::addPerson()", np.lastError());
    QSqlDatabase::database().commit();

//    endInsertRows();    
    updateModel();
}


void PersonSQLModel::updateModel()
{
    printf("PersonSQLModel::updateModelInfo()\n");
    query().exec();
    emit dataChanged(index(0, 0), index(rowCount(), columnCount()));
}


TransactionSQLModel::TransactionSQLModel(QObject *parent) : QSqlQueryModel(parent)
{
}


QVariant TransactionSQLModel::data(const QModelIndex &index, int role) const
{
    QVariant value = QSqlQueryModel::data(index, role);

    if (value.isValid() && role == Qt::DisplayRole)
    {
        switch (index.column())
        {
            case 1:
                return moneyValueToStr(value.toDouble());
            
            case 2:
                return dateTimeToStr(value.toDateTime());
        }
    }

    if (index.column() == 1 && role == Qt::ForegroundRole)
    {
        double val = QSqlQueryModel::data(index, Qt::DisplayRole).toDouble();
        if (val < 0)
            return QVariant::fromValue(QColor(Qt::red));
        else
            return QVariant::fromValue(QColor(Qt::green));
    }

    return value;
}


void TransactionSQLModel::updateModel()
{
    printf("TransactionSQLModel::updateModelInfo()\n");
    query().exec();
    emit dataChanged(QModelIndex(), QModelIndex());
}