-- select * from dicts_contact;

/*
id	code	descr
1	vv	Vu en visuel
2	vm	Vu en main
3	du	Contact acoustique
4	te	Télémétrie
5	ca	Cadavre récent > vm
6	ro	Restes osseux > vm
7	gu	Guano > vv
8	cr	Cri audible > vv
9	tr	Traces (charnier, urine, etc.) > vv
10	nc	Inconnu
 */

-- select * from dicts_method;
/*
id	code	contact	descr
1	duan	du	Détection automatique en zero-crossing
2	duea	du	Détection automatique en temps réel
3	dudv	du	Détection en division de fréquence
4	duex	du	Détection expansion
5	duht	du	Détection hétérodyne
6	teaz	te	Télémétrie croisement d'azimut
7	tegi	te	Télémétrie recherche de gîte
8	tehi	te	Télémétrie Homing-In, recherche émetteur
9	vmfi	vm	Capture au filet
10	vmht	vm	Capture Harp-Trap
11	vmma	vm	Capture à la main
12	vvam	vv	Amplificateur de lumière, vision nocturne
13	vves	vv	Comptage estimé (effectif moyen)
14	vvma	vv	Lecture d'un marquage (bague, num., couleur)
15	vvmi	vv	Comptage minimal, partiel, non exhaustif
16	vvnc	vv	Non comptés
17	vvph	vv	Comptage sur photo
18	vvpp	vv	Piège photo
19	vvsg	vv	Comptage en sortie de gîte
20	vvsu	vv	Estimation selon surface
21	vvvu	vv	Comptage précis à vue
*/

INSERT INTO dicts_method VALUES
  (22, 'vvtr', 'vv', 'Traces (charnier, urine, etc.)'),
  (23, 'vvcr', 'vv', 'Cris audibles'),
  (24, 'vvgu', 'vv', 'Guano'),
  (25, 'vmca', 'vm', 'Cadavre récent'),
  (26, 'vmro', 'ro', 'Restes osseux');

/* Mise à jour des données existantes */

/* ca */
UPDATE sights_countdetail
SET method_id = 25 FROM sights_sighting, sights_session
WHERE contact_id = 5 AND sighting_id = sights_sighting.id_sighting AND
      sights_sighting.session_id = sights_session.id_session;

/* ro */

UPDATE sights_countdetail
SET method_id = 26 FROM sights_sighting, sights_session
WHERE contact_id = 6 AND sighting_id = sights_sighting.id_sighting AND
      sights_sighting.session_id = sights_session.id_session;

/* ro */

UPDATE sights_countdetail
SET method_id = 26 FROM sights_sighting, sights_session
WHERE contact_id = 6 AND sighting_id = sights_sighting.id_sighting AND
      sights_sighting.session_id = sights_session.id_session;
/* ro */

UPDATE sights_countdetail
SET method_id = 26 FROM sights_sighting, sights_session
WHERE contact_id = 6 AND sighting_id = sights_sighting.id_sighting AND
      sights_sighting.session_id = sights_session.id_session;
/* ro */

UPDATE sights_countdetail
SET method_id = 26 FROM sights_sighting, sights_session
WHERE contact_id = 6 AND sighting_id = sights_sighting.id_sighting AND
      sights_sighting.session_id = sights_session.id_session;

/* Test de sélection des données */

SELECT
  c2.code,
  count(*)
FROM sights_session sse LEFT JOIN dicts_contact c2 ON sse.contact_id = c2.id
WHERE contact_id IN (5, 6, 7, 8, 9)
GROUP BY c2.code;

SELECT
  dc.code,
  scd.method_id,
  count(*)
FROM sights_countdetail scd LEFT JOIN sights_sighting ssi ON scd.sighting_id = ssi.id_sighting
  LEFT JOIN sights_session sse ON sse.id_session = ssi.session_id
  LEFT JOIN dicts_contact dc ON sse.contact_id = dc.id
WHERE contact_id IN (5, 6, 7, 8, 9)
GROUP BY dc.code, scd.method_id;

SELECT
  c2.code,
  count(*)
FROM sights_sighting
  LEFT JOIN sights_session ss ON sights_sighting.session_id = ss.id_session
  LEFT JOIN dicts_contact c2 ON ss.contact_id = c2.id
  LEFT JOIN sights_countdetail sc ON sights_sighting.id_sighting = sc.sighting_id
WHERE contact_id IN (5, 6, 7, 8, 9)
GROUP BY c2.code;

BEGIN;
SAVEPOINT check_update_ro;
UPDATE sights_countdetail
SET method_id = 26 FROM sights_sighting, sights_session
WHERE contact_id = 6 AND sighting_id = sights_sighting.id_sighting AND
      sights_sighting.session_id = sights_session.id_session;
SELECT DISTINCT
  method_id,
  count(*)
FROM sights_countdetail
GROUP BY method_id;
ROLLBACK TO check_update_ro;
COMMIT;

SELECT DISTINCT
  method_id,
  count(*)
FROM sights_countdetail
GROUP BY method_id;


