--
-- Create Table 'module_forms_webservices'
--
CREATE TABLE `module_forms_webservices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `params` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Create Table 'module_forms_webservices_fields'
--
CREATE TABLE `module_forms_webservices_fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `webservice_id` int(11) NOT NULL,
  `label` text NOT NULL,
  `name` varchar(255) NOT NULL,
  `hidden` tinyint(4) DEFAULT '0',
  `default_value` text,
  PRIMARY KEY (`id`),
  KEY `fk_webservices_webservices_fields` (`webservice_id`),
  CONSTRAINT `fk_webservices_webservices_fields`
      FOREIGN KEY (`webservice_id`)
      REFERENCES `module_forms_webservices` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Create Table 'module_forms_forms'
--
CREATE TABLE `module_forms_forms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `status` tinyint(4) DEFAULT '0',
  `webservice_id` int(11) DEFAULT NULL,
  `email_notify` varchar(255) DEFAULT NULL,
  `confirmation_text` text,
  PRIMARY KEY (`id`),
  KEY `fk_module_forms_forms_webservice` (`webservice_id`),
  CONSTRAINT `fk_module_forms_forms_webservice` FOREIGN KEY (`webservice_id`) REFERENCES `module_forms_webservices` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Create Table 'module_forms_types'
--
CREATE TABLE `module_forms_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Create Table 'module_forms_questions'
--
CREATE TABLE `module_forms_questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question` TEXT NOT NULL,
  `type_id` INT NOT NULL,
  `description` TEXT DEFAULT NULL,
  `variable` INT DEFAULT NULL,
  `multi_selections` TINYINT DEFAULT 0,
  `required` TINYINT DEFAULT 1,
  PRIMARY KEY(`id`),
  CONSTRAINT `fk_module_forms_types_questions`
	    FOREIGN KEY (`type_id`)
      REFERENCES `module_forms_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Create Table 'module_forms_choices'
--
CREATE TABLE `module_forms_choices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question_id` INT NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY(`id`),
  CONSTRAINT `fk_module_forms_questions_choices`
	    FOREIGN KEY (`question_id`)
      REFERENCES `module_forms_questions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Create Table 'module_forms_questions_forms'
--
CREATE TABLE `module_forms_questions_forms` (
	`id` INT AUTO_INCREMENT NOT NULL,
    `form_id` INT NOT NULL,
    `question_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_module_forms_questions_forms`
        FOREIGN KEY (`form_id`)
        REFERENCES `module_forms_forms` (`id`),
	  CONSTRAINT `fk_module_forms_forms_questions`
		    FOREIGN KEY (`question_id`)
        REFERENCES `module_forms_questions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Alter Table 'module_forms_questions'
--
ALTER TABLE `module_forms_questions`
ADD COLUMN `max_characters` INTEGER DEFAULT NULL;

--
-- Insert Table 'module_forms_types'
--
INSERT INTO `module_forms_types` (`id`, `name`) VALUES
    ('10', 'Short text'),
    ('11', 'Long text'),
    ('12', 'Date'),
    ('13', 'Hour'),
    ('14', 'Multiple choice'),
    ('15', 'Number'),
    ('16', 'Yes / No'),
    ('17', 'Email'),
    ('18', 'Website'),
    ('19', 'Stars'),
    ('20', 'Question group'),
    ('21', 'Opinion scale'),
    ('22', 'Statement'),
    ('23', 'File upload');

--
-- Alter Table 'module_forms_questions'
--
ALTER TABLE `module_forms_questions`
ADD COLUMN `range_value` VARCHAR(255) DEFAULT NULL;

--
-- Alter Table 'module_forms_questions'
--
ALTER TABLE `module_forms_questions`
ADD COLUMN `parent_id` INT DEFAULT NULL,
ADD COLUMN `sorder` INT DEFAULT NULL;

--
-- Create Table 'module_forms_conditions'
--
CREATE TABLE `module_forms_conditions`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `condition` VARCHAR(255) NOT NULL,
    `operator` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Create Table 'module_forms_types_conditions'
--
CREATE TABLE `module_forms_types_conditions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type_id` int(11) NOT NULL,
  `condition_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_module_forms_types`
		  FOREIGN KEY (`type_id`)
      REFERENCES `module_forms_types` (`id`),
  CONSTRAINT `fk_module_forms_conditions_types`
		  FOREIGN KEY (`condition_id`)
      REFERENCES `module_forms_conditions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Insert Table 'module_forms_conditions'
--
INSERT INTO `module_forms_conditions` (`id`, `condition`, `operator`) VALUES
  ('1', 'is equal to', '=='),
  ('2', 'is not equal to', '!='),
  ('3', 'begins with', ''),
  ('4', 'ends with', ''),
  ('5', 'contains', ''),
  ('6', 'does not contain', ''),
  ('9', 'is lower than', '<'),
  ('10', 'lower than or equal to', '<='),
  ('11', 'is greater than', '>'),
  ('12', 'greater than or equal to', '>='),
  ('13', 'is', ''),
  ('14', 'is not', ''),
  ('15', 'is on', ''),
  ('16', 'is not on', ''),
  ('17', 'is before', ''),
  ('18', 'is before or on', ''),
  ('19', 'is after', ''),
  ('20', 'is after or on', '');

--
-- Create Table 'module_forms_logic_jumps'
--
CREATE TABLE `module_forms_logic_jumps` (
    `id`            INT AUTO_INCREMENT NOT NULL,
    `question_id`   INT NOT NULL,
    `condition_id`  INT NOT NULL,
    `value`         VARCHAR(255) DEFAULT NULL,
    `jump_to`       INT NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_module_forms_logic_jumps_questions`
		    FOREIGN KEY (`question_id`)
        REFERENCES `module_forms_questions` (`id`),
    CONSTRAINT `fk_module_forms_logic_jumps_conditions`
		    FOREIGN KEY (`condition_id`)
        REFERENCES `module_forms_conditions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Insert into 'module_forms_types_conditions'
--
INSERT INTO `module_forms_types_conditions` (`id`,`type_id`,`condition_id`) VALUES
('1', '10', '1'),
('2', '10', '3'),
('3', '10', '4'),
('18', '10', '5'),
('19', '10', '2'),
('22', '11', '3'),
('23', '11', '6'),
('24', '10', '6'),
('25', '15', '1'),
('26', '15', '2'),
('27', '15', '9'),
('28', '15', '10'),
('29', '15', '11'),
('30', '15', '12'),
('31', '17', '1'),
('32', '17', '2'),
('33', '17', '3'),
('34', '17', '4'),
('35', '17', '5'),
('36', '17', '6');

--
-- Alter Table 'module_forms_logic_jumps'
--
-- ALTER TABLE `module_forms_logic_jumps`
-- DROP COLUMN `default_jump_to`;

--
-- Alter Table 'module_forms_questions'
--
ALTER TABLE `module_forms_questions`
ADD COLUMN `default_jump_to` INT DEFAULT NULL AFTER `parent_id`;

--
-- Insert into 'module_forms_types_conditions'
--
INSERT INTO `module_forms_types_conditions` (`id`,`type_id`,`condition_id`) VALUES
('37', '14', '13'),
('38', '14', '14'),
('39', '12', '15'),
('40', '12', '16'),
('41', '12', '18'),
('42', '12', '17'),
('43', '12', '19'),
('44', '12', '20'),
('45', '16', '13'),
('46', '16', '14'),
('47', '21', '1'),
('48', '21', '2'),
('49', '21', '9'),
('50', '21', '10'),
('51', '21', '11'),
('52', '21', '12'),
('53', '19', '1'),
('54', '19', '2'),
('55', '19', '9'),
('56', '19', '10'),
('57', '19', '11'),
('58', '19', '12');

--
-- Alter Table 'module_forms_questions'
--
ALTER TABLE `module_forms_questions`
ADD COLUMN `webservice_field_id` INT DEFAULT NULL AFTER `default_jump_to`;

--
-- Alter Table 'module_forms_questions'
--
ALTER TABLE `module_forms_questions`
ADD COLUMN `multiple_choice` TINYINT DEFAULT 0 AFTER `variable`;

--
-- Alter Table 'module_forms_choices'
--
ALTER TABLE `module_forms_choices`
ADD COLUMN `sorder` INT DEFAULT NULL;



--
-- Alter Table 'module_forms_questions'
--
ALTER TABLE `module_forms_types`
ADD COLUMN `alias` VARCHAR(255) NOT NULL;

UPDATE `module_forms_types` SET `alias`='short_text' WHERE `id`='10';
UPDATE `module_forms_types` SET `alias`='long_text' WHERE `id`='11';
UPDATE `module_forms_types` SET `alias`='date' WHERE `id`='12';
UPDATE `module_forms_types` SET `alias`='hour' WHERE `id`='13';
UPDATE `module_forms_types` SET `alias`='multiple_choice' WHERE `id`='14';
UPDATE `module_forms_types` SET `alias`='number' WHERE `id`='15';
UPDATE `module_forms_types` SET `alias`='bollean' WHERE `id`='16';
UPDATE `module_forms_types` SET `alias`='email' WHERE `id`='17';
UPDATE `module_forms_types` SET `alias`='website' WHERE `id`='18';
UPDATE `module_forms_types` SET `alias`='stars' WHERE `id`='19';
UPDATE `module_forms_types` SET `alias`='question_group' WHERE `id`='20';
UPDATE `module_forms_types` SET `alias`='scale' WHERE `id`='21';
UPDATE `module_forms_types` SET `alias`='statement' WHERE `id`='22';
UPDATE `module_forms_types` SET `alias`='file_upload' WHERE `id`='23';