/*
 * pstamptool.c
 *
 * Copyright (C) 2008
 *
 * 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 <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <inttypes.h>

#include "pxtools.h"
#include "pxdata.h"
#include "pstamptool.h"
#include "pstamp.h"

static bool adddatastoreMode(pxConfig *config);
static bool datastoreMode(pxConfig *config);
static bool moddatastoreMode(pxConfig *config);
static bool addreqMode(pxConfig *config);
static bool completedreqMode(pxConfig *config);
static bool listreqMode(pxConfig *config);
static bool pendingreqMode(pxConfig *config);
static bool updatereqMode(pxConfig *config);
static bool revertreqMode(pxConfig *config);
static bool pendingcleanupMode(pxConfig *config);
static bool addjobMode(pxConfig *config);
static bool listjobMode(pxConfig *config);
static bool pendingjobMode(pxConfig *config);
static bool updatejobMode(pxConfig *config);
static bool revertjobMode(pxConfig *config);
static bool addprojectMode(pxConfig *config);
static bool projectMode(pxConfig *config);
static bool modprojectMode(pxConfig *config);
static bool getdependentMode(pxConfig *config);
static bool pendingdependentMode(pxConfig *config);
static bool updatedependentMode(pxConfig *config);
static bool revertdependentMode(pxConfig *config);
static bool getwebrequestnumMode(pxConfig *config);

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


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

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

    switch (config->mode) {
        MODECASE(PSTAMPTOOL_MODE_ADDDATASTORE, adddatastoreMode);
        MODECASE(PSTAMPTOOL_MODE_DATASTORE, datastoreMode);
        MODECASE(PSTAMPTOOL_MODE_MODDATASTORE, moddatastoreMode);
        MODECASE(PSTAMPTOOL_MODE_ADDREQ, addreqMode);
        MODECASE(PSTAMPTOOL_MODE_COMPLETEDREQ, completedreqMode);
        MODECASE(PSTAMPTOOL_MODE_LISTREQ, listreqMode);
        MODECASE(PSTAMPTOOL_MODE_PENDINGREQ, pendingreqMode);
        MODECASE(PSTAMPTOOL_MODE_UPDATEREQ, updatereqMode);
        MODECASE(PSTAMPTOOL_MODE_REVERTREQ, revertreqMode);
        MODECASE(PSTAMPTOOL_MODE_PENDINGCLEANUP, pendingcleanupMode);
        MODECASE(PSTAMPTOOL_MODE_ADDJOB, addjobMode);
        MODECASE(PSTAMPTOOL_MODE_LISTJOB, listjobMode);
        MODECASE(PSTAMPTOOL_MODE_PENDINGJOB, pendingjobMode);
        MODECASE(PSTAMPTOOL_MODE_UPDATEJOB, updatejobMode);
        MODECASE(PSTAMPTOOL_MODE_REVERTJOB, revertjobMode);
        MODECASE(PSTAMPTOOL_MODE_ADDPROJECT, addprojectMode);
        MODECASE(PSTAMPTOOL_MODE_MODPROJECT, modprojectMode);
        MODECASE(PSTAMPTOOL_MODE_PROJECT, projectMode);
        MODECASE(PSTAMPTOOL_MODE_GETDEPENDENT, getdependentMode);
        MODECASE(PSTAMPTOOL_MODE_PENDINGDEPENDENT, pendingdependentMode);
        MODECASE(PSTAMPTOOL_MODE_UPDATEDEPENDENT, updatedependentMode);
        MODECASE(PSTAMPTOOL_MODE_REVERTDEPENDENT, revertdependentMode);
        MODECASE(PSTAMPTOOL_MODE_GETWEBREQUESTNUM, getwebrequestnumMode);
        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 adddatastoreMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_STR(uri,         config->args, "-set_uri",           true, false);
    PXOPT_LOOKUP_STR(outProduct,  config->args, "-set_out_product",   true, false);
    PXOPT_LOOKUP_STR(lastFileset, config->args, "-set_last_fileset", false, false);
    PXOPT_LOOKUP_STR(state,       config->args, "-set_state",         false, false);
    PXOPT_LOOKUP_STR(label,       config->args, "-set_label",         false, false);
    PXOPT_LOOKUP_S32(pollInterval, config->args, "-set_poll_interval",false, false);

    if (!pstampDataStoreInsert(config->dbh,
            0,
            state,
            lastFileset,
            NULL,       // timestamp
            label,      // label
            outProduct,
            uri,
            pollInterval
        )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}

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

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

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

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

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereSQL(where, NULL);
        psStringAppend(&query, " %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);
    if (ready) {
        psStringAppend(&query, " %s", "\nAND TIMESTAMPDIFF(SECOND, timestamp, now()) > pollInterval");
    }

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

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

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

    psFree(output);

    return true;
}
static bool moddatastoreMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_S64(ds_id,       config->args, "-ds_id",         true, false);
    PXOPT_LOOKUP_STR(lastFileset, config->args, "-set_last_fileset",  false, false);
    PXOPT_LOOKUP_STR(state,       config->args, "-set_state",         false, false);
    PXOPT_LOOKUP_STR(label,       config->args, "-set_label",         false, false);
    PXOPT_LOOKUP_S32(pollInterval, config->args, "-set_poll_interval",         false, false);
    PXOPT_LOOKUP_BOOL(update_timestamp, config->args, "-update_timestamp", false);

    if (!state && !lastFileset && !pollInterval && !update_timestamp && !label) {
        psError(PS_ERR_UNKNOWN, true, "at least one of -last_fileset or -set_state is required");
        return false;
    }

    char *query = psStringCopy ("UPDATE pstampDataStore SET timestamp = CURRENT_TIMESTAMP() ");

    if (lastFileset) {
        psStringAppend(&query, " , lastFileset = '%s'", lastFileset);
    }

    if (state) {
        psStringAppend(&query, ", state = '%s'", state);
    }

    if (label) {
        psStringAppend(&query, ", label = '%s'", label);
    }

    if (pollInterval) {
        psStringAppend(&query, ", pollInterval = %d", pollInterval);
    }

    psStringAppend(&query, " WHERE ds_id = %" PRId64, ds_id);

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

    psU64 affected = psDBAffectedRows(config->dbh);
    if (affected != 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected one row but %"
                                        PRIu64 " rows were modified", affected);
        return false;
    }

    return true;
}

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

    PXOPT_LOOKUP_STR(uri,         config->args, "-uri",   true, false);
    PXOPT_LOOKUP_STR(name,        config->args, "-name",  false, false);
    PXOPT_LOOKUP_STR(label,       config->args, "-label",  false, false);
    PXOPT_LOOKUP_S64(ds_id,       config->args, "-ds_id", false, false);

    if (!pstampRequestInsert(config->dbh,
        0,      // req_id
        ds_id,
        "new",  //state
        name,
        NULL,   // reqType
        label,
        NULL,   // outProduct
        uri,
        NULL,   // outdir
        NULL,   // timestamp
        0       // fault
        )) {
        psError(PS_ERR_UNKNOWN, false, "failed to insert request");
        return false;
    }

    psS64 req_id = psDBLastInsertID(config->dbh);

    printf("%" PRId64 "\n", req_id);

    return true;
}

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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where, "-req_id", "req_id", "==");
    pxAddLabelSearchArgs(config, where, "-label", "label", "LIKE");

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

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

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

    psStringAppend(&query, " ORDER BY priority DESC, req_id");

    // 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("pstamptool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

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

    psFree(output);

    return true;
}

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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where, "-req_id", "req_id", "==");
    pxAddLabelSearchArgs(config, where, "-label", "label", "LIKE");

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

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

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

    psStringAppend(&query, " ORDER BY priority DESC, req_id");

    // 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("pstamptool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

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

    psFree(output);

    return true;
}

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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where, "-req_id", "req_id", "==");
    PXOPT_COPY_S64(config->args, where, "-not_req_id", "req_id", "!=");
    PXOPT_COPY_STR(config->args, where, "-name", "name", "==");

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

    if (!psListLength(where->list)) {
        psError(PS_ERR_UNKNOWN, true, "-req_id or -name must be supplied");
        return false;
    }

    psString query = psStringCopy("SELECT * from pstampRequest");

    psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
    psStringAppend(&query, " WHERE %s", whereClause);
    psFree(whereClause);
    psFree(where);

    // 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("pstamptool", PS_LOG_INFO, "request not found");
        // This causes main to exit with PS_EXIT_DATA_ERROR which the script is looking for
        psError(PXTOOLS_ERR_CONFIG, true, "request not found");
        psFree(output);
        // we return false so that the caller can determine that a request does not exist
        return false;
    }

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

    psFree(output);

    return true;
}

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

    psMetadata *where = psMetadataAlloc();
    pxAddLabelSearchArgs(config, where, "-label", "label", "LIKE");

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

    psString query = pxDataGet("pstamptool_completedreq.sql");

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

    psStringAppend(&query, " ORDER BY priority DESC, req_id");

    // 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;
    }

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

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

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

    psFree(output);

    return true;
}

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

    // PXOPT_LOOKUP_S64(req_id,     config->args, "-req_id",   true, false);
    PXOPT_LOOKUP_STR(state,      config->args, "-set_state",      false, false);
    PXOPT_LOOKUP_STR(label,      config->args, "-set_label",      false, false);
    PXOPT_LOOKUP_STR(outProduct, config->args, "-set_outProduct", false, false);
    PXOPT_LOOKUP_S16(fault,      config->args, "-set_fault",          false, false);
    PXOPT_LOOKUP_STR(uri,        config->args, "-set_uri",        false, false);
    PXOPT_LOOKUP_STR(outdir,     config->args, "-set_outdir",        false, false);
    PXOPT_LOOKUP_STR(name,       config->args, "-set_name",       false, false);
    PXOPT_LOOKUP_STR(reqType,    config->args, "-set_reqType",    false, false);
    PXOPT_LOOKUP_BOOL(clearfault,config->args, "-clearfault",    false);

    if (!state && !label && !outProduct && !fault && !uri && !outdir && !name && !reqType && !clearfault) {
        psError(PS_ERR_UNKNOWN, true, "at least one set option is required");
        return false;
    }

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where, "-req_id",     "req_id", "==");
    PXOPT_COPY_S64(config->args, where, "-req_id_max", "req_id", "<=");
    PXOPT_COPY_S32(config->args, where, "-fault",      "fault", "==");
    PXOPT_COPY_STR(config->args, where, "-state",      "state", "==");
    PXOPT_COPY_TIME(config->args, where, "-timestamp_begin", "timestamp", ">=");
    PXOPT_COPY_TIME(config->args, where, "-timestamp_end", "timestamp", "<=");
    pxAddLabelSearchArgs(config, where, "-label",      "pstampRequest.label", "LIKE");
    if (!psListLength(where->list)) {
        psFree(where);
        psError(PXTOOLS_ERR_CONFIG, false, "search parameters are required");
        return false;
    }

    psString query = psStringCopy("UPDATE pstampRequest SET timestamp = UTC_TIMESTAMP()");

    psString stateCheck = NULL;
    if (state) {
        psStringAppend(&query, ", state = '%s'", state);
        if (!strcmp(state, "goto_cleaned")) {
            psStringAppend(&stateCheck, " AND state != 'cleaned'");
        }
    }
    if (label) {
        psStringAppend(&query, ", label = '%s'", label);
    }
    if (outProduct) {
        psStringAppend(&query, ", outProduct = '%s'", outProduct);
    }
    if (outdir) {
        psStringAppend(&query, ", outdir = '%s'", outdir);
    }
    if (clearfault) {
        if (fault) {
            psError(PXTOOLS_ERR_CONFIG, true, "only one of -fault and -clearfault is allowed");
            return false;
        }
        psStringAppend(&query, ", fault = 0");
    } else if (fault) {
        psStringAppend(&query, ", fault = %d", fault);
    }
    if (uri) {
        psStringAppend(&query, ", uri = '%s'", uri);
    }
    if (name) {
        psStringAppend(&query, ", name = '%s'", name);
    }
    if (reqType) {
        psStringAppend(&query, ", reqType = '%s'", reqType);
    }

    psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
    psStringAppend(&query, "\nWHERE %s", whereClause);
    psFree(whereClause);
    psFree(where);

    if (stateCheck) {
        psStringAppend(&query, "%s", stateCheck);
    }

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

    // psU64 affected = psDBAffectedRows(config->dbh);
    // psLogMsg("pstamptool", PS_LOG_INFO, "Updated %" PRIu64 " pstampRequests", affected);

    return true;
}

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

    psMetadata *where = psMetadataAlloc();

    PXOPT_COPY_S64(config->args, where, "-req_id", "req_id", "==");
    PXOPT_COPY_S64(config->args, where, "-fault", "fault", "==");
    PXOPT_COPY_STR(config->args, where, "-state", "pstampRequest.state", "==");
    pxAddLabelSearchArgs(config, where, "-label", "pstampRequest.label", "LIKE");

    if (!psListLength(where->list)) {
        psFree(where);
        psError(PXTOOLS_ERR_CONFIG, false, "search parameters are required");
        return false;
    }

    psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
    psFree(where);

    // delete any jobs that were queued by requests that didn't complete parsing (pstampRequest.state = 'new'
    // If state =  'run' was supplied this will be a no-op
    psString query = pxDataGet("pstamptool_revertreq_deletejobs.sql");
    psStringAppend(&query, " AND %s", whereClause);
    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    psFree(query);

    // clear fault for requests
    query = pxDataGet("pstamptool_revertreq.sql");
    psStringAppend(&query, " AND %s", whereClause);

    psFree(whereClause);

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

    return true;
}

static bool addjobMode(pxConfig *config)
{
    bool    stampJob = false;

    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_S64(req_id,      config->args, "-req_id",     true, false);
    PXOPT_LOOKUP_STR(rownum,      config->args, "-rownum",     true, false);
    PXOPT_LOOKUP_STR(job_type,    config->args, "-job_type",   false, false);
    PXOPT_LOOKUP_STR(outputBase,  config->args, "-outputBase", false,  false);
    PXOPT_LOOKUP_STR(stateString, config->args, "-state",      false, false);
    PXOPT_LOOKUP_S16(fault,       config->args, "-fault",      false, false);
    PXOPT_LOOKUP_S64(exp_id,      config->args, "-exp_id",     false, false);
    PXOPT_LOOKUP_S64(options,     config->args, "-options",     false, false);
    PXOPT_LOOKUP_S64(dep_id,      config->args, "-dep_id",     false, false);

    // unless the job is being inserted with stop state require outputBase
    if (strcmp(stateString, "stop") && !outputBase) {
        psError(PS_ERR_UNKNOWN, true, "-outputBase is required");
        return false;
    }

    // default value for job_type is defined in pstamptoolConfig.c
    if (!strcmp(job_type, "get_image") || !strcmp(job_type, "detect_query") || !strcmp(job_type, "none")) {
        stampJob = false;
    } else if (!strcmp(job_type, "stamp")) {
        stampJob = true;
    } else {
        psError(PS_ERR_UNKNOWN, false, "unknown value for -job_type: %s", job_type);
        return false;
    }
    if (!pstampJobInsert(config->dbh,
            0, // job_id
            req_id,
            rownum,
            stateString,
            job_type,
            fault,
            exp_id,
            outputBase,
            options,
            dep_id
            )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    psU64 affected = psDBAffectedRows(config->dbh);
    if (affected != 1) {
        psError(PS_ERR_UNKNOWN, false,
            "should have affected one row but %" PRIu64 " rows were modified",
            affected);
        return false;
    }

    psS64 job_id = psDBLastInsertID(config->dbh);
    printf("%" PRId64 "\n", job_id);

    return true;
}

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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where, "-job_id", "job_id", "==");
    PXOPT_COPY_S64(config->args, where, "-req_id", "req_id", "==");
    PXOPT_COPY_S64(config->args, where, "-fault",  "fault", "==");

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

    if (!psListLength(where->list)) {
        fprintf(stderr, "search arguments are required\n");
        exit (1);
    }

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

    // use psDBGenerateWhereSQL because the SQL yields an intermediate table
    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "pstampJob");
        psStringAppend(&query, " WHERE %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    // 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("pstamptool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

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

    psFree(output);

    return true;
}

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

    psMetadata *where = psMetadataAlloc();

    PXOPT_COPY_S64(config->args, where, "-job_id", "job_id", "==");
    PXOPT_COPY_S64(config->args, where, "-req_id", "req_id", "==");
    pxAddLabelSearchArgs(config, where, "-label", "pstampRequest.label", "LIKE");

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

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

    // use psDBGenerateWhereSQL because the SQL yields an intermediate table
    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    psStringAppend(&query, " ORDER BY priority DESC, req_id");

    // 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("pstamptool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

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

    psFree(output);

    return true;
}

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

    PXOPT_LOOKUP_S64(job_id,    config->args, "-job_id", false, false);
    PXOPT_LOOKUP_S64(req_id,    config->args, "-req_id", false, false);
    PXOPT_LOOKUP_S64(dep_id,    config->args, "-dep_id", false, false);

    if (!job_id && !req_id && !dep_id) {
        psError(PS_ERR_UNKNOWN, true, "at least one of -job_id -req_id or -dep_id is required");
        return false;
    }

    PXOPT_LOOKUP_STR(state,     config->args, "-set_state",  false, false);
    PXOPT_LOOKUP_S32(fault,     config->args, "-set_fault",  false, false);
    
    if (!state && !fault) {
        psError(PS_ERR_UNKNOWN, true, "at least one of -set_state and -set_fault is required");
        return false;
    }

    psMetadata *where = psMetadataAlloc();

    PXOPT_COPY_S64(config->args, where, "-job_id", "job_id", "==");
    PXOPT_COPY_S64(config->args, where, "-req_id", "req_id", "==");
    PXOPT_COPY_S64(config->args, where, "-dep_id", "dep_id", "==");
    PXOPT_COPY_STR(config->args, where, "-state",  "pstampJob.state", "==");

    psString query = pxDataGet("pstamptool_updatejob.sql");

    char c = ' ';
    if (state) {
        psStringAppend(&query, "\n %c pstampJob.state = '%s'", c, state);
        c = ',';
    }
    if (fault) {
        psStringAppend(&query, "\n %c pstampJob.fault = %d", c, fault);
        c = ',';
    }
    
    psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
    psStringAppend(&query, " WHERE %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);

    psU64 affected = psDBAffectedRows(config->dbh);
    psLogMsg("pstamptool", PS_LOG_INFO, "Updated %" PRIu64 " pstampJobs", affected);

    return true;
}

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

    psMetadata *where = psMetadataAlloc();

    PXOPT_COPY_S64(config->args, where, "-job_id", "job_id", "==");
    PXOPT_COPY_S64(config->args, where, "-req_id", "req_id", "==");
    PXOPT_COPY_S64(config->args, where, "-fault",  "pstampJob.fault", "==");
    PXOPT_COPY_S64(config->args, where, "-req_id_min",  "req_id", ">=");
    pxAddLabelSearchArgs(config, where, "-label", "pstampRequest.label", "LIKE");

    PXOPT_LOOKUP_BOOL(all, config->args, "-all", false);
    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_S16(fault, config->args, "-fault",  false, false);

    // By default only revert faults < PSTAMP_FIRST_ERROR_CODE which are our "ipp exit codes"
    // codes larger than that are the pstamp request interface.
    // Don't fault those unless -fault waa provided
    psString faultClause = psStringCopy("");
    if (!fault) {
        psStringAppend(&faultClause, " \nAND (pstampJob.fault < %d)", PSTAMP_FIRST_ERROR_CODE);
    }

    psString query = pxDataGet("pstamptool_revertjob.sql");
    if (!psListLength(where->list) && !all) {
        psFree(where);
        psError(PXTOOLS_ERR_CONFIG, false, "search parameters or -all are required");
        return false;
    }
    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

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

    psFree(faultClause);

    return true;
}
static bool addprojectMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_STR(name,  config->args, "-name", true, false);
    PXOPT_LOOKUP_STR(state,  config->args, "-state", false, false);
    PXOPT_LOOKUP_STR(imagedb, config->args, "-imagedb", true, false);
    PXOPT_LOOKUP_STR(dvodb,  config->args, "-dvodb", false, false);
    PXOPT_LOOKUP_STR(camera, config->args, "-inst", true, false);
    PXOPT_LOOKUP_STR(telescope, config->args, "-telescope", true, false);
    PXOPT_LOOKUP_BOOL(need_magic, config->args, "-need_magic", false);

    if (!pstampProjectInsert(config->dbh,
            0,
            name,
            state,
            imagedb,
            dvodb,
            camera,
            telescope,
            need_magic
        )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}

static bool projectMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);
    psMetadata *where = psMetadataAlloc();

    PXOPT_COPY_STR(config->args, where, "-name", "name", "==");

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

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

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

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

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

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

    psFree(output);

    return true;
}
static bool modprojectMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_S64(proj_id,    config->args, "-proj_id", true, false);
    PXOPT_LOOKUP_STR(state,  config->args, "-state", true, false);

    char *query = psStringCopy ("UPDATE pstampProject SET");

    psStringAppend(&query, " state = '%s'", state);

    psStringAppend(&query, " WHERE proj_id = %" PRId64, proj_id);

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

    psU64 affected = psDBAffectedRows(config->dbh);
    if (affected != 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected one row but %"
                                        PRIu64 " rows were modified", affected);
        return false;
    }

    return true;
}

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

    PXOPT_LOOKUP_STR(stage,       config->args, "-stage",   true, false);
    PXOPT_LOOKUP_S64(stage_id,    config->args, "-stage_id", true, false);
    PXOPT_LOOKUP_STR(component,   config->args, "-component",  true, false);
    PXOPT_LOOKUP_STR(imagedb,     config->args, "-imagedb",  true, false);
    PXOPT_LOOKUP_STR(rlabel,      config->args, "-rlabel",  true, false);
    PXOPT_LOOKUP_STR(outdir,      config->args, "-outdir",  true, false);
    PXOPT_LOOKUP_BOOL(need_magic, config->args, "-need_magic", false);
    PXOPT_LOOKUP_BOOL(no_create,  config->args, "-no_create", false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-stage", "stage", "==");
    PXOPT_COPY_STR(config->args, where, "-imagedb", "imagedb", "==");
    PXOPT_COPY_S64(config->args, where, "-stage_id", "stage_id", "==");
    PXOPT_COPY_STR(config->args, where, "-component", "component", "==");

    // start a transaction eraly so it will contain any row level locks
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    psString query = pxDataGet("pstamptool_getdependent.sql");
    if (!query) {
        psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement");
        return false;
    }
    psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
    if (!no_create) {
        // This will lock the row until the transaction is committed
        psStringAppend(&query, " AND %s FOR UPDATE", whereClause);
    } else {
        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);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (psArrayLength(output)) {
        psMetadata *dep = output->data[0];
        psS64 dep_id = psMetadataLookupS64(NULL, dep, "dep_id");
        if (!dep_id) {
            psError(PS_ERR_UNKNOWN, false, "database error");
            psFree(output);
            return false;
        }
        psS32 fault = psMetadataLookupS64(NULL, dep, "fault");
        if (fault > 0) {
            fprintf(stderr, "existing dependent has fault %d\n", fault);
            exit (fault);
        }
        printf("%" PRId64 "\n", dep_id);
        psFree(output);
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        return true;
    }
    if (no_create) {
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
            return false;
        }
        return true;
    }
    // no existing dependent that matches, insert one
    // Since we have multiple processes running jobs we have a
    // race condition here so that's why we need to lock the table

    if (!pstampDependentInsert(
        config->dbh,
        0,              // dep_id
        "new",          // state
        stage,
        stage_id,
        component,
        imagedb,
        rlabel,
        need_magic,
        outdir,
        0               // fault
        )) {
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "failed to insert pstampDependent");
        return false;
    }

    // if we try and get this after commit zero is returned
    psS64 dep_id = psDBLastInsertID(config->dbh);
    if (!dep_id) {
        psError(PS_ERR_UNKNOWN, false, "psDBLastInsertID returned NULL");
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        return false;
    }

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

    printf("%" PRId64 "\n", dep_id);

    return true;
}

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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-stage", "stage", "==");
    PXOPT_COPY_STR(config->args, where, "-imagedb", "imagedb", "==");
    PXOPT_COPY_S64(config->args, where, "-stage_id", "stage_id", "==");
    PXOPT_COPY_S64(config->args, where, "-dep_id", "dep_id", "==");
    PXOPT_COPY_STR(config->args, where, "-rlabel", "rlabel", "==");
    pxAddLabelSearchArgs(config, where, "-label", "pstampRequest.label", "==");

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

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

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

    psStringAppend(&query, " ORDER BY priority DESC, req_id");

    // 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("pstamptool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

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

    psFree(output);

    return true;
}
static bool updatedependentMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_S64(dep_id,    config->args, "-dep_id", true, false);
    PXOPT_LOOKUP_STR(state,     config->args, "-set_state",  false, false);
    PXOPT_LOOKUP_S16(fault,     config->args, "-set_fault",  false, false);

    if (!fault && !state) {
        psError(PS_ERR_UNKNOWN, true, "at least one of -set_state or fault is required");
        return false;
    }
    psString query = psStringCopy("UPDATE pstampDependent SET");
    bool needComma = false;
    if (state) {
        psStringAppend(&query, " state = '%s'", state);
        needComma = true;
    }
    if (fault) {
        psStringAppend(&query, "%s fault = %d", needComma ? ", " : "", fault);
        needComma = true;
    }
    psStringAppend(&query, " WHERE dep_id = %" PRId64, dep_id);

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

    psU64 affected = psDBAffectedRows(config->dbh);
    if (affected != 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected one row but %"
                                        PRIu64 " rows were modified", affected);
        return false;
    }

    return true;
}
static bool revertdependentMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where, "-fault", "pstampDependent.fault", "==");
    PXOPT_COPY_S64(config->args, where, "-dep_id", "dep_id", "==");
    pxAddLabelSearchArgs(config, where, "-label", "pstampRequest.label", "==");

    if (!psListLength(where->list)) {
        psFree(where);
        psError(PXTOOLS_ERR_CONFIG, false, "search parameters are required");
        return false;
    }

    PXOPT_LOOKUP_S16(fault,      config->args, "-fault",          false, false);

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

    psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
    psStringAppend(&query, " AND %s", whereClause);
    psFree(whereClause);
    psFree(where);

    if (!fault) {
        // unless asked to clear a specific fault value, restrict reverts to fault codes
        // that are less than the minimum fault in the API
        psStringAppend(&query, " AND (pstampDependent.fault < %d)", PSTAMP_FIRST_ERROR_CODE);
    }

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

    return true;
}

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

    if (!pstampWebRequestInsert(config->dbh, 0 )) {
        psError(PS_ERR_UNKNOWN, false, "failed to insert pstampWebRequest");
        return false;
    }

    psS64 req_id = psDBLastInsertID(config->dbh);

    printf("%" PRId64 "\n", req_id);

    return true;
}
