/*
 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_location_seq
  start with 1 increment by 1 cache 20;


create table sport_location
(ID NUMBER(3) NOT NULL,
 name varchar2(60) NOT NULL,
 city varchar2(60) NOT NULL,
 seating_capacity NUMBER(7),
 levels NUMBER(1),
 sections NUMBER(4),
 constraint sport_location_pk primary key(id)
);


SET DEFINE OFF
insert into sport_location(id,name,city,seating_capacity)
values(1,'Angel Stadium','Anaheim California',45483);
insert into sport_location(id,name,city,seating_capacity)
values(2,'AT&T Park','San Francisco California',41915);
insert into sport_location(id,name,city,seating_capacity)
values(3,'Busch Stadium','St Louis Missouri',43975);
insert into sport_location(id,name,city,seating_capacity)
values(4,'Chase Field','Phoenix Arizona',48519);
insert into sport_location(id,name,city,seating_capacity)
values(5,'Citi Field','Queens New York',41922);
insert into sport_location(id,name,city,seating_capacity)
values(6,'Citizens Bank Park','Philadelphia Pennsylvania',43651);
insert into sport_location(id,name,city,seating_capacity)
values(7,'Comercia Park','Detroit Michigan',41297);
insert into sport_location(id,name,city,seating_capacity)
values(8,'Coors Field','Denver Colorado',50398);
insert into sport_location(id,name,city,seating_capacity)
values(9,'Dodger Stadium','Los Angeles California',56000);
insert into sport_location(id,name,city,seating_capacity)
values(10,'Fenway Park','Boston Massachusetts',37949);
insert into sport_location(id,name,city,seating_capacity)
values(11,'GLobe Life Park','Arlington Texas',48114);
insert into sport_location(id,name,city,seating_capacity)
values(12,'Great American Ball Park','Cincinnati Ohio',42319);
insert into sport_location(id,name,city,seating_capacity)
values(13,'Kauffman Stadium','Kansas City Missouri',37903);
insert into sport_location(id,name,city,seating_capacity)
values(14,'Martins Park','Miami Florida',36742);
insert into sport_location(id,name,city,seating_capacity)
values(15,'Miller Park','Milwaukee Wisconsin',41900);
insert into sport_location(id,name,city,seating_capacity)
values(16,'Minute Maid Park','Houston Texas',41676);
insert into sport_location(id,name,city,seating_capacity)
values(17,'Nationals Park','Washington D.C.',41313);
insert into sport_location(id,name,city,seating_capacity)
values(18,'Oakland Coliseum','Oakland California',35067);
insert into sport_location(id,name,city,seating_capacity)
values(19,'Camden Yards','Baltimore Maryland',45971);
insert into sport_location(id,name,city,seating_capacity)
values(20,'Petco Park','San Diego California',40162);
insert into sport_location(id,name,city,seating_capacity)
values(21,'PNC Park','Pittsburgh Pennsylvania',38362);
insert into sport_location(id,name,city,seating_capacity)
values(22,'Progressive Field','Cleveland Ohio',35225);
insert into sport_location(id,name,city,seating_capacity)
values(23,'Rogers Centre','Toronto Ontario',49282);
insert into sport_location(id,name,city,seating_capacity)
values(24,'Safeco Field','Seattle Washington',47963);
insert into sport_location(id,name,city,seating_capacity)
values(25,'Target Field','Minneapolis Minnesota',38871);
insert into sport_location(id,name,city,seating_capacity)
values(26,'Tropicana Field','St. Petersburg Florida',31042);
insert into sport_location(id,name,city,seating_capacity)
values(27,'Turner Field','Atlanta Georgia',49586);
insert into sport_location(id,name,city,seating_capacity)
values(28,'US Cellular Field','Chicago Illinois',40615);
insert into sport_location(id,name,city,seating_capacity)
values(29,'Wrigley Field','Chicago Illinois',41268);
insert into sport_location(id,name,city,seating_capacity)
values(30,'Yankee Stadium','Bronx New York',49642);
SET DEFINE ON

UPDATE sport_location
SET levels = 2
WHERE seating_capacity < 50000
AND levels is null;

UPDATE sport_location
SET levels = 3
WHERE seating_capacity >= 50000
AND levels is null;

UPDATE sport_location 
SET sections = ROUND(seating_capacity/1000)
WHERE sections is null;

-------------------------------
-- load NFL team stadiums
-------------------------------
--- make sure sequence is > the max id in the table to avoid collisions

DECLARE
  cur_max NUMBER;
  v_new_id NUMBER := 0;
BEGIN
    select max(id) into cur_max from sport_location;

    WHILE v_new_id <= cur_max LOOP
      v_new_id := sport_location_seq.nextval;  -- make sure the id > the current max (some are set by hand)
    END LOOP;
END;
/


UPDATE nfl_stadium_data
SET sport_location_id = sport_location_seq.nextval;

INSERT INTO sport_location(id,name, city, seating_capacity, sections)
SELECT sport_location_id, stadium, location, seating_capacity, ROUND(seating_capacity/1000)
FROM nfl_stadium_data;

UPDATE sport_location
SET levels = 2
WHERE seating_capacity < 75000
AND levels is null;

UPDATE sport_location
SET levels = 3
WHERE seating_capacity >= 75000
AND levels is null;