CREATE TABLE sistema.t_chat
(
  id serial NOT NULL,
  "from" character varying,
  "to" character varying,
  message text,
  sent timestamp without time zone,
  recd integer NOT NULL DEFAULT 0,
  CONSTRAINT pkey_chat_id PRIMARY KEY (id)
)
WITHOUT OIDS;


CREATE TYPE sistema.d_chat AS
   (id integer,
    "from" character varying,
    "to" character varying,
    message text,
    sent timestamp without time zone,
    recd integer);
    
CREATE OR REPLACE FUNCTION sistema.f_chat_insert(p_from character varying, p_to character varying, p_msj character varying)
  RETURNS boolean AS
$BODY$
/* New function body */
begin
insert into sistema.t_chat ("from","to",message,sent) values ($1,$2 ,$3,now());
return 't';
end
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;


CREATE OR REPLACE FUNCTION sistema.f_chat_list()
  RETURNS SETOF sistema.d_diccionario AS
$BODY$
declare
 r sistema.d_diccionario;

BEGIN

for r in
select u.idusuario as item , cuenta as descripcion from sistema.t_usuarios u
inner join (
	select max(fechahora) as f_ini,idusuario from sistema.t_logs where  
	mensaje  ilike '%inicia%' and (date_part('day',( fechahora - now()))=0 and date_part('hour',( fechahora - now()))=0 and date_part('minutes',( fechahora - now()))<-0.1) group by idusuario
) ini on
ini.idusuario=u.idusuario
left join (
	select max(fechahora) as f_fin,idusuario from sistema.t_logs where  
	mensaje ilike '%termina%' and (date_part('day',( fechahora - now()))=0 and date_part('hour',( fechahora - now()))=0 and date_part('minutes',( fechahora - now()))< -1) group by idusuario
) ter on
ter.idusuario=u.idusuario 
--where f_ini > f_fin

loop
    return next r;
end loop;

end

$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;


CREATE OR REPLACE FUNCTION sistema.f_chat_select(p_username character varying)
  RETURNS SETOF sistema.d_chat AS
$BODY$
/* New function body */

select * from sistema.t_chat c where (c.to like $1 AND c.recd = 0) order by c.id ASC

$BODY$
  LANGUAGE 'sql' VOLATILE SECURITY DEFINER;
  
CREATE OR REPLACE FUNCTION sistema.f_chat_update(p_username character varying)
  RETURNS boolean AS
$BODY$
/* New function body */
begin
update sistema.t_chat set recd = 1 where t_chat.to like p_username and recd = 0;
return 't';
end
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

  
INSERT INTO "sistema"."t_eventos" ("idevento", "evento", "descripcion", "idsistema", "seteable", "permiso", "navegable")
VALUES (1100, 'chatheartbeat', 'CHAT - recivir', 1, 0, 1100, 0);

INSERT INTO "sistema"."t_eventos" ("idevento", "evento", "descripcion", "idsistema", "seteable", "permiso", "navegable")
VALUES (1101, 'sendchat', 'CHAT - enviar', 1, 0, 1100, 0);

INSERT INTO "sistema"."t_eventos" ("idevento", "evento", "descripcion", "idsistema", "seteable", "permiso", "navegable")
VALUES (1102, 'closechat', 'CHAT - close', 1, 0, 1100, 0);

INSERT INTO "sistema"."t_eventos" ("idevento", "evento", "descripcion", "idsistema", "seteable", "permiso", "navegable")
VALUES (1103, 'startchatsession', 'CHAT - iniciar', 1, 1, 1100, 0);