--pg_dump --dbname=cache --format=plain --schema-only -n 'corestatus' >/app/pg/backup/corestatus.sql
--
--


SELECT pg_catalog.set_config('search_path', '', false);

--
-- Name: corestatus; Type: SCHEMA; Schema: -; Owner: corestatus
--

CREATE SCHEMA corestatus;


ALTER SCHEMA corestatus OWNER TO corestatus;

--
-- Name: SCHEMA corestatus; Type: COMMENT; Schema: -; Owner: corestatus
--

COMMENT ON SCHEMA corestatus IS 'schema CORESTATUS';


--
-- Name: enum_action; Type: TYPE; Schema: corestatus; Owner: corestatus
--

CREATE TYPE corestatus.enum_action AS ENUM (
    'i',
    'u',
    'd'
);


ALTER TYPE corestatus.enum_action OWNER TO corestatus;

--
-- Name: TYPE enum_action; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON TYPE corestatus.enum_action IS 'Перелік можливих значень dml команд';




--
-- Name: system_module; Type: TABLE; Schema: corestatus; Owner: corestatus
--

CREATE TABLE corestatus.system_module (
    system_name text NOT NULL,
    module_name text NOT NULL,
    state boolean NOT NULL,
    start_time character varying(5),
    finish_time character varying(5),
    system_day date,
    next_system_day date
);


ALTER TABLE corestatus.system_module OWNER TO corestatus;

--
-- Name: TABLE system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON TABLE corestatus.system_module IS 'Таблиця доступності модулів облікових систем';


--
-- Name: COLUMN system_module.system_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.system_module.system_name IS 'облікова система';


--
-- Name: COLUMN system_module.module_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.system_module.module_name IS 'модуль облікової системи';


--
-- Name: COLUMN system_module.state; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.system_module.state IS 'ознака доступності';


--
-- Name: COLUMN system_module.start_time; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.system_module.start_time IS 'початок доступності бізнес-функціоналу';


--
-- Name: COLUMN system_module.finish_time; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.system_module.finish_time IS 'завершення доступності бізнес-функціоналу';


--
-- Name: COLUMN system_module.system_day; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.system_module.system_day IS 'поточна операційна дата';


--
-- Name: COLUMN system_module.next_system_day; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.system_module.next_system_day IS 'наступна операційна дата';


--
-- Name: get_info(text, text); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--

CREATE FUNCTION corestatus.get_info(i_system text, i_module text) RETURNS SETOF corestatus.system_module
    LANGUAGE sql SECURITY DEFINER
    SET search_path TO 'corestatus'
    AS $$select system_name, module_name, state, start_time, finish_time, system_day, next_system_day
                  from v_system_module
                 where system_name = coalesce(lower(i_system), system_name)
                   and module_name = coalesce(lower(i_module), module_name)$$;


ALTER FUNCTION corestatus.get_info(i_system text, i_module text) OWNER TO corestatus;

--
-- Name: FUNCTION get_info(i_system text, i_module text); Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON FUNCTION corestatus.get_info(i_system text, i_module text) IS 'повертає набір записів представлення v_system_module';


--
-- Name: sub_system_day_settings; Type: TABLE; Schema: corestatus; Owner: corestatus
--

CREATE TABLE corestatus.sub_system_day_settings (
    system_name text NOT NULL,
    current_day date NOT NULL,
    next_day date NOT NULL
);


ALTER TABLE corestatus.sub_system_day_settings OWNER TO corestatus;

--
-- Name: TABLE sub_system_day_settings; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON TABLE corestatus.sub_system_day_settings IS 'налаштування операційного дня облікової системи';


--
-- Name: COLUMN sub_system_day_settings.system_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_day_settings.system_name IS 'облікова система';


--
-- Name: COLUMN sub_system_day_settings.current_day; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_day_settings.current_day IS 'поточна операційна дата';


--
-- Name: COLUMN sub_system_day_settings.next_day; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_day_settings.next_day IS 'дата наступного операційного дня';


--
-- Name: get_system_days(text); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--

CREATE FUNCTION corestatus.get_system_days(i_system text) RETURNS corestatus.sub_system_day_settings
    LANGUAGE sql SECURITY DEFINER
    SET search_path TO 'corestatus'
    AS $$ select * from sub_system_day_settings where lower(system_name) = lower(i_system); $$;


ALTER FUNCTION corestatus.get_system_days(i_system text) OWNER TO corestatus;

--
-- Name: FUNCTION get_system_days(i_system text); Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON FUNCTION corestatus.get_system_days(i_system text) IS 'повертає запис таблиці sub_system_day_settings по заданій обліковій системі';


--
-- Name: sub_system_module; Type: TABLE; Schema: corestatus; Owner: corestatus
--

CREATE TABLE corestatus.sub_system_module (
    system_name text NOT NULL,
    module_name text NOT NULL,
    module_status text NOT NULL
);


ALTER TABLE corestatus.sub_system_module OWNER TO corestatus;

--
-- Name: TABLE sub_system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON TABLE corestatus.sub_system_module IS 'модулі облікових систем та їх статуси доступності';


--
-- Name: COLUMN sub_system_module.system_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_module.system_name IS 'облікова система';


--
-- Name: COLUMN sub_system_module.module_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_module.module_name IS 'модуль облікової системи';


--
-- Name: COLUMN sub_system_module.module_status; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_module.module_status IS 'статус модуля облікової системи';


--
-- Name: get_system_module(text, text); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--

CREATE FUNCTION corestatus.get_system_module(i_system text DEFAULT NULL::text, i_module text DEFAULT NULL::text) RETURNS SETOF corestatus.sub_system_module
    LANGUAGE sql SECURITY DEFINER
    SET search_path TO 'corestatus'
    AS $$
  select * from sub_system_module
   where system_name = coalesce(lower(i_system), system_name) and module_name = coalesce(lower(i_module), module_name);
$$;


ALTER FUNCTION corestatus.get_system_module(i_system text, i_module text) OWNER TO corestatus;

--
-- Name: FUNCTION get_system_module(i_system text, i_module text); Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON FUNCTION corestatus.get_system_module(i_system text, i_module text) IS 'повертає записи з таблиці sub_system_module по заданим параметрам';


--
-- Name: insert_sub_system_day_settings_log(text, text, date, date); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--

CREATE FUNCTION corestatus.insert_sub_system_day_settings_log(i_action text, i_system_name text, i_current_day date, i_next_day date) RETURNS void
    LANGUAGE sql SECURITY DEFINER
    SET search_path TO 'corestatus'
    AS $$
  insert into sub_system_day_settings_log
    values (i_action, lower(i_system_name), i_current_day, i_next_day, current_timestamp);
$$;


ALTER FUNCTION corestatus.insert_sub_system_day_settings_log(i_action text, i_system_name text, i_current_day date, i_next_day date) OWNER TO corestatus;

--
-- Name: FUNCTION insert_sub_system_day_settings_log(i_action text, i_system_name text, i_current_day date, i_next_day date); Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON FUNCTION corestatus.insert_sub_system_day_settings_log(i_action text, i_system_name text, i_current_day date, i_next_day date) IS 'добавляє запис в журнал змін по записам таблиці insert_sub_system_day_settings_log';


--
-- Name: insert_sub_system_module_log(text, text, text, text); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--

CREATE FUNCTION corestatus.insert_sub_system_module_log(i_action text, i_system_name text, i_module_name text, i_module_status text) RETURNS void
    LANGUAGE sql SECURITY DEFINER
    SET search_path TO 'corestatus'
    AS $$
  insert into sub_system_module_log values (
    i_action, i_system_name, i_module_name, i_module_status, current_timestamp
  );
$$;


ALTER FUNCTION corestatus.insert_sub_system_module_log(i_action text, i_system_name text, i_module_name text, i_module_status text) OWNER TO corestatus;

--
-- Name: FUNCTION insert_sub_system_module_log(i_action text, i_system_name text, i_module_name text, i_module_status text); Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON FUNCTION corestatus.insert_sub_system_module_log(i_action text, i_system_name text, i_module_name text, i_module_status text) IS 'функція добавлення запису в журнал зміни статусів модулів обілкових систем';


--
-- Name: jsonb_nullif_value(jsonb); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--

CREATE FUNCTION corestatus.jsonb_nullif_value(i_jsonb jsonb) RETURNS jsonb
    LANGUAGE sql
    AS $_$select jsonb_object(array_agg(key), array_agg(nullif(value, ''))) from jsonb_each_text($1)$_$;


ALTER FUNCTION corestatus.jsonb_nullif_value(i_jsonb jsonb) OWNER TO corestatus;

--
-- Name: FUNCTION jsonb_nullif_value(i_jsonb jsonb); Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON FUNCTION corestatus.jsonb_nullif_value(i_jsonb jsonb) IS 'не заповнені значення змінює на null';


--
-- Name: save_log(); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--

CREATE FUNCTION corestatus.save_log() RETURNS trigger
    LANGUAGE plpgsql
    SET search_path TO 'corestatus'
    AS $$declare
                        v_log system_module_log;
                begin
                        v_log.sync_time := current_timestamp;

                        if lower(tg_op) = 'insert' then
                                v_log.new_row := to_json(new.*);
                        elsif lower(tg_op) = 'update' then
                                v_log.old_row := to_json(old.*);
                                v_log.new_row := to_json(new.*);
                        elsif lower(tg_op) = 'delete' then
                                v_log.old_row := to_json(old.*);
                        else
                                return null;
                        end if;

                        insert into system_module_log select v_log.*;

                        return null;
                end$$;


ALTER FUNCTION corestatus.save_log() OWNER TO corestatus;

--
-- Name: FUNCTION save_log(); Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON FUNCTION corestatus.save_log() IS 'тригерна функція для логування змін по рядках таблиці system_module';


--
-- Name: sync(text); Type: PROCEDURE; Schema: corestatus; Owner: corestatus
--

CREATE PROCEDURE corestatus.sync(i_msg_text text)
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'corestatus'
    AS $$declare
                        v_jsonb jsonb;

                        v_action enum_action;
                        v_record system_module;
                begin
                        v_jsonb := i_msg_text;
                        v_jsonb := jsonb_nullif_value(v_jsonb);

                        v_action := v_jsonb ->> 'action';

                        v_record := jsonb_populate_record(null::system_module, v_jsonb);
                        v_record.system_name := lower(v_record.system_name);
                        v_record.module_name := lower(v_record.module_name);

                        if v_action = 'i' then
                                insert into system_module select v_record.*;
                        elsif v_action = 'u' then
                                update system_module
                                   set start_time = v_record.start_time, next_system_day = v_record.next_system_day,
                                           system_day = v_record.system_day, finish_time = v_record.finish_time, state = v_record.state
                                 where system_name = v_record.system_name
                                   and module_name = v_record.module_name;
                        elsif v_action = 'd' then
                                delete from system_module
                                 where system_name = v_record.system_name
                                   and module_name = v_record.module_name;
                        else
                                raise exception 'invalid input value for enum enum_action -> %', v_action;
                        end if;
                end$$;


ALTER PROCEDURE corestatus.sync(i_msg_text text) OWNER TO corestatus;

--
-- Name: PROCEDURE sync(i_msg_text text); Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON PROCEDURE corestatus.sync(i_msg_text text) IS 'аналізує текст вхідного повідомлення та виконує відповідну команду';


--
-- Name: traiud_sub_system_day_settings_do_log(); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--

CREATE FUNCTION corestatus.traiud_sub_system_day_settings_do_log() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'corestatus'
    AS $$
  begin
        perform insert_sub_system_day_settings_log(
                      i_action      => substr(tg_op, 1),
                          i_system_name => new.system_name,
                          i_current_day => new.current_day,
                          i_next_day    => new.next_day
                    );
          return null;
  end;
$$;


ALTER FUNCTION corestatus.traiud_sub_system_day_settings_do_log() OWNER TO corestatus;

--
-- Name: FUNCTION traiud_sub_system_day_settings_do_log(); Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON FUNCTION corestatus.traiud_sub_system_day_settings_do_log() IS 'допоміжна тригерна функція для тригера tr_log_sub_system_day_settings';


--
-- Name: traiud_sub_system_module_do_log(); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--

CREATE FUNCTION corestatus.traiud_sub_system_module_do_log() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'corestatus'
    AS $$
  begin
        perform insert_sub_system_module_log(
                      i_action        => substr(tg_op, 1, 1)::char,
                          i_system_name   => new.system_name,
                          i_module_name   => new.module_name,
                          i_module_status => new.module_status
                    );
        return null;
  end;
$$;


ALTER FUNCTION corestatus.traiud_sub_system_module_do_log() OWNER TO corestatus;

--
-- Name: FUNCTION traiud_sub_system_module_do_log(); Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON FUNCTION corestatus.traiud_sub_system_module_do_log() IS 'допоміжна тригерна функція для однойменного тригера';


--
-- Name: trau_b2_close_do_same_to_all(); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--

CREATE FUNCTION corestatus.trau_b2_close_do_same_to_all() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'corestatus'
    AS $$
  declare
        rec sub_system_module%rowtype;
  begin
        for rec in (select *
                     from sub_system_module
                    where system_name = new.system_name
                      and module_name <> 'b2')
        loop
          call upsert_sub_system_module(
                                i_system_name   => rec.system_name,
                                i_module_name   => rec.module_name,
                                i_module_status => new.module_status
                          );
        end loop;

        return null;
 end;
$$;


ALTER FUNCTION corestatus.trau_b2_close_do_same_to_all() OWNER TO corestatus;

--
-- Name: FUNCTION trau_b2_close_do_same_to_all(); Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON FUNCTION corestatus.trau_b2_close_do_same_to_all() IS 'допоміжна функція однойменного тригера';


--
-- Name: upsert_sub_system_day_settings(text, date, date); Type: PROCEDURE; Schema: corestatus; Owner: corestatus
--

CREATE PROCEDURE corestatus.upsert_sub_system_day_settings(i_system_name text, i_current_day date, i_next_day date)
    LANGUAGE sql SECURITY DEFINER
    SET search_path TO 'corestatus'
    AS $$
  insert into sub_system_day_settings values (lower(i_system_name), i_current_day, i_next_day)
          on conflict (system_name) do update
         set current_day = i_current_day, next_day = i_next_day;
$$;


ALTER PROCEDURE corestatus.upsert_sub_system_day_settings(i_system_name text, i_current_day date, i_next_day date) OWNER TO corestatus;

--
-- Name: PROCEDURE upsert_sub_system_day_settings(i_system_name text, i_current_day date, i_next_day date); Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON PROCEDURE corestatus.upsert_sub_system_day_settings(i_system_name text, i_current_day date, i_next_day date) IS 'добавляє або вносить зміни в існуючий запис таблиці sub_system_day_settings';


--
-- Name: upsert_sub_system_module(text, text, text); Type: PROCEDURE; Schema: corestatus; Owner: corestatus
--

CREATE PROCEDURE corestatus.upsert_sub_system_module(i_system_name text, i_module_name text, i_module_status text)
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'corestatus'
    AS $$
begin
  insert into sub_system_module values (lower(i_system_name), lower(i_module_name), lower(i_module_status))
          on conflict (system_name, module_name) do update
         set module_status = lower(i_module_status);
end; $$;


ALTER PROCEDURE corestatus.upsert_sub_system_module(i_system_name text, i_module_name text, i_module_status text) OWNER TO corestatus;

--
-- Name: PROCEDURE upsert_sub_system_module(i_system_name text, i_module_name text, i_module_status text); Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON PROCEDURE corestatus.upsert_sub_system_module(i_system_name text, i_module_name text, i_module_status text) IS 'добавляє або вносить зміни в існуючий запис таблиці sub_system_module';


--
-- Name: sub_system_day_settings_log; Type: TABLE; Schema: corestatus; Owner: corestatus
--

CREATE TABLE corestatus.sub_system_day_settings_log (
    action text,
    system_name text,
    current_day date,
    next_day date,
    sync_time timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE corestatus.sub_system_day_settings_log OWNER TO corestatus;

--
-- Name: TABLE sub_system_day_settings_log; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON TABLE corestatus.sub_system_day_settings_log IS 'журнал зміни операційних днів облікових систем';


--
-- Name: COLUMN sub_system_day_settings_log.action; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_day_settings_log.action IS 'dml команда';


--
-- Name: COLUMN sub_system_day_settings_log.system_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_day_settings_log.system_name IS 'облікова система';


--
-- Name: COLUMN sub_system_day_settings_log.current_day; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_day_settings_log.current_day IS 'поточний операційни день';


--
-- Name: COLUMN sub_system_day_settings_log.next_day; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_day_settings_log.next_day IS 'наступний операційни день';


--
-- Name: COLUMN sub_system_day_settings_log.sync_time; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_day_settings_log.sync_time IS 'дата та час виконання dml команди';


--
-- Name: sub_system_module_log; Type: TABLE; Schema: corestatus; Owner: corestatus
--

CREATE TABLE corestatus.sub_system_module_log (
    action text,
    system_name text,
    module_name text,
    module_status text,
    sync_time timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE corestatus.sub_system_module_log OWNER TO corestatus;

--
-- Name: TABLE sub_system_module_log; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON TABLE corestatus.sub_system_module_log IS 'журнал зміни статусів модулів облікових систем';


--
-- Name: COLUMN sub_system_module_log.action; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_module_log.action IS 'dml команда';


--
-- Name: COLUMN sub_system_module_log.system_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_module_log.system_name IS 'облікова система';


--
-- Name: COLUMN sub_system_module_log.module_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_module_log.module_name IS 'модуль облікової системи';


--
-- Name: COLUMN sub_system_module_log.module_status; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_module_log.module_status IS 'статус модуля облікової системи';


--
-- Name: COLUMN sub_system_module_log.sync_time; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.sub_system_module_log.sync_time IS 'дата та час виконання dml команди';


--
-- Name: system_module_log; Type: TABLE; Schema: corestatus; Owner: corestatus
--

CREATE TABLE corestatus.system_module_log (
    old_row json,
    new_row json,
    sync_time timestamp without time zone NOT NULL
);


ALTER TABLE corestatus.system_module_log OWNER TO corestatus;

--
-- Name: TABLE system_module_log; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON TABLE corestatus.system_module_log IS 'журнал доступності модуля зовнішніх систем';


--
-- Name: COLUMN system_module_log.old_row; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.system_module_log.old_row IS 'дані до внесення змін';


--
-- Name: COLUMN system_module_log.new_row; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.system_module_log.new_row IS 'дані після внесення змін';


--
-- Name: COLUMN system_module_log.sync_time; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON COLUMN corestatus.system_module_log.sync_time IS 'час виконання операції';


--
-- Name: v_system_module; Type: VIEW; Schema: corestatus; Owner: corestatus
--

CREATE VIEW corestatus.v_system_module AS
 SELECT m.system_name,
    m.module_name,
    m.start_time,
    m.finish_time,
    v.system_day,
    v.next_system_day,
    (((m.state)::integer * v.state))::boolean AS state
   FROM corestatus.system_module m,
    ( SELECT mb2.system_day,
            mb2.next_system_day,
            (mb2.state)::integer AS state
           FROM corestatus.system_module mb2
          WHERE ((mb2.system_name = 'b2'::text) AND (mb2.module_name = 'b2'::text))) v;


ALTER TABLE corestatus.v_system_module OWNER TO corestatus;

--
-- Name: VIEW v_system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON VIEW corestatus.v_system_module IS 'Уявллення доступності модулів облікових систем';


--
-- Name: sub_system_day_settings pk_sub_system_day_settings; Type: CONSTRAINT; Schema: corestatus; Owner: corestatus
--

ALTER TABLE ONLY corestatus.sub_system_day_settings
    ADD CONSTRAINT pk_sub_system_day_settings PRIMARY KEY (system_name);


--
-- Name: CONSTRAINT pk_sub_system_day_settings ON sub_system_day_settings; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON CONSTRAINT pk_sub_system_day_settings ON corestatus.sub_system_day_settings IS 'первинний ключ таблиці sub_system_day_settings';


--
-- Name: sub_system_module pk_sub_system_module; Type: CONSTRAINT; Schema: corestatus; Owner: corestatus
--

ALTER TABLE ONLY corestatus.sub_system_module
    ADD CONSTRAINT pk_sub_system_module PRIMARY KEY (system_name, module_name);


--
-- Name: CONSTRAINT pk_sub_system_module ON sub_system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON CONSTRAINT pk_sub_system_module ON corestatus.sub_system_module IS 'первинний ключ таблиці sub_system_module';


--
-- Name: system_module pk_system_module; Type: CONSTRAINT; Schema: corestatus; Owner: corestatus
--

ALTER TABLE ONLY corestatus.system_module
    ADD CONSTRAINT pk_system_module PRIMARY KEY (system_name, module_name);


--
-- Name: system_module on_delete_b2_instead_nothing; Type: RULE; Schema: corestatus; Owner: corestatus
--

CREATE RULE on_delete_b2_instead_nothing AS
    ON DELETE TO corestatus.system_module
   WHERE (old.module_name = 'b2'::text) DO INSTEAD NOTHING;


--
-- Name: RULE on_delete_b2_instead_nothing ON system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON RULE on_delete_b2_instead_nothing ON corestatus.system_module IS 'ігнорує видалення запису по модулю b2 з таблиці system_module';


--
-- Name: system_module on_delete_not_b2_also_delete_sub_system_module; Type: RULE; Schema: corestatus; Owner: corestatus
--

CREATE RULE on_delete_not_b2_also_delete_sub_system_module AS
    ON DELETE TO corestatus.system_module
   WHERE (old.module_name <> 'b2'::text) DO  DELETE FROM corestatus.sub_system_module
  WHERE ((sub_system_module.system_name = old.system_name) AND (sub_system_module.module_name = old.module_name));


--
-- Name: RULE on_delete_not_b2_also_delete_sub_system_module ON system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON RULE on_delete_not_b2_also_delete_sub_system_module ON corestatus.system_module IS 'після delete не ключового модуля Б2 в system_module видаляє запис в sub_system_module';


--
-- Name: system_module on_insert_also_insert_sub_system_day_settings; Type: RULE; Schema: corestatus; Owner: corestatus
--

CREATE RULE on_insert_also_insert_sub_system_day_settings AS
    ON INSERT TO corestatus.system_module
   WHERE (new.module_name = 'b2'::text) DO  INSERT INTO corestatus.sub_system_day_settings (system_name, current_day, next_day)
  VALUES (new.system_name, new.system_day, new.next_system_day) ON CONFLICT(system_name) DO UPDATE SET current_day = new.system_day, next_day = new.next_system_day;


--
-- Name: RULE on_insert_also_insert_sub_system_day_settings ON system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON RULE on_insert_also_insert_sub_system_day_settings ON corestatus.system_module IS 'після insert в system_module запису по модулю b2 оновняє дані в sub_system_day_settings';


--
-- Name: system_module on_insert_also_insert_sub_system_module; Type: RULE; Schema: corestatus; Owner: corestatus
--

CREATE RULE on_insert_also_insert_sub_system_module AS
    ON INSERT TO corestatus.system_module DO  INSERT INTO corestatus.sub_system_module (system_name, module_name, module_status)
  VALUES (new.system_name, new.module_name,
        CASE
            WHEN new.state THEN 'open'::text
            ELSE 'close'::text
        END) ON CONFLICT(system_name, module_name) DO UPDATE SET module_status =
        CASE
            WHEN new.state THEN 'open'::text
            ELSE 'close'::text
        END;


--
-- Name: RULE on_insert_also_insert_sub_system_module ON system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON RULE on_insert_also_insert_sub_system_module ON corestatus.system_module IS 'після insert в system_module оновняє дані в sub_system_module';


--
-- Name: system_module on_update_also_update_sub_system_day_settings; Type: RULE; Schema: corestatus; Owner: corestatus
--

CREATE RULE on_update_also_update_sub_system_day_settings AS
    ON UPDATE TO corestatus.system_module
   WHERE (new.module_name = 'b2'::text) DO  INSERT INTO corestatus.sub_system_day_settings (system_name, current_day, next_day)
  VALUES (new.system_name, new.system_day, new.next_system_day) ON CONFLICT(system_name) DO UPDATE SET current_day = new.system_day, next_day = new.next_system_day;


--
-- Name: RULE on_update_also_update_sub_system_day_settings ON system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON RULE on_update_also_update_sub_system_day_settings ON corestatus.system_module IS 'після update в system_module запису по модулю b2 оновлює дані sub_system_day_settings';


--
-- Name: system_module on_update_also_update_sub_system_module; Type: RULE; Schema: corestatus; Owner: corestatus
--

CREATE RULE on_update_also_update_sub_system_module AS
    ON UPDATE TO corestatus.system_module DO  INSERT INTO corestatus.sub_system_module (system_name, module_name, module_status)
  VALUES (new.system_name, new.module_name,
        CASE
            WHEN new.state THEN 'open'::text
            ELSE 'close'::text
        END) ON CONFLICT(system_name, module_name) DO UPDATE SET module_status =
        CASE
            WHEN new.state THEN 'open'::text
            ELSE 'close'::text
        END;


--
-- Name: RULE on_update_also_update_sub_system_module ON system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON RULE on_update_also_update_sub_system_module ON corestatus.system_module IS 'після update в system_module оновлює дані sub_system_module';


--
-- Name: system_module taiud_system_module; Type: TRIGGER; Schema: corestatus; Owner: corestatus
--

CREATE TRIGGER taiud_system_module AFTER INSERT OR DELETE OR UPDATE ON corestatus.system_module FOR EACH ROW EXECUTE FUNCTION corestatus.save_log();


--
-- Name: TRIGGER taiud_system_module ON system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON TRIGGER taiud_system_module ON corestatus.system_module IS 'відслідковує зміни по рядках таблиці system_module та ініціює процедуру логування';


--
-- Name: sub_system_day_settings traiud_sub_system_day_settings_do_log; Type: TRIGGER; Schema: corestatus; Owner: corestatus
--

CREATE TRIGGER traiud_sub_system_day_settings_do_log AFTER INSERT OR DELETE OR UPDATE ON corestatus.sub_system_day_settings FOR EACH ROW EXECUTE FUNCTION corestatus.traiud_sub_system_day_settings_do_log();


--
-- Name: TRIGGER traiud_sub_system_day_settings_do_log ON sub_system_day_settings; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON TRIGGER traiud_sub_system_day_settings_do_log ON corestatus.sub_system_day_settings IS 'відслідковує та фіксує зміни по записам таблиці sub_system_day_settings';


--
-- Name: sub_system_module traiud_sub_system_module_do_log; Type: TRIGGER; Schema: corestatus; Owner: corestatus
--

CREATE TRIGGER traiud_sub_system_module_do_log AFTER INSERT OR DELETE OR UPDATE ON corestatus.sub_system_module FOR EACH ROW EXECUTE FUNCTION corestatus.traiud_sub_system_module_do_log();


--
-- Name: TRIGGER traiud_sub_system_module_do_log ON sub_system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON TRIGGER traiud_sub_system_module_do_log ON corestatus.sub_system_module IS 'відслідковує зміни в записах таблиці sub_system_module і після цього викликає однойменну функцію ';


--
-- Name: sub_system_module trau_b2_close_do_same_to_all; Type: TRIGGER; Schema: corestatus; Owner: corestatus
--

CREATE TRIGGER trau_b2_close_do_same_to_all AFTER UPDATE ON corestatus.sub_system_module FOR EACH ROW WHEN (((new.module_name = 'b2'::text) AND (new.module_status = 'close'::text))) EXECUTE FUNCTION corestatus.trau_b2_close_do_same_to_all();


--
-- Name: TRIGGER trau_b2_close_do_same_to_all ON sub_system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--

COMMENT ON TRIGGER trau_b2_close_do_same_to_all ON corestatus.sub_system_module IS 'відслідковує зміну статусу модуля B2 на close та забезпечує наслідкову дію';


--
-- Name: SCHEMA corestatus; Type: ACL; Schema: -; Owner: corestatus
--

GRANT USAGE ON SCHEMA corestatus TO status_api;
GRANT USAGE ON SCHEMA corestatus TO cache;


--
-- Name: TABLE system_module; Type: ACL; Schema: corestatus; Owner: corestatus
--

GRANT SELECT,DELETE,UPDATE ON TABLE corestatus.system_module TO cache;


--
-- Name: FUNCTION get_info(i_system text, i_module text); Type: ACL; Schema: corestatus; Owner: corestatus
--

GRANT ALL ON FUNCTION corestatus.get_info(i_system text, i_module text) TO status_api;


--
-- Name: TABLE sub_system_day_settings; Type: ACL; Schema: corestatus; Owner: corestatus
--

GRANT SELECT,DELETE,UPDATE ON TABLE corestatus.sub_system_day_settings TO cache;


--
-- Name: FUNCTION get_system_days(i_system text); Type: ACL; Schema: corestatus; Owner: corestatus
--

GRANT ALL ON FUNCTION corestatus.get_system_days(i_system text) TO status_api;


--
-- Name: TABLE sub_system_module; Type: ACL; Schema: corestatus; Owner: corestatus
--

GRANT SELECT,DELETE,UPDATE ON TABLE corestatus.sub_system_module TO cache;


--
-- Name: FUNCTION get_system_module(i_system text, i_module text); Type: ACL; Schema: corestatus; Owner: corestatus
--

GRANT ALL ON FUNCTION corestatus.get_system_module(i_system text, i_module text) TO status_api;


--
-- Name: PROCEDURE sync(i_msg_text text); Type: ACL; Schema: corestatus; Owner: corestatus
--

GRANT ALL ON PROCEDURE corestatus.sync(i_msg_text text) TO status_api;


--
-- Name: PROCEDURE upsert_sub_system_day_settings(i_system_name text, i_current_day date, i_next_day date); Type: ACL; Schema: corestatus; Owner: corestatus
--

GRANT ALL ON PROCEDURE corestatus.upsert_sub_system_day_settings(i_system_name text, i_current_day date, i_next_day date) TO status_api;


--
-- Name: PROCEDURE upsert_sub_system_module(i_system_name text, i_module_name text, i_module_status text); Type: ACL; Schema: corestatus; Owner: corestatus
--

GRANT ALL ON PROCEDURE corestatus.upsert_sub_system_module(i_system_name text, i_module_name text, i_module_status text) TO status_api;


--
-- Name: TABLE sub_system_day_settings_log; Type: ACL; Schema: corestatus; Owner: corestatus
--

GRANT SELECT,DELETE,UPDATE ON TABLE corestatus.sub_system_day_settings_log TO cache;


--
-- Name: TABLE sub_system_module_log; Type: ACL; Schema: corestatus; Owner: corestatus
--

GRANT SELECT,DELETE,UPDATE ON TABLE corestatus.sub_system_module_log TO cache;


--
-- Name: TABLE system_module_log; Type: ACL; Schema: corestatus; Owner: corestatus
--

GRANT SELECT,DELETE,UPDATE ON TABLE corestatus.system_module_log TO cache;


--
-- Name: TABLE v_system_module; Type: ACL; Schema: corestatus; Owner: corestatus
--

GRANT SELECT,DELETE,UPDATE ON TABLE corestatus.v_system_module TO cache;


--
--
