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

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 "\
                          "SELECT data_key, b_yr, b_day, b_msec, e_yr, e_day, e_msec, size, 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, _data_sources [i],
                          _btime->GetYear (), _btime->GetDay (), _btime->GetMSec (),
                          _etime->GetYear (), _etime->GetDay (), _etime->GetMSec (), max_number);

        dbQuery (tmp_str);
    }

    dbQuery ("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_date TIMESTAMP, "\
             "PRIMARY KEY (data_key, b_yr, b_day, b_msec), "\
             "INDEX (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_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);
        dbQuery (tmp_str);
        sprintf (tmp_str, "DROP TABLE t%d", i);
        dbQuery (tmp_str);
    }

    dbQuery ("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);
    dbQuery ("DROP TABLE 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, _data_sources [_src_in_use],
                        _btime->GetYear (), _btime->GetDay (), _btime->GetMSec (),
                        _etime->GetYear (), _etime->GetDay (), _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];
    SDDAS_INT count = 0;
    MYSQL_RES *result;
    MYSQL_ROW row;

    do {

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

        if ((result = (MYSQL_RES *) queryFunc (this, whichDB, max_number)) != NULL) {
            while ((row = mysql_fetch_row (result))) {

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

                Entry *entry = new Entry ();
                entry->SetSource (data_key);
                entry->SetVirtualName (dbVirtualName (data_key));
                entry->SetBTime (atoi (row [0]), atoi (row [1]), atoi (row [2]));
                entry->SetETime (atoi (row [3]), atoi (row [4]), atoi (row [5]));
                entry->FillEntry (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 (mysql_num_rows (result) == 0) {
                if (beenHere == false) {

// this free was causing problems on the web

                    dbFreeResult (result);
                    result = NULL;

                    if (strlen (dbErrorMsg ()) == 0) {
                        strcpy (_error_msg, "No data found!");
                        std::cerr << _error_msg << std::endl;
                    } else {
                        strcpy (_error_msg, dbErrorMsg ());
                        std::cerr << dbErrorMsg () << std::endl;
                    }
                    if (AutoGetMeta (data_key) == false) {
                        complete = true;
                        _haveMeta = DB_ERROR;
                        count--;
                    }
                    beenHere = true;  // we will try one more time
                } else complete = true;
            } else complete = true;

// this free was causing problems on the web

            if (result != NULL) dbFreeResult (result);

        } else {
            if (strlen (dbErrorMsg ()) == 0) {
                strcpy (_error_msg, "No data found!");
                std::cerr << _error_msg << std::endl;
            } else {
                strcpy (_error_msg, dbErrorMsg ());
                std::cerr << dbErrorMsg () << std::endl;
            }
            complete = true;
        }
    } 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 the source is invalid

    if (_data_sources.size () == 1) {
        std::cerr << "libPromote/BuildDBIntersectionList: You need 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_) {
        if (_haveMeta != DB_ERROR) 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 ();

        E->MakePIDFEntry (_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;
    _haveMeta = DB_GOOD;      // assume meta data exists

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

        _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 ();

            E->MakePIDFEntry (_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);
}