-- http://www.phpmyadmin.net
--
-- Client :  spiceupfrbdior.mysql.db
-- Généré le :  Ven 16 Octobre 2015 à 14:57
-- Version du serveur :  5.5.44-0+deb7u1-log
-- Version de PHP :  5.3.8




--
-- Base de données :  `spiceupfrbdior`
--

-- --------------------------------------------------------

--
-- Structure de la table `languages`
--

CREATE TABLE IF NOT EXISTS `languages` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `short` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Contenu de la table `languages`
--

INSERT INTO `languages` (`id`, `name`, `short`, `created_at`, `updated_at`) VALUES
(1, 'Français', 'fr', '2015-08-07 09:01:03', '2015-08-07 09:01:03'),
(2, 'Anglais', 'en', '2015-08-07 09:01:03', '2015-08-07 09:01:03');

-- --------------------------------------------------------

--
-- Structure de la table `photos`
--

CREATE TABLE IF NOT EXISTS `photos` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` varchar(1000) NOT NULL,
  `position` int(10) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8;

--
-- Contenu de la table `photos`
--

INSERT INTO `photos` (`id`, `name`, `title`, `description`, `position`, `created_at`, `updated_at`) VALUES
(38, '5613e0d7c9568-badii.jpg', 'Baadi Palace', 'The breathtaking Sultan''s home', 10, '2015-10-06 16:55:19', '2015-10-07 09:44:50'),
(39, '5613e0e4a3396-PalmeraieGolfPalace-3.jpg', 'Palmeraie resort', 'Both modern and authentic', 8, '2015-10-06 16:55:32', '2015-10-06 17:08:17'),
(40, '5613e0f2df6a8-jemaa_el_fna.jpg', 'Jemaa el fna', 'Famous square in la Médina, designated as World heritage by UNESCO', 1, '2015-10-06 16:55:46', '2015-10-07 14:36:10'),
(41, '5613e1159a339-bahia.jpg', 'Palais de la bahia', 'The Palace of the Beautiful', 3, '2015-10-06 16:56:21', '2015-10-06 17:05:43'),
(42, '5613e1236e91e-soiree_berbere.jpg', 'Traditional evening in la palmeraie', 'Berber evening in the oasis', 6, '2015-10-06 16:56:35', '2015-10-06 17:07:46'),
(43, '5613e13686858-koutoubia.jpg', 'KOUTOUBIA MOSQUE', 'The great emblem', 2, '2015-10-06 16:56:54', '2015-10-07 14:36:10'),
(44, '5613e13c2ce24-souk.jpg', 'The souks', 'Endless maze of markets and shops', 9, '2015-10-06 16:57:00', '2015-10-07 09:44:50'),
(45, '5613e14191cca-medina.jpg', 'La medina', 'Marrakech heart and soul', 5, '2015-10-06 16:57:05', '2015-10-06 17:07:17'),
(46, '5613e14f4d22b-majorelle.jpg', 'Jardin Majorelle', 'An enchanting garden in the heart of Marrakech', 4, '2015-10-06 16:57:19', '2015-10-06 17:06:17'),
(48, '5613e195dbcb1-PalmeraieGolfPalace.jpg', '', '', 7, '2015-10-06 16:58:29', '2015-10-06 17:00:36');

-- --------------------------------------------------------

--
-- Structure de la table `questions`
--

CREATE TABLE IF NOT EXISTS `questions` (
  `id` int(11) NOT NULL,
  `question_theme_id` int(11) NOT NULL,
  `question` text NOT NULL,
  `answer` text NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

--
-- Contenu de la table `questions`
--

INSERT INTO `questions` (`id`, `question_theme_id`, `question`, `answer`, `created_at`, `updated_at`) VALUES
(1, 1, 'Do I need a visa to go to Marrakech ?', 'If you are a citizen of the European Union, you will only need a valid passport. For other countries, please find hereafter the link to the Moroccan Consulate. It discloses the list of countries exempted of visa entry in Morocco and all information to check out if you need a visa : <a href="http://www.moroccanconsulate.com/visa.cfm" target="_blank">http://www.moroccanconsulate.com/visa.cfm</a>\nIMPORTANT : Passport and visa requirements are liable to change at short notice. You are strongly advised to check your entry requirements with your embassy or the consulate related to your own nationality. ', '2015-10-02 00:00:00', '2015-10-12 17:32:12'),
(2, 1, 'I need special documents to complete my visa request, what shall I do ? ', 'If needed, do not hesitate to ask us for any booking confirmation, invitation letter or else, in order to support the visa request. ', '2015-10-02 00:00:00', '2015-10-12 17:32:48'),
(3, 2, 'Do I need to pay something onsite ? ', 'During your trip, you will only need money for your personal expenses (extras, additional nights in Marrakech…), everything else is taken care of by Dior. ', '2015-10-02 00:00:00', '2015-10-12 17:33:27'),
(4, 3, 'What is the weather in February in Marrakech ? ', 'In February, the average temperature in Marrakech is between 12°C and 20°C during the day. We recommend you to check the weather forecast the day before your departure. ', '2015-10-02 00:00:00', '2015-10-06 16:39:58'),
(5, 2, 'What is the local currency in Marrakech ? ', 'The Moroccan currency is the Dirham (exchange rate in October 5th 2015) : \n1 MAD = 0.092 EUR / 1 EUR = 10.899 MAD\n1 MAD = 0.103 USD / 1 USD = 9.719 MAD\nMajor credit cards and banking cards are accepted.', '2015-10-02 15:07:11', '2015-10-06 16:39:23'),
(11, 4, 'What is the local time ?', 'The time zone is Western European Time in February (GMT 0).', '2015-10-02 15:33:15', '2015-10-06 16:40:33'),
(16, 1, 'Are there any mandatory vaccinations?', 'No vaccination is required to go to Morocco', '2015-10-06 16:38:35', '2015-10-12 17:33:15'),
(17, 3, 'What do I need in my luggage ?', 'February in Marrakech is not so rainy ! But do not forget to bring warm and comfortable clothes and shoes for the outside activities and evenings. We kindly remind you to be careful with luggage weight allowance (20kg) for airlines which are increasingly strict.', '2015-10-06 16:40:09', '2015-10-12 17:33:53'),
(18, 4, 'What is the official language in Morocco ? ', 'The official language is Moroccan Arabic (many people speak French though).', '2015-10-06 16:40:48', '2015-10-06 16:40:48'),
(19, 4, 'What is the electrical appliance ?', 'Electrical current is 220 Volts, Moroccan plug with two circular metal pinsare standard. Do not forget to bring an adapter for your electrical appliance if not European. ', '2015-10-06 16:41:00', '2015-10-06 16:41:00'),
(20, 4, 'How can I make a phone call from/to Morocco ?', 'From abroad to Morocco, dial 00 + 212 + your correspondant number\nFrom Morocco, dial 00 + your country code + your correspondant number.', '2015-10-06 16:41:16', '2015-10-06 16:41:16'),
(22, 9, 'Can I chose my roommate ?', 'Yes, you can chose the person you want to share your room with. Go to the registration form and enter the name and surname of the person in the dedicated fields. ', '2015-10-06 16:44:38', '2015-10-06 16:44:38'),
(23, 9, 'Can I stay in a single room ? ', 'Yes, you can stay in a single room, providing availability. Please ask for it in your registration form (in the comment field), we will let you know if we can approve your request. ', '2015-10-06 16:44:50', '2015-10-12 17:34:50'),
(24, 9, 'Can I extend my stay in Marrakech, before or after the Convention ? ', 'If you want to stay before or after the Convention, please contact us by email : <a href="mailto:diorconvention2016@spiceup.fr">diorconvention2016@spiceup.fr</a>, within 48 hours after your registration. We kindly remind you that you will have to bear any pre or post convention stay.', '2015-10-06 16:45:04', '2015-10-12 17:35:09');

-- --------------------------------------------------------

--
-- Structure de la table `question_themes`
--

CREATE TABLE IF NOT EXISTS `question_themes` (
  `id` int(11) NOT NULL,
  `theme` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

--
-- Contenu de la table `question_themes`
--

INSERT INTO `question_themes` (`id`, `theme`, `created_at`, `updated_at`) VALUES
(1, 'Travel Regulations', '2015-10-01 00:00:00', '2015-10-06 16:37:23'),
(2, 'Foreign exchange and Expenses ', '2015-10-01 00:00:00', '2015-10-06 16:38:46'),
(3, 'Climate and clothing', '2015-10-01 00:00:00', '2015-10-06 16:39:43'),
(4, 'Pratical', '2015-10-01 00:00:00', '2015-10-06 16:40:21'),
(9, 'Accommodation', '2015-10-06 16:44:28', '2015-10-06 16:44:28');

-- --------------------------------------------------------

--
-- Structure de la table `registrations`
--

CREATE TABLE IF NOT EXISTS `registrations` (
  `id` int(11) NOT NULL,
  `firstname` varchar(255) NOT NULL,
  `surname` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `date_of_birth` date NOT NULL,
  `nationality` varchar(255) NOT NULL,
  `passport_number` varchar(255) NOT NULL,
  `passport_date` date NOT NULL,
  `date_airport` varchar(255) NOT NULL,
  `invitation_letter` enum('n','y') NOT NULL,
  `address` varchar(1000) NOT NULL,
  `address_bis` varchar(1000) NOT NULL,
  `city` varchar(255) NOT NULL,
  `zip_code` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL,
  `firstname_second` varchar(255) NOT NULL,
  `surname_second` varchar(255) NOT NULL,
  `dietary_restrictions` varchar(255) NOT NULL,
  `desc_dietary_restrictions` text NOT NULL,
  `activity` varchar(255) NOT NULL,
  `comment` text NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure de la table `roles`
--

CREATE TABLE IF NOT EXISTS `roles` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(200) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;

--
-- Contenu de la table `roles`
--

INSERT INTO `roles` (`id`, `name`) VALUES
(1, 'Accès aux administrateurs'),
(38, 'Accès aux inscriptions'),
(47, 'Accès aux questions'),
(48, 'Accès aux photos');

-- --------------------------------------------------------

--
-- Structure de la table `role_user`
--

CREATE TABLE IF NOT EXISTS `role_user` (
  `id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `role_id` int(10) unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8;

--
-- Contenu de la table `role_user`
--

INSERT INTO `role_user` (`id`, `user_id`, `role_id`, `created_at`, `updated_at`) VALUES
(63, 2, 1, '2015-10-02 00:00:00', '2015-10-02 00:00:00'),
(64, 2, 38, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(65, 2, 48, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(66, 2, 47, '0000-00-00 00:00:00', '0000-00-00 00:00:00');

--
-- Index pour les tables exportées
--

--
-- Index pour la table `languages`
--
ALTER TABLE `languages`
  ADD PRIMARY KEY (`id`);

--
-- Index pour la table `photos`
--
ALTER TABLE `photos`
  ADD PRIMARY KEY (`id`);

--
-- Index pour la table `questions`
--
ALTER TABLE `questions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `Theme` (`question_theme_id`);

--
-- Index pour la table `question_themes`
--
ALTER TABLE `question_themes`
  ADD PRIMARY KEY (`id`);

--
-- Index pour la table `registrations`
--
ALTER TABLE `registrations`
  ADD PRIMARY KEY (`id`);

--
-- Index pour la table `roles`
--
ALTER TABLE `roles`
  ADD PRIMARY KEY (`id`);

--
-- Index pour la table `role_user`
--
ALTER TABLE `role_user`
  ADD PRIMARY KEY (`id`),
  ADD KEY `role_user_user_id_foreign` (`user_id`),
  ADD KEY `role_user_role_id_foreign` (`role_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `role_id` (`role_id`),
  ADD KEY `user_id_2` (`user_id`),
  ADD KEY `user_id_3` (`user_id`),
  ADD KEY `role_id_2` (`role_id`);

--
-- AUTO_INCREMENT pour les tables exportées
--

--
-- AUTO_INCREMENT pour la table `languages`
--
ALTER TABLE `languages`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT pour la table `photos`
--
ALTER TABLE `photos`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=50;
--
-- AUTO_INCREMENT pour la table `questions`
--
ALTER TABLE `questions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=27;
--
-- AUTO_INCREMENT pour la table `question_themes`
--
ALTER TABLE `question_themes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=10;
--
-- AUTO_INCREMENT pour la table `registrations`
--
ALTER TABLE `registrations`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT pour la table `roles`
--
ALTER TABLE `roles`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=49;
--
-- AUTO_INCREMENT pour la table `role_user`
--
ALTER TABLE `role_user`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=67;
--
-- Contraintes pour les tables exportées
--

--
-- Contraintes pour la table `questions`
--
ALTER TABLE `questions`
  ADD CONSTRAINT `questions_ibfk_1` FOREIGN KEY (`question_theme_id`) REFERENCES `question_themes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Contraintes pour la table `role_user`
--
ALTER TABLE `role_user`
  ADD CONSTRAINT `roleidindex` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `useridindex` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

