-- NODES --

-- cette table récupère l'ancienne version des nodes deleted
-- Cette table est vidée à la fin de la mise à jour d'un changeset
-- par la fonction osmosisUpdate
CREATE TABLE nodes_deleted AS TABLE nodes WITH NO DATA;

CREATE TABLE public.nodes_history
(
    gid SERIAL PRIMARY KEY,
    id bigint NOT NULL,
    version integer NOT NULL,
    user_id integer NOT NULL,
    tstamp timestamp without time zone NOT NULL,
    changeset_id bigint NOT NULL,
    tags hstore,
    visible boolean DEFAULT TRUE,
    geom geometry(Point,4326)
);

-- durant le processus de mise à jour des ways, la première passe
-- met à jour les tags et les noeuds, et le second met à jour
-- la géométrie. Cette table contient les mises à jour et seule la dernière
-- version est conservée.
CREATE TABLE nodes_history_temp AS TABLE nodes WITH NO DATA;
ALTER TABLE nodes_history_temp ADD CONSTRAINT nodes_history_temp_unique_id UNIQUE(id);

CREATE OR REPLACE FUNCTION save_deleted_nodes() RETURNS trigger AS
$$BEGIN
   INSERT INTO nodes_deleted (id, version, user_id, tstamp, changeset_id, tags, geom)
     VALUES (OLD.id, OLD.version, OLD.user_id, OLD.tstamp, OLD.changeset_id, OLD.tags, OLD.geom);
   RETURN OLD;
END;$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION save_update_nodes() RETURNS trigger AS
$$BEGIN
  -- insert only the first updated version on same update. Subsequent updates will be alteration of geometry or other
  -- stuff dones by osmosis. We want to keep only the first version, which was the previous valid one in the table
  -- nodes.
  INSERT INTO nodes_history_temp (id, version, user_id, tstamp, changeset_id, tags, geom)
    VALUES (OLD.id, OLD.version, OLD.user_id, OLD.tstamp, OLD.changeset_id, OLD.tags, OLD.geom)
    ON CONFLICT ON CONSTRAINT nodes_history_temp_unique_id 
      DO NOTHING
    ;
  RETURN NEW;
END;$$ LANGUAGE plpgsql;



DROP TRIGGER IF EXISTS  save_deleted_nodes_tg ON nodes;
CREATE TRIGGER save_deleted_nodes_tg
    BEFORE DELETE ON nodes
    FOR EACH ROW
    EXECUTE PROCEDURE save_deleted_nodes();

-- old name for the trigger. keep until first release
DROP TRIGGER IF EXISTS save_modif_nodes_tg ON nodes;
DROP TRIGGER IF EXISTS save_update_nodes_tg ON nodes;
CREATE TRIGGER save_update_nodes_tg
    BEFORE UPDATE ON nodes
    FOR EACH ROW
    EXECUTE PROCEDURE save_update_nodes();

-- WAYS --

-- cette table récupère l'ancienne version des ways deleted
-- Cette table est vidée à la fin de la mise à jour d'un changeset
-- par la fonction osmosisUpdate
CREATE TABLE ways_deleted AS TABLE ways WITH NO DATA;

CREATE TABLE public.ways_history
(
    gid SERIAL PRIMARY KEY,
    id bigint NOT NULL,
    version integer NOT NULL,
    user_id integer NOT NULL,
    tstamp timestamp without time zone NOT NULL,
    changeset_id bigint NOT NULL,
    tags hstore,
    visible boolean DEFAULT TRUE,
    nodes bigint[],
    bbox geometry(Geometry,4326),
    linestring geometry(Geometry,4326)
);

-- durant le processus de mise à jour des ways, la première passe
-- met à jour les tags et les noeuds, et le second met à jour
-- la géométrie. Cette table contient les mises à jour et seule la dernière
-- version est conservée.
CREATE TABLE ways_history_temp AS TABLE ways WITH NO DATA;
ALTER TABLE ways_history_temp ADD CONSTRAINT ways_history_temp_unique_id UNIQUE(id);

CREATE OR REPLACE FUNCTION save_deleted_ways() RETURNS trigger AS
$$BEGIN
   INSERT INTO ways_deleted (id, version, user_id, tstamp, changeset_id, tags, nodes, bbox, linestring)
     VALUES (OLD.id, OLD.version, OLD.user_id, OLD.tstamp, OLD.changeset_id, OLD.tags, OLD.nodes, OLD.bbox, OLD.linestring);
   RETURN OLD;
END;$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION save_update_ways() RETURNS trigger AS
$$BEGIN
  -- insert only the first updated version on same update. Subsequent updates will be alteration of geometry or other
  -- stuff dones by osmosis. We want to keep only the first version, which was the previous valid one in the table
  -- ways.
  INSERT INTO ways_history_temp (id, version, user_id, tstamp, changeset_id, tags, nodes, bbox, linestring)
    VALUES (OLD.id, OLD.version, OLD.user_id, OLD.tstamp, OLD.changeset_id, OLD.tags, OLD.nodes, OLD.bbox, OLD.linestring)
    ON CONFLICT ON CONSTRAINT ways_history_temp_unique_id 
      DO NOTHING
    ;
  RETURN NEW;
END;$$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS  save_deleted_ways_tg ON ways;
CREATE TRIGGER save_deleted_ways_tg
    BEFORE DELETE ON ways
    FOR EACH ROW
    EXECUTE PROCEDURE save_deleted_ways();

-- previous name of the trigger 
-- keep for developers until first release
DROP TRIGGER IF EXISTS  save_modif_ways_tg ON ways;
DROP TRIGGER IF EXISTS  save_update_ways_tg ON ways;
CREATE TRIGGER save_update_ways_tg
    BEFORE UPDATE ON ways
    FOR EACH ROW
    EXECUTE PROCEDURE save_update_ways();

-- RELATIONS --
-- cette table récupère l'ancienne version des relations deleted
-- Cette table est vidée à la fin de la mise à jour d'un changeset
-- par la fonction osmosisUpdate
CREATE TABLE relations_deleted AS TABLE relations WITH NO DATA;

-- During osmosis update, multiple UPDATE queries may hit the same 
-- element on the table relation.
-- This table contains the version before the first update.
CREATE TABLE relations_history_temp AS TABLE relations WITH NO DATA;
ALTER TABLE relations_history_temp ADD CONSTRAINT relations_history_temp_unique_id UNIQUE(id);

CREATE TABLE public.relations_history
(
    gid SERIAL PRIMARY KEY,
    id bigint NOT NULL,
    version integer NOT NULL,
    user_id integer NOT NULL,
    tstamp timestamp without time zone NOT NULL,
    changeset_id bigint NOT NULL,
    tags hstore,
    visible boolean DEFAULT TRUE
);

CREATE OR REPLACE FUNCTION save_deleted_relations() RETURNS trigger AS
$$BEGIN
  -- insert only the first updated version on same update. Subsequent updates will be alteration of geometry or other
  -- stuff dones by osmosis. We want to keep only the first version, which was the previous valid one in the table
  -- relations.
  INSERT INTO relations_history_temp (id, version, user_id, tstamp, changeset_id, tags)
    VALUES (OLD.id, OLD.version, OLD.user_id, OLD.tstamp, OLD.changeset_id, OLD.tags)
    ON CONFLICT ON CONSTRAINT relations_history_temp_unique_id 
      DO NOTHING
    ;
   RETURN OLD;
END;$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION save_update_relations() RETURNS trigger AS
$$BEGIN
  INSERT INTO relations_history (id, version, user_id, tstamp, changeset_id, tags)
    VALUES (OLD.id, OLD.version, OLD.user_id, OLD.tstamp, OLD.changeset_id, OLD.tags);
  RETURN NEW;
END;$$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS save_deleted_relations_tg ON relations;
CREATE TRIGGER save_deleted_relations_tg
    BEFORE DELETE ON relations
    FOR EACH ROW
    EXECUTE PROCEDURE save_deleted_relations();

-- old name for the trigger. keep until first release
DROP TRIGGER IF EXISTS save_modif_relations_tg ON relations;
DROP TRIGGER IF EXISTS save_update_relations_tg ON relations;
CREATE TRIGGER save_update_relations_tg
    BEFORE UPDATE ON relations
    FOR EACH ROW
    EXECUTE PROCEDURE save_update_relations();


-- ACTIONS --
-- enregistre les modifications effectuées
CREATE TABLE public.actions_history
(
    gid SERIAL PRIMARY KEY,
    data_type character(1) COLLATE pg_catalog."default" NOT NULL,
    action character(1) COLLATE pg_catalog."default" NOT NULL,
    action_time TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    id bigint NOT NULL
);

-- cette fonction est exécutée par osmosis avant de terminer
-- l'import d'un changeset. 
CREATE OR REPLACE FUNCTION public.osmosisupdate(
	)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE

AS $BODY$
DECLARE
BEGIN
  -- insère les noeuds créés ou modifiés
  INSERT INTO public.actions_history (data_type, action, id, action_time)
    SELECT data_type, action, id, NOW() FROM public.actions;

  -- copy the record from ways_history_temp to ways_history
  INSERT INTO ways_history (id, version, user_id, tstamp, changeset_id, tags, visible, nodes, bbox, linestring)
    SELECT id, version, user_id, tstamp, changeset_id, tags, TRUE, nodes, bbox, linestring FROM ways_history_temp;
  DELETE FROM ways_history_temp;
  -- copy the record from nodes_history_temp to nodes_history
  INSERT INTO nodes_history (id, version, user_id, tstamp, changeset_id, tags, visible, geom)
    SELECT id, version, user_id, tstamp, changeset_id, tags, TRUE, geom FROM nodes_history_temp;
  DELETE FROM nodes_history_temp;
  -- copy the record from relations_history_temp to relations_history
  INSERT INTO relations_history (id, version, user_id, tstamp, changeset_id, tags, visible)
    SELECT id, version, user_id, tstamp, changeset_id, tags, TRUE FROM relations_history_temp;
  DELETE FROM relations_history_temp;
  
END;
$BODY$;
