/*---------------------DROP_TABLES--------------------------*/
DROP TABLE best_path_algorithm CASCADE CONSTRAINT;
DROP TABLE section_direction CASCADE CONSTRAINT;
DROP TABLE section_typology CASCADE CONSTRAINT;
DROP TABLE vehicle_motorization CASCADE CONSTRAINT;
DROP TABLE vehicle_type CASCADE CONSTRAINT;
DROP TABLE vehicle_toll_class CASCADE CONSTRAINT;
DROP TABLE vehicle CASCADE CONSTRAINT;
DROP TABLE road_network CASCADE CONSTRAINT;
DROP TABLE toll CASCADE CONSTRAINT;
DROP TABLE toll_max CASCADE CONSTRAINT;
DROP TABLE network_analysis CASCADE CONSTRAINT;
DROP TABLE road CASCADE CONSTRAINT;
DROP TABLE node CASCADE CONSTRAINT;
DROP TABLE section CASCADE CONSTRAINT;
DROP TABLE segment CASCADE CONSTRAINT;

/*---------------------CREATE_TABLES--------------------------*/
CREATE TABLE best_path_algorithm (
    best_path_algorithm VARCHAR(40),
    constraint pk_best_path_algorithm primary key (best_path_algorithm)
);

CREATE TABLE section_direction(
    direction VARCHAR(30),
    constraint pk_section_direction primary key(direction)
);

CREATE TABLE section_typology(
    typology VARCHAR(30),
    constraint pk_section_typology primary key(typology)
);

CREATE TABLE toll (
    id NUMBER(10),
    constraint pk_toll primary key(id)
);

CREATE TABLE road_network(
    name VARCHAR(20),
    description VARCHAR(255),
    constraint pk_road_network primary key(name)
);

CREATE TABLE vehicle_toll_class(
    toll_class NUMBER(10),
    constraint pk_vehicle_toll_class primary key(toll_class)
);

CREATE TABLE vehicle_motorization (
    motorization VARCHAR(20),
    constraint pk_vehicle_motorization primary key(motorization)
);

CREATE TABLE vehicle_type (
    TYPE VARCHAR(20),
    constraint pk_vehicle_type primary key(TYPE)
);

CREATE TABLE vehicle (
    name VARCHAR(20),
    TYPE VARCHAR(20),
    toll_class NUMBER(10),
    motorization VARCHAR(20),
    road_network_name VARCHAR(20),
    mass NUMBER(19, 0),
    load NUMBER(4, 1),
    drag_coeficient NUMBER(10, 0),
    rolling_resistence_coefficient NUMBER(19, 0),
    wheel_size NUMBER(19, 0),
    energy_function VARCHAR(255),
    braking_energy_regeneration NUMBER(19, 0),
    constraint pk_vehicle primary key(name),
    constraint fk_vehicle_vehicle_road_network foreign key(road_network_name) references road_network(name),
    constraint fk_vehicle_vehicle_motorization foreign key(motorization) references vehicle_motorization(motorization),
    constraint fk_vehicle_vehicle_toll_class foreign key(toll_class) references vehicle_toll_class(toll_class),
    constraint fk_vehicle_vehicle_type foreign key(TYPE) references vehicle_type(TYPE)
);

CREATE TABLE network_analysis (
    vehicle_name VARCHAR(20),
    best_path_algorithm VARCHAR(40),
    toll_costs NUMBER(4, 2),
    travelling_time VARCHAR(8),
    energy_consumption NUMBER(5, 2),
    sections_list VARCHAR(200),
    constraint fk_network_analysis_best_path_algorithm foreign key(best_path_algorithm) references best_path_algorithm(best_path_algorithm),
    constraint fk_netword_analysis_vehicle foreign key(vehicle_name) references vehicle(name),
    constraint pk_network_analysis primary key(vehicle_name, best_path_algorithm)
);


CREATE TABLE road(
    name VARCHAR(10),
    road_network_name VARCHAR(20),
    constraint fk_road_road_network foreign key(road_network_name) references road_network(name),
    constraint pk_road primary key(name)
);

CREATE TABLE node(
    id NUMBER(10),
    constraint pk_node primary key(id)
);

CREATE TABLE section(
    section_id NUMBER(10),
    typology VARCHAR(30),
    road_name VARCHAR(10),
    direction VARCHAR(30),
    ending_node NUMBER(10),
    begining_node NUMBER(10),
    ordered_sequence_segments VARCHAR(255),
    constraint pk_section primary key(section_id),
    constraint fk_section_road foreign key(road_name) references road(name),
    constraint fk_section_section_direction foreign key(direction) references section_direction(direction),
    constraint fk_section_ending_node foreign key(ending_node) references node(id),
    constraint fk_section_begining_node foreign key(begining_node) references node(id),
    constraint fk_section_typology foreign key(typology) references section_typology(typology)
);

CREATE TABLE segment(
    segment_id NUMBER(10),
    section_id NUMBER(10),
    initial_height NUMBER(10),
    slope NUMBER(19,0),
    LENGTH NUMBER(10),
    wind_direction NUMBER(19,0),
    wind_speed NUMBER(19,0),
    maximum_velocity NUMBER(5,0),
    minimum_velocity NUMBER(5,0),
    toll NUMBER(10),
    constraint fk_segment_toll foreign key(toll) references toll(id),
    constraint fk_segment_section foreign key(section_id) references section(section_id),
    constraint pk_segment primary key(segment_id)
);

CREATE TABLE toll_max (
    vehicle_toll_class NUMBER(10),
    toll_id NUMBER(10),
    price NUMBER(4, 2),
    constraint fk_toll_max_vehicle_toll_class foreign key(vehicle_toll_class) references vehicle_toll_class(toll_class),
    constraint fk_toll_max_toll foreign key(toll_id) references toll(id)
);