--create tables
DROP TABLE park CASCADE CONSTRAINTS PURGE;
DROP TABLE bicycle CASCADE CONSTRAINTS PURGE;
DROP TABLE bike_type CASCADE CONSTRAINTS PURGE;
DROP TABLE app_user CASCADE CONSTRAINTS PURGE;
DROP TABLE rental CASCADE CONSTRAINTS PURGE;

CREATE TABLE park(
    id_park                 integer         CONSTRAINT pk_park_id_park PRIMARY KEY,
    park_name               varchar(50)     CONSTRAINT nn_park_id_park NOT NULL
                                            CONSTRAINT uk_park_id_park UNIQUE,
    latitude                number(10,2)    CONSTRAINT nn_park_latitude NOT NULL,
    longitude               number(10,2)    CONSTRAINT nn_park_longitude NOT NULL,
    altitude                number(10,2)    CONSTRAINT nn_park_altitude NOT NULL,
    capacity_electrical      integer         CONSTRAINT nn_park_capacity_electrical NOT NULL
                                            CONSTRAINT ck_park_capacity_electrical CHECK (capacity_electrical>=0),
    capacity_standard       integer         CONSTRAINT nn_park_capacity_road_mountain NOT NULL
                                            CONSTRAINT ck_park_capacity_road_mountain CHECK(capacity_standard>=0)
);

CREATE TABLE bike_type(
    id_type     integer         CONSTRAINT pk_bike_type_id_type PRIMARY KEY,
    descrip     varchar(100)    CONSTRAINT nn_bike_type_descrip NOT NULL,
                                CONSTRAINT ck_bike_type_descrip CHECK (UPPER(descrip) IN ('MOUNTAIN', 'ROAD', 'ELECTRICALLY ASSISTED'))
);

CREATE TABLE bicycle(
    bike_number         integer         CONSTRAINT pk_bicycle_bike_number PRIMARY KEY,
    id_type             integer         CONSTRAINT nn_bicycle_id_type NOT NULL,
    id_park             integer,
    current_autonomy    number(10,2)    CONSTRAINT ck_bicycle_current_autonomy CHECK (current_autonomy>=0.00),
    max_autonomy        number(10,2)    CONSTRAINT ck_bicycle_max_autonomy CHECK (max_autonomy>=0.00),
    consumption         number(10,2),
                                    CONSTRAINT fk_bicycle_id_type FOREIGN KEY (id_type) REFERENCES bike_type(id_type),
                                    CONSTRAINT fk_bicycle_id_park FOREIGN KEY (id_park) REFERENCES park(id_park)
);

CREATE TABLE app_user(
    id_user             integer     CONSTRAINT pk_user_id_user PRIMARY KEY,
    user_name           varchar(20) CONSTRAINT nn_user_user_name NOT NULL,
    credit_card_number  varchar(16) CONSTRAINT nn_user_credit_card_number NOT NULL
                                    CONSTRAINT uk_user_credit_card_numer UNIQUE,
    height              number(3)   CONSTRAINT nn_user_height NOT NULL
                                    CONSTRAINT ck_user_height CHECK(height>0),
    weight              number(3)   CONSTRAINT nn_user_weight NOT NULL
                                    CONSTRAINT ck_user_weight CHECK(weight>0),
    paid                integer     CONSTRAINT nn_user_paid NOT NULL
                                    CONSTRAINT ck_user_paid CHECK (paid IN (0,1)),
    email               varchar(80) CONSTRAINT uk_user_email UNIQUE
                                    CONSTRAINT ck_user_email CHECK (REGEXP_LIKE (email, '^[A-Za-z]+[A-Za-z0-9.]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'))
);
CREATE TABLE rental(
    id_rental       integer GENERATED AS IDENTITY CONSTRAINT pk_rental_id_rental PRIMARY KEY,
    id_user         integer     CONSTRAINT nn_rental_id_user NOT NULL,
    bike_number     integer     CONSTRAINT nn_rental_bike_number NOT NULL,
    id_park_start   integer     CONSTRAINT nn_rental_id_park_start NOT NULL
                                CONSTRAINT fk_rental_id_park_start REFERENCES park(id_park),
    id_park_end     integer     CONSTRAINT fk_rental_id_park_end REFERENCES park(id_park),
    initial_date    timestamp   CONSTRAINT nn_rental_data_inicio NOT NULL,
    end_date        timestamp,
                        CONSTRAINT fk_rental_id_user FOREIGN KEY (id_user) REFERENCES app_user(id_user),
                        CONSTRAINT fk_rental_bike_number FOREIGN KEY (bike_number) REFERENCES bicycle(bike_number)
);

INSERT INTO bike_type VALUES (1, 'Mountain');
INSERT INTO bike_type VALUES (2, 'Road');
INSERT INTO bike_type VALUES (3, 'Electrically assisted');

INSERT INTO park VALUES (87, 'Park Test', 2.00, 23.30, 0.10, 24, 56);
INSERT INTO park VALUES (16, 'Park Test2', 3.00, 3.00, 45.03, 24, 56);
INSERT INTO park VALUES (1, 'Park Test3', 4.00, 5.00, 0.44, 24, 56);

INSERT INTO bicycle VALUES(90, 1, 87, 0.00, 0.00, NULL);
INSERT INTO bicycle VALUES(10, 2, 87, 0.00, 0.00, NULL);
INSERT INTO bicycle VALUES(20, 3, 87, 0.00, 0.00, 10);
INSERT INTO bicycle VALUES(30, 1, 87, 0.00, 0.00, NULL);
INSERT INTO bicycle VALUES(1, 2, 87, 0.00, 0.00, NULL);
INSERT INTO bicycle VALUES(2, 2, 87, 0.00, 0.00, NULL);
INSERT INTO bicycle(bike_number, id_type, current_autonomy, max_autonomy) VALUES(21, 3, 1.00, 1.00);

INSERT INTO app_user VALUES(01, 'Thom', '4322345934290372', 167, 68, 1, 'rh@email.com');
INSERT INTO app_user VALUES(02, 'David', '4278770330700696', 170, 79, 0, 'pf@email.com');
INSERT INTO app_user VALUES(03, 'Justin', '4485879296463584', 177, 68, 1, 'bi@email.com');


INSERT INTO rental(id_user, bike_number, id_park_start, initial_date) VALUES(1, 21, 87, TO_TIMESTAMP ('2018-12-16 12:00:00', 'YYYY-MM-DD HH24:MI:SS'));