/*
    Table "user"
*/
CREATE TABLE "user" (
    id INTEGER NOT NULL,
    username CHARACTER VARYING(50) NOT NULL,
    password CHARACTER VARYING(256) NOT NULL
);

CREATE SEQUENCE user_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE "user" ALTER COLUMN id SET DEFAULT NEXTVAL('user_id_seq'::regclass);
ALTER TABLE ONLY "user" ADD CONSTRAINT user_pkey PRIMARY KEY (id);
ALTER TABLE ONLY "user" ADD CONSTRAINT user_username_key UNIQUE (username);

/*
    Table "log"
*/
CREATE TABLE log (
    userid INTEGER NOT NULL,
    registered TIMESTAMP WITHOUT TIME zone DEFAULT now() NOT NULL,
    lastvisit TIMESTAMP WITHOUT TIME zone NOT NULL,
    visits INTEGER DEFAULT 0 NOT NULL
);

ALTER TABLE ONLY log ADD CONSTRAINT log_pkey PRIMARY KEY (userid);
ALTER TABLE ONLY log ADD CONSTRAINT log_userid_fkey FOREIGN KEY (userid) REFERENCES "user"(id) ON DELETE CASCADE;

/*
    Table "groups"
*/
CREATE TABLE groups (
    id INTEGER NOT NULL,
    groupname CHARACTER VARYING(256) NOT NULL
);

CREATE SEQUENCE groups_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE groups ALTER COLUMN id SET DEFAULT NEXTVAL('groups_id_seq'::regclass);
ALTER TABLE ONLY groups ADD CONSTRAINT groups_groupname_key UNIQUE (groupname);
ALTER TABLE ONLY groups ADD CONSTRAINT groups_pkey PRIMARY KEY (id);

/*
    Table "user_groups"
*/
CREATE TABLE user_groups (
    userid INTEGER NOT NULL,
    groupid INTEGER NOT NULL
);

ALTER TABLE ONLY user_groups ADD CONSTRAINT user_groups_userid_key UNIQUE (userid, groupid);
ALTER TABLE ONLY user_groups ADD CONSTRAINT user_groups_userid_fkey FOREIGN KEY (userid) REFERENCES "user"(id) ON DELETE CASCADE;
ALTER TABLE ONLY user_groups ADD CONSTRAINT user_groups_groupid_fkey FOREIGN KEY (groupid) REFERENCES "groups"(id) ON DELETE CASCADE;

/*
    Table "user_data"
*/
CREATE TABLE user_data (
    userid INTEGER NOT NULL,
    firstname CHARACTER VARYING(256) NOT NULL,
    lastname CHARACTER VARYING(256) NOT NULL,
    email CHARACTER VARYING(256) NOT NULL
);

ALTER TABLE ONLY user_data ADD CONSTRAINT user_data_email_key UNIQUE (email);
ALTER TABLE ONLY user_data ADD CONSTRAINT user_data_pkey PRIMARY KEY (userid);
ALTER TABLE ONLY user_data ADD CONSTRAINT user_data_userid_fkey FOREIGN KEY (userid) REFERENCES "user"(id) ON DELETE CASCADE;