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

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

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <math.h>
#include <ippdb.h>

#include "pxtools.h"
#include "difftool.h"

static bool definerunMode(pxConfig *config);
static bool updaterunMode(pxConfig *config);
static bool addinputskyfileMode(pxConfig *config);
static bool inputskyfileMode(pxConfig *config);
static bool todiffskyfileMode(pxConfig *config);
static bool adddiffskyfileMode(pxConfig *config);
static bool diffskyfileMode(pxConfig *config);
static bool revertdiffskyfileMode(pxConfig *config);
static bool definepoprunMode(pxConfig *config);
static bool definebyqueryMode(pxConfig *config);
static bool pendingcleanuprunMode(pxConfig *config);
static bool pendingcleanupskyfileMode(pxConfig *config);
static bool donecleanupMode(pxConfig *config);
static bool updatediffskyfileMode(pxConfig *config);

static bool setdiffRunState(pxConfig *config, psS64 diff_id, const char *state);

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

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

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

    switch (config->mode) {
        MODECASE(DIFFTOOL_MODE_DEFINERUN,             definerunMode);
        MODECASE(DIFFTOOL_MODE_UPDATERUN,             updaterunMode);
        MODECASE(DIFFTOOL_MODE_ADDINPUTSKYFILE,       addinputskyfileMode);
        MODECASE(DIFFTOOL_MODE_INPUTSKYFILE,          inputskyfileMode);
        MODECASE(DIFFTOOL_MODE_TODIFFSKYFILE,         todiffskyfileMode);
        MODECASE(DIFFTOOL_MODE_ADDDIFFSKYFILE,        adddiffskyfileMode);
        MODECASE(DIFFTOOL_MODE_DIFFSKYFILE,           diffskyfileMode);
        MODECASE(DIFFTOOL_MODE_REVERTDIFFSKYFILE,     revertdiffskyfileMode);
        MODECASE(DIFFTOOL_MODE_DEFINEPOPRUN,          definepoprunMode);
        MODECASE(DIFFTOOL_MODE_DEFINEBYQUERY,         definebyqueryMode);
        MODECASE(DIFFTOOL_MODE_PENDINGCLEANUPRUN,     pendingcleanuprunMode);
        MODECASE(DIFFTOOL_MODE_PENDINGCLEANUPSKYFILE, pendingcleanupskyfileMode);
        MODECASE(DIFFTOOL_MODE_DONECLEANUP,           donecleanupMode);
        MODECASE(DIFFTOOL_MODE_UPDATEDIFFSKYFILE,     updatediffskyfileMode);
        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 definerunMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // required options
    PXOPT_LOOKUP_STR(workdir, config->args, "-workdir", true, false);
    PXOPT_LOOKUP_STR(skycell_id, config->args, "-skycell_id", true, false);
    PXOPT_LOOKUP_STR(tess_id, config->args, "-tess_id", true, false);
    PXOPT_LOOKUP_STR(label, config->args, "-label", false, false);
    PXOPT_LOOKUP_STR(reduction, config->args, "-reduction", false, false);

    // default
    PXOPT_LOOKUP_TIME(registered, config->args, "-registered", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    diffRunRow *run = diffRunRowAlloc(
            0,          // ID
            "reg",      // state
            label,
            reduction,
            workdir,
            NULL,       // dvodb
            registered,
            skycell_id,
            tess_id
    );
    if (!run) {
        psError(PS_ERR_UNKNOWN, false, "failed to alloc diffRun object");
        return true;
    }
    if (!diffRunInsertObject(config->dbh, run)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(run);
        return true;
    }

    // get the assigned diff_id
    run->diff_id = psDBLastInsertID(config->dbh);

    if (!diffRunPrintObject(stdout, run, !simple)) {
            psError(PS_ERR_UNKNOWN, false, "failed to print object");
            psFree(run);
            return false;
    }

    psFree(run);

    return true;
}


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

    // required options
    PXOPT_LOOKUP_S64(diff_id, config->args, "-diff_id", true, false);
    PXOPT_LOOKUP_STR(state, config->args, "-state", true, false);

    if (state) {
        // set detRun.state to state
        return setdiffRunState(config, diff_id, state);
    }

    return true;
}


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

    // required options
    PXOPT_LOOKUP_S64(diff_id, config->args, "-diff_id", true, false);

    // optional
    PXOPT_LOOKUP_S64(stack_id, config->args, "-stack_id", false, false);
    PXOPT_LOOKUP_S64(warp_id, config->args, "-warp_id", false, false);
    PXOPT_LOOKUP_STR(kind, config->args, "-kind", false, false);

    // defaults to false
    PXOPT_LOOKUP_BOOL(template, config->args, "-template", false);

    // must provide either stack_id or warp_id but not BOTH
    if (!(stack_id || warp_id)) {
        psError(PS_ERR_UNKNOWN, true, "either -stack_id or -warp_id must be specified");
        return false;
    }
    if (stack_id && warp_id) {
        psError(PS_ERR_UNKNOWN, true, "either -stack_id or -warp_id must be specified");
        return false;
    }

    // if a warp_id was provided we need to lookup the skycell_id and tess_id
    // from the warpRun
    psString skycell_id = NULL;
    psString tess_id = NULL;
    if (warp_id) {
        if (!p_psDBRunQuery(config->dbh, "SELECT * from diffRun WHERE diff_id = %" PRId64, diff_id)) {
            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)) {
            psError(PS_ERR_UNKNOWN, false, "diff_id %" PRId64 " not found", diff_id);
            psFree(output);
            return false;
        }

        diffRunRow *run = diffRunObjectFromMetadata(output->data[0]);
        skycell_id = run->skycell_id;
        tess_id = run->tess_id;
    }

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

    if (!diffInputSkyfileInsert(config->dbh,
            diff_id,
            template,
            stack_id ? stack_id : PS_MAX_S64, // defined or NULL
            warp_id ? warp_id : PS_MAX_S64, // defined or NULL
            skycell_id,
            tess_id,
            kind
        )) {
        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, "SELECT count(diff_id) FROM diffRun JOIN diffInputSkyfile USING(diff_id) WHERE diff_id = %" PRId64, diff_id)) {
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

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

    bool status;
    psS32 count = psMetadataLookupS32(&status, output->data[0], "count(diff_id)");

    if (count == 2) {
        if (!setdiffRunState(config, diff_id, "new")) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "database error");
            return false;
        }
    }

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

    return true;
}


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

    psMetadata *where = psMetadataAlloc();

    PXOPT_COPY_S64(config->args, where,  "-diff_id", "diff_id", "==");
    PXOPT_COPY_S64(config->args, where, "-warp_id", "warp_id", "==");
    PXOPT_COPY_STR(config->args, where, "-skycell_id", "skycell_id", "==");
    PXOPT_COPY_STR(config->args, where,  "-tess_id", "tess_id", "==");
    PXOPT_COPY_STR(config->args, where,  "-kind", "kind", "==");

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

    if (template && input) {
        // User apparently wants both, which is the default behaviour
        template = false;
        input = false;
    }

    // find all rawImfiles matching the default query
    psString query = pxDataGet("difftool_inputskyfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    psString whereClause = NULL;
    if (psListLength(where->list)) {
        whereClause = psDBGenerateWhereConditionSQL(where, NULL);
        psStringPrepend(&whereClause, "\n AND ");
    }
    psFree(where);

    // Add condition to get only templates or only inputs
    {
        psString templateClause = NULL;
        if (template) {
            psStringAppend(&templateClause, " %s", " template != 0");
        } else if (input) {
            psStringAppend(&templateClause, " %s", " template = 0");
        }
        if (templateClause) {
            psStringAppend(&whereClause, "\n AND %s", templateClause);
        }
        psFree(templateClause);
    }


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

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

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

    psFree(output);

    return true;
}


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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where,  "-diff_id", "diffRun.diff_id", "==");
    PXOPT_COPY_STR(config->args, where,  "-label", "diffRun.label", "==");

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

    psString query = pxDataGet("difftool_todiffskyfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, 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);

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

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

    psFree(output);

    return true;
}


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

    PXOPT_LOOKUP_S64(diff_id, config->args, "-diff_id", true, false); // required
    PXOPT_LOOKUP_S16(code, config->args, "-code", false, false);
    PXOPT_LOOKUP_STR(uri, config->args, "-uri", (code == 0), false);
    PXOPT_LOOKUP_STR(path_base, config->args, "-path_base", (code == 0), false);

    // optional
    PXOPT_LOOKUP_F64(bg, config->args, "-bg", false, false);
    PXOPT_LOOKUP_F64(bg_stdev, config->args, "-bg_stdev", false, false);
    PXOPT_LOOKUP_F32(dtime_diff, config->args, "-dtime_diff", false, false);
    PXOPT_LOOKUP_F32(dtime_match, config->args, "-dtime_match", false, false);
    PXOPT_LOOKUP_F32(dtime_phot, config->args, "-dtime_phot", false, false);
    PXOPT_LOOKUP_F32(dtime_script, config->args, "-dtime_script", false, false);
    PXOPT_LOOKUP_S32(stamps_num, config->args, "-stamps_num", false, false);
    PXOPT_LOOKUP_F32(stamps_mean, config->args, "-stamps_mean", false, false);
    PXOPT_LOOKUP_F32(stamps_rms, config->args, "-stamps_rms", false, false);
    PXOPT_LOOKUP_F32(norm, config->args, "-norm", false, false);
    PXOPT_LOOKUP_F32(bg_diff, config->args, "-bg_diff", false, false);
    PXOPT_LOOKUP_F32(kernel_x, config->args, "-kernel_x", false, false);
    PXOPT_LOOKUP_F32(kernel_y, config->args, "-kernel_y", false, false);
    PXOPT_LOOKUP_F32(kernel_xx, config->args, "-kernel_xx", false, false);
    PXOPT_LOOKUP_F32(kernel_xy, config->args, "-kernel_xy", false, false);
    PXOPT_LOOKUP_F32(kernel_yy, config->args, "-kernel_yy", false, false);
    PXOPT_LOOKUP_S32(sources, config->args, "-sources", false, false);
    PXOPT_LOOKUP_STR(hostname, config->args, "-hostname", false, false);
    PXOPT_LOOKUP_F32(good_frac, config->args, "-good_frac", false, false);

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

    if (!diffSkyfileInsert(config->dbh,
                           diff_id,
                           uri,
                           path_base,
                           bg,
                           bg_stdev,
                           stamps_num,
                           stamps_mean,
                           stamps_rms,
                           norm,
                           bg_diff,
                           kernel_x,
                           kernel_y,
                           kernel_xx,
                           kernel_xy,
                           kernel_yy,
                           sources,
                           dtime_diff,
                           dtime_match,
                           dtime_phot,
                           dtime_script,
                           hostname,
                           good_frac,
                           code
          )) {
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    if (!setdiffRunState(config, diff_id, "full")) {
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

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

    return true;
}


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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where,  "-diff_id", "diffSkyfile.diff_id", "==");
    PXOPT_COPY_STR(config->args, where, "-skycell_id", "diffSkyfile.skycell_id", "==");
    PXOPT_COPY_STR(config->args, where, "-tess_id", "diffSkyfile.tess_id", "==");
    PXOPT_COPY_S16(config->args, where, "-code", "diffSkyfile.fault", "==");
    PXOPT_COPY_S64(config->args, where, "-exp_id", "rawExp.exp_id", "==");
    PXOPT_COPY_STR(config->args, where, "-exp_name", "rawExp.exp_name", "==");

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

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

    if (psListLength(where->list)) {
        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) {
        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("difftool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

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

    psFree(output);

    return true;
}


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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where,  "-diff_id", "diffSkyfile.diff_id", "==");
    PXOPT_COPY_STR(config->args, where,  "-label", "diffRun.label", "==");
    PXOPT_COPY_S16(config->args, where, "-code",     "fault", "==");

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

    int num;                            // Number affected

    // Update state to 'new'
    {
        psString query = pxDataGet("difftool_revertdiffskyfile_update.sql");
        if (!query) {
            psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            return false;
        }

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

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

        num = psDBAffectedRows(config->dbh);

        if (num < 1) {
            psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            return false;
        }
    }

    // Delete product
    {
        psString query = pxDataGet("difftool_revertdiffskyfile_delete.sql");
        if (!query) {
            psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            return false;
        }

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

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

        if (psDBAffectedRows(config->dbh) != num) {
            psError(PS_ERR_UNKNOWN, false, "Updated and deleted different number of entries!");
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            return false;
        }
    }

    psFree(where);

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

    return true;
}


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

    // check that state is a valid string value
    if (!pxIsValidState(state)) {
        psError(PS_ERR_UNKNOWN, false, "invalid diffRun state: %s", state);
        return false;
    }

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

    return true;
}

// Generate a single populated run
static bool populatedrun(psArray *list, // List of runs, to print
                         const char *workdir, // Working directory
                         const char *skycell_id, // Skycell identifier
                         const char *tess_id, // Tessellation identifier
                         const char *label, // label
                         const char *reduction, // reduction
                         psS64 input_warp_id, // Warp identifier for input image, PS_MAX_S64 for none
                         psS64 input_stack_id, // Stack identifier for input image, PS_MAX_S64 for none
                         psS64 template_warp_id, // Warp identifier for template image, PS_MAX_S64 for none
                         psS64 template_stack_id, // Stack identifier for template image, PS_MAX_S64 for none
                         pxConfig *config // Configuration
                         )
{
    PS_ASSERT_STRING_NON_EMPTY(workdir, false);
    PS_ASSERT_STRING_NON_EMPTY(skycell_id, false);
    PS_ASSERT_STRING_NON_EMPTY(tess_id, false);
    if ((input_warp_id == PS_MAX_S64 && input_stack_id == PS_MAX_S64) ||
        (input_warp_id != PS_MAX_S64 && input_stack_id != PS_MAX_S64)) {
        psError(PS_ERR_BAD_PARAMETER_VALUE, true, "One, and only one, input must be defined.");
        return false;
    }
    if ((template_warp_id == PS_MAX_S64 && template_stack_id == PS_MAX_S64) ||
        (template_warp_id != PS_MAX_S64 && template_stack_id != PS_MAX_S64)) {
        psError(PS_ERR_BAD_PARAMETER_VALUE, true, "One, and only one, template must be defined.");
        return false;
    }

    // default
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
    PXOPT_LOOKUP_TIME(registered, config->args, "-registered", false, false);

    diffRunRow *run = diffRunRowAlloc(
            0,          // ID
            "reg",      // state
            label,
            reduction,
            workdir,
            NULL,       // dvodb
            registered,
            skycell_id,
            tess_id
    );

    if (!run) {
        psError(PS_ERR_UNKNOWN, false, "failed to alloc diffRun object");
        return true;
    }

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

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

    // get the assigned diff_id
    run->diff_id = psDBLastInsertID(config->dbh);

    // Template
    if (!diffInputSkyfileInsert(config->dbh,
            run->diff_id,
            true,
            template_stack_id,
            template_warp_id,
            skycell_id,
            tess_id,
            NULL    // kind
        )) {
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    // Input
    if (!diffInputSkyfileInsert(config->dbh,
            run->diff_id,
            false,
            input_stack_id,
            input_warp_id,
            skycell_id,
            tess_id,
            NULL    // kind
        )) {
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    char *query = "UPDATE diffRun SET state = 'new' WHERE diff_id = '%" PRId64 "'";
    if (!p_psDBRunQuery(config->dbh, query, run->diff_id)) {
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false,
                "failed to set state to run for diff_id %" PRId64, run->diff_id);
        return false;
    }

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

    if (list) {
        psArrayAdd(list, list->n, run);
    }

    psFree(run);

    return true;
}


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

    PXOPT_LOOKUP_STR(workdir, config->args, "-workdir", true, false); // required options
    PXOPT_LOOKUP_STR(skycell_id, config->args, "-skycell_id", true, false); // required options
    PXOPT_LOOKUP_STR(tess_id, config->args, "-tess_id", true, false); // required options
    PXOPT_LOOKUP_STR(label, config->args, "-label", false, false);
    PXOPT_LOOKUP_STR(reduction, config->args, "-reduction", false, false);
    PXOPT_LOOKUP_S64(template_warp_id, config->args, "-template_warp_id", false, false);
    PXOPT_LOOKUP_S64(template_stack_id, config->args, "-template_stack_id", false, false);
    PXOPT_LOOKUP_S64(input_warp_id, config->args, "-input_warp_id", false, false);
    PXOPT_LOOKUP_S64(input_stack_id, config->args, "-input_stack_id", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    if (template_stack_id && template_warp_id) {
        psError(PS_ERR_BAD_PARAMETER_VALUE, true, "Only one template can be defined.");
        return false;
    }
    if (!template_stack_id && !template_warp_id) {
        psError(PS_ERR_BAD_PARAMETER_VALUE, true,
                "No template has been defined (-template_stack_id or -template_warp_id)");
        return false;
    }

    if (input_stack_id && input_warp_id) {
        psError(PS_ERR_BAD_PARAMETER_VALUE, true, "Only one input can be defined.");
        return false;
    }
    if (!input_stack_id && !input_warp_id) {
        psError(PS_ERR_BAD_PARAMETER_VALUE, true,
                "No input has been defined (-input_stack_id or -input_warp_id)");
        return false;
    }

    psArray *list = psArrayAllocEmpty(16); // List of runs, to print

    if (!populatedrun(list, workdir, skycell_id, tess_id, label, reduction,
                      input_warp_id ? input_warp_id : PS_MAX_S64,
                      input_stack_id ? input_stack_id : PS_MAX_S64,
                      template_warp_id ? template_warp_id : PS_MAX_S64,
                      template_stack_id ? template_stack_id : PS_MAX_S64,
                      config)) {
        psError(PS_ERR_UNKNOWN, false, "failed to create populated diffRun");
        psFree(list);
        return false;
    }

    if (!diffRunPrintObjects(stdout, list, !simple)) {
            psError(PS_ERR_UNKNOWN, false, "failed to print object");
            psFree(list);
            return false;
    }
    psFree(list);

    return true;
}


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

    psMetadata *where = psMetadataAlloc();

    PXOPT_COPY_S64(config->args, where, "-warp_id", "warpsToDiff.warp_id", "==");
    PXOPT_COPY_STR(config->args, where, "-skycell_id", "warpsToDiff.skycell_id", "==");
    PXOPT_COPY_STR(config->args, where, "-tess_id", "warpsToDiff.tess_id", "==");
    PXOPT_COPY_STR(config->args, where, "-filter", "warpsToDiff.filter", "==");
    PXOPT_COPY_STR(config->args, where, "-stack_label", "stacksForDiff.stack_label", "==");
    PXOPT_COPY_STR(config->args, where, "-warp_label", "warpsToDiff.warp_label", "==");
    PXOPT_COPY_STR(config->args, where,  "-kind", "warpsToDiff.kind", "==");
    PXOPT_COPY_F32(config->args, where,  "-good_frac", "warpsToDiff.good_frac", ">=");

    PXOPT_LOOKUP_STR(workdir, config->args, "-workdir", true, false); // required options
    PXOPT_LOOKUP_STR(reduction, config->args, "-reduction", false, false); // option
    PXOPT_LOOKUP_STR(label, config->args, "-label", false, false); // option
    PXOPT_LOOKUP_TIME(registered, config->args, "-registered", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
    PXOPT_LOOKUP_BOOL(newTemplates, config->args, "-new-templates", false);

    // find all things to queue
    psString query = pxDataGet("difftool_definebyquery.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (newTemplates) {
        // Warps that haven't been diffed OR warps that can take a newer template in the diff
        psStringAppend(&query, " WHERE (diff_id IS NULL OR best_stack_id > current_stack_id)");
    } else {
        // Only warps that haven't been diffed
        psStringAppend(&query, " WHERE diff_id IS NULL");
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
        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) {
        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("difftool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    psArray *list = psArrayAllocEmpty(16); // List of runs, to print
    long numGood = 0;                   // Number of good rows added
    for (long i = 0; i < output->n; i++) {
        psMetadata *row = output->data[i]; // Output row from query

        // Selected parameters
        bool mdok;                      // Status of MD lookup
        const char *skycell_id = psMetadataLookupStr(&mdok, row, "skycell_id");
        if (!mdok || !skycell_id) {
            psWarning("skycell_id not found --- ignoring row %ld", i);
            continue;
        }
        const char *tess_id = psMetadataLookupStr(&mdok, row, "tess_id");
        if (!mdok || !tess_id) {
            psWarning("tess_id not found --- ignoring row %ld", i);
            continue;
        }
        psS64 stack_id = psMetadataLookupS64(&mdok, row, "best_stack_id");
        if (!mdok) {
            psWarning("stack_id not found --- ignoring row %ld", i);
            continue;
        }
        psS64 warp_id = psMetadataLookupS64(&mdok, row, "warp_id");
        if (!mdok) {
            psWarning("warp_id not found --- ignoring row %ld", i);
            continue;
        }

        if (!populatedrun(list, workdir, skycell_id, tess_id, label, reduction, warp_id, PS_MAX_S64, PS_MAX_S64,
                          stack_id, config)) {
            psWarning("Unable to add run for %s,%s,%" PRId64 ",%" PRId64, skycell_id, tess_id,
                      warp_id, stack_id);
            psErrorClear();
            continue;
        }

        numGood++;
    }
    psFree(output);

    if (!diffRunPrintObjects(stdout, list, !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print object");
        psFree(list);
        return false;
    }
    psFree(list);

    return true;
}

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

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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-label", "label", "==");

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

    if (where && psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
        psStringAppend(&query, " AND %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("difftool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

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

    psFree(output);

    return true;
}


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

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

    psMetadata *where = psMetadataAlloc();
    if (diff_id) {
        PXOPT_COPY_S64(config->args, where, "-diff_id", "diff_id", "==");
    }
    PXOPT_COPY_STR(config->args, where, "-label", "label", "==");

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

    if (where && psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
        psStringAppend(&query, " AND %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("difftool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

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

    psFree(output);
    return true;
}


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

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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-label", "label", "==");

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

    if (where && psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
        psStringAppend(&query, " AND %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("difftool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

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

    psFree(output);
    return true;
}

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

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

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

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

    return true;
}

