-- How to manually change the database between versions via mySQL commands.
-- 
-- This file is generated manually, and may not be complete.


-- Version 1.1.20 --> 1.1.21:

alter table camProcessedExp drop nastro;
alter table camProcessedExp add (fwhm double, fwhm_range double, n_stars int(11), n_extended int(11), n_cr int(11), n_astrom int(11));
alter table chipProcessedImfile add column (bias double, bias_stdev double, fringe_0 double, fringe_1 double, fringe_2 double, sigma_ra double, sigma_dec double, ap_resid double, ap_resid_stdev double, fwhm double, fwhm_range double, n_stars int(11), n_extended int(11), n_cr int(11), n_astrom int(11));
alter table warpSkyfile add column path_base varchar(255) NULL DEFAULT NULL after uri;
alter table diffSkyfile add column path_base varchar(255) NULL DEFAULT NULL after uri;
alter table stackSumSkyfile add column path_base varchar(255) NULL DEFAULT NULL after uri;


-- Version 1.1.22 --> 1.1.23
-- Adding support for reduction classes (which are used to specify recipes for parts of the pipeline).
# Adding 'reduction' to detRun
# Renaming 'recipe' in {chip,cam}{Pending,Processed}Exp to 'reduction'

alter table detRun add column reduction varchar(64) NULL DEFAULT NULL after exp_type;
alter table chipPendingExp change column recipe reduction varchar(64);
alter table chipProcessedExp change column recipe reduction varchar(64);
alter table camPendingExp change column recipe reduction varchar(64);
alter table camProcessedExp change column recipe reduction varchar(64);

-- Version ??? --> 1.1.29

alter table warpSkyfile add column ignored tinyint after good_frac;

--
-- The following set of changes were applied to update the gpc1 database on 2008/02/07
--

alter table calDB change column catdir dvodb varchar(64);

alter table camProcessedExp change column fwhm fwhm_major float;
alter table camProcessedExp change column fwhm_range fwhm_minor float;

-- In order to keep the column order correct this is split up below
--alter table camProcessedExp add column ( bias float, bias_stdev float, fringe_0 float, fringe_1 float, fringe_2 float, ap_resid float, ap_resid_stdev float, dtime_detrend float, dtime_photom float, dtime_astrom float, hostname varchar(64) );

alter table camProcessedExp add column bias float after bg_mean_stdev;
alter table camProcessedExp add column bias_stdev float after bias;
alter table camProcessedExp add column fringe_0 float after bias_stdev;
alter table camProcessedExp add column fringe_1 float after fringe_0;
alter table camProcessedExp add column fringe_2 float after fringe_1;
alter table camProcessedExp add column ap_resid float after sigma_dec;
alter table camProcessedExp add column ap_resid_stdev float after ap_resid;
alter table camProcessedExp add column dtime_detrend float after fwhm_minor;
alter table camProcessedExp add column dtime_photom float after dtime_detrend;
alter table camProcessedExp add column dtime_astrom float after dtime_photom;
alter table camProcessedExp add column hostname varchar(64) after dtime_astrom;


alter table chipProcessedImfile change column fwhm fwhm_major float;
alter table chipProcessedImfile change column fwhm_range fwhm_minor float;

-- In order to keep the column order correct this is split up below
-- alter table chipProcessedImfile add column ( zp_mean float, zp_stdev float, dtime_detrend float, dtime_photom float, dtime_astrom float, hostname varchar(64) );

alter table chipProcessedImfile add column zp_mean float after ap_resid_stdev;
alter table chipProcessedImfile add column zp_stdev float after zp_mean;
alter table chipProcessedImfile add column dtime_detrend float after fwhm_minor;
alter table chipProcessedImfile add column dtime_photom float after dtime_detrend;
alter table chipProcessedImfile add column dtime_astrom float after dtime_photom;
alter table chipProcessedImfile add column hostname varchar(64) after dtime_astrom;


-- In order to keep the column order correct this is split up below
-- alter table diffSkyfile add column ( dtime_diff float, hostname varchar(64) );
alter table diffSkyfile add column dtime_diff float after bg_stdev;
alter table diffSkyfile add column hostname varchar(64) after dtime_diff;
alter table diffSkyfile change column good_frac good_frac float;

alter table flatcorrRun add column filter varchar(64) after dvodb;

alter table stackSumSkyfile change column good_frac good_frac float;

-- alter table stackSumSkyfile add column ( dtime_stack float, hostname varchar(64) );
alter table stackSumSkyfile add column dtime_stack float after bg_stdev;
alter table stackSumSkyfile add column hostname varchar(64) after dtime_stack;

#alter table warpSkyfile add column ( dtime_warp float, hostname varchar(64) );
alter table warpSkyfile add column dtime_warp float after bg_stdev;
alter table warpSkyfile add column hostname varchar(64) after dtime_warp;

alter table warpSkyfile change column good_frac good_frac float;

alter table newExp add column dvodb varchar(255) after reduction;
alter table newExp add column tess_id varchar(64) after dvodb;
alter table rawExp add column dvodb varchar(255) after reduction;
alter table rawExp add column tess_id varchar(64) after dvodb;
alter table chipRun add column tess_id varchar(64) after dvodb;
alter table camRun add column tess_id varchar(64) after dvodb;
alter table warpRun add column tess_id varchar(64) after dvodb;

alter table camRun add column end_stage varchar(64) after tess_id;
alter table chipRun add column end_stage varchar(64) after tess_id;
alter table warpRun add column end_stage varchar(64) after tess_id;
alter table newExp add column end_stage varchar(64) after tess_id;
alter table rawExp add column end_stage varchar(64) after tess_id;
alter table camRun add key (end_stage);
alter table chipRun add key (end_stage);
alter table warpRun add key (end_stage);
alter table newExp add key (end_stage);
alter table rawExp add key (end_stage);

alter table warpRun add column workdir_state varchar(64) after workdir;
alter table warpRun add key (workdir_state);

alter table pzDoneImfile add column fault smallint(6);

-- end of changes to 1.1.29

alter table newExp add column label varchar(64) after end_stage;

-- 1.1.30

drop table pzDoneExp;
drop table pzDoneImfile;
drop table pzPendingExp;
drop table pzPendingImfile;
alter table flatcorrRun add column region VARCHAR(255) after stats;

-- 1.1.31

ALTER TABLE warpRun ADD COLUMN cam_id BIGINT AFTER warp_id;
ALTER TABLE warpRun ADD KEY(cam_id);
ALTER TABLE warpRun ADD KEY(warp_id, cam_id);
ALTER TABLE warpRun ADD CONSTRAINT FOREIGN KEY(cam_id) REFERENCES
camRun(cam_id);
ALTER TABLE warpRun ADD COLUMN magiced TINYINT AFTER registered;
ALTER TABLE warpSkyCellMap DROP FOREIGN KEY warpSkyCellMap_ibfk_1;
UPDATE warpRun JOIN warpInputExp USING(warp_id) SET warpRun.cam_id =
warpInputExp.cam_id WHERE warpRun.warp_id = warpInputExp.warp_id;
ALTER TABLE warpSkyCellMap ADD CONSTRAINT FOREIGN KEY(warp_id, cam_id)
REFERENCES warpRun(warp_id, cam_id);
DROP TABLE warpInputExp;

-- 1.1.32
ALTER TABLE warpRun ADD COLUMN label VARCHAR(64) AFTER workdir_state;
ALTER TABLE warpRun ADD KEY(label);
CREATE TABLE warpMask (label VARCHAR(64), PRIMARY KEY(label)) ENGINE=innodb;

-- 1.1.33
ALTER TABLE summitExp ADD COLUMN fault smallint(6) NOT NULL AFTER imfiles;
ALTER TABLE summitExp ADD KEY(fault);

-- 1.1.34
ALTER TABLE rawImfile ADD KEY(exp_name);

-- 1.1.35
ALTER TABLE rawImfile ADD INDEX UNQIUE(exp_id, tmp_class_id);

-- 1.1.36
ALTER TABLE warpSkyfile ADD COLUMN (xmin INT, xmax INT, ymin INT, ymax INT);
ALTER TABLE diffSkyfile ADD COLUMN (stamps_num INT, stamps_rms FLOAT, sources INT);
ALTER TABLE rawImfile ADD COLUMN hostname VARCHAR(64) AFTER object;
ALTER TABLE rawExp ADD COLUMN hostname VARCHAR(64) AFTER object;

-- 1.1.37
ALTER TABLE pzDataStore ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER uri;
ALTER TABLE summitExp ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER fault;
ALTER TABLE summitImfile ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER uri;
ALTER TABLE pzDownloadExp ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER state;
ALTER TABLE pzDownloadImfile ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER fault;
ALTER TABLE newExp ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER label;
ALTER TABLE newImfile ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER uri;
ALTER TABLE rawExp ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER fault;
ALTER TABLE rawImfile ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER fault;

-- 1.1.38

-- convert from utf8 -> latin1 for performance/space gains
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE calDB CONVERT TO CHARACTER SET latin1;
ALTER TABLE calRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE camMask CONVERT TO CHARACTER SET latin1;
ALTER TABLE camProcessedExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE camRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE chipMask CONVERT TO CHARACTER SET latin1;
ALTER TABLE chipProcessedImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE chipRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE detInputExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE detNormalizedExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE detNormalizedImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE detNormalizedStatImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE detProcessedExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE detProcessedImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE detRegisteredImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE detResidExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE detResidImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE detRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE detRunSummary CONVERT TO CHARACTER SET latin1;
ALTER TABLE detStackedImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE diffInputSkyfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE diffRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE diffSkyfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE fakeMask CONVERT TO CHARACTER SET latin1;
ALTER TABLE fakeProcessedImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE fakeRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE flatcorrExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE flatcorrRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE guidePendingExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE magicInputSkyfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE magicMask CONVERT TO CHARACTER SET latin1;
ALTER TABLE magicNodeResult CONVERT TO CHARACTER SET latin1;
ALTER TABLE magicRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE magicSkyfileMask CONVERT TO CHARACTER SET latin1;
ALTER TABLE magicTree CONVERT TO CHARACTER SET latin1;
ALTER TABLE newExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE newImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE pstampDataStore CONVERT TO CHARACTER SET latin1;
ALTER TABLE pstampJob CONVERT TO CHARACTER SET latin1;
ALTER TABLE pstampRequest CONVERT TO CHARACTER SET latin1;
ALTER TABLE pzDataStore CONVERT TO CHARACTER SET latin1;
ALTER TABLE pzDownloadExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE pzDownloadImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE rawExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE rawImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE stackInputSkyfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE stackRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE stackSumSkyfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE summitExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE summitImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE warpMask CONVERT TO CHARACTER SET latin1;
ALTER TABLE warpRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE warpSkyCellMap CONVERT TO CHARACTER SET latin1;
ALTER TABLE warpSkyfile CONVERT TO CHARACTER SET latin1;
SET FOREIGN_KEY_CHECKS=1;

-- first use of fake* tables
-- drop and recreate any fake* tables created prior to this point

DROP TABLE IF EXISTS fakeRun;
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;

DROP TABLE IF EXISTS fakeProcessedImfile;
CREATE TABLE fakeProcessedImfile (
    fake_id BIGINT AUTO_INCREMENT,
    exp_id BIGINT(20),
    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;

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

-- re-normalize camProcessedExp table
-- unrelated to other changes
ALTER TABLE camProcessedExp DROP FOREIGN KEY camProcessedExp_ibfk_1;
ALTER TABLE camProcessedExp DROP COLUMN chip_id;
ALTER TABLE camProcessedExp ADD FOREIGN KEY (cam_id) REFERENCES camRun (cam_id);

-- insert fakeRun betwen camRun and warpRun
-- populate fakeRun/fakeProcessedImfile
-- then heal the foreign key references

CREATE TEMPORARY TABLE warpCamMap (
    warp_id BIGINT,
    cam_id  BIGINT,
    PRIMARY KEY(warp_id),
    KEY(cam_id)
) ENGINE=MEMORY;

INSERT INTO warpCamMap SELECT warp_id, cam_id FROM warpRun;

ALTER TABLE warpRun drop FOREIGN KEY warpRun_ibfk_1;
ALTER TABLE warpSkyCellMap DROP FOREIGN KEY warpSkyCellMap_ibfk_1;

ALTER TABLE warpRun change COLUMN cam_id fake_id BIGINT(20) DEFAULT NULL;
ALTER TABLE warpSkyCellMap DROP COLUMN cam_id;

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE warpRun ADD FOREIGN KEY (fake_id) REFERENCES fakeRun (fake_id);
SET FOREIGN_KEY_CHECKS=1;
ALTER TABLE warpSkyCellMap ADD FOREIGN KEY (warp_id) REFERENCES warpRun(warp_id);

INSERT INTO fakeRun
SELECT
    NULL,
    camRun.cam_id,
    'stop',
    workdir,
    label,
    reduction,
    expgroup,
    dvodb, 
    tess_id, 
    end_stage, 
    NULL 
FROM camRun
JOIN warpCamMap
    using(cam_id);

INSERT INTO fakeProcessedImfile
SELECT
    fake_id,
    chipRun.exp_id,
    class_id,
    uri,
    0,
    hostname,
    path_base, 
    fault, 
    NULL
FROM fakeRun
JOIN camRun
    USING(cam_id)
JOIN chipRun
    USING(chip_id)
JOIN chipProcessedImfile
    USING(chip_id);

UPDATE warpRun, fakeRun, warpCamMap
SET warpRun.fake_id = fakeRun.fake_id
WHERE warpRun.warp_id = warpCamMap.warp_id
    AND warpCamMap.cam_id = fakeRun.fake_id;

DROP TABLE warpCamMap;


-- new values for chipRun.state

update chipRun set state = 'new' where state = 'run';
update chipRun set state = 'full' where state = 'stop';

-- from eam_branch_20080806
-- add 'data_state' to detrend tables

alter table detProcessedImfile      add column data_state varchar(64) after path_base;
alter table detProcessedExp         add column data_state varchar(64) after path_base;
alter table detStackedImfile        add column data_state varchar(64) after user_5;
alter table detNormalizedStatImfile add column data_state varchar(64) after norm;
alter table detNormalizedImfile     add column data_state varchar(64) after path_base;
alter table detNormalizedExp 	    add column data_state varchar(64) after path_base;
alter table detResidImfile 	    add column data_state varchar(64) after path_base;
alter table detResidExp 	    add column data_state varchar(64) after path_base;
alter table detRunSummary 	    add column data_state varchar(64) after iteration;
alter table detRegisteredImfile     add column data_state varchar(64) after path_base;

update camRun set state = 'new' where state = 'run';
update camRun set state = 'full' where state = 'stop';

update fakeRun set state = 'new' where state = 'run';
update fakeRun set state = 'full' where state = 'stop';

update warpRun set state = 'new' where state = 'run';
update warpRun set state = 'full' where state = 'stop';

update stackRun set state = 'new' where state = 'run';
update stackRun set state = 'full' where state = 'stop';

update diffRun set state = 'new' where state = 'run';
update diffRun set state = 'full' where state = 'stop';

-- not sure when 'filter' was added to stackRun, but it is needed now:

alter table stackRun add column filter varchar(64) after tess_id;

-- changes to the flatcorr tables : no valid flatcorr tables have been created to date expect my tests
-- this sql just drops and recreates the flatcorrRun with the correct layout

delete from flatcorrExp;
delete from flatcorrRun;

drop table flatcorrExp;
drop table flatcorrRun;

CREATE TABLE flatcorrRun (
        corr_id BIGINT AUTO_INCREMENT,
        dvodb VARCHAR(64),
        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;

CREATE TABLE flatcorrChipLink (
        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 flatcorrCamLink (
        corr_id BIGINT,
        chip_id BIGINT,
        cam_id BIGINT,
        PRIMARY KEY(corr_id, chip_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;

-- I initially defined flatcorrCamLin without the chip_id entry.  this
-- command corrects for that, but should not be needed widely.
-- ALTER TABLE flatcorrCamLink ADD COLUMN chip_id BIGINT AFTER corr_id;
-- alter table flatcorrRun add column hostname varchar(64) after region;
-- alter table flatcorrRun add column fault smallint after hostname;
-- alter table flatcorrRun add column reduction varchar(64) after stats;

-- changes to the chip tables to support cleanup and reprocessing

ALTER TABLE chipProcessedImfile ADD COLUMN data_state VARCHAR(64) AFTER class_id;
UPDATE chipProcessedImfile SET data_state = 'full';

-- I have added 'label' and 'reduction' entries to stack and diff

ALTER TABLE stackRun ADD COLUMN label VARCHAR(64) AFTER workdir;
ALTER TABLE stackRun ADD COLUMN reduction VARCHAR(64) AFTER label;

ALTER TABLE stackSumSkyfile add column data_state varchar(64) after path_base;
UPDATE stackSumSkyfile SET data_state = 'full';

alter table diffRun add column label varchar(64) after workdir;
alter table diffRun add column reduction varchar(64) after label;

ALTER TABLE diffSkyfile add column data_state varchar(64) after path_base;
UPDATE diffSkyfile SET data_state = 'full';

ALTER TABLE warpSkyfile add column data_state varchar(64) after path_base;
UPDATE warpSkyfile SET data_state = 'full';

ALTER TABLE fakeProcessedImfile add column data_state varchar(64) after path_base;
update fakeProcessedImfile set data_state ='full';

-- adding ref_det_id and ref_iter to detRun and detResidImfile

ALTER TABLE detResidImfile add column ref_det_id bigint after iteration;
ALTER TABLE detResidImfile add column ref_iter int after ref_det_id;

ALTER TABLE detRun change column parent ref_det_id bigint;
ALTER TABLE detRun add column ref_iter int after ref_det_id;

-- populate ref_det_id and ref_iter 
UPDATE detResidImfile SET ref_det_id = det_id, ref_iter = iteration;

-- Next we drop the old constraint and add the new one
ALTER TABLE detResidImfile DROP FOREIGN KEY detResidImfile_ibfk_3;
ALTER TABLE detResidImfile add FOREIGN KEY  (ref_det_id, ref_iter)
    REFERENCES  detNormalizedExp(det_id, iteration);

--- Additions for stack/diff QA by PAP.
ALTER TABLE diffSkyfile ADD COLUMN stamps_mean FLOAT AFTER stamps_num;
ALTER TABLE diffSkyfile ADD COLUMN dtime_match FLOAT AFTER dtime_diff;
ALTER TABLE diffSkyfile ADD COLUMN dtime_phot FLOAT AFTER dtime_match;

ALTER TABLE stackSumSkyfile ADD COLUMN dtime_match_mean FLOAT AFTER dtime_stack;
ALTER TABLE stackSumSkyfile ADD COLUMN dtime_match_stdev FLOAT AFTER dtime_match_mean;
ALTER TABLE stackSumSkyfile ADD COLUMN dtime_initial FLOAT AFTER dtime_match_stdev;
ALTER TABLE stackSumSkyfile ADD COLUMN dtime_reject FLOAT AFTER dtime_initial;
ALTER TABLE stackSumSkyfile ADD COLUMN dtime_final FLOAT AFTER dtime_reject;
ALTER TABLE stackSumSkyfile ADD COLUMN dtime_phot FLOAT AFTER dtime_final;
ALTER TABLE stackSumSkyfile ADD COLUMN match_mean FLOAT AFTER dtime_phot;
ALTER TABLE stackSumSkyfile ADD COLUMN match_stdev FLOAT AFTER match_mean;
ALTER TABLE stackSumSkyfile ADD COLUMN match_rms FLOAT AFTER match_stdev;
ALTER TABLE stackSumSkyfile ADD COLUMN stamps_mean FLOAT AFTER match_rms;
ALTER TABLE stackSumSkyfile ADD COLUMN stamps_stdev FLOAT AFTER stamps_mean;
ALTER TABLE stackSumSkyfile ADD COLUMN stamps_min INT AFTER stamps_stdev;
ALTER TABLE stackSumSkyfile ADD COLUMN reject_images INT AFTER stamps_min;
ALTER TABLE stackSumSkyfile ADD COLUMN reject_pix_mean FLOAT AFTER reject_images;
ALTER TABLE stackSumSkyfile ADD COLUMN reject_pix_stdev FLOAT AFTER reject_pix_mean;
ALTER TABLE stackSumSkyfile ADD COLUMN sources INT AFTER reject_pix_stdev;
ALTER TABLE stackSumSkyfile DROP COLUMN data_state;
ALTER TABLE diffSkyfile DROP COLUMN data_state;

-- Adding the new solar-system info (sun & moon data)
ALTER TABLE rawExp CHANGE COLUMN solang sun_angle FLOAT;
ALTER TABLE rawExp ADD COLUMN sun_alt    FLOAT AFTER sun_angle;
ALTER TABLE rawExp ADD COLUMN moon_angle FLOAT AFTER sun_alt;
ALTER TABLE rawExp ADD COLUMN moon_alt   FLOAT AFTER moon_angle;
ALTER TABLE rawExp ADD COLUMN moon_phase FLOAT AFTER moon_alt;

ALTER TABLE rawImfile ADD COLUMN sun_angle  FLOAT AFTER object;
ALTER TABLE rawImfile ADD COLUMN sun_alt    FLOAT AFTER sun_angle;
ALTER TABLE rawImfile ADD COLUMN moon_angle FLOAT AFTER sun_alt;
ALTER TABLE rawImfile ADD COLUMN moon_alt   FLOAT AFTER moon_angle;
ALTER TABLE rawImfile ADD COLUMN moon_phase FLOAT AFTER moon_alt;

CREATE TABLE pstampProject (
        proj_id BIGINT AUTO_INCREMENT,
        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;


-- Reusing astrometry time field since ppImage doesn't do that any more
ALTER TABLE chipProcessedImfile CHANGE COLUMN dtime_astrom dtime_total FLOAT;

-- Deleting fields requiring astrometric matching from chipProcessedImfile
ALTER TABLE chipProcessedImfile DROP COLUMN sigma_ra;
ALTER TABLE chipProcessedImfile DROP COLUMN sigma_dec;
ALTER TABLE chipProcessedImfile DROP COLUMN zp_mean;
ALTER TABLE chipProcessedImfile DROP COLUMN zp_stdev;
ALTER TABLE chipProcessedImfile DROP COLUMN n_astrom;

-- No need to propagate processing times from chip to camera
ALTER TABLE camProcessedExp DROP COLUMN dtime_detrend;
ALTER TABLE camProcessedExp DROP COLUMN dtime_photom;
ALTER TABLE camProcessedExp DROP COLUMN dtime_astrom;

-- Add "dtime_script" to processing results
ALTER TABLE chipProcessedImfile ADD COLUMN dtime_script FLOAT AFTER dtime_total;
ALTER TABLE camProcessedExp ADD COLUMN dtime_script FLOAT AFTER fwhm_minor;
ALTER TABLE fakeProcessedImfile ADD COLUMN dtime_script FLOAT AFTER dtime_fake;
ALTER TABLE warpSkyfile ADD COLUMN dtime_script FLOAT AFTER dtime_warp;
ALTER TABLE stackSumSkyfile ADD COLUMN dtime_script FLOAT AFTER dtime_phot;
ALTER TABLE diffSkyfile ADD COLUMN dtime_script FLOAT AFTER dtime_phot;

* 1.1.40

-- Add moments statistics (M2, M3, M4)

ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2     FLOAT AFTER fwhm_minor;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2_err FLOAT AFTER iq_m2;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2_lq  FLOAT AFTER iq_m2_err;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2_uq  FLOAT AFTER iq_m2_lq;

ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2c     FLOAT AFTER iq_m2_uq;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2c_err FLOAT AFTER iq_m2c;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2c_lq  FLOAT AFTER iq_m2c_err;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2c_uq  FLOAT AFTER iq_m2c_lq;

ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2s     FLOAT AFTER iq_m2c_uq;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2s_err FLOAT AFTER iq_m2s;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2s_lq  FLOAT AFTER iq_m2s_err;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2s_uq  FLOAT AFTER iq_m2s_lq;

ALTER TABLE chipProcessedImfile ADD COLUMN iq_m3     FLOAT AFTER iq_m2s_uq;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m3_err FLOAT AFTER iq_m3;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m3_lq  FLOAT AFTER iq_m3_err;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m3_uq  FLOAT AFTER iq_m3_lq;

ALTER TABLE chipProcessedImfile ADD COLUMN iq_m4     FLOAT AFTER iq_m3_uq;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m4_err FLOAT AFTER iq_m4;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m4_lq  FLOAT AFTER iq_m4_err;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m4_uq  FLOAT AFTER iq_m4_lq;

ALTER TABLE chipProcessedImfile ADD COLUMN n_psfstars INT AFTER n_stars;
ALTER TABLE chipProcessedImfile ADD COLUMN n_iqstars INT AFTER n_psfstars;


ALTER TABLE camProcessedExp ADD COLUMN iq_m2      FLOAT AFTER fwhm_minor;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2_err  FLOAT AFTER iq_m2;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2_lq   FLOAT AFTER iq_m2_err;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2_uq   FLOAT AFTER iq_m2_lq;

ALTER TABLE camProcessedExp ADD COLUMN iq_m2c     FLOAT AFTER iq_m2_uq;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2c_err FLOAT AFTER iq_m2c;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2c_lq  FLOAT AFTER iq_m2c_err;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2c_uq  FLOAT AFTER iq_m2c_lq;

ALTER TABLE camProcessedExp ADD COLUMN iq_m2s     FLOAT AFTER iq_m2c_uq;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2s_err FLOAT AFTER iq_m2s;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2s_lq  FLOAT AFTER iq_m2s_err;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2s_uq  FLOAT AFTER iq_m2s_lq;

ALTER TABLE camProcessedExp ADD COLUMN iq_m3      FLOAT AFTER iq_m2s_uq;
ALTER TABLE camProcessedExp ADD COLUMN iq_m3_err  FLOAT AFTER iq_m3;
ALTER TABLE camProcessedExp ADD COLUMN iq_m3_lq   FLOAT AFTER iq_m3_err;
ALTER TABLE camProcessedExp ADD COLUMN iq_m3_uq   FLOAT AFTER iq_m3_lq;

ALTER TABLE camProcessedExp ADD COLUMN iq_m4      FLOAT AFTER iq_m3_uq;
ALTER TABLE camProcessedExp ADD COLUMN iq_m4_err  FLOAT AFTER iq_m4;
ALTER TABLE camProcessedExp ADD COLUMN iq_m4_lq   FLOAT AFTER iq_m4_err;
ALTER TABLE camProcessedExp ADD COLUMN iq_m4_uq   FLOAT AFTER iq_m4_lq;

ALTER TABLE camProcessedExp ADD COLUMN n_psfstars INT AFTER n_stars;
ALTER TABLE camProcessedExp ADD COLUMN n_iqstars INT AFTER n_psfstars;

* 1.1.41

ALTER TABLE camProcessedExp ADD COLUMN dtime_astrom FLOAT AFTER dtime_script;
ALTER TABLE camProcessedExp ADD COLUMN dtime_addstar FLOAT AFTER dtime_astrom;

* 1.1.42

ALTER TABLE diffSkyfile ADD COLUMN norm FLOAT AFTER stamps_rms;
ALTER TABLE diffSkyfile ADD COLUMN bg_diff FLOAT AFTER norm;
ALTER TABLE diffSkyfile ADD COLUMN kernel_x FLOAT AFTER bg_diff;
ALTER TABLE diffSkyfile ADD COLUMN kernel_y FLOAT AFTER kernel_x;
ALTER TABLE diffSkyfile ADD COLUMN kernel_xx FLOAT AFTER kernel_y;
ALTER TABLE diffSkyfile ADD COLUMN kernel_xy FLOAT AFTER kernel_xx;
ALTER TABLE diffSkyfile ADD COLUMN kernel_yy FLOAT AFTER kernel_xy;

* 1.1.43

ALTER TABLE rawExp ADD COLUMN obs_mode VARCHAR(64) AFTER comment;
ALTER TABLE rawExp ADD COLUMN obs_group VARCHAR(64) AFTER obs_mode;

ALTER TABLE rawImfile ADD COLUMN obs_mode VARCHAR(64) AFTER comment;
ALTER TABLE rawImfile ADD COLUMN obs_group VARCHAR(64) AFTER obs_mode;

* 1.1.44

ALTER TABLE chipProcessedImfile ADD COLUMN fwhm_major_lq FLOAT AFTER fwhm_major;
ALTER TABLE chipProcessedImfile ADD COLUMN fwhm_major_uq FLOAT AFTER fwhm_major_lq;

ALTER TABLE chipProcessedImfile ADD COLUMN fwhm_minor_lq FLOAT AFTER fwhm_minor;
ALTER TABLE chipProcessedImfile ADD COLUMN fwhm_minor_uq FLOAT AFTER fwhm_minor_lq;

ALTER TABLE chipProcessedImfile ADD COLUMN iq_fwhm_major     FLOAT AFTER fwhm_minor_uq;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_fwhm_major_err FLOAT AFTER iq_fwhm_major;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_fwhm_minor     FLOAT AFTER iq_fwhm_major_err;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_fwhm_minor_err FLOAT AFTER iq_fwhm_minor;

ALTER TABLE camProcessedExp ADD COLUMN fwhm_major_lq 	 FLOAT AFTER fwhm_major;
ALTER TABLE camProcessedExp ADD COLUMN fwhm_major_uq 	 FLOAT AFTER fwhm_major_lq;

ALTER TABLE camProcessedExp ADD COLUMN fwhm_minor_lq 	 FLOAT AFTER fwhm_minor;
ALTER TABLE camProcessedExp ADD COLUMN fwhm_minor_uq 	 FLOAT AFTER fwhm_minor_lq;

ALTER TABLE camProcessedExp ADD COLUMN iq_fwhm_major     FLOAT AFTER fwhm_minor_uq;
ALTER TABLE camProcessedExp ADD COLUMN iq_fwhm_major_err FLOAT AFTER iq_fwhm_major;
ALTER TABLE camProcessedExp ADD COLUMN iq_fwhm_minor     FLOAT AFTER iq_fwhm_major_err;
ALTER TABLE camProcessedExp ADD COLUMN iq_fwhm_minor_err FLOAT AFTER iq_fwhm_minor;

ALTER TABLE flatcorrRun ADD COLUMN camera VARCHAR(64) AFTER dvodb;
ALTER TABLE flatcorrRun ADD COLUMN telescope VARCHAR(64) AFTER camera;
ALTER TABLE flatcorrRun ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER telescope;

* 1.1.45

ALTER TABLE camProcessedExp CHANGE COLUMN zp_mean  zpt_obs   FLOAT;
ALTER TABLE camProcessedExp CHANGE COLUMN zp_stdev zpt_stdev FLOAT;
ALTER TABLE camProcessedExp ADD COLUMN    zpt_lq	     FLOAT AFTER zpt_stdev;
ALTER TABLE camProcessedExp ADD COLUMN    zpt_uq 	     FLOAT AFTER zpt_lq;

* 1.1.46

ALTER TABLE pzDownloadImfile ADD COLUMN hostname varchar(64) after epoch;
ALTER TABLE flatcorrRun ADD COLUMN det_type varchar(64) after corr_id;
ALTER TABLE flatcorrChipLink ADD COLUMN include tinyint after chip_id;
ALTER TABLE flatcorrCamLink ADD COLUMN include tinyint after cam_id;

ALTER TABLE rawImfile ADD COLUMN ignored TINYINT DEFAULT 0 AFTER moon_phase;

-- add new tables to control magic de-streaking
CREATE TABLE magicDSRun (
        magic_ds_id BIGINT AUTO_INCREMENT,
        magic_id BIGINT,
        state VARCHAR(64),
        stage VARCHAR(64),
        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;

-- add to the run table to make the queries far less expensive
alter table magicDSRun add column stage_id BIGINT after stage;
alter table magicDSRun add column cam_id BIGINT after stage_id;

--  Version: 1.1.47

-- WARNING WARNING WARNING
-- Updating a database which has populated diffRun's requires editing
-- the contents of the tables in a way that cannot be expresed here.
-- The script tools/collapsdiffinputs.pl does the appropriate edits
-- and should be run at the spot indicated below

-- fix order of workdir to match pxadmin_create_tables.sql
ALTER TABLE diffRun CHANGE COLUMN workdir workdir VARCHAR(255) AFTER state;

-- add exposure id to diffRun
ALTER TABLE diffRun ADD COLUMN exp_id BIGINT;
ALTER TABLE diffRun ADD FOREIGN KEY (exp_id) REFERENCES rawExp(exp_id);
ALTER TABLE diffRun DROP COLUMN skycell_id;

-- add unique ids for each image and flags that indicate whether the image has been
-- magic destreaked
ALTER TABLE diffSkyfile ADD COLUMN skycell_id VARCHAR(64) AFTER diff_id;
ALTER TABLE diffSkyfile DROP PRIMARY KEY, ADD PRIMARY KEY (diff_id, skycell_id);
ALTER TABLE diffSkyfile ADD COLUMN diff_image_id BIGINT NOT NULL AUTO_INCREMENT, ADD KEY(diff_image_id);
ALTER TABLE diffSkyfile ADD COLUMN magicked TINYINT;

-- drop existing foreign key constraints
ALTER TABLE diffInputSkyfile drop FOREIGN KEY diffInputSkyfile_ibfk_1;
ALTER TABLE diffInputSkyfile drop FOREIGN KEY diffInputSkyfile_ibfk_2;
ALTER TABLE diffInputSkyfile drop FOREIGN KEY diffInputSkyfile_ibfk_3;

ALTER TABLE diffInputSkyfile DROP COLUMN kind;
ALTER TABLE diffInputSkyfile CHANGE COLUMN skycell_id skycell_id VARCHAR(64) AFTER diff_id;


ALTER TABLE diffInputSkyfile CHANGE COLUMN warp_id warp1 BIGINT;
ALTER TABLE diffInputSkyfile ADD COLUMN stack1 BIGINT AFTER warp1, ADD KEY(stack1);
ALTER TABLE diffInputSkyfile ADD COLUMN warp2 BIGINT AFTER stack1, ADD KEY(warp2);
ALTER TABLE diffInputSkyfile CHANGE COLUMN stack_id stack2 BIGINT AFTER warp2;

-- WARNING WARNING
-- If you have existing diff runs they must be converted

-- run the script collapse_diffinputs.pl here.

--        collapse_diffinputs.pl dbname dbuser dbpass dbhost

DELETE FROM diffInputSkyfile where template = 1;

ALTER TABLE diffInputSkyfile DROP PRIMARY KEY;
ALTER TABLE diffInputSkyfile ADD PRIMARY KEY (diff_id, skycell_id);
ALTER TABLE diffInputSkyfile DROP COLUMN template;

ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id);

ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (warp1, skycell_id, tess_id) REFERENCES warpSkyfile(warp_id, skycell_id, tess_id);
ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (warp2, skycell_id, tess_id) REFERENCES warpSkyfile(warp_id, skycell_id, tess_id);

ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (stack1) REFERENCES stackRun(stack_id);
ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (stack2) REFERENCES stackRun(stack_id); 


-- add unique ids for each image and flags that indicate whether the image has been
-- magic destreaked
ALTER TABLE warpRun DROP COLUMN magiced;

ALTER TABLE warpSkyfile ADD COLUMN warp_image_id BIGINT NOT NULL AUTO_INCREMENT, ADD KEY(warp_image_id);
ALTER TABLE warpSkyfile ADD COLUMN magicked TINYINT;

ALTER TABLE chipProcessedImfile ADD COLUMN chip_image_id BIGINT NOT NULL AUTO_INCREMENT, ADD KEY(chip_image_id);
ALTER TABLE chipProcessedImfile ADD COLUMN magicked TINYINT;

ALTER TABLE rawImfile ADD COLUMN raw_image_id BIGINT NOT NULL AUTO_INCREMENT, ADD KEY(raw_image_id);
ALTER TABLE rawImfile ADD COLUMN magicked TINYINT;

-- magic adaptations to diff reorginization
ALTER TABLE magicRun ADD COLUMN diff_id BIGINT AFTER exp_id;
ALTER TABLE magicRun ADD CONSTRAINT FOREIGN KEY(diff_id) REFERENCES diffRun(diff_id);
ALTER TABLE magicRun ADD CONSTRAINT FOREIGN KEY(exp_id) REFERENCES rawExp(exp_id);
ALTER TABLE magicInputSkyfile DROP PRIMARY KEY, ADD PRIMARY KEY(magic_id, diff_id, node);

-- Version: 1.1.48

ALTER TABLE chipProcessedImfile DROP COLUMN chip_image_id;
ALTER TABLE diffSkyfile DROP COLUMN diff_image_id;
ALTER TABLE warpSkyfile DROP COLUMN warp_image_id;

ALTER TABLE diffInputSkyfile ADD COLUMN diff_skyfile_id BIGINT AUTO_INCREMENT, ADD KEY(diff_id, skycell_id), ADD KEY(diff_skyfile_id);

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 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;

INSERT INTO chipImfile
   SELECT chip_id, class_id, 0
   FROM rawImfile JOIN chipRun USING(exp_id)
   LEFT JOIN chipImfile USING(chip_id, class_id)
   WHERE chipImfile.class_id IS NULL;

INSERT INTO warpImfile 
    SELECT DISTINCT warp_id, skycell_id, 0 
    FROM warpSkyCellMap LEFT JOIN warpImfile USING(warp_id, skycell_id)
    WHERE warpImfile.skycell_id IS NULL;

-- Version: 1.1.49 (change detrend sequence)

show create table detResidImfile;
alter table detResidImfile drop foreign key detResidImfile_ibfk_3;


-- Version: 1.1.50  magic and distribution changes

ALTER TABLE magicDSRun ADD COLUMN label VARCHAR(64) after cam_id;
ALTER TABLE magicDSRun ADD KEY(label);

-- replace null values for magicked flags with zero
UPDATE rawImfile SET magicked = 0 WHERE magicked IS NULL;
UPDATE chipProcessedImfile SET magicked = 0 WHERE magicked IS NULL;
UPDATE warpSkyfile SET magicked = 0 WHERE magicked IS NULL;
UPDATE diffSkyfile SET magicked = 0 WHERE magicked IS NULL;

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,
    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;


-- change state fields to match the rest of the system
UPDATE magicRun SET state = 'new' WHERE state = 'run';
UPDATE magicRun SET state = 'full' WHERE state = 'stop';
UPDATE magicDSRun SET state = 'new' WHERE state = 'run';
UPDATE magicDSRun SET state = 'full' WHERE state = 'stop';

ALTER TABLE rawExp ADD COLUMN magicked TINYINT;
ALTER TABLE chipRun ADD COLUMN magicked TINYINT;
ALTER TABLE warpRun ADD COLUMN magicked TINYINT;
ALTER TABLE diffRun ADD COLUMN magicked TINYINT;

update rawExp  SET magicked = 0;
update chipRun SET magicked = 0;
update warpRun SET magicked = 0;
update diffRun SET magicked = 0;

-- The following only applies to the gpc1 database (and recent copies)
update chipRun SET magicked = 1 where chip_id = 11955;


-- Version 1.1.51

-- Distribution updates
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 rcDSProduct (
    prod_id     BIGINT AUTO_INCREMENT,
    name        VARCHAR(64),
    dbname      VARCHAR(64),
    dbhost      VARCHAR(64),
    prod_root   VARCHAR(255),
    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,
    prod_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),
    FOREIGN KEY(prod_id) REFERENCES rcDSProduct(prod_id)
)  ENGINE=innodb DEFAULT CHARSET=latin1;

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

CREATE TABLE rcRun (
    rc_id       BIGINT AUTO_INCREMENT,
    fs_id       BIGINT,
    dest_id     BIGINT,
    state       VARCHAR(64),
    registered  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    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;

ALTER TABLE distRun ADD COLUMN no_magic TINYINT AFTER clean;


-- Adding data quality flags
DROP TABLE guidePendingExp;
ALTER TABLE diffSkyfile ADD COLUMN quality SMALLINT NOT NULL DEFAULT 0 AFTER fault;
ALTER TABLE stackSumSkyfile ADD COLUMN quality SMALLINT NOT NULL DEFAULT 0 AFTER fault;
ALTER TABLE warpSkyfile ADD COLUMN quality SMALLINT NOT NULL DEFAULT 0 AFTER fault;
ALTER TABLE camProcessedExp ADD COLUMN quality SMALLINT NOT NULL DEFAULT 0 AFTER fault;
ALTER TABLE chipProcessedImfile ADD COLUMN quality SMALLINT NOT NULL DEFAULT 0 AFTER fault;
ALTER TABLE rawImfile ADD COLUMN quality SMALLINT NOT NULL DEFAULT 0 AFTER fault;


ALTER TABLE diffSkyfile ADD KEY(quality);
ALTER TABLE stackSumSkyfile ADD KEY(quality);
ALTER TABLE warpSkyfile ADD KEY(quality);
ALTER TABLE camProcessedExp ADD KEY(quality);
ALTER TABLE chipProcessedImfile ADD KEY(quality);
ALTER TABLE rawImfile ADD KEY(quality);

UPDATE TABLE warpSkyfile SET quality = 8007 WHERE ignored != 0;
ALTER TABLE warpSkyfile DROP COLUMN ignored;

-- Version 1.1.52

-- drop unneeded column and the associated constraint
ALTER TABLE rcInterest DROP FOREIGN KEY rcInterest_ibfk_3;
ALTER TABLE rcInterest DROP COLUMN prod_id;

ALTER TABLE rcDSProduct DROP column prod_root;

-- just re-create these two tables
DROP TABLE rcRun;
DROP TABLE rcDSFileset;

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)
)  ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE rcRun (
    rc_id       BIGINT AUTO_INCREMENT,
    fs_id       BIGINT,
    dest_id     BIGINT,
    state       VARCHAR(64),
    registered  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    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;


ALTER TABLE diffSkyfile ADD COLUMN data_state VARCHAR(64) AFTER path_base;
UPDATE diffSkyfile SET data_state = 'full' WHERE data_state IS NULL;


-- 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
    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
    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
    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;

ALTER TABLE rcRun ADD COLUMN fault SMALLINT DEFAULT 0;
ALTER TABLE rcRun ADD COLUMN status_fs VARCHAR(64) AFTER state;

ALTER TABLE diffSkyfile ADD COLUMN deconv_max FLOAT AFTER kernel_yy;

-- This key makes it faster to find the ccd_temp for XY24
ALTER TABLE rawImfile ADD KEY(dateobs);

ALTER TABLE receiveSource ADD COLUMN status_product VARCHAR(64);
ALTER TABLE receiveSource ADD COLUMN ds_dbname VARCHAR(64);
ALTER TABLE receiveSource ADD COLUMN ds_dbhost VARCHAR(64);

ALTER TABLE receiveFileset ADD COLUMN state VARCHAR(64);
ALTER TABLE receiveFileset ADD COLUMN dbinfo_uri VARCHAR(255);
ALTER TABLE receiveFileset ADD COLUMN fault SMALLINT NOT NULL DEFAULT 0;

ALTER TABLE receiveFile ADD COLUMN bytes BIGINT;
ALTER TABLE receiveFile ADD COLUMN md5sum VARCHAR(255);
ALTER TABLE receiveFile ADD COLUMN file_type VARCHAR(64);
ALTER TABLE receiveFile ADD COLUMN component VARCHAR(64);

ALTER TABLE receiveFileset CHANGE COLUMN dbinfo_uri dbinfo VARCHAR(255);
ALTER TABLE receiveFileset ADD COLUMN dirinfo VARCHAR(255) AFTER state;

-- Playing with the diffs for warp-warp
ALTER TABLE diffRun ADD COLUMN bothways TINYINT DEFAULT 0 AFTER tess_id;
UPDATE diffRun SET bothways = 1 WHERE reduction = 'WARPWARP';
ALTER TABLE diffRun DROP FOREIGN KEY diffRun_ibfk_1;
ALTER TABLE diffRun DROP COLUMN exp_id;
ALTER TABLE diffRun ADD KEY(label);
ALTER TABLE diffRun ADD COLUMN exposure TINYINT DEFAULT 0 AFTER bothways;
UPDATE diffRun, diffInputSkyfile SET diffRun.exposure = 1 WHERE diffRun.diff_id = diffInputSkyfile.diff_id and diffInputSkyfile.warp1 IS NOT NULL;
ALTER TABLE stackRun ADD KEY(label);

ALTER TABLE magicRun ADD COLUMN inverse TINYINT NOT NULL DEFAULT 0 AFTER diff_id;
ALTER TABLE diffSkyfile DROP COLUMN uri;

DROP TABLE IF EXISTS magicSkyfileMask;
ALTER TABLE magicInputSkyfile DROP FOREIGN KEY magicInputSkyfile_ibfk_2;
ALTER TABLE magicInputSkyfile DROP KEY diff_id;
ALTER TABLE magicInputSkyfile DROP PRIMARY KEY, ADD PRIMARY KEY(magic_id,node);
ALTER TABLE magicInputSkyfile DROP COLUMN diff_id;
ALTER TABLE magicNodeResult CHANGE COLUMN uri path_base VARCHAR(255);

-- Version 1.1.53

ALTER TABLE warpRun ADD COLUMN reduction VARCHAR(64) AFTER tess_id;

-- Tables to support publishing of detections to a Science Client
-- Clients to which we send stuff
CREATE TABLE publishClient (
    client_id BIGINT AUTO_INCREMENT, -- unique identifier
    product VARCHAR(64),             -- product name
    stage VARCHAR(64) NOT NULL, -- stage of interest (chip, camera, diff, etc.)
    workdir VARCHAR(255) NOT NULL, -- working directory
    comment VARCHAR(255),            -- for human memory
    PRIMARY KEY(client_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;
-- Publishing a set of data (e.g., a specific diffRun)
CREATE TABLE publishRun (
    pub_id BIGINT AUTO_INCREMENT, -- unique identifier
    client_id BIGINT NOT NULL,  -- link to publishClient
    stage_id BIGINT NOT NULL,   -- link to various stage tables
    label VARCHAR(64),          -- label for run
    state VARCHAR(64),          -- state of run (new, full, etc.)
    PRIMARY KEY(pub_id),
    KEY(client_id),
    KEY(stage_id),
    KEY(label),
    KEY(state),
    FOREIGN KEY(client_id) REFERENCES publishClient(client_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;
-- Publishing a file within a set
CREATE TABLE publishDone (
    pub_id BIGINT AUTO_INCREMENT, -- link to publishRun
    path_base VARCHAR(255),     -- base path of output
    fault SMALLINT NOT NULL DEFAULT 0, -- Fault code
    PRIMARY KEY(pub_id),
    KEY(fault),
    FOREIGN KEY(pub_id) REFERENCES publishRun(pub_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

-- Version 1.1.54

-- changed magicked from a flag to a BIGINT
-- The value is the magic_ds_id of the magicDSRun that destreaked the file/run

ALTER TABLE magicDSFile ADD COLUMN data_state VARCHAR(64);
ALTER TABLE magicDSFile ADD KEY(data_state);
UPDATE magicDSFile SET data_state = 'full' WHERE data_state IS NULL;

ALTER TABLE rawImfile CHANGE COLUMN magicked  magicked BIGINT;
ALTER TABLE rawExp CHANGE COLUMN magicked  magicked BIGINT;

ALTER TABLE chipProcessedImfile CHANGE COLUMN magicked  magicked BIGINT;
ALTER TABLE chipRun CHANGE COLUMN magicked  magicked BIGINT;

-- so far only chipRuns have been magicked in product
UPDATE chipProcessedImfile JOIN magicDSRun ON stage = 'chip' AND chip_id = stage_id
    SET chipProcessedImfile.magicked = magic_ds_id 
    WHERE magicDSRun.re_place
    AND chipProcessedImfile.magicked > 0;

UPDATE chipRun JOIN magicDSRun ON stage = 'chip' AND chip_id = stage_id
    SET chipRun.magicked = magic_ds_id 
    WHERE magicDSRun.re_place
    AND chipRun.magicked > 0;

ALTER TABLE warpSkyfile CHANGE COLUMN magicked  magicked BIGINT;
ALTER TABLE warpRun CHANGE COLUMN magicked  magicked BIGINT;

ALTER TABLE diffSkyfile CHANGE COLUMN magicked  magicked BIGINT;
ALTER TABLE diffRun CHANGE COLUMN magicked  magicked BIGINT;

ALTER TABLE camRun ADD COLUMN magicked BIGINT;

ALTER TABLE distTarget CHANGE COLUMN obs_mode label VARCHAR(64);
ALTER TABLE distTarget ADD COLUMN filter VARCHAR(64) AFTER label;
ALTER TABLE distTarget DROP KEY clean;
ALTER TABLE distTarget ADD CONSTRAINT UNIQUE(label, filter, stage, clean);
