 DROP TABLE IF EXISTS changes;
 DROP TABLE IF EXISTS changesets;
 
--- Create a table for changes.
-CREATE TABLE changes (
-    id bigserial NOT NULL,
-    user_id int NOT NULL,
-    version int NOT NULL,
-    changeset_id bigint NOT NULL,
-    tstamp timestamp without time zone NOT NULL,
-    action character varying(10) NOT NULL,
-    element_type character varying(10) NOT NULL,
-    element_id bigint NOT NULL,
-    old_tags hstore,
-    new_tags hstore,
-    old_geom geometry,
-    new_geom geometry
-);
+DROP TYPE IF EXISTS element_type;
+CREATE TYPE element_type AS ENUM ('N', 'W', 'R');
+
+DROP TYPE IF EXISTS action;
+CREATE TYPE action AS ENUM ('CREATE', 'MODIFY', 'DELETE');
+
+DROP TYPE IF EXISTS change_type;
+CREATE TYPE change_type AS ENUM ('NEW', 'DELETE', 'CHANGE_GEOM', 'CHANGE_TAGS');
 
 -- Create a table for changesets.
 CREATE TABLE changesets (
-  id bigserial NOT NULL,
-  user_id bigint NOT NULL,
+  id bigserial PRIMARY KEY,
+  user_id bigint NOT NULL REFERENCES users,
   created_at timestamp without time zone NOT NULL,
   closed_at timestamp without time zone NOT NULL,
   num_changes integer NOT NULL DEFAULT 0,

  geom geometry
);

ALTER TABLE ONLY changes ADD CONSTRAINT pk_changes PRIMARY KEY (id);
ALTER TABLE ONLY changesets ADD CONSTRAINT pk_changesets PRIMARY KEY (id);

DROP INDEX IF EXISTS idx_changes_changeset_id;
CREATE INDEX idx_changes_changeset_id ON changes USING btree (changeset_id);

  SELECT st_collect(g.geom)
  FROM
  (
    SELECT (ST_Dump(geom)).geom FROM changes WHERE changeset_id = $1 AND el_type = 'Way'
    UNION
    SELECT (ST_Dump(new_geom)).geom FROM changes WHERE changeset_id = $1 AND el_type = 'Way'
    UNION
    SELECT ST_Boundary(ST_Buffer(geom, 0.00000001, 1)) FROM changes WHERE changeset_id = $1 AND el_type = 'Node'
    UNION
    SELECT ST_Boundary(ST_Buffer(new_geom, 0.00000001, 1)) FROM changes WHERE changeset_id = $1 AND el_type = 'Node'
  ) g);

UPDATE changesets SET geom = changeset_geom WHERE id = $1;
