/********************************************************************************************
 *                                                                                          *
 * Copyright (C) 2017 Armin Felder, Dennis Beier                                            *
 * This file is part of RocketChatMobileEngine <https://git.fairkom.net/chat/fairchat>.     *
 *                                                                                          *
 * RocketChatMobileEngine is free software: you can redistribute it and/or modify           *
 * it under the terms of the GNU General Public License as published by                     *
 * the Free Software Foundation, either version 3 of the License, or                        *
 * (at your option) any later version.                                                      *
 *                                                                                          *
 * RocketChatMobileEngine is distributed in the hope that it will be useful,                *
 * but WITHOUT ANY WARRANTY; without even the implied warranty of                           *
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the                            *
 * GNU General Public License for more details.                                             *
 *                                                                                          *
 * You should have received a copy of the GNU General Public License                        *
 * along with RocketChatMobileEngine. If not, see <http://www.gnu.org/licenses/>.           *
 *                                                                                          *
 ********************************************************************************************/



#include <QDebug>
#include <QDir>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QSqlRecord>
#include <QStandardPaths>
#include <QDebug>
#include <exception>
#include <QSqlError>
#include <QIODevice>
#include <QTime>
#include <QDateTime>
#include <QMimeType>

#include "persistancelayer.h"

PersistanceLayer *PersistanceLayer::persistanceLayer = nullptr;

PersistanceLayer *PersistanceLayer::instance()
{
    if ( persistanceLayer == nullptr ) {
        persistanceLayer = new PersistanceLayer;
        //persistanceLayer->init();
    }

    return persistanceLayer;
}

void PersistanceLayer::initShema()
{
    initQueries();
    mDb.transaction();
    QSqlQuery pragmaquery;
    pragmaquery.prepare( QStringLiteral( "PRAGMA synchronous = OFF" ) );

    if ( !pragmaquery.exec() ) {
        qDebug() << pragmaquery.lastError();
    }

    pragmaquery.prepare( QStringLiteral( "PRAGMA JOURNAL_MODE=MEMORY" ) );

    if ( !pragmaquery.exec() ) {
        qDebug() << pragmaquery.lastError();
    }

    pragmaquery.prepare( QStringLiteral( "PRAGMA TEMP_STORE=MEMORY" ) );

    if ( !pragmaquery.exec() ) {
        qDebug() << pragmaquery.lastError();
    }

    pragmaquery.prepare( QStringLiteral( "PRAGMA PAGE_SIZE=4096" ) );

    if ( !pragmaquery.exec() ) {
        qDebug() << pragmaquery.lastError();
    }

    /*    pragmaquery.prepare( "PRAGMA locking_mode = EXCLUSIVE" );

        if ( !pragmaquery.exec() ) {
            qDebug() << pragmaquery.lastError();
        }
    */
    QSqlQuery query;
    query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS user_data "
                                   "(id integer primary key,"
                                   "username varchar(40),"
                                   "password varchar(40),"
                                   "token varchar(100),"
                                   "token_expire integer,"
                                   "user_id varchar(12),"
                                   "server varchar(100))" ) );

    if ( !query.exec() ) {
        qWarning() << query.lastError();
    }

    query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS rooms "
                                   "(id varchar(12) primary key, "
                                   "name varchar(100),"
                                   "type varchar(10),"
                                   "read_only bool,"
                                   "muted text,"
                                   "archived bool,"
                                   "joined bool, "
                                   "blocked bool DEFAULT 0, "
                                   "username varchar(100) DEFAULT \"\", "
                                   "chat_partner_id varchar(100) DEFAULT \"\", "
                                   "updated_at integer)" ) );

    if ( !query.exec() ) {
        qWarning() << query.lastError();
    }

    query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS messages "
                                   "(id varchar(12) primary key,"
                                   "rid varchar(12),"
                                   "author varchar(30),"
                                   "user_id varchar(12),"
                                   "ts integer,"
                                   "read BOOLEAN DEFAULT FALSE,"
                                   "json text)" ) );

    if ( !query.exec() ) {
        qWarning() << query.lastError();
    }

    query.prepare( QStringLiteral( "CREATE INDEX msgRidIndex "
                                   "ON messages (rid)" ) );

    if ( !query.exec() ) {
        qWarning() << query.lastError();
    }

    query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS file_cache "
                                   "(url varchar(200) primary key,"
                                   "path varchar(200),"
                                   "ts integer)" ) );

    if ( !query.exec() ) {
        qWarning() << query.lastError();
    }

    query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS current_room"
                                   "(id integer primary key,"
                                   "rid varchar(12),"
                                   "name varchar(30))" ) );

    if ( !query.exec() ) {
        qWarning() << query.lastError();
    }

    query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS custom_emojis"
                                   "(id varchar(30) primary key,"
                                   "file varchar(200),"
                                   "html varchar(200),"
                                   "unicode varchar(20),"
                                   "category varchar(100),"
                                   "sort_order integer DEFAULT 1)" ) );

    if ( !query.exec() ) {
        qWarning() << query.lastError();
    }

    query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS blocked_users"
                                   "(id varchar(30) primary key, author varchar(30) )" ) );

    if ( !query.exec() ) {
        qWarning() << query.lastError();
    }

    /* query.prepare( QStringLiteral( "CREATE INDEX authorIdx "
                                    "ON blocked_users (author)" ) );

     if ( !query.exec() ) {
         qWarning() << query.lastError();
     }
     */

    mDb.commit();

    query.prepare( QStringLiteral( "SELECT db_ver FROM app_info WHERE id=0" ) );
    int dbVer = -1;

    if ( !query.exec() ) {
        qWarning() << query.lastError();
    } else {
        dbVer = 0;
        QSqlRecord rec = query.record();
        int dbVerIndex = rec.indexOf( QStringLiteral( "db_ver" ) );

        while ( query.next() ) {
            dbVer = query.value( dbVerIndex ).toInt();
        }
    }

    query.finish();

    if ( !dbVer ) {
        upgradeSchema();
    } else if ( dbVer == -1 ) {
        mDb.transaction();
        query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS app_info"
                                       "(id varchar(30) primary key,"
                                       "db_ver integer,"
                                       "app_ver integer)" ) );

        if ( !query.exec() ) {
            qWarning() << query.lastError();
        }

        QString dbVersion = QString::number( DBVERSION );
        query.prepare( QStringLiteral( "INSERT INTO app_info (db_ver) VALUES(" ) + dbVersion + ")" );

        if ( !query.exec() ) {
            qWarning() << query.lastError();
        }

        mDb.commit();
    }

    mDb.transaction();

    query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS app_settings"
                                   "(id integer primary key,"
                                   "serverid integer,"
                                   "property varchar(100) UNIQUE,"
                                   "value varchar(100))" ) );

    if ( !query.exec() ) {
        qWarning() << query.lastError();
    }

    mDb.commit();

    query.prepare( QStringLiteral( "SELECT id FROM custom_emojis" ) );

    if ( !query.exec() ) {
        qWarning() << "failed SELECT custom_emojis";
        qWarning() << query.lastError();
    } else {
        int size = 0;

        while ( query.next() ) {
            size++;
            break;
        }

        query.finish();

        if ( size <= 0 ) {
            QFile dump( QStringLiteral( ":/sql/emojis.sql" ) );

            if ( !dump.open( QIODevice::ReadOnly | QIODevice::Text ) ) {
                qWarning() << "error loading dump";
                qWarning() << dump.errorString();
            } else {
                mDb.transaction();
                QTextStream in( &dump );
                QString dumpString;
                dumpString.reserve( 200 );

                while ( !in.atEnd() ) {

                    dumpString = in.readLine();

                    query.prepare( dumpString );

                    if ( !query.exec() ) {
                        qWarning() << "failed execute import dump query";
                        qWarning() << query.lastError();
                    }
                }

                dump.close();
                mDb.commit();
            }
        }
    }

    mDb.commit();
}

void PersistanceLayer::upgradeSchema()
{
    QSqlQuery query;
    query.prepare( QStringLiteral( "SELECT db_ver FROM app_info" ) );

    if ( !query.exec() ) {
        qWarning() << "failed execute app_version query";
    } else {
        int dbVersion = 0;

        QSqlRecord rec = query.record();
        int dbVerIndex = rec.indexOf( "db_ver" );

        while ( query.next() ) {
            dbVersion = query.value( dbVerIndex ).toInt();
        }

        query.finish();

        if ( dbVersion < DBVERSION ) {
            QFile sqlFile;
            int currentTarget = 0;

            if ( dbVersion < 1 ) {
                currentTarget = 1;
                sqlFile.setFileName( QStringLiteral( ":/sql/migrations/1.sql" ) );
                query.prepare( QStringLiteral( "DELETE FROM custom_emojis" ) );

                if ( query.exec() ) {
                    qWarning() << "truncation of, custom_emojis failed";
                    qWarning() << query.lastError();
                }
            } else if ( dbVersion < 2 ) {
                currentTarget = 2;
                sqlFile.setFileName( QStringLiteral( ":/sql/migrations/2.sql" ) );
            } else if ( dbVersion < 3 ) {
                currentTarget = 3;
                sqlFile.setFileName( QStringLiteral( ":/sql/migrations/3.sql" ) );
            } else if ( dbVersion < 4 ) {
                currentTarget = 4;
                sqlFile.setFileName( QStringLiteral( ":/sql/migrations/4.sql" ) );
            } else if ( dbVersion < 5 ) {
                currentTarget = 5;
                sqlFile.setFileName( QStringLiteral( ":/sql/migrations/5.sql" ) );
            } else if ( dbVersion < 6 ) {
                currentTarget = 6;
                sqlFile.setFileName( QStringLiteral( ":/sql/migrations/6.sql" ) );
            }



            if ( !sqlFile.open( QIODevice::ReadOnly | QIODevice::Text ) ) {
                qWarning() << "error loading dump";
                qWarning() << sqlFile.errorString();
            } else {
                mDb.transaction();

                QTextStream in( &sqlFile );
                QString line;

                while ( !in.atEnd() ) {
                    line = in.readLine();
                    query.prepare( line );

                    if ( !query.exec() ) {
                        qWarning() << "migration to DB ver " << currentTarget << " failed";
                        qWarning() << query.lastError();
                        qWarning() << query.lastQuery();
                        exit( EXIT_FAILURE );
                    }
                }

                mDb.commit();
            }

            upgradeSchema();
        }
    }
}

void PersistanceLayer::wipeDb()

{
    mDb.close();
    mDbReady = false;
    QString directory = QStandardPaths::writableLocation( QStandardPaths::AppLocalDataLocation );
    QString filePath = directory + QStringLiteral( "/data.sqlite" );
    QFile file( filePath );

    if ( !file.remove() ) {
        qWarning() << file.errorString();
    } else {
        qDebug() << "db wiped";
        QDir fileDownloadsDir( QStandardPaths::writableLocation( QStandardPaths::TempLocation ) + QStringLiteral( "/rocketChat" ) );

        if ( fileDownloadsDir.removeRecursively() ) {
            qDebug() << "Downloads wiped";
        } else {
            qWarning() << "can't wipe downloads";
        }
    }

    init();
}

void PersistanceLayer::transaction( )
{
    commitMutex.lock();

    // if ( mTransactionCounter == 0 ) {
    if ( !mDb.transaction() ) {
        qWarning() << mDb.lastError();
    }

    //  }

    mTransactionCounter++;
    commitMutex.unlock();
}

void PersistanceLayer::askForcommit()
{
    // commitMutex.lock();

    //  mCommitCounter++;

    //  commitMutex.unlock();
    commit( );
}

void PersistanceLayer::commit( )
{
    commitMutex.lock();

    //if ( mCommitCounter && mCommitCounter == mTransactionCounter ) {
    qDebug() << "commit !";

    if ( !mDb.commit() ) {
        qWarning() << mDb.lastError();
    } else {
        mCommitCounter = 0;
        mTransactionCounter = 0;
    }

    //}

    commitMutex.unlock();
}



void PersistanceLayer::setUserName( const QString &pUsername )

{
    QSqlQuery querySetUsername;
    querySetUsername.prepare( QStringLiteral( "UPDATE user_data "
                              "SET username=:username WHERE id=:id" ) );
    querySetUsername.bindValue( QStringLiteral( ":id" ), 0 );
    querySetUsername.bindValue( QStringLiteral( ":username" ), pUsername );

    if ( !querySetUsername.exec() ) {
        qWarning() << querySetUsername.lastError();
    }

}

void PersistanceLayer::setPassword( const QString &pPassword )
{
    QSqlQuery querySetPassword;
    querySetPassword.prepare( QStringLiteral( "UPDATE user_data "
                              "SET password=:password WHERE id=:id" ) );

    querySetPassword.bindValue( QStringLiteral( ":password" ), pPassword );
    querySetPassword.bindValue( QStringLiteral( ":id" ), 0 );

    if ( !querySetPassword.exec() ) {
        qWarning() << querySetPassword.lastError();
    }

}

void PersistanceLayer::setToken( const QString &pToken, uint pExpire )
{
    QSqlQuery querySetToken;
    querySetToken.prepare( QStringLiteral( "UPDATE user_data "
                                           "SET token=:token, token_expire=:token_expire WHERE id=:id" ) );

    querySetToken.bindValue( QStringLiteral( ":token" ), pToken );
    querySetToken.bindValue( QStringLiteral( ":id" ), 0 );
    querySetToken.bindValue( QStringLiteral( ":token_expire" ), pExpire );

    if ( !querySetToken.exec() ) {
        qWarning() << querySetToken.lastError();
    }
}

void PersistanceLayer::setUserData( const QString &pUser, const QString &pPass )
{
    QSqlQuery querySetUser;
    querySetUser.prepare( QStringLiteral( "REPLACE INTO user_data "
                                          "(id,password,username) VALUES(:id,:password,:username)" ) );

    querySetUser.bindValue( ":id", 0 );
    querySetUser.bindValue( ":password", pPass );
    querySetUser.bindValue( ":username", pUser );

    if ( !querySetUser.exec() ) {
        qWarning() << querySetUser.lastError();
        qWarning() << querySetUser.lastQuery();
    }
}

void PersistanceLayer::setUserId( const QString &pUserId )
{
    QSqlQuery querySetUserId;
    querySetUserId.prepare( QStringLiteral( "UPDATE user_data SET user_id=:userId WHERE id=:id" ) );
    querySetUserId.bindValue( QStringLiteral( ":id" ), 0 );
    querySetUserId.bindValue( QStringLiteral( ":userId" ), pUserId );

    if ( !querySetUserId.exec() ) {
        qWarning() << querySetUserId.lastError();
    }
}

void PersistanceLayer::setCurrentChannel( const QString &pChannelId, const QString &pChannelName )
{
    QSqlQuery querySetChannel;
    querySetChannel.prepare( QStringLiteral( "REPLACE INTO current_room"
                             " (id,rid,name) VALUES(:id,:rid,:name)" ) );

    querySetChannel.bindValue( QStringLiteral( ":id" ), 0 );
    querySetChannel.bindValue( QStringLiteral( ":rid" ), pChannelId );
    querySetChannel.bindValue( QStringLiteral( ":name" ), pChannelName );

    if ( !querySetChannel.exec() ) {
        qWarning() << querySetChannel.lastError();
    }
}

void PersistanceLayer::setSetting( const QString &pProperty, const QString &pValue )
{
    QSqlQuery querySetSetting;
    querySetSetting.prepare( QStringLiteral( "REPLACE INTO app_settings"
                             " (property,value) VALUES(:property,:value)" ) );

    querySetSetting.bindValue( QStringLiteral( ":property" ), pProperty );
    querySetSetting.bindValue( QStringLiteral( ":value" ), pValue );

    if ( !querySetSetting.exec() ) {
        qWarning() << querySetSetting.lastQuery();
        qWarning() << querySetSetting.lastError();
    }
}

void PersistanceLayer::addChannel( const QString &pId, const QString &pName, const QString &pType, qint64 pUpdatedAt, bool pJoined, bool pReadOnly, const QString &pMuted, bool pArchived, bool pBlocked, const QString &pUsername, const QString &pChatPartnerId )
{
    QSqlQuery queryAddChannel;
    queryAddChannel.prepare( QStringLiteral( "REPLACE INTO rooms "
                             "(id,name,type,joined,read_only,muted,archived,blocked,username,chat_partner_id, updated_at) VALUES(:id,:name,:type,:joined,:read_only,:muted,:archived,:blocked,:username,:chat_partner_id,:updated_at)" ) );

    queryAddChannel.bindValue( QStringLiteral( ":id" ), pId );
    queryAddChannel.bindValue( QStringLiteral( ":name" ), pName );
    queryAddChannel.bindValue( QStringLiteral( ":type" ), pType );
    queryAddChannel.bindValue( QStringLiteral( ":joined" ), pJoined );
    queryAddChannel.bindValue( QStringLiteral( ":read_only" ), pReadOnly );
    queryAddChannel.bindValue( QStringLiteral( ":muted" ), pMuted );
    queryAddChannel.bindValue( QStringLiteral( ":archived" ), pArchived );
    queryAddChannel.bindValue( QStringLiteral( ":blocked" ), pBlocked );
    queryAddChannel.bindValue( QStringLiteral( ":username" ), pUsername );
    queryAddChannel.bindValue( QStringLiteral( ":chat_partner_id" ), pChatPartnerId );
    queryAddChannel.bindValue( QStringLiteral( ":updated_at" ), pUpdatedAt );


    if ( !queryAddChannel.exec() ) {
        qWarning() << queryAddChannel.lastError();
        qWarning() << queryAddChannel.lastQuery();
    }

    queryAddChannel.finish();

}

void PersistanceLayer::deleteChannel( const QString &pId )
{
    QSqlQuery queryDeleteChannel;
    queryDeleteChannel.prepare( QStringLiteral( "DELETE FROM rooms WHERE id=:id" ) );

    queryDeleteChannel.bindValue( QStringLiteral( ":id" ), pId );

    if ( !queryDeleteChannel.exec() ) {
        qWarning() << queryDeleteChannel.lastError();
    } else {
        QSqlQuery queryDeleteMessagesFromChannel;
        queryDeleteMessagesFromChannel.prepare( QStringLiteral( "DELETE FROM messages WHERE rid=:id" ) );

        queryDeleteMessagesFromChannel.bindValue( QStringLiteral( ":id" ), pId );

        if ( !queryDeleteMessagesFromChannel.exec() ) {
            qWarning() << queryDeleteMessagesFromChannel.lastError();
        }
    }
}

void PersistanceLayer::addMessage( const QString &pId, const QString &pRid, const QString &pAuthor, qint64 pTs, const QString &pJson, const QString &pUserId )
{
    QSqlQuery queryAddMessage;
    queryAddMessage.prepare( QStringLiteral( "REPLACE INTO messages "
                             "(id,rid,author,ts,json,read, user_id) VALUES (:id,:rid,:author,:ts,:json,0,:user_id)" ) );

    queryAddMessage.bindValue( QStringLiteral( ":id" ), pId );
    queryAddMessage.bindValue( QStringLiteral( ":rid" ), pRid );
    queryAddMessage.bindValue( QStringLiteral( ":author" ), pAuthor );
    queryAddMessage.bindValue( QStringLiteral( ":ts" ), pTs );
    queryAddMessage.bindValue( QStringLiteral( ":json" ), pJson );
    queryAddMessage.bindValue( QStringLiteral( ":user_id" ), pUserId );

    if ( !queryAddMessage.exec() ) {
        qWarning() << "id: " << pId << " rid: " << pRid << " author: " << pAuthor << " ts: " << pTs << " json: " << pJson << "userId: " << pUserId;
        qWarning() << queryAddMessage.lastQuery();
        qWarning() << queryAddMessage.executedQuery();
        qWarning() << queryAddMessage.lastError();
    }

}

void PersistanceLayer::addFileCacheEntry( const QString &pUrl, const QString &pPath )
{
    QSqlQuery queryAddCacheEntry;
    queryAddCacheEntry.prepare( QStringLiteral( "REPLACE INTO file_cache (url,path,ts)"
                                " VALUES (:url,:path,:ts)" ) );
    qDebug() << pUrl;
    qDebug() << pPath;

    QDateTime dateTime = QDateTime::currentDateTime();

    queryAddCacheEntry.bindValue( QStringLiteral( ":url" ), pUrl );
    queryAddCacheEntry.bindValue( QStringLiteral( ":path" ), pPath );
    queryAddCacheEntry.bindValue( QStringLiteral( ":ts" ), dateTime.toTime_t() );

    if ( !queryAddCacheEntry.exec() ) {
        qWarning() << queryAddCacheEntry.lastError();
    }
}

void PersistanceLayer::addCustomEmoji( const QString &pTag, const QString &pPath, const QString &pHtml, const QString &pCategory )
{
    addCustomEmoji( pTag, pPath, pHtml, pCategory, QString( "" ) );
}

void PersistanceLayer::addCustomEmoji( const QString &pTag, const QString &pPath, const QString &pHtml, const QString &pCategory, const QString &pUnicode )
{
    addCustomEmoji( pTag, pPath, pHtml, pCategory, pUnicode, 0 );
}
void PersistanceLayer::addCustomEmoji( const QString &pTag, const QString &pPath, const QString &pHtml, const QString &pCategory, const QString &pUnicode, int pOrder )
{
    QSqlQuery queryAddEmoji;
    queryAddEmoji.prepare( QStringLiteral( "REPLACE INTO custom_emojis (id,file,html,unicode,category,sort_order)"
                                           " VALUES (:id,:file,:html,:unicode,:category,:sort_order)" ) );

    queryAddEmoji.bindValue( QStringLiteral( ":id" ), pTag );
    queryAddEmoji.bindValue( QStringLiteral( ":file" ), pPath );
    queryAddEmoji.bindValue( QStringLiteral( ":html" ), pHtml );
    queryAddEmoji.bindValue( QStringLiteral( ":unicode" ), pUnicode );
    queryAddEmoji.bindValue( QStringLiteral( ":category" ), pCategory );
    queryAddEmoji.bindValue( QStringLiteral( ":sort_order" ), pOrder );

    if ( !queryAddEmoji.exec() ) {
        qWarning() << queryAddEmoji.lastError();
    }
}

void PersistanceLayer::addUserToBlockList( const QString &pUserId, const QString &pUsername )
{
    QSqlQuery queryAddUser;
    queryAddUser.prepare( QStringLiteral( "INSERT INO blocked_users(id, author) VALUES(:id,:author)" ) );
    queryAddUser.bindValue( ":id", pUserId );
    queryAddUser.bindValue( ":author", pUsername );

    if ( !queryAddUser.exec() ) {
        qWarning() << queryAddUser.lastError();
    }


}


QHash<QString, QString> PersistanceLayer::getMessageByid( const QString &pId )
{
    QSqlQuery queryGetMessageByRid;
    queryGetMessageByRid.prepare( QStringLiteral( "SELECT * FROM messages LEFT JOIN blocked_users ON (messages.user_id=blocked_users.user_id) WHERE blocked_users.user_id IS NULL AND id=:id" ) );

    queryGetMessageByRid.bindValue( QStringLiteral( ":id" ), pId );
    QHash<QString, QString> row;

    if ( !queryGetMessageByRid.exec() ) {
        qWarning() << queryGetMessageByRid.lastError();
    } else {

        QSqlRecord rec = queryGetMessageByRid.record();
        int idCol = rec.indexOf( QStringLiteral( "id" ) );
        int ridCol = rec.indexOf( QStringLiteral( "rid" ) );
        int authorCol = rec.indexOf( QStringLiteral( "author" ) );
        int tsCol = rec.indexOf( QStringLiteral( "ts" ) );
        int messageCol = rec.indexOf( QStringLiteral( "message" ) );
        int typeCol = rec.indexOf( QStringLiteral( "type" ) );

        while ( queryGetMessageByRid.next() ) {
            row[QStringLiteral( "id" )] = queryGetMessageByRid.value( idCol ).toString();
            row[QStringLiteral( "rid" )] = queryGetMessageByRid.value( ridCol ).toString();
            row[QStringLiteral( "author" )] = queryGetMessageByRid.value( authorCol ).toString();
            row[QStringLiteral( "message" )] = queryGetMessageByRid.value( messageCol ).toString();
            row[QStringLiteral( "ts" )] = queryGetMessageByRid.value( tsCol ).toString();
            row[QStringLiteral( "type" )] = queryGetMessageByRid.value( typeCol ).toString();
        }
    }

    queryGetMessageByRid.finish();
    return row;
}


QString PersistanceLayer::getUserName()
{
    QSqlQuery queryGetName;
    queryGetName.prepare( QStringLiteral( "SELECT username FROM user_data WHERE id=0" ) );

    QString name = "";

    if ( !queryGetName.exec() ) {
        qWarning() << queryGetName.lastError();
    } else {

        QSqlRecord rec = queryGetName.record();
        int nameCol = rec.indexOf( QStringLiteral( "username" ) );

        while ( queryGetName.next() ) {
            name = queryGetName.value( nameCol ).toString();
        }
    }

    return name;
}

std::tuple<QString, QString> PersistanceLayer::getCurrentChannel()
{
    QString id;
    QString name;

    QSqlQuery queryGetCurrentChannel;
    queryGetCurrentChannel.prepare( QStringLiteral( "SELECT * FROM current_room WHERE id=0" ) );

    if ( !queryGetCurrentChannel.exec() ) {
        qWarning() << queryGetCurrentChannel.lastError();
    } else {
        while ( queryGetCurrentChannel.next() ) {
            id = queryGetCurrentChannel.value( QStringLiteral( "rid" ) ).toString();
            name = queryGetCurrentChannel.value( QStringLiteral( "name" ) ).toString();
        }
    }

    queryGetCurrentChannel.finish();
    return std::tuple<QString, QString>( id, name );
}

QString PersistanceLayer::getPassword()
{
    QString pass = "";

    QSqlQuery queryGetPass;
    queryGetPass.prepare( QStringLiteral( "SELECT password FROM user_data WHERE id=0" ) );

    if ( !queryGetPass.exec() ) {
        qWarning() << queryGetPass.lastError();
    } else {

        QSqlRecord rec = queryGetPass.record();
        int nameCol = rec.indexOf( QStringLiteral( "password" ) );

        while ( queryGetPass.next() ) {
            pass = queryGetPass.value( nameCol ).toString();
        }
    }

    queryGetPass.finish();
    return pass;
}

QPair<QString, uint> PersistanceLayer::getToken()
{
    // getToken.bindValue( ":id", 0 );
    QPair<QString, uint> token;

    QSqlQuery queryGetToken;
    queryGetToken.prepare( QStringLiteral( "SELECT token, token_expire FROM user_data WHERE id=0" ) );

    if ( !queryGetToken.exec() ) {
        qDebug() << queryGetToken.lastError();
    } else {
        QSqlRecord rec = queryGetToken.record();
        int tokenCol = rec.indexOf( QStringLiteral( "token" ) );
        int expireCol = rec.indexOf( QStringLiteral( "token_expire" ) );

        while ( queryGetToken.next() ) {
            token.first = queryGetToken.value( tokenCol ).toString();
            token.second = queryGetToken.value( expireCol ).toUInt();
        }
    }

    queryGetToken.finish();
    return token;
}

QString PersistanceLayer::getUserId()
{
    QSqlQuery queryGetUserId;
    queryGetUserId.prepare( "SELECT id,user_id FROM user_data WHERE id=0" );

    //getUserId.addBindValue( ":id", 0 );
    QString userId;

    if ( !queryGetUserId.exec() ) {
        qWarning() << "userid error" << queryGetUserId.lastError();
    } else {
        QSqlRecord rec = queryGetUserId.record();
        int userIdCol = rec.indexOf( QStringLiteral( "user_id" ) );

        while ( queryGetUserId.next() ) {
            userId = queryGetUserId.value( userIdCol ).toString();
        }
    }

    queryGetUserId.finish();
    return userId;
}

QList<QJsonObject> PersistanceLayer::getMessagesByRid( const QString &pRid )
{
    QSqlQuery queryGetMessagesByRid;
    queryGetMessagesByRid.prepare( QStringLiteral( "SELECT * FROM messages WHERE rid=:rid ORDER BY ts DESC LIMIT 20" ) );

    queryGetMessagesByRid.bindValue( QStringLiteral( ":rid" ), pRid );

    QList<QJsonObject> rows;
    rows.reserve( 50 );

    if ( !queryGetMessagesByRid.exec() ) {
        qWarning() << QStringLiteral( "request room with rid: " ) << pRid;
        qWarning() << queryGetMessagesByRid.lastError();
    } else {

        QSqlRecord rec = queryGetMessagesByRid.record();
        int jsonCol = rec.indexOf( QStringLiteral( "json" ) );
        int readCol = rec.indexOf( QStringLiteral( "read" ) );


        while ( queryGetMessagesByRid.next() ) {
            //QHash<QString,QJsonObject> entry;
            QString json = queryGetMessagesByRid.value( jsonCol ).toString();
            bool read =  queryGetMessagesByRid.value( readCol ).toBool();
            QJsonDocument doc = QJsonDocument::fromJson( json.toUtf8() );
            QJsonObject object = doc.object();
            object[QStringLiteral( "read" )] = read;
            //entry[id] = object;
            //   rows[id] = object;
            rows.append( object );
            //rows.append(entry);
        }
    }

    queryGetMessagesByRid.finish();
    return rows;
}

QList<QJsonObject> PersistanceLayer::getMessagesByRid( const QString &pRid, qint64 pFrom, qint64 pTo )
{
    QSqlQuery queryGetMessageByRidAndRange;
    queryGetMessageByRidAndRange.prepare( QStringLiteral( "SELECT * FROM messages WHERE rid=:rid AND ts<=:from AND ts>=:to ORDER BY ts DESC LIMIT 50" ) );

    queryGetMessageByRidAndRange.bindValue( QStringLiteral( ":rid" ), pRid );
    queryGetMessageByRidAndRange.bindValue( QStringLiteral( ":from" ), static_cast<qint64>( pFrom / 1000 ) );
    queryGetMessageByRidAndRange.bindValue( QStringLiteral( ":to" ), static_cast<qint64>( pTo / 1000 ) );

    QList<QJsonObject> rows;
    rows.reserve( 50 );

    if ( !queryGetMessageByRidAndRange.exec() ) {
        qWarning() << "request room with rid: " << pRid;
        qWarning() << queryGetMessageByRidAndRange.lastError();
    } else {

        QSqlRecord rec = queryGetMessageByRidAndRange.record();
        int jsonCol = rec.indexOf( QStringLiteral( "json" ) );
        int readCol = rec.indexOf( QStringLiteral( "read" ) );


        while ( queryGetMessageByRidAndRange.next() ) {
            QString json = queryGetMessageByRidAndRange.value( jsonCol ).toString();
            bool read =  queryGetMessageByRidAndRange.value( readCol ).toBool();
            QJsonDocument doc = QJsonDocument::fromJson( json.toUtf8() );
            QJsonObject object = doc.object();
            object[QStringLiteral( "read" )] = read;
            //entry["id"] = id;
            // entry["json"] = object;
            // rows.append(entry);
            rows.append( object );
        }
    }

    queryGetMessageByRidAndRange.finish();
    return rows;
}

QList<QJsonObject > PersistanceLayer::getMessagesByRid( const QString &pRid, qint64 pFrom, int pLimit )
{
    QSqlQuery queryGetMessagesByRidLimit;
    queryGetMessagesByRidLimit.prepare( QStringLiteral( "SELECT * FROM messages WHERE rid=:rid AND ts<=:from ORDER BY ts DESC LIMIT :limit" ) );

    queryGetMessagesByRidLimit.bindValue( QStringLiteral( ":rid" ), pRid );
    queryGetMessagesByRidLimit.bindValue( QStringLiteral( ":from" ), static_cast<qint64>( pFrom ) );
    queryGetMessagesByRidLimit.bindValue( QStringLiteral( ":limit" ), pLimit );

    QList<QJsonObject > rows;
    rows.reserve( 50 );

    if ( !queryGetMessagesByRidLimit.exec() ) {
        qWarning() << "request room with rid: " << pRid;
        qWarning() << queryGetMessagesByRidLimit.lastError();
    } else {

        QSqlRecord rec = queryGetMessagesByRidLimit.record();
        int jsonCol = rec.indexOf( QStringLiteral( "json" ) );
        int readCol = rec.indexOf( QStringLiteral( "read" ) );


        while ( queryGetMessagesByRidLimit.next() ) {
            // QHash<QString,QJsonObject> entry;
            QString json = queryGetMessagesByRidLimit.value( jsonCol ).toString();
            bool read =  queryGetMessagesByRidLimit.value( readCol ).toBool();
            QJsonDocument doc = QJsonDocument::fromJson( json.toUtf8() );
            QJsonObject object = doc.object();

            if ( object.isEmpty() ) {
                qWarning() << "invalid message Json: " << json;
            } else {
                object[QStringLiteral( "read" )] = read;

                //   QJsonValue idJson = id;
                //   entry["json"] = object;
                rows.append( object );
            }
        }
    }

    queryGetMessagesByRidLimit.finish();
    return rows;
}

QList<QPair<QString, QString >> PersistanceLayer::getListOfBlockedUsers()
{
    QList<QPair<QString, QString >> retList;
    QSqlQuery queryGetBlockedUsers;
    queryGetBlockedUsers.prepare( QStringLiteral( "SELECT * FROM blocked_users" ) );

    if ( !queryGetBlockedUsers.exec() ) {
        qWarning() << "getList of blocked users ";
        qWarning() << queryGetBlockedUsers.lastError();
    } else {
        QSqlRecord rec = queryGetBlockedUsers.record();
        int idCol = rec.indexOf( "id" );
        int nameCol = rec.indexOf( "author" );

        while ( queryGetBlockedUsers.next() ) {
            QPair<QString, QString> pair;
            pair.first = queryGetBlockedUsers.value( idCol ).String;
            pair.second = queryGetBlockedUsers.value( nameCol ).String;
            retList.append( pair );
        }
    }

    queryGetBlockedUsers.finish();
    return retList;
}


QList<QVariantHash> PersistanceLayer::getChannels( )
{
    QList<QVariantHash> roomsList;
    roomsList.reserve( 100 );

    QSqlQuery queryGetChannels;
    queryGetChannels.prepare( QStringLiteral( "SELECT * FROM rooms ORDER BY updated_at DESC " ) );

    if ( !queryGetChannels.exec() ) {
        qWarning() << queryGetChannels.lastError();
    } else {
        QSqlRecord rec = queryGetChannels.record();
        int idCol = rec.indexOf( QStringLiteral( "id" ) );
        int nameCol = rec.indexOf( QStringLiteral( "name" ) );
        int joinedCol = rec.indexOf( QStringLiteral( "joined" ) );
        int typeCol = rec.indexOf( QStringLiteral( "type" ) );
        int readOnlyCol = rec.indexOf( QStringLiteral( "read_only" ) );
        int mutedCol =  rec.indexOf( QStringLiteral( "muted" ) );
        int archivedCol = rec.indexOf( QStringLiteral( "archived" ) );
        int blocked = rec.indexOf( QStringLiteral( "blocked" ) );
        int usernameCol = rec.indexOf( QStringLiteral( "username" ) );
        int chatPartnerIdCol = rec.indexOf( QStringLiteral( "chat_partner_id" ) );
        int updatedAt = rec.indexOf( QStringLiteral( "updated_at" ) );


        while ( queryGetChannels.next() ) {
            QVariantHash roomsHash;
            QString muted = queryGetChannels.value( mutedCol ).toString();
            roomsHash[QStringLiteral( "id" )] = queryGetChannels.value( idCol ).toString();
            roomsHash[QStringLiteral( "name" )] = queryGetChannels.value( nameCol ).toString();
            roomsHash[QStringLiteral( "joined" )] = queryGetChannels.value( joinedCol ).toBool();
            roomsHash[QStringLiteral( "type" )] = queryGetChannels.value( typeCol ).toString();
            roomsHash[QStringLiteral( "readOnly" )] = queryGetChannels.value( readOnlyCol ).toBool();
            roomsHash[QStringLiteral( "muted" )] = muted;
            roomsHash[QStringLiteral( "list" )] = muted.split( ',' );
            roomsHash[QStringLiteral( "archived" )] = queryGetChannels.value( archivedCol ).toBool();
            roomsHash[QStringLiteral( "blocked" )] = queryGetChannels.value( blocked ).toBool();
            roomsHash[QStringLiteral( "username" )] = queryGetChannels.value( usernameCol ).toString();
            roomsHash[QStringLiteral( "chatPartnerId" )] = queryGetChannels.value( chatPartnerIdCol ).toString();
            roomsHash[QStringLiteral( "updatedAt" )] = queryGetChannels.value( updatedAt ).toLongLong();

            roomsList.append( roomsHash );
        }
    }

    queryGetChannels.finish();
    return roomsList;
}

QList< QHash<QString, QString > > PersistanceLayer::getCustomEmojis()
{
    QList<QHash<QString, QString> > returnList;
    returnList.reserve( 3000 );

    QSqlQuery queryGetEmojies;
    queryGetEmojies.prepare( QStringLiteral( "SELECT id,file,html,category,unicode,sort_order FROM custom_emojis ORDER BY sort_order ASC" ) );

    if ( !queryGetEmojies.exec() ) {
        qWarning() << queryGetEmojies.lastError();
    } else {

        QSqlRecord rec = queryGetEmojies.record();

        int idCol = rec.indexOf( QStringLiteral( "id" ) );
        int fileCol = rec.indexOf( QStringLiteral( "file" ) );
        int htmlCol = rec.indexOf( QStringLiteral( "html" ) );
        int catCol = rec.indexOf( QStringLiteral( "category" ) );
        int unicodeCol = rec.indexOf( QStringLiteral( "unicode" ) );
        int orderCol = rec.indexOf( QStringLiteral( "sort_order" ) );

        while ( queryGetEmojies.next() ) {
            QHash<QString, QString> entry;
            entry[QStringLiteral( "id" )] = queryGetEmojies.value( idCol ).toString();
            entry[QStringLiteral( "file" )] = queryGetEmojies.value( fileCol ).toString();
            entry[QStringLiteral( "html" )] = queryGetEmojies.value( htmlCol ).toString();
            entry[QStringLiteral( "category" )] = queryGetEmojies.value( catCol ).toString();
            entry[QStringLiteral( "unicode" )] = queryGetEmojies.value( unicodeCol ).toString();
            entry[QStringLiteral( "sort_order" )] = queryGetEmojies.value( orderCol ).toString();

            returnList.append( entry );
        }
    }

    queryGetEmojies.finish();
    return returnList;
}


QList<QHash<QString, QString>> PersistanceLayer::getFiles()
{
    QList<QHash<QString, QString> > returnList;

    QSqlQuery getFilesQuery;
    getFilesQuery.prepare( "SELECT path,url FROM file_cache " );

    if ( !getFilesQuery.exec() ) {
        qWarning() << getFilesQuery.lastError();
    } else {
        QSqlRecord rec = getFilesQuery.record();
        int pathCol = rec.indexOf( QStringLiteral( "path" ) );
        int urlCol = rec.indexOf( QStringLiteral( "url" ) );

        while ( getFilesQuery.next() ) {
            QHash<QString, QString> entry;
            entry[QStringLiteral( "path" )] = getFilesQuery.value( pathCol ).toString();
            entry[QStringLiteral( "url" )] = getFilesQuery.value( urlCol ).toString();

            returnList.append( entry );
        }
    }

    return returnList;
}

QString PersistanceLayer::getFileCacheEntry( const QString &pUrl )
{
    QSqlQuery queryGetSetting;
    queryGetSetting.prepare( QStringLiteral( "SELECT property,value FROM app_settings WHERE property=:property" ) );

    queryGetFileCacheEntry.bindValue( QStringLiteral( ":url" ), pUrl );
    QString returnString = "";

    if ( !queryGetFileCacheEntry.exec() ) {
        qWarning() << queryGetFileCacheEntry.lastError();
    } else {

        QSqlRecord rec = queryGetFileCacheEntry.record();
        int pathCol = rec.indexOf( QStringLiteral( "path" ) );

        while ( queryGetFileCacheEntry.next() ) {
            returnString = queryGetFileCacheEntry.value( pathCol ).toString();
        }

        if ( returnString != "" ) {
            QFile cachedFile( returnString );

            if ( !cachedFile.exists() ) {
                removeFileCacheEntry( pUrl, returnString );
                returnString = "";
            }
        }
    }

    queryGetFileCacheEntry.finish();
    return returnString;
}

QString PersistanceLayer::getSetting( const QString &pProperty )
{
    QSqlQuery queryRemoveFileCacheEntry;
    queryRemoveFileCacheEntry.prepare( QStringLiteral( "DELETE FROM file_cache WHERE url=:url" ) );

    queryGetSetting.bindValue( QStringLiteral( ":property" ), pProperty );
    QString returnString = "";

    if ( !queryGetSetting.exec() ) {
        qWarning() << queryGetSetting.lastError();
    } else {
        QSqlRecord rec = queryGetSetting.record();

        int valueClol = rec.indexOf( QStringLiteral( "value" ) );

        while ( queryGetSetting.next() ) {
            returnString = queryGetSetting.value( valueClol ).toString();
        }
    }

    queryGetSetting.finish();
    return returnString;
}


void PersistanceLayer::removeFileCacheEntry( const QString &pUrl )
{
    QString fileName = getFileCacheEntry( pUrl );
    removeFileCacheEntry( pUrl, fileName );
}

void PersistanceLayer::removeFileCacheEntry( const QString &pUrl, const QString &pPath )
{
    if ( pUrl != "" && pPath != "" ) {
        QFile cachedFile( pPath );

        if ( cachedFile.exists() ) {
            if ( !cachedFile.remove() ) {
                qWarning() << cachedFile.errorString();
            }
        }
    }

    queryRemoveFileCacheEntry.bindValue( QStringLiteral( ":url" ), pUrl );

    if ( !queryRemoveFileCacheEntry.exec() ) {
        qWarning() << queryRemoveFileCacheEntry.lastError();
    }
}


QString PersistanceLayer::getNewVideoPath( )
{
    QString path;
    path = QStandardPaths::writableLocation( QStandardPaths::MoviesLocation ) + QStringLiteral( "/recordings" );

    if ( path != "" ) {
        QDir dir( path );

        if ( !dir.exists() ) {
            if ( !dir.mkpath( path ) ) {
                qWarning() << "failed to create folder: " << path;
            }
        }

        QDateTime dateTime = QDateTime::currentDateTime();
        uint timeStamp = dateTime.toTime_t();
        QFile file( path + '/' + QString( timeStamp ) + QStringLiteral( ".mp4" ) );

        if ( !file.exists() ) {
            //file.open( QIODevice::ReadWrite );
            //file.close();
        }

        qDebug() << path + '/' + QString::number( timeStamp ) + ".mp4";
        return path + '/' + QString::number( timeStamp ) + QStringLiteral( ".mp4" );
    }

    return "";

}

QString PersistanceLayer::getNewImagePath( )
{
    QString path;
    path = QStandardPaths::writableLocation( QStandardPaths::PicturesLocation ) + QStringLiteral( "/recordings" );

    if ( path != "" ) {
        QDir dir( path );

        if ( !dir.exists() ) {
            if ( !dir.mkpath( path ) ) {
                qWarning() << "failed to create folder: " << path;
            }
        }

        QDateTime dateTime = QDateTime::currentDateTime();
        uint timeStamp = dateTime.toTime_t();
        QFile file( path + '/' + timeStamp + QStringLiteral( ".jpg" ) );

        if ( !file.exists() ) {
            //file.open( QIODevice::ReadWrite );
            //file.close();
        }

        qDebug() << path + '/' + QString::number( timeStamp ) + QStringLiteral( ".jpg" );
        return path + '/' + QString::number( timeStamp ) + QStringLiteral( ".jpg" );
    }

    return "";

}





PersistanceLayer::~PersistanceLayer()
{
    mDb.close();
}

void PersistanceLayer::init()
{
    if ( !mDbReady ) {
        QString directory = QStandardPaths::writableLocation( QStandardPaths::AppLocalDataLocation );
        QString filePath = directory + QStringLiteral( "/data.sqlite" );
        qDebug() << "SQLITE: " << filePath;
        mDb.setDatabaseName( filePath );
        QDir dbPath( directory );

        if ( !dbPath.exists() ) {
            dbPath.mkpath( directory );
        }

        QFile file( filePath );

        if ( !file.exists() ) {
            file.open( QIODevice::ReadWrite );
            file.close();
        }

        if ( !mDb.open() ) {
            qDebug() << mDb.lastError().text() + "file used:" + filePath;
            throw std::runtime_error( mDb.lastError().text().toStdString() + "file used:" + filePath.toStdString() );
        }

        // commitTimer.setInterval( 10000 );
        connect( &commitTimer, &QTimer::timeout, [&]() {
            commit();
            commitTimer.start();
        } );
        //  commitTimer.start();
        mDbReady = true;
        initShema();
        emit( ready() );


    } else {
        emit( ready() );
    }
}

void PersistanceLayer::initQueries()
{


}

void PersistanceLayer::deleteMessage( const QString id )
{
    QSqlQuery deleteMessage( mDb );
    deleteMessage.prepare( "DELETE FROM messages WHERE id=:id;" );
    deleteMessage.bindValue( ":id", id );

    if ( !deleteMessage.exec() ) {
        qWarning() << "id: " << id;
        qWarning() << deleteMessage.lastQuery();
        qWarning() << deleteMessage.executedQuery();
        qWarning() << deleteMessage.lastError();
    }
}

void PersistanceLayer::close()
{
    persistanceLayer->deleteLater();
}