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

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

CREATE DATABASE IF NOT EXISTS `uncertainty` DEFAULT CHARACTER SET ascii COLLATE ascii_bin;
USE `uncertainty`;

CREATE TABLE IF NOT EXISTS `admincodes` (
`id` int(11) NOT NULL,
  `copilotid` int(11) NOT NULL,
  `adminid` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin;

CREATE TABLE IF NOT EXISTS `iplog` (
`id` int(11) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ip` char(255) COLLATE ascii_bin NOT NULL COMMENT 'This so wide because X-FORWARED-FOR might have multiples',
  `suid` char(8) COLLATE ascii_bin NOT NULL,
  `copilotid` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin;

CREATE TABLE IF NOT EXISTS `resultcache` (
`id` int(10) unsigned NOT NULL,
  `mode` enum('roulette','fixedInterval','bisection','probability','tertile') COLLATE ascii_bin NOT NULL,
  `data` text COLLATE ascii_bin NOT NULL,
  `result` text COLLATE ascii_bin NOT NULL,
  `lastused` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin;

CREATE TABLE IF NOT EXISTS `transaction` (
`id` mediumint(8) unsigned NOT NULL,
  `transactiontype` enum('roulette','fixedInterval','bisection','roulettefeedback','bisectionfeedback','fixedfeedback','parameters','placeholder','probability','probabilityfeedback','tertile','tertilefeedback','terminate') COLLATE ascii_bin NOT NULL,
  `copilotid` bigint(11) unsigned NOT NULL,
  `UID` char(8) COLLATE ascii_bin NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `data` longtext COLLATE ascii_bin NOT NULL,
  `suid` char(8) COLLATE ascii_bin NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin;


ALTER TABLE `admincodes`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `iplog`
 ADD PRIMARY KEY (`id`), ADD KEY `suid` (`suid`);

ALTER TABLE `resultcache`
 ADD PRIMARY KEY (`id`), ADD KEY `mode` (`mode`), ADD FULLTEXT KEY `data` (`data`);

ALTER TABLE `transaction`
 ADD PRIMARY KEY (`id`), ADD KEY `suid` (`suid`), ADD KEY `UID` (`UID`), ADD KEY `copilotid` (`copilotid`), ADD KEY `timestamp` (`timestamp`), ADD KEY `transactiontype` (`transactiontype`);


ALTER TABLE `admincodes`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `iplog`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `resultcache`
MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT;
ALTER TABLE `transaction`
MODIFY `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
