/*
 * regtool.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 HAVE_CONFIG_H
#include <config.h>
#endif

#include <stdlib.h>
#include <stdint.h>
#include <math.h>

#include "pxtools.h"
#include "pxdata.h"
#include "regtool.h"
#include "chiptool.h"

// imfile
static bool pendingimfileMode(pxConfig *config);
static bool addprocessedimfileMode(pxConfig *config);
static bool processedimfileMode(pxConfig *config);
static bool revertprocessedimfileMode(pxConfig *config);
static bool updateprocessedimfileMode(pxConfig *config);
// exp
static bool pendingexpMode(pxConfig *config);
static bool addprocessedexpMode(pxConfig *config);
static bool processedexpMode(pxConfig *config);
static bool revertprocessedexpMode(pxConfig *config);
static bool updateprocessedexpMode(pxConfig *config);

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

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

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

    switch (config->mode) {
        // imfile
        MODECASE(REGTOOL_MODE_PENDINGIMFILE,         pendingimfileMode);
        MODECASE(REGTOOL_MODE_ADDPROCESSEDIMFILE,    addprocessedimfileMode);
        MODECASE(REGTOOL_MODE_PROCESSEDIMFILE,       processedimfileMode);
        MODECASE(REGTOOL_MODE_REVERTPROCESSEDIMFILE, revertprocessedimfileMode);
        MODECASE(REGTOOL_MODE_UPDATEPROCESSEDIMFILE, updateprocessedimfileMode);
        // exp
        MODECASE(REGTOOL_MODE_PENDINGEXP,            pendingexpMode);
        MODECASE(REGTOOL_MODE_ADDPROCESSEDEXP,       addprocessedexpMode);
        MODECASE(REGTOOL_MODE_PROCESSEDEXP,          processedexpMode);
        MODECASE(REGTOOL_MODE_REVERTPROCESSEDEXP,    revertprocessedexpMode);
        MODECASE(REGTOOL_MODE_UPDATEPROCESSEDEXP,    updateprocessedexpMode);
        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 pendingimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    // select newImfiles that:
    // exp_id is in newExp
    // don't have their exp_id in rawExp
    // XXX having the same exp_id in newExp and raw*Exp is probably an error
    // that should be checked for

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

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

    if (!p_psDBRunQuery(config->dbh, query)) {
        // XXX PS_EXIT_PROG_ERROR (incorrect SQL) or SYS_ERROR (database comms)
        psError(PXTOOLS_ERR_PROG, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psErrorCode err = psErrorCodeLast();
        switch (err) {
            case PS_ERR_DB_CLIENT:
                psError(PXTOOLS_ERR_SYS, false, "database error");
            case PS_ERR_DB_SERVER:
                psError(PXTOOLS_ERR_PROG, false, "database error");
            default:
                psError(PXTOOLS_ERR_PROG, false, "unknown error");
        }

        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("regtool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

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

    psFree(output);

    return true;
}


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

    // required
    PXOPT_LOOKUP_STR(exp_id, config->args, "-exp_id", true, false);
    PXOPT_LOOKUP_STR(exp_name, config->args, "-exp_name", true, false);
    PXOPT_LOOKUP_STR(camera, config->args, "-inst", true, false);
    PXOPT_LOOKUP_STR(telescope, config->args, "-telescope", true, false);
    PXOPT_LOOKUP_STR(tmp_class_id, config->args, "-tmp_class_id", true, false);
    PXOPT_LOOKUP_STR(class_id, config->args, "-class_id", true, false);
    PXOPT_LOOKUP_STR(uri, config->args, "-uri", true, false);

    // optional
    PXOPT_LOOKUP_STR(exp_type, config->args, "-exp_type", false, false);
    PXOPT_LOOKUP_STR(filelevel, config->args, "-filelevel", false, false);
    PXOPT_LOOKUP_STR(filter, config->args, "-filter", false, false);
    PXOPT_LOOKUP_STR(comment, config->args, "-comment", false, false);

    PXOPT_LOOKUP_F32(airmass, config->args, "-airmass", false, false);
    PXOPT_LOOKUP_F64(ra, config->args, "-ra", false, false);
    PXOPT_LOOKUP_F64(decl, config->args, "-decl", false, false);
    PXOPT_LOOKUP_F32(exp_time, config->args, "-exp_time", false, false);
    PXOPT_LOOKUP_F32(sat_pixel_frac, config->args, "-sat_pixel_frac", false, false);
    PXOPT_LOOKUP_F64(bg, config->args, "-bg", false, false);
    PXOPT_LOOKUP_F64(bg_stdev, config->args, "-bg_stdev", false, false);
    PXOPT_LOOKUP_F64(bg_mean_stdev, config->args, "-bg_mean_stdev", false, false);
    PXOPT_LOOKUP_F64(alt, config->args, "-alt", false, false);
    PXOPT_LOOKUP_F64(az, config->args, "-az", false, false);
    PXOPT_LOOKUP_F32(ccd_temp, config->args, "-ccd_temp", false, false);
    PXOPT_LOOKUP_F64(posang, config->args, "-posang", false, false);
    PXOPT_LOOKUP_F32(m1_x, config->args, "-m1_x", false, false);
    PXOPT_LOOKUP_F32(m1_y, config->args, "-m1_y", false, false);
    PXOPT_LOOKUP_F32(m1_z, config->args, "-m1_z", false, false);
    PXOPT_LOOKUP_F32(m1_tip, config->args, "-m1_tip", false, false);
    PXOPT_LOOKUP_F32(m1_tilt, config->args, "-m1_tilt", false, false);
    PXOPT_LOOKUP_F32(m2_x, config->args, "-m2_x", false, false);
    PXOPT_LOOKUP_F32(m2_y, config->args, "-m2_y", false, false);
    PXOPT_LOOKUP_F32(m2_z, config->args, "-m2_z", false, false);
    PXOPT_LOOKUP_F32(m2_tip, config->args, "-m2_tip", false, false);
    PXOPT_LOOKUP_F32(m2_tilt, config->args, "-m2_tilt", false, false);

    PXOPT_LOOKUP_F32(env_temp, config->args, "-env_temperature", false, false);
    PXOPT_LOOKUP_F32(env_humid, config->args, "-env_humidity", false, false);
    PXOPT_LOOKUP_F32(env_wind, config->args, "-env_wind_speed", false, false);
    PXOPT_LOOKUP_F32(env_dir, config->args, "-env_wind_dir", false, false);
    PXOPT_LOOKUP_F32(teltemp_m1, config->args, "-teltemp_m1", false, false);
    PXOPT_LOOKUP_F32(teltemp_m1cell, config->args, "-teltemp_m1cell", false, false);
    PXOPT_LOOKUP_F32(teltemp_m2, config->args, "-teltemp_m2", false, false);
    PXOPT_LOOKUP_F32(teltemp_spider, config->args, "-teltemp_spider", false, false);
    PXOPT_LOOKUP_F32(teltemp_truss, config->args, "-teltemp_truss", false, false);
    PXOPT_LOOKUP_F32(teltemp_extra, config->args, "-teltemp_extra", false, false);
    PXOPT_LOOKUP_F32(pon_time, config->args, "-pon_time", false, false);
    PXOPT_LOOKUP_F64(user_1, config->args, "-user_1", false, false);
    PXOPT_LOOKUP_F64(user_2, config->args, "-user_2", false, false);
    PXOPT_LOOKUP_F64(user_3, config->args, "-user_3", false, false);
    PXOPT_LOOKUP_F64(user_4, config->args, "-user_4", false, false);
    PXOPT_LOOKUP_F64(user_5, config->args, "-user_5", false, false);
    PXOPT_LOOKUP_STR(object, config->args, "-object", false, false);
    PXOPT_LOOKUP_TIME(dateobs, config->args, "-dateobs", false, false);
    PXOPT_LOOKUP_S16(code, config->args, "-code", false, false);

    if (!rawImfileInsert(
        config->dbh,
        (psS64)atoll(exp_id),
        exp_name,
        camera,
        telescope,
        dateobs,
        tmp_class_id,
        class_id,
        uri,
        exp_type,
        filelevel,
        filter,
        comment,
        airmass,
        ra,
        decl,
        exp_time,
        sat_pixel_frac,
        bg,
        bg_stdev,
        bg_mean_stdev,
        alt,
        az,
        ccd_temp,
        posang,
        m1_x,
        m1_y,
        m1_z,
        m1_tip,
        m1_tilt,
        m2_x,
        m2_y,
        m2_z,
        m2_tip,
        m2_tilt,
        env_temp,
        env_humid,
        env_wind,
        env_dir,
        teltemp_m1,
        teltemp_m1cell,
        teltemp_m2,
        teltemp_spider,
        teltemp_truss,
        teltemp_extra,
        pon_time,
        user_1,
        user_2,
        user_3,
        user_4,
        user_5,
        object,
        code
    )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}


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

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
    PXOPT_LOOKUP_BOOL(faulted, config->args, "-faulted", false);

    psString query = pxDataGet("regtool_processedimfile.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 (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND rawImfile.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND rawImfile.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) {
        psErrorCode err = psErrorCodeLast();
        switch (err) {
            case PS_ERR_DB_CLIENT:
                psError(PXTOOLS_ERR_SYS, false, "database error");
            case PS_ERR_DB_SERVER:
                psError(PXTOOLS_ERR_PROG, false, "database error");
            default:
                psError(PXTOOLS_ERR_PROG, false, "unknown error");
        }

        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("regtool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    if (psArrayLength(output)) {
        // 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("regtool_revertprocessedimfile.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);

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

    return true;
}


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

    PXOPT_LOOKUP_S16(code, config->args, "-code", true, false);

    if (!pxSetFaultCode(config->dbh, "rawImfile", config->where, code)) {
        psError(PS_ERR_UNKNOWN, false, "failed to set set fault flag");
        return false;
    }

    return true;
}


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

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    // return only exps that:
    // are not in rawExp
    // have ALL of their imfiles in rawImfile (by count)
    // and have no associated imfiles left in newImfile
    psString query = pxDataGet("regtool_pendingexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

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

    if (!p_psDBRunQuery(config->dbh, query)) {
        // XXX PS_EXIT_PROG_ERROR (incorrect SQL) or SYS_ERROR (database comms)
        psError(PXTOOLS_ERR_PROG, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psErrorCode err = psErrorCodeLast();
        switch (err) {
            case PS_ERR_DB_CLIENT:
                psError(PXTOOLS_ERR_SYS, false, "database error");
            case PS_ERR_DB_SERVER:
                psError(PXTOOLS_ERR_PROG, false, "database error");
            default:
                psError(PXTOOLS_ERR_PROG, false, "unknown error");
        }

        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("regtool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negate simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "regPendingExp", !simple)) {
        psError(PXTOOLS_ERR_PROG, 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);

    // make sure that the exp_id(s) are ready to be updated based on:
    // exp_id is not in rawExp
    // exp_id is not in newImfile
    // that the correct count of imfiles is in rawImfile

    // required
    PXOPT_LOOKUP_STR(exp_id, config->args, "-exp_id", true, false);
    PXOPT_LOOKUP_STR(exp_name, config->args, "-exp_name", true, false);
    PXOPT_LOOKUP_STR(camera, config->args, "-inst", true, false);
    PXOPT_LOOKUP_STR(telescope, config->args, "-telescope", true, false);
    PXOPT_LOOKUP_STR(exp_tag, config->args, "-exp_tag", true, false);
    PXOPT_LOOKUP_STR(filelevel, config->args, "-filelevel", true, false);

    // optional
    PXOPT_LOOKUP_STR(dvodb, config->args, "-dvodb", false, false);
    PXOPT_LOOKUP_STR(tess_id, config->args, "-tess_id", false, false);
    PXOPT_LOOKUP_STR(end_stage, config->args, "-end_stage", false, false);
    PXOPT_LOOKUP_STR(exp_type, config->args, "-exp_type", false, false);
    PXOPT_LOOKUP_STR(workdir, config->args, "-workdir", false, false);
    PXOPT_LOOKUP_STR(reduction, config->args, "-reduction", false, false);
    PXOPT_LOOKUP_STR(filter, config->args, "-filter", false, false);
    PXOPT_LOOKUP_STR(comment, config->args, "-comment", false, false);
    PXOPT_LOOKUP_F32(airmass, config->args, "-airmass", false, false);
    PXOPT_LOOKUP_F64(ra, config->args, "-ra", false, false);
    PXOPT_LOOKUP_F64(decl, config->args, "-decl", false, false);
    PXOPT_LOOKUP_F32(exp_time, config->args, "-exp_time", false, false);
    PXOPT_LOOKUP_F32(sat_pixel_frac, config->args, "-sat_pixel_frac", false, false);
    PXOPT_LOOKUP_F64(bg, config->args, "-bg", false, false);
    PXOPT_LOOKUP_F64(bg_stdev, config->args, "-bg_stdev", false, false);
    PXOPT_LOOKUP_F64(bg_mean_stdev, config->args, "-bg_mean_stdev", false, false);
    PXOPT_LOOKUP_F64(alt, config->args, "-alt", false, false);
    PXOPT_LOOKUP_F64(az, config->args, "-az", false, false);
    PXOPT_LOOKUP_F32(ccd_temp, config->args, "-ccd_temp", false, false);
    PXOPT_LOOKUP_F64(posang, config->args, "-posang", false, false);
    PXOPT_LOOKUP_F32(m1_x, config->args, "-m1_x", false, false);
    PXOPT_LOOKUP_F32(m1_y, config->args, "-m1_y", false, false);
    PXOPT_LOOKUP_F32(m1_z, config->args, "-m1_z", false, false);
    PXOPT_LOOKUP_F32(m1_tip, config->args, "-m1_tip", false, false);
    PXOPT_LOOKUP_F32(m1_tilt, config->args, "-m1_tilt", false, false);
    PXOPT_LOOKUP_F32(m2_x, config->args, "-m2_x", false, false);
    PXOPT_LOOKUP_F32(m2_y, config->args, "-m2_y", false, false);
    PXOPT_LOOKUP_F32(m2_z, config->args, "-m2_z", false, false);
    PXOPT_LOOKUP_F32(m2_tip, config->args, "-m2_tip", false, false);
    PXOPT_LOOKUP_F32(m2_tilt, config->args, "-m2_tilt", false, false);

    PXOPT_LOOKUP_F32(env_temp, config->args, "-env_temperature", false, false);
    PXOPT_LOOKUP_F32(env_humid, config->args, "-env_humidity", false, false);
    PXOPT_LOOKUP_F32(env_wind, config->args, "-env_wind_speed", false, false);
    PXOPT_LOOKUP_F32(env_dir, config->args, "-env_wind_dir", false, false);
    PXOPT_LOOKUP_F32(teltemp_m1, config->args, "-teltemp_m1", false, false);
    PXOPT_LOOKUP_F32(teltemp_m1cell, config->args, "-teltemp_m1cell", false, false);
    PXOPT_LOOKUP_F32(teltemp_m2, config->args, "-teltemp_m2", false, false);
    PXOPT_LOOKUP_F32(teltemp_spider, config->args, "-teltemp_spider", false, false);
    PXOPT_LOOKUP_F32(teltemp_truss, config->args, "-teltemp_truss", false, false);
    PXOPT_LOOKUP_F32(teltemp_extra, config->args, "-teltemp_extra", false, false);
    PXOPT_LOOKUP_F32(pon_time, config->args, "-pon_time", false, false);
    PXOPT_LOOKUP_F64(user_1, config->args, "-user_1", false, false);
    PXOPT_LOOKUP_F64(user_2, config->args, "-user_2", false, false);
    PXOPT_LOOKUP_F64(user_3, config->args, "-user_3", false, false);
    PXOPT_LOOKUP_F64(user_4, config->args, "-user_4", false, false);
    PXOPT_LOOKUP_F64(user_5, config->args, "-user_5", false, false);
    PXOPT_LOOKUP_STR(object, config->args, "-object", false, false);
    PXOPT_LOOKUP_STR(label,  config->args, "-label", false, false);
    PXOPT_LOOKUP_TIME(dateobs, config->args, "-dateobs", false, false);

    // default
    PXOPT_LOOKUP_S16(code, config->args, "-code", false, false);

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

    {
        // build a query to search by exp_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);
            psFree(query);
            return false;
        }

        psString whereClause = psDBGenerateWhereSQL(where, NULL);
        psFree(where);
        if (whereClause) {
	    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) {
        psErrorCode err = psErrorCodeLast();
        switch (err) {
            case PS_ERR_DB_CLIENT:
                psError(PXTOOLS_ERR_SYS, false, "database error");
            case PS_ERR_DB_SERVER:
                psError(PXTOOLS_ERR_PROG, false, "database error");
            default:
                psError(PXTOOLS_ERR_PROG, false, "unknown error");
        }

        return false;
    }
    if (!psArrayLength(output)) {
        psError(PS_ERR_UNKNOWN, false, "no pending newExp rows found");
        psFree(output);
        return false;
    }
    // sanity check that we only got one row
    if (psArrayLength(output) != 1) {
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "should have gotten 1 row but %lu rows were returned", psArrayLength(output));
        psFree(output);
        return NULL;
    }

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

    // insert the exp into rawExp
    psMetadata *row = output->data[0];
    // convert metadata into a newExp object
    newExpRow *newExp = newExpObjectFromMetadata(row);
    psFree(output);

    // carry through these values
    // new CLI options overwrite existing values
    workdir   = workdir   ? workdir   : newExp->workdir;
    reduction = reduction ? reduction : newExp->reduction;
    dvodb     = dvodb     ? dvodb     : newExp->dvodb;
    tess_id   = tess_id   ? tess_id   : newExp->tess_id;
    end_stage = end_stage ? end_stage : newExp->end_stage;
    label     = label     ? label     : newExp->label;
    // don't free newExp until just before we return, or these refs will break

    if (!rawExpInsert(config->dbh,
        (psS64)atoll(exp_id),
        exp_name,
        camera,
        telescope,
        dateobs,
        exp_tag,
        exp_type,
        filelevel,
        workdir,
        reduction,
        dvodb,
        tess_id,
        end_stage,
        filter,
        comment,
        airmass,
        ra,
        decl,
        exp_time,
        sat_pixel_frac,
        bg,
        bg_stdev,
        bg_mean_stdev,
        alt,
        az,
        ccd_temp,
        posang,
        m1_x,
        m1_y,
        m1_z,
        m1_tip,
        m1_tilt,
        m2_x,
        m2_y,
        m2_z,
        m2_tip,
        m2_tilt,
        env_temp,
        env_humid,
        env_wind,
        env_dir,
        teltemp_m1,
        teltemp_m1cell,
        teltemp_m2,
        teltemp_spider,
        teltemp_truss,
        teltemp_extra,
        pon_time,
        user_1,
        user_2,
        user_3,
        user_4,
        user_5,
        object,
        0.0,
        code
    )) {
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "database error");
	psFree(newExp);
        return false;
    }

    // set the state for the newExp to stop
    if (!pxnewExpSetState(config, exp_id, "stop")) {
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "failed to change newExp.state for exp_id: %s", exp_id);
	psFree(newExp);
        return false;
    }

    // should we stop here and proceed on to the chip stage?
    // NULL for end_stage means go as far as possible
    if (end_stage && psStrcasestr(end_stage, "reg")) {
        // then we are done here
        if (!psDBCommit(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
            return false;
        }

	psFree(newExp);
        return true;
    }
    // else continue on...


    // insert an entry into the chipPendingExp table
    // this can only be run as the newExp's state has been set to stop
    if (!pxchipQueueByExpTag(config,
                (psS64)atoll(exp_id),
                workdir,
	        label,
                reduction,
                NULL, // expgroup
                dvodb,
                tess_id,
                end_stage
    )) {
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "failed to queue chipPendingExp");
	psFree(newExp);
        return false;
    }

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

    psFree(newExp);
    return true;
}


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

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
    PXOPT_LOOKUP_BOOL(faulted, config->args, "-faulted", false);

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

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

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND rawExp.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND rawExp.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");
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psErrorCode err = psErrorCodeLast();
        switch (err) {
            case PS_ERR_DB_CLIENT:
                psError(PXTOOLS_ERR_SYS, false, "database error");
            case PS_ERR_DB_SERVER:
                psError(PXTOOLS_ERR_PROG, false, "database error");
            default:
                psError(PXTOOLS_ERR_PROG, false, "unknown error");
        }

        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("regtool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    if (psArrayLength(output)) {
        // 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 revertprocessedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

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

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

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

    return true;
}


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

    PXOPT_LOOKUP_S16(code, config->args, "-code", true, false);

    if (!pxSetFaultCode(config->dbh, "rawExp", config->where, code)) {
        psError(PS_ERR_UNKNOWN, false, "failed to set set fault flag");
        return false;
    }

    return true;
}
