Reset guided tours

The commands to reset the tours for users are either:

Reset tour for a specific user

DELETE FROM public.guided_tour_progress WHERE user_id = '<UUID>';

Reset tour for all users

DELETE FROM public.guided_tour_progress;

Complete tour for a specific user

INSERT INTO public.guided_tour_progress (user_id, step_key)
SELECT
  '<TARGET_USER_UUID>'::uuid,
  step_key
FROM (
  SELECT DISTINCT step_key
  FROM public.guided_tour_progress
) s
ON CONFLICT (user_id, step_key) DO NOTHING;

For this to work at least one user has to have completed the guided tour before.
If the user the tour should be completed for is the first user to ever complete it then the step keys which are defined in GuidedTourStepIds.ts in the frontend have to be used in the insert.

Unpause tour for a specific user

UPDATE public.guided_tour_states SET pause_count = 0 WHERE user_id = '<UUID>';
or
DELETE FROM public.guided_tour_states WHERE user_id = '<UUID>';

Unpause tour for all users

UPDATE public.guided_tour_states SET pause_count = 0;
or
DELETE FROM public.guided_tour_states;

Statistics for maps

Get the number of plantings per map

SELECT m.id, count(*) AS "number of platings" FROM plantings pl JOIN layers l ON pl.layer_id = l.id JOIN maps m ON l.map_id = m.id GROUP BY m.id;

Get the number of plantings per map for current day

SELECT m.id, count(*) AS "number of platings" FROM plantings pl JOIN layers l ON pl.layer_id = l.id JOIN maps m ON l.map_id = m.id WHERE pl.add_date <= CURRENT_DATE AND pl.remove_date >= CURRENT_DATE GROUP BY m.id;

Get the number of plantings per map for current year

SELECT m.id, count(*) AS "number of platings" FROM plantings pl JOIN layers l ON pl.layer_id = l.id JOIN maps m ON l.map_id = m.id WHERE date_part('year', pl.add_date) <= date_part('year', CURRENT_DATE) AND date_part('year', pl.remove_date) >= date_part('year', CURRENT_DATE) GROUP BY m.id;

Get the number of plantings per map that where modified in the last 30 days

SELECT m.id, count(*) AS "number of platings modified in the last 30 days" FROM plantings pl JOIN layers l ON pl.layer_id = l.id JOIN maps m ON l.map_id = m.id WHERE pl.modified_at >= now() - interval '30 day' GROUP BY m.id;

Get number of different kinds of plantings per map

SELECT m.id, count(DISTINCT pl.seed_id) AS "number of different plantings" FROM plantings pl JOIN layers l ON pl.layer_id = l.id JOIN maps m ON l.map_id = m.id GROUP BY m.id;

Get number of drawings per map

SELECT m.id, count(*) AS "number of drawings" FROM drawings d JOIN layers l ON d.layer_id = l.id JOIN maps m ON l.map_id = m.id GROUP BY m.id;

Get number of shadings per map

SELECT m.id, count(*) AS "number of shadings" FROM shadings s JOIN layers l ON s.layer_id = l.id JOIN maps m ON l.map_id = m.id GROUP BY m.id;

Get number of soil textures per map

SELECT m.id, count(*) AS "number of soil_textures" FROM soil_textures st JOIN layers l ON st.layer_id = l.id JOIN maps m ON l.map_id = m.id GROUP BY m.id;

Get number of hydrologies per map

SELECT m.id, count(*) AS "number of hydrologies" FROM hydrologies h JOIN layers l ON h.layer_id = l.id JOIN maps m ON l.map_id = m.id GROUP BY m.id;