 --
 
 CREATE TABLE `locations` (
-  `game_id` int(11) NOT NULL,
-  `id` int(11) NOT NULL,
-  `code` binary(16) NOT NULL,
-  `internal_note` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
-  `lat` float NOT NULL,
-  `lng` float NOT NULL,
-  `image_path` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
-  `description` text COLLATE utf8_unicode_ci
+  `id` int(10) UNSIGNED NOT NULL,
+  `code` char(8) COLLATE utf8_unicode_ci NOT NULL,
+  `target_state` tinyint(4) DEFAULT NULL,
+  `description` varchar(50) COLLATE utf8_unicode_ci NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Dumping data for table `locations`
+--
+
+INSERT INTO `locations` (`id`, `code`, `target_state`, `description`) VALUES
+(1, 'BqeyekUv', 10, 'Borgo Mercatale'),
+(2, 'Q7zF0q0V', 20, 'Magistero'),
+(3, 'IZoJzRgJ', 30, 'Selfie point'),
+(4, 'vVVxASDS', 40, 'Mensa Tridente'),
+(5, 'GvL7tVCc', 50, 'Teatro La Vela'),
+(6, 'SjFyria2', 10, 'Porta Santa Lucia');
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `reached_locations`
+--
+
+CREATE TABLE `reached_locations` (
+  `id` int(10) UNSIGNED NOT NULL,
+  `location_id` int(10) UNSIGNED NOT NULL,
+  `timestamp` datetime NOT NULL,
+  `correct_answer` tinyint(1) NOT NULL DEFAULT '0'
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 -- --------------------------------------------------------
 
 --
--- Table structure for table `log`
+-- Table structure for table `schools`
 --
 
-CREATE TABLE `log` (
-  `timestamp` datetime NOT NULL,
-  `tag` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
-  `message` text COLLATE utf8_unicode_ci NOT NULL,
-  `severity` tinyint(3) UNSIGNED NOT NULL,
-  `group_id` int(11) DEFAULT NULL,
-  `telegram_chat_id` int(11) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+CREATE TABLE `schools` (
+  `ISTAT_COM` smallint(6) UNSIGNED NOT NULL,
+  `ISTAT_PROV` tinyint(3) UNSIGNED NOT NULL,
+  `ISTAT_REG` tinyint(3) UNSIGNED NOT NULL,
+  `ISTAT_REG_7C` smallint(7) UNSIGNED NOT NULL,
+  `codice_scuola` char(10) NOT NULL,
+  `denominazione` varchar(255) NOT NULL,
+  `des_tipo_scuola` varchar(255) NOT NULL,
+  `indirizzo` varchar(255) DEFAULT NULL,
+  `comune` varchar(255) DEFAULT NULL,
+  `cap` char(5) DEFAULT NULL,
+  `telefono` varchar(16) DEFAULT NULL,
+  `fax` varchar(16) DEFAULT NULL,
+  `email` varchar(255) DEFAULT NULL,
+  `pec` varchar(255) DEFAULT NULL,
+  `sito_web` varchar(255) DEFAULT NULL,
+  `cod_istituto_principale` char(10) DEFAULT NULL,
+  `statale` varchar(20) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 -- --------------------------------------------------------
 
 --
--- Table structure for table `riddles`
+-- Table structure for table `stats`
 --
 
-CREATE TABLE `riddles` (
-  `game_id` int(11) NOT NULL,
-  `id` int(11) NOT NULL,
-  `image_path` varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL,
-  `text` text COLLATE utf8_unicode_ci NOT NULL,
-  `solution` text COLLATE utf8_unicode_ci NOT NULL,
-  `hint` text COLLATE utf8_unicode_ci
+CREATE TABLE `stats` (
+  `type` smallint(5) UNSIGNED NOT NULL,
+  `date` date NOT NULL,
+  `message_id` int(10) UNSIGNED NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 --

--

--
-- Indexes for table `assigned_locations`
--
ALTER TABLE `assigned_locations`
  ADD PRIMARY KEY (`game_id`,`location_id`,`group_id`) USING BTREE,
  ADD KEY `assloc_group_constraint` (`group_id`);

--
-- Indexes for table `assigned_riddles`
--
ALTER TABLE `assigned_riddles`
  ADD PRIMARY KEY (`game_id`,`riddle_id`,`group_id`),
  ADD KEY `assriddles_group_constraint` (`group_id`);

--
-- Indexes for table `events`
--
ALTER TABLE `events`
  ADD PRIMARY KEY (`event_id`),
  ADD KEY `event_organizer_index` (`organizer_id`);

--
-- Indexes for table `games`
--
ALTER TABLE `games`
  ADD PRIMARY KEY (`game_id`),
  ADD KEY `game_event_index` (`event_id`),
  ADD KEY `game_organizer_index` (`organizer_id`);

--
-- Indexes for table `groups`
--
ALTER TABLE `groups`
  ADD PRIMARY KEY (`game_id`,`group_id`),
  ADD KEY `status_group_constraint` (`group_id`);

--
-- Indexes for table `identities`
--
ALTER TABLE `identities`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `telegram_id` (`telegram_id`);

--
-- Indexes for table `locations`
--
ALTER TABLE `locations`
  ADD PRIMARY KEY (`game_id`,`id`),
  ADD UNIQUE KEY `location_code_index` (`game_id`,`code`) USING BTREE;

--
-- Indexes for table `log`
--
ALTER TABLE `log`
  ADD KEY `timestamp` (`timestamp`),
  ADD KEY `tag` (`tag`),
  ADD KEY `telegram_chat_id` (`telegram_chat_id`),
  ADD KEY `group_id` (`group_id`);

--
-- Indexes for table `riddles`
--
ALTER TABLE `riddles`
  ADD PRIMARY KEY (`game_id`,`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `events`
--
ALTER TABLE `events`
  MODIFY `event_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `games`
--
ALTER TABLE `games`
  MODIFY `game_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `identities`
--
ALTER TABLE `identities`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Internal ID';
--
-- Constraints for dumped tables
--

--
-- Constraints for table `assigned_locations`
--
ALTER TABLE `assigned_locations`
  ADD CONSTRAINT `assloc_group_constraint` FOREIGN KEY (`group_id`) REFERENCES `identities` (`id`),
  ADD CONSTRAINT `assloc_location_constraint` FOREIGN KEY (`game_id`,`location_id`) REFERENCES `locations` (`game_id`, `id`);

--
-- Constraints for table `assigned_riddles`
--
ALTER TABLE `assigned_riddles`
  ADD CONSTRAINT `assriddles_group_constraint` FOREIGN KEY (`group_id`) REFERENCES `identities` (`id`),
  ADD CONSTRAINT `assriddles_riddle_constraint` FOREIGN KEY (`game_id`,`riddle_id`) REFERENCES `riddles` (`game_id`, `id`);

--
-- Constraints for table `games`
--
ALTER TABLE `games`
  ADD CONSTRAINT `game_event_constraint` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`) ON DELETE CASCADE;

--
-- Constraints for table `groups`
--
ALTER TABLE `groups`
  ADD CONSTRAINT `status_group_constraint` FOREIGN KEY (`group_id`) REFERENCES `identities` (`id`);

--
-- Constraints for table `log`
--
ALTER TABLE `log`
  ADD CONSTRAINT `log_group_constraint` FOREIGN KEY (`group_id`) REFERENCES `identities` (`id`) ON DELETE SET NULL ON UPDATE SET NULL;

-- phpMyAdmin SQL Dump
-- version 4.6.4
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Jan 31, 2017 at 07:59 PM
-- Server version: 5.5.53-0+deb8u1
-- PHP Version: 5.6.27-0+deb8u1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `treasurehuntbot_zelda`
--

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

