CREATE TABLE $schema.users (
id VARCHAR(36) NOT NULL PRIMARY KEY,
username VARCHAR(255)
);
CREATE TABLE $schema.groups (
id VARCHAR(36) NOT NULL PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE $schema.members (
id VARCHAR(36) NOT NULL PRIMARY KEY,
user_id VARCHAR(36),
group_id VARCHAR(36),
CONSTRAINT user_fk FOREIGN KEY(user_id) REFERENCES $schema.users(id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT group_fk FOREIGN KEY(group_id) REFERENCES $schema.groups(id) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE $schema.$resource$_shares (
member_id VARCHAR(36) NOT NULL,
resource_id BIGINT NOT NULL,
action VARCHAR(255) NOT NULL,
CONSTRAINT thread_share PRIMARY KEY (member_id, resource_id, action),
CONSTRAINT thread_share_member_fk FOREIGN KEY(member_id) REFERENCES $schema.members(id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE FUNCTION $schema.merge_users(key VARCHAR, data VARCHAR) RETURNS VOID AS
$$
BEGIN
LOOP
UPDATE $schema.users SET username = data WHERE id = key;
IF found THEN
RETURN;
END IF;
BEGIN
INSERT INTO $schema.users(id,username) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION $schema.insert_users_members() RETURNS TRIGGER AS
$$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO $schema.members (id, user_id) VALUES (NEW.id, NEW.id);
RETURN NEW;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION $schema.insert_groups_members() RETURNS TRIGGER AS
$$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO $schema.members (id, group_id) VALUES (NEW.id, NEW.id);
RETURN NEW;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER users_trigger
AFTER INSERT ON $schema.users
FOR EACH ROW EXECUTE PROCEDURE $schema.insert_users_members();
CREATE TRIGGER groups_trigger
AFTER INSERT ON actualites.groups
FOR EACH ROW EXECUTE PROCEDURE $schema.insert_groups_members();

CREATE TYPE $schema.share_tuple as (member_id VARCHAR(36), action VARCHAR(255));