Hack23/cia

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


    <changeSet author="party_trends" id="2414872417007-324"
        failOnError="true">
        
        <dropView viewName="view_riksdagen_party_ballot_support_annual_summary" /> <createView
            viewName="view_riksdagen_party_ballot_support_annual_summary">SELECT to_char(date_trunc('month',p1.vote_date),'YYYY-MM-DD') as embedded_id_date, p1.embedded_id_party as embedded_id_party, p2.embedded_id_party as embedded_id_other_party, sum( case when p1.party_approved = p2.party_approved then 1 else 0 end) as agree_count, sum( case when p1.party_approved = p2.party_approved then 0 else 1 end) as disagre_count, (sum( case when p1.party_approved = p2.party_approved then 0 else 1 end) *100 )/ count(*) as disagree_percentage,  count(*) as total_ballots
                                                                        FROM view_riksdagen_vote_data_ballot_party_summary p1
                                                                        JOIN view_riksdagen_vote_data_ballot_party_summary p2 ON p1.embedded_id_party &lt;&gt; p2.embedded_id_party and p1.embedded_id_ballot_id = p2.embedded_id_ballot_id and p1.embedded_id_issue = p2.embedded_id_issue group by p1.embedded_id_party,p2.embedded_id_party,to_char(date_trunc('month',p1.vote_date),'YYYY-MM-DD')</createView>
    </changeSet>

    <changeSet author="pether" id="2414872417007-325">

        <dropView viewName="view_riksdagen_party_role_member" /> <createView
            viewName="view_riksdagen_party_role_member">select hjid as role_id,detail, role_code,first_name,last_name,from_date,to_date,person_data.id as person_id,person_data.party as party,(CASE WHEN to_date > CURRENT_DATE or to_date is null THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date > CURRENT_DATE THEN CURRENT_DATE ELSE from_date END) as total_days_served,(CASE WHEN to_date is null or to_date > CURRENT_DATE and from_date &lt; CURRENT_DATE THEN true ELSE false END) as active from assignment_data left join person_data on assignment_data.intressent_id = person_data.id where assignment_type='partiuppdrag'</createView>

    </changeSet>

    <changeSet author="pether" id="2414872417007-326">

        <createTable tableName="rule_violation">
            <column name="id" type="INT8">
                <constraints nullable="false" />
            </column>
            <column name="detected_date" type="TIMESTAMP WITHOUT TIME ZONE" />            
            <column name="reference_id" type="VARCHAR(255)" />
            <column name="name" type="VARCHAR(255)" />
            <column name="resource_type" type="VARCHAR(255)" />
            <column name="rule_description" type="VARCHAR(255)" />
            <column name="rule_group" type="VARCHAR(255)" />
            <column name="status" type="VARCHAR(255)" />
            <column name="positive" type="VARCHAR(255)" />                                    
        </createTable>
    </changeSet>

    <changeSet author="pether" id="2414872417007-327">
        <addColumn tableName="rule_violation">
            <column name="rule_name" type="varchar(255)" />
        </addColumn>
    </changeSet>
    
    <changeSet author="pether (generated)" id="2414872417007-328">
        <addPrimaryKey columnNames="hjid"
            constraintName="application_configuration_pkey" tableName="application_configuration" />
    </changeSet>
    
    <changeSet author="pether (generated)" id="1414872417007-329">
    
        <sql>DROP MATERIALIZED VIEW view_worldbank_indicator_data_country_summary</sql>
    
        <modifyDataType columnName="indicator_name" newDataType="VARCHAR(2048)"
            tableName="indicator_element" />
        <modifyDataType columnName="source_id" newDataType="VARCHAR(2048)"
            tableName="indicator_element" />
        <modifyDataType columnName="source_value" newDataType="VARCHAR(2048)"
            tableName="indicator_element" />
        <sql>create materialized view view_worldbank_indicator_data_country_summary as select indicator_id as embedded_id_indicator_id,country_id as embedded_id_country_id,max(indicator_name) as indicator_name,max(source_id) as source_id,max(source_value) as source_value,max(source_note) as source_note,max(source_organization) as source_organization,min(year_date) as start_year, max(year_date) as end_year,count(*) as data_point,max(topics::text) as topics from world_bank_data  left join (select * from indicator_element   inner join (select topic_topics_hjid,string_agg(distinct value_,';') as topics,count(*) as number_of_topics from topic group by topic_topics_hjid) AS derivedTable  on indicator_element.topics_indicator_element_hjid=derivedTable.topic_topics_hjid) as indicators on indicator_id = indicators.id where data_value!='' and data_value!='0' and year_date is not null and source_organization is not null and source_organization !='' group by indicator_id,country_id</sql>

    </changeSet>
    

    <changeSet author="pether (generated)" id="1414872417007-330">
        
        <createIndex indexName="application_action_event_created_date_idx" tableName="application_action_event">
            <column name="created_date"/>
        </createIndex>
    
        <createIndex indexName="application_action_event_sessionid_idx" tableName="application_action_event">
            <column name="session_id"/>
        </createIndex>

        <createIndex indexName="application_action_event_element_idx" tableName="application_action_event">
            <column name="element_id"/>
        </createIndex>

        <createIndex indexName="application_session_ip_idx" tableName="application_session">
            <column name="ip_information"/>
        </createIndex>

        <createIndex indexName="application_session_created_date_idx" tableName="application_session">
            <column name="created_date"/>
        </createIndex>
        
        
    </changeSet>

</databaseChangeLog>