//
// Created by pans on 4/28/18.
//
#include <iostream>

#include <QtSql/QSql>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlError>
#include <QtSql/QSqlQuery>
#include <QtCore/QString>
#include <QtCore/QDebug>
#include <QtCore/QVariantList>
#include <basic/debug/Debug.h>

#include "SqliteFaceEncap.h"

using namespace std;


static QSqlDatabase g_db = QSqlDatabase::addDatabase("QSQLITE", "GBond");

SqliteFaceEncap::SqliteFaceEncap(std::string t_string) : sqliteEncapsulation(t_string.c_str()) {
    /* Open database */
    int rc;
    string sql;
    bool initRet = false;
    bool *test = &initRet;
    //--------------init db-------------------
    rc = sqlite3_open(t_string.c_str(), &db);
    string initRetSql = "select count(*) from sqlite_master where type='table' and name = '" + g_tableName + "';";
    rc = sqlite3_exec(db, initRetSql.c_str(), isExist, (void *) test, &zErrMsg);
    if (rc != SQLITE_OK) {
        SQLERR(zErrMsg);
        ERR(initRetSql);
        sqlite3_free(zErrMsg);
    } else {
        if (!initRet) {
            /* Create SQL statement */
            sql = getInitDBSql();
            /* Execute SQL statement */
            rc = sqlite3_exec(db, sql.c_str(), NULL, 0, &zErrMsg);
            if (rc != SQLITE_OK) {
                SQLERR(zErrMsg);
                ERR(sql);
                sqlite3_free(zErrMsg);
            } else {
                fprintf(stdout, "sys_o_tables created successfully\n");
            }
            fprintf(stdout, "Operation done successfully\n");
        }
    }
    //--------------init db-------------------
    g_db.setDatabaseName(t_string.c_str());
    g_db.open();

}

//#todo
bool SqliteFaceEncap::createTable(std::string t_tableName, FieldValues &fieldValues) {
    int rc;
    //--------------create table-------------------
    /* Create SQL statement */
    string sql = getCreateFaceTableSql(t_tableName);

    /* Execute SQL statement */
//            cout << sql << endl;
    rc = sqlite3_exec(db, sql.c_str(), NULL, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        SQLERR(zErrMsg);
        ERR(sql);
        sqlite3_free(zErrMsg);
        return false;
    } else {
        fprintf(stdout, "Table created successfully\n");
//    INSERT INTO sys_o_tables(name,people_name,type,color,table_name,table_type,is_self,is_long_control)
//    VALUES ('在逃库','在逃人员','人','red','taseee',2,0,0);
//        FieldValues fieldValues;
//        fieldValues.insert(std::make_pair("name","在逃库"));
//        fieldValues.insert(std::make_pair("people_name","在逃人员"));
//        fieldValues.insert(std::make_pair("type","人"));
//        fieldValues.insert(std::make_pair("color","red"));
//        fieldValues.insert(std::make_pair("table_name",t_tableName));
//        fieldValues.insert(std::make_pair("table_type","2"));
//        fieldValues.insert(std::make_pair("is_self","0"));
//        fieldValues.insert(std::make_pair("is_long_control","0"));
        string insertSql = getInsertSql("sys_o_tables", fieldValues);
        /* Execute SQL statement */
        rc = sqlite3_exec(db, insertSql.c_str(), NULL, 0, &zErrMsg);
        if (rc != SQLITE_OK) {
            SQLERR(zErrMsg);
            ERR(insertSql);
            sqlite3_free(zErrMsg);
            //#todo throw exp
//            throw exception("insert fail");
        } else {
            fprintf(stdout, " insert data successfully\n");
        }
    }
    //--------------create table-------------------
    return true;
}


bool SqliteFaceEncap::updateTable(std::string t_tableName, FieldValues &fieldValues) {
    int rc;
    //--------------create table-------------------
    /* Create SQL statement */
    //#todo
    string sql = getUpdateFaceTableSql(t_tableName, fieldValues);

    //#todo create facefea

    /* Execute SQL statement */
//            cout << sql << endl;
    rc = sqlite3_exec(db, sql.c_str(), NULL, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        SQLERR(zErrMsg);
        ERR(sql);
        sqlite3_free(zErrMsg);
        return false;
    } else {
        fprintf(stdout, "updateTable successfully\n");
    }
    //--------------create table-------------------
    return true;
}

//#todo backup data?
bool SqliteFaceEncap::deleteTable(std::string t_tableName) {
    string delTableSql = getDeleteFaceTableSql(t_tableName);
    int rc = sqlite3_exec(db, delTableSql.c_str(), NULL, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        SQLERR(zErrMsg);
        ERR(delTableSql);
        sqlite3_free(zErrMsg);
        return false;
    } else {
        fprintf(stdout, "Table deleted successfully\n");
        FieldValues fieldValues;
        fieldValues.insert(std::make_pair("tableName", t_tableName));
        string deleteSql = getDeleteSql("sys_o_tables", fieldValues);
//        cout << deleteSql << endl;
        /* Execute SQL statement */
        rc = sqlite3_exec(db, deleteSql.c_str(), NULL, 0, &zErrMsg);
        if (rc != SQLITE_OK) {
            SQLERR(zErrMsg);
            ERR(deleteSql);
            sqlite3_free(zErrMsg);
            //#todo throw exp
//            throw exception("insert fail");
        } else {
            fprintf(stdout, " delete data successfully\n");
        }
    }
    return true;
}


bool SqliteFaceEncap::updateFace(std::string t_tableName, AddFaceData &faceData, FieldValues &fieldValues) {

//    updateFaceData
    bool ret = updateFaceData(t_tableName + "_fea", faceData);
    updateFaceInfo(t_tableName, fieldValues);
    return ret;
}

bool SqliteFaceEncap::updateFaceData(std::string t_tableName, AddFaceData &faceData) {

    QByteArray qFea((char *) faceData.feature.data(), faceData.feature.size());
    QSqlQuery query(g_db);
    QString strSql = "update ";
    strSql.append(t_tableName.c_str());
    if (faceData.feature.size() > 0 || faceData.faceUrl.size() > 0) {
        strSql.append(" set ");

        if (faceData.feature.size() > 0) {
            strSql.append(" feature =  :feature ,");
        }
        if (faceData.faceUrl.size() > 0) {
            strSql.append(" faceUrl =  :faceUrl ,");
        }
//        if (faceData.enabled.size() > 0) {
//            strSql.append(" enabled =  :enabled ,");
//        }

        //#todo
        strSql.append(" update_time = '2010-01-03 01:01:00' ");

        strSql.append(" where del_flag = 0 and uuid = '" + QString::fromStdString(faceData.uuid) + "';");
        query.prepare(strSql);
//        query.addBindValue(QString::fromStdString(faceData.uuid));
        if (faceData.feature.size() > 0) {
            query.addBindValue(qFea);
        }
        if (faceData.faceUrl.size() > 0) {
            query.addBindValue(QString::fromStdString(faceData.faceUrl));
        }
//        if (faceData.enabled.size() > 0) {
//            query.addBindValue(QString::fromStdString(faceData.enabled));
//        }

        if (!query.exec()) {
            ERR("updateFaceData fail");
            qDebug() << "updateFaceData" << __FILE__ << __LINE__ <<
                     query.lastQuery() << "   " << query.lastError();
        } else {
            DBG("更新记录成功  " << faceData.uuid);
            return true;
        }
    }

    return false;
}

//#wait todo
bool SqliteFaceEncap::updateFaceInfo(std::string t_tableName, FieldValues &fieldValues) {
    std::string sql = getUpdateFaceTableSql(t_tableName, fieldValues);
    int rc = sqlite3_exec(db, sql.c_str(), NULL, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        SQLERR(zErrMsg)
        sqlite3_free(zErrMsg);
        return false;
    } else {
        fprintf(stdout, "addFaceInfo successfully\n");
    }
    return true;
}//#todo

std::string SqliteFaceEncap::addFace(std::string t_tableName, AddFaceData &faceData, FieldValues &fieldValues) {
    std::string uuid = addFaceData(t_tableName + "_fea", faceData);
    if (uuid.size() > 0) {
        fieldValues["uuid"] = uuid;
        addFaceInfo(t_tableName, fieldValues);
    }
    return uuid;
}

//INSERT INTO aaaFace (id, feature, del_flag ) VALUES (12,'sdfasdfasdfasdf',0);
std::string SqliteFaceEncap::addFaceData(std::string t_tableName, AddFaceData &faceData) {

    QByteArray qFea((char *) faceData.feature.data(), faceData.feature.size());
    QSqlQuery query(g_db);
    QString strSql = "INSERT INTO ";
    strSql.append(t_tableName.c_str());

    strSql.append("(uuid,feature,faceUrl) VALUES (:uuid,:feature,:faceUrl);");
    query.prepare(strSql);
    query.addBindValue(QString::fromStdString(faceData.uuid));
    query.addBindValue(qFea);
//    cout << faceData.faceUrl << endl;
    query.addBindValue(QString::fromStdString(faceData.faceUrl));

    if (!query.exec()) {
        ERR("addFaceData fail");
        qDebug() << "addFaceData" << __FILE__ << __LINE__ << query.lastError();
        return "";
    } else {
        DBG("插入记录成功  " << faceData.uuid);
    }
    return faceData.uuid;
}

bool SqliteFaceEncap::addFaceInfo(std::string t_tableName, FieldValues &fieldValues) {
    std::string sql = getInsertSql(t_tableName, fieldValues);
    int rc = sqlite3_exec(db, sql.c_str(), NULL, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        SQLERR(zErrMsg)
        sqlite3_free(zErrMsg);
        return false;
    } else {
        fprintf(stdout, "addFaceInfo successfully\n");
    }
    return true;
}

bool SqliteFaceEncap::deleteFace(std::string t_tableName, std::string uuid) {
    deleteFaceData(t_tableName, uuid);
    std::string t_tmp(t_tableName);
    t_tmp.append("_fea");
    bool ret = deleteFaceData(t_tmp.c_str(), uuid);

    return ret;
}

//DELETE FROM aaaFace where 1=1  and id = 122
//#todo update del_flag ?
bool SqliteFaceEncap::deleteFaceData(std::string t_tableName, std::string uuid) {
//    FieldValues fieldValues;
//    fieldValues.insert(std::make_pair("uuid", uuid));
//    string deleteSql = getDeleteSql(t_tableName, fieldValues);
    string deleteSql = "update ";
    deleteSql.append(t_tableName + " set del_flag = 1 ,update_time = '" + AppUtil::getTimeSecString() + "' ");
    deleteSql.append(" where uuid = '" + uuid + "' ");
//        cout << deleteSql << endl;
    /* Execute SQL statement */
    int rc = sqlite3_exec(db, deleteSql.c_str(), NULL, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        SQLERR(zErrMsg)
        sqlite3_free(zErrMsg);
        //#todo throw exp
//            throw exception("insert fail");
        return false;
    } else {
        fprintf(stdout, " delete data successfully\n");
    }
    return true;
}

std::vector<string> SqliteFaceEncap::getTableNameList(std::string type) {
    std::vector<string> tables;
    QString sql = QString::fromStdString(getTableListSqlWithType(type));
    QSqlQuery query(g_db);
//    qDebug() << strSql;
    query.prepare(sql);
    if (!query.exec()) {
        qDebug() << "getTableNameList" << __FILE__ << __LINE__ << query.lastError();
    } else {
        while (query.next()) {
            string tableName = query.value(0).toString().toStdString();
            tables.push_back(tableName);
        }
    }
    return tables;
}

FeatureDBWithUrlCache *SqliteFaceEncap::getFacesFromTable(std::string tableName) {
    ClockTimer clk("loading " + tableName);
    FeatureDBWithUrlCache &t_db = m_dbCache[tableName];
    QString sql = QString::fromStdString(getFacesFromTableSql(tableName));
    QSqlQuery query(g_db);
    query.prepare(sql);
    if (!query.exec()) {
        qDebug() << "getFacesFromTable" << __FILE__ << __LINE__ << query.lastError();
    } else {
        while (query.next()) {
            int del_flag = query.value(4).toInt();
            std::string uuid = query.value(0).toString().toStdString();
            if (del_flag) {
                t_db.erase(uuid);
            } else {
                FaceFeatureWithUrl &t_feas = t_db[uuid];
                t_feas.uuid = uuid;
                QByteArray t_fea = query.value(1).toByteArray();
//            std::cout << t_fea.size() << std::endl;
                t_feas.faceFeature.resize(t_fea.size());
                memcpy(t_feas.faceFeature.data(), t_fea.data(), t_fea.size());
//            string create_time = query.value(2).toString().toStdString();
                t_feas.faceurl = query.value(3).toString().toStdString();
            }
        }
    }
    return &t_db;
}


FeatureDBWithUrlCache SqliteFaceEncap::getFacesFromTableWhereDate(std::string tableName, std::string date) {
    //#todo not used del_flag

    return FeatureDBWithUrlCache();
}

TableInfos SqliteFaceEncap::getTableInfos() {
    TableInfos tableInfos;
    QString sql = QString::fromStdString(getTableInfosSql());
    qDebug() << sql;
    QSqlQuery query(g_db);
    query.prepare(sql);
    if (!query.exec()) {
        qDebug() << "getTableInfos" << __FILE__ << __LINE__ << query.lastError() << "  " << query.lastQuery();
    } else {
        while (query.next()) {
            TableInfo tableInfo;
            tableInfo.uuid = query.value(0).toString().toStdString();
            tableInfo.tableName = query.value(1).toString().toStdString();
            tableInfo.tableType = query.value(2).toString().toStdString();
            tableInfo.bwType = query.value(3).toString().toStdString();
            tableInfo.startTime = query.value(4).toString().toStdString();
            tableInfo.endTime = query.value(5).toString().toStdString();
            tableInfo.uploadFlag = query.value(6).toString().toStdString();
            tableInfo.cmpThreshold = query.value(7).toString().toStdString();
            tableInfo.enabled = query.value(8).toString().toStdString();
            tableInfo.createBy = query.value(9).toString().toStdString();

            tableInfos.push_back(tableInfo);
        }
    }
    return tableInfos;
}

FaceInfosCache SqliteFaceEncap::getFaceInfoFromTable(std::string tableName) {
    FaceInfosCache faceInfosCache;
    QString sql = QString::fromStdString(getTableInfosSql(tableName));
    std::lock_guard<std::mutex> dataGuard(dataMtx);
    QSqlQuery query(g_db);
    query.prepare(sql);
    if (!query.exec()) {
        qDebug() << "getTableNameList" << __FILE__ << __LINE__ << query.lastError();
    } else {
        while (query.next()) {
            int del_falg = query.value(9).toInt();
            if (del_falg == 1) {
                continue;
            }
            FaceInfo tableInfo;
            tableInfo.uuid = query.value(0).toString().toStdString();
            tableInfo.personName = query.value(1).toString().toStdString();
            tableInfo.age = query.value(2).toString().toStdString();
            tableInfo.sex = query.value(3).toString().toStdString();
            tableInfo.idCard = query.value(4).toString().toStdString();
            tableInfo.phoneNum = query.value(5).toString().toStdString();
            tableInfo.enable = query.value(10).toString().toStdString();
            tableInfo.monLevel = query.value(11).toString().toStdString();
            faceInfosCache.insert(std::make_pair(tableInfo.uuid, tableInfo));
        }
    }
    return faceInfosCache;
}