#Because I am having a lot of trouble getting FK's to work nicely in this schema, I've decided to do away with them.

#Research is showing me that the main use of FK's is to enforce referential integrity and to generate additional indexes to speed queries.  I am going to trust the data provided by OOTP is valid.  

#Instead of using FK's, I am going to index the columns that ordinarily would be FK's.

#I am also assuming that those tables with composite PK's will have all of those fields indexed.

#Regarding PK's for the league history and player career tables, I decided to add an auto_increment PK to those tables.  I was getting lots of duplicates on joins, and this seems a more elegant option than having 6 tables with 4-or-more-column composite keys.

DROP DATABASE IF EXISTS TEST1;

CREATE DATABASE TEST1;

USE TEST1;


CREATE TABLE `nations` (
  `nation_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `short_name` varchar(50) DEFAULT NULL,
  `abbreviation` varchar(50) DEFAULT NULL,
  `demonym` varchar(50) DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `baseball_quality` int(11) DEFAULT NULL,
  `continent_id` int(11) DEFAULT NULL,
  `main_language_id` int(11) DEFAULT NULL,
  `quality_total` int(11) DEFAULT NULL,
  `capital_id` int(11) DEFAULT NULL,
  `use_hardcoded_ml_player_origins` tinyint(4) DEFAULT NULL,
  `this_is_the_usa` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`nation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `cities` (
  `city_id` int(11) NOT NULL,
  `nation_id` int(11) DEFAULT NULL,
  `state_id` int(11) DEFAULT NULL,
  `name` varchar(80) DEFAULT NULL,
  `abbreviation` varchar(10) DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `main_language_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `leagues` (
  `league_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `abbr` varchar(50) DEFAULT NULL,
  `nation_id` int(11) DEFAULT NULL,
  `language_id` int(11) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `historical_league` tinyint(4) DEFAULT NULL,
  `logo_file_name` varchar(200) DEFAULT NULL,
  `players_path` varchar(200) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `preferred_start_date` date DEFAULT NULL,
  `pitcher_award_name` varchar(50) DEFAULT NULL,
  `mvp_award_name` varchar(50) DEFAULT NULL,
  `rookie_award_name` varchar(50) DEFAULT NULL,
  `defense_award_name` varchar(50) DEFAULT NULL,
  `fictional_players` tinyint(4) DEFAULT NULL,
  `start_fantasy_draft` tinyint(4) DEFAULT NULL,
  `trading_deadline` tinyint(4) DEFAULT NULL,
  `winter_meetings` tinyint(4) DEFAULT NULL,
  `arbitration_offering` tinyint(4) DEFAULT NULL,
  `show_draft_pool` tinyint(4) DEFAULT NULL,
  `rosters_expanded` tinyint(4) DEFAULT NULL,
  `draft_date` date DEFAULT NULL,
  `rule_5_draft_date` date DEFAULT NULL,
  `roster_expand_date` date DEFAULT NULL,
  `trade_deadline_date` date DEFAULT NULL,
  `allstar_date` date DEFAULT NULL,
  `days_until_deadline` int(11) DEFAULT NULL,
  `next_draft_type` int(11) DEFAULT NULL,
  `parent_league_id` int(11) DEFAULT NULL,
  `league_state` smallint(6) DEFAULT NULL,
  `season_year` int(11) DEFAULT NULL,
  `historical_year` smallint(6) DEFAULT NULL,
  `league_level` smallint(6) DEFAULT NULL,
  `stats_detail` int(11) DEFAULT NULL,
  `historical_import_path` varchar(200) DEFAULT NULL,
  `foreigner_percentage` smallint(6) DEFAULT NULL,
  `was_ootp6` tinyint(4) DEFAULT NULL,
  `was_65` tinyint(4) DEFAULT NULL,
  `allstar_game` tinyint(4) DEFAULT NULL,
  `auto_schedule_allstar` tinyint(4) DEFAULT NULL,
  `allstar_team_id0` int(11) DEFAULT NULL,
  `allstar_team_id1` int(11) DEFAULT NULL,
  `schedule_file_1` varchar(200) DEFAULT NULL,
  `schedule_file_2` varchar(200) DEFAULT NULL,
  `rules_rule_5` tinyint(4) DEFAULT NULL,
  `rules_minor_league_options` tinyint(4) DEFAULT NULL,
  `rules_trading` tinyint(4) DEFAULT NULL,
  `rules_draft_pick_trading` tinyint(4) DEFAULT NULL,
  `rules_financials` tinyint(4) DEFAULT NULL,
  `rules_amateur_draft` tinyint(4) DEFAULT NULL,
  `rules_fa_compensation` tinyint(4) DEFAULT NULL,
  `rules_schedule_balanced` tinyint(4) DEFAULT NULL,
  `rules_schedule_inter_league` tinyint(4) DEFAULT NULL,
  `rules_schedule_force_start_day` tinyint(4) DEFAULT NULL,
  `rules_trades_other_leagues` tinyint(4) DEFAULT NULL,
  `rules_free_agents_from_other_leagues` tinyint(4) DEFAULT NULL,
  `rules_free_agents_leave_other_leagues` tinyint(4) DEFAULT NULL,
  `rules_allstar_game` tinyint(4) DEFAULT NULL,
  `rules_spring_training` tinyint(4) DEFAULT NULL,
  `rules_active_roster_limit` smallint(6) DEFAULT NULL,
  `rules_secondary_roster_limit` smallint(6) DEFAULT NULL,
  `rules_expanded_roster_limit` smallint(6) DEFAULT NULL,
  `rules_min_service_days` smallint(6) DEFAULT NULL,
  `rules_waiver_period_length` smallint(6) DEFAULT NULL,
  `rules_dfa_period_length` smallint(6) DEFAULT NULL,
  `rules_fa_minimum_years` smallint(6) DEFAULT NULL,
  `rules_salary_arbitration_minimum_years` smallint(6) DEFAULT NULL,
  `rules_minor_league_fa_minimum_years` smallint(6) DEFAULT NULL,
  `rules_foreigner_limit` smallint(6) DEFAULT NULL,
  `rules_foreigner_pitcher_limit` smallint(6) DEFAULT NULL,
  `rules_foreigner_hitter_limit` smallint(6) DEFAULT NULL,
  `rules_schedule_games_per_team` smallint(6) DEFAULT NULL,
  `rules_schedule_typical_series` smallint(6) DEFAULT NULL,
  `rules_schedule_preferred_start_day` smallint(6) DEFAULT NULL,
  `rules_amateur_draft_rounds` smallint(6) DEFAULT NULL,
  `rules_minimum_salary` int(11) DEFAULT NULL,
  `rules_salary_cap` int(11) DEFAULT NULL,
  `rules_player_salary0` int(11) DEFAULT NULL,
  `rules_player_salary1` int(11) DEFAULT NULL,
  `rules_player_salary2` int(11) DEFAULT NULL,
  `rules_player_salary3` int(11) DEFAULT NULL,
  `rules_player_salary4` int(11) DEFAULT NULL,
  `rules_player_salary5` int(11) DEFAULT NULL,
  `rules_player_salary6` int(11) DEFAULT NULL,
  `rules_player_salary7` int(11) DEFAULT NULL,
  `rules_average_coach_salary` int(11) DEFAULT NULL,
  `rules_average_attendance` int(11) DEFAULT NULL,
  `rules_average_national_media_contract` int(11) DEFAULT NULL,
  `rules_cash_maximum` int(11) DEFAULT NULL,
  `rules_average_ticket_price` double DEFAULT NULL,
  `rules_revenue_sharing` tinyint(4) DEFAULT NULL,
  `rules_national_media_contract_fixed` tinyint(4) DEFAULT NULL,
  `rules_owner_decides_budget` tinyint(4) DEFAULT NULL,
  `rules_schedule_auto_adjust_dates` tinyint(4) DEFAULT NULL,
  `rules_historical_import_rookies` tinyint(4) DEFAULT NULL,
  `avg_rating_contact` int(11) DEFAULT NULL,
  `avg_rating_gap` int(11) DEFAULT NULL,
  `avg_rating_power` int(11) DEFAULT NULL,
  `avg_rating_eye` int(11) DEFAULT NULL,
  `avg_rating_strikeouts` int(11) DEFAULT NULL,
  `avg_rating_stuff` int(11) DEFAULT NULL,
  `avg_rating_movement` int(11) DEFAULT NULL,
  `avg_rating_control` int(11) DEFAULT NULL,
  `avg_rating_fielding0` int(11) DEFAULT NULL,
  `avg_rating_fielding1` int(11) DEFAULT NULL,
  `avg_rating_fielding2` int(11) DEFAULT NULL,
  `avg_rating_fielding3` int(11) DEFAULT NULL,
  `avg_rating_fielding4` int(11) DEFAULT NULL,
  `avg_rating_fielding5` int(11) DEFAULT NULL,
  `avg_rating_fielding6` int(11) DEFAULT NULL,
  `avg_rating_fielding7` int(11) DEFAULT NULL,
  `avg_rating_fielding8` int(11) DEFAULT NULL,
  `avg_rating_fielding9` int(11) DEFAULT NULL,
  `avg_rating_overall` int(11) DEFAULT NULL,
  `avg_rating_age` double DEFAULT NULL,
  `league_totals_ab` int(11) DEFAULT NULL,
  `league_totals_h` int(11) DEFAULT NULL,
  `league_totals_d` int(11) DEFAULT NULL,
  `league_totals_t` int(11) DEFAULT NULL,
  `league_totals_hr` int(11) DEFAULT NULL,
  `league_totals_bb` int(11) DEFAULT NULL,
  `league_totals_hp` int(11) DEFAULT NULL,
  `league_totals_k` int(11) DEFAULT NULL,
  `league_totals_pa` int(11) DEFAULT NULL,
  `league_totals_babip` double DEFAULT NULL,
  `league_totals_mod_h` double DEFAULT NULL,
  `league_totals_mod_d` double DEFAULT NULL,
  `league_totals_mod_t` double DEFAULT NULL,
  `league_totals_mod_hr` double DEFAULT NULL,
  `league_totals_mod_bb` double DEFAULT NULL,
  `league_totals_mod_hp` double DEFAULT NULL,
  `league_totals_mod_k` double DEFAULT NULL,
  `league_totals_mod_babip` double DEFAULT NULL,
  `ml_equivalencies_avg` double DEFAULT NULL,
  `ml_equivalencies_hr` double DEFAULT NULL,
  `ml_equivalencies_eb` double DEFAULT NULL,
  `ml_equivalencies_bb` double DEFAULT NULL,
  `ml_equivalencies_k` double DEFAULT NULL,
  `ml_equivalencies_hp` double DEFAULT NULL,
  `player_creation_modifier_contact` double DEFAULT NULL,
  `player_creation_modifier_gap` double DEFAULT NULL,
  `player_creation_modifier_power` double DEFAULT NULL,
  `player_creation_modifier_eye` double DEFAULT NULL,
  `player_creation_modifier_strikeouts` double DEFAULT NULL,
  `player_creation_modifier_stuff` double DEFAULT NULL,
  `player_creation_modifier_movement` double DEFAULT NULL,
  `player_creation_modifier_control` double DEFAULT NULL,
  `player_creation_modifier_speed` double DEFAULT NULL,
  `player_creation_modifier_fielding` double DEFAULT NULL,
  `financial_coefficient` double DEFAULT NULL,
  `world_start_year` int(11) DEFAULT NULL,
  `current_date` date DEFAULT NULL,
  `background_color_id` varchar(8) DEFAULT NULL,
  `text_color_id` varchar(8) DEFAULT NULL,
  `scouting_coach_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`league_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sub_leagues` (
  `league_id` int(11) NOT NULL,
  `sub_league_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `abbr` varchar(50) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `designated_hitter` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`league_id`,`sub_league_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `divisions` (
  `league_id` int(11) NOT NULL,
  `sub_league_id` int(11) NOT NULL,
  `division_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  PRIMARY KEY (`league_id`,`sub_league_id`,`division_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `league_history` (
  `league_id` int(11) NOT NULL,
  `sub_league_id` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `best_hitter_id` int(11) DEFAULT NULL,
  `best_pitcher_id` int(11) DEFAULT NULL,
  `best_rookie_id` int(11) DEFAULT NULL,
  `best_manager_id` int(11) DEFAULT NULL,
  `best_fielder_id0` int(11) DEFAULT NULL,
  `best_fielder_id1` int(11) DEFAULT NULL,
  `best_fielder_id2` int(11) DEFAULT NULL,
  `best_fielder_id3` int(11) DEFAULT NULL,
  `best_fielder_id4` int(11) DEFAULT NULL,
  `best_fielder_id5` int(11) DEFAULT NULL,
  `best_fielder_id6` int(11) DEFAULT NULL,
  `best_fielder_id7` int(11) DEFAULT NULL,
  `best_fielder_id8` int(11) DEFAULT NULL,
  `best_fielder_id9` int(11) DEFAULT NULL,
  PRIMARY KEY (`league_id`,`sub_league_id`,`year`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `league_history_batting_stats` (
  `lhbs_id` int(11) NOT NULL AUTO_INCREMENT,    
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `game_id` int(11) DEFAULT NULL,
  `league_id` int(11) NOT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) DEFAULT NULL,
  `pa` int(11) DEFAULT NULL,
  `ab` int(11) DEFAULT NULL,
  `h` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `tb` int(11) DEFAULT NULL,
  `s` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `t` int(11) DEFAULT NULL,
  `hr` int(11) DEFAULT NULL,
  `sb` int(11) DEFAULT NULL,
  `cs` int(11) DEFAULT NULL,
  `rbi` int(11) DEFAULT NULL,
  `r` int(11) DEFAULT NULL,
  `bb` int(11) DEFAULT NULL,
  `ibb` int(11) DEFAULT NULL,
  `hp` int(11) DEFAULT NULL,
  `sh` int(11) DEFAULT NULL,
  `sf` int(11) DEFAULT NULL,
  `ci` int(11) DEFAULT NULL,
  `gdp` int(11) DEFAULT NULL,
  `g` int(11) DEFAULT NULL,
  `gs` int(11) DEFAULT NULL,
  `ebh` int(11) DEFAULT NULL,
  `pitches_seen` int(11) DEFAULT NULL,
  `avg` double DEFAULT NULL,
  `obp` double DEFAULT NULL,
  `slg` double DEFAULT NULL,
  `rc` double DEFAULT NULL,
  `rc27` double DEFAULT NULL,
  `iso` double DEFAULT NULL,
  `woba` double DEFAULT NULL,
  `ops` double DEFAULT NULL,
  `sbp` double DEFAULT NULL,
  `ws` double DEFAULT NULL,
  `kp` double DEFAULT NULL,
  `bbp` double DEFAULT NULL,
  `wpa` double DEFAULT NULL,
  `babip` double DEFAULT NULL,
  PRIMARY KEY (`lhbs_id`),
  INDEX `lhbs_ix1` (`year`),
  INDEX `lhbs_ix2` (`league_id`),
  INDEX `lhbs_ix3` (`split_id`)    
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `league_history_fielding_stats` (
  `lhfs_id` int(11) NOT NULL AUTO_INCREMENT,    
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `league_id` int(11) NOT NULL,
  `sub_league_id` int(11) DEFAULT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) NOT NULL,
  `position` smallint(6) DEFAULT NULL,
  `g` int(11) DEFAULT NULL,
  `gs` int(11) DEFAULT NULL,
  `tc` int(11) DEFAULT NULL,
  `a` int(11) DEFAULT NULL,
  `po` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  `dp` int(11) DEFAULT NULL,
  `tp` int(11) DEFAULT NULL,
  `pb` int(11) DEFAULT NULL,
  `sba` int(11) DEFAULT NULL,
  `rto` int(11) DEFAULT NULL,
  `er` int(11) DEFAULT NULL,
  `ip` int(11) DEFAULT NULL,
  `ipf` int(11) DEFAULT NULL,
  `pct` double DEFAULT NULL,
  `range` double DEFAULT NULL,
  `rtop` double DEFAULT NULL,
  `cera` double DEFAULT NULL,
  `zr` double DEFAULT NULL,
  `plays` int(11) DEFAULT NULL,
  `plays_base` int(11) DEFAULT NULL,
  `roe` int(11) DEFAULT NULL,
  `eff` int(11) DEFAULT NULL,
  PRIMARY KEY (`lhfs_id`),
  INDEX `lhfs_ix1` (`year`),
  INDEX `lhfs_ix2` (`league_id`),
  INDEX `lhfs_ix3` (`split_id`) 
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `league_history_pitching_stats` (
  `lhps_id` int(11)  NOT NULL AUTO_INCREMENT,    
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `game_id` int(11) DEFAULT NULL,
  `league_id` int(11) NOT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) DEFAULT NULL,
  `ab` int(11) DEFAULT NULL,
  `ip` int(11) DEFAULT NULL,
  `bf` int(11) DEFAULT NULL,
  `tb` int(11) DEFAULT NULL,
  `ha` int(11) DEFAULT NULL,
  `k` int(11) DEFAULT NULL,
  `rs` int(11) DEFAULT NULL,
  `bb` int(11) DEFAULT NULL,
  `r` int(11) DEFAULT NULL,
  `er` int(11) DEFAULT NULL,
  `gb` int(11) DEFAULT NULL,
  `fb` int(11) DEFAULT NULL,
  `pi` int(11) DEFAULT NULL,
  `ipf` int(11) DEFAULT NULL,
  `g` int(11) DEFAULT NULL,
  `gs` int(11) DEFAULT NULL,
  `w` int(11) DEFAULT NULL,
  `l` int(11) DEFAULT NULL,
  `s` int(11) DEFAULT NULL,
  `sa` int(11) DEFAULT NULL,
  `da` int(11) DEFAULT NULL,
  `sh` int(11) DEFAULT NULL,
  `sf` int(11) DEFAULT NULL,
  `ta` int(11) DEFAULT NULL,
  `hra` int(11) DEFAULT NULL,
  `bk` int(11) DEFAULT NULL,
  `ci` int(11) DEFAULT NULL,
  `iw` int(11) DEFAULT NULL,
  `wp` int(11) DEFAULT NULL,
  `hp` int(11) DEFAULT NULL,
  `gf` int(11) DEFAULT NULL,
  `dp` int(11) DEFAULT NULL,
  `qs` int(11) DEFAULT NULL,
  `svo` int(11) DEFAULT NULL,
  `bs` int(11) DEFAULT NULL,
  `ra` int(11) DEFAULT NULL,
  `ir` int(11) DEFAULT NULL,
  `irs` int(11) DEFAULT NULL,
  `cg` int(11) DEFAULT NULL,
  `sho` int(11) DEFAULT NULL,
  `sb` int(11) DEFAULT NULL,
  `cs` int(11) DEFAULT NULL,
  `hld` int(11) DEFAULT NULL,
  `r9` double DEFAULT NULL,
  `avg` double DEFAULT NULL,
  `obp` double DEFAULT NULL,
  `slg` double DEFAULT NULL,
  `ops` double DEFAULT NULL,
  `h9` double DEFAULT NULL,
  `k9` double DEFAULT NULL,
  `hr9` double DEFAULT NULL,
  `bb9` double DEFAULT NULL,
  `cgp` double DEFAULT NULL,
  `fip` double DEFAULT NULL,
  `qsp` double DEFAULT NULL,
  `winp` double DEFAULT NULL,
  `rsg` double DEFAULT NULL,
  `svp` double DEFAULT NULL,
  `bsvp` double DEFAULT NULL,
  `irsp` double DEFAULT NULL,
  `gfp` double DEFAULT NULL,
  `era` double DEFAULT NULL,
  `pig` double DEFAULT NULL,
  `ws` double DEFAULT NULL,
  `whip` double DEFAULT NULL,
  `gbfbp` double DEFAULT NULL,
  `kbb` double DEFAULT NULL,
  `babip` double DEFAULT NULL,
  `wpa` double DEFAULT NULL,
  PRIMARY KEY (`lhps_id`),
  INDEX `lhps_ix1` (`year`),
  INDEX `lhps_ix2` (`league_id`),
  INDEX `lhps_ix3` (`split_id`)     
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `parks` (
  `park_id` int(11) NOT NULL,
  `dimensions_x` smallint(6) DEFAULT NULL,
  `dimensions_y` smallint(6) DEFAULT NULL,
  `batter_left_x` smallint(6) DEFAULT NULL,
  `batter_left_y` smallint(6) DEFAULT NULL,
  `batter_right_x` smallint(6) DEFAULT NULL,
  `batter_right_y` smallint(6) DEFAULT NULL,
  `bases_x0` smallint(6) DEFAULT NULL,
  `bases_x1` smallint(6) DEFAULT NULL,
  `bases_x2` smallint(6) DEFAULT NULL,
  `bases_y0` smallint(6) DEFAULT NULL,
  `bases_y1` smallint(6) DEFAULT NULL,
  `bases_y2` smallint(6) DEFAULT NULL,
  `positions_x0` smallint(6) DEFAULT NULL,
  `positions_x1` smallint(6) DEFAULT NULL,
  `positions_x2` smallint(6) DEFAULT NULL,
  `positions_x3` smallint(6) DEFAULT NULL,
  `positions_x4` smallint(6) DEFAULT NULL,
  `positions_x5` smallint(6) DEFAULT NULL,
  `positions_x6` smallint(6) DEFAULT NULL,
  `positions_x7` smallint(6) DEFAULT NULL,
  `positions_x8` smallint(6) DEFAULT NULL,
  `positions_x9` smallint(6) DEFAULT NULL,
  `positions_y0` smallint(6) DEFAULT NULL,
  `positions_y1` smallint(6) DEFAULT NULL,
  `positions_y2` smallint(6) DEFAULT NULL,
  `positions_y3` smallint(6) DEFAULT NULL,
  `positions_y4` smallint(6) DEFAULT NULL,
  `positions_y5` smallint(6) DEFAULT NULL,
  `positions_y6` smallint(6) DEFAULT NULL,
  `positions_y7` smallint(6) DEFAULT NULL,
  `positions_y8` smallint(6) DEFAULT NULL,
  `positions_y9` smallint(6) DEFAULT NULL,
  `avg` double DEFAULT NULL,
  `avg_l` double DEFAULT NULL,
  `avg_r` double DEFAULT NULL,
  `d` double DEFAULT NULL,
  `t` double DEFAULT NULL,
  `hr` double DEFAULT NULL,
  `hr_r` double DEFAULT NULL,
  `hr_l` double DEFAULT NULL,
  `temperature0` smallint(6) DEFAULT NULL,
  `temperature1` smallint(6) DEFAULT NULL,
  `temperature2` smallint(6) DEFAULT NULL,
  `temperature3` smallint(6) DEFAULT NULL,
  `temperature4` smallint(6) DEFAULT NULL,
  `temperature5` smallint(6) DEFAULT NULL,
  `temperature6` smallint(6) DEFAULT NULL,
  `temperature7` smallint(6) DEFAULT NULL,
  `temperature8` smallint(6) DEFAULT NULL,
  `temperature9` smallint(6) DEFAULT NULL,
  `temperature10` smallint(6) DEFAULT NULL,
  `temperature11` smallint(6) DEFAULT NULL,
  `rain0` smallint(6) DEFAULT NULL,
  `rain1` smallint(6) DEFAULT NULL,
  `rain2` smallint(6) DEFAULT NULL,
  `rain3` smallint(6) DEFAULT NULL,
  `rain4` smallint(6) DEFAULT NULL,
  `rain5` smallint(6) DEFAULT NULL,
  `rain6` smallint(6) DEFAULT NULL,
  `rain7` smallint(6) DEFAULT NULL,
  `rain8` smallint(6) DEFAULT NULL,
  `rain9` smallint(6) DEFAULT NULL,
  `rain10` smallint(6) DEFAULT NULL,
  `rain11` smallint(6) DEFAULT NULL,
  `wind` smallint(6) DEFAULT NULL,
  `wind_direction` smallint(6) DEFAULT NULL,
  `distances0` smallint(6) DEFAULT NULL,
  `distances1` smallint(6) DEFAULT NULL,
  `distances2` smallint(6) DEFAULT NULL,
  `distances3` smallint(6) DEFAULT NULL,
  `distances4` smallint(6) DEFAULT NULL,
  `distances5` smallint(6) DEFAULT NULL,
  `distances6` smallint(6) DEFAULT NULL,
  `wall_heights0` smallint(6) DEFAULT NULL,
  `wall_heights1` smallint(6) DEFAULT NULL,
  `wall_heights2` smallint(6) DEFAULT NULL,
  `wall_heights3` smallint(6) DEFAULT NULL,
  `wall_heights4` smallint(6) DEFAULT NULL,
  `wall_heights5` smallint(6) DEFAULT NULL,
  `wall_heights6` smallint(6) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `picture` varchar(200) DEFAULT NULL,
  `picture_night` varchar(200) DEFAULT NULL,
  `nation_id` int(11) DEFAULT NULL,
  `capacity` int(11) DEFAULT NULL,
  `type` smallint(6) DEFAULT NULL,
  `foul_ground` smallint(6) DEFAULT NULL,
  `turf` tinyint(4) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `relative_path_3d_model` varchar(200) DEFAULT NULL,
  `file_name_3d_model` varchar(200) DEFAULT NULL,
  `home_team_dugout_is_at_first_base` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`park_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `teams` (
  `team_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `abbr` varchar(50) DEFAULT NULL,
  `nickname` varchar(50) DEFAULT NULL,
  `logo_file_name` varchar(200) DEFAULT NULL,
  `city_id` int(11) DEFAULT NULL,
  `park_id` int(11) DEFAULT NULL,
  `league_id` int(11) DEFAULT NULL,
  `sub_league_id` int(11) DEFAULT NULL,
  `division_id` int(11) DEFAULT NULL,
  `nation_id` int(11) DEFAULT NULL,
  `parent_team_id` int(11) DEFAULT NULL,
  `level` int(11) DEFAULT NULL,
  `prevent_any_moves` tinyint(4) DEFAULT NULL,
  `human_team` tinyint(4) DEFAULT NULL,
  `human_id` int(11) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `background_color_id` varchar(8) DEFAULT NULL,
  `text_color_id` varchar(8) DEFAULT NULL,
  `ballcaps_main_color_id` varchar(8) DEFAULT NULL,
  `ballcaps_visor_color_id` varchar(8) DEFAULT NULL,
  `jersey_main_color_id` varchar(8) DEFAULT NULL,
  `jersey_away_color_id` varchar(8) DEFAULT NULL,
  `jersey_secondary_color_id` varchar(8) DEFAULT NULL,
  `jersey_pin_stripes_color_id` varchar(8) DEFAULT NULL,
  `allstar_team` tinyint(4) DEFAULT NULL,
  `historical_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`team_id`),
  INDEX `teams_ix1` (`park_id`),
  INDEX `teams_ix2` (`league_id`),
  INDEX `teams_ix3` (`sub_league_id`),
  INDEX `teams_ix4` (`division_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#team_id and league_id in the `players` table looks like they will only be used in determining the player's team and league at the time of the data dump, that is, their current.    
CREATE TABLE `players` (
  `player_id` int(11) NOT NULL,
  `team_id` int(11) DEFAULT NULL,
  `league_id` int(11) DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `nick_name` varchar(50) DEFAULT NULL,
  `age` smallint(6) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `city_of_birth_id` int(11) DEFAULT NULL,
  `nation_id` int(11) DEFAULT NULL,
  `second_nation_id` int(11) DEFAULT NULL,
  `weight` smallint(6) DEFAULT NULL,
  `height` smallint(6) DEFAULT NULL,
  `retired` tinyint(4) DEFAULT NULL,
  `free_agent` tinyint(4) DEFAULT NULL,
  `last_league_id` int(11) DEFAULT NULL,
  `last_team_id` int(11) DEFAULT NULL,
  `organization_id` int(11) DEFAULT NULL,
  `last_organization_id` int(11) DEFAULT NULL,
  `language_ids0` int(11) DEFAULT NULL,
  `language_ids1` int(11) DEFAULT NULL,
  `uniform_number` smallint(6) DEFAULT NULL,
  `experience` smallint(6) DEFAULT NULL,
  `person_type` smallint(6) DEFAULT NULL,
  `bats` smallint(6) DEFAULT NULL,
  `throws` smallint(6) DEFAULT NULL,
  `personality_greed` smallint(6) DEFAULT NULL,
  `personality_loyalty` smallint(6) DEFAULT NULL,
  `personality_play_for_winner` smallint(6) DEFAULT NULL,
  `personality_work_ethic` smallint(6) DEFAULT NULL,
  `personality_intelligence` smallint(6) DEFAULT NULL,
  `personality_leader` smallint(6) DEFAULT NULL,
  `historical_id` varchar(50) DEFAULT NULL,
  `historical_team_id` varchar(50) DEFAULT NULL,
  `best_contract_offer_id` int(11) DEFAULT NULL,
  `injury_is_injured` tinyint(4) DEFAULT NULL,
  `injury_dtd_injury` tinyint(4) DEFAULT NULL,
  `injury_career_ending` tinyint(4) DEFAULT NULL,
  `injury_dl_left` smallint(6) DEFAULT NULL,
  `injury_dl_playoff_round` smallint(6) DEFAULT NULL,
  `injury_left` smallint(6) DEFAULT NULL,
  `dtd_injury_effect` smallint(6) DEFAULT NULL,
  `injury_id` int(11) DEFAULT NULL,
  `prone_overall` smallint(6) DEFAULT NULL,
  `prone_leg` smallint(6) DEFAULT NULL,
  `prone_back` smallint(6) DEFAULT NULL,
  `prone_arm` smallint(6) DEFAULT NULL,
  `fatigue_pitches0` smallint(6) DEFAULT NULL,
  `fatigue_pitches1` smallint(6) DEFAULT NULL,
  `fatigue_pitches2` smallint(6) DEFAULT NULL,
  `fatigue_pitches3` smallint(6) DEFAULT NULL,
  `fatigue_pitches4` smallint(6) DEFAULT NULL,
  `fatigue_pitches5` smallint(6) DEFAULT NULL,
  `fatigue_points` smallint(6) DEFAULT NULL,
  `fatigue_played_today` tinyint(4) DEFAULT NULL,
  `running_ratings_speed` smallint(6) DEFAULT NULL,
  `running_ratings_stealing` smallint(6) DEFAULT NULL,
  `running_ratings_baserunning` smallint(6) DEFAULT NULL,
  `position` smallint(6) DEFAULT NULL,
  `role` smallint(6) DEFAULT NULL,
  `college` tinyint(4) DEFAULT NULL,
  `draft_year` smallint(6) DEFAULT NULL,
  `draft_round` smallint(6) DEFAULT NULL,
  `draft_supplemental` tinyint(4) DEFAULT NULL,
  `draft_pick` smallint(6) DEFAULT NULL,
  `draft_overall_pick` smallint(6) DEFAULT NULL,
  `draft_eligible` tinyint(4) DEFAULT NULL,
  `hidden` tinyint(4) DEFAULT NULL,
  `draft_league_id` int(11) DEFAULT NULL,
  `draft_team_id` int(11) DEFAULT NULL,
  `turned_coach` tinyint(4) DEFAULT NULL,
  `hall_of_fame` tinyint(4) DEFAULT NULL,
  `rust` smallint(6) DEFAULT NULL,
  `inducted` smallint(6) DEFAULT NULL,
  `strategy_override_team` tinyint(4) DEFAULT NULL,
  `strategy_stealing` int(11) DEFAULT NULL,
  `strategy_running` int(11) DEFAULT NULL,
  `strategy_bunt_for_hit` int(11) DEFAULT NULL,
  `strategy_sac_bunt` int(11) DEFAULT NULL,
  `strategy_hit_run` int(11) DEFAULT NULL,
  `strategy_hook_start` int(11) DEFAULT NULL,
  `strategy_hook_relief` int(11) DEFAULT NULL,
  `strategy_pitch_count` int(11) DEFAULT NULL,
  `strategy_only_allow_single_inning_saves` tinyint(4) DEFAULT NULL,
  `strategy_pitch_around` int(11) DEFAULT NULL,
  `strategy_sticky_lineup` tinyint(4) DEFAULT NULL,
  `strategy_sticky_position` tinyint(4) DEFAULT NULL,
  `strategy_no_pinch_if_rested` tinyint(4) DEFAULT NULL,
  `strategy_never_pinch_hit` tinyint(4) DEFAULT NULL,
  `strategy_defensive_sub` tinyint(4) DEFAULT NULL,
  `strategy_never_defensive_sub_for` tinyint(4) DEFAULT NULL,
  `local_pop` smallint(6) DEFAULT NULL,
  `national_pop` smallint(6) DEFAULT NULL,
  `draft_protected` tinyint(4) DEFAULT NULL,
  `morale` smallint(6) DEFAULT NULL,
  `morale_player_performance` smallint(6) DEFAULT NULL,
  `morale_team_performance` smallint(6) DEFAULT NULL,
  `morale_team_transactions` smallint(6) DEFAULT NULL,
  `expectation` smallint(6) DEFAULT NULL,
  `morale_player_role` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`player_id`),
  INDEX `players_ix1` (`team_id`),
  INDEX `players_ix2` (`league_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE `players_career_batting_stats` (
  `pcbs_id` int(11) NOT NULL AUTO_INCREMENT,
  `player_id` int(11) NOT NULL,
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `game_id` int(11) DEFAULT NULL,
  `league_id` int(11) DEFAULT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) NOT NULL,
  `position` smallint(6) DEFAULT NULL,
  `ab` smallint(6) DEFAULT NULL,
  `h` smallint(6) DEFAULT NULL,
  `k` smallint(6) DEFAULT NULL,
  `pa` smallint(6) DEFAULT NULL,
  `pitches_seen` smallint(6) DEFAULT NULL,
  `g` smallint(6) DEFAULT NULL,
  `gs` smallint(6) DEFAULT NULL,
  `d` smallint(6) DEFAULT NULL,
  `t` smallint(6) DEFAULT NULL,
  `hr` smallint(6) DEFAULT NULL,
  `r` smallint(6) DEFAULT NULL,
  `rbi` smallint(6) DEFAULT NULL,
  `sb` smallint(6) DEFAULT NULL,
  `cs` smallint(6) DEFAULT NULL,
  `bb` smallint(6) DEFAULT NULL,
  `ibb` smallint(6) DEFAULT NULL,
  `gdp` smallint(6) DEFAULT NULL,
  `sh` smallint(6) DEFAULT NULL,
  `sf` smallint(6) DEFAULT NULL,
  `hp` smallint(6) DEFAULT NULL,
  `ci` smallint(6) DEFAULT NULL,
  `wpa` double DEFAULT NULL,
  `stint` smallint(6) NOT NULL,
  `war` double DEFAULT NULL,
  PRIMARY KEY (`pcbs_id`),
  INDEX `pcbs_ix1` (`league_id`),
  INDEX `pcbs_ix2` (year),
  INDEX `pcbs_ix3` (`player_id`),
  INDEX `pcbs_ix4` (`team_id`),
  INDEX `pcbs_ix5` (`split_id`)    
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `players_career_fielding_stats` (
  `pcfs_id` int(11) NOT NULL Auto_Increment,    
  `player_id` int(11) NOT NULL,
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `league_id` int(11) DEFAULT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) DEFAULT NULL,
  `position` smallint(6) NOT NULL,
  `tc` smallint(6) DEFAULT NULL,
  `a` smallint(6) DEFAULT NULL,
  `po` smallint(6) DEFAULT NULL,
  `er` smallint(6) DEFAULT NULL,
  `ip` smallint(6) DEFAULT NULL,
  `g` smallint(6) DEFAULT NULL,
  `gs` smallint(6) DEFAULT NULL,
  `e` smallint(6) DEFAULT NULL,
  `dp` smallint(6) DEFAULT NULL,
  `tp` smallint(6) DEFAULT NULL,
  `pb` smallint(6) DEFAULT NULL,
  `sba` smallint(6) DEFAULT NULL,
  `rto` smallint(6) DEFAULT NULL,
  `ipf` smallint(6) DEFAULT NULL,
  `plays` smallint(6) DEFAULT NULL,
  `plays_base` smallint(6) DEFAULT NULL,
  `roe` smallint(6) DEFAULT NULL,
  `zr` double DEFAULT NULL,
  PRIMARY KEY (`pcfs_id`),
  INDEX `pcfs_ix1` (`league_id`),
  INDEX `pcfs_ix2` (year),
  INDEX `pcfs_ix3` (`player_id`),
  INDEX `pcfs_ix4` (`team_id`),
  INDEX `pcfs_ix5` (`split_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `players_career_pitching_stats` (
  `pcps_id` int(11) NOT NULL AUTO_INCREMENT,    
  `player_id` int(11) NOT NULL,
  `year` smallint(6) NOT NULL,
  `team_id` int(11) NOT NULL,
  `game_id` int(11) DEFAULT NULL,
  `league_id` int(11) DEFAULT NULL,
  `level_id` smallint(6) DEFAULT NULL,
  `split_id` smallint(6) NOT NULL,
  `ip` smallint(6) DEFAULT NULL,
  `ab` smallint(6) DEFAULT NULL,
  `tb` smallint(6) DEFAULT NULL,
  `ha` smallint(6) DEFAULT NULL,
  `k` smallint(6) DEFAULT NULL,
  `bf` smallint(6) DEFAULT NULL,
  `rs` smallint(6) DEFAULT NULL,
  `bb` smallint(6) DEFAULT NULL,
  `r` smallint(6) DEFAULT NULL,
  `er` smallint(6) DEFAULT NULL,
  `gb` smallint(6) DEFAULT NULL,
  `fb` smallint(6) DEFAULT NULL,
  `pi` smallint(6) DEFAULT NULL,
  `ipf` smallint(6) DEFAULT NULL,
  `g` smallint(6) DEFAULT NULL,
  `gs` smallint(6) DEFAULT NULL,
  `w` smallint(6) DEFAULT NULL,
  `l` smallint(6) DEFAULT NULL,
  `s` smallint(6) DEFAULT NULL,
  `sa` smallint(6) DEFAULT NULL,
  `da` smallint(6) DEFAULT NULL,
  `sh` smallint(6) DEFAULT NULL,
  `sf` smallint(6) DEFAULT NULL,
  `ta` smallint(6) DEFAULT NULL,
  `hra` smallint(6) DEFAULT NULL,
  `bk` smallint(6) DEFAULT NULL,
  `ci` smallint(6) DEFAULT NULL,
  `iw` smallint(6) DEFAULT NULL,
  `wp` smallint(6) DEFAULT NULL,
  `hp` smallint(6) DEFAULT NULL,
  `gf` smallint(6) DEFAULT NULL,
  `dp` smallint(6) DEFAULT NULL,
  `qs` smallint(6) DEFAULT NULL,
  `svo` smallint(6) DEFAULT NULL,
  `bs` smallint(6) DEFAULT NULL,
  `ra` smallint(6) DEFAULT NULL,
  `cg` smallint(6) DEFAULT NULL,
  `sho` smallint(6) DEFAULT NULL,
  `sb` smallint(6) DEFAULT NULL,
  `cs` smallint(6) DEFAULT NULL,
  `hld` smallint(6) DEFAULT NULL,
  `ir` double DEFAULT NULL,
  `irs` double DEFAULT NULL,
  `wpa` double DEFAULT NULL,
  `li` double DEFAULT NULL,
  `stint` smallint(6) NOT NULL,
  `outs` smallint(6) DEFAULT NULL,
  `war` double DEFAULT NULL,
  PRIMARY KEY (`pcps_id`),
  INDEX `pcps_ix1` (`league_id`),
  INDEX `pcps_ix2` (year),
  INDEX `pcps_ix3` (`player_id`),
  INDEX `pcps_ix4` (`team_id`),
  INDEX `pcps_ix5` (`split_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `players_individual_batting_stats` (
  `player_id` int(11) NOT NULL, #Batter
  `opponent_id` int(11) NOT NULL, #Pitcher
  `ab` smallint(6) DEFAULT NULL,
  `h` smallint(6) DEFAULT NULL,
  `hr` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`player_id`,`opponent_id`),
  INDEX `pibs_ix1` (`opponent_id`)    
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `states` (
  `state_id` int(11) NOT NULL,
  `nation_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `abbreviation` varchar(50) DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `main_language_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`state_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `team_affiliations` (
  `team_id` int(11) NOT NULL,
  `affiliated_team_id` int(11) NOT NULL,
  PRIMARY KEY (`team_id`,`affiliated_team_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `team_history_financials` (
  `team_id` int(11) NOT NULL,
  `year` smallint(6) NOT NULL,
  `league_id` int(11) DEFAULT NULL,
  `sub_league_id` int(11) DEFAULT NULL,
  `division_id` int(11) DEFAULT NULL,
  `gate_revenue` int(11) DEFAULT NULL,
  `season_ticket_revenue` int(11) DEFAULT NULL,
  `media_revenue` int(11) DEFAULT NULL,
  `merchandising_revenue` int(11) DEFAULT NULL,
  `other_revenue` int(11) DEFAULT NULL,
  `revenue_sharing` int(11) DEFAULT NULL,
  `playoff_revenue` int(11) DEFAULT NULL,
  `cash` int(11) DEFAULT NULL,
  `cash_owner` int(11) DEFAULT NULL,
  `cash_trades` int(11) DEFAULT NULL,
  `previous_balance` int(11) DEFAULT NULL,
  `player_expenses` int(11) DEFAULT NULL,
  `staff_expenses` int(11) DEFAULT NULL,
  `stadium_expenses` int(11) DEFAULT NULL,
  `season_tickets` int(11) DEFAULT NULL,
  `attendance` int(11) DEFAULT NULL,
  `fan_interest` smallint(6) DEFAULT NULL,
  `fan_loyalty` smallint(6) DEFAULT NULL,
  `fan_modifier` smallint(6) DEFAULT NULL,
  `ticket_price` double DEFAULT NULL,
  `local_media_contract` int(11) DEFAULT NULL,
  `local_media_contract_expires` int(11) DEFAULT NULL,
  `national_media_contract` int(11) DEFAULT NULL,
  `national_media_contract_expires` int(11) DEFAULT NULL,
  `development_budget` int(11) DEFAULT NULL,
  `draft_budget` int(11) DEFAULT NULL,
  `draft_expenses` int(11) DEFAULT NULL,
  `budget` int(11) DEFAULT NULL,
  `market` smallint(6) DEFAULT NULL,
  `owner_expectation` smallint(6) DEFAULT NULL,
  `scouting_budget` int(11) DEFAULT NULL,
  `scouting_amateur` int(11) DEFAULT NULL,
  `scouting_major` int(11) DEFAULT NULL,
  `scouting_minor` int(11) DEFAULT NULL,
  `scouting_international` int(11) DEFAULT NULL,
  PRIMARY KEY (`team_id`,`year`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `team_relations` (
  `league_id` int(11) NOT NULL,
  `sub_league_id` int(11) NOT NULL,
  `division_id` int(11) NOT NULL,
  `team_id` int(11) NOT NULL,
  PRIMARY KEY (`league_id`,`sub_league_id`,`division_id`,`team_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# All tables from OOTP data are created above.
# Below are views I'll be using as the foundation for advanced stats.
# Starting with Tango's views to derive linear weights and wOBA

CREATE OR REPLACE VIEW vLeagueRunsPerOut AS
SELECT p.year
, p.league_id
, SUM(p.r)/sum(p.outs) AS "RperOut"
, sum(p.r) AS "totR"
, sum(p.outs) AS "totOuts"
, sum(p.outs)+sum(p.ha)+sum(p.bb)+ sum(p.iw)+ sum(p.sh)+ sum(p.sf) AS "totPA"
, round(sum(p.r)/(sum(p.outs)+sum(p.ha)+sum(p.bb)+ sum(p.iw)+ sum(p.sh)+ sum(p.sf)),8) AS "RperPA"
FROM players_career_pitching_stats AS p
GROUP BY p.year, p.league_id;


CREATE OR REPLACE VIEW vRunValues AS
SELECT l.year
, l.league_id
, l.rperout
, l.rperout+0.14 AS runBB
, l.rperout+0.14+0.025 AS runHB
, l.rperout+0.14+0.155 AS run1b
, l.rperout+0.14+0.155+0.3 AS run2b
, l.rperout+0.14+0.155+0.3+0.27 AS run3b
, 1.4 AS runHR
, 0.2 AS runSB
, (2*l.RperOut)+0.075 AS runCS
FROM vLeagueRunsPerOut AS l;


CREATE OR REPLACE VIEW vRunValues2 AS
SELECT 
r.year
, r.league_id
, r.RperOut 
, r.runBB 
, r.runHB 
, r.run1B 
, r.run2B 
, r.run3B 
, r.runHR 
, r.runSB 
, r.runCS 
, Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF) AS runMinus 
, Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS) / Sum(b.BB - b.IBB + b.HP +b.H) AS runPlus 
#, lhb.woba AS lgwOBA 

, #1/([runPlus]+[runMinus]) AS wOBAscale 
1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF))) AS wOBAscale

, #([runBB]+[runMinus])*[wOBAscale] AS wobaBB 
(r.runBB + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF))) * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS wobaBB

, #([runHB]+[runMinus])*[wOBAscale] AS wobaHB 
(r.runHB +(Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF))) * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS wobaHB

, #([run1B]+[runMinus])*[wOBAscale] AS woba1B 
(r.run1b + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF))) * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS woba1B

, #([run2B]+[runMinus])*[wOBAscale] AS woba2B 
(r.run2b + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF))) * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS woba2B

, #([run3B]+[runMinus])*[wOBAscale] AS woba3B 
(r.run3B + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF))) * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS woba3B

, #([runHR]+[runMinus])*[wOBAscale] AS wobaHR 
(r.runHR + (Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF))) * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS wobaHR

, #[runSB]*[wOBAscale] AS wobaSB 
r.runSB * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS wobaSB

, #[runCS]*[wOBAscale] AS wobaCS
r.runCS * (1/ ((Sum(r.runBB * (b.BB - b.ibb) + r.runHB * b.HP +r.run1B * (b.H -b.d -b.t -b.hr) + r.run2B *b.d + r.run3B * b.t + 1.4 *b.hr + r.runSB * b.SB - r.runCS * b.CS)) / (Sum(b.BB - b.IBB + b.HP +b.H) + Sum(r.runBB* (b.bb - b.ibb) + r.runHB * b.HP + r.run1B * (b.H -b.d - b.t -b.hr) + r.run2B * b.d + r.run3B * b.t + 1.4*b.HR + r.runSB * b.SB - r.runCS *b.CS) / Sum(b.ab -b.h + b.SF)))) AS wobaCS

FROM 
vRunValues r
INNER JOIN players_career_batting_stats b ON r.year=b.year AND r.league_id=b.league_id
INNER JOIN league_history_batting_stats lhb ON r.year=lhb.year AND r.league_id=lhb.league_id

GROUP BY 
r.year
, r.league_id
, r.RperOut 
, r.runBB 
, r.runHB 
, r.run1B 
, r.run2B 
, r.run3B 
, r.runHR 
, r.runSB 
, r.runCS
, lhb.woba

ORDER BY 
r.year DESC; 

#View to add League wRC to league_history_batting_stats so that I can derive wRC+ in the next view
#While the league history batting stats table is nice, it can't be copied without summing and grouping by league_id, as the data is stratified by team_id and game_id.  Until I figure out how to use those columns to identify a subleague, I will have to sum all columns.
CREATE OR REPLACE VIEW vLeagueHistoryBattingStatsPlus AS
SELECT 
b.year
, b.league_id
, b.split_id
, sum(b.pa) as lgPA
, sum(b.ab) as lgAB
, sum(b.h) as lgH
, sum(b.k) as lgK
, sum(b.tb) as lgTB
, sum(b.s) as lgS
, sum(b.d) as lgD
, sum(b.t) as lgT
, sum(b.hr) as lgHR
, sum(b.sb) as lgSB
, sum(b.cs) as lgCS
, sum(b.rbi) as lgRBI
, sum(b.r) as lgR
, sum(b.bb) as lgBB
, sum(b.ibb) as lgIBB
, sum(b.hp) as lgHP
, sum(b.sh) as lgSH
, sum(b.sf) as lgSF
, sum(b.ci) as lgCI
, sum(b.gdp) as lgGDP
, sum(b.g) as lgG
, sum(b.gs) as lgGS
, sum(b.ebh) as lgEBH
, sum(b.pitches_seen) as lgPitches_Seen
, sum(b.avg) as lgAVG
, sum(b.obp) as lgOBP
, sum(b.slg) as lgSLG
, sum(b.rc) as lgRC
, round((r.wobaBB*sum(b.bb) + r.wobahb*sum(b.hp) + r.woba1b*sum(b.s) + r.woba2b*sum(b.d) + r.woba3b*sum(b.t) + r.wobahr*sum(b.hr))/(sum(b.ab)+sum(b.bb) - sum(b.ibb) + sum(b.sf) + sum(b.hp)),3) as lgwoba
,#League wRC is problematic as the formula calls for essentially lgwOBA-lgwOBA divided by other
#things - essentially making it 0 + (lgTotalRuns/lgTotalPA) * lgTotalPA which = lgTotalRuns
#round(((([woba]-r.lgwoba)/r.wobascale)+(l.totr/l.totpa))*[PA],0) as wRC
(l.totR/l.totPA)*sum(l.totPA) as lgwRC
FROM league_history_batting_stats b 
INNER JOIN vRunValues2 r ON b.year=r.year AND b.league_id=r.league_id
INNER JOIN vLeagueRunsPerOut l ON b.year=l.year AND b.league_id=l.league_id
GROUP BY b.year, b.league_id, b.split_id;

#View for Calculated Batting Stats for individual players
CREATE OR REPLACE VIEW vBatStats AS

SELECT b.year
, b.player_id
, b.stint
, b.team_id
, t.abbr AS team
, g.abbr as league
, b.g
, b.ab
, b.pa
, b.r 
, b.h
, b.d
, b.t
, b.hr
, b.rbi
, b.sb
, b.cs
, b.bb
, b.k
, b.ibb
, b.hp
, b.sh
, b.sf
, b.gdp
, round(b.h/b.ab,3) AS `avg`
, round(b.k/b.ab,3) as krate
, round(b.bb/b.ab,3) as bbrate
, round((b.h + b.bb + b.hp)/(b.ab + b.bb + b.hp + b.sf),3) AS obp

, #round(100*([obp]/r.lgwoba),0) as `obp+`
round(100*((round((b.h + b.bb + b.hp)/(b.ab + b.bb + b.hp + b.sf),3)) /r.lgwoba),0) as `obp+`

, round((b.h + b.d + 2 * b.t + 3 * b.hr) / b.ab,3) as slg

, #[obp]+[slg] as ops
round((b.h + b.bb + b.hp)/(b.ab + b.bb + b.hp + b.sf),3) + round((b.h + b.d + 2 * b.t + 3 * b.hr) / b.ab,3) as ops

, #[slg]-[avg] as iso
round((b.h + b.d + 2 * b.t + 3 * b.hr) / b.ab,3) - round(b.h/b.ab,3) as iso

, round((r.wobaBB*b.bb + r.wobahb*b.hp + r.woba1b*(b.h-b.d-b.t-b.hr) + r.woba2b*b.d + r.woba3b*b.t + r.wobahr*b.hr)/(b.ab+b.bb - b.ibb + b.sf + b.hp),3) as woba

, #round((([woba]-r.lgwoba)/r.wobascale)*[PA],1) as wRAA
round((((round((r.wobaBB*b.bb + r.wobahb*b.hp + r.woba1b*(b.h-b.d-b.t-b.hr) + r.woba2b*b.d + r.woba3b*b.t + r.wobahr*b.hr)/(b.ab+b.bb - b.ibb + b.sf + b.hp),3))-r.lgwoba)/r.wobascale)*b.pa,1) as wRAA

, #round(((([woba]-r.lgwoba)/r.wobascale)+(l.totr/l.totpa))*[PA],0) as wRC
round(((((round((r.wobaBB*b.bb + r.wobahb*b.hp + r.woba1b*(b.h-b.d-b.t-b.hr) + r.woba2b*b.d + r.woba3b*b.t + r.wobahr*b.hr)/(b.ab+b.bb - b.ibb + b.sf + b.hp),3))-r.lgwoba)/r.wobascale)+(l.totr/l.totpa))*b.pa,0) as wRC

,# ((([wRAA]/[PA] + l.RperPA) + (l.RperPA - t.bpf*l.RperPA))/(lb.wRC/lb.PA))*100 AS [wRC+]
((((round((((round((r.wobaBB*b.bb + r.wobahb*b.hp + r.woba1b*(b.h-b.d-b.t-b.hr) + r.woba2b*b.d + r.woba3b*b.t + r.wobahr*b.hr)/(b.ab+b.bb - b.ibb + b.sf + b.hp),3))-r.lgwoba)/r.wobascale)*b.pa,1))/b.pa + l.RperPA) + (l.RperPA - p.avg*l.RperPA))/(lb.lgwRC/lb.lgPA))*100 AS `wRC+`

, b.war
FROM players_career_batting_stats b 
INNER JOIN vRunValues2 r ON b.year=r.year AND b.league_id=r.league_id
INNER JOIN vLeagueRunsPerOut l ON b.year=l.year AND b.league_id=l.league_id
INNER JOIN teams t ON b.team_id=t.team_id AND b.league_id=t.league_id AND b.level_id=t.level
INNER JOIN leagues g ON b.league_id=g.league_id AND b.level_id=g.league_level
INNER JOIN vLeagueHistoryBattingStatsPlus lb ON b.year=lb.year AND b.league_id=lb.league_id 
INNER JOIN parks p ON t.park_id=p.park_id
WHERE b.split_id=1
GROUP BY 
ORDER BY b.player_id, b.year