/////////////////////////////////////////////////////////////////////////////
//
// New database routines
//
/////////////////////////////////////////////////////////////////////////////

#if !defined(MYSQL_DB) && !defined(SQLITE_DB) 
void *
Promote::RegularQueryCallback (Promote *obj, char *whichDB, int max_number)
{
    return (obj->RegularQuery (whichDB, max_number));
}

void *
Promote::IntersectionQueryCallback (Promote *obj, char *whichDB, int max_number)
{
    return (obj->IntersectionQuery (whichDB, max_number));
}


void *
Promote::IntersectionQuery (char *whichDB, int max_number)
{
    char tmp_str [1024];

    for (unsigned int i = 0; i < _data_sources.size (); i++) {
        memset (tmp_str, 0, sizeof (tmp_str));
        sprintf (tmp_str, "CREATE TEMPORARY TABLE t%d "\
                          "AS SELECT data_key, b_yr, b_day, b_msec, e_yr, e_day, e_msec, size, "\
                          "archive_label, archive_date "\
                          "FROM %s "\
                          "WHERE DATA_KEY=%ld AND "\
                          "BETWEEN_TIMES(b_yr, b_day, b_msec, e_yr, e_day, e_msec, "\
                                        "%d, %d, %ld, %d, %d, %ld) > 0 "\
                          "LIMIT %d;",
                          i, whichDB, (long) _data_sources [i],
                          (int) _btime->GetYear (), (int) _btime->GetDay (), (long) _btime->GetMSec (),
                          (int) _etime->GetYear (), (int) _etime->GetDay (), (long) _etime->GetMSec (), max_number);

        dbQueryExec (tmp_str);
    }

    dbQueryExec ("CREATE TEMPORARY TABLE answer ("\
             "data_key INT NOT NULL, "\
             "b_yr YEAR NOT NULL, "\
             "b_day SMALLINT UNSIGNED NOT NULL, "\
             "b_msec INT UNSIGNED NOT NULL, "\
             "e_yr YEAR NOT NULL, "\
             "e_day SMALLINT UNSIGNED NOT NULL, "\
             "e_msec INT UNSIGNED NOT NULL, "\
             "size MEDIUMINT UNSIGNED, "\
             "archive_label CHAR, "\
             "archive_date TIMESTAMP, "\
             "PRIMARY KEY (data_key, b_yr, b_day, b_msec) "\
             ");");
    dbQueryExec ("CREATE INDEX index_answer ON answer (data_key, b_yr, b_day, b_msec);");

    for (unsigned int i = 1; i < _data_sources.size (); i++) {
        sprintf (tmp_str, "INSERT INTO answer "\
                          "SELECT DISTINCT t0.data_key, t0.b_yr, t0.b_day, t0.b_msec, "\
                                          "t0.e_yr, t0.e_day, t0.e_msec, t0.size, "\
                                          "t0.archive_label, t0.archive_date "\
                          "FROM t0,t%d "\
                          "WHERE BETWEEN_TIMES(t0.b_yr, t0.b_day, t0.b_msec, t0.e_yr, t0.e_day, t0.e_msec,"\
                                              "t%d.b_yr, t%d.b_day, t%d.b_msec, "\
                                              "t%d.e_yr, t%d.e_day, t%d.e_msec) > 0;",
                          i, i, i, i, i, i, i);
        dbQueryExec (tmp_str);
        sprintf (tmp_str, "DROP TABLE t%d", i);
        dbQueryExec (tmp_str);
    }

    dbQueryExec ("DROP TABLE t0");
    strcpy (tmp_str, "SELECT b_yr, b_day, b_msec, e_yr, e_day, e_msec FROM answer;");
    void *ret_val = dbQueryStore (tmp_str);
    dbQueryExec ("DROP TABLE answer");
    dbQueryExec ("DROP INDEX index_answer");

    return (ret_val);
}

void *
Promote::RegularQuery (char *whichDB, int max_number)
{
    char query_str [1024];
    sprintf (query_str, "SELECT b_yr, b_day, b_msec, e_yr, e_day, e_msec FROM %s WHERE data_key=%ld "\
                        "AND BETWEEN_TIMES(b_yr, b_day, b_msec, e_yr, e_day, e_msec, "\
                                          "%d, %d, %ld, %d, %d, %ld) > 0 "\
                        "LIMIT %d;",
                        whichDB, (long) _data_sources [_src_in_use],
                        (int) _btime->GetYear (), (int) _btime->GetDay (), (long) _btime->GetMSec (),
                        (int) _etime->GetYear (), (int) _etime->GetDay (), (long) _etime->GetMSec (), max_number);

    void *ret_val = dbQueryStore (query_str);

    return ret_val;
}

unsigned int
Promote::MakeList (int fileType, FileStatusType status, int max_number, queryProcType queryFunc)
{

    char whichDB [20];

    switch (fileType) {
        case _HD_FILE_ :
        case _D_FILE_  :
        case _H_FILE_  : strcpy (whichDB, "hd_entries");
                         break;
        case _V3_FILE_ :
        case _I_FILE_  : strcpy (whichDB, "v_entries");
                         break;
    } /* switch */

// this is where we do our query to the data base

    bool complete = false;
    bool beenHere = false;
    SDDAS_ULONG data_key = _data_sources [_src_in_use];
    unsigned int count = 0;
    void *result;
    char **row;
    char *vinst = dbVirtualName (data_key);

    do {

// We use the store result here since we need to access the database when doing the entry

        if ((result = (void *) queryFunc (this, whichDB, max_number)) != NULL) {
            while ((row = (char **)dbFetchRow (result))) {

// We found some entries, so lets add them to our list

                Entry *entry = new Entry ();
                entry->SetSource (data_key);
                entry->SetVirtualName (vinst);
                entry->SetBTime (atoi (row [0]), atoi (row [1]), atoi (row [2]));
                entry->SetETime (atoi (row [3]), atoi (row [4]), atoi (row [5]));
                if(dbIsLocal()) 
                   entry->FillEntry (fileType);
                else
                   entry->FillRemoteEntry(fileType);

                if (entry->GetStatus () == BAD) {
                    _haveMeta = DB_ERROR; // no meta
                    break;
                }

                if ((status == EITHER) || (status == entry->GetStatus ())) {
                    _entryList.push_back (entry);
                    _ServerUsed.push_back (_src_in_use);
                    count++;
                }

// if the user asked for both H & D files, lets save some time and convert the current
// entry to the other type and add it to our list

                if (fileType == _HD_FILE_) {
                    Entry *e2 = entry->ConvertEntry ();
                    if ((status == EITHER) || (status == e2->GetStatus ())) {
                        _entryList.push_back (e2);
                        _ServerUsed.push_back (_src_in_use);
                        count++;
                    } else delete e2;
                }

// can't delete it earlier since we need it to convert

                if ((status != EITHER) && (status != entry->GetStatus ())) delete entry;
            }
            if (dbNumberRows (result) == 0) {
                if (beenHere == false) {
                    dbFreeResult (result);
                    result = NULL;
                    strcpy (_error_msg, "ERROR (no data, trying to promote it): ");
                    strcat (_error_msg, dbErrorMsg ());
                    if (AutoGetMeta (data_key) == sFalse) {
                        _haveMeta = DB_ERROR; // no meta
                        complete = true;  // failed to get meta data, abort...
                    }
                    beenHere = true;  // we will try one more time
                } else {
                    complete = true;  // tried again, still failed
                    _haveMeta = DB_NO_DATA; // no meta
                }
            } else complete = true;   // worked!
        } else {
            std::cerr << "Promote::MakeList: " << dbErrorMsg () << std::endl;
            complete = true;
        }
        if (result != NULL) dbFreeResult (result);
    } while (complete == false);

    return (count);
}

// Send in the type of file we are looking for, status = online/offline/either
// maximum number to promote

SDDAS_INT
Promote::BuildDBIntersectionList (int IDFSType, FileStatusType status, int max_number)
{

    if (_data_sources.size () == 1) {
        std::cerr << "You must choose more than one source!" << std::endl;
        return -1; 
    }

// break only when we are done and we haven't gone past max files to promote

    SDDAS_INT count = 0;
    _haveMeta = DB_GOOD;  // assume meta data exists
    _src_in_use = 0;

    if (IDFSType & _V3_FILE_) {
        count += MakeList (_V3_FILE_, status, max_number, IntersectionQueryCallback);
    }
    if (IDFSType & _I_FILE_) {
        if (_haveMeta != DB_ERROR) count += MakeList (_I_FILE_, status, max_number, IntersectionQueryCallback);
    }
    if ((IDFSType & _H_FILE_) && (IDFSType & _D_FILE_)) {
        if (_haveMeta != DB_ERROR) count += MakeList (_HD_FILE_, status, max_number, IntersectionQueryCallback);
    }
        else

// If we don't need both, we need either one or none, but they use the same database

    if ((IDFSType & _H_FILE_) || (IDFSType & _D_FILE_)) {
        if (IDFSType & _H_FILE_) {
            if (_haveMeta != DB_ERROR) count += MakeList (_H_FILE_, status, max_number, IntersectionQueryCallback);
        }
        if (IDFSType & _D_FILE_) {
            if (_haveMeta != DB_ERROR) count += MakeList (_D_FILE_, status, max_number, IntersectionQueryCallback);
        }
    }
    if (IDFSType & _P_FILE_) {
        Entry *E = new Entry ();

        if(dbIsLocal())
           E->MakePIDFEntry (_data_sources [_src_in_use]);
        else
           E->MakeRemotePIDFEntry (_data_sources [_src_in_use]);
        if ((status == EITHER) || (E->GetStatus () == status)) {
            _entryList.insert (_entryList.begin (), E);
            _ServerUsed.insert (_ServerUsed.begin (), _src_in_use);
            count++;
        } else delete E;
    }

    return (count);
}

// Send in the type of file we are looking for, status = online/offline/either
// maximum number to promote

SDDAS_INT
Promote::SQL_BuildDBList (int IDFSType, FileStatusType status, int max_number)
{

// go through all our sources and build a list
// break only when we are done and we haven't gone past max files to promote

    unsigned int src_num = 0;
    SDDAS_INT count = 0;

    while ((count < max_number) && (src_num < _data_sources.size ())) {

        _haveMeta = DB_GOOD; // assume meta data is available for the source until we find out otherwise
        _src_in_use = src_num;

        if (IDFSType & _V3_FILE_) {
            if (_haveMeta != DB_ERROR) count += MakeList (_V3_FILE_, status, max_number, RegularQueryCallback);
        }
        if (IDFSType & _I_FILE_) {
            if (_haveMeta != DB_ERROR) count += MakeList (_I_FILE_, status, max_number, RegularQueryCallback);
        }
        if ((IDFSType & _H_FILE_) && (IDFSType & _D_FILE_)) {
            if (_haveMeta != DB_ERROR) count += MakeList (_HD_FILE_, status, max_number, RegularQueryCallback);
        }
            else

// If we don't need both, we need either one or none, but they use the same database

        if ((IDFSType & _H_FILE_) || (IDFSType & _D_FILE_)) {
            if (IDFSType & _H_FILE_) {
                if (_haveMeta != DB_ERROR) count += MakeList (_H_FILE_, status, max_number, RegularQueryCallback);
            }
            if (IDFSType & _D_FILE_) {
                if (_haveMeta != DB_ERROR) count += MakeList (_D_FILE_, status, max_number, RegularQueryCallback);
            }
        }
        if (IDFSType & _P_FILE_) {
            Entry *E = new Entry ();

            if(dbIsLocal())
              E->MakePIDFEntry (_data_sources [_src_in_use]);
            else
              E->MakeRemotePIDFEntry (_data_sources [_src_in_use]);
            if ((status == EITHER) || (E->GetStatus () == status)) {
                _entryList.insert (_entryList.begin (), E);
                _ServerUsed.insert (_ServerUsed.begin (), _src_in_use);
                count++;
            } else delete E;
        }
        src_num++;
    } // while

    return (count);
}
#endif

#if defined(MYSQL_DB) || defined(ALL_DB) 

Promote::DBPromoteStatus
Promote::PromoteMetaForSource_MYSQL (char *which_server,
                                     SDDAS_ULONG local_data_key, char *proj,
                                     char *miss, char *exp,
                                     char *inst, char *vinst)
{
    char pBuf [MAX_IDFS_NAME],
         mBuf [MAX_IDFS_NAME],
         eBuf [MAX_IDFS_NAME],
         iBuf [MAX_IDFS_NAME],
         vBuf [MAX_IDFS_VIRT_NAME];
      
    char query_str[1024];
    char whichDB [20];

    int num_rows = 0;

// We need either a data key or a hierarchy for this routine to work

    assert ((proj == NULL && local_data_key != 0) || (local_data_key == 0 && proj != NULL));

// If we do not know our hierarchy, we need to get it, otherwise get the data key

    if (proj == NULL) {

        if (dbKeyToStrings (local_data_key, pBuf, mBuf, eBuf, iBuf, vBuf) != sTrue) return Promote::DB_ERROR;
        proj = pBuf;
        miss = mBuf;
        exp = eBuf;
        inst = iBuf;
        vinst = vBuf;
    } else {
        local_data_key = dbGetDataKey (proj, miss, exp, inst, vinst);
    }

    try {
        _mirror = new MirrorDatabase (which_server, "idfs");
        _mirror->BeginTransaction ();

        for (int i = 0; i < 2; i++) {

            switch (i) {
                case 0 : strcpy (whichDB, "v_entries"); break;
                case 1 : strcpy (whichDB, "hd_entries");
            } // switch

            sprintf (query_str, "SELECT b_yr, b_day, b_msec, e_yr, e_day, e_msec, size, "\
                                "archive_label, archive_date "\
                                "FROM hierarchy,%s WHERE hierarchy.data_key=%s.data_key "\
                                "AND project='%s' AND mission='%s' AND experiment='%s' "\
                                "AND instrument='%s' AND virtual='%s' "\
                                "AND BETWEEN_TIMES(b_yr, b_day, b_msec, e_yr, e_day, e_msec, "\
                                              "%d, %d, %ld, %d, %d, %ld) > 0;",
                     whichDB, whichDB, proj, miss, exp, inst, vinst,
                     (int) _btime->GetYear (), (int) _btime->GetDay (), (long) _btime->GetMSec (),
                     (int) _etime->GetYear (), (int) _etime->GetDay (), (long) _etime->GetMSec ());

            _mirror->SetTables (whichDB);
            _mirror->RemoteSetQuery (query_str);
            _mirror->RemoteExecute ();
//            _mirror->LocalAddTable (whichDB);

            num_rows = _mirror->RemoteGetNumberRows ();
//            num_cols = _mirror->RemoteGetNumberFields ();
            
            unsigned int remote_number_rows = _mirror->RemoteGetNumberRows ();
            unsigned int remote_number_fields = _mirror->RemoteGetNumberFields ();

            for (unsigned int row = 0; row < remote_number_rows; row++) {
                _mirror->LocalAddField ("data_key");
                _mirror->LocalAddValue (local_data_key);
                for (unsigned int column = 0; column < remote_number_fields; column++) {
                    _mirror->LocalAddField (_mirror->RemoteGetFieldName (column));
                    _mirror->LocalAddValue (_mirror->RemoteGetFieldValue (column));
/*
 * This was taken out, but it was used to make a progress bar appear

                    count++;
                    if ((count % 100) == 0) {
                        int pct = (int) ((((float) count / (float) (num_rows*num_cols))) * 100.0);
                        if (progress != NULL) {
                            progress->SetValue (pct);
                        }
                    }
                    if ((progress != NULL) && (progress->wasCanceled ())) {
                        delete progress;
                        _mirror->EndRemoteQuery ();
                        _mirror->EndTransaction ();
                        delete _mirror; 
                        return (Promote::DB_ERROR);
                    }
 */
                }
                _mirror->LocalAddRow ();
                _mirror->RemoteNextRow();
            }
            _mirror->EndRemoteQuery ();
        } // for
        _mirror->EndTransaction ();
    } // try
    catch (const char* err_msg) {
        std::cerr << "PromoteMetaForSource_MySQL() Error: " << err_msg << std::endl;
        if (_mirror != NULL) {  // can be NULL since constructor will throw
            _mirror->EndRemoteQuery ();
            _mirror->EndTransaction ();
        } else {  // currently we only use the external method if mirror never connected
// Unfortunately, we don't know if we got data or not!
            if (PromoteMetaForSource_EXTERNAL (which_server, local_data_key) == sFalse)
                return Promote::DB_NO_DATA; 
            else return Promote::DB_GOOD;
        }
    }
    delete _mirror;
    if (num_rows == 0) { // couldn't have promoted anything if there was no rows
        return Promote::DB_NO_DATA; 
    }
    return Promote::DB_GOOD;
}
#endif