


CREATE TABLE IF NOT EXISTS `competitor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `competitor_type_id` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `first_name` varchar(128) DEFAULT NULL,
  `status` varchar(16) DEFAULT NULL,
  `prive` tinyint(1) DEFAULT NULL,
  `country` varchar(3) DEFAULT NULL,
  `last_name` varchar(128) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `slug` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_E0D53BAA989D9B62` (`slug`),
  KEY `IDX_E0D53BAA4ACA2D41` (`competitor_type_id`),
  KEY `IDX_E0D53BAADE12AB56` (`created_by`),
  KEY `IDX_E0D53BAA16FE72E1` (`updated_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

INSERT INTO `competitor` (`id`, `competitor_type_id`, `created_by`, `first_name`, `status`, `prive`, `country`, `last_name`, `updated_by`, `created_at`, `updated_at`, `slug`) VALUES
(5, 1, 4, 'Roger', 'ready', 0, 'CH', 'Federer', 4, '2011-12-29 20:26:18', '2011-12-29 23:00:14', '5-federer-roger'),
(9, 1, 4, 'Jo-Wilfried', 'ready', 0, 'FR', 'Tsonga', NULL, '2011-12-29 22:59:55', '2011-12-29 22:59:55', 'tsonga-jo-wilfried'),
(10, 1, 4, 'Rafael', 'ready', 0, 'ES', 'Nadal', NULL, '2011-12-29 23:25:55', '2011-12-29 23:25:55', 'nadal-rafael'),
(11, 1, 4, 'Novak', 'ready', 0, 'RS', 'Djokovic', NULL, '2012-01-05 13:25:45', '2012-01-05 13:25:45', 'djokovic-novak'),
(12, 2, 4, NULL, 'ready', 0, 'FR', 'Nancy', 4, '2012-01-05 22:30:58', '2012-01-06 10:23:22', 'nancy'),
(14, 2, 4, NULL, 'ready', 0, 'FR', 'Rennes', NULL, '2012-01-06 11:13:12', '2012-01-06 11:13:12', 'rennes');

CREATE TABLE IF NOT EXISTS `competitors_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `status` varchar(16) DEFAULT NULL,
  `prive` tinyint(1) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `slug` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_CDAD66D989D9B62` (`slug`),
  KEY `IDX_CDAD66DDE12AB56` (`created_by`),
  KEY `IDX_CDAD66D16FE72E1` (`updated_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `competitors_group` (`id`, `created_by`, `name`, `status`, `prive`, `updated_by`, `created_at`, `updated_at`, `slug`) VALUES
(1, 4, 'L1 2011-2012', 'open', 0, 4, '2012-01-03 22:33:16', '2012-01-03 22:33:16', '1-L1-2011-2012'),
(2, 4, 'L2 2011-2012', 'open', 0, 4, '2012-01-05 12:14:35', '2012-01-05 12:14:50', '2-L2-2011-2012');

CREATE TABLE IF NOT EXISTS `competitors_group_themas` (
  `competitor_group_id` int(11) NOT NULL,
  `thema_id` int(11) NOT NULL,
  PRIMARY KEY (`competitor_group_id`,`thema_id`),
  KEY `IDX_2F881A0ED660E3D0` (`thema_id`),
  KEY `IDX_2F881A0E9606EC1E` (`competitor_group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `competitors_in_groups` (
  `competitorsgroup_id` int(11) NOT NULL,
  `competitor_id` int(11) NOT NULL,
  PRIMARY KEY (`competitor_id`,`competitorsgroup_id`),
  KEY `IDX_BB4A663478A5D405` (`competitor_id`),
  KEY `IDX_BB4A6634F2C39DA9` (`competitorsgroup_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `competitors_in_groups` (`competitorsgroup_id`, `competitor_id`) VALUES
(1, 12);

CREATE TABLE IF NOT EXISTS `competitors_themas` (
  `competitor_id` int(11) NOT NULL,
  `thema_id` int(11) NOT NULL,
  PRIMARY KEY (`competitor_id`,`thema_id`),
  KEY `IDX_A942C69278A5D405` (`competitor_id`),
  KEY `IDX_A942C692D660E3D0` (`thema_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `competitors_themas` (`competitor_id`, `thema_id`) VALUES
(5, 1),
(9, 1),
(10, 1),
(11, 1),
(12, 3),
(14, 3);

CREATE TABLE IF NOT EXISTS `competitor_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) DEFAULT NULL,
  `type` varchar(128) NOT NULL,
  `status` varchar(16) DEFAULT NULL,
  `prive` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_197ADB92DE12AB56` (`created_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `competitor_type` (`id`, `created_by`, `type`, `status`, `prive`) VALUES
(1, NULL, 'Individual', 'ready', 0),
(2, NULL, 'Team', 'ready', 0);

CREATE TABLE IF NOT EXISTS `event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `slug` varchar(128) NOT NULL,
  `location` varchar(64) DEFAULT NULL,
  `status` varchar(16) DEFAULT NULL,
  `prive` tinyint(1) DEFAULT NULL,
  `name_or_pseudo` tinyint(1) DEFAULT NULL,
  `max_players` smallint(6) DEFAULT NULL,
  `game_player` tinyint(1) DEFAULT NULL,
  `game_type` tinyint(1) DEFAULT NULL,
  `min_team` smallint(6) DEFAULT NULL,
  `max_team` smallint(6) DEFAULT NULL,
  `points_team_from` smallint(6) DEFAULT NULL,
  `register_at` datetime DEFAULT NULL,
  `begin_at` datetime DEFAULT NULL,
  `finish_at` datetime DEFAULT NULL,
  `register_code` varchar(64) DEFAULT NULL,
  `structure_id` int(11) DEFAULT NULL,
  `description` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_3BAE0AA7989D9B62` (`slug`),
  UNIQUE KEY `UNIQ_3BAE0AA7A3722038` (`register_code`),
  KEY `IDX_3BAE0AA7727ACA70` (`parent_id`),
  KEY `IDX_3BAE0AA7DE12AB56` (`created_by`),
  KEY `IDX_3BAE0AA72534008B` (`structure_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `event` (`id`, `parent_id`, `created_by`, `name`, `slug`, `location`, `status`, `prive`, `name_or_pseudo`, `max_players`, `game_player`, `game_type`, `min_team`, `max_team`, `points_team_from`, `register_at`, `begin_at`, `finish_at`, `register_code`, `structure_id`, `description`) VALUES
(1, NULL, 4, 'Coupe du monde de rugby 2012', 'coupe-du-monde-de-rugby-2012', NULL, 'creation', 1, 0, 500, 1, 1, NULL, 0, 3, '2011-12-15 00:00:00', '2012-01-11 18:00:00', '2012-01-24 10:43:00', 'test', 5, NULL),
(2, NULL, 4, 'Coupe du monde de rugby 2019', 'coupe-du-monde-de-rugby-2019', NULL, 'creation', 1, 0, NULL, 1, 0, NULL, NULL, NULL, NULL, NULL, NULL, 'toto', NULL, NULL);

CREATE TABLE IF NOT EXISTS `events_themas` (
  `event_id` int(11) NOT NULL,
  `thema_id` int(11) NOT NULL,
  PRIMARY KEY (`event_id`,`thema_id`),
  KEY `IDX_A393CEEF71F7E88B` (`event_id`),
  KEY `IDX_A393CEEFD660E3D0` (`thema_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `event_node` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) DEFAULT NULL,
  `event_id` int(11) DEFAULT NULL,
  `test_type_id` int(11) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `nb_jokers` smallint(6) DEFAULT NULL,
  `test_match` int(11) DEFAULT NULL,
  `node_id` int(11) DEFAULT NULL,
  `ranking` tinyint(1) DEFAULT NULL,
  `ranking_rules` int(11) DEFAULT NULL,
  `points_rules` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_9AB4A4EEDE12AB56` (`created_by`),
  KEY `IDX_9AB4A4EE71F7E88B` (`event_id`),
  KEY `IDX_9AB4A4EE460D9FD7` (`node_id`),
  KEY `IDX_9AB4A4EE845ECE8D` (`ranking_rules`),
  KEY `IDX_9AB4A4EE621AD136` (`points_rules`),
  KEY `IDX_9AB4A4EE7185630E` (`test_match`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=52 ;

INSERT INTO `event_node` (`id`, `created_by`, `event_id`, `test_type_id`, `name`, `nb_jokers`, `test_match`, `node_id`, `ranking`, `ranking_rules`, `points_rules`) VALUES
(1, 4, 1, NULL, NULL, 0, NULL, 87, 0, NULL, NULL),
(38, 4, 1, NULL, NULL, NULL, NULL, 124, 1, NULL, NULL),
(39, 4, 1, NULL, NULL, 2, NULL, 125, 1, NULL, NULL),
(40, 4, 1, NULL, NULL, 2, NULL, 126, 1, NULL, NULL),
(41, 4, 1, 1, NULL, NULL, 4, 127, 0, NULL, 1),
(42, 4, 1, 1, NULL, NULL, 2, 128, NULL, NULL, NULL),
(43, 4, 1, 1, NULL, NULL, 1, 129, 0, NULL, NULL),
(44, 4, 1, 0, NULL, NULL, NULL, 130, 0, NULL, NULL),
(45, 4, 1, NULL, NULL, NULL, NULL, 131, NULL, NULL, NULL),
(46, 4, 1, NULL, NULL, NULL, NULL, 132, NULL, NULL, NULL),
(47, 4, 1, NULL, NULL, NULL, NULL, 133, NULL, NULL, NULL),
(48, 4, 1, NULL, NULL, NULL, NULL, 134, NULL, NULL, NULL),
(49, 4, 1, 1, NULL, NULL, 9, 135, NULL, NULL, 1),
(51, 4, 1, NULL, NULL, NULL, NULL, 137, NULL, NULL, NULL);

CREATE TABLE IF NOT EXISTS `ext_log_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `action` varchar(8) NOT NULL,
  `logged_at` datetime NOT NULL,
  `object_id` varchar(32) DEFAULT NULL,
  `object_class` varchar(255) NOT NULL,
  `version` int(11) NOT NULL,
  `data` longtext COMMENT '(DC2Type:array)',
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `log_class_lookup_idx` (`object_class`),
  KEY `log_date_lookup_idx` (`logged_at`),
  KEY `log_user_lookup_idx` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `ext_translations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `locale` varchar(8) NOT NULL,
  `object_class` varchar(255) NOT NULL,
  `field` varchar(32) NOT NULL,
  `foreign_key` varchar(64) NOT NULL,
  `content` longtext,
  PRIMARY KEY (`id`),
  UNIQUE KEY `lookup_unique_idx` (`locale`,`object_class`,`foreign_key`,`field`),
  KEY `translations_lookup_idx` (`locale`,`object_class`,`foreign_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `migration_versions` (
  `version` varchar(14) NOT NULL,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `migration_versions` (`version`) VALUES
('20111110163600'),
('20111112142612'),
('20111112185425'),
('20111112185657'),
('20111114093335'),
('20111114110658'),
('20111114184314'),
('20111114190029'),
('20111121145408'),
('20111124122934'),
('20111124134924'),
('20111124135121'),
('20111124135212'),
('20111129125719'),
('20111206135724'),
('20111206145851'),
('20111206145947'),
('20111210172024'),
('20111210181352'),
('20111210185839'),
('20111211142956'),
('20111211143215'),
('20111211144159'),
('20111211144551'),
('20111219094718'),
('20111219095029'),
('20111222150847'),
('20111229113253'),
('20111229122834'),
('20111229191725'),
('20111229202223'),
('20120103141747'),
('20120103142042'),
('20120105211530'),
('20120106101032'),
('20120109200106'),
('20120109201127'),
('20120109201725'),
('20120112220509'),
('20120112221152'),
('20120112230655'),
('20120112233445'),
('20120119192443'),
('20120120091602'),
('20120120091941'),
('20120124225850'),
('20120125143747'),
('20120126101004'),
('20120127122146'),
('20120127204506'),
('20120127210000'),
('20120128223642'),
('20120130111452'),
('20120201180554'),
('20120201222557'),
('20120201233940'),
('20120202101221'),
('20120202110236');

CREATE TABLE IF NOT EXISTS `node` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `node_name` varchar(128) DEFAULT NULL,
  `lft` int(11) DEFAULT NULL,
  `lvl` int(11) DEFAULT NULL,
  `rgt` int(11) DEFAULT NULL,
  `root` int(11) DEFAULT NULL,
  `node_type` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_857FE845DE12AB56` (`created_by`),
  KEY `IDX_857FE845727ACA70` (`parent_id`),
  KEY `IDX_857FE8454B7ADABF` (`node_type`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=138 ;

INSERT INTO `node` (`id`, `created_by`, `parent_id`, `node_name`, `lft`, `lvl`, `rgt`, `root`, `node_type`) VALUES
(87, 4, NULL, 'Coupe du monde de rugby 2012', -2, 0, 30, 87, 1),
(124, 4, 87, 'Phase de poules', 4, 1, 29, 87, 2),
(125, 4, 124, 'Poule A', 5, 2, 18, 87, 2),
(126, 4, 124, 'Poule B', 19, 2, 28, 87, 2),
(127, 4, 125, 'game 1', 6, 3, 7, 87, 3),
(128, 4, 125, 'game 2', 8, 3, 9, 87, 3),
(129, 4, 126, 'game 3', 20, 3, 21, 87, 3),
(130, 4, 126, 'game 4', 22, 3, 23, 87, 3),
(131, 4, 126, 'game 5', 24, 3, 25, 87, 3),
(132, 4, 126, 'game 6', 26, 3, 27, 87, 3),
(133, 4, 125, 'New node', 10, 3, 11, 87, 2),
(134, 4, 125, 'bob', 12, 3, 13, 87, 2),
(135, 4, 125, 'game 7', 14, 3, 15, 87, 3),
(137, 4, 125, 'New node', 16, 3, 17, 87, 3);

CREATE TABLE IF NOT EXISTS `node_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `status` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_4B7ADABFDE12AB56` (`created_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `node_type` (`id`, `created_by`, `name`, `status`) VALUES
(1, NULL, 'structure', 'ready'),
(2, NULL, 'set', 'ready'),
(3, NULL, 'test', 'ready');

CREATE TABLE IF NOT EXISTS `ranking_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_25E3D8A3DE12AB56` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `description` varchar(256) DEFAULT NULL,
  `definition` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_899A993CDE12AB56` (`created_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `rules` (`id`, `created_by`, `name`, `description`, `definition`) VALUES
(1, 4, 'Colmarienne', NULL, NULL);

CREATE TABLE IF NOT EXISTS `rules_testtype` (
  `rules_id` int(11) NOT NULL,
  `testtype_id` int(11) NOT NULL,
  PRIMARY KEY (`rules_id`,`testtype_id`),
  KEY `IDX_1CC5270AFB699244` (`rules_id`),
  KEY `IDX_1CC5270AABA66C3E` (`testtype_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `singles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thema` int(11) DEFAULT NULL,
  `title` varchar(128) NOT NULL,
  `slug` varchar(128) NOT NULL,
  `question` longtext NOT NULL,
  `choices` longtext COMMENT '(DC2Type:array)',
  `result` longtext COMMENT '(DC2Type:array)',
  `status` varchar(16) DEFAULT NULL,
  `prive` tinyint(1) DEFAULT NULL,
  `test_type` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `bet_before` datetime NOT NULL,
  `show_bets` tinyint(1) DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `play_code` varchar(64) NOT NULL,
  `admin_code` varchar(64) NOT NULL,
  `mail_created_by` varchar(128) DEFAULT NULL,
  `max_choices` smallint(6) DEFAULT NULL,
  `creator_name` varchar(64) NOT NULL,
  `rules` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_7FAFC0EC989D9B62` (`slug`),
  UNIQUE KEY `UNIQ_7FAFC0EC7C10CE49` (`play_code`),
  UNIQUE KEY `UNIQ_7FAFC0EC570CB278` (`admin_code`),
  UNIQUE KEY `use_idx` (`play_code`,`admin_code`),
  KEY `IDX_7FAFC0EC90182311` (`thema`),
  KEY `IDX_7FAFC0EC89A77DE6` (`test_type`),
  KEY `IDX_7FAFC0EC899A993C` (`rules`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=63 ;

INSERT INTO `singles` (`id`, `thema`, `title`, `slug`, `question`, `choices`, `result`, `status`, `prive`, `test_type`, `created_at`, `bet_before`, `show_bets`, `closed_at`, `play_code`, `admin_code`, `mail_created_by`, `max_choices`, `creator_name`, `rules`) VALUES
(47, NULL, 'time', 'time', 'time ?', 'a:0:{}', 'N;', 'ready', 1, 7, '2012-01-31 09:40:32', '2012-01-31 00:00:00', 1, '2012-02-02 00:00:00', '59cbwbcci3rgqmmn', '59cbwbcci3rgqmmn60wz6yz3', 'troadec.vincent@wanadoo.fr', 1, 'Leto', NULL),
(48, NULL, 'date', 'date', 'date ?', 'a:0:{}', 'a:1:{i:0;d:1329346800;}', 'ready', 1, 6, '2012-01-31 09:48:13', '2012-01-31 00:00:00', 1, '2012-02-02 00:00:00', 'eth8v1cfshakl867', 'eth8v1cfshakl867rxctr2i3', 'troadec.vincent@wanadoo.fr', 1, 'Leto', NULL),
(50, NULL, 'oui non', 'oui-non', '???', 'a:0:{}', 'N;', 'ready', 1, 11, '2012-01-31 12:19:13', '2012-01-31 00:00:00', 1, '2012-02-02 00:00:00', 'ia5tq25e4itlh8ux', 'ia5tq25e4itlh8uxyzhe5x0q', 'troadec.vincent@wanadoo.fr', 1, 'Leto', NULL),
(51, NULL, 'Choice', 'choice', 'Choice ?', 'a:5:{i:0;s:5:"Sarko";i:1;s:8:"Hollande";i:2;s:6:"Le Pen";i:3;s:6:"Bayrou";i:4;s:4:"Joly";}', 'a:2:{i:0;s:5:"Sarko";i:1;s:8:"Hollande";}', 'ready', 1, 4, '2012-01-31 12:28:10', '2012-02-01 23:00:00', 1, '2012-02-02 00:00:00', 'dza3s6xucxe13807', 'dza3s6xucxe13807yv5wn7bh', 'troadec.vincent@wanadoo.fr', 2, 'Leto', NULL),
(52, NULL, 'Value', 'value', 'Value ?', 'a:0:{}', 'a:1:{i:0;s:1:"5";}', 'closed', 1, 5, '2012-01-31 20:13:21', '2012-02-02 19:03:10', 1, '2012-02-03 00:00:00', 'kap2eyy6ea7b7vhn', 'kap2eyy6ea7b7vhncr1wupql', 'troadec.vincent@wanadoo.fr', 1, 'Leto', 3),
(53, NULL, 'Datetime', 'datetime', 'Datetime ?', 'a:0:{}', 'a:1:{i:0;d:1329174000;}', 'closed', 1, 8, '2012-02-01 18:32:02', '2012-02-03 00:22:02', 1, '2012-02-02 00:00:00', 'eyj59m6ic9dgmhl4', 'eyj59m6ic9dgmhl4yqklm7ct', 'troadec.vincent@wanadoo.fr', 1, 'Leto', 3),
(58, NULL, 'Choice 2', 'choice-2', 'sfghjfgjhjs', 'a:2:{i:0;s:3:"bob";i:1;s:4:"rita";}', 'N;', 'ready', 1, 4, '2012-02-01 20:13:09', '2012-02-01 23:00:00', 1, '2012-02-02 00:00:00', 'hk1mlubtwc92erm7', 'hk1mlubtwc92erm7vtqj0jhl', 'troadec.vincent@wanadoo.fr', NULL, 'Lzto', NULL),
(59, NULL, 'Second tour', 'second-tour', 'Qui sera au second tour ?', 'a:3:{i:0;s:3:"Bob";i:1;s:4:"Toto";i:2;s:5:"Raoul";}', 'N;', 'ready', 1, 4, '2012-02-01 20:26:58', '2012-02-01 23:00:00', 1, '2012-02-02 00:00:00', 'kdehe1nx9np8d3cw', 'kdehe1nx9np8d3cw6l3mtr2q', 'troadec.vincent@wanadoo.fr', NULL, 'Leto', NULL),
(60, NULL, 'Second tour', 'second-tour-1', 'Qui sera au second tour ?', 'a:3:{i:0;s:3:"Bob";i:1;s:4:"Toto";i:2;s:5:"Raoul";}', 'N;', 'ready', 1, 4, '2012-02-01 20:29:05', '2012-02-01 23:00:00', 1, '2012-02-02 00:00:00', 'vltllpmsqte0nr2t', 'vltllpmsqte0nr2teduuz1k2', 'troadec.vincent@wanadoo.fr', NULL, 'Leto', NULL),
(61, NULL, 'Second tour', 'second-tour-2', 'Qui sera au second tour ?', 'a:4:{i:0;s:3:"Bob";i:1;s:4:"Toto";i:2;s:5:"Raoul";i:3;s:6:"Donald";}', 'a:2:{i:0;s:3:"Bob";i:3;s:6:"Donald";}', 'closed', 1, 4, '2012-02-01 20:32:47', '2012-02-02 23:21:18', 1, '2012-02-03 00:00:00', 'yx99pljy14qfmxlc', 'yx99pljy14qfmxlcat42kmyl', 'troadec.vincent@wanadoo.fr', 2, 'Leto', 3),
(62, NULL, 'Combien ?', 'combien', 'Combien ?', 'a:0:{}', 'a:1:{i:0;s:2:"20";}', 'closed', 1, 5, '2012-02-03 00:32:26', '2012-02-03 00:51:00', 1, '2012-02-04 00:00:00', '4tg5jlkze2efkbsd', '4tg5jlkze2efkbsd1ep7sqig', NULL, NULL, 'Leto', 2);

CREATE TABLE IF NOT EXISTS `singles_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `definition` varchar(64) DEFAULT NULL,
  `description` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_C19460BCDE12AB56` (`created_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `singles_rules` (`id`, `created_by`, `name`, `definition`, `description`) VALUES
(1, 1, 'Manuel', 'Manuel', 'Points are given manually by the creator of the event'),
(2, 1, 'Exact result', 'Exact result', 'Only players having the good result have points'),
(3, 1, 'Proportionnal', 'Proportionnal', 'The points are allocated according to the variation to the good result');

CREATE TABLE IF NOT EXISTS `singles_test_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) DEFAULT NULL,
  `definition` varchar(32) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `status` varchar(16) DEFAULT NULL,
  `prive` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_386F67AEDE12AB56` (`created_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

INSERT INTO `singles_test_type` (`id`, `created_by`, `definition`, `name`, `status`, `prive`) VALUES
(1, 1, '1 N 2', '1 N 2', 'closed', 0),
(2, 1, 'Score', 'Score', 'closed', 0),
(3, 1, 'Race', 'Race', 'closed', 0),
(4, 1, 'Choice', 'Choice', 'ready', 0),
(5, 1, 'Ask for a "value"', 'Ask for a "number"', 'ready', 0),
(6, 1, 'Ask for a "date"', 'Ask for a "date"', 'ready', 0),
(7, 1, 'Ask for a "time"', 'Ask for a "time"', 'ready', 0),
(8, 1, 'Ask for a "date/time"', 'Ask for a "date/time"', 'ready', 0),
(11, 1, 'Ask for "Yes/No"', 'Ask for "Yes/No"', 'ready', 0);

CREATE TABLE IF NOT EXISTS `singles_test_type_rules` (
  `singlesrules_id` int(11) NOT NULL,
  `singlestesttype_id` int(11) NOT NULL,
  PRIMARY KEY (`singlesrules_id`,`singlestesttype_id`),
  KEY `IDX_429BFD1E70C3E9C4` (`singlesrules_id`),
  KEY `IDX_429BFD1EB05B9DFE` (`singlestesttype_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `singles_test_type_rules` (`singlesrules_id`, `singlestesttype_id`) VALUES
(2, 4),
(2, 5),
(2, 6),
(2, 7),
(2, 8),
(2, 11),
(3, 4),
(3, 5),
(3, 6),
(3, 7),
(3, 8);

CREATE TABLE IF NOT EXISTS `singles_thema` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) DEFAULT NULL,
  `name` varchar(128) NOT NULL,
  `status` varchar(16) DEFAULT NULL,
  `prive` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_D816DA91DE12AB56` (`created_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `singles_thema` (`id`, `created_by`, `name`, `status`, `prive`) VALUES
(1, 1, 'Sport', 'ready', 0),
(2, 1, 'Politics', 'ready', 0),
(3, 1, 'Business', 'ready', 0),
(4, 1, 'Friends', 'ready', 0),
(5, 1, 'Economy', 'ready', 0);

CREATE TABLE IF NOT EXISTS `structure` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `root_node_id` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `description` varchar(256) DEFAULT NULL,
  `slug` varchar(128) NOT NULL,
  `status` varchar(16) DEFAULT NULL,
  `prive` tinyint(1) DEFAULT NULL,
  `used_once` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_6F0137EA989D9B62` (`slug`),
  UNIQUE KEY `UNIQ_6F0137EAAA4CD71D` (`root_node_id`),
  KEY `IDX_6F0137EADE12AB56` (`created_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `structure` (`id`, `root_node_id`, `created_by`, `name`, `description`, `slug`, `status`, `prive`, `used_once`) VALUES
(5, 87, 4, 'Coupe du monde de rugby 2012', NULL, 'coupe-du-monde-de-rugby-2012', 'creation', 1, NULL);

CREATE TABLE IF NOT EXISTS `structures_themas` (
  `structure_id` int(11) NOT NULL,
  `thema_id` int(11) NOT NULL,
  PRIMARY KEY (`structure_id`,`thema_id`),
  KEY `IDX_FD6617712534008B` (`structure_id`),
  KEY `IDX_FD661771D660E3D0` (`thema_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `tag` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `slug` varchar(50) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_389B7835E237E06` (`name`),
  UNIQUE KEY `UNIQ_389B783989D9B62` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `tagging` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_id` int(11) DEFAULT NULL,
  `resource_type` varchar(50) NOT NULL,
  `resource_id` varchar(50) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tagging_idx` (`tag_id`,`resource_type`,`resource_id`),
  KEY `IDX_A4AED123BAD26311` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `test_match` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `updated_by` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `thema` int(11) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `type_comp1` int(11) DEFAULT NULL,
  `competitor1` int(11) DEFAULT NULL,
  `name_alt1` varchar(128) DEFAULT NULL,
  `type_comp2` int(11) DEFAULT NULL,
  `competitor2` int(11) DEFAULT NULL,
  `name_alt2` varchar(128) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `updated_at` datetime NOT NULL,
  `status` varchar(16) DEFAULT NULL,
  `prive` tinyint(1) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_7185630E16FE72E1` (`updated_by`),
  KEY `IDX_7185630EDE12AB56` (`created_by`),
  KEY `IDX_7185630E90182311` (`thema`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

INSERT INTO `test_match` (`id`, `updated_by`, `created_by`, `thema`, `name`, `type_comp1`, `competitor1`, `name_alt1`, `type_comp2`, `competitor2`, `name_alt2`, `date`, `updated_at`, `status`, `prive`, `created_at`) VALUES
(1, 4, 4, NULL, NULL, NULL, 0, 'Nancy', NULL, NULL, NULL, '2012-01-30 15:00:00', '2012-01-23 22:24:12', NULL, NULL, '2012-01-20 00:08:30'),
(2, 4, 4, NULL, NULL, NULL, NULL, 'Rennes', NULL, NULL, 'PSG', '2012-01-27 07:00:00', '2012-01-23 22:24:12', NULL, NULL, '2012-01-20 00:08:55'),
(4, 4, 4, NULL, NULL, NULL, NULL, 'Rennes', NULL, NULL, 'PSG', '2012-01-26 14:00:00', '2012-01-23 22:24:11', NULL, NULL, '2012-01-20 20:29:13'),
(9, 4, 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2012-01-28 16:00:00', '2012-01-23 22:24:13', NULL, NULL, '2012-01-23 09:37:41');

CREATE TABLE IF NOT EXISTS `test_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `status` varchar(16) DEFAULT NULL,
  `prive` tinyint(1) DEFAULT NULL,
  `definition` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_89A77DE6DE12AB56` (`created_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

INSERT INTO `test_type` (`id`, `created_by`, `name`, `status`, `prive`, `definition`) VALUES
(1, 1, '1 N 2', 'ready', 0, '1 N 2'),
(2, 1, 'Score', 'ready', 0, 'Score'),
(3, 1, 'Race', 'ready', 0, 'Race'),
(4, 1, 'Choice', 'ready', 0, 'Choice'),
(5, 1, 'Ask for a "number"', 'ready', 0, 'Ask for a "value"'),
(6, 1, 'Ask for a "date"', 'ready', 0, 'Ask for a "date"'),
(7, 1, 'Ask for a "time"', 'ready', 0, 'Ask for a "time"'),
(8, 1, 'Ask for a "date/time"', 'ready', 0, 'Ask for a "date/time"'),
(11, 1, 'Ask for "Yes/No"', 'ready', 0, 'Ask for "Yes/No"');

CREATE TABLE IF NOT EXISTS `thema` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) DEFAULT NULL,
  `name` varchar(128) NOT NULL,
  `status` varchar(16) DEFAULT NULL,
  `prive` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_90182311DE12AB56` (`created_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `thema` (`id`, `created_by`, `name`, `status`, `prive`) VALUES
(1, NULL, 'tennis', 'ready', 0),
(2, NULL, 'rugby', 'ready', 0),
(3, 4, 'football', 'ready', 0);


ALTER TABLE `competitor`
  ADD CONSTRAINT `FK_E0D53BAA16FE72E1` FOREIGN KEY (`updated_by`) REFERENCES `userprofile` (`id`),
  ADD CONSTRAINT `FK_E0D53BAA4ACA2D41` FOREIGN KEY (`competitor_type_id`) REFERENCES `competitor_type` (`id`),
  ADD CONSTRAINT `FK_E0D53BAADE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `competitors_group`
  ADD CONSTRAINT `FK_CDAD66D16FE72E1` FOREIGN KEY (`updated_by`) REFERENCES `userprofile` (`id`),
  ADD CONSTRAINT `FK_CDAD66DDE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `competitors_group_themas`
  ADD CONSTRAINT `FK_2F881A0E9606EC1E` FOREIGN KEY (`competitor_group_id`) REFERENCES `competitors_group` (`id`),
  ADD CONSTRAINT `FK_2F881A0ED660E3D0` FOREIGN KEY (`thema_id`) REFERENCES `thema` (`id`);

ALTER TABLE `competitors_in_groups`
  ADD CONSTRAINT `FK_BB4A663478A5D405` FOREIGN KEY (`competitor_id`) REFERENCES `competitor` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `FK_BB4A6634F2C39DA9` FOREIGN KEY (`competitorsgroup_id`) REFERENCES `competitors_group` (`id`) ON DELETE CASCADE;

ALTER TABLE `competitors_themas`
  ADD CONSTRAINT `FK_A942C69278A5D405` FOREIGN KEY (`competitor_id`) REFERENCES `competitor` (`id`),
  ADD CONSTRAINT `FK_A942C692D660E3D0` FOREIGN KEY (`thema_id`) REFERENCES `thema` (`id`);

ALTER TABLE `competitor_type`
  ADD CONSTRAINT `FK_197ADB92DE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `event`
  ADD CONSTRAINT `FK_3BAE0AA72534008B` FOREIGN KEY (`structure_id`) REFERENCES `structure` (`id`),
  ADD CONSTRAINT `FK_3BAE0AA7727ACA70` FOREIGN KEY (`parent_id`) REFERENCES `event` (`id`),
  ADD CONSTRAINT `FK_3BAE0AA7DE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `events_themas`
  ADD CONSTRAINT `FK_A393CEEF71F7E88B` FOREIGN KEY (`event_id`) REFERENCES `event` (`id`),
  ADD CONSTRAINT `FK_A393CEEFD660E3D0` FOREIGN KEY (`thema_id`) REFERENCES `thema` (`id`);

ALTER TABLE `event_node`
  ADD CONSTRAINT `FK_9AB4A4EE460D9FD7` FOREIGN KEY (`node_id`) REFERENCES `node` (`id`),
  ADD CONSTRAINT `FK_9AB4A4EE621AD136` FOREIGN KEY (`points_rules`) REFERENCES `rules` (`id`),
  ADD CONSTRAINT `FK_9AB4A4EE7185630E` FOREIGN KEY (`test_match`) REFERENCES `test_match` (`id`),
  ADD CONSTRAINT `FK_9AB4A4EE71F7E88B` FOREIGN KEY (`event_id`) REFERENCES `event` (`id`),
  ADD CONSTRAINT `FK_9AB4A4EE845ECE8D` FOREIGN KEY (`ranking_rules`) REFERENCES `rules` (`id`),
  ADD CONSTRAINT `FK_9AB4A4EEDE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `node`
  ADD CONSTRAINT `FK_857FE8454B7ADABF` FOREIGN KEY (`node_type`) REFERENCES `node_type` (`id`),
  ADD CONSTRAINT `FK_857FE845727ACA70` FOREIGN KEY (`parent_id`) REFERENCES `node` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `FK_857FE845DE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `node_type`
  ADD CONSTRAINT `FK_4B7ADABFDE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `ranking_type`
  ADD CONSTRAINT `FK_25E3D8A3DE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `rules`
  ADD CONSTRAINT `FK_899A993CDE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `rules_testtype`
  ADD CONSTRAINT `FK_1CC5270AABA66C3E` FOREIGN KEY (`testtype_id`) REFERENCES `test_type` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `FK_1CC5270AFB699244` FOREIGN KEY (`rules_id`) REFERENCES `rules` (`id`) ON DELETE CASCADE;

ALTER TABLE `singles`
  ADD CONSTRAINT `FK_7FAFC0EC899A993C` FOREIGN KEY (`rules`) REFERENCES `singles_rules` (`id`),
  ADD CONSTRAINT `FK_7FAFC0EC89A77DE6` FOREIGN KEY (`test_type`) REFERENCES `test_type` (`id`),
  ADD CONSTRAINT `FK_7FAFC0EC90182311` FOREIGN KEY (`thema`) REFERENCES `thema` (`id`);

ALTER TABLE `singles_rules`
  ADD CONSTRAINT `FK_C19460BCDE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `singles_test_type`
  ADD CONSTRAINT `FK_386F67AEDE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `singles_test_type_rules`
  ADD CONSTRAINT `FK_429BFD1EB05B9DFE` FOREIGN KEY (`singlestesttype_id`) REFERENCES `singles_test_type` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `FK_429BFD1E70C3E9C4` FOREIGN KEY (`singlesrules_id`) REFERENCES `singles_rules` (`id`) ON DELETE CASCADE;

ALTER TABLE `singles_thema`
  ADD CONSTRAINT `FK_D816DA91DE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `structure`
  ADD CONSTRAINT `FK_6F0137EAAA4CD71D` FOREIGN KEY (`root_node_id`) REFERENCES `node` (`id`),
  ADD CONSTRAINT `FK_6F0137EADE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `structures_themas`
  ADD CONSTRAINT `FK_FD6617712534008B` FOREIGN KEY (`structure_id`) REFERENCES `structure` (`id`),
  ADD CONSTRAINT `FK_FD661771D660E3D0` FOREIGN KEY (`thema_id`) REFERENCES `thema` (`id`);

ALTER TABLE `tagging`
  ADD CONSTRAINT `FK_A4AED123BAD26311` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`);

ALTER TABLE `test_match`
  ADD CONSTRAINT `FK_7185630E16FE72E1` FOREIGN KEY (`updated_by`) REFERENCES `userprofile` (`id`),
  ADD CONSTRAINT `FK_7185630E90182311` FOREIGN KEY (`thema`) REFERENCES `thema` (`id`),
  ADD CONSTRAINT `FK_7185630EDE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `test_type`
  ADD CONSTRAINT `FK_89A77DE6DE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);

ALTER TABLE `thema`
  ADD CONSTRAINT `FK_90182311DE12AB56` FOREIGN KEY (`created_by`) REFERENCES `userprofile` (`id`);
