/*
 * dettool.c
 *
 * Copyright (C) 2006  Joshua Hoblitt
 *
 * This program is free software; you can redistribute it and/or modify it
 * under the terms of the GNU General Public License as published by the Free
 * Software Foundation; version 2 of the License.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 * FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
 * more details.
 *
 * You should have received a copy of the GNU General Public License along with
 * program; see the file COPYING. If not, write to the Free Software
 * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA.
 */

#ifdef HAVB_CONFIG_H
#include <config.h>
#endif

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <stdint.h>
#include <inttypes.h>

#include <ippdb.h>

#include "pxtools.h"
#include "dettool.h"

static bool pendingMode(pxConfig *config);
static bool definebytagMode(pxConfig *config);
static bool definebyqueryMode(pxConfig *config);
static bool definebydetrunMode(pxConfig *config);
static bool runsMode(pxConfig *config);
static bool childlessrunMode(pxConfig *config);
static bool inputMode(pxConfig *config);
static bool rawMode(pxConfig *config);
// processedimfile
static bool toprocessedimfileMode(pxConfig *config);
static bool addprocessedimfileMode(pxConfig *config);
static bool processedimfileMode(pxConfig *config);
static bool revertprocessedimfileMode(pxConfig *config);
// processedexp
static bool toprocessedexpMode(pxConfig *config);
static bool addprocessedexpMode(pxConfig *config);
static bool processedexpMode(pxConfig *config);
static bool revertprocessedexpMode(pxConfig *config);
// stackedimfile
static bool tostackedMode(pxConfig *config);
static bool addstackedMode(pxConfig *config);
static bool stackedMode(pxConfig *config);
static bool revertstackedMode(pxConfig *config);
// normalizedstat
static bool tonormalizedstatMode(pxConfig *config);
static bool addnormalizedstatMode(pxConfig *config);
static bool normalizedstatMode(pxConfig *config);
static bool revertnormalizedstatMode(pxConfig *config);
// normalizedimfile
static bool tonormalizeMode(pxConfig *config);
static bool addnormalizedimfileMode(pxConfig *config);
static bool normalizedimfileMode(pxConfig *config);
static bool revertnormalizedimfileMode(pxConfig *config);
// normalizedexp
static bool tonormalizedexpMode(pxConfig *config);
static bool addnormalizedexpMode(pxConfig *config);
static bool normalizedexpMode(pxConfig *config);
static bool revertnormalizedexpMode(pxConfig *config);
// residimfile
static bool toresidimfileMode(pxConfig *config);
static bool addresidimfileMode(pxConfig *config);
static bool residimfileMode(pxConfig *config);
static bool revertresidimfileMode(pxConfig *config);
// residexp
static bool toresidexpMode(pxConfig *config);
static bool addresidexpMode(pxConfig *config);
static bool residexpMode(pxConfig *config);
static bool revertresidexpMode(pxConfig *config);
static bool updateresidexpMode(pxConfig *config);
// detrunsummary
static bool todetrunsummaryMode(pxConfig *config);
static bool adddetrunsummaryMode(pxConfig *config);
static bool detrunsummaryMode(pxConfig *config);
static bool revertdetrunsummaryMode(pxConfig *config);
static bool updatedetrunMode(pxConfig *config);
static bool rerunMode(pxConfig *config);
// register
static bool register_detrendMode(pxConfig *config);
static bool register_detrend_imfileMode(pxConfig *config);

static detNormalizedStatImfileRow *detStackedToDetNormalizedStatImfile(pxConfig *config, detStackedImfileRow *stackedImfile);
static  detNormalizedImfileRow *detNormalizedStatToDetNormalizedmfile(pxConfig *config, detNormalizedStatImfileRow *statImfile);
static detResidExpRow *mdToDetResidExp(pxConfig *config, psMetadata *row);
static detRunSummaryRow *mdToDetRunSummary(pxConfig *config, psMetadata *row);
//static psArray *validDetInputClassIds(pxConfig *config, const char *det_id);
//static psArray *searchInputImfiles(pxConfig *config, const char *det_id);
static detInputExpRow *rawDetrenTodetInputExpRow(rawExpRow *rawExp, psS64 det_id, psS32 iteration);
static psArray *searchRawImfiles(pxConfig *config, psMetadata *where);
static bool startNewIteration(pxConfig *config, psS64 det_id);
static psS32 incrementIteration(pxConfig *config, psS64 det_id);
static bool setDetRunState(pxConfig *config, psS64 det_id, const char *state);
static bool isValidMode(pxConfig *config, const char *mode);

# define MODECASE(caseName, func) \
    case caseName: \
    if (!func(config)) { \
        goto FAIL; \
    } \
    break;

int main(int argc, char **argv)
{
    psLibInit(NULL);

    pxConfig *config = dettoolConfig(NULL, argc, argv);
    if (!config) {
        psError(PXTOOLS_ERR_CONFIG, false, "failed to configure");
        goto FAIL;
    }

    switch (config->mode) {
        MODECASE(DETTOOL_MODE_PENDING,          pendingMode);
        MODECASE(DETTOOL_MODE_DEFINEBYTAG,      definebytagMode);
        MODECASE(DETTOOL_MODE_DEFINEBYQUERY,    definebyqueryMode);
        MODECASE(DETTOOL_MODE_DEFINEBYDETRUN,   definebydetrunMode);
        MODECASE(DETTOOL_MODE_RUNS,             runsMode);
        MODECASE(DETTOOL_MODE_CHILDLESSRUN,     childlessrunMode);
        MODECASE(DETTOOL_MODE_INPUT,            inputMode);
        MODECASE(DETTOOL_MODE_RAW,              rawMode);
        // imfile
        MODECASE(DETTOOL_MODE_TOPROCESSEDIMFILE,toprocessedimfileMode);
        MODECASE(DETTOOL_MODE_ADDPROCESSEDIMFILE,addprocessedimfileMode);
        MODECASE(DETTOOL_MODE_PROCESSEDIMFILE,  processedimfileMode);
        MODECASE(DETTOOL_MODE_REVERTPROCESSEDIMFILE, revertprocessedimfileMode);
        // exp
        MODECASE(DETTOOL_MODE_TOPROCESSEDEXP,   toprocessedexpMode);
        MODECASE(DETTOOL_MODE_ADDPROCESSEDEXP,  addprocessedexpMode);
        MODECASE(DETTOOL_MODE_PROCESSEDEXP,     processedexpMode);
        MODECASE(DETTOOL_MODE_REVERTPROCESSEDEXP, revertprocessedexpMode);
        // stacked
        MODECASE(DETTOOL_MODE_TOSTACKED,        tostackedMode);
        MODECASE(DETTOOL_MODE_ADDSTACKED,       addstackedMode);
        MODECASE(DETTOOL_MODE_STACKED,          stackedMode);
        MODECASE(DETTOOL_MODE_REVERTSTACKED,    revertstackedMode);
        // normalizedstat
        MODECASE(DETTOOL_MODE_TONORMALIZEDSTAT, tonormalizedstatMode);
        MODECASE(DETTOOL_MODE_ADDNORMALIZEDSTAT,addnormalizedstatMode);
        MODECASE(DETTOOL_MODE_NORMALIZEDSTAT,   normalizedstatMode);
        MODECASE(DETTOOL_MODE_REVERTNORMALIZEDSTAT, revertnormalizedstatMode);
        // normalizedimfile
        MODECASE(DETTOOL_MODE_TONORMALIZE,      tonormalizeMode);
        MODECASE(DETTOOL_MODE_ADDNORMALIZEDIMFILE,addnormalizedimfileMode);
        MODECASE(DETTOOL_MODE_NORMALIZEDIMFILE, normalizedimfileMode);
        MODECASE(DETTOOL_MODE_REVERTNORMALIZEDIMFILE, revertnormalizedimfileMode);
        // normalizedexp
        MODECASE(DETTOOL_MODE_TONORMALIZEDEXP,  tonormalizedexpMode);
        MODECASE(DETTOOL_MODE_ADDNORMALIZEDEXP, addnormalizedexpMode);
        MODECASE(DETTOOL_MODE_NORMALIZEDEXP,    normalizedexpMode);
        MODECASE(DETTOOL_MODE_REVERTNORMALIZEDEXP, revertnormalizedexpMode);
        // residimfile
        MODECASE(DETTOOL_MODE_TORESIDIMFILE,    toresidimfileMode);
        MODECASE(DETTOOL_MODE_ADDRESIDIMFILE,   addresidimfileMode);
        MODECASE(DETTOOL_MODE_RESIDIMFILE,      residimfileMode);
        MODECASE(DETTOOL_MODE_REVERTRESIDIMFILE,revertresidimfileMode);
        // residexp
        MODECASE(DETTOOL_MODE_TORESIDEXP,       toresidexpMode);
        MODECASE(DETTOOL_MODE_ADDRESIDEXP,      addresidexpMode);
        MODECASE(DETTOOL_MODE_RESIDEXP,         residexpMode);
        MODECASE(DETTOOL_MODE_REVERTRESIDEXP,   revertresidexpMode);
        MODECASE(DETTOOL_MODE_UPDATERESIDEXP,   updateresidexpMode);
        // detrunsummary
        MODECASE(DETTOOL_MODE_TODETRUNSUMMARY,  todetrunsummaryMode);
        MODECASE(DETTOOL_MODE_ADDDETRUNSUMMARY, adddetrunsummaryMode);
        MODECASE(DETTOOL_MODE_DETRUNSUMMARY,    detrunsummaryMode);
        MODECASE(DETTOOL_MODE_REVERTDETRUNSUMMARY, revertdetrunsummaryMode);
        MODECASE(DETTOOL_MODE_UPDATEDETRUN,     updatedetrunMode);
        MODECASE(DETTOOL_MODE_RERUN,            rerunMode);
        // register
        MODECASE(DETTOOL_MODE_REGISTER_DETREND, register_detrendMode);
        MODECASE(DETTOOL_MODE_REGISTER_DETREND_IMFILE, register_detrend_imfileMode);
        default:
            psAbort("invalid option (this should not happen)");
    }

    psFree(config);
    pmConfigDone();
    psLibFinalize();

    exit(EXIT_SUCCESS);

FAIL:
    psErrorStackPrint(stderr, "\n");
    int exit_status = pxerrorGetExitStatus();

    psFree(config);
    pmConfigDone();
    psLibFinalize();

    exit(exit_status);
}

static bool pendingMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);

     psString query = psStringCopy(
        "SELECT"
        "   rawExp.*"
        " FROM rawExp"
        " LEFT JOIN detInputExp"
        "   ON rawExp.exp_id = detInputExp.exp_id"
        " WHERE"
        "    detInputExp.exp_id IS NULL"
        "    AND rawExp.obstype != 'object'"
    );

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "rawExp");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "rawExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool definebytagMode(pxConfig *config)
{
    bool status     = false;

    PS_ASSERT_PTR_NON_NULL(config, false);

    // what type of detRun is this?
    // det_type is required
    psString det_type = psMetadataLookupStr(&status, config->args, "-det_type");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_type");
        return false;
    }
    if (!det_type) {
        psError(PS_ERR_UNKNOWN, true, "-det_type is required");
        return false;
    }

    // optional
    psString filelevel = psMetadataLookupStr(&status, config->args, "-filelevel");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filelevel");
        return false;
    }

    psString workdir = psMetadataLookupStr(&status, config->args, "-workdir");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -workdir");
        return false;
    }
    if (!workdir) {
        psError(PS_ERR_UNKNOWN, true, "-workdir is required");
        return false;
    }

    // optional
    psString mode = psMetadataLookupStr(&status, config->args, "-mode");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -mode");
        return false;
    }
    // check mode
    if (mode && !isValidMode(config, mode)) {
        psError(PS_ERR_UNKNOWN, false, "invalud mode");
        return false;
    }
    psString camera = psMetadataLookupStr(&status, config->args, "-inst");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -inst");
        return false;
    }

    psString telescope = psMetadataLookupStr(&status, config->args, "-telescope");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -telescope");
        return false;
    }

    psString exp_type = psMetadataLookupStr(&status, config->args, "-exp_type");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_type");
        return false;
    }

    psString filter = psMetadataLookupStr(&status, config->args, "-filter");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filter");
        return false;
    }

    psF32 airmass_min = psMetadataLookupF32(&status, config->args, "-airmass_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -airmass_min");
        return false;
    }

    psF32 airmass_max = psMetadataLookupF32(&status, config->args, "-airmass_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -airmass_max");
        return false;
    }

    psF32 exp_time_min = psMetadataLookupF32(&status, config->args, "-exp_time_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_time_min");
        return false;
    }

    psF32 exp_time_max = psMetadataLookupF32(&status, config->args, "-exp_time_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_time_max");
        return false;
    }

    psF32 ccd_temp_min = psMetadataLookupF32(&status, config->args, "-ccd_temp_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -ccd_temp_min");
        return false;
    }

    psF32 ccd_temp_max = psMetadataLookupF32(&status, config->args, "-ccd_temp_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -ccd_temp_max");
        return false;
    }

    psF64 posang_min = psMetadataLookupF32(&status, config->args, "-posang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -posang_min");
        return false;
    }

    psF64 posang_max = psMetadataLookupF32(&status, config->args, "-posang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -posang_max");
        return false;
    }
    psF64 solang_min = psMetadataLookupF32(&status, config->args, "-solang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -solang_min");
        return false;
    }

    psF64 solang_max = psMetadataLookupF32(&status, config->args, "-solang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -solang_max");
        return false;
    }

    psTime *registered = NULL;
    {
        psString registeredStr = psMetadataLookupStr(&status, config->args, "-registered");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -registered");
            return false;
        }
        // pass through NULL as this is an optional field
        if (registeredStr) {
            registered = psTimeFromISO(registeredStr, PS_TIME_UTC);
            if (!registered) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", registeredStr);
                return false;
            }
        } else {
            registered = NULL;
        }
    }

    psTime *time_begin = NULL;
    {
        psString time_beginStr = psMetadataLookupStr(&status, config->args, "-time_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -time_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_beginStr) {
            time_begin = psTimeFromISO(time_beginStr, PS_TIME_UTC);
            if (!time_begin) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", time_beginStr);
                return false;
            }
        } else {
            time_begin = NULL;
        }
    }

    psTime *time_end = NULL;
    {
        psString time_endStr = psMetadataLookupStr(&status, config->args, "-time_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -time_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_endStr) {
            time_end = psTimeFromISO(time_endStr, PS_TIME_UTC);
            if (!time_end) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", time_endStr);
                return false;
            }
        } else {
            time_end = NULL;
        }
    }

    psTime *use_begin = NULL;
    {
        psString use_beginStr = psMetadataLookupStr(&status, config->args, "-use_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -use_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_beginStr) {
            use_begin = psTimeFromISO(use_beginStr, PS_TIME_UTC);
            if (!use_begin) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", use_beginStr);
                return false;
            }
        } else {
            use_begin = NULL;
        }
    }

    psTime *use_end = NULL;
    {
        psString use_endStr = psMetadataLookupStr(&status, config->args, "-use_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -use_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_endStr) {
            use_end = psTimeFromISO(use_endStr, PS_TIME_UTC);
            if (!use_end) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", use_endStr);
                return false;
            }
        } else {
            use_end = NULL;
        }
    }

    psString reduction = psMetadataLookupStr(&status, config->args, "-reduction");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -reduction");
        return false;
    }

    psString label = psMetadataLookupStr(&status, config->args, "-label");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -label");
        return false;
    }

    // we have to support multipe exp_ids
    psMetadataItem *item = psMetadataLookup(config->args, "-exp_id");
    if (!item) {
        // this shouldn't actually happen when using psArgs
        psError(PS_ERR_UNKNOWN, true, "-exp_id is required");
        return false;
    }
    psMetadata *where = psMetadataAlloc();

    // make sure that -exp_id was parsed correctly
    // XXX this can be removed someday
    if (item->type == PS_DATA_METADATA_MULTI) {
        psListIterator *iter = psListIteratorAlloc(item->data.list, 0, false);
        psMetadataItem *mItem = NULL;
        while ((mItem = psListGetAndIncrement(iter))) {
            psString exp_id = mItem->data.V;
            // if exp_id is NULL then it means that -exp_id has not been
            // specified
            if (!exp_id) {
                psError(PS_ERR_UNKNOWN, true,
                        "at least one -exp_id is required");
                psFree(where);
                return false;
            }

            if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_id",
                 PS_META_DUPLICATE_OK, "==", exp_id)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
                psFree(iter);
                psFree(where);
                return false;
            }
        }
        psFree(iter);
    } else {
        psAbort(                "-exp_id was not parsed correctly (this should not happen");
    }

    if (psListLength(where->list) < 1) {
        psFree(where);
        where = NULL;
    }

    // check that the specified exp_ids actually exist
    psArray *detrendExps = rawExpSelectRowObjects(config->dbh, where, 0);
    psFree(where);
    if (!detrendExps) {
        psError(PS_ERR_UNKNOWN, false, "no rawExp rows found");
        return false;
    }

    // we should have one rawExp row per exp_id specified
    if (psListLength(item->data.list) != psArrayLength(detrendExps)) {
        psAbort(    "an -exp_id matched more then one rawExp (this should not happen");

    }

    // check to see if -filelevel was set on the command line
    if (!filelevel) {
        filelevel = psStringCopy(((rawExpRow *)detrendExps->data[0])->filelevel);
    }

    // start a transaction so we don't end up with childlessed det_ids
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(detrendExps);
        return false;
    }

    // the first iteration is always 0
    // XXX the camera name is set from the first inputExp
    // XXX det_id
    detRunInsert(config->dbh,
                 0,
                 0,
                 det_type,
                 mode,
                 "run",
                 filelevel,
                 workdir,
                 camera,
                 telescope,
                 exp_type,
                 reduction,
                 filter,
                 airmass_min,
                 airmass_max,
                 exp_time_min,
                 exp_time_max,
                 ccd_temp_min,
                 ccd_temp_max,
                 posang_min,
                 posang_max,
                 registered,
                 time_begin,
                 time_end,
                 use_begin,
                 use_end,
                 solang_min,
                 solang_max,
                 label,
                 0       // parent
        );
    psFree(registered);
    psFree(time_begin);
    psFree(time_end);
    psFree(use_begin);
    psFree(use_end);
    psS64 det_id = psDBLastInsertID(config->dbh);

    // create new detInputExp row(s) from the rawExp row(s)
    psArray *inputExps = psArrayAllocEmpty(psArrayLength(detrendExps));
    for (long i = 0; i < psArrayLength(detrendExps); i++) {
        detInputExpRow *inputExp = rawDetrenTodetInputExpRow(
            detrendExps->data[i],
            det_id,
            0 // the first iteration is explicitly 0
        );
        psArrayAdd(inputExps, 0, inputExp);
        psFree(inputExp);
    }

    psFree(detrendExps);

    // insert detInputExp objects into the database
    if (!detInputExpInsertObjects(config->dbh, inputExps)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psFree(inputExps);
        return false;
    }
    psFree(inputExps);

    // point of no return for det_id creation
    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // print the new det_id
    psArray *detRuns = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==", det_id);
        detRuns = psDBSelectRows(config->dbh, "detRun", where, 0);
        psFree(where);
    }
    if (!detRuns) {
        psError(PS_ERR_UNKNOWN, false, "can't find the detRun we just created");
        return false;
    }
    // sanity check results
    if (psArrayLength(detRuns) != 1) {
        psAbort("found more then one detRun matching det_id %" PRId64 " (this should not happen)", det_id);
        return false;
    }

    if (!convertIdToStr(detRuns)) {
        psError(PS_ERR_UNKNOWN, false, "failed to convert id fields into a strings");
        psFree(detRuns);
        return false;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, detRuns, "detRun", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(detRuns);
        return false;
    }
    psFree(detRuns);

    return true;
}

#if 0
// This function is used to convert the det_id from an int, as it is used
// internally, to be a string for external use.  The rational being that we may
// want to change how det_id is generated in the future and don't want to
// external programs to become depending on this value being an int.
static bool convertDetIdToStr(psArray *mds)
{
    PS_ASSERT_PTR_NON_NULL(mds, false);

    for (long i = 0; i < psArrayLength(mds); i++) {
        psMetadata *md = mds->data[i];
        bool status = false;
        psS32 det_id = psMetadataLookupS32(&status, md, "det_id");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for det_id");
            return false;
        }
        psMetadataRemoveKey(md, "det_id");
        psString det_idStr = psDBIntToString((psU64)det_id);
        psMetadataAddStr(mds->data[i], PS_LIST_HEAD, "det_id", 0, NULL, det_idStr);
        psFree(det_idStr);
    }

    return true;
}
#endif

static bool definebyqueryMode(pxConfig *config)
{
    bool status     = false;

    PS_ASSERT_PTR_NON_NULL(config, false);

    // what type of detRun is this?
    // det_type is required
    psString det_type = psMetadataLookupStr(&status, config->args, "-det_type");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_type");
        return false;
    }
    if (!det_type) {
        psError(PS_ERR_UNKNOWN, true, "-det_type is required");
        return false;
    }

    // optional
    psString filelevel = psMetadataLookupStr(&status, config->args, "-filelevel");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filelevel");
        return false;
    }

    psString workdir = psMetadataLookupStr(&status, config->args, "-workdir");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -workdir");
        return false;
    }
    if (!workdir) {
        psError(PS_ERR_UNKNOWN, true, "-workdir is required");
        return false;
    }
    psString camera = psMetadataLookupStr(&status, config->args, "-inst");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -inst");
        return false;
    }
    if (!camera) {
        psError(PS_ERR_UNKNOWN, false, "-inst is required");
        return false;
    }

    psMetadata *where = psMetadataAlloc();
    {
        bool status = false;
        psString exp_type = psMetadataLookupStr(&status, config->args, "-select_exp_type");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_exp_type");
            return false;
        }
        if (exp_type) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_type", 0, "==", exp_type)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_type");
                psFree(where);
                return false;
            }
        }

        // map -inst -> camera
        psString camera = psMetadataLookupStr(&status, config->args, "-select_inst");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_inst");
            return false;
        }
        if (camera) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "camera", 0, "==", camera)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item inst");
                psFree(where);
                return false;
            }
        }

        psString telescope = psMetadataLookupStr(&status, config->args, "-select_telescope");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_telescope");
            return false;
        }
        if (telescope) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "telescope", 0, "==", telescope)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item telescope");
                psFree(where);
                return false;
            }
        }

        psString filter = psMetadataLookupStr(&status, config->args, "-select_filter");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_filter");
            return false;
        }
        if (filter) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "filter", 0, "==", filter)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item filter");
                psFree(where);
                return false;
            }
        }

        psString uri = psMetadataLookupStr(&status, config->args, "-select_uri");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_uri");
            return false;
        }
        if (uri) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "uri", 0, "==", uri)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item uri");
                psFree(where);
                return false;
            }
        }

        psString dateobs_begin = psMetadataLookupStr(&status, config->args, "-select_dateobs_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_dateobs_begin");
            psFree(where);
            return false;
        }
        if (dateobs_begin) {
            psTime *time = psTimeFromISO(dateobs_begin, PS_TIME_UTC);
            if (!time) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", dateobs_begin);
                psFree(time);
                psFree(where);
                return false;
            }
            if (!psMetadataAddTime(where, PS_LIST_TAIL, "dateobs", PS_META_DUPLICATE_OK, ">=", time)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item dateobs");
                psFree(time);
                psFree(where);
                return false;
            }
            psFree(time);
        }

        psString dateobs_end = psMetadataLookupStr(&status, config->args, "-select_dateobs_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_dateobs_end");
            psFree(where);
            return false;
        }
        if (dateobs_end) {
            psTime *time = psTimeFromISO(dateobs_end, PS_TIME_UTC);
            if (!time) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", dateobs_end);
                psFree(time);
                psFree(where);
                return false;
            }
            if (!psMetadataAddTime(where, PS_LIST_TAIL, "dateobs", PS_META_DUPLICATE_OK, "<", time)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item dateobs");
                psFree(time);
                psFree(where);
                return false;
            }
            psFree(time);
        }

        /** selection based on airmass range **/
        psF32 select_airmass_min = psMetadataLookupF32(&status, config->args, "-select_airmass_min");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_airmass_min");
            psFree(where);
            return false;
        }
        if (isfinite(select_airmass_min)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "airmass", PS_META_DUPLICATE_OK, ">=", select_airmass_min)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item airmass");
                psFree(where);
                return false;
            }
        }
        psF32 select_airmass_max = psMetadataLookupF32(&status, config->args, "-select_airmass_max");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_airmass_max");
            psFree(where);
            return false;
        }
        if (isfinite(select_airmass_max)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "airmass", PS_META_DUPLICATE_OK, "<=", select_airmass_max)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item airmass");
                psFree(where);
                return false;
            }
        }

        psF32 select_sat_pixel_frac_max = psMetadataLookupF32(&status, config->args, "-select_sat_pixel_frac_max");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_sat_pixel_frac_max");
            psFree(where);
            return false;
        }
        if (isfinite(select_sat_pixel_frac_max)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "sat_pixel_frac", PS_META_DUPLICATE_OK, "<=", select_sat_pixel_frac_max)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item sat_pixel_frac");
                psFree(where);
                return false;
            }
        }

        /** selection based on exp_time range **/
        psF32 select_exp_time_min = psMetadataLookupF32(&status, config->args, "-select_exp_time_min");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_exp_time_min");
            psFree(where);
            return false;
        }
        if (isfinite(select_exp_time_min)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "exp_time", PS_META_DUPLICATE_OK, ">=", select_exp_time_min)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_time");
                psFree(where);
                return false;
            }
        }
        psF32 select_exp_time_max = psMetadataLookupF32(&status, config->args, "-select_exp_time_max");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_exp_time_max");
            psFree(where);
            return false;
        }
        if (isfinite(select_exp_time_max)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "exp_time", PS_META_DUPLICATE_OK, "<=", select_exp_time_max)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_time");
                psFree(where);
                return false;
            }
        }

        /** selection based on ccd_temp range **/
        psF32 select_ccd_temp_min = psMetadataLookupF32(&status, config->args, "-select_ccd_temp_min");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_ccd_temp_min");
            psFree(where);
            return false;
        }
        if (isfinite(select_ccd_temp_min)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "ccd_temp", PS_META_DUPLICATE_OK, ">=", select_ccd_temp_min)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item ccd_temp");
                psFree(where);
                return false;
            }
        }
        psF32 select_ccd_temp_max = psMetadataLookupF32(&status, config->args, "-select_ccd_temp_max");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_ccd_temp_max");
            psFree(where);
            return false;
        }
        if (isfinite(select_ccd_temp_max)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "ccd_temp", PS_META_DUPLICATE_OK, "<=", select_ccd_temp_max)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item ccd_temp");
                psFree(where);
                return false;
            }
        }

        /** selection based on posang **/
        psF32 select_posang_min = psMetadataLookupF32(&status, config->args, "-select_posang_min");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_posang_min");
            psFree(where);
            return false;
        }
        if (isfinite(select_posang_min)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "posang", PS_META_DUPLICATE_OK, ">=", select_posang_min)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item posang");
                psFree(where);
                return false;
            }
        }
        psF32 select_posang_max = psMetadataLookupF32(&status, config->args, "-select_posang_max");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_posang_max");
            psFree(where);
            return false;
        }
        if (isfinite(select_posang_max)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "posang", PS_META_DUPLICATE_OK, "<=", select_posang_max)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item posang");
                psFree(where);
                return false;
            }
        }

        /** selection based on solang **/
        psF32 select_solang_min = psMetadataLookupF32(&status, config->args, "-select_solang_min");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_solang_min");
            psFree(where);
            return false;
        }
        if (isfinite(select_solang_min)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "solang", PS_META_DUPLICATE_OK, ">=", select_solang_min)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item solang");
                psFree(where);
                return false;
            }
        }
        psF32 select_solang_max = psMetadataLookupF32(&status, config->args, "-select_solang_max");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_solang_max");
            psFree(where);
            return false;
        }
        if (isfinite(select_solang_max)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "solang", PS_META_DUPLICATE_OK, "<=", select_solang_max)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item solang");
                psFree(where);
                return false;
            }
        }

    }

    if (!psListLength(where->list)) {
        psFree(where);
        where = NULL;
    }

    // there is some namespace overlap between the names of the fields we'd
    // like to search by to setup a detrun and the names of the fields we'd
    // like to assign values to so I've seperated them but prepending set- to
    // the assigned values

    // optional
    psString mode = psMetadataLookupStr(&status, config->args, "-mode");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -mode");
        return false;
    }
    // check mode
    if (mode && !isValidMode(config, mode)) {
        psError(PS_ERR_UNKNOWN, false, "invalud mode");
        return false;
    }

    psString telescope = psMetadataLookupStr(&status, config->args, "-telescope");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -telescope");
        return false;
    }

    psString filter = psMetadataLookupStr(&status, config->args, "-filter");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filter");
        return false;
    }

    psF32 airmass_min = psMetadataLookupF32(&status, config->args, "-airmass_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -airmass_min");
        return false;
    }

    psF32 airmass_max = psMetadataLookupF32(&status, config->args, "-airmass_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -airmass_max");
        return false;
    }

    psF32 exp_time_min = psMetadataLookupF32(&status, config->args, "-exp_time_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_time_min");
        return false;
    }

    psF32 exp_time_max = psMetadataLookupF32(&status, config->args, "-exp_time_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_time_max");
        return false;
    }

    psF32 ccd_temp_min = psMetadataLookupF32(&status, config->args, "-ccd_temp_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -ccd_temp_min");
        return false;
    }

    psF32 ccd_temp_max = psMetadataLookupF32(&status, config->args, "-ccd_temp_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -ccd_temp_max");
        return false;
    }

    psF64 posang_min = psMetadataLookupF32(&status, config->args, "-posang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -posang_min");
        return false;
    }

    psF64 posang_max = psMetadataLookupF32(&status, config->args, "-posang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -posang_max");
        return false;
    }

    psF64 solang_min = psMetadataLookupF32(&status, config->args, "-solang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -solang_min");
        return false;
    }

    psF64 solang_max = psMetadataLookupF32(&status, config->args, "-solang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -solang_max");
        return false;
    }

    psTime *registered = NULL;
    {
        psString registeredStr = psMetadataLookupStr(&status, config->args, "-registered");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -registered");
            return false;
        }
        // pass through NULL as this is an optional field
        if (registeredStr) {
            registered = psTimeFromISO(registeredStr, PS_TIME_UTC);
            if (!registered) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", registeredStr);
                return false;
            }
        } else {
            registered = NULL;
        }
    }

    psTime *time_begin = NULL;
    {
        psString time_beginStr = psMetadataLookupStr(&status, config->args, "-time_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -time_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_beginStr) {
            time_begin = psTimeFromISO(time_beginStr, PS_TIME_UTC);
            if (!time_begin) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", time_beginStr);
                return false;
            }
        } else {
            time_begin = NULL;
        }
    }

    psTime *time_end = NULL;
    {
        psString time_endStr = psMetadataLookupStr(&status, config->args, "-time_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -time_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_endStr) {
            time_end = psTimeFromISO(time_endStr, PS_TIME_UTC);
            if (!time_end) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", time_endStr);
                return false;
            }
        } else {
            time_end = NULL;
        }
    }

    psTime *use_begin = NULL;
    {
        psString use_beginStr = psMetadataLookupStr(&status, config->args, "-use_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -use_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_beginStr) {
            use_begin = psTimeFromISO(use_beginStr, PS_TIME_UTC);
            if (!use_begin) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", use_beginStr);
                return false;
            }
        } else {
            use_begin = NULL;
        }
    }

    psTime *use_end = NULL;
    {
        psString use_endStr = psMetadataLookupStr(&status, config->args, "-use_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -use_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_endStr) {
            use_end = psTimeFromISO(use_endStr, PS_TIME_UTC);
            if (!use_end) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", use_endStr);
                return false;
            }
        } else {
            use_end = NULL;
        }
    }

    psString reduction = psMetadataLookupStr(&status, config->args, "-reduction");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -reduction");
        return false;
    }

    psString label = psMetadataLookupStr(&status, config->args, "-label");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -label");
        return false;
    }

    bool simple = psMetadataLookupBool(&status, config->args, "-simple");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
        psFree(registered);
        psFree(use_begin);
        psFree(use_end);
        return false;
    }

    bool pretend = psMetadataLookupBool(&status, config->args, "-pretend");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -pretend");
        psFree(registered);
        psFree(use_begin);
        psFree(use_end);
        return false;
    }

    // search for rawExps with the specified options
    psArray *detrendExps = rawExpSelectRowObjects(config->dbh, where, 0);
    psFree(where);
    // make sure that we found at least one rawExp
    if (!detrendExps) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(registered);
        psFree(use_begin);
        psFree(use_end);
        return false;
    }
    if (!psArrayLength(detrendExps)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(detrendExps);
        psFree(registered);
        psFree(use_begin);
        psFree(use_end);
        return true;
    }

    // check to see if -filelevel was set on the command line
    if (!filelevel) {
        filelevel = psStringCopy(((rawExpRow *)detrendExps->data[0])->filelevel);
    }

    if (pretend) {
        // negative simple so the default is true
        if (!rawExpPrintObjects(stdout, detrendExps, !simple)) {
            psError(PS_ERR_UNKNOWN, false, "failed to print array");
            psFree(detrendExps);
            psFree(registered);
            psFree(use_begin);
            psFree(use_end);
            return false;
        }
        psFree(detrendExps);
        psFree(registered);
        psFree(use_begin);
        psFree(use_end);
        return true;
    }

    // start a transaction so we don't end up with childlessed det_ids
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(detrendExps);
        psFree(registered);
        psFree(use_begin);
        psFree(use_end);
        return false;
    }

    // the first iteration is always 0
    // XXX det_id
    detRunInsert(config->dbh,
                 0,      // det_id
                 0,      // iteration
                 det_type,
                 mode,
                 "run",  // state
                 filelevel,
                 workdir,
                 camera,
                 telescope,
                 "NA",
                 reduction,
                 filter,
                 airmass_min,
                 airmass_max,
                 exp_time_min,
                 exp_time_max,
                 ccd_temp_min,
                 ccd_temp_max,
                 posang_min,
                 posang_max,
                 registered,
                 time_begin,
                 time_end,
                 use_begin,
                 use_end,
                 solang_min,
                 solang_max,
                 label,
                 0       // parent
        );
    psFree(registered);
    psFree(time_begin);
    psFree(time_end);
    psFree(use_end);
    psFree(use_begin);
    psFree(use_end);
    psS64 det_id = psDBLastInsertID(config->dbh);

    // create new detInputExp row(s) from the rawExp row(s)
    psArray *inputExps = psArrayAllocEmpty(psArrayLength(detrendExps));
    for (long i = 0; i < psArrayLength(detrendExps); i++) {
        detInputExpRow *inputExp = rawDetrenTodetInputExpRow(
            detrendExps->data[i],
            det_id,
            0 // the first iteration is explicitly 0
        );
        psArrayAdd(inputExps, 0, inputExp);
        psFree(inputExp);
    }

    psFree(detrendExps);

    // insert detInputExp objects into the database
    if (!detInputExpInsertObjects(config->dbh, inputExps)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psFree(inputExps);
        return false;
    }
    psFree(inputExps);

    // point of no return for det_id creation
    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }


    // print the new det_id
    psArray *detRuns = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==", det_id);
        detRuns = psDBSelectRows(config->dbh, "detRun", where, 0);
        psFree(where);
    }
    if (!detRuns) {
        psError(PS_ERR_UNKNOWN, false, "can't find the detRun we just created");
        return false;
    }
    // sanity check results
    if (psArrayLength(detRuns) != 1) {
        psAbort("found more then one detRun matching det_id %" PRId64 " (this should not happen)", det_id);
        return false;
    }

    // convert det_id to a string externaly
    if (!convertIdToStr(detRuns)) {
        psError(PS_ERR_UNKNOWN, false, "failed to convert det_id to a string");
        psFree(detRuns);
        return false;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, detRuns, "detRun", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(detRuns);
        return false;
    }
    psFree(detRuns);

    return true;
}

static bool definebydetrunMode(pxConfig *config)
{
    bool status     = false;

    PS_ASSERT_PTR_NON_NULL(config, false);

    // det_id is the only required input
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }

    // lookup the detRun that we will be basing this one on
    psArray *detRuns = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==", (psS64)atoll(det_id));
        detRuns = detRunSelectRowObjects(config->dbh, where, 0);
        psFree(where);
    }
    if (!detRuns) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    // sanity check the result... we should have only found one det_id
    if (psArrayLength(detRuns) != 1) {
        psAbort("found more then one detRun matching det_id %" PRId64 " (this should not happen)", (psS64)atoll(det_id));
        return false;                   // unreachable
    }

    // pull the detRun object out the result array
    detRunRow *detRun = psMemIncrRefCounter(detRuns->data[0]);

    // discard the resultarray
    psFree(detRuns);

    // set the det_id to 0/NULL so the database can assign it
    detRun->det_id = 0;

    // reset the iteration to 0
    detRun->iteration = 0;

    // reset the state to "run"
    psFree(detRun->state);
    detRun->state = psStringCopy("run");

    // walk through the optional values and update the detRun as required
    psString det_type = psMetadataLookupStr(&status, config->args, "-set_det_type");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_det_type");
        return false;
    }
    if (det_type) {
        psFree(detRun->det_type);
        detRun->det_type = psStringCopy(det_type);
    }

    psString mode = psMetadataLookupStr(&status, config->args, "-set_mode");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_mode");
        return false;
    }
    // check mode
    if (mode && !isValidMode(config, mode)) {
        psError(PS_ERR_UNKNOWN, false, "invalud mode");
        return false;
    }
    if (mode) {
        psFree(detRun->mode);
        detRun->mode = psStringCopy(mode);
    }

    psString camera = psMetadataLookupStr(&status, config->args, "-set_inst");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_inst");
        return false;
    }
    if (camera) {
        psFree(detRun->camera);
        detRun->camera = psStringCopy(camera);
    }

    psString telescope = psMetadataLookupStr(&status, config->args, "-set_telescope");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_telescope");
        return false;
    }
    if (telescope) {
        psFree(detRun->telescope);
        detRun->telescope = psStringCopy(telescope);
    }

    psString exp_type = psMetadataLookupStr(&status, config->args, "-set_exp_type");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_exp_type");
        return false;
    }
    if (exp_type) {
        psFree(detRun->exp_type);
        detRun->exp_type = psStringCopy(exp_type);
    }

    psString filelevel = psMetadataLookupStr(&status, config->args, "-set_filelevel");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_filelevel");
        return false;
    }
    if (filelevel) {
        psFree(detRun->filelevel);
        detRun->filelevel = psStringCopy(filelevel);
    }

    psString workdir = psMetadataLookupStr(&status, config->args, "-set_workdir");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_workdir");
        return false;
    }
    if (workdir) {
        psFree(detRun->workdir);
        detRun->workdir = psStringCopy(workdir);
    }

    psString filter = psMetadataLookupStr(&status, config->args, "-set_filter");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_filter");
        return false;
    }
    if (filter) {
        psFree(detRun->filter);
        detRun->filter = psStringCopy(filter);
    }

    psF32 airmass_min = psMetadataLookupF32(&status, config->args, "-set_airmass_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_airmass_min");
        return false;
    }
    if (!isnan(airmass_min)) {
        detRun->airmass_min = airmass_min;
    }

    psF32 airmass_max = psMetadataLookupF32(&status, config->args, "-set_airmass_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_airmass_max");
        return false;
    }
    if (!isnan(airmass_max)) {
        detRun->airmass_max = airmass_max;
    }

    psF32 exp_time_min = psMetadataLookupF32(&status, config->args, "-set_exp_time_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_exp_time_min");
        return false;
    }
    if (!isnan(exp_time_min)) {
        detRun->exp_time_min = exp_time_min;
    }

    psF32 exp_time_max = psMetadataLookupF32(&status, config->args, "-set_exp_time_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_exp_time_max");
        return false;
    }
    if (!isnan(exp_time_max)) {
        detRun->exp_time_max = exp_time_max;
    }

    psF32 ccd_temp_min = psMetadataLookupF32(&status, config->args, "-set_ccd_temp_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_ccd_temp_min");
        return false;
    }
    if (!isnan(ccd_temp_min)) {
        detRun->ccd_temp_min = ccd_temp_min;
    }

    psF32 ccd_temp_max = psMetadataLookupF32(&status, config->args, "-set_ccd_temp_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_ccd_temp_max");
        return false;
    }
    if (!isnan(ccd_temp_max)) {
        detRun->ccd_temp_max = ccd_temp_max;
    }

    psF64 posang_min = psMetadataLookupF32(&status, config->args, "-set_posang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_posang_min");
        return false;
    }
    if (!isnan(posang_min)) {
        detRun->posang_min = posang_min;
    }

    psF64 posang_max = psMetadataLookupF32(&status, config->args, "-set_posang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_posang_max");
        return false;
    }
    if (!isnan(posang_max)) {
        detRun->posang_max = posang_max;
    }

    psF64 solang_min = psMetadataLookupF32(&status, config->args, "-set_solang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_solang_min");
        return false;
    }
    if (!isnan(solang_min)) {
        detRun->solang_min = solang_min;
    }

    psF64 solang_max = psMetadataLookupF32(&status, config->args, "-set_solang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_solang_max");
        return false;
    }
    if (!isnan(solang_max)) {
        detRun->solang_max = solang_max;
    }

    psString label = psMetadataLookupStr(&status, config->args, "-set_label");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_label");
        return false;
    }
    if (label) {
        detRun->label = label;
    }

    psTime *registered = NULL;
    {
        psString registeredStr = psMetadataLookupStr(&status, config->args, "-set_registered");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_registered");
            return false;
        }
        // pass through NULL as this is an optional field
        if (registeredStr) {
            psFree(detRun->registered);
            registered = psTimeFromISO(registeredStr, PS_TIME_UTC);
            detRun->registered = psMemIncrRefCounter(registered);
            psFree(registered);
        }
    }

    psTime *time_begin = NULL;
    {
        psString time_beginStr = psMetadataLookupStr(&status, config->args, "-set_time_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_time_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_beginStr) {
            psFree(detRun->time_begin);
            time_begin = psTimeFromISO(time_beginStr, PS_TIME_UTC);
            detRun->time_begin = psMemIncrRefCounter(time_begin);
            psFree(time_begin);
        }
    }

    psTime *time_end = NULL;
    {
        psString time_endStr = psMetadataLookupStr(&status, config->args, "-set_time_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_time_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_endStr) {
            psFree(detRun->time_end);
            time_end = psTimeFromISO(time_endStr, PS_TIME_UTC);
            detRun->time_end = psMemIncrRefCounter(time_end);
            psFree(time_end);
        }
    }

    psTime *use_begin = NULL;
    {
        psString use_beginStr = psMetadataLookupStr(&status, config->args, "-set_use_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_use_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_beginStr) {
            psFree(detRun->use_begin);
            use_begin = psTimeFromISO(use_beginStr, PS_TIME_UTC);
            detRun->use_begin = psMemIncrRefCounter(use_begin);
            psFree(use_begin);
        }
    }

    psTime *use_end = NULL;
    {
        psString use_endStr = psMetadataLookupStr(&status, config->args, "-set_use_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_use_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_endStr) {
            psFree(detRun->use_end);
            use_end = psTimeFromISO(use_endStr, PS_TIME_UTC);
            detRun->use_end = psMemIncrRefCounter(use_end);
            psFree(use_end);
        }
    }

    psString reduction = psMetadataLookupStr(&status, config->args, "-set_reduction");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_reduction");
        return false;
    }
    if (reduction) {
        psFree(detRun->reduction);
        detRun->reduction = psStringCopy(reduction);
    }

    // create a metadata to restrict detInputExp's be in in the specified range

    psMetadata *time_filter = psMetadataAlloc();

    {
        psString timeStr = psMetadataLookupStr(&status, config->args, "-filter_input_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filter_input_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (timeStr) {
            psTime *time = psTimeFromISO(timeStr, PS_TIME_UTC);
            if (!psMetadataAddTime(time_filter, PS_LIST_TAIL, "dateobs", PS_META_DUPLICATE_OK, ">=", time)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item dateobs");
                psFree(time);
                psFree(time_filter);
                return false;
            }
            psFree(time);
        }
    }

    {
        psString timeStr = psMetadataLookupStr(&status, config->args, "-filter_input_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filter_input_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (timeStr) {
            psTime *time = psTimeFromISO(timeStr, PS_TIME_UTC);
            if (!psMetadataAddTime(time_filter, PS_LIST_TAIL, "dateobs", PS_META_DUPLICATE_OK, "<", time)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item dateobs");
                psFree(time);
                psFree(time_filter);
                return false;
            }
            psFree(time);
        }
    }


    // start a transaction so we don't end up with childlessed det_ids
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(time_filter);
        psFree(detRun);
        return false;
    }

    if (!detRunInsertObject(config->dbh, detRun)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psFree(time_filter);
        psFree(detRun);
        return false;
    }
    psFree(detRun);

    // get the det_id
    psS64 newDet_id = psDBLastInsertID(config->dbh);

    psString query = psStringCopy(
        "INSERT INTO detInputExp"
        "   SELECT"
        "       %d,"
        "       0,"
        "       detResidExp.exp_id,"
        "       detResidExp.accept"
        "   FROM detResidExp"
        "   JOIN rawExp"
        "       USING(exp_id)"
        "   WHERE det_id = %d"
    );

    if (time_filter->list->n) {
        psString whereClause = psDBGenerateWhereConditionSQL(time_filter, "rawExp");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(time_filter);

    if (!p_psDBRunQuery(config->dbh, query, (psS64)newDet_id, (psS64)atoll(det_id))) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    // point of no return for det_id creation
    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // print the new det_id
    psArray *newDetRuns = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==", newDet_id);
        newDetRuns = psDBSelectRows(config->dbh, "detRun", where, 0);
        psFree(where);
    }
    if (!newDetRuns) {
        psError(PS_ERR_UNKNOWN, false, "can't find the detRun we just created");
        return false;
    }
    // sanity check the result... we should have only found one det_id
    if (psArrayLength(newDetRuns) != 1) {
        psAbort("found more then one detRun matching det_id %" PRId64 " (this should not happen)", newDet_id);
        return false;                   // unreachable
    }

    // convert det_id to a string externaly
    if (!convertIdToStr(detRuns)) {
        psError(PS_ERR_UNKNOWN, false, "failed to convert id fields into a strings");
        psFree(detRuns);
        return false;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, newDetRuns, "detRun", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(newDetRuns);
        return false;
    }
    psFree(newDetRuns);

    return true;
}

static bool runsMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // XXX fix the hard coding of the table name
    psArray *runs = psDBSelectRows(config->dbh, "detRun", config->where, 0);
    if (!runs) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    if (!psArrayLength(runs)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(runs);
        return true;
    }

    // convert det_id to a string externaly
    if (!convertIdToStr(runs)) {
        psError(PS_ERR_UNKNOWN, false, "failed to convert id fields into a strings");
        psFree(runs);
        return false;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, runs, "detRun", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(runs);
        return false;
    }

    psFree(runs);

    return true;
}

static bool childlessrunMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    psString query = psStringCopy(
            "SELECT DISTINCT\n"
            "   detRun.*\n"
            " FROM detRun\n"
            " LEFT JOIN detRun as foo\n"
            "   ON foo.parent = detRun.det_id\n"
            " WHERE\n"
            "   detRun.state = 'stop'\n"
            "   AND detRun.mode = 'master'\n"
            "   AND foo.det_id IS NULL\n"
        );

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detRun");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // convert det_id to a string externaly
    if (!convertIdToStr(output)) {
        psError(PS_ERR_UNKNOWN, false, "failed to convert id fields into a strings");
        psFree(output);
        return false;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            psFree(output);
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detRun", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static detInputExpRow *rawDetrenTodetInputExpRow(rawExpRow *rawExp, psS64 det_id, psS32 iteration)
{
    PS_ASSERT_PTR_NON_NULL(rawExp, NULL);

    return detInputExpRowAlloc(
        det_id,
        iteration,
        rawExp->exp_id,
        true            // use
    );
}

static bool inputMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // select detInputExp.*
    // select rawExp.*
    // by:
    // exp_id

    psString query = psStringCopy(
        "SELECT DISTINCT *"
        " FROM detInputExp"
        " JOIN rawExp"
        " USING(exp_id)"
        );

    if (config->where) {
        psString whereClause = psDBGenerateWhereSQL(config->where, "detInputExp");
        psStringAppend(&query, " %s", whereClause);
        psFree(whereClause);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detInputExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool rawMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psString query = pxDataGet("dettool_raw.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "rawImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "rawImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool toprocessedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    psString query = pxDataGet("dettool_toprocessedimfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "rawImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingProcessedImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static psArray *searchRawImfiles(pxConfig *config, psMetadata *where)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);

    // use the default where if "where" is NULL
    if (!where) {
        where = config->where;
    }

    // select exp_ids from detInputExp matching det_idp
    // where query should be pre-generated
    psArray *detInputExp =
        detInputExpSelectRowObjects(config->dbh, where, 0);
    if (!detInputExp) {
        psError(PS_ERR_UNKNOWN, false, "no rawExp rows found");
        return NULL;
    }

    // generate where query with just the exp_ids
    psMetadata *where_exp_ids = psMetadataAlloc();
    for (long i = 0; i < psArrayLength(detInputExp); i++) {
        detInputExpRow *row = detInputExp->data[i];
        if (!psMetadataAddS64(where_exp_ids, PS_LIST_TAIL, "exp_id",
                PS_META_DUPLICATE_OK, "==", row->exp_id)
        ) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
            psFree(detInputExp);
            psFree(where_exp_ids);
            return NULL;
        }
    }
    psFree(detInputExp);

    // select rawImfiles with matching exp_ids
    psArray *rawImfiles =
        rawImfileSelectRowObjects(config->dbh, where_exp_ids, 0);
    psFree(where_exp_ids);
    if (!rawImfiles) {
        psError(PS_ERR_UNKNOWN, false, "no rawImfile rows found");
        return NULL;
    }

    return rawImfiles;
}

static bool addprocessedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // det_id, exp_id, class_id, uri, recipe, -bg, -bg_stdev
    // are required
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }
    psString exp_id = psMetadataLookupStr(&status, config->args, "-exp_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_id");
        return false;
    }
    if (!exp_id) {
        psError(PS_ERR_UNKNOWN, true, "-exp_id is required");
        return false;
    }
    psString class_id = psMetadataLookupStr(&status, config->args, "-class_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -class_id");
        return false;
    }
    if (!class_id) {
        psError(PS_ERR_UNKNOWN, true, "-class_id is required");
        return false;
    }
    psString uri    = psMetadataLookupStr(&status, config->args, "-uri");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -uri");
        return false;
    }
    if (!uri) {
        psError(PS_ERR_UNKNOWN, true, "-uri is required");
        return false;
    }
    psString recipe = psMetadataLookupStr(&status, config->args, "-recip");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -recip");
        return false;
    }
    if (!recipe) {
        psError(PS_ERR_UNKNOWN, true, "-recip is required");
        return false;
    }
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }
    //if (isnan(bg)) {
    //psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //return false;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return false;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }

    // optional
    psF64 fringe_0 = psMetadataLookupF64(&status, config->args, "-fringe_0");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_0");
        return false;
    }
    psF64 fringe_1 = psMetadataLookupF64(&status, config->args, "-fringe_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_1");
        return false;
    }
    psF64 fringe_2 = psMetadataLookupF64(&status, config->args, "-fringe_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_2");
        return false;
    }

    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        return false;
    }

    // find the matching rawImfile by exp_id/class_id
    psMetadata *where = psMetadataAlloc();
    if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_id", 0, "==", exp_id)) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
        psFree(where);
        return false;
    }
    if (!psMetadataAddStr(where, PS_LIST_TAIL, "class_id", 0, "==", class_id)) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item class_id");
        psFree(where);
        return false;
    }

    // default values
    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    psArray *rawImfiles = rawImfileSelectRowObjects(config->dbh, where, 0);
    psFree(where);
    if (!rawImfiles) {
        psError(PS_ERR_UNKNOWN, false, "no rawImfile rows found ");
        return false;
    }


    // create a new detProcessedImfile object
    detProcessedImfileRow *detRow = detProcessedImfileRowAlloc(
        (psS64)atoll(det_id),
        (psS64)atoll(exp_id),
        class_id,
        uri,
        recipe,
        bg,
        bg_stdev,
        bg_mean_stdev,
        fringe_0,
        fringe_1,
        fringe_2,
        user_1,
        user_2,
        user_3,
        user_4,
        user_5,
        path_base,
        code
    );
    psFree(rawImfiles);

    // insert the new row into the detProcessedImfile table
    if (!detProcessedImfileInsertObject(config->dbh, detRow)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(detRow);
        return false;
    }

    psFree(detRow);

    return true;
}

static bool toprocessedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    psString query = pxDataGet("dettool_toprocessedexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingProcessedExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool addprocessedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // det_id, exp_id, recip, -bg, -bg_stdev
    // are required
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }
    psString exp_id = psMetadataLookupStr(&status, config->args, "-exp_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_id");
        return false;
    }
    if (!exp_id) {
        psError(PS_ERR_UNKNOWN, true, "-exp_id is required");
        return false;
    }
    psString recipe = psMetadataLookupStr(&status, config->args, "-recip");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -recip");
        return false;
    }
    if (!recipe) {
        psError(PS_ERR_UNKNOWN, true, "-recip is required");
        return false;
    }
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }
    //if (isnan(bg)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //  return false;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return false;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }
    // optional
    psF64 fringe_0 = psMetadataLookupF64(&status, config->args, "-fringe_0");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_0");
        return false;
    }
    psF64 fringe_1 = psMetadataLookupF64(&status, config->args, "-fringe_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_1");
        return false;
    }
    psF64 fringe_2 = psMetadataLookupF64(&status, config->args, "-fringe_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_2");
        return false;
    }

    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        return false;
    }

    // default values
    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    psString query = psStringCopy(
        " SELECT DISTINCT"
        "    detProcessedImfile.det_id,"
        "    detRun.iteration,"
        "    detRun.det_type,"
        "    detProcessedImfile.exp_id"
        " FROM detRun"
        " JOIN detInputExp"
        "    ON detRun.det_id = detInputExp.det_id"
        "    AND detRun.iteration = detInputExp.iteration"
        " JOIN rawExp"
        "    ON detInputExp.exp_id = rawExp.exp_id"
        " JOIN detProcessedImfile"
        "    ON detInputExp.det_id = detProcessedImfile.det_id"
        "    AND detInputExp.exp_id = detProcessedImfile.exp_id"
        " LEFT JOIN detProcessedExp"
        "    ON detInputExp.det_id = detProcessedExp.det_id"
        "    AND detProcessedImfile.exp_id= detProcessedExp.exp_id"
        " LEFT JOIN rawImfile"
        "    ON detInputExp.exp_id = rawImfile.exp_id"
        "    AND detProcessedImfile.class_id = rawImfile.class_id"
        " WHERE"
        "   detRun.state = 'run'"
        "   AND detRun.mode = 'master'"
        "   AND detProcessedExp.det_id IS NULL"
        "   AND detProcessedExp.exp_id IS NULL"
        "   AND detInputExp.include = 1"
        "   AND detRun.det_id = %s"
        "   AND detProcessedImfile.exp_id = '%s'"
        " GROUP BY"
        "    detProcessedImfile.class_id,"
        "    rawImfile.class_id,"
        "    detRun.det_id"
        " HAVING"
        "    COUNT(detProcessedImfile.class_id) = COUNT(rawImfile.class_id)"
        );

    if (!p_psDBRunQuery(config->dbh, query, det_id, exp_id)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }
    psFree(output);

    // create a new detProcessedImfile object
    detProcessedExpRow *detRow = detProcessedExpRowAlloc(
        (psS64)atoll(det_id),
        (psS64)atoll(exp_id),
        recipe,
        bg,
        bg_stdev,
        bg_mean_stdev,
        fringe_0,
        fringe_1,
        fringe_2,
        user_1,
        user_2,
        user_3,
        user_4,
        user_5,
        path_base,
        code
    );

    // insert the new row into the detProcessedImfile table
    if (!detProcessedExpInsertObject(config->dbh, detRow)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(detRow);
        return false;
    }

    psFree(detRow);

    return true;
}

static bool processedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    psString query = psStringCopy("SELECT * FROM detProcessedExp");

    if (config->where) {
        psString whereClause = psDBGenerateWhereSQL(config->where, NULL);
        psStringAppend(&query, " %s", whereClause);
        psFree(whereClause);
    }

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detProcessedExp.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detProcessedExp.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detProcessedExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool revertprocessedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psString query = pxDataGet("dettool_revertprocessedexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detProcessedExp");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}


static bool tostackedMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    psString query = pxDataGet("dettool_tostacked.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingStackedImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool processedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    char *value = NULL;
    bool status = false;

    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    psString query = psStringCopy(
        " SELECT DISTINCT"
        "   detRun.det_type,"
        "   rawExp.exp_time,"
        "   detProcessedImfile.*"
        " FROM detProcessedImfile"
        " JOIN detRun"
        "   USING(det_id)"
        " JOIN detInputExp"
        "   ON detRun.det_id = detInputExp.det_id"
        "   AND detRun.iteration = detInputExp.iteration"
        "   AND detProcessedImfile.exp_id = detInputExp.exp_id"
        " JOIN rawExp"
        "   ON rawExp.exp_id = detProcessedImfile.exp_id"
        " WHERE"
    );
    // NOTE the above WHERE is completed with the following line:

    // add the two required restrictions: detRun.state and detRun.mode
    if ((value = psMetadataLookupStr(&status, config->args, "-select_state"))) {
        psStringAppend(&query, " detRun.state = '%s'", value);
    } else {
        psStringAppend(&query, " detRun.state = 'run'");
    }
    if ((value = psMetadataLookupStr(&status, config->args, "-select_mode"))) {
        psStringAppend(&query, " AND detRun.mode = '%s'", value);
    } else {
        psStringAppend(&query, " AND detRun.mode = 'master'");
    }

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detProcessedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    {
        bool status = false;
        bool included = psMetadataLookupBool(&status, config->args, "-included");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
        // restrict search to included imfiles
        if (included) {
            psStringAppend(&query, " AND detInputExp.include = 1");
        }
    }

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detProcessedImfile.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detProcessedImfile.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "rawImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool revertprocessedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psString query = pxDataGet("dettool_revertprocessedimfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detProcessedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}


static bool addstackedMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // det_id, class_id, uri, & recipe are required
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }
    psString class_id = psMetadataLookupStr(&status, config->args, "-class_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -class_id");
        return false;
    }
    if (!class_id) {
        psError(PS_ERR_UNKNOWN, true, "-class_id is required");
        return false;
    }
    psString uri    = psMetadataLookupStr(&status, config->args, "-uri");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -uri");
        return false;
    }
    if (!uri) {
        psError(PS_ERR_UNKNOWN, true, "-uri is required");
        return false;
    }
    psString recipe = psMetadataLookupStr(&status, config->args, "-recip");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -recip");
        return false;
    }
    if (!recipe) {
        psError(PS_ERR_UNKNOWN, true, "-recip is required");
        return false;
    }
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }
    //if (isnan(bg)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //  return false;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return false;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }

    // optional values
    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    // default values
    psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration");
        return false;
    }

    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    // correlate the class_id against the input exposure(s)

    // we have to generate our own where clause as we want to search only by the
    // det_id
    psMetadata *where = psMetadataAlloc();
    if (!psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==",
            (psS64)atoll(det_id))) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
        psFree(where);
        return false;
    }

    psArray *rawImfiles = searchRawImfiles(config, where);
    psFree(where);

    bool valid_class_id = false;
    if (rawImfiles) {
        for (long i = 0; i < psArrayLength(rawImfiles); i++) {
            if (strcmp(class_id, ((rawImfileRow *)rawImfiles->data[i])->class_id) == 0) {
                valid_class_id = true;
                break;
            }
        }
        psFree(rawImfiles);
    }

    if (!valid_class_id) {
        psError(PS_ERR_UNKNOWN, true,
            "class_id can not be correlated with the input exposures");
        return false;
    }

    // create a new detStackedImfile object
    detStackedImfileRow *stackedImfile = detStackedImfileRowAlloc(
            (psS64)atoll(det_id),
            iteration,
            class_id,
            uri,
            recipe,
            bg,
            bg_stdev,
            bg_mean_stdev,
            user_1,
            user_2,
            user_3,
            user_4,
            user_5,
            code
        );

    // insert the new row into the detProcessedImfile table
    if (!detStackedImfileInsertObject(config->dbh, stackedImfile)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(stackedImfile);
        return false;
    }

    psFree(stackedImfile);

    return true;
}

static bool stackedMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    // select detStackedImfile.*
    // by:
    // where det_id, iteration, class_id is not in detNormalizedImfile

    psString query = psStringCopy(
        "SELECT"
        "   detStackedImfile.*"
        " FROM detStackedImfile"
        " JOIN detRun"
        "   USING(det_id, iteration)"
        " WHERE"
        "   detRun.state = 'run'"
        "   AND detRun.mode = 'master'"
    );

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detStackedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detStackedImfile.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detStackedImfile.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            psFree(output);
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "rawImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool revertstackedMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psString query = pxDataGet("dettool_revertstacked.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detStackedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}

static bool tonormalizedstatMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    psString query = pxDataGet("dettool_tonormalizedstat.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingNormStatImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool addnormalizedstatMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);

    // select * from detStackedImfile
    // by det_id, iteration, class_id
    // where det_id, iteration, class_id is not in detNormalizedStatImfile
    psString query = psStringCopy(
        "SELECT DISTINCT"
        "   detStackedImfile.*"
        " FROM detStackedImfile"
        " LEFT JOIN detNormalizedStatImfile"
        "   USING(det_id, iteration, class_id)"
        " WHERE"
        "  detNormalizedStatImfile.det_id IS NULL"
        "  AND detNormalizedStatImfile.iteration IS NULL"
        "  AND detNormalizedStatImfile.class_id IS NULL"
        );

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detStackedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // start a transaction so it's all rows or nothing
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(output);
        return false;
    }

    for (long i = 0; i < psArrayLength(output); i++) {
        psMetadata *row = output->data[i];
        // convert metadata into a detStackedImfile object
        detStackedImfileRow *stackedImfile = detStackedImfileObjectFromMetadata(row);
        // convert detStackedImfile object into a detNormalizedStat object
        detNormalizedStatImfileRow *stat = detStackedToDetNormalizedStatImfile(config, stackedImfile);
        psFree(stackedImfile);
        if (!stat) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to convert detStackedImfile to detNormalizedStatImfile");
            psFree(output);
            return false;
        }
        // insert detNormlized Stat object into the database
        if (!detNormalizedStatImfileInsertObject(config->dbh, stat)) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "database error");
            psFree(stat);
            psFree(output);
        }
        psFree(stat);
    }

    psFree(output);

    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}

static detNormalizedStatImfileRow *detStackedToDetNormalizedStatImfile(pxConfig *config, detStackedImfileRow *stackedImfile)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);
    PS_ASSERT_PTR_NON_NULL(stackedImfile, NULL);

    bool status = false;
    psF32 norm = psMetadataLookupF32(&status, config->args, "-norm");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -norm");
        return false;
    }
    //if (isnan(norm)) {
    //  psError(PS_ERR_UNKNOWN, true, "-norm is required");
    //  return false;
    //}

    // default values
    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    return detNormalizedStatImfileRowAlloc(
        stackedImfile->det_id,
        stackedImfile->iteration,
        stackedImfile->class_id,
        norm,
        code
    );
}


static bool normalizedstatMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    psString query = pxDataGet("dettool_normalizedstat.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "WHERE fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "WHERE fault = 0");
    }

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, NULL);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            psFree(output);
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detNormalizedStatImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool revertnormalizedstatMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psString query = pxDataGet("dettool_revertnormalizedstat.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detNormalizedStatImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}

static bool tonormalizeMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    // select detNormalizedStatImfile.*
    // by:
    // where det_id, iteration, class_id is not in detNormalizedImfile

    psString query = psStringCopy(
        "SELECT DISTINCT"
        "   detRun.det_type,"
        "   detRun.workdir,"
        "   rawExp.camera,"
        "   detStackedImfile.uri,"
        "   detNormalizedStatImfile.*"
        " FROM detRun"
        " JOIN detStackedImfile"
        "   USING(det_id, iteration)"
        " JOIN detInputExp"
        "   USING(det_id, iteration)"
        " JOIN rawExp"
        "   ON detInputExp.exp_id = rawExp.exp_id"
        " JOIN detNormalizedStatImfile"
        "   ON detStackedImfile.det_id = detNormalizedStatImfile.det_id"
        "   AND detStackedImfile.iteration = detNormalizedStatImfile.iteration"
        "   AND detStackedImfile.class_id = detNormalizedStatImfile.class_id"
        " LEFT JOIN detNormalizedImfile"
        "   ON detNormalizedStatImfile.det_id = detNormalizedImfile.det_id"
        "   AND detNormalizedStatImfile.iteration = detNormalizedImfile.iteration"
        "   AND detNormalizedStatImfile.class_id = detNormalizedImfile.class_id"
        " WHERE"
        "   detNormalizedImfile.det_id IS NULL"
        "   AND detNormalizedImfile.iteration IS NULL"
        "   AND detNormalizedImfile.class_id IS NULL"
        "   AND detNormalizedStatImfile.fault = 0"
        );

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            psFree(output);
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingNormImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

#if 0
// XXX this function was left in commented as this method may be useful in the
// future
static psArray *validDetInputClassIds(pxConfig *config, const char *det_id)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);
    // det_id is input as a string because the fact that it is an integer
    // is just a database impliementation detail.
    PS_ASSERT_PTR_NON_NULL(det_id, NULL);

    psArray *rawImfiles = searchInputImfiles(config, det_id);
    if (!rawImfiles) {
        return NULL;
    }

    psArray *valid_class_ids = NULL;
    {
        // All this jumping through hoops is so that we end up with unique
        // values. PP thinks that making multiple passes through this array and
        // deleting matched elements would end up being more expensive then a
        // double sort and stagger scheme. JH thinks it would be cheaper to
        // just do a unqiue sort and delete.  So this is really just a cheap
        // hack to avoid implimenting a unique sort function but at least it's
        // stable.
        psHash *hash = psHashAlloc(psArrayLength(rawImfiles));
        for (long i = 0; i < psArrayLength(rawImfiles); i++) {
            psHashAdd(hash,
                ((rawImfileRow *)rawImfiles->data[i])->class_id,
                ((rawImfileRow *)rawImfiles->data[i])->class_id
            );
        }
        valid_class_ids = psHashToArray(hash);
        psFree(hash);
    }
    psFree(rawImfiles);

    return valid_class_ids;
}

static psArray *searchInputImfiles(pxConfig *config, const char *det_id)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);
    // det_id is input as a string because the fact that it is an integer
    // is just a database impliementation detail.
    PS_ASSERT_PTR_NON_NULL(det_id, NULL);

    psArray *inputExps = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        if (!psMetadataAddS32(where, PS_LIST_TAIL, "det_id", 0, "==",
                (psS32)atoi(det_id))) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item det_id");
            psFree(where);
            return NULL;
        }
        inputExps = detInputExpSelectRowObjects(config->dbh, where, 0);
        psFree(where);
    }
    if (!inputExps) {
        psError(PS_ERR_UNKNOWN, false, "no detInputExp rows found");
        return NULL;
    }

    // find rawImfiles associated with detInputExps
    psArray *rawImfiles = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        for (long i = 0; i < psArrayLength(inputExps); i++) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_id",
                    PS_META_DUPLICATE_OK, "==",
                    ((detInputExpRow *)inputExps->data[i])->exp_id)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
                psFree(inputExps);
                psFree(where);
                return NULL;
            }
        }
        psFree(inputExps);
        rawImfiles = rawImfileSelectRowObjects(config->dbh, where, 0);
        // XXX this really should be sorted for uniqueness
        psFree(where);
    }
    if (!rawImfiles) {
        psError(PS_ERR_UNKNOWN, false, "no rawImfile rows found");
        return NULL;
    }

    return rawImfiles;
}
#endif

static bool addnormalizedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // make sure that there is a respondoing entry in detNormalizedStatImfile
    // select * from detNormalizedStatImfile
    // by det_id, iteration, class_id
    // where det_id, iteration, class_id is not in detNormalizedImfile
    psString query = psStringCopy(
        "SELECT"
        "   detNormalizedStatImfile.*"
        " FROM detNormalizedStatImfile"
        " LEFT JOIN detNormalizedImfile"
        "   USING(det_id, iteration, class_id)"
        " WHERE"
        "  detNormalizedImfile.det_id IS NULL"
        "  AND detNormalizedImfile.iteration IS NULL"
        "  AND detNormalizedImfile.class_id IS NULL"
        );

    {
        // build a query to search by det_id, iteration, class_id
        psMetadata *where = psMetadataAlloc();
        bool status = false;
        psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
            psFree(where);
            psFree(query);
            return false;
        }
        if (det_id) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "det_id", 0, "==", det_id)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item det_id");
                psFree(where);
                psFree(query);
                return false;
            }
        }
        psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration");
            psFree(where);
            psFree(query);
            return false;
        }
        // always set iteration
        if (!psMetadataAddS32(where, PS_LIST_TAIL, "iteration", 0, "==", iteration)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item iteration");
            psFree(where);
            psFree(query);
            return false;
        }
        psString class_id = psMetadataLookupStr(&status, config->args, "-class_id");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -class_id");
            psFree(where);
            psFree(query);
            return false;
        }
        if (class_id) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "class_id", 0, "==", class_id)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item class_id");
                psFree(where);
                psFree(query);
                return false;
            }
        }

        // there's not
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detNormalizedStatImfile");
        psFree(where);
        if (whereClause) {
            psStringAppend(&query, " AND %s", whereClause);
            psFree(whereClause);
        }
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // start a transaction so it's all rows or nothing
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(output);
        return false;
    }

    for (long i = 0; i < psArrayLength(output); i++) {
        psMetadata *row = output->data[i];
        // convert metadata into a detNormalizedStatImfile object
        detNormalizedStatImfileRow *statImfile = detNormalizedStatImfileObjectFromMetadata(row);
        // convert detNormalizedStatImfile object into a detNormalizedImfile
        detNormalizedImfileRow *normalizedImfile  = detNormalizedStatToDetNormalizedmfile(config, statImfile);
        psFree(statImfile);
        if (!normalizedImfile) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to convert detStackedImfile to detNormalizedStatImfile");
            psFree(output);
            return false;
        }
        // insert detNormlized Stat object into the database
        if (!detNormalizedImfileInsertObject(config->dbh, normalizedImfile)) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "database error");
            psFree(normalizedImfile);
            psFree(output);
        }
        psFree(normalizedImfile);
    }

    psFree(output);

    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}


static bool normalizedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    psString query = psStringCopy(
        "SELECT"
        " detNormalizedImfile.*"
        " FROM detNormalizedImfile"
        " JOIN detRun"
        "   USING(det_id, iteration)"
        " WHERE"
        "   detRun.state = 'run'"
        "   AND detRun.mode = 'master'"
    );

    if (config->where) {
        bool status;
        int iteration = psMetadataLookupS32 (&status, config->where, "iteration");
        if (status) {
            psMetadataRemoveKey (config->where, "iteration");
            psMetadataAddS32 (config->where, PS_LIST_TAIL, "iteration", 0, "==", iteration);
        }
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detNormalizedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detNormalizedImfile.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detNormalizedImfile.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detNormalizedImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool revertnormalizedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psString query = pxDataGet("dettool_revertnormalizedimfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detNormalizedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}


static bool tonormalizedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    psString query = pxDataGet("dettool_tonormalizedexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingNormExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool addnormalizedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // det_id, recip, -bg, -bg_stdev
    // are required
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }
    psString recipe = psMetadataLookupStr(&status, config->args, "-recip");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -recip");
        return false;
    }
    if (!recipe) {
        psError(PS_ERR_UNKNOWN, true, "-recip is required");
        return false;
    }
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }
    //if (isnan(bg)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //  return false;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return false;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }

    // optional
    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    // iteration has a default value
    psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration");        return false;
    }

    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    // optional
    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        return false;
    }

    psString query = pxDataGet("dettool_tonormalizedexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    psStringAppend(&query,
            "WHERE det_id = %s"
            " AND iteration = %d", det_id, iteration);


    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }
    psFree(output);

    // create a new detProcessedImfile object
    detNormalizedExpRow *detRow = detNormalizedExpRowAlloc(
        (psS32)atoll(det_id),
        iteration,
        recipe,
        bg,
        bg_stdev,
        bg_mean_stdev,
        user_1,
        user_2,
        user_3,
        user_4,
        user_5,
        path_base,
        code
    );

    // insert the new row into the detProcessedImfile table
    if (!detNormalizedExpInsertObject(config->dbh, detRow)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(detRow);
        return false;
    }

    psFree(detRow);

    return true;
}


static bool normalizedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    psString query = psStringCopy(
        "SELECT"
        "   detNormalizedExp.*"
        " FROM detNormalizedExp"
        " JOIN detRun"
        "   USING(det_id, iteration)"
        " WHERE"
        "   detRun.state = 'run'"
        "   AND detRun.mode = 'master'"
    );

    if (config->where) {
        psString whereClause = psDBGenerateWhereSQL(config->where, NULL);
        psStringAppend(&query, " %s", whereClause);
        psFree(whereClause);
    }

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detNormalizedExp.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detNormalizedExp.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detNormalizedExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static  detNormalizedImfileRow *detNormalizedStatToDetNormalizedmfile(pxConfig *config, detNormalizedStatImfileRow *statImfile)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);
    PS_ASSERT_PTR_NON_NULL(statImfile, NULL);

    bool status = false;
    psString uri = psMetadataLookupStr(&status, config->args, "-uri");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -uri");
        return false;
    }
    if (!uri) {
        psError(PS_ERR_UNKNOWN, true, "-uri is required");
        return false;
    }
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }
    //if (isnan(bg)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //  return false;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return false;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }
    // optional
    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    // optional
    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        return false;
    }

    // default values
    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    return detNormalizedImfileRowAlloc(
        statImfile->det_id,
        statImfile->iteration,
        statImfile->class_id,
        uri,
        bg,
        bg_stdev,
        bg_mean_stdev,
        user_1,
        user_2,
        user_3,
        user_4,
        user_5,
        path_base,
        code
    );
}


static bool revertnormalizedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psString query = pxDataGet("dettool_revertnormalizedexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detNormalizedExp");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}


static bool toresidimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    psString query = pxDataGet("dettool_toresidimfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            psFree(output);
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingResidImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool addresidimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }

    // defaults to 0
    psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration");
        return false;
    }

    psString class_id = psMetadataLookupStr(&status, config->args, "-class_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -class_id");
        return false;
    }
    if (!class_id) {
        psError(PS_ERR_UNKNOWN, false, "-class_id is required");
        return false;
    }

    psString exp_id = psMetadataLookupStr(&status, config->args, "-exp_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_id");
        return false;
    }
    if (!exp_id) {
        psError(PS_ERR_UNKNOWN, true, "-exp_id is required");
        return false;
    }
    psString uri = psMetadataLookupStr(&status, config->args, "-uri");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -uri");
        return false;
    }
    if (!uri) {
        psError(PS_ERR_UNKNOWN, true, "-uri is required");
        return false;
    }
    psString recipe = psMetadataLookupStr(&status, config->args, "-recip");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -recip");
        return false;
    }
    if (!recipe) {
        psError(PS_ERR_UNKNOWN, true, "-recip is required");
        return false;
    }
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }
    //if (isnan(bg)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //  return false;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return false;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }
    psF64 bg_skewness = psMetadataLookupF64(&status, config->args, "-bg_skewness");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_skewness");
        return false;
    }
    psF64 bg_kurtosis = psMetadataLookupF64(&status, config->args, "-bg_kurtosis");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_kurtosis");
        return false;
    }
    psF64 bin_stdev = psMetadataLookupF64(&status, config->args, "-bin_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bin_stdev");
        return false;
    }

    // optional
    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        return false;
    }

    // optional
    psF64 fringe_0 = psMetadataLookupF64(&status, config->args, "-fringe_0");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_0");
        return false;
    }
    psF64 fringe_1 = psMetadataLookupF64(&status, config->args, "-fringe_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_1");
        return false;
    }
    psF64 fringe_2 = psMetadataLookupF64(&status, config->args, "-fringe_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_2");
        return false;
    }

    psF64 fringe_resid_0 = psMetadataLookupF64(&status, config->args, "-fringe_resid_0");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_resid_0");
        return false;
    }
    psF64 fringe_resid_1 = psMetadataLookupF64(&status, config->args, "-fringe_resid_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_resid_1");
        return false;
    }
    psF64 fringe_resid_2 = psMetadataLookupF64(&status, config->args, "-fringe_resid_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_resid_2");
        return false;
    }

    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    // default values
    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    // create a new detResidImfileRow and insert it
    if (!detResidImfileInsert(
            config->dbh,
            (psS64)atoll(det_id),
            iteration,
            (psS64)atoll(exp_id),
            class_id,
            uri,
            recipe,
            bg,
            bg_stdev,
            bg_mean_stdev,
            bg_skewness,
            bg_kurtosis,
            bin_stdev,
            fringe_0,
            fringe_1,
            fringe_2,
            fringe_resid_0,
            fringe_resid_1,
            fringe_resid_2,
            user_1,
            user_2,
            user_3,
            user_4,
            user_5,
            path_base,
            code
    )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}

static bool residimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    char *value = NULL;
    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    // select detResidImfile.*
    // by:
    // where det_id, iteration, exp_id is not in detResidExp;

    psString query = psStringCopy(
        "SELECT"
        "   detRun.det_type,"
        "   detRun.mode,"
        "   detResidImfile.*,"
        "   rawExp.exp_time"
        " FROM detResidImfile"
        " JOIN detRun"
        "   USING(det_id, iteration)"
        " JOIN rawExp"
        "   USING(exp_id)"
        " WHERE"
    );
    // NOTE the above WHERE is completed with the following line:

    // add the two required restrictions: detRun.state and detRun.mode
    if ((value = psMetadataLookupStr(&status, config->args, "-select_state"))) {
        psStringAppend(&query, " detRun.state = '%s'", value);
    } else {
        psStringAppend(&query, " detRun.state = 'run'");
    }

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detResidImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detResidImfile.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detResidImfile.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            psFree(output);
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "rawResidImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool revertresidimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psString query = pxDataGet("dettool_revertresidimfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detResidImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}


static bool toresidexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    /*
which returns a list of exposures for which all component class ids
have had residuals created.  This list includes the detrend id,
iteration, exposure id, detrend type, and whether the exposure was
included in the stack for this iteration.
    */


    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    psString query = pxDataGet("dettool_toresidexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingResidExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool addresidexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    /*
which returns a list of exposures for which all component class ids
have had residuals created.  This list includes the detrend id,
iteration, exposure id, detrend type, and whether the exposure was
included in the stack for this iteration.
    */


    // select detRun.det_id
    // select detRun.iteration
    // select detRun.det_type
    // select detInputExp.exp_id
    // select detInputExp.include
    // by:
    // find the current iteration bassed on det_id
    // find all exp_ids in the current det_id/iteration from detInputExp
    // compare to detInputExp.imfiles to derResidImfile by class_id
    // and:
    // detResidImfile.{det_id, iteration, exp_id} is not in detResidExp

    psString query = pxDataGet("dettool_toresidexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    {
        // build a query to search by det_id, iteration, exp_id
        psMetadata *where = psMetadataAlloc();
        bool status = false;
        psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
            return false;
        }
        if (!det_id) {
            psError(PS_ERR_UNKNOWN, true, "-det_id is required");
            return false;
        }
        if (!psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==", (psS64)atoll(det_id))) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item det_id");
            psFree(where);
            psFree(query);
            return false;
        }

        psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration");
            psFree(where);
            psFree(query);
            return false;
        }
        // always set iteration
        if (!psMetadataAddS32(where, PS_LIST_TAIL, "iteration", 0, "==", iteration)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item iteration");
            psFree(where);
            psFree(query);
            return false;
        }
        psString exp_id = psMetadataLookupStr(&status, config->args, "-exp_id");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_id");
            psFree(where);
            psFree(query);
            return false;
        }
        if (exp_id) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_id", 0, "==", exp_id)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
                psFree(where);
                psFree(query);
                return false;
            }
        }

        // there's not
        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
        psFree(where);
        if (whereClause) {
            psStringAppend(&query, " WHERE %s", whereClause);
            psFree(whereClause);
        }
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        // XXX check psError here
        psError(PS_ERR_UNKNOWN, false, "no detResidImfile rows found");
        psFree(output);
        return false;
    }

    // start a transaction so it's all rows or nothing
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(output);
        return false;
    }

    for (long i = 0; i < psArrayLength(output); i++) {
        psMetadata *row = output->data[i];
        // convert metadata into a detResidExp object
        detResidExpRow *residExp = mdToDetResidExp(config, row);
        if (!residExp) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to convert metadata to detResidExp");
            psFree(output);
            return false;
        }
        // insert detResidExp object into the database
        if (!detResidExpInsertObject(config->dbh, residExp)) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "database error");
            psFree(residExp);
            psFree(output);
        }
        psFree(residExp);
    }

    psFree(output);

    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}

static detResidExpRow *mdToDetResidExp(pxConfig *config, psMetadata *row)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);
    PS_ASSERT_PTR_NON_NULL(row, NULL);

    bool status = false;
    // values from row
    psS64 det_id = psMetadataLookupS64(&status, row, "det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for det_id");
        return false;
    }
    //if (isnan(det_id)) {
    //  psError(PS_ERR_UNKNOWN, true, "det_id is required");
    //  return false;
    //}
    psS32 iteration = psMetadataLookupS32(&status, row, "iteration");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for iteration");
        return false;
    }
    psS64 exp_id = psMetadataLookupS64(&status, row, "exp_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for exp_id");
        return false;
    }
    if (!exp_id) {
        psError(PS_ERR_UNKNOWN, true, "exp_id is required");
        return false;
    }

    // values from config
    psString recipe = psMetadataLookupStr(&status, config->args, "-recip");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -recip");
        return false;
    }
    if (!recipe) {
        psError(PS_ERR_UNKNOWN, true, "-recip is required");
        return false;
    }
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }
    //if (isnan(bg)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //  return false;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return false;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }
    psF64 bg_skewness = psMetadataLookupF64(&status, config->args, "-bg_skewness");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_skewness");
        return false;
    }
    psF64 bg_kurtosis = psMetadataLookupF64(&status, config->args, "-bg_kurtosis");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_kurtosis");
        return false;
    }
    psF64 bin_stdev = psMetadataLookupF64(&status, config->args, "-bin_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bin_stdev");
        return false;
    }
    // optional
    psF64 fringe_0 = psMetadataLookupF64(&status, config->args, "-fringe_0");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_0");
        return false;
    }
    psF64 fringe_1 = psMetadataLookupF64(&status, config->args, "-fringe_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_1");
        return false;
    }
    psF64 fringe_2 = psMetadataLookupF64(&status, config->args, "-fringe_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_2");
        return false;
    }

    psF64 fringe_resid_0 = psMetadataLookupF64(&status, config->args, "-fringe_resid_0");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_resid_0");
        return false;
    }
    psF64 fringe_resid_1 = psMetadataLookupF64(&status, config->args, "-fringe_resid_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_resid_1");
        return false;
    }
    psF64 fringe_resid_2 = psMetadataLookupF64(&status, config->args, "-fringe_resid_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_resid_2");
        return false;
    }

    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    // optional
    bool reject = psMetadataLookupBool(&status, config->args, "-reject");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -reject");
        return false;
    }
    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        return false;
    }
    if (!path_base) {
        psError(PS_ERR_UNKNOWN, true, "-path_base is required");
        return false;
    }

    // default values
    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    // create a new detResidImfileRow and insert it
    return detResidExpRowAlloc(
            det_id,
            iteration,
            exp_id,
            recipe,
            bg,
            bg_stdev,
            bg_mean_stdev,
            bg_skewness,
            bg_kurtosis,
            bin_stdev,
            fringe_0,
            fringe_1,
            fringe_2,
            fringe_resid_0,
            fringe_resid_1,
            fringe_resid_2,
            user_1,
            user_2,
            user_3,
            user_4,
            user_5,
            path_base,
            !reject,
            code
        );
}

static bool residexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    psString query = psStringCopy(
        "SELECT"
        "   detRun.mode,"
        "   detResidExp.*,"
        "   detInputExp.include"
        " FROM detRun"
        " JOIN detInputExp"
        "   USING(det_id, iteration)"
        " JOIN detResidExp"
        "   USING(det_id, iteration, exp_id)"
        " WHERE"
        "   detRun.state = 'run'"
    );

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detResidExp");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detResidExp.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detResidExp.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detResidExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool revertresidexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psString query = pxDataGet("dettool_revertresidexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detResidExp");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}


static bool todetrunsummaryMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    /* which returns a list of detrend runs (with detrend id, iteration and
     * detrend type) which have completed all residexps.
     */

    psString query = pxDataGet("dettool_todetrunsummary.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detRejectExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool updateresidexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // build a query to search by det_id, iteration, exp_id
    psMetadata *where = psMetadataAlloc();
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        psFree(where);
        return false;
    }
    if (det_id) {
        if (!psMetadataAddStr(where, PS_LIST_TAIL, "det_id", 0, "==", det_id)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item det_id");
            psFree(where);
            return false;
        }
    }
    psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration");
        psFree(where);
        return false;
    }
    // always where iteration
    if (!psMetadataAddS32(where, PS_LIST_TAIL, "iteration", 0, "==", iteration)) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item iteration");
        psFree(where);
        return false;
    }
    psString exp_id = psMetadataLookupStr(&status, config->args, "-exp_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_id");
        psFree(where);
        return false;
    }
    if (exp_id) {
        if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_id", 0, "==", exp_id)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
            psFree(where);
            return false;
        }
    }

    // find the values we're going to set
    // copy everything but det_id, iteration, & exp_id from the args and
    // remove the '-' prefix
    psMetadata *set = psMetadataAlloc();
    psString recipe = psMetadataLookupStr(&status, config->args, "-recip");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -recip");
        psFree(set);
        psFree(where);
        return false;
    }
    if (recipe) {
        if (!psMetadataAddStr(set, PS_LIST_TAIL, "recipe", 0, "==", recipe)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item recipe");
            psFree(set);
            psFree(where);
            return false;
        }
    }

    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        psFree(set);
        psFree(where);
        return false;
    }
    if (!isnan(bg)) {
        if (!psMetadataAddF64(set, PS_LIST_TAIL, "bg", 0, "==", bg)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item bg");
            psFree(set);
            psFree(where);
            return false;
        }
    }

    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        psFree(set);
        psFree(where);
        return false;
    }
    if (!isnan(bg_stdev)) {
        if (!psMetadataAddF64(set, PS_LIST_TAIL, "bg_stdev", 0, "==", bg_stdev)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item bg_stdev");
            psFree(set);
            psFree(where);
            return false;
        }
    }

    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        psFree(set);
        psFree(where);
        return false;
    }
    if (!isnan(bg_mean_stdev)) {
        if (!psMetadataAddF64(set, PS_LIST_TAIL, "bg_mean_stdev", 0, "==", bg_mean_stdev)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item bg_mean_stdev");
            psFree(set);
            psFree(where);
            return false;
        }
    }

    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        psFree(set);
        psFree(where);
        return false;
    }
    if (path_base) {
        if (!psMetadataAddStr(set, PS_LIST_TAIL, "path_base", 0, "==", path_base)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item path_base");
            psFree(set);
            psFree(where);
            return false;
        }
    }

    bool reject = psMetadataLookupBool(&status, config->args, "-reject");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -reject");
        psFree(set);
        psFree(where);
        return false;
    }
    if (!psMetadataAddBool(set, PS_LIST_TAIL, "accept", 0, "==", !reject)) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item accept");
        psFree(set);
        psFree(where);
        return false;
    }

    long changed = psDBUpdateRows(config->dbh, "detResidExp", where, set);
    psFree(set);
    psFree(where);

    if (changed < 0) {
        psError(PS_ERR_UNKNOWN, false, "no rows were updated");
        return false;
    }

    return true;
}

static bool adddetrunsummaryMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // required
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }

    // optional
    bool again = psMetadataLookupBool(&status, config->args, "-again");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -again");
        return false;
    }

    psString query = pxDataGet("dettool_find_completed_runs.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    {
        // build a query to search by det_id, iteration, exp_id
        psMetadata *where = psMetadataAlloc();
        bool status = false;
        if (det_id) {
            if (!psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==", (psS64)atoll(det_id))) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item det_id");
                psFree(where);
                psFree(query);
                return false;
            }
        }
        psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration");
            psFree(where);
            psFree(query);
            return false;
        }
        // always set iteration
        if (!psMetadataAddS32(where, PS_LIST_TAIL, "iteration", 0, "==", iteration)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item iteration");
            psFree(where);
            psFree(query);
            return false;
        }

        // there's not
        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
        psFree(where);
        if (whereClause) {
            psStringAppend(&query, " WHERE %s", whereClause);
            psFree(whereClause);
        }
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // start a transaction so it's all rows or nothing
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(output);
        return false;
    }

    for (long i = 0; i < psArrayLength(output); i++) {
        psMetadata *row = output->data[i];
        // convert metadata into a detResidExp object
        detRunSummaryRow *runSummary = mdToDetRunSummary(config, row);
        if (!runSummary) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to convert metadata to detResidExp");
            psFree(output);
            return false;
        }
        // insert detResidExp object into the database
        if (!detRunSummaryInsertObject(config->dbh, runSummary)) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "database error");
            psFree(runSummary);
            psFree(output);
            return false;
        }
        psFree(runSummary);
    }

    psFree(output);

    // XXX this logic does not deal with the case of -code being set
    if (again) {
        if (!startNewIteration(config, (psS64)atoll(det_id))) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to start new iteration");
            return false;
        }
    } else {
        // set detRun.state to stop
        if (!setDetRunState(config, (psS64)atoll(det_id), "stop")) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to set detRun.state");
            return false;
        }
    }

    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    return true;
}

static detRunSummaryRow *mdToDetRunSummary(pxConfig *config, psMetadata *row)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    // from row
    psS64 det_id = psMetadataLookupS64(&status, row, "det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for det_id");
        return false;
    }
    psS32 iteration = psMetadataLookupS32(&status, row, "iteration");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for iteration");
        return false;
    }

    // from config->args
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return NULL;
    }
    //if (isnan(bg)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //  return NULL;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return NULL;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return NULL;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return NULL;
    }
    // optional
    bool accept = psMetadataLookupBool(&status, config->args, "-accept");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -accept");
        return NULL;
    }
    // default values
    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    return detRunSummaryRowAlloc(
            det_id,
            iteration,
            bg,
            bg_stdev,
            bg_mean_stdev,
            accept,
            code
        );
}

static bool detrunsummaryMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    psString query = psStringCopy(
        "SELECT DISTINCT\n"
        "   detRunSummary.*,\n"
        "   detRun.det_type,\n"
        "   detRun.mode\n"
        " FROM detRun\n"
        " JOIN detRunSummary\n"
        "   USING(det_id, iteration)\n"
        " WHERE\n"
        "   detRun.state = 'run'\n"
        );

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, NULL);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detRunSummary.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detRunSummary.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "rawDetrendImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool revertdetrunsummaryMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psString query = pxDataGet("dettool_revertdetrunsummary.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detRunSummary");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}


static bool updatedetrunMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // det_id is required
    // XXX this isn't strictly nessicary but not having the det_id complicates
    // incrementing the iteration number
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }
    // either -rerun or -state must be specified
    bool again = psMetadataLookupBool(&status, config->args, "-again");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -again");
        return false;
    }
    psString state = psMetadataLookupStr(&status, config->args, "-state");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -state");
        return false;
    }
    if (!(again || state)) {
        psError(PS_ERR_UNKNOWN, true, "either -again or -state must be specified");
        return false;
    }
    if (again && state) {
        psError(PS_ERR_UNKNOWN, true, "either -again or -state must be specified");
        return false;
    }

    if (state) {
        // set detRun.state to state
        return setDetRunState(config, (psS64)atoll(det_id), state);
    }

    // else
    // -again
    if (!startNewIteration(config, (psS64)atoll(det_id))) {
        psError(PS_ERR_UNKNOWN, false, "failed to start new iteration");
        return false;
    }

    return true;
}

static bool startNewIteration(pxConfig *config, psS64 det_id)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psString query = pxDataGet("dettool_start_new_iteration.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // XXX this query was not restricted by det_id, resulting
    // in an inconsistent UPDATE below.  I added this AND clause
    // though there may be a cleaner method (EAM 2006.10.08)
    psStringAppend(&query, " WHERE det_id = %" PRId64 , det_id);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psError(PS_ERR_UNKNOWN, false, "det_id %" PRId64 " not found", det_id);
        psFree(output);
        return false;
    }

    // start a transaction so we don't end up with an incremented iteration
    // count but no detInputExps
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(output);
        return false;
    }

    // up the detRuns iteration count for for the single det_id we are
    // operating on
    // XXX this will have to changed in order to support multiple det_ids with
    // a single invocation of this functions
    psS32 newIteration = incrementIteration(config, det_id);
    if (!newIteration) {
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psFree(output);
        return false;
    }

    for (long i = 0; i < psArrayLength(output); i++) {
        psMetadata *row = output->data[i];
        bool status = false;
        psS64 exp_id = psMetadataLookupS64(&status, row, "exp_id");
        if (!status) {
            // rollback
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for exp_id");
            psFree(output);
            return false;
        }
        bool accept = psMetadataLookupBool(&status, row, "accept");
        if (!status) {
            // rollback
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for accept");
            psFree(output);
            return false;
        }

        // detResidExp.include is used to set detInputExp.include
        if (!detInputExpInsert(
                    config->dbh,
                    det_id,
                    newIteration,
                    exp_id,
                    accept
                )
        ) {
            // rollback
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "database error");
            psFree(output);
            return false;
        }
    }

    psFree(output);

    // point of no return for det_id creation
    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}

static bool rerunMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // det_id is required
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }

    // we have to support multipe exp_ids
    psMetadataItem *item = psMetadataLookup(config->args, "-exp_id");
    if (!item) {
        // this shouldn't actually happen when using psArgs
        psError(PS_ERR_UNKNOWN, true, "-exp_id is required");
        return false;
    }

    psList *exp_id_list = item->data.list;
    psMetadata *where = psMetadataAlloc();
    // make sure that -exp_id was parsed correctly
    // XXX this can be removed someday
    if (item->type == PS_DATA_METADATA_MULTI) {
        psListIterator *iter = psListIteratorAlloc(item->data.list, 0, false);
        psMetadataItem *mItem = NULL;
        while ((mItem = psListGetAndIncrement(iter))) {
            psString exp_id = mItem->data.V;
            // if exp_id is NULL then it means that -exp_id has not been
            // specified
            if (!exp_id) {
                psError(PS_ERR_UNKNOWN, true,
                        "at least one -exp_id is required");
                psFree(where);
                return false;
            }

            if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_id",
                        PS_META_DUPLICATE_OK, "==", exp_id)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
                psFree(iter);
                psFree(where);
                return false;
            }
        }
        psFree(iter);
    } else {
        psAbort("-exp_id was not parsed correctly (this should not happen");
    }

    // check that the specified exp_ids actually exist in the iteration zero
    // detInputExp set

    // add the det_id & iteration == 0 to the where clause
    if (!psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==",
                (psS64)atoll(det_id))) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item det_id");
        psFree(where);
        return false;
    }
    if (!psMetadataAddS32(where, PS_LIST_TAIL, "iteration", 0, "==", 0)) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item iteration");
        psFree(where);
        return false;
    }

    psArray *detrendExps = detInputExpSelectRowObjects(config->dbh, where, 0);
    psFree(where);
    if (!detrendExps) {
        psError(PS_ERR_UNKNOWN, false, "no rawExp rows found");
        psFree(where);
        return false;
    }

    // build a hash for the valid exp_ids
    psHash *valid_exp_ids = psHashAlloc(psArrayLength(detrendExps));
    for (long i = 0; i < psArrayLength(detrendExps); i++) {
        psString exp_idStr = psDBIntToString(((detInputExpRow *)detrendExps->data[i])->exp_id);
        psHashAdd(valid_exp_ids, exp_idStr, detrendExps->data[i]);
        psFree(exp_idStr);
    }
    psFree(detrendExps);

    // start a transaction so we don't end up with an incremented iteration
    // count but no detInputExps
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(valid_exp_ids);
        return false;
    }

    // up the detRuns iteration count
    psS32 newIteration = incrementIteration(config, (psS64)atoll(det_id));
    if (!newIteration) {
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psFree(valid_exp_ids);
        return false;
    }

    // check exp_ids and build up an array of new detInputExp rows at the same
    // time
    psListIterator *iter = psListIteratorAlloc(exp_id_list, 0, false);
    psMetadataItem *mItem = NULL;
    psArray *newInputExps = psArrayAllocEmpty(psListLength(exp_id_list));
    while ((mItem = psListGetAndIncrement(iter))) {
        detInputExpRow *inputExp = psHashLookup(valid_exp_ids,
                (char *)mItem->data.V);
        if (!inputExp) {
            // rollback
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            // invalid exp_id
            psError(PS_ERR_UNKNOWN, false, "exp_id %s is invalid for det_id %s",
                    (char *)mItem->data.V, det_id);
            psFree(iter);
            psFree(valid_exp_ids);
            return false;
        }
        detInputExpRow *newInputExp = detInputExpRowAlloc(
            (psS64)atoll(det_id),
            newIteration,
            inputExp->exp_id,
            true   // use
        );
        psArrayAdd(newInputExps, 0, newInputExp);
        psFree(newInputExp);
    }
    psFree(iter);
    psFree(valid_exp_ids);

    for (long i = 0; i < psArrayLength(newInputExps); i++) {
        if (!detInputExpInsertObject(config->dbh, newInputExps->data[i])) {
            psError(PS_ERR_UNKNOWN, false, "database error");
            // rollback
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psFree(newInputExps);
            return false;
        }
    }
    psFree(newInputExps);

    // point of no return for det_id creation
    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}

static bool register_detrendMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // required options
    bool status = false;
    psString det_type = psMetadataLookupStr(&status, config->args, "-det_type");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_type");
        return false;
    }
    if (!det_type) {
        psError(PS_ERR_UNKNOWN, true, "-det_type is required");
        return false;
    }

    psString filelevel = psMetadataLookupStr(&status, config->args, "-filelevel");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filelevel");
        return false;
    }
    if (!filelevel) {
        psError(PS_ERR_UNKNOWN, true, "-filelevel is required");
        return false;
    }

    psString workdir = psMetadataLookupStr(&status, config->args, "-workdir");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -workdir");
        return false;
    }

    psString camera = psMetadataLookupStr(&status, config->args, "-inst");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -inst");
        return false;
    }

    psString telescope = psMetadataLookupStr(&status, config->args, "-telescope");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -telescope");
        return false;
    }

    psString exp_type = psMetadataLookupStr(&status, config->args, "-exp_type");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_type");
        return false;
    }

    psString filter = psMetadataLookupStr(&status, config->args, "-filter");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filter");
        return false;
    }

    psF32 airmass_min = psMetadataLookupF32(&status, config->args, "-airmass_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -airmass_min");
        return false;
    }

    psF32 airmass_max = psMetadataLookupF32(&status, config->args, "-airmass_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -airmass_max");
        return false;
    }

    psF32 exp_time_min = psMetadataLookupF32(&status, config->args, "-exp_time_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_time_min");
        return false;
    }

    psF32 exp_time_max = psMetadataLookupF32(&status, config->args, "-exp_time_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_time_max");
        return false;
    }

    psF32 ccd_temp_min = psMetadataLookupF32(&status, config->args, "-ccd_temp_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -ccd_temp_min");
        return false;
    }

    psF32 ccd_temp_max = psMetadataLookupF32(&status, config->args, "-ccd_temp_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -ccd_temp_max");
        return false;
    }

    psF64 posang_min = psMetadataLookupF32(&status, config->args, "-posang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -posang_min");
        return false;
    }

    psF64 posang_max = psMetadataLookupF32(&status, config->args, "-posang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -posang_max");
        return false;
    }

    psF64 solang_min = psMetadataLookupF32(&status, config->args, "-solang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -solang_min");
        return false;
    }

    psF64 solang_max = psMetadataLookupF32(&status, config->args, "-solang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -solang_max");
        return false;
    }

    psTime *registered = NULL;
    {
        psString registeredStr = psMetadataLookupStr(&status, config->args, "-registered");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -registered");
            return false;
        }
        // pass through NULL as this is an optional field
        if (registeredStr) {
            registered = psTimeFromISO(registeredStr, PS_TIME_UTC);
        } else {
            registered = NULL;
        }
    }

    psTime *time_begin = NULL;
    {
        psString time_beginStr = psMetadataLookupStr(&status, config->args, "-time_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -time_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_beginStr) {
            time_begin = psTimeFromISO(time_beginStr, PS_TIME_UTC);
        } else {
            time_begin = NULL;
        }
    }

    psTime *time_end = NULL;
    {
        psString time_endStr = psMetadataLookupStr(&status, config->args, "-time_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -time_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_endStr) {
            time_end = psTimeFromISO(time_endStr, PS_TIME_UTC);
        } else {
            time_end = NULL;
        }
    }

    psTime *use_begin = NULL;
    {
        psString use_beginStr = psMetadataLookupStr(&status, config->args, "-use_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -use_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_beginStr) {
            use_begin = psTimeFromISO(use_beginStr, PS_TIME_UTC);
        } else {
            use_begin = NULL;
        }
    }

    psTime *use_end = NULL;
    {
        psString use_endStr = psMetadataLookupStr(&status, config->args, "-use_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -use_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_endStr) {
            use_end = psTimeFromISO(use_endStr, PS_TIME_UTC);
        } else {
            use_end = NULL;
        }
    }

    psString parent = psMetadataLookupStr(&status, config->args, "-parent");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -parent");
        return false;
    }

    psString label = psMetadataLookupStr(&status, config->args, "-label");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -label");
        return false;
    }

    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    if (!detRunInsert(config->dbh,
                      0,            // det_id
                      0,            // the iteration is fixed at 0
                      det_type,
                      "register",   // mode
                      "register",   // state
                      filelevel,
                      workdir,
                      camera,
                      telescope,
                      exp_type,
                      NULL,
                      filter,
                      airmass_min,
                      airmass_max,
                      exp_time_min,
                      exp_time_max,
                      ccd_temp_min,
                      ccd_temp_max,
                      posang_min,
                      posang_max,
                      registered,
                      time_begin,
                      time_end,
                      use_begin,
                      use_end,
                      solang_min,
                      solang_max,
                      label,      // label
                      parent ? (psS64)atoll(parent) : 0
            )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psFree(registered);
        return false;
    }
    psFree(registered);

    // print the new detRun
    psS64 det_id = psDBLastInsertID(config->dbh);

    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    psArray *detRuns = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==", det_id);
        detRuns = psDBSelectRows(config->dbh, "detRun", where, 0);
        psFree(where);
    }
    if (!detRuns) {
        psError(PS_ERR_UNKNOWN, false, "can't find the detRun we just created");
        return false;
    }
    // sanity check results
    if (psArrayLength(detRuns) != 1) {
        psAbort("found more then one detRun matching det_id %" PRId64 "(this should not happen)", det_id);
        return false;
    }

    if (!convertIdToStr(detRuns)) {
        psError(PS_ERR_UNKNOWN, false, "failed to convert id fields into a strings");
        psFree(detRuns);
        return false;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, detRuns, "detRun", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(detRuns);
        return false;
    }
    psFree(detRuns);

    return true;
}

static bool register_detrend_imfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // required options
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }

    psString class_id = psMetadataLookupStr(&status, config->args, "-class_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -class_id");
        return false;
    }
    if (!class_id) {
        psError(PS_ERR_UNKNOWN, true, "-class_id is required");
        return false;
    }

    psString uri    = psMetadataLookupStr(&status, config->args, "-uri");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -uri");
        return false;
    }
    if (!uri) {
        psError(PS_ERR_UNKNOWN, true, "-uri is required");
        return false;
    }

    // everything else is optional
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }

    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }

    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }

    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        return false;
    }

    if (!detRegisteredImfileInsert(config->dbh,
                                   (psS64)atoll(det_id),
                                   0,  // the iteration is fixed at 0
                                   class_id,
                                   uri,
                                   bg,
                                   bg_stdev,
                                   bg_mean_stdev,
                                   user_1,
                                   user_2,
                                   user_3,
                                   user_4,
                                   user_5,
                                   path_base,
                                   0       // fault code
            )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}

static psS32 incrementIteration(pxConfig *config, psS64 det_id)
{
    // this function returns zero on error
    PS_ASSERT_PTR_NON_NULL(config, 0);

    char *query = "UPDATE detRun SET iteration = iteration + 1 WHERE det_id = %" PRId64;
    if (!p_psDBRunQuery(config->dbh, query, det_id)) {
        psError(PS_ERR_UNKNOWN, false,
                "failed to increment iteration for det_id %" PRId64, det_id);
        return 0;
    }

    psMetadata *where = psMetadataAlloc();
    if (!psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==", det_id)) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item det_id");
        psFree(where);
        return 0;
    }

    psArray *detRuns = detRunSelectRowObjects(config->dbh, where, 0);
    psFree(where);
    if (!detRuns) {
        psError(PS_ERR_UNKNOWN, false, "no detRun rows found");
        return 0;
    }

    // sanity check the database
    if (psArrayLength(detRuns) != 1) {
        // this should no happen
        psAbort(                "database query return too many rows (this should not happen");
    }

    psS32 newIteration = ((detRunRow *)detRuns->data[0])->iteration;
    psFree(detRuns);

    return newIteration;
}

static bool setDetRunState(pxConfig *config, psS64 det_id, const char *state)
{
    PS_ASSERT_PTR_NON_NULL(config, false);
    PS_ASSERT_PTR_NON_NULL(state, false);

    // check that state is a valid string value
    if (!(
            (strncmp(state, "run", 4) == 0)
            || (strncmp(state, "stop", 5) == 0)
            || (strncmp(state, "drop", 5) == 0)
            || (strncmp(state, "register", 4) == 0)
        )
    ) {
        psError(PS_ERR_UNKNOWN, false,
                "invalid detRun state: %s", state);
        return false;
    }

    char *query = "UPDATE detRun SET state = '%s' WHERE det_id = %" PRId64;
    if (!p_psDBRunQuery(config->dbh, query, state, det_id)) {
        psError(PS_ERR_UNKNOWN, false,
                "failed to change state for det_id %" PRId64, det_id);
        return false;
    }

    return true;
}

static bool isValidMode(pxConfig *config, const char *mode)
{
    PS_ASSERT_PTR_NON_NULL(config, false);
    PS_ASSERT_PTR_NON_NULL(mode, false);

    // check that state is a valid string value
    if (!(
            (strncmp(mode, "master", 7) == 0)
            || (strncmp(mode, "verify", 7) == 0)
        )
    ) {
        psError(PS_ERR_UNKNOWN, false,
                "invalid detRun mode: %s", mode);
        return false;
    }

    return true;
}
