DO $$
DECLARE
 version_new_ CONSTANT integer := 20;-- new version number --
 ver_ integer;
BEGIN
  LOCK TABLE variables;
  SELECT version_is('Ls') INTO ver_;
  IF ver_ < version_new_ THEN
    -- begin update --

-- Table: object_log_info


CREATE TABLE object_log_info
(
  _id bigserial,
  _object integer NOT NULL,
  hours_start timestamp with time zone NOT NULL,
  hours_end timestamp with time zone NOT NULL,
  last_trunc timestamp with time zone,
  last_clean timestamp with time zone,
  CONSTRAINT object_log_info_pkey PRIMARY KEY (_id),
  CONSTRAINT object_log_info__object_fkey FOREIGN KEY (_object)
      REFERENCES object (_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT object_log_info__object_key UNIQUE (_object)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE object_log_info
  OWNER TO su;
GRANT ALL ON TABLE object_log_info TO su;
GRANT SELECT, UPDATE ON TABLE object_log_info TO usr;

-- Index: object_log_info__object_idx

-- DROP INDEX object_log_info__object_idx;

CREATE INDEX object_log_info__object_idx
  ON object_log_info
  USING btree
  (_object);

-- Index: public.object_log_info_last_clean_idx

-- DROP INDEX public.object_log_info_last_clean_idx;

CREATE INDEX object_log_info_last_clean_idx
  ON public.object_log_info
  USING btree
  (last_clean);

-- Index: public.object_log_info_last_trunc_idx

-- DROP INDEX public.object_log_info_last_trunc_idx;

CREATE INDEX object_log_info_last_trunc_idx
  ON public.object_log_info
  USING btree
  (last_trunc);

-- Function: object_log_trunc_hours(integer, integer)

-- DROP FUNCTION object_log_trunc_hours(integer, integer);

CREATE OR REPLACE FUNCTION object_log_trunc_hours(
    object_ integer,
    period_hours_ integer)
  RETURNS void AS
$BODY$
DECLARE
  start_period_time_ timestamp with time zone;
  end_period_time_ timestamp with time zone;
BEGIN

  SELECT hours_end FROM object_log_info 
    WHERE _object=object_ INTO start_period_time_;
  end_period_time_ := date_trunc('hours', now() - interval '1 hour' * period_hours_);

  IF (start_period_time_ IS NOT NULL) THEN
   INSERT INTO object_log_hours(_object, period_hour, thread_name, work_name, 
     total_time, circles, work_time, longest_work, log_count)
   (SELECT _object, date_trunc('hours', period_start) AS period_hour, thread_name, work_name, 
     SUM(total_time)/12, SUM(circles)/12, SUM(work_time)/12, MAX(longest_work), COUNT(_id)
     FROM object_log 
     WHERE _object=object_ AND period_start >= start_period_time_ AND period_start < end_period_time_ 
     GROUP BY _object, period_hour, thread_name, work_name
     ORDER BY period_hour, thread_name, work_name
   );
   DELETE FROM object_log 
     WHERE _object=object_ AND period_start >= start_period_time_ AND period_start < end_period_time_;
   UPDATE object_log_info 
     SET hours_end=end_period_time_, last_trunc = now() 
     WHERE _object=object_;
  ELSE
   INSERT INTO object_log_hours(_object, period_hour, thread_name, work_name, 
     total_time, circles, work_time, longest_work, log_count)
   (SELECT _object, date_trunc('hours', period_start) AS period_hour, thread_name, work_name, 
     SUM(total_time)/12, SUM(circles)/12, SUM(work_time)/12, MAX(longest_work), COUNT(_id)
     FROM object_log 
     WHERE _object=object_ AND period_start < end_period_time_ 
     GROUP BY _object, period_hour, thread_name, work_name
     ORDER BY period_hour, thread_name, work_name
   );
   DELETE FROM object_log WHERE _object=object_ AND period_start < end_period_time_;
   INSERT INTO object_log_info(_object, hours_start, hours_end)
     (SELECT _object, MIN(period_hour), MAX(period_hour) FROM object_log_hours 
        WHERE _object=object_ GROUP BY _object
     );
   UPDATE object_log_info 
     SET hours_end=end_period_time_, last_trunc = now()
     WHERE _object=object_;
  END IF;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1000;
ALTER FUNCTION object_log_trunc_hours(integer, integer)
  OWNER TO su;

-- Function: object_log_clean_hours(integer, integer)

-- DROP FUNCTION object_log_clean_hours(integer, integer);

CREATE OR REPLACE FUNCTION object_log_clean_hours(
    object_ integer,
    period_hours_ integer)
  RETURNS void AS
$BODY$
DECLARE
  start_period_time_ timestamp with time zone;
  end_period_time_ timestamp with time zone;
BEGIN

  SELECT hours_start FROM object_log_info 
    WHERE _object=object_ INTO start_period_time_;
  end_period_time_ := date_trunc('hours', now() - interval '1 hour' * period_hours_);

  IF (start_period_time_ IS NOT NULL) THEN
   DELETE FROM object_log_hours 
     WHERE _object=object_ AND period_hour >= start_period_time_ AND period_hour < end_period_time_;
   UPDATE object_log_info SET hours_start=end_period_time_, last_clean = now()
     WHERE _object=object_;
  END IF;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1000;
ALTER FUNCTION object_log_clean_hours(integer, integer)
  OWNER TO su;

INSERT INTO object_type (_id, name, descr) 
  VALUES (9, 'cle', 'Clean log service');
INSERT INTO object(_id, _otype, guid, name, descr, revision, uri, status) 
  VALUES (9, 9, 'cle', 'Clean log service', 'Clean log service default', 0, '', 0);
INSERT INTO object_connection(_omaster, _oslave, type) 
  VALUES (0, 9, 0);

INSERT INTO object_settings_type(_otype, key, name, descr, type, min_value, max_value) 
  VALUES (9, 'TruncHours', 'Truncate hours', 'Truncate logs to hours (late than defined hours)', 'int', '168', '8760');
INSERT INTO object_settings_type(_otype, key, name, descr, type, min_value, max_value) 
  VALUES (9, 'CleanHours', 'Clean hours', 'Clean logs (late than defined hours)', 'int', '720', '43800');
INSERT INTO object_settings_type(_otype, key, name, descr, type, min_value, max_value) 
  VALUES (9, 'TruncPeriodMs', 'Truncate period', 'Truncate in period', 'time_period', '1 h', '24 h');
INSERT INTO object_settings_type(_otype, key, name, descr, type, min_value, max_value) 
  VALUES (9, 'CleanPeriodMs', 'Clean period', 'Clean in period', 'time_period', '1 h', '48 h');
INSERT INTO object_settings_type(_otype, key, name, descr, type, min_value, max_value) 
  VALUES (9, 'ObjectTypeList', 'Object type list', 'Types of objects to process', 'string', '', '');

INSERT INTO object_settings(_object, key, value) 
  VALUES (9, 'TruncHours', '720');
INSERT INTO object_settings(_object, key, value) 
  VALUES (9, 'CleanHours', '8760');
INSERT INTO object_settings(_object, key, value) 
  VALUES (9, 'TruncPeriodMs', '7200000');
INSERT INTO object_settings(_object, key, value) 
  VALUES (9, 'CleanPeriodMs', '43200000');
INSERT INTO object_settings(_object, key, value) 
  VALUES (9, 'ObjectTypeList', '2,3,4,5');

    -- end update --
    PERFORM version_set('Ls', version_new_);
  END IF;
END$$;