/*
 * 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);
static bool cleardupexpMode(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);
        MODECASE(REGTOOL_MODE_CLEARDUPEXP,           cleardupexpMode);
        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_S64(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_STR(obs_mode, config->args, "-obs_mode", false, false);
    PXOPT_LOOKUP_STR(obs_group, config->args, "-obs_group", 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_F32(sun_angle,  config->args, "-sun_angle", false, false);
    PXOPT_LOOKUP_F32(sun_alt,    config->args, "-sun_alt",   false, false);
    PXOPT_LOOKUP_F32(moon_angle, config->args, "-moon_angle", false, false);
    PXOPT_LOOKUP_F32(moon_alt,   config->args, "-moon_alt",   false, false);
    PXOPT_LOOKUP_F32(moon_phase, config->args, "-moon_phase", false, false);
    PXOPT_LOOKUP_BOOL(ignored, config->args, "-ignore", false);
    PXOPT_LOOKUP_TIME(dateobs, config->args, "-dateobs", false, false);
    PXOPT_LOOKUP_STR(hostname, config->args, "-hostname", false, false);
    PXOPT_LOOKUP_S16(code, config->args, "-code", false, false);

    if (!rawImfileInsert(
        config->dbh,
        exp_id,
        exp_name,
        camera,
        telescope,
        dateobs,
        tmp_class_id,
        class_id,
        uri,
        exp_type,
        filelevel,
        filter,
        comment,
        obs_mode,
        obs_group,
        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,
        sun_angle,
        sun_alt,
        moon_angle,
        moon_alt,
        moon_phase,
        ignored,
        hostname,
        code,
        NULL,
        0
    )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}


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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where,  "-exp_id", "exp_id", "==");
    PXOPT_COPY_STR(config->args, where,  "-exp_name", "exp_name", "==");
    PXOPT_COPY_STR(config->args, where,  "-class_id", "class_id", "==");

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

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

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

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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where,  "-exp_id",       "exp_id", "==");
    PXOPT_COPY_STR(config->args, where,  "-tmp_class_id", "tmp_class_id", "==");
    PXOPT_COPY_STR(config->args, where,  "-class_id",     "class_id", "==");
    PXOPT_COPY_S16(config->args, where,  "-code",         "fault", "==");

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

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

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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where,  "-exp_id",       "exp_id", "==");
    PXOPT_COPY_STR(config->args, where,  "-class_id",     "class_id", "==");

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

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

    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"
    psString limitString = NULL;
    if (limit) {
        limitString = psDBGenerateLimitSQL(limit);
        // skip past the "hook" comment
        psStringPrepend(&limitString, "\n");
    }

    // 1st arg: where hook, 2nd arg: limit hook
    if (!p_psDBRunQuery(config->dbh, query, "", limitString ? limitString : "")) {
        // XXX PS_EXIT_PROG_ERROR (incorrect SQL) or SYS_ERROR (database comms)
        psError(PXTOOLS_ERR_PROG, false, "database error");
        psFree(limitString);
        psFree(query);
        return false;
    }
    psFree(limitString);
    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_S64(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_TIME(dateobs, config->args, "-dateobs", 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(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(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_STR(obs_mode, config->args, "-obs_mode", false, false);
    PXOPT_LOOKUP_STR(obs_group, config->args, "-obs_group", 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_F32(sun_angle,  config->args, "-sun_angle", false, false);
    PXOPT_LOOKUP_F32(sun_alt,    config->args, "-sun_alt",   false, false);
    PXOPT_LOOKUP_F32(moon_angle, config->args, "-moon_angle", false, false);
    PXOPT_LOOKUP_F32(moon_alt,   config->args, "-moon_alt",   false, false);
    PXOPT_LOOKUP_F32(moon_phase, config->args, "-moon_phase", false, false);
    PXOPT_LOOKUP_STR(label,  config->args, "-label", false, false);
    PXOPT_LOOKUP_STR(hostname, config->args, "-hostname", 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;
    }

    psString whereClause = NULL;
    {
        // build a query to search by exp_id
        psMetadata *where = psMetadataAlloc();
        if (!psMetadataAddS64(where, PS_LIST_TAIL, "newExp.exp_id", 0, "==", exp_id)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
            psFree(where);
            psFree(query);
            return false;
        }

        whereClause = psDBGenerateWhereConditionSQL(where, NULL);
        psFree(where);
        if (whereClause) {
            // skip past comment "hook"
            psStringPrepend(&whereClause, "\n AND ");
        }
    }

    // 1st arg: where hook, 2nd arg: limit hook
    if (!p_psDBRunQuery(config->dbh, query, whereClause ? whereClause : "", "")) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(whereClause);
        psFree(query);
        return false;
    }
    psFree(whereClause);
    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
    psMetadataConfigPrint(stdout, row);
    newExpRow *newExp = newExpObjectFromMetadata(row);
    psFree(output);
    if (!newExp) {
        psError(PS_ERR_UNKNOWN, false, "this should not happen");
        return false;
    }

    // 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,
        exp_id,
        exp_name,
        camera,
        telescope,
        dateobs,
        exp_tag,
        exp_type,
        filelevel,
        workdir,
        reduction,
        dvodb,
        tess_id,
        end_stage,
        filter,
        comment,
        obs_mode,
        obs_group,
        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,
        sun_angle,
        sun_alt,
        moon_angle,
        moon_alt,
        moon_phase,
        hostname,
        code,
        NULL
    )) {
        // 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: %"PRId64, 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,
                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);

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

    // XX test this out; need to make this consistent with the list in regtoolConfig.c
    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where,  "-exp_id", "exp_id", "==");
    PXOPT_COPY_STR(config->args, where,  "-exp_name", "exp_name", "==");
    PXOPT_COPY_STR(config->args, where,  "-inst", "camera", "==");
    PXOPT_COPY_STR(config->args, where,  "-telescope", "telescope", "==");
    PXOPT_COPY_TIME(config->args, where, "-dateobs_begin", "dateobs", ">=");
    PXOPT_COPY_TIME(config->args, where, "-dateobs_end", "dateobs", "<=");
    PXOPT_COPY_STR(config->args, where,  "-exp_tag", "exp_tag", "==");
    PXOPT_COPY_STR(config->args, where,  "-exp_type", "exp_type", "==");
    PXOPT_COPY_STR(config->args, where,  "-filelevel", "filelevel", "==");
    PXOPT_COPY_STR(config->args, where,  "-reduction", "reduction", "==");
    PXOPT_COPY_STR(config->args, where,  "-filter", "filter", "==");
    PXOPT_COPY_F32(config->args, where,  "-airmass_min", "airmass", ">=");
    PXOPT_COPY_F32(config->args, where,  "-airmass_max", "airmass", "<");
    PXOPT_COPY_F64(config->args, where,  "-ra_min", "ra", ">=");
    PXOPT_COPY_F64(config->args, where,  "-ra_max", "ra", "<");
    PXOPT_COPY_F64(config->args, where,  "-decl_min", "decl", ">=");
    PXOPT_COPY_F64(config->args, where,  "-decl_max", "decl", "<");
    PXOPT_COPY_F32(config->args, where,  "-exp_time_min", "exp_time", ">=");
    PXOPT_COPY_F32(config->args, where,  "-exp_time_max", "exp_time", "<");
    PXOPT_COPY_F32(config->args, where,  "-sat_pixel_frac_min", "sat_pixel_frac", ">=");
    PXOPT_COPY_F32(config->args, where,  "-sat_pixel_frac_max", "sat_pixel_frac", "<");
    PXOPT_COPY_F64(config->args, where,  "-bg_min", "bg", ">=");
    PXOPT_COPY_F64(config->args, where,  "-bg_max", "bg", "<");
    PXOPT_COPY_F64(config->args, where,  "-bg_stdev_min", "bg_stdev", ">=");
    PXOPT_COPY_F64(config->args, where,  "-bg_stdev_max", "bg_stdev", "<");
    PXOPT_COPY_F64(config->args, where,  "-bg_mean_stdev_min", "bg_mean_stdev", ">=");
    PXOPT_COPY_F64(config->args, where,  "-bg_mean_stdev_max", "bg_mean_stdev", "<");
    PXOPT_COPY_F64(config->args, where,  "-alt_min", "alt", ">=");
    PXOPT_COPY_F64(config->args, where,  "-alt_max", "alt", "<");
    PXOPT_COPY_F64(config->args, where,  "-az_min", "az", ">=");
    PXOPT_COPY_F64(config->args, where,  "-az_max", "az", "<");
    PXOPT_COPY_F64(config->args, where,  "-ccd_temp_min", "ccd_temp", ">=");
    PXOPT_COPY_F64(config->args, where,  "-ccd_temp_max", "ccd_temp", "<");
    PXOPT_COPY_F64(config->args, where,  "-posang_min", "posang", ">=");
    PXOPT_COPY_F64(config->args, where,  "-posang_max", "posang", "<");
    PXOPT_COPY_STR(config->args, where,  "-object", "object", "==");
    PXOPT_COPY_F32(config->args, where,  "-solang_min", "solang", ">=");
    PXOPT_COPY_F32(config->args, where,  "-solang_max", "solang", "<");

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

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

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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where,  "-exp_id",       "exp_id", "==");
    PXOPT_COPY_S16(config->args, where,  "-code",         "fault", "==");

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

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

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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where,  "-exp_id",       "exp_id", "==");

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

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

    return true;
}


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

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

// create temp table of exp_ids to be removed
{
    psString query = pxDataGet("regtool_create_dup_table.sql");
    if (!query) {
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

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

// populate that table
// XXX note that this query currently doesn't not correctly handle the case
// where there is more than one duplicate
{
    psString query = pxDataGet("regtool_populate_dup_table.sql");
    if (!query) {
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

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

    if (!p_psDBRunQuery(config->dbh, "DELETE FROM rawImfile USING rawImfile, duplicate WHERE duplicate.exp_id = rawImfile.exp_id")) {
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    if (!p_psDBRunQuery(config->dbh, "DELETE FROM rawExp USING rawExp, duplicate WHERE duplicate.exp_id = rawExp.exp_id")) {
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    if (!p_psDBRunQuery(config->dbh, "DELETE FROM newImfile USING newImfile, duplicate WHERE duplicate.exp_id = newImfile.exp_id")) {
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    if (!p_psDBRunQuery(config->dbh, "DELETE FROM newExp USING newExp, duplicate WHERE duplicate.exp_id = newExp.exp_id")) {
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

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

    return true;
}
