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,
    magicked TINYINT,
    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),
    quality SMALLINT NOT NULL DEFAULT 0,
    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),
    KEY(quality),
    KEY(dateobs),
    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 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),
    magicked TINYINT,
    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 chipImfile (
    chip_id BIGINT,
    class_id VARCHAR(64),
    chip_imfile_id BIGINT AUTO_INCREMENT,
    PRIMARY KEY(chip_id, class_id),
    KEY(chip_imfile_id),
    FOREIGN KEY(chip_id) REFERENCES chipRun(chip_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),
    quality SMALLINT NOT NULL DEFAULT 0,
    fault SMALLINT NOT NULL,
    magicked BIGINT,
    PRIMARY KEY(chip_id, exp_id, class_id),
    KEY(data_state),
    KEY(fault),
    KEY(quality),
    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),
    quality SMALLINT NOT NULL DEFAULT 0,
    fault SMALLINT NOT NULL,
    PRIMARY KEY(cam_id),
    KEY(fault),
    KEY(quality),
    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,
    PRIMARY KEY(det_id),
    KEY(det_id),
    KEY(iteration),
    KEY(det_type),
    KEY(mode),
    KEY(state),
    KEY(label),
    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)
) 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,
    magicked TINYINT,
    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 warpImfile (
    warp_id BIGINT,
    skycell_id VARCHAR(64),
    warp_skyfile_id BIGINT AUTO_INCREMENT,
    PRIMARY KEY(warp_id, skycell_id),
    KEY(warp_skyfile_id),
    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,
    quality SMALLINT NOT NULL DEFAULT 0,
    fault SMALLINT,
    magicked TINYINT,
    PRIMARY KEY(warp_id, skycell_id, tess_id),
    KEY(good_frac),
    KEY(fault),
    KEY(quality),
    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),
        KEY(label)
) 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,
        quality SMALLINT NOT NULL DEFAULT 0,
        fault SMALLINT,
        PRIMARY KEY(stack_id),
        KEY(dtime_stack),
        KEY(good_frac),
        KEY(fault),
        KEY(quality),
        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),
        bothways TINYINT DEFAULT 0,
        exposure TINYINT DEFAULT 0,
        magicked TINYINT,
        PRIMARY KEY(diff_id),
        KEY(diff_id),
        KEY(state),
        KEY(tess_id),
        KEY(label)
) 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),
        diff_skyfile_id BIGINT AUTO_INCREMENT,
        KEY(diff_id, skycell_id),
        KEY(diff_skyfile_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),
        path_base VARCHAR(255),
        data_state VARCHAR(64),
        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,
        deconv_max FLOAT,
        sources INT,
        dtime_diff FLOAT,
        dtime_match FLOAT,
        dtime_phot FLOAT,
        dtime_script FLOAT,
        hostname VARCHAR(64),
        good_frac FLOAT,
        quality SMALLINT NOT NULL DEFAULT 0,
        fault SMALLINT,
        magicked TINYINT,
        PRIMARY KEY(diff_id, skycell_id),
        KEY(good_frac),
        KEY(fault),
        KEY(quality),
        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,
        inverse TINYINT NOT NULL DEFAULT 0,
        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),
        path_base 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,
        label VARCHAR(64),
        outroot VARCHAR(255),
        recoveryroot VARCHAR(255),
        re_place TINYINT,
        remove TINYINT,
        PRIMARY KEY(magic_ds_id),
        KEY(magic_ds_id),
        KEY(state),
        KEY(magic_id),
        KEY(label),
        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;

CREATE TABLE distTarget (
    target_id   BIGINT AUTO_INCREMENT,
    obs_mode    VARCHAR(64),
    stage       VARCHAR(64),
    clean       TINYINT,
    state       VARCHAR(64),
    comment     VARCHAR(255),
    PRIMARY KEY(target_id)
)  ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE distRun (
    dist_id     BIGINT AUTO_INCREMENT,
    target_id   BIGINT,
    stage       VARCHAR(64),
    stage_id    BIGINT,
    label       VARCHAR(64),
    outroot     VARCHAR(255),
    clean       TINYINT,
    no_magic    TINYINT,
    state       VARCHAR(64),
    time_stamp  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fault       SMALLINT,
    PRIMARY KEY(dist_id),
    KEY(state),
    KEY(label),
    FOREIGN KEY(target_id) REFERENCES distTarget(target_id)
)  ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE distComponent (
    dist_id     BIGINT,
    component   VARCHAR(64),
    bytes       INT,
    md5sum      VARCHAR(32),
    state       VARCHAR(64),
    name        VARCHAR(255),
    fault       SMALLINT,
    PRIMARY KEY(dist_id, component),
    KEY(state),
    FOREIGN KEY(dist_id) REFERENCES distRun(dist_id)
)  ENGINE=innodb DEFAULT CHARSET=latin1;


CREATE TABLE rcDSProduct (
    prod_id     BIGINT AUTO_INCREMENT,
    name        VARCHAR(64),
    dbname      VARCHAR(64),
    dbhost      VARCHAR(64),
    PRIMARY KEY(prod_id)
)  ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE rcDestination (
    dest_id     BIGINT AUTO_INCREMENT,
    prod_id     BIGINT,
    name        VARCHAR(64),
    status_uri  VARCHAR(255),
    comment     VARCHAR(255),
    last_fileset VARCHAR(255),
    state       VARCHAR(64),
    PRIMARY KEY(dest_id),
    FOREIGN KEY(prod_id) REFERENCES rcDSProduct(prod_id)
)  ENGINE=innodb DEFAULT CHARSET=latin1;


CREATE TABLE rcInterest (
    int_id      BIGINT AUTO_INCREMENT,
    dest_id     BIGINT,
    target_id   BIGINT,
    state       VARCHAR(64),
    PRIMARY KEY(int_id),
    FOREIGN KEY(dest_id) REFERENCES rcDestination(dest_id),
    FOREIGN KEY(target_id) REFERENCES distTarget(target_id)
)  ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE rcDSFileset (
    fs_id       BIGINT AUTO_INCREMENT,
    dist_id     BIGINT,
    prod_id     BIGINT,
    name        VARCHAR(255),
    state       VARCHAR(64),
    registered  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fault       SMALLINT DEFAULT 0,
    PRIMARY KEY(dist_id, prod_id),
    KEY(fs_id),
    FOREIGN KEY(dist_id) REFERENCES distRun(dist_id),
    FOREIGN KEY(prod_id) REFERENCES rcDSProduct(prod_id)
--    KEY(dist_id, prod_id)
)  ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE rcRun (
    rc_id       BIGINT AUTO_INCREMENT,
    fs_id       BIGINT,
    dest_id     BIGINT,
    state       VARCHAR(64),
    status_fs_name   VARCHAR(64),
    registered  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fault       SMALLINT DEFAULT 0,
    PRIMARY KEY(rc_id),
    FOREIGN KEY(fs_id) REFERENCES rcDSFileset(fs_id),
    FOREIGN KEY(dest_id) REFERENCES rcDestination(dest_id)
)  ENGINE=innodb DEFAULT CHARSET=latin1;

-- Sources from which to receive files
CREATE TABLE receiveSource (
    source_id BIGINT AUTO_INCREMENT, -- unique identifier
    source VARCHAR(128) NOT NULL, -- source URI
    product VARCHAR(64) NOT NULL, -- product of interest
    workdir VARCHAR(255) NOT NULL, -- where to extract
    comment VARCHAR(255),       -- for human memory
    fileset_last VARCHAR(128),  -- last fileset seen
    status_product VARCHAR(64), -- status data store product
    ds_dbname VARCHAR(64),      -- status data store's database name
    ds_dbhost VARCHAR(64),      -- status data store's host name
    PRIMARY KEY(source_id),
    KEY(source),
    KEY(product),
    KEY(comment)
) ENGINE=innodb DEFAULT CHARSET=latin1;

-- Filesets to receive
CREATE TABLE receiveFileset (
    fileset_id BIGINT AUTO_INCREMENT, -- unique identifier
    source_id BIGINT NOT NULL,  -- link to receiveSource
    fileset VARCHAR(128) NOT NULL, -- fileset to receive
    state VARCHAR(64), -- new or full
    dirinfo VARCHAR(255), -- uri for directory info file for this run
    dbinfo VARCHAR(255), -- uri for database dump file for this run
    fault SMALLINT NOT NULL DEFAULT 0, -- Fault code
    PRIMARY KEY(fileset_id),
    KEY(source_id),
    CONSTRAINT UNIQUE(source_id, fileset),
    FOREIGN KEY(source_id) REFERENCES receiveSource(source_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

-- Files to receive
CREATE TABLE receiveFile (
    file_id BIGINT AUTO_INCREMENT, -- unique identifier
    fileset_id BIGINT NOT NULL,  -- link to receiveFileset
    file VARCHAR(128) NOT NULL, -- file to receive
    bytes BIGINT,
    md5sum VARCHAR(255),
    file_type VARCHAR(64),
    component VARCHAR(64),
    PRIMARY KEY(file_id),
    KEY(fileset_id),
    CONSTRAINT UNIQUE(fileset_id, file),
    FOREIGN KEY(fileset_id) REFERENCES receiveFileset(fileset_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

-- Result of receiving files
CREATE TABLE receiveResult (
    file_id BIGINT AUTO_INCREMENT, -- link to receiveFile
    dtime_copy FLOAT,           -- Time to copy
    dtime_extract FLOAT,        -- Time to extract
    fault SMALLINT NOT NULL DEFAULT 0, -- Fault code
    PRIMARY KEY(file_id),
    KEY(fault),
    FOREIGN KEY(file_id) REFERENCES receiveFile(file_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;


-- This comment line is here to avoid empty query error.
-- Another way to avoid that problem is to omit the semicolon above but I think that is untidy.
