/**********************************************************************************************************************
 *                   IMPORTATION DES DONNEES DE LA PREMIERE INSTANCE GCRA.DBCHIRO.ORG                                 *
 **********************************************************************************************************************/

/* Comptes utilisateurs */

TRUNCATE accounts_profile CASCADE;

INSERT INTO accounts_profile (id,
                              password,
                              last_login,
                              is_superuser,
                              username,
                              first_name,
                              last_name,
                              email,
                              is_staff,
                              is_active,
                              date_joined,
                              is_resp,
                              access_all_data,
                              edit_all_data,
                              organism,
                              comment,
                              timestamp_create, timestamp_update, created_by_id)
  SELECT
    u.id,
    u.password,
    u.last_login,
    u.is_superuser,
    u.username,
    u.first_name,
    u.last_name,
    u.email,
    u.is_staff,
    u.is_active,
    u.date_joined,
    CASE WHEN status = 'coord'
      THEN TRUE
    ELSE FALSE END AS is_resp,
    CASE WHEN ud.auth_access_alldata IS TRUE
      THEN TRUE
    ELSE FALSE END AS access_all_data,
    CASE WHEN ud.auth_edit_alldata IS TRUE
      THEN TRUE
    ELSE FALSE END AS edit_all_data,
    ud.organism,
    ud.comment,
    ud.timestamp_create,
    ud.timestamp_update,
    2
  FROM dbchirogcra.auth_user u LEFT JOIN dbchirogcra.sights_userdetail ud ON u.id = ud.user_id;


/* Territoires de responsabilité */
INSERT INTO accounts_profile_resp_territory
  SELECT
    ut.id,
    ud.user_id,
    ut.territory_id
  FROM dbchirogcra.sights_userdetail_coord_territory AS ut LEFT JOIN dbchirogcra.sights_userdetail ud
      ON ut.userdetail_id = ud.id;


/* Etudes */
/* Réalisé directement via la fonction "Copy table to" de DataGrip */

/* Localités */

INSERT INTO sights_place (
  id_place,
  name,
  is_hidden,
  is_gite,
  is_managed,
  proprietary,
  convention,
  convention_file,
  photo_file,
  habitat,
  altitude,
  id_bdcavite,
  plan_localite,
  comment,
  bdsource,
  id_bdsource,
  geom,
  x,
  y,
  timestamp_create,
  timestamp_update,
  type_id,
  territory_id,
  precision_id,
  municipality_id,
  domain_id,
  created_by_id
) SELECT
    id_place,
    "name",
    hidden                                           AS is_hidden,
    CASE WHEN is_gite IS TRUE
      THEN TRUE
    ELSE FALSE END                                   AS is_gite,
    FALSE                                            AS is_managed,
    proprietary,
    convention,
    convention_file,
    photo_file,
    habitat,
    altitude,
    id_bdcavite,
    plan_localite,
    "comment",
    bdsource,
    id_bdsource,
    st_transform(st_setsrid(geom, 2154), 4326)       AS geom,
    st_x(st_transform(st_setsrid(geom, 2154), 4326)) AS x,
    st_y(st_transform(st_setsrid(geom, 2154), 4326)) AS Y,
    timestamp_create,
    timestamp_update,
    CASE WHEN type_id < 3
      THEN NULL
    ELSE type_id END                                 AS type_id,
    territory_id,
    precision_id,
    municipality_id,
    domain_id,
    creator_id
  FROM dbchirogcra.sights_place;

UPDATE sights_place d
SET is_hidden = hidden FROM dbchirogcra.sights_place s
WHERE s.id_place = d.id_place;

/* Sessions */
INSERT INTO sights_session (
  id_session,
  name,
  date_start,
  time_start,
  date_end,
  time_end,
  data_file,
  is_confidential,
  comment,
  timestamp_create,
  timestamp_update,
  contact_id,
  main_observer_id,
  place_id,
  study_id,
  created_by_id,
  updated_by_id)
  SELECT
    id_session,
    concat('loc', place_id, '_', date_start, '_', dc.code, '_', u.username) AS name,
    date_start,
    time_start,
    date_end,
    time_end,
    data_file,
    FALSE                                                                   AS is_confidential,
    s.comment,
    s.timestamp_create,
    s.timestamp_update,
    dc.id                                                                   AS id_contact,
    main_observer_id,
    place_id,
    study_id,
    creator_id,
    creator_id
  FROM dbchirogcra.sights_session s LEFT JOIN dbchirogcra.sights_dictmethod tm ON s.method_id = tm.id
    LEFT JOIN dicts_contact dc ON tm.contact = dc.code
    LEFT JOIN accounts_profile u ON s.main_observer_id = u.id;

/* Session other observer */
/* Réalisé directement via la fonction "Copy table to" de DataGrip */

/* Sighting */
/* Réalisé directement via la fonction "Copy table to" de DataGrip */
/* Mise à jour de la période de l'année */
UPDATE sights_sighting
SET period =
CASE WHEN extract(DOY FROM s.date_start) <= 60 OR extract(DOY FROM s.date_start) > 335
  THEN 'Hivernant'
WHEN extract(DOY FROM s.date_start) > 60 AND extract(DOY FROM s.date_start) <= 136
  THEN 'Transit printanier'
WHEN extract(DOY FROM s.date_start) > 136 AND extract(DOY FROM s.date_start) <= 228
  THEN 'Estivage'
WHEN extract(DOY FROM s.date_start) > 228 AND extract(DOY FROM s.date_start) <= 335
  THEN 'Transit automnal' END
FROM sights_session s
WHERE session_id = s.id_session;

/* CountDetail */
/* Réalisé directement via la fonction "Copy table to" de DataGrip */
INSERT INTO public.sights_countdetail (
  id_countdetail,
  count,
  time,
  ab,
  d5,
  d3,
  pouce,
  queue,
  tibia,
  pied,
  cm3,
  tragus,
  poids,
  etat_sexuel,
  comment,
  timestamp_create,
  timestamp_update,
  age_id,
  chinspot_id,
  created_by_id,
  device_id,
  epididyme_id,
  epiphyse_id,
  gestation_id,
  gland_coul_id,
  gland_taille_id,
  mamelle_id,
  manipulator_id,
  precision_id,
  sex_id,
  sighting_id,
  testicule_id,
  tuniq_vag_id,
  unit_id,
  usure_dent_id,
  validator_id,
  method_id
)
  SELECT
    id_countdetail,
    count,
    time,
    ab,
    d5,
    d3,
    pouce,
    queue,
    tibia,
    pied,
    cm3,
    tragus,
    poids,
    etat_sexuel,
    c.comment,
    c.timestamp_create,
    c.timestamp_update,
    age_id,
    chinspot_id,
    c.creator_id,
    device_id,
    epididyme_id,
    epiphyse_id,
    gestation_id,
    gland_coul_id,
    gland_taille_id,
    mamelle_id,
    manipulator_id,
    precision_id,
    sex_id,
    sighting_id,
    testicule_id,
    tuniq_vag_id,
    unit_id,
    usure_dent_id,
    validator_id,
    sights_session.method_id
  FROM dbchirogcra.sights_countdetail c
    LEFT JOIN dbchirogcra.sights_sighting ON sighting_id = id_sighting
    LEFT JOIN dbchirogcra.sights_session ON session_id = id_session;


/* Tree */
INSERT INTO sights_tree (
  id_tree,
  visit_date,
  situation,
  tree_diameter,
  comment,
  timestamp_create,
  timestamp_update,
  circumstance_id,
  context_id,
  forest_stands_id,
  place_id,
  tree_specie_id,
  created_by_id,
  health_id)
  SELECT
    id_placetreedetail,
    visit_date,
    situation,
    tree_diameter,
    comment,
    timestamp_create,
    timestamp_update,
    circumstance_id,
    context_id,
    forest_stands_id,
    place_id,
    tree_specie_id,
    creator_id,
    CASE WHEN state LIKE 'alive'
      THEN 1
    WHEN state LIKE 'dead'
      THEN 2
    ELSE NULL END AS health_id
  FROM dbchirogcra.sights_placetreedetail;

SELECT max(id_tree)
FROM sights_tree;
ALTER SEQUENCE sights_tree_id_tree_seq RESTART WITH 40;

/* TreeGite */

INSERT INTO sights_treegite (gite_high,
                             gite_tree_diameter,
                             gite_access_orientation,
                             gite_access_size,
                             timestamp_create,
                             timestamp_update,
                             gite_localisation_id,
                             gite_origin_id,
                             gite_type_id,
                             tree_id,
                             created_by_id,
                             updated_by_id) SELECT
                                              gite_high,
                                              gite_tree_diameter,
                                              gite_access_orientation,
                                              gite_access_size,
                                              timestamp_create,
                                              timestamp_update,
                                              gite_localisation_id,
                                              gite_origin_id,
                                              gite_type_id,
                                              id_placetreedetail,
                                              creator_id,
                                              CASE WHEN timestamp_update != timestamp_create
                                                THEN creator_id
                                              ELSE NULL END
                                            FROM dbchirogcra.sights_placetreedetail;
SELECT max(id_treegite)
FROM sights_treegite;
ALTER SEQUENCE sights_treegite_id_treegite_seq RESTART WITH 40;


/* Mise à jour des séquences erronnées du fait d'une importation manuelle */

SELECT *
FROM sights_countdetail_id_countdetail_seq;
SELECT max(id_place)
FROM sights_place;
ALTER SEQUENCE sights_place_id_place_seq RESTART WITH 27222;
SELECT *
FROM sights_place_id_place_seq;
ALTER SEQUENCE sights_session_id_session_seq RESTART WITH 404;
SELECT max(id_session)
FROM dbchirodb.public.sights_session;
SELECT max(id_sighting)
FROM sights_sighting;
SELECT last_value
FROM sights_sighting_id_sighting_seq;
ALTER SEQUENCE sights_sighting_id_sighting_seq RESTART WITH 558;
SELECT last_value
FROM accounts_profile_id_seq;
SELECT max(id)
FROM accounts_profile;
ALTER SEQUENCE accounts_profile_id_seq RESTART WITH 1674;
SELECT max(id_study)
FROM management_study;
ALTER SEQUENCE management_study_id_study_seq RESTART WITH 6;
SELECT max(id_tree)
FROM sights_tree;

/* Transmitter */
/* Réalisé directement via la fonction "Copy table to" de DataGrip */

/* Cave */
/* Réalisé directement via la fonction "Copy table to" de DataGrip */

/* Bridge */
/* Réalisé directement via la fonction "Copy table to" de DataGrip */

/* Build */
INSERT INTO sights_build (
  id_build,
  visit_date,
  cavity_front,
  attic,
  attic_access_id,
  bell_tower,
  bell_tower_screen,
  bell_tower_access_id,
  cover_id,
  cellar,
  cellar_access_id,
  ext_light,
  access_light,
  comment,
  timestamp_create,
  timestamp_update,
  created_by_id,
  updated_by_id,
  place_id
)
  SELECT
    id_placebuilddetail   AS id_build,
    visit_date,
    cavity_front,
    has_attic             AS attic,
    CASE WHEN attic_open IS TRUE
      THEN 1
    WHEN attic_obstruct
      THEN 3
    WHEN attic_open IS FALSE
      THEN 2
    ELSE NULL END         AS attic_access_id,
    has_bell_tower        AS bell_tower,
    bell_tower_screen,
    CASE WHEN bell_tower_access IS TRUE
      THEN 1
    WHEN bell_tower_access IS FALSE
      THEN 2
    ELSE NULL END         AS bell_tower_access_id,
    CASE WHEN cover LIKE 'Tuile r%'
      THEN 1
    WHEN cover LIKE 'Tuile'
      THEN 2
    ELSE NULL END         AS cover_id,
    has_cellar            AS cellar,
    CASE WHEN cellar_accessible IS TRUE
      THEN 1
    WHEN cellar_accessible IS FALSE
      THEN 2
    ELSE NULL END         AS cellar_acces_id,
    ext_light,
    access_light,
    comment,
    timestamp_create,
    timestamp_update,
    creator_id            AS created_by_id,
    CASE WHEN timestamp_update != timestamp_create
      THEN creator_id END AS updated_by_id,
    place_id
  FROM dbchirogcra.sights_placebuilddetail;