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

CREATE TABLE IF NOT EXISTS `achievements` (
  `aid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `identifier` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `broadcast` varchar(128) NOT NULL,
  `datafile` varchar(16) NOT NULL DEFAULT 'default.dat' COMMENT 'no broadcast if null',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`aid`),
  UNIQUE KEY `identifier` (`identifier`)
) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `user-achievements` (
  `id` int(11) NOT NULL,
  `uid` int(11) NOT NULL COMMENT 'user ID',
  `aid` int(11) NOT NULL COMMENT 'achieved ID',
  `achieved` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `users` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB;





INSERT INTO `achievements` (`aid`, `identifier`, `name`, `broadcast`, `datafile`, `created`) VALUES
(1, 'test', 'This is test', '<playername> has got test achievement', 'test1.dat', '2013-09-03 22:50:48'),
(2, 'test2', 'Another test', 'Yay, <playername> has got achievement', 'test2.dat', '2013-09-03 22:54:19');

INSERT INTO `user-achievements` (`id`, `uid`, `aid`, `achieved`) VALUES
(0, 1, 1, '2013-09-03 22:52:15'),
(2, 4, 2, '2013-09-03 22:54:56');

INSERT INTO `users` (`uid`, `name`) VALUES
(1, 'Thasan'),
(2, 'Mathieu1903'),
(3, 'seetricks'),
(4, 'Zerot');