-- DROP FUNCTION log_insert(integer, text, text, text, text);
-- DROP VIEW logview;
-- DROP TABLE log;
-- DROP TABLE logsubject;
-- DROP TABLE loguser;
-- DROP TABLE userprofile;

-- Table: userprofile

CREATE TABLE userprofile
(
  userprofileid serial NOT NULL,
  username text,
  fullname text,
  CONSTRAINT userprofile_pkey PRIMARY KEY (userprofileid ),
  CONSTRAINT userprofile_username_unique UNIQUE (username )
)
WITH (
  OIDS=FALSE
);

INSERT INTO userprofile(username, fullname)
    VALUES ('user1', 'fullname');

-- Table: loguser

CREATE TABLE loguser
(
  luid serial NOT NULL,
  userprofileid bigint NOT NULL,
  ip text NOT NULL,
  manager text,
  CONSTRAINT pkey_loguser PRIMARY KEY (luid ),
  CONSTRAINT fkey_userprofile FOREIGN KEY (userprofileid)
      REFERENCES userprofile (userprofileid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
-- Table: logsubject

CREATE TABLE logsubject
(
  sid integer NOT NULL,
  subject text NOT NULL,
  CONSTRAINT pkey_logsubject PRIMARY KEY (sid)
)
WITH (
  OIDS=FALSE
);

INSERT INTO logsubject(sid, subject) VALUES (0, 'None');
INSERT INTO logsubject(sid, subject) VALUES (1, 'Update');
INSERT INTO logsubject(sid, subject) VALUES (2, 'UpdateError');
INSERT INTO logsubject(sid, subject) VALUES (3, 'UpdateSuccess');

-- Table: log

CREATE TABLE log
(
  logtime timestamp without time zone NOT NULL DEFAULT now(),
  sid integer NOT NULL,
  luid bigint NOT NULL,
  log text,
  CONSTRAINT fkey_logsubject FOREIGN KEY (sid)
      REFERENCES logsubject (sid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkey_loguser FOREIGN KEY (luid)
      REFERENCES loguser (luid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

-- View: logview

CREATE OR REPLACE VIEW logview AS 
  SELECT log.logtime, sid as subject, logsubject.subject as subject_text, loguser.ip, userprofile.username, loguser.manager, log.log
   FROM log
   JOIN logsubject USING (sid)
   JOIN loguser USING (luid)
   left JOIN userprofile USING (userprofileid)
  ORDER BY log.logtime DESC
 LIMIT 1000;

-- Function: log_insert(integer, text, text, text, text)

CREATE OR REPLACE FUNCTION log_insert(sub integer, client_ip text, uname text, man text, logdata text)
  RETURNS integer AS
$BODY$BEGIN
IF man ISNULL
THEN
insert into log (sid, luid, log) 
select sub, luid, logdata from loguser
join userprofile using (userprofileid)
where loguser.ip = client_ip and username = uname and manager isnull;
ELSE
insert into log (sid, luid, log) 
select sub, luid, logdata from loguser
join userprofile using (userprofileid)
where loguser.ip = client_ip and username = uname and manager = man;
END IF;
IF FOUND 
THEN return 1;
END IF;
insert into loguser (userprofileid, ip, manager)
select userprofileid, client_ip, man from userprofile 
where username = uname and (userprofileid, client_ip, man) not in (select userprofileid, ip, manager from loguser);

IF man ISNULL
THEN
insert into log (sid, luid, log) 
select sub, luid, logdata from loguser
join userprofile using (userprofileid)
where loguser.ip = client_ip and username = uname and manager isnull;
ELSE
insert into log (sid, luid, log) 
select sub, luid, logdata from loguser
join userprofile using (userprofileid)
where loguser.ip = client_ip and username = uname and manager = man;
END IF;
IF FOUND 
THEN 
  return 1;
ELSE
  return 0;
END IF; 
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

