SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = 'SYSTEM';
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

CREATE DATABASE `starbase` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `starbase`;

DROP TABLE IF EXISTS `moons`;
CREATE TABLE `moons` (
  `id` BIGINT(20) UNSIGNED NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `pos`;
CREATE TABLE `pos` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) NOT NULL COMMENT 'Structure name',
  `region` VARCHAR(64) NOT NULL COMMENT 'Region',
  `system` VARCHAR(64) NOT NULL COMMENT 'Solar system',
  `planet` INT(2) UNSIGNED NOT NULL COMMENT 'Planet',
  `moon` INT(3) UNSIGNED NOT NULL COMMENT 'Moon',
  `status` enum('Normal','Reinforced','Low Fuel') NOT NULL COMMENT 'Normal / Reinforced / Low fuel  /etc.',
  `corp` VARCHAR(50) NOT NULL COMMENT 'Corp. owned by',
  `owner` VARCHAR(24) NOT NULL COMMENT 'Manager / Owner',
  `private` INT(1) NOT NULL COMMENT '1 = yea',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `pos` (`id`, `name`, `region`, `system`, `planet`, `moon`, `status`, `corp`, `owner`, `private`) VALUES
(1, 'Tiny', 'Venal',    '2TH-3F',   3,  13, 'Normal',   'Dreddit',  'Invitus',  0),
(2, 'Your Mum', 'Delkin',   'DKUK-G',   2,  3,  'Low Fuel', 'Goonwaffe',    'Joe Blogs',    0),
(3, 'John Claude Van Damme',    'Pure Blind',   'CR-AQH',   8,  5,  'Normal',   'Dreddit',  'demo', 0);

DROP TABLE IF EXISTS `setting`;
CREATE TABLE `setting` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `value` VARCHAR(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `setting` (`id`, `name`, `value`) VALUES
(1, 'notice',   'This site is currently under construction. Try not to fag it up.'),
(2, 'alliance', 'Test Alliance Please Ignore'),
(3, 'alliance_tag', 'TEST'),
(4, 'corps',    'Dreddit,Did I Just Do That,Rim Collection RC,Ars Ex Discordia');

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user` VARCHAR(24) NOT NULL COMMENT 'Username',
  `pass` VARCHAR(32) NOT NULL COMMENT 'Hashed password',
  `rights` VARCHAR(5) NOT NULL DEFAULT '0,0,0' COMMENT 'User rights. admin, add pos, mod corp',
  `corp` VARCHAR(50) NOT NULL COMMENT 'Users corp.',
  `active` enum('true','false') NOT NULL DEFAULT 'false',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;