DROP VIEW IF EXISTS apis.epn_core;
DROP VIEW IF EXISTS apis.apis_products ;
CREATE VIEW apis.apis_products as
SELECT  DISTINCT 
	-- les fichiers calibrés originaux
	substr(d.data_id, 0,5) || cast(substr(d.data_id, 5) as numeric)*100+1 as id, 
	d.data_id as data_id,
	'original_data' as dataset_id,
	1 as dataset_index,
	integer '3' AS processing_level,
	CASE obstype
		WHEN 'IMAGING'  THEN TEXT 'phot.count;obs.image' 
		WHEN 'SPECTROSCOPIC'  THEN TEXT 'phot.flux.density;em.UV.100-200nm'
		ELSE  CAST(NULL AS TEXT)
		END AS  measurement_type,
	calibrated_filename as access_url,
	regexp_replace(calibrated_preview, '.jpg', '_small.jpg') as thumbnail_url,
	telescop, instrume, targname, tdateobs, ttimeobs, datetime, texptime, 
	obstype, obsmode, photmode, detector, opt_elem, filter, 
	orientat, sunangle, moonangl, sun_alt, 
	bandwid, specres, centrwv, minwave, maxwave, bunit, spectral_step,
	proposid, pr_inv_l, propttl1, origin, rootname, campaign, 
	ra_targ, dec_targ, platesc, tardescr, date, cal_ver, 
	subelat, subslat, subelon, subslon, dists, diste, rap, np_pos, phase, 
	skr_n, skr_s, hemis1, hemis2, modified_at, date_processed, last_update, lon_io, lt_io, lon_eur, lt_eur, 
	lon_gan, lt_gan, lon_cal, lt_cal, lt_mim, lt_enc, lt_the, lt_dio, 
	lt_rhe, lt_tit, rj_gal, lat_gal, lt_gal, rs_cas, lat_cas, lt_cas  
FROM apis.apis_data d WHERE calibrated_filename IS NOT NULL and  targname is not null and obstype is not null 
UNION SELECT  DISTINCT 
	-- les fichiers processés
	substr(d.data_id, 0,5) || cast(substr(d.data_id, 5) as numeric)*100+2 as id, 
	d.data_id as data_id,
	'processed_data' as dataset_id,
	2 as dataset_index,
	integer '4' AS processing_level,
	CASE obstype
		WHEN 'IMAGING'  THEN TEXT 'phot.count;obs.image' 
		WHEN 'SPECTROSCOPIC'  THEN TEXT 'phot.flux.density;em.UV.100-200nm'
		ELSE  CAST(NULL AS TEXT)
		END AS  measurement_type,
	processed_filename as access_url, 	
	regexp_replace(processed_preview, '.jpg', '_small.jpg') as thumbnail_url, 	
	telescop, instrume, targname, tdateobs, ttimeobs, datetime, texptime, 
	obstype, obsmode, photmode, detector, opt_elem, filter, 
	orientat, sunangle, moonangl, sun_alt, 
	bandwid, specres, centrwv, minwave, maxwave, bunit, spectral_step,
	proposid, pr_inv_l, propttl1, origin, rootname, campaign, 
	ra_targ, dec_targ, platesc, tardescr, date, cal_ver, 
	subelat, subslat, subelon, subslon, dists, diste, rap, np_pos, phase, 
	skr_n, skr_s, hemis1, hemis2, modified_at, date_processed, last_update, lon_io, lt_io, lon_eur, lt_eur, 
	lon_gan, lt_gan, lon_cal, lt_cal, lt_mim, lt_enc, lt_the, lt_dio, 
	lt_rhe, lt_tit, rj_gal, lat_gal, lt_gal, rs_cas, lat_cas, lt_cas  
FROM apis.apis_data d WHERE processed_filename IS NOT NULL  and obstype is not null 
UNION SELECT  DISTINCT 
	-- les fichiers processés en pdf
	substr(d.data_id, 0,5) || cast(substr(d.data_id, 5) as numeric)*100+2 as id, 
	d.data_id as data_id,
	'processed_data' as dataset_id,
	2 as dataset_index,
	integer '4' AS processing_level,
	CASE obstype
		WHEN 'IMAGING'  THEN TEXT 'phot.count;obs.image' 
		WHEN 'SPECTROSCOPIC'  THEN TEXT 'phot.flux.density;em.UV.100-200nm'
		ELSE  CAST(NULL AS TEXT)
		END AS  measurement_type,
	processed_pdf as access_url, 	
	regexp_replace(processed_preview, '.jpg', '_small.jpg') as thumbnail_url, 	
	telescop, instrume, targname, tdateobs, ttimeobs, datetime, texptime, 
	obstype, obsmode, photmode, detector, opt_elem, filter, 
	orientat, sunangle, moonangl, sun_alt, 
	bandwid, specres, centrwv, minwave, maxwave, bunit, spectral_step,
	proposid, pr_inv_l, propttl1, origin, rootname, campaign, 
	ra_targ, dec_targ, platesc, tardescr, date, cal_ver, 
	subelat, subslat, subelon, subslon, dists, diste, rap, np_pos, phase, 
	skr_n, skr_s, hemis1, hemis2, modified_at, date_processed, last_update, lon_io, lt_io, lon_eur, lt_eur, 
	lon_gan, lt_gan, lon_cal, lt_cal, lt_mim, lt_enc, lt_the, lt_dio, 
	lt_rhe, lt_tit, rj_gal, lat_gal, lt_gal, rs_cas, lat_cas, lt_cas  
FROM apis.apis_data d WHERE processed_pdf IS NOT NULL  and obstype is not null 
UNION SELECT  DISTINCT 
	-- les projections cylindriques
	substr(d.data_id, 0,5) || cast(substr(d.data_id, 5) as numeric)*100+3 as id, 
	d.data_id as data_id,
	'cylindric_proj' as dataset_id,
	3 as dataset_index,
	integer '5' AS processing_level,
	'phot.flux;obs.image' as measurement_type,
	cylindric_filename as access_url, 	
	regexp_replace(cylindric_preview, '.jpg', '_small.jpg') as thumbnail_url, 	
	telescop, instrume, targname, tdateobs, ttimeobs, datetime, texptime, 
	obstype, obsmode, photmode, detector, opt_elem, filter, 
	orientat, sunangle, moonangl, sun_alt, 
	bandwid, specres, centrwv, minwave, maxwave, bunit, spectral_step,
	proposid, pr_inv_l, propttl1, origin, rootname, campaign, 
	ra_targ, dec_targ, platesc, tardescr, date, cal_ver, 
	subelat, subslat, subelon, subslon, dists, diste, rap, np_pos, phase, 
	skr_n, skr_s, hemis1, hemis2, modified_at, date_processed, last_update, lon_io, lt_io, lon_eur, lt_eur, 
	lon_gan, lt_gan, lon_cal, lt_cal, lt_mim, lt_enc, lt_the, lt_dio, 
	lt_rhe, lt_tit, rj_gal, lat_gal, lt_gal, rs_cas, lat_cas, lt_cas  
FROM apis.apis_data d WHERE cylindric_filename IS NOT NULL and obstype is not null  
UNION SELECT  DISTINCT 
	-- les projections cylindriques en pdf
	substr(d.data_id, 0,5) || cast(substr(d.data_id, 5) as numeric)*100+3 as id, 
	d.data_id as data_id,
	'cylindric_proj' as dataset_id,
	3 as dataset_index,
	integer '5' AS processing_level,
	'phot.flux;obs.image' as measurement_type,
	cylindric_pdf as access_url, 	
	regexp_replace(cylindric_preview, '.jpg', '_small.jpg') as thumbnail_url, 	
	telescop, instrume, targname, tdateobs, ttimeobs, datetime, texptime, 
	obstype, obsmode, photmode, detector, opt_elem, filter, 
	orientat, sunangle, moonangl, sun_alt, 
	bandwid, specres, centrwv, minwave, maxwave, bunit, spectral_step,
	proposid, pr_inv_l, propttl1, origin, rootname, campaign, 
	ra_targ, dec_targ, platesc, tardescr, date, cal_ver, 
	subelat, subslat, subelon, subslon, dists, diste, rap, np_pos, phase, 
	skr_n, skr_s, hemis1, hemis2, modified_at, date_processed, last_update, lon_io, lt_io, lon_eur, lt_eur, 
	lon_gan, lt_gan, lon_cal, lt_cal, lt_mim, lt_enc, lt_the, lt_dio, 
	lt_rhe, lt_tit, rj_gal, lat_gal, lt_gal, rs_cas, lat_cas, lt_cas  
FROM apis.apis_data d WHERE cylindric_pdf IS NOT NULL and obstype is not null  
UNION SELECT  DISTINCT 
	-- les projections polaires N
	substr(d.data_id, 0,5) || cast(substr(d.data_id, 5) as numeric)*100+4 as id, 
	d.data_id as data_id,
	'polar_proj_north' as dataset_id,
	4 as dataset_index,
	integer '5' AS processing_level,
	'phot.flux;obs.image' as measurement_type,
	polar_n_filename as access_url, 	
	regexp_replace(polar_n_preview, '.jpg', '_small.jpg') as thumbnail_url, 		
	telescop, instrume, targname, tdateobs, ttimeobs, datetime, texptime, 
	obstype, obsmode, photmode, detector, opt_elem, filter, 
	orientat, sunangle, moonangl, sun_alt, 
	bandwid, specres, centrwv, minwave, maxwave, bunit, spectral_step,
	proposid, pr_inv_l, propttl1, origin, rootname, campaign, 
	ra_targ, dec_targ, platesc, tardescr, date, cal_ver, 
	subelat, subslat, subelon, subslon, dists, diste, rap, np_pos, phase, 
	skr_n, skr_s, hemis1, hemis2, modified_at, date_processed, last_update, lon_io, lt_io, lon_eur, lt_eur, 
	lon_gan, lt_gan, lon_cal, lt_cal, lt_mim, lt_enc, lt_the, lt_dio, 
	lt_rhe, lt_tit, rj_gal, lat_gal, lt_gal, rs_cas, lat_cas, lt_cas  
FROM apis.apis_data d WHERE polar_n_filename IS NOT NULL and obstype is not null  
UNION SELECT  DISTINCT 
	-- les projections polaires N en pdf
	substr(d.data_id, 0,5) || cast(substr(d.data_id, 5) as numeric)*100+4 as id, 
	d.data_id as data_id,
	'polar_proj_north' as dataset_id,
	4 as dataset_index,
	integer '5' AS processing_level,
	'phot.flux;obs.image' as measurement_type,
	polar_n_pdf as access_url, 		
	regexp_replace(polar_n_preview, '.jpg', '_small.jpg') as thumbnail_url, 		
	telescop, instrume, targname, tdateobs, ttimeobs, datetime, texptime, 
	obstype, obsmode, photmode, detector, opt_elem, filter, 
	orientat, sunangle, moonangl, sun_alt, 
	bandwid, specres, centrwv, minwave, maxwave, bunit, spectral_step,
	proposid, pr_inv_l, propttl1, origin, rootname, campaign, 
	ra_targ, dec_targ, platesc, tardescr, date, cal_ver, 
	subelat, subslat, subelon, subslon, dists, diste, rap, np_pos, phase, 
	skr_n, skr_s, hemis1, hemis2, modified_at, date_processed, last_update, lon_io, lt_io, lon_eur, lt_eur, 
	lon_gan, lt_gan, lon_cal, lt_cal, lt_mim, lt_enc, lt_the, lt_dio, 
	lt_rhe, lt_tit, rj_gal, lat_gal, lt_gal, rs_cas, lat_cas, lt_cas  
FROM apis.apis_data d WHERE polar_n_pdf IS NOT NULL and obstype is not null  
UNION SELECT  DISTINCT 
	-- les projections polaires S 
	substr(d.data_id, 0,5) || cast(substr(d.data_id, 5) as numeric)*100+5 as id, 
	d.data_id as data_id,
	'polar_proj_south' as dataset_id,
	5 as dataset_index,
	integer '5' AS processing_level,
	'phot.flux;obs.image' as measurement_type,
	polar_s_filename as access_url, 			
	regexp_replace(polar_s_preview, '.jpg', '_small.jpg') as thumbnail_url, 		
	telescop, instrume, targname, tdateobs, ttimeobs, datetime, texptime, 
	obstype, obsmode, photmode, detector, opt_elem, filter, 
	orientat, sunangle, moonangl, sun_alt, 
	bandwid, specres, centrwv, minwave, maxwave, bunit, spectral_step,
	proposid, pr_inv_l, propttl1, origin, rootname, campaign, 
	ra_targ, dec_targ, platesc, tardescr, date, cal_ver, 
	subelat, subslat, subelon, subslon, dists, diste, rap, np_pos, phase, 
	skr_n, skr_s, hemis1, hemis2, modified_at, date_processed, last_update, lon_io, lt_io, lon_eur, lt_eur, 
	lon_gan, lt_gan, lon_cal, lt_cal, lt_mim, lt_enc, lt_the, lt_dio, 
	lt_rhe, lt_tit, rj_gal, lat_gal, lt_gal, rs_cas, lat_cas, lt_cas  
FROM apis.apis_data d WHERE polar_s_filename IS NOT NULL and obstype is not null  
UNION SELECT  DISTINCT 
	-- les projections polaires S en pdf
	substr(d.data_id, 0,5) || cast(substr(d.data_id, 5) as numeric)*100+5 as id, 
	d.data_id as data_id,
	'polar_proj_south' as dataset_id,
	5 as dataset_index,
	integer '5' AS processing_level,
	'phot.flux;obs.image' as measurement_type,
	polar_s_pdf as access_url, 			
	regexp_replace(polar_s_preview, '.jpg', '_small.jpg') as thumbnail_url, 		
	telescop, instrume, targname, tdateobs, ttimeobs, datetime, texptime, 
	obstype, obsmode, photmode, detector, opt_elem, filter, 
	orientat, sunangle, moonangl, sun_alt, 
	bandwid, specres, centrwv, minwave, maxwave, bunit, spectral_step,
	proposid, pr_inv_l, propttl1, origin, rootname, campaign, 
	ra_targ, dec_targ, platesc, tardescr, date, cal_ver, 
	subelat, subslat, subelon, subslon, dists, diste, rap, np_pos, phase, 
	skr_n, skr_s, hemis1, hemis2, modified_at, date_processed, last_update, lon_io, lt_io, lon_eur, lt_eur, 
	lon_gan, lt_gan, lon_cal, lt_cal, lt_mim, lt_enc, lt_the, lt_dio, 
	lt_rhe, lt_tit, rj_gal, lat_gal, lt_gal, rs_cas, lat_cas, lt_cas  
FROM apis.apis_data d WHERE polar_s_pdf IS NOT NULL and obstype is not null  
UNION SELECT DISTINCT 
	-- les spectres 1D
	substr(d.data_id, 0,5) || cast(substr(d.data_id, 5) as numeric)*100+6 as id, 
	d.data_id as data_id,
	'1D_spectra' as dataset_id,
	6 as dataset_index,
	integer '5' AS processing_level,
	'phot.flux.density;em.UV.100-200nm' as measurement_type,
	spectrum_filename as access_url, 	
	regexp_replace(spectrum_preview, '.jpg', '_small.jpg') as thumbnail_url, 		
	telescop, instrume, targname, tdateobs, ttimeobs, datetime, texptime, 
	obstype, obsmode, photmode, detector, opt_elem, filter, 
	orientat, sunangle, moonangl, sun_alt, 
	bandwid, specres, centrwv, minwave, maxwave, bunit, spectral_step,
	proposid, pr_inv_l, propttl1, origin, rootname, campaign, 
	ra_targ, dec_targ, platesc, tardescr, date, cal_ver, 
	subelat, subslat, subelon, subslon, dists, diste, rap, np_pos, phase, 
	skr_n, skr_s, hemis1, hemis2, modified_at, date_processed, last_update, lon_io, lt_io, lon_eur, lt_eur, 
	lon_gan, lt_gan, lon_cal, lt_cal, lt_mim, lt_enc, lt_the, lt_dio, 
	lt_rhe, lt_tit, rj_gal, lat_gal, lt_gal, rs_cas, lat_cas, lt_cas  
FROM apis.apis_data d WHERE spectrum_filename IS NOT NULL and obstype is not null  
UNION SELECT DISTINCT 
	-- les spectres 1D en pdf
	substr(d.data_id, 0,5) || cast(substr(d.data_id, 5) as numeric)*100+6 as id, 
	d.data_id as data_id,
	'1D_spectra' as dataset_id,
	6 as dataset_index,
	integer '5' AS processing_level,
	'phot.flux.density;em.UV.100-200nm' as measurement_type,
	spectrum_pdf as access_url, 	
	regexp_replace(spectrum_preview, '.jpg', '_small.jpg') as thumbnail_url, 		
	telescop, instrume, targname, tdateobs, ttimeobs, datetime, texptime, 
	obstype, obsmode, photmode, detector, opt_elem, filter, 
	orientat, sunangle, moonangl, sun_alt, 
	bandwid, specres, centrwv, minwave, maxwave, bunit, spectral_step,
	proposid, pr_inv_l, propttl1, origin, rootname, campaign, 
	ra_targ, dec_targ, platesc, tardescr, date, cal_ver, 
	subelat, subslat, subelon, subslon, dists, diste, rap, np_pos, phase, 
	skr_n, skr_s, hemis1, hemis2, modified_at, date_processed, last_update, lon_io, lt_io, lon_eur, lt_eur, 
	lon_gan, lt_gan, lon_cal, lt_cal, lt_mim, lt_enc, lt_the, lt_dio, 
	lt_rhe, lt_tit, rj_gal, lat_gal, lt_gal, rs_cas, lat_cas, lt_cas  
FROM apis.apis_data d WHERE spectrum_pdf IS NOT NULL and obstype is not null  
;
