 -- phpMyAdmin SQL Dump
--- version 4.6.0
--- http://www.phpmyadmin.net
+-- version 4.6.4
+-- https://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
+-- 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`
+-- Database: `treasurehuntbot_zelda`
 --
-
--- --------------------------------------------------------
-
---
--- 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;
 
 -- --------------------------------------------------------
 

--

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
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `log`
--

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;

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

--
-- Table structure for table `riddles`
--

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
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

--

CREATE TABLE `identities` (
  `id` int(10) UNSIGNED NOT NULL COMMENT 'Internal ID',
  `telegram_id` int(11) NOT NULL,
  `full_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `state` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
  `school_code` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `first_access` datetime NOT NULL,
  `last_access` datetime NOT NULL,
  `is_admin` bit(1) NOT NULL DEFAULT b'0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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