/* Localités manquantes */
SELECT *
FROM bdchironalpesra.datas_localites dl
WHERE NOT EXISTS
(SELECT id_bdsource
 FROM sights_place sp
 WHERE dl.id_loc = id_bdsource);

INSERT INTO sights_place (
  name,
  is_hidden,
  is_gite,
  is_managed,
  proprietary,
  convention,
  habitat,
  altitude,
  id_bdcavite,
  comment,
  bdsource,
  id_bdsource,
  x,
  y,
  geom,
  timestamp_update,
  timestamp_create,
  domain_id,
  municipality_id,
  territory_id,
  precision_id,
  type_id,
  other_imported_data,
  created_by_id)
  SELECT
    dl.localite                                                          AS name,
    dl.site_confidentiel                                                 AS is_hidden,
    CASE WHEN dl.type_gite IS NOT NULL
      THEN TRUE
    ELSE FALSE END                                                       AS is_gite,
    dl.site_gere                                                         AS is_managed,
    NULL                                                                 AS proprietary,
    FALSE                                                                AS convention,
    dl.commentaires_milieu                                               AS habitat,
    dl.alt                                                               AS altitude,
    dl.id_bdcavite                                                       AS id_bdcavite,
    CASE WHEN notes IS NULL AND acces IS NULL
      THEN NULL
    WHEN notes IS NULL AND acces IS NOT NULL
      THEN acces
    WHEN notes IS NOT NULL AND acces IS NULL
      THEN notes
    WHEN notes IS NOT NULL AND acces IS NOT NULL
      THEN concat(notes, CHR(10), acces)
    END                                                                  AS comment,
    'bdchironalpes'                                                      AS bdsource,
    id_loc                                                               AS id_bdsource,
    st_x(st_transform(st_setsrid(st_makepoint(dl.x, dl.y), 2154), 4326)) AS x,
    st_y(st_transform(st_setsrid(st_makepoint(dl.x, dl.y), 2154), 4326)) AS y,
    st_transform(st_setsrid(st_makepoint(dl.x, dl.y), 2154), 4326)       AS geom,
    now()                                                                AS timestamp_update,
    now()                                                                AS timestamp_create,
    CASE WHEN dl.foncier LIKE 'Particulier'
      THEN 3
    WHEN dl.foncier IN ('Etat', 'Conseil départemental 07', 'Commune')
      THEN 2
    ELSE NULL END                                                        AS domain_id,
    gm.id                                                                AS municipality_id,
    gt.id                                                                AS territory_id,
    CASE dl.precision_xy
    WHEN 4
      THEN 1
    WHEN 3
      THEN 2
    WHEN 2
      THEN 4
    ELSE 4 END                                                           AS precision_id,
    dtp.id                                                               AS type_id,
    json_build_object(
        'foncier', dl.foncier,
        'contact', dl.contact,
        'pont_numDDE', dl."pont_numDDE",
        'pont_numCORA', dl."pont_numCORA",
        'pont_typevoie', dl.pont_typevoie,
        'pont_obstacle', dl.pont_obstacle,
        'pont_route', dl.pont_route,
        'pont_creationdate', dl.pont_creationdate,
        'pont_date1erevisite', dl.pont_date1erevisite)                   AS other_imported_data,
    2                                                                    AS created_by_id
  FROM bdchironalpesra.datas_localites dl LEFT JOIN dicts_typeplace dtp ON lower(dl.type_gite) = dtp.code
    LEFT JOIN geodata_territory gt ON st_within(st_transform(st_setsrid(st_makepoint(dl.x, dl.y), 2154), 4326), gt.geom)
    LEFT JOIN geodata_municipality gm
      ON st_within(st_transform(st_setsrid(st_makepoint(dl.x, dl.y), 2154), 4326), gm.geom)
  WHERE NOT EXISTS
  (SELECT id_bdsource
   FROM sights_place sp
   WHERE dl.id_loc = id_bdsource) AND st_makepoint(dl.x, dl.y) IS NOT NULL;

/* Identification des données de croisements d'azimuth téléétrique */
UPDATE sights_place
SET telemetric_crossaz = TRUE
WHERE "name" LIKE '%TEAZ-%';

update sights_place set is_managed=True where id_place in (select distinct place_id from sights_placemanagement);

/* Profils utilisateurs */

UPDATE accounts_profile a
SET id_bdsource = o.code FROM bdchironalpesra.datas_observateurs o
WHERE (a.first_name, a.last_name) = (o.prenom, o.nom);

UPDATE accounts_profile a
SET bdsource = 'bdchironalpes'
WHERE ID_BDSOURCE LIKE 'OBS%';

INSERT INTO accounts_profile (
  password,
  is_superuser,
  username,
  first_name,
  last_name,
  email,
  is_staff,
  is_active,
  date_joined,
  is_resp,
  access_all_data,
  edit_all_data,
  id_bdsource,
  bdsource,
  timestamp_create,
  timestamp_update,
  created_by_id)
  SELECT
    'none'               AS password,
    FALSE                AS is_superuser,
    CASE WHEN replace(replace(concat(substring(lower(CASE WHEN prenom IS NULL
      THEN '0'
                                                     ELSE prenom END), 1, 1), lower(nom)), ' ', ''), '-', '') IN
              (SELECT username
               FROM accounts_profile)
      THEN concat(replace(replace(concat(substring(lower(CASE WHEN prenom IS NULL
        THEN '0'
                                                         ELSE prenom END), 1, 1), lower(nom)), ' ', ''), '-', ''), 2)
    ELSE replace(replace(concat(substring(lower(CASE WHEN prenom IS NULL
      THEN '0'
                                                ELSE prenom END), 1, 1), lower(nom)), ' ', ''), '-',
                 '') END AS username,
    CASE WHEN prenom IS NULL
      THEN '0'
    ELSE prenom END      AS first_name,
    nom                  AS last_name,
    'example@domain.tld' AS email,
    FALSE                AS is_staff,
    FALSE                AS is_active,
    now()                AS date_joined,
    FALSE                AS is_resp,
    FALSE                AS access_alldata,
    FALSE                AS edit_alldata,
    oo.code              AS id_bdsource,
    'bdchironalpes'      AS bdsource,
    now()                AS ts_create,
    now()                AS ts_update,
    2                    AS created_by_id
  FROM bdchironalpesra.datas_observateurs oo
  WHERE NOT EXISTS
  (SELECT id_bdsource
   FROM accounts_profile ap
   WHERE (ap.first_name, ap.last_name) = (oo.prenom, oo.nom)) AND nom_complet NOT LIKE 'ANONYME';

SELECT
  id,
  username,
  first_name,
  last_name,
  o.prenom,
  o.nom,
  o.code,
  bdsource,
  id_bdsource
FROM
  accounts_profile a LEFT JOIN bdchironalpesra.datas_observateurs o ON (a.first_name, a.last_name) = (o.prenom, o.nom)
WHERE o.code IS NULL;


SELECT *
FROM sights_session;

WITH codes AS (SELECT DISTINCT
                 substring(prenom, 1, 1) || substring(nom, 1, 2) code_user,
                 nom_complet,
                 code,
                 count(dd.*) AS                                  nbobs
               FROM
                 bdchironalpesra.datas_observateurs oo LEFT JOIN bdchironalpesra.datas dd
                   ON (dd.observ_1 = oo.nom_complet OR dd.observ_2 = oo.nom_complet)
               WHERE SUBSTRING(prenom, 1, 1) ||
                     SUBSTRING(nom, 1, 2) IN (SELECT DISTINCT SUBSTRING(id_data, 1, 3)
                                              FROM bdchironalpesra.datas)
               GROUP BY substring(prenom, 1, 1) || substring(nom, 1, 2),
                 nom_complet
               ORDER BY substring(prenom, 1, 1) || substring(nom, 1, 2),
                 count(dd.*) DESC)
SELECT
  row_number()
  OVER (
    PARTITION BY code_user ) AS rang,
  *
FROM codes;

/* Création des sessions depuis la table datas */

SELECT *
FROM public.sights_session;

DELETE FROM sights_session
WHERE id_bdsource LIKE '{%';

SELECT max(id_session)
FROM dbchirodb.public.sights_session;
ALTER SEQUENCE sights_session_id_session_seq RESTART WITH 404;
SELECT last_value
FROM
  sights_session_id_session_seq;
INSERT INTO sights_session (
  name,
  is_confidential,
  date_start,
  contact_id,
  id_bdsource,
  bdsource,
  comment,
  timestamp_create,
  timestamp_update,
  main_observer_id,
  place_id,
  created_by_id)
  SELECT DISTINCT
    concat('loc',
           sp.id_place,
           '_',
           make_date(an, mois, jour), '_',
           coalesce(dc.code, 'nc'))                   AS name,
    FALSE                                             AS is_confidential,
    make_date(an, mois, jour)                         AS date_start,
    coalesce(dc.id, '10')                             AS contact_id,
    array_agg(id_data)                                AS id_bdsource,
    'bdchironalpes'                                   AS bdsource,
    'Généré depuis les observations de bdchironalpes' AS comment,
    now()                                             AS timestamp_create,
    now()                                             AS timestamp_update,
    CASE WHEN (array_agg(DISTINCT ap.id)) [1] IS NOT NULL
      THEN (array_agg(DISTINCT ap.id)) [1]
    ELSE 704 END                                      AS main_observer_id,
    sp.id_place                                       AS place_id,
    2                                                 AS created_by_id
  FROM bdchironalpesra.datas od
    LEFT JOIN sights_place sp ON od.id_loc = sp.id_bdsource
    LEFT JOIN bdchironalpesra.datas_observateurs oo ON od.observ_1 = oo.nom_complet
    LEFT JOIN accounts_profile ap ON (ap.first_name, ap.last_name) = (oo.prenom, oo.nom)
    LEFT JOIN dicts_contact dc ON replace(lower(contact), ' ', '') = dc.code
  WHERE make_date(an, mois, jour) IS NOT NULL AND sp.id_place IS NOT NULL
  GROUP BY
    make_date(an, mois, jour), coalesce(dc.id, '10'), coalesce(dc.code, 'nc'), sp.id_place;


/* Création des observations depuis la table datas */
DELETE FROM sights_sighting
WHERE bdsource = 'bdchironalpes';


SELECT max(id_sighting)
FROM sights_sighting;
ALTER SEQUENCE sights_sighting_id_sighting_seq RESTART WITH 558;
INSERT INTO sights_sighting (
  period,
  total_count,
  breed_colo,
  is_doubtful,
  id_bdsource,
  bdsource,
  comment,
  timestamp_create,
  timestamp_update,
  codesp_id,
  observer_id,
  session_id,
  created_by_id)
  SELECT DISTINCT
    CASE WHEN extract(DOY FROM make_date(an, mois, jour)) <= 60 OR extract(DOY FROM make_date(an, mois, jour)) > 335
      THEN 'Hivernant'
    WHEN extract(DOY FROM make_date(an, mois, jour)) > 60 AND extract(DOY FROM make_date(an, mois, jour)) <= 136
      THEN 'Transit printanier'
    WHEN extract(DOY FROM make_date(an, mois, jour)) > 136 AND extract(DOY FROM make_date(an, mois, jour)) <= 228
      THEN 'Estivage'
    WHEN extract(DOY FROM make_date(an, mois, jour)) > 228 AND extract(DOY FROM make_date(an, mois, jour)) <= 335
      THEN 'Transit automnal' END      AS period,
    sum(od.n :: INT)                   AS total_count,
    bool_or(od.colo_repro)             AS breed_colo,
    bool_or(od.douteux)                AS is_doubtful,
    array_agg(od.id_data)              AS id_bdsource,
    'bdchironalpes'                    AS bdsource,
    string_agg(od.commentaires, ' / ') AS comment,
    now()                              AS timestamp_create,
    now()                              AS timestamp_update,
    (array_agg(ds.id)) [1] :: INT      AS codesp_id,
    CASE WHEN (array_agg(ap.id)) [1] IS NOT NULL
      THEN (array_agg(ap.id)) [1]
    ELSE 704 END                       AS observer_id,
    ss.id_session                      AS session_id,
    2                                  AS created_by_id
  FROM bdchironalpesra.datas od
    LEFT JOIN sights_place sp ON od.id_loc = sp.id_bdsource
    LEFT JOIN bdchironalpesra.datas_observateurs oo ON od.observ_1 = oo.nom_complet
    LEFT JOIN accounts_profile ap ON (ap.first_name, ap.last_name) = (oo.prenom, oo.nom)
    LEFT JOIN dicts_contact dc ON lower(contact) = dc.code
    LEFT JOIN sights_session ss ON ss.name = concat('loc',
                                                    sp.id_place,
                                                    '_',
                                                    make_date(an, mois, jour), '_',
                                                    coalesce(dc.code, 'nc'))
    LEFT JOIN dicts_specie ds ON replace(lower(od.codesp), ' ', '') LIKE ds.codesp
  WHERE make_date(an, mois, jour) IS NOT NULL AND sp.id_place IS NOT NULL
  --         AND ss.id_session = 2949
  GROUP BY
    make_date(an, mois, jour), coalesce(dc.id, '10'), sp.id_place, ds.codesp, ss.id_session,
    ds.id;


/* CountDetails */
/* Step 1 biometry */
SELECT
  heure :: TIME,
  *
FROM bdchironalpesra.datas_bio db
WHERE heure IS NOT NULL;

DELETE FROM sights_countdetail
WHERE bdsource = 'bdchironalpes';

SELECT max(id_countdetail)
FROM sights_countdetail;
SELECT last_value
FROM sights_countdetail_id_countdetail_seq;
ALTER SEQUENCE sights_countdetail_id_countdetail_seq RESTART WITH 1032;

INSERT INTO sights_countdetail (
  sighting_id,
  method_id,
  time,
  etat_sexuel,
  sex_id,
  age_id,
  ab,
  poids,
  d3,
  d5,
  pouce,
  pied,
  tibia,
  cm3,
  comment,
  other_imported_data,
  timestamp_create,
  timestamp_update,
  manipulator_id,
  created_by_id,
  id_fromsrc,
  bdsource
)
  WITH correspsightdata AS (
      SELECT
        id_sighting,
        unnest(id_bdsource :: TEXT []) AS id_data,
        observer_id
      FROM sights_sighting
      WHERE id_bdsource LIKE '{%')
  SELECT
    id_sighting                                         AS sighting_id,
    dm.id                                               AS method_id,
    db.heure :: TIME                                    AS time,
    db.repro                                            AS etat_sexuel,
    CASE db.sexe
    WHEN 'F'
      THEN 1
    WHEN 'M'
      THEN 2
    WHEN 'FM'
      THEN 3
    WHEN NULL
      THEN 4 END                                        AS sex_id,
    CASE db.age
    WHEN 'A'
      THEN 3
    WHEN 'AJ'
      THEN 4
    WHEN 'I'
      THEN 2
    WHEN 'J'
      THEN 1
    WHEN 'V'
      THEN 5 END                                        AS age_id,
    "AB"                                                AS ab,
    "P"                                                 AS poids,
    "D3"                                                AS d3,
    "D5"                                                AS d5,
    pc                                                  AS pouce,
    pied                                                AS pied,
    tibia                                               AS tibia,
    "CM3"                                               AS cm3,
    CASE WHEN qualitatif IS NULL AND db.commentaires IS NULL
      THEN NULL
    WHEN qualitatif IS NULL AND db.commentaires IS NOT NULL
      THEN db.commentaires
    WHEN qualitatif IS NOT NULL AND db.commentaires IS NULL
      THEN qualitatif
    ELSE concat(qualitatif, ' | ', db.commentaires) END AS comment,
    json_build_object(
        'mesures_poignet', mesures_poignet,
        'pc', pc,
        'griffe', griffe,
        'long_oreille', "Long_oreille",
        'larg_oreille', "larg_oreille",
        'long_tragus', "Long_tragus")                   AS other_import_data,
    now()                                               AS timestamp_create,
    now()                                               AS timestamp_update,
    observer_id                                         AS observer_id,
    2                                                   AS creator,
    id_data_bio                                         AS id_bdsource,
    'bdcbironalpes'                                     AS bdsource
  FROM bdchironalpesra.datas_bio db LEFT JOIN correspsightdata s ON s.id_data = db.id_data
    LEFT JOIN bdchironalpesra.datas d ON db.id_data = d.id_data
    LEFT JOIN dicts_method dm ON lower(d.methode) = dm.code
  WHERE id_sighting IS NOT NULL;

/* step2 data_eff */
INSERT INTO sights_countdetail (
  sighting_id,
  method_id,
  etat_sexuel,
  sex_id,
  age_id,
  count,
  timestamp_create,
  timestamp_update,
  created_by_id,
  id_fromsrc,
  bdsource
)
  WITH correspsightdata AS (
      SELECT
        id_sighting,
        unnest(id_bdsource :: TEXT []) AS id_data,
        observer_id
      FROM sights_sighting
      WHERE id_bdsource LIKE '{%')
  SELECT
    id_sighting     AS sighting_id,
    dm.id           AS method_id,
    db.repro        AS etat_sexuel,
    CASE db.sexe
    WHEN 'F'
      THEN 1
    WHEN 'M'
      THEN 2
    WHEN 'FM'
      THEN 3
    WHEN NULL
      THEN 4 END    AS sex_id,
    CASE db.age
    WHEN 'A'
      THEN 3
    WHEN 'AJ'
      THEN 4
    WHEN 'I'
      THEN 2
    WHEN 'J'
      THEN 1
    WHEN 'V'
      THEN 5 END    AS age_id,
    db.n            AS count,
    now()           AS timestamp_create,
    now()           AS timestamp_update,
    2               AS creator,
    id_data_eff     AS id_bdsource,
    'bdcbironalpes' AS bdsource
  FROM bdchironalpesra.datas_eff db LEFT JOIN correspsightdata s ON s.id_data = db.id_data
    LEFT JOIN bdchironalpesra.datas d ON db.id_data = d.id_data
    LEFT JOIN dicts_method dm ON lower(d.methode) = dm.code
  WHERE id_sighting IS NOT NULL;
VACUUM sights_countdetail;

/* Site géré */
TRUNCATE  sights_placemanagement;
insert into sights_placemanagement (date, action_id, comment, timestamp_create, timestamp_update, referent_id, place_id, created_by_id)
SELECT
  case when dsg.date_gestion is null then '2000-01-01'
    else
    date(dsg.date_gestion) end as date,
  dpm.id,
  concat(
      'commentaire: ', coalesce(comm_gestion,'-'), '<br />',
      'Type de protection: ',coalesce(type_protection,'-'), '<br />',
      'Signataires de convention: ',coalesce(signataires_convention,'-')) as comment,
  now() as timestamp_create,
  now() as timestamp_update,
  704 as referent_id,
  sp.id_place as place_id,
  2 as created_by
FROM
  bdchironalpesra.datas_sites_geres dsg LEFT JOIN dicts_placemanagementaction dpm ON lower(dsg.type_gestion) = dpm.code
  LEFT JOIN sights_place sp ON dsg.id_loc = sp.id_bdsource;

select * from sights_place where id_place in (select place_id from sights_placemanagement);

update sights_sighting ss set timestamp_create = se.date_start from sights_session se where ss.session_id = se.id_session and ss.bdsource like 'bdchironalpes';


