/*
 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 sporting_event_ticket_seq
  start with 1 increment by 10 cache 100;

create table sporting_event_ticket
( id NUMBER NOT NULL,
  sporting_event_id NUMBER NOT NULL
    constraint set_sporting_event_fk references sporting_event(id),
  sport_location_id NUMBER NOT NULL,
  seat_level NUMBER(1) NOT NULL,
  seat_section VARCHAR(15) NOT NULL,
  seat_row VARCHAR2(10) NOT NULL,
  seat     VARCHAR2(10) NOT NULL,
  ticketholder_id NUMBER 
    constraint set_person_id references person(id),
  ticket_price NUMBER(8,2) NOT NULL,
  constraint set_seat_fk foreign key (sport_location_id,seat_level,seat_section,seat_row, seat) 
   references seat(sport_location_id,seat_level,seat_section,seat_row,seat),
  constraint sporting_event_ticket_pk primary key(id)
);


create index set_sporting_event_idx on sporting_event_ticket(sporting_event_id);

create index set_seat_idx on sporting_event_ticket(sport_location_id,seat_level,seat_section,seat_row,seat);

create index set_ticketholder_idx on sporting_event_ticket(ticketholder_id);

create index set_ev_id_tkholder_id_idx on sporting_event_ticket(sporting_event_id,ticketholder_id,'');