
CREATE TABLE apis_data (
	id serial primary key,
	-- fits
	calibrated_filename varchar(255),
	processed_filename varchar(255),
	cylindric_filename varchar(255),
	polar_filename varchar(255),
	-- jpeg
	calibrated_preview  varchar(255),
	processed_preview varchar(255),
	cylindric_preview varchar(255),
	polar_preview varchar(255),
	-- pdf
	processed_pdf varchar(255),
	cylindric_pdf varchar(255),
	polar_pdf varchar(255),
	-- param
	telescop varchar(80),
	instrume varchar(80),
	targname varchar(80),
	tdateobs date,
	ttimeobs time,
	datetime timestamp,
	campaign varchar(80),
	texptime float,
	obstype  varchar(80),
	obsmode  varchar(80),
	photmode varchar(80),
	detector varchar(80),
	opt_elem varchar(80),
	filter varchar(80),
	bunit  varchar(80),
	proposid integer,
	pr_inv_l varchar(80),
	propttl1 varchar(255),
	origin   varchar(80),
	date     datetime,
	ra_targ  float,
	dec_targ float,
	orientat float,
	sunangle float,
	moonangl float,
	sun_alt  float,
	cenwave  integer,
	bandwid  float,
	specres  float,
	centrwv  float,
	minwave  float,
	maxwave  float,
	platesc  float,
	tardescr varchar(80),
	cal_ver varchar(80),
	rootname varchar(80),
	aperture varchar(80),
	last_update timestamp
	);

CREATE OR REPLACE FUNCTION update_datetime() RETURNS TRIGGER AS $update_datetime$
	BEGIN
		NEW.datetime := NEW.tdateobs + NEW.ttimeobs ;
		NEW.last_update = now() ;
		RETURN NEW;
	END;
$update_datetime$ LANGUAGE plpgsql;

DROP TRIGGER update_datetime ON apis_data;
CREATE TRIGGER update_datetime
BEFORE UPDATE OR INSERT ON apis_data
   FOR EACH ROW EXECUTE PROCEDURE update_datetime();
   



-- Mise à jour du 26/06/2013

ALTER TABLE apis_data 
	ADD COLUMN subelat float,
	ADD COLUMN subslat float,
	ADD COLUMN subelon float,
	ADD COLUMN subslon float,
	ADD COLUMN dists float,
	ADD COLUMN diste float,
	ADD COLUMN rap float,
	ADD COLUMN np_pos float,
	ADD COLUMN phase float,
	ADD COLUMN skr_n float,
	ADD COLUMN skr_s float,
	ADD COLUMN hemis1 varchar(80),
	ADD COLUMN hemis2 varchar(80),
	ADD COLUMN spectrum_filename varchar(255),
	ADD COLUMN spectrum_preview varchar(255),
	ADD COLUMN spectrum_pdf varchar(255),
	ADD COLUMN polar_n_filename varchar(255),
	ADD COLUMN polar_s_filename varchar(255),
	ADD COLUMN polar_n_preview varchar(255),
	ADD COLUMN polar_s_preview varchar(255),
	ADD COLUMN polar_n_pdf varchar(255),
	ADD COLUMN polar_s_pdf varchar(255),
	DROP COLUMN polar_filename,
	DROP COLUMN polar_preview,
	DROP COLUMN polar_pdf;
	

-- cibles secondaires

CREATE TABLE apis_secondary_targets (
	id integer not null references apis_data(id) on delete cascade,
	targname varchar(80),
	unique (id, targname)
	);
	
-- MàJ du 02/07/13

alter table apis_data add column modified_at timestamp;
update apis_data set modified_at = 'epoch' ;

-- MàJ du 04/07/13

alter table apis_data add column date_processed timestamp;

-- MàJ du 03/07/14

ALTER TABLE apis_data 
-- longitue et local time des satellites de Jupiter
	ADD COLUMN lon_io float,
	ADD COLUMN lt_io float,
	ADD COLUMN lon_eur float,
	ADD COLUMN lt_eur float,
	ADD COLUMN lon_gan float,
	ADD COLUMN lt_gan float,
	ADD COLUMN lon_cal float,
	ADD COLUMN lt_cal float,
-- longitude des satellites de Saturne
	ADD COLUMN lt_mim float,
	ADD COLUMN lt_enc float,
	ADD COLUMN lt_the float,
	ADD COLUMN lt_dio float,
	ADD COLUMN lt_rhe float,
	ADD COLUMN lt_tit float,
-- ephemerides des satellites Galileo (pour Jupiter) et Cassini (pour Saturne)
	ADD COLUMN rs_gal float,
	ADD COLUMN lat_gal float,
	ADD COLUMN lt_gal float,
	ADD COLUMN rs_cas float,
	ADD COLUMN lat_cas float,
	ADD COLUMN lt_cas float;
	
-- espacement entre les milieux de 2 canaux adjacents
ALTER TABLE apis_data ADD column spectral_step float;

-- MàJ du 09/10/2018
ALTER TABLE apis.bastet_data ADD COLUMN publisher_original varchar, ADD COLUMN service_title_original varchar;
ALTER TABLE apis.hisaki_data ADD COLUMN publisher_original varchar, ADD COLUMN service_title_original varchar;

-- MàJ du 29/06/2020
ALTER TABLE apis.bastet_data 
	ADD COLUMN mlat_jun float,
	ADD COLUMN mlat_io float,
	ADD COLUMN mlat_gan float,
	ADD COLUMN mlat_eur float,
	ADD COLUMN mlat_cal float;

ALTER TABLE apis.bastet_data 
	ADD COLUMN rj_jun float,
	ADD COLUMN lt_jun float,
	ADD COLUMN lat_jun float;
