-- Create the pitchfx database
DROP DATABASE IF EXISTS `pitchfx`;
 `pitchfx`.CREATE DATABASE `pitchfx` /*!40100 DEFAULT CHARACTER SET latin1 */;

-- Create the players table
DROP TABLE IF EXISTS `pitchfx`.`players`;
CREATE TABLE  `pitchfx`.`players` (
  `EliasId` int(10) unsigned NOT NULL auto_increment,
  `FirstName` varchar(20) NOT NULL,
  `LastName` varchar(20) NOT NULL,
  `LahmanId` varchar(10) default NULL,
  PRIMARY KEY  (`EliasId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Create the umpires table
DROP TABLE IF EXISTS `pitchfx`.`umpires`;
CREATE TABLE  `pitchfx`.`umpires` (
  `UmpireId` int(10) unsigned NOT NULL auto_increment,
  `FirstName` varchar(20) NOT NULL,
  `LastName` varchar(20) NOT NULL,
  PRIMARY KEY  (`UmpireId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Create the games table
DROP TABLE IF EXISTS `pitchfx`.`games`;
CREATE TABLE  `pitchfx`.`games` (
  `GameId` varchar(12) NOT NULL,
  `HomeTeam` varchar(3) NOT NULL,
  `VisTeam` varchar(3) NOT NULL,
  `Year` int(10) unsigned NOT NULL,
  `Month` int(10) unsigned NOT NULL,
  `Day` int(10) unsigned NOT NULL,
  `DHGame` int(10) unsigned NOT NULL,
  `Umpire_Home` int(10) unsigned default NULL,
  `Umpire_First` int(10) unsigned default NULL,
  `Umpire_Second` int(10) unsigned default NULL,
  `Umpire_Third` int(10) unsigned default NULL,
  PRIMARY KEY  (`GameId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- Create the events table
DROP TABLE IF EXISTS `pitchfx`.`events`;
CREATE TABLE  `pitchfx`.`events` (
  `GameID` varchar(12) NOT NULL,
  `EventNumber` int(10) unsigned NOT NULL,
  `AtBatNum` int(10) unsigned default NULL,
  `Inning` int(10) unsigned NOT NULL,
  `BattingTeam` int(10) unsigned NOT NULL,
  `Balls` int(10) unsigned NOT NULL,
  `Strikes` int(10) unsigned NOT NULL,
  `Outs` int(10) unsigned NOT NULL,
  `Batter` varchar(6) NOT NULL,
  `Pitcher` varchar(6) default NULL,
  `Description` varchar(1000) NOT NULL,
  `Stand` varchar(1) default NULL,
  `Event` varchar(50) NOT NULL,
  `Hit_X` double default NULL,
  `Hit_Y` double default NULL,
  `Hit_Type` varchar(3) default NULL,
  `Pitch` int(10) unsigned default NULL,
  PRIMARY KEY  (`GameID`,`EventNumber`),
  KEY `batter` (`Batter`),
  KEY `pitcher` (`Pitcher`),
  KEY `event` (`Event`),
  KEY `eventnumber` (`EventNumber`),
  KEY `gameid` (`GameID`),
  CONSTRAINT `FK_events_1` FOREIGN KEY (`GameID`) REFERENCES `games` (`GameId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Create the pitches table
DROP TABLE IF EXISTS `pitchfx`.`pitches`;
CREATE TABLE  `pitchfx`.`pitches` (
  `GameId` varchar(12) NOT NULL,
  `EventNumber` int(10) unsigned NOT NULL,
  `PitchId` int(10) unsigned NOT NULL,
  `Description` varchar(45) NOT NULL,
  `Type` varchar(1) NOT NULL,
  `X` double default NULL,
  `Y` double default NULL,
  `Start_Speed` double default NULL,
  `End_Speed` double default NULL,
  `Sz_Top` double default NULL,
  `Sz_Bottom` double default NULL,
  `Pfx_X` double default NULL,
  `Pfx_Y` double default NULL,
  `Pfx_Z` double default NULL,
  `Px` double default NULL,
  `Pz` double default NULL,
  `X0` double default NULL,
  `Y0` double default NULL,
  `Z0` double default NULL,
  `Vx0` double default NULL,
  `Vy0` double default NULL,
  `Vz0` double default NULL,
  `Ax` double default NULL,
  `Ay` double default NULL,
  `Az` double default NULL,
  `Break_Y` double default NULL,
  `Break_Angle` double default NULL,
  `Break_Length` double default NULL,
  PRIMARY KEY  (`GameId`,`PitchId`,`EventNumber`),
  KEY `FK_pitches_1` (`GameId`,`EventNumber`),
  KEY `description` (`Description`),
  CONSTRAINT `FK_pitches_1` FOREIGN KEY (`GameId`, `EventNumber`) REFERENCES `events` (`GameID`, `EventNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;