/*
 * magicdstool.c
 *
 * Copyright (C) 2006-2009  IfA
 *
 * 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 <ippStages.h>

#include "pxtools.h"
#include "magicdstool.h"

static bool definebyqueryMode(pxConfig *config);
static psS64 definerunMode(pxConfig *config);
static bool updaterunMode(pxConfig *config);
static bool todestreakMode(pxConfig *config);
static bool adddestreakedfileMode(pxConfig *config);
static bool revertdestreakedfileMode(pxConfig *config);
static bool getskycellsMode(pxConfig *config);
static bool toremoveMode(pxConfig *config);
static bool torestoreMode(pxConfig *config);
static bool torevertMode(pxConfig *config);

static bool setmagicDSRunState(pxConfig *config, psS64 magic_id, const char *state);
static bool magicDSRunComplete(pxConfig *config, bool setmagicked);
static bool magicDSGetIDs(pxConfig *config, psString stage, psS64 magic_id, psS64 *stage_id, psS64 *cam_id);

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

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

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

    switch (config->mode) {
        MODECASE(MAGICDSTOOL_MODE_DEFINEBYQUERY,       definebyqueryMode);
        MODECASE(MAGICDSTOOL_MODE_DEFINERUN,           definerunMode);
        MODECASE(MAGICDSTOOL_MODE_UPDATERUN,           updaterunMode);
        MODECASE(MAGICDSTOOL_MODE_TODESTREAK,          todestreakMode);
        MODECASE(MAGICDSTOOL_MODE_ADDDESTREAKEDFILE,   adddestreakedfileMode);
        MODECASE(MAGICDSTOOL_MODE_REVERTDESTREAKEDFILE,revertdestreakedfileMode);
        MODECASE(MAGICDSTOOL_MODE_GETSKYCELLS,         getskycellsMode);
        MODECASE(MAGICDSTOOL_MODE_TOREMOVE,            toremoveMode);
        MODECASE(MAGICDSTOOL_MODE_TORESTORE,           torestoreMode);
        MODECASE(MAGICDSTOOL_MODE_TOREVERT,            torevertMode);
        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 definebyqueryMode(pxConfig *config)
{

    // Required
    PXOPT_LOOKUP_STR(stage, config->args, "-stage", true, false);

    // Optional
    PXOPT_LOOKUP_STR(workdir, config->args, "-workdir", false, false);
    PXOPT_LOOKUP_STR(recoveryroot, config->args, "-recoveryroot", false, false);
    PXOPT_LOOKUP_BOOL(re_place, config->args, "-replace", false);
    PXOPT_LOOKUP_STR(set_label, config->args, "-set_label", false, false);
    PXOPT_LOOKUP_BOOL(rerun, config->args, "-rerun", false);
    PXOPT_LOOKUP_BOOL(dry_run, config->args, "-dry_run", false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);

    // search args
    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where, "-exp_id",  "exp_id", "==");
    PXOPT_COPY_S64(config->args, where, "-chip_id", "chip_id", "==");
    PXOPT_COPY_S64(config->args, where, "-cam_id",  "cam_id", "==");
    PXOPT_COPY_S64(config->args, where, "-warp_id", "warp_id", "==");
    PXOPT_COPY_S64(config->args, where, "-diff_id", "diff_id", "==");
    PXOPT_COPY_S64(config->args, where, "-magic_id","magic_id", "==");
    PXOPT_COPY_S32(config->args, where, "-streaks_max","streaks", "<=");

    pxAddLabelSearchArgs (config, where, "-label", "magicRun.label", "==");

    ippStage stageNum = ippStringToStage(stage);
    
    psString query = NULL;
    switch (stageNum) {
    case IPP_STAGE_RAW:
        query = pxDataGet("magicdstool_definebyquery_raw.sql");
        break;
    case IPP_STAGE_CHIP:
        query = pxDataGet("magicdstool_definebyquery_chip.sql");
        break;
    case IPP_STAGE_CAMERA:
        query = pxDataGet("magicdstool_definebyquery_camera.sql");
        break;
    case IPP_STAGE_WARP:
        query = pxDataGet("magicdstool_definebyquery_warp.sql");
        break;
    case IPP_STAGE_DIFF:
        query = pxDataGet("magicdstool_definebyquery_diff.sql");
        break;
    case IPP_STAGE_FAKE:
    case IPP_STAGE_STACK:
        psError(PXTOOLS_ERR_DATA, true, "%sRuns do not need to be destreaked", stage);
        return false;
    case IPP_STAGE_NONE:
        psError(PXTOOLS_ERR_DATA, true, "%s is not a valid stage", stage);
        return false;
    default:
        psError(PXTOOLS_ERR_PROG, true, "ippStageToString returned %d for invalid stage %s",
            stageNum, stage);
        return false;
    }

    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }
    char *rerun_flag =  rerun ? "\n1 " : "\n0" ;

    if (stageNum != IPP_STAGE_DIFF) {
        if (psListLength(where->list)) {
            psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
            psStringAppend(&query, "\nAND %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_psDBRunQueryF(config->dbh, query, rerun_flag)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
            psFree(query);
            return false;
        }
    } else {
        // diff stage query has two types bothways and !bothways
        // so we need to send the rerun flag and the where data twice
        psString whereString = psStringCopy("");
        if (psListLength(where->list)) {
            psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
            psStringAppend(&whereString, "\nAND %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_psDBRunQueryF(config->dbh, query, rerun_flag, whereString, rerun_flag, whereString)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
            psFree(whereString);
            psFree(query);
            return false;
        }
        psFree(whereString);
    }
    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("magictool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // Parse the list of runs ready to be destreaked

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

    psArray *list = psArrayAllocEmpty(16); // List of runs, to print
    for (long i = 0; i < psArrayLength(output); i++) {
        psMetadata *row = output->data[i]; // Row of interest
        psS64 stage_id = psMetadataLookupS64(NULL, row, "stage_id");
        psS64 exp_id = psMetadataLookupS64(NULL, row, "exp_id");
        psS64 magic_id = psMetadataLookupS64(NULL, row, "magic_id");
        psS64 inv_magic_id = psMetadataLookupS64(NULL, row, "inv_magic_id");
        psS64 cam_id = psMetadataLookupS64(NULL, row, "cam_id");
        psString magicRunLabel = psMetadataLookupStr(NULL, row, "label");
        psString magicRunWorkdir = psMetadataLookupStr(NULL, row, "workdir");
        
        // if workdir is not supplied use the magicRun's
        if (!workdir) {
            workdir = magicRunWorkdir;
        }

        psString outroot = NULL;
        // set outroot to workdir/exp_id/stage for example /somewhere/424242/chip
        psStringAppend(&outroot, "%s/%" PRId64 "/%s", workdir, exp_id, stage);

        // create a new magicRun for this group
        magicDSRunRow *run = magicDSRunRowAlloc(
                0, // magic_ds_id
                magic_id,
                inv_magic_id,
                "new",
                stage,
                stage_id,
                cam_id,
                set_label ? set_label : magicRunLabel,
                outroot,
                recoveryroot,
                re_place,
                0); // remove

        psFree(outroot);
        if (!run) {
            psAbort("failed to alloc magicDSRun object");
        }

        if (!dry_run) {
            if (!magicDSRunInsertObject(config->dbh, run)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
                psFree(run);
                psFree(output);
                psFree(list);
                if (!psDBRollback(config->dbh)) {
                    psError(PS_ERR_UNKNOWN, false, "database error");
                }
                return false;
            }
            psS64 magic_ds_id = psDBLastInsertID(config->dbh); // Assigned identifier
            run->magic_ds_id = magic_ds_id;
        }

        psArrayAdd(list, list->n, run);
        psFree(run);
    }

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

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

    psFree(list);

    return true;
}

static psS64 definerunMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // required
    PXOPT_LOOKUP_S64(magic_id, config->args, "-magic_id", true, false);
    PXOPT_LOOKUP_STR(stage, config->args, "-stage", true, false);
    PXOPT_LOOKUP_STR(outroot, config->args, "-outroot", true, false);

    // optional
    PXOPT_LOOKUP_STR(recoveryroot, config->args, "-recoveryroot", false, false);
    PXOPT_LOOKUP_BOOL(re_place, config->args, "-replace", false);
    PXOPT_LOOKUP_BOOL(remove, config->args, "-remove", false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
    PXOPT_LOOKUP_STR(label, config->args, "-label", false, false);

    psS64 stage_id = 0, cam_id = 0;

    if (!magicDSGetIDs(config, stage, magic_id, &stage_id, &cam_id)) {
        psError(PS_ERR_UNKNOWN, false, "failed to get ids");
        return false;
    }

    magicDSRunRow *run = magicDSRunRowAlloc(
            0,          // ID
            magic_id,
            0,          // inv_magic_id
            "new",      // state
            stage,
            stage_id,
            cam_id,
            label,
            outroot,
            recoveryroot,
            re_place,
            remove
    );

    if (!run) {
        psError(PS_ERR_UNKNOWN, false, "failed to alloc magicRun object");
        return false;
    }
    if (!magicDSRunInsertObject(config->dbh, run)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(run);
        return false;
    }

    psS64 magic_ds_id = psDBLastInsertID(config->dbh);
    run->magic_ds_id = magic_ds_id;

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

    psFree(run);

    return magic_id;
}


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

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

    if (state) {
        // set detRun.state to state
        return setmagicDSRunState(config, magic_ds_id, state);
    }

    return true;
}


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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where, "-magic_ds_id", "magic_ds_id", "==");
    PXOPT_COPY_S64(config->args, where, "-magic_id", "magic_id", "==");
    pxAddLabelSearchArgs (config, where, "-label", "label", "==");
    PXOPT_COPY_STR(config->args, where, "-stage", "stage", "==");

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

    // look for "inputs" that need to processed
    psString query = pxDataGet("magicdstool_todestreak.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("magicdstool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

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

    psFree(output);

    return true;
}


static bool
setMagicked(pxConfig *config, psS64 magic_ds_id, psString component)
{
    // first query the magicDSRun to find the stage and the stage_id
    psString query = "SELECT stage, stage_id from magicDSRun where magic_ds_id = %" PRId64;

    if (!p_psDBRunQueryF(config->dbh, query, magic_ds_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)) {
        psTrace("magicdstool", PS_LOG_INFO, "magic_ds_id: %" PRId64 " not found", magic_ds_id);
        psFree(output);
        return true;
    }
    if (psArrayLength(output) > 1) {
        psError(PS_ERR_UNKNOWN, true, "unexpected number of rows found %ld for magic_ds_id %" PRId64,
            psArrayLength(output), magic_ds_id);
        psFree(output);
        return false;
    }
    psMetadata *row = output->data[0];

    psString stage= psMetadataLookupStr(NULL, row, "stage");
    psS64 stage_id = psMetadataLookupS64(NULL, row, "stage_id");

    ippStage stageNum = ippStringToStage(stage);

    // chose the appropriate query based on the stage
    switch (stageNum) {
    case IPP_STAGE_RAW:
        query = "UPDATE rawImfile SET magicked = %" PRId64 " where exp_id = %" PRId64 " AND class_id = '%s'";
        break;
    case IPP_STAGE_CHIP:
        query = "UPDATE chipProcessedImfile SET magicked = %" PRId64 " where chip_id = %" PRId64 " AND class_id = '%s'";
        break;
    case IPP_STAGE_CAMERA:
        // no there is no magicked column in camProcessedExp so we have nothing to do
        psFree(output);
        return true;
    case IPP_STAGE_WARP:
        query = "UPDATE warpSkyfile SET magicked = %" PRId64 " where warp_id = %" PRId64 " AND skycell_id = '%s'";
        break;
    case IPP_STAGE_DIFF:
        query = "UPDATE diffSkyfile SET magicked = %" PRId64 " where diff_id = %" PRId64 " AND skycell_id = '%s'";
        break;
    default:
        psError(PS_ERR_UNKNOWN, true, "unexpected value for stage: %s found", stage);
        psFree(output);
        return false;
    }

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

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

    return true;
}

static bool
setRunMagicked(pxConfig *config, psS64 magic_ds_id)
{
    // first query the magicDSRun to find the stage and the stage_id
    psString query = "SELECT stage, stage_id from magicDSRun where magic_ds_id = %" PRId64;

    if (!p_psDBRunQueryF(config->dbh, query, magic_ds_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, true, "magicDSRun not found for magic_ds_id %" PRId64, magic_ds_id);
        psFree(output);
        return false;
    }
    if (psArrayLength(output) > 1) {
        psError(PS_ERR_UNKNOWN, true, "unexpected number of rows found %ld for magic_ds_id %" PRId64,
            psArrayLength(output), magic_ds_id);
        psFree(output);
        return false;
    }
    psMetadata *row = output->data[0];

    psString stage = psMetadataLookupStr(NULL, row, "stage");
    psS64 stage_id = psMetadataLookupS64(NULL, row, "stage_id");

    ippStage stageNum = ippStringToStage(stage);

    // chose the appropriate query based on the stage
    switch (stageNum) {
    case IPP_STAGE_RAW:
        query = "UPDATE rawExp SET magicked = %" PRId64 " where exp_id = %" PRId64;
        break;
    case IPP_STAGE_CHIP:
        query = "UPDATE chipRun SET magicked = %" PRId64 " where chip_id = %" PRId64;
        break;
    case IPP_STAGE_CAMERA:
        query = "UPDATE camRun SET magicked = %" PRId64 " where cam_id = %" PRId64;
        break;
    case IPP_STAGE_WARP:
        query = "UPDATE warpRun SET magicked = %" PRId64 " where warp_id = %" PRId64;
        break;
    case IPP_STAGE_DIFF:
        query = "UPDATE diffRun SET magicked = %" PRId64 " where diff_id = %" PRId64;
        break;
    default:
        psError(PS_ERR_UNKNOWN, true, "unexpected value for stage: %s found", stage);
        psFree(output);
        return false;
    }
    if (!p_psDBRunQueryF(config->dbh, query, magic_ds_id, stage_id)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    psFree(output);

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

    return true;
}

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

    // required values
    PXOPT_LOOKUP_S64(magic_ds_id, config->args, "-magic_ds_id", true, false);
    PXOPT_LOOKUP_STR(component, config->args, "-component", true, false);

    // default values
    PXOPT_LOOKUP_S16(fault, config->args, "-fault", false, false);
    PXOPT_LOOKUP_STR(backup_path_base, config->args, "-backup_path_base", false, false);
    PXOPT_LOOKUP_STR(recovery_path_base, config->args, "-recovery_path_base", false, false);
    PXOPT_LOOKUP_BOOL(setmagicked, config->args, "-setmagicked", false);

    if (setmagicked && (fault != 0)) {
        psError(PS_ERR_UNKNOWN, true, " cannot setmagicked for faulted file");
        return false;
    }

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

    if (setmagicked) {
        // set the image file's magicked flag
        if (!setMagicked(config, magic_ds_id, component)) {
            psError(PS_ERR_UNKNOWN, false, "setMagicked failed");
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            return false;
        }
    }

    if (!magicDSFileInsert(config->dbh, magic_ds_id, component, backup_path_base, recovery_path_base, fault, "full")) {
            // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    if (!magicDSRunComplete(config, setmagicked)) {
            // 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;
}

static bool magicDSGetIDs(pxConfig *config, psString stage, psS64 magic_id, psS64 *stage_id, psS64 *cam_id)
{
    PS_ASSERT_PTR_NON_NULL(config, false);
    PS_ASSERT_PTR_NON_NULL(stage, false);
    PS_ASSERT_PTR_NON_NULL(stage_id, false);
    PS_ASSERT_PTR_NON_NULL(cam_id, false);

    int stageNum = ippStringToStage(stage);;
    if (stageNum == IPP_STAGE_NONE) {
        psError(PXTOOLS_ERR_DATA, false, "%s is not a valid value for stage", stage);
        return false;
    }

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

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

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("magicdstool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }
    if (psArrayLength(output) > 1) {
        psError(PS_ERR_UNKNOWN, true, "unexpected number of rows found %ld for magic_id %" PRId64,
            psArrayLength(output), magic_id);
        return false;
    }
    psMetadata *row = output->data[0];

    *cam_id = psMetadataLookupS64(NULL, row, "cam_id");
    switch (stageNum) {
    case IPP_STAGE_RAW:
        *stage_id = psMetadataLookupS64(NULL, row, "exp_id");
        break;
    case IPP_STAGE_CHIP:
        *stage_id = psMetadataLookupS64(NULL, row, "chip_id");
        break;
    case IPP_STAGE_CAMERA:
        *stage_id = *cam_id;
        return true;
    case IPP_STAGE_WARP:
        *stage_id = psMetadataLookupS64(NULL, row, "warp_id");
        break;
    case IPP_STAGE_DIFF:
        *stage_id = psMetadataLookupS64(NULL, row, "diff_id");
        break;
    }

    return true;
}

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

    // look for completed magicDSRuns
    psString query = pxDataGet("magicdstool_completed_runs.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    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("magicdstool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }
    for (long i = 0; i < psArrayLength(output); i++) {
        psMetadata *row = output->data[i];

        psS64 magic_ds_id = psMetadataLookupS64(NULL, row, "magic_ds_id");

        // if requested, set stageRun.magicked
        if (setmagicked && !setRunMagicked(config, magic_ds_id)) {
            psError(PS_ERR_UNKNOWN, false, "failed to change stageRun.magicked for magic_ds_id: %" PRId64,
                magic_ds_id);
            return false;
        }

        // set magicDSRun.state to 'full'
        if (!setmagicDSRunState(config, magic_ds_id, "full")) {
            psError(PS_ERR_UNKNOWN, false, "failed to change magicDSRun.state for magic_ds_id: %" PRId64,
                magic_ds_id);
            psFree(output);
            return false;
        }
    }


    return true;
}


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

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_S64(config->args, where, "-magic_ds_id", "magicDSRun.magic_ds_id", "==");
    PXOPT_COPY_STR(config->args, where, "-component", "component", "==");
    PXOPT_COPY_S16(config->args, where, "-fault", "fault", "==");
    pxAddLabelSearchArgs (config, where, "-label", "label", "==");

    psString query = psStringCopy("DELETE FROM magicDSFile USING magicDSFile, magicDSRun  WHERE (magicDSRun.magic_ds_id = magicDSFile.magic_ds_id) AND magicDSFile.fault != 0");

    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, "failed to revert");
        return false;
    }
    return true;
}

static bool getskycellsMode(pxConfig *config)
{
    // required
    PXOPT_LOOKUP_S64(magic_ds_id, config->args, "-magic_ds_id", true, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-class_id",    "warpSkyCellMap.class_id", "==");
    PXOPT_COPY_STR(config->args, where, "-skycell_id",  "warpSkyCellMap.skycell_id", "==");

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

    psString query = pxDataGet("magicdstool_getskycells.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);

    if (!p_psDBRunQueryF(config->dbh, query, magic_ds_id)) {
        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("magicdstool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

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

    psFree(output);

    return true;
}

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

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

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

    return true;
}

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

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

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

    // look for "inputs" that need to processed
    psString query = pxDataGet("magicdstool_toremove.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("magicdstool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

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

    psFree(output);

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

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

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

    // look for "inputs" that need to processed
    psString query = pxDataGet("magicdstool_torestore.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("magicdstool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

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

    psFree(output);

    return true;
}


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

    psMetadata *where = psMetadataAlloc();
    PXOPT_LOOKUP_STR(stage, config->args, "-stage", true, false);

    PXOPT_COPY_S64(config->args, where, "-magic_ds_id", "magic_ds_id", "==");
    PXOPT_COPY_S64(config->args, where, "-magic_id", "magic_id", "==");
    pxAddLabelSearchArgs (config, where, "-label", "magicDSRun.label", "==");

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

    psString sql_file = NULL;
    psStringAppend(&sql_file, "magicdstool_torevert_%s.sql", stage);

    psString query = pxDataGet(sql_file);
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement from %s", sql_file);
        psFree(sql_file);
        return false;
    }
    psFree(sql_file);

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

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

    psFree(output);

    return true;
}


