/*
 Copyright 2017 Amazon.com

  Licensed under the Apache License, Version 2.0 (the "License");
  you may not use this file except in compliance with the License.
  You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.
*/

create sequence sport_team_seq
  start with 1 increment by 10 cache 20;

create table sport_team
(id               NUMBER NOT NULL,
 name             varchar2(30) NOT NULL,
 abbreviated_name varchar2(10),
 home_field_id    NUMBER(3) 
  constraint home_field_fk references sport_location(id),
 sport_type_name       varchar2(15) NOT NULL
  constraint st_sport_type_fk references sport_type(name),
 sport_league_short_name  varchar2(10) NOT NULL,
 sport_division_short_name varchar2(10),
 constraint sport_team_pk primary key(id)
);

CREATE TRIGGER sport_team_id_trg
  BEFORE INSERT ON sport_team
  FOR EACH ROW
DECLARE
BEGIN
  IF( :new.id IS NULL )
  THEN
    :new.id := sport_team_seq.nextval;
  END IF;
END;
/


-- unique index to enforce unique names w/in a sport type and league
create unique index sport_team_u on sport_team(sport_type_name,sport_league_short_name,name);

-- Load NFL Data
create or replace procedure loadNFLTeams AS
  v_sport_type VARCHAR2(10) := 'football';
  v_league     VARCHAR2(10) := 'NFL';
  v_division   VARCHAR2(10);
BEGIN
  v_division := 'AFC North';
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Baltimore Ravens','BAL',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Cincinnati Bengals','CIN',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Cleveland Browns','CLE',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Pittsburgh Steelers','PIT',v_sport_type,v_league,v_division);

  v_division := 'AFC South';
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Houston Texans','HOU',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Indianapolis Colts','IND',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Jacksonville Jaguars','JAX',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Tennessee Titans','TEN',v_sport_type,v_league,v_division);

  v_division := 'AFC East';
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Buffalo Bills','BUF',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Miami Dolphins','MIA',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('New England Patriots','NE',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('New York Jets','NYJ',v_sport_type,v_league,v_division);

  v_division := 'AFC West';
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Denver Broncos','DEN',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Kansas City Chiefs','KC',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Oakland Raiders','OAK',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('San Diego Chargers','SD',v_sport_type,v_league,v_division);

  v_division := 'NFC North';
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Chicago Bears','CHI',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Detroit Lions','DET',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Green Bay Packers','GB',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Minnesota Vikings','MIN',v_sport_type,v_league,v_division);

  v_division := 'NFC South';
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Atlanta Falcons','ATL',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Carolina Panthers','CAR',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('New Orleans Saints','NO',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Tampa Bay Buccaneers','TB',v_sport_type,v_league,v_division);

  v_division := 'NFC East';
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Dallas Cowboys','DAL',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('New York Giants','NYG',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Philadelphia Eagles','PHI',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Washington Redskins','WAS',v_sport_type,v_league,v_division);

  v_division := 'NFC West';
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Arizona Cardinals','ARI',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Los Angeles Rams','LA',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('San Francisco 49ers','SF',v_sport_type,v_league,v_division);
  INSERT INTO sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
  VALUES ('Seattle Seahawks','SEA',v_sport_type,v_league,v_division);
END;
/

-- Load MLB Data
CREATE or replace PROCEDURE LoadMLBTeams AS
  v_div sport_division.short_name%TYPE;

  CURSOR mlb_teams IS
  select distinct decode(TRIM(mlb_team),'AAA','LAA',mlb_team) a_name,
                decode(TRIM(mlb_team_long),'Anaheim Angels', 'Los Angeles Angels',mlb_team_long) l_name
  from mlb_data;

BEGIN
  FOR trec IN mlb_teams LOOP

    CASE 
     WHEN trec.a_name IN ('BAL', 'BOS', 'TOR', 'TB', 'NYY')  THEN v_div := 'AL East';
     WHEN trec.a_name IN  ('CLE','DET','KC','CWS','MIN') THEN v_div := 'AL Central';
     WHEN trec.a_name IN  ('TEX','SEA','HOU','OAK','LAA') THEN v_div := 'AL West';
     WHEN trec.a_name IN  ('WSH','MIA','NYM','PHI','ATL')THEN v_div := 'NL East';
     WHEN trec.a_name IN  ('CHC','STL','PIT','MIL','CIN') THEN v_div := 'NL Central';
     WHEN trec.a_name IN  ('COL','SD','LAD','SF','ARI') THEN v_div := 'NL West';
    END CASE;
      
    insert into sport_team(name,abbreviated_name,sport_type_name,sport_league_short_name,sport_division_short_name)
    values(trec.l_name, trec.a_name, 'baseball','MLB',v_div);
  END LOOP;
END;
/