
UPDATE menu_link SET presenter = 'AgendaConfiguration:Config', action = 'agenda' WHERE presenter = 'Configuration:Config' AND action = 'agenda';
UPDATE menu_link SET presenter = 'AgendaConfiguration:Config', action = 'segment' WHERE presenter = 'Configuration:Config' AND action = 'segment';
UPDATE menu_link SET presenter = 'AgendaConfiguration:Config', action = 'attributeGroup' WHERE presenter = 'Configuration:Config' AND action = 'attributeGroup';
UPDATE menu_link SET presenter = 'AgendaConfiguration:Config', action = 'attribute' WHERE presenter = 'Configuration:Config' AND action = 'attribute';
UPDATE menu_link SET presenter = 'AgendaConfiguration:Agenda', action = 'detail' WHERE presenter = 'Configuration:Agenda' AND action = 'detail';
UPDATE menu_link SET presenter = 'Agenda:Admin', action = 'enableAgendaEntity' WHERE presenter = 'Flexii:Admin' AND action = 'enableAgendaEntity';
UPDATE menu_link SET presenter = 'Base:Admin' WHERE presenter = 'Flexii:Admin';
UPDATE menu_link SET presenter = 'Agenda:Admin', action = 'email' WHERE  name = 'admin_email';
UPDATE menu_link SET presenter = 'Agenda:Admin', action = 'editEmail' WHERE name = 'admin_email_edit';
UPDATE menu_link SET presenter = 'Base:Admin', action = 'editLookup' WHERE name = 'admin_lookup_edit';

-- --------------------------- DISCLAIMER ---------------------------
-- These changes are already in structure and data, but need to run at client databases
----------------------------------------------------------------------

-- CREATE TABLE locale
-- (
--     id          SERIAL                              NOT NULL
--         CONSTRAINT pk_locale
--             PRIMARY KEY,
--     locale      VARCHAR(2)                          NOT NULL,
--     description VARCHAR(50)                         NOT NULL,
--     flag_icon   VARCHAR(100)                        NOT NULL,
--     enabled     BOOLEAN   DEFAULT TRUE              NOT NULL,
--     dt_ins      TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
--     dt_upd      TIMESTAMP
-- );
--
-- INSERT INTO locale (locale, description, flag_icon, enabled) VALUES ('cs', 'Čeština', 'cs.png', true);
-- INSERT INTO locale (locale, description, flag_icon, enabled) VALUES ('en', 'English', 'en.png', false);
--
-- DROP TABLE translate_lookup_item;
--
-- alter table appuser rename column id_lookup_locale to id_locale;
--
-- alter table appuser drop constraint fk_appuser_locale;
--
-- alter table appuser
--     add constraint fk_appuser_locale
--         foreign key (id_locale) references locale
--             on update restrict on delete restrict;
--
-- DROP TABLE lookup_locale;
--
----------------------------------------------------------------------


alter table user_group
    add description varchar(200);

UPDATE user_group SET description = name WHERE 1 = 1;

------------------- #7400 + #7401 --
INSERT INTO menu_link (name, presenter, action)
VALUES (
    'admin_evosoft_tools_rabbitDisplay',
    'EvosoftTools:RabbitDisplay',
    'default'
);

INSERT INTO menu_item (id_menu_section, id_agenda, id_calendar, name, icon, id_menu_link, priority)
VALUES (
    (SELECT ID FROM menu_section WHERE name = 'administration')
    , null
    , null
    , 'rabbitDisplayAdmin'
    , 'fad fa-rabbit'
    , (SELECT ID FROM menu_link WHERE name = 'admin_evosoft_tools_rabbitDisplay')
    , '990'
);

INSERT INTO menu_link (name, presenter, action)
VALUES (
   'admin_evosoft_tools_serverinfo',
   'EvosoftTools:ServerInfo',
   'default'
);

INSERT INTO menu_item (id_menu_section, id_agenda, id_calendar, name, icon, id_menu_link, priority)
VALUES (
   (SELECT ID FROM menu_section WHERE name = 'administration')
   , null
   , null
   , 'serverInfoAdmin'
   , 'fad fa-server'
   , (SELECT ID FROM menu_link WHERE name = 'admin_evosoft_tools_serverinfo')
   , '999'
);


UPDATE menu_item SET priority = 980, is_separator_before = true WHERE name = 'rebuild_agenda_views';

----------------------------------------------------
-- 2.3.8
-- SQL for this version ends here
----------------------------------------------------

------------------- #4287
update report
set id_appuser_created_by = (select id from appuser where username = 'admin1')
where id_appuser_created_by is null;

alter table report alter column id_appuser_created_by set not null;

update report
set id_appuser_updated_by = id_appuser_created_by
where id_appuser_updated_by is null;

alter table report alter column id_appuser_updated_by set not null;

-- #7468
update gdpr_agenda_definition_duration_date set id_appuser_updated_by = id_appuser_created_by where id_appuser_updated_by is null;
update gdpr_agenda_definition_exception_on_agenda_entity set id_appuser_updated_by = id_appuser_created_by where id_appuser_updated_by is null;
update report set id_appuser_updated_by = id_appuser_created_by where id_appuser_updated_by is null;
update agenda_values set id_appuser_updated_by = id_appuser_created_by where id_appuser_updated_by is null;
update agenda_has_file set id_appuser_updated_by = id_appuser_created_by where id_appuser_updated_by is null;
update agenda_entity_has_agenda_entity set id_appuser_updated_by = id_appuser_created_by where id_appuser_updated_by is null;
update agenda_has_agenda_values set id_appuser_updated_by = id_appuser_created_by where id_appuser_updated_by is null;
update agenda_entity set id_appuser_updated_by = id_appuser_created_by where id_appuser_updated_by is null;
update gdpr_agenda_entity_reason_has_gdpr_agenda_definition set id_appuser_updated_by = id_appuser_created_by where id_appuser_updated_by is null;
update gdpr_agenda_definition set id_appuser_updated_by = id_appuser_created_by where id_appuser_updated_by is null;
update gdpr_agenda_entity_reason set id_appuser_updated_by = id_appuser_created_by where id_appuser_updated_by is null;

alter table gdpr_agenda_definition_duration_date alter column id_appuser_created_by set not null;
alter table gdpr_agenda_definition_exception_on_agenda_entity alter column id_appuser_created_by set not null;
alter table report alter column id_appuser_created_by set not null;
alter table agenda_values alter column id_appuser_created_by set not null;
alter table agenda_has_file alter column id_appuser_created_by set not null;
alter table agenda_entity_has_agenda_entity alter column id_appuser_created_by set not null;
alter table agenda_has_agenda_values alter column id_appuser_created_by set not null;
alter table agenda_entity alter column id_appuser_created_by set not null;
alter table gdpr_agenda_entity_reason_has_gdpr_agenda_definition alter column id_appuser_created_by set not null;
alter table gdpr_agenda_definition alter column id_appuser_created_by set not null;
alter table gdpr_agenda_entity_reason alter column id_appuser_created_by set not null;

alter table gdpr_agenda_definition_duration_date alter column id_appuser_updated_by set not null;
alter table gdpr_agenda_definition_exception_on_agenda_entity alter column id_appuser_updated_by set not null;
alter table report alter column id_appuser_updated_by set not null;
alter table agenda_values alter column id_appuser_updated_by set not null;
alter table agenda_has_file alter column id_appuser_updated_by set not null;
alter table agenda_entity_has_agenda_entity alter column id_appuser_updated_by set not null;
alter table agenda_has_agenda_values alter column id_appuser_updated_by set not null;
alter table agenda_entity alter column id_appuser_updated_by set not null;
alter table gdpr_agenda_entity_reason_has_gdpr_agenda_definition alter column id_appuser_updated_by set not null;
alter table gdpr_agenda_definition alter column id_appuser_updated_by set not null;
alter table gdpr_agenda_entity_reason alter column id_appuser_updated_by set not null;

update gdpr_agenda_entity_reason set dt_upd = dt_ins where dt_upd is null;
update agenda_has_agenda_merge_into_tab set dt_upd = dt_ins where dt_upd is null;
update gdpr_agenda_entity_reason_has_gdpr_agenda_definition set dt_upd = dt_ins where dt_upd is null;
update agenda_has_agenda_merge_into_tab_column set dt_upd = dt_ins where dt_upd is null;
update gdpr_segment_control_by_agenda_value set dt_upd = dt_ins where dt_upd is null;
update gdpr_agenda_definition_duration_date set dt_upd = dt_ins where dt_upd is null;
update lookup_general_config_extension set dt_upd = dt_ins where dt_upd is null;
update agenda_entity_exchange set dt_upd = dt_ins where dt_upd is null;
update gdpr_agenda_definition_exception_on_agenda_entity set dt_upd = dt_ins where dt_upd is null;
update appaction set dt_upd = dt_ins where dt_upd is null;
update appuser set dt_upd = dt_ins where dt_upd is null;
update appuser_has_favorite_report set dt_upd = dt_ins where dt_upd is null;
update appuser_session set dt_upd = dt_ins where dt_upd is null;
update attribute set dt_upd = dt_ins where dt_upd is null;
update agenda_unique_value set dt_upd = dt_ins where dt_upd is null;
update element_graph set dt_upd = dt_ins where dt_upd is null;
update element_header set dt_upd = dt_ins where dt_upd is null;
update element_input set dt_upd = dt_ins where dt_upd is null;
update element_table set dt_upd = dt_ins where dt_upd is null;
update email_definition set dt_upd = dt_ins where dt_upd is null;
update email_queue set dt_upd = dt_ins where dt_upd is null;
update email_recipient set dt_upd = dt_ins where dt_upd is null;
update external_application set dt_upd = dt_ins where dt_upd is null;
update log_operation set dt_upd = dt_ins where dt_upd is null;
update lookup_banker set dt_upd = dt_ins where dt_upd is null;
update agenda_unique_value_control set dt_upd = dt_ins where dt_upd is null;
update lookup_country set dt_upd = dt_ins where dt_upd is null;
update lookup_currency set dt_upd = dt_ins where dt_upd is null;
update lookup_identifier_type set dt_upd = dt_ins where dt_upd is null;
update lookup_language set dt_upd = dt_ins where dt_upd is null;
update lookup_legal_status set dt_upd = dt_ins where dt_upd is null;
update lookup_rounding set dt_upd = dt_ins where dt_upd is null;
update lookup_subject_type set dt_upd = dt_ins where dt_upd is null;
update lookup_title set dt_upd = dt_ins where dt_upd is null;
update lookup_title_post set dt_upd = dt_ins where dt_upd is null;
update master_lookup set dt_upd = dt_ins where dt_upd is null;
update report set dt_upd = dt_ins where dt_upd is null;
update report_has_elem_has_elem_input set dt_upd = dt_ins where dt_upd is null;
update report_has_element set dt_upd = dt_ins where dt_upd is null;
update translate_lookup_item set dt_upd = dt_ins where dt_upd is null;
update user_group set dt_upd = dt_ins where dt_upd is null;
update agenda_unique_value_definition set dt_upd = dt_ins where dt_upd is null;
update user_group_has_appaction set dt_upd = dt_ins where dt_upd is null;
update user_group_has_appuser set dt_upd = dt_ins where dt_upd is null;
update user_group_has_report set dt_upd = dt_ins where dt_upd is null;
update locale set dt_upd = dt_ins where dt_upd is null;
update lookup_gdpr_base set dt_upd = dt_ins where dt_upd is null;
update lookup_subject_category set dt_upd = dt_ins where dt_upd is null;
update agenda set dt_upd = dt_ins where dt_upd is null;
update agenda_segment set dt_upd = dt_ins where dt_upd is null;
update agenda_attribute_group set dt_upd = dt_ins where dt_upd is null;
update lookup_contact_person set dt_upd = dt_ins where dt_upd is null;
update agenda_definition set dt_upd = dt_ins where dt_upd is null;
update agenda_has_agenda set dt_upd = dt_ins where dt_upd is null;
update agenda_values set dt_upd = dt_ins where dt_upd is null;
update agenda_has_file set dt_upd = dt_ins where dt_upd is null;
update menu_section set dt_upd = dt_ins where dt_upd is null;
update menu_item set dt_upd = dt_ins where dt_upd is null;
update menu_link set dt_upd = dt_ins where dt_upd is null;
update menu_item_has_menu_link set dt_upd = dt_ins where dt_upd is null;
update agenda_entity_has_agenda_entity set dt_upd = dt_ins where dt_upd is null;
update agenda_has_agenda_definition set dt_upd = dt_ins where dt_upd is null;
update agenda_has_agenda_values set dt_upd = dt_ins where dt_upd is null;
update calendar_attribute set dt_upd = dt_ins where dt_upd is null;
update lookup_reminder set dt_upd = dt_ins where dt_upd is null;
update admin_editable_lookup set dt_upd = dt_ins where dt_upd is null;
update agenda_has_agenda_show_attribute set dt_upd = dt_ins where dt_upd is null;
update user_group_has_agenda set dt_upd = dt_ins where dt_upd is null;
update agenda_has_agenda_to_agenda_has_agenda set dt_upd = dt_ins where dt_upd is null;
update lookup_connection_role set dt_upd = dt_ins where dt_upd is null;
update agenda_definition_specification_by_value set dt_upd = dt_ins where dt_upd is null;
update lookup_general_config_category set dt_upd = dt_ins where dt_upd is null;
update agenda_entity set dt_upd = dt_ins where dt_upd is null;
update agenda_entity_permission_by_definition set dt_upd = dt_ins where dt_upd is null;
update agenda_entity_has_agenda_entity_permission_by_definition set dt_upd = dt_ins where dt_upd is null;
update agenda_definition_to_agenda_definition_relation set dt_upd = dt_ins where dt_upd is null;
update lookup_operator set dt_upd = dt_ins where dt_upd is null;
update agenda_has_agenda_to_agenda_has_agenda_definition set dt_upd = dt_ins where dt_upd is null;
update agenda_has_custom_class set dt_upd = dt_ins where dt_upd is null;
update agenda_definition_to_agenda_definition_mapping set dt_upd = dt_ins where dt_upd is null;
update agenda_to_agenda_sync set dt_upd = dt_ins where dt_upd is null;
update sync_has_mapping set dt_upd = dt_ins where dt_upd is null;
update gdpr_agenda_definition set dt_upd = dt_ins where dt_upd is null;
update agenda_has_agenda_merge_into_tab_agenda_has_agenda set dt_upd = dt_ins where dt_upd is null;
update agenda_has_agenda_merge_into_tab_column_agenda_has_agenda set dt_upd = dt_ins where dt_upd is null;
update agenda_entity_search_in_overview set dt_upd = dt_ins where dt_upd is null;
update configuration_group set dt_upd = dt_ins where dt_upd is null;
update configuration_key set dt_upd = dt_ins where dt_upd is null;
update configuration_definition set dt_upd = dt_ins where dt_upd is null;
update configuration_values set dt_upd = dt_ins where dt_upd is null;
update calendar set dt_upd = dt_ins where dt_upd is null;
update calendar_has_attribute set dt_upd = dt_ins where dt_upd is null;
update calendar_has_agenda set dt_upd = dt_ins where dt_upd is null;
update log_api_request set dt_upd = dt_ins where dt_upd is null;
update log_api_response set dt_upd = dt_ins where dt_upd is null;
update lookup_error_action set dt_upd = dt_ins where dt_upd is null;
update exchange_attribute set dt_upd = dt_ins where dt_upd is null;
update exchange_attribute_has_agenda_definition set dt_upd = dt_ins where dt_upd is null;
-- update general_config set dt_upd = dt_ins where dt_upd is null;
update general_config set dt_upd = dt_int where dt_upd is null;

alter table gdpr_agenda_entity_reason alter column dt_upd set not null;
alter table agenda_has_agenda_merge_into_tab alter column dt_upd set not null;
alter table gdpr_agenda_entity_reason_has_gdpr_agenda_definition alter column dt_upd set not null;
alter table agenda_has_agenda_merge_into_tab_column alter column dt_upd set not null;
alter table gdpr_segment_control_by_agenda_value alter column dt_upd set not null;
alter table gdpr_agenda_definition_duration_date alter column dt_upd set not null;
alter table lookup_general_config_extension alter column dt_upd set not null;
alter table agenda_entity_exchange alter column dt_upd set not null;
alter table gdpr_agenda_definition_exception_on_agenda_entity alter column dt_upd set not null;
alter table appaction alter column dt_upd set not null;
alter table appuser alter column dt_upd set not null;
alter table appuser_has_favorite_report alter column dt_upd set not null;
alter table appuser_session alter column dt_upd set not null;
alter table attribute alter column dt_upd set not null;
alter table agenda_unique_value alter column dt_upd set not null;
alter table element_graph alter column dt_upd set not null;
alter table element_header alter column dt_upd set not null;
alter table element_input alter column dt_upd set not null;
alter table element_table alter column dt_upd set not null;
alter table email_definition alter column dt_upd set not null;
alter table email_queue alter column dt_upd set not null;
alter table email_recipient alter column dt_upd set not null;
alter table external_application alter column dt_upd set not null;
alter table log_operation alter column dt_upd set not null;
alter table lookup_banker alter column dt_upd set not null;
alter table agenda_unique_value_control alter column dt_upd set not null;
alter table lookup_country alter column dt_upd set not null;
alter table lookup_currency alter column dt_upd set not null;
alter table lookup_identifier_type alter column dt_upd set not null;
alter table lookup_language alter column dt_upd set not null;
alter table lookup_legal_status alter column dt_upd set not null;
alter table lookup_rounding alter column dt_upd set not null;
alter table lookup_subject_type alter column dt_upd set not null;
alter table lookup_title alter column dt_upd set not null;
alter table lookup_title_post alter column dt_upd set not null;
alter table master_lookup alter column dt_upd set not null;
alter table report alter column dt_upd set not null;
alter table report_has_elem_has_elem_input alter column dt_upd set not null;
alter table report_has_element alter column dt_upd set not null;
alter table translate_lookup_item alter column dt_upd set not null;
alter table user_group alter column dt_upd set not null;
alter table agenda_unique_value_definition alter column dt_upd set not null;
alter table user_group_has_appaction alter column dt_upd set not null;
alter table user_group_has_appuser alter column dt_upd set not null;
alter table user_group_has_report alter column dt_upd set not null;
alter table locale alter column dt_upd set not null;
alter table lookup_gdpr_base alter column dt_upd set not null;
alter table lookup_subject_category alter column dt_upd set not null;
alter table agenda alter column dt_upd set not null;
alter table agenda_segment alter column dt_upd set not null;
alter table agenda_attribute_group alter column dt_upd set not null;
alter table lookup_contact_person alter column dt_upd set not null;
alter table agenda_definition alter column dt_upd set not null;
alter table agenda_has_agenda alter column dt_upd set not null;
alter table agenda_values alter column dt_upd set not null;
alter table agenda_has_file alter column dt_upd set not null;
alter table menu_section alter column dt_upd set not null;
alter table menu_item alter column dt_upd set not null;
alter table menu_link alter column dt_upd set not null;
alter table menu_item_has_menu_link alter column dt_upd set not null;
alter table agenda_entity_has_agenda_entity alter column dt_upd set not null;
alter table agenda_has_agenda_definition alter column dt_upd set not null;
alter table agenda_has_agenda_values alter column dt_upd set not null;
alter table calendar_attribute alter column dt_upd set not null;
alter table lookup_reminder alter column dt_upd set not null;
alter table admin_editable_lookup alter column dt_upd set not null;
alter table agenda_has_agenda_show_attribute alter column dt_upd set not null;
alter table user_group_has_agenda alter column dt_upd set not null;
alter table agenda_has_agenda_to_agenda_has_agenda alter column dt_upd set not null;
alter table lookup_connection_role alter column dt_upd set not null;
alter table agenda_definition_specification_by_value alter column dt_upd set not null;
alter table lookup_general_config_category alter column dt_upd set not null;
alter table agenda_entity alter column dt_upd set not null;
alter table agenda_entity_permission_by_definition alter column dt_upd set not null;
alter table agenda_entity_has_agenda_entity_permission_by_definition alter column dt_upd set not null;
alter table agenda_definition_to_agenda_definition_relation alter column dt_upd set not null;
alter table lookup_operator alter column dt_upd set not null;
alter table agenda_has_agenda_to_agenda_has_agenda_definition alter column dt_upd set not null;
alter table agenda_has_custom_class alter column dt_upd set not null;
alter table agenda_definition_to_agenda_definition_mapping alter column dt_upd set not null;
alter table agenda_to_agenda_sync alter column dt_upd set not null;
alter table sync_has_mapping alter column dt_upd set not null;
alter table gdpr_agenda_definition alter column dt_upd set not null;
alter table agenda_has_agenda_merge_into_tab_agenda_has_agenda alter column dt_upd set not null;
alter table agenda_has_agenda_merge_into_tab_column_agenda_has_agenda alter column dt_upd set not null;
alter table agenda_entity_search_in_overview alter column dt_upd set not null;
alter table configuration_group alter column dt_upd set not null;
alter table configuration_key alter column dt_upd set not null;
alter table configuration_definition alter column dt_upd set not null;
alter table configuration_values alter column dt_upd set not null;
alter table calendar alter column dt_upd set not null;
alter table calendar_has_attribute alter column dt_upd set not null;
alter table calendar_has_agenda alter column dt_upd set not null;
alter table log_api_request alter column dt_upd set not null;
alter table log_api_response alter column dt_upd set not null;
alter table lookup_error_action alter column dt_upd set not null;
alter table exchange_attribute alter column dt_upd set not null;
alter table exchange_attribute_has_agenda_definition alter column dt_upd set not null;
-- alter table general_config alter column dt_upd set not null;
alter table general_config alter column dt_upd set not null;

alter table gdpr_agenda_entity_reason alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_has_agenda_merge_into_tab alter column dt_upd set default CURRENT_TIMESTAMP;
alter table gdpr_agenda_entity_reason_has_gdpr_agenda_definition alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_has_agenda_merge_into_tab_column alter column dt_upd set default CURRENT_TIMESTAMP;
alter table gdpr_segment_control_by_agenda_value alter column dt_upd set default CURRENT_TIMESTAMP;
alter table gdpr_agenda_definition_duration_date alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_general_config_extension alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_entity_exchange alter column dt_upd set default CURRENT_TIMESTAMP;
alter table gdpr_agenda_definition_exception_on_agenda_entity alter column dt_upd set default CURRENT_TIMESTAMP;
alter table appaction alter column dt_upd set default CURRENT_TIMESTAMP;
alter table appuser alter column dt_upd set default CURRENT_TIMESTAMP;
alter table appuser_has_favorite_report alter column dt_upd set default CURRENT_TIMESTAMP;
alter table appuser_session alter column dt_upd set default CURRENT_TIMESTAMP;
alter table attribute alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_unique_value alter column dt_upd set default CURRENT_TIMESTAMP;
alter table element_graph alter column dt_upd set default CURRENT_TIMESTAMP;
alter table element_header alter column dt_upd set default CURRENT_TIMESTAMP;
alter table element_input alter column dt_upd set default CURRENT_TIMESTAMP;
alter table element_table alter column dt_upd set default CURRENT_TIMESTAMP;
alter table email_definition alter column dt_upd set default CURRENT_TIMESTAMP;
alter table email_queue alter column dt_upd set default CURRENT_TIMESTAMP;
alter table email_recipient alter column dt_upd set default CURRENT_TIMESTAMP;
alter table external_application alter column dt_upd set default CURRENT_TIMESTAMP;
alter table log_operation alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_banker alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_unique_value_control alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_country alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_currency alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_identifier_type alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_language alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_legal_status alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_rounding alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_subject_type alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_title alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_title_post alter column dt_upd set default CURRENT_TIMESTAMP;
alter table master_lookup alter column dt_upd set default CURRENT_TIMESTAMP;
alter table report alter column dt_upd set default CURRENT_TIMESTAMP;
alter table report_has_elem_has_elem_input alter column dt_upd set default CURRENT_TIMESTAMP;
alter table report_has_element alter column dt_upd set default CURRENT_TIMESTAMP;
alter table translate_lookup_item alter column dt_upd set default CURRENT_TIMESTAMP;
alter table user_group alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_unique_value_definition alter column dt_upd set default CURRENT_TIMESTAMP;
alter table user_group_has_appaction alter column dt_upd set default CURRENT_TIMESTAMP;
alter table user_group_has_appuser alter column dt_upd set default CURRENT_TIMESTAMP;
alter table user_group_has_report alter column dt_upd set default CURRENT_TIMESTAMP;
alter table locale alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_gdpr_base alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_subject_category alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_segment alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_attribute_group alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_contact_person alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_definition alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_has_agenda alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_values alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_has_file alter column dt_upd set default CURRENT_TIMESTAMP;
alter table menu_section alter column dt_upd set default CURRENT_TIMESTAMP;
alter table menu_item alter column dt_upd set default CURRENT_TIMESTAMP;
alter table menu_link alter column dt_upd set default CURRENT_TIMESTAMP;
alter table menu_item_has_menu_link alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_entity_has_agenda_entity alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_has_agenda_definition alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_has_agenda_values alter column dt_upd set default CURRENT_TIMESTAMP;
alter table calendar_attribute alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_reminder alter column dt_upd set default CURRENT_TIMESTAMP;
alter table admin_editable_lookup alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_has_agenda_show_attribute alter column dt_upd set default CURRENT_TIMESTAMP;
alter table user_group_has_agenda alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_has_agenda_to_agenda_has_agenda alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_connection_role alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_definition_specification_by_value alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_general_config_category alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_entity alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_entity_permission_by_definition alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_entity_has_agenda_entity_permission_by_definition alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_definition_to_agenda_definition_relation alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_operator alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_has_agenda_to_agenda_has_agenda_definition alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_has_custom_class alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_definition_to_agenda_definition_mapping alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_to_agenda_sync alter column dt_upd set default CURRENT_TIMESTAMP;
alter table sync_has_mapping alter column dt_upd set default CURRENT_TIMESTAMP;
alter table gdpr_agenda_definition alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_has_agenda_merge_into_tab_agenda_has_agenda alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_has_agenda_merge_into_tab_column_agenda_has_agenda alter column dt_upd set default CURRENT_TIMESTAMP;
alter table agenda_entity_search_in_overview alter column dt_upd set default CURRENT_TIMESTAMP;
alter table configuration_group alter column dt_upd set default CURRENT_TIMESTAMP;
alter table configuration_key alter column dt_upd set default CURRENT_TIMESTAMP;
alter table configuration_definition alter column dt_upd set default CURRENT_TIMESTAMP;
alter table configuration_values alter column dt_upd set default CURRENT_TIMESTAMP;
alter table calendar alter column dt_upd set default CURRENT_TIMESTAMP;
alter table calendar_has_attribute alter column dt_upd set default CURRENT_TIMESTAMP;
alter table calendar_has_agenda alter column dt_upd set default CURRENT_TIMESTAMP;
alter table log_api_request alter column dt_upd set default CURRENT_TIMESTAMP;
alter table log_api_response alter column dt_upd set default CURRENT_TIMESTAMP;
alter table lookup_error_action alter column dt_upd set default CURRENT_TIMESTAMP;
alter table exchange_attribute alter column dt_upd set default CURRENT_TIMESTAMP;
alter table exchange_attribute_has_agenda_definition alter column dt_upd set default CURRENT_TIMESTAMP;
-- alter table general_config alter column dt_upd set default CURRENT_TIMESTAMP;
alter table general_config alter column dt_upd set default CURRENT_TIMESTAMP;

-------------------------

-- #4874
alter table agenda drop column is_for_stalker;
alter table agenda_segment drop column icon;
alter table agenda_attribute_group drop column is_collapsed;
alter table agenda_attribute_group drop column is_importable;
alter table attribute drop column dependant_lookup;
alter table attribute drop column id_dependant_attribute;
alter table attribute drop column external_value_table;
alter table attribute drop column external_value_column;
alter table attribute drop column external_value_row;
alter table menu_section drop column is_default;

-- #4924

--
-- new table agenda_has_segment_definition
--
CREATE TABLE agenda_has_segment_definition
(
    id                SERIAL                              NOT NULL
        CONSTRAINT pk_agenda_has_segment_definition
            PRIMARY KEY,
    id_agenda         SMALLINT                             NOT NULL
        CONSTRAINT fk_agenda_has_segment_definition_agenda_id
            REFERENCES agenda,
    id_agenda_segment SMALLINT
        CONSTRAINT fk_agenda_has_segment_definition_agenda_segment_id
            REFERENCES agenda_segment,
    priority          INTEGER                             NOT NULL,
    is_main           BOOLEAN   DEFAULT FALSE             NOT NULL,
    enabled           BOOLEAN   DEFAULT TRUE              NOT NULL,
    dt_ins            TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd            TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE UNIQUE INDEX unq_agenda_has_segment_definition_id_agenda_id_agenda_segment
    ON agenda_has_segment_definition (id_agenda, id_agenda_segment);

--
-- new table agenda_has_segment_has_attribute_group_definition
--
create table agenda_has_segment_has_attribute_group_definition
(
    id serial not null
        constraint pk_agenda_has_segment_has_attribute_group_definition
            primary key,
    id_agenda_has_segment_definition SMALLINT not null
        constraint fk_agenda_has_seg_has_att_grp_def_agenda_has_seg_def_id
            references agenda_has_segment_definition,
    id_agenda_attribute_group SMALLINT not null
        constraint fk_agenda_has_seg_has_att_group_def_agenda_att_grp_id
            references agenda_attribute_group,
    is_read_only bool default FALSE not null,
    priority          SMALLINT                           NOT NULL,
    enabled bool default true not null,
    dt_ins timestamp default CURRENT_TIMESTAMP not null,
    dt_upd timestamp default CURRENT_TIMESTAMP not null
);

create unique index unq_agenda_has_segment_has_attribute_group_definition
    on agenda_has_segment_has_attribute_group_definition (id_agenda_has_segment_definition, id_agenda_attribute_group);

-- modify table agenda_definition
alter table agenda_definition
    add id_agenda_segment_attribute_group_definition int; -- not null

alter table agenda_definition
    add constraint fk_agenda_def_agenda_has_seg_has_att_grp_def_id
        foreign key (id_agenda_segment_attribute_group_definition) references agenda_has_segment_has_attribute_group_definition;


--
-- new table - attribute_data_type
--
CREATE TABLE attribute_data_type (
                                     id serial NOT NULL,
                                     name varchar(400) NOT NULL,
                                     priority smallint NOT NULL,
                                     enabled boolean NOT NULL DEFAULT true,
                                     dt_ins timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                     dt_upd timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                     CONSTRAINT pk_attribute_data_type PRIMARY KEY (id),
                                     CONSTRAINT unq_attribute_data_type_name UNIQUE (name),
                                     CONSTRAINT unq_attribute_data_type_priority UNIQUE (priority)
);

--
-- data - attribute_data_type
--
insert into attribute_data_type (name, priority) VALUES
    ('auto', 10)
    , ('string', 20)
    , ('bool', 30)
    , ('float', 40)
    , ('int', 50)
    , ('agenda_has_agenda', 60)
    , ('date', 70)
    , ('datetime', 80)
    , ('file', 90)
    , ('multiple_files', 100)
    , ('email', 110)
    , ('password', 120)
    , ('avatar',  130)
    , ('lookup', 140)
    , ('lookup_solo_table', 150)
    , ('lookup_table', 160)
    , ('lookup_table_contra', 170)
    , ('appuser', 180)
    , ('long_text', 190)
    , ('url', 200)
    , ('year', 210)
    , ('agenda', 220)
    , ('depended_aha', 230)
    , ('aha_to_aha', 240)
    , ('format_text', 250)
;

--
-- update table - attribute
--
alter table attribute
    add id_attribute_data_type int default null;

alter table attribute
    add constraint fk_id_attribute_data_type
        foreign key (id_attribute_data_type) references attribute_data_type
            on update restrict on delete restrict;

--
-- new materialized view - mv_agenda_definition
--
-- DROP MATERIALIZED VIEW mv_api_agenda_mapping;
-- DROP VIEW v_agenda_entity_title;
-- DROP VIEW v_agenda_definition_specification_by_value;
-- DROP MATERIALIZED VIEW mv_agenda_definition;
--
CREATE MATERIALIZED VIEW mv_agenda_definition AS
SELECT ad.id
       , ahsd.id_agenda
       , ahsd.id_agenda_segment
       , ahshagd.id_agenda_attribute_group
       , ad.is_control_for_group
       , ad.id_attribute
       , ad.priority
       , ad.is_not_null
       , ad.default_value
       , ad.is_disabled
       , ad.is_read_only
       , ad.tooltip
       , ad.field_column_length
       , ad.skip_column_length
       , ad.field_column_height
       , ad.in_title_and_priority
       , ad.is_in_datagrid
       , ad.is_editable_in_datagrid
       , ad.is_hidden_in_datagrid
       , ad.datagrid_order_priority
       , ad.datagrid_order_by
       , ad.is_search_by
       , ad.is_show_in_linked_agenda
       , ad.is_unique
       , ad.min_length
       , ad.max_length
       , ad.min_value
       , ad.max_value
       , ad.pattern
       , ad.custom_validation
       , ad.is_in
       , ad.is_not_in
       , ad.is_importable
       , ad.id_agenda_definition_depended
       , ad.enabled
       , ad.dt_ins
       , ad.dt_upd
       , ad.id_agenda_segment_attribute_group_definition
       , ahshagd.id_agenda_has_segment_definition
       , a.name AS agenda_name
       , aseg.name AS segment_name
       , aag.name AS attribute_group_name
       , att.name AS attribute_name
       , attdt.name AS data_type
FROM agenda_definition ad
         JOIN agenda_has_segment_has_attribute_group_definition ahshagd
              ON ad.id_agenda_segment_attribute_group_definition = ahshagd.id
         JOIN agenda_has_segment_definition ahsd ON ahshagd.id_agenda_has_segment_definition = ahsd.id
         JOIN agenda a ON ahsd.id_agenda = a.id
         JOIN agenda_segment aseg ON ahsd.id_agenda_segment = aseg.id
         JOIN agenda_attribute_group aag ON ahshagd.id_agenda_attribute_group = aag.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN attribute_data_type attdt ON att.id_attribute_data_type = attdt.id
WHERE ad.enabled = TRUE
  AND ahshagd.enabled = TRUE
  AND ahsd.enabled = TRUE
  AND a.enabled = TRUE
  AND aseg.enabled = TRUE
  AND aag.enabled = TRUE
  AND att.enabled = TRUE
  AND attdt.enabled = TRUE;

CREATE UNIQUE INDEX ON mv_agenda_definition (id);


--
-- updated view - v_agenda_entity_title
--
CREATE OR REPLACE VIEW v_agenda_entity_title AS
SELECT
    final.id_agenda_entity
     , string_agg((final.value)::text, ' '::text ORDER BY final.in_title_and_priority ASC) AS title
     , ae.uid
     , ae.id_agenda
     , ae.enabled
     , ae.is_anonymized
     , ae.id_appuser_created_by
     , ae.id_appuser_updated_by
     , ae.dt_ins
     , ae.dt_upd
FROM
    ((SELECT
          val.value
           , val.id_agenda_entity
           , ad.in_title_and_priority
      FROM
          (
           (SELECT
                val_1.value
                 , val_1.id_agenda_definition
                 , val_1.id_agenda_entity
            FROM
                agenda_values val_1
            WHERE
                    val_1.id_agenda_definition IN (
                    SELECT ad_1.id
                    FROM mv_agenda_definition ad_1
                             JOIN agenda_has_segment_definition ahsd ON ad_1.id_agenda_has_segment_definition = ahsd.id
                    WHERE ad_1.enabled = TRUE
                      AND ahsd.enabled = TRUE
                      AND ad_1.in_title_and_priority IS NOT NULL
                      AND ahsd.is_main = TRUE
                )
           ) val
              JOIN agenda_definition ad ON ((val.id_agenda_definition = ad.id))
              )
      ORDER BY ad.in_title_and_priority) final
        JOIN agenda_entity ae ON ((final.id_agenda_entity = ae.id)))
GROUP BY final.id_agenda_entity, ae.uid, ae.id_agenda, ae.enabled, ae.is_anonymized, ae.id_appuser_created_by
       , ae.id_appuser_updated_by, ae.dt_ins, ae.dt_upd;

--
-- updated view - v_agenda_definition_specification_by_value
--
CREATE OR REPLACE VIEW v_agenda_definition_specification_by_value AS
SELECT ad.id_agenda,
       att.name       AS name_definition,
       ad.is_not_null AS not_null_definition,
       adsbv.is_hidden,
       adsbv.is_not_null,
       adsbv.recurring_sequence,
       att_control.name,
       adsbv.value_control
FROM agenda_definition_specification_by_value adsbv
         JOIN mv_agenda_definition ad ON adsbv.id_agenda_definition = ad.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN mv_agenda_definition ad_control ON adsbv.id_agenda_definition_control = ad_control.id
         JOIN attribute att_control ON ad_control.id_attribute = att_control.id;

--
-- update table - external_application
--
alter table external_application
    add id_appuser int; --  not null

create unique index unq_external_application_id_appuser
    on external_application (id_appuser);

alter table external_application
    add constraint fk_external_application_appuser_id
        foreign key (id_appuser) references appuser;

--
-- new materialized view - mv_api_agenda_mapping
--
CREATE MATERIALIZED VIEW mv_api_agenda_mapping AS
SELECT
    aapthpa.id
     , aapt.id AS id_template
     , aapt.name AS template_name
     , ad.id_agenda
     , aapa.name AS template_attribute_name
     , ad.id AS id_agenda_definition
     , aapam.id AS id_page_attribute_mapping
     , aadp.id_external_application
FROM api_agenda_page_template aapt
    JOIN api_agenda_page_template_has_page_attribute aapthpa ON aapt.id = aapthpa.id_api_agenda_page_template
    JOIN api_agenda_page_attribute aapa ON aapa.id = aapthpa.id_api_agenda_page_attribute
    JOIN api_agenda_page_attribute_mapping aapam ON aapthpa.id = aapam.id_api_agenda_page_template_has_page_attribute
    JOIN api_agenda_definition_publish aadp ON aadp.id = aapam.id_api_agenda_definition_publish
    JOIN mv_agenda_definition ad ON ad.id = aadp.id_agenda_definition
WHERE aapt.enabled = TRUE
  AND aapthpa.enabled = TRUE
  AND aapa.enabled = TRUE
  AND aapam.enabled = TRUE
  AND aadp.enabled = TRUE;

CREATE UNIQUE INDEX ON mv_api_agenda_mapping (id);

-- update table - agenda_has_agenda
alter table agenda_has_agenda
    add id_agenda_definition_max_count_limit int default NULL;

alter table agenda_has_agenda
    add constraint fk_agenda_has_agenda_agenda_definition_id
        foreign key (id_agenda_definition_max_count_limit) references agenda_definition;

-- #5736
drop view v_agenda_entity_title;
alter table agenda_values alter column value type varchar using value::varchar;

create view v_agenda_entity_title
            (id_agenda_entity, title, uid, id_agenda, enabled, is_anonymized, id_appuser_created_by, id_appuser_updated_by, dt_ins, dt_upd) as
SELECT final.id_agenda_entity,
       string_agg(final.value::text, ' '::text ORDER BY final.in_title_and_priority) AS title,
       ae.uid,
       ae.id_agenda,
       ae.enabled,
       ae.is_anonymized,
       ae.id_appuser_created_by,
       ae.id_appuser_updated_by,
       ae.dt_ins,
       ae.dt_upd
FROM (SELECT val.value,
             val.id_agenda_entity,
             ad.in_title_and_priority
      FROM (SELECT val_1.value,
                   val_1.id_agenda_definition,
                   val_1.id_agenda_entity
            FROM agenda_values val_1
            WHERE (val_1.id_agenda_definition IN (SELECT ad_1.id
                                                  FROM mv_agenda_definition ad_1
                                                           JOIN agenda_has_segment_definition ahsd ON ad_1.id_agenda_has_segment_definition = ahsd.id
                                                  WHERE ad_1.enabled = true
                                                    AND ahsd.enabled = true
                                                    AND ad_1.in_title_and_priority IS NOT NULL
                                                    AND ahsd.is_main = true))) val
               JOIN agenda_definition ad ON val.id_agenda_definition = ad.id
      ORDER BY ad.in_title_and_priority) final
         JOIN agenda_entity ae ON final.id_agenda_entity = ae.id
GROUP BY final.id_agenda_entity, ae.uid, ae.id_agenda, ae.enabled, ae.is_anonymized, ae.id_appuser_created_by, ae.id_appuser_updated_by, ae.dt_ins,
         ae.dt_upd;


update configuration_values set value = '2.4.0.0'
where id_configuration_definition = (
    SELECT ID FROM configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.4.0.0
-- SQL for this version ends here
----------------------------------------------------

-- new reporting
-- alter table report alter column sql_query type text using sql_query::text;
--
-- create table element_message
-- (
--     id      serial                              not null
--         constraint pk_element_message
--             primary key,
--     name    varchar(200)
--         constraint unq_element_message
--             unique,
--     text    varchar(400)                        not null,
--     msg_type varchar(30)                        not null,
--     enabled boolean   default true              not null,
--     dt_ins  timestamp default CURRENT_TIMESTAMP not null,
--     dt_upd  timestamp default CURRENT_TIMESTAMP not null
-- );
--
-- alter table element_message owner to flexii;
--
-- create table element_hr
-- (
--     id      serial                              not null
--         constraint pk_element_hr
--             primary key,
--     name    varchar(200)
--         constraint unq_element_hr
--             unique,
--     enabled boolean   default true              not null,
--     dt_ins  timestamp default CURRENT_TIMESTAMP not null,
--     dt_upd  timestamp default CURRENT_TIMESTAMP not null
-- );

-- alter table element_hr owner to flexii;
--
-- alter table report_has_element drop constraint report_has_element_element_type_check;
-- alter table report_has_element add constraint report_has_element_element_type_check CHECK (((element_type)::text = ANY ((ARRAY['graph'::character varying, 'header'::character varying, 'table'::character varying, 'hr'::character varying, 'message'::character varying, 'input'::character varying])::text[])));
--
-- alter table element_input add replacement_tag varchar(200) not null;
-- alter table element_input add is_required bool default false;
--
-- alter table element_input add constraint element_input_data_type_check CHECK (((type)::text = ANY ((ARRAY['string'::character varying, 'int'::character varying, 'date'::character varying])::text[])));
--
-- alter table element_table alter column sql_query type text using sql_query::text;
-- alter table element_graph alter column sql_query type text using sql_query::text;
--
-- alter table element_input add "default_value" varchar(200);
--
-- alter table element_graph add cols_before int default 0;
-- alter table element_graph add cols_element int default 0;
-- alter table element_graph add cols_after int default 0;
--
-- alter table element_table add cols_before int default 0;
-- alter table element_table add cols_element int default 0;
-- alter table element_table add cols_after int default 0;
--
-- alter table element_header add cols_before int default 0;
-- alter table element_header add cols_element int default 0;
-- alter table element_header add cols_after int default 0;
--
-- alter table element_message add cols_before int default 0;
-- alter table element_message add cols_element int default 0;
-- alter table element_message add cols_after int default 0;

-- #11369
ALTER TABLE lookup_country ADD description varchar(200);
UPDATE lookup_country SET description = name WHERE 1 = 1;

ALTER TABLE lookup_currency ADD description varchar(200);
UPDATE lookup_currency SET description = name WHERE 1 = 1;

ALTER TABLE lookup_language ADD description varchar(200);
UPDATE lookup_language SET description = name WHERE 1 = 1;

CREATE TYPE flexii.value_with_lookup AS (value varchar, description varchar);

update configuration_values set value = '2.4.1.0'
where id_configuration_definition = (
    SELECT ID FROM configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.4.1.0
-- SQL for this version ends here
----------------------------------------------------

-- base
CREATE SCHEMA base;
ALTER SCHEMA base OWNER TO flexii;

ALTER TABLE appuser SET SCHEMA base;
ALTER TABLE appuser_session SET SCHEMA base;
ALTER TABLE locale SET SCHEMA base;
ALTER TABLE log_api_request SET SCHEMA base;
ALTER TABLE log_api_response SET SCHEMA base;
ALTER TABLE log_operation SET SCHEMA base;
ALTER TABLE user_group SET SCHEMA base;
ALTER TABLE user_group_has_appuser SET SCHEMA base;

ALTER FUNCTION update_dt_upd() SET SCHEMA base;

-- configuration
CREATE SCHEMA configuration;
ALTER SCHEMA configuration OWNER TO flexii;

ALTER TABLE configuration_definition SET SCHEMA configuration;
ALTER TABLE configuration_group SET SCHEMA configuration;
ALTER TABLE configuration_key SET SCHEMA configuration;
ALTER TABLE configuration_values SET SCHEMA configuration;

CREATE SCHEMA log;
ALTER SCHEMA log OWNER TO flexii;

ALTER TABLE log_error SET SCHEMA log;
ALTER TABLE log_exception SET SCHEMA log;
ALTER TABLE lookup_error_action SET SCHEMA log;

------ #11222
-- flexii.agenda_has_agenda_control_relation
CREATE TABLE flexii.agenda_has_agenda_control_relation
(
    id                   serial
        CONSTRAINT pk_agenda_has_agenda_control_relation
            PRIMARY KEY,
    id_agenda_has_agenda int                                 NOT NULL
        CONSTRAINT fk_agenda_has_agenda_control_relation_agenda_has_agenda_id
            REFERENCES agenda_has_agenda,
    id_agenda_definition int                                 NOT NULL
        CONSTRAINT fk_agenda_has_agenda_control_relation_agenda_definition_id
            REFERENCES agenda_definition,
    value_control        varchar(100),
    operator             varchar(3)                          NOT NULL,
    enabled              bool      DEFAULT TRUE,
    dt_ins               timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd               timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER tr_agenda_has_agenda_control_relation_dt_upd
    BEFORE UPDATE
    ON flexii.agenda_has_agenda_control_relation
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

-- #11223
ALTER TABLE flexii.agenda_has_agenda ADD is_list_static_agenda boolean DEFAULT FALSE NOT NULL;
ALTER TABLE flexii.agenda_has_agenda ADD is_list_static_agenda_linked bool DEFAULT FALSE NOT NULL;

-- #11506
alter table flexii.email_definition add send_if_not_empty boolean default true not null;


update configuration.configuration_values set value = '2.5.0.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.0.0
-- SQL for this version ends here
----------------------------------------------------


update configuration.configuration_values set value = '2.5.1.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.1.0
-- SQL for this version ends here
----------------------------------------------------


update configuration.configuration_values set value = '2.5.2.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.2.0
-- SQL for this version ends here
----------------------------------------------------

update configuration.configuration_values set value = '2.5.2.1'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.2.1
-- SQL for this version ends here
----------------------------------------------------

update configuration.configuration_values set value = '2.5.3.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.3.0
-- SQL for this version ends here
----------------------------------------------------

update configuration.configuration_values set value = '2.5.4.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.4.0
-- SQL for this version ends here
----------------------------------------------------

CREATE OR REPLACE VIEW v_agenda_has_agenda_to_agenda_has_agenda
        (id, id_agenda_has_agenda, id_agenda_has_agenda_linked, view_direction, name, is_custom_name, is_show_disabled_entity, priority,
         id_agenda, id_agenda_connection, id_agenda_linked)
AS
SELECT id,
       id_agenda_has_agenda,
       id_agenda_has_agenda_linked,
       view_direction,
       name,
       is_custom_name,
       is_show_disabled_entity,
       priority,
       id_agenda,
       id_agenda_connection,
       CASE
           WHEN id_agenda_first != id_agenda AND id_agenda_first != id_agenda_connection                 THEN id_agenda_first
           WHEN id_agenda_linked_first != id_agenda AND id_agenda_linked_first != id_agenda_connection   THEN id_agenda_linked_first
           WHEN id_agenda_second != id_agenda AND id_agenda_second != id_agenda_connection               THEN id_agenda_second
           WHEN id_agenda_linked_second != id_agenda AND id_agenda_linked_second != id_agenda_connection THEN id_agenda_linked_second
                                                                                                         ELSE NULL::smallint
       END AS id_agenda_linked
FROM (
         SELECT def.id,
                def.id_agenda_has_agenda,
                def.id_agenda_has_agenda_linked,
                def.view_direction,
                def.name,
                def.is_custom_name,
                def.is_show_disabled_entity,
                def.priority,
                CASE
                    WHEN def.id_agenda_first <> def.id_agenda_second AND def.id_agenda_first < def.id_agenda_second THEN def.id_agenda_first
                    WHEN def.id_agenda_first <> def.id_agenda_linked_second AND def.id_agenda_first < def.id_agenda_linked_second
                                                                                                                    THEN def.id_agenda_first
                    WHEN def.id_agenda_linked_first <> def.id_agenda_second AND def.id_agenda_linked_first < def.id_agenda_second
                                                                                                                    THEN def.id_agenda_linked_first
                    WHEN def.id_agenda_linked_first <> def.id_agenda_linked_second AND def.id_agenda_linked_first < def.id_agenda_linked_second
                                                                                                                    THEN def.id_agenda_linked_first
                                                                                                                    ELSE NULL::smallint
                END AS id_agenda,
                CASE
                    WHEN def.id_agenda_first = def.id_agenda_second               THEN def.id_agenda_first
                    WHEN def.id_agenda_first = def.id_agenda_linked_second        THEN def.id_agenda_first
                    WHEN def.id_agenda_linked_first = def.id_agenda_second        THEN def.id_agenda_linked_first
                    WHEN def.id_agenda_linked_first = def.id_agenda_linked_second THEN def.id_agenda_linked_first
                                                                                  ELSE NULL::smallint
                END AS id_agenda_connection,
                id_agenda_first,
                id_agenda_linked_first,
                id_agenda_second,
                id_agenda_linked_second
         FROM (SELECT ahataha.id,
                      ahataha.id_agenda_has_agenda,
                      ahataha.id_agenda_has_agenda_linked,
                      ahataha.view_direction,
                      ahataha.name,
                      ahataha.is_custom_name,
                      ahataha.is_show_disabled_entity,
                      ahataha.priority,
                      aha.id_agenda               AS id_agenda_first,
                      aha.id_agenda_linked        AS id_agenda_linked_first,
                      aha_linked.id_agenda        AS id_agenda_second,
                      aha_linked.id_agenda_linked AS id_agenda_linked_second
               FROM (SELECT ahataha_1.id,
                            ahataha_1.id_agenda_has_agenda,
                            ahataha_1.id_agenda_has_agenda_linked,
                            ahataha_1.view_direction,
                            ahataha_1.name,
                            ahataha_1.is_custom_name,
                            ahataha_1.is_show_disabled_entity,
                            ahataha_1.priority
                     FROM agenda_has_agenda_to_agenda_has_agenda ahataha_1
                     WHERE ahataha_1.enabled = TRUE) ahataha
                        JOIN agenda_has_agenda aha ON ahataha.id_agenda_has_agenda = aha.id
                        JOIN agenda_has_agenda aha_linked ON ahataha.id_agenda_has_agenda_linked = aha_linked.id
               WHERE aha.enabled = TRUE
                 AND aha_linked.enabled = TRUE) def) fin;


update configuration.configuration_values set value = '2.5.5.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.5.0
-- SQL for this version ends here
----------------------------------------------------

update configuration.configuration_values set value = '2.5.6.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.6.0
-- SQL for this version ends here
----------------------------------------------------

-- #11737
alter table email_definition drop constraint fk_email_definition_report;
alter table email_definition add constraint fk_email_definition_report foreign key (id_report) references report on update restrict on delete restrict;

ALTER TABLE agenda_definition ADD is_hidden bool DEFAULT FALSE NOT NULL;

DROP MATERIALIZED VIEW mv_api_agenda_mapping;
DROP VIEW v_agenda_entity_title;
DROP VIEW v_agenda_definition_specification_by_value;
DROP MATERIALIZED VIEW mv_agenda_definition;

CREATE MATERIALIZED VIEW mv_agenda_definition AS
SELECT ad.id
     , ahsd.id_agenda
     , ahsd.id_agenda_segment
     , ahshagd.id_agenda_attribute_group
     , ad.is_control_for_group
     , ad.id_attribute
     , ad.priority
     , ad.is_not_null
     , ad.is_hidden
     , ad.default_value
     , ad.is_disabled
     , ad.is_read_only
     , ad.tooltip
     , ad.field_column_length
     , ad.skip_column_length
     , ad.field_column_height
     , ad.in_title_and_priority
     , ad.is_in_datagrid
     , ad.is_editable_in_datagrid
     , ad.is_hidden_in_datagrid
     , ad.datagrid_order_priority
     , ad.datagrid_order_by
     , ad.is_search_by
     , ad.is_show_in_linked_agenda
     , ad.is_unique
     , ad.min_length
     , ad.max_length
     , ad.min_value
     , ad.max_value
     , ad.pattern
     , ad.custom_validation
     , ad.is_in
     , ad.is_not_in
     , ad.is_importable
     , ad.id_agenda_definition_depended
     , ad.enabled
     , ad.dt_ins
     , ad.dt_upd
     , ad.id_agenda_segment_attribute_group_definition
     , ahshagd.id_agenda_has_segment_definition
     , a.name AS agenda_name
     , aseg.name AS segment_name
     , aag.name AS attribute_group_name
     , att.name AS attribute_name
     , attdt.name AS data_type
FROM agenda_definition ad
         JOIN agenda_has_segment_has_attribute_group_definition ahshagd
              ON ad.id_agenda_segment_attribute_group_definition = ahshagd.id
         JOIN agenda_has_segment_definition ahsd ON ahshagd.id_agenda_has_segment_definition = ahsd.id
         JOIN agenda a ON ahsd.id_agenda = a.id
         JOIN agenda_segment aseg ON ahsd.id_agenda_segment = aseg.id
         JOIN agenda_attribute_group aag ON ahshagd.id_agenda_attribute_group = aag.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN attribute_data_type attdt ON att.id_attribute_data_type = attdt.id
WHERE ad.enabled = TRUE
  AND ahshagd.enabled = TRUE
  AND ahsd.enabled = TRUE
  AND a.enabled = TRUE
  AND aseg.enabled = TRUE
  AND aag.enabled = TRUE
  AND att.enabled = TRUE
  AND attdt.enabled = TRUE;

CREATE UNIQUE INDEX ON mv_agenda_definition (id);

CREATE MATERIALIZED VIEW mv_api_agenda_mapping AS
SELECT
    aapthpa.id
     , aapt.id AS id_template
     , aapt.name AS template_name
     , ad.id_agenda
     , aapa.name AS template_attribute_name
     , ad.id AS id_agenda_definition
     , aapam.id AS id_page_attribute_mapping
     , aadp.id_external_application
FROM api_agenda_page_template aapt
         JOIN api_agenda_page_template_has_page_attribute aapthpa ON aapt.id = aapthpa.id_api_agenda_page_template
         JOIN api_agenda_page_attribute aapa ON aapa.id = aapthpa.id_api_agenda_page_attribute
         JOIN api_agenda_page_attribute_mapping aapam ON aapthpa.id = aapam.id_api_agenda_page_template_has_page_attribute
         JOIN api_agenda_definition_publish aadp ON aadp.id = aapam.id_api_agenda_definition_publish
         JOIN mv_agenda_definition ad ON ad.id = aadp.id_agenda_definition
WHERE aapt.enabled = TRUE
  AND aapthpa.enabled = TRUE
  AND aapa.enabled = TRUE
  AND aapam.enabled = TRUE
  AND aadp.enabled = TRUE;

create OR REPLACE view v_agenda_entity_title
        (id_agenda_entity, title, uid, id_agenda, enabled, is_anonymized, id_appuser_created_by, id_appuser_updated_by, dt_ins, dt_upd) as
SELECT final.id_agenda_entity,
       string_agg(final.value::text, ' '::text ORDER BY final.in_title_and_priority) AS title,
       ae.uid,
       ae.id_agenda,
       ae.enabled,
       ae.is_anonymized,
       ae.id_appuser_created_by,
       ae.id_appuser_updated_by,
       ae.dt_ins,
       ae.dt_upd
FROM (SELECT val.value,
             val.id_agenda_entity,
             ad.in_title_and_priority
      FROM (SELECT val_1.value,
                   val_1.id_agenda_definition,
                   val_1.id_agenda_entity
            FROM agenda_values val_1
            WHERE (val_1.id_agenda_definition IN (SELECT ad_1.id
                                                  FROM mv_agenda_definition ad_1
                                                           JOIN agenda_has_segment_definition ahsd ON ad_1.id_agenda_has_segment_definition = ahsd.id
                                                  WHERE ad_1.enabled = true
                                                    AND ahsd.enabled = true
                                                    AND ad_1.in_title_and_priority IS NOT NULL
                                                    AND ahsd.is_main = true))) val
               JOIN agenda_definition ad ON val.id_agenda_definition = ad.id
      ORDER BY ad.in_title_and_priority) final
         JOIN agenda_entity ae ON final.id_agenda_entity = ae.id
GROUP BY final.id_agenda_entity, ae.uid, ae.id_agenda, ae.enabled, ae.is_anonymized, ae.id_appuser_created_by, ae.id_appuser_updated_by, ae.dt_ins,
         ae.dt_upd;

CREATE OR REPLACE VIEW v_agenda_definition_specification_by_value AS
SELECT ad.id_agenda,
       att.name       AS name_definition,
       ad.is_not_null AS not_null_definition,
       adsbv.is_hidden,
       adsbv.is_not_null,
       adsbv.recurring_sequence,
       att_control.name,
       adsbv.value_control
FROM agenda_definition_specification_by_value adsbv
         JOIN mv_agenda_definition ad ON adsbv.id_agenda_definition = ad.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN mv_agenda_definition ad_control ON adsbv.id_agenda_definition_control = ad_control.id
         JOIN attribute att_control ON ad_control.id_attribute = att_control.id;



update configuration.configuration_values set value = '2.5.6.1'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.6.1
-- SQL for this version ends here
----------------------------------------------------

update configuration.configuration_values set value = '2.5.6.2'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.6.2
-- SQL for this version ends here
----------------------------------------------------

update configuration.configuration_values set value = '2.5.6.3'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.6.3
-- SQL for this version ends here
----------------------------------------------------

update configuration.configuration_values set value = '2.5.7.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.7.0
-- SQL for this version ends here
----------------------------------------------------

update configuration.configuration_values set value = '2.5.7.1'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.7.1
-- SQL for this version ends here
----------------------------------------------------

CREATE TABLE flexii.log_external_source
(
    id         serial
        CONSTRAINT pk_log_external_source
            PRIMARY KEY,
    action     varchar(100)                            NOT NULL,
    file       varchar(200)  DEFAULT NULL,
    dt_start   timestamp     DEFAULT NULL,
    dt_end     timestamp     DEFAULT NULL,
    is_success bool          DEFAULT NULL,
    message    varchar(1000) DEFAULT NULL,
    dt_ins     timestamp     DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd     timestamp     DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER tr_log_external_source_dt_upd
    BEFORE UPDATE
    ON flexii.log_external_source
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

CREATE TABLE flexii.log_external_source_row
(
    id                     bigserial
        CONSTRAINT pk_log_external_source_row
            PRIMARY KEY,
    id_log_external_source int                                     NOT NULL
        CONSTRAINT fk_log_external_source_row_log_external_source_id
            REFERENCES flexii.log_external_source,
    data                   text                                    NOT NULL,
    dt_start               timestamp     DEFAULT NULL,
    dt_end                 timestamp     DEFAULT NULL,
    is_success             bool          DEFAULT NULL,
    message                varchar(1000) DEFAULT NULL,
    dt_ins                 timestamp     DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd                 timestamp     DEFAULT CURRENT_TIMESTAMP NOT NULL
);


CREATE TRIGGER tr_log_external_source_row_dt_upd
    BEFORE UPDATE
    ON flexii.log_external_source_row
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();


CREATE TABLE flexii.agenda_overview_button
(
    id          serial
        CONSTRAINT pk_agenda_overview_button
            PRIMARY KEY,
    id_agenda   int                                    NOT NULL
        CONSTRAINT fk_agenda_button_agenda_id
            REFERENCES agenda,
    name        varchar(10)                            NOT NULL,
    description varchar(20)                            NOT NULL,
    icon        varchar(20)                            NOT NULL,
    presenter   varchar(50)                            NOT NULL,
    action      varchar(50)                            NOT NULL,
    parameters  varchar(400) DEFAULT NULL,
    enabled     bool         DEFAULT TRUE              NOT NULL,
    dt_ins      timestamp    DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd      timestamp    DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE UNIQUE INDEX unq_agenda_overview_button_name
    ON flexii.agenda_overview_button (name);

CREATE TRIGGER tr_agenda_overview_button_dt_upd
    BEFORE UPDATE
    ON flexii.agenda_overview_button
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

CREATE TABLE flexii.agenda_has_external_source
(
    id          serial
        CONSTRAINT pk_agenda_has_external_source
            PRIMARY KEY,
    id_agenda   int                                    NOT NULL
        CONSTRAINT fk_agenda_has_external_source_id
            REFERENCES agenda,
    name        varchar(64)                            NOT NULL,
    enabled     bool         DEFAULT TRUE              NOT NULL,
    dt_ins      timestamp    DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd      timestamp    DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE UNIQUE INDEX unq_agenda_has_external_source_agenda ON flexii.agenda_has_external_source (id_agenda);
CREATE UNIQUE INDEX unq_agenda_has_external_source_name ON flexii.agenda_has_external_source (name);

CREATE TRIGGER tr_agenda_has_external_source_dt_upd
    BEFORE UPDATE
    ON flexii.agenda_has_external_source
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

update configuration.configuration_values set value = '2.5.8.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.8.0
-- SQL for this version ends here
----------------------------------------------------


update configuration.configuration_values set value = '2.5.8.1'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.8.1
-- SQL for this version ends here
----------------------------------------------------


update configuration.configuration_values set value = '2.5.8.2'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.8.2
-- SQL for this version ends here
----------------------------------------------------

update configuration.configuration_values set value = '2.5.8.3'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.8.3
-- SQL for this version ends here
----------------------------------------------------

insert into base.user_group (name, description, enabled)
    select 'reporting', 'Uživatelská skupina pro oprávnění zobrazit modul reportingu', true
where not exists(
    select id from base.user_group where name = 'reporting'
);



------------------------------------------------------------------


update configuration.configuration_values set value = '2.5.9.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.9.0
-- SQL for this version ends here
----------------------------------------------------

-- #12139
update flexii.menu_link set name = 'system_process', action = 'systemProcess' where name = 'rebuild_agenda_views';
update flexii.menu_item set name = 'system_process', icon = 'fad fa-microchip' where name = 'rebuild_agenda_views';

-- version
update configuration.configuration_values set value = '2.5.9.1'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.9.1
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.5.9.2'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.9.2
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.5.9.3'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.9.3
-- SQL for this version ends here
----------------------------------------------------
-- #11667
ALTER TABLE agenda_has_agenda_definition ADD is_editable_in_datagrid bool NOT NULL DEFAULT false;

-- #12103
-- V tabulce "agenda" vytvorit nove sloupce "is_dt_ins_in_datagrid" a "is_dt_upd_in_datagrid" typu bool s defaultem false
ALTER TABLE agenda ADD is_dt_ins_in_datagrid bool NOT NULL DEFAULT false;
ALTER TABLE agenda ADD is_dt_upd_in_datagrid bool NOT NULL DEFAULT false;

-- #12165 Přehledy v jednotlivých agendách - odstranit křížek na smazání entity (zakomentovat, nebo dát na parametr v konfiguraci)
ALTER TABLE agenda ADD is_deletable_in_datagrid bool NOT NULL DEFAULT true;

-- version
update configuration.configuration_values set value = '2.5.10.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.10.0
-- SQL for this version ends here
----------------------------------------------------

insert into attribute_data_type (name, priority) VALUES ('money', 270);
insert into attribute_data_type (name, priority) VALUES ('multi_agenda', 260);

-- kalendar defaultni zobrazeni
ALTER TABLE flexii.calendar ADD default_view varchar(100) NOT NULL DEFAULT 'week',
    ADD CONSTRAINT chck_calendar_default_view CHECK (default_view IN ('day','week','month'))
;

ALTER TABLE flexii.calendar ADD is_without_hours bool NOT NULL DEFAULT false;

-- version
update configuration.configuration_values set value = '2.5.11.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.11.0
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.5.11.1'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.11.1
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.5.11.2'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.11.2
-- SQL for this version ends here
----------------------------------------------------

--#12339
alter table calendar_attribute
    add constraint unq_calendar_attribute_name
        UNIQUE(name);

-- #12163 administrace - nová funkce - Permanentní smazání entit
INSERT INTO menu_link (name, presenter, action, parameters) VALUES('permanent_ae_delete', 'Agenda:Admin', 'deleteAgendaEntity', NULL);

INSERT INTO menu_item (id_menu_section, id_agenda, "name", icon, id_menu_link, priority, is_default, is_separator_before)
VALUES((SELECT id FROM menu_section WHERE name = 'administration'), NULL, 'permanent_ae_delete', 'fad fa-trash', (SELECT id FROM menu_link WHERE name = 'permanent_ae_delete'), 130, false, false);

CREATE TABLE flexii.log_deleted_entity
(
    id         serial
        CONSTRAINT pk_log_deleted_entity
            PRIMARY KEY,
            agenda varchar(400) NOT NULL,
            id_agenda_entity integer,
            uid_agenda_entity integer,
            title varchar(400) NOT NULL,
    id_appuser_created_by integer,
    dt_ins     timestamp     DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER tr_log_deleted_entity_dt_upd
    BEFORE UPDATE
    ON flexii.log_deleted_entity
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

--#12320
ALTER TABLE flexii.calendar_has_attribute ADD priority smallint NOT NULL DEFAULT 0
;

-- version
update configuration.configuration_values set value = '2.5.12.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.12.0
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.5.12.1'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.12.1
-- SQL for this version ends here
----------------------------------------------------

--
--
-- -- #12312
-- create table if not exists reporting.element_table_has_column
-- (
--     id                  serial                              not null constraint pk_element_table_has_column primary key,
--     id_element_table    integer                             not null constraint fk_element_table_has_column_element_table references reporting.element_table,
--     name                varchar(200)                        not null,
--     description         varchar(200)                        not null,
--     type                varchar(200)
--         constraint element_table_has_column_data_type_check
--             check ((type)::text = ANY
--                    (ARRAY [
--                        ('string'::character varying)::text
--                        , ('bool'::character varying)::text
--                        , ('int'::character varying)::text
--                        , ('float'::character varying)::text
--                        , ('date'::character varying)::text
--                        , ('datetime'::character varying)::text
--                        ])),
--     priority            integer                             not null,
--     sort                varchar(200)
--         constraint element_table_has_column_sort_check
--             check ((sort)::text = ANY
--                    (ARRAY [
--                        ('default'::character varying)::text
--                        , ('asc'::character varying)::text
--                        , ('desc'::character varying)::text
--                        ])),
--     enabled             boolean   default true              not null,
--     dt_ins              timestamp default CURRENT_TIMESTAMP not null,
--     dt_upd              timestamp default CURRENT_TIMESTAMP not null
-- );
--
-- -- #12238
-- create table if not exists reporting.group
-- (
--     id                    serial                              not null
--         constraint pk_reporting_group
--             primary key,
--     name                  varchar(200)                        not null
--         constraint unq_reporting_group
--             unique,
--     description           varchar(400),
--     priority              integer                             not null,
--     require               boolean   default false             not null,
--     enabled               boolean   default true              not null,
--     dt_ins                timestamp default CURRENT_TIMESTAMP not null,
--     dt_upd                timestamp default CURRENT_TIMESTAMP not null
-- );
--
-- alter table reporting.group add column if not exists require boolean default false;
--
-- create table if not exists reporting.group_has_user_group
-- (
--     id                    serial                              not null
--         constraint pk_group_has_user_group
--             primary key,
--     id_group              integer                             not null
--         constraint fk_report_group_has_user_group_group
--             references reporting.group,
--     id_user_group              integer                             not null
--         constraint fk_report_group_has_user_group_user_group
--             references base.user_group,
--     enabled               boolean   default true              not null,
--     dt_ins                timestamp default CURRENT_TIMESTAMP not null,
--     dt_upd                timestamp default CURRENT_TIMESTAMP not null,
--     constraint unq_group_has_user_group
--         unique (id_group, id_user_group)
-- );
--
-- create table if not exists reporting.report_has_group
-- (
--     id                    serial                              not null
--         constraint pk_report_has_group
--             primary key,
--     id_report              integer                             not null
--         constraint fk_report_has_group_report
--             references reporting.report,
--     id_group              integer                             not null
--         constraint fk_report_has_group_group
--             references base.user_group,
--     enabled               boolean   default true              not null,
--     dt_ins                timestamp default CURRENT_TIMESTAMP not null,
--     dt_upd                timestamp default CURRENT_TIMESTAMP not null,
--     constraint unq_report_has_group
--         unique (id_report, id_group)
-- );
--
-- alter table reporting.report add column if not exists in_group bool default false;
-- alter table reporting.report add column if not exists id_group int default null;
-- update reporting.report set in_group = true where id_group is not null;
--
-- insert into reporting.report_has_group (id_report, id_group, enabled)
-- select r.id, r.id_group, r.enabled
-- from reporting.report r
-- where r.id_group is not null
-- and not exists(
--     select h.id from reporting.report_has_group h where h.id_report = r.id and h.id_group = r.id_group
-- );
--
-- alter table reporting.report drop column if exists id_group;

-- version
update configuration.configuration_values set value = '2.5.12.2'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.12.2
-- SQL for this version ends here
----------------------------------------------------


-- #12617
-- alter table reporting.element_table_has_column add column if not exists order_priority int default 0;


-- version
update configuration.configuration_values set value = '2.5.13.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.13.0
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.5.13.1'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.13.1
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.5.13.2'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.13.2
-- SQL for this version ends here
----------------------------------------------------

alter table base.APPUSER rename column PASSWORD to PSSWD;
ALTER TABLE base.LOG_API_REQUEST RENAME COLUMN ID_AGENDA_ENTITY TO ID_ENTITY;
ALTER TABLE log.log_exception RENAME COLUMN FILE TO filepath;

-- alter table reporting.element_table_has_column add if not exists order_priority int default 0;

--- 12733 + 12734
CREATE TABLE flexii.agenda_button
(
    id          serial
        CONSTRAINT pk_agenda_button
            PRIMARY KEY,
    id_agenda   integer
        CONSTRAINT fk_agenda_button_agenda_id
            REFERENCES flexii.agenda,
    name        varchar(100)                        NOT NULL
        CONSTRAINT unq_agenda_button_name
            UNIQUE,
    description varchar(100),
    icon        varchar(150),
    class       varchar(200),
    enabled     bool      DEFAULT TRUE              NOT NULL,
    dt_ins      timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd      timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER tr_agenda_button_dt_upd
    BEFORE UPDATE
    ON flexii.agenda_button
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

CREATE TABLE flexii.agenda_action
(
    id            serial
        CONSTRAINT pk_agenda_action
            PRIMARY KEY,
    name          varchar(100)                        NOT NULL
        CONSTRAINT unq_agenda_action_name
            UNIQUE,
    description   varchar(100),
    class_name    varchar(200),
    is_via_rabbit boolean   DEFAULT FALSE             NOT NULL,
    enabled       bool      DEFAULT TRUE              NOT NULL,
    dt_ins        timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd        timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER tr_agenda_action_dt_upd
    BEFORE UPDATE
    ON flexii.agenda_action
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

CREATE TABLE flexii.agenda_button_has_action
(
    id                   serial
        CONSTRAINT pk_agenda_button_has_action
            PRIMARY KEY,
    id_agenda_button integer                             NOT NULL
        CONSTRAINT fk_agenda_button_id
            REFERENCES flexii.agenda_button,
    id_agenda_action     integer                             NOT NULL
        CONSTRAINT fk_agenda_action_id
            REFERENCES flexii.agenda_action,
    priority             smallint                            NOT NULL,
    enabled              bool      DEFAULT TRUE              NOT NULL,
    dt_ins               timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd               timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

create unique index unq_agenda_button_has_action_main
    on agenda_button_has_action (id_agenda_button desc, id_agenda_action asc, priority asc);

CREATE TRIGGER tr_agenda_button_has_action_dt_upd
    BEFORE UPDATE
    ON flexii.agenda_button_has_action
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

alter table agenda_entity add is_locked bool default FALSE not null;

-- LOG upgrade to version 5.1.0
ALTER TABLE log.log_error RENAME TO lg_log_error;
ALTER TABLE log.log_exception RENAME TO lg_log_exception;
ALTER TABLE log.lookup_error_action RENAME TO lg_lookup_error_action;

ALTER TABLE log.lg_log_error ALTER COLUMN message TYPE varchar(2000);
ALTER TABLE log.lg_log_error ALTER COLUMN data TYPE text;
ALTER TABLE log.lg_log_error DROP COLUMN id_external_application;

ALTER TABLE log.lg_log_exception ALTER COLUMN message TYPE varchar(2000);

ALTER TABLE log.lg_lookup_error_action ALTER COLUMN name TYPE varchar(50);
ALTER TABLE log.lg_lookup_error_action ALTER COLUMN description TYPE varchar(400);


CREATE TABLE log.lg_lookup_logger_level
(
    id integer GENERATED ALWAYS AS IDENTITY
        CONSTRAINT pk_lg_lookup_logger_level
            PRIMARY KEY,
    name     varchar(50) NOT NULL,
    priority smallint
);

INSERT INTO log.lg_lookup_logger_level (name, priority) VALUES ('emergency', 1);
INSERT INTO log.lg_lookup_logger_level (name, priority) VALUES ('alert', 2);
INSERT INTO log.lg_lookup_logger_level (name, priority) VALUES ('critical', 3);
INSERT INTO log.lg_lookup_logger_level (name, priority) VALUES ('error', 4);
INSERT INTO log.lg_lookup_logger_level (name, priority) VALUES ('warning', 5);
INSERT INTO log.lg_lookup_logger_level (name, priority) VALUES ('notice', 6);
INSERT INTO log.lg_lookup_logger_level (name, priority) VALUES ('info', 7);
INSERT INTO log.lg_lookup_logger_level (name, priority) VALUES ('debug', 8);

CREATE TABLE log.lg_logger
(
    id                 integer GENERATED ALWAYS AS IDENTITY
        CONSTRAINT pk_lg_logger
            PRIMARY KEY,
    id_lg_lookup_level smallint                NOT NULL
        CONSTRAINT fk_lg_logger_lg_lookup_logger_level
            REFERENCES lg_lookup_logger_level
            ON UPDATE RESTRICT ON DELETE RESTRICT,
    dt                 timestamp DEFAULT NOW() NOT NULL,
    message            varchar(500)            NOT NULL,
    context            text,
    id_user            integer,
    entity_type        varchar(100),
    id_entity          integer
);
------------------------------------------

-- #13117
-- alter table reporting.element_table_has_column add if not exists text_wrapping boolean default true;

-- #12043
alter table flexii.agenda_definition add if not exists file_specification varchar(400) default null;

DROP MATERIALIZED VIEW mv_api_agenda_mapping;
DROP VIEW v_agenda_entity_title;
DROP VIEW v_agenda_definition_specification_by_value;
DROP MATERIALIZED VIEW mv_agenda_definition;

CREATE MATERIALIZED VIEW mv_agenda_definition AS
SELECT ad.id
     , ahsd.id_agenda
     , ahsd.id_agenda_segment
     , ahshagd.id_agenda_attribute_group
     , ad.is_control_for_group
     , ad.id_attribute
     , ad.priority
     , ad.is_not_null
     , ad.is_hidden
     , ad.default_value
     , ad.is_disabled
     , ad.is_read_only
     , ad.tooltip
     , ad.field_column_length
     , ad.skip_column_length
     , ad.field_column_height
     , ad.in_title_and_priority
     , ad.is_in_datagrid
     , ad.is_editable_in_datagrid
     , ad.is_hidden_in_datagrid
     , ad.datagrid_order_priority
     , ad.datagrid_order_by
     , ad.is_search_by
     , ad.is_show_in_linked_agenda
     , ad.is_unique
     , ad.min_length
     , ad.max_length
     , ad.min_value
     , ad.max_value
     , ad.pattern
     , ad.custom_validation
     , ad.is_in
     , ad.is_not_in
     , ad.is_importable
     , ad.file_specification
     , ad.id_agenda_definition_depended
     , ad.enabled
     , ad.dt_ins
     , ad.dt_upd
     , ad.id_agenda_segment_attribute_group_definition
     , ahshagd.id_agenda_has_segment_definition
     , a.name AS agenda_name
     , aseg.name AS segment_name
     , aag.name AS attribute_group_name
     , att.name AS attribute_name
     , attdt.name AS data_type
FROM agenda_definition ad
         JOIN agenda_has_segment_has_attribute_group_definition ahshagd
              ON ad.id_agenda_segment_attribute_group_definition = ahshagd.id
         JOIN agenda_has_segment_definition ahsd ON ahshagd.id_agenda_has_segment_definition = ahsd.id
         JOIN agenda a ON ahsd.id_agenda = a.id
         JOIN agenda_segment aseg ON ahsd.id_agenda_segment = aseg.id
         JOIN agenda_attribute_group aag ON ahshagd.id_agenda_attribute_group = aag.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN attribute_data_type attdt ON att.id_attribute_data_type = attdt.id
WHERE ad.enabled = TRUE
  AND ahshagd.enabled = TRUE
  AND ahsd.enabled = TRUE
  AND a.enabled = TRUE
  AND aseg.enabled = TRUE
  AND aag.enabled = TRUE
  AND att.enabled = TRUE
  AND attdt.enabled = TRUE;

CREATE UNIQUE INDEX ON mv_agenda_definition (id);

CREATE MATERIALIZED VIEW mv_api_agenda_mapping AS
SELECT
    aapthpa.id
     , aapt.id AS id_template
     , aapt.name AS template_name
     , ad.id_agenda
     , aapa.name AS template_attribute_name
     , ad.id AS id_agenda_definition
     , aapam.id AS id_page_attribute_mapping
     , aadp.id_external_application
FROM api_agenda_page_template aapt
         JOIN api_agenda_page_template_has_page_attribute aapthpa ON aapt.id = aapthpa.id_api_agenda_page_template
         JOIN api_agenda_page_attribute aapa ON aapa.id = aapthpa.id_api_agenda_page_attribute
         JOIN api_agenda_page_attribute_mapping aapam ON aapthpa.id = aapam.id_api_agenda_page_template_has_page_attribute
         JOIN api_agenda_definition_publish aadp ON aadp.id = aapam.id_api_agenda_definition_publish
         JOIN mv_agenda_definition ad ON ad.id = aadp.id_agenda_definition
WHERE aapt.enabled = TRUE
  AND aapthpa.enabled = TRUE
  AND aapa.enabled = TRUE
  AND aapam.enabled = TRUE
  AND aadp.enabled = TRUE;

create OR REPLACE view v_agenda_entity_title
        (id_agenda_entity, title, uid, id_agenda, enabled, is_anonymized, id_appuser_created_by, id_appuser_updated_by, dt_ins, dt_upd) as
SELECT final.id_agenda_entity,
       string_agg(final.value::text, ' '::text ORDER BY final.in_title_and_priority) AS title,
       ae.uid,
       ae.id_agenda,
       ae.enabled,
       ae.is_anonymized,
       ae.id_appuser_created_by,
       ae.id_appuser_updated_by,
       ae.dt_ins,
       ae.dt_upd
FROM (SELECT val.value,
             val.id_agenda_entity,
             ad.in_title_and_priority
      FROM (SELECT val_1.value,
                   val_1.id_agenda_definition,
                   val_1.id_agenda_entity
            FROM agenda_values val_1
            WHERE (val_1.id_agenda_definition IN (SELECT ad_1.id
                                                  FROM mv_agenda_definition ad_1
                                                           JOIN agenda_has_segment_definition ahsd ON ad_1.id_agenda_has_segment_definition = ahsd.id
                                                  WHERE ad_1.enabled = true
                                                    AND ahsd.enabled = true
                                                    AND ad_1.in_title_and_priority IS NOT NULL
                                                    AND ahsd.is_main = true))) val
               JOIN agenda_definition ad ON val.id_agenda_definition = ad.id
      ORDER BY ad.in_title_and_priority) final
         JOIN agenda_entity ae ON final.id_agenda_entity = ae.id
GROUP BY final.id_agenda_entity, ae.uid, ae.id_agenda, ae.enabled, ae.is_anonymized, ae.id_appuser_created_by, ae.id_appuser_updated_by, ae.dt_ins,
         ae.dt_upd;

CREATE OR REPLACE VIEW v_agenda_definition_specification_by_value AS
SELECT ad.id_agenda,
       att.name       AS name_definition,
       ad.is_not_null AS not_null_definition,
       adsbv.is_hidden,
       adsbv.is_not_null,
       adsbv.recurring_sequence,
       att_control.name,
       adsbv.value_control
FROM agenda_definition_specification_by_value adsbv
         JOIN mv_agenda_definition ad ON adsbv.id_agenda_definition = ad.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN mv_agenda_definition ad_control ON adsbv.id_agenda_definition_control = ad_control.id
         JOIN attribute att_control ON ad_control.id_attribute = att_control.id;

-- #11682
alter table flexii.agenda_has_agenda add column if not exists hide_entity_in_datagrid bool default false;
alter table flexii.agenda_has_agenda add column if not exists hide_entity_in_datagrid_linked bool default false;

-- version
update configuration.configuration_values set value = '2.5.14.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.14.0
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.5.14.1'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.14.1
-- SQL for this version ends here
----------------------------------------------------

-- alter table reporting.element_table_has_column add if not exists order_priority int default 0;
-- alter table reporting.element_table_has_column add if not exists number_precision int default null;
--
-- alter table reporting.element_table add column if not exists multisort boolean default false;
-- alter table reporting.element_table_has_column add if not exists text_wrapping boolean default true;

-- #12633
update flexii.agenda_values
set value = '1'
where id in (
    select id
    from flexii.agenda_values av
    where 1 = 1
    and lower(value) = 'true'
    and id_agenda_definition in (
        select id from flexii.agenda_definition where id_attribute in (
            select id from flexii.attribute where id_attribute_data_type = (
                select id from flexii.attribute_data_type where name = 'bool'
            )
        )
    )
);

update flexii.agenda_values
set value = '0'
where id in (
    select id
    from flexii.agenda_values av
    where 1 = 1
    and lower(value) = 'false'
    and id_agenda_definition in (
        select id from flexii.agenda_definition where id_attribute in (
            select id from flexii.attribute where id_attribute_data_type = (
                select id from flexii.attribute_data_type where name = 'bool'
            )
        )
    )
);



-- version
update configuration.configuration_values set value = '2.5.14.2'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.14.2
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.5.14.3'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.14.3
-- SQL for this version ends here
----------------------------------------------------

-- #12527
alter table flexii.calendar add column if not exists is_without_weekends bool default false;
alter table flexii.calendar add column if not exists min_time varchar default '6:00';
alter table flexii.calendar add column if not exists max_time varchar default '21:00';
alter table flexii.calendar add column if not exists business_min_time varchar default '8:00';
alter table flexii.calendar add column if not exists business_max_time varchar default '17:00';

-- #13432
DROP MATERIALIZED VIEW mv_api_agenda_mapping;
DROP VIEW v_agenda_entity_title;
DROP VIEW v_agenda_definition_specification_by_value;
DROP MATERIALIZED VIEW mv_agenda_definition;

alter table agenda_definition alter column min_value type float using min_value::float;
alter table agenda_definition alter column max_value type float using max_value::float;

CREATE MATERIALIZED VIEW mv_agenda_definition AS
SELECT ad.id
     , ahsd.id_agenda
     , ahsd.id_agenda_segment
     , ahshagd.id_agenda_attribute_group
     , ad.is_control_for_group
     , ad.id_attribute
     , ad.priority
     , ad.is_not_null
     , ad.is_hidden
     , ad.default_value
     , ad.is_disabled
     , ad.is_read_only
     , ad.tooltip
     , ad.field_column_length
     , ad.skip_column_length
     , ad.field_column_height
     , ad.in_title_and_priority
     , ad.is_in_datagrid
     , ad.is_editable_in_datagrid
     , ad.is_hidden_in_datagrid
     , ad.datagrid_order_priority
     , ad.datagrid_order_by
     , ad.is_search_by
     , ad.is_show_in_linked_agenda
     , ad.is_unique
     , ad.min_length
     , ad.max_length
     , ad.min_value
     , ad.max_value
     , ad.pattern
     , ad.custom_validation
     , ad.is_in
     , ad.is_not_in
     , ad.is_importable
     , ad.file_specification
     , ad.id_agenda_definition_depended
     , ad.enabled
     , ad.dt_ins
     , ad.dt_upd
     , ad.id_agenda_segment_attribute_group_definition
     , ahshagd.id_agenda_has_segment_definition
     , a.name AS agenda_name
     , aseg.name AS segment_name
     , aag.name AS attribute_group_name
     , att.name AS attribute_name
     , attdt.name AS data_type
FROM agenda_definition ad
         JOIN agenda_has_segment_has_attribute_group_definition ahshagd
              ON ad.id_agenda_segment_attribute_group_definition = ahshagd.id
         JOIN agenda_has_segment_definition ahsd ON ahshagd.id_agenda_has_segment_definition = ahsd.id
         JOIN agenda a ON ahsd.id_agenda = a.id
         JOIN agenda_segment aseg ON ahsd.id_agenda_segment = aseg.id
         JOIN agenda_attribute_group aag ON ahshagd.id_agenda_attribute_group = aag.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN attribute_data_type attdt ON att.id_attribute_data_type = attdt.id
WHERE ad.enabled = TRUE
  AND ahshagd.enabled = TRUE
  AND ahsd.enabled = TRUE
  AND a.enabled = TRUE
  AND aseg.enabled = TRUE
  AND aag.enabled = TRUE
  AND att.enabled = TRUE
  AND attdt.enabled = TRUE;

CREATE UNIQUE INDEX ON mv_agenda_definition (id);

CREATE MATERIALIZED VIEW mv_api_agenda_mapping AS
SELECT
    aapthpa.id
     , aapt.id AS id_template
     , aapt.name AS template_name
     , ad.id_agenda
     , aapa.name AS template_attribute_name
     , ad.id AS id_agenda_definition
     , aapam.id AS id_page_attribute_mapping
     , aadp.id_external_application
FROM api_agenda_page_template aapt
         JOIN api_agenda_page_template_has_page_attribute aapthpa ON aapt.id = aapthpa.id_api_agenda_page_template
         JOIN api_agenda_page_attribute aapa ON aapa.id = aapthpa.id_api_agenda_page_attribute
         JOIN api_agenda_page_attribute_mapping aapam ON aapthpa.id = aapam.id_api_agenda_page_template_has_page_attribute
         JOIN api_agenda_definition_publish aadp ON aadp.id = aapam.id_api_agenda_definition_publish
         JOIN mv_agenda_definition ad ON ad.id = aadp.id_agenda_definition
WHERE aapt.enabled = TRUE
  AND aapthpa.enabled = TRUE
  AND aapa.enabled = TRUE
  AND aapam.enabled = TRUE
  AND aadp.enabled = TRUE;

create OR REPLACE view v_agenda_entity_title
        (id_agenda_entity, title, uid, id_agenda, enabled, is_anonymized, id_appuser_created_by, id_appuser_updated_by, dt_ins, dt_upd) as
SELECT final.id_agenda_entity,
       string_agg(final.value::text, ' '::text ORDER BY final.in_title_and_priority) AS title,
       ae.uid,
       ae.id_agenda,
       ae.enabled,
       ae.is_anonymized,
       ae.id_appuser_created_by,
       ae.id_appuser_updated_by,
       ae.dt_ins,
       ae.dt_upd
FROM (SELECT val.value,
             val.id_agenda_entity,
             ad.in_title_and_priority
      FROM (SELECT val_1.value,
                   val_1.id_agenda_definition,
                   val_1.id_agenda_entity
            FROM agenda_values val_1
            WHERE (val_1.id_agenda_definition IN (SELECT ad_1.id
                                                  FROM mv_agenda_definition ad_1
                                                           JOIN agenda_has_segment_definition ahsd ON ad_1.id_agenda_has_segment_definition = ahsd.id
                                                  WHERE ad_1.enabled = true
                                                    AND ahsd.enabled = true
                                                    AND ad_1.in_title_and_priority IS NOT NULL
                                                    AND ahsd.is_main = true))) val
               JOIN agenda_definition ad ON val.id_agenda_definition = ad.id
      ORDER BY ad.in_title_and_priority) final
         JOIN agenda_entity ae ON final.id_agenda_entity = ae.id
GROUP BY final.id_agenda_entity, ae.uid, ae.id_agenda, ae.enabled, ae.is_anonymized, ae.id_appuser_created_by, ae.id_appuser_updated_by, ae.dt_ins,
         ae.dt_upd;

CREATE OR REPLACE VIEW v_agenda_definition_specification_by_value AS
SELECT ad.id_agenda,
       att.name       AS name_definition,
       ad.is_not_null AS not_null_definition,
       adsbv.is_hidden,
       adsbv.is_not_null,
       adsbv.recurring_sequence,
       att_control.name,
       adsbv.value_control
FROM agenda_definition_specification_by_value adsbv
         JOIN mv_agenda_definition ad ON adsbv.id_agenda_definition = ad.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN mv_agenda_definition ad_control ON adsbv.id_agenda_definition_control = ad_control.id
         JOIN attribute att_control ON ad_control.id_attribute = att_control.id;

-- #12954
-- alter table reporting.element_table_has_column add if not exists number_precision int default null;

-- version
update configuration.configuration_values set value = '2.5.15.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.15.0
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.5.15.1'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.15.1
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.5.15.2'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.15.2
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.5.15.3'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.5.15.3
-- SQL for this version ends here
----------------------------------------------------

create unique index unq_agenda_definition_att_id_group_definition
    on agenda_definition (id_attribute, id_agenda_segment_attribute_group_definition);

-- #13549
alter table agenda_has_agenda
    add is_move_primary_on_change bool default FALSE not null;

comment on column agenda_has_agenda.is_move_primary_on_change is 'When Primary relation is changed, its will become non-primary and will be preserved in relations';

create unique index unq_api_agenda_def_publish_id_ext_app_id_agenda_def
    on api_agenda_definition_publish (id_external_application, id_agenda_definition);

-- #13413
UPDATE menu_item SET is_for_admin = false, is_for_superadmin = true WHERE name = 'permanent_ae_delete';

-- #13590
create unique index unq_aha_definition_id_agenda_has_agenda_id_attribute
    on agenda_has_agenda_definition (id_agenda_has_agenda, id_attribute);

-- #13619
alter table agenda_has_agenda_definition add column id_agenda_has_agenda_dependent int DEFAULT null;
alter table agenda_has_agenda_definition
    add constraint fk_agenda_has_agenda_definition_agenda_has_agenda_definition_id
        foreign key (id_agenda_has_agenda_dependent) references agenda_has_agenda_definition;

alter table attribute
    add custom_data_class varchar(50);


--------------- #13727 ---------------

CREATE TABLE flexii.agenda_definition_custom
(
    id                   serial
        CONSTRAINT pk_agenda_definition_custom
            PRIMARY KEY,
    id_agenda_definition integer                             NOT NULL
        CONSTRAINT fk_agenda_definition_custom_agenda_definition_id
            REFERENCES flexii.agenda_definition,
    class_name           varchar(100)                        NOT NULL,
    enabled              boolean   DEFAULT TRUE              NOT NULL,
    dt_ins               timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd               timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE UNIQUE INDEX unq_agenda_definition_custom_id_agenda_definition_class_name
    ON flexii.agenda_definition_custom (id_agenda_definition, class_name);

CREATE TRIGGER tr_agenda_definition_custom_dt_upd
    BEFORE UPDATE
    ON flexii.agenda_definition_custom
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

-- #13687 --
create index idx_agenda_entity_has_agenda_entity_enabled
    on flexii.agenda_entity_has_agenda_entity (enabled desc);

create index idx_agenda_definition_in_title_and_priority
    on flexii.agenda_definition (in_title_and_priority);

DROP VIEW flexii.v_agenda_entity_title;

CREATE MATERIALIZED VIEW flexii.v_agenda_entity_title AS
SELECT final.id_agenda_entity,
       STRING_AGG(final.value::text, ' '::text ORDER BY final.in_title_and_priority) AS title,
       ae.uid,
       ae.id_agenda,
       ae.enabled,
       ae.is_anonymized,
       ae.id_appuser_created_by,
       ae.id_appuser_updated_by,
       ae.dt_ins,
       ae.dt_upd
FROM (SELECT val.value,
             val.id_agenda_entity,
             ad.in_title_and_priority
      FROM (SELECT val_1.value,
                   val_1.id_agenda_definition,
                   val_1.id_agenda_entity
            FROM flexii.agenda_values val_1
            WHERE (val_1.id_agenda_definition IN (SELECT ad_1.id
                                                  FROM flexii.mv_agenda_definition ad_1
                                                           JOIN flexii.agenda_has_segment_definition ahsd
                                                                ON ad_1.id_agenda_has_segment_definition = ahsd.id
                                                  WHERE ad_1.enabled = TRUE
                                                    AND ahsd.enabled = TRUE
                                                    AND ad_1.in_title_and_priority IS NOT NULL
                                                    AND ahsd.is_main = TRUE))) val
               JOIN flexii.agenda_definition ad ON val.id_agenda_definition = ad.id
      ORDER BY ad.in_title_and_priority) final
         JOIN flexii.agenda_entity ae ON final.id_agenda_entity = ae.id
GROUP BY final.id_agenda_entity, ae.uid, ae.id_agenda, ae.enabled, ae.is_anonymized, ae.id_appuser_created_by, ae.id_appuser_updated_by, ae.dt_ins,
         ae.dt_upd;

CREATE UNIQUE INDEX v_agenda_entity_title_id_ae ON v_agenda_entity_title (id_agenda_entity);


-- #13773
alter table agenda_has_agenda
    add is_editable_agenda bool default TRUE not null;

alter table agenda_has_agenda
    add is_editable_agenda_linked bool default TRUE not null;

-- #12366
UPDATE agenda_values SET value = '1' WHERE id_agenda_definition IN (SELECT ad.id FROM mv_agenda_definition ad JOIN attribute att ON att.id = ad.id_attribute WHERE att.lookup = 'lookup_yes_no') AND value = (SELECT id::varchar from master_lookup WHERE lookup_name = 'lookup_yes_no' AND name = 'yes');
UPDATE agenda_values SET value = '0' WHERE id_agenda_definition IN (SELECT ad.id FROM mv_agenda_definition ad JOIN attribute att ON att.id = ad.id_attribute WHERE att.lookup = 'lookup_yes_no') AND value = (SELECT id::varchar from master_lookup WHERE lookup_name = 'lookup_yes_no' AND name = 'no');

UPDATE attribute SET LOOKUP = null, id_attribute_data_type = (select id from attribute_data_type where name = 'bool') WHERE lookup IN ('lookup_yes_no', 'lookup_bool');


-- version
update configuration.configuration_values set value = '2.6.0.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.0.0
-- SQL for this version ends here
----------------------------------------------------
-------- #12304 --------
alter table agenda_definition_specification_by_value alter column value_control drop not null;
alter table agenda_definition_specification_by_value add is_value_control_filled bool default NULL;

-------- #13870 --------
ALTER TABLE log.lg_logger add is_last boolean not null default false;
ALTER TABLE log.lg_logger add id_parent int default null;
ALTER TABLE log.lg_logger Add CONSTRAINT fk_lg_logger_parent FOREIGN KEY (id_parent)
    REFERENCES log.lg_logger (id)
    ON UPDATE RESTRICT ON DELETE CASCADE;

alter table base.log_api_response alter column body type text using body::text;

-- version
update configuration.configuration_values set value = '2.6.1.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.1.0
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.6.1.1'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.1.1
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.6.1.2'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.1.2
-- SQL for this version ends here
----------------------------------------------------

-- #13775
INSERT INTO base.appuser (username, fullname, email, id_locale, is_system_user) VALUES ('system_user', 'Flexii System', 'info@evosoft.cz', 1, true);

-- #13743
create table flexii.agenda_definition_validation
(
    id                   serial                              not null
        constraint pk_agenda_definition_validation
            primary key,
    id_agenda_definition integer                             not null,
    class_name           varchar(100)                        not null,
    priority             smallint                            not null,
    enabled              bool      default TRUE              not null,
    dt_ins               timestamp default current_timestamp not null,
    dt_upd               timestamp default current_timestamp not null
);

CREATE TRIGGER tr_agenda_definition_validation_dt_upd
    BEFORE UPDATE
    ON flexii.agenda_definition_validation
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

create unique index unq_agenda_definition_validation_class_name
    on flexii.agenda_definition_validation (id_agenda_definition, class_name);

create unique index unq_agenda_definition_validation_priority
    on flexii.agenda_definition_validation (id_agenda_definition, priority);

-- #14035
alter table agenda_has_agenda add is_tab_hidden bool default FALSE not null;
alter table agenda_has_agenda add is_tab_hidden_linked bool default FALSE not null;

-- #14025
CREATE TABLE agenda_has_agenda_show_attribute_group
(
    id                                                   serial
        CONSTRAINT pk_agenda_has_agenda_show_attribute_group
            PRIMARY KEY,
    id_agenda_has_agenda                                 integer                             NOT NULL
        CONSTRAINT fk_agenda_has_agenda_show_attribute_group_agenda_has_agenda_id
            REFERENCES agenda_has_agenda,
    id_agenda_has_segment_has_attribute_group_definition integer                             NOT NULL
        CONSTRAINT fk_agenda_has_agenda_show_attribute_group_agenda_definition_id
            REFERENCES agenda_has_segment_has_attribute_group_definition,
    enabled                                              boolean   DEFAULT TRUE              NOT NULL,
    dt_ins                                               timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd                                               timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER tr_agenda_has_agenda_show_attribute_group
    BEFORE UPDATE
    ON flexii.agenda_has_agenda_show_attribute_group
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

CREATE UNIQUE INDEX unq_agenda_has_agenda_show_attribute_group
    ON agenda_has_agenda_show_attribute_group (id_agenda_has_agenda, id_agenda_has_segment_has_attribute_group_definition);


-- #12183
create table if not exists flexii.user_group_has_agenda_definition
(
    id serial constraint pk_user_group_has_agenda_definition primary key,
    id_user_group integer not null constraint fk_user_group_has_agenda_definition_user_group references base.user_group,
    id_agenda_definition integer not null constraint fk_user_group_has_agenda_definition_definition references flexii.agenda_definition,
    is_read boolean default false not null,
    is_write boolean default false not null ,
    enabled boolean default true not null,
    dt_ins timestamp default current_timestamp not null,
    dt_upd timestamp default current_timestamp not null,
    constraint unq_user_group_has_agenda_definition unique (id_user_group, id_agenda_definition)
);

create trigger tr_user_group_has_agenda_definition_dt_upd
    before update
    on flexii.user_group_has_agenda_definition
    for each row
execute procedure base.update_dt_upd();

create table if not exists flexii.user_group_has_agenda_has_segment_definition
(
    id serial constraint pk_user_group_has_agenda_has_segment_definition primary key,
    id_user_group integer not null constraint fk_user_group_has_agenda_has_segment_definition_user_group references base.user_group,
    id_agenda_has_segment_definition integer not null constraint fk_user_group_has_agenda_has_segment_definition_segment references flexii.agenda_has_segment_definition,
    is_read boolean default false not null,
    is_write boolean default false not null ,
    enabled boolean default true not null,
    dt_ins timestamp default current_timestamp not null,
    dt_upd timestamp default current_timestamp not null,
    constraint unq_user_group_has_agenda_has_segment_definition unique (id_user_group, id_agenda_has_segment_definition)
);

create trigger tr_user_group_has_agenda_has_segment_definition_dt_upd
    before update
    on flexii.user_group_has_agenda_has_segment_definition
    for each row
execute procedure base.update_dt_upd();

create table if not exists flexii.user_group_has_agenda_has_segment_group_definition
(
    id serial constraint pk_user_group_has_agenda_has_segment_group_definition primary key,
    id_user_group integer not null constraint fk_user_group_has_agenda_has_segment_group_definition_ug references base.user_group,
    id_agenda_has_segment_has_attribute_group_definition integer not null constraint fk_user_group_has_agenda_has_segment_group_definition_segment references flexii.agenda_definition,
    is_read boolean default false not null,
    is_write boolean default false not null ,
    enabled boolean default true not null,
    dt_ins timestamp default current_timestamp not null,
    dt_upd timestamp default current_timestamp not null,
    constraint unq_user_group_has_agenda_has_segment_group_definition unique (id_user_group, id_agenda_has_segment_has_attribute_group_definition)
);

create trigger tr_user_group_has_agenda_has_segment_group_definition_dt_upd
    before update
    on flexii.user_group_has_agenda_has_segment_group_definition
    for each row
execute procedure base.update_dt_upd();

drop function if exists flexii.agenda_definition_check_agenda_title;
create or replace function flexii.agenda_definition_check_agenda_title()
    returns trigger
    language plpgsql
    stable
    called on null input
    security invoker
    cost 1
as $$
declare
    conflicts integer default 0;
begin
    select count(ad.id) as count
    into conflicts
    from flexii.agenda_definition ad
    where 1 = 1
    and ad.id = new.id_agenda_definition
    and ad.in_title_and_priority is not null
    and new.is_read = false
    and new.enabled = true
    and ad.enabled = true;

    if conflicts > 0 then
        raise exception 'Agenda definition ID: % (rows find: %) can not be is_read = false because is defined in in_title_and_priority.', new.id_agenda_definition, conflicts;
    end if;

    return new;
end;
$$;

drop trigger if exists tr_user_group_has_agenda_definition_check on flexii.user_group_has_agenda_definition;
create trigger tr_user_group_has_agenda_definition_check
    before insert or update
    on flexii.user_group_has_agenda_definition
    for each row
execute procedure flexii.agenda_definition_check_agenda_title();

drop function if exists flexii.agenda_has_segment_group_definition_check_agenda_title;
create or replace function flexii.agenda_has_segment_group_definition_check_agenda_title()
    returns trigger
    language plpgsql
    stable
    called on null input
    security invoker
    cost 1
as $$
declare
    conflicts integer default 0;
begin
    select count(ad.id) as count
    into conflicts
    from flexii.agenda_definition ad
    where 1 = 1
    and ad.id_agenda_segment_attribute_group_definition = new.id_agenda_has_segment_has_attribute_group_definition
    and ad.in_title_and_priority is not null
    and new.is_read = false
    and new.enabled = true
    and ad.enabled = true;

    if conflicts > 0 then
        raise exception 'Agenda has segment attribute group definition ID: % (rows find: %) can not be is_read = false because is defined in in_title_and_priority.', new.id_agenda_has_segment_definition, conflicts;
    end if;

    return new;
end;
$$;

drop trigger if exists tr_user_group_has_agenda_has_segment_group_definition_check on flexii.user_group_has_agenda_has_segment_group_definition;
create trigger tr_user_group_has_agenda_has_segment_group_definition_check
    before insert or update
    on flexii.user_group_has_agenda_has_segment_group_definition
    for each row
execute procedure flexii.agenda_has_segment_group_definition_check_agenda_title();

drop function if exists flexii.agenda_has_segment_definition_check_agenda_title;
create or replace function flexii.agenda_has_segment_definition_check_agenda_title()
    returns trigger
    language plpgsql
    stable
    called on null input
    security invoker
    cost 1
as $$
declare
    conflicts integer default 0;
begin
    select count(ad.id) as count
    into conflicts
    from flexii.agenda_definition ad
    where 1 = 1
    and ad.id_agenda_segment_attribute_group_definition in (
        select id from flexii.agenda_has_segment_has_attribute_group_definition where id_agenda_has_segment_definition = new.id_agenda_has_segment_definition
    )
    and ad.in_title_and_priority is not null
    and new.is_read = false
    and new.enabled = true
    and ad.enabled = true;

    if conflicts > 0 then
        raise exception 'Agenda has segment definition ID: % (rows find: %) can not be is_read = false because is defined in in_title_and_priority.', new.id_agenda_has_segment_definition, conflicts;
    end if;

    return new;
end;
$$;

drop trigger if exists tr_user_group_has_agenda_has_segment_definition_check on flexii.user_group_has_agenda_has_segment_definition;
create trigger tr_user_group_has_agenda_has_segment_definition_check
    before insert or update
    on flexii.user_group_has_agenda_has_segment_definition
    for each row
execute procedure flexii.agenda_has_segment_definition_check_agenda_title();

-- mv_user_group_has_agenda_definition
--drop materialized view if exists flexii.mv_user_group_has_agenda_definition;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_definition AS
SELECT id_user_group
     , user_name
     , id_agenda_definition
     , is_read
     , is_write
     , id_agenda
     , id_agenda_segment
     , id_agenda_attribute_group
     , id_attribute
     , agenda_name
     , segment_name
     , attribute_group_name
     , attribute_name
FROM (SELECT id_user_group,
             user_name,
             id_agenda_definition,
             is_read,
             is_write
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughad.id_agenda_definition, ughad.is_read, ughad.is_write
            FROM base.user_group ug
                     JOIN user_group_has_agenda_definition ughad ON ug.id = ughad.id_user_group
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughad.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, ad.id AS id_agenda_definition, TRUE AS is_read, TRUE AS is_write
       FROM base.user_group ug,
            agenda_definition ad
       WHERE ug.enabled = TRUE
         AND ad.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, ad.id) NOT IN (SELECT id_user_group, id_agenda_definition
                                    FROM user_group_has_agenda_definition ughad
                                    WHERE enabled = TRUE))) base
         JOIN mv_agenda_definition ad ON base.id_agenda_definition = ad.id;

-- mv_user_group_has_agenda_has_segment_definition
--drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_definition;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_definition AS
SELECT id_user_group
     , user_name
     , id_agenda_has_segment_definition
     , is_read
     , is_write
     , id_agenda
     , id_agenda_segment
FROM (SELECT id_user_group,
             user_name,
             id_agenda_has_segment_definition,
             is_read,
             is_write,
             id_agenda,
             id_agenda_segment
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_definition, ughasd.is_read, ughasd.is_write, ahsd.id_agenda, ahsd.id_agenda_segment
            FROM base.user_group ug
                JOIN user_group_has_agenda_has_segment_definition ughasd ON ug.id = ughasd.id_user_group
                JOIN agenda_has_segment_definition ahsd ON ahsd.id = ughasd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_definition, TRUE AS is_read, TRUE AS is_write, def.id_agenda, def.id_agenda_segment
       FROM base.user_group ug,
            agenda_has_segment_definition def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_definition
                                     FROM user_group_has_agenda_has_segment_definition ughad
                                     WHERE enabled = TRUE))) base;

-- mv_user_group_has_agenda_has_segment_group_definition
--drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_group_definition;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_group_definition AS
SELECT id_user_group
     , user_name
     , id_agenda_has_segment_has_attribute_group_definition
     , is_read
     , is_write
     , id_agenda_attribute_group
     , id_agenda_segment
     , id_agenda
FROM (SELECT id_user_group,
             user_name,
             id_agenda_has_segment_has_attribute_group_definition,
             is_read,
             is_write
             , id_agenda_attribute_group
             , id_agenda_segment
             , id_agenda
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_has_attribute_group_definition, ughasd.is_read, ughasd.is_write, ahshagd.id_agenda_attribute_group, ahsd.id_agenda_segment, ahsd.id_agenda
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_group_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_has_attribute_group_definition ahshagd ON ughasd.id_agenda_has_segment_has_attribute_group_definition = ahshagd.id
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_has_attribute_group_definition, TRUE AS is_read, TRUE AS is_write, def.id_agenda_attribute_group, def.id_agenda_segment, def.id_agenda
       FROM base.user_group ug,
            (SELECT ahshagd.*, ahsd.id_agenda, ahsd.id_agenda_segment
             FROM agenda_has_segment_has_attribute_group_definition ahshagd
                 JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
             ) def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_has_attribute_group_definition
                                     FROM user_group_has_agenda_has_segment_group_definition ughad
                                     WHERE enabled = TRUE))) base;


-- mv_user_group_has_agenda_permission
--drop materialized view if exists flexii.mv_user_group_has_agenda_permission;

create materialized view flexii.mv_user_group_has_agenda_permission as
select type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_agenda_definition, id_user_group, is_read, is_write
from (
         select
             'definition' as type,
             ughad.id_agenda,
             ughad.id_agenda_segment,
             ughad.id_agenda_attribute_group,
             ughad.id_attribute,
             ughad.id_agenda_definition,
             ughad.id_user_group as id_user_group,
             ughad.is_read,
             ughad.is_write
         from flexii.mv_user_group_has_agenda_definition ughad
         union all
         select
             'segment' as type,
             ughahsd.id_agenda,
             ughahsd.id_agenda_segment,
             null,
             null,
             null,
             ughahsd.id_user_group as id_user_group,
             ughahsd.is_read,
             ughahsd.is_write
         from flexii.mv_user_group_has_agenda_has_segment_definition ughahsd
         union all
         select
             'group' as type,
             ughahsgd.id_agenda,
             ughahsgd.id_agenda_segment,
             ughahsgd.id_agenda_attribute_group,
             null,
             null,
             ughahsgd.id_user_group as id_user_group,
             ughahsgd.is_read,
             ughahsgd.is_write
         from flexii.mv_user_group_has_agenda_has_segment_group_definition ughahsgd
     ) definitions;

CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission ON mv_user_group_has_agenda_permission (type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_user_group);
CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission_def ON mv_user_group_has_agenda_permission (type, id_agenda_definition, id_user_group);

-- version
update configuration.configuration_values set value = '2.6.2.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.2.0
-- SQL for this version ends here
----------------------------------------------------
-- #13938
create schema if not exists migration;
create table if not exists migration.external_system_entity_mapping
(
    id serial constraint pk_migration_external_system_entity_mapping primary key,
    id_own_entity integer null,
    own_entity_type varchar(256) null,
    external_system varchar(256) not null,
    id_external_system integer null,
    external_system_entity_type varchar(256) null,
    dt_ins timestamp default current_timestamp not null,
    dt_upd timestamp default current_timestamp not null,
    constraint unq_migration_external_system_entity_mapping unique nulls not distinct (id_own_entity, own_entity_type, external_system, id_external_system, external_system_entity_type)
);

-- #14021
alter table agenda_has_agenda add column if not exists is_addable boolean default true;
alter table agenda_has_agenda add column if not exists is_deletable boolean default true;
alter table agenda_has_agenda add column if not exists is_addable_linked boolean default true;
alter table agenda_has_agenda add column if not exists is_deletable_linked boolean default true;

-- #13600
alter table flexii.agenda_button add column if not exists confirmation text default null;

-- #13864
CREATE TABLE flexii.custom_import_type
(
    id          serial
        CONSTRAINT pk_custom_import_type PRIMARY KEY,
    id_agenda   int                                 NOT NULL
        CONSTRAINT fk_custom_import_type_agenda_id REFERENCES flexii.agenda,
    name        VARCHAR(100)                        NOT NULL,
    description VARCHAR(400)                        NOT NULL,
    enabled     bool      DEFAULT TRUE              NOT NULL,
    dt_ins      timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd      timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE UNIQUE INDEX unq_custom_import_type_name
    ON flexii.custom_import_type (name);

CREATE TRIGGER tr_custom_import_type_dt_upd
    BEFORE UPDATE
    ON flexii.custom_import_type
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

CREATE TABLE flexii.custom_import
(
    id                    serial
        CONSTRAINT pk_custom_import PRIMARY KEY,
    id_custom_import_type int                                 NOT NULL
        CONSTRAINT fk_custom_import_custom_import_type_id REFERENCES flexii.custom_import_type,
    id_appuser            int                                 NOT NULL
        CONSTRAINT fk_custom_import_appuser_id REFERENCES base.appuser,
    id_agenda_entity      int                                 DEFAULT NULL
        CONSTRAINT fk_custom_import_agenda_entity_id REFERENCES flexii.agenda_entity,
    file_name             VARCHAR(50)                         NOT NULL,
    original_file_name    VARCHAR(100)                        NOT NULL,
    dt_start              timestamp DEFAULT NULL,
    dt_end                timestamp DEFAULT NULL,
    enabled               bool      DEFAULT TRUE              NOT NULL,
    dt_ins                timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd                timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER tr_custom_import_dt_upd
    BEFORE UPDATE
    ON flexii.custom_import
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

CREATE TABLE flexii.custom_import_row
(
    id                    serial
        CONSTRAINT pk_custom_import_row PRIMARY KEY,
    id_custom_import int                                 NOT NULL
        CONSTRAINT fk_custom_import_row_custom_import_id REFERENCES flexii.custom_import,
    id_agenda_entity      int                                 DEFAULT NULL
        CONSTRAINT fk_custom_import_row_agenda_entity_id REFERENCES flexii.agenda_entity,
    DATA                  json                                NOT NULL,
    FILE_ROW              smallint                            NOT NULL,
    DT_START              timestamp DEFAULT NULL,
    DT_END                timestamp DEFAULT NULL,
    IS_SUCCESS            bool      DEFAULT NULL,
    MESSAGE               text      DEFAULT NULL,
    ENABLED               bool      DEFAULT true                 NOT NULL,
    DT_INS                timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    DT_UPD                timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER tr_custom_import_row_dt_upd
    BEFORE UPDATE
    ON flexii.custom_import_row
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

-- #13600
alter table flexii.agenda_button add column if not exists confirmation text default null;
-- #14021
alter table agenda_has_agenda add column if not exists is_addable boolean default true;
alter table agenda_has_agenda add column if not exists is_deletable boolean default true;
alter table agenda_has_agenda add column if not exists is_addable_linked boolean default true;
alter table agenda_has_agenda add column if not exists is_deletable_linked boolean default true;

-- version
update configuration.configuration_values set value = '2.6.3.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.3.0
-- SQL for this version ends here
----------------------------------------------------
-- #13600
alter table flexii.agenda_button add column if not exists confirmation text default null;

--------------------------
-- #13634 - permissions --

alter table user_group_has_agenda add is_can_create bool;
alter table user_group_has_agenda add is_can_delete bool;

create unique index unq_user_group_has_agenda_id_user_group_id_agenda
    on user_group_has_agenda (id_user_group desc, id_agenda asc);

drop materialized view if exists flexii.mv_user_group_has_agenda_permission;
drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_definition;
drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_group_definition;
drop materialized view if exists flexii.mv_user_group_has_agenda_definition;

-- mv_user_group_has_agenda_definition

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , ad.id_agenda
     , id_agenda_segment
     , id_agenda_attribute_group
     , id_attribute
     , agenda_name
     , segment_name
     , attribute_group_name
     , attribute_name
FROM (SELECT id_user_group,
             user_name,
             id_agenda_definition,
             is_read,
             is_write
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughad.id_agenda_definition, ughad.is_write, ughad.is_read
            FROM base.user_group ug
                     JOIN user_group_has_agenda_definition ughad ON ug.id = ughad.id_user_group
                JOIN mv_agenda_definition ad ON ad.id = ughad.id_agenda_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughad.enabled = TRUE
        ) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, ad.id AS id_agenda_definition, TRUE AS is_read, TRUE AS is_write
       FROM base.user_group ug,
            mv_agenda_definition ad
       WHERE ug.enabled = TRUE
         AND ad.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, ad.id) NOT IN (SELECT id_user_group, id_agenda_definition
                                    FROM user_group_has_agenda_definition ughad
                                    WHERE enabled = TRUE)
        )
      ) base
         JOIN mv_agenda_definition ad ON base.id_agenda_definition = ad.id
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = ad.id_agenda;

-- mv_user_group_has_agenda_has_segment_definition

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_has_segment_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , base.id_agenda
     , id_agenda_segment
FROM (SELECT id_user_group,
             user_name,
             id_agenda_has_segment_definition,
             is_read,
             is_write,
             id_agenda,
             id_agenda_segment
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_definition, ughasd.is_read, ughasd.is_write, ahsd.id_agenda, ahsd.id_agenda_segment
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ughasd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_definition, TRUE AS is_read, TRUE AS is_write, def.id_agenda, def.id_agenda_segment
       FROM base.user_group ug,
            agenda_has_segment_definition def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_definition
                                     FROM user_group_has_agenda_has_segment_definition ughad
                                     WHERE enabled = TRUE))) base
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = base.id_agenda;

-- mv_user_group_has_agenda_has_segment_group_definition

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_group_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_has_segment_has_attribute_group_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , id_agenda_attribute_group
     , id_agenda_segment
     , base.id_agenda
FROM (SELECT id_user_group,
             user_name,
             id_agenda_has_segment_has_attribute_group_definition,
             is_read,
             is_write
              , id_agenda_attribute_group
              , id_agenda_segment
              , id_agenda
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_has_attribute_group_definition, ughasd.is_read, ughasd.is_write, ahshagd.id_agenda_attribute_group, ahsd.id_agenda_segment, ahsd.id_agenda
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_group_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_has_attribute_group_definition ahshagd ON ughasd.id_agenda_has_segment_has_attribute_group_definition = ahshagd.id
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_has_attribute_group_definition, TRUE AS is_read, TRUE AS is_write, def.id_agenda_attribute_group, def.id_agenda_segment, def.id_agenda
       FROM base.user_group ug,
            (SELECT ahshagd.*, ahsd.id_agenda, ahsd.id_agenda_segment
             FROM agenda_has_segment_has_attribute_group_definition ahshagd
                      JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            ) def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_has_attribute_group_definition
                                     FROM user_group_has_agenda_has_segment_group_definition ughad
                                     WHERE enabled = TRUE))) base
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = base.id_agenda;


-- mv_user_group_has_agenda_permission

create materialized view flexii.mv_user_group_has_agenda_permission as
select type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_agenda_definition, id_user_group, is_read, is_write
from (
         select
             'definition' as type,
             ughad.id_agenda,
             ughad.id_agenda_segment,
             ughad.id_agenda_attribute_group,
             ughad.id_attribute,
             ughad.id_agenda_definition,
             ughad.id_user_group as id_user_group,
             ughad.is_read,
             ughad.is_write
         from flexii.mv_user_group_has_agenda_definition ughad
         union all
         select
             'segment' as type,
             ughahsd.id_agenda,
             ughahsd.id_agenda_segment,
             null,
             null,
             null,
             ughahsd.id_user_group as id_user_group,
             ughahsd.is_read,
             ughahsd.is_write
         from flexii.mv_user_group_has_agenda_has_segment_definition ughahsd
         union all
         select
             'group' as type,
             ughahsgd.id_agenda,
             ughahsgd.id_agenda_segment,
             ughahsgd.id_agenda_attribute_group,
             null,
             null,
             ughahsgd.id_user_group as id_user_group,
             ughahsgd.is_read,
             ughahsgd.is_write
         from flexii.mv_user_group_has_agenda_has_segment_group_definition ughahsgd
     ) definitions;

CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission ON mv_user_group_has_agenda_permission (type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_user_group);
CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission_def ON mv_user_group_has_agenda_permission (type, id_agenda_definition, id_user_group);

CREATE TABLE flexii.user_group_has_definition_value
(
    id                   SERIAL                                 NOT NULL
        CONSTRAINT pk_user_group_has_definition_value
            PRIMARY KEY,
    id_user_group        integer                                NOT NULL
        CONSTRAINT fk_user_group_has_definition_value_user_group_id
            REFERENCES base.user_group,
    id_agenda_definition integer                                NOT NULL
        CONSTRAINT fk_user_group_has_definition_value_agenda_definition_id
            REFERENCES agenda_definition,
    value                varchar(400) DEFAULT NULL,
    recurring_sequence   integer      DEFAULT NULL,
    enabled              bool         DEFAULT TRUE              NOT NULL,
    dt_ins               timestamp    DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd               timestamp    DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER tr_user_group_has_definition_value_dtu
    BEFORE UPDATE
    ON flexii.user_group_has_definition_value
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

create unique index unq_user_group_has_definition_value_main
    on user_group_has_definition_value (id_user_group desc, id_agenda_definition asc, value);

CREATE TABLE flexii.user_group_has_definition_value_for_aha
(
    id                   serial                              NOT NULL
        CONSTRAINT pk_user_group_has_definition_value_for_aha
            PRIMARY KEY,
    name                 varchar(50)                         NOT NULL
        CONSTRAINT unq_user_group_has_definition_value_for_aha_name
            UNIQUE,
    id_recursion         integer   DEFAULT NULL
        CONSTRAINT fk_user_group_has_definition_value_for_aha_recursion_id
            REFERENCES user_group_has_definition_value_for_aha,
    id_user_group        integer                             NOT NULL
        CONSTRAINT fk_user_group_has_definition_value_for_aha_user_group_id
            REFERENCES user_group (id),
    id_agenda            integer   DEFAULT NULL
        CONSTRAINT fk_user_group_has_definition_value_for_aha_agenda_id
            REFERENCES agenda,
    id_agenda_has_agenda integer   DEFAULT NULL
        CONSTRAINT fk_user_group_has_definition_value_for_aha_agenda_has_agenda_id
            REFERENCES agenda_has_agenda,
    id_agenda_definition integer   DEFAULT NULL
        CONSTRAINT fk_user_group_has_definition_value_for_aha_agenda_definition_id
            REFERENCES agenda_definition,
    value                varchar   DEFAULT NULL,
    enabled              bool      DEFAULT TRUE              NOT NULL,
    dt_ins               timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd               timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER tr_user_group_has_definition_value_for_aha_dtu
    BEFORE UPDATE
    ON flexii.user_group_has_definition_value_for_aha
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();


-- #13634 - permissions --
--------------------------


-- #14115
create schema if not exists photon;


create table photon.pht_log (
    id serial constraint pk_pht_pht_log primary key,
    type int not null,
    user_from int,
    user_to text not null,
    head varchar(100) not null,
    body text not null,
    data text not null,
    dt_inserted timestamp default current_timestamp not null
);

create table photon.pht_app (
    id serial constraint pk_pht_pht_app primary key,
    id_notification int not null,
    dt_ins timestamp default current_timestamp not null,
    dt_view timestamp,
    dt_click timestamp
);

create table photon.pht_mail (
    id              serial constraint pk_pht_pht_mail primary key,
    id_notification int                                 not null,
    dt_ins          timestamp default current_timestamp not null,
    is_sent         bool default false,
    dt_view         timestamp default null
);


-- version
update configuration.configuration_values set value = '2.6.4.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.4.0
-- SQL for this version ends here
----------------------------------------------------

CREATE TABLE flexii.agenda_has_agenda_custom_row_button
(
    id                   SERIAL                              NOT NULL
        CONSTRAINT pk_agenda_has_agenda_custom_row_button
            PRIMARY KEY,
    id_agenda_has_agenda integer                             NOT NULL
        CONSTRAINT fk_agenda_has_agenda_custom_row_button_agenda_has_agenda_id
            REFERENCES agenda_has_agenda,
    class_name           varchar(50)                         NOT NULL,
    enabled              bool      DEFAULT TRUE              NOT NULL,
    dt_ins               timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd               timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT unq_agenda_has_agenda_custom_row_button
        UNIQUE (id_agenda_has_agenda, class_name)
);

CREATE TRIGGER tr_agenda_has_agenda_custom_row_button_dtu
    BEFORE UPDATE
    ON flexii.agenda_has_agenda_custom_row_button
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

-- version
update configuration.configuration_values set value = '2.6.5.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.5.0
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.6.5.1'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.5.1
-- SQL for this version ends here
----------------------------------------------------

create unique index unq_agenda_has_agenda_show_attribute_main
    on agenda_has_agenda_show_attribute (id_agenda_has_agenda, id_agenda_definition);

drop index idx_agenda_entity_has_agenda_entity_id_agenda_has_agenda;

create index idx_agenda_entity_has_agenda_entity_id_agenda_has_agenda
    on agenda_entity_has_agenda_entity (id_agenda_has_agenda, is_primary);

create unique index unq_agenda_entity_has_agenda_entity_id_agenda_entity_and_linked
    on agenda_entity_has_agenda_entity (id_agenda_entity desc, id_agenda_entity_linked asc);

create index idx_agenda_has_file_id_agenda_entity_id_agenda_definition
    on agenda_has_file (id_agenda_entity desc, id_agenda_definition asc);

alter table agenda_entity
    add title varchar(400);

DROP MATERIALIZED VIEW v_agenda_entity_title;

CREATE VIEW flexii.v_agenda_entity_title AS
SELECT final.id_agenda_entity,
       STRING_AGG(final.value::text, ' '::text ORDER BY final.in_title_and_priority) AS title,
       ae.uid,
       ae.id_agenda,
       ae.enabled,
       ae.is_anonymized,
       ae.id_appuser_created_by,
       ae.id_appuser_updated_by,
       ae.dt_ins,
       ae.dt_upd
FROM (SELECT val.value,
             val.id_agenda_entity,
             ad.in_title_and_priority
      FROM (SELECT val_1.value,
                   val_1.id_agenda_definition,
                   val_1.id_agenda_entity
            FROM agenda_values val_1
            WHERE (val_1.id_agenda_definition IN (SELECT ad_1.id
                                                  FROM mv_agenda_definition ad_1
                                                           JOIN agenda_has_segment_definition ahsd ON ad_1.id_agenda_has_segment_definition = ahsd.id
                                                  WHERE ad_1.enabled = TRUE
                                                    AND ahsd.enabled = TRUE
                                                    AND ad_1.in_title_and_priority IS NOT NULL
                                                    AND ahsd.is_main = TRUE))) val
               JOIN agenda_definition ad ON val.id_agenda_definition = ad.id
      ORDER BY ad.in_title_and_priority) final
         JOIN agenda_entity ae ON final.id_agenda_entity = ae.id
GROUP BY final.id_agenda_entity, ae.uid, ae.id_agenda, ae.enabled, ae.is_anonymized, ae.id_appuser_created_by, ae.id_appuser_updated_by, ae.dt_ins,
         ae.dt_upd;


-- version
update configuration.configuration_values set value = '2.6.5.2'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.5.2
-- SQL for this version ends here
----------------------------------------------------

drop index unq_agenda_entity_has_agenda_entity_id_agenda_entity_and_linked;

create unique index unq_agenda_entity_has_agenda_entity_main
    on agenda_entity_has_agenda_entity (id_agenda_has_agenda desc, id_agenda_entity desc, id_agenda_entity_linked asc);

-- version
update configuration.configuration_values set value = '2.6.5.3'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.5.3
-- SQL for this version ends here
----------------------------------------------------

drop index unq_agenda_entity_has_agenda_entity_main;

create index idx_agenda_entity_has_agenda_entity_id_agenda_entity_and_linked
    on agenda_entity_has_agenda_entity (id_agenda_entity desc, id_agenda_entity_linked asc);

DROP MATERIALIZED VIEW mv_api_agenda_mapping;
DROP VIEW v_agenda_entity_title;
DROP VIEW v_agenda_definition_specification_by_value;
drop materialized view if exists flexii.mv_user_group_has_agenda_permission;
drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_definition;
drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_group_definition;
drop materialized view if exists flexii.mv_user_group_has_agenda_definition;

-- before drop mv_agenda_definition, don't forget to drop view dv_agenda ...
DROP MATERIALIZED VIEW mv_agenda_definition;

CREATE MATERIALIZED VIEW flexii.mv_agenda_definition AS
SELECT ad.id,
       ahsd.id_agenda,
       ahsd.id_agenda_segment,
       ahshagd.id_agenda_attribute_group,
       ad.is_control_for_group,
       ad.id_attribute,
       ad.priority,
       ad.is_not_null,
       ad.is_hidden,
       ad.default_value,
       ad.is_disabled,
       ad.is_read_only,
       ad.tooltip,
       ad.field_column_length,
       ad.skip_column_length,
       ad.field_column_height,
       ad.in_title_and_priority,
       ad.is_in_datagrid,
       ad.is_editable_in_datagrid,
       ad.is_hidden_in_datagrid,
       ad.datagrid_order_priority,
       ad.datagrid_order_by,
       ad.is_search_by,
       ad.is_show_in_linked_agenda,
       ad.is_unique,
       ad.min_length,
       ad.max_length,
       ad.min_value,
       ad.max_value,
       ad.pattern,
       ad.custom_validation,
       ad.is_in,
       ad.is_not_in,
       ad.is_importable,
       ad.file_specification,
       ad.id_agenda_definition_depended,
       ad.enabled,
       ad.dt_ins,
       ad.dt_upd,
       ad.id_agenda_segment_attribute_group_definition,
       ahshagd.id_agenda_has_segment_definition,
       a.name     AS agenda_name,
       aseg.name  AS segment_name,
       aag.name   AS attribute_group_name,
       att.name   AS attribute_name,
       attdt.name AS data_type,
       a.name || '-' || aseg.name || '-' || aag.name || '-' || att.name AS full_unique_name,
       aseg.name || '-' || aag.name || '-' || att.name AS unique_name
FROM agenda_definition ad
         JOIN agenda_has_segment_has_attribute_group_definition ahshagd ON ad.id_agenda_segment_attribute_group_definition = ahshagd.id
         JOIN agenda_has_segment_definition ahsd ON ahshagd.id_agenda_has_segment_definition = ahsd.id
         JOIN agenda a ON ahsd.id_agenda = a.id
         JOIN agenda_segment aseg ON ahsd.id_agenda_segment = aseg.id
         JOIN agenda_attribute_group aag ON ahshagd.id_agenda_attribute_group = aag.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN attribute_data_type attdt ON att.id_attribute_data_type = attdt.id
WHERE ad.enabled = TRUE
  AND ahshagd.enabled = TRUE
  AND ahsd.enabled = TRUE
  AND a.enabled = TRUE
  AND aseg.enabled = TRUE
  AND aag.enabled = TRUE
  AND att.enabled = TRUE
  AND attdt.enabled = TRUE;

CREATE UNIQUE INDEX mv_agenda_definition_id_idx
    ON mv_agenda_definition (id);


CREATE MATERIALIZED VIEW flexii.mv_api_agenda_mapping AS
SELECT
    aapthpa.id
     , aapt.id AS id_template
     , aapt.name AS template_name
     , ad.id_agenda
     , aapa.name AS template_attribute_name
     , ad.id AS id_agenda_definition
     , aapam.id AS id_page_attribute_mapping
     , aadp.id_external_application
FROM api_agenda_page_template aapt
         JOIN api_agenda_page_template_has_page_attribute aapthpa ON aapt.id = aapthpa.id_api_agenda_page_template
         JOIN api_agenda_page_attribute aapa ON aapa.id = aapthpa.id_api_agenda_page_attribute
         JOIN api_agenda_page_attribute_mapping aapam ON aapthpa.id = aapam.id_api_agenda_page_template_has_page_attribute
         JOIN api_agenda_definition_publish aadp ON aadp.id = aapam.id_api_agenda_definition_publish
         JOIN mv_agenda_definition ad ON ad.id = aadp.id_agenda_definition
WHERE aapt.enabled = TRUE
  AND aapthpa.enabled = TRUE
  AND aapa.enabled = TRUE
  AND aapam.enabled = TRUE
  AND aadp.enabled = TRUE;

CREATE VIEW flexii.v_agenda_entity_title AS
SELECT final.id_agenda_entity,
       STRING_AGG(final.value::text, ' '::text ORDER BY final.in_title_and_priority) AS title,
       ae.uid,
       ae.id_agenda,
       ae.enabled,
       ae.is_anonymized,
       ae.id_appuser_created_by,
       ae.id_appuser_updated_by,
       ae.dt_ins,
       ae.dt_upd
FROM (SELECT val.value,
             val.id_agenda_entity,
             ad.in_title_and_priority
      FROM (SELECT val_1.value,
                   val_1.id_agenda_definition,
                   val_1.id_agenda_entity
            FROM agenda_values val_1
            WHERE (val_1.id_agenda_definition IN (SELECT ad_1.id
                                                  FROM mv_agenda_definition ad_1
                                                           JOIN agenda_has_segment_definition ahsd ON ad_1.id_agenda_has_segment_definition = ahsd.id
                                                  WHERE ad_1.enabled = TRUE
                                                    AND ahsd.enabled = TRUE
                                                    AND ad_1.in_title_and_priority IS NOT NULL
                                                    AND ahsd.is_main = TRUE))) val
               JOIN agenda_definition ad ON val.id_agenda_definition = ad.id
      ORDER BY ad.in_title_and_priority) final
         JOIN agenda_entity ae ON final.id_agenda_entity = ae.id
GROUP BY final.id_agenda_entity, ae.uid, ae.id_agenda, ae.enabled, ae.is_anonymized, ae.id_appuser_created_by, ae.id_appuser_updated_by, ae.dt_ins,
         ae.dt_upd;


CREATE OR REPLACE VIEW flexii.v_agenda_definition_specification_by_value AS
SELECT ad.id_agenda,
       att.name       AS name_definition,
       ad.is_not_null AS not_null_definition,
       adsbv.is_hidden,
       adsbv.is_not_null,
       adsbv.recurring_sequence,
       att_control.name,
       adsbv.value_control
FROM agenda_definition_specification_by_value adsbv
         JOIN mv_agenda_definition ad ON adsbv.id_agenda_definition = ad.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN mv_agenda_definition ad_control ON adsbv.id_agenda_definition_control = ad_control.id
         JOIN attribute att_control ON ad_control.id_attribute = att_control.id;


CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , ad.id_agenda
     , id_agenda_segment
     , id_agenda_attribute_group
     , id_attribute
     , agenda_name
     , segment_name
     , attribute_group_name
     , attribute_name
FROM (SELECT id_user_group,
             user_name,
             id_agenda_definition,
             is_read,
             is_write
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughad.id_agenda_definition, ughad.is_write, ughad.is_read
            FROM base.user_group ug
                     JOIN user_group_has_agenda_definition ughad ON ug.id = ughad.id_user_group
                     JOIN mv_agenda_definition ad ON ad.id = ughad.id_agenda_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughad.enabled = TRUE
           ) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, ad.id AS id_agenda_definition, TRUE AS is_read, TRUE AS is_write
       FROM base.user_group ug,
            mv_agenda_definition ad
       WHERE ug.enabled = TRUE
         AND ad.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, ad.id) NOT IN (SELECT id_user_group, id_agenda_definition
                                    FROM user_group_has_agenda_definition ughad
                                    WHERE enabled = TRUE)
      )
     ) base
         JOIN mv_agenda_definition ad ON base.id_agenda_definition = ad.id
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = ad.id_agenda;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_has_segment_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , base.id_agenda
     , id_agenda_segment
FROM (SELECT id_user_group,
             user_name,
             id_agenda_has_segment_definition,
             is_read,
             is_write,
             id_agenda,
             id_agenda_segment
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_definition, ughasd.is_read, ughasd.is_write, ahsd.id_agenda, ahsd.id_agenda_segment
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ughasd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_definition, TRUE AS is_read, TRUE AS is_write, def.id_agenda, def.id_agenda_segment
       FROM base.user_group ug,
            agenda_has_segment_definition def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_definition
                                     FROM user_group_has_agenda_has_segment_definition ughad
                                     WHERE enabled = TRUE))) base
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = base.id_agenda;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_group_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_has_segment_has_attribute_group_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , id_agenda_attribute_group
     , id_agenda_segment
     , base.id_agenda
FROM (SELECT id_user_group,
             user_name,
             id_agenda_has_segment_has_attribute_group_definition,
             is_read,
             is_write
              , id_agenda_attribute_group
              , id_agenda_segment
              , id_agenda
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_has_attribute_group_definition, ughasd.is_read, ughasd.is_write, ahshagd.id_agenda_attribute_group, ahsd.id_agenda_segment, ahsd.id_agenda
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_group_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_has_attribute_group_definition ahshagd ON ughasd.id_agenda_has_segment_has_attribute_group_definition = ahshagd.id
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_has_attribute_group_definition, TRUE AS is_read, TRUE AS is_write, def.id_agenda_attribute_group, def.id_agenda_segment, def.id_agenda
       FROM base.user_group ug,
            (SELECT ahshagd.*, ahsd.id_agenda, ahsd.id_agenda_segment
             FROM agenda_has_segment_has_attribute_group_definition ahshagd
                      JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            ) def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_has_attribute_group_definition
                                     FROM user_group_has_agenda_has_segment_group_definition ughad
                                     WHERE enabled = TRUE))) base
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = base.id_agenda;

create materialized view flexii.mv_user_group_has_agenda_permission as
select type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_agenda_definition, id_user_group, is_read, is_write
from (
         select
             'definition' as type,
             ughad.id_agenda,
             ughad.id_agenda_segment,
             ughad.id_agenda_attribute_group,
             ughad.id_attribute,
             ughad.id_agenda_definition,
             ughad.id_user_group as id_user_group,
             ughad.is_read,
             ughad.is_write
         from flexii.mv_user_group_has_agenda_definition ughad
         union all
         select
             'segment' as type,
             ughahsd.id_agenda,
             ughahsd.id_agenda_segment,
             null,
             null,
             null,
             ughahsd.id_user_group as id_user_group,
             ughahsd.is_read,
             ughahsd.is_write
         from flexii.mv_user_group_has_agenda_has_segment_definition ughahsd
         union all
         select
             'group' as type,
             ughahsgd.id_agenda,
             ughahsgd.id_agenda_segment,
             ughahsgd.id_agenda_attribute_group,
             null,
             null,
             ughahsgd.id_user_group as id_user_group,
             ughahsgd.is_read,
             ughahsgd.is_write
         from flexii.mv_user_group_has_agenda_has_segment_group_definition ughahsgd
     ) definitions;

CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission ON mv_user_group_has_agenda_permission (type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_user_group);
CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission_def ON mv_user_group_has_agenda_permission (type, id_agenda_definition, id_user_group);

-- version
update configuration.configuration_values set value = '2.6.5.4'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.5.4
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.6.5.5'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.5.5
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.6.5.6'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.5.6
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.6.5.7'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.5.7
-- SQL for this version ends here
----------------------------------------------------
-- RUN THIS ON MIGRATION
INSERT INTO configuration.configuration_key (name, data_type, enabled) VALUES ('two_factor_forced', 'bool', true);

INSERT INTO configuration.configuration_definition (name, id_configuration_group, id_configuration_key, is_read_only, is_not_null, default_value,
                                      example, sequence_mandatory, note, priority)
VALUES ('two_factor_forced', (SELECT id FROM configuration.configuration_group WHERE name = 'general'),
        (SELECT id FROM configuration.configuration_key WHERE name = 'two_factor_forced'), FALSE, FALSE, '0', NULL, NULL, 'Vynucení dvoufaktorové autorizace', 80);

-- version
update configuration.configuration_values set value = '2.6.5.8'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.5.8
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.6.5.9'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.5.9
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.6.5.10'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.5.10
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.6.5.11'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.5.11
-- SQL for this version ends here
----------------------------------------------------

alter table custom_import_row add agenda_entity_uid int default NULL;

alter table custom_import_row drop constraint fk_custom_import_row_custom_import_id;

alter table custom_import_row
    add constraint fk_custom_import_row_custom_import_id
        foreign key (id_custom_import) references custom_import
            on delete cascade;

alter table log_deleted_entity alter column title drop not null;

-- version
update configuration.configuration_values set value = '2.6.6.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.6.0
-- SQL for this version ends here
----------------------------------------------------

CREATE TABLE flexii.user_group_has_agenda_has_agenda
(
    id                   SERIAL                              NOT NULL
        CONSTRAINT pk_user_group_has_agenda_has_agenda
            PRIMARY KEY,
    id_user_group        integer                             NOT NULL
        CONSTRAINT fk_user_group_has_agenda_has_agenda_user_group_id
            REFERENCES base.user_group,
    id_agenda_has_agenda integer                             NOT NULL
        CONSTRAINT fk_user_group_has_agenda_has_agenda_aha_id
            REFERENCES agenda_has_agenda,
    is_can_read          bool                                NOT NULL,
    is_can_write         bool                                NOT NULL,
    is_can_create        bool                                NOT NULL,
    is_can_delete        bool                                NOT NULL,
    enabled              bool      DEFAULT TRUE              NOT NULL,
    dt_ins               timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd               timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER tr_user_group_has_agenda_has_agenda_dtu
    BEFORE UPDATE
    ON flexii.user_group_has_agenda_has_agenda
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

create unique index unq_user_group_has_agenda_has_agenda_main
    on user_group_has_agenda_has_agenda (id_user_group desc, id_agenda_has_agenda asc);

-- DROP MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_agenda;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_agenda AS
SELECT id_user_group, id_agenda_has_agenda, is_can_read, is_can_write, is_can_create, is_can_delete
FROM user_group_has_agenda_has_agenda
WHERE enabled = TRUE
UNION
SELECT grp.id AS id_user_group
     , a.id   AS id_agenda_has_agenda
     , false  AS is_can_read
     , FALSE  AS is_can_write
     , FALSE  AS is_can_create
     , FALSE  AS is_can_delete
FROM user_group grp,
     agenda_has_agenda a
WHERE (grp.id, a.id) NOT IN (SELECT id_user_group, id_agenda_has_agenda FROM user_group_has_agenda_has_agenda WHERE enabled = TRUE);

INSERT INTO menu_section (name, icon, priority) VALUES('permission', NULL, 115);

-- admin menu
INSERT INTO menu_link (name, presenter, action, parameters) VALUES('permission_user_group_aha', 'Agenda:Permission', 'userGroupToAha', NULL);

INSERT INTO menu_item (id_menu_section, id_agenda, "name", icon, id_menu_link, priority, is_default, is_separator_before, is_for_admin)
VALUES((SELECT id FROM menu_section WHERE name = 'permission'), NULL, 'permission_user_group_aha', 'fad fa-link', (SELECT id FROM menu_link WHERE name = 'permission_user_group_aha'), 50, false, false, true);

UPDATE menu_link SET presenter = 'Agenda:Permission' WHERE name = 'admin_user_group_agenda';
UPDATE menu_item SET id_menu_section = (SELECT id FROM menu_section WHERE name = 'permission') WHERE name IN ('admin_users', 'admin_users_ldap', 'admin_user_to_group', 'admin_user_group', 'admin_user_group_agenda');

-- version
update configuration.configuration_values set value = '2.6.6.1'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.6.1
-- SQL for this version ends here
----------------------------------------------------
-- version
update configuration.configuration_values set value = '2.6.6.2'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.6.2
-- SQL for this version ends here
----------------------------------------------------
-- version
update configuration.configuration_values set value = '2.6.6.3'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.6.3
-- SQL for this version ends here
----------------------------------------------------

-- version
update configuration.configuration_values set value = '2.6.6.4'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.6.4
-- SQL for this version ends here
----------------------------------------------------


drop materialized view if exists flexii.mv_user_group_has_agenda_permission;
drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_definition;
drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_group_definition;
drop materialized view if exists flexii.mv_user_group_has_agenda_definition;


CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_definition
     , base.is_read
     , base.is_write
     , ad.id_agenda
     , id_agenda_segment
     , id_agenda_attribute_group
     , id_attribute
     , agenda_name
     , segment_name
     , attribute_group_name
     , attribute_name
FROM (SELECT id_user_group,
             user_name,
             id_agenda_definition,
             is_read,
             is_write
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughad.id_agenda_definition, ughad.is_write, ughad.is_read
            FROM base.user_group ug
                     JOIN user_group_has_agenda_definition ughad ON ug.id = ughad.id_user_group
                     JOIN mv_agenda_definition ad ON ad.id = ughad.id_agenda_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughad.enabled = TRUE
           ) p_set
      UNION
      (SELECT ug.id AS id_user_group, ug.name AS user_name, ad.id AS id_agenda_definition, TRUE AS is_read, TRUE AS is_write
       FROM base.user_group ug,
            mv_agenda_definition ad
       WHERE ug.enabled = TRUE
         AND ad.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, ad.id) NOT IN (SELECT id_user_group, id_agenda_definition
                                    FROM user_group_has_agenda_definition ughad
                                    WHERE enabled = TRUE)
      )
     ) base
         JOIN mv_agenda_definition ad ON base.id_agenda_definition = ad.id;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_has_segment_definition
     , base.is_read
     , base.is_write
     , base.id_agenda
     , id_agenda_segment
FROM (SELECT id_user_group,
             user_name,
             id_agenda_has_segment_definition,
             is_read,
             is_write,
             id_agenda,
             id_agenda_segment
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_definition, ughasd.is_read, ughasd.is_write, ahsd.id_agenda, ahsd.id_agenda_segment
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ughasd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_definition, TRUE AS is_read, TRUE AS is_write, def.id_agenda, def.id_agenda_segment
       FROM base.user_group ug,
            agenda_has_segment_definition def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_definition
                                     FROM user_group_has_agenda_has_segment_definition ughad
                                     WHERE enabled = TRUE))) base;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_group_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_has_segment_has_attribute_group_definition
     , base.is_read
     , base.is_write
     , id_agenda_attribute_group
     , id_agenda_segment
     , base.id_agenda
FROM (SELECT id_user_group,
             user_name,
             id_agenda_has_segment_has_attribute_group_definition,
             is_read,
             is_write
              , id_agenda_attribute_group
              , id_agenda_segment
              , id_agenda
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_has_attribute_group_definition, ughasd.is_read, ughasd.is_write, ahshagd.id_agenda_attribute_group, ahsd.id_agenda_segment, ahsd.id_agenda
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_group_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_has_attribute_group_definition ahshagd ON ughasd.id_agenda_has_segment_has_attribute_group_definition = ahshagd.id
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_has_attribute_group_definition, TRUE AS is_read, TRUE AS is_write, def.id_agenda_attribute_group, def.id_agenda_segment, def.id_agenda
       FROM base.user_group ug,
            (SELECT ahshagd.*, ahsd.id_agenda, ahsd.id_agenda_segment
             FROM agenda_has_segment_has_attribute_group_definition ahshagd
                      JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            ) def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_has_attribute_group_definition
                                     FROM user_group_has_agenda_has_segment_group_definition ughad
                                     WHERE enabled = TRUE))) base;

create materialized view flexii.mv_user_group_has_agenda_permission as
select type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_agenda_definition, id_user_group, is_read, is_write
from (
         select
             'definition' as type,
             ughad.id_agenda,
             ughad.id_agenda_segment,
             ughad.id_agenda_attribute_group,
             ughad.id_attribute,
             ughad.id_agenda_definition,
             ughad.id_user_group as id_user_group,
             ughad.is_read,
             ughad.is_write
         from flexii.mv_user_group_has_agenda_definition ughad
         union all
         select
             'segment' as type,
             ughahsd.id_agenda,
             ughahsd.id_agenda_segment,
             null,
             null,
             null,
             ughahsd.id_user_group as id_user_group,
             ughahsd.is_read,
             ughahsd.is_write
         from flexii.mv_user_group_has_agenda_has_segment_definition ughahsd
         union all
         select
             'group' as type,
             ughahsgd.id_agenda,
             ughahsgd.id_agenda_segment,
             ughahsgd.id_agenda_attribute_group,
             null,
             null,
             ughahsgd.id_user_group as id_user_group,
             ughahsgd.is_read,
             ughahsgd.is_write
         from flexii.mv_user_group_has_agenda_has_segment_group_definition ughahsgd
     ) definitions;

CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission ON mv_user_group_has_agenda_permission (type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_user_group);
CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission_def ON mv_user_group_has_agenda_permission (type, id_agenda_definition, id_user_group);


-- #14252
alter table agenda_has_agenda_values drop column recurring_sequence;

-- #14259
alter table user_group_has_definition_value_for_aha add is_primary bool default NULL;

alter table user_group_has_agenda_has_segment_group_definition
    drop constraint fk_user_group_has_agenda_has_segment_group_definition_segment;

alter table user_group_has_agenda_has_segment_group_definition
    add constraint fk_user_group_has_agenda_has_segment_group_definition_segment
        foreign key (id_agenda_has_segment_has_attribute_group_definition) references agenda_has_segment_has_attribute_group_definition;


-- version
update configuration.configuration_values set value = '2.6.7.0'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.7.0
-- SQL for this version ends here
----------------------------------------------------
-- version
update configuration.configuration_values set value = '2.6.7.1'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.7.1
-- SQL for this version ends here
----------------------------------------------------
-- version
update configuration.configuration_values set value = '2.6.7.2'
where id_configuration_definition = (
    SELECT ID FROM configuration.configuration_definition WHERE name = 'db_version'
);

----------------------------------------------------
-- 2.6.7.2
-- SQL for this version ends here
----------------------------------------------------

--- ALREADY AT
create index idx_agenda_values_id_agenda_definition_value
    on agenda_values (id_agenda_definition, value);

CREATE INDEX idx_master_lookup_lookup_name_name
    ON master_lookup (lookup_name ASC, name DESC);

CREATE INDEX idx_mv_agenda_definition_full_unique_name
    ON mv_agenda_definition (full_unique_name ASC);

CREATE INDEX idx_mv_agenda_definition_main_name_index
    ON mv_agenda_definition (agenda_name ASC, segment_name ASC, attribute_group_name ASC, attribute_name ASC);

create index idx_agenda_entity_has_agenda_entity_aha_and_entity_id
    on agenda_entity_has_agenda_entity (id_agenda_has_agenda asc, id_agenda_entity desc);

create index idx_agenda_entity_has_agenda_entity_aha_and_entity_linked_id
    on agenda_entity_has_agenda_entity (id_agenda_has_agenda asc, id_agenda_entity_linked desc);

----------------------------------------------------
-- 2.6.7.5
-- SQL for this version ends here
----------------------------------------------------

alter table agenda add is_editable bool default TRUE not null;

-- #14276
alter table agenda_has_agenda add is_limited_by_permission bool default FALSE not null;
alter table agenda_has_agenda add is_limited_by_permission_linked bool default FALSE not null;

----------------------------------------------------
-- 2.6.8.0
-- SQL for this version ends here
----------------------------------------------------

-- #14327
DROP MATERIALIZED VIEW flexii.mv_user_group_has_agenda_permission;
DROP MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_group_definition;
DROP MATERIALIZED VIEW flexii.mv_user_group_has_agenda_definition;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_group_definition AS
SELECT base.id_user_group
     , group_name
     , id_agenda_has_segment_has_attribute_group_definition
     , base.is_read
     , base.is_write
     , id_agenda_attribute_group
     , id_agenda_segment
     , base.id_agenda
FROM (SELECT id_user_group
           , group_name
           , id_agenda_has_segment_has_attribute_group_definition
           , is_read
           , is_write
           , id_agenda_attribute_group
           , id_agenda_segment
           , id_agenda
      FROM (SELECT ug.id   AS id_user_group,
                   ug.name AS group_name,
                   ughasd.id_agenda_has_segment_has_attribute_group_definition,
                   ughasd.is_read,
                   ughasd.is_write,
                   ahshagd.id_agenda_attribute_group,
                   ahsd.id_agenda_segment,
                   ahsd.id_agenda
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_group_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_has_attribute_group_definition ahshagd
                          ON ughasd.id_agenda_has_segment_has_attribute_group_definition = ahshagd.id
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION
      SELECT grp.id_user_group,
             grp.group_name,
             id_agenda_has_segment_has_attribute_group_definition,
             ughahs.is_read,
             ughahs.is_write,
             grp.id_agenda_attribute_group,
             grp.id_agenda_segment,
             grp.id_agenda
      FROM ((SELECT ug.id   AS id_user_group,
                    ug.name AS group_name,
                    def.id  AS id_agenda_has_segment_has_attribute_group_definition,
                    TRUE    AS is_read,
                    TRUE    AS is_write,
                    def.id_agenda_attribute_group,
                    def.id_agenda_segment,
                    def.id_agenda
             FROM base.user_group ug,
                  (SELECT ahshagd.*, ahsd.id_agenda, ahsd.id_agenda_segment
                   FROM agenda_has_segment_has_attribute_group_definition ahshagd
                            JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition) def
             WHERE ug.enabled = TRUE
               AND def.enabled = TRUE
               AND ug.is_admin_group = FALSE
               AND ug.is_superadmin_group = FALSE
               AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_has_attribute_group_definition
                                           FROM user_group_has_agenda_has_segment_group_definition ughad
                                           WHERE enabled = TRUE))) grp
               JOIN flexii.mv_user_group_has_agenda_has_segment_definition ughahs ON ughahs.id_user_group = grp.id_user_group
          AND ughahs.id_agenda = grp.id_agenda
          AND ughahs.id_agenda_segment = grp.id_agenda_segment
     ) base;


CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_definition AS
SELECT base.id_user_group
     , group_name
     , id_agenda_definition
     , base.is_read
     , base.is_write
     , ad.id_agenda
     , id_agenda_segment
     , id_agenda_attribute_group
     , id_attribute
     , agenda_name
     , segment_name
     , attribute_group_name
     , attribute_name
FROM (SELECT id_user_group,
             group_name,
             id_agenda_definition,
             is_read,
             is_write
      FROM (SELECT ug.id AS id_user_group, ug.name AS group_name, ughad.id_agenda_definition, ughad.is_write, ughad.is_read
            FROM base.user_group ug
                     JOIN user_group_has_agenda_definition ughad ON ug.id = ughad.id_user_group
                     JOIN mv_agenda_definition ad ON ad.id = ughad.id_agenda_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughad.enabled = TRUE
           ) p_set
      UNION
      SELECT def.id_user_group, def.group_name, def.id_agenda_definition, ughahsgd.is_read, ughahsgd.is_write
      FROM (SELECT ug.id AS id_user_group, ug.name AS group_name, ad.id AS id_agenda_definition, ad.id_agenda, ad.id_agenda_attribute_group, ad.id_agenda_segment
            FROM base.user_group ug,
                 mv_agenda_definition ad
            WHERE ug.enabled = TRUE
              AND ad.enabled = TRUE
              AND ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND (ug.id, ad.id) NOT IN (SELECT id_user_group, id_agenda_definition
                                         FROM user_group_has_agenda_definition ughad
                                         WHERE enabled = TRUE)
           ) def JOIN flexii.mv_user_group_has_agenda_has_segment_group_definition ughahsgd ON ughahsgd.id_user_group = def.id_user_group AND ughahsgd.id_agenda = def.id_agenda
          AND ughahsgd.id_agenda_attribute_group = def.id_agenda_attribute_group AND ughahsgd.id_agenda_segment = def.id_agenda_segment
     ) base
         JOIN mv_agenda_definition ad ON base.id_agenda_definition = ad.id;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_permission AS
select type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_agenda_definition, id_user_group, is_read, is_write
from (
         select
             'definition' as type,
             ughad.id_agenda,
             ughad.id_agenda_segment,
             ughad.id_agenda_attribute_group,
             ughad.id_attribute,
             ughad.id_agenda_definition,
             ughad.id_user_group as id_user_group,
             ughad.is_read,
             ughad.is_write
         from flexii.mv_user_group_has_agenda_definition ughad
         union all
         select
             'segment' as type,
             ughahsd.id_agenda,
             ughahsd.id_agenda_segment,
             null,
             null,
             null,
             ughahsd.id_user_group as id_user_group,
             ughahsd.is_read,
             ughahsd.is_write
         from flexii.mv_user_group_has_agenda_has_segment_definition ughahsd
         union all
         select
             'group' as type,
             ughahsgd.id_agenda,
             ughahsgd.id_agenda_segment,
             ughahsgd.id_agenda_attribute_group,
             null,
             null,
             ughahsgd.id_user_group as id_user_group,
             ughahsgd.is_read,
             ughahsgd.is_write
         from flexii.mv_user_group_has_agenda_has_segment_group_definition ughahsgd
     ) definitions;

CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission ON mv_user_group_has_agenda_permission (type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_user_group);
CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission_def ON mv_user_group_has_agenda_permission (type, id_agenda_definition, id_user_group);

----------------------------------------------------
-- 2.6.8.2
-- SQL for this version ends here
----------------------------------------------------

-- #14376
alter table agenda_has_file
    alter column original_name type varchar(400) using original_name::varchar(400);

alter table agenda_has_file
    alter column file_name type varchar(400) using file_name::varchar(400);

----------------------------------------------------
-- 2.6.8.4
-- SQL for this version ends here
----------------------------------------------------

-- #14270
alter table migration.external_system_entity_mapping alter column id_external_system type text using id_external_system::text;

-- #14321
create table flexii.agenda_entity_segment_lock
(
    id                serial
        constraint pk_agenda_entity_segment_lock
            primary key,
    id_agenda_entity  integer                             not null
        constraint fk_agenda_entity_segment_lock_agenda_entity_id
            references agenda_entity,
    id_agenda_segment integer                             not null
        constraint fk_agenda_entity_segment_lock_agenda_segment_id
            references agenda_segment,
    is_locked         bool      default TRUE              not null,
    enabled           bool      default true              not null,
    dt_ins            timestamp default current_timestamp not null,
    constraint unq_agenda_entity_segment_lock_entity_segment
        unique (id_agenda_entity, id_agenda_segment)
);

create table flexii.agenda_entity_attribute_group_lock
(
    id                serial
        constraint pk_agenda_entity_attribute_group_lock
            primary key,
    id_agenda_entity  integer                             not null
        constraint fk_agenda_entity_attribute_group_lock_agenda_entity_id
            references agenda_entity,
    id_agenda_has_segment_has_attribute_group_definition integer             not null
        constraint fk_agenda_entity_attribute_group_lock_agenda_group_segment_id
            references agenda_has_segment_has_attribute_group_definition,
    is_locked         bool      default TRUE              not null,
    enabled           bool      default true              not null,
    dt_ins            timestamp default current_timestamp not null,
    constraint unq_agenda_entity_attribute_group_lock_entity_agenda_grp_seg
        unique (id_agenda_entity, id_agenda_has_segment_has_attribute_group_definition)
);



-- #13801
ALTER TABLE agenda_has_agenda_show_attribute
    ADD COLUMN datagrid_order_priority SMALLINT,
    ADD COLUMN datagrid_order_by VARCHAR(4);

ALTER TABLE agenda_has_agenda_definition
    ADD COLUMN datagrid_order_priority SMALLINT,
    ADD COLUMN datagrid_order_by VARCHAR(4);

ALTER TABLE agenda_has_agenda
    ADD COLUMN uid_datagrid_order_priority SMALLINT,
    ADD COLUMN uid_datagrid_order_by VARCHAR(4),
    ADD COLUMN name_datagrid_order_priority SMALLINT,
    ADD COLUMN name_datagrid_order_by VARCHAR(4);

-- #14187
CREATE TABLE flexii.definition_key
(
    id            serial                              NOT NULL
        CONSTRAINT pk_definition_key
            PRIMARY KEY,
    name          varchar(50)                         NOT NULL,
    table_name    varchar(50)                         NOT NULL,
    data_type     varchar(20)                         NOT NULL,
    default_value varchar(400)                        ,
    enabled       bool      DEFAULT TRUE              NOT NULL,
    dt_ins        timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

create unique index unq_definition_key_main
    on definition_key (name desc, table_name asc);

INSERT INTO definition_key (name, table_name, data_type, default_value) VALUES ('is_static_list', 'agenda_definition', 'bool', NULL);
INSERT INTO definition_key (name, table_name, data_type, default_value) VALUES ('is_static_list_with_one_default_value', 'agenda_definition', 'bool', NULL);

CREATE TABLE flexii.definition_table_value
(
    id                serial                              NOT NULL
        CONSTRAINT pk_definition_table_value
            PRIMARY KEY,
    id_definition_key integer                             NOT NULL
        CONSTRAINT fk_definition_table_value_definition_key_id
            REFERENCES definition_key,
    id_definition     integer                             NOT NULL,
    value             varchar(400),
    enabled           bool      DEFAULT TRUE              NOT NULL,
    dt_ins            timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd            timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

create unique index unq_definition_table_value_main
    on definition_table_value (id_definition desc, id_definition_key asc);

CREATE TRIGGER tr_definition_table_value_dtu
    BEFORE UPDATE
    ON flexii.definition_table_value
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

-- #14187
CREATE MATERIALIZED VIEW flexii.mv_agenda_definition_property AS
SELECT id_agenda_definition, is_static_list, is_static_list_with_one_default_value
FROM crosstab($$SELECT id_agenda_definition, core.name, CASE WHEN core.default_value IS NULL THEN dtv.value ELSE core.default_value END AS value
FROM (SELECT ad.id AS id_agenda_definition, dk.id AS id_definition_key, dk.name, dk.default_value
      FROM (SELECT id
            FROM agenda_definition
            WHERE enabled = TRUE) ad,
           (SELECT id, name, default_value FROM definition_key WHERE table_name = 'agenda_definition' AND enabled = TRUE) dk) core
         LEFT JOIN definition_table_value dtv ON dtv.id_definition = core.id_agenda_definition AND core.id_definition_key = dtv.id_definition_key ORDER BY id_agenda_definition, name$$,
              $$VALUES ('is_static_list'), ('is_static_list_with_one_default_value')$$) AS t (id_agenda_definition int, is_static_list bool,
                                                                                              is_static_list_with_one_default_value bool);

CREATE UNIQUE INDEX mv_agenda_definition_property_idx
    ON flexii.mv_agenda_definition_property (id_agenda_definition);

-- #14187
alter table agenda_has_agenda add is_search_limited_by_permission bool default true not null;
alter table agenda_has_agenda add is_search_limited_by_permission_linked bool default true not null;

----------------------------------------------------
-- 2.6.9.0
-- SQL for this version ends here
----------------------------------------------------
-- #14407
INSERT INTO flexii.definition_key (name, table_name, data_type, default_value) VALUES ('is_agenda_lookup_restricted_by_permission', 'agenda_definition', 'bool', 'true');

DROP MATERIALIZED VIEW mv_agenda_definition_property;

CREATE MATERIALIZED VIEW flexii.mv_agenda_definition_property AS
SELECT id_agenda_definition,
       is_static_list,
       is_static_list_with_one_default_value,
       is_agenda_lookup_restricted_by_permission
FROM crosstab('SELECT id_agenda_definition, core.name, CASE WHEN dtv.id IS NOT NULL THEN dtv.value ELSE core.default_value END AS value
FROM (SELECT ad.id AS id_agenda_definition, dk.id AS id_definition_key, dk.name, dk.default_value
      FROM (SELECT id
            FROM agenda_definition
            WHERE enabled = TRUE) ad,
           (SELECT id, name, default_value FROM definition_key WHERE table_name = ''agenda_definition'' AND enabled = TRUE) dk) core
         LEFT JOIN definition_table_value dtv ON dtv.id_definition = core.id_agenda_definition AND core.id_definition_key = dtv.id_definition_key ORDER BY id_agenda_definition, name'::text,
              'VALUES (''is_static_list''), (''is_static_list_with_one_default_value''), (''is_agenda_lookup_restricted_by_permission'')'::text) t(id_agenda_definition integer,
                                                                                                  is_static_list boolean, is_static_list_with_one_default_value boolean, is_agenda_lookup_restricted_by_permission boolean);

CREATE UNIQUE INDEX mv_agenda_definition_property_idx
    ON flexii.mv_agenda_definition_property (id_agenda_definition);

----------------------------------------------------
-- 2.6.9.2
-- SQL for this version ends here
----------------------------------------------------


-- #14179
create table base.bse_lookup_system_message_type
(
    id          serial                              not null
        constraint pk_bse_lookup_system_message_type
            primary key,
    name        varchar(50)                         not null
        constraint unq_bse_lookup_system_message_type_name
            unique,
    description varchar(50)                         not null,
    priority    smallint                            not null
        constraint unq_bse_lookup_system_message_type_priority
            unique,
    enabled     bool      default TRUE              not null,
    dt_ins      timestamp default current_timestamp not null,
    dt_upd      timestamp default current_timestamp not null
);

CREATE TRIGGER tr_bse_lookup_system_message_type_dtu
    BEFORE UPDATE
    ON base.bse_lookup_system_message_type
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

CREATE TABLE base.bse_system_message
(
    id                     serial                              NOT NULL
        CONSTRAINT pk_bse_system_message
            PRIMARY KEY,
    id_system_message_type int                                 NOT NULL
        CONSTRAINT fk_bse_system_message_lookup_system_message_type_id
            REFERENCES bse_lookup_system_message_type,
    dt_from                timestamp                           NOT NULL,
    dt_to                  timestamp                           NOT NULL,
    enabled                bool      DEFAULT TRUE              NOT NULL,
    dt_ins                 timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd                 timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER tr_bse_system_message_dtu
    BEFORE UPDATE
    ON base.bse_system_message
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

CREATE TABLE base.bse_system_message_locale
(
    id                     serial                              NOT NULL
        CONSTRAINT pk_bse_system_message_locale
            PRIMARY KEY,
    id_system_message              int                                 NOT NULL
        CONSTRAINT fk_bse_system_message_locale_bse_system_message_id
            REFERENCES bse_system_message,
    id_locale              int                                 NOT NULL
        CONSTRAINT fk_bse_system_message_locale_id
            REFERENCES locale,
    message                varchar(200)                        NOT NULL,
    dt_ins                 timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd                 timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

alter table bse_system_message_locale
    add constraint unq_bse_system_message_locale_main
        unique (id_system_message, id_locale);

CREATE TRIGGER tr_bse_system_message_locale_dtu
    BEFORE UPDATE
    ON base.bse_system_message_locale
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

INSERT INTO base.bse_lookup_system_message_type (name, description, priority) VALUES ('info', 'Informace', 30);
INSERT INTO base.bse_lookup_system_message_type (name, description, priority) VALUES ('warning', 'Varování', 20);
INSERT INTO base.bse_lookup_system_message_type (name, description, priority) VALUES ('danger', 'Nebezpečí', 10);

INSERT INTO menu_link (name, presenter, action, parameters) VALUES('admin_system_message', 'Base:Admin', 'systemMessage', NULL);

INSERT INTO menu_item (id_menu_section, id_agenda, "name", icon, id_menu_link, priority, is_default, is_separator_before, is_for_admin)
VALUES((SELECT id FROM menu_section WHERE name = 'administration'), NULL, 'admin_system_message', 'fad fa-message-exclamation', (SELECT id FROM menu_link WHERE name = 'admin_system_message'), 600, false, true, true);

UPDATE menu_item SET is_separator_before = false WHERE name = 'system_process';

----------------------------------------------------
-- 2.6.10.0
-- SQL for this version ends here
----------------------------------------------------

-- #14427
alter table agenda_has_agenda_show_attribute_group
    add is_limited_by_permission bool default TRUE not null;

----------------------------------------------------
-- 2.6.10.1
-- SQL for this version ends here
----------------------------------------------------

INSERT INTO flexii.definition_key (name, table_name, data_type, default_value) VALUES ('is_datagrid_filter_key_as_lookup_name', 'agenda_has_agenda_definition', 'bool', 'false');

-- DROP MATERIALIZED VIEW mv_agenda_has_agenda_definition_property;

CREATE MATERIALIZED VIEW flexii.mv_agenda_has_agenda_definition_property AS
SELECT ids[1] AS id_agenda_has_agenda,
       ids[2] AS id_agenda_has_agenda_definition,
       is_datagrid_filter_key_as_lookup_name
FROM crosstab('SELECT ARRAY[id_agenda_has_agenda, id_agenda_has_agenda_definition]::int[] AS ids, core.name, CASE WHEN dtv.id IS NOT NULL THEN dtv.value ELSE core.default_value END AS value
FROM (SELECT ad.id AS id_agenda_has_agenda_definition, ad.id_agenda_has_agenda, dk.id AS id_definition_key, dk.name, dk.default_value
      FROM (SELECT id, id_agenda_has_agenda
            FROM agenda_has_agenda_definition
            WHERE enabled = TRUE) ad,
           (SELECT id, name, default_value FROM definition_key WHERE table_name = ''agenda_has_agenda_definition'' AND enabled = TRUE) dk) core
         LEFT JOIN definition_table_value dtv ON dtv.id_definition = core.id_agenda_has_agenda_definition AND core.id_definition_key = dtv.id_definition_key ORDER BY id_agenda_has_agenda_definition, name'::text,
              'VALUES (''is_datagrid_filter_key_as_lookup_name'')'::text) t(ids int[], is_datagrid_filter_key_as_lookup_name boolean);

CREATE UNIQUE INDEX mv_agenda_has_agenda_definition_property_idx
    ON flexii.mv_agenda_has_agenda_definition_property (id_agenda_has_agenda, id_agenda_has_agenda_definition);

-- #14511
CREATE TABLE agenda_definition_validation_by_operator
(
    id                          serial       NOT NULL,
    id_agenda_definition        INT          NOT NULL,
    operator                    VARCHAR(255) NOT NULL,
    id_agenda_definition_second INT,
    value                       VARCHAR(255),
    enabled                     BOOLEAN      NOT NULL DEFAULT TRUE,
    dt_ins                      timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    dt_upd                      timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE agenda_definition_validation_by_operator
    ADD constraint one_col_filled
        CHECK (
                (id_agenda_definition_second IS NOT NULL AND value IS NULL)
                OR
                (value IS NOT NULL AND id_agenda_definition_second IS NULL)
            );

CREATE OR REPLACE FUNCTION check_same_agenda_definitions() RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT COUNT(DISTINCT id_agenda)
        FROM mv_agenda_definition
        WHERE id = NEW.id_agenda_definition OR id = NEW.id_agenda_definition_second) > 1 THEN
        RAISE EXCEPTION 'More than one distinct agenda definition found.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER same_agenda_definitions_trigger
    BEFORE INSERT OR UPDATE ON agenda_definition_validation_by_operator
    FOR EACH ROW EXECUTE FUNCTION check_same_agenda_definitions();


ALTER TABLE agenda_definition_validation_by_operator
    ADD constraint valid_operators
        CHECK (
                operator IN ('=', '!=', '<', '>','>=','<=')
            );

----------------------------------------------------
-- 2.6.11.0
-- SQL for this version ends here
----------------------------------------------------

-- #14654
ALTER TABLE agenda_definition_validation_by_operator
    SET SCHEMA flexii;

ALTER FUNCTION check_same_agenda_definitions
    SET SCHEMA flexii;

alter table agenda_definition_validation_by_operator
    add constraint pk_agenda_definition_validation_by_operator_id
        primary key (id);

alter table agenda_definition_validation_by_operator
    add constraint fk_agenda_definition_validation_by_operator_ad_id
        foreign key (id_agenda_definition) references agenda_definition;

alter table agenda_definition_validation_by_operator
    add constraint fk_agenda_definition_validation_by_operator_ad_sec_id
        foreign key (id_agenda_definition_second) references agenda_definition;

----------------------------------------------------
-- 2.6.11.1
-- SQL for this version ends here
----------------------------------------------------

-- #13986
alter table base.user_group
    add is_system_group bool default FALSE not null;

CREATE TABLE flexii.agenda_definition_unique
(
    id               serial                              NOT NULL
        CONSTRAINT pk_agenda_definition_unique_id
            PRIMARY KEY,
    id_agenda        int                                 NOT NULL
        CONSTRAINT fk_agenda_definition_unique_agenda_id
            REFERENCES agenda,
    name             varchar(50)                         NOT NULL UNIQUE,
    description      varchar(200)                        NOT NULL UNIQUE,
    is_agenda_unique bool                                NOT NULL,
    is_entity_unique bool                                NOT NULL,
    enabled          bool                                NOT NULL DEFAULT TRUE,
    dt_ins           timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd           timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

alter table agenda_definition_unique
    add constraint chck_agenda_definition_unique_settings
        check ((is_agenda_unique = true OR is_entity_unique = true));

CREATE TRIGGER tr_agenda_definition_unique_dtu
    BEFORE UPDATE
    ON flexii.agenda_definition_unique
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();


CREATE TABLE flexii.agenda_definition_unique_definition
(
    id                          serial                              NOT NULL
        CONSTRAINT pk_agenda_definition_unique_definition_id
            PRIMARY KEY,
    id_agenda_definition_unique int                                 NOT NULL
        CONSTRAINT fk_adud_agenda_definition_unique_id
            REFERENCES agenda_definition,
    id_agenda_definition        int                                 NOT NULL
        CONSTRAINT fk_agenda_definition_unique_def_ad_id
            REFERENCES agenda_definition,
    enabled                     bool                                NOT NULL DEFAULT TRUE,
    dt_ins                      timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd                      timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

ALTER TABLE agenda_definition_unique_definition
    ADD CONSTRAINT unq_agenda_definition_unique_definition_main
        UNIQUE (id_agenda_definition_unique, id_agenda_definition);

CREATE TRIGGER tr_agenda_definition_unique_definition_dtu
    BEFORE UPDATE
    ON flexii.agenda_definition_unique_definition
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

--
DROP TRIGGER tr_agenda_entity_dt_upd ON flexii.agenda_entity;

CREATE OR REPLACE FUNCTION flexii.update_dt_upd_on_agenda_entity()
    RETURNS TRIGGER AS $$
BEGIN
    IF OLD.title IS DISTINCT FROM NEW.title THEN
        RETURN NEW;
    ELSE
        NEW.dt_upd = current_timestamp;
        RETURN NEW;
    END IF;
END;
    $$ LANGUAGE plpgsql;

CREATE TRIGGER tr_agenda_entity_dt_upd
    BEFORE UPDATE
    ON flexii.agenda_entity
    FOR EACH ROW
EXECUTE PROCEDURE flexii.update_dt_upd_on_agenda_entity();

DROP MATERIALIZED VIEW flexii.mv_agenda_has_agenda_definition_property;

CREATE MATERIALIZED VIEW flexii.mv_agenda_has_agenda_definition_property AS
SELECT ids[1] AS id_agenda_has_agenda,
       ids[2] AS id_agenda_has_agenda_definition,
       is_datagrid_filter_key_as_lookup_name
FROM crosstab('SELECT ARRAY[id_agenda_has_agenda, id_agenda_has_agenda_definition]::int[] AS ids, core.name, CASE WHEN dtv.id IS NOT NULL THEN dtv.value ELSE core.default_value END AS value
FROM (SELECT ad.id AS id_agenda_has_agenda_definition, ad.id_agenda_has_agenda, dk.id AS id_definition_key, dk.name, dk.default_value
      FROM (SELECT id, id_agenda_has_agenda
            FROM flexii.agenda_has_agenda_definition
            WHERE enabled = TRUE) ad,
           (SELECT id, name, default_value FROM definition_key WHERE table_name = ''agenda_has_agenda_definition'' AND enabled = TRUE) dk) core
         LEFT JOIN flexii.definition_table_value dtv ON dtv.id_definition = core.id_agenda_has_agenda_definition AND core.id_definition_key = dtv.id_definition_key ORDER BY id_agenda_has_agenda_definition, name'::text,
              'VALUES (''is_datagrid_filter_key_as_lookup_name'')'::text) t(ids int[], is_datagrid_filter_key_as_lookup_name boolean);

CREATE UNIQUE INDEX mv_agenda_has_agenda_definition_property_idx
    ON flexii.mv_agenda_has_agenda_definition_property (id_agenda_has_agenda, id_agenda_has_agenda_definition);

ALTER TABLE api_agenda_definition_publish DROP COLUMN id_appuser_created_by;
ALTER TABLE api_agenda_definition_publish DROP COLUMN id_appuser_updated_by;

----------------------------------------------------
-- 2.6.12.0
-- SQL for this version ends here
----------------------------------------------------

-- #14798
CREATE OR REPLACE FUNCTION flexii.check_agenda_definition_title_duplicity() RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT 1
              FROM agenda_has_segment_definition ahsd
                       JOIN agenda_has_segment_has_attribute_group_definition asagd ON ahsd.id = asagd.id_agenda_has_segment_definition
                       JOIN agenda_definition ad ON asagd.id = ad.id_agenda_segment_attribute_group_definition
              WHERE ahsd.id_agenda = (SELECT DISTINCT id_agenda
                                      FROM agenda_has_segment_definition
                                      WHERE id = (SELECT id_agenda_has_segment_definition
                                                  FROM agenda_has_segment_has_attribute_group_definition
                                                  WHERE id = NEW.id_agenda_segment_attribute_group_definition))
              AND ad.id != NEW.id
              AND ad.in_title_and_priority = NEW.in_title_and_priority) = 1 THEN
        RAISE EXCEPTION 'Duplicate IN_TITLE_AND_PRIORITY in agenda!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- DROP TRIGGER tgr_agenda_definition_title_duplicity ON flexii.agenda_definition;
CREATE TRIGGER tgr_agenda_definition_title_duplicity
    BEFORE INSERT OR UPDATE ON agenda_definition
    FOR EACH ROW EXECUTE FUNCTION flexii.check_agenda_definition_title_duplicity();

----------------------------------------------------
-- 2.6.13.0
-- SQL for this version ends here
----------------------------------------------------

-- #14864
DELETE
FROM agenda_values
WHERE id_agenda_definition IN (SELECT id
                               FROM mv_agenda_definition
                               WHERE id_agenda_attribute_group IN (SELECT id FROM agenda_attribute_group WHERE is_group_recurring = TRUE))
  AND recurring_sequence IS NULL;

----------------------------------------------------
-- 2.6.14.0
-- SQL for this version ends here
----------------------------------------------------

-- #14365
CREATE TABLE flexii.agenda_datagrid_default_filter
(
    id          serial                              NOT NULL
        CONSTRAINT pk_agenda_datagrid_default_filter_id
            PRIMARY KEY,
    id_agenda_definition   int                                 NOT NULL
        CONSTRAINT fk_agenda_datagrid_default_filter_agenda_definition_id
            REFERENCES agenda_definition,
    value       VARCHAR(400)                        NOT NULL,
    enabled     bool                                NOT NULL DEFAULT TRUE,
    dt_ins      timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    dt_upd      timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

ALTER TABLE agenda_datagrid_default_filter
    ADD CONSTRAINT unq_agenda_datagrid_default_filter_main
        UNIQUE (id_agenda_definition, value);

CREATE TRIGGER tr_agenda_datagrid_default_filter_dtu
    BEFORE UPDATE
    ON flexii.agenda_datagrid_default_filter
    FOR EACH ROW
EXECUTE PROCEDURE base.update_dt_upd();

alter table agenda_definition add column datagrid_priority int DEFAULT NULL;


DROP MATERIALIZED VIEW mv_api_agenda_mapping;
DROP VIEW v_agenda_entity_title;
DROP VIEW v_agenda_definition_specification_by_value;
drop materialized view if exists flexii.mv_user_group_has_agenda_permission;
drop materialized view if exists flexii.mv_user_group_has_agenda_definition;
drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_group_definition;
drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_definition;

-- before drop mv_agenda_definition, don't forget to drop view dv_agenda ...
DROP MATERIALIZED VIEW mv_agenda_definition;

CREATE MATERIALIZED VIEW flexii.mv_agenda_definition AS
SELECT ad.id,
       ahsd.id_agenda,
       ahsd.id_agenda_segment,
       ahshagd.id_agenda_attribute_group,
       ad.is_control_for_group,
       ad.id_attribute,
       ad.priority,
       ad.is_not_null,
       ad.is_hidden,
       ad.default_value,
       ad.is_disabled,
       ad.is_read_only,
       ad.tooltip,
       ad.field_column_length,
       ad.skip_column_length,
       ad.field_column_height,
       ad.in_title_and_priority,
       ad.datagrid_priority,
       ad.is_editable_in_datagrid,
       ad.is_hidden_in_datagrid,
       ad.datagrid_order_priority,
       ad.datagrid_order_by,
       ad.is_search_by,
       ad.is_show_in_linked_agenda,
       ad.is_unique,
       ad.min_length,
       ad.max_length,
       ad.min_value,
       ad.max_value,
       ad.pattern,
       ad.custom_validation,
       ad.is_in,
       ad.is_not_in,
       ad.is_importable,
       ad.file_specification,
       ad.id_agenda_definition_depended,
       ad.enabled,
       ad.dt_ins,
       ad.dt_upd,
       ad.id_agenda_segment_attribute_group_definition,
       ahshagd.id_agenda_has_segment_definition,
       a.name     AS agenda_name,
       aseg.name  AS segment_name,
       aag.name   AS attribute_group_name,
       att.name   AS attribute_name,
       attdt.name AS data_type,
       a.name || '-' || aseg.name || '-' || aag.name || '-' || att.name AS full_unique_name,
       aseg.name || '-' || aag.name || '-' || att.name AS unique_name
FROM agenda_definition ad
         JOIN agenda_has_segment_has_attribute_group_definition ahshagd ON ad.id_agenda_segment_attribute_group_definition = ahshagd.id
         JOIN agenda_has_segment_definition ahsd ON ahshagd.id_agenda_has_segment_definition = ahsd.id
         JOIN agenda a ON ahsd.id_agenda = a.id
         JOIN agenda_segment aseg ON ahsd.id_agenda_segment = aseg.id
         JOIN agenda_attribute_group aag ON ahshagd.id_agenda_attribute_group = aag.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN attribute_data_type attdt ON att.id_attribute_data_type = attdt.id
WHERE ad.enabled = TRUE
  AND ahshagd.enabled = TRUE
  AND ahsd.enabled = TRUE
  AND a.enabled = TRUE
  AND aseg.enabled = TRUE
  AND aag.enabled = TRUE
  AND att.enabled = TRUE
  AND attdt.enabled = TRUE;

CREATE UNIQUE INDEX mv_agenda_definition_id_idx
    ON mv_agenda_definition (id);


CREATE MATERIALIZED VIEW flexii.mv_api_agenda_mapping AS
SELECT
    aapthpa.id
     , aapt.id AS id_template
     , aapt.name AS template_name
     , ad.id_agenda
     , aapa.name AS template_attribute_name
     , ad.id AS id_agenda_definition
     , aapam.id AS id_page_attribute_mapping
     , aadp.id_external_application
FROM api_agenda_page_template aapt
         JOIN api_agenda_page_template_has_page_attribute aapthpa ON aapt.id = aapthpa.id_api_agenda_page_template
         JOIN api_agenda_page_attribute aapa ON aapa.id = aapthpa.id_api_agenda_page_attribute
         JOIN api_agenda_page_attribute_mapping aapam ON aapthpa.id = aapam.id_api_agenda_page_template_has_page_attribute
         JOIN api_agenda_definition_publish aadp ON aadp.id = aapam.id_api_agenda_definition_publish
         JOIN mv_agenda_definition ad ON ad.id = aadp.id_agenda_definition
WHERE aapt.enabled = TRUE
  AND aapthpa.enabled = TRUE
  AND aapa.enabled = TRUE
  AND aapam.enabled = TRUE
  AND aadp.enabled = TRUE;

CREATE VIEW flexii.v_agenda_entity_title AS
SELECT final.id_agenda_entity,
       STRING_AGG(final.value::text, ' '::text ORDER BY final.in_title_and_priority) AS title,
       ae.uid,
       ae.id_agenda,
       ae.enabled,
       ae.is_anonymized,
       ae.id_appuser_created_by,
       ae.id_appuser_updated_by,
       ae.dt_ins,
       ae.dt_upd
FROM (SELECT val.value,
             val.id_agenda_entity,
             ad.in_title_and_priority
      FROM (SELECT val_1.value,
                   val_1.id_agenda_definition,
                   val_1.id_agenda_entity
            FROM agenda_values val_1
            WHERE (val_1.id_agenda_definition IN (SELECT ad_1.id
                                                  FROM mv_agenda_definition ad_1
                                                           JOIN agenda_has_segment_definition ahsd ON ad_1.id_agenda_has_segment_definition = ahsd.id
                                                  WHERE ad_1.enabled = TRUE
                                                    AND ahsd.enabled = TRUE
                                                    AND ad_1.in_title_and_priority IS NOT NULL
                                                    AND ahsd.is_main = TRUE))) val
               JOIN agenda_definition ad ON val.id_agenda_definition = ad.id
      ORDER BY ad.in_title_and_priority) final
         JOIN agenda_entity ae ON final.id_agenda_entity = ae.id
GROUP BY final.id_agenda_entity, ae.uid, ae.id_agenda, ae.enabled, ae.is_anonymized, ae.id_appuser_created_by, ae.id_appuser_updated_by, ae.dt_ins,
         ae.dt_upd;


CREATE OR REPLACE VIEW flexii.v_agenda_definition_specification_by_value AS
SELECT ad.id_agenda,
       att.name       AS name_definition,
       ad.is_not_null AS not_null_definition,
       adsbv.is_hidden,
       adsbv.is_not_null,
       adsbv.recurring_sequence,
       att_control.name,
       adsbv.value_control
FROM agenda_definition_specification_by_value adsbv
         JOIN mv_agenda_definition ad ON adsbv.id_agenda_definition = ad.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN mv_agenda_definition ad_control ON adsbv.id_agenda_definition_control = ad_control.id
         JOIN attribute att_control ON ad_control.id_attribute = att_control.id;


CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , ad.id_agenda
     , id_agenda_segment
     , id_agenda_attribute_group
     , id_attribute
     , agenda_name
     , segment_name
     , attribute_group_name
     , attribute_name
FROM (SELECT id_user_group,
             user_name,
             id_agenda_definition,
             is_read,
             is_write
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughad.id_agenda_definition, ughad.is_write, ughad.is_read
            FROM base.user_group ug
                     JOIN user_group_has_agenda_definition ughad ON ug.id = ughad.id_user_group
                     JOIN mv_agenda_definition ad ON ad.id = ughad.id_agenda_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughad.enabled = TRUE
           ) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, ad.id AS id_agenda_definition, TRUE AS is_read, TRUE AS is_write
       FROM base.user_group ug,
            mv_agenda_definition ad
       WHERE ug.enabled = TRUE
         AND ad.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, ad.id) NOT IN (SELECT id_user_group, id_agenda_definition
                                    FROM user_group_has_agenda_definition ughad
                                    WHERE enabled = TRUE)
      )
     ) base
         JOIN mv_agenda_definition ad ON base.id_agenda_definition = ad.id
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = ad.id_agenda;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_has_segment_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , base.id_agenda
     , id_agenda_segment
FROM (SELECT id_user_group,
             user_name,
             id_agenda_has_segment_definition,
             is_read,
             is_write,
             id_agenda,
             id_agenda_segment
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_definition, ughasd.is_read, ughasd.is_write, ahsd.id_agenda, ahsd.id_agenda_segment
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ughasd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_definition, TRUE AS is_read, TRUE AS is_write, def.id_agenda, def.id_agenda_segment
       FROM base.user_group ug,
            agenda_has_segment_definition def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_definition
                                     FROM user_group_has_agenda_has_segment_definition ughad
                                     WHERE enabled = TRUE))) base
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = base.id_agenda;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_group_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_has_segment_has_attribute_group_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , id_agenda_attribute_group
     , id_agenda_segment
     , base.id_agenda
FROM (SELECT id_user_group,
             user_name,
             id_agenda_has_segment_has_attribute_group_definition,
             is_read,
             is_write
              , id_agenda_attribute_group
              , id_agenda_segment
              , id_agenda
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_has_attribute_group_definition, ughasd.is_read, ughasd.is_write, ahshagd.id_agenda_attribute_group, ahsd.id_agenda_segment, ahsd.id_agenda
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_group_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_has_attribute_group_definition ahshagd ON ughasd.id_agenda_has_segment_has_attribute_group_definition = ahshagd.id
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_has_attribute_group_definition, TRUE AS is_read, TRUE AS is_write, def.id_agenda_attribute_group, def.id_agenda_segment, def.id_agenda
       FROM base.user_group ug,
            (SELECT ahshagd.*, ahsd.id_agenda, ahsd.id_agenda_segment
             FROM agenda_has_segment_has_attribute_group_definition ahshagd
                      JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            ) def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_has_attribute_group_definition
                                     FROM user_group_has_agenda_has_segment_group_definition ughad
                                     WHERE enabled = TRUE))) base
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = base.id_agenda;

create materialized view flexii.mv_user_group_has_agenda_permission as
select type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_agenda_definition, id_user_group, is_read, is_write
from (
         select
             'definition' as type,
             ughad.id_agenda,
             ughad.id_agenda_segment,
             ughad.id_agenda_attribute_group,
             ughad.id_attribute,
             ughad.id_agenda_definition,
             ughad.id_user_group as id_user_group,
             ughad.is_read,
             ughad.is_write
         from flexii.mv_user_group_has_agenda_definition ughad
         union all
         select
             'segment' as type,
             ughahsd.id_agenda,
             ughahsd.id_agenda_segment,
             null,
             null,
             null,
             ughahsd.id_user_group as id_user_group,
             ughahsd.is_read,
             ughahsd.is_write
         from flexii.mv_user_group_has_agenda_has_segment_definition ughahsd
         union all
         select
             'group' as type,
             ughahsgd.id_agenda,
             ughahsgd.id_agenda_segment,
             ughahsgd.id_agenda_attribute_group,
             null,
             null,
             ughahsgd.id_user_group as id_user_group,
             ughahsgd.is_read,
             ughahsgd.is_write
         from flexii.mv_user_group_has_agenda_has_segment_group_definition ughahsgd
     ) definitions;

CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission ON mv_user_group_has_agenda_permission (type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_user_group);
CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission_def ON mv_user_group_has_agenda_permission (type, id_agenda_definition, id_user_group);

-- #14663
INSERT INTO flexii.menu_link (name, presenter, action, parameters, is_exact_match) VALUES ('admin_agenda_sync', 'Agenda:Admin', 'agendaSync', null, false);
INSERT INTO flexii.menu_item (id_menu_section, id_agenda, id_calendar, name, icon, id_menu_link, priority, is_default, is_separator_before,
                              is_for_admin, is_for_dpo, is_for_superadmin)
VALUES ((SELECT id FROM menu_section WHERE name = 'administration'), NULL, NULL, 'adminAgendaSync', 'fad fa-rotate', (SELECT id FROM menu_link WHERE name = 'admin_agenda_sync'), 135, FALSE, FALSE, TRUE, FALSE, FALSE);

----------------------------------------------------
-- 2.6.16.0
-- SQL for this version ends here
----------------------------------------------------

---- #15007
---- 2)
ALTER TABLE agenda_has_agenda_show_attribute
    ADD COLUMN datagrid_column_priority SMALLINT;

ALTER TABLE agenda_has_agenda_definition
    ADD COLUMN datagrid_column_priority SMALLINT;

create unique index unq_aha_show_attribute_datagrid_column_priority
    on flexii.agenda_has_agenda_show_attribute (id_agenda_has_agenda, datagrid_column_priority);

create unique index unq_aha_agenda_definition_datagrid_column_priority
    on flexii.agenda_has_agenda_definition (id_agenda_has_agenda, datagrid_column_priority);

----------------------------------------------------
-- 2.6.17.0
-- SQL for this version ends here
----------------------------------------------------

-- #15175
alter table agenda_has_segment_definition alter column id_agenda_segment set not null;

----------------------------------------------------
-- 2.6.20.0
-- SQL for this version ends here
----------------------------------------------------

create index idx_mv_agenda_definition_unique_name on mv_agenda_definition (unique_name ASC);
create index idx_mv_agenda_definition_full_unique_name on mv_agenda_definition (full_unique_name ASC);
drop index idx_master_lookup_lookup_name_name;

-- #14395
alter table user_group_has_agenda_definition add is_create boolean default false not null;
alter table user_group_has_agenda_has_segment_definition add is_create boolean default false not null;
alter table user_group_has_agenda_has_segment_group_definition add is_create boolean default false not null;

drop materialized view if exists flexii.mv_user_group_has_agenda_permission;
drop materialized view if exists flexii.mv_user_group_has_agenda_definition;
drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_group_definition;
drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_definition;


CREATE VIEW flexii.v_user_group_has_agenda_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , CASE WHEN ugha.is_can_create = TRUE THEN base.is_create ELSE false END AS is_create
     , ad.id_agenda
     , id_agenda_segment
     , id_agenda_attribute_group
     , id_attribute
     , agenda_name
     , segment_name
     , attribute_group_name
     , attribute_name
FROM (SELECT id_user_group,
             user_name,
             id_agenda_definition,
             is_read,
             is_write,
             is_create
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughad.id_agenda_definition, ughad.is_write, ughad.is_read, ughad.is_create
            FROM base.user_group ug
                     JOIN user_group_has_agenda_definition ughad ON ug.id = ughad.id_user_group
                     JOIN mv_agenda_definition ad ON ad.id = ughad.id_agenda_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughad.enabled = TRUE
           ) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, ad.id AS id_agenda_definition, TRUE AS is_read, TRUE AS is_write, TRUE AS is_create
       FROM base.user_group ug,
            mv_agenda_definition ad
       WHERE ug.enabled = TRUE
         AND ad.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, ad.id) NOT IN (SELECT id_user_group, id_agenda_definition
                                    FROM user_group_has_agenda_definition ughad
                                    WHERE enabled = TRUE)
      )
     ) base
         JOIN mv_agenda_definition ad ON base.id_agenda_definition = ad.id
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = ad.id_agenda;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_definition AS
SELECT * FROM v_user_group_has_agenda_definition;

CREATE VIEW flexii.v_user_group_has_agenda_has_segment_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_has_segment_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , CASE WHEN ugha.is_can_create = TRUE THEN base.is_create ELSE false END AS is_create
     , base.id_agenda
     , id_agenda_segment
FROM (SELECT id_user_group,
             user_name,
             id_agenda_has_segment_definition,
             is_read,
             is_write,
             is_create,
             id_agenda,
             id_agenda_segment
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_definition, ughasd.is_read, ughasd.is_write, ughasd.is_create, ahsd.id_agenda, ahsd.id_agenda_segment
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ughasd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_definition, TRUE AS is_read, TRUE AS is_write, TRUE AS is_create, def.id_agenda, def.id_agenda_segment
       FROM base.user_group ug,
            agenda_has_segment_definition def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_definition
                                     FROM user_group_has_agenda_has_segment_definition ughad
                                     WHERE enabled = TRUE))) base
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = base.id_agenda;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_definition AS
SELECT * FROM v_user_group_has_agenda_has_segment_definition;

CREATE VIEW flexii.v_user_group_has_agenda_has_segment_group_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_has_segment_has_attribute_group_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , CASE WHEN ugha.is_can_create = TRUE THEN base.is_create ELSE false END AS is_create
     , id_agenda_attribute_group
     , id_agenda_segment
     , base.id_agenda
FROM (SELECT id_user_group
             , user_name
             , id_agenda_has_segment_has_attribute_group_definition
             , is_read
             , is_write
             ,  is_create
             , id_agenda_attribute_group
             , id_agenda_segment
             , id_agenda
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_has_attribute_group_definition, ughasd.is_read, ughasd.is_write, ughasd.is_create, ahshagd.id_agenda_attribute_group, ahsd.id_agenda_segment, ahsd.id_agenda
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_group_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_has_attribute_group_definition ahshagd ON ughasd.id_agenda_has_segment_has_attribute_group_definition = ahshagd.id
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_has_attribute_group_definition, TRUE AS is_read, TRUE AS is_write, TRUE AS is_create, def.id_agenda_attribute_group, def.id_agenda_segment, def.id_agenda
       FROM base.user_group ug,
            (SELECT ahshagd.*, ahsd.id_agenda, ahsd.id_agenda_segment
             FROM agenda_has_segment_has_attribute_group_definition ahshagd
                      JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            ) def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_has_attribute_group_definition
                                     FROM user_group_has_agenda_has_segment_group_definition ughad
                                     WHERE enabled = TRUE))) base
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = base.id_agenda;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_group_definition AS
SELECT * FROM v_user_group_has_agenda_has_segment_group_definition;

create materialized view flexii.mv_user_group_has_agenda_permission as
select type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_agenda_definition, id_user_group, is_read, is_write, is_create
from (
         select
             'definition' as type,
             ughad.id_agenda,
             ughad.id_agenda_segment,
             ughad.id_agenda_attribute_group,
             ughad.id_attribute,
             ughad.id_agenda_definition,
             ughad.id_user_group as id_user_group,
             ughad.is_read,
             ughad.is_write,
             ughad.is_create
         from flexii.v_user_group_has_agenda_definition ughad
         union all
         select
             'segment' as type,
             ughahsd.id_agenda,
             ughahsd.id_agenda_segment,
             null,
             null,
             null,
             ughahsd.id_user_group as id_user_group,
             ughahsd.is_read,
             ughahsd.is_write,
             ughahsd.is_create
         from flexii.v_user_group_has_agenda_has_segment_definition ughahsd
         union all
         select
             'group' as type,
             ughahsgd.id_agenda,
             ughahsgd.id_agenda_segment,
             ughahsgd.id_agenda_attribute_group,
             null,
             null,
             ughahsgd.id_user_group as id_user_group,
             ughahsgd.is_read,
             ughahsgd.is_write,
             ughahsgd.is_create
         from flexii.v_user_group_has_agenda_has_segment_group_definition ughahsgd
     ) definitions;

CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission ON mv_user_group_has_agenda_permission (type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_user_group);
CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission_def ON mv_user_group_has_agenda_permission (type, id_agenda_definition, id_user_group);

----------------------------------------------------
-- 2.6.21.0
-- SQL for this version ends here
----------------------------------------------------

alter table base.log_api_request alter column body type text using body::text;
alter table base.log_api_request alter column action type VARCHAR(400);
alter table base.log_api_response alter column body type text using body::text;

----------------------------------------------------
-- 2.6.22.0
-- SQL for this version ends here
----------------------------------------------------

alter table external_application
    add is_encrypted boolean default false not null;

----------------------------------------------------
-- 2.6.22.2
-- SQL for this version ends here
----------------------------------------------------

CREATE INDEX idx_agenda_entity_title ON agenda_entity (title);

----------------------------------------------------
-- 2.6.22.5
-- SQL for this version ends here
----------------------------------------------------


-- #15405
create index idx_agenda_entity_id_agenda_enabled on flexii.agenda_entity (id_agenda, enabled);
create index idx_agenda_entity_enabled on flexii.agenda_entity (enabled);

-- logger index
create index idx_lg_logger_dt
    on log.lg_logger (dt);

create index idx_lg_logger_entity_type_id_entity
    on log.lg_logger (entity_type, id_entity);

create index idx_lg_logger_id_parent
    on log.lg_logger (id_parent);

create index idx_lg_logger_is_last
    on log.lg_logger (is_last);

create index idx_agenda_values_id_agenda_definition_id_agenda_entity
    on flexii.agenda_values (id_agenda_definition, id_agenda_entity);

create index idx_agenda_values_is_anonymized
    on flexii.agenda_values (is_anonymized);

----------------------------------------------------
-- 2.6.23.0
-- SQL for this version ends here
----------------------------------------------------

CREATE EXTENSION IF NOT EXISTS pg_trgm;
drop index idx_agenda_entity_title;
CREATE INDEX idx_agenda_entity_title ON agenda_entity USING gin (title gin_trgm_ops);


DROP MATERIALIZED VIEW mv_api_agenda_mapping;
DROP VIEW v_agenda_entity_title;
DROP VIEW v_agenda_definition_specification_by_value;
drop materialized view if exists flexii.mv_user_group_has_agenda_permission;
drop materialized view if exists flexii.mv_user_group_has_agenda_definition;
drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_group_definition;
drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_definition;
drop view if exists flexii.v_user_group_has_agenda_definition;
drop view if exists flexii.v_user_group_has_agenda_has_segment_group_definition;
drop view if exists flexii.v_user_group_has_agenda_has_segment_definition;

-- before drop mv_agenda_definition, don't forget to drop view dv_agenda ...
DROP MATERIALIZED VIEW mv_agenda_definition;

alter table agenda_definition alter column tooltip type varchar(2000) using tooltip::varchar(2000);

CREATE MATERIALIZED VIEW flexii.mv_agenda_definition AS
SELECT ad.id,
       ahsd.id_agenda,
       ahsd.id_agenda_segment,
       ahshagd.id_agenda_attribute_group,
       ad.is_control_for_group,
       ad.id_attribute,
       ad.priority,
       ad.is_not_null,
       ad.is_hidden,
       ad.default_value,
       ad.is_disabled,
       ad.is_read_only,
       ad.tooltip,
       ad.field_column_length,
       ad.skip_column_length,
       ad.field_column_height,
       ad.in_title_and_priority,
       ad.datagrid_priority,
       ad.is_editable_in_datagrid,
       ad.is_hidden_in_datagrid,
       ad.datagrid_order_priority,
       ad.datagrid_order_by,
       ad.is_search_by,
       ad.is_show_in_linked_agenda,
       ad.is_unique,
       ad.min_length,
       ad.max_length,
       ad.min_value,
       ad.max_value,
       ad.pattern,
       ad.custom_validation,
       ad.is_in,
       ad.is_not_in,
       ad.is_importable,
       ad.file_specification,
       ad.id_agenda_definition_depended,
       ad.enabled,
       ad.dt_ins,
       ad.dt_upd,
       ad.id_agenda_segment_attribute_group_definition,
       ahshagd.id_agenda_has_segment_definition,
       a.name     AS agenda_name,
       aseg.name  AS segment_name,
       aag.name   AS attribute_group_name,
       att.name   AS attribute_name,
       attdt.name AS data_type,
       a.name || '-' || aseg.name || '-' || aag.name || '-' || att.name AS full_unique_name,
       aseg.name || '-' || aag.name || '-' || att.name AS unique_name
FROM agenda_definition ad
         JOIN agenda_has_segment_has_attribute_group_definition ahshagd ON ad.id_agenda_segment_attribute_group_definition = ahshagd.id
         JOIN agenda_has_segment_definition ahsd ON ahshagd.id_agenda_has_segment_definition = ahsd.id
         JOIN agenda a ON ahsd.id_agenda = a.id
         JOIN agenda_segment aseg ON ahsd.id_agenda_segment = aseg.id
         JOIN agenda_attribute_group aag ON ahshagd.id_agenda_attribute_group = aag.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN attribute_data_type attdt ON att.id_attribute_data_type = attdt.id
WHERE ad.enabled = TRUE
  AND ahshagd.enabled = TRUE
  AND ahsd.enabled = TRUE
  AND a.enabled = TRUE
  AND aseg.enabled = TRUE
  AND aag.enabled = TRUE
  AND att.enabled = TRUE
  AND attdt.enabled = TRUE;

CREATE UNIQUE INDEX mv_agenda_definition_id_idx
    ON mv_agenda_definition (id);


CREATE MATERIALIZED VIEW flexii.mv_api_agenda_mapping AS
SELECT
    aapthpa.id
     , aapt.id AS id_template
     , aapt.name AS template_name
     , ad.id_agenda
     , aapa.name AS template_attribute_name
     , ad.id AS id_agenda_definition
     , aapam.id AS id_page_attribute_mapping
     , aadp.id_external_application
FROM api_agenda_page_template aapt
         JOIN api_agenda_page_template_has_page_attribute aapthpa ON aapt.id = aapthpa.id_api_agenda_page_template
         JOIN api_agenda_page_attribute aapa ON aapa.id = aapthpa.id_api_agenda_page_attribute
         JOIN api_agenda_page_attribute_mapping aapam ON aapthpa.id = aapam.id_api_agenda_page_template_has_page_attribute
         JOIN api_agenda_definition_publish aadp ON aadp.id = aapam.id_api_agenda_definition_publish
         JOIN mv_agenda_definition ad ON ad.id = aadp.id_agenda_definition
WHERE aapt.enabled = TRUE
  AND aapthpa.enabled = TRUE
  AND aapa.enabled = TRUE
  AND aapam.enabled = TRUE
  AND aadp.enabled = TRUE;

CREATE VIEW flexii.v_agenda_entity_title AS
SELECT final.id_agenda_entity,
       STRING_AGG(final.value::text, ' '::text ORDER BY final.in_title_and_priority) AS title,
       ae.uid,
       ae.id_agenda,
       ae.enabled,
       ae.is_anonymized,
       ae.id_appuser_created_by,
       ae.id_appuser_updated_by,
       ae.dt_ins,
       ae.dt_upd
FROM (SELECT val.value,
             val.id_agenda_entity,
             ad.in_title_and_priority
      FROM (SELECT val_1.value,
                   val_1.id_agenda_definition,
                   val_1.id_agenda_entity
            FROM agenda_values val_1
            WHERE (val_1.id_agenda_definition IN (SELECT ad_1.id
                                                  FROM mv_agenda_definition ad_1
                                                           JOIN agenda_has_segment_definition ahsd ON ad_1.id_agenda_has_segment_definition = ahsd.id
                                                  WHERE ad_1.enabled = TRUE
                                                    AND ahsd.enabled = TRUE
                                                    AND ad_1.in_title_and_priority IS NOT NULL
                                                    AND ahsd.is_main = TRUE))) val
               JOIN agenda_definition ad ON val.id_agenda_definition = ad.id
      ORDER BY ad.in_title_and_priority) final
         JOIN agenda_entity ae ON final.id_agenda_entity = ae.id
GROUP BY final.id_agenda_entity, ae.uid, ae.id_agenda, ae.enabled, ae.is_anonymized, ae.id_appuser_created_by, ae.id_appuser_updated_by, ae.dt_ins,
         ae.dt_upd;


CREATE OR REPLACE VIEW flexii.v_agenda_definition_specification_by_value AS
SELECT ad.id_agenda,
       att.name       AS name_definition,
       ad.is_not_null AS not_null_definition,
       adsbv.is_hidden,
       adsbv.is_not_null,
       adsbv.recurring_sequence,
       att_control.name,
       adsbv.value_control
FROM agenda_definition_specification_by_value adsbv
         JOIN mv_agenda_definition ad ON adsbv.id_agenda_definition = ad.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN mv_agenda_definition ad_control ON adsbv.id_agenda_definition_control = ad_control.id
         JOIN attribute att_control ON ad_control.id_attribute = att_control.id;

CREATE VIEW flexii.v_user_group_has_agenda_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , CASE WHEN ugha.is_can_create = TRUE THEN base.is_create ELSE false END AS is_create
     , ad.id_agenda
     , id_agenda_segment
     , id_agenda_attribute_group
     , id_attribute
     , agenda_name
     , segment_name
     , attribute_group_name
     , attribute_name
FROM (SELECT id_user_group,
             user_name,
             id_agenda_definition,
             is_read,
             is_write,
             is_create
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughad.id_agenda_definition, ughad.is_write, ughad.is_read, ughad.is_create
            FROM base.user_group ug
                     JOIN user_group_has_agenda_definition ughad ON ug.id = ughad.id_user_group
                     JOIN mv_agenda_definition ad ON ad.id = ughad.id_agenda_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughad.enabled = TRUE
           ) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, ad.id AS id_agenda_definition, TRUE AS is_read, TRUE AS is_write, TRUE AS is_create
       FROM base.user_group ug,
            mv_agenda_definition ad
       WHERE ug.enabled = TRUE
         AND ad.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, ad.id) NOT IN (SELECT id_user_group, id_agenda_definition
                                    FROM user_group_has_agenda_definition ughad
                                    WHERE enabled = TRUE)
      )
     ) base
         JOIN mv_agenda_definition ad ON base.id_agenda_definition = ad.id
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = ad.id_agenda;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_definition AS
SELECT * FROM v_user_group_has_agenda_definition;

CREATE VIEW flexii.v_user_group_has_agenda_has_segment_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_has_segment_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , CASE WHEN ugha.is_can_create = TRUE THEN base.is_create ELSE false END AS is_create
     , base.id_agenda
     , id_agenda_segment
FROM (SELECT id_user_group,
             user_name,
             id_agenda_has_segment_definition,
             is_read,
             is_write,
             is_create,
             id_agenda,
             id_agenda_segment
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_definition, ughasd.is_read, ughasd.is_write, ughasd.is_create, ahsd.id_agenda, ahsd.id_agenda_segment
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ughasd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_definition, TRUE AS is_read, TRUE AS is_write, TRUE AS is_create, def.id_agenda, def.id_agenda_segment
       FROM base.user_group ug,
            agenda_has_segment_definition def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_definition
                                     FROM user_group_has_agenda_has_segment_definition ughad
                                     WHERE enabled = TRUE))) base
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = base.id_agenda;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_definition AS
SELECT * FROM v_user_group_has_agenda_has_segment_definition;

CREATE VIEW flexii.v_user_group_has_agenda_has_segment_group_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_has_segment_has_attribute_group_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , CASE WHEN ugha.is_can_create = TRUE THEN base.is_create ELSE false END AS is_create
     , id_agenda_attribute_group
     , id_agenda_segment
     , base.id_agenda
FROM (SELECT id_user_group
           , user_name
           , id_agenda_has_segment_has_attribute_group_definition
           , is_read
           , is_write
           ,  is_create
           , id_agenda_attribute_group
           , id_agenda_segment
           , id_agenda
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_has_attribute_group_definition, ughasd.is_read, ughasd.is_write, ughasd.is_create, ahshagd.id_agenda_attribute_group, ahsd.id_agenda_segment, ahsd.id_agenda
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_group_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_has_attribute_group_definition ahshagd ON ughasd.id_agenda_has_segment_has_attribute_group_definition = ahshagd.id
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_has_attribute_group_definition, TRUE AS is_read, TRUE AS is_write, TRUE AS is_create, def.id_agenda_attribute_group, def.id_agenda_segment, def.id_agenda
       FROM base.user_group ug,
            (SELECT ahshagd.*, ahsd.id_agenda, ahsd.id_agenda_segment
             FROM agenda_has_segment_has_attribute_group_definition ahshagd
                      JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            ) def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_has_attribute_group_definition
                                     FROM user_group_has_agenda_has_segment_group_definition ughad
                                     WHERE enabled = TRUE))) base
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = base.id_agenda;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_group_definition AS
SELECT * FROM v_user_group_has_agenda_has_segment_group_definition;

create materialized view flexii.mv_user_group_has_agenda_permission as
select type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_agenda_definition, id_user_group, is_read, is_write, is_create
from (
         select
             'definition' as type,
             ughad.id_agenda,
             ughad.id_agenda_segment,
             ughad.id_agenda_attribute_group,
             ughad.id_attribute,
             ughad.id_agenda_definition,
             ughad.id_user_group as id_user_group,
             ughad.is_read,
             ughad.is_write,
             ughad.is_create
         from flexii.v_user_group_has_agenda_definition ughad
         union all
         select
             'segment' as type,
             ughahsd.id_agenda,
             ughahsd.id_agenda_segment,
             null,
             null,
             null,
             ughahsd.id_user_group as id_user_group,
             ughahsd.is_read,
             ughahsd.is_write,
             ughahsd.is_create
         from flexii.v_user_group_has_agenda_has_segment_definition ughahsd
         union all
         select
             'group' as type,
             ughahsgd.id_agenda,
             ughahsgd.id_agenda_segment,
             ughahsgd.id_agenda_attribute_group,
             null,
             null,
             ughahsgd.id_user_group as id_user_group,
             ughahsgd.is_read,
             ughahsgd.is_write,
             ughahsgd.is_create
         from flexii.v_user_group_has_agenda_has_segment_group_definition ughahsgd
     ) definitions;

CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission ON mv_user_group_has_agenda_permission (type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_user_group);
CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission_def ON mv_user_group_has_agenda_permission (type, id_agenda_definition, id_user_group);

----------------------------------------------------
-- 2.6.24.1
-- SQL for this version ends here
----------------------------------------------------

DROP MATERIALIZED VIEW mv_api_agenda_mapping;
DROP VIEW v_agenda_entity_title;
DROP VIEW v_agenda_definition_specification_by_value;
drop materialized view if exists flexii.mv_user_group_has_agenda_permission;
drop materialized view if exists flexii.mv_user_group_has_agenda_definition;
drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_group_definition;
drop materialized view if exists flexii.mv_user_group_has_agenda_has_segment_definition;
drop view if exists flexii.v_user_group_has_agenda_definition;
drop view if exists flexii.v_user_group_has_agenda_has_segment_group_definition;
drop view if exists flexii.v_user_group_has_agenda_has_segment_definition;

-- before drop mv_agenda_definition, don't forget to drop view dv_agenda ...
DROP MATERIALIZED VIEW mv_agenda_definition;

alter table attribute alter column name type varchar(38) using name::varchar(38);

alter table agenda_attribute_group add name_short varchar(5);
alter table agenda_segment add name_short varchar(5);

CREATE MATERIALIZED VIEW flexii.mv_agenda_definition AS
SELECT ad.id,
       ahsd.id_agenda,
       ahsd.id_agenda_segment,
       ahshagd.id_agenda_attribute_group,
       ad.is_control_for_group,
       ad.id_attribute,
       ad.priority,
       ad.is_not_null,
       ad.is_hidden,
       ad.default_value,
       ad.is_disabled,
       ad.is_read_only,
       ad.tooltip,
       ad.field_column_length,
       ad.skip_column_length,
       ad.field_column_height,
       ad.in_title_and_priority,
       ad.datagrid_priority,
       ad.is_editable_in_datagrid,
       ad.is_hidden_in_datagrid,
       ad.datagrid_order_priority,
       ad.datagrid_order_by,
       ad.is_search_by,
       ad.is_show_in_linked_agenda,
       ad.is_unique,
       ad.min_length,
       ad.max_length,
       ad.min_value,
       ad.max_value,
       ad.pattern,
       ad.custom_validation,
       ad.is_in,
       ad.is_not_in,
       ad.is_importable,
       ad.file_specification,
       ad.id_agenda_definition_depended,
       ad.enabled,
       ad.dt_ins,
       ad.dt_upd,
       ad.id_agenda_segment_attribute_group_definition,
       ahshagd.id_agenda_has_segment_definition,
       a.name     AS agenda_name,
       aseg.name  AS segment_name,
       aseg.name_short  AS segment_name_short,
       aag.name   AS attribute_group_name,
       aag.name_short   AS attribute_group_name_short,
       att.name   AS attribute_name,
       attdt.name AS data_type,
       a.name || '-' || aseg.name || '-' || aag.name || '-' || att.name AS full_unique_name,
       aseg.name || '-' || aag.name || '-' || att.name AS unique_name
FROM agenda_definition ad
         JOIN agenda_has_segment_has_attribute_group_definition ahshagd ON ad.id_agenda_segment_attribute_group_definition = ahshagd.id
         JOIN agenda_has_segment_definition ahsd ON ahshagd.id_agenda_has_segment_definition = ahsd.id
         JOIN agenda a ON ahsd.id_agenda = a.id
         JOIN agenda_segment aseg ON ahsd.id_agenda_segment = aseg.id
         JOIN agenda_attribute_group aag ON ahshagd.id_agenda_attribute_group = aag.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN attribute_data_type attdt ON att.id_attribute_data_type = attdt.id
WHERE ad.enabled = TRUE
  AND ahshagd.enabled = TRUE
  AND ahsd.enabled = TRUE
  AND a.enabled = TRUE
  AND aseg.enabled = TRUE
  AND aag.enabled = TRUE
  AND att.enabled = TRUE
  AND attdt.enabled = TRUE;

CREATE UNIQUE INDEX mv_agenda_definition_id_idx
    ON mv_agenda_definition (id);


CREATE MATERIALIZED VIEW flexii.mv_api_agenda_mapping AS
SELECT
    aapthpa.id
     , aapt.id AS id_template
     , aapt.name AS template_name
     , ad.id_agenda
     , aapa.name AS template_attribute_name
     , ad.id AS id_agenda_definition
     , aapam.id AS id_page_attribute_mapping
     , aadp.id_external_application
FROM api_agenda_page_template aapt
         JOIN api_agenda_page_template_has_page_attribute aapthpa ON aapt.id = aapthpa.id_api_agenda_page_template
         JOIN api_agenda_page_attribute aapa ON aapa.id = aapthpa.id_api_agenda_page_attribute
         JOIN api_agenda_page_attribute_mapping aapam ON aapthpa.id = aapam.id_api_agenda_page_template_has_page_attribute
         JOIN api_agenda_definition_publish aadp ON aadp.id = aapam.id_api_agenda_definition_publish
         JOIN mv_agenda_definition ad ON ad.id = aadp.id_agenda_definition
WHERE aapt.enabled = TRUE
  AND aapthpa.enabled = TRUE
  AND aapa.enabled = TRUE
  AND aapam.enabled = TRUE
  AND aadp.enabled = TRUE;

CREATE VIEW flexii.v_agenda_entity_title AS
SELECT final.id_agenda_entity,
       STRING_AGG(final.value::text, ' '::text ORDER BY final.in_title_and_priority) AS title,
       ae.uid,
       ae.id_agenda,
       ae.enabled,
       ae.is_anonymized,
       ae.id_appuser_created_by,
       ae.id_appuser_updated_by,
       ae.dt_ins,
       ae.dt_upd
FROM (SELECT val.value,
             val.id_agenda_entity,
             ad.in_title_and_priority
      FROM (SELECT val_1.value,
                   val_1.id_agenda_definition,
                   val_1.id_agenda_entity
            FROM agenda_values val_1
            WHERE (val_1.id_agenda_definition IN (SELECT ad_1.id
                                                  FROM mv_agenda_definition ad_1
                                                           JOIN agenda_has_segment_definition ahsd ON ad_1.id_agenda_has_segment_definition = ahsd.id
                                                  WHERE ad_1.enabled = TRUE
                                                    AND ahsd.enabled = TRUE
                                                    AND ad_1.in_title_and_priority IS NOT NULL
                                                    AND ahsd.is_main = TRUE))) val
               JOIN agenda_definition ad ON val.id_agenda_definition = ad.id
      ORDER BY ad.in_title_and_priority) final
         JOIN agenda_entity ae ON final.id_agenda_entity = ae.id
GROUP BY final.id_agenda_entity, ae.uid, ae.id_agenda, ae.enabled, ae.is_anonymized, ae.id_appuser_created_by, ae.id_appuser_updated_by, ae.dt_ins,
         ae.dt_upd;


CREATE OR REPLACE VIEW flexii.v_agenda_definition_specification_by_value AS
SELECT ad.id_agenda,
       att.name       AS name_definition,
       ad.is_not_null AS not_null_definition,
       adsbv.is_hidden,
       adsbv.is_not_null,
       adsbv.recurring_sequence,
       att_control.name,
       adsbv.value_control
FROM agenda_definition_specification_by_value adsbv
         JOIN mv_agenda_definition ad ON adsbv.id_agenda_definition = ad.id
         JOIN attribute att ON ad.id_attribute = att.id
         JOIN mv_agenda_definition ad_control ON adsbv.id_agenda_definition_control = ad_control.id
         JOIN attribute att_control ON ad_control.id_attribute = att_control.id;

CREATE VIEW flexii.v_user_group_has_agenda_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , CASE WHEN ugha.is_can_create = TRUE THEN base.is_create ELSE false END AS is_create
     , ad.id_agenda
     , id_agenda_segment
     , id_agenda_attribute_group
     , id_attribute
     , agenda_name
     , segment_name
     , attribute_group_name
     , attribute_name
FROM (SELECT id_user_group,
             user_name,
             id_agenda_definition,
             is_read,
             is_write,
             is_create
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughad.id_agenda_definition, ughad.is_write, ughad.is_read, ughad.is_create
            FROM base.user_group ug
                     JOIN user_group_has_agenda_definition ughad ON ug.id = ughad.id_user_group
                     JOIN mv_agenda_definition ad ON ad.id = ughad.id_agenda_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughad.enabled = TRUE
           ) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, ad.id AS id_agenda_definition, TRUE AS is_read, TRUE AS is_write, TRUE AS is_create
       FROM base.user_group ug,
            mv_agenda_definition ad
       WHERE ug.enabled = TRUE
         AND ad.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, ad.id) NOT IN (SELECT id_user_group, id_agenda_definition
                                    FROM user_group_has_agenda_definition ughad
                                    WHERE enabled = TRUE)
      )
     ) base
         JOIN mv_agenda_definition ad ON base.id_agenda_definition = ad.id
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = ad.id_agenda;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_definition AS
SELECT * FROM v_user_group_has_agenda_definition;

CREATE VIEW flexii.v_user_group_has_agenda_has_segment_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_has_segment_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , CASE WHEN ugha.is_can_create = TRUE THEN base.is_create ELSE false END AS is_create
     , base.id_agenda
     , id_agenda_segment
FROM (SELECT id_user_group,
             user_name,
             id_agenda_has_segment_definition,
             is_read,
             is_write,
             is_create,
             id_agenda,
             id_agenda_segment
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_definition, ughasd.is_read, ughasd.is_write, ughasd.is_create, ahsd.id_agenda, ahsd.id_agenda_segment
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ughasd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_definition, TRUE AS is_read, TRUE AS is_write, TRUE AS is_create, def.id_agenda, def.id_agenda_segment
       FROM base.user_group ug,
            agenda_has_segment_definition def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_definition
                                     FROM user_group_has_agenda_has_segment_definition ughad
                                     WHERE enabled = TRUE))) base
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = base.id_agenda;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_definition AS
SELECT * FROM v_user_group_has_agenda_has_segment_definition;

CREATE VIEW flexii.v_user_group_has_agenda_has_segment_group_definition AS
SELECT base.id_user_group
     , user_name
     , id_agenda_has_segment_has_attribute_group_definition
     , CASE WHEN ugha.is_can_read = TRUE THEN base.is_read ELSE false END AS is_read
     , CASE WHEN ugha.is_can_write = TRUE THEN base.is_write ELSE false END AS is_write
     , CASE WHEN ugha.is_can_create = TRUE THEN base.is_create ELSE false END AS is_create
     , id_agenda_attribute_group
     , id_agenda_segment
     , base.id_agenda
FROM (SELECT id_user_group
           , user_name
           , id_agenda_has_segment_has_attribute_group_definition
           , is_read
           , is_write
           ,  is_create
           , id_agenda_attribute_group
           , id_agenda_segment
           , id_agenda
      FROM (SELECT ug.id AS id_user_group, ug.name AS user_name, ughasd.id_agenda_has_segment_has_attribute_group_definition, ughasd.is_read, ughasd.is_write, ughasd.is_create, ahshagd.id_agenda_attribute_group, ahsd.id_agenda_segment, ahsd.id_agenda
            FROM base.user_group ug
                     JOIN user_group_has_agenda_has_segment_group_definition ughasd ON ug.id = ughasd.id_user_group
                     JOIN agenda_has_segment_has_attribute_group_definition ahshagd ON ughasd.id_agenda_has_segment_has_attribute_group_definition = ahshagd.id
                     JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            WHERE ug.is_admin_group = FALSE
              AND ug.is_superadmin_group = FALSE
              AND ug.enabled = TRUE
              AND ughasd.enabled = TRUE) p_set
      UNION ALL
      (SELECT ug.id AS id_user_group, ug.name AS user_name, def.id AS id_agenda_has_segment_has_attribute_group_definition, TRUE AS is_read, TRUE AS is_write, TRUE AS is_create, def.id_agenda_attribute_group, def.id_agenda_segment, def.id_agenda
       FROM base.user_group ug,
            (SELECT ahshagd.*, ahsd.id_agenda, ahsd.id_agenda_segment
             FROM agenda_has_segment_has_attribute_group_definition ahshagd
                      JOIN agenda_has_segment_definition ahsd ON ahsd.id = ahshagd.id_agenda_has_segment_definition
            ) def
       WHERE ug.enabled = TRUE
         AND def.enabled = TRUE
         AND ug.is_admin_group = FALSE
         AND ug.is_superadmin_group = FALSE
         AND (ug.id, def.id) NOT IN (SELECT id_user_group, id_agenda_has_segment_has_attribute_group_definition
                                     FROM user_group_has_agenda_has_segment_group_definition ughad
                                     WHERE enabled = TRUE))) base
         LEFT JOIN user_group_has_agenda ugha ON ugha.id_user_group = base.id_user_group AND ugha.id_agenda = base.id_agenda;

CREATE MATERIALIZED VIEW flexii.mv_user_group_has_agenda_has_segment_group_definition AS
SELECT * FROM v_user_group_has_agenda_has_segment_group_definition;

create materialized view flexii.mv_user_group_has_agenda_permission as
select type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_agenda_definition, id_user_group, is_read, is_write, is_create
from (
         select
             'definition' as type,
             ughad.id_agenda,
             ughad.id_agenda_segment,
             ughad.id_agenda_attribute_group,
             ughad.id_attribute,
             ughad.id_agenda_definition,
             ughad.id_user_group as id_user_group,
             ughad.is_read,
             ughad.is_write,
             ughad.is_create
         from flexii.v_user_group_has_agenda_definition ughad
         union all
         select
             'segment' as type,
             ughahsd.id_agenda,
             ughahsd.id_agenda_segment,
             null,
             null,
             null,
             ughahsd.id_user_group as id_user_group,
             ughahsd.is_read,
             ughahsd.is_write,
             ughahsd.is_create
         from flexii.v_user_group_has_agenda_has_segment_definition ughahsd
         union all
         select
             'group' as type,
             ughahsgd.id_agenda,
             ughahsgd.id_agenda_segment,
             ughahsgd.id_agenda_attribute_group,
             null,
             null,
             ughahsgd.id_user_group as id_user_group,
             ughahsgd.is_read,
             ughahsgd.is_write,
             ughahsgd.is_create
         from flexii.v_user_group_has_agenda_has_segment_group_definition ughahsgd
     ) definitions;

CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission ON mv_user_group_has_agenda_permission (type, id_agenda, id_agenda_segment, id_agenda_attribute_group, id_attribute, id_user_group);
CREATE UNIQUE INDEX unq_mv_user_group_has_agenda_permission_def ON mv_user_group_has_agenda_permission (type, id_agenda_definition, id_user_group);

----------------------------------------------------
-- 2.6.27.0
-- SQL for this version ends here
----------------------------------------------------

INSERT INTO flexii.definition_key (name, table_name, data_type, default_value) VALUES ('is_create_link_on_attribute', 'agenda_has_agenda', 'bool', 'false');
INSERT INTO flexii.definition_key (name, table_name, data_type, default_value) VALUES ('is_create_link_on_attribute_linked', 'agenda_has_agenda', 'bool', 'false');

-- #15569
CREATE MATERIALIZED VIEW flexii.mv_agenda_has_agenda_property AS
SELECT id_agenda_has_agenda, is_create_link_on_attribute, is_create_link_on_attribute_linked
FROM crosstab($$SELECT id_agenda_has_agenda, core.name, CASE WHEN dtv.id IS NULL THEN core.default_value ELSE dtv.value END AS value
FROM (SELECT ad.id AS id_agenda_has_agenda, dk.id AS id_definition_key, dk.name, dk.default_value
      FROM (SELECT id
            FROM agenda_has_agenda
            WHERE enabled = TRUE) ad,
           (SELECT id, name, default_value FROM definition_key WHERE table_name = 'agenda_has_agenda' AND enabled = TRUE) dk) core
         LEFT JOIN definition_table_value dtv ON dtv.id_definition = core.id_agenda_has_agenda AND core.id_definition_key = dtv.id_definition_key ORDER BY id_agenda_has_agenda, name$$,
              $$VALUES ('is_create_link_on_attribute'), ('is_create_link_on_attribute_linked')$$) AS t (id_agenda_has_agenda int, is_create_link_on_attribute bool, is_create_link_on_attribute_linked bool);

CREATE UNIQUE INDEX mv_agenda_has_agenda_property_idx
    ON flexii.mv_agenda_has_agenda_property (id_agenda_has_agenda);

----------------------------------------------------
-- 2.6.28.0
-- SQL for this version ends here
----------------------------------------------------

-- #15763
alter table custom_import
    alter column original_file_name type varchar(260) using original_file_name::varchar(260);

----------------------------------------------------
-- 2.6.28.2
-- SQL for this version ends here
----------------------------------------------------

-- #15743
TRUNCATE TABLE log_api_response;
DELETE FROM log_api_request WHERE 1=1;
TRUNCATE TABLE lg_log_error;
TRUNCATE TABLE lg_log_exception;


-- pre flexii 3 scripts

DO
$$
    DECLARE
        rec                    RECORD;
        current_sequence_value bigint;
    BEGIN
        FOR rec IN
            WITH column_defaults AS (SELECT n.nspname                       AS table_schema, -- Schéma tabulky
                                            c.relname                       AS table_name,   -- Název tabulky
                                            PG_GET_EXPR(d.adbin, d.adrelid) AS default_value -- Výchozí hodnota sloupce
                                     FROM pg_attribute a
                                              JOIN
                                          pg_class c ON a.attrelid = c.oid
                                              JOIN
                                          pg_namespace n ON c.relnamespace = n.oid
                                              LEFT JOIN
                                          pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
                                     WHERE a.attnum > 0       -- Pouze běžné sloupce
                                       AND NOT a.attisdropped -- Vyloučíme odstraněné sloupce
                                       AND a.attname = 'id'
                                       AND c.relkind = 'r'    -- Pouze běžné tabulky
                                       AND c.relname != 'external_system_entity_mapping'
                                       AND PG_GET_EXPR(d.adbin, d.adrelid) LIKE 'nextval(%' -- Pouze sloupce využívající nextval
            ),
                 sequence_info AS (SELECT cd.table_schema,
                                          cd.table_name,
                                          REGEXP_REPLACE(
                                                  cd.default_value,
                                                  '^nextval\(''(.+?)''::regclass\)$',
                                                  '\1'
                                          ) AS sequence_name -- Extrakce názvu sekvence z nextval()
                                   FROM column_defaults cd),
                 final_result AS (SELECT si.table_schema,
                                         si.table_name,
                                         si.sequence_name
                                  FROM sequence_info si
                                           LEFT JOIN
                                       pg_class sc ON sc.relname = si.sequence_name
                                           LEFT JOIN
                                       pg_namespace n ON sc.relnamespace = n.oid)
            SELECT DISTINCT table_schema,
                   table_name,
                   sequence_name
            FROM final_result
            ORDER BY table_schema, table_name
            LOOP
                RAISE NOTICE 'Processing table %.%', rec.table_schema, rec.table_name;

                BEGIN
                    -- Získání aktuální hodnoty sekvence pomocí currval nebo nextval
                    EXECUTE FORMAT(
                            'SELECT nextval(%L)',
                            rec.sequence_name
                            ) INTO current_sequence_value;
                EXCEPTION
                    WHEN OTHERS THEN
                        RAISE WARNING 'Could not retrieve value for sequence %, skipping table %.%', rec.sequence_name, rec.table_schema, rec.table_name;
                        CONTINUE;
                END;

                -- Odstranění výchozí hodnoty sloupce id
                EXECUTE FORMAT(
                        'ALTER TABLE %I.%I ALTER COLUMN id DROP DEFAULT',
                        rec.table_schema,
                        rec.table_name
                        );

                -- Nastavení sloupce id jako IDENTITY
                EXECUTE FORMAT(
                        'ALTER TABLE %I.%I ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (START WITH %s INCREMENT BY 1)',
                        rec.table_schema,
                        rec.table_name,
                        current_sequence_value -- Zvýšení o 1 pro další hodnotu
                        );

                -- Odstranění původní sekvence
                EXECUTE FORMAT(
                        'DROP SEQUENCE IF EXISTS %I CASCADE',
                        rec.sequence_name
                        );

                RAISE NOTICE 'Table %.% processed successfully.', rec.table_schema, rec.table_name;
            END LOOP;
    END
$$;

----------------------------------------------------
-- 2.6.29.0
-- SQL for this version ends here
----------------------------------------------------