#include "mbwatchdb.h"
#include <QRandomGenerator>
#include <QSqlError>
#include <QSqlQuery>
#include <QDebug>
#include "mbfileutil.h"
#include "mbbackupdirop.h"
#include "mbdateutils.h"

CMBWatchDb::CMBWatchDb()
{
    m_logTableName = "TN_LOG";
    m_backupDirTableName = "TN_BACKUPDIR";
}

CMBWatchDb::~CMBWatchDb()
{

}


QSqlDatabase CMBWatchDb::openDataBase()
{
    quint32 value = QRandomGenerator::global()->generate();
    QSqlDatabase db;
    if ( true == QSqlDatabase::contains(QString::number(value)))
    {
        db = QSqlDatabase::database(QString::number(value));
    }
    else
    {
        db = QSqlDatabase::addDatabase("QSQLITE", QString::number(value));
        db.setDatabaseName(m_path);
    }

    if (false == db.open())
    {
        qDebug() << db.lastError().text();
    }
    return db;
}

void CMBWatchDb::closeDataBase(QString connectionName)
{
    if( QSqlDatabase::contains(connectionName) ){
       QSqlDatabase::removeDatabase(connectionName);
    }
}

bool CMBWatchDb::execSql(QString sql, QString &errInfo)
{
    QMutexLocker locker(&m_mutex);

    QString connectionName;
    {
        QSqlDatabase db = openDataBase();
        connectionName = db.connectionName();

        QSqlQuery query(db);
        query.prepare(sql);

        if( !query.exec()){
            errInfo = query.lastError().text();
            return false;
        }
        query.clear();
    }

    closeDataBase(connectionName);

    return true;
}

bool CMBWatchDb::execSql(QSqlDatabase &db, QString sql, QString &errInfo)
{
    QSqlQuery query(db);
    query.prepare(sql);

    if( !query.exec()){
        errInfo = query.lastError().text();
        return false;
    }
    query.clear();

    return true;
}

bool CMBWatchDb::getFiledValueSql(QString sql, QString &tmpValue, QString tmpFiled, QString &errInfo)
{
    QMutexLocker locker(&m_mutex);

    QString connectionName;
    {
        QSqlDatabase db = openDataBase();
        connectionName = db.connectionName();

        QSqlQuery query(db);
        query.prepare(sql);

        if( !query.exec()){
            errInfo = query.lastError().text();
            return false;
        }
        while(query.next())
        {
            tmpValue = query.value(tmpFiled).toString();
        }

        query.clear();
    }

    closeDataBase(connectionName);

    return true;
}

bool CMBWatchDb::exsitSql(QString sql, bool &bExsit, QString &errInfo)
{
    QMutexLocker locker(&m_mutex);

    bExsit = false;

    QString connectionName;
    {
        QSqlDatabase db = openDataBase();
        connectionName = db.connectionName();

        QSqlQuery query(db);
        query.prepare(sql);

        if( !query.exec()){
            errInfo = query.lastError().text();
            return false;
        }
        // 获取大小
        int initialPos = query.at();
        int nCount = 0;
        if (query.last())
            nCount = query.at() + 1;
        else
            nCount = 0;
        query.seek(initialPos);
        // end 获取大小
        if( nCount >= 1 ){
            bExsit = true;
        }

        query.clear();
    }

    closeDataBase(connectionName);
    return true;
}

bool CMBWatchDb::isTableExist(QString table)
{
    QMutexLocker locker(&m_mutex);

    bool bExsit = false;

    QString sql = QString("select * from sqlite_master where name='%1'").arg(table);

    QString connectionName;
    {
        QSqlDatabase db = openDataBase();
        connectionName = db.connectionName();

        QSqlQuery query(db);
        query.prepare(sql);

        query.exec(sql);

        bExsit = query.next();


        query.clear();
    }

    closeDataBase(connectionName);

    return bExsit;
}


bool CMBWatchDb::createLogTable(QString &errInfo)
{
    QString sql;

    sql = QString("CREATE TABLE %1 ("
                   "CN_S_FILE_NAME      VARCHAR (128) PRIMARY KEY NOT NULL,"
                   "CN_T_LAST_MODFIY    DATE    NOT NULL"
               ")").arg(m_logTableName);

    if( isTableExist(m_logTableName) )
        return true;

    if(!execSql(sql,errInfo))
        return false;

    return true;
}

bool CMBWatchDb::createBackupTable(QString &errInfo)
{
    QString sql;

    sql = QString("CREATE TABLE %1 ("
                   "CN_G_ID             VARCHAR (128) PRIMARY KEY NOT NULL,"
                   "CN_S_FILE_NAME      VARCHAR (128) NOT NULL,"
                   "CN_S_NEWFILE_NAME   VARCHAR (128),"
                   "CN_S_SERVER_PATH    VARCHAR (128),"
                   "CN_S_SERVER_FLAG    VARCHAR (128),"
                   "CN_S_OP_TYPE        TEXT (128),"
                   "CN_T_CREATE         DATE (128)"
               ")").arg(m_backupDirTableName);

    if( isTableExist(m_backupDirTableName) )
        return true;

    if(!execSql(sql,errInfo))
        return false;

    return true;
}


bool CMBWatchDb::init(QString path,QString &errInfo)
{
    m_path = path;

    // 创建数据库
    QString filePath = CMBFileUtil::getPath(path);
    CMBFileUtil::createLevelDir(filePath);

    if( !createLogTable(errInfo) )
        return false;

    if(!createBackupTable(errInfo) )
        return false;

    return true;
}

bool CMBWatchDb::loadDB(QSqlQuery &query)
{
    while(query.next())
    {
//        QString login     = query.value("CN_S_LOGIN").toString();
//        QString svrID     = query.value("CN_S_SERVER_ID").toString();
//        QString backupDir = query.value("CN_S_BACKUP_DIR").toString();
//        QString svrPath   = query.value("CN_S_SERVER_PATH").toString();
//        QString isLogin   = query.value("CN_S_ISLOGIN").toString();

    }

    return true;
}

bool CMBWatchDb::queryDB(QString &errInfo)
{
    QMutexLocker locker(&m_mutex);

    QString connectionName,sql;

    //sql = QString("select * from %1 where CN_S_ISLOGIN='Y'").arg(m_tableName);

    {
        QSqlDatabase db = openDataBase();
        connectionName = db.connectionName();

        QSqlQuery query(db);
        query.prepare(sql);

        if( !query.exec()){
            errInfo = query.lastError().text();
            return false;
        }

        if(!loadDB(query))
            return false;

        query.clear();
    }

    closeDataBase(connectionName);

    return true;
}

bool CMBWatchDb::exsitLogDB(QString fileName, bool &bExsit, QString &errInfo)
{
    QString sql;

    sql = QString("SELECT * FROM %1 where  CN_S_FILE_NAME='%2'").arg(m_logTableName,fileName);

    if(!exsitSql(sql, bExsit, errInfo))
        return false;

    return true;
}

bool CMBWatchDb::updateLogDB(QString fileName, QString lastMdfTime, QString &errInfo)
{
    QMutexLocker locker(&m_mutex);

    QString connectionName,sql;

    sql = QString("UPDATE  %1 SET "
                  "CN_T_LAST_MODFIY=':lastmdy'"
                  " where CN_S_FILE_NAME=':filename'").arg(m_logTableName);

    {
        QSqlDatabase db = openDataBase();
        connectionName = db.connectionName();

        QSqlQuery queryAdd(db);
        queryAdd.prepare(sql);

        queryAdd.bindValue(":lastmdy", lastMdfTime);
        queryAdd.bindValue(":filename", fileName);


        if(!queryAdd.exec())
        {
            errInfo = queryAdd.lastError().text();
            return false;
        }
    }
    closeDataBase(connectionName);

    return true;
}

bool CMBWatchDb::insertLogDB(QString fileName, QString lastMdfTime, QString &errInfo)
{
    QMutexLocker locker(&m_mutex);

    QString connectionName,sql;

    sql = QString("INSERT INTO %1 "
                  "(CN_S_FILE_NAME,CN_T_LAST_MODFIY)"
                  "  VALUES (:filename,:lastmdf)").arg(m_logTableName);

    {
        QSqlDatabase db = openDataBase();
        connectionName = db.connectionName();

        QSqlQuery queryAdd(db);
        queryAdd.prepare(sql);

        queryAdd.bindValue(":filename", fileName);
        queryAdd.bindValue(":lastmdf", lastMdfTime);

        if(!queryAdd.exec())
        {
            errInfo = queryAdd.lastError().text();
            return false;
        }
    }
    closeDataBase(connectionName);

    return true;
}

bool CMBWatchDb::getLastMdfTimeLogDB(QString fileName, QString &lastMdfTime, QString &errInfo)
{
    QString sql;
    QString tmpFiled;

    tmpFiled = "CN_T_LAST_MODFIY";
    sql      = QString("SELECT * FROM %1 where  CN_S_FILE_NAME='%2'").arg(m_logTableName,fileName);

    if(!getFiledValueSql(sql, lastMdfTime, tmpFiled,errInfo))
        return false;

    return true;
}

bool CMBWatchDb::setLogDB(QString &cmd,bool &bOpDB, QString fileName, QString lastMdfTime, QString &errInfo)
{
    bOpDB = true;

    lastMdfTime = lastMdfTime.trimmed();

    bool  bExsit = false;

    if( !exsitLogDB(fileName, bExsit, errInfo) )
        return false;

    if( bExsit ){

        QString tmpLastMdfTime;
        if(!getLastMdfTimeLogDB(fileName, tmpLastMdfTime, errInfo))
            return false;

        tmpLastMdfTime = tmpLastMdfTime.trimmed();

        if( tmpLastMdfTime == lastMdfTime ){
            bOpDB = false;
            return true;
        }

        cmd = CMBBackupdirOp::CMD_MODIFY;

        if( !updateLogDB(fileName, lastMdfTime,errInfo) )
            return false;

        return true;
    }

    cmd = CMBBackupdirOp::CMD_NEW;
    if( !insertLogDB(fileName, lastMdfTime,errInfo) )
        return false;

    return true;
}

bool CMBWatchDb::setLogDB(bool &bOpDB, QString fileName, QString lastMdfTime, QString &errInfo)
{
    QString cmd;
    return setLogDB(cmd,bOpDB, fileName, lastMdfTime, errInfo);
}

bool CMBWatchDb::insertBackupDirDB(QString opType, QString fileName, QString newFileName, QString svrPath, QString svrFlag, QString &errInfo)
{
    QMutexLocker locker(&m_mutex);

    QString connectionName,sql,id,tCreate;

    id      = CMBFileUtil::genGuid();
    tCreate = CMBDateUtils::getCurTime();

    sql = QString("INSERT INTO %1 "
                  "(CN_G_ID,CN_S_FILE_NAME,CN_S_NEWFILE_NAME,CN_S_SERVER_PATH,CN_S_SERVER_FLAG,CN_S_OP_TYPE,CN_T_CREATE)"
                  "  VALUES (:id,:filename,:newfilename,:svrpath,:svrflag,:optype,:tcreate)").arg(m_backupDirTableName);

    {
        QSqlDatabase db = openDataBase();
        connectionName = db.connectionName();

        QSqlQuery queryAdd(db);
        queryAdd.prepare(sql);

        queryAdd.bindValue(":id", id);
        queryAdd.bindValue(":filename", fileName);
        queryAdd.bindValue(":newfilename", newFileName);
        queryAdd.bindValue(":svrflag", svrFlag);
        queryAdd.bindValue(":svrpath", svrPath);
        queryAdd.bindValue(":optype", opType);
        queryAdd.bindValue(":tcreate", tCreate);

        if(!queryAdd.exec())
        {
            errInfo = queryAdd.lastError().text();
            return false;
        }
    }
    closeDataBase(connectionName);

    return true;
}

bool CMBWatchDb::exsitFileNameBackupDirDB(QString fileName, bool &bExsit, QString &errInfo)
{
    QString sql;

    sql = QString("SELECT * FROM %1 where  CN_S_FILE_NAME='%2'").arg(m_backupDirTableName,fileName);

    if(!exsitSql(sql, bExsit, errInfo))
        return false;

    return true;
}

bool CMBWatchDb::delBackupDirDB(QString fileName, QString &errInfo)
{
    QString sql;

    sql = QString("delete from %1 where CN_S_FILE_NAME='%2'").arg(m_backupDirTableName,fileName);

    if(!execSql(sql,errInfo))
        return false;

    return true;
}

bool CMBWatchDb::renameBakcupDirDB(QString opType, QString fileName, QString newFileName, QString svrPath,  QString svrFlag, QString &errInfo)
{
    return insertBackupDirDB(opType, fileName, newFileName, svrPath, svrFlag, errInfo);
}

bool CMBWatchDb::newBakcupDirDB(QString opType, QString fileName, QString newFileName, QString svrPath, QString svrFlag, QString &errInfo)
{
    return insertBackupDirDB(opType, fileName, newFileName, svrPath, svrFlag, errInfo);
}

bool CMBWatchDb::mdyBakcupDirDB(QString opType, QString fileName, QString newFileName, QString svrPath, QString svrFlag, QString &errInfo)
{
    bool bExsit = false;
    if(!exsitFileNameBackupDirDB(fileName, bExsit, errInfo))
        return false;
    if( bExsit )
        return true;
    return insertBackupDirDB(opType, fileName, newFileName, svrPath, svrFlag, errInfo);
}

bool CMBWatchDb::setBackupDirDB(QString opType, QString fileName, QString newFileName, QString svrPath, QString lastMdfTime, QString svrFlag, QString &errInfo)
{
    opType = opType.trimmed();
    if( opType == CMBBackupdirOp::CMD_NEW ){
        return newBakcupDirDB(opType, fileName, newFileName, svrPath,  svrFlag, errInfo);
    }else if( opType == CMBBackupdirOp::CMD_MODIFY ){
        return mdyBakcupDirDB(opType, fileName, newFileName, svrPath,  svrFlag, errInfo);
    }else if( opType == CMBBackupdirOp::CMD_DELETE ){
        return delBackupDirDB(fileName, errInfo);
    }else if( opType == CMBBackupdirOp::CMD_RENAME ){
        return renameBakcupDirDB(opType, fileName, newFileName, svrPath,  svrFlag, errInfo);
    }
    return true;
}

bool CMBWatchDb::adjDB(QString opType, QString fileName, QString newFileName, QString svrPath, QString lastMdfTime,QString svrFlag,QString &errInfo)
{
    bool    bOpDB = false;

    if( !setLogDB(bOpDB,fileName, lastMdfTime, errInfo) )
        return false;

    if(!bOpDB)
        return true;

    // 写 backup dir db
    if( !setBackupDirDB(opType, fileName,newFileName, svrPath,lastMdfTime, svrFlag, errInfo) )
        return false;

    return true;
}

// 自已决定，操作类型
bool CMBWatchDb::adjDB(QString fileName, QString newFileName,
                                   QString svrPath, QString lastMdfTime,QString svrFlag,QString &errInfo)
{
    QString opType;
    bool    bOpDB = false;

    if( !setLogDB(opType,bOpDB,fileName, lastMdfTime, errInfo) )
        return false;

    if(!bOpDB)
        return true;

    //opType = CMBBackupdirOp::CMD_DELETE;
    // 写 backup dir db
    if( !setBackupDirDB(opType, fileName,newFileName, svrPath,lastMdfTime, svrFlag, errInfo) )
        return false;

    return true;
}