<?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="ilyin">
        <comment>edit view_criminal_administrative_liability</comment>
        <sql>
            ALTER TABLE metrics.criminal_administrative_liability ADD COLUMN IF NOT EXISTS count_all bigint NOT NULL DEFAULT 0;
			
			DROP VIEW IF EXISTS metrics.view_criminal_administrative_liability;

			CREATE OR REPLACE VIEW metrics.view_criminal_administrative_liability AS
			SELECT
			criminal_administrative_liability_id,
			round(count_criminal::numeric * 100 / count_all) AS percent_criminal,
			round(count_administrative::numeric * 100 / count_all) AS percent_administrative,
			count_all - count_criminal AS count_without_criminal,
			count_all - count_administrative AS count_without_administrative
			FROM metrics.criminal_administrative_liability;
        </sql>
    </changeSet>

</databaseChangeLog>