drop table Seat_MaTrix;
drop table customer;
drop table bus_instance;
drop table class_type;
DROP TABLE BUS_INFO;
Drop TABLE Bus_stand;


Drop table Route_frequency;
Drop table Service_class;
Drop table Path_order;
Drop table ROUTE;
Drop table Route_element;
Drop table Fuel_Index;
Drop table Place;

drop sequence c_seq;
drop sequence instance_seq;
drop sequence place_seq;
drop sequence reid_seq;
drop sequence route_seq;


create  sequence c_seq;
create  sequence instance_seq;
create  sequence place_seq;
create  sequence reid_seq;
create  sequence route_seq;


CREATE TABLE FUEL_INDEX
 (
   FPI             NUMBER(3)          NOT NULL,
   Multiplier      FLOAT              DEFAULT 1.0,
   CONSTRAINT fpi_pk PRIMARY KEY(FPI)
);


CREATE TABLE PLACE
(
   Place            CHAR(8)            NOT NULL,
   Full_Name        CHAR(25)           NOT NULL,
   CONSTRAINT Place_pk PRIMARY KEY(Place)
);   

CREATE TABLE ROUTE
 (
   Route_ID         NUMBER(5)          NOT NULL,
   Place1           CHAR(8)            NOT NULL,
   Place2           CHAR(8)            NOT NULL,
   Stops            NUMBER(4)          NOT NULL,
   KM               NUMBER(5)          NOT NULL,
   FPI              NUMBER(3)          DEFAULT 1,
   Stoptime         NUMBER(5)          NOT NULL,
   TravelMins       NUMBER(5)          NOT NULL,
   CONSTRAINT Route_pk PRIMARY KEY(Route_ID)
 );

CREATE TABLE ROUTE_FREQUENCY
 (
   Route_id         NUMBER(5)          NOT NULL,
   Day              NUMBER(2)          NOT NULL,
   Frequency        NUMBER(6)          NOT NULL,
   Time_Hrs         NUMBER(3)          NOT NULL,
   Time_mins        NUMBER(3)          NOT NULL,
   CONSTRAINT rf_pk PRIMARY KEY(route_id,day),
   CONSTRAINT rf_fk FOREIGN KEY(route_Id) REFERENCES ROUTE ON DELETE CASCADE
);

CREATE TABLE SERVICE_CLASS
(
   Route_id         NUMBER(5)          NOT NULL,
   Day              NUMBER(2)          NOT NULL,
   Class            NUMBER(2)          NOT NULL,
   CONSTRAINT  Service_class_pk PRIMARY KEY(Route_ID,Day,Class),
   CONSTRAINT  sc_fk FOREIGN KEY(route_Id) REFERENCES ROUTE ON DELETE CASCADE
);

CREATE TABLE PATH_ORDER
 (
   Route_Id        NUMBER(5)           NOT NULL,
   Sequence_No     NUMBER(4)           NOT NULL,
   REID            NUMBER(5)           NOT NULL,
   CONSTRAINT Path_order PRIMARY KEY(Route_ID,REID),
   CONSTRAINT PATH_order_fk FOREIGN KEY(route_id) REFERENCES ROUTE ON DELETE CASCADE
);
  
CREATE TABLE ROUTE_ELEMENT
 (
   REID		   NUMBER(5)           NOT NULL,
   Place1          CHAR(8)             NOT NULL,
   Place2          CHAR(8)             NOT NULL,
   KM              NUMBER(5)           NOT NULL,
   CONSTRAINT REID PRIMARY KEY(REID)
 );


CREATE TABLE BUS_STAND
(
   Stand_Id          NUMBER(8)     NOT NULL,
   Location          CHAR(25),
   City              CHAR(20)      NOT NULL,
   Capacity          NUMBER(4),
   CONSTRAINT bus_stand_pk PRIMARY KEY(Stand_Id)
 );

CREATE TABLE CLASS_TYPE
(
  class              NUMBER(2)     NOT NULL,
  Description        CHAR(20),
  Base               NUMBER(4)     NOT NULL,
  Multiplier         FLOAT         DEFAULT 1.0,
  CONSTRAINT class_pk PRIMARY KEY(class)
);

CREATE TABLE BUS_INFO
(  Registration           CHAR(15)          NOT NULL,
   Total_Seats            NUMBER(3)         NOT NULL,
   Type                   CHAR(4),           
   Company                CHAR(15)          NOT NULL,
   Date_of_Commissioning  DATE              DEFAULT SYSDATE,
   Available              CHAR(1)           DEFAULT 'Y',
   CONSTRAINT  bus_info_pk PRIMARY KEY (Registration),
   CHECK (Available IN ('Y','N') )
);

CREATE TABLE BUS_INSTANCE
(  
   Instance_ID       NUMBER(10)    NOT NULL,
   Bus_ID            NUMBER(5)     NOT NULL,
   Dep_Date          DATE          NOT NULL,
   Dep_Time          CHAR(5)       NOT NULL,
   Departure_Station NUMBER(4)     NOT NULL,
   Registration      CHAR(15)      NOT NULL,
   Class             NUMBER(2)     NOT NULL,
   Free_seats        NUMBER(3)     NOT NULL,
   CONSTRAINT bi_pk PRIMARY KEY(Instance_ID),
   CONSTRAINT class_restr_fk FOREIGN KEY(class) REFERENCES CLASS_TYPE(class),  
   CONSTRAINT regn_fk FOREIGN KEY (Registration) REFERENCES BUS_INFO(Registration),
   CONSTRAINT Arr_stand_fk FOREIGN KEY(Departure_station) REFERENCES BUS_STAND(Stand_Id) ON DELETE Cascade,
   CONSTRAINT Route_fk FOREIGN KEY(Bus_ID) REFERENCES ROUTE(Route_ID) ON DELETE Cascade
);


 CREATE TABLE CUSTOMER
 (
   Customer_ID       NUMBER(11)         NOT NULL,
   Instance_ID	     NUMBER(10)		NOT NULL,
   Journey_Date      DATE		NOT NULL,
   Name              CHAR(14)           NOT NULL,
   Age		     NUMBER(3),
   Phone	     NUMBER(20),
   Gender	     CHAR(1),
   Origin            CHAR(8),
   Destin            CHAR(8),
   Fare              NUMBER(6)         DEFAULT 0,  
   CONSTRAINT  Cid_pk PRIMARY KEY (Customer_Id)
);


CREATE TABLE SEAT_MATRIX
 (
   Instance_ID	     NUMBER(10)    NOT NULL,
   Seat_no	     NUMBER(3) 	   NOT NULL,
   Route_Sequence1   NUMBER(4)     NOT NULL,
   Route_sequence2   NUMBER(4)     NOT NULL,
   Customer_ID       NUMBER(11)    NOT NULL,
   Status            CHAR(1)       DEFAULT 'a',   
   CONSTRAINT  res_pk PRIMARY KEY (Seat_no,Instance_ID,Route_sequence1,Route_sequence2,Customer_ID),
   CONSTRAINT  Bus_id_fk FOREIGN KEY (Instance_ID) REFERENCES  BUS_INSTANCE,
   CHECK (Status IN ('a','r','l') ) ,
CONSTRAINT Custo_id_fk FOREIGN KEY (Customer_ID) REFERENCES CUSTOMER 
);

drop view vticket;
create view vticket(CustomerID,Route, JourneyDate, Origin, Destination, Fare, Class)
as
SELECT distinct c.customer_id,b.bus_id,c.Journey_date, o.Full_name,d.Full_name,c.fare,class_type.Description
FROM Customer c, 
bus_instance b, 
(Select * from Place, customer cu , Bus_instance bu where place = cu.origin AND bu.instance_id=cu.Instance_id) o,
(Select * from Place, customer cu, Bus_instance bu where place = cu.destin AND bu.instance_id=cu.Instance_id) d,
class_type
WHERE b.Instance_id = c.Instance_id AND o.Place = c.origin AND d.place = c.destin AND class_type.class=b.class ;

drop view bTimeTable;
create view bTimeTable as
( select bi.instance_id, bi.Bus_Id, bi.Dep_date, ds.location,ds.city, cl.description  from
bus_instance bi, (select * from bus_stand) ds, (select * from class_type) cl
where bi.Departure_station = ds.Stand_id AND bi.class = cl.class);

drop table tempbusroute;
create Table tempbusroute(
seat_no    number(3),
mat        char(500),
constraint tpk primary key(seat_no))
ORGANIZATION INDEX;

drop view routeserviceview;
create view routeserviceview as
(select r.route_id,bi.instance_id,bi.dep_date,r.km from bus_instance bi,route r where bi.bus_id = r.route_id );

drop view accidentview;
create view accidentview(Instance, registration,Customer_Name,Age,gender,phone) as
(select bi.instance_id,bi.registration,c.name,c.age,c.gender,c.phone from bus_instance bi, customer c where bi.instance_id = c.instance_id);


