<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">

    <changeSet id="0001" author="akochetkov">
        <comment>create SCHEMA</comment>
        <sql>
            CREATE SCHEMA IF NOT EXISTS metrics;
            ALTER SCHEMA metrics OWNER TO ervu_business_metrics;
        </sql>
    </changeSet>

    <changeSet id="0002" author="akochetkov">
        <comment>create EXTENSION uuid-ossp</comment>
        <sql>
            CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
        </sql>
    </changeSet>

    <changeSet id="0003" author="akochetkov">
        <comment>add table recruitment and regions</comment>
        <sql>
            CREATE TABLE IF NOT EXISTS metrics.region (
            region_id serial NOT NULL PRIMARY KEY,
            subject_code character varying(3) NOT NULL,
            subject_name character varying(150) NOT NULL,
            subject_codename character varying(200) NOT NULL,
            CONSTRAINT uni_region1 UNIQUE (subject_code)
            )WITH (OIDS = FALSE);
            ALTER TABLE metrics.region OWNER TO ervu_business_metrics;

            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('01', 'Республика Адыгея', '01 Республика Адыгея') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('02', 'Республика Башкортостан', '02 Республика Башкортостан') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('03', 'Республика Бурятия', '03 Республика Бурятия') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('04', 'Республика Алтай', '04 Республика Алтай') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('05', 'Республика Дагестан', '05 Республика Дагестан') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('06', 'Республика Ингушетия', '06 Республика Ингушетия') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('07', 'Кабардино-Балкарская республика', '07 Кабардино-Балкарская республика') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('08', 'Республика Калмыкия', '08 Республика Калмыкия') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('09', 'Карачаево-Черкесская республика', '09 Карачаево-Черкесская республика') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('10', 'Республика Карелия', '10 Республика Карелия') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('11', 'Республика Коми', '11 Республика Коми') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('12', 'Республика Марий Эл', '12 Республика Марий Эл') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('13', 'Республика Мордовия', '13 Республика Мордовия') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('14', 'Республика Саха (Якутия)', '14 Республика Саха (Якутия)') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('15', 'Республика Северная Осетия — Алания', '15 Республика Северная Осетия — Алания') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('16', 'Республика Татарстан', '16 Республика Татарстан') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('17', 'Республика Тыва', '17 Республика Тыва') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('18', 'Удмуртская республика', '18 Удмуртская республика') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('19', 'Республика Хакасия', '19 Республика Хакасия') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('20', 'Чеченская республика', '20 Чеченская республика') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('21', 'Чувашская республика', '21 Чувашская республика') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('22', 'Алтайский край', '22 Алтайский край') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('23', 'Краснодарский край', '23 Краснодарский край') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('24', 'Красноярский край', '24 Красноярский край') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('25', 'Приморский край', '25 Приморский край') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('26', 'Ставропольский край', '26 Ставропольский край') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('27', 'Хабаровский край', '27 Хабаровский край') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('28', 'Амурская область', '28 Амурская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('29', 'Архангельская область', '29 Архангельская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('30', 'Астраханская область', '30 Астраханская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('31', 'Белгородская область', '31 Белгородская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('32', 'Брянская область', '32 Брянская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('33', 'Владимирская область', '33 Владимирская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('34', 'Волгоградская область', '34 Волгоградская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('35', 'Вологодская область', '35 Вологодская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('36', 'Воронежская область', '36 Воронежская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('37', 'Ивановская область', '37 Ивановская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('38', 'Иркутская область', '38 Иркутская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('39', 'Калининградская область', '39 Калининградская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('40', 'Калужская область', '40 Калужская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('41', 'Камчатский край', '41 Камчатский край') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('42', 'Кемеровская область', '42 Кемеровская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('43', 'Кировская область', '43 Кировская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('44', 'Костромская область', '44 Костромская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('45', 'Курганская область', '45 Курганская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('46', 'Курская область', '46 Курская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('47', 'Ленинградская область', '47 Ленинградская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('48', 'Липецкая область', '48 Липецкая область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('49', 'Магаданская область', '49 Магаданская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('50', 'Московская область', '50 Московская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('51', 'Мурманская область', '51 Мурманская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('52', 'Нижегородская область', '52 Нижегородская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('53', 'Новгородская область', '53 Новгородская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('54', 'Новосибирская область', '54 Новосибирская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('55', 'Омская область', '55 Омская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('56', 'Оренбургская область', '56 Оренбургская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('57', 'Орловская область', '57 Орловская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('58', 'Пензенская область', '58 Пензенская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('59', 'Пермский край', '59 Пермский край') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('60', 'Псковская область', '60 Псковская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('61', 'Ростовская область', '61 Ростовская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('62', 'Рязанская область', '62 Рязанская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('63', 'Самарская область', '63 Самарская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('64', 'Саратовская область', '64 Саратовская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('65', 'Сахалинская область', '65 Сахалинская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('66', 'Свердловская область', '66 Свердловская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('67', 'Смоленская область', '67 Смоленская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('68', 'Тамбовская область', '68 Тамбовская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('69', 'Тверская область', '69 Тверская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('70', 'Томская область', '70 Томская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('71', 'Тульская область', '71 Тульская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('72', 'Тюменская область', '72 Тюменская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('73', 'Ульяновская область', '73 Ульяновская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('74', 'Челябинская область', '74 Челябинская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('75', 'Забайкальский край', '75 Забайкальский край') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('76', 'Ярославская область', '76 Ярославская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('77', 'Москва', '77 Москва') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('78', 'Санкт-Петербург', '78 Санкт-Петербург') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('79', 'Еврейская автономная область', '79 Еврейская автономная область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('83', 'Ненецкий автономный округ', '83 Ненецкий автономный округ') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('86', 'Ханты-Мансийский автономный округ - Югра', '86 Ханты-Мансийский автономный округ - Югра') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('87', 'Чукотский автономный округ', '87 Чукотский автономный округ') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('88', 'МИД', '88 МИД') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('89', 'Ямало-Ненецкий автономный округ', '89 Ямало-Ненецкий автономный округ') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('90', 'Запорожская область', '90 Запорожская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('91', 'Республика Крым', '91 Республика Крым') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('92', 'Севастополь', '92 Севастополь') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('93', 'Донецкая Народная Республика', '93 Донецкая Народная Республика') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('94', 'Луганская Народная Республика', '94 Луганская Народная Республика') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('95', 'Херсонская область', '95 Херсонская область') ON CONFLICT DO NOTHING;
            INSERT INTO  metrics.region (subject_code, subject_name,subject_codename) values ('99', 'Байконур', '99 Байконур') ON CONFLICT DO NOTHING;

            CREATE TABLE IF NOT EXISTS metrics.recruitment
            (
            id varchar(36) NOT NULL DEFAULT uuid_generate_v4(),
            idm_id character varying(256) COLLATE pg_catalog."default",
            parent_id character varying COLLATE pg_catalog."default",
            version integer,
            created_at timestamp without time zone NOT NULL DEFAULT now(),
            updated_at timestamp without time zone NOT NULL DEFAULT now(),
            schema character varying(64) COLLATE pg_catalog."default" NOT NULL,
            military_code character varying(16) COLLATE pg_catalog."default",
            shortname character varying COLLATE pg_catalog."default" NOT NULL,
            fullname character varying COLLATE pg_catalog."default" NOT NULL,
            dns character varying(64) COLLATE pg_catalog."default",
            email character varying(255) COLLATE pg_catalog."default",
            phone character varying(24) COLLATE pg_catalog."default",
            address character varying COLLATE pg_catalog."default",
            address_id character varying COLLATE pg_catalog."default",
            postal_address character varying COLLATE pg_catalog."default",
            postal_address_id character varying COLLATE pg_catalog."default",
            nsi_department_id character varying COLLATE pg_catalog."default",
            nsi_organization_id character varying COLLATE pg_catalog."default",
            oktmo character varying COLLATE pg_catalog."default",
            org_ogrn character varying COLLATE pg_catalog."default",
            dep_ogrn character varying COLLATE pg_catalog."default",
            epgu_id character varying COLLATE pg_catalog."default",
            kpp character varying(64) COLLATE pg_catalog."default",
            inn character varying(64) COLLATE pg_catalog."default",
            okato character varying(64) COLLATE pg_catalog."default",
            division_type character varying(64) COLLATE pg_catalog."default",
            tns_department_id character varying COLLATE pg_catalog."default",
            enabled boolean NOT NULL DEFAULT true,
            timezone character varying(64) COLLATE pg_catalog."default",
            reports_enabled boolean,
            subpoena_series_code character varying(64) COLLATE pg_catalog."default",
            hidden boolean NOT NULL DEFAULT false,
            region_id int  NOT NULL constraint fk_recruitment_region_id references metrics.region,
            ts timestamp without time zone NOT NULL DEFAULT now(),
            CONSTRAINT recruitment_pkey PRIMARY KEY (id),
            CONSTRAINT recruitment_idm_id_key UNIQUE (idm_id)
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.recruitment  OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.recruitment   IS 'Военный комиссариат';
            COMMENT ON COLUMN metrics.recruitment.id  IS 'Идентификатор ВК';
            COMMENT ON COLUMN metrics.recruitment.idm_id   IS 'Идентификатор организации';
            COMMENT ON COLUMN metrics.recruitment.parent_id  IS 'Идентификатор вышестоящей организации';
            COMMENT ON COLUMN metrics.recruitment.version  IS 'Версия записи';
            COMMENT ON COLUMN metrics.recruitment.created_at  IS 'Дата создания';
            COMMENT ON COLUMN metrics.recruitment.updated_at  IS 'Дата обновления';
            COMMENT ON COLUMN metrics.recruitment.schema   IS 'Схема';
            COMMENT ON COLUMN metrics.recruitment.military_code IS 'Код организации';
            COMMENT ON COLUMN metrics.recruitment.shortname  IS 'Укороченное наименование организации';
            COMMENT ON COLUMN metrics.recruitment.fullname IS 'Полное наименование организации';
            COMMENT ON COLUMN metrics.recruitment.dns  IS 'ДНС организации';
            COMMENT ON COLUMN metrics.recruitment.email  IS 'Е-mail организации';
            COMMENT ON COLUMN metrics.recruitment.phone  IS 'Телефон организации';
            COMMENT ON COLUMN metrics.recruitment.address  IS 'Адрес организации';
            COMMENT ON COLUMN metrics.recruitment.address_id  IS 'Идентификатор адреса организации';
            COMMENT ON COLUMN metrics.recruitment.postal_address  IS 'Почтовый адрес организации';
            COMMENT ON COLUMN metrics.recruitment.postal_address_id  IS 'Идентификатор почтового адреса организации';
            COMMENT ON COLUMN metrics.recruitment.nsi_department_id  IS 'Идентификатор департамента из НСИ';
            COMMENT ON COLUMN metrics.recruitment.nsi_organization_id IS 'Идентификатор организации из НСИ';
            COMMENT ON COLUMN metrics.recruitment.oktmo  IS 'ОКТМО';
            COMMENT ON COLUMN metrics.recruitment.org_ogrn IS 'ОГРН организации';
            COMMENT ON COLUMN metrics.recruitment.dep_ogrn IS 'ОГРН департамента';
            COMMENT ON COLUMN metrics.recruitment.epgu_id IS 'Идентификатор ЕПГУ';
            COMMENT ON COLUMN metrics.recruitment.kpp IS 'КПП';
            COMMENT ON COLUMN metrics.recruitment.inn IS 'ИНН';
            COMMENT ON COLUMN metrics.recruitment.okato  IS 'ОКАТО';
            COMMENT ON COLUMN metrics.recruitment.division_type  IS 'Тип дивизиона';
            COMMENT ON COLUMN metrics.recruitment.tns_department_id  IS 'Идентификатор департамента из ТНС';
            COMMENT ON COLUMN metrics.recruitment.enabled  IS 'Признак актуальности';
            COMMENT ON COLUMN metrics.recruitment.timezone  IS 'Часовой пояс';
            COMMENT ON COLUMN metrics.recruitment.reports_enabled  IS 'Признак актуальности для отчета';
            COMMENT ON COLUMN metrics.recruitment.region_id  IS 'Идентификатор региона';
            COMMENT ON COLUMN metrics.recruitment.subpoena_series_code  IS 'Серия';
            COMMENT ON COLUMN metrics.recruitment.hidden  IS 'Признак скрытого';
            CREATE INDEX IF NOT EXISTS recruitment_idm_idx ON metrics.recruitment  (idm_id);
            CREATE INDEX IF NOT EXISTS recruitment_military_code_idx  ON metrics.recruitment  (military_code);
            CREATE INDEX IF NOT EXISTS recruitment_parent_idx ON metrics.recruitment   (parent_id);
            CREATE INDEX IF NOT EXISTS recruitment_region_idx ON metrics.recruitment (region_id);
        </sql>
    </changeSet>

    <changeSet id="0004" author="akochetkov">
        <comment>add tables for convertation info</comment>
        <sql>
            CREATE TABLE IF NOT EXISTS metrics.convert_info_records_from_easu
            (
            convert_info_records_from_easu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_unique bigint NOT NULL DEFAULT 0,
            count_flk_and_duplicate bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.convert_info_records_from_easu  OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.convert_info_records_from_easu IS 'Загрузка Данных из ЕАСУ "Горизонт-М"';

            CREATE INDEX IF NOT EXISTS idx_convert_info_records_from_easu_date ON metrics.convert_info_records_from_easu (info_date);
            CREATE INDEX IF NOT EXISTS idx_convert_info_records_from_easu_recr ON metrics.convert_info_records_from_easu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_convert_info_records_from_easu_recr_date ON metrics.convert_info_records_from_easu (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW metrics.view_convert_info_records_from_easu AS
			SELECT
			convert_info_records_from_easu_id,
			count_unique + count_flk_and_duplicate AS count_all,
			round(count_unique::numeric * 100 / (count_unique + count_flk_and_duplicate)) AS percent_unique,
			round(count_flk_and_duplicate::numeric * 100 / (count_unique + count_flk_and_duplicate)) AS percent_flk_and_duplicate
			FROM metrics.convert_info_records_from_easu;

            CREATE TABLE IF NOT EXISTS metrics.convert_info_not_identifited_records
            (
            convert_info_not_identifited_records_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_not_in_csv bigint NOT NULL DEFAULT 0,
            count_not_in_xml bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.convert_info_not_identifited_records  OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.convert_info_not_identifited_records IS 'записи неидентифицированные в ГИР ВУ';

            CREATE INDEX IF NOT EXISTS idx_convert_info_not_identifited_records_date ON metrics.convert_info_not_identifited_records (info_date);
            CREATE INDEX IF NOT EXISTS idx_convert_info_not_identifited_records_recr ON metrics.convert_info_not_identifited_records (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_convert_info_not_identifited_records_recr_date ON metrics.convert_info_not_identifited_records (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW metrics.view_convert_info_not_identifited_records AS
			SELECT
			convert_info_not_identifited_records_id,
			count_not_in_csv + count_not_in_xml AS count_all,
			round(count_not_in_xml::numeric * 100 / (count_not_in_csv + count_not_in_xml)) AS percent_not_in_xml,
			round(count_not_in_csv::numeric * 100 / (count_not_in_csv + count_not_in_xml)) AS percent_not_in_csv
			FROM metrics.convert_info_not_identifited_records;


            CREATE TABLE IF NOT EXISTS metrics.convert_info_loaded_records
            (
            convert_info_loaded_records_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_success bigint NOT NULL DEFAULT 0,
            count_error bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.convert_info_loaded_records  OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.convert_info_loaded_records IS 'загрузка записей в ГИС ЕРВУ';

            CREATE INDEX IF NOT EXISTS idx_convert_info_loaded_records_date ON metrics.convert_info_loaded_records (info_date);
            CREATE INDEX IF NOT EXISTS idx_convert_info_loaded_records_recr ON metrics.convert_info_loaded_records (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_convert_info_loaded_records_recr_date ON metrics.convert_info_loaded_records (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW metrics.view_convert_info_loaded_records AS
			SELECT
			convert_info_loaded_records_id,
			count_success + count_error AS count_all,
			round(count_error::numeric * 100 / (count_success + count_error)) AS percent_error,
			round(count_success::numeric * 100 / (count_success + count_error)) AS percent_success
			FROM metrics.convert_info_loaded_records;


            CREATE TABLE IF NOT EXISTS metrics.convert_info_common_results
            (
            convert_info_common_results_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_confirmed bigint NOT NULL DEFAULT 0,
            count_signed bigint NOT NULL DEFAULT 0,
            count_manual_convert bigint NOT NULL DEFAULT 0,
            count_canceled bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.convert_info_common_results  OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.convert_info_common_results IS 'Конвертация. Результаты';

            CREATE INDEX IF NOT EXISTS idx_convert_info_common_results_date ON metrics.convert_info_common_results (info_date);
            CREATE INDEX IF NOT EXISTS idx_convert_info_common_results_recr ON metrics.convert_info_common_results (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_convert_info_common_results_recr_date ON metrics.convert_info_common_results (recruitment_id, info_date);

            CREATE TABLE IF NOT EXISTS metrics.convert_info_sent_to_lk_epgu
            (
            convert_info_sent_to_lk_epgu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_status_formed bigint NOT NULL DEFAULT 0,
            count_sended bigint NOT NULL DEFAULT 0,
            count_delivered bigint NOT NULL DEFAULT 0,
            count_error bigint NOT NULL DEFAULT 0,
            count_viewed bigint NOT NULL DEFAULT 0,
            count_not_viewed bigint NOT NULL DEFAULT 0,
            count_status_not_formed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.convert_info_sent_to_lk_epgu  OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.convert_info_sent_to_lk_epgu IS 'Конвертация. Доставка уведомлений в ЛК на ЕПГУ';

            CREATE INDEX IF NOT EXISTS idx_convert_info_sent_to_lk_epgu_date ON metrics.convert_info_sent_to_lk_epgu (info_date);
            CREATE INDEX IF NOT EXISTS idx_convert_info_sent_to_lk_epgu_recr ON metrics.convert_info_sent_to_lk_epgu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_convert_info_sent_to_lk_epgu_recr_date ON metrics.convert_info_sent_to_lk_epgu (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW metrics.view_convert_info_sent_to_lk_epgu AS
			SELECT
			convert_info_sent_to_lk_epgu_id,
			round(count_status_formed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_status_formed,
			round(count_status_not_formed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_status_not_formed,
			round(count_sended::numeric* 100 / (count_status_formed + count_status_not_formed)) AS percent_sended,
			round(count_delivered::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_delivered,
			round(count_error::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_error,
			round(count_viewed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_viewed,
			round(count_not_viewed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_not_viewed			
			FROM metrics.convert_info_sent_to_lk_epgu;


            CREATE TABLE IF NOT EXISTS metrics.convert_info_comparison_csv_xml
            (
            convert_info_comparison_csv_xml_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_xml_formed bigint NOT NULL DEFAULT 0,
            count_csv_formed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.convert_info_comparison_csv_xml  OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.convert_info_comparison_csv_xml IS 'Конвертация. Результат сопоставления файлов';

            CREATE INDEX IF NOT EXISTS idx_convert_info_comparison_csv_xml_date ON metrics.convert_info_comparison_csv_xml (info_date);
            CREATE INDEX IF NOT EXISTS idx_convert_info_comparison_csv_xml_recr ON metrics.convert_info_comparison_csv_xml (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_convert_info_comparison_csv_xml_recr_date ON metrics.convert_info_comparison_csv_xml (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW metrics.view_convert_info_comparison_csv_xml AS
			SELECT
			convert_info_comparison_csv_xml_id,
			round(count_csv_formed::numeric * 100 / count_xml_formed ) AS percent_csv_formed
			FROM metrics.convert_info_comparison_csv_xml;
        </sql>
    </changeSet>

    <changeSet id="0005" author="akochetkov">
        <comment>add tables in schema init_registration_info</comment>
        <sql>
            CREATE SCHEMA IF NOT EXISTS init_registration_info;

            CREATE TABLE IF NOT EXISTS init_registration_info.citizens_next_year_age
            (
            citizens_next_year_age_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            info_source varchar NOT NULL,
            info_age varchar NOT NULL,
            count_all bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS init_registration_info.citizens_next_year_age  OWNER to ervu_business_metrics;

            COMMENT ON TABLE init_registration_info.citizens_next_year_age IS 'Первоначальная постановка на учет. Данные по количеству гражан на учете в  возрасте 17 или 18 лет и по источнику самих данных';
            COMMENT ON COLUMN init_registration_info.citizens_next_year_age.info_source IS 'информация об источнике  - PERSONAL_VISIT, GIR_VU,  EPGU';
            COMMENT ON COLUMN init_registration_info.citizens_next_year_age.info_age IS 'Информациия о каком возрасте 17_YEARS, 18_YEAR';

            CREATE INDEX IF NOT EXISTS idx_citizens_next_year_age_date ON init_registration_info.citizens_next_year_age (info_date);
            CREATE INDEX IF NOT EXISTS idx_citizens_next_year_age_recr ON init_registration_info.citizens_next_year_age (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_citizens_next_year_age_recr_date ON init_registration_info.citizens_next_year_age (recruitment_id, info_date);

            CREATE TABLE IF NOT EXISTS init_registration_info.records_info_id_uk_id_ern
            (
            records_info_id_uk_id_ern_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            info_source varchar NOT NULL,
            info_age varchar NOT NULL,
            count_all bigint NOT NULL DEFAULT 0,
            records_with_id_uk bigint NOT NULL DEFAULT 0,
            records_without_id_uk bigint NOT NULL DEFAULT 0,
            records_with_id_ern bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS init_registration_info.records_info_id_uk_id_ern  OWNER to ervu_business_metrics;

            COMMENT ON TABLE init_registration_info.records_info_id_uk_id_ern IS 'Первоначальная постановка на учет. Информация о налиции ИД ЕРН и ИД УК';
            COMMENT ON COLUMN init_registration_info.records_info_id_uk_id_ern.info_source IS 'Источник - PERSONAL_VISIT, GIR_VU, EPGU';
            COMMENT ON COLUMN init_registration_info.records_info_id_uk_id_ern.info_age IS 'Информациия о каком возрасте 17_YEARS, 18_YEAR';

            CREATE INDEX IF NOT EXISTS idx_records_info_id_uk_id_ern_date ON init_registration_info.records_info_id_uk_id_ern (info_date);
            CREATE INDEX IF NOT EXISTS idx_records_info_id_uk_id_ern_recr ON init_registration_info.records_info_id_uk_id_ern (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_records_info_id_uk_id_ern_recr_date ON init_registration_info.records_info_id_uk_id_ern (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW init_registration_info.view_records_info_id_uk_id_ern AS
			SELECT
			records_info_id_uk_id_ern_id,
			round(records_with_id_uk::numeric * 100 / count_all) AS percent_with_id_uk,
			round(records_without_id_uk::numeric * 100 / count_all) AS percent_without_id_uk,
			round(records_with_id_ern::numeric * 100 / count_all) AS percent_with_id_ern		
			FROM init_registration_info.records_info_id_uk_id_ern;


            CREATE TABLE IF NOT EXISTS init_registration_info.info_sent_to_lk_epgu
            (
            info_sent_to_lk_epgu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            info_source varchar NOT NULL,
            info_age varchar NOT NULL,
            count_status_formed bigint NOT NULL DEFAULT 0,
            count_sended bigint NOT NULL DEFAULT 0,
            count_delivered bigint NOT NULL DEFAULT 0,
            count_error bigint NOT NULL DEFAULT 0,
            count_viewed bigint NOT NULL DEFAULT 0,
            count_not_viewed bigint NOT NULL DEFAULT 0,
            count_status_not_formed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS init_registration_info.info_sent_to_lk_epgu  OWNER to ervu_business_metrics;

            COMMENT ON TABLE init_registration_info.info_sent_to_lk_epgu IS 'Первоначальная постановка на учет. Доставка уведомлений в ЛК на ЕПГУ';
            COMMENT ON COLUMN init_registration_info.info_sent_to_lk_epgu.info_source IS 'информация об источнике  - PERSONAL_VISIT, GIR_VU,  EPGU';
            COMMENT ON COLUMN init_registration_info.info_sent_to_lk_epgu.info_age IS 'Информациия о каком возрасте 17_YEARS, 18_YEAR';

            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_date ON init_registration_info.info_sent_to_lk_epgu (info_date);
            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_recr ON init_registration_info.info_sent_to_lk_epgu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_recr_date ON init_registration_info.info_sent_to_lk_epgu (recruitment_id, info_date);



            CREATE TABLE IF NOT EXISTS init_registration_info.decision_formation_status
            (
            decision_formation_status_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            info_source varchar NOT NULL,
            info_age varchar NOT NULL,
            count_signed bigint NOT NULL DEFAULT 0,
            count_suggested bigint NOT NULL DEFAULT 0,
            count_waiting_sign bigint NOT NULL DEFAULT 0,
            count_refused bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS init_registration_info.decision_formation_status  OWNER to ervu_business_metrics;

            COMMENT ON TABLE init_registration_info.decision_formation_status IS 'Первоначальная постановка на учет. Статус формирования решений';
            COMMENT ON COLUMN init_registration_info.decision_formation_status.info_source IS 'информация об источнике  - PERSONAL_VISIT, GIR_VU,  EPGU';
            COMMENT ON COLUMN init_registration_info.decision_formation_status.info_age IS 'Информациия о каком возрасте 17_YEARS, 18_YEAR';
            COMMENT ON COLUMN init_registration_info.decision_formation_status.count_suggested IS 'Предпоставлено';

            CREATE INDEX IF NOT EXISTS idx_decision_formation_status_date ON init_registration_info.decision_formation_status (info_date);
            CREATE INDEX IF NOT EXISTS idx_decision_formation_status_recr ON init_registration_info.decision_formation_status (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_decision_formation_status_recr_date ON init_registration_info.decision_formation_status (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW init_registration_info.view_decision_formation_status AS
			SELECT
			decision_formation_status_id,
			count_signed + count_suggested + count_waiting_sign + count_refused AS count_all,
			round(count_signed::numeric * 100 / (count_signed + count_suggested + count_waiting_sign + count_refused)) AS percent_signed,
			round(count_suggested::numeric * 100 / (count_signed + count_suggested + count_waiting_sign + count_refused)) AS percent_suggested,
			round(count_waiting_sign::numeric * 100 / (count_signed + count_suggested + count_waiting_sign + count_refused)) AS percent_waiting_sign,
			round(count_refused::numeric * 100 / (count_signed + count_suggested + count_waiting_sign + count_refused)) AS percent_refused
			FROM init_registration_info.decision_formation_status;


            CREATE TABLE IF NOT EXISTS init_registration_info.incidents_info
            (
            incidents_info_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            info_source varchar NOT NULL,
            info_age varchar NOT NULL,
            count_without_id_ern bigint NOT NULL DEFAULT 0,
            count_discrepancy_epgu_info bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS init_registration_info.incidents_info  OWNER to ervu_business_metrics;

            COMMENT ON TABLE init_registration_info.incidents_info IS 'Первоначальная постановка на учет. Инциденты';
            COMMENT ON COLUMN init_registration_info.incidents_info.info_source IS 'информация об источнике  - PERSONAL_VISIT,  EPGU';
            COMMENT ON COLUMN init_registration_info.incidents_info.info_age IS 'Информациия о каком возрасте 17_YEARS, 18_YEAR';

            CREATE INDEX IF NOT EXISTS idx_incidents_info_date ON init_registration_info.incidents_info (info_date);
            CREATE INDEX IF NOT EXISTS idx_incidents_info_recr ON init_registration_info.incidents_info (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_incidents_info_recr_date ON init_registration_info.incidents_info (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW init_registration_info.view_incidents_info AS
			SELECT
			incidents_info_id,
			count_without_id_ern + count_discrepancy_epgu_info AS count_all,
			round(count_without_id_ern::numeric * 100 / (count_without_id_ern + count_discrepancy_epgu_info)) AS percent_without_id_ern,
			round(count_discrepancy_epgu_info::numeric * 100 / (count_without_id_ern + count_discrepancy_epgu_info)) AS percent_discrepancy_epgu_info
			FROM init_registration_info.incidents_info;


            CREATE TABLE IF NOT EXISTS init_registration_info.init_registration_from_gir_vu
            (
            init_registration_from_gir_vu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_all bigint NOT NULL DEFAULT 0,
            count_womens bigint NOT NULL DEFAULT 0,
            count_received_citizenship bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS init_registration_info.init_registration_from_gir_vu  OWNER to ervu_business_metrics;

            COMMENT ON TABLE init_registration_info.init_registration_from_gir_vu IS 'Первоначальная постановка на учет. Первоначальная постановка на учет по данным из ГИР ВУ';

            CREATE INDEX IF NOT EXISTS idx_init_registration_from_gir_vu_date ON init_registration_info.init_registration_from_gir_vu (info_date);
            CREATE INDEX IF NOT EXISTS idx_init_registration_from_gir_vu_recr ON init_registration_info.init_registration_from_gir_vu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_init_registration_from_gir_vu_recr_date ON init_registration_info.init_registration_from_gir_vu (recruitment_id, info_date);


            CREATE TABLE IF NOT EXISTS init_registration_info.applications_received_from_epgu
            (
            applications_received_from_epgu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_all bigint NOT NULL DEFAULT 0,
            count_executor_appointed bigint NOT NULL DEFAULT 0,
            count_registered bigint NOT NULL DEFAULT 0,
            count_registration_refusal bigint NOT NULL DEFAULT 0,
            count_refusal_provide_service bigint NOT NULL DEFAULT 0,
            count_executor_not_appointed_expired bigint NOT NULL DEFAULT 0,
            count_executor_not_appointed_on_time bigint NOT NULL DEFAULT 0,
            count_received bigint NOT NULL DEFAULT 0,
            count_provided bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS init_registration_info.applications_received_from_epgu  OWNER to ervu_business_metrics;

            COMMENT ON TABLE init_registration_info.applications_received_from_epgu IS 'Первоначальная постановка на учет. Заявления, поступившие через ЕПГУ';
			
			COMMENT ON COLUMN init_registration_info.applications_received_from_epgu.count_executor_not_appointed_expired IS 'Не назначен исполнитель свыше допустимых сроков (просрочено)';
			COMMENT ON COLUMN init_registration_info.applications_received_from_epgu.count_executor_not_appointed_on_time IS 'Не назначен исполнитель в пределах допустимых сроков';
			COMMENT ON COLUMN init_registration_info.applications_received_from_epgu.count_received IS 'Заявление получено ведомством';
			COMMENT ON COLUMN init_registration_info.applications_received_from_epgu.count_registration_refusal IS 'Получен отказ в регистрации';
			COMMENT ON COLUMN init_registration_info.applications_received_from_epgu.count_refusal_provide_service IS 'Получен отказ в предоставлении услуги по заявлению';
			COMMENT ON COLUMN init_registration_info.applications_received_from_epgu.count_provided IS 'Услуга по заявлению оказана';

            CREATE INDEX IF NOT EXISTS idx_applications_received_from_epgu_date ON init_registration_info.applications_received_from_epgu (info_date);
            CREATE INDEX IF NOT EXISTS idx_applications_received_from_epgu_recr ON init_registration_info.applications_received_from_epgu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_applications_received_from_epgu_recr_date ON init_registration_info.applications_received_from_epgu (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW init_registration_info.view_applications_received_from_epgu AS
			SELECT
			applications_received_from_epgu_id,
			round(count_executor_appointed::numeric * 100 / count_all) AS percent_executor_appointed,
			round(count_registered::numeric * 100 / count_all) AS percent_registered,
			round(count_registration_refusal::numeric * 100 / count_all) AS percent_registration_refusal,
			round(count_refusal_provide_service::numeric * 100 / count_all) AS percent_refusal_provide_service,
			round(count_executor_not_appointed_expired::numeric * 100 / count_all) AS percent_executor_not_appointed_expired,
			round(count_executor_not_appointed_on_time::numeric * 100 / count_all) AS percent_executor_not_appointed_on_time,
			round(count_received::numeric * 100 / count_all) AS percent_received,
			round(count_provided::numeric * 100 / count_all) AS percent_provided			
			FROM init_registration_info.applications_received_from_epgu;


            CREATE TABLE IF NOT EXISTS init_registration_info.statuses_decisions_on_epgu
            (
            statuses_decisions_on_epgu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_loaded bigint NOT NULL DEFAULT 0,
            count_signed bigint NOT NULL DEFAULT 0,
            count_formed_for_signing bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS init_registration_info.statuses_decisions_on_epgu  OWNER to ervu_business_metrics;

            COMMENT ON TABLE init_registration_info.statuses_decisions_on_epgu IS 'Первоначальная постановка на учет. Статусы решений по Первоначальной постановке на учет(ЕПГУ)';

            CREATE INDEX IF NOT EXISTS idx_statuses_decisions_on_epgu_date ON init_registration_info.statuses_decisions_on_epgu (info_date);
            CREATE INDEX IF NOT EXISTS idx_statuses_decisions_on_epgu_recr ON init_registration_info.statuses_decisions_on_epgu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_statuses_decisions_on_epgu_recr_date ON init_registration_info.statuses_decisions_on_epgu (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW init_registration_info.view_statuses_decisions_on_epgu AS
			SELECT
			statuses_decisions_on_epgu_id,
			round(count_signed::numeric * 100 / count_loaded) AS percent_signed,
			round(count_formed_for_signing::numeric * 100 / count_loaded) AS percent_formed_for_signing		
			FROM init_registration_info.statuses_decisions_on_epgu;
			
			
            CREATE TABLE IF NOT EXISTS metrics.criminal_administrative_liability
            (
            criminal_administrative_liability_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_administrative bigint NOT NULL DEFAULT 0,
            count_criminal bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.criminal_administrative_liability OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.criminal_administrative_liability IS 'Учет уголовной, административной ответственности';

            CREATE INDEX IF NOT EXISTS idx_criminal_administrative_liability_date ON metrics.criminal_administrative_liability (info_date);
            CREATE INDEX IF NOT EXISTS idx_criminal_administrative_liability_recr ON metrics.criminal_administrative_liability (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_criminal_administrative_liability_recr_date ON metrics.criminal_administrative_liability (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW metrics.view_criminal_administrative_liability AS
			SELECT
			criminal_administrative_liability_id,
			count_administrative + count_criminal AS count_all,
			round(count_criminal::numeric * 100 / (count_administrative + count_criminal)) AS percent_criminal,
			round(count_administrative::numeric * 100 / (count_administrative + count_criminal)) AS percent_administrative
			FROM metrics.criminal_administrative_liability;
        </sql>
    </changeSet>
	
    <changeSet id="0006" author="ilyin">
        <comment>add table incidents</comment>
        <sql>
			CREATE TABLE IF NOT EXISTS metrics.incidents_change_data_from_gir_vu
            (
            incidents_change_data_from_gir_vu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_epgu_citizen_appeal bigint NOT NULL DEFAULT 0,
            count_manual bigint NOT NULL DEFAULT 0,
            count_send_to_gir_vu bigint NOT NULL DEFAULT 0,
            count_accepted bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.incidents_change_data_from_gir_vu OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.incidents_change_data_from_gir_vu IS 'Инциденты. Изменения сведений из ГИР ВУ';

            CREATE INDEX IF NOT EXISTS idx_incidents_change_data_from_gir_vu_date ON metrics.incidents_change_data_from_gir_vu (info_date);
            CREATE INDEX IF NOT EXISTS idx_incidents_change_data_from_gir_vu_recr ON metrics.incidents_change_data_from_gir_vu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_incidents_change_data_from_gir_vu_recr_date ON metrics.incidents_change_data_from_gir_vu (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW metrics.view_incidents_change_data_from_gir_vu AS
			SELECT
			incidents_change_data_from_gir_vu_id,
			count_epgu_citizen_appeal + count_manual + count_send_to_gir_vu + count_accepted AS count_all,
			round(count_epgu_citizen_appeal::numeric * 100 / (count_epgu_citizen_appeal + count_manual + count_send_to_gir_vu + count_accepted)) AS percent_epgu_citizen_appeal,
			round(count_manual::numeric * 100 / (count_epgu_citizen_appeal + count_manual + count_send_to_gir_vu + count_accepted)) AS percent_manual,
			round(count_send_to_gir_vu::numeric * 100 / (count_epgu_citizen_appeal + count_manual + count_send_to_gir_vu + count_accepted)) AS percent_send_to_gir_vu,
			round(count_accepted::numeric * 100 / (count_epgu_citizen_appeal + count_manual + count_send_to_gir_vu + count_accepted)) AS percent_accepted
			FROM metrics.incidents_change_data_from_gir_vu;
			
			
			CREATE TABLE IF NOT EXISTS metrics.incidents_recieved_by_data_from_gir_vu
            (
            incidents_recieved_by_data_from_gir_vu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_confirmed bigint NOT NULL DEFAULT 0,
            count_not_confirmed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.incidents_recieved_by_data_from_gir_vu OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.incidents_recieved_by_data_from_gir_vu IS 'Инциденты. Принятые решения на основании данных из ГИР ВУ';

            CREATE INDEX IF NOT EXISTS idx_incidents_recieved_by_data_from_gir_vu_date ON metrics.incidents_recieved_by_data_from_gir_vu (info_date);
            CREATE INDEX IF NOT EXISTS idx_incidents_recieved_by_data_from_gir_vu_recr ON metrics.incidents_recieved_by_data_from_gir_vu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_incidents_recieved_by_data_from_gir_vu_recr_date ON metrics.incidents_recieved_by_data_from_gir_vu (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW metrics.view_incidents_recieved_by_data_from_gir_vu AS
			SELECT
			incidents_recieved_by_data_from_gir_vu_id,
			count_confirmed + count_not_confirmed AS count_all,
			round(count_confirmed::numeric * 100 / (count_confirmed + count_not_confirmed)) AS percent_confirmed,
			round(count_not_confirmed::numeric * 100 / (count_confirmed + count_not_confirmed)) AS percent_not_confirmed
			FROM metrics.incidents_recieved_by_data_from_gir_vu;
			
			
			CREATE TABLE IF NOT EXISTS metrics.incidents_closed
            (
            incidents_closed_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_refused_to_archive bigint NOT NULL DEFAULT 0,
            count_accepted_to_archive bigint NOT NULL DEFAULT 0,
            count_auto_closed bigint NOT NULL DEFAULT 0,
            count_manual_closed bigint NOT NULL DEFAULT 0,
            count_returned bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.incidents_closed OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.incidents_closed IS 'Инциденты. Закрытые инциденты';

            CREATE INDEX IF NOT EXISTS idx_incidents_closed_date ON metrics.incidents_closed (info_date);
            CREATE INDEX IF NOT EXISTS idx_incidents_closed_recr ON metrics.incidents_closed (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_incidents_closed_recr_date ON metrics.incidents_closed (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW metrics.view_incidents_closed AS
			SELECT
			incidents_closed_id,
			count_refused_to_archive + count_accepted_to_archive + count_auto_closed + count_manual_closed + count_returned AS count_all,
			round(count_refused_to_archive::numeric * 100 / (count_refused_to_archive + count_accepted_to_archive + count_auto_closed + count_manual_closed + count_returned)) AS percent_refused_to_archive,
			round(count_accepted_to_archive::numeric * 100 / (count_refused_to_archive + count_accepted_to_archive + count_auto_closed + count_manual_closed + count_returned)) AS percent_accepted_to_archive,
			round(count_auto_closed::numeric * 100 / (count_refused_to_archive + count_accepted_to_archive + count_auto_closed + count_manual_closed + count_returned)) AS percent_auto_closed,
			round(count_manual_closed::numeric * 100 / (count_refused_to_archive + count_accepted_to_archive + count_auto_closed + count_manual_closed + count_returned)) AS percent_manual_closed,
			round(count_returned::numeric * 100 / (count_refused_to_archive + count_accepted_to_archive + count_auto_closed + count_manual_closed + count_returned)) AS percent_returned
			FROM metrics.incidents_closed;
        </sql>
    </changeSet>

    <changeSet id="0007" author="ilyin">
        <comment>add table appeals</comment>
        <sql>
			CREATE TABLE IF NOT EXISTS metrics.citizen_appeals
            (
            citizen_appeals_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_submitted bigint NOT NULL DEFAULT 0,
            count_satisfided bigint NOT NULL DEFAULT 0,
            count_not_satisfided bigint NOT NULL DEFAULT 0,
            count_accepted_expired bigint NOT NULL DEFAULT 0,
            count_accepted_on_time bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.citizen_appeals OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.citizen_appeals IS 'Обжалования. Обжалования граждан';
			
			COMMENT ON COLUMN metrics.citizen_appeals.count_submitted IS 'Подано обращений';
			COMMENT ON COLUMN metrics.citizen_appeals.count_satisfided IS 'Удовлетворенных';
			COMMENT ON COLUMN metrics.citizen_appeals.count_not_satisfided IS 'Не удовлетворенных';
			COMMENT ON COLUMN metrics.citizen_appeals.count_accepted_expired IS 'Просроченных';
			COMMENT ON COLUMN metrics.citizen_appeals.count_accepted_on_time IS 'Принято в срок';

            CREATE INDEX IF NOT EXISTS idx_citizen_appeals_date ON metrics.citizen_appeals (info_date);
            CREATE INDEX IF NOT EXISTS idx_citizen_appeals_recr ON metrics.citizen_appeals (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_citizen_appeals_recr_date ON metrics.citizen_appeals (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW metrics.view_citizen_appeals AS
			SELECT
			citizen_appeals_id,
			count_satisfided + count_not_satisfided + count_accepted_expired + count_accepted_on_time AS count_accepted_to_consideration,
			round((count_satisfided + count_not_satisfided + count_accepted_expired + count_accepted_on_time)::numeric * 100 / count_submitted) AS percent_accepted_to_consideration,
			round(count_satisfided::numeric * 100 / (count_satisfided + count_not_satisfided + count_accepted_expired + count_accepted_on_time)) AS percent_satisfided,
			round(count_not_satisfided::numeric * 100 / (count_satisfided + count_not_satisfided + count_accepted_expired + count_accepted_on_time)) AS percent_not_satisfided,
			round(count_accepted_expired::numeric * 100 / (count_satisfided + count_not_satisfided + count_accepted_expired + count_accepted_on_time)) AS percent_accepted_expired,
			round(count_accepted_on_time::numeric * 100 / (count_satisfided + count_not_satisfided + count_accepted_expired + count_accepted_on_time)) AS percent_accepted_on_time
			FROM metrics.citizen_appeals;



			CREATE TABLE IF NOT EXISTS metrics.fz_53_appeals
            (
            fz_53_appeals_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_accepted bigint NOT NULL DEFAULT 0,
            count_refused bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.fz_53_appeals OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.fz_53_appeals IS 'Обжалования. Обжалования решений ФЗ-53';
			
			COMMENT ON COLUMN metrics.fz_53_appeals.count_accepted IS 'Удовлетворенных жалоб';
			COMMENT ON COLUMN metrics.fz_53_appeals.count_refused IS 'Отклоненных жалоб';

            CREATE INDEX IF NOT EXISTS idx_fz_53_appeals_date ON metrics.fz_53_appeals (info_date);
            CREATE INDEX IF NOT EXISTS idx_fz_53_appeals_recr ON metrics.fz_53_appeals (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_fz_53_appeals_recr_date ON metrics.fz_53_appeals (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW metrics.view_fz_53_appeals AS
			SELECT
			fz_53_appeals_id,
			count_accepted + count_refused AS count_all,
			round(count_accepted::numeric * 100 / (count_accepted + count_refused)) AS percent_accepted,
			round(count_refused::numeric * 100 / (count_accepted + count_refused)) AS percent_refused
			FROM metrics.fz_53_appeals;
			
			
			CREATE TABLE IF NOT EXISTS metrics.active_applications
            (
            active_applications_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_active_applications bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.active_applications OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.active_applications IS 'Обжалования. Активные заявления в ГИС ЕРВУ';

            CREATE INDEX IF NOT EXISTS idx_active_applications_date ON metrics.active_applications (info_date);
            CREATE INDEX IF NOT EXISTS idx_active_applications_recr ON metrics.active_applications (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_active_applications_recr_date ON metrics.active_applications (recruitment_id, info_date);
			
			
			CREATE TABLE IF NOT EXISTS metrics.records_about_citizen
            (
            records_about_citizen_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_records_about_citizen bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.records_about_citizen OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.records_about_citizen IS 'Обжалования. Записи о гражданах, обновленных в ГИС ЕРВУ';

            CREATE INDEX IF NOT EXISTS idx_records_about_citizen_date ON metrics.records_about_citizen (info_date);
            CREATE INDEX IF NOT EXISTS idx_records_about_citizen_recr ON metrics.records_about_citizen (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_records_about_citizen_recr_date ON metrics.records_about_citizen (recruitment_id, info_date);



			ALTER TABLE metrics.convert_info_records_from_easu ADD COLUMN IF NOT EXISTS count_all bigint NOT NULL DEFAULT 0;
			
			DROP VIEW IF EXISTS metrics.view_convert_info_records_from_easu;
			CREATE OR REPLACE VIEW metrics.view_convert_info_records_from_easu AS
			SELECT
			convert_info_records_from_easu_id,
			round(count_unique::numeric * 100 / count_all) AS percent_unique,
			round(count_flk_and_duplicate::numeric * 100 / count_all) AS percent_flk_and_duplicate,
			count_all - count_unique AS count_without_unique,
			count_all - count_flk_and_duplicate AS count_without_flk_and_duplicate
			FROM metrics.convert_info_records_from_easu;
       </sql>
    </changeSet>

    <changeSet id="0008" author="ilyin">
        <comment>create schema summonses_list_subsystem</comment>
        <sql>
		
			CREATE SCHEMA IF NOT EXISTS summonses_list_subsystem;
		
			CREATE TABLE IF NOT EXISTS summonses_list_subsystem.personal_accounts
            (
            personal_accounts_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_registered bigint NOT NULL DEFAULT 0,
            count_not_registered bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS summonses_list_subsystem.personal_accounts OWNER to ervu_business_metrics;

            COMMENT ON TABLE summonses_list_subsystem.personal_accounts IS 'Реестр повесток подсистема. Личные кабинеты';

            CREATE INDEX IF NOT EXISTS idx_personal_accounts_date ON summonses_list_subsystem.personal_accounts (info_date);
            CREATE INDEX IF NOT EXISTS idx_personal_accounts_recr ON summonses_list_subsystem.personal_accounts (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_personal_accounts_recr_date ON summonses_list_subsystem.personal_accounts (recruitment_id, info_date);
			
			COMMENT ON COLUMN summonses_list_subsystem.personal_accounts.count_registered IS 'Состоящих на учете';
			COMMENT ON COLUMN summonses_list_subsystem.personal_accounts.count_not_registered IS 'Не состоящих на учете';
			
			CREATE OR REPLACE VIEW summonses_list_subsystem.view_personal_accounts AS
			SELECT
			personal_accounts_id,
			count_registered + count_not_registered AS count_all,
			round(count_registered::numeric * 100 / (count_registered + count_not_registered)) AS percent_registered,
			round(count_not_registered::numeric * 100 / (count_registered + count_not_registered)) AS percent_not_registered
			FROM summonses_list_subsystem.personal_accounts;
			
			
			CREATE TABLE IF NOT EXISTS summonses_list_subsystem.summonses_direct_to_pers_acc
            (
            summonses_direct_to_pers_acc_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_draft_board bigint NOT NULL DEFAULT 0,
            count_place_of_military_service bigint NOT NULL DEFAULT 0,
            count_med_inspection bigint NOT NULL DEFAULT 0,
            count_doc_clarification bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS summonses_list_subsystem.summonses_direct_to_pers_acc OWNER to ervu_business_metrics;

            COMMENT ON TABLE summonses_list_subsystem.summonses_direct_to_pers_acc IS 'Реестр повесток подсистема. Направление повесток в ЛК РП';

            CREATE INDEX IF NOT EXISTS idx_summonses_direct_to_pers_acc_date ON summonses_list_subsystem.summonses_direct_to_pers_acc (info_date);
            CREATE INDEX IF NOT EXISTS idx_summonses_direct_to_pers_acc_recr ON summonses_list_subsystem.summonses_direct_to_pers_acc (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_summonses_direct_to_pers_acc_recr_date ON summonses_list_subsystem.summonses_direct_to_pers_acc (recruitment_id, info_date);
			
			COMMENT ON COLUMN summonses_list_subsystem.summonses_direct_to_pers_acc.count_draft_board IS 'Для прохождения призывной комиссии';
			COMMENT ON COLUMN summonses_list_subsystem.summonses_direct_to_pers_acc.count_place_of_military_service IS 'Для отправки к месту службы';
			COMMENT ON COLUMN summonses_list_subsystem.summonses_direct_to_pers_acc.count_med_inspection IS 'Для медосвидетельствования';
			COMMENT ON COLUMN summonses_list_subsystem.summonses_direct_to_pers_acc.count_doc_clarification IS 'Для уточнения документов ВУ';
			
			CREATE OR REPLACE VIEW summonses_list_subsystem.view_summonses_direct_to_pers_acc AS
			SELECT
			summonses_direct_to_pers_acc_id,
			count_draft_board + count_place_of_military_service + count_med_inspection + count_doc_clarification AS count_all,
			round(count_draft_board::numeric * 100 / (count_draft_board + count_place_of_military_service + count_med_inspection + count_doc_clarification)) AS percent_draft_board,
			round(count_place_of_military_service::numeric * 100 / (count_draft_board + count_place_of_military_service + count_med_inspection + count_doc_clarification)) AS percent_place_of_military_service,
			round(count_med_inspection::numeric * 100 / (count_draft_board + count_place_of_military_service + count_med_inspection + count_doc_clarification)) AS percent_med_inspection,
			round(count_doc_clarification::numeric * 100 / (count_draft_board + count_place_of_military_service + count_med_inspection + count_doc_clarification)) AS percent_doc_clarification
			FROM summonses_list_subsystem.summonses_direct_to_pers_acc;
			
			
			CREATE TABLE IF NOT EXISTS summonses_list_subsystem.summonses_list
            (
            summonses_list_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            summonse_type character varying NOT NULL,
            count_view_arrived bigint NOT NULL DEFAULT 0,
            count_not_view_arrived bigint NOT NULL DEFAULT 0,
			count_view_less_20_days bigint NOT NULL DEFAULT 0,
			count_not_view_less_20_days bigint NOT NULL DEFAULT 0,
            count_view_greater_20_days bigint NOT NULL DEFAULT 0,
            count_not_view_greater_20_days bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS summonses_list_subsystem.summonses_list OWNER to ervu_business_metrics;

            COMMENT ON TABLE summonses_list_subsystem.summonses_list IS 'Реестр повесток подсистема. Для прохождения призывной комиссии';

            CREATE INDEX IF NOT EXISTS idx_summonses_list_date ON summonses_list_subsystem.summonses_list (info_date);
            CREATE INDEX IF NOT EXISTS idx_summonses_list_recr ON summonses_list_subsystem.summonses_list (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_summonses_list_recr_date ON summonses_list_subsystem.summonses_list (recruitment_id, info_date);
			
			COMMENT ON COLUMN summonses_list_subsystem.summonses_list.summonse_type IS 'Тип повестки (DRAFT_BOARD = призывная комиссия; MILITARY_SERVICE = отправка к месту службы; MED_INSPECTION = медосвидетельствование; DOC_CLARIFICATION = уточнение документов; NOT_REGISTERED = не состоит на учете)';
			COMMENT ON COLUMN summonses_list_subsystem.summonses_list.count_view_arrived IS 'Гражданин явился, просмотрено';
			COMMENT ON COLUMN summonses_list_subsystem.summonses_list.count_not_view_arrived IS 'Гражданин явился, не просмотрено';
			COMMENT ON COLUMN summonses_list_subsystem.summonses_list.count_view_less_20_days IS 'Менее 20 дней, просмотрено';
			COMMENT ON COLUMN summonses_list_subsystem.summonses_list.count_not_view_less_20_days IS 'Менее 20 дней, не просмотрено';
			COMMENT ON COLUMN summonses_list_subsystem.summonses_list.count_view_greater_20_days IS 'Более 20 дней, просмотрено';
			COMMENT ON COLUMN summonses_list_subsystem.summonses_list.count_not_view_greater_20_days IS 'Более 20 дней, не просмотрено';
			
			CREATE OR REPLACE VIEW summonses_list_subsystem.view_summonses_list AS
			SELECT
			summonses_list_id,
			count_view_arrived + count_not_view_arrived AS count_arrived_all,
			round(count_view_arrived::numeric * 100 / (count_view_arrived + count_not_view_arrived)) AS percent_view_arrived,
			round(count_not_view_arrived::numeric * 100 / (count_view_arrived + count_not_view_arrived)) AS percent_not_view_arrived,
			count_view_less_20_days + count_not_view_less_20_days AS count_less_20_days_all,
			round(count_view_less_20_days::numeric * 100 / (count_view_less_20_days + count_not_view_less_20_days)) AS percent_view_less_20_days,
			round(count_not_view_less_20_days::numeric * 100 / (count_view_less_20_days + count_not_view_less_20_days)) AS percent_not_view_less_20_days,
			count_view_greater_20_days + count_not_view_greater_20_days AS count_greater_20_days_all,
			round(count_view_greater_20_days::numeric * 100 / (count_view_greater_20_days + count_not_view_greater_20_days)) AS percent_view_greater_20_days,
			round(count_not_view_greater_20_days::numeric * 100 / (count_view_greater_20_days + count_not_view_greater_20_days)) AS percent_not_view_greater_20_days
			FROM summonses_list_subsystem.summonses_list;
			
			
			CREATE TABLE IF NOT EXISTS summonses_list_subsystem.requests_direct_to_pers_acc
            (
            requests_direct_to_pers_acc_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_registered bigint NOT NULL DEFAULT 0,
            count_registered_formed bigint NOT NULL DEFAULT 0,
            count_not_registered bigint NOT NULL DEFAULT 0,
            count_not_registered_formed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS summonses_list_subsystem.requests_direct_to_pers_acc OWNER to ervu_business_metrics;

            COMMENT ON TABLE summonses_list_subsystem.requests_direct_to_pers_acc IS 'Реестр повесток подсистема. Направление запросов в ЛК РП для выписки';

            CREATE INDEX IF NOT EXISTS idx_requests_direct_to_pers_acc_date ON summonses_list_subsystem.requests_direct_to_pers_acc (info_date);
            CREATE INDEX IF NOT EXISTS idx_requests_direct_to_pers_acc_recr ON summonses_list_subsystem.requests_direct_to_pers_acc (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_requests_direct_to_pers_acc_recr_date ON summonses_list_subsystem.requests_direct_to_pers_acc (recruitment_id, info_date);
			
			COMMENT ON COLUMN summonses_list_subsystem.requests_direct_to_pers_acc.count_registered IS 'состоящие на ВУ';
			COMMENT ON COLUMN summonses_list_subsystem.requests_direct_to_pers_acc.count_registered_formed IS 'сформировано для состоящих на ВУ';
			COMMENT ON COLUMN summonses_list_subsystem.requests_direct_to_pers_acc.count_not_registered IS 'не состоящие на ВУ';
			COMMENT ON COLUMN summonses_list_subsystem.requests_direct_to_pers_acc.count_not_registered_formed IS 'сформировано для не состоящих на ВУ';
			
			CREATE OR REPLACE VIEW summonses_list_subsystem.view_requests_direct_to_pers_acc AS
			SELECT
			requests_direct_to_pers_acc_id,
			count_registered + count_not_registered AS count_all,
			round(count_registered::numeric * 100 / (count_registered + count_not_registered)) AS percent_registered,
			round(count_not_registered::numeric * 100 / (count_registered + count_not_registered)) AS percent_not_registered,
			round(count_registered_formed::numeric * 100 / count_registered) AS percent_registered_formed,
			round(count_not_registered_formed::numeric * 100 / count_not_registered) AS percent_not_registered_formed
			FROM summonses_list_subsystem.requests_direct_to_pers_acc;
			

        </sql>
    </changeSet>

    <changeSet id="0009" author="ilyin">
        <comment>create schema registration_change_address</comment>
        <sql>
			CREATE SCHEMA IF NOT EXISTS registration_change_address;
			
			CREATE TABLE IF NOT EXISTS registration_change_address.change_data_from_gir_vu
            (
            change_data_from_gir_vu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_all bigint NOT NULL DEFAULT 0,
            count_requested_to_send bigint NOT NULL DEFAULT 0,
            count_accepted_to_send_from_first bigint NOT NULL DEFAULT 0,
            count_sent_from_first bigint NOT NULL DEFAULT 0,
            count_solution_send_signed bigint NOT NULL DEFAULT 0,
            count_unloaded bigint NOT NULL DEFAULT 0,
            count_solutions_registration_formed bigint NOT NULL DEFAULT 0,
            count_solutions_registration_signed bigint NOT NULL DEFAULT 0            
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS registration_change_address.change_data_from_gir_vu OWNER to ervu_business_metrics;

            COMMENT ON TABLE registration_change_address.change_data_from_gir_vu IS 'Постановка на воинский учет при смене адреса. Изменение сведений из ГИР ВУ';

            CREATE INDEX IF NOT EXISTS idx_change_data_from_gir_vu_date ON registration_change_address.change_data_from_gir_vu (info_date);
            CREATE INDEX IF NOT EXISTS idx_change_data_from_gir_vu_recr ON registration_change_address.change_data_from_gir_vu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_change_data_from_gir_vu_recr_date ON registration_change_address.change_data_from_gir_vu (recruitment_id, info_date);
			
			COMMENT ON COLUMN registration_change_address.change_data_from_gir_vu.count_all IS 'всего';
			COMMENT ON COLUMN registration_change_address.change_data_from_gir_vu.count_requested_to_send IS 'запрошено личных дел';
			COMMENT ON COLUMN registration_change_address.change_data_from_gir_vu.count_accepted_to_send_from_first IS 'принято решений о направлении дела из первого вк';
			COMMENT ON COLUMN registration_change_address.change_data_from_gir_vu.count_sent_from_first IS 'отправлено';
			COMMENT ON COLUMN registration_change_address.change_data_from_gir_vu.count_solution_send_signed IS 'подписано решений о направлении';
			COMMENT ON COLUMN registration_change_address.change_data_from_gir_vu.count_unloaded IS 'выгружено дел';
			COMMENT ON COLUMN registration_change_address.change_data_from_gir_vu.count_solutions_registration_formed IS 'сформировано решений по постановке на ВУ по новому адресу';
			COMMENT ON COLUMN registration_change_address.change_data_from_gir_vu.count_solutions_registration_signed IS 'подписано решений по постановке на ВУ по новому адресу';
			
			CREATE OR REPLACE VIEW registration_change_address.view_change_data_from_gir_vu AS
			SELECT
			change_data_from_gir_vu_id,
			round(count_requested_to_send::numeric * 100 / count_all ) AS percent_requested_to_send,
			round(count_accepted_to_send_from_first::numeric * 100 / count_all ) AS percent_accepted_to_send_from_first,
			round(count_sent_from_first::numeric * 100 / count_all ) AS percent_sent_from_first,
			round(count_solution_send_signed::numeric * 100 / count_all ) AS percent_requested_solution_send_signed,
			round(count_unloaded::numeric * 100 / count_all ) AS percent_requested_unloaded,
			round(count_solutions_registration_formed::numeric * 100 / count_all ) AS percent_requested_solutions_formed,
			round(count_solutions_registration_signed::numeric * 100 / count_all ) AS percent_solutions_registration_signed
			FROM registration_change_address.change_data_from_gir_vu;
			
			
			CREATE TABLE IF NOT EXISTS registration_change_address.summonses_sign
            (
            summonses_sign_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            info_source varchar NOT NULL,
            count_refused_in_second bigint NOT NULL DEFAULT 0,
            count_accepted_in_second bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS registration_change_address.summonses_sign OWNER to ervu_business_metrics;

            COMMENT ON TABLE registration_change_address.summonses_sign IS 'Постановка на воинский учет при смене адреса. Повестки, подписанные в первом ВК';

            CREATE INDEX IF NOT EXISTS idx_summonses_sign_date ON registration_change_address.summonses_sign (info_date);
            CREATE INDEX IF NOT EXISTS idx_summonses_sign_recr ON registration_change_address.summonses_sign (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_summonses_sign_recr_date ON registration_change_address.summonses_sign (recruitment_id, info_date);
			
			COMMENT ON COLUMN registration_change_address.summonses_sign.info_source IS 'информация об источнике - PERSONAL_VISIT, GIR_VU, EPGU';
			COMMENT ON COLUMN registration_change_address.summonses_sign.count_refused_in_second IS 'отмененные во 2 ВК';
			COMMENT ON COLUMN registration_change_address.summonses_sign.count_accepted_in_second IS 'подтвержденные во 2 ВК';
			
			CREATE OR REPLACE VIEW registration_change_address.view_summonses_sign AS
			SELECT
			summonses_sign_id,
			count_refused_in_second + count_accepted_in_second AS count_all,
			round(count_refused_in_second::numeric * 100 / (count_refused_in_second + count_accepted_in_second)) AS percent_refused_in_second,
			round(count_accepted_in_second::numeric * 100 / (count_refused_in_second + count_accepted_in_second)) AS percent_accepted_in_second
			FROM registration_change_address.summonses_sign;
			

            CREATE TABLE IF NOT EXISTS registration_change_address.info_sent_to_lk_epgu
            (
            info_sent_to_lk_epgu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            info_source character varying NOT NULL,
            count_status_formed bigint NOT NULL DEFAULT 0,
            count_sended bigint NOT NULL DEFAULT 0,
            count_delivered bigint NOT NULL DEFAULT 0,
            count_error bigint NOT NULL DEFAULT 0,
            count_viewed bigint NOT NULL DEFAULT 0,
            count_not_viewed bigint NOT NULL DEFAULT 0,
            count_status_not_formed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS registration_change_address.info_sent_to_lk_epgu  OWNER to ervu_business_metrics;

            COMMENT ON TABLE registration_change_address.info_sent_to_lk_epgu IS 'Постановка на воинский учет при смене адреса. Отправка уведомлений в ЛК на ЕПГУ';

            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_date ON registration_change_address.info_sent_to_lk_epgu (info_date);
            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_recr ON registration_change_address.info_sent_to_lk_epgu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_recr_date ON registration_change_address.info_sent_to_lk_epgu (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW registration_change_address.view_info_sent_to_lk_epgu AS
			SELECT
			info_sent_to_lk_epgu_id,
			round(count_status_formed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_status_formed,
			round(count_status_not_formed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_status_not_formed,
			round(count_sended::numeric* 100 / (count_status_formed + count_status_not_formed)) AS percent_sended,
			round(count_delivered::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_delivered,
			round(count_error::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_error,
			round(count_viewed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_viewed,
			round(count_not_viewed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_not_viewed			
			FROM registration_change_address.info_sent_to_lk_epgu;

			
			CREATE TABLE IF NOT EXISTS registration_change_address.records_about_registrated_citizen
            (
            records_about_registrated_citizen_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_registrated_by_app_vk bigint NOT NULL DEFAULT 0,
            count_registrated_another_vk bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS registration_change_address.records_about_registrated_citizen OWNER to ervu_business_metrics;

            COMMENT ON TABLE registration_change_address.records_about_registrated_citizen IS 'Постановка на воинский учет при смене адреса. Граждане, поставленные на ВУ';

            CREATE INDEX IF NOT EXISTS idx_records_about_citizen_date ON registration_change_address.records_about_registrated_citizen (info_date);
            CREATE INDEX IF NOT EXISTS idx_records_about_registrated_citizen_recr ON registration_change_address.records_about_registrated_citizen (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_records_about_registrated_citizen_recr_date ON registration_change_address.records_about_registrated_citizen (recruitment_id, info_date);


			CREATE OR REPLACE VIEW registration_change_address.view_records_about_registrated_citizen AS
			SELECT
			records_about_registrated_citizen_id,
			count_registrated_by_app_vk + count_registrated_another_vk AS count_all,
			round(count_registrated_by_app_vk::numeric * 100 / (count_registrated_by_app_vk + count_registrated_another_vk)) AS percent_registrated_by_app_vk,
			round(count_registrated_another_vk::numeric * 100 / (count_registrated_by_app_vk + count_registrated_another_vk)) AS percent_registrated_another_vk
			FROM registration_change_address.records_about_registrated_citizen;
			
			
			CREATE TABLE IF NOT EXISTS registration_change_address.personal_info_stat
            (
            personal_info_stat_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_refused bigint NOT NULL DEFAULT 0,
            count_unloaded bigint NOT NULL DEFAULT 0,
            count_accepted_to_send bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS registration_change_address.personal_info_stat OWNER to ervu_business_metrics;

            COMMENT ON TABLE registration_change_address.personal_info_stat IS 'Постановка на воинский учет при смене адреса. Статистика по запросу личных дел';

            CREATE INDEX IF NOT EXISTS idx_records_about_citizen_date ON registration_change_address.personal_info_stat (info_date);
            CREATE INDEX IF NOT EXISTS idx_personal_info_stat_recr ON registration_change_address.personal_info_stat (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_personal_info_stat_recr_date ON registration_change_address.personal_info_stat (recruitment_id, info_date);


			CREATE OR REPLACE VIEW registration_change_address.view_personal_info_stat AS
			SELECT
			personal_info_stat_id,
			count_refused + count_accepted_to_send AS count_all,
			round(count_refused::numeric * 100 / (count_refused + count_accepted_to_send)) AS percent_refused,
			round(count_unloaded::numeric * 100 / (count_refused + count_accepted_to_send)) AS percent_unloaded,
			round(count_accepted_to_send::numeric * 100 / (count_refused + count_accepted_to_send)) AS percent_accepted_to_send
			FROM registration_change_address.personal_info_stat;
			
			
            CREATE TABLE IF NOT EXISTS registration_change_address.compare_citizen_data
            (
            compare_citizen_data_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_compare_record bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS registration_change_address.compare_citizen_data OWNER to ervu_business_metrics;

            COMMENT ON TABLE registration_change_address.compare_citizen_data IS 'Постановка на воинский учет при смене адреса. Сопоставление записей о гражданине в ГИС ЕРВУ';
			
			
			CREATE TABLE IF NOT EXISTS registration_change_address.inactive_app_epgu
            (
            inactive_app_epgu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_inactive_app_epgu bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS registration_change_address.inactive_app_epgu OWNER to ervu_business_metrics;

            COMMENT ON TABLE registration_change_address.inactive_app_epgu IS 'Постановка на воинский учет при смене адреса. Кол-во неактивных заявлений в ЕПГУ';
			
			
			CREATE TABLE IF NOT EXISTS registration_change_address.change_data_personal_visit
            (
            change_data_personal_visit_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_for_sign bigint NOT NULL DEFAULT 0,
            count_signed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS registration_change_address.change_data_personal_visit OWNER to ervu_business_metrics;

            COMMENT ON TABLE registration_change_address.change_data_personal_visit IS 'Постановка на воинский учет при смене адреса. Изменения сведений при личной явке';

            CREATE INDEX IF NOT EXISTS idx_records_about_citizen_date ON registration_change_address.change_data_personal_visit (info_date);
            CREATE INDEX IF NOT EXISTS idx_change_data_personal_visit_recr ON registration_change_address.change_data_personal_visit (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_change_data_personal_visit_recr_date ON registration_change_address.change_data_personal_visit (recruitment_id, info_date);


			CREATE OR REPLACE VIEW registration_change_address.view_change_data_personal_visit AS
			SELECT
			change_data_personal_visit_id,
			count_for_sign + count_signed AS count_all,
			round(count_for_sign::numeric * 100 / (count_for_sign + count_signed)) AS percent_for_sign,
			round(count_signed::numeric * 100 / (count_for_sign + count_signed)) AS percent_signed
			FROM registration_change_address.change_data_personal_visit;
        </sql>
    </changeSet>

    <changeSet id="0010" author="ilyin">
        <comment>add init_registration_info.view_info_sent_to_lk_epgu </comment>
        <sql>
			CREATE OR REPLACE VIEW init_registration_info.view_info_sent_to_lk_epgu AS
			SELECT
			info_sent_to_lk_epgu_id,
			round(count_status_formed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_status_formed,
			round(count_status_not_formed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_status_not_formed,
			round(count_sended::numeric* 100 / (count_status_formed + count_status_not_formed)) AS percent_sended,
			round(count_delivered::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_delivered,
			round(count_error::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_error,
			round(count_viewed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_viewed,
			round(count_not_viewed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_not_viewed			
			FROM init_registration_info.info_sent_to_lk_epgu;
        </sql>
    </changeSet>

   <changeSet id="0011" author="ilyin">
       <comment>create SCHEMA deregistration</comment>
       <sql>
			CREATE SCHEMA IF NOT EXISTS deregistration;
			
			CREATE TABLE IF NOT EXISTS deregistration.data_from_gir_vu
            (
            data_from_gir_vu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_arrived_death_reason bigint NOT NULL DEFAULT 0,
            count_arrived_age_limit bigint NOT NULL DEFAULT 0,
            count_formed_death_reason bigint NOT NULL DEFAULT 0,
            count_formed_age_limit bigint NOT NULL DEFAULT 0,
            count_for_sign_death_reason bigint NOT NULL DEFAULT 0,
            count_for_sign_age_limit bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS deregistration.data_from_gir_vu OWNER to ervu_business_metrics;

            COMMENT ON TABLE deregistration.data_from_gir_vu IS 'Снятие с воинского учета. Сведения из ГИР ВУ';

            CREATE INDEX IF NOT EXISTS idx_records_about_citizen_date ON deregistration.data_from_gir_vu (info_date);
            CREATE INDEX IF NOT EXISTS idx_data_from_gir_vu_recr ON deregistration.data_from_gir_vu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_data_from_gir_vu_recr_date ON deregistration.data_from_gir_vu (recruitment_id, info_date);
			
			COMMENT ON COLUMN deregistration.data_from_gir_vu.count_arrived_death_reason IS 'поступило по причине смерти';
			COMMENT ON COLUMN deregistration.data_from_gir_vu.count_arrived_age_limit IS 'поступило по предельному возрасту';
			COMMENT ON COLUMN deregistration.data_from_gir_vu.count_formed_death_reason IS 'решений сформировано по причине смерти';
			COMMENT ON COLUMN deregistration.data_from_gir_vu.count_formed_age_limit IS 'решений сформировано по предельному возрасту';
			COMMENT ON COLUMN deregistration.data_from_gir_vu.count_for_sign_death_reason IS 'решений на подписании по причине смерти';
			COMMENT ON COLUMN deregistration.data_from_gir_vu.count_for_sign_age_limit IS 'решений на подписании по предельному возрасту';

			CREATE OR REPLACE VIEW deregistration.view_data_from_gir_vu AS
			SELECT
			data_from_gir_vu_id,
			count_arrived_death_reason + count_arrived_age_limit AS count_arrived_all,
			count_formed_death_reason + count_formed_age_limit AS count_formed_all,
			count_for_sign_death_reason + count_for_sign_age_limit AS count_for_sign_all,
			round(count_arrived_death_reason::numeric * 100 / (count_arrived_death_reason + count_arrived_age_limit)) AS percent_arrived_death_reason,
			round(count_arrived_age_limit::numeric * 100 / (count_arrived_death_reason + count_arrived_age_limit)) AS percent_arrived_age_limit,
			round(count_formed_death_reason::numeric * 100 / (count_formed_death_reason + count_formed_age_limit)) AS percent_formed_death_reason,
			round(count_formed_age_limit::numeric * 100 / (count_formed_death_reason + count_formed_age_limit)) AS percent_formed_age_limit,
			round(count_for_sign_death_reason::numeric * 100 / (count_for_sign_death_reason + count_for_sign_age_limit)) AS percent_for_sign_death_reason,
			round(count_for_sign_age_limit::numeric * 100 / (count_for_sign_death_reason + count_for_sign_age_limit)) AS percent_for_sign_age_limit
			FROM deregistration.data_from_gir_vu;


			CREATE TABLE IF NOT EXISTS deregistration.solutions
            (
            solutions_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            info_source character varying NOT NULL,
            count_accepted_signed bigint NOT NULL DEFAULT 0,
            count_accepted_for_sign bigint NOT NULL DEFAULT 0,
            count_refused_signed bigint NOT NULL DEFAULT 0,
            count_refused_for_sign bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS deregistration.solutions OWNER to ervu_business_metrics;

            COMMENT ON TABLE deregistration.solutions IS 'Снятие с воинского учета. Решение о снятии с ВУ';

            CREATE INDEX IF NOT EXISTS idx_records_about_citizen_date ON deregistration.solutions (info_date);
            CREATE INDEX IF NOT EXISTS idx_solutions_recr ON deregistration.solutions (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_solutions_recr_date ON deregistration.solutions (recruitment_id, info_date);
			
			COMMENT ON COLUMN deregistration.solutions.info_source IS 'информация об источнике - PERSONAL_VISIT, GIR_VU, EPGU';
			COMMENT ON COLUMN deregistration.solutions.count_accepted_signed IS 'решений о снятии подписано';
			COMMENT ON COLUMN deregistration.solutions.count_accepted_for_sign IS 'решений о снятии доступно для подписания';
			COMMENT ON COLUMN deregistration.solutions.count_refused_signed IS 'решений об отказе в снятии подписано';
			COMMENT ON COLUMN deregistration.solutions.count_refused_for_sign IS 'решений об отказе в снятии доступно для подписания';

			CREATE OR REPLACE VIEW deregistration.view_solutions AS
			SELECT
			solutions_id,
			count_accepted_signed + count_accepted_for_sign AS count_accepted_all,
			count_refused_signed + count_refused_for_sign AS count_refused_all,
			round(count_accepted_signed::numeric * 100 / (count_accepted_signed + count_accepted_for_sign)) AS percent_accepted_signed,
			round(count_accepted_for_sign::numeric * 100 / (count_accepted_signed + count_accepted_for_sign)) AS percent_accepted_for_sign,
			round(count_refused_signed::numeric * 100 / (count_refused_signed + count_refused_for_sign)) AS percent_refused_signed,
			round(count_refused_for_sign::numeric * 100 / (count_refused_signed + count_refused_for_sign)) AS percent_refused_for_sign
			FROM deregistration.solutions;
			
			
			CREATE TABLE IF NOT EXISTS deregistration.info_sent_to_lk_epgu
            (
            info_sent_to_lk_epgu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            info_source character varying NOT NULL,
            count_status_formed bigint NOT NULL DEFAULT 0,
            count_sended bigint NOT NULL DEFAULT 0,
            count_delivered bigint NOT NULL DEFAULT 0,
            count_error bigint NOT NULL DEFAULT 0,
            count_viewed bigint NOT NULL DEFAULT 0,
            count_not_viewed bigint NOT NULL DEFAULT 0,
            count_status_not_formed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS deregistration.info_sent_to_lk_epgu  OWNER to ervu_business_metrics;

            COMMENT ON TABLE deregistration.info_sent_to_lk_epgu IS 'Снятие с воинского учета. Отправка уведомлений в ЛК на ЕПГУ';

            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_date ON deregistration.info_sent_to_lk_epgu (info_date);
            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_recr ON deregistration.info_sent_to_lk_epgu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_recr_date ON deregistration.info_sent_to_lk_epgu (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW deregistration.view_info_sent_to_lk_epgu AS
			SELECT
			info_sent_to_lk_epgu_id,
			round(count_status_formed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_status_formed,
			round(count_status_not_formed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_status_not_formed,
			round(count_sended::numeric* 100 / (count_status_formed + count_status_not_formed)) AS percent_sended,
			round(count_delivered::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_delivered,
			round(count_error::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_error,
			round(count_viewed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_viewed,
			round(count_not_viewed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_not_viewed			
			FROM deregistration.info_sent_to_lk_epgu;



			CREATE TABLE IF NOT EXISTS deregistration.applications_sent_from_epgu
            (
            applications_sent_from_epgu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_moved_residence bigint NOT NULL DEFAULT 0,
            count_departure_more_6_month bigint NOT NULL DEFAULT 0,
            count_health_condition bigint NOT NULL DEFAULT 0,
            count_age_limit bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS deregistration.applications_sent_from_epgu  OWNER to ervu_business_metrics;

            COMMENT ON TABLE deregistration.applications_sent_from_epgu IS 'Снятие с воинского учета. Отправленные заявления ЕПГУ';

            CREATE INDEX IF NOT EXISTS idx_applications_sent_from_epgu_date ON deregistration.applications_sent_from_epgu (info_date);
            CREATE INDEX IF NOT EXISTS idx_applications_sent_from_epgu_recr ON deregistration.applications_sent_from_epgu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_applications_sent_from_epgu_recr_date ON deregistration.applications_sent_from_epgu (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW deregistration.view_applications_sent_from_epgu AS
			SELECT
			applications_sent_from_epgu_id,
			count_moved_residence + count_departure_more_6_month + count_health_condition + count_age_limit AS count_all,
			round(count_moved_residence::numeric * 100 / (count_moved_residence + count_departure_more_6_month + count_health_condition + count_age_limit)) AS percent_moved_residence,
			round(count_departure_more_6_month::numeric * 100 / (count_moved_residence + count_departure_more_6_month + count_health_condition + count_age_limit)) AS percent_departure_more_6_month,
			round(count_health_condition::numeric * 100 / (count_moved_residence + count_departure_more_6_month + count_health_condition + count_age_limit)) AS percent_health_condition,
			round(count_age_limit::numeric * 100 / (count_moved_residence + count_departure_more_6_month + count_health_condition + count_age_limit)) AS percent_age_limit	
			FROM deregistration.applications_sent_from_epgu;
			
			
			CREATE TABLE IF NOT EXISTS deregistration.applications_received_from_epgu
            (
            applications_received_from_epgu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_all bigint NOT NULL DEFAULT 0,
            count_executor_appointed bigint NOT NULL DEFAULT 0,
            count_registered bigint NOT NULL DEFAULT 0,
            count_registration_refusal bigint NOT NULL DEFAULT 0,
            count_refusal_provide_service bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS deregistration.applications_received_from_epgu  OWNER to ervu_business_metrics;

            COMMENT ON TABLE deregistration.applications_received_from_epgu IS 'Снятие с воинского учета. Заявления, поступившие из ЕПГУ';
			
			COMMENT ON COLUMN deregistration.applications_received_from_epgu.count_executor_appointed IS 'Назначен исполнитель';
			COMMENT ON COLUMN deregistration.applications_received_from_epgu.count_registered IS 'Зарегистрировано заявлений';
			COMMENT ON COLUMN deregistration.applications_received_from_epgu.count_registration_refusal IS 'Получен отказ в регистрации';
			COMMENT ON COLUMN deregistration.applications_received_from_epgu.count_refusal_provide_service IS 'Получен отказ в предоставлении услуги';

            CREATE INDEX IF NOT EXISTS idx_applications_received_from_epgu_date ON deregistration.applications_received_from_epgu (info_date);
            CREATE INDEX IF NOT EXISTS idx_applications_received_from_epgu_recr ON deregistration.applications_received_from_epgu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_applications_received_from_epgu_recr_date ON deregistration.applications_received_from_epgu (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW deregistration.view_applications_received_from_epgu AS
			SELECT
			applications_received_from_epgu_id,
			round(count_executor_appointed::numeric * 100 / count_all) AS percent_executor_appointed,
			round(count_registered::numeric * 100 / count_all) AS percent_registered,
			round(count_registration_refusal::numeric * 100 / count_all) AS percent_registration_refusal,
			round(count_refusal_provide_service::numeric * 100 / count_all) AS percent_refusal_provide_service
			FROM deregistration.applications_received_from_epgu;
			
			
			CREATE TABLE IF NOT EXISTS deregistration.incidents_info
            (
            incidents_info_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_for_sign bigint NOT NULL DEFAULT 0,
            count_signed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS deregistration.incidents_info  OWNER to ervu_business_metrics;

            COMMENT ON TABLE deregistration.incidents_info IS 'Снятие с воинского учета. Инциденты (сверка данных от гражданина с ГИС ЕРВУ)';

            CREATE INDEX IF NOT EXISTS idx_incidents_info_date ON deregistration.incidents_info (info_date);
            CREATE INDEX IF NOT EXISTS idx_incidents_info_recr ON deregistration.incidents_info (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_incidents_info_recr_date ON deregistration.incidents_info (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW deregistration.view_incidents_info AS
			SELECT
			incidents_info_id,
			count_for_sign + count_signed AS count_all,
			round(count_for_sign::numeric * 100 / (count_for_sign + count_signed)) AS percent_for_sign,
			round(count_signed::numeric * 100 / (count_for_sign + count_signed)) AS percent_signed
			FROM deregistration.incidents_info;
			
			
			CREATE TABLE IF NOT EXISTS deregistration.active_applications
            (
            active_applications_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_active_applications bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS deregistration.active_applications OWNER to ervu_business_metrics;

            COMMENT ON TABLE deregistration.active_applications IS 'Снятие с воинского учета. Активные заявления в ГИС ЕРВУ';

            CREATE INDEX IF NOT EXISTS idx_active_applications_date ON deregistration.active_applications (info_date);
            CREATE INDEX IF NOT EXISTS idx_active_applications_recr ON deregistration.active_applications (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_active_applications_recr_date ON deregistration.active_applications (recruitment_id, info_date);

       </sql>
   </changeSet>
   
    <changeSet id="0012" author="ilyin">
        <comment>add_incidents_change_address</comment>
        <sql>
			CREATE TABLE IF NOT EXISTS registration_change_address.incidents_epgu_info
            (
            incidents_epgu_info_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_identify bigint NOT NULL DEFAULT 0,
            count_registered bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS registration_change_address.incidents_epgu_info  OWNER to ervu_business_metrics;

            COMMENT ON TABLE registration_change_address.incidents_epgu_info IS 'Постановка на воинский учет при смене адреса. Инциденты ЕПГУ';

            CREATE INDEX IF NOT EXISTS idx_incidents_epgu_info_date ON registration_change_address.incidents_epgu_info (info_date);
            CREATE INDEX IF NOT EXISTS idx_incidents_epgu_info_recr ON registration_change_address.incidents_epgu_info (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_incidents_epgu_info_recr_date ON registration_change_address.incidents_epgu_info (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW registration_change_address.view_incidents_epgu_info AS
			SELECT
			incidents_epgu_info_id,
			round(count_registered::numeric * 100 / count_identify) AS percent_registered,
			count_identify - count_registered AS count_not_identify
			FROM registration_change_address.incidents_epgu_info;
			
			
			
            CREATE TABLE IF NOT EXISTS registration_change_address.incidents_info
            (
            incidents_info_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_without_id_ern bigint NOT NULL DEFAULT 0,
            count_discrepancy_epgu_info bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS registration_change_address.incidents_info  OWNER to ervu_business_metrics;

            COMMENT ON TABLE registration_change_address.incidents_info IS 'Постановка на воинский учет при смене адреса. Инциденты';

            CREATE INDEX IF NOT EXISTS idx_incidents_info_date ON registration_change_address.incidents_info (info_date);
            CREATE INDEX IF NOT EXISTS idx_incidents_info_recr ON registration_change_address.incidents_info (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_incidents_info_recr_date ON registration_change_address.incidents_info (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW registration_change_address.view_incidents_info AS
			SELECT
			incidents_info_id,
			count_without_id_ern + count_discrepancy_epgu_info AS count_all,
			round(count_without_id_ern::numeric * 100 / (count_without_id_ern + count_discrepancy_epgu_info)) AS percent_without_id_ern,
			round(count_discrepancy_epgu_info::numeric * 100 / (count_without_id_ern + count_discrepancy_epgu_info)) AS percent_discrepancy_epgu_info
			FROM registration_change_address.incidents_info;
			
			DROP VIEW metrics.view_citizen_appeals;
			CREATE OR REPLACE VIEW metrics.view_citizen_appeals AS
			SELECT
			citizen_appeals_id,
			count_satisfided + count_not_satisfided + count_accepted_expired + count_accepted_on_time AS count_accepted_to_consideration,
			count_submitted - (count_satisfided + count_not_satisfided + count_accepted_expired + count_accepted_on_time) AS count_submitted_difference_accepted,
			round((count_satisfided + count_not_satisfided + count_accepted_expired + count_accepted_on_time)::numeric * 100 / count_submitted) AS percent_accepted_to_consideration,
			round(count_satisfided::numeric * 100 / (count_satisfided + count_not_satisfided + count_accepted_expired + count_accepted_on_time)) AS percent_satisfided,
			round(count_not_satisfided::numeric * 100 / (count_satisfided + count_not_satisfided + count_accepted_expired + count_accepted_on_time)) AS percent_not_satisfided,
			round(count_accepted_expired::numeric * 100 / (count_satisfided + count_not_satisfided + count_accepted_expired + count_accepted_on_time)) AS percent_accepted_expired,
			round(count_accepted_on_time::numeric * 100 / (count_satisfided + count_not_satisfided + count_accepted_expired + count_accepted_on_time)) AS percent_accepted_on_time
			FROM metrics.citizen_appeals;

        </sql>
    </changeSet>

    <changeSet id="0013" author="ilyin">
        <comment>create SCHEMA actualization</comment>
        <sql>
			CREATE SCHEMA IF NOT EXISTS actualization;
			
			CREATE TABLE IF NOT EXISTS actualization.change_data_from_gir_vu
            (
            change_data_from_gir_vu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_reg_address bigint NOT NULL DEFAULT 0,
            count_place_of_stay bigint NOT NULL DEFAULT 0,
            count_work bigint NOT NULL DEFAULT 0,
            count_place_of_study bigint NOT NULL DEFAULT 0,
            count_family_status bigint NOT NULL DEFAULT 0,
            count_education bigint NOT NULL DEFAULT 0,
            count_renaming bigint NOT NULL DEFAULT 0,
            count_med_info bigint NOT NULL DEFAULT 0,
            count_other bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS actualization.change_data_from_gir_vu OWNER to ervu_business_metrics;

            COMMENT ON TABLE actualization.change_data_from_gir_vu IS 'Актуализация. Изменения сведений из ГИР ВУ';
			
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_reg_address IS 'новое место жит-ва';
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_place_of_stay IS 'новое место пребывания';
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_work IS 'новое место работы';
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_place_of_study IS 'новое место учебы';
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_family_status IS 'новое семейное положение';
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_education IS 'новое образование';
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_renaming IS 'перемена ФИО';
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_med_info IS 'новые мед данные';
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_other IS 'другие причины';

            CREATE INDEX IF NOT EXISTS idx_change_data_from_gir_vu_date ON actualization.change_data_from_gir_vu (info_date);
            CREATE INDEX IF NOT EXISTS idx_change_data_from_gir_vu_recr ON actualization.change_data_from_gir_vu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_change_data_from_gir_vu_recr_date ON actualization.change_data_from_gir_vu (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW actualization.view_change_data_from_gir_vu AS
			SELECT
			change_data_from_gir_vu_id,
			count_reg_address + count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming + count_med_info + count_other AS count_all,
			round(count_reg_address::numeric * 100 / (count_reg_address + count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming + count_med_info + count_other)) AS percent_reg_address,
			round(count_place_of_stay::numeric * 100 / (count_reg_address + count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming + count_med_info + count_other)) AS percent_place_of_stay,
			round(count_work::numeric * 100 / (count_reg_address + count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming + count_med_info + count_other)) AS percent_work,
			round(count_place_of_study::numeric * 100 / (count_reg_address + count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming + count_med_info + count_other)) AS percent_place_of_study,
			round(count_family_status::numeric * 100 / (count_reg_address + count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming + count_med_info + count_other)) AS percent_family_status,
			round(count_education::numeric * 100 / (count_reg_address + count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming + count_med_info + count_other)) AS percent_education,
			round(count_renaming::numeric * 100 / (count_reg_address + count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming + count_med_info + count_other)) AS percent_renaming,
			round(count_med_info::numeric * 100 / (count_reg_address + count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming + count_med_info + count_other)) AS percent_med_info,
			round(count_other::numeric * 100 / (count_reg_address + count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming + count_med_info + count_other)) AS percent_other
			FROM actualization.change_data_from_gir_vu;
			
			
			
			CREATE TABLE IF NOT EXISTS actualization.info_sent_to_lk_epgu
            (
            info_sent_to_lk_epgu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            info_source character varying NOT NULL,
            count_status_formed bigint NOT NULL DEFAULT 0,
            count_sended bigint NOT NULL DEFAULT 0,
            count_delivered bigint NOT NULL DEFAULT 0,
            count_error bigint NOT NULL DEFAULT 0,
            count_viewed bigint NOT NULL DEFAULT 0,
            count_not_viewed bigint NOT NULL DEFAULT 0,
            count_status_not_formed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS actualization.info_sent_to_lk_epgu  OWNER to ervu_business_metrics;

            COMMENT ON TABLE actualization.info_sent_to_lk_epgu IS 'Актуализация. Отправка уведомлений в ЛК на ЕПГУ';

            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_date ON actualization.info_sent_to_lk_epgu (info_date);
            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_recr ON actualization.info_sent_to_lk_epgu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_recr_date ON actualization.info_sent_to_lk_epgu (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW actualization.view_info_sent_to_lk_epgu AS
			SELECT
			info_sent_to_lk_epgu_id,
			round(count_status_formed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_status_formed,
			round(count_status_not_formed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_status_not_formed,
			round(count_sended::numeric* 100 / (count_status_formed + count_status_not_formed)) AS percent_sended,
			round(count_delivered::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_delivered,
			round(count_error::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_error,
			round(count_viewed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_viewed,
			round(count_not_viewed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_not_viewed			
			FROM actualization.info_sent_to_lk_epgu;
			
			
			CREATE TABLE IF NOT EXISTS actualization.app_reason
            (
            app_reason_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_place_of_stay bigint NOT NULL DEFAULT 0,
            count_work bigint NOT NULL DEFAULT 0,
			count_place_of_study bigint NOT NULL DEFAULT 0,
            count_family_status bigint NOT NULL DEFAULT 0,
            count_education bigint NOT NULL DEFAULT 0,
            count_renaming bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS actualization.app_reason OWNER to ervu_business_metrics;

            COMMENT ON TABLE actualization.app_reason IS 'Актуализация. Причины заявлений';
			
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_place_of_stay IS 'новое место пребывания';
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_work IS 'новое место работы';
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_place_of_study IS 'новое место учебы';
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_family_status IS 'новое семейное положение';
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_education IS 'новое образование';
			COMMENT ON COLUMN actualization.change_data_from_gir_vu.count_renaming IS 'перемена ФИО';

            CREATE INDEX IF NOT EXISTS idx_app_reason_date ON actualization.app_reason (info_date);
            CREATE INDEX IF NOT EXISTS idx_app_reason_recr ON actualization.app_reason (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_app_reason_recr_date ON actualization.app_reason (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW actualization.view_app_reason AS
			SELECT
			app_reason_id,
			count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming AS count_all,
			round(count_place_of_stay::numeric * 100 / (count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming)) AS percent_place_of_stay,
			round(count_work::numeric * 100 / (count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming)) AS percent_work,
			round(count_place_of_study::numeric * 100 / (count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming)) AS percent_place_of_study,
			round(count_family_status::numeric * 100 / (count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming)) AS percent_family_status,
			round(count_education::numeric * 100 / (count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming)) AS percent_education,
			round(count_renaming::numeric * 100 / (count_place_of_stay + count_work + count_place_of_study + count_family_status + count_education + count_renaming)) AS percent_renaming
			FROM actualization.app_reason;
			
			
			CREATE TABLE IF NOT EXISTS actualization.applications_status
            (
            applications_status_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_accepted_to_consideration bigint NOT NULL DEFAULT 0,
            count_registration_refusal bigint NOT NULL DEFAULT 0,
            count_refusal_provide_service bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS actualization.applications_status  OWNER to ervu_business_metrics;

            COMMENT ON TABLE actualization.applications_status IS 'Актуализация. Заявления, поступившие через ЕПГУ';
			
			COMMENT ON COLUMN actualization.applications_status.count_accepted_to_consideration IS 'Заявление принято к рассмотрению';
			COMMENT ON COLUMN actualization.applications_status.count_registration_refusal IS 'Получен отказ в регистрации';
			COMMENT ON COLUMN actualization.applications_status.count_refusal_provide_service IS 'Получен отказ в предоставлении услуги по заявлению';

            CREATE INDEX IF NOT EXISTS idx_applications_status_date ON actualization.applications_status (info_date);
            CREATE INDEX IF NOT EXISTS idx_applications_status_recr ON actualization.applications_status (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_applications_status_recr_date ON actualization.applications_status (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW actualization.view_applications_status AS
			SELECT
			applications_status_id,
			count_accepted_to_consideration + count_registration_refusal + count_refusal_provide_service AS count_all,
			round(count_accepted_to_consideration::numeric * 100 / (count_accepted_to_consideration + count_registration_refusal + count_refusal_provide_service)) AS percent_accepted_to_consideration,		
			round(count_registration_refusal::numeric * 100 / (count_accepted_to_consideration + count_registration_refusal + count_refusal_provide_service)) AS percent_registration_refusal,		
			round(count_refusal_provide_service::numeric * 100 / (count_accepted_to_consideration + count_registration_refusal + count_refusal_provide_service)) AS percent_refusal_provide_service		
			FROM actualization.applications_status;
			
			
			CREATE TABLE IF NOT EXISTS actualization.incidents_epgu_info
            (
            incidents_epgu_info_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_identify bigint NOT NULL DEFAULT 0,
            count_registered bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS actualization.incidents_epgu_info  OWNER to ervu_business_metrics;

            COMMENT ON TABLE actualization.incidents_epgu_info IS 'Актуализация. Инциденты ЕПГУ';

            CREATE INDEX IF NOT EXISTS idx_incidents_epgu_info_date ON actualization.incidents_epgu_info (info_date);
            CREATE INDEX IF NOT EXISTS idx_incidents_epgu_info_recr ON actualization.incidents_epgu_info (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_incidents_epgu_info_recr_date ON actualization.incidents_epgu_info (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW actualization.view_incidents_epgu_info AS
			SELECT
			incidents_epgu_info_id,
			round(count_registered::numeric * 100 / count_identify) AS percent_registered,
			count_identify - count_registered AS count_not_identify
			FROM actualization.incidents_epgu_info;
			
			
			CREATE TABLE IF NOT EXISTS actualization.records_about_citizen
            (
            records_about_citizen_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_records_updated bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS actualization.records_about_citizen OWNER to ervu_business_metrics;

            COMMENT ON TABLE actualization.records_about_citizen IS 'Актуализация. Записей обновленных на овновании заявлений граждан';

            CREATE INDEX IF NOT EXISTS idx_records_about_citizen_date ON actualization.records_about_citizen (info_date);
            CREATE INDEX IF NOT EXISTS idx_records_about_citizen_recr ON actualization.records_about_citizen (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_records_about_citizen_recr_date ON actualization.records_about_citizen (recruitment_id, info_date);
			
			
            CREATE TABLE IF NOT EXISTS actualization.citizen_records
            (
            citizen_records_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_for_sign bigint NOT NULL DEFAULT 0,
            count_signed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS actualization.citizen_records  OWNER to ervu_business_metrics;

            COMMENT ON TABLE actualization.citizen_records IS 'Актуализация. Записи граждан (личное посещение)';

            CREATE INDEX IF NOT EXISTS idx_citizen_records_date ON actualization.citizen_records (info_date);
            CREATE INDEX IF NOT EXISTS idx_citizen_records_recr ON actualization.citizen_records (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_citizen_records_recr_date ON actualization.citizen_records (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW actualization.view_citizen_records AS
			SELECT
			citizen_records_id,
			count_for_sign + count_signed AS count_all,
			round(count_for_sign::numeric * 100 / (count_for_sign + count_signed)) AS percent_for_sign,
			round(count_signed::numeric * 100 / (count_for_sign + count_signed)) AS percent_signed
			FROM actualization.citizen_records;
			
			
            CREATE TABLE IF NOT EXISTS actualization.incidents_info
            (
            incidents_info_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_without_id_ern bigint NOT NULL DEFAULT 0,
            count_discrepancy_epgu_info bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS actualization.incidents_info  OWNER to ervu_business_metrics;

            COMMENT ON TABLE actualization.incidents_info IS 'Актуализация. Инциденты (личное посещение)';

            CREATE INDEX IF NOT EXISTS idx_incidents_info_date ON actualization.incidents_info (info_date);
            CREATE INDEX IF NOT EXISTS idx_incidents_info_recr ON actualization.incidents_info (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_incidents_info_recr_date ON actualization.incidents_info (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW actualization.view_incidents_info AS
			SELECT
			incidents_info_id,
			count_without_id_ern + count_discrepancy_epgu_info AS count_all,
			round(count_without_id_ern::numeric * 100 / (count_without_id_ern + count_discrepancy_epgu_info)) AS percent_without_id_ern,
			round(count_discrepancy_epgu_info::numeric * 100 / (count_without_id_ern + count_discrepancy_epgu_info)) AS percent_discrepancy_epgu_info
			FROM actualization.incidents_info;
        </sql>
    </changeSet>
	
    <changeSet id="0014" author="ilyin">
        <comment>create schema summonses_list</comment>
        <sql>
			CREATE SCHEMA IF NOT EXISTS summonses_list;
			
			CREATE TABLE IF NOT EXISTS summonses_list.all_summonses
            (
            all_summonses_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_init_registration bigint NOT NULL DEFAULT 0,
            count_draft_company bigint NOT NULL DEFAULT 0,
            count_management_military_registration bigint NOT NULL DEFAULT 0,
            count_mobilization bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS summonses_list.all_summonses OWNER to ervu_business_metrics;

            COMMENT ON TABLE summonses_list.all_summonses IS 'Реестр повесток. Список всех повесток';
			
			COMMENT ON COLUMN summonses_list.all_summonses.count_init_registration IS 'в рамках первоначальной постановки на ВУ';
			COMMENT ON COLUMN summonses_list.all_summonses.count_draft_company IS 'в рамках призывной кампании';
			COMMENT ON COLUMN summonses_list.all_summonses.count_management_military_registration IS 'в рамках ведения воинского учета';
			COMMENT ON COLUMN summonses_list.all_summonses.count_mobilization IS 'в рамках мобилизации';

            CREATE INDEX IF NOT EXISTS idx_all_summonses_date ON summonses_list.all_summonses (info_date);
            CREATE INDEX IF NOT EXISTS idx_all_summonses_recr ON summonses_list.all_summonses (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_all_summonses_recr_date ON summonses_list.all_summonses (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW summonses_list.view_all_summonses AS
			SELECT
			all_summonses_id,
			count_init_registration + count_draft_company + count_management_military_registration + count_mobilization AS count_all,
			round(count_init_registration::numeric * 100 / (count_init_registration + count_draft_company + count_management_military_registration + count_mobilization)) AS percent_init_registration,
			round(count_draft_company::numeric * 100 / (count_init_registration + count_draft_company + count_management_military_registration + count_mobilization)) AS percent_draft_company,
			round(count_management_military_registration::numeric * 100 / (count_init_registration + count_draft_company + count_management_military_registration + count_mobilization)) AS percent_management_military_registration,
			round(count_mobilization::numeric * 100 / (count_init_registration + count_draft_company + count_management_military_registration + count_mobilization)) AS percent_mobilization
			FROM summonses_list.all_summonses;



			CREATE TABLE IF NOT EXISTS summonses_list.summonses_reason
            (
            summonses_reason_id bigserial NOT NULL PRIMARY KEY,
			parent_summonses_reason_id bigint constraint fk_parent_summonses_reason_id references summonses_list.summonses_reason,
			code character varying NOT NULL,
			name character varying NOT NULL,
			CONSTRAINT uni_summonses_reason1 UNIQUE (code)
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS summonses_list.summonses_reason OWNER to ervu_business_metrics;

            COMMENT ON TABLE summonses_list.summonses_reason IS 'Реестр повесток. Причины выдачи повестки';
			
			COMMENT ON COLUMN summonses_list.summonses_reason.parent_summonses_reason_id IS 'родительская причина';
			COMMENT ON COLUMN summonses_list.summonses_reason.code IS 'код причины';
			COMMENT ON COLUMN summonses_list.summonses_reason.name IS 'наименование причины';
			
			INSERT INTO summonses_list.summonses_reason(code, name) VALUES ('1', 'Первоначальная постановка на воинский учет') ON CONFLICT DO NOTHING;
			UPDATE summonses_list.summonses_reason SET parent_summonses_reason_id = summonses_reason_id WHERE code = '1';
			
			INSERT INTO summonses_list.summonses_reason(code, name) VALUES ('2', 'Призывная кампания') ON CONFLICT DO NOTHING;
			UPDATE summonses_list.summonses_reason SET parent_summonses_reason_id = summonses_reason_id WHERE code = '2';
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '2'), '3', 'Уточнение документов воинского учета') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '2'), '4', 'Прохождение медицинского освидетельствования или мероприятий связанных с ним') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '2'), '5', 'Прохождение повторного медицинского освидетельствования') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '2'), '6', 'Прохождение контрольного медицинского освидетельствования') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '2'), '7', 'Прохождение призывной комиссии с целью принятия решений о призыве на военную службу') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '2'), '8', 'Прохождение призывной комиссии с целью принятия решений о направлении на альтернативную военную службу') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '2'), '9', 'Прохождение призывной комиссии с целью принятия решений о предоставлении отсрочки') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '2'), '10', 'Прохождение призывной комиссии с целью принятия решений об освобождении от призыва и зачисления в запас') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '2'), '11', 'Прохождение призывной комиссии с целью принятия решений об освобождении от исполнения воинской обязанности') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '2'), '12', 'Прохождение призывной комиссии субъекта Российской Федерации') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '2'), '13', 'Отправка к месту прохождения службы') ON CONFLICT DO NOTHING;
			
			INSERT INTO summonses_list.summonses_reason(code, name) VALUES ('14', 'Ведение воинского учета') ON CONFLICT DO NOTHING;
			UPDATE summonses_list.summonses_reason SET parent_summonses_reason_id = summonses_reason_id WHERE code = '14';
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '14'), '15', 'Проведение мероприятий по медицинскому освидетельствованию и медицинскому обследованию') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '14'), '16', 'Проведение мероприятий по профессиональному психологическому отбору граждан') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '14'), '17', 'Уточнение военно-учетных данных') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '14'), '18', 'Внесение изменений в документы воинского учета') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '14'), '19', 'Получение документов воинского учета') ON CONFLICT DO NOTHING;
			INSERT INTO summonses_list.summonses_reason(parent_summonses_reason_id, code, name) VALUES ((SELECT summonses_reason_id FROM summonses_list.summonses_reason WHERE code = '14'), '20', 'Прохождение мероприятий по проведению военных сборов') ON CONFLICT DO NOTHING;
			
			INSERT INTO summonses_list.summonses_reason(code, name) VALUES ('21', 'Мобилизация') ON CONFLICT DO NOTHING;
			UPDATE summonses_list.summonses_reason SET parent_summonses_reason_id = summonses_reason_id WHERE code = '21';
			
			
			CREATE TABLE IF NOT EXISTS summonses_list.formed_summonses
            (
            formed_summonses_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
			summonses_reason_id bigint NOT NULL constraint fk_summonses_reason_id references summonses_list.summonses_reason,
            count_formed bigint NOT NULL DEFAULT 0,
            count_signed bigint NOT NULL DEFAULT 0,
            count_sent bigint NOT NULL DEFAULT 0,
            count_delivered_on_hands bigint NOT NULL DEFAULT 0,
            count_delivered_by_letter bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS summonses_list.formed_summonses OWNER to ervu_business_metrics;

            COMMENT ON TABLE summonses_list.formed_summonses IS 'Реестр повесток. Количество сформированных повесток';
			
			COMMENT ON COLUMN summonses_list.formed_summonses.count_formed IS 'сформировано';
			COMMENT ON COLUMN summonses_list.formed_summonses.count_signed IS 'подписано';
			COMMENT ON COLUMN summonses_list.formed_summonses.count_sent IS 'направлено';
			COMMENT ON COLUMN summonses_list.formed_summonses.count_delivered_on_hands IS 'доставлено нарочно';
			COMMENT ON COLUMN summonses_list.formed_summonses.count_delivered_by_letter IS 'доставлено заказным письмом';

            CREATE INDEX IF NOT EXISTS idx_formed_summonses_date ON summonses_list.formed_summonses (info_date);
            CREATE INDEX IF NOT EXISTS idx_formed_summonses_recr ON summonses_list.formed_summonses (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_formed_summonses_reason ON summonses_list.formed_summonses (summonses_reason_id);
            CREATE INDEX IF NOT EXISTS idx_formed_summonses_recr_date ON summonses_list.formed_summonses (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW summonses_list.view_formed_summonses AS
			SELECT
			formed_summonses_id,
			count_formed + count_signed + count_sent + count_delivered_on_hands + count_delivered_by_letter AS count_all,
			round(count_formed::numeric * 100 / (count_formed + count_signed + count_sent + count_delivered_on_hands + count_delivered_by_letter)) AS percent_formed,
			round(count_signed::numeric * 100 / (count_formed + count_signed + count_sent + count_delivered_on_hands + count_delivered_by_letter)) AS percent_signed,
			round(count_sent::numeric * 100 / (count_formed + count_signed + count_sent + count_delivered_on_hands + count_delivered_by_letter)) AS percent_sent,
			round(count_delivered_on_hands::numeric * 100 / (count_formed + count_signed + count_sent + count_delivered_on_hands + count_delivered_by_letter)) AS percent_delivered_on_hands,
			round(count_delivered_by_letter::numeric * 100 / (count_formed + count_signed + count_sent + count_delivered_on_hands + count_delivered_by_letter)) AS percent_delivered_by_letter
			FROM summonses_list.formed_summonses;
			
			
			CREATE TABLE IF NOT EXISTS summonses_list.recruit_office_visit
            (
            recruit_office_visit_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
			summonses_reason_id bigint NOT NULL constraint fk_summonses_reason_id references summonses_list.summonses_reason,
            count_visit bigint NOT NULL DEFAULT 0,
            count_not_visit bigint NOT NULL DEFAULT 0,
            count_valid_reason bigint NOT NULL DEFAULT 0,
            count_not_need bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS summonses_list.recruit_office_visit OWNER to ervu_business_metrics;

            COMMENT ON TABLE summonses_list.recruit_office_visit IS 'Реестр повесток. Явка в военный комиссариат';
			
			COMMENT ON COLUMN summonses_list.recruit_office_visit.count_visit IS 'явились';
			COMMENT ON COLUMN summonses_list.recruit_office_visit.count_not_visit IS 'не явились';
			COMMENT ON COLUMN summonses_list.recruit_office_visit.count_valid_reason IS 'не явились по уважительной причине';
			COMMENT ON COLUMN summonses_list.recruit_office_visit.count_not_need IS 'явка не требовалась';

            CREATE INDEX IF NOT EXISTS idx_recruit_office_visit_date ON summonses_list.recruit_office_visit (info_date);
            CREATE INDEX IF NOT EXISTS idx_recruit_office_visit_recr ON summonses_list.recruit_office_visit (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_recruit_office_visit_reason ON summonses_list.recruit_office_visit (summonses_reason_id);
            CREATE INDEX IF NOT EXISTS idx_recruit_office_visit_recr_date ON summonses_list.recruit_office_visit (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW summonses_list.view_recruit_office_visit AS
			SELECT
			recruit_office_visit_id,
			count_visit + count_not_visit + count_valid_reason + count_not_need count_all,
			round(count_visit::numeric * 100 / (count_visit + count_not_visit + count_valid_reason + count_not_need)) AS percent_visit,
			round(count_not_visit::numeric * 100 / (count_visit + count_not_visit + count_valid_reason + count_not_need)) AS percent_not_visit,
			round(count_valid_reason::numeric * 100 / (count_visit + count_not_visit + count_valid_reason + count_not_need)) AS percent_valid_reason,
			round(count_not_need::numeric * 100 / (count_visit + count_not_visit + count_valid_reason + count_not_need)) AS percent_not_need
			FROM summonses_list.recruit_office_visit;
			
			
			CREATE TABLE IF NOT EXISTS summonses_list.imposition_tmp_measures
            (
            imposition_tmp_measures_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
			summonses_reason_id bigint NOT NULL constraint fk_summonses_reason_id references summonses_list.summonses_reason,
            count_for_sign bigint NOT NULL DEFAULT 0,
            count_apply bigint NOT NULL DEFAULT 0,
            count_enter bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS summonses_list.imposition_tmp_measures OWNER to ervu_business_metrics;

            COMMENT ON TABLE summonses_list.imposition_tmp_measures IS 'Реестр повесток. Решение о принятии временных мер';
			
			COMMENT ON COLUMN summonses_list.imposition_tmp_measures.count_for_sign IS 'доступно для подписания';
			COMMENT ON COLUMN summonses_list.imposition_tmp_measures.count_apply IS 'применена мера';
			COMMENT ON COLUMN summonses_list.imposition_tmp_measures.count_enter IS 'введлена мера';

            CREATE INDEX IF NOT EXISTS idx_imposition_tmp_measures_date ON summonses_list.imposition_tmp_measures (info_date);
            CREATE INDEX IF NOT EXISTS idx_imposition_tmp_measures_recr ON summonses_list.imposition_tmp_measures (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_imposition_tmp_measures_reason ON summonses_list.imposition_tmp_measures (summonses_reason_id);
            CREATE INDEX IF NOT EXISTS idx_imposition_tmp_measures_recr_date ON summonses_list.imposition_tmp_measures (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW summonses_list.view_imposition_tmp_measures AS
			SELECT
			imposition_tmp_measures_id,
			count_for_sign + count_apply + count_enter count_all,
			round(count_for_sign::numeric * 100 / (count_for_sign + count_apply + count_enter)) AS percent_for_sign,
			round(count_apply::numeric * 100 / (count_for_sign + count_apply + count_enter)) AS percent_apply,
			round(count_enter::numeric * 100 / (count_for_sign + count_apply + count_enter)) AS percent_enter
			FROM summonses_list.imposition_tmp_measures;
			
			
			CREATE TABLE IF NOT EXISTS summonses_list.remove_tmp_measures
            (
            remove_tmp_measures_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_criminal_administrative_liability_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
			summonses_reason_id bigint NOT NULL constraint fk_summonses_reason_id references summonses_list.summonses_reason,
            count_formed bigint NOT NULL DEFAULT 0,
            count_for_sign bigint NOT NULL DEFAULT 0,
            count_signed bigint NOT NULL DEFAULT 0,
            count_removed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS summonses_list.remove_tmp_measures OWNER to ervu_business_metrics;

            COMMENT ON TABLE summonses_list.remove_tmp_measures IS 'Реестр повесток. Решение о снятии временных мер';
			
			COMMENT ON COLUMN summonses_list.remove_tmp_measures.count_formed IS 'сформировано решений';
			COMMENT ON COLUMN summonses_list.remove_tmp_measures.count_for_sign IS 'доступно для подписания';
			COMMENT ON COLUMN summonses_list.remove_tmp_measures.count_signed IS 'подписано';
			COMMENT ON COLUMN summonses_list.remove_tmp_measures.count_removed IS 'снято временных мер';

            CREATE INDEX IF NOT EXISTS idx_remove_tmp_measures_date ON summonses_list.remove_tmp_measures (info_date);
            CREATE INDEX IF NOT EXISTS idx_remove_tmp_measures_recr ON summonses_list.remove_tmp_measures (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_remove_tmp_measures_reason ON summonses_list.remove_tmp_measures (summonses_reason_id);
            CREATE INDEX IF NOT EXISTS idx_remove_tmp_measures_recr_date ON summonses_list.remove_tmp_measures (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW summonses_list.view_remove_tmp_measures AS
			SELECT
			remove_tmp_measures_id,
			count_formed + count_for_sign + count_signed + count_removed count_all,
			round(count_formed::numeric * 100 / (count_formed + count_for_sign + count_signed + count_removed)) AS percent_formed,
			round(count_for_sign::numeric * 100 / (count_formed + count_for_sign + count_signed + count_removed)) AS percent_for_sign,
			round(count_signed::numeric * 100 / (count_formed + count_for_sign + count_signed + count_removed)) AS percent_signed,
			round(count_removed::numeric * 100 / (count_formed + count_for_sign + count_signed + count_removed)) AS percent_removed
			FROM summonses_list.remove_tmp_measures;
			
			
			CREATE TABLE IF NOT EXISTS summonses_list.info_sent_to_lk_epgu
            (
            info_sent_to_lk_epgu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            summonses_reason_id bigint NOT NULL constraint fk_summonses_reason_id references summonses_list.summonses_reason,
            count_status_formed bigint NOT NULL DEFAULT 0,
            count_sended bigint NOT NULL DEFAULT 0,
            count_delivered bigint NOT NULL DEFAULT 0,
            count_error bigint NOT NULL DEFAULT 0,
            count_viewed bigint NOT NULL DEFAULT 0,
            count_not_viewed bigint NOT NULL DEFAULT 0,
            count_status_not_formed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS summonses_list.info_sent_to_lk_epgu  OWNER to ervu_business_metrics;

            COMMENT ON TABLE summonses_list.info_sent_to_lk_epgu IS 'Реестр повесток. Отправка уведомлений в ЛК на ЕПГУ';

            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_date ON summonses_list.info_sent_to_lk_epgu (info_date);
            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_recr ON summonses_list.info_sent_to_lk_epgu (recruitment_id);
			CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_reason ON summonses_list.imposition_tmp_measures (summonses_reason_id);
            CREATE INDEX IF NOT EXISTS idx_info_sent_to_lk_epgu_recr_date ON summonses_list.info_sent_to_lk_epgu (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW summonses_list.view_info_sent_to_lk_epgu AS
			SELECT
			info_sent_to_lk_epgu_id,
			round(count_status_formed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_status_formed,
			round(count_status_not_formed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_status_not_formed,
			round(count_sended::numeric* 100 / (count_status_formed + count_status_not_formed)) AS percent_sended,
			round(count_delivered::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_delivered,
			round(count_error::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_error,
			round(count_viewed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_viewed,
			round(count_not_viewed::numeric * 100 / (count_status_formed + count_status_not_formed)) AS percent_not_viewed			
			FROM summonses_list.info_sent_to_lk_epgu;
			
			
			CREATE TABLE IF NOT EXISTS summonses_list.download_for_print
            (
            download_for_print_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            summonses_reason_id bigint NOT NULL constraint fk_summonses_reason_id references summonses_list.summonses_reason,
            count_donloaded bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS summonses_list.download_for_print  OWNER to ervu_business_metrics;

            COMMENT ON TABLE summonses_list.download_for_print IS 'Реестр повесток. Отправка уведомлений в ЛК на ЕПГУ';

            CREATE INDEX IF NOT EXISTS idx_download_for_print_date ON summonses_list.download_for_print (info_date);
            CREATE INDEX IF NOT EXISTS idx_download_for_print_recr ON summonses_list.download_for_print (recruitment_id);
			CREATE INDEX IF NOT EXISTS idx_download_for_print_reason ON summonses_list.imposition_tmp_measures (summonses_reason_id);
            CREATE INDEX IF NOT EXISTS idx_download_for_print_recr_date ON summonses_list.download_for_print (recruitment_id, info_date);
        </sql>
    </changeSet>


    <changeSet id="0015" author="ilyin">
        <comment>skipped_indexes_and_check_constraint</comment>
        <sql>
            CREATE INDEX IF NOT EXISTS idx_personal_info_stat_date ON registration_change_address.personal_info_stat (info_date);
            CREATE INDEX IF NOT EXISTS idx_change_data_personal_visit_date ON registration_change_address.change_data_personal_visit (info_date);
			CREATE INDEX IF NOT EXISTS idx_data_from_gir_vu_date ON deregistration.data_from_gir_vu (info_date);
			CREATE INDEX IF NOT EXISTS idx_solutions_date ON deregistration.solutions (info_date);
			
			ALTER TABLE actualization.info_sent_to_lk_epgu DROP CONSTRAINT IF EXISTS chk_info_source;
			ALTER TABLE actualization.info_sent_to_lk_epgu ADD CONSTRAINT chk_info_source CHECK (info_source::text = ANY (ARRAY['PERSONAL_VISIT'::character varying::text, 'GIR_VU'::character varying::text, 'EPGU'::character varying::text]));

			ALTER TABLE deregistration.info_sent_to_lk_epgu DROP CONSTRAINT IF EXISTS chk_info_source;
			ALTER TABLE deregistration.info_sent_to_lk_epgu ADD CONSTRAINT chk_info_source CHECK (info_source::text = ANY (ARRAY['PERSONAL_VISIT'::character varying::text, 'GIR_VU'::character varying::text, 'EPGU'::character varying::text]));

			ALTER TABLE deregistration.solutions DROP CONSTRAINT IF EXISTS chk_info_source;
			ALTER TABLE deregistration.solutions ADD CONSTRAINT chk_info_source CHECK (info_source::text = ANY (ARRAY['PERSONAL_VISIT'::character varying::text, 'GIR_VU'::character varying::text, 'EPGU'::character varying::text]));

			ALTER TABLE init_registration_info.citizens_next_year_age DROP CONSTRAINT IF EXISTS chk_info_source;
			ALTER TABLE init_registration_info.citizens_next_year_age ADD CONSTRAINT chk_info_source CHECK (info_source::text = ANY (ARRAY['PERSONAL_VISIT'::character varying::text, 'GIR_VU'::character varying::text, 'EPGU'::character varying::text]));
			ALTER TABLE init_registration_info.citizens_next_year_age DROP CONSTRAINT IF EXISTS chk_info_age;
			ALTER TABLE init_registration_info.citizens_next_year_age ADD CONSTRAINT chk_info_age CHECK (info_age::text = ANY (ARRAY['17_YEARS'::character varying::text, '18_YEARS'::character varying::text, 'EPGU'::character varying::text]));
			COMMENT ON COLUMN init_registration_info.citizens_next_year_age.info_age IS 'Информациия о каком возрасте 17_YEARS, 18_YEARS';

			ALTER TABLE init_registration_info.decision_formation_status DROP CONSTRAINT IF EXISTS chk_info_source;
			ALTER TABLE init_registration_info.decision_formation_status ADD CONSTRAINT chk_info_source CHECK (info_source::text = ANY (ARRAY['PERSONAL_VISIT'::character varying::text, 'GIR_VU'::character varying::text, 'EPGU'::character varying::text]));
			ALTER TABLE init_registration_info.decision_formation_status DROP CONSTRAINT IF EXISTS chk_info_age;
			ALTER TABLE init_registration_info.decision_formation_status ADD CONSTRAINT chk_info_age CHECK (info_age::text = ANY (ARRAY['17_YEARS'::character varying::text, '18_YEARS'::character varying::text, 'EPGU'::character varying::text]));
			COMMENT ON COLUMN init_registration_info.decision_formation_status.info_age IS 'Информациия о каком возрасте 17_YEARS, 18_YEARS';

			ALTER TABLE init_registration_info.incidents_info DROP CONSTRAINT IF EXISTS chk_info_source;
			ALTER TABLE init_registration_info.incidents_info ADD CONSTRAINT chk_info_source CHECK (info_source::text = ANY (ARRAY['PERSONAL_VISIT'::character varying::text, 'GIR_VU'::character varying::text, 'EPGU'::character varying::text]));
			ALTER TABLE init_registration_info.incidents_info DROP CONSTRAINT IF EXISTS chk_info_age;
			ALTER TABLE init_registration_info.incidents_info ADD CONSTRAINT chk_info_age CHECK (info_age::text = ANY (ARRAY['17_YEARS'::character varying::text, '18_YEARS'::character varying::text, 'EPGU'::character varying::text]));
			COMMENT ON COLUMN init_registration_info.incidents_info.info_age IS 'Информациия о каком возрасте 17_YEARS, 18_YEARS';

			ALTER TABLE init_registration_info.info_sent_to_lk_epgu DROP CONSTRAINT IF EXISTS chk_info_source;
			ALTER TABLE init_registration_info.info_sent_to_lk_epgu ADD CONSTRAINT chk_info_source CHECK (info_source::text = ANY (ARRAY['PERSONAL_VISIT'::character varying::text, 'GIR_VU'::character varying::text, 'EPGU'::character varying::text]));
			ALTER TABLE init_registration_info.info_sent_to_lk_epgu DROP CONSTRAINT IF EXISTS chk_info_age;
			ALTER TABLE init_registration_info.info_sent_to_lk_epgu ADD CONSTRAINT chk_info_age CHECK (info_age::text = ANY (ARRAY['17_YEARS'::character varying::text, '18_YEARS'::character varying::text, 'EPGU'::character varying::text]));
			COMMENT ON COLUMN init_registration_info.info_sent_to_lk_epgu.info_age IS 'Информациия о каком возрасте 17_YEARS, 18_YEARS';

			ALTER TABLE init_registration_info.records_info_id_uk_id_ern DROP CONSTRAINT IF EXISTS chk_info_source;
			ALTER TABLE init_registration_info.records_info_id_uk_id_ern ADD CONSTRAINT chk_info_source CHECK (info_source::text = ANY (ARRAY['PERSONAL_VISIT'::character varying::text, 'GIR_VU'::character varying::text, 'EPGU'::character varying::text]));

			ALTER TABLE init_registration_info.records_info_id_uk_id_ern DROP CONSTRAINT IF EXISTS chk_info_source;
			ALTER TABLE init_registration_info.records_info_id_uk_id_ern ADD CONSTRAINT chk_info_source CHECK (info_source::text = ANY (ARRAY['PERSONAL_VISIT'::character varying::text, 'GIR_VU'::character varying::text, 'EPGU'::character varying::text]));
			ALTER TABLE init_registration_info.records_info_id_uk_id_ern DROP CONSTRAINT IF EXISTS chk_info_age;
			ALTER TABLE init_registration_info.records_info_id_uk_id_ern ADD CONSTRAINT chk_info_age CHECK (info_age::text = ANY (ARRAY['17_YEARS'::character varying::text, '18_YEARS'::character varying::text, 'EPGU'::character varying::text]));
			COMMENT ON COLUMN init_registration_info.records_info_id_uk_id_ern.info_age IS 'Информациия о каком возрасте 17_YEARS, 18_YEARS';

			ALTER TABLE registration_change_address.info_sent_to_lk_epgu DROP CONSTRAINT IF EXISTS chk_info_source;
			ALTER TABLE registration_change_address.info_sent_to_lk_epgu ADD CONSTRAINT chk_info_source CHECK (info_source::text = ANY (ARRAY['PERSONAL_VISIT'::character varying::text, 'GIR_VU'::character varying::text, 'EPGU'::character varying::text]));

			ALTER TABLE registration_change_address.summonses_sign DROP CONSTRAINT IF EXISTS chk_info_source;
			ALTER TABLE registration_change_address.summonses_sign ADD CONSTRAINT chk_info_source CHECK (info_source::text = ANY (ARRAY['PERSONAL_VISIT'::character varying::text, 'GIR_VU'::character varying::text, 'EPGU'::character varying::text]));

			ALTER TABLE summonses_list_subsystem.summonses_list DROP CONSTRAINT IF EXISTS chk_summonse_type;
			ALTER TABLE summonses_list_subsystem.summonses_list ADD CONSTRAINT chk_summonse_type CHECK (summonse_type::text = ANY (ARRAY['DRAFT_BOARD'::character varying::text, 'MILITARY_SERVICE'::character varying::text, 'MED_INSPECTION'::character varying::text, 'DOC_CLARIFICATION'::character varying::text, 'NOT_REGISTERED'::character varying::text]));
			
			CREATE OR REPLACE VIEW init_registration_info.view_init_registration_from_gir_vu AS
			SELECT
			init_registration_from_gir_vu_id,
			round(count_womens::numeric * 100 / count_all) AS percent_womens,
			round(count_received_citizenship::numeric * 100 / count_all) AS percent_received_citizenship,
			count_all - count_womens AS count_without_womens,
			count_all - count_received_citizenship AS count_without_received_citizenship		
			FROM init_registration_info.init_registration_from_gir_vu;
        </sql>
    </changeSet>
	
	    <changeSet id="0016" author="ilyin">
        <comment>create schema admin_indicators</comment>
        <sql>
			CREATE TABLE IF NOT EXISTS registration_change_address.applications_received_from_epgu
            (
            applications_received_from_epgu_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_all bigint NOT NULL DEFAULT 0,
            count_executor_appointed bigint NOT NULL DEFAULT 0,
            count_registered bigint NOT NULL DEFAULT 0,
            count_registration_refusal bigint NOT NULL DEFAULT 0,
            count_refusal_provide_service bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS registration_change_address.applications_received_from_epgu  OWNER to ervu_business_metrics;

            COMMENT ON TABLE registration_change_address.applications_received_from_epgu IS 'Постановка на воинский учет при смене адреса. Заявления, поступившие из ЕПГУ';
			
			COMMENT ON COLUMN registration_change_address.applications_received_from_epgu.count_executor_appointed IS 'Назначен исполнитель';
			COMMENT ON COLUMN registration_change_address.applications_received_from_epgu.count_registered IS 'Зарегистрировано заявлений';
			COMMENT ON COLUMN registration_change_address.applications_received_from_epgu.count_registration_refusal IS 'Получен отказ в регистрации';
			COMMENT ON COLUMN registration_change_address.applications_received_from_epgu.count_refusal_provide_service IS 'Получен отказ в предоставлении услуги';

            CREATE INDEX IF NOT EXISTS idx_applications_received_from_epgu_date ON registration_change_address.applications_received_from_epgu (info_date);
            CREATE INDEX IF NOT EXISTS idx_applications_received_from_epgu_recr ON registration_change_address.applications_received_from_epgu (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_applications_received_from_epgu_recr_date ON registration_change_address.applications_received_from_epgu (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW registration_change_address.view_applications_received_from_epgu AS
			SELECT
			applications_received_from_epgu_id,
			round(count_executor_appointed::numeric * 100 / count_all) AS percent_executor_appointed,
			round(count_registered::numeric * 100 / count_all) AS percent_registered,
			round(count_registration_refusal::numeric * 100 / count_all) AS percent_registration_refusal,
			round(count_refusal_provide_service::numeric * 100 / count_all) AS percent_refusal_provide_service
			FROM registration_change_address.applications_received_from_epgu;
        </sql>
    </changeSet>

    <changeSet id="0017" author="ilyin">
        <comment>create schema admin_indicators</comment>
        <sql>
			CREATE SCHEMA IF NOT EXISTS admin_indicators;
			
			CREATE TABLE IF NOT EXISTS admin_indicators.user_analysis
            (
            user_analysis_id bigserial NOT NULL PRIMARY KEY,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_offices bigint NOT NULL DEFAULT 0,
            count_reg_users bigint NOT NULL DEFAULT 0,
            count_active_users bigint NOT NULL DEFAULT 0,
            count_role_1 bigint NOT NULL DEFAULT 0,
            count_role_2 bigint NOT NULL DEFAULT 0,
            count_role_3 bigint NOT NULL DEFAULT 0,
            count_role_4 bigint NOT NULL DEFAULT 0,
            count_role_5 bigint NOT NULL DEFAULT 0,
            name_role_1 character varying NOT NULL,
            name_role_2 character varying NOT NULL,
            name_role_3 character varying NOT NULL,
            name_role_4 character varying NOT NULL,
            name_role_5 character varying NOT NULL
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS admin_indicators.user_analysis  OWNER to ervu_business_metrics;

            COMMENT ON TABLE admin_indicators.user_analysis IS 'Администрирование. Анализ пользователей';
			
			COMMENT ON COLUMN admin_indicators.user_analysis.count_offices IS 'количество военкоматов';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_reg_users IS 'зарегистрировано пользователей';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_active_users IS 'активных пользователей';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_role_1 IS 'количество пользователей с ролью 1';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_role_2 IS 'количество пользователей с ролью 2';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_role_3 IS 'количество пользователей с ролью 3';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_role_4 IS 'количество пользователей с ролью 4';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_role_5 IS 'количество пользователей с ролью 5';
			COMMENT ON COLUMN admin_indicators.user_analysis.name_role_1 IS 'имя роли 1';
			COMMENT ON COLUMN admin_indicators.user_analysis.name_role_2 IS 'имя роли 2';
			COMMENT ON COLUMN admin_indicators.user_analysis.name_role_3 IS 'имя роли 3';
			COMMENT ON COLUMN admin_indicators.user_analysis.name_role_4 IS 'имя роли 4';
			COMMENT ON COLUMN admin_indicators.user_analysis.name_role_5 IS 'имя роли 5';

            CREATE INDEX IF NOT EXISTS idx_user_analysis_date ON admin_indicators.user_analysis (info_date);
			
			
			CREATE TABLE IF NOT EXISTS admin_indicators.entry_analysis
            (
            entry_analysis_id bigserial NOT NULL PRIMARY KEY,
			recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_incorrect_authentication bigint NOT NULL DEFAULT 0,
            first_time_entry timestamp,
            last_time_entry timestamp,
            time_out timestamp,
            work_time_all character varying,
            work_time_last character varying,
            interval_before_entry character varying,
            average_session_time character varying
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS admin_indicators.entry_analysis  OWNER to ervu_business_metrics;

            COMMENT ON TABLE admin_indicators.entry_analysis IS 'Администрирование. Анализ входов';
			
			COMMENT ON COLUMN admin_indicators.entry_analysis.count_incorrect_authentication IS 'количество неверных аутентификации';
			COMMENT ON COLUMN admin_indicators.entry_analysis.first_time_entry IS 'время первого входа в систему';
			COMMENT ON COLUMN admin_indicators.entry_analysis.last_time_entry IS 'время последнего входа в систему';
			COMMENT ON COLUMN admin_indicators.entry_analysis.time_out IS 'время выхода из системы';
			COMMENT ON COLUMN admin_indicators.entry_analysis.work_time_all IS 'время работы в системе (общее)';
			COMMENT ON COLUMN admin_indicators.entry_analysis.work_time_last IS 'время работы в системе (последняя сессия)';
			COMMENT ON COLUMN admin_indicators.entry_analysis.interval_before_entry IS 'среднее время между входами';
			COMMENT ON COLUMN admin_indicators.entry_analysis.average_session_time IS 'среднее время сессии';

            CREATE INDEX IF NOT EXISTS idx_entry_analysis_date ON admin_indicators.entry_analysis (info_date);
            CREATE INDEX IF NOT EXISTS idx_entry_analysis_recr ON admin_indicators.entry_analysis (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_entry_analysis_recr_date ON admin_indicators.entry_analysis (recruitment_id, info_date);
			
			
			CREATE TABLE IF NOT EXISTS admin_indicators.last_entry_time_by_days
            (
            last_entry_time_by_days_id bigserial NOT NULL PRIMARY KEY,
			recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_mon bigint NOT NULL DEFAULT 0,
            count_tue bigint NOT NULL DEFAULT 0,
            count_wed bigint NOT NULL DEFAULT 0,
            count_thu bigint NOT NULL DEFAULT 0,
            count_fri bigint NOT NULL DEFAULT 0,
            count_sat bigint NOT NULL DEFAULT 0,
            count_sun bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS admin_indicators.last_entry_time_by_days  OWNER to ervu_business_metrics;

            COMMENT ON TABLE admin_indicators.last_entry_time_by_days IS 'Администрирование. Распределение времени последнего входа по дням';
			
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_days.count_mon IS 'пн';
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_days.count_tue IS 'вт';
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_days.count_wed IS 'ср';
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_days.count_thu IS 'чт';
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_days.count_fri IS 'пт';
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_days.count_sat IS 'сб';
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_days.count_sun IS 'вс';

            CREATE INDEX IF NOT EXISTS idx_last_entry_time_by_days_date ON admin_indicators.last_entry_time_by_days (info_date);
            CREATE INDEX IF NOT EXISTS idx_last_entry_time_by_days_recr ON admin_indicators.last_entry_time_by_days (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_last_entry_time_by_days_recr_date ON admin_indicators.last_entry_time_by_days (recruitment_id, info_date);
			
			
						
			CREATE TABLE IF NOT EXISTS admin_indicators.last_entry_time_by_hours
            (
            last_entry_time_by_hours_id bigserial NOT NULL PRIMARY KEY,
			recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_06_08 bigint NOT NULL DEFAULT 0,
            count_08_10 bigint NOT NULL DEFAULT 0,
            count_10_12 bigint NOT NULL DEFAULT 0,
            count_12_14 bigint NOT NULL DEFAULT 0,
            count_14_16 bigint NOT NULL DEFAULT 0,
            count_16_18 bigint NOT NULL DEFAULT 0,
            count_18_20 bigint NOT NULL DEFAULT 0,
            count_20_22 bigint NOT NULL DEFAULT 0,
            count_22_06 bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS admin_indicators.last_entry_time_by_hours  OWNER to ervu_business_metrics;

            COMMENT ON TABLE admin_indicators.last_entry_time_by_hours IS 'Администрирование. Распределение времени последнего входа по времени суток';
			
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_hours.count_06_08 IS 'количество входов с 06:00 до 08:00';
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_hours.count_08_10 IS 'количество входов с 08:00 до 10:00';
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_hours.count_10_12 IS 'количество входов с 10:00 до 12:00';
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_hours.count_12_14 IS 'количество входов с 12:00 до 14:00';
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_hours.count_14_16 IS 'количество входов с 14:00 до 16:00';
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_hours.count_16_18 IS 'количество входов с 16:00 до 18:00';
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_hours.count_18_20 IS 'количество входов с 18:00 до 20:00';
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_hours.count_20_22 IS 'количество входов с 20:00 до 22:00';
			COMMENT ON COLUMN admin_indicators.last_entry_time_by_hours.count_22_06 IS 'количество входов с 22:00 до 06:00';

            CREATE INDEX IF NOT EXISTS idx_last_entry_time_by_hours_date ON admin_indicators.last_entry_time_by_hours (info_date);
            CREATE INDEX IF NOT EXISTS idx_last_entry_time_by_hours_recr ON admin_indicators.last_entry_time_by_hours (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_last_entry_time_by_hours_recr_date ON admin_indicators.last_entry_time_by_hours (recruitment_id, info_date);
			
			
			
			CREATE TABLE IF NOT EXISTS admin_indicators.ip_adresses
            (
            ip_adresses_id bigserial NOT NULL PRIMARY KEY,
			recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            ip_address character varying NOT NULL,
            entry_date timestamp without time zone NOT NULL DEFAULT now()
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS admin_indicators.ip_adresses  OWNER to ervu_business_metrics;

            COMMENT ON TABLE admin_indicators.ip_adresses IS 'Администрирование. IP адреса';

            CREATE INDEX IF NOT EXISTS idx_ip_adresses_date ON admin_indicators.ip_adresses (info_date);
            CREATE INDEX IF NOT EXISTS idx_ip_adresses_recr ON admin_indicators.ip_adresses (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_ip_adresses_recr_date ON admin_indicators.ip_adresses (recruitment_id, info_date);
			
			
			CREATE TABLE IF NOT EXISTS admin_indicators.feedback_analysis
            (
            feedback_analysis_id bigserial NOT NULL PRIMARY KEY,
			recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_offer bigint NOT NULL DEFAULT 0,
            count_claim bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS admin_indicators.feedback_analysis  OWNER to ervu_business_metrics;

            COMMENT ON TABLE admin_indicators.feedback_analysis IS 'Администрирование. Анализ обратной связи';
			
            COMMENT ON COLUMN admin_indicators.feedback_analysis.count_offer IS 'предложения';
            COMMENT ON COLUMN admin_indicators.feedback_analysis.count_claim IS 'жалобы';

            CREATE INDEX IF NOT EXISTS idx_feedback_analysis_date ON admin_indicators.feedback_analysis (info_date);
            CREATE INDEX IF NOT EXISTS idx_feedback_analysis_recr ON admin_indicators.feedback_analysis (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_feedback_analysis_recr_date ON admin_indicators.feedback_analysis (recruitment_id, info_date);
			
			
			CREATE TABLE IF NOT EXISTS admin_indicators.user_account_analysis
            (
            user_account_analysis_id bigserial NOT NULL PRIMARY KEY,
			recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_created bigint NOT NULL DEFAULT 0,
            count_change_password bigint NOT NULL DEFAULT 0,
            count_change_role bigint NOT NULL DEFAULT 0,
            count_change_org bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS admin_indicators.user_account_analysis  OWNER to ervu_business_metrics;

            COMMENT ON TABLE admin_indicators.user_account_analysis IS 'Администрирование. Анализ учетных записей';
			
            COMMENT ON COLUMN admin_indicators.user_account_analysis.count_created IS 'созданные записи';
            COMMENT ON COLUMN admin_indicators.user_account_analysis.count_change_password IS 'смена пароля';
            COMMENT ON COLUMN admin_indicators.user_account_analysis.count_change_role IS 'изменение ролей';
            COMMENT ON COLUMN admin_indicators.user_account_analysis.count_change_org IS 'изменение организаций';

            CREATE INDEX IF NOT EXISTS idx_user_account_analysis_date ON admin_indicators.user_account_analysis (info_date);
            CREATE INDEX IF NOT EXISTS idx_user_account_analysis_recr ON admin_indicators.user_account_analysis (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_user_account_analysis_recr_date ON admin_indicators.user_account_analysis (recruitment_id, info_date);
			
			
			CREATE TABLE IF NOT EXISTS admin_indicators.report_analysis
            (
            report_analysis_id bigserial NOT NULL PRIMARY KEY,
			recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_formed bigint NOT NULL DEFAULT 0,
            count_download bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS admin_indicators.report_analysis  OWNER to ervu_business_metrics;

            COMMENT ON TABLE admin_indicators.report_analysis IS 'Администрирование. Анализ формирования отчетов';
			
            COMMENT ON COLUMN admin_indicators.report_analysis.count_formed IS 'сформировано отчетов';
            COMMENT ON COLUMN admin_indicators.report_analysis.count_download IS 'качано отчетов';

            CREATE INDEX IF NOT EXISTS idx_report_analysis_date ON admin_indicators.report_analysis (info_date);
            CREATE INDEX IF NOT EXISTS idx_report_analysis_recr ON admin_indicators.report_analysis (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_report_analysis_recr_date ON admin_indicators.report_analysis (recruitment_id, info_date);
			
			
			CREATE TABLE IF NOT EXISTS admin_indicators.event_msg_jour
            (
            event_msg_jour_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_authentication bigint NOT NULL DEFAULT 0,
            count_user_operation bigint NOT NULL DEFAULT 0,
            count_outer_system bigint NOT NULL DEFAULT 0,
            count_data_exchange bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS admin_indicators.event_msg_jour  OWNER to ervu_business_metrics;

            COMMENT ON TABLE admin_indicators.event_msg_jour IS 'Администрирование. Сообщения в журнале событий';
			
			COMMENT ON COLUMN admin_indicators.event_msg_jour.count_authentication IS 'об аутентификации пользователей';
			COMMENT ON COLUMN admin_indicators.event_msg_jour.count_user_operation IS 'об операциях пользователей';
			COMMENT ON COLUMN admin_indicators.event_msg_jour.count_outer_system IS 'о взаимодействии с внешними системами';
			COMMENT ON COLUMN admin_indicators.event_msg_jour.count_data_exchange IS 'об обмене данных с Реестром повесток';

            CREATE INDEX IF NOT EXISTS idx_event_msg_jour_date ON admin_indicators.event_msg_jour (info_date);
            CREATE INDEX IF NOT EXISTS idx_event_msg_jour_recr ON admin_indicators.event_msg_jour (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_event_msg_jour_recr_date ON admin_indicators.event_msg_jour (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW admin_indicators.view_event_msg_jour AS
			SELECT
			event_msg_jour_id,
			count_authentication + count_user_operation + count_outer_system + count_data_exchange AS count_all,
			round(count_authentication::numeric * 100 / (count_authentication + count_user_operation + count_outer_system + count_data_exchange)) AS percent_authentication,
			round(count_user_operation::numeric * 100 / (count_authentication + count_user_operation + count_outer_system + count_data_exchange)) AS percent_user_operation,
			round(count_outer_system::numeric * 100 / (count_authentication + count_user_operation + count_outer_system + count_data_exchange)) AS percent_outer_system,
			round(count_data_exchange::numeric * 100 / (count_authentication + count_user_operation + count_outer_system + count_data_exchange)) AS percent_data_exchange
			FROM admin_indicators.event_msg_jour;
        </sql>
    </changeSet>

        <changeSet id="0018" author="ilyin">
        <comment>add_user_list_table</comment>
        <sql>
			CREATE TABLE IF NOT EXISTS metrics.users
            (
            users_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
			start_date timestamp without time zone NOT NULL DEFAULT now(),
			close_date timestamp without time zone,
			user_login character varying NOT NULL,
			user_secondname character varying NOT NULL,
			user_firstname character varying NOT NULL,
			user_middlename character varying NOT NULL,
			user_fio character varying NOT NULL
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS metrics.users  OWNER to ervu_business_metrics;

            COMMENT ON TABLE metrics.users IS 'Пользователи';
			
			CREATE INDEX IF NOT EXISTS idx_users_recr ON metrics.users (recruitment_id);
        </sql>
    </changeSet>

    <changeSet id="0019" author="ilyin">
        <comment>CREATE SCHEMA journal_log</comment>
        <sql>
			CREATE SCHEMA IF NOT EXISTS journal_log;
				
			CREATE TABLE IF NOT EXISTS journal_log.message_journal
            (
            message_journal_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
			info_source character varying NOT NULL,
			org_source character varying NOT NULL,
            count_subpoena bigint NOT NULL DEFAULT 0,
            count_ern bigint NOT NULL DEFAULT 0,
            count_app_epgu bigint NOT NULL DEFAULT 0,
            count_tmp_measures bigint NOT NULL DEFAULT 0,
			CONSTRAINT chk_info_source CHECK (info_source::text = ANY (ARRAY['IN'::character varying::text, 'OUT'::character varying::text, 'FORMED'::character varying::text, 'IN_ERROR'::character varying::text])),
			CONSTRAINT chk_org_source CHECK (org_source::text = ANY (ARRAY['MVD'::character varying::text, 'FBI'::character varying::text, 'FNS'::character varying::text, 'MEASURES_RELEASE'::character varying::text, 'MIN_DIGIT'::character varying::text, 'RUS_REESTR'::character varying::text]))
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.message_journal  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.message_journal IS 'Журналирование. Журналы входящих, исходящих, сформированных сообщений';
			
			CREATE INDEX IF NOT EXISTS idx_message_journal_recr ON journal_log.message_journal (recruitment_id);
			CREATE INDEX IF NOT EXISTS idx_message_journal_date ON journal_log.message_journal (info_date);
			CREATE INDEX IF NOT EXISTS idx_message_journal_recr_date ON journal_log.message_journal (recruitment_id, info_date);
			
			COMMENT ON COLUMN journal_log.message_journal.info_source IS 'тип сообщений IN - входящие, OUT - исходящие, FORMED - сформированные, IN_ERROR - входящие с ошибками';
			COMMENT ON COLUMN journal_log.message_journal.org_source IS 'тип организаций MVD - МДВ, FBI - ФБР, FNS - ФНС, MEASURES_RELEASE - Реализ. мер, MIN_DIGIT - минцифры, RUS_REESTR - Росреестр';
			COMMENT ON COLUMN journal_log.message_journal.count_subpoena IS 'реестр повесток';
			COMMENT ON COLUMN journal_log.message_journal.count_ern IS 'сведения об ерн';
			COMMENT ON COLUMN journal_log.message_journal.count_app_epgu IS 'прием заявлений с епгу';
			COMMENT ON COLUMN journal_log.message_journal.count_tmp_measures IS 'введение временных мер';
			
			
			
			CREATE TABLE IF NOT EXISTS journal_log.external_system_transaction
            (
            external_system_transaction_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_in bigint NOT NULL DEFAULT 0,
            count_out bigint NOT NULL DEFAULT 0,
            count_formed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.external_system_transaction  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.external_system_transaction IS 'Журналирование. Транзакции с внешними систиемами';
			
			CREATE INDEX IF NOT EXISTS idx_external_system_transaction_recr ON journal_log.external_system_transaction (recruitment_id);
			CREATE INDEX IF NOT EXISTS idx_external_system_transaction_date ON journal_log.external_system_transaction (info_date);
			CREATE INDEX IF NOT EXISTS idx_external_system_transaction_recr_date ON journal_log.external_system_transaction (recruitment_id, info_date);
			
			COMMENT ON COLUMN journal_log.external_system_transaction.count_in IS 'вх сообщения';
			COMMENT ON COLUMN journal_log.external_system_transaction.count_out IS 'исх сообщения';
			COMMENT ON COLUMN journal_log.external_system_transaction.count_formed IS 'сформированные сообщения';
			
			
			CREATE TABLE IF NOT EXISTS journal_log.dictionary_update
            (
            dictionary_update_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_dictionary_1 bigint NOT NULL DEFAULT 0,
            count_dictionary_2 bigint NOT NULL DEFAULT 0,
            count_dictionary_3 bigint NOT NULL DEFAULT 0,
            count_dictionary_4 bigint NOT NULL DEFAULT 0,
            name_dictionary_1 character varying NOT NULL,
            name_dictionary_2 character varying NOT NULL,
            name_dictionary_3 character varying NOT NULL,
            name_dictionary_4 character varying NOT NULL
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.dictionary_update  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.dictionary_update IS 'Журналирование. Обновление справочников';

            CREATE INDEX IF NOT EXISTS idx_dictionary_update_date ON journal_log.dictionary_update (info_date);
            CREATE INDEX IF NOT EXISTS idx_dictionary_update_recr ON journal_log.dictionary_update (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_dictionary_update_recr_date ON journal_log.dictionary_update (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW journal_log.view_dictionary_update AS
			SELECT
			dictionary_update_id,
			count_dictionary_1 + count_dictionary_2 + count_dictionary_3 + count_dictionary_4 AS count_all,
			count_dictionary_2 + count_dictionary_3 + count_dictionary_4 AS count_without_1,
			count_dictionary_1 + count_dictionary_3 + count_dictionary_4 AS count_without_2,
			count_dictionary_1 + count_dictionary_2 + count_dictionary_4 AS count_without_3,
			count_dictionary_1 + count_dictionary_2 + count_dictionary_4 AS count_without_4
			FROM journal_log.dictionary_update;
			
			
			CREATE TABLE IF NOT EXISTS journal_log.summonses_list_data_exchange
            (
            summonses_list_data_exchange_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_entry_system bigint NOT NULL DEFAULT 0,
            count_entry_system_error bigint NOT NULL DEFAULT 0,
            count_request_extract bigint NOT NULL DEFAULT 0,
            count_request_extract_error bigint NOT NULL DEFAULT 0            
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.summonses_list_data_exchange  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.summonses_list_data_exchange IS 'Журналирование. Обмен данными с реестром повесток';

            CREATE INDEX IF NOT EXISTS idx_summonses_list_data_exchange_date ON journal_log.summonses_list_data_exchange (info_date);
            CREATE INDEX IF NOT EXISTS idx_summonses_list_data_exchange_recr ON journal_log.summonses_list_data_exchange (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_summonses_list_data_exchange_recr_date ON journal_log.summonses_list_data_exchange (recruitment_id, info_date);
			
			COMMENT ON COLUMN journal_log.summonses_list_data_exchange.count_entry_system IS 'вход в систему';
			COMMENT ON COLUMN journal_log.summonses_list_data_exchange.count_entry_system_error IS 'вход в систему с ошибкой';
			COMMENT ON COLUMN journal_log.summonses_list_data_exchange.count_request_extract IS 'запрос выписки';
			COMMENT ON COLUMN journal_log.summonses_list_data_exchange.count_request_extract_error IS 'запрос выписки с ошибкой';
			
			CREATE OR REPLACE VIEW journal_log.view_summonses_list_data_exchange AS
			SELECT
			summonses_list_data_exchange_id,
			count_entry_system + count_request_extract AS count_all,
			round(count_entry_system::numeric * 100 / (count_entry_system + count_request_extract)) AS percent_entry_system,
			round(count_entry_system_error::numeric * 100 / count_entry_system) AS percent_entry_system_error,
			round(count_request_extract::numeric * 100 / (count_entry_system + count_request_extract)) AS percent_request_extract,
			round(count_request_extract_error::numeric * 100 / count_request_extract) AS percent_request_extract_error
			FROM journal_log.summonses_list_data_exchange;
			
			
			CREATE TABLE IF NOT EXISTS journal_log.summonses_list_data_exchange_error
            (
            summonses_list_data_exchange_error_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_error_1 bigint NOT NULL DEFAULT 0,
            count_error_2 bigint NOT NULL DEFAULT 0,
            count_error_3 bigint NOT NULL DEFAULT 0,
            count_error_4 bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.summonses_list_data_exchange_error  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.summonses_list_data_exchange_error IS 'Журналирование. Ошибки обмена данными с реестром повесток';

            CREATE INDEX IF NOT EXISTS idx_summonses_list_data_exchange_error_date ON journal_log.summonses_list_data_exchange_error (info_date);
            CREATE INDEX IF NOT EXISTS idx_summonses_list_data_exchange_error_recr ON journal_log.summonses_list_data_exchange_error (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_summonses_list_data_exchange_error_recr_date ON journal_log.summonses_list_data_exchange_error (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW journal_log.view_summonses_list_data_exchange_error AS
			SELECT
			summonses_list_data_exchange_error_id,
			count_error_1 + count_error_2 + count_error_3 + count_error_4 AS count_all,
			round(count_error_1::numeric * 100 / (count_error_1 + count_error_2 + count_error_3 + count_error_4)) AS percent_error_1,
			round(count_error_2::numeric * 100 / (count_error_1 + count_error_2 + count_error_3 + count_error_4)) AS percent_error_2,
			round(count_error_3::numeric * 100 / (count_error_1 + count_error_2 + count_error_3 + count_error_4)) AS percent_error_3,
			round(count_error_4::numeric * 100 / (count_error_1 + count_error_2 + count_error_3 + count_error_4)) AS percent_error_4
			FROM journal_log.summonses_list_data_exchange_error;
			
			
			CREATE TABLE IF NOT EXISTS journal_log.epgu_notice_sent
            (
            epgu_notice_sent_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_0records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_notice_1 bigint NOT NULL DEFAULT 0,
            count_notice_2 bigint NOT NULL DEFAULT 0,
            count_notice_3 bigint NOT NULL DEFAULT 0,
            count_notice_4 bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.epgu_notice_sent  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.epgu_notice_sent IS 'Журналирование. Направление уведомлений на ЕПГУ';

            CREATE INDEX IF NOT EXISTS idx_epgu_notice_sent_date ON journal_log.epgu_notice_sent (info_date);
            CREATE INDEX IF NOT EXISTS idx_epgu_notice_sent_recr ON journal_log.epgu_notice_sent (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_epgu_notice_sent_recr_date ON journal_log.epgu_notice_sent (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW journal_log.view_epgu_notice_sent AS
			SELECT
			epgu_notice_sent_id,
			count_notice_1 + count_notice_2 + count_notice_3 + count_notice_4 AS count_all,
			round(count_notice_1::numeric * 100 / (count_notice_1 + count_notice_2 + count_notice_3 + count_notice_4)) AS percent_notice_1,
			round(count_notice_2::numeric * 100 / (count_notice_1 + count_notice_2 + count_notice_3 + count_notice_4)) AS percent_notice_2,
			round(count_notice_3::numeric * 100 / (count_notice_1 + count_notice_2 + count_notice_3 + count_notice_4)) AS percent_notice_3,
			round(count_notice_4::numeric * 100 / (count_notice_1 + count_notice_2 + count_notice_3 + count_notice_4)) AS percent_notice_4
			FROM journal_log.epgu_notice_sent;
			
			
			CREATE TABLE IF NOT EXISTS journal_log.epgu_notice_sent_error
            (
            epgu_notice_sent_error_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_sending_timeout bigint NOT NULL DEFAULT 0,
            count_delivery_timeout bigint NOT NULL DEFAULT 0,
            count_error bigint NOT NULL DEFAULT 0,
            count_not_formed bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.epgu_notice_sent_error  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.epgu_notice_sent_error IS 'Журналирование. Ошибки направлений уведомлений на ЕПГУ';
			
			COMMENT ON COLUMN journal_log.epgu_notice_sent_error.count_sending_timeout IS 'превышено время ожидания отправки';
			COMMENT ON COLUMN journal_log.epgu_notice_sent_error.count_delivery_timeout IS 'превышено время ожидания доставки';
			COMMENT ON COLUMN journal_log.epgu_notice_sent_error.count_error IS 'ошибка. адресат не найден';
			COMMENT ON COLUMN journal_log.epgu_notice_sent_error.count_not_formed IS 'не сформировано';

            CREATE INDEX IF NOT EXISTS idx_epgu_notice_sent_error_date ON journal_log.epgu_notice_sent_error (info_date);
            CREATE INDEX IF NOT EXISTS idx_epgu_notice_sent_error_recr ON journal_log.epgu_notice_sent_error (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_epgu_notice_sent_error_recr_date ON journal_log.epgu_notice_sent_error (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW journal_log.view_epgu_notice_sent_error AS
			SELECT
			epgu_notice_sent_error_id,
			count_sending_timeout + count_delivery_timeout + count_error + count_not_formed AS count_all,
			round(count_sending_timeout::numeric * 100 / (count_sending_timeout + count_delivery_timeout + count_error + count_not_formed)) AS percent_sending_timeout,
			round(count_delivery_timeout::numeric * 100 / (count_sending_timeout + count_delivery_timeout + count_error + count_not_formed)) AS percent_delivery_timeout,
			round(count_error::numeric * 100 / (count_sending_timeout + count_delivery_timeout + count_error + count_not_formed)) AS percent_error,
			round(count_not_formed::numeric * 100 / (count_sending_timeout + count_delivery_timeout + count_error + count_not_formed)) AS percent_not_formed
			FROM journal_log.epgu_notice_sent_error;
			
			
			CREATE TABLE IF NOT EXISTS journal_log.load_record_jour
            (
            load_record_jour_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            info_source character varying NOT NULL,
            count_inform_system bigint NOT NULL DEFAULT 0,
            count_gir_vu bigint NOT NULL DEFAULT 0,
            count_compare_citizen_info bigint NOT NULL DEFAULT 0,
			CONSTRAINT chk_info_source CHECK (info_source::text = ANY (ARRAY['LOAD'::character varying::text, 'ERROR'::character varying::text]))
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.load_record_jour  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.load_record_jour IS 'Журналирование. Журнал загрузки записей. Загруженные и незагруженные записи';
			
			COMMENT ON COLUMN journal_log.load_record_jour.info_source IS 'результат загрузки LOAD - успешно загружено, ERROR - ошибка загрузки';
			COMMENT ON COLUMN journal_log.load_record_jour.count_inform_system IS 'из информ. систем МО РФ';
			COMMENT ON COLUMN journal_log.load_record_jour.count_gir_vu IS 'из ГИР ВУ';
			COMMENT ON COLUMN journal_log.load_record_jour.count_compare_citizen_info IS 'сверка сведений граждан';

            CREATE INDEX IF NOT EXISTS idx_load_record_jour_date ON journal_log.load_record_jour (info_date);
            CREATE INDEX IF NOT EXISTS idx_load_record_jour_recr ON journal_log.load_record_jour (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_load_record_jour_recr_date ON journal_log.load_record_jour (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW journal_log.view_load_record_jour AS
			SELECT
			load_record_jour_id,
			count_inform_system + count_gir_vu + count_compare_citizen_info AS count_all,
			round(count_inform_system::numeric * 100 / (count_inform_system + count_gir_vu + count_compare_citizen_info)) AS percent_inform_system,
			round(count_gir_vu::numeric * 100 / (count_inform_system + count_gir_vu + count_compare_citizen_info)) AS percent_gir_vu,
			round(count_compare_citizen_info::numeric * 100 / (count_inform_system + count_gir_vu + count_compare_citizen_info)) AS percent_compare_citizen_info
			FROM journal_log.load_record_jour;
			
			
			CREATE TABLE IF NOT EXISTS journal_log.processing_time_files_and_records
            (
            processing_time_files_and_records_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            info_source character varying NOT NULL,
            record_time_sec bigint NOT NULL DEFAULT 0,
            file_time_sec bigint NOT NULL DEFAULT 0,
            count_files_in_record bigint NOT NULL DEFAULT 0
			CONSTRAINT chk_info_source CHECK (info_source::text = ANY (ARRAY['CSV'::character varying::text, 'XML'::character varying::text]))
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.processing_time_files_and_records  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.processing_time_files_and_records IS 'Журналирование. Журнал загрузки записей. Среднее время обработки файлов и записей';
			
			COMMENT ON COLUMN journal_log.processing_time_files_and_records.info_source IS 'тип фапйла CSV или XML';
			COMMENT ON COLUMN journal_log.processing_time_files_and_records.record_time_sec IS 'время обработки записи';
			COMMENT ON COLUMN journal_log.processing_time_files_and_records.file_time_sec IS 'время обработки файла';
			COMMENT ON COLUMN journal_log.processing_time_files_and_records.count_files_in_record IS 'количество записей в файле';

            CREATE INDEX IF NOT EXISTS idx_processing_time_files_and_records_date ON journal_log.processing_time_files_and_records (info_date);
            CREATE INDEX IF NOT EXISTS idx_processing_time_files_and_records_recr ON journal_log.processing_time_files_and_records (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_processing_time_files_and_records_recr_date ON journal_log.processing_time_files_and_records (recruitment_id, info_date);



			CREATE TABLE IF NOT EXISTS journal_log.processed_records
            (
            processed_records_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_flk_error bigint NOT NULL DEFAULT 0,
            count_time_error bigint NOT NULL DEFAULT 0,
            count_new bigint NOT NULL DEFAULT 0,
            count_actual bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.processed_records  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.processed_records IS 'Журналирование. Журнал загрузки записей. Обработанные записи';
			
			COMMENT ON COLUMN journal_log.processed_records.count_flk_error IS 'ошибки ФЛК';
			COMMENT ON COLUMN journal_log.processed_records.count_time_error IS 'ошибки времени';
			COMMENT ON COLUMN journal_log.processed_records.count_new IS 'новые записи';
			COMMENT ON COLUMN journal_log.processed_records.count_actual IS 'актуализированные записи';

            CREATE INDEX IF NOT EXISTS idx_processed_records_date ON journal_log.processed_records (info_date);
            CREATE INDEX IF NOT EXISTS idx_processed_records_recr ON journal_log.processed_records (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_processed_records_recr_date ON journal_log.processed_records (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW journal_log.view_processed_records AS
			SELECT
			processed_records_id,
			count_flk_error + count_time_error + count_new + count_actual AS count_all,
			round(count_flk_error::numeric * 100 / (count_flk_error + count_time_error + count_new + count_actual)) AS percent_flk_error,
			round(count_time_error::numeric * 100 / (count_flk_error + count_time_error + count_new + count_actual)) AS percent_time_error,
			round(count_new::numeric * 100 / (count_flk_error + count_time_error + count_new + count_actual)) AS percent_new,
			round(count_actual::numeric * 100 / (count_flk_error + count_time_error + count_new + count_actual)) AS percent_actual
			FROM journal_log.processed_records;
			
			
			CREATE TABLE IF NOT EXISTS journal_log.load_files_jour
            (
            load_files_jour_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_load_without_error bigint NOT NULL DEFAULT 0,
            count_not_load_by_error bigint NOT NULL DEFAULT 0,
            count_load_with_error bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.load_files_jour  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.load_files_jour IS 'Журналирование. Журнал загрузки файлов';
			
			COMMENT ON COLUMN journal_log.load_files_jour.count_load_without_error IS 'загружено без ошибок';
			COMMENT ON COLUMN journal_log.load_files_jour.count_not_load_by_error IS 'не загружено из-за ошибок';
			COMMENT ON COLUMN journal_log.load_files_jour.count_load_with_error IS 'загружено с ошибками';

            CREATE INDEX IF NOT EXISTS idx_load_files_jour_date ON journal_log.load_files_jour (info_date);
            CREATE INDEX IF NOT EXISTS idx_load_files_jour_recr ON journal_log.load_files_jour (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_load_files_jour_recr_date ON journal_log.load_files_jour (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW journal_log.view_load_files_jour AS
			SELECT
			load_files_jour_id,
			count_load_without_error + count_not_load_by_error + count_load_with_error AS count_all,
			round(count_load_without_error::numeric * 100 / (count_load_without_error + count_not_load_by_error + count_load_with_error)) AS percent_load_without_error,
			round(count_not_load_by_error::numeric * 100 / (count_load_without_error + count_not_load_by_error + count_load_with_error)) AS percent_not_load_by_error,
			round(count_load_with_error::numeric * 100 / (count_load_without_error + count_not_load_by_error + count_load_with_error)) AS percent_load_with_error
			FROM journal_log.load_files_jour;
			
			
			CREATE TABLE IF NOT EXISTS journal_log.load_files_speed_jour
            (
            load_files_speed_jour_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_files_in_record bigint NOT NULL DEFAULT 0,
            file_time_sec bigint NOT NULL DEFAULT 0,
            file_name character varying NOT NULL
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.load_files_speed_jour  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.load_files_speed_jour IS 'Журналирование. Скорость загрузки файлов';
			
			COMMENT ON COLUMN journal_log.load_files_speed_jour.count_files_in_record IS 'количество записей в файле';
			COMMENT ON COLUMN journal_log.load_files_speed_jour.file_time_sec IS 'время загрузки файла';
			COMMENT ON COLUMN journal_log.load_files_speed_jour.file_name IS 'имя файла';

            CREATE INDEX IF NOT EXISTS idx_load_files_speed_jour_date ON journal_log.load_files_speed_jour (info_date);
            CREATE INDEX IF NOT EXISTS idx_load_files_speed_jour_recr ON journal_log.load_files_speed_jour (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_load_files_speed_jour_recr_date ON journal_log.load_files_speed_jour (recruitment_id, info_date);
			
			
			CREATE TABLE IF NOT EXISTS journal_log.user_info
            (
            user_info_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_recruitment_id references metrics.recruitment,
            user_id bigint NOT NULL constraint fk_user_id references metrics.users,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_role bigint NOT NULL DEFAULT 0,
            count_error bigint NOT NULL DEFAULT 0,
            count_authentication bigint NOT NULL DEFAULT 0,
            count_work_together bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.user_info  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.user_info IS 'Журналирование. Информация о пользователе';
			
            CREATE INDEX IF NOT EXISTS idx_user_info_date ON journal_log.user_info (info_date);
            CREATE INDEX IF NOT EXISTS idx_user_info_recr ON journal_log.user_info (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_user_info_user ON journal_log.user_info (user_id);
            CREATE INDEX IF NOT EXISTS idx_user_info_recr_date ON journal_log.user_info (recruitment_id, info_date);
			
			
			
			CREATE TABLE IF NOT EXISTS journal_log.user_work_time
            (
            user_work_time_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_recruitment_id references metrics.recruitment,
            user_id bigint NOT NULL constraint fk_user_id references metrics.users,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
			start_working_time timestamp without time zone NOT NULL DEFAULT now(),
			stop_working_time timestamp without time zone NOT NULL DEFAULT now()
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.user_work_time  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.user_work_time IS 'Журналирование. Время работы пользователей';
			
            CREATE INDEX IF NOT EXISTS idx_user_work_time_date ON journal_log.user_work_time (info_date);
            CREATE INDEX IF NOT EXISTS idx_user_work_time_recr ON journal_log.user_work_time (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_user_work_time_user ON journal_log.user_work_time (user_id);
            CREATE INDEX IF NOT EXISTS idx_user_work_time_recr_date ON journal_log.user_work_time (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW journal_log.view_user_work_time AS
			SELECT
			user_work_time_id,
			stop_working_time - start_working_time AS working_time
			FROM journal_log.user_work_time;
			
			
			CREATE TABLE IF NOT EXISTS journal_log.user_operation
            (
            user_operation_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
			user_id bigint constraint fk_user_id references metrics.users,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            info_source character varying NOT NULL,
            action_source character varying NOT NULL,
            count_1 bigint NOT NULL DEFAULT 0,
            count_2 bigint NOT NULL DEFAULT 0,
            count_3 bigint NOT NULL DEFAULT 0,
			CONSTRAINT chk_info_source CHECK((info_source = 'USER' AND user_id IS NOT NULL) OR info_source = 'VK'),
			CONSTRAINT chk_action_source CHECK (action_source::text = ANY (ARRAY['CATEGORY'::character varying::text, 'ACTION_ANALYSIS'::character varying::text]))			
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.user_operation  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.user_operation IS 'Журналирование. Операции пользователей по категориям/Анализ действий';
			
			COMMENT ON COLUMN journal_log.user_operation.info_source IS 'USER - в разрезе пользователей, VK - в разрезе ВК';
			COMMENT ON COLUMN journal_log.user_operation.action_source IS 'CATEGORY - Операции пользователей по категориям, ACTION_ANALYSIS - Анализ действий';
			
            CREATE INDEX IF NOT EXISTS idx_user_operation_date ON journal_log.user_operation (info_date);
            CREATE INDEX IF NOT EXISTS idx_user_operation_recr ON journal_log.user_operation (recruitment_id);
			CREATE INDEX IF NOT EXISTS idx_user_operation_user ON journal_log.user_operation (user_id);
            CREATE INDEX IF NOT EXISTS idx_user_operation_recr_date ON journal_log.user_operation (recruitment_id, info_date);


			CREATE TABLE IF NOT EXISTS journal_log.sign_journal
            (
            sign_journal_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_sign bigint NOT NULL DEFAULT 0,
            count_cancel_sign bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS journal_log.sign_journal  OWNER to ervu_business_metrics;

            COMMENT ON TABLE journal_log.sign_journal IS 'Журналирование. Подписание УКЭП';
			
            CREATE INDEX IF NOT EXISTS idx_sign_journal_date ON journal_log.sign_journal (info_date);
            CREATE INDEX IF NOT EXISTS idx_sign_journal_recr ON journal_log.sign_journal (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_sign_journal_recr_date ON journal_log.sign_journal (recruitment_id, info_date);
			
			CREATE OR REPLACE VIEW journal_log.view_sign_journal AS
			SELECT
			sign_journal_id,
			count_sign + count_cancel_sign AS count_all,
			round(count_sign::numeric * 100 / (count_sign + count_cancel_sign)) AS percent_sign,
			round(count_cancel_sign::numeric * 100 / (count_sign + count_cancel_sign)) AS percent_cancel_sign
			FROM journal_log.sign_journal;
        </sql>
    </changeSet>


    <changeSet id="0020" author="ilyin">
        <comment>edit summonses_list_subsystem.view_summonses_list</comment>
        <sql>
			DROP VIEW IF EXISTS summonses_list_subsystem.view_summonses_list;
			CREATE OR REPLACE VIEW summonses_list_subsystem.view_summonses_list AS
			SELECT
			summonses_list_id,
			count_view_arrived + count_not_view_arrived + count_view_less_20_days + count_not_view_less_20_days + count_view_greater_20_days + count_not_view_greater_20_days AS count_all,
			count_view_arrived + count_not_view_arrived AS count_arrived_all,
			round((count_view_arrived + count_not_view_arrived)::numeric * 100 / (count_view_arrived + count_not_view_arrived + count_view_less_20_days + count_not_view_less_20_days + count_view_greater_20_days + count_not_view_greater_20_days)) AS percent_arrived_all,
			round(count_view_arrived::numeric * 100 / (count_view_arrived + count_not_view_arrived)) AS percent_view_arrived,
			round(count_not_view_arrived::numeric * 100 / (count_view_arrived + count_not_view_arrived)) AS percent_not_view_arrived,
			count_view_less_20_days + count_not_view_less_20_days AS count_less_20_days_all,
			round((count_view_less_20_days + count_not_view_less_20_days)::numeric * 100 / (count_view_arrived + count_not_view_arrived + count_view_less_20_days + count_not_view_less_20_days + count_view_greater_20_days + count_not_view_greater_20_days)) AS percent_less_20_days_all,
			round(count_view_less_20_days::numeric * 100 / (count_view_less_20_days + count_not_view_less_20_days)) AS percent_view_less_20_days,
			round(count_not_view_less_20_days::numeric * 100 / (count_view_less_20_days + count_not_view_less_20_days)) AS percent_not_view_less_20_days,
			count_view_greater_20_days + count_not_view_greater_20_days AS count_greater_20_days_all,
			round((count_view_greater_20_days + count_not_view_greater_20_days)::numeric * 100 / (count_view_arrived + count_not_view_arrived + count_view_less_20_days + count_not_view_less_20_days + count_view_greater_20_days + count_not_view_greater_20_days)) AS percent_greater_20_days_all,
			round(count_view_greater_20_days::numeric * 100 / (count_view_greater_20_days + count_not_view_greater_20_days)) AS percent_view_greater_20_days,
			round(count_not_view_greater_20_days::numeric * 100 / (count_view_greater_20_days + count_not_view_greater_20_days)) AS percent_not_view_greater_20_days
			FROM summonses_list_subsystem.summonses_list;
		</sql>
    </changeSet>
	
    <changeSet id="0021" author="ilyin">
        <comment>edit admin_indicators.view_summonses_list</comment>
        <sql>
			CREATE TABLE IF NOT EXISTS admin_indicators.active_service_time
            (
            active_service_time_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_convert bigint NOT NULL DEFAULT 0,
            count_init_registration bigint NOT NULL DEFAULT 0,
            count_deregistration bigint NOT NULL DEFAULT 0,
            count_summonses_list_access bigint NOT NULL DEFAULT 0,
            count_actualization bigint NOT NULL DEFAULT 0,
            count_responsibility_info bigint NOT NULL DEFAULT 0,
			average_count_convert bigint NOT NULL DEFAULT 0,
            average_count_init_registration bigint NOT NULL DEFAULT 0,
            average_count_deregistration bigint NOT NULL DEFAULT 0,
            average_count_summonses_list_access bigint NOT NULL DEFAULT 0,
            average_count_actualization bigint NOT NULL DEFAULT 0,
            average_count_responsibility_info bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS admin_indicators.active_service_time  OWNER to ervu_business_metrics;

            COMMENT ON TABLE admin_indicators.active_service_time IS 'Администрирование. Анализ времени активности сервисов';
			
            CREATE INDEX IF NOT EXISTS idx_active_service_time_date ON admin_indicators.active_service_time (info_date);
            CREATE INDEX IF NOT EXISTS idx_active_service_time_recr ON admin_indicators.active_service_time (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_active_service_time_recr_date ON admin_indicators.active_service_time (recruitment_id, info_date);
			
			COMMENT ON COLUMN admin_indicators.active_service_time.count_convert IS 'конвертация';
			COMMENT ON COLUMN admin_indicators.active_service_time.count_init_registration IS 'постановка на ву';
			COMMENT ON COLUMN admin_indicators.active_service_time.count_deregistration IS 'снятие с ву';
			COMMENT ON COLUMN admin_indicators.active_service_time.count_actualization IS 'актуализация сведений';
			COMMENT ON COLUMN admin_indicators.active_service_time.count_summonses_list_access IS 'доступ в Реестр повесток';
			COMMENT ON COLUMN admin_indicators.active_service_time.count_responsibility_info IS 'внесение сведений об ответственности';
			COMMENT ON COLUMN admin_indicators.active_service_time.average_count_convert IS 'среднее время конвертация';
			COMMENT ON COLUMN admin_indicators.active_service_time.average_count_init_registration IS 'среднее время постановка на ву';
			COMMENT ON COLUMN admin_indicators.active_service_time.average_count_deregistration IS 'среднее время снятие с ву';
			COMMENT ON COLUMN admin_indicators.active_service_time.average_count_actualization IS 'среднее время актуализация сведений';
			COMMENT ON COLUMN admin_indicators.active_service_time.average_count_summonses_list_access IS 'среднее время доступ в Реестр повесток';
			COMMENT ON COLUMN admin_indicators.active_service_time.average_count_responsibility_info IS 'среднее время внесение сведений об ответственности';
			
			
			CREATE TABLE IF NOT EXISTS admin_indicators.user_activity_dynamics
            (
            user_activity_dynamics_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_external_exchange bigint NOT NULL DEFAULT 0,
            count_summonses_list bigint NOT NULL DEFAULT 0,
            count_etalon_data bigint NOT NULL DEFAULT 0,
            count_accounting bigint NOT NULL DEFAULT 0,
            count_sign_batch bigint NOT NULL DEFAULT 0,
            count_analitic_subsystem bigint NOT NULL DEFAULT 0,
			count_data_quality_control bigint NOT NULL DEFAULT 0,
			count_users_administation bigint NOT NULL DEFAULT 0,
			count_reports bigint NOT NULL DEFAULT 0,
			count_dashboards bigint NOT NULL DEFAULT 0,
			count_desktop bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS admin_indicators.user_activity_dynamics  OWNER to ervu_business_metrics;

            COMMENT ON TABLE admin_indicators.user_activity_dynamics IS 'Администрирование. Динамика активности пользователей';
			
            CREATE INDEX IF NOT EXISTS idx_user_activity_dynamics_date ON admin_indicators.user_activity_dynamics (info_date);
            CREATE INDEX IF NOT EXISTS idx_user_activity_dynamics_recr ON admin_indicators.user_activity_dynamics (recruitment_id);
            CREATE INDEX IF NOT EXISTS idx_user_activity_dynamics_recr_date ON admin_indicators.user_activity_dynamics (recruitment_id, info_date);
			
			COMMENT ON COLUMN admin_indicators.user_activity_dynamics.count_external_exchange IS 'внешний обмен';
			COMMENT ON COLUMN admin_indicators.user_activity_dynamics.count_summonses_list IS 'реестр повесток';
			COMMENT ON COLUMN admin_indicators.user_activity_dynamics.count_etalon_data IS 'эталонные данные';
			COMMENT ON COLUMN admin_indicators.user_activity_dynamics.count_accounting IS 'ведение учета';
			COMMENT ON COLUMN admin_indicators.user_activity_dynamics.count_sign_batch IS 'пакетное подписание';
			COMMENT ON COLUMN admin_indicators.user_activity_dynamics.count_analitic_subsystem IS 'аналитическая подсистема';
			COMMENT ON COLUMN admin_indicators.user_activity_dynamics.count_data_quality_control IS 'контроль качества данных';
			COMMENT ON COLUMN admin_indicators.user_activity_dynamics.count_users_administation IS 'администрирование УЗ';
			COMMENT ON COLUMN admin_indicators.user_activity_dynamics.count_reports IS 'отчеты';
			COMMENT ON COLUMN admin_indicators.user_activity_dynamics.count_dashboards IS 'дашборды';
			COMMENT ON COLUMN admin_indicators.user_activity_dynamics.count_desktop IS 'рабочий стол';
		</sql>
    </changeSet>

    <changeSet id="0022" author="ilyin">
        <comment>replace actualization.incidents_epgu_info</comment>
        <sql>
			DROP VIEW IF EXISTS actualization.view_incidents_epgu_info;
			DROP TABLE IF EXISTS actualization.incidents_epgu_info;
			CREATE TABLE IF NOT EXISTS actualization.incidents_epgu_info
			(
            incidents_epgu_info_id bigserial NOT NULL PRIMARY KEY,
            recruitment_id varchar(36) NOT NULL constraint fk_conv_info_records_from_easu_recruitment_id references metrics.recruitment,
            update_date timestamp without time zone NOT NULL DEFAULT now(),
            info_date date NOT NULL,
            count_resolved bigint NOT NULL DEFAULT 0,
            count_registered bigint NOT NULL DEFAULT 0
            )
            WITH (OIDS = FALSE);
            ALTER TABLE IF EXISTS actualization.incidents_epgu_info  OWNER to ervu_business_metrics;

            COMMENT ON TABLE actualization.incidents_epgu_info IS 'Актуализация. Инциденты ЕПГУ';

			COMMENT ON COLUMN actualization.incidents_epgu_info.count_resolved IS 'разрешено';
			COMMENT ON COLUMN actualization.incidents_epgu_info.count_registered IS 'зарегистрировано';


			CREATE OR REPLACE VIEW actualization.view_incidents_epgu_info AS
			SELECT
			incidents_epgu_info_id,
			round(count_resolved::numeric * 100 / count_registered) AS percent_resolved,
			count_registered - count_resolved  AS count_not_resolved
			FROM actualization.incidents_epgu_info;
        </sql>
    </changeSet>

</databaseChangeLog>