<?xml version="1.0" encoding="UTF-8"?>
<update>
    <step id="10">DELETE FROM %PREFIX%_preferences WHERE prf_name IN ('db_version', 'db_version_beta')</step>
    <step id="20">ALTER TABLE %PREFIX%_members ADD COLUMN mem_approved integer unsigned default null</step>
    <step id="30">ALTER TABLE %PREFIX%_members ADD COLUMN mem_comment varchar(4000)</step>
    <step id="40">ALTER TABLE %PREFIX%_members ADD COLUMN mem_count_guests integer unsigned not null default '0'</step>
    <step id="50">UPDATE %PREFIX%_preferences SET prf_value = '25' WHERE prf_name = 'lists_members_per_page'</step>
    <step id="60">CREATE TABLE %PREFIX%_messages
        (
            msg_id                        integer         unsigned NOT NULL AUTO_INCREMENT,
            msg_type                      varchar(10)     NOT NULL,
            msg_subject                   varchar(256)    NOT NULL,
            msg_usr_id_sender             integer         unsigned NOT NULL,
            msg_usr_id_receiver           varchar(256)    NOT NULL,
            msg_timestamp                 timestamp       NOT NULL default CURRENT_TIMESTAMP,
            msg_read                      smallint        NOT NULL DEFAULT 0,
            primary key (msg_id)
        )
        engine = InnoDB
        default character set = utf8
        collate = utf8_unicode_ci</step>
    <step id="65">CREATE TABLE %PREFIX%_messages_content
        (
            msc_id                        integer         unsigned NOT NULL AUTO_INCREMENT,
            msc_msg_id                    integer         unsigned NOT NULL,
            msc_part_id                   integer         unsigned NOT NULL,
            msc_usr_id                    integer         unsigned,
            msc_message                   text            NOT NULL,
            msc_timestamp                 timestamp       NOT NULL default CURRENT_TIMESTAMP,
            primary key (msc_id)
        )
        engine = InnoDB
        default character set = utf8
        collate = utf8_unicode_ci</step>
    <step id="70">UPDATE %PREFIX%_preferences SET prf_value = '0' WHERE prf_name = 'enable_pm_module'</step>
    <step id="80">DELETE FROM %PREFIX%_preferences WHERE prf_name IN ('enable_forum_interface', 'forum_version', 'forum_export_user', 'forum_praefix', 'forum_sqldata_from_admidio', 'forum_db', 'forum_srv', 'forum_usr', 'forum_pw', 'forum_set_admin', 'forum_link_intern', 'forum_width')</step>
    <step id="90">DELETE FROM %PREFIX%_preferences WHERE prf_name IN ('dates_show_calendar_select')</step>
    <step id="100" database="mysql">UPDATE %PREFIX%_preferences pr1 INNER JOIN %PREFIX%_preferences pr2 ON pr2.prf_name = 'system_show_all_users' SET pr1.prf_value = pr2.prf_value WHERE pr1.prf_name = 'members_show_all_users'</step>
    <step id="105" database="pgsql">UPDATE %PREFIX%_preferences pr1 SET prf_value = pr2.prf_value FROM %PREFIX%_preferences pr2 WHERE pr2.prf_name = 'system_show_all_users' AND pr1.prf_name = 'members_show_all_users'</step>
    <step id="110">DELETE FROM %PREFIX%_preferences WHERE prf_name IN ('system_show_all_users')</step>
    <step id="120" database="mysql">ALTER TABLE %PREFIX%_preferences MODIFY COLUMN prf_name varchar(50) NOT NULL</step>
    <step id="130" database="pgsql">ALTER TABLE %PREFIX%_preferences ALTER COLUMN prf_name TYPE varchar(50)</step>
    <step id="160">INSERT INTO %PREFIX%_components (com_type, com_name, com_name_intern, com_version, com_beta)
                         VALUES ('MODULE', 'SYS_ANNOUNCEMENTS', 'ANNOUNCEMENTS', '3.0.0', 0)
                              , ('MODULE', 'SYS_DATABASE_BACKUP', 'BACKUP', '3.0.0', 0)
                              , ('MODULE', 'SYS_CATEGORIES', 'CATEGORIES', '3.0.0', 0)
                              , ('MODULE', 'SYS_EVENTS', 'DATES', '3.0.0', 0)
                              , ('MODULE', 'DOW_DOWNLOADS', 'DOWNLOADS', '3.0.0', 0)
                              , ('MODULE', 'GBO_GUESTBOOK', 'GUESTBOOK', '3.0.0', 0)
                              , ('MODULE', 'SYS_WEBLINKS', 'LINKS', '3.0.0', 0)
                              , ('MODULE', 'LST_LISTS', 'LISTS', '3.0.0', 0)
                              , ('MODULE', 'SYS_USER_MANAGEMENT', 'MEMBERS', '3.0.0', 0)
                              , ('MODULE', 'SYS_MESSAGES', 'MESSAGES', '3.0.0', 0)
                              , ('MODULE', 'PHO_PHOTOS', 'PHOTOS', '3.0.0', 0)
                              , ('MODULE', 'SYS_SETTINGS', 'PREFERENCES', '3.0.0', 0)
                              , ('MODULE', 'SYS_PROFILE', 'PROFILE', '3.0.0', 0)
                              , ('MODULE', 'SYS_REGISTRATION', 'REGISTRATION', '3.0.0', 0)
                              , ('MODULE', 'ROL_ROLE_ADMINISTRATION', 'ROLES', '3.0.0', 0)
                              , ('MODULE', 'SYS_ROOM_MANAGEMENT', 'ROOMS', '3.0.0', 0)</step>
    <step id="170" database="mysql">ALTER TABLE %PREFIX%_roles MODIFY COLUMN rol_name varchar(100) NOT NULL</step>
    <step id="180" database="pgsql">ALTER TABLE %PREFIX%_roles ALTER COLUMN rol_name TYPE varchar(100)</step>
    <step id="190" database="mysql">ALTER TABLE %PREFIX%_roles MODIFY COLUMN rol_location varchar(100)</step>
    <step id="200" database="pgsql">ALTER TABLE %PREFIX%_roles ALTER COLUMN rol_location TYPE varchar(100)</step>
    <step id="220">DELETE FROM %PREFIX%_preferences WHERE prf_name IN ('photo_thumbs_column', 'photo_thumbs_row')</step>
    <step id="230" database="mysql">ALTER TABLE %PREFIX%_user_data MODIFY COLUMN usd_value varchar(4000)</step>
    <step id="240" database="pgsql">ALTER TABLE %PREFIX%_user_data ALTER COLUMN usd_value TYPE varchar(4000)</step>
    <step id="250" database="mysql">ALTER TABLE %PREFIX%_roles MODIFY COLUMN rol_description varchar(4000)</step>
    <step id="260" database="pgsql">ALTER TABLE %PREFIX%_roles ALTER COLUMN rol_description TYPE varchar(4000)</step>
    <step id="270" database="mysql">ALTER TABLE %PREFIX%_user_log MODIFY COLUMN usl_value_old varchar(4000)</step>
    <step id="280" database="pgsql">ALTER TABLE %PREFIX%_user_log ALTER COLUMN usl_value_old TYPE varchar(4000)</step>
    <step id="290" database="mysql">ALTER TABLE %PREFIX%_user_log MODIFY COLUMN usl_value_new varchar(4000)</step>
    <step id="300" database="pgsql">ALTER TABLE %PREFIX%_user_log ALTER COLUMN usl_value_new TYPE varchar(4000)</step>
    <step id="310" database="mysql">ALTER TABLE %PREFIX%_user_fields MODIFY COLUMN usf_icon varchar(2000)</step>
    <step id="320" database="pgsql">ALTER TABLE %PREFIX%_user_fields ALTER COLUMN usf_icon TYPE varchar(2000)</step>
    <step id="330" database="mysql">ALTER TABLE %PREFIX%_user_fields MODIFY COLUMN usf_url varchar(2000)</step>
    <step id="340" database="pgsql">ALTER TABLE %PREFIX%_user_fields ALTER COLUMN usf_url TYPE varchar(2000)</step>
    <step id="350" database="mysql">ALTER TABLE %PREFIX%_links MODIFY COLUMN lnk_url varchar(2000)</step>
    <step id="360" database="pgsql">ALTER TABLE %PREFIX%_links ALTER COLUMN lnk_url TYPE varchar(2000)</step>
    <step id="370">UPDATE %PREFIX%_user_fields SET usf_type = 'DECIMAL' WHERE usf_type = 'NUMERIC'</step>
    <step id="380">CREATE TABLE IF NOT EXISTS %PREFIX%_invent_fields
            (
               inf_id                         integer       unsigned not null AUTO_INCREMENT,
               inf_cat_id                     integer       unsigned not null,
               inf_type                       varchar(30)   not null,
               inf_name_intern                varchar(110)  not null,
               inf_name                       varchar(100)  not null,
               inf_description                text,
               inf_value_list                 text,
               inf_system                     boolean       not null default '0',
               inf_disabled                   boolean       not null default '0',
               inf_hidden                     boolean       not null default '0',
               inf_mandatory                  boolean       not null default '0',
               inf_sequence                   smallint      not null,
               inf_usr_id_create              integer       unsigned,
               inf_timestamp_create           timestamp     not null default CURRENT_TIMESTAMP,
               inf_usr_id_change              integer       unsigned,
               inf_timestamp_change           timestamp     null default null,
               primary key (inf_id)
            )
            engine = InnoDB auto_increment = 1 default character set = utf8 collate = utf8_unicode_ci;</step>
    <step id="390">CREATE TABLE IF NOT EXISTS %PREFIX%_invent_data
            (
               ind_id                         integer       unsigned not null AUTO_INCREMENT,
               ind_itm_id                     integer       unsigned not null,
               ind_inf_id                     integer       unsigned not null,
               ind_value                      varchar(255),
               primary key (ind_id)
            )
            engine = InnoDB auto_increment = 1 default character set = utf8 collate = utf8_unicode_ci;</step>
    <step id="400">CREATE TABLE IF NOT EXISTS %PREFIX%_invent
            (
               inv_id                         integer       unsigned not null AUTO_INCREMENT,
               inv_photo                      blob,
               inv_text                       text,
               inv_for_loan                   boolean       not null default '0',
               inv_last_lent                  timestamp     null default null,
               inv_usr_id_lent                integer         unsigned,
               inv_lent_until                 timestamp     null default null,
               inv_number_lent                integer       not null default 0,
               inv_usr_id_create              integer       unsigned,
               inv_timestamp_create           timestamp     not null default CURRENT_TIMESTAMP,
               inv_usr_id_change              integer       unsigned,
               inv_timestamp_change           timestamp     null default null,
               inv_valid                      boolean       not null default '0',
               primary key (inv_id)
            )
            engine = InnoDB auto_increment = 1 default character set = utf8 collate = utf8_unicode_ci;</step>
    <step id="430" database="mysql">UPDATE %PREFIX%_preferences pr1 INNER JOIN %PREFIX%_preferences pr2 ON pr2.prf_name = 'ecard_view_width' SET pr1.prf_value = pr2.prf_value WHERE pr1.prf_name = 'ecard_thumbs_scale'</step>
    <step id="435" database="pgsql">UPDATE %PREFIX%_preferences pr1 SET prf_value = pr2.prf_value FROM %PREFIX%_preferences pr2 WHERE pr2.prf_name = 'ecard_view_width' AND pr1.prf_name = 'ecard_thumbs_scale'</step>
    <step id="440">DELETE FROM %PREFIX%_preferences WHERE prf_name IN ('ecard_view_width', 'ecard_view_height', 'enable_ecard_cc_recipients', 'ecard_cc_recipients', 'photo_upload_mode', 'photo_slideshow_speed')</step>
    <step id="450">ALTER TABLE %PREFIX%_roles ADD COLUMN rol_inventory boolean not null default '0'</step>
    <step id="460">alter table %PREFIX%_messages add constraint %PREFIX%_FK_MSG_USR_SENDER foreign key (msg_usr_id_sender) references %PREFIX%_users (usr_id) on delete restrict on update restrict</step>
    <step id="480">create index IDX_MSC_PART_ID on %PREFIX%_messages_content (msc_part_id)</step>
    <step id="500">alter table %PREFIX%_messages_content add constraint %PREFIX%_FK_MSC_USR_ID foreign key (msc_usr_id) references %PREFIX%_users (usr_id) on delete set null on update restrict</step>
    <step id="510">alter table %PREFIX%_messages_content add constraint %PREFIX%_FK_MSC_MSG_ID foreign key (msc_msg_id) references %PREFIX%_messages (msg_id) on delete restrict on update restrict</step>
    <step id="520">create table %PREFIX%_ids
            (
               ids_usr_id                     integer       unsigned not null,
               ids_reference_id               integer       unsigned not null
            )
            engine = InnoDB
            default character set = utf8
            collate = utf8_unicode_ci;</step>
    <step id="530">alter table %PREFIX%_ids add constraint %PREFIX%_FK_IDS_USR_ID foreign key (ids_usr_id) references %PREFIX%_users (usr_id) on delete restrict on update restrict</step>
    <step id="540">UPDATE %PREFIX%_preferences SET prf_value = '13' WHERE prf_name = 'captcha_signature_font_size' AND prf_value = '9'</step>
    <step id="550">UpdateStepsCode::updateStep30DeleteDateRoles</step>
    <step id="560">create unique index IDX_%PREFIX%_IND_ITM_INF_ID on %PREFIX%_invent_data (ind_itm_id, ind_inf_id)</step>
    <step id="570">create unique index IDX_%PREFIX%_INF_NAME_INTERN on %PREFIX%_invent_fields (inf_name_intern)</step>
    <step id="580" database="mysql">ALTER TABLE %PREFIX%_components MODIFY COLUMN com_beta smallint not null default 0</step>
    <step id="590" database="pgsql">ALTER TABLE %PREFIX%_components ALTER COLUMN com_beta SET DEFAULT 0</step>
    <step id="600" database="pgsql">ALTER TABLE %PREFIX%_components ALTER COLUMN com_beta SET NOT NULL</step>
    <step id="610">UPDATE %PREFIX%_preferences SET prf_value = 'modern' WHERE prf_name = 'theme'</step>
    <step>stop</step>
</update>
