#include "DBData.h"
#include "NuoscParam.h"
#include <cstdio>



DBData::DBData()
    :fMysql(0)
{
}

DBData::DBData(const char* host, const char* user, const char* db, 
               const char* password)
    :fMysql(0)
{
    assert(this->Init(host,user,db,password));
}

DBData::~DBData()
{
    this->Close();
}

void DBData::Close(void)
{
    mysql_close(fMysql);
    fMysql=0;
}

bool DBData::Init(const char* host, const char* user, const char* db, 
                  const char* password)
{
    if (fMysql) this->Close();

    fMysql = mysql_init(0);
    if (!fMysql) {
        cerr << "Can't initialize MySQL\n";
        return false;
    }
    else {
        cerr << "mysql " << user << "@" << host << ":" << db << " accessed\n";
    }
    if (!mysql_real_connect(fMysql,host,user,password,db,0,0,0)) {
        cerr << "Can't connect to MySQL db " << db << ", "
             << user << "@" << host << endl;
        cerr << "MySQL error: " << mysql_error(fMysql) << endl;
        this->Close();
        return false;
    }
    return true;
}
    
bool DBData::AddXsec(int id, vector<double>& evec, vector<double>& xvec,
                     const char* description)
{
    char buf[1024];

    snprintf(buf,1023,"DELETE FROM xsec WHERE id = %d;",id);
    mysql_query(fMysql,buf);

    int siz = evec.size();
    for (int i=0; i<siz; ++i) {
        snprintf(buf,1023,"INSERT INTO xsec VALUES(%d,%f,%f);",
                 id,evec[i],xvec[i]);
        if (mysql_query(fMysql,buf)) {
            cerr << "Failed to insert " << id << ": " 
                 << evec[i] << ", " << xvec[i] << endl;
            cerr << "MySQL error: " << mysql_error(fMysql) << endl;
            return false;
        }
    }
    snprintf(buf,1023,"DELETE FROM xsecdescr WHERE id = %d;",id);
    mysql_query(fMysql,buf);

    snprintf(buf,1023,"INSERT INTO xsecdescr VALUES(%d,\"%s\");",
             id,description);

    if (mysql_query(fMysql,buf)) {
        cerr << "Failed to insert desc for #" << id << ": "
             << description << endl;
        cerr << "MySQL error: " << mysql_error(fMysql) << endl;
        return false;
    }
    return true;
}

bool DBData::GetXsec(int id, vector<double>& evec, vector<double>& xvec,
                     char* description)
{
    char buf[1024];
    snprintf(buf,1023,
             "SELECT energy,xsec FROM xsec WHERE id = %d ORDER BY energy;",
             id);
    if (mysql_query(fMysql,buf)) {
        cerr << "Failed to get cross sections for id #" << id << endl;
        return false;
    }

    MYSQL_RES* res;
    if (!(res = mysql_store_result(fMysql))) {
        cerr << "Failed to store result cross sections for id #" << id << endl;
        return false;
    }
    MYSQL_ROW row;
    while ((row = mysql_fetch_row(res))) {
        evec.push_back(atof(*row));
        ++row;
        xvec.push_back(atof(*row));
    }
    mysql_free_result(res);

    snprintf(buf,1023, "SELECT descr FROM xsecdescr WHERE id = %d;", id);
    if (mysql_query(fMysql,buf)) {
        description[0] = '\0';
        return true;
    }
    if (!(res = mysql_store_result(fMysql))) return false;
    row = mysql_fetch_row(res);
    sprintf(description,"%s",*row);
    mysql_free_result(res);

    return true;
}

bool DBData::GetXsecDescr(vector<int>& id, vector<string>& descr)
{
    if (mysql_query(fMysql,"SELECT id,descr FROM xsecdescr ORDER BY id;" )) {
        cerr << "Failed to get xsec descriptions\n";
        return false;
    }

    MYSQL_RES* res;    
    if (!(res = mysql_store_result(fMysql))) {
        cerr << "Failed to get result set xsec descriptions\n";
        return false;
    }

    MYSQL_ROW row;
    while ((row = mysql_fetch_row(res))) {
        id.push_back(atoi(*row));
        ++row;
        descr.push_back(*row);
        ++row;
    }

    mysql_free_result(res);
    
    return true;
}

int DBData::AddNuoscProb(int id, const NuoscParam& np,
                         vector<double>& E,
                         vector<double>& Pnue,
                         vector<double>& Pnumu,
                         vector<double>& Pnutau)
{
    // add to params table
    char buf[1024];
    if (id) 
        snprintf(buf,1023, 
                 "INSERT INTO params "
                 "VALUES(%d,%d,%d,%f,%f,%f,%f,%f,%f,%f)",
                 id,
                 np.type,(np.matter?1:0),
                 np.baseline,
                 np.ss2t12,np.ss2t23,np.ss2t13,np.cpphase,
                 np.dm2sol,np.dm2atm);
    else
        snprintf(buf,1023, 
                 "INSERT INTO params "
                 "VALUES(NULL,%d,%d,%f,%f,%f,%f,%f,%f,%f)",
                 np.type,(np.matter?1:0),
                 np.baseline,
                 np.ss2t12,np.ss2t23,np.ss2t13,np.cpphase,
                 np.dm2sol,np.dm2atm);

    if (mysql_query(fMysql,buf)) {
        cerr << "Failed to insert NuoscParam\n";
        return 0;
    }
    id = mysql_insert_id(fMysql);

    // create and fill probability table
    int siz = E.size();
    for (int i = 0; i < siz; ++i) {
        snprintf(buf,1023,
                 "INSERT INTO prob VALUES ( last_insert_id() ,%f,%f,%f,%f );",
                 E[i], Pnue[i], Pnumu[i], Pnutau[i]);
        if (mysql_query(fMysql,buf)) {
            cerr << "Failed to insert probabilities\n";
            return 0;
        }
    }
    return id;
}

bool DBData::GetNuoscProb(int id, NuoscParam& np, 
                          vector<double>& E, 
                          vector<double>& Pnue,
                          vector<double>& Pnumu,
                          vector<double>& Pnutau)
{
    char buf[1024];
    snprintf(buf,1023,
             "SELECT "
             "type,matter,baseline,ss2t12,ss2t23,ss2t13,cpphase,dm2sol,dm2atm "
             "FROM params WHERE id = %d;", id);
    if (mysql_query(fMysql,buf)) {
        cerr << "Failed to get param id #" << id << endl;
        return false;
    }

    MYSQL_RES* res;
    if (!(res = mysql_store_result(fMysql))) {
        cerr << "Failed to store result set for params #" << id << endl;
        return false;
    }
    
    MYSQL_ROW row;

    while ((row = mysql_fetch_row(res))) {
        np.type = atoi(*row); ++row;
        np.matter = atoi(*row); ++row;
        np.baseline = atof(*row); ++row;
        np.ss2t12 = atof(*row); ++row;
        np.ss2t23 = atof(*row); ++row;
        np.ss2t13 = atof(*row); ++row;
        np.dm2sol = atof(*row); ++row;
        np.dm2atm = atof(*row); ++row;
    }
    mysql_free_result(res);

    snprintf(buf,1023,
             "select energy,pnue,pnumu,pnutau "
             "from prob where id=%d order by energy;",
             id);
    if (mysql_query(fMysql,buf)) {
        cerr << "Failed to find probabilites associated with parameter set\n";
        cerr << "query:\n" << buf <<endl;
        return false;
    }

    if (!(res = mysql_store_result(fMysql))) {
        cerr << "Failed to get result set of probabilities\n";
        return false;
    }

    while ((row = mysql_fetch_row(res))) {
        E.push_back(atof(*row));
        ++row;
        Pnue.push_back(atof(*row));
        ++row;
        Pnumu.push_back(atof(*row));
        ++row;
        Pnutau.push_back(atof(*row));
    }

    mysql_free_result(res);

    return true;
}
bool DBData::GetNuoscProb(const NuoscParam& np, 
                          vector<double>& E, 
                          vector<double>& Pnue,
                          vector<double>& Pnumu,
                          vector<double>& Pnutau)
{
    char buf[1024];
    snprintf(buf,1023,
             "select @theid:=id from params where type = %d and "
             "matter = %d and "
             "baseline = %.0f and "
             "ss2t12 <= %f and ss2t12 >= %f and "
             "ss2t23 <= %f and ss2t23 >= %f and "
             "ss2t13 <= %f and ss2t13 >= %f and "
             "cpphase <= %f and cpphase >= %f and "
             "dm2sol <= %f and dm2sol >= %f and "
             "dm2atm <= %f and dm2atm >= %f ;",
             np.type,
             np.matter,
             np.baseline,
             np.ss2t12*1.01,np.ss2t12*0.99,
             np.ss2t23*1.01,np.ss2t23*0.99,
             np.ss2t13*1.01,np.ss2t13*0.99,
             np.cpphase*1.01,np.cpphase*0.99,
             np.dm2sol*1.01,np.dm2sol*0.99,
             np.dm2atm*1.01,np.dm2atm*0.99);

    if (mysql_query(fMysql,buf)) {
        cerr << "Failed to find parameter set.  Query:\n";
        cerr << buf << endl;
        return false;
    }

    MYSQL_RES* res;
    if (!(res = mysql_store_result(fMysql))) {
        cerr << "Failed to get result set for id number\n";
        return false;
    }
    
    MYSQL_ROW row;
    row = mysql_fetch_row(res);
    int id = atoi(*row); 
    mysql_free_result(res);

    snprintf(buf,1023,
             "select energy,pnue,pnumu,pnutau "
             "from prob where id=%d order by energy;",
             id);
    if (mysql_query(fMysql,buf)) {
        cerr << "Failed to find probabilites associated with parameter set\n";
        cerr << "query:\n" << buf <<endl;
        return false;
    }

    if (!(res = mysql_store_result(fMysql))) {
        cerr << "Failed to get result set of probabilities\n";
        return false;
    }

    while ((row = mysql_fetch_row(res))) {
        E.push_back(atof(*row));
        ++row;
        Pnue.push_back(atof(*row));
        ++row;
        Pnumu.push_back(atof(*row));
        ++row;
        Pnutau.push_back(atof(*row));
    }

    mysql_free_result(res);
    
    return true;
}

bool DBData::GetNuoscParam(vector<int>& id, vector<NuoscParam>& params)
{

    if (mysql_query(fMysql,"SELECT type,matter,baseline,ss2t12,ss2t23,ss2t13,cpphase,dm2sol,dm2atm FROM params order by type,baseline,matter,dm2sol,dm2atm,ss2t12,ss2t23,ss2t13;")) {
        cerr << "Failed to get nosc params\n";
        return false;
    }

    MYSQL_RES* res;    
    if (!(res = mysql_store_result(fMysql))) {
        cerr << "Failed to get result set nuosc params\n";
        return false;
    }

    MYSQL_ROW row;
    while ((row = mysql_fetch_row(res))) {
        // to do
    }

    mysql_free_result(res);
    
    return true;
}