#include "Sql/DBHelper.h"
#include "CppCommon/Common/Basic/FileProxy.h"
#include <QVariant>

Incart::CablesDatabase::DBHelper::DBHelper() : m_hasValidDatabase(false), m_databasePath(QCoreApplication::applicationDirPath() + "/CablesSQLite.db")
{
    QString databaseSettingsPath = QCoreApplication::applicationDirPath() + "/DatabaseSettings.json";
    QVariantMap databaseSettingsMap;
    if (Common::FileProxy::readJsonMapFromFile(databaseSettingsPath, databaseSettingsMap))
    {
        QString databasePath = databaseSettingsMap["DatabasePath"].toString();
        if (!databasePath.isEmpty())
        {
            m_databasePath = databasePath;
        }
    }

    m_hasValidDatabase = Common::FileProxy::isFileExist(m_databasePath);
    if (m_hasValidDatabase)
    {
        m_cablesDatabase = QSqlDatabase::addDatabase("QSQLITE");
        m_cablesDatabase.setDatabaseName(m_databasePath);
    }
    else
    {
        qDebug() << "Error" << "Database(" + m_databasePath +") is not exist!";
    }
}

Incart::CablesDatabase::DBHelper::~DBHelper(){
    m_cablesDatabase.close();
}

bool Incart::CablesDatabase::DBHelper::hasValidDatabase()
{
    return m_hasValidDatabase;
}
QString Incart::CablesDatabase::DBHelper::getDatabasePath()
{
    return m_databasePath;
}

QJsonObject Incart::CablesDatabase::DBHelper::getCableByType(qint8 type)
{
    m_cablesDatabase.open();
    QSqlQuery getCableQuery(m_cablesDatabase);
    getCableQuery.prepare("SELECT * FROM CabCls WHERE Ctyp = ?;");
    getCableQuery.bindValue(0, type);
    getCableQuery.exec();

    int fieldName = getCableQuery.record().indexOf("CName");
    int fieldPack = getCableQuery.record().indexOf("CPack");
    int fieldSerie = getCableQuery.record().indexOf("CSerie");
    if(getCableQuery.first())
    {
        QString name = getCableQuery.value(fieldName).toString();
        QString serie = getCableQuery.value(fieldSerie).toString();
        qint8 pack = getCableQuery.value(fieldPack).toInt();
        m_cablesDatabase.close();
        return cableToJson(pack, type, name, serie);
    }
    m_cablesDatabase.close();
    return QJsonObject();
}

qint32 Incart::CablesDatabase::DBHelper::getTypeBySerial(QString serial)
{
    m_cablesDatabase.open();
    QSqlQuery getCableQuery(m_cablesDatabase);
    getCableQuery.prepare("SELECT * FROM CabCls WHERE CVin = ?;");
    getCableQuery.bindValue(0, serial);
    getCableQuery.exec();

    int fieldType = getCableQuery.record().indexOf("Ctyp");
    if(getCableQuery.first())
    {
        qint32 type = getCableQuery.value(fieldType).toInt();
        m_cablesDatabase.close();
        return type;
    }
    m_cablesDatabase.close();
    return 0;
}

QJsonArray Incart::CablesDatabase::DBHelper::getCablesByMode(qint8 mode, QString serie)
{
    m_cablesDatabase.open();
    QSqlQuery getCableQuery(m_cablesDatabase);
    getCableQuery.prepare("SELECT * FROM CabCls WHERE CPack = ? AND CSerie = ?;");
    getCableQuery.bindValue(0, mode);
    getCableQuery.bindValue(1, serie);
    getCableQuery.exec();

    int fieldName = getCableQuery.record().indexOf("CName");
    int fieldType = getCableQuery.record().indexOf("Ctyp");

    QJsonArray out;
    while(getCableQuery.next())
    {
        QString name = getCableQuery.value(fieldName).toString();
        qint8 type = getCableQuery.value(fieldType).toInt();
        out.append(cableToJson(mode, type, name, serie));
    }
    m_cablesDatabase.close();
    return out;
}

qint32 Incart::CablesDatabase::DBHelper::getLastNumberByType(qint8 type)
{
    m_cablesDatabase.open();
    QSqlQuery getNumberQuery(m_cablesDatabase);
    getNumberQuery.prepare("SELECT Num FROM CabList WHERE Typ = ? ORDER BY Num DESC;");
    getNumberQuery.bindValue(0, type);
    getNumberQuery.exec();

    int fieldNum = getNumberQuery.record().indexOf("Num");

    qint32 num = 0;
    if(getNumberQuery.first())
    {
        num = getNumberQuery.value(fieldNum).toInt();
    }
    m_cablesDatabase.close();
    return num;
}

QJsonArray Incart::CablesDatabase::DBHelper::getAllCableTypes()
{
    m_cablesDatabase.open();
    QSqlQuery getCableQuery(m_cablesDatabase);
    getCableQuery.prepare("SELECT CTyp, CVin FROM CabCls;");
    getCableQuery.exec();

    int fieldTypeIndex = getCableQuery.record().indexOf("Ctyp");
    int fieldVinIndex =getCableQuery.record().indexOf("CVin");

    QJsonArray out;
    while (getCableQuery.next())
    {
        QString vin = getCableQuery.value(fieldVinIndex).toString();
        int type = getCableQuery.value(fieldTypeIndex).toInt();
        out.append(QJsonObject({
          {"Type", type},
          {"Vin", vin},
        }));
    }
    m_cablesDatabase.close();
    return out;
}

bool Incart::CablesDatabase::DBHelper::isExistNumber(qint8 type, qint32 number)
{
    m_cablesDatabase.open();
    QSqlQuery getCableQuery(m_cablesDatabase);
    getCableQuery.prepare("SELECT * FROM CabList WHERE Num = ? AND Typ = ?;");
    getCableQuery.bindValue(0, number);
    getCableQuery.bindValue(1, type);
    getCableQuery.exec();
    bool isExist = getCableQuery.next();
    m_cablesDatabase.close();
    return isExist;
}

bool Incart::CablesDatabase::DBHelper::addCable(qint8 type)
{
    qint32 num = getLastNumberByType(type);
    QString date = QDateTime::currentDateTime().toString("yyyy-MM-ddThh:mm:ss");

    m_cablesDatabase.open();
    QSqlQuery addCableQuery(m_cablesDatabase);
    addCableQuery.prepare("INSERT INTO CabList (Typ, Num, DateNum) VALUES (?, ?, ?);");
    addCableQuery.bindValue(0, type);
    addCableQuery.bindValue(1, num);
    addCableQuery.bindValue(2, date);
    bool success = addCableQuery.exec();
    m_cablesDatabase.close();
    return success;
}

bool Incart::CablesDatabase::DBHelper::addCable(qint8 type, qint32 number, int numName, int prodName, QString prodDate, QString numDate)
{
   QString numDateFormatted = QDateTime::fromString(numDate, "yyyy-MM-dd-hh-mm-ss").toString("yyyy-MM-ddThh:mm:ss");
   QString prodDateFormatted = QDateTime::fromString(prodDate, "yyyy-MM-dd").toString("yyyy-MM-ddThh:mm:ss");
    qDebug() << numDate << prodDate;
    m_cablesDatabase.open();
    QSqlQuery addCableQuery(m_cablesDatabase);
    addCableQuery.prepare("INSERT INTO CabList (Typ, Num, DateNum, IdNumerator, DateProdact, IdProductor) VALUES (?, ?, ?, ?, ?, ?);");
    addCableQuery.bindValue(0, type);
    addCableQuery.bindValue(1, number);
    addCableQuery.bindValue(2, numDateFormatted);
    addCableQuery.bindValue(3, numName);
    addCableQuery.bindValue(4, prodDateFormatted);
    addCableQuery.bindValue(5, prodName);
    bool success = addCableQuery.exec();
    m_cablesDatabase.close();
    return success;
}

QJsonObject Incart::CablesDatabase::DBHelper::getPeopleList()
{
    m_cablesDatabase.open();
    QSqlQuery findPeopleQuery(m_cablesDatabase);
    findPeopleQuery.exec("SELECT * FROM FioList;");
    int fieldFio = findPeopleQuery.record().indexOf("FIO");
    int fieldId = findPeopleQuery.record().indexOf("ID");
    int fieldRole = findPeopleQuery.record().indexOf("IDrole");
    QJsonArray numerators;
    QJsonArray creators;
    while(findPeopleQuery.next())
    {
        QString fio = findPeopleQuery.value(fieldFio).toString();
        int id = findPeopleQuery.value(fieldId).toInt();
        int role = findPeopleQuery.value(fieldRole).toInt();
        if(role == 0 || role == 1)
        {
            creators.append(QJsonObject({{"id", id}, {"name", fio}}));
        }
        if(role == 0 || role != 1)
        {
            numerators.append(QJsonObject({{"id", id}, {"name", fio}}));
        }
    }
    m_cablesDatabase.close();
    return QJsonObject({{"creators", creators}, {"numerators", numerators}});
}

qint32 Incart::CablesDatabase::DBHelper::getPersonId(QString name)
{
    m_cablesDatabase.open();
    QSqlQuery findPeopleQuery(m_cablesDatabase);
    findPeopleQuery.prepare("SELECT ID FROM FioList WHERE FIO = ?;");
    findPeopleQuery.bindValue(0, name);
    findPeopleQuery.exec();
    int fieldId = findPeopleQuery.record().indexOf("ID");
    qint32 id = 0;
    if(findPeopleQuery.first())
    {
        id = findPeopleQuery.value(fieldId).toInt();
    }
    m_cablesDatabase.close();
    return id;
}

bool Incart::CablesDatabase::DBHelper::addPerson(QString name, QString info)
{
    m_cablesDatabase.open();
    QSqlQuery addPersonQuery(m_cablesDatabase);
    addPersonQuery.prepare("INSERT INTO FioList (FIO, Comment) VALUES (?, ?);");
    addPersonQuery.bindValue(0, name);
    addPersonQuery.bindValue(1, info);
    bool success = addPersonQuery.exec();
    m_cablesDatabase.close();
    return success;
}

qint32 Incart::CablesDatabase::DBHelper::getPeopleNumber(qint32 id, qint32 role, QString dateAfter, QString dateBefore)
{
    QString queryStr;
    if(role == 1)
    {
        queryStr = "SELECT COUNT(*) FROM CabList WHERE IdProductor = ? AND DateProdact BETWEEN ? AND ?;";
    } else
    {
        queryStr = "SELECT COUNT(*) FROM CabList WHERE IdNumerator = ? AND DateNum BETWEEN ? AND ?;";
    }
    m_cablesDatabase.open();
    QSqlQuery getNumberQuery(m_cablesDatabase);
    getNumberQuery.prepare(queryStr);
    getNumberQuery.bindValue(0, id);
    getNumberQuery.bindValue(1, dateAfter);
    getNumberQuery.bindValue(2, dateBefore);
    getNumberQuery.exec();
    int count = 0;
    if(getNumberQuery.first())
    {
        count = getNumberQuery.value(0).toInt();
    }
    m_cablesDatabase.close();
    return count;
}

void Incart::CablesDatabase::DBHelper::getReport(QString date)
{
    QStringList dateParts = date.split("-");
    if(dateParts.size() == 0)
    {
        return;
    }
    int month = dateParts[1].toInt();
    int year = dateParts[0].toInt();
    QDate dateStart = QDate(year, month, 1);
    QDate dateFinish = QDate(year, month, dateStart.daysInMonth());
    QString dateStartStr = dateStart.toString("yyyy-MM-dd") + "T00:00:00";
    QString dateFinishStr = dateFinish.toString("yyyy-MM-dd") + "T23:59:59";

    QJsonObject peopleList = getPeopleList();
    QJsonArray creators = peopleList.find("creators")->toArray();
    QJsonArray numerators = peopleList.find("numerators")->toArray();
    QFile report("report.csv");
    report.open(QIODevice::WriteOnly);
    QTextStream out;
    out.setDevice(&report);
    //out.setCodec("UTF-8");
    out << QString("Изготовители:\n").toUtf8();
    for(QJsonValue val : creators)
    {
        QJsonObject currentPerson = val.toObject();
        QString fio = currentPerson.find("name")->toString();
        int id = currentPerson.find("id")->toInt();
        int count = getPeopleNumber(id, 1, dateStartStr, dateFinishStr);
        if(count != 0)
        {
            out << QString(fio).toUtf8() << ";" << QString::number(count) << "\n";
        }
    }
    out << QString("Нумеровщики:\n").toUtf8();
    for(QJsonValue val : numerators)
    {
        QJsonObject currentPerson = val.toObject();
        QString fio = currentPerson.find("name")->toString();
        int id = currentPerson.find("id")->toInt();
        int count = getPeopleNumber(id, 2, dateStartStr, dateFinishStr);
        if(count != 0)
        {
            out << QString(fio).toUtf8() << ";" << QString::number(count) << "\n";
        }
    }
    report.close();
}