<?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="e.makarova">
    <comment>Create table user_link_user_application_user_application_update_role</comment>
    <sql>
     CREATE TABLE IF NOT EXISTS public.link_user_application_user_application_update_role
    (
     link_user_application_user_update_role_id bigserial,
     user_application_list_id bigint NOT NULL,
     user_update_role_id character varying(36) COLLATE pg_catalog."default" NOT NULL,
     created timestamp without time zone DEFAULT now(),
     CONSTRAINT pk_link_user_application_update_role PRIMARY KEY (link_user_application_user_update_role_id),
     CONSTRAINT uni_user_application_update_role UNIQUE (user_application_list_id, user_update_role_id),
     CONSTRAINT fk1_user_role FOREIGN KEY (user_update_role_id)
        REFERENCES public.user_application_role (user_role_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
     CONSTRAINT fk3_user_application_list FOREIGN KEY (user_application_list_id)
        REFERENCES public.user_application_list (user_application_list_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
    )

     TABLESPACE pg_default;

     ALTER TABLE IF EXISTS public.link_user_application_user_application_update_role
     OWNER to ervu_account_applications;

     GRANT ALL ON TABLE public.link_user_application_user_application_update_role TO ervu_account_applications;
    </sql>
  </changeSet>

  <changeSet id="0002" author="e.makarova">
    <comment>Create table public.link_user_application_update_ip_address</comment>
    <sql>
     CREATE TABLE IF NOT EXISTS public.link_user_application_update_ip_address
    (
     link_user_application_update_ip_address_id bigserial,
     user_application_list_id bigint NOT NULL,
     ip_address_update character varying COLLATE pg_catalog."default" NOT NULL,
     CONSTRAINT pk_link_user_application_update_ip_address PRIMARY KEY (link_user_application_update_ip_address_id),
     CONSTRAINT ip_address_update UNIQUE (user_application_list_id, ip_address_update),
     CONSTRAINT fk2_user_application_list FOREIGN KEY (user_application_list_id)
        REFERENCES public.user_application_list (user_application_list_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
    )

     TABLESPACE pg_default;

     ALTER TABLE IF EXISTS public.link_user_application_update_ip_address
     OWNER to ervu_account_applications;

     GRANT ALL ON TABLE public.link_user_application_update_ip_address TO ervu_account_applications;

     COMMENT ON TABLE public.link_user_application_update_ip_address
     IS 'Таблица связи заявок и ip адрессов пользователя на изменение';

     COMMENT ON COLUMN public.link_user_application_update_ip_address.user_application_list_id
     IS 'Идентификатор списка заявок пользователя';

     COMMENT ON COLUMN public.link_user_application_update_ip_address.ip_address_update
     IS 'ip адресс на изменение';
    </sql>
  </changeSet>

  <changeSet id="0003" author="e.makarova">
    <comment>Create table user_application_list</comment>
    <sql>
      ALTER TABLE IF EXISTS public.user_application_list
	  ADD COLUMN IF NOT EXISTS person_id character varying;
	  
	  COMMENT ON COLUMN public.user_application_list.person_id
      IS 'ссылка на персональные данные';
	  
	  ALTER TABLE IF EXISTS public.user_application_list
	  ADD COLUMN IF NOT EXISTS update_secondname character varying(1000);

      COMMENT ON COLUMN public.user_application_list.update_secondname
      IS 'Фамилия на изменение';

      ALTER TABLE IF EXISTS public.user_application_list
	  ADD COLUMN IF NOT EXISTS update_firstname character varying(1000);

      COMMENT ON COLUMN public.user_application_list.update_firstname
      IS 'Имя на изменение';
	  
	  ALTER TABLE IF EXISTS public.user_application_list
	  ADD COLUMN IF NOT EXISTS update_middlename character varying(1000);

      COMMENT ON COLUMN public.user_application_list.update_middlename
      IS 'Отчество на изменение';

      ALTER TABLE IF EXISTS public.user_application_list
	  ADD COLUMN IF NOT EXISTS update_sex character varying;

      COMMENT ON COLUMN public.user_application_list.update_sex
      IS 'Пол на изменение';

      ALTER TABLE IF EXISTS public.user_application_list
	  ADD COLUMN IF NOT EXISTS update_birth_date date;

      COMMENT ON COLUMN public.user_application_list.update_birth_date
      IS 'Дата рождения на изменение';

      ALTER TABLE IF EXISTS public.user_application_list
	  ADD COLUMN IF NOT EXISTS update_job_position character varying;

      COMMENT ON COLUMN public.user_application_list.update_job_position
      IS 'Должность на изменение';
    </sql>
  </changeSet>
</databaseChangeLog>