SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE `articles`
  CHANGE `is_showed` `is_showed` tinyint(1) DEFAULT '1';

ALTER TABLE `articles_refnm`
  CHANGE `ref_nm` `ref_nm` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'article reference name';

ALTER TABLE `cnslt`
  CHANGE `proj` `proj` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'gehört zu SD oder zur eigst Jugpol',
  CHANGE `inp_fr` `inp_fr` datetime DEFAULT NULL COMMENT 'Input possible from date on',
  CHANGE `inp_to` `inp_to` datetime DEFAULT NULL COMMENT 'Input possible till',
  CHANGE `is_input_phase_showed` `is_input_phase_showed` tinyint(1) NOT NULL DEFAULT '1',
  CHANGE `is_support_phase_showed` `is_support_phase_showed` tinyint(1) NOT NULL DEFAULT '0',
  CHANGE `spprt_fr` `spprt_fr` datetime DEFAULT NULL COMMENT 'support button clickable from',
  CHANGE `spprt_to` `spprt_to` datetime DEFAULT NULL COMMENT 'Supporting possible until',

  CHANGE `vot_fr` `vot_fr` datetime DEFAULT NULL COMMENT 'Voting possible from date on',
  CHANGE `vot_to` `vot_to` datetime DEFAULT NULL COMMENT 'Voting possible till',
  CHANGE `is_voting_phase_showed` `is_voting_phase_showed` tinyint(1) NOT NULL DEFAULT '1',
  CHANGE `vot_expl` `vot_expl` text COLLATE utf8mb4_unicode_ci COMMENT 'info text for voting start',
  CHANGE `is_voting_result_phase_showed` `is_voting_result_phase_showed` tinyint(1) NOT NULL DEFAULT '0',
  CHANGE `is_followup_phase_showed` `is_followup_phase_showed` tinyint(1) NOT NULL DEFAULT '0',

  CHANGE `titl` `titl` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Title of consultation',
  CHANGE `titl_short` `titl_short` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Shortened title (for slider, mails etc.)',
  CHANGE `titl_sub` `titl_sub` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'subtitle (optional)',

  CHANGE `img_expl` `img_expl` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'explanatory text for title graphics',
  CHANGE `expl_short` `expl_short` text COLLATE utf8mb4_unicode_ci NOT NULL,
  CHANGE `ln` `ln` char(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Language',
  CHANGE `is_public` `is_public` tinyint(1) NOT NULL DEFAULT '0',

  CHANGE `is_vt_finalized` `is_vt_finalized` tinyint(1) NOT NULL DEFAULT '1',
  CHANGE `is_vt_anonymized` `is_vt_anonymized` tinyint(1) NOT NULL DEFAULT '0',
  CHANGE `phase_info` `phase_info` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
  CHANGE `phase_support` `phase_support` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
  CHANGE `phase_input` `phase_input` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
  CHANGE `phase_voting` `phase_voting` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
  CHANGE `phase_followup` `phase_followup` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',

  CHANGE `state_field_label` `state_field_label` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',

  ADD `anonymous_contribution` tinyint(1) NOT NULL DEFAULT '0',
  ADD `anonymous_contribution_finish_info` text COLLATE utf8mb4_unicode_ci;



ALTER TABLE `dirs`
  CHANGE `dir_name` `dir_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '';


ALTER TABLE `email`
  CHANGE `project_code` `project_code` char(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  CHANGE `sent_by_user` `sent_by_user` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
  CHANGE `subject` `subject` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
  CHANGE `body_html` `body_html` text COLLATE utf8mb4_unicode_ci NOT NULL,
  CHANGE `body_text` `body_text` text COLLATE utf8mb4_unicode_ci NOT NULL;


ALTER TABLE `email_attachment`
  CHANGE `filepath` `filepath` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '';

ALTER TABLE `email_attachment`
    DROP FOREIGN KEY `email_attachment_email_id_ibfk`;

ALTER TABLE `email_attachment`
    ADD CONSTRAINT `email_attachment_ibfk_1` FOREIGN KEY (`email_id`) REFERENCES `email` (`id`) ON DELETE CASCADE;

ALTER TABLE `email_component`
  CHANGE `name` `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  CHANGE `project_code` `project_code` char(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  CHANGE `body_html` `body_html` text COLLATE utf8mb4_unicode_ci NOT NULL,
  CHANGE `body_text` `body_text` text COLLATE utf8mb4_unicode_ci NOT NULL,
  CHANGE `description` `description` text COLLATE utf8mb4_unicode_ci NOT NULL;


ALTER TABLE `email_placeholder`
  CHANGE `name` `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '';


ALTER TABLE `email_recipient`
  CHANGE `name` `name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT '',
  CHANGE `email` `email` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT '';


-- POSSIBLE ERROR IF keys don't exist
ALTER TABLE `email_recipient`
  DROP FOREIGN KEY `email_recipient_email_id_ibfk`;
ALTER TABLE `email_recipient`
  DROP FOREIGN KEY `email_recipient_ibfk_1`;
ALTER TABLE `email_recipient`
  DROP FOREIGN KEY `email_recipient_ibfk_2`;
-- POSSIBLE ERROR IF keys don't exist

ALTER TABLE `email_recipient`
  ADD CONSTRAINT `email_recipient_ibfk_1` FOREIGN KEY (`type`) REFERENCES `email_recipient_type` (`name`),
  ADD CONSTRAINT `email_recipient_ibfk_2` FOREIGN KEY (`email_id`) REFERENCES `email` (`id`) ON DELETE CASCADE;


ALTER TABLE `email_template`
  CHANGE `name` `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',

  CHANGE `project_code` `project_code` char(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  CHANGE `subject` `subject` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  CHANGE `body_html` `body_html` text COLLATE utf8mb4_unicode_ci NOT NULL,
  CHANGE `body_text` `body_text` text COLLATE utf8mb4_unicode_ci NOT NULL;


ALTER TABLE `email_template_type`
  CHANGE `name` `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '';


ALTER TABLE `footer`
  CHANGE `proj` `proj` char(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '';


ALTER TABLE `fowup_fls`
  CHANGE `titl` `titl` varchar(300) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Title of follow-up document',
  CHANGE `who` `who` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Who gave the follow-up',

  CHANGE `is_only_month_year_showed` `is_only_month_year_showed` tinyint(1) DEFAULT '0',
  CHANGE `ref_doc` `ref_doc` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'reference to downloadable document',
  CHANGE `ref_view` `ref_view` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'introduction to viewable version of document',
  CHANGE `gfx_who` `gfx_who` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Graphic of who',

  ADD `type` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT 'general';

ALTER TABLE `fowup_fls`
  ADD  KEY `type` (`type`),
  ADD CONSTRAINT `fowup_fls_ibfk_1` FOREIGN KEY (`type`) REFERENCES `fowups_type` (`name`);


ALTER TABLE `fowups`
  CHANGE  `embed` `embed` varchar(600) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'embedding for multimedia',
  CHANGE `expl` `expl` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Erläuterung',

  DROP `hlvl`;


ALTER TABLE `help_text`
  CHANGE `name` `name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT '',

  CHANGE `project_code` `project_code` char(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  CHANGE `module` `module` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'default';


ALTER TABLE `inpt`
  CHANGE `thes` `thes` varchar(330) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'User reply',
  CHANGE `expl` `expl` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Longer explanation',

  CHANGE `tg_nrs` `tg_nrs` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Nummern der Keywords (100-999), max 14 Tags',
  CHANGE `notiz` `notiz` varchar(300) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Notes for internal use',

  CHANGE `video_id` `video_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '';


ALTER TABLE `input_discussion`
  CHANGE `video_id` `video_id` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT '';


ALTER TABLE `license`
  CHANGE `title` `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  CHANGE `description` `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  CHANGE `text` `text` text COLLATE utf8mb4_unicode_ci NOT NULL,
  CHANGE `link` `link` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  CHANGE `icon` `icon` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  CHANGE `alt` `alt` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '';


ALTER TABLE `notification_type`
  CHANGE `name` `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '';


ALTER TABLE `proj`
  CHANGE `titl_short` `titl_short` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Short title/name for project',
  CHANGE `vot_q` `vot_q` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Question used for voting',

  CHANGE `color_accent_1` `color_accent_1` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
  CHANGE `color_primary` `color_primary` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
  CHANGE `color_accent_2` `color_accent_2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
  CHANGE `logo` `logo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
  CHANGE `favicon` `favicon` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',

  CHANGE `locale` `locale` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',

  ADD `teaser_enabled` int(1) NOT NULL DEFAULT '1';


ALTER TABLE `quests`
  CHANGE `nr` `nr` varchar(4) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Number shown in ordered list',
  CHANGE `q` `q` varchar(300) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'The question itself',
  CHANGE `q_xpl` `q_xpl` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Explanation for question',
  CHANGE `ln` `ln` char(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Language',
  CHANGE `vot_q` `vot_q` varchar(220) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Introducing voting question';


ALTER TABLE `tgs`
  CHANGE `tg_de` `tg_de` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'German translation of tag';


ALTER TABLE `theme`
  CHANGE `name` `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  CHANGE `color_accent_1` `color_accent_1` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  CHANGE `color_primary` `color_primary` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  CHANGE `color_accent_2` `color_accent_2` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '';


ALTER TABLE `urlkey_action`
  CHANGE `urlkey` `urlkey` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',

  CHANGE `handler_class` `handler_class` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '';


ALTER TABLE `user_info`
  CHANGE `cmnt` `cmnt` varchar(400) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Internal comments for admins',
  CHANGE `source` `source` set('d','g','p','m') COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Dialogue, Group, Misc, Position paper',
  CHANGE `src_misc` `src_misc` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'explanation of misc source',

  CHANGE `name_group` `name_group` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Name of group',
  CHANGE `name_pers` `name_pers` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Name of contact person',

  CHANGE `regio_pax` `regio_pax` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Bundesländer',
  CHANGE `is_receiving_consultation_results` `is_receiving_consultation_results` tinyint(1) DEFAULT '1',
  CHANGE `date_added` `date_added` timestamp NULL DEFAULT NULL,
  CHANGE `cmnt_ext` `cmnt_ext` varchar(600) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',

  CHANGE `confirmation_key` `confirmation_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
  CHANGE `name` `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '';


ALTER TABLE `users`
  CHANGE `name` `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
  CHANGE `email` `email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Mail Address',
  CHANGE `password` `password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
  CHANGE `is_subscribed_newsletter` `is_subscribed_newsletter` tinyint(1) DEFAULT '0',
  CHANGE `cmnt` `cmnt` varchar(400) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Internal comments for admins',

  CHANGE `source` `source` set('d','g','p','m') COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Dialogue, Group, Misc, Position paper',
  CHANGE `src_misc` `src_misc` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Explanation of misc source',

  CHANGE `name_group` `name_group` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Name of group',
  CHANGE `name_pers` `name_pers` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Name of contact person',

  CHANGE `regio_pax` `regio_pax` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',

  CHANGE `nick` `nick` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '';


ALTER TABLE `video_service`
  CHANGE `name` `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL;


ALTER TABLE `vt_final`
  CHANGE `is_followups` `is_followups` tinyint(1) NOT NULL DEFAULT '0',
  CHANGE `id` `id` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'md5 (tid''.-.''uid)';


ALTER TABLE `vt_grps`
  CHANGE `sub_user` `sub_user` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'email address',
  CHANGE `sub_uid` `sub_uid` char(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'md5 of mail.kid',

  CHANGE `vt_inp_list` `vt_inp_list` text COLLATE utf8mb4_unicode_ci COMMENT 'list of votable tids',
  CHANGE `vt_rel_qid` `vt_rel_qid` text COLLATE utf8mb4_unicode_ci COMMENT 'list of rel QIDs',
  CHANGE `vt_tg_list` `vt_tg_list` text COLLATE utf8mb4_unicode_ci COMMENT 'list of all (still) available tags for this user';


ALTER TABLE `vt_indiv`
  CHANGE `sub_uid` `sub_uid` char(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'individual subuser',
  CHANGE `is_pimp` `is_pimp` tinyint(1) DEFAULT '0',
  CHANGE `confirmation_hash` `confirmation_hash` char(32) COLLATE utf8mb4_unicode_ci DEFAULT '';


ALTER TABLE `vt_rights`
  CHANGE `vt_code` `vt_code` char(8) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Voting access code for this group';


ALTER TABLE `vt_settings`
  CHANGE `is_btn_important` `is_btn_important` tinyint(1) NOT NULL DEFAULT '0',

  CHANGE `btn_important_label` `btn_important_label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Ist mir besonders wichtig' COMMENT 'Label for the super preference button',

  CHANGE `btn_labels` `btn_labels` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Stimme nicht zu,Nicht wichtig,Wichtig,Sehr wichtig,Super wichtig' COMMENT 'labels of voting buttons, comma-separated';
SET FOREIGN_KEY_CHECKS=1;

