 --
 
 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;
 
 --

-- phpMyAdmin SQL Dump
-- version 4.6.0
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Sep 21, 2016 at 06:46 PM
-- Server version: 5.5.46-0+deb8u1
-- PHP Version: 5.6.17-0+deb8u1

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

--
-- Database: `treasurehuntbot`
--

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

--
-- Table structure for table `assigned_locations`
--

CREATE TABLE `assigned_locations` (
  `game_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `assigned_on` datetime NOT NULL,
  `reached_on` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `assigned_riddles`
--

CREATE TABLE `assigned_riddles` (
  `game_id` int(11) NOT NULL,
  `riddle_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `assigned_on` datetime NOT NULL,
  `last_answer_on` datetime DEFAULT NULL,
  `solved_on` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `events`
--

CREATE TABLE `events` (
  `event_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `logo_path` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `registered_on` datetime NOT NULL,
  `num_steps` tinyint(3) UNSIGNED NOT NULL DEFAULT '10',
  `organizer_id` int(10) UNSIGNED NOT NULL,
  `min_avg_distance` float DEFAULT NULL COMMENT 'Minimum average distance between locations (in kms)',
  `telegram_channel` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `games`
--

CREATE TABLE `games` (
  `game_id` int(10) UNSIGNED NOT NULL,
  `event_id` int(10) UNSIGNED NOT NULL,
  `status` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `location_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `location_lat` float DEFAULT NULL,
  `location_lng` float DEFAULT NULL,
  `organizer_id` int(10) UNSIGNED DEFAULT NULL,
  `organizer_email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `telegram_channel` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tmp_location_lat` float DEFAULT NULL,
  `tmp_location_lng` float DEFAULT NULL,
  `tmp_location_image_path` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tmp_location_description` text COLLATE utf8_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `groups`
--

CREATE TABLE `groups` (
  `game_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `participants_count` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `photo_path` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Path to the group''s photo',
  `state` tinyint(2) UNSIGNED NOT NULL DEFAULT '0',
  `registration` datetime NOT NULL COMMENT 'Original generation timestamp',
  `last_state_change` datetime NOT NULL COMMENT 'Timestamp of last state change'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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


--

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

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

--
-- Indexes for table `reached_locations`
--
ALTER TABLE `reached_locations`
  ADD PRIMARY KEY (`id`,`location_id`),
  ADD KEY `location_id` (`location_id`);

--
-- Indexes for table `schools`
--
ALTER TABLE `schools`
  ADD PRIMARY KEY (`codice_scuola`);

--
-- Indexes for table `stats`
--
ALTER TABLE `stats`
  ADD PRIMARY KEY (`type`,`date`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `identities`
--
ALTER TABLE `identities`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Internal ID', AUTO_INCREMENT=79;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `identities`
--
ALTER TABLE `identities`
  ADD CONSTRAINT `school_constraint` FOREIGN KEY (`school_code`) REFERENCES `schools` (`codice_scuola`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `reached_locations`
--
ALTER TABLE `reached_locations`
  ADD CONSTRAINT `user_id` FOREIGN KEY (`id`) REFERENCES `identities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `location_id` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`);
