<?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>create table</comment>
		<sql>
			CREATE TABLE IF NOT EXISTS deregistration.rejection_decisions (
			rejection_decisions_id bigserial NOT NULL,
			recruitment_id varchar(36) NOT NULL,
			update_date timestamp DEFAULT now() NOT NULL,
			info_date date NOT NULL,
			count_for_sign int8 DEFAULT 0 NOT NULL,
			count_signed int8 DEFAULT 0 NOT NULL,
			count_refused int8 DEFAULT 0 NOT NULL,
			CONSTRAINT rejection_decisions_pkey PRIMARY KEY (rejection_decisions_id),
			CONSTRAINT fk_rejection_decisions_recruitment_id FOREIGN KEY (recruitment_id) REFERENCES metrics.recruitment(id)
			);
			CREATE INDEX IF NOT EXISTS idx_rejection_decisions_date ON deregistration.rejection_decisions USING btree (info_date);
			CREATE INDEX IF NOT EXISTS idx_rejection_decisions_recr ON deregistration.rejection_decisions USING btree (recruitment_id);
			CREATE INDEX IF NOT EXISTS idx_rejection_decisions_recr_date ON deregistration.rejection_decisions USING btree (recruitment_id, info_date);

			ALTER TABLE IF EXISTS deregistration.rejection_decisions OWNER to ervu_business_metrics;

			COMMENT ON TABLE deregistration.rejection_decisions IS 'Снятие с воинского учета. Сформированные решения об отказе в снятии с ВУ';

			COMMENT ON COLUMN deregistration.rejection_decisions.count_for_sign IS 'Доступно для подписания';
			COMMENT ON COLUMN deregistration.rejection_decisions.count_signed IS 'Подписано';
			COMMENT ON COLUMN deregistration.rejection_decisions.count_refused IS 'Отклонено';


			CREATE OR REPLACE VIEW deregistration.view_rejection_decisions
			AS SELECT rejection_decisions.rejection_decisions_id,
				rejection_decisions.count_for_sign + rejection_decisions.count_signed + rejection_decisions.count_refused AS count_all,
				round(rejection_decisions.count_for_sign::numeric * 100::numeric / (rejection_decisions.count_for_sign + rejection_decisions.count_signed + rejection_decisions.count_refused)::numeric) AS percent_for_sign,
				round(rejection_decisions.count_signed::numeric * 100::numeric / (rejection_decisions.count_for_sign + rejection_decisions.count_signed + rejection_decisions.count_refused)::numeric) AS percent_signed,
				round(rejection_decisions.count_refused::numeric * 100::numeric / (rejection_decisions.count_for_sign + rejection_decisions.count_signed + rejection_decisions.count_refused)::numeric) AS percent_refused
			   FROM deregistration.rejection_decisions;
		</sql>
	</changeSet>
	
	<changeSet id="0002" author="saliakhov">
        <comment>edit table</comment>
		<sql>
			DROP VIEW IF EXISTS registration_change_address.view_change_data_from_gir_vu; 

			ALTER TABLE registration_change_address.change_data_from_gir_vu DROP COLUMN IF EXISTS count_sent_from_first;

			ALTER TABLE registration_change_address.change_data_from_gir_vu ADD COLUMN IF NOT EXISTS count_denied_requests int8 DEFAULT 0 NOT NULL;

			COMMENT ON COLUMN registration_change_address.change_data_from_gir_vu.count_denied_requests IS 'Отклонено запросов личного дела';

			CREATE OR REPLACE VIEW registration_change_address.view_change_data_from_gir_vu
			AS SELECT change_data_from_gir_vu.change_data_from_gir_vu_id,
				round(change_data_from_gir_vu.count_requested_to_send::numeric * 100::numeric / change_data_from_gir_vu.count_all::numeric) AS percent_requested_to_send,
				round(change_data_from_gir_vu.count_accepted_to_send_from_first::numeric * 100::numeric / change_data_from_gir_vu.count_all::numeric) AS percent_accepted_to_send_from_first,
				round(change_data_from_gir_vu.count_denied_requests::numeric * 100::numeric / change_data_from_gir_vu.count_all::numeric) AS percent_denied_requests,
				round(change_data_from_gir_vu.count_solution_send_signed::numeric * 100::numeric / change_data_from_gir_vu.count_all::numeric) AS percent_requested_solution_send_signed,
				round(change_data_from_gir_vu.count_unloaded::numeric * 100::numeric / change_data_from_gir_vu.count_all::numeric) AS percent_requested_unloaded,
				round(change_data_from_gir_vu.count_solutions_registration_formed::numeric * 100::numeric / change_data_from_gir_vu.count_all::numeric) AS percent_requested_solutions_formed,
				round(change_data_from_gir_vu.count_solutions_registration_signed::numeric * 100::numeric / change_data_from_gir_vu.count_all::numeric) AS percent_solutions_registration_signed
			   FROM registration_change_address.change_data_from_gir_vu;
		</sql>
	</changeSet>
	
	<changeSet id="0003" author="absatarov">
        <comment>create table</comment>
		<sql>
			CREATE TABLE IF NOT EXISTS registration_change_address.support_summ_sing
			(
				support_summ_sing_id bigserial,
				progress boolean NOT NULL,
				change_rec boolean NOT NULL,
				code character varying(10) COLLATE pg_catalog."default",
				source_last_update character varying(10) COLLATE pg_catalog."default" NOT NULL,
				recruitment_id character varying(36) COLLATE pg_catalog."default" NOT NULL,
				CONSTRAINT support_summ_sing_pkey PRIMARY KEY (support_summ_sing_id)
			)

			TABLESPACE pg_default;

			ALTER TABLE IF EXISTS registration_change_address.support_summ_sing OWNER to ervu_business_metrics;



			CREATE TABLE IF NOT EXISTS registration_change_address.support_tmp_meas
			(
				support_tmp_meas_id bigserial,
				source_last_update character varying(10) COLLATE pg_catalog."default" NOT NULL,
				status_applied character varying(10) COLLATE pg_catalog."default",
				recruitment_id character varying(36) COLLATE pg_catalog."default" NOT NULL,
				CONSTRAINT support_tmp_meas_pkey PRIMARY KEY (support_tmp_meas_id)
			)

			TABLESPACE pg_default;

			ALTER TABLE IF EXISTS registration_change_address.support_tmp_meas OWNER to ervu_business_metrics;



			CREATE TABLE IF NOT EXISTS summonses_list.support_lk
			(
				support_lk_id bigserial,
				recruitment_id character varying(36) COLLATE pg_catalog."default" NOT NULL,
				summonses_reason_id bigint NOT NULL DEFAULT 0,
				system_pgs_status character varying(10) COLLATE pg_catalog."default",
				CONSTRAINT support_lk_pkey PRIMARY KEY (support_lk_id)
			)

			TABLESPACE pg_default;

			ALTER TABLE IF EXISTS summonses_list.support_lk OWNER to ervu_business_metrics;
		</sql>
	</changeSet>
</databaseChangeLog>