-- Tables

-- Alters

do $$ begin
IF EXISTS(select * from pg_indexes where schemaname ilike 'AKUZ' and tablename ilike 'T_CDA_SECTION_WORK_CHILD' and indexname ilike 'UQ_T_CDA_SECTION_WORK_CHILD_CdaChildHealthState')
THEN
drop index AKUZ.UQ_T_CDA_SECTION_WORK_CHILD_CdaChildHealthState;
END IF;
end; $$;


-- Добавляем вычисляемый столбец, если он не существует
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 
        FROM information_schema.columns 
        WHERE table_schema ilike 'akuz' 
        AND table_name ilike 't_cda_section_work_child' 
        AND column_name ilike 'cdachildhealthstate_uq'
    ) THEN
        EXECUTE 'ALTER TABLE akuz.t_cda_section_work_child 
                 ADD COLUMN cdachildhealthstate_uq INTEGER GENERATED ALWAYS AS 
                 (CASE WHEN cdachildhealthstate IS NOT NULL THEN NULL ELSE cdachildhealthstate END) STORED';
    END IF;
END $$;

-- Создаем уникальный индекс, если он не существует
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_indexes
        WHERE schemaname ilike 'akuz'
        AND tablename ilike 't_cda_section_work_child'
        AND indexname ilike 'uq_t_cda_section_work_child_cdachildhealthstate'
    ) THEN
        EXECUTE 'CREATE UNIQUE INDEX uq_t_cda_section_work_child_cdachildhealthstate 
                 ON akuz.t_cda_section_work_child (cdachildhealthstate, cdachildhealthstate_uq)';
    END IF;
END $$;