<?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="saliakhov">
        <comment>edit admin_indicators.user_analysis</comment>
		<sql>
			ALTER TABLE admin_indicators.user_analysis DROP COLUMN IF EXISTS count_active_users;
			ALTER TABLE admin_indicators.user_analysis DROP COLUMN IF EXISTS count_role_1;
			ALTER TABLE admin_indicators.user_analysis DROP COLUMN IF EXISTS count_role_2;
			ALTER TABLE admin_indicators.user_analysis DROP COLUMN IF EXISTS count_role_3;
			ALTER TABLE admin_indicators.user_analysis DROP COLUMN IF EXISTS count_role_4;
			ALTER TABLE admin_indicators.user_analysis DROP COLUMN IF EXISTS count_role_5;
			ALTER TABLE admin_indicators.user_analysis DROP COLUMN IF EXISTS name_role_1;
			ALTER TABLE admin_indicators.user_analysis DROP COLUMN IF EXISTS name_role_2;
			ALTER TABLE admin_indicators.user_analysis DROP COLUMN IF EXISTS name_role_3;
			ALTER TABLE admin_indicators.user_analysis DROP COLUMN IF EXISTS name_role_4;
			ALTER TABLE admin_indicators.user_analysis DROP COLUMN IF EXISTS name_role_5;

			ALTER TABLE admin_indicators.user_analysis ADD COLUMN IF NOT EXISTS count_invalid_authentication int8 DEFAULT 0 NOT NULL;
			ALTER TABLE admin_indicators.user_analysis ADD COLUMN IF NOT EXISTS count_administrator_is int8 DEFAULT 0 NOT NULL;
			ALTER TABLE admin_indicators.user_analysis ADD COLUMN IF NOT EXISTS count_administrator_poib int8 DEFAULT 0 NOT NULL;
			ALTER TABLE admin_indicators.user_analysis ADD COLUMN IF NOT EXISTS count_employee_gomy int8 DEFAULT 0 NOT NULL;
			ALTER TABLE admin_indicators.user_analysis ADD COLUMN IF NOT EXISTS count_observer_gomy int8 DEFAULT 0 NOT NULL;
			ALTER TABLE admin_indicators.user_analysis ADD COLUMN IF NOT EXISTS count_supervisor_gomy int8 DEFAULT 0 NOT NULL;
			ALTER TABLE admin_indicators.user_analysis ADD COLUMN IF NOT EXISTS count_military_commissar int8 DEFAULT 0 NOT NULL;
			ALTER TABLE admin_indicators.user_analysis ADD COLUMN IF NOT EXISTS count_specialist_statements int8 DEFAULT 0 NOT NULL;
			ALTER TABLE admin_indicators.user_analysis ADD COLUMN IF NOT EXISTS count_observer_vo int8 DEFAULT 0 NOT NULL;
			ALTER TABLE admin_indicators.user_analysis ADD COLUMN IF NOT EXISTS count_observer_vk int8 DEFAULT 0 NOT NULL;
			ALTER TABLE admin_indicators.user_analysis ADD COLUMN IF NOT EXISTS count_administrator_military_office int8 DEFAULT 0 NOT NULL;
			ALTER TABLE admin_indicators.user_analysis ADD COLUMN IF NOT EXISTS count_specialist_military_accounting int8 DEFAULT 0 NOT NULL;
			ALTER TABLE admin_indicators.user_analysis ADD COLUMN IF NOT EXISTS count_specialist_acquisition int8 DEFAULT 0 NOT NULL;

			COMMENT ON COLUMN admin_indicators.user_analysis.count_invalid_authentication IS 'неверные аутентификации';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_administrator_is IS 'Администратор ИС';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_administrator_poib IS 'Администратор ПОИБ';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_employee_gomy IS 'Сотрудник ГОМУ';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_observer_gomy IS 'Наблюдатель ГОМУ';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_supervisor_gomy IS 'Супервайзер ГОМУ';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_military_commissar IS 'Военный комиссар';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_specialist_statements IS 'Специалист ВК по обработке заявлений';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_observer_vo IS 'Наблюдатель штаба ВО';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_observer_vk IS 'Наблюдатель ВК субъекта';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_administrator_military_office IS 'Администратор военкомата';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_specialist_military_accounting IS 'Специалист ВК по ВУ';
			COMMENT ON COLUMN admin_indicators.user_analysis.count_specialist_acquisition IS 'Специалист по комплектованию';


			CREATE OR REPLACE VIEW admin_indicators.view_user_analysis
						AS SELECT user_analysis.user_analysis_id,
							user_analysis.count_administrator_is + user_analysis.count_administrator_poib + user_analysis.count_employee_gomy + user_analysis.count_observer_gomy + user_analysis.count_supervisor_gomy + user_analysis.count_military_commissar + user_analysis.count_specialist_statements + user_analysis.count_observer_vo + user_analysis.count_observer_vk + user_analysis.count_administrator_military_office + user_analysis.count_specialist_military_accounting + user_analysis.count_specialist_acquisition AS count_all,
							round(user_analysis.count_administrator_is::numeric * 100::numeric / (user_analysis.count_administrator_is + user_analysis.count_administrator_poib + user_analysis.count_employee_gomy + user_analysis.count_observer_gomy + user_analysis.count_supervisor_gomy + user_analysis.count_military_commissar + user_analysis.count_specialist_statements + user_analysis.count_observer_vo + user_analysis.count_observer_vk + user_analysis.count_administrator_military_office + user_analysis.count_specialist_military_accounting + user_analysis.count_specialist_acquisition)::numeric) AS percent_administrator_is,
							round(user_analysis.count_administrator_poib::numeric * 100::numeric / (user_analysis.count_administrator_is + user_analysis.count_administrator_poib + user_analysis.count_employee_gomy + user_analysis.count_observer_gomy + user_analysis.count_supervisor_gomy + user_analysis.count_military_commissar + user_analysis.count_specialist_statements + user_analysis.count_observer_vo + user_analysis.count_observer_vk + user_analysis.count_administrator_military_office + user_analysis.count_specialist_military_accounting + user_analysis.count_specialist_acquisition)::numeric) AS percent_administrator_poib,
							round(user_analysis.count_employee_gomy::numeric * 100::numeric / (user_analysis.count_administrator_is + user_analysis.count_administrator_poib + user_analysis.count_employee_gomy + user_analysis.count_observer_gomy + user_analysis.count_supervisor_gomy + user_analysis.count_military_commissar + user_analysis.count_specialist_statements + user_analysis.count_observer_vo + user_analysis.count_observer_vk + user_analysis.count_administrator_military_office + user_analysis.count_specialist_military_accounting + user_analysis.count_specialist_acquisition)::numeric) AS percent_employee_gomy,
							round(user_analysis.count_observer_gomy::numeric * 100::numeric / (user_analysis.count_administrator_is + user_analysis.count_administrator_poib + user_analysis.count_employee_gomy + user_analysis.count_observer_gomy + user_analysis.count_supervisor_gomy + user_analysis.count_military_commissar + user_analysis.count_specialist_statements + user_analysis.count_observer_vo + user_analysis.count_observer_vk + user_analysis.count_administrator_military_office + user_analysis.count_specialist_military_accounting + user_analysis.count_specialist_acquisition)::numeric) AS percent_bserver_gomy,
							round(user_analysis.count_supervisor_gomy::numeric * 100::numeric / (user_analysis.count_administrator_is + user_analysis.count_administrator_poib + user_analysis.count_employee_gomy + user_analysis.count_observer_gomy + user_analysis.count_supervisor_gomy + user_analysis.count_military_commissar + user_analysis.count_specialist_statements + user_analysis.count_observer_vo + user_analysis.count_observer_vk + user_analysis.count_administrator_military_office + user_analysis.count_specialist_military_accounting + user_analysis.count_specialist_acquisition)::numeric) AS percent_supervisor_gomy,
							round(user_analysis.count_military_commissar::numeric * 100::numeric / (user_analysis.count_administrator_is + user_analysis.count_administrator_poib + user_analysis.count_employee_gomy + user_analysis.count_observer_gomy + user_analysis.count_supervisor_gomy + user_analysis.count_military_commissar + user_analysis.count_specialist_statements + user_analysis.count_observer_vo + user_analysis.count_observer_vk + user_analysis.count_administrator_military_office + user_analysis.count_specialist_military_accounting + user_analysis.count_specialist_acquisition)::numeric) AS percent_military_commissar,
							round(user_analysis.count_specialist_statements::numeric * 100::numeric / (user_analysis.count_administrator_is + user_analysis.count_administrator_poib + user_analysis.count_employee_gomy + user_analysis.count_observer_gomy + user_analysis.count_supervisor_gomy + user_analysis.count_military_commissar + user_analysis.count_specialist_statements + user_analysis.count_observer_vo + user_analysis.count_observer_vk + user_analysis.count_administrator_military_office + user_analysis.count_specialist_military_accounting + user_analysis.count_specialist_acquisition)::numeric) AS percent_specialist_statements,
							round(user_analysis.count_observer_vo::numeric * 100::numeric / (user_analysis.count_administrator_is + user_analysis.count_administrator_poib + user_analysis.count_employee_gomy + user_analysis.count_observer_gomy + user_analysis.count_supervisor_gomy + user_analysis.count_military_commissar + user_analysis.count_specialist_statements + user_analysis.count_observer_vo + user_analysis.count_observer_vk + user_analysis.count_administrator_military_office + user_analysis.count_specialist_military_accounting + user_analysis.count_specialist_acquisition)::numeric) AS percent_observer_vo,
							round(user_analysis.count_observer_vk::numeric * 100::numeric / (user_analysis.count_administrator_is + user_analysis.count_administrator_poib + user_analysis.count_employee_gomy + user_analysis.count_observer_gomy + user_analysis.count_supervisor_gomy + user_analysis.count_military_commissar + user_analysis.count_specialist_statements + user_analysis.count_observer_vo + user_analysis.count_observer_vk + user_analysis.count_administrator_military_office + user_analysis.count_specialist_military_accounting + user_analysis.count_specialist_acquisition)::numeric) AS percent_observer_vk,
							round(user_analysis.count_administrator_military_office::numeric * 100::numeric / (user_analysis.count_administrator_is + user_analysis.count_administrator_poib + user_analysis.count_employee_gomy + user_analysis.count_observer_gomy + user_analysis.count_supervisor_gomy + user_analysis.count_military_commissar + user_analysis.count_specialist_statements + user_analysis.count_observer_vo + user_analysis.count_observer_vk + user_analysis.count_administrator_military_office + user_analysis.count_specialist_military_accounting + user_analysis.count_specialist_acquisition)::numeric) AS percent_administrator_military_office,
							round(user_analysis.count_specialist_military_accounting::numeric * 100::numeric / (user_analysis.count_administrator_is + user_analysis.count_administrator_poib + user_analysis.count_employee_gomy + user_analysis.count_observer_gomy + user_analysis.count_supervisor_gomy + user_analysis.count_military_commissar + user_analysis.count_specialist_statements + user_analysis.count_observer_vo + user_analysis.count_observer_vk + user_analysis.count_administrator_military_office + user_analysis.count_specialist_military_accounting + user_analysis.count_specialist_acquisition)::numeric) AS percent_specialist_military_accounting,
							round(user_analysis.count_specialist_acquisition::numeric * 100::numeric / (user_analysis.count_administrator_is + user_analysis.count_administrator_poib + user_analysis.count_employee_gomy + user_analysis.count_observer_gomy + user_analysis.count_supervisor_gomy + user_analysis.count_military_commissar + user_analysis.count_specialist_statements + user_analysis.count_observer_vo + user_analysis.count_observer_vk + user_analysis.count_administrator_military_office + user_analysis.count_specialist_military_accounting + user_analysis.count_specialist_acquisition)::numeric) AS percent_specialist_acquisition
						   FROM admin_indicators.user_analysis;
		</sql>
	</changeSet>
	
	<changeSet id="0002" author="saliakhov">
        <comment>edit admin_indicators.user_analysis</comment>
		<sql>
			DROP VIEW IF EXISTS registration_change_address.view_change_data_personal_visit;

			ALTER TABLE registration_change_address.change_data_personal_visit ADD COLUMN IF NOT EXISTS count_refused int8 DEFAULT 0 NOT NULL;

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

	<changeSet id="0003" author="saliakhov">
        <comment>edit metrics.view_incidents_change_data_from_gir_vu</comment>
		<sql>
			DROP VIEW IF EXISTS metrics.view_incidents_change_data_from_gir_vu;

			CREATE OR REPLACE VIEW metrics.view_incidents_change_data_from_gir_vu
			AS SELECT incidents_change_data_from_gir_vu.incidents_change_data_from_gir_vu_id,
				incidents_change_data_from_gir_vu.count_epgu_citizen_appeal + incidents_change_data_from_gir_vu.count_manual + incidents_change_data_from_gir_vu.count_send_to_gir_vu AS count_all,
				round(incidents_change_data_from_gir_vu.count_epgu_citizen_appeal::numeric * 100::numeric / (incidents_change_data_from_gir_vu.count_epgu_citizen_appeal + incidents_change_data_from_gir_vu.count_manual + incidents_change_data_from_gir_vu.count_send_to_gir_vu)::numeric) AS percent_epgu_citizen_appeal,
				round(incidents_change_data_from_gir_vu.count_manual::numeric * 100::numeric / (incidents_change_data_from_gir_vu.count_epgu_citizen_appeal + incidents_change_data_from_gir_vu.count_manual + incidents_change_data_from_gir_vu.count_send_to_gir_vu)::numeric) AS percent_manual,
				round(incidents_change_data_from_gir_vu.count_send_to_gir_vu::numeric * 100::numeric / (incidents_change_data_from_gir_vu.count_epgu_citizen_appeal + incidents_change_data_from_gir_vu.count_manual + incidents_change_data_from_gir_vu.count_send_to_gir_vu)::numeric) AS percent_send_to_gir_vu
			   FROM metrics.incidents_change_data_from_gir_vu;
		</sql>
	</changeSet>
	
	<changeSet id="0004" author="saliakhov">
        <comment>create deregistration.removal_registration</comment>
		<sql>
			CREATE TABLE IF NOT EXISTS deregistration.removal_registration (
				removal_registration_id bigserial NOT NULL,
				recruitment_id varchar(36) NOT NULL,
				update_date timestamp DEFAULT now() NOT NULL,
				info_date date NOT NULL,
				count_age_limit int8 DEFAULT 0 NOT NULL,
				count_reason_death int8 DEFAULT 0 NOT NULL,
				count_loss_citizenship int8 DEFAULT 0 NOT NULL,
				count_cancellation_record int8 DEFAULT 0 NOT NULL,
				CONSTRAINT removal_registration_pkey PRIMARY KEY (removal_registration_id),
				CONSTRAINT fk_removal_registration_recruitment_id FOREIGN KEY (recruitment_id) REFERENCES metrics.recruitment(id)
			);
			CREATE INDEX IF NOT EXISTS idx_removal_registration_date ON deregistration.removal_registration USING btree (info_date);
			CREATE INDEX IF NOT EXISTS idx_removal_registration_recr ON deregistration.removal_registration USING btree (recruitment_id);
			CREATE INDEX IF NOT EXISTS idx_removal_registration_recr_date ON deregistration.removal_registration USING btree (recruitment_id, info_date);




			CREATE OR REPLACE VIEW deregistration.view_removal_registration
			AS SELECT removal_registration.removal_registration_id,
				removal_registration.count_age_limit + removal_registration.count_reason_death + removal_registration.count_loss_citizenship + removal_registration.count_cancellation_record AS count_all,
				round(removal_registration.count_age_limit::numeric * 100::numeric / (removal_registration.count_age_limit + removal_registration.count_reason_death + removal_registration.count_loss_citizenship + removal_registration.count_cancellation_record)::numeric) AS percent_age_limit,
				round(removal_registration.count_reason_death::numeric * 100::numeric / (removal_registration.count_age_limit + removal_registration.count_reason_death + removal_registration.count_loss_citizenship + removal_registration.count_cancellation_record)::numeric) AS percent_reason_death,
				round(removal_registration.count_loss_citizenship::numeric * 100::numeric / (removal_registration.count_age_limit + removal_registration.count_reason_death + removal_registration.count_loss_citizenship + removal_registration.count_cancellation_record)::numeric) AS percent_loss_citizenship,
				round(removal_registration.count_cancellation_record::numeric * 100::numeric / (removal_registration.count_age_limit + removal_registration.count_reason_death + removal_registration.count_loss_citizenship + removal_registration.count_cancellation_record)::numeric) AS percent_cancellation_record
			   FROM deregistration.removal_registration;
		</sql>
	</changeSet>
	
	<changeSet id="0005" author="saliakhov">
        <comment>create table metrics.incidents_files_requests</comment>
		<sql>
			CREATE TABLE IF NOT EXISTS metrics.incidents_files_requests (
				incidents_files_requests_id bigserial NOT NULL,
				recruitment_id varchar(36) NOT NULL,
				update_date timestamp DEFAULT now() NOT NULL,
				info_date date NOT NULL,
				count_requisites_entered int8 DEFAULT 0 NOT NULL,
				count_results_entered int8 DEFAULT 0 NOT NULL,
				count_formed_fns int8 DEFAULT 0 NOT NULL,
				count_uploaded_fns int8 DEFAULT 0 NOT NULL,
				CONSTRAINT incidents_files_requests_pkey PRIMARY KEY (incidents_files_requests_id),
				CONSTRAINT fk_incidents_files_requests_recruitment_id FOREIGN KEY (recruitment_id) REFERENCES metrics.recruitment(id)
			);
			CREATE INDEX IF NOT EXISTS idx_incidents_files_requests_date ON metrics.incidents_files_requests USING btree (info_date);
			CREATE INDEX IF NOT EXISTS idx_incidents_files_requests_recr ON metrics.incidents_files_requests USING btree (recruitment_id);
			CREATE INDEX IF NOT EXISTS idx_incidents_files_requests_recr_date ON metrics.incidents_files_requests USING btree (recruitment_id, info_date);

			COMMENT ON COLUMN metrics.incidents_files_requests.count_requisites_entered IS 'реквизитов обращений внесено';
			COMMENT ON COLUMN metrics.incidents_files_requests.count_results_entered IS 'результатов рассмотрения обращений внесено';
			COMMENT ON COLUMN metrics.incidents_files_requests.count_formed_fns IS 'файлов сформировано для выгрузки в ФНС';
			COMMENT ON COLUMN metrics.incidents_files_requests.count_uploaded_fns IS 'файлов выгружено для ФНС';


			CREATE OR REPLACE VIEW metrics.view_incidents_files_requests
			AS SELECT incidents_files_requests.incidents_files_requests_id,
				round(incidents_files_requests.count_requisites_entered::numeric * 100::numeric / (incidents_files_requests.count_requisites_entered)::numeric) AS percent_requisites_entered,
				round(incidents_files_requests.count_results_entered::numeric * 100::numeric / (incidents_files_requests.count_requisites_entered)::numeric) AS percent_results_entered
			   FROM metrics.incidents_files_requests
		</sql>
	</changeSet>
	
	<changeSet id="0006" author="saliakhov">
        <comment>edit init_registration_info.init_registration_from_gir_vu</comment>
		<sql>
			DROP VIEW IF EXISTS init_registration_info.view_init_registration_from_gir_vu;

			ALTER TABLE init_registration_info.init_registration_from_gir_vu DROP COLUMN IF EXISTS count_all;

			ALTER TABLE init_registration_info.init_registration_from_gir_vu ADD COLUMN IF NOT EXISTS count_serving_sentence int8 DEFAULT 0 NOT NULL;

			COMMENT ON COLUMN init_registration_info.init_registration_from_gir_vu.count_serving_sentence IS 'граждан, отбывавших наказания в местах лишения свободы';

			CREATE OR REPLACE VIEW init_registration_info.view_init_registration_from_gir_vu
			AS SELECT init_registration_from_gir_vu.init_registration_from_gir_vu_id,
				init_registration_from_gir_vu.count_womens + init_registration_from_gir_vu.count_received_citizenship + init_registration_from_gir_vu.count_serving_sentence AS count_all,
				round(init_registration_from_gir_vu.count_womens::numeric * 100::numeric / (init_registration_from_gir_vu.count_womens + init_registration_from_gir_vu.count_received_citizenship + init_registration_from_gir_vu.count_serving_sentence)::numeric) AS percent_womens,
				round(init_registration_from_gir_vu.count_received_citizenship::numeric * 100::numeric / (init_registration_from_gir_vu.count_womens + init_registration_from_gir_vu.count_received_citizenship + init_registration_from_gir_vu.count_serving_sentence)::numeric) AS percent_received_citizenship,
				round(init_registration_from_gir_vu.count_serving_sentence::numeric * 100::numeric / (init_registration_from_gir_vu.count_womens + init_registration_from_gir_vu.count_received_citizenship + init_registration_from_gir_vu.count_serving_sentence)::numeric) AS percent_serving_sentence
			   FROM init_registration_info.init_registration_from_gir_vu;
		</sql>
	</changeSet>
</databaseChangeLog>