CREATE TABLE players (
   name VARCHAR(50) NOT NULL,
   team INTEGER,
   position INTEGER NOT NULL,
   strPlus INTEGER NOT NULL,
   agiPlus INTEGER NOT NULL,
   movPlus INTEGER NOT NULL,
   avPlus INTEGER NOT NULL,
   skillsPlus VARCHAR(50),
   injuries VARCHAR(50),
   completions INTEGER NOT NULL,
   tds INTEGER NOT NULL,
   interceptions INTEGER NOT NULL,
   casualties INTEGER NOT NULL,
   mvps INTEGER NOT NULL,
   totalSpps INTEGER NOT NULL,
   
   player_id INTEGER NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(player_id)
);

CREATE TABLE positions (
   name VARCHAR(50) NOT NULL,
   str INTEGER NOT NULL,
   agi INTEGER NOT NULL,
   mov INTEGER NOT NULL,
   av INTEGER NOT NULL,
   skills VARCHAR(50) NOT NULL,
   value INTEGER NOT NULL,
   limit INTEGER NOT NULL,
   race INTEGER NOT NULL,

   position_id INTEGER NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(position_id)
);

CREATE TABLE teams (
   name VARCHAR(50) NOT NULL,
   coach INTEGER NOT NULL,
   race INTEGER NOT NULL,
   fan_factor INTEGER NOT NULL,
   rerolls INTEGER NOT NULL,
   rating INTEGER NOT NULL,
   treasury INTEGER NOT NULL,
   apothecary INTEGER NOT NULL,
   wizard INTEGER NOT NULL,
   cheerleaders INTEGER NOT NULL,
   asstCoaches INTEGER NOT NULL,
   wins INTEGER NOT NULL,
   losses INTEGER NOT NULL,
   ties INTEGER NOT NULL,
   bannerUrl VARCHAR(50),
   notes TEXT,

   team_id INTEGER NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(team_id)
);

CREATE TABLE games (
   home_game_stats INTEGER NOT NULL,
   away_game_stats INTEGER NOT NULL,

   game_id INTEGER NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(game_id)
);

CREATE TABLE game_stats (
   team INTEGER NOT NULL,
   score INTEGER NOT NULL,
   bhFor INTEGER NOT NULL,
   siFor INTEGER NOT NULL,
   kFor INTEGER NOT NULL,
   bhAgainst INTEGER NOT NULL,
   siAgainst INTEGER NOT NULL,
   kAgainst INTEGER NOT NULL,
   gate INTEGER NOT NULL,
   winnings INTEGER NOT NULL,

   game_stats_id INTEGER NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(game_stats_id)
);

CREATE TABLE races(
   name VARCHAR(50) NOT NULL,
   reroll INTEGER NOT NULL,

   race_id INTEGER NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(race_id)
);


CREATE TABLE user (
   login CHAR(20) NOT NULL,
   password CHAR(20) NOT NULL,
   email VARCHAR(50),
   first CHAR(20),
   last CHAR(20),
   url VARCHAR(50),
   flags SET("admin"),
   created_dt TIMESTAMP NOT NULL,
   lastsubmit_dt TIMESTAMP,

   user_id INTEGER NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(user_id, login)
);

CREATE TABLE news (
   subject VARCHAR(50),
   text TEXT,
   user INTEGER,
   submitted_dt TIMESTAMP NOT NULL,

   news_id INTEGER NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(news_id)
);

CREATE TABLE messages (
   subject VARCHAR(50),
   text TEXT,
   user INTEGER,
   submitted_dt TIMESTAMP NOT NULL,
   parent_id INTEGER default 0,
   root_id INTEGER default 0,

   message_id INTEGER NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(message_id)
);