CREATE TABLE lowz_spec_2 (specind int, ra_j2000 float, dec_j2000 float, survey varchar,
z float, z_err float, z_q int,
sdss_mjd int, sdss_plate int, sdss_fiberID int, 
twodf_seqnum varchar, sixdf_specid int, 
uzc_zname varchar, cfa_RFN varchar,
hectospec_date varchar, hectospec_dataset varchar, hectospec_spec varchar, 
lamost_obsid int, lamost_planid varchar, lamost_obsdate varchar, lamost_lmjd int, lamost_spid int, lamost_fiberid int, lega_c_spectid varchar, 
deep2_objno int, deep3_objno int, wigglez_specfile varchar, gama_specid varchar, twodflens_target int, fast_date varchar, fast_dataset varchar, fast_spec varchar);

CREATE TABLE highz_spec_2 (specind int, ra_j2000 float, dec_j2000 float, survey varchar,
z float, z_err float, z_q int,
sdss_mjd int, sdss_plate int, sdss_fiberID int, 
twodf_seqnum varchar, sixdf_specid int, 
uzc_zname varchar, cfa_RFN varchar,
hectospec_date varchar, hectospec_dataset varchar, hectospec_spec varchar, 
lamost_obsid int, lamost_planid varchar, lamost_obsdate varchar, lamost_lmjd int, lamost_spid int,
lamost_fiberid int, lega_c_spectid varchar, 
deep2_objno int, deep3_objno int, wigglez_specfile varchar, gama_specid varchar, twodflens_target int, fast_date varchar, fast_dataset varchar, fast_spec varchar);

\COPY highz_spec_2 FROM /data1/rcsed_v2/postgres/speclist_highz_correctsdss.csv WITH csv header;
\COPY lowz_spec_2 FROM /data1/rcsed_v2/postgres/speclist_lowz_correctsdss.csv WITH csv header;

ALTER TABLE lowz_spec_2 ADD COLUMN crd2000 spoint;
UPDATE lowz_spec_2 SET crd2000=spoint(ra_j2000*pi()/180.0,dec_j2000*pi()/180.0);
VACUUM FULL lowz_spec_2;        
CREATE INDEX s_index_2 ON lowz_spec_2 using gist (crd2000);
VACUUM ANALYZE lowz_spec_2; 
ALTER TABLE lowz_spec_2 ADD COLUMN true_filter int;
UPDATE lowz_spec_2 SET true_filter=1 WHERE ((z_q is null) OR (z_q >= 3 AND 
(survey='2dF' OR survey='6dF' OR survey='gama' OR survey='2dFLenS')));    

SELECT a.*, b.pgc, b.ra, b.dec, a.crd2000 <-> b.crd2000 AS dist, b.z AS redshift, b.objtype 
INTO TABLE lowz_forspectable_2 FROM lowz_spec_2 AS a, hlgal_z_pa AS b WHERE (b.objtype='G' OR b.objtype='g' OR b.objtype='M' OR b.objtype='M2' OR b.objtype='M3' OR 
b.objtype='MG' OR b.objtype='MC') AND a.crd2000 @ b.gal AND ABS(a.z-b.z)<2*GREATEST(ABS(a.z_err), ABS(b.z_err), 0.0005);

DELETE FROM lowz_forspectable_2 T1
    USING   lowz_forspectable_2 T2
WHERE   T1.dist > T2.dist  
    AND T1.specind  = T2.specind; 
    
SELECT * INTO full_spec_2 FROM
(SELECT specind, survey, ra_j2000, dec_j2000, z, z_err, z_q, sdss_mjd, sdss_plate, sdss_fiberid, 
twodf_seqnum, sixdf_specid, uzc_zname, cfa_rfn, hectospec_date, hectospec_dataset, hectospec_spec, 
lamost_obsid, lamost_planid, lamost_obsdate, lamost_lmjd, lamost_spid,
lamost_fiberid, lega_c_spectid, deep2_objno, deep3_objno, wigglez_specfile, 
gama_specid, twodflens_target, fast_date, fast_dataset, fast_spec  FROM highz_spec_2 UNION SELECT (row_number() OVER(order by specind))+6000000, 
survey, ra_j2000, dec_j2000, z, z_err, z_q, sdss_mjd, sdss_plate, sdss_fiberid, twodf_seqnum, sixdf_specid, uzc_zname, cfa_rfn, hectospec_date, 
hectospec_dataset, hectospec_spec, lamost_obsid, lamost_planid, lamost_obsdate, lamost_lmjd, lamost_spid,
lamost_fiberid, lega_c_spectid, deep2_objno, 
deep3_objno, wigglez_specfile, gama_specid, twodflens_target, fast_date, fast_dataset, fast_spec FROM lowz_forspectable_2) AS tmp;    

CREATE INDEX ind ON full_spec_2(specind);

SELECT a.*, full_spec_spoint.r2id_spec, full_spec_spoint.r2id_obj INTO full_spec_3 FROM full_spec_2 AS a LEFT JOIN full_spec_spoint AS b ON (a., a.h, 
a., a., a., a., a., a., a., a.)


UPDATE full_spec_3 SET r2id_spec=full_spec_spoint.r2id_spec FROM full_spec_spoint WHERE full_spec_3.sdss_mjd=full_spec_spoint.sdss_mjd AND full_spec_3.sdss_plate=full_spec_spoint.sdss_plate AND full_spec_3.sdss_fiberid=full_spec_spoint.sdss_fiberid AND full_spec_3.survey='sdss';

UPDATE full_spec_3 SET r2id_spec=full_spec_spoint.r2id_spec FROM full_spec_spoint WHERE full_spec_3.survey='2df' AND full_spec_3.ra_j2000=full_spec_spoint.ra_j2000 AND full_spec_3.dec_j2000=full_spec_spoint.dec_j2000 AND full_spec_3.z=full_spec_spoint.z;

UPDATE full_spec_3 SET r2id_spec=full_spec_spoint.r2id_spec FROM full_spec_spoint WHERE full_spec_3.sixdf_specid=full_spec_spoint.sixdf_specid AND full_spec_3.survey='6df';

UPDATE full_spec_3 SET r2id_spec=full_spec_spoint.r2id_spec FROM full_spec_spoint WHERE full_spec_3.survey='uzc' AND full_spec_3.ra_j2000=full_spec_spoint.ra_j2000 AND full_spec_3.dec_j2000=full_spec_spoint.dec_j2000 AND full_spec_3.z=full_spec_spoint.z;

UPDATE full_spec_3 SET survey='cfa' WHERE survey is null;
UPDATE full_spec_3 SET r2id_spec=full_spec_spoint.r2id_spec FROM full_spec_spoint WHERE full_spec_3.survey='cfa' AND full_spec_3.ra_j2000=full_spec_spoint.ra_j2000 AND full_spec_3.dec_j2000=full_spec_spoint.dec_j2000 AND full_spec_3.z=full_spec_spoint.z;

UPDATE full_spec_3 SET r2id_spec=full_spec_spoint.r2id_spec FROM full_spec_spoint WHERE full_spec_3.hectospec_date=full_spec_spoint.hectospec_date AND full_spec_3.hectospec_dataset=full_spec_spoint.hectospec_dataset AND full_spec_3.hectospec_spec=full_spec_spoint.hectospec_spec AND full_spec_3.survey='hectospec';

UPDATE full_spec_3 SET r2id_spec=full_spec_spoint.r2id_spec FROM full_spec_spoint WHERE full_spec_3.lamost_obsid=full_spec_spoint.lamost_obsid AND full_spec_3.lamost_planid=full_spec_spoint.lamost_planid AND full_spec_3.lamost_obsdate=full_spec_spoint.lamost_obsdate AND full_spec_3.lamost_lmjd=full_spec_spoint.lamost_lmjd AND full_spec_3.lamost_spid=full_spec_spoint.lamost_spid AND
full_spec_3.lamost_fiberid=full_spec_spoint.lamost_fiberid AND full_spec_3.survey='lamost';

UPDATE full_spec_3 SET r2id_spec=full_spec_spoint.r2id_spec FROM full_spec_spoint WHERE full_spec_3.lega_c_spectid=full_spec_spoint.lega_c_spectid AND full_spec_3.survey='lega_c';

UPDATE full_spec_3 SET r2id_spec=full_spec_spoint.r2id_spec FROM full_spec_spoint WHERE full_spec_3.deep2_objno=full_spec_spoint.deep2_objno AND full_spec_3.survey='deep2';

UPDATE full_spec_3 SET r2id_spec=full_spec_spoint.r2id_spec FROM full_spec_spoint WHERE full_spec_3.deep3_objno=full_spec_spoint.deep3_objno AND full_spec_3.survey='deep3';

UPDATE full_spec_3 SET r2id_spec=full_spec_spoint.r2id_spec FROM full_spec_spoint WHERE full_spec_3.wigglez_specfile=full_spec_spoint.wigglez_specfile AND full_spec_3.survey='wigglez';

UPDATE full_spec_3 SET r2id_spec=full_spec_spoint.r2id_spec FROM full_spec_spoint WHERE full_spec_3.gama_specid=full_spec_spoint.gama_specid AND full_spec_3.survey='gama';

UPDATE full_spec_3 SET r2id_spec=full_spec_spoint.r2id_spec FROM full_spec_spoint WHERE full_spec_3.twodflens_target=full_spec_spoint.twodflens_target AND full_spec_3.survey='2dflens';

UPDATE full_spec_3 SET r2id_spec=full_spec_spoint.r2id_spec FROM full_spec_spoint WHERE full_spec_3.fast_date=full_spec_spoint.fast_date AND full_spec_3.fast_dataset=full_spec_spoint.fast_dataset AND full_spec_3.fast_spec=full_spec_spoint.fast_spec AND full_spec_3.survey='fast';

UPDATE full_spec_3 SET r2id_obj=full_spec_spoint.r2id_obj FROM full_spec_spoint WHERE full_spec_3.r2id_spec=full_spec_spoint.r2id_spec;

SELECT a.*, (row_number() OVER (order by specind))+5728159 AS r2is_spec_new INTO TABLE full_spec_4 from full_spec_3 WHERE r2id_spec is null;

UPDATE full_spec_3 SET r2id_spec=full_spec_4.r2is_spec_new FROM full_spec_4 WHERE full_spec_3.specind=full_spec_4.specind;

DELETE FROM full_spec_3 T1
    USING   full_spec_3 T2
WHERE   T1.specind > T2.specind  
    AND T1.r2id_spec  = T2.r2id_spec;

ALTER TABLE full_spec_3 ADD COLUMN crd2000 spoint;
UPDATE full_spec_3 SET crd2000=spoint(ra_j2000*pi()/180.0,dec_j2000*pi()/180.0);
VACUUM FULL full_spec_3;        
CREATE INDEX s_index_3 ON full_spec_3 using gist (crd2000);
VACUUM ANALYZE full_spec_3; 

UPDATE full_spec_3 SET r2id_obj=rcsed_galaxies.r2id_obj FROM rcsed_galaxies WHERE full_spec_3.r2id_obj is null AND full_spec_3.crd2000 @ rcsed_galaxies.gal;

CREATE TABLE newsdss_size (
r2id_spec int, sdss_mjd int, sdss_plate int, sdss_fiberid int,ra_j2000 float, dec_j2000 float, z float, z_err float, ra float, dec float, objid bigint, petrorad_r float, deVAB_r float, deVPhi_r float);
\COPY newsdss_size FROM /data1/rcsed_v2/postgres/sdss_size_casjobs.csv WITH csv header;

SELECT a.r2id_spec, a.ra_j2000, a.dec_j2000, b.ra AS ra_obj, b.dec AS dec_obj, b.petrorad_r AS amaj, 1-b.deVAB_r AS ell, b.deVPhi_r AS pa, b.objid INTO TABLE newsdss_sizes_2 from full_spec_3 AS a LEFT JOIN newsdss_size AS b ON a.r2id_spec=b.r2id_spec WHERE a.r2id_obj is null;

ALTER TABLE newsdss_sizes_2 ADD COLUMN size_survey varchar;
UPDATE newsdss_sizes_2 SET size_survey='sdss' WHERE ra_obj is not null;
UPDATE newsdss_sizes_2 SET amaj=1 WHERE amaj is null;
UPDATE newsdss_sizes_2 SET ell=0 WHERE ell is null;
UPDATE newsdss_sizes_2 SET pa=0 WHERE pa is null;
UPDATE newsdss_sizes_2 SET ra_obj=ra_j2000 WHERE ra_obj is null;
UPDATE newsdss_sizes_2 SET dec_obj=dec_j2000 WHERE dec_obj is null;
UPDATE newsdss_sizes_2 SET objid=r2id_spec WHERE objid is null;

SELECT a.*, (dense_rank() over (order by objid))+4823050 AS r2id_obj INTO TABLE newsdss_sizes_3 FROM newsdss_sizes_2 AS a;

UPDATE newsdss_sizes_3 SET objid=null WHERE size_survey is null;

UPDATE full_spec_3 SET r2id_obj=newsdss_sizes_3.r2id_obj FROM newsdss_sizes_3 where full_spec_3.r2id_spec=newsdss_sizes_3.r2id_spec;


DELETE FROM newsdss_sizes_3 T1
    USING   newsdss_sizes_3 T2
WHERE   T1.objid is not null AND T1.r2id_spec > T2.r2id_spec  
    AND T1.objid  = T2.objid;
    
SELECT * into rcsed_galaxies_new FROM rcsed_galaxies;    
INSERT INTO rcsed_galaxies_new (size_survey, objid, ra_obj, dec_obj, amaj, ell, pa, r2id_obj) SELECT size_survey, objid, ra_obj, dec_obj, amaj, ell, pa, r2id_obj FROM newsdss_sizes_3;

ALTER TABLE rcsed_galaxies_new ADD COLUMN r2id_spec int;
UPDATE rcsed_galaxies_new SET r2id_spec=full_spec_3.r2id_spec FROM full_spec_3 WHERE rcsed_galaxies_new.r2id_obj=full_spec_3.r2id_obj;

DELETE FROM rcsed_galaxies_new WHERE r2id_spec is null;
ALTER TABLE rcsed_galaxies_new DROP COLUMN  r2id_spec;

UPDATE rcsed_galaxies_new SET crd2000=spoint(ra_obj*pi()/180.0, dec_obj*pi()/180.0) WHERE crd2000 is null;

UPDATE rcsed_galaxies_new SET gal=sellipse(crd2000, amaj/206265, amaj*(1-ell)/206265,
pa*pi()/180.0) WHERE gal is null;

=================================================

CREATE INDEX specindex_new_3 ON full_spec_3 (r2id_spec);
ALTER TABLE full_spec_3 ADD CONSTRAINT spec_constraint_3 UNIQUE(r2id_spec);
CREATE INDEX index_survey_id_3 ON full_spec_3 (survey, r2id_spec);

ALTER TABLE rcsed_galaxies_new ADD CONSTRAINT galaxy_new_3 UNIQUE(r2id_obj);
CREATE INDEX crd_ind_3 ON rcsed_galaxies_new using gist (crd2000);
CREATE INDEX gal_ind_3 ON rcsed_galaxies_new using gist (gal);

ALTER TABLE full_spec_3 ADD CONSTRAINT specfk_3 FOREIGN KEY (r2id_obj) REFERENCES rcsed_galaxies_new (r2id_obj);

SELECT a.*, b.r2id_spec AS r2id_spec_spec INTO TABLE aper_phot_2 FROM aper_phot AS a LEFT JOIN full_spec_3 AS b ON a.r2id_spec=b.r2id_spec;
DELETE FROM aper_phot_2 WHERE r2id_spec_spec is null;
ALTER TABLE aper_phot_2 DROP COLUMN r2id_spec_spec;
ALTER TABLE aper_phot_2 ADD CONSTRAINT photaperfk_new FOREIGN KEY (r2id_spec) REFERENCES full_spec_3 (r2id_spec);

SELECT a.*, b.r2id_spec AS r2id_spec_spec INTO TABLE raw_aper_phot_2 FROM raw_aper_phot AS a LEFT JOIN full_spec_3 AS b ON a.r2id_spec=b.r2id_spec;
DELETE FROM raw_aper_phot_2 WHERE r2id_spec_spec is null;
ALTER TABLE raw_aper_phot_2 DROP COLUMN r2id_spec_spec;
ALTER TABLE raw_aper_phot_2 ADD CONSTRAINT photspecfk_new FOREIGN KEY (r2id_spec) REFERENCES full_spec_3 (r2id_spec);	

SELECT a.*, b.r2id_obj AS r2id_obj_obj INTO TABLE raw_tot_phot_2 FROM raw_tot_phot AS a LEFT JOIN rcsed_galaxies_new AS b ON a.r2id_obj=b.r2id_obj;
DELETE FROM raw_tot_phot_2 WHERE r2id_obj_obj is null;
ALTER TABLE raw_tot_phot_2 DROP COLUMN r2id_obj_obj;
ALTER TABLE raw_tot_phot_2 ADD CONSTRAINT photobjfk_new FOREIGN KEY (r2id_obj) REFERENCES rcsed_galaxies_new (r2id_obj);

SELECT a.*, b.r2id_obj AS r2id_obj_obj INTO TABLE tot_phot_2 FROM tot_phot AS a LEFT JOIN rcsed_galaxies_new AS b ON a.r2id_obj=b.r2id_obj;
DELETE FROM tot_phot_2 WHERE r2id_obj_obj is null;
ALTER TABLE tot_phot_2 DROP COLUMN r2id_obj_obj;
ALTER TABLE tot_phot_2 ADD CONSTRAINT photfullfk_new FOREIGN KEY (r2id_obj) REFERENCES rcsed_galaxies_new (r2id_obj);


CREATE INDEX aperspecindex_new ON aper_phot_2 (r2id_spec);

CREATE INDEX filter_ind_new ON raw_aper_phot_2 (filter_id);
CREATE INDEX spec_ind_new ON raw_aper_phot_2 (r2id_spec);

CREATE INDEX totobjindex_new ON tot_phot_2 (r2id_obj);




ALTER TABLE full_spec_spoint RENAME TO full_spec_spoint_old_2;
ALTER TABLE rcsed_galaxies RENAME TO rcsed_galaxies_old_2;
ALTER TABLE tot_phot RENAME TO tot_phot_old_2;
ALTER TABLE aper_phot RENAME TO aper_phot_old_2;
ALTER TABLE raw_tot_phot RENAME TO raw_tot_phot_old_2;
ALTER TABLE raw_aper_phot RENAME TO raw_aper_phot_old_2;

ALTER TABLE full_spec_3 RENAME TO full_spec_spoint;
ALTER TABLE rcsed_galaxies_new RENAME TO rcsed_galaxies;
ALTER TABLE tot_phot_2 RENAME TO tot_phot;
ALTER TABLE aper_phot_2 RENAME TO aper_phot;
ALTER TABLE raw_tot_phot_2 RENAME TO raw_tot_phot;
ALTER TABLE raw_aper_phot_2 RENAME TO raw_aper_phot;

GRANT SELECT ON full_spec_spoint TO dj_user;
GRANT SELECT ON rcsed_galaxies TO dj_user;
GRANT SELECT ON tot_phot TO dj_user;
GRANT SELECT ON aper_phot TO dj_user;
GRANT SELECT ON raw_tot_phot TO dj_user;
GRANT SELECT ON raw_aper_phot TO dj_user;










