START TRANSACTION;

CREATE DATABASE IF NOT EXISTS `TAK_multiedicion_quiz` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`scheduled_report` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `emails` text COLLATE utf8_unicode_ci COMMENT 'tipo json',
  `tipo` varchar(125) COLLATE utf8_unicode_ci DEFAULT 'mensual' COMMENT '"semanal", "mensual"',
  `frecuencia` varchar(125) COLLATE utf8_unicode_ci DEFAULT 'first' COMMENT 'mensual: first, middle, last; semanal: 1, 2, 3, 4, 5, 6, 7',
  `is_active` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`about` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `multimedia_id` bigint(20) DEFAULT NULL,
  `rank` bigint(20) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `multimedia_id_idx` (`multimedia_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`administrator_session` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `administrator_id` bigint(20) DEFAULT NULL,
  `ip` varchar(50) DEFAULT NULL,
  `user_agent` text,
  `accept_language` varchar(255) NOT NULL,
  `date_logout` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `administrator_id_idx` (`administrator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`answer` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `game_id` bigint(20) DEFAULT NULL,
  `question_id` bigint(20) DEFAULT NULL,
  `question_option_id` bigint(20) DEFAULT NULL,
  `points` float(18,2) DEFAULT '0.00',
  `is_completed` tinyint(1) DEFAULT '0',
  `is_correct` tinyint(1) DEFAULT '0',
  `date_started` datetime DEFAULT NULL,
  `date_completed` datetime DEFAULT NULL,
  `duration` bigint(20) DEFAULT '0',
  `culture` varchar(10) DEFAULT NULL,
  `calculated_type` varchar(20) DEFAULT NULL,
  `level` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `game_id_idx` (`game_id`),
  KEY `question_id_idx` (`question_id`),
  KEY `question_option_id_idx` (`question_option_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `piece_id` bigint(20) DEFAULT NULL,
  `multimedia_id` bigint(20) DEFAULT NULL,
  `num_questions` bigint(20) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `piece_id_idx` (`piece_id`),
  KEY `multimedia_id_idx` (`multimedia_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`category_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`challenge` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `player1_id` bigint(20) DEFAULT NULL,
  `player2_id` bigint(20) DEFAULT NULL,
  `date_started` datetime DEFAULT NULL,
  `date_finished` datetime DEFAULT NULL,
  `winner_id` bigint(20) DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL,
  `points_player1` bigint(20) DEFAULT NULL,
  `points_player2` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `player1_id_idx` (`player1_id`),
  KEY `player2_id_idx` (`player2_id`),
  KEY `winner_id_idx` (`winner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`challenge_answer` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `challenge_id` bigint(20) DEFAULT NULL,
  `answer_id` bigint(20) DEFAULT NULL,
  `miliseconds` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `challenge_id_idx` (`challenge_id`),
  KEY `answer_id_idx` (`answer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`configuration` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `type` varchar(20) DEFAULT NULL,
  `is_visible` tinyint(1) NOT NULL DEFAULT '1',
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  `slug` varchar(100) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `number` float(18,2) NOT NULL DEFAULT '0.00',
  `number_min` float(18,2) NOT NULL DEFAULT '0.00',
  `number_max` float(18,2) NOT NULL DEFAULT '0.00',
  `date_from` datetime DEFAULT NULL,
  `date_to` datetime DEFAULT NULL,
  `is_checked` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `TAK_multiedicion_quiz`.`configuration` (`id`, `type`, `is_visible`, `name`, `description`, `slug`, `category`, `number`, `number_min`, `number_max`, `date_from`, `date_to`, `is_checked`) VALUES
(1, 'video', 1, 'Vídeo introducción', 'Datos del vídeo de introducción', 'introduction-video', 'start', 0.00, 0.00, 0.00, NULL, NULL, 0),
(2, 'download', 1, 'Instrucciones del juego', 'Breve descripción de las instrucciones, y archivo con las mismas', 'manual', 'start', 0.00, 0.00, 0.00, NULL, NULL, 0);

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`configuration_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `plain_text` text,
  `long_text_wiki` text,
  `long_text_html` text,
  `file_name` varchar(100) DEFAULT NULL,
  `file_mime_type` varchar(20) DEFAULT NULL,
  `file_size` bigint(20) DEFAULT NULL,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `TAK_multiedicion_quiz`.`configuration_translation` (`id`, `plain_text`, `long_text_wiki`, `long_text_html`, `file_name`, `file_mime_type`, `file_size`, `lang`) VALUES
(1, NULL, 'Welcome to this educational activity, mainly aimed at enhancing knowledge about in a playful way. We propose you a fun game with questions related to various aspects of the company, where you can compete with your colleagues\r\n', '<p>Welcome to this educational activity, mainly aimed at enhancing\r\nknowledge about in a playful way. We propose you a fun game\r\nwith questions related to various aspects of the company, where you\r\ncan compete with your colleagues</p>\r\n', 'presentation-en.mp4', NULL, NULL, 'en'),
(1, NULL, 'Te damos la bienvenida a esta acción formativa cuyo objetivo principal es estimular el aprendizaje sobre , de manera lúdica. Te proponemos un divertido juego de preguntas relacionadas con diferentes temas sobre la compañía, en el que podrás competir con tus compañeros.\r\n', '<p>Te damos la bienvenida a esta acción formativa cuyo objetivo principal\r\nes estimular el aprendizaje sobre , de manera lúdica. Te proponemos\r\nun divertido juego de preguntas relacionadas con diferentes temas sobre\r\nla compañía, en el que podrás competir con tus compañeros.</p>\r\n', 'presentation-es.mp4', NULL, NULL, 'es'),
(2, NULL, 'Learn about the rules of the game and ask any questions you may have while playing. How the game works, how to score points, the ranking and tools like changing languages.\n', '<p>Learn about the rules of the game and ask any questions you may\nhave while playing. How the game works, how to score points, the\nranking and tools like changing languages.</p>\n', 'manual-en.pdf', NULL, NULL, 'en'),
(2, NULL, 'Conoce las reglas del juego y consulta cualquier duda que te pueda surgir durante la partida. Mecánica de juego, forma de puntuación, ranking y utilidades como el cambio de idioma.\n', '<p>Conoce las reglas del juego y consulta cualquier duda que te pueda surgir\ndurante la partida. Mecánica de juego, forma de puntuación, ranking y\nutilidades como el cambio de idioma.</p>\n', 'manual-es.pdf', NULL, NULL, 'es');

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`custom1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rank` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`custom1_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`custom2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rank` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`custom2_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`custom3` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rank` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`custom3_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`documentation` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `multimedia_id` bigint(20) DEFAULT NULL,
  `piece_id` bigint(20) DEFAULT NULL,
  `rank` bigint(20) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `multimedia_id_idx` (`multimedia_id`),
  KEY `piece_id_idx` (`piece_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`event` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  `remote_id` varchar(20) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`feedback` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `predeterminado` tinyint(1) DEFAULT '0',
  `type` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `TAK_multiedicion_quiz`.`feedback` (`id`, `predeterminado`, `type`) VALUES
(1, 1, 'ok'),
(2, 1, 'error');

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`feedback_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `texto` text,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `TAK_multiedicion_quiz`.`feedback_translation` (`id`, `texto`, `lang`) VALUES
(1, '', 'es'),
(2, '', 'es');

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`forum_message` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title_id` bigint(20) DEFAULT NULL,
  `player_id` bigint(20) DEFAULT NULL,
  `active` tinyint(1) DEFAULT '0',
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `title_id_idx` (`title_id`),
  KEY `player_id_idx` (`player_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`forum_message_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `message` text,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`forum_title` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`forum_title_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `title` text,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`game` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `player_id` bigint(20) DEFAULT NULL,
  `profile_player_id` bigint(20) DEFAULT NULL,
  `zone_id` bigint(20) DEFAULT NULL,
  `event_id` bigint(20) DEFAULT NULL,
  `custom1_id` bigint(20) DEFAULT NULL,
  `custom2_id` bigint(20) DEFAULT NULL,
  `custom3_id` bigint(20) DEFAULT NULL,
  `num_answers_ok` bigint(20) DEFAULT '0',
  `num_answers_error` bigint(20) DEFAULT '0',
  `points` double(18,2) DEFAULT '0.00',
  `is_completed` tinyint(1) DEFAULT '0',
  `date_started` datetime DEFAULT NULL,
  `date_completed` datetime DEFAULT NULL,
  `rank_player` bigint(20) DEFAULT NULL,
  `rank_general` bigint(20) DEFAULT NULL,
  `rank_zone` bigint(20) DEFAULT NULL,
  `rank_event` bigint(20) DEFAULT NULL,
  `rank_custom1` bigint(20) DEFAULT NULL,
  `rank_custom2` bigint(20) DEFAULT NULL,
  `rank_custom3` bigint(20) DEFAULT NULL,
  `initial_level` bigint(20) DEFAULT NULL,
  `level_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `player_id_idx` (`player_id`),
  KEY `profile_player_id_idx` (`profile_player_id`),
  KEY `zone_id_idx` (`zone_id`),
  KEY `event_id_idx` (`event_id`),
  KEY `custom1_id_idx` (`custom1_id`),
  KEY `custom2_id_idx` (`custom2_id`),
  KEY `custom3_id_idx` (`custom3_id`),
  KEY `level_id_idx` (`level_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`game_configuration` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `game_id` bigint(20) DEFAULT NULL,
  `configuration_id` bigint(20) DEFAULT NULL,
  `culture` varchar(10) DEFAULT NULL,
  `num_file_downloads` bigint(20) DEFAULT '0',
  `num_video0` bigint(20) DEFAULT '0',
  `num_video25` bigint(20) DEFAULT '0',
  `num_video50` bigint(20) DEFAULT '0',
  `num_video75` bigint(20) DEFAULT '0',
  `num_video100` bigint(20) DEFAULT '0',
  `date_file_first` datetime DEFAULT NULL,
  `date_file_last` datetime DEFAULT NULL,
  `date_video_first` datetime DEFAULT NULL,
  `date_video_last` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `game_id_idx` (`game_id`),
  KEY `configuration_id_idx` (`configuration_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`game_multimedia` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `game_id` bigint(20) DEFAULT NULL,
  `multimedia_id` bigint(20) DEFAULT NULL,
  `culture` varchar(10) DEFAULT NULL,
  `num_file_downloads` bigint(20) DEFAULT '0',
  `num_video0` bigint(20) DEFAULT '0',
  `num_video25` bigint(20) DEFAULT '0',
  `num_video50` bigint(20) DEFAULT '0',
  `num_video75` bigint(20) DEFAULT '0',
  `num_video100` bigint(20) DEFAULT '0',
  `date_file_first` datetime DEFAULT NULL,
  `date_file_last` datetime DEFAULT NULL,
  `date_video_first` datetime DEFAULT NULL,
  `date_video_last` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `game_id_idx` (`game_id`),
  KEY `multimedia_id_idx` (`multimedia_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`glosary` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) DEFAULT '1',
  `letter` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`glosary_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  `example` text,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`instructions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `multimedia_id` bigint(20) DEFAULT NULL,
  `rank` bigint(20) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `multimedia_id_idx` (`multimedia_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`level` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `level` bigint(20) DEFAULT NULL,
  `points` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `TAK_multiedicion_quiz`.`level` (`id`, `level`, `points`) VALUES
(1, 1, 1000);

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`level_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `name` text,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `TAK_multiedicion_quiz`.`level_translation` (`id`, `name`, `lang`) VALUES
(1, 'Nivel 1', 'es');

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`multimedia` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `type` varchar(50) DEFAULT NULL,
  `rank` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`multimedia_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `url` text,
  `name` text,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`piece` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rank` bigint(20) DEFAULT NULL,
  `color` varchar(10) DEFAULT NULL,
  `num_categories` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `TAK_multiedicion_quiz`.`piece` (`id`, `rank`, `color`, `num_categories`) VALUES
(1, 1, '#489f34', 3),
(2, 2, '#f1c825', 3),
(3, 3, '#d97311', 3),
(4, 4, '#bc518b', 3),
(5, 5, '#0b9eca', 3),
(6, 6, '#474984', 3);

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`piece_level` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `piece_id` bigint(20) DEFAULT NULL,
  `level_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `piece_id_idx` (`piece_id`),
  KEY `level_id_idx` (`level_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

INSERT INTO `TAK_multiedicion_quiz`.`piece_level` (`id`, `piece_id`, `level_id`) VALUES
(2, 1, 1),
(3, 2, 1),
(4, 3, 1),
(5, 4, 1),
(6, 5, 1),
(7, 6, 1);

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`piece_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `TAK_multiedicion_quiz`.`piece_translation` (`id`, `name`, `description`, `lang`) VALUES
(1, 'Verde', '', 'es'),
(2, 'Amarillo', '', 'es'),
(3, 'Naranja', '', 'es'),
(4, 'Rosa', '', 'es'),
(5, 'Celeste', '', 'es'),
(6, 'Azul', '', 'es');

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`player` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `zone_id` bigint(20) DEFAULT NULL,
  `event_id` bigint(20) DEFAULT NULL,
  `administrator_id` bigint(20) DEFAULT NULL,
  `custom1_id` bigint(20) DEFAULT NULL,
  `custom2_id` bigint(20) DEFAULT NULL,
  `custom3_id` bigint(20) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `surname` varchar(255) DEFAULT NULL,
  `remote_id` varchar(50) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `is_sign_up` tinyint(1) DEFAULT '0',
  `first_login` datetime DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `has_completed` tinyint(1) DEFAULT '0',
  `date_has_completed` datetime DEFAULT NULL,
  `culture` varchar(5) DEFAULT 'es',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `remote_id` (`remote_id`),
  UNIQUE KEY `email` (`email`),
  KEY `zone_id_idx` (`zone_id`),
  KEY `event_id_idx` (`event_id`),
  KEY `administrator_id_idx` (`administrator_id`),
  KEY `custom1_id_idx` (`custom1_id`),
  KEY `custom2_id_idx` (`custom2_id`),
  KEY `custom3_id_idx` (`custom3_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=60 ;

INSERT INTO `TAK_multiedicion_quiz`.`player` (`id`, `zone_id`, `event_id`, `administrator_id`, `custom1_id`, `custom2_id`, `custom3_id`, `name`, `surname`, `remote_id`, `email`, `is_active`, `is_sign_up`, `first_login`, `last_login`, `has_completed`, `date_has_completed`, `culture`, `created_at`, `updated_at`) VALUES
(58, NULL, NULL, NULL, NULL, NULL, NULL, 'Admin', 'TAK', 'admin@tak.es', 'admin@tak.es', 1, 0, '2018-12-20 12:29:21', '2018-12-20 12:29:21', 0, NULL, 'es', '2018-12-20 12:29:21', '2018-12-20 12:29:21'),
(59, NULL, NULL, NULL, NULL, NULL, NULL, 'Iñigo', 'Yagüe', 'iyague@tak.es', 'iyague@tak.es', 1, 0, '2018-12-27 11:13:08', '2018-12-27 11:13:08', 0, NULL, 'es', '2018-12-27 11:13:08', '2018-12-27 11:13:08');

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`profile` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rank` bigint(20) DEFAULT NULL,
  `activated` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

INSERT INTO `TAK_multiedicion_quiz`.`profile` (`id`, `rank`, `activated`) VALUES
(1, 1, 1);

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`profile_category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `profile_id` bigint(20) DEFAULT NULL,
  `category_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `profile_id_idx` (`profile_id`),
  KEY `category_id_idx` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`profile_piece` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `profile_id` bigint(20) DEFAULT NULL,
  `piece_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `profile_id_idx` (`profile_id`),
  KEY `piece_id_idx` (`piece_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=93 ;

INSERT INTO `TAK_multiedicion_quiz`.`profile_piece` (`id`, `profile_id`, `piece_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4),
(5, 1, 5),
(6, 1, 6);

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`profile_player` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `profile_id` bigint(20) DEFAULT NULL,
  `player_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `profile_id_idx` (`profile_id`),
  KEY `player_id_idx` (`player_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=66 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`profile_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `TAK_multiedicion_quiz`.`profile_translation` (`id`, `name`, `description`, `lang`) VALUES
(1, 'General', NULL, 'es');

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`question` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `piece_id` bigint(20) DEFAULT NULL,
  `type` varchar(20) DEFAULT NULL,
  `category_id` bigint(20) DEFAULT NULL,
  `multimedia_id` bigint(20) DEFAULT NULL,
  `exercise_type` varchar(20) DEFAULT NULL,
  `feedback_ok` bigint(20) DEFAULT NULL,
  `feedback_error` bigint(20) DEFAULT NULL,
  `feedback_time` bigint(20) DEFAULT NULL,
  `rank` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `piece_id_idx` (`piece_id`),
  KEY `category_id_idx` (`category_id`),
  KEY `multimedia_id_idx` (`multimedia_id`),
  KEY `feedback_time_idx` (`feedback_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`question_multimedia` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `question_id` bigint(20) DEFAULT NULL,
  `multimedia_id` bigint(20) DEFAULT NULL,
  `required_first_time` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `question_id_idx` (`question_id`),
  KEY `multimedia_id_idx` (`multimedia_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`question_option` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `question_id` bigint(20) DEFAULT NULL,
  `is_correct` tinyint(1) DEFAULT '0',
  `rank` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `question_id_idx` (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`question_option_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `content` text,
  `feedback` text,
  `option_data` text,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`question_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `name` varchar(150) DEFAULT NULL,
  `question` text,
  `question_data` text,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`user_session` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `ip` varchar(50) DEFAULT NULL,
  `user_agent` text,
  `accept_language` varchar(255) NOT NULL,
  `date_logout` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`variables` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `slug` varchar(255) DEFAULT NULL,
  `value` text,
  `description` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;

INSERT INTO `TAK_multiedicion_quiz`.`variables` (`id`, `slug`, `value`, `description`) VALUES
(1, 'NEXT_LEVEL_WITH', 'POINTS', 'Determina si el nivel se calcula sobre los puntos o sobre packs respondidos.\nOpciones:\n-PACKS\n-POINTS'),
(2, 'NORMAL_ANSWER_LIMIT', '30', 'Tiempo estandar para responder las preguntas'),
(3, 'SUM_FIRST_PACKS_ON_LEVEL', '3', 'Número de packs que hay que responder correctamente por cada piece en el nivel inicial.'),
(4, 'SUM_PACKS_ON_LEVEL', '3', 'Número de packs que hay que responder correctamente por cada piece en el nivel actual.'),
(5, 'SUM_NEW_PACKS_ON_LEVEL', '3', 'Número de packs que hay que responder correctamente por cada nuevo piece en el nivel actual.'),
(6, 'BONUS_FOR_LEVEL', NULL, 'Puntuación que se multiplicará por el nivel al que se sube.\nSi el valor es nulo, no habrá bonus por nivel.'),
(7, 'BONUS_TEXT_PATTERN', '%XX%', 'Patrón que se va a usar en el feedback del bonus. La cual se reemplazará con la puntuación que se configure.'),
(8, 'CHANGE_PASS_URL', '', 'URL que se adjuntará en el email de cambio de contraseña. En cada servidor deberá ser actualizada.'),
(9, 'EMAIL_CHANGE_PASS_SUBJECT', 'cambio de contraseña satisfactoria', 'Asunto para el email que se envía para la confirmación del cambio de contraseña'),
(10, 'EMAIL_CHANGE_PASS_BODY', 'Su contraseña se ha cambiado satisfactoriamente\n<br>\nAtentamente,\n<br>\n<br>', 'Mensaje para el email que se envía para la confirmación del cambio de contraseña'),
(11, 'EMAIL_CHANGE_PASS_REQUEST_SUBJECT', 'cambio de contraseña', 'Asunto para el email que se envía cuando se hace la petición del cambio de contraseña'),
(12, 'EMAIL_CHANGE_PASS_REQUEST_BODY', '<<username>>, para poder proceder al cambio de tu contraseña deberás entrar al link inferior. Este link será válido durante 15 minutos.<br><br><div style="padding: 10px;"><a href="<<url>>"  style="text-align:center; font-size:16px; padding: 10px; color:white; background: -webkit-radial-gradient(left top , circle cover , rgb(78, 86, 94) 24% , rgb(82, 90, 99) 100%); font-weight:bold; text-decoration:none;">Cambiar contraseña</a><br></div>\n<br>\nEn caso de que el enlace no funcione, copia y pega este enlace en tu navegador: <a href="<<url>>"><<url>></a>\n<br>\nAtentamente,\n<br>\n<br>', 'Mensaje para el email que se envía cuando se hace la petición del cambio de contraseña'),
(13, 'EMAIL_SIGN_UP_SUBJECT', '<<username>> registro correcto', 'Asunto para el email que se envía al registrarse'),
(14, 'EMAIL_SIGN_UP_BODY', '<<username>>, te has dado de alta.\n<br>\nMuchas gracias por confiar en nosotros.\n<br>\nAtentamente,\n<br>', 'Mensaje para el email que se envía al registrarse'),
(15, 'FEEDBACK_COMPLETED_BETTER', '¡Enhorabuena, has mejorado tu mejor puntuación!', 'Feedback cuando terminas la partida con mejor puntuación que cualquier partida anterior.'),
(16, 'FEEDBACK_COMPLETED_WORSE', '¡Enhorabuena, has finalizado la partida pero no has conseguido mejorar tu puntuación! Vuelve a intentarlo', 'Feedback cuando terminas la partida con peor puntuación que ninguna partida anterior.'),
(17, 'DEFAULT_EMAIL_FROM', 'noreply@tak.es', 'Correo predeterminado que se mostrará como emisor en los emails'),
(18, 'PASS_REQUEST_NO_EMAIL', 'No hay un correo electrónico definido', 'Error que se muestra cuando se pide el restablecimiento de una contraseña y no se envía ningún email. (no-email)'),
(19, 'PASS_REQUEST_INVALID_EMAIL', 'El correo electrónico enviado no es válido', 'Error que se muestra cuando se pide el restablecimiento de una contraseña y el email no es válido. (invalid-email)'),
(20, 'PASS_REQUEST_NO_USER', 'El correo electrónico enviado no corresponde con ningún usuario', 'Error que se muestra cuando se pide el restablecimiento de una contraseña y el email no pertenece a ningún usuario. (no-user)'),
(21, 'PASS_REQUEST_NO_EMAIL_SENT', 'Ha habido un error con el envío del correo electrónico. Si el error persiste, contacte con el administrador.', 'Error que se muestra cuando se pide el restablecimiento de una contraseña y el email de restablecimiento no se envía por alguna razón interna. (no-email-sent)'),
(22, 'PASS_REQUEST_OK_EMAIL_SENT', 'En breve recibirá un correo electrónico para restablecer su contraseña', 'Mensaje que se muestra cuando se pide el restablecimiento de una contraseña y todo ha ido correctamente. (ok-email-sent)'),
(23, 'OK_PERCENTAGE', '60', 'Porcentaje de acierto que se debe cumplir para que salga el feedback ok'),
(24, 'MULTI_PARTIDA', 'true', 'Variable que decide si se puede jugar más de una partida o sólo una'),
(25, 'MAX_PARTIDAS', '3', 'Variable que decide el número máximo de partidas que puede jugar un usuario.');

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`welcome` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `multimedia_id` bigint(20) DEFAULT NULL,
  `text` text DEFAULT NULL,
  `rank` bigint(20) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `multimedia_id_idx` (`multimedia_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`zone` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `country` varchar(10) DEFAULT NULL,
  `iso_code` varchar(10) DEFAULT NULL,
  `remote_id` varchar(20) DEFAULT NULL,
  `is_special` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=44 ;

INSERT INTO `TAK_multiedicion_quiz`.`zone` (`id`, `country`, `iso_code`, `remote_id`, `is_special`) VALUES
(1, 'es', 'es-an', NULL, 0),
(2, 'es', 'es-ar', NULL, 0),
(3, 'es', 'es-as', NULL, 0),
(4, 'es', 'es-cn', NULL, 0),
(5, 'es', 'es-cb', NULL, 0),
(6, 'es', 'es-cm', NULL, 0),
(7, 'es', 'es-cl', NULL, 0),
(8, 'es', 'es-ct', NULL, 0),
(9, 'es', 'es-ex', NULL, 0),
(10, 'es', 'es-ga', NULL, 0),
(11, 'es', 'es-ib', NULL, 0),
(12, 'es', 'es-ri', NULL, 0),
(13, 'es', 'es-md', NULL, 0),
(14, 'es', 'es-mc', NULL, 0),
(15, 'es', 'es-nc', NULL, 0),
(16, 'es', 'es-pv', NULL, 0),
(17, 'es', 'es-vc', NULL, 0),
(18, 'es', 'es-ce', NULL, 1),
(19, 'es', 'es-ml', NULL, 1),
(20, 'fr', 'fr', NULL, 0),
(21, 'de', 'de', NULL, 0),
(22, 'gb', 'gb', NULL, 0),
(23, 'it', 'it', NULL, 0),
(24, 'pt', 'pt', NULL, 0),
(25, 'es', 'es', NULL, 0),
(26, 'es', 'es', NULL, 0),
(27, NULL, NULL, NULL, 0),
(28, NULL, NULL, NULL, 0),
(29, NULL, NULL, NULL, 0),
(30, NULL, NULL, NULL, 0),
(31, NULL, NULL, NULL, 0),
(32, NULL, NULL, NULL, 0),
(33, NULL, NULL, NULL, 0),
(34, NULL, NULL, NULL, 0),
(35, NULL, NULL, NULL, 0),
(36, NULL, NULL, NULL, 0),
(37, NULL, NULL, NULL, 0),
(38, NULL, NULL, NULL, 0),
(39, NULL, NULL, NULL, 0),
(40, NULL, NULL, NULL, 0),
(41, NULL, NULL, NULL, 0),
(42, NULL, NULL, NULL, 0),
(43, NULL, NULL, NULL, 0);

CREATE TABLE IF NOT EXISTS `TAK_multiedicion_quiz`.`zone_translation` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `name` varchar(100) DEFAULT NULL,
  `lang` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `TAK_multiedicion_quiz`.`zone_translation` (`id`, `name`, `lang`) VALUES
(1, 'Andalucía', 'en'),
(1, 'Andalucía', 'es'),
(2, 'Aragón', 'en'),
(2, 'Aragón', 'es'),
(3, 'Asturias', 'en'),
(3, 'Asturias', 'es'),
(4, 'Canarias', 'en'),
(4, 'Canarias', 'es'),
(5, 'Cantabria', 'en'),
(5, 'Cantabria', 'es'),
(6, 'Castilla-La mancha', 'en'),
(6, 'Castilla-La mancha', 'es'),
(7, 'Castilla y León', 'en'),
(7, 'Castilla y León', 'es'),
(8, 'Catalonia', 'en'),
(8, 'Cataluña', 'es'),
(9, 'Extremadura', 'en'),
(9, 'Extremadura', 'es'),
(10, 'Galicia', 'en'),
(10, 'Galicia', 'es'),
(11, 'Baleares', 'en'),
(11, 'Islas Baleares', 'es'),
(12, 'La Rioja', 'en'),
(12, 'La Rioja', 'es'),
(13, 'Madrid', 'en'),
(13, 'Madrid', 'es'),
(14, 'Murcia', 'en'),
(14, 'Murcia', 'es'),
(15, 'Navarra', 'en'),
(15, 'Navarra', 'es'),
(16, 'País Vasco', 'en'),
(16, 'País Vasco', 'es'),
(17, 'Valencia', 'en'),
(17, 'Valencia', 'es'),
(18, 'Ceuta', 'en'),
(18, 'Ceuta', 'es'),
(19, 'Melilla', 'en'),
(19, 'Melilla', 'es'),
(20, 'France', 'en'),
(20, 'Francia', 'es'),
(21, 'Germany', 'en'),
(21, 'Alemania', 'es'),
(22, 'United Kingdom', 'en'),
(22, 'United Kingdom', 'es'),
(23, 'Italia', 'en'),
(23, 'Italia', 'es'),
(24, 'Portugal', 'en'),
(24, 'Portugal', 'es'),
(25, 'Antiguo2', 'es'),
(26, 'Amara nuevo', 'es');


ALTER TABLE `TAK_multiedicion_quiz`.`about`
  ADD CONSTRAINT `about_multimedia_id_multimedia_id` FOREIGN KEY (`multimedia_id`) REFERENCES `TAK_multiedicion_quiz`.`multimedia` (`id`) ON DELETE CASCADE;
ALTER TABLE `TAK_multiedicion_quiz`.`instructions`
  ADD CONSTRAINT `instructions_multimedia_id_multimedia_id` FOREIGN KEY (`multimedia_id`) REFERENCES `TAK_multiedicion_quiz`.`multimedia` (`id`) ON DELETE CASCADE;



COMMIT;