CREATE TABLE pzDataStore (
    camera VARCHAR(64),
    telescope VARCHAR(64),
    uri VARCHAR(255),
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(camera, telescope)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE summitExp (
    exp_name VARCHAR(64),
    camera VARCHAR(64),
    telescope VARCHAR(64),
    dateobs DATETIME,
    exp_type VARCHAR(64),
    uri VARCHAR(255),
    imfiles INT,
    fault SMALLINT NOT NULL,
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(exp_name, camera, telescope),
    KEY(fault))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE summitImfile (
    exp_name VARCHAR(64),
    camera VARCHAR(64),
    telescope VARCHAR(64),
    file_id VARCHAR(64),
    bytes INT,
    md5sum VARCHAR(32),
    class VARCHAR(64),
    class_id VARCHAR(64),
    uri VARCHAR(255),
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(exp_name, camera, telescope, class, class_id),
    KEY(file_id),
    FOREIGN KEY (exp_name, camera, telescope)
        REFERENCES  summitExp(exp_name, camera, telescope))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE pzDownloadExp (
    exp_name VARCHAR(64),
    camera VARCHAR(64),
    telescope VARCHAR(64),
    state VARCHAR(64),
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(exp_name, camera, telescope),
    KEY(state),
    FOREIGN KEY (exp_name, camera, telescope)
        REFERENCES  summitExp(exp_name, camera, telescope))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE pzDownloadImfile (
    exp_name VARCHAR(64),
    camera VARCHAR(64),
    telescope VARCHAR(64),
    class VARCHAR(64),
    class_id VARCHAR(64),
    uri VARCHAR(255),
    fault SMALLINT NOT NULL,
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    hostname VARCHAR(64),
    PRIMARY KEY(exp_name, camera, telescope, class, class_id),
    KEY(fault),
    FOREIGN KEY (exp_name, camera, telescope)
        REFERENCES  pzDownloadExp(exp_name, camera, telescope),
    FOREIGN KEY (exp_name, camera, telescope, class, class_id)
        REFERENCES  summitImfile(exp_name, camera, telescope, class, class_id)) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE newExp (
    exp_id BIGINT AUTO_INCREMENT,
    tmp_exp_name VARCHAR(64),
    tmp_camera VARCHAR(64),
    tmp_telescope VARCHAR(64),
    state VARCHAR(64),
    workdir VARCHAR(255),
    workdir_state VARCHAR(64),
    reduction VARCHAR(64),
    dvodb VARCHAR(255),
    tess_id VARCHAR(64),
    end_stage VARCHAR(64),
    label VARCHAR(64),
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(exp_id),
    KEY(exp_id),
    KEY(tmp_exp_name),
    KEY(tmp_camera),
    KEY(tmp_telescope),
    KEY(state),
    KEY(workdir_state),
    KEY(end_stage),
    KEY(label)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE newImfile (
    exp_id BIGINT,
    tmp_class_id VARCHAR(64),
    uri VARCHAR(255),
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(exp_id, tmp_class_id),
    FOREIGN KEY (exp_id)
        REFERENCES  newExp(exp_id))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE rawExp (
    exp_id BIGINT,
    exp_name VARCHAR(64),
    camera VARCHAR(64),
    telescope VARCHAR(64),
    dateobs DATETIME,
    exp_tag VARCHAR(255),
    exp_type VARCHAR(64),
    filelevel VARCHAR(64),
    workdir VARCHAR(255),
    reduction VARCHAR(64),
    dvodb VARCHAR(255),
    tess_id VARCHAR(64),
    end_stage VARCHAR(64),
    filter VARCHAR(64),
    comment VARCHAR(80),
    obs_mode VARCHAR(64),
    obs_group VARCHAR(64),
    airmass FLOAT,
    ra DOUBLE,
    decl DOUBLE,
    exp_time FLOAT,
    sat_pixel_frac FLOAT,
    bg DOUBLE,
    bg_stdev DOUBLE,
    bg_mean_stdev DOUBLE,
    alt DOUBLE,
    az DOUBLE,
    ccd_temp FLOAT,
    posang DOUBLE,
    m1_x FLOAT,
    m1_y FLOAT,
    m1_z FLOAT,
    m1_tip FLOAT,
    m1_tilt FLOAT,
    m2_x FLOAT,
    m2_y FLOAT,
    m2_z FLOAT,
    m2_tip FLOAT,
    m2_tilt FLOAT,
    env_temperature FLOAT,
    env_humidity FLOAT,
    env_wind_speed FLOAT,
    env_wind_dir FLOAT,
    teltemp_m1 FLOAT,
    teltemp_m1cell FLOAT,
    teltemp_m2 FLOAT,
    teltemp_spider FLOAT,
    teltemp_truss FLOAT,
    teltemp_extra FLOAT,
    pon_time FLOAT,
    user_1 DOUBLE,
    user_2 DOUBLE,
    user_3 DOUBLE,
    user_4 DOUBLE,
    user_5 DOUBLE,
    object VARCHAR(64),
    sun_angle FLOAT,
    sun_alt FLOAT,
    moon_angle FLOAT,
    moon_alt FLOAT,
    moon_phase FLOAT,
    hostname VARCHAR(64),
    fault SMALLINT NOT NULL,
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(exp_id),
    KEY(exp_name),
    KEY(end_stage),
    KEY(fault),
    FOREIGN KEY (exp_id)
        REFERENCES  newExp(exp_id))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE rawImfile (
    exp_id BIGINT,
    exp_name VARCHAR(64),
    camera VARCHAR(64),
    telescope VARCHAR(64),
    dateobs DATETIME,
    tmp_class_id VARCHAR(64),
    class_id VARCHAR(64),
    uri VARCHAR(255),
    exp_type VARCHAR(64),
    filelevel VARCHAR(64),
    filter VARCHAR(64),
    comment VARCHAR(80),
    obs_mode VARCHAR(64),
    obs_group VARCHAR(64),
    airmass FLOAT,
    ra DOUBLE,
    decl DOUBLE,
    exp_time FLOAT,
    sat_pixel_frac FLOAT,
    bg DOUBLE,
    bg_stdev DOUBLE,
    bg_mean_stdev DOUBLE,
    alt DOUBLE,
    az DOUBLE,
    ccd_temp FLOAT,
    posang DOUBLE,
    m1_x FLOAT,
    m1_y FLOAT,
    m1_z FLOAT,
    m1_tip FLOAT,
    m1_tilt FLOAT,
    m2_x FLOAT,
    m2_y FLOAT,
    m2_z FLOAT,
    m2_tip FLOAT,
    m2_tilt FLOAT,
    env_temperature FLOAT,
    env_humidity FLOAT,
    env_wind_speed FLOAT,
    env_wind_dir FLOAT,
    teltemp_m1 FLOAT,
    teltemp_m1cell FLOAT,
    teltemp_m2 FLOAT,
    teltemp_spider FLOAT,
    teltemp_truss FLOAT,
    teltemp_extra FLOAT,
    pon_time FLOAT,
    user_1 DOUBLE,
    user_2 DOUBLE,
    user_3 DOUBLE,
    user_4 DOUBLE,
    user_5 DOUBLE,
    object VARCHAR(64),
    sun_angle FLOAT,
    sun_alt FLOAT,
    moon_angle FLOAT,
    moon_alt FLOAT,
    moon_phase FLOAT,
    ignored TINYINT DEFAULT 0,
    hostname VARCHAR(64),
    fault SMALLINT NOT NULL,
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    raw_image_id BIGINT AUTO_INCREMENT,
    magicked TINYINT,
    PRIMARY KEY(exp_id, class_id),
    KEY(tmp_class_id),
    KEY(fault),
    KEY(raw_image_id),
    UNIQUE KEY(exp_id, tmp_class_id),
    FOREIGN KEY (exp_id, tmp_class_id)
        REFERENCES newImfile(exp_id, tmp_class_id))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE guidePendingExp (guide_id BIGINT AUTO_INCREMENT, exp_id BIGINT, recipe VARCHAR(64), PRIMARY KEY(guide_id), KEY(guide_id), KEY(exp_id)) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE chipRun (
    chip_id BIGINT AUTO_INCREMENT,
    exp_id BIGINT,
    state VARCHAR(64),
    workdir VARCHAR(255),
    workdir_state VARCHAR(64),
    label VARCHAR(64),
    reduction VARCHAR(64),
     expgroup VARCHAR(64),
    dvodb VARCHAR(255),
    tess_id VARCHAR(64),
    end_stage VARCHAR(64),
    PRIMARY KEY(chip_id),
    KEY(chip_id), KEY(exp_id),
    KEY(state),
    KEY(workdir_state),
    KEY(label),
    KEY(expgroup),
    KEY(end_stage),
    INDEX(chip_id, exp_id),
    FOREIGN KEY (exp_id)
        REFERENCES rawExp(exp_id))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE chipProcessedImfile (
    chip_id BIGINT,
    exp_id BIGINT,
    class_id VARCHAR(64),
    data_state VARCHAR(64),
    uri VARCHAR(255),
    bg FLOAT,
    bg_stdev FLOAT,
    bg_mean_stdev FLOAT,
    bias FLOAT,
    bias_stdev FLOAT,
    fringe_0 FLOAT,
    fringe_1 FLOAT,
    fringe_2 FLOAT,
    ap_resid FLOAT,
    ap_resid_stdev FLOAT,
    fwhm_major FLOAT,
    fwhm_major_lq FLOAT,
    fwhm_major_uq FLOAT,
    fwhm_minor FLOAT,
    fwhm_minor_lq FLOAT,
    fwhm_minor_uq FLOAT,
    iq_fwhm_major FLOAT,
    iq_fwhm_major_err FLOAT,
    iq_fwhm_minor FLOAT,
    iq_fwhm_minor_err FLOAT,
    iq_m2 FLOAT,
    iq_m2_err FLOAT,
    iq_m2_lq FLOAT,
    iq_m2_uq FLOAT,
    iq_m2c FLOAT,
    iq_m2c_err FLOAT,
    iq_m2c_lq FLOAT,
    iq_m2c_uq FLOAT,
    iq_m2s FLOAT,
    iq_m2s_err FLOAT,
    iq_m2s_lq FLOAT,
    iq_m2s_uq FLOAT,
    iq_m3 FLOAT,
    iq_m3_err FLOAT,
    iq_m3_lq FLOAT,
    iq_m3_uq FLOAT,
    iq_m4 FLOAT,
    iq_m4_err FLOAT,
    iq_m4_lq FLOAT,
    iq_m4_uq FLOAT,
    dtime_detrend FLOAT,
    dtime_photom FLOAT,
    dtime_total FLOAT,
    dtime_script FLOAT,
    hostname VARCHAR(64),
    n_stars INT,
    n_psfstars INT,
    n_iqstars INT,
    n_extended INT,
    n_cr INT,
    path_base VARCHAR(255),
    fault SMALLINT NOT NULL,
    chip_image_id BIGINT AUTO_INCREMENT,
    magicked BIGINT,
    PRIMARY KEY(chip_id, exp_id, class_id),
    KEY(data_state),
    KEY(fault),
    KEY(chip_image_id),
    FOREIGN KEY (chip_id, exp_id)
        REFERENCES  chipRun(chip_id, exp_id),
    FOREIGN KEY (exp_id, class_id)
        REFERENCES  rawImfile(exp_id, class_id))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE chipMask (
    label VARCHAR(64),
    PRIMARY KEY(label))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE camRun (
    cam_id BIGINT AUTO_INCREMENT,
    chip_id BIGINT,
    state VARCHAR(64),
    workdir VARCHAR(255),
    workdir_state VARCHAR(64),
    label VARCHAR(64),
    reduction VARCHAR(64),
    expgroup VARCHAR(64),
    dvodb VARCHAR(255),
    tess_id VARCHAR(64),
    end_stage VARCHAR(64),
    PRIMARY KEY(cam_id),
    KEY(cam_id),
    KEY(chip_id),
    KEY(state),
    KEY(workdir_state),
    KEY(label),
    KEY(expgroup),
    KEY(end_stage),
    INDEX(cam_id, chip_id),
    FOREIGN KEY (chip_id)
        REFERENCES chipRun(chip_id))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE camProcessedExp (
    cam_id BIGINT,
    uri VARCHAR(255),
    bg FLOAT,
    bg_stdev FLOAT,
    bg_mean_stdev FLOAT,
    bias FLOAT,
    bias_stdev FLOAT,
    fringe_0 FLOAT,
    fringe_1 FLOAT,
    fringe_2 FLOAT,
    sigma_ra FLOAT,
    sigma_dec FLOAT,
    ap_resid FLOAT,
    ap_resid_stdev FLOAT,
    zpt_obs FLOAT,
    zpt_stdev FLOAT,
    zpt_lq FLOAT,
    zpt_uq FLOAT,
    fwhm_major FLOAT,
    fwhm_major_lq FLOAT,
    fwhm_major_uq FLOAT,
    fwhm_minor FLOAT,
    fwhm_minor_lq FLOAT,
    fwhm_minor_uq FLOAT,
    iq_fwhm_major FLOAT,
    iq_fwhm_major_err FLOAT,
    iq_fwhm_minor FLOAT,
    iq_fwhm_minor_err FLOAT,
    iq_m2 FLOAT,
    iq_m2_err FLOAT,
    iq_m2_lq FLOAT,
    iq_m2_uq FLOAT,
    iq_m2c FLOAT,
    iq_m2c_err FLOAT,
    iq_m2c_lq FLOAT,
    iq_m2c_uq FLOAT,
    iq_m2s FLOAT,
    iq_m2s_err FLOAT,
    iq_m2s_lq FLOAT,
    iq_m2s_uq FLOAT,
    iq_m3 FLOAT,
    iq_m3_err FLOAT,
    iq_m3_lq FLOAT,
    iq_m3_uq FLOAT,
    iq_m4 FLOAT,
    iq_m4_err FLOAT,
    iq_m4_lq FLOAT,
    iq_m4_uq FLOAT,
    dtime_script FLOAT,
    dtime_astrom FLOAT,
    dtime_addstar FLOAT,
    hostname VARCHAR(64),
    n_stars INT,
    n_psfstars INT,
    n_iqstars INT,
    n_extended INT,
    n_cr INT,
    n_astrom INT,
    path_base VARCHAR(255),
    fault SMALLINT NOT NULL,
    PRIMARY KEY(cam_id),
    KEY(fault),
    FOREIGN KEY (cam_id)
        REFERENCES camRun(cam_id))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE camMask (
    label VARCHAR(64),
    PRIMARY KEY(label))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE fakeRun (
    fake_id BIGINT AUTO_INCREMENT,
    cam_id BIGINT,
    state VARCHAR(64),
    workdir VARCHAR(255),
    label VARCHAR(64),
    reduction VARCHAR(64),
    expgroup VARCHAR(64),
    dvodb VARCHAR(255),
    tess_id VARCHAR(64),
    end_stage VARCHAR(64),
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(fake_id),
    KEY(cam_id),
    KEY(state),
    KEY(label),
    KEY(expgroup),
    KEY(end_stage),
    INDEX(fake_id, cam_id),
    FOREIGN KEY (cam_id)
        REFERENCES camRun(cam_id))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE fakeProcessedImfile (
    fake_id BIGINT AUTO_INCREMENT,
    exp_id BIGINT,
    class_id VARCHAR(64),
    uri VARCHAR(255),
    dtime_fake FLOAT,
    dtime_script FLOAT,
    hostname VARCHAR(64),
    path_base VARCHAR(255),
    data_state VARCHAR(64),
    fault SMALLINT NOT NULL,
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(fake_id, exp_id, class_id),
    KEY(fault),
    FOREIGN KEY (fake_id)
        REFERENCES fakeRun(fake_id),
    FOREIGN KEY (exp_id, class_id)
        REFERENCES rawImfile(exp_id, class_id))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE fakeMask (
    label VARCHAR(64),
    PRIMARY KEY(label))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE detRun (
    det_id BIGINT AUTO_INCREMENT,
    iteration INT,
    det_type VARCHAR(64),
    mode VARCHAR(64),
    state VARCHAR(64),
    filelevel VARCHAR(64),
    workdir VARCHAR(255),
    camera VARCHAR(64),
    telescope VARCHAR(64),
    exp_type VARCHAR(64),
    reduction VARCHAR(64),
    filter VARCHAR(64),
    airmass_min FLOAT,
    airmass_max FLOAT,
    exp_time_min FLOAT,
    exp_time_max FLOAT,
    ccd_temp_min FLOAT,
    ccd_temp_max FLOAT,
    posang_min DOUBLE,
    posang_max DOUBLE,
    registered DATETIME,
    time_begin DATETIME,
    time_end DATETIME,
    use_begin DATETIME,
    use_end DATETIME,
    solang_min FLOAT,
    solang_max FLOAT,
    label VARCHAR(64),
    ref_det_id BIGINT,
    ref_iter INT,
    -- parent INT, :: dropping this
    PRIMARY KEY(det_id),
    KEY(det_id),
    KEY(iteration),
    KEY(det_type),
    KEY(mode),
    KEY(state),
    KEY(label),
    -- KEY(parent), :: dropping this
    INDEX(det_id, iteration))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE detInputExp (
det_id BIGINT,
iteration INT,
exp_id BIGINT,
include TINYINT,
PRIMARY KEY(det_id, iteration, exp_id),
INDEX(det_id, exp_id),
INDEX(det_id, iteration),
FOREIGN KEY (det_id)
REFERENCES  detRun(det_id),
FOREIGN KEY (exp_id)
REFERENCES  rawExp(exp_id))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE detProcessedImfile (
    det_id BIGINT,
    exp_id BIGINT,
    class_id VARCHAR(64),
    uri VARCHAR(255),
    recipe VARCHAR(64),
    bg DOUBLE,
    bg_stdev DOUBLE,
    bg_mean_stdev DOUBLE,
    fringe_0 DOUBLE,
    fringe_1 DOUBLE,
    fringe_2 DOUBLE,
    user_1 DOUBLE,
    user_2 DOUBLE,
    user_3 DOUBLE,
    user_4 DOUBLE,
    user_5 DOUBLE,
    path_base VARCHAR(255),
    data_state VARCHAR(64),
    fault SMALLINT NOT NULL,
    PRIMARY KEY(det_id, exp_id, class_id),
    KEY(fault),
    INDEX(det_id, class_id),
    INDEX(det_id, exp_id),
    FOREIGN KEY (det_id, exp_id)
        REFERENCES  detInputExp(det_id, exp_id),
    FOREIGN KEY (exp_id, class_id)
        REFERENCES  rawImfile(exp_id, class_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE detProcessedExp (
    det_id BIGINT,
    exp_id BIGINT,
    recipe VARCHAR(64),
    bg DOUBLE,
    bg_stdev DOUBLE,
    bg_mean_stdev DOUBLE,
    fringe_0 DOUBLE,
    fringe_1 DOUBLE,
    fringe_2 DOUBLE,
    user_1 DOUBLE,
    user_2 DOUBLE,
    user_3 DOUBLE,
    user_4 DOUBLE,
    user_5 DOUBLE,
    path_base VARCHAR(255),
    data_state VARCHAR(64),
    fault SMALLINT NOT NULL,
    PRIMARY KEY(det_id, exp_id),
    KEY(fault),
    FOREIGN KEY (det_id, exp_id)
        REFERENCES  detInputExp(det_id, exp_id),
    FOREIGN KEY (det_id, exp_id)
        REFERENCES  detProcessedImfile(det_id, exp_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE detStackedImfile (
    det_id BIGINT,
    iteration INT,
    class_id VARCHAR(64),
    uri VARCHAR(255),
    recipe VARCHAR(64),
    bg DOUBLE,
    bg_stdev DOUBLE,
    bg_mean_stdev DOUBLE,
    user_1 DOUBLE,
    user_2 DOUBLE,
    user_3 DOUBLE,
    user_4 DOUBLE,
    user_5 DOUBLE,
    data_state VARCHAR(64),
    fault SMALLINT NOT NULL,
    PRIMARY KEY(det_id, iteration, class_id),
    KEY(fault),
    FOREIGN KEY (det_id, iteration)
        REFERENCES  detInputExp(det_id, iteration),
    FOREIGN KEY (det_id, class_id)
        REFERENCES  detProcessedImfile(det_id, class_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE detNormalizedStatImfile (
    det_id BIGINT,
    iteration INT,
    class_id VARCHAR(64),
    norm FLOAT,
    data_state VARCHAR(64),
    fault SMALLINT NOT NULL,
    PRIMARY KEY(det_id, iteration, class_id),
    KEY(fault),
    FOREIGN KEY (det_id, iteration)
    REFERENCES  detInputExp(det_id, iteration),
    FOREIGN KEY (det_id, iteration, class_id)
    REFERENCES  detStackedImfile(det_id, iteration, class_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE detNormalizedImfile (
    det_id BIGINT,
    iteration INT,
    class_id VARCHAR(64),
    uri VARCHAR(255),
    bg DOUBLE,
    bg_stdev DOUBLE,
    bg_mean_stdev DOUBLE,
    user_1 DOUBLE,
    user_2 DOUBLE,
    user_3 DOUBLE,
    user_4 DOUBLE,
    user_5 DOUBLE,
    path_base VARCHAR(255),
    data_state VARCHAR(64),
    fault SMALLINT NOT NULL,
    PRIMARY KEY(det_id, iteration, class_id),
    KEY(fault),
    INDEX(det_id, iteration),
    FOREIGN KEY (det_id)
    REFERENCES  detInputExp(det_id),
    FOREIGN KEY (det_id, iteration, class_id)
    REFERENCES  detNormalizedStatImfile(det_id, iteration, class_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE detNormalizedExp (
    det_id BIGINT,
    iteration INT,
    recipe VARCHAR(64),
    bg DOUBLE,
    bg_stdev DOUBLE,
    bg_mean_stdev DOUBLE,
    user_1 DOUBLE,
    user_2 DOUBLE,
    user_3 DOUBLE,
    user_4 DOUBLE,
    user_5 DOUBLE,
    path_base VARCHAR(255),
    data_state VARCHAR(64),
    fault SMALLINT NOT NULL,
    PRIMARY KEY(det_id, iteration),
    KEY(fault),
    FOREIGN KEY (det_id, iteration)
    REFERENCES  detInputExp(det_id, iteration),
    FOREIGN KEY (det_id, iteration)
    REFERENCES  detNormalizedImfile(det_id, iteration)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE detResidImfile (
    det_id BIGINT,
    iteration INT,
    ref_det_id BIGINT,
    ref_iter INT,
    exp_id BIGINT,
    class_id VARCHAR(64),
    uri VARCHAR(255),
    recipe VARCHAR(64),
    bg DOUBLE,
    bg_stdev DOUBLE,
    bg_mean_stdev DOUBLE,
    bg_skewness DOUBLE,
    bg_kurtosis DOUBLE,
    bin_stdev DOUBLE,
    fringe_0 DOUBLE,
    fringe_1 DOUBLE,
    fringe_2 DOUBLE,
    fringe_resid_0 DOUBLE,
    fringe_resid_1 DOUBLE,
    fringe_resid_2 DOUBLE,
    user_1 DOUBLE,
    user_2 DOUBLE,
    user_3 DOUBLE,
    user_4 DOUBLE,
    user_5 DOUBLE,
    path_base VARCHAR(255),
    data_state VARCHAR(64),
    fault SMALLINT NOT NULL,
    PRIMARY KEY(det_id, iteration, exp_id, class_id),
    KEY(fault),
    INDEX(det_id, iteration, exp_id),
    FOREIGN KEY (det_id, iteration, exp_id)
    REFERENCES  detInputExp(det_id, iteration, exp_id),
    FOREIGN KEY (det_id, exp_id, class_id)
    REFERENCES  detProcessedImfile(det_id, exp_id, class_id),
    FOREIGN KEY (ref_det_id, ref_iter)
    REFERENCES  detNormalizedExp(det_id, iteration)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE detResidExp (
       det_id BIGINT,
       iteration INT,
       exp_id BIGINT,
       recipe VARCHAR(64),
       bg DOUBLE,
       bg_stdev DOUBLE,
       bg_mean_stdev DOUBLE,
       bg_skewness DOUBLE,
       bg_kurtosis DOUBLE,
       bin_stdev DOUBLE,
       fringe_0 DOUBLE,
       fringe_1 DOUBLE,
       fringe_2 DOUBLE,
       fringe_resid_0 DOUBLE,
       fringe_resid_1 DOUBLE,
       fringe_resid_2 DOUBLE,
       user_1 DOUBLE,
       user_2 DOUBLE,
       user_3 DOUBLE,
       user_4 DOUBLE,
       user_5 DOUBLE,
       path_base VARCHAR(255),
       data_state VARCHAR(64),
       accept TINYINT,
       fault SMALLINT NOT NULL,
       PRIMARY KEY(det_id, iteration, exp_id),
       KEY(fault),
       INDEX(det_id, iteration),
       FOREIGN KEY (det_id, iteration, exp_id)
       REFERENCES  detInputExp(det_id, iteration, exp_id),
       FOREIGN KEY (det_id, iteration, exp_id)
       REFERENCES  detResidImfile(det_id, iteration, exp_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE detRunSummary (
       det_id BIGINT,
       iteration INT,
       data_state VARCHAR(64),
       bg DOUBLE,
       bg_stdev DOUBLE,
       bg_mean_stdev DOUBLE,
       accept TINYINT,
       fault SMALLINT NOT NULL,
       PRIMARY KEY(det_id, iteration),
       KEY(fault),
       FOREIGN KEY (det_id, iteration)
         REFERENCES  detInputExp(det_id, iteration),
       FOREIGN KEY (det_id, iteration)
         REFERENCES  detResidExp(det_id, iteration)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE detRegisteredImfile (
       det_id BIGINT,
       iteration INT,
       class_id VARCHAR(64),
       uri VARCHAR(255),
       bg DOUBLE,
       bg_stdev DOUBLE,
       bg_mean_stdev DOUBLE,
       user_1 DOUBLE,
       user_2 DOUBLE,
       user_3 DOUBLE,
       user_4 DOUBLE,
       user_5 DOUBLE,
       path_base VARCHAR(255),
       data_state VARCHAR(64),
       fault SMALLINT NOT NULL,
       PRIMARY KEY(det_id, iteration, class_id),
       KEY(fault),
       FOREIGN KEY (det_id, iteration)
         REFERENCES  detRun(det_id, iteration)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE warpRun (
    warp_id BIGINT AUTO_INCREMENT,
    fake_id BIGINT,
    mode VARCHAR(64),
    state VARCHAR(64),
    workdir VARCHAR(255),
    workdir_state VARCHAR(64),
    label VARCHAR(64),
    dvodb VARCHAR(255),
    tess_id VARCHAR(64),
    end_stage VARCHAR(64),
    registered DATETIME,
    PRIMARY KEY(warp_id),
    KEY(warp_id),
    KEY(fake_id),
    KEY(mode),
    KEY(state),
    KEY(workdir_state),
    KEY(label),
    KEY(end_stage),
    INDEX(warp_id, fake_id),
    FOREIGN KEY (fake_id)
        REFERENCES fakeRun(fake_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE warpSkyCellMap (
    warp_id BIGINT,
    skycell_id VARCHAR(64),
    tess_id VARCHAR(64),
    class_id VARCHAR(64),
    fault SMALLINT,
    PRIMARY KEY(warp_id, skycell_id, tess_id, class_id),
    KEY(fault),
    FOREIGN KEY (warp_id)
        REFERENCES warpRun(warp_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE warpSkyfile (
    warp_id BIGINT,
    skycell_id VARCHAR(64),
    tess_id VARCHAR(64),
    uri VARCHAR(255),
    path_base VARCHAR(255),
    data_state VARCHAR(64),
    bg DOUBLE,
    bg_stdev DOUBLE,
    dtime_warp FLOAT,
    dtime_script FLOAT,
    hostname VARCHAR(64),
    good_frac FLOAT,
    xmin INT,
    xmax INT,
    ymin INT,
    ymax INT,
    ignored TINYINT,
    fault SMALLINT,
    warp_image_id BIGINT AUTO_INCREMENT,
    magicked TINYINT,
    PRIMARY KEY(warp_id, skycell_id, tess_id),
    KEY(good_frac),
    KEY(ignored), KEY(fault),
    KEY(warp_image_id),
    FOREIGN KEY (warp_id, skycell_id, tess_id)
        REFERENCES warpSkyCellMap(warp_id, skycell_id, tess_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE warpMask (
    label VARCHAR(64),
    PRIMARY KEY(label)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE stackRun (
        stack_id BIGINT AUTO_INCREMENT,
        state VARCHAR(64),
        workdir VARCHAR(255),
        label VARCHAR(64),
        reduction VARCHAR(64),
        dvodb VARCHAR(255),
        registered DATETIME,
        skycell_id VARCHAR(64),
        tess_id VARCHAR(64),
        filter VARCHAR(64),
        PRIMARY KEY(stack_id),
        KEY(stack_id),
        KEY(state),
        KEY(skycell_id),
        KEY(tess_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE stackInputSkyfile (
        stack_id BIGINT,
        warp_id BIGINT,
        PRIMARY KEY(stack_id, warp_id),
        FOREIGN KEY (stack_id)  REFERENCES  stackRun(stack_id),
        FOREIGN KEY (warp_id)  REFERENCES  warpSkyfile(warp_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE stackSumSkyfile (
        stack_id BIGINT,
        uri VARCHAR(255),
        path_base VARCHAR(255),
        bg DOUBLE,
        bg_stdev DOUBLE,
        dtime_stack FLOAT,
        dtime_match_mean FLOAT,
        dtime_match_stdev FLOAT,
        dtime_initial FLOAT,
        dtime_reject FLOAT,
        dtime_final FLOAT,
        dtime_phot FLOAT,
        dtime_script FLOAT,
        match_mean FLOAT,
        match_stdev FLOAT,
        match_rms FLOAT,
        stamps_mean FLOAT,
        stamps_stdev FLOAT,
        stamps_min INT,
        reject_images INT,
        reject_pix_mean FLOAT,
        reject_pix_stdev FLOAT,
        sources INT,
        hostname VARCHAR(64),
        good_frac FLOAT,
        fault SMALLINT,
        PRIMARY KEY(stack_id),
        KEY(dtime_stack),
        KEY(good_frac),
        KEY(fault),
        FOREIGN KEY (stack_id)  REFERENCES  stackRun(stack_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE diffRun (
        diff_id BIGINT AUTO_INCREMENT,
        state VARCHAR(64),
        workdir VARCHAR(255),
        label VARCHAR(64),
        reduction VARCHAR(64),
        dvodb VARCHAR(255),
        registered DATETIME,
        tess_id VARCHAR(64),
        exp_id  BIGINT,
        PRIMARY KEY(diff_id),
        KEY(diff_id),
        KEY(state),
        KEY(tess_id),
        FOREIGN KEY (exp_id) REFERENCES rawExp(exp_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE diffInputSkyfile (
        diff_id BIGINT,
        skycell_id VARCHAR(64),
        warp1 BIGINT,
        stack1 BIGINT,
        warp2 BIGINT,
        stack2 BIGINT,
        tess_id VARCHAR(64),
        PRIMARY KEY(diff_id, skycell_id),
        KEY(warp1),
        KEY(warp2),
        KEY(stack1),
        KEY(stack2),
        KEY(skycell_id),
        KEY(tess_id),
        FOREIGN KEY (diff_id)  REFERENCES  diffRun(diff_id),
        FOREIGN KEY (warp1, skycell_id, tess_id)  REFERENCES  warpSkyfile(warp_id, skycell_id, tess_id),
        FOREIGN KEY (warp2, skycell_id, tess_id)  REFERENCES  warpSkyfile(warp_id, skycell_id, tess_id),
        FOREIGN KEY (stack1)  REFERENCES  stackSumSkyfile(stack_id),
        FOREIGN KEY (stack2)  REFERENCES  stackSumSkyfile(stack_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE diffSkyfile (
        diff_id BIGINT,
        skycell_id VARCHAR(64),
        uri VARCHAR(255),
        path_base VARCHAR(255),
        bg DOUBLE,
        bg_stdev DOUBLE,
        stamps_num INT,
        stamps_mean FLOAT,
        stamps_rms FLOAT,
        norm FLOAT,
        bg_diff FLOAT,
        kernel_x FLOAT,
        kernel_y FLOAT,
        kernel_xx FLOAT,
        kernel_xy FLOAT,
        kernel_yy FLOAT,
        sources INT,
        dtime_diff FLOAT,
        dtime_match FLOAT,
        dtime_phot FLOAT,
        dtime_script FLOAT,
        hostname VARCHAR(64),
        good_frac FLOAT,
        fault SMALLINT,
        diff_image_id BIGINT AUTO_INCREMENT,
        magicked TINYINT,
        PRIMARY KEY(diff_id, skycell_id),
        KEY(good_frac),
        KEY(fault),
        KEY(diff_image_id),
        FOREIGN KEY (diff_id)  REFERENCES  diffRun(diff_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE magicRun (
        magic_id BIGINT AUTO_INCREMENT,
        exp_id BIGINT,
        diff_id BIGINT,
        state VARCHAR(64),
        workdir VARCHAR(255),
        workdir_state VARCHAR(255),
        label VARCHAR(64),
        dvodb VARCHAR(255),
        registered DATETIME,
        fault SMALLINT,
        PRIMARY KEY(magic_id),
        KEY(magic_id),
        KEY(state),
        KEY(workdir_state),
        KEY(label),
        KEY(fault),
        FOREIGN KEY (exp_id)  REFERENCES rawExp(exp_id),
        FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE magicInputSkyfile (
        magic_id BIGINT,
        diff_id BIGINT,
        node VARCHAR(64),
        PRIMARY KEY(magic_id, diff_id, node),
        FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id),
        FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE magicTree (
        magic_id BIGINT,
        node VARCHAR(64),
        dep VARCHAR(64),
        KEY(magic_id),
        KEY(node),
        KEY(dep),
        INDEX(magic_id, node),
        FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE magicNodeResult (
        magic_id BIGINT,
        node VARCHAR(64),
        uri VARCHAR(255),
        fault SMALLINT,
        PRIMARY KEY(magic_id, node),
        FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id),
        FOREIGN KEY (magic_id, node)  REFERENCES  magicTree(magic_id, node),
        KEY(fault)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE magicMask (
        magic_id BIGINT,
        uri VARCHAR(255),
        streaks INT,
        fault SMALLINT,
        PRIMARY KEY(magic_id),
        FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id),
        KEY(fault)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE magicDSRun (
        magic_ds_id BIGINT AUTO_INCREMENT,
        magic_id BIGINT,
        state VARCHAR(64),
        stage VARCHAR(64),
        stage_id BIGINT,
        cam_id BIGINT,
        outroot VARCHAR(255),
        recoveryroot VARCHAR(255),
        re_place TINYINT,
        remove TINYINT,
        PRIMARY KEY(magic_ds_id),
        KEY(magic_ds_id),
        KEY(state),
        FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE magicDSFile (
    magic_ds_id BIGINT,
    component VARCHAR(64),
    backup_path_base VARCHAR(255),
    recovery_path_base VARCHAR(255),
    fault SMALLINT,
    PRIMARY KEY(magic_ds_id, component),
    KEY(fault),
    FOREIGN KEY (magic_ds_id) REFERENCES magicDSRun(magic_ds_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE calDB (
        cal_id BIGINT AUTO_INCREMENT,
        dvodb VARCHAR(64),
        state VARCHAR(64),
        PRIMARY KEY(cal_id),
        KEY(cal_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE calRun (
        cal_id BIGINT AUTO_INCREMENT,
        region VARCHAR(64),
        last_step VARCHAR(64),
        state VARCHAR(64),
        PRIMARY KEY(cal_id),
        KEY(cal_id),
        KEY(last_step),
        FOREIGN KEY (cal_id)  REFERENCES  calDB(cal_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE flatcorrRun (
        corr_id BIGINT AUTO_INCREMENT,
        det_type VARCHAR(64),
        dvodb VARCHAR(64),
        camera VARCHAR(64),
        telescope VARCHAR(64),
        epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        filter VARCHAR(64),
        state VARCHAR(64),
        workdir VARCHAR(255),
        label VARCHAR(64),
        reduction VARCHAR(64),
        region VARCHAR(64),
        hostname VARCHAR(64),
        fault SMALLINT NOT NULL,
        PRIMARY KEY(corr_id),
        KEY(corr_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

-- these two tables link the flatcorrRun to the associated chip and camera runs
CREATE TABLE flatcorrChipLink (
        corr_id BIGINT,
        chip_id BIGINT,
        include TINYINT,
        PRIMARY KEY(corr_id, chip_id),
        FOREIGN KEY (corr_id)  REFERENCES  flatcorrRun(corr_id),
        FOREIGN KEY (chip_id)  REFERENCES  chipRun(chip_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE flatcorrCamLink (
        corr_id BIGINT,
        chip_id BIGINT,
        cam_id BIGINT,
        include TINYINT,
        PRIMARY KEY(corr_id, cam_id),
        FOREIGN KEY (corr_id)  REFERENCES  flatcorrRun(corr_id),
        FOREIGN KEY (chip_id)  REFERENCES  chipRun(chip_id),
        FOREIGN KEY (cam_id)  REFERENCES  camRun(cam_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE pstampDataStore (
        ds_id BIGINT AUTO_INCREMENT,
        state VARCHAR(64),
        lastFileset VARCHAR(64),
        outProduct VARCHAR(64) UNIQUE,
        uri VARCHAR(255),
        PRIMARY KEY(ds_id),
        KEY(ds_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE pstampProject (
        proj_id BIGINT AUTO_INCREMENT,
        name VARCHAR(64) UNIQUE,
        state VARCHAR(64),
        dbname VARCHAR(64),
        dvodb VARCHAR(64),
        camera VARCHAR(64),
        telescope VARCHAR(64),
        need_magic TINYINT,
        PRIMARY KEY(proj_id),
        KEY(proj_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE pstampRequest (
        req_id BIGINT AUTO_INCREMENT,
        ds_id BIGINT,
        state VARCHAR(64),
        name VARCHAR(64) UNIQUE,
        reqType VARCHAR(16),
        outProduct VARCHAR(64),
        uri VARCHAR(255),
        fault SMALLINT,
        PRIMARY KEY(req_id),
        KEY(req_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE pstampJob (
        job_id BIGINT AUTO_INCREMENT,
        req_id BIGINT,
        rownum VARCHAR(64),
        state VARCHAR(64),
        jobType VARCHAR(16),
        fault SMALLINT,
        uri VARCHAR(255),
        exp_id BIGINT,
        outputBase VARCHAR(255),
        args VARCHAR(511),
        PRIMARY KEY(job_id, req_id),
        KEY(job_id),
        FOREIGN KEY (req_id)  REFERENCES  pstampRequest(req_id)
) ENGINE=innodb DEFAULT CHARSET=latin1
