#include "ndb.h"




Ndb::Ndb(QObject *parent) : QObject(parent)
{

}



// 添加sqlite数据库
QSqlDatabase Ndb::db = QSqlDatabase::addDatabase("QSQLITE");
QString Ndb::tableName = "tasks";
QSqlQuery Ndb::query = QSqlQuery(Ndb::db);
QString Ndb::DateFormat = "yyyy-MM-dd";
QVector<Field> Ndb::fields = {{"id","int"},
                              {"taskName","str"},
                              {"sort","int"},
                              {"eachDayTomatoes","int"},
                              {"tomatoesTotal","int"},
                              {"tomatoesAchieveTotal","int"},
                              {"subTasks","str"},
                              {"todayAchieveTomato","int"},
                              {"today","str"},
                              {"days","int"},
                              {"startDate","str"},
                              {"describe","str"}};

Ndb::Ndb(QString dbName, QObject *parent): QObject(parent)
{
    //打印数据库驱动
    qDebug() << QSqlDatabase::drivers();
    // 设置数据库
    QStringList pathList;
    pathList << QApplication::applicationDirPath() << dbName;
    this->db.setDatabaseName(pathList.join(QDir::separator()));

    // 打开数据库
    if (!db.open()){
        emit DataBaseOpenError();
        return;
    }


    if (!isTableExist(tableName)){
        createTaskTable(tableName);
    }
    if (!isTableExist("note")){
        createNoteTable();
    }
    if (!isTableExist("tasklog")){
        createLogTable();
    }
    if (!isTableExist("timevec")){
        createTimevecTable();
    }
    if (!isTableExist("subtask")){
        createSubTaskTable();
    }
}

void Ndb::DbOutputCsv(QString fileName)
{
    QString sql;

    QStringList fieldName;
    QStringList lineContent;



    QFile file(fileName);
    if (!file.open(QIODevice::WriteOnly | QFile::Text))
    {
        qDebug() << fileName << " open error!";
    }

    QTextStream out(&file);
    out.setCodec("gbk");

    for (int i=0; i<Ndb::fields.size(); ++i)
    {
        fieldName << Ndb::fields[i]._name;
    }

    sql = QString("select %1 from %2").arg(fieldName.join(',')).arg(Ndb::tableName);
    qDebug() << "query data sql " << sql << " is activate " << Ndb::query.isActive();

    if (!Ndb::query.exec(sql))
    {
         qDebug() << "dataBase output  query data error! " << Ndb::query.lastError();
    }

    out << fieldName.join(',')  << '\n';
    while(Ndb::query.next())
    {
        for(int i=0; i<fieldName.size(); ++i)
        {
            if (fieldName.at(i) == "startDate"){
                lineContent << Ndb::query.value(QString(fieldName.at(i).toLocal8Bit())).toDateTime().toString("yyyy-MM-dd hh:mm:ss");
            } else
                lineContent << Ndb::query.value(QString(fieldName.at(i).toLocal8Bit())).toString().replace(CSVSPLITER,SPLITER_REPLACE);
        }
        out << lineContent.join(CSVSPLITER) << '\n';
        qDebug() << "write " << lineContent.join(',');
        lineContent.clear();
    }

    file.close();
}



bool Ndb::DbInputCsv(QString fileName, const QString &columnName)
{
    QFile file(fileName);
    if (!file.open(QIODevice::ReadOnly | QFile::Text))
    {
        qDebug() << fileName << " open error!";
        return false;
    }


    QTextStream in(&file);

    in.setCodec("gbk");
    in.seek(0);

    QString Titleline;
    QStringList Titlelist;
    // 读入标题
    if (columnName.isEmpty())
    {
        Titleline = in.readLine();
        Titlelist = Titleline.split(CSVSPLITER, QString::SkipEmptyParts);
    } else
    {
        Titleline = columnName;
        Titlelist = Titleline.split(CSVSPLITER, QString::SkipEmptyParts);
    }

    qDebug() << "title line " << Titlelist.join(",");
    // 读入第一行
    auto pos = in.pos();
    QString line = in.readLine();
    QStringList list = line.split(CSVSPLITER);

    qDebug() << "list " << list << "title list " << Titlelist;
    if (list.count() != Titlelist.count())
        return false;


    qDebug() << "set pos " << __LINE__;
    in.seek(pos);


    Ndb::db.transaction();

    //先删除原来的数据
    QString sql = QString("delete from %1").arg(Ndb::tableName);
    Ndb::query.exec(sql);

    QStringList sql_values;
    qDebug() << "prepare read data " << __LINE__;
    while(!in.atEnd())
    {
        line = in.readLine();

        list = line.split(CSVSPLITER);
        for (int i=0; i < list.size(); ++i)
        {
            list[i] = list[i].replace(SPLITER_REPLACE, CSVSPLITER);
        }
        qDebug() << "read data " << list;

        sql = QString("insert into %1(%2) values(%3)").arg(Ndb::tableName).arg(Titlelist.join(","));
        sql_values.clear();
        for (int i=0; i<Titlelist.size(); ++i)
        {
           sql_values << QString(":%1").arg(Titlelist.at(i));
        }
        Ndb::query.clear();
        Ndb::query.prepare(sql.arg(sql_values.join(",")));
        for(int i=0; i<list.size(); ++i)
        {
            QVariant v;
            Titlelist.at(i) == "startDate"? v=QDateTime::fromString(list.at(i), "yyyy-MM-dd hh:mm:ss"):v= list.at(i);
            Ndb::query.bindValue(QString(":%1").arg(Titlelist.at(i)), v);
        }

        if (!Ndb::query.exec())
        {
            qDebug() << "insert error "  << Ndb::query.lastError();
        }


    }

    //提交数据库事务
    
    if (!Ndb::db.commit()) {
        qDebug() << "commit error " << __LINE__;
        Ndb::db.rollback();
        return false;
    }
    file.close();
    return true;
}








bool Ndb::isTableExist(QString table){

    QString sql = QString("select count(*) from sqlite_master where type = 'table' name = '%1'").arg(table);
    if(db.tables().contains(table))
    {
        return true;
    }
  return false;

}

void Ndb::createNoteTable()
{
    QString sql = "create table if not exists note ("
                 "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                 "taskId INTEGER NOT NULL,"
                 "text TEXT,"
                 "datetime DATETIME DEFAULT (datetime('now','localtime'))"
                 ")";
    if (!query.exec(sql))
    {
        qDebug() << "Error: Fail to create table note "  << query.lastError();
    } else {
        qDebug() << "create table note" ;
    }
}

void Ndb::createLogTable()
{
    QString sql = "create table if not exists tasklog ("
            "id INTEGER PRIMARY KEY AUTOINCREMENT,"
            "taskid INT NOT NULL,"
            "taskname VARCHAR(30) NOT NULL,"
            "sort INT NOT NULL,"
            "costTime INT UNSIGNED NOT NULL,"
            "datetime DATETIME DEFAULT(datetime('now','localtime')),"
            "finished BOOLEAN  DEFAULT(true) NOT NULL,"
            "CONSTRAINT fk_task "
            "FOREIGN KEY (taskid) "
            "REFERENCES "+Ndb::tableName+" (id) "
                  ");";
    if(!query.exec(sql))
    {
        qDebug() << "Error: Fail to create table tasklog" << query.lastError();
    }
    else
    {
        qDebug() << "create task table tasklog";
     }
}

void Ndb::createTimevecTable()
{
    QString sql = "create table if not exists timevec ("
            "id INTEGER PRIMARY KEY AUTOINCREMENT,"
            "logid INT NOT NULL,"
            "taskid INT NOT NULL,"
            "starttime TIME NOT NULL,"
            "endtime TIME NOT NULL,"
            "CONSTRAINT fk_log "
            "FOREIGN KEY (logid) "
            "REFERENCES tasklog (id) "
            "on update cascade on delete cascade "
              "CONSTRAINT fk_task "
              "FOREIGN KEY (taskid) "
              "REFERENCES "+Ndb::tableName+" (id)"
                  ");";

    if(!query.exec(sql))
    {
        qDebug() << "Error: Fail to create table timevec" << query.lastError();
    }
    else
    {
        qDebug() << "create task table timevec";
     }

}

int Ndb::addTaskLog(int taskid, const QString taskname, int sort,unsigned int costtime, bool isfinished)
{
    QString sql = "INSERT INTO tasklog(taskid,taskname, sort, costTime, finished) values(:taskid, :taskname, :sort,:costTime, :finished);";
    Ndb::query.prepare(sql);
    Ndb::query.bindValue(":taskid", taskid);
    Ndb::query.bindValue(":taskname", taskname);
    Ndb::query.bindValue(":sort", sort);
    Ndb::query.bindValue(":costTime", costtime);
    Ndb::query.bindValue(":finished", isfinished );

    if (!Ndb::query.exec())
    {
        qDebug() << "db: insert into tasklog error" << Ndb::query.lastError();
        return -1;
    }
    return getLastInsertId("tasklog");

}


void Ndb::addTimeVec(int taskid, int logid, QTime &start_time, QTime &end_time)
{
    QString sql = "INSERT INTO timevec(taskid,logid,starttime, endtime) values(:taskid, :logid, :starttime, :endtime);";
    Ndb::query.prepare(sql);
    Ndb::query.bindValue(":taskid", taskid);
    Ndb::query.bindValue(":logid", logid);
    Ndb::query.bindValue(":starttime", start_time.toString("hh:mm:ss"));
    Ndb::query.bindValue(":endtime", end_time.toString("hh:mm:ss"));

    if (!Ndb::query.exec())
    {
        qDebug() << "db: insert into tasklog error" << Ndb::query.lastError();
        return;
    }

}

int Ndb::addSubTasks(int taskid, QMap<int, subTask> &subTaskMap)
{
//    const QString sql1 = "SELECT id FROM subtask;";
//    Ndb::query.exec(sql1);
//    QSet<int> idSet;
//    while (Ndb::query.next())
//    {
//        idSet.insert(Ndb::query.value("id").toInt());
//    }


    const QString sql = "INSERT INTO subtask(taskid, name, tomato, achieve, describe) "
            "VALUES(:taskid, :name, :tomato, :achieve, :describe);";

    QVariantList taskids;
    QVariantList names;
    QVariantList tomatoes;
    QVariantList achieves;
    QVariantList describes;

    for (subTask &subtask: subTaskMap)
    {
        if (subtask.id < 0)
        {
            taskids << taskid;
            names << subtask.name;
            tomatoes << subtask.tomato;
            achieves << subtask.achieve;
            describes << subtask.describe;
        }
    }
    Ndb::query.prepare(sql);
    Ndb::query.bindValue(":taskid", taskids);
    Ndb::query.bindValue(":name", names);
    Ndb::query.bindValue(":tomato", tomatoes);
    Ndb::query.bindValue(":achieve", achieves);
    Ndb::query.bindValue(":describe", describes);


    if (!Ndb::query.execBatch())
    {
        qDebug() << "batch insert subtask error  " << Ndb::query.lastError();
        return -1;
    }
    return Ndb::query.numRowsAffected();
}

void Ndb::deleteSubTask(int taskid, const QString subname)
{
    const QString sql = "DELETE FROM subtask WHERE taskid=:taskid AND name=:name";

    Ndb::query.prepare(sql);
    Ndb::query.bindValue(":taskid", taskid);
    Ndb::query.bindValue(":name", subname);

    if (!Ndb::query.exec())
    {
        qDebug() << "delete subtask error subtask name " << subname << " " << Ndb::query.lastError();
        return ;
    }
}

void Ndb::deleteSubTask(int subtaskid)
{
    const QString sql = "DELETE FROM subtask WHERE id=:id";

    Ndb::query.prepare(sql);
    Ndb::query.bindValue(":id", subtaskid);


    if (!Ndb::query.exec())
    {
        qDebug() << "delete subtask error subtask id " << subtaskid << " " << Ndb::query.lastError();
        return ;
    }
}

void Ndb::updateSubTask(const subTask &subtask)
{

    const QString sql = "UPDATE subtask SET name=:name, tomato=:tomato, achieve=:achieve, describe=:describe WHERE id=:id;";
    Ndb::query.prepare(sql);
    Ndb::query.bindValue(":id", subtask.id);

    Ndb::query.bindValue(":name", subtask.name);
    Ndb::query.bindValue(":tomato", subtask.tomato);
    Ndb::query.bindValue(":achieve", subtask.achieve);
    Ndb::query.bindValue(":describe", subtask.describe);
    if (!Ndb::query.exec())
    {
        qDebug() << "update subtask id=" << subtask.id << " name=" << subtask.name;
        return;
    }
}
void Ndb::createTaskTable(QString table){
    const QString sql = "create table if not exists "+table+"("
            "id INTEGER PRIMARY KEY AUTOINCREMENT,"
            "taskName VARCHAR(30) NOT NULL,"
            "sort INT UNSIGNED NOT NULL,"
            "eachDayTomatoes INT UNSIGNED NOT NULL,"
            "tomatoesTotal INT UNSIGNED NOT NULL,"
            "tomatoesAchieveTotal INT UNSIGNED NOT NULL,"
            "todayAchieveTomato INT UNSIGNED DEFAULT 0,"
            "today INT UNSIGNED DEFAULT (datetime('now','localtime')),"
            "days INT DEFAULT -1,"
            "startDate TIMESTAMP  DEFAULT (datetime('now','localtime')),"
            "describe TEXT"
            ")";

    if(!query.exec(sql))
    {
        qDebug() << "Error: Fail to create table." << query.lastError();
    }
    else
    {
        qDebug() << "create task table " << table;
     }

}



void Ndb::createSubTaskTable()
{
    QString sql = "create table if not exists subtask ("
            "id INTEGER PRIMARY KEY AUTOINCREMENT,"
            "name VARCHAR(30) NOT NULL,"
            "taskid INT NOT NULL,"
            "tomato INT NOT NULL,"
            "achieve INT NOT NULL,"
            "describe TEXT,"
            "CONSTRAINT fk_task "
            "FOREIGN KEY (taskid) "
            "REFERENCES "+Ndb::tableName+" (id) "
            "on update cascade on delete cascade "
                  ");";

    if(!query.exec(sql))
    {
        qDebug() << "Error: Fail to create table subtask" << query.lastError();
    }
    else
    {
        qDebug() << "create task table subtask";
     }
}



int Ndb::getLastInsertId(const QString &tablename)
{
    QString sql = QString("select seq from sqlite_sequence where name='%1';").arg(tablename);
    query.exec(sql);
    query.next();
    return query.value(0).toInt();
}