drop view dynastvo.newfit;
create view dynastvo.newfit as select distinct on (id_identifying_numbers)  * from (select * from dynastvo.fit order by created desc) as foo;


drop view dynastvo.epn_core;
drop materialized view dynastvo.vepn_core;
CREATE MATERIALIZED VIEW dynastvo.vepn_core AS SELECT 
elements[1] as semi_major_axis,
elements[2] as eccentricity,
elements[3] as inclination,
elements[4] as long_asc,
elements[5] as arg_perihel,
elements[6] as mean_anomaly,
elements[7] as epoch,
elements[14] as epochc,
elements[8] as cartesian_x,
elements[9] as cartesian_y,
elements[10] as cartesian_z,
elements[11] as cartesian_vx,
elements[12] as cartesian_vy,
elements[13] as cartesian_vz,
errors[1] as semi_major_axis_error,
errors[2] as eccentricity_error,
errors[3] as inclination_error,
errors[4] as long_asc_error,
errors[5] as arg_perihel_error,
errors[6] as mean_anomaly_error,
'{"variance-covariance":[ {"11":"'||cast(errors[7] as text)||'"},{"12":"'||cast(errors[8] as text)||'"},{"13":"'||cast(errors[9] as text)||'"},{"14":"'||cast(errors[10] as text)||'"},{"15":"'||cast(errors[11] as text)||'"},{"16":"'||cast(errors[12] as text)||'"},{"22":"'||cast(errors[13] as text)||'"},{"23":"'||
cast(errors[14] as text)||'"},{"24":"'||cast(errors[15] as text)||'"},{"25":"'||cast(errors[16] as text)||'"},{"26":"'||cast(errors[17] as text)||'"},{"33":"'||cast(errors[18] as text)||'"},{"34":"'||cast(errors[19] as text)||'"},{"35":"'||cast(errors[20] as text)||'"},{"36":"'||cast(errors[21] as text)||'"},{"44":"'||cast(errors[22] as text)||'"},{"45":"'||cast(errors[23] as text)||'"},{"46":"'||cast(errors[24] as text)||'"},{"55":"'||cast(errors[25] as text)||'"},{"56":"'||cast(errors[26] as text)||'"},{"66":"'||cast(errors[10] as text)||'"} ]}' as covar_mat,
moid as moid,
spu as spu,
anacronym as orbit_property_anacronym,
dynastvo.object_types.description as orbit_property_decription,
h as mag,
g as slope,
date_computed as modification_date,
dynastvo.orbit_elements.created as creation_date,
case when dynastvo.name.name = '' then
   cast(dynastvo.identifying_numbers.id as text)
else
    array_to_string(array(select dynastvo.designation.designation from dynastvo.designation where dynastvo.designation.id_identifying_numbers = dynastvo.newfit.id_identifying_numbers order by designation desc limit 1),'') 
end as target_name,
-- array_to_string(array(select dynastvo.designation.designation from dynastvo.designation where dynastvo.designation.id_identifying_numbers = dynastvo.fit.id_identifying_numbers order by designation desc limit 1),'') as target_name_bis,
dynastvo.identifying_numbers.id_mpc as id_mpc,
dynastvo.identifying_numbers.id_spk as id_spk,
dynastvo.identifying_numbers.id as id_gaia,
array_to_string(array(select distinct "designation" from dynastvo.designation where dynastvo.designation.id_identifying_numbers = dynastvo.newfit.id_identifying_numbers),'#')||'#'||cast(dynastvo.identifying_numbers.id_mpc as text)||'#'|| cast(dynastvo.identifying_numbers.id_spk as text)||'#'||cast(dynastvo.identifying_numbers.id as text) as alt_target_name,
case when position('PHA' in anacronym) != 0
then text 'PHA'
else
cast(NULL as TEXT)
end 
as pha,
case when position('NEA' in anacronym) = 1
then text 'NEO'
else
case when position('MBA' in anacronym) = 1
then text 'MBA'
else
case when position('NEA-' in anacronym) = 1
then  text 'NEO'
else
case when position('TNO' in anacronym) = 1
then text 'TNO'
else
cast(NULL as TEXT)
end 
end 
end 
end
as dynamical_class,
case when position('TROJANS' in anacronym) = 1
then text 'TROJAN'
else
case when position('SATELLITE' in anacronym) = 1
then text 'SATELLITE'
else
case when position('ATEN' in anacronym) != 0
then text 'ATEN'
else
case when position('APOLLO' in anacronym) != 0
then text 'APOLLO'
else
case when position('ATIRA' in anacronym) != 0
then text 'ATIRA'
else
case when position('AMOR' in anacronym) != 0
then text 'AMOR'
else
case when position('ATEN' in anacronym) != 0
then text 'ATEN'
else
case when position('HUNGARIAS' in anacronym) != 0
then text 'HUNGARIA'
else
case when position('PHOCAEAS' in anacronym) != 0
then text 'PHOCAEA'
else
case when position('CYBELES' in anacronym) != 0
then text 'CYBELE'
else
case when position('HILDAS' in anacronym) != 0
then text 'HILDA'
else
case when position('TROJANS' in anacronym) != 0
then text 'TROJAN'
else
case when position('CENTAURS' in anacronym) != 0
then text 'CENTAUR'
else
case when position('IIIA' in anacronym) != 0
then text 'IIIA'
else
case when position('IIIB' in anacronym) != 0
then text 'IIIB'
else
case when position('IIA' in anacronym) != 0
then text 'IIA'
else
case when position('IIB' in anacronym) != 0
then text 'IIB'
else
case when position('I' in anacronym) != 0
then text 'I'
else
cast(NULL as TEXT)
end 
end 
end 
end 
end 
end 
end 
end 
end 
end 
end 
end 
end 
end 
end 
end 
end 
end 
as dynamical_type,
(dynastvo.newfit.conditions).noba as fit_nb_obs_used,
(dynastvo.newfit.conditions).nobr as fit_nb_obs_rejected,
(dynastvo.newfit.conditions).nobrr as fit_nb_obs_ranging,
(dynastvo.newfit.conditions).nobvr as fit_nb_obs_doppler,
(dynastvo.newfit.conditions).jdmin as fit_jd_initial,
(dynastvo.newfit.conditions).jdmax as fit_jd_final,
dynastvo.newfit.mpecs as lastdou,
dynastvo.newfit.rms as rms_obs


FROM dynastvo.orbit_elements INNER JOIN dynastvo.orbit_elements_errors  ON (dynastvo.orbit_elements.id_fit = dynastvo.orbit_elements_errors.id_fit) INNER JOIN dynastvo.orbit_properties ON (dynastvo.orbit_elements.id_fit = dynastvo.orbit_properties.id_fit) INNER JOIN dynastvo.object_types ON (dynastvo.orbit_properties.id_object_type = dynastvo.object_types.id) INNER JOIN dynastvo.physical_properties ON (dynastvo.physical_properties.id_fit = dynastvo.orbit_elements.id_fit) INNER JOIN dynastvo.newfit ON (dynastvo.orbit_elements.id_fit = dynastvo.newfit.id) 
 LEFT JOIN dynastvo.name ON (dynastvo.name.id_identifying_numbers = dynastvo.newfit.id_identifying_numbers) INNER JOIN dynastvo.identifying_numbers ON (dynastvo.identifying_numbers.id = dynastvo.newfit.id_identifying_numbers);

