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,
    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),
    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),
    solang 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),
    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),
    hostname VARCHAR(64),
    fault SMALLINT NOT NULL,
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(exp_id, class_id),
    KEY(tmp_class_id),
    KEY(fault),
    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), 
    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, 
    zp_mean FLOAT, 
    zp_stdev FLOAT, 
    fwhm_major FLOAT, 
    fwhm_minor FLOAT, 
    dtime_detrend FLOAT, 
    dtime_photom FLOAT, 
    dtime_astrom FLOAT, 
    hostname VARCHAR(64), 
    n_stars INT, 
    n_extended INT, 
    n_cr INT, 
    n_astrom INT, 
    path_base VARCHAR(255), 
    fault SMALLINT NOT NULL, 
    PRIMARY KEY(chip_id, exp_id, class_id), 
    KEY(fault), 
    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, 
    zp_mean FLOAT, 
    zp_stdev FLOAT, 
    fwhm_major FLOAT, 
    fwhm_minor FLOAT, 
    dtime_detrend FLOAT, 
    dtime_photom FLOAT, 
    dtime_astrom FLOAT, 
    hostname VARCHAR(64),
    n_stars 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, 
    hostname VARCHAR(64),
    path_base VARCHAR(255), 
    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), 
    parent INT, 
    PRIMARY KEY(det_id), 
    KEY(det_id), 
    KEY(iteration), 
    KEY(det_type), 
    KEY(mode), 
    KEY(state), 
    KEY(label), 
    KEY(parent), 
    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), 
    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), 
    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, 
    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, 
    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), 
    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), 
    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, 
    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), 
    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 (det_id, iteration)  
    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), 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, 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), 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, 
    magiced TINYINT, 
    PRIMARY KEY(warp_id), 
    KEY(warp_id), 
    KEY(fake_id), 
    KEY(mode), 
    KEY(state), 
    KEY(workdir_state), 
    KEY(label), 
    KEY(end_stage), 
    KEY(magiced), 
    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), 
    bg DOUBLE, 
    bg_stdev DOUBLE, 
    dtime_warp FLOAT, 
    hostname VARCHAR(64), 
    good_frac FLOAT, 
    xmin INT, 
    xmax INT, 
    ymin INT, 
    ymax INT, 
    ignored TINYINT, 
    fault SMALLINT, 
    PRIMARY KEY(warp_id, skycell_id, tess_id), 
    KEY(good_frac), 
    KEY(ignored), KEY(fault), 
    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),
	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,
	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),
	dvodb VARCHAR(255),
	registered DATETIME,
	skycell_id VARCHAR(64),
	tess_id VARCHAR(64),
	PRIMARY KEY(diff_id),
	KEY(diff_id),
	KEY(state),
	KEY(skycell_id),
	KEY(tess_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

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

CREATE TABLE diffSkyfile (
	diff_id BIGINT,
	uri VARCHAR(255),
	path_base VARCHAR(255),
	bg DOUBLE,
	bg_stdev DOUBLE,
	stamps_num INT,
	stamps_rms FLOAT,
	sources INT,
	dtime_diff FLOAT,
	hostname VARCHAR(64),
	good_frac FLOAT,
	fault SMALLINT,
	PRIMARY KEY(diff_id),
	KEY(good_frac),
	KEY(fault),
	FOREIGN KEY (diff_id)  REFERENCES  diffRun(diff_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE magicRun (
	magic_id BIGINT AUTO_INCREMENT,
	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)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE magicInputSkyfile (
	magic_id BIGINT,
	diff_id BIGINT,
	node VARCHAR(64),
	PRIMARY KEY(magic_id, diff_id),
	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 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, dvodb VARCHAR(64), filter VARCHAR(64), state VARCHAR(64), workdir VARCHAR(255), label VARCHAR(64), stats VARCHAR(255), region VARCHAR(255), PRIMARY KEY(corr_id), KEY(corr_id)) ENGINE=innodb DEFAULT CHARSET=latin1;
CREATE TABLE flatcorrExp (corr_id BIGINT, chip_id BIGINT, 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 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 pstampRequest (req_id BIGINT AUTO_INCREMENT, ds_id BIGINT, state VARCHAR(64), outFileset VARCHAR(64) UNIQUE, uri VARCHAR(255), resultsFile SMALLINT, PRIMARY KEY(req_id), KEY(req_id)) ENGINE=innodb DEFAULT CHARSET=latin1;
CREATE TABLE pstampJob (job_id BIGINT AUTO_INCREMENT, req_id BIGINT, state VARCHAR(64), jobType VARCHAR(16), result INT, uri VARCHAR(255), 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
