Dory_Mathis_510-3-create_insert.sql

-- ===========================
-- WARNING: respect following order to run scripts
-- 1. Dory_Mathis_510-1-identity_resource_mgmt.sql
-- 2. Dory_Mathis_510-2-privs_roles.sql
-- 3. Dory_Mathis_510-3-create_insert.sql
-- 4. Dory_Mathis_510-4-encryption.sql
-- 5. Dory_Mathis_510-5-audit.sql
-- 6. Dory_Mathis_510-6-application_security.sql
-- 7. Dory_Mathis_510-7-data_masking.sql
-- ===========================

-- The following script is run by the user appcar_admin_app in the PDB
-- Author: Mathis Dory
-- Date: 2023-12-26
-- Group 510



-- Switch to the PDB
ALTER SESSION SET CONTAINER = ORCLPDB;
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM DUAL;

-- Create tables for the project

 -- ==================== PART 1 ====================

-- User table
CREATE TABLE USERS
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    surname VARCHAR2(100) NOT NULL,
    sex CHAR(1) NOT NULL CHECK ( sex IN ('M', 'F')),
    birthdate DATE NOT NULL,
    password VARCHAR2(100) NOT NULL,
    email VARCHAR2(100) NOT NULL UNIQUE
);


-- Customers table
CREATE TABLE CUSTOMERS
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    license VARCHAR2(50) NOT NULL UNIQUE,
    id_user INT NOT NULL,
    FOREIGN KEY (id_user) REFERENCES USERS (id)
);


 -- ==================== PART 2 ====================

 -- Employees table
CREATE TABLE APPCAR_HR_MANAGER.EMPLOYEES
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    department VARCHAR2(100) NOT NULL CHECK (department IN ('hr', 'commercial', 'administrative')),
    id_user INT NOT NULL,
    FOREIGN KEY (id_user) REFERENCES USERS (id)
);

-- States table
CREATE TABLE STATES
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR2(50) NOT NULL UNIQUE
);

-- Models table
CREATE TABLE APPCAR_FLEET_RESPONSIBLE.MODELS
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name  VARCHAR2(100) NOT NULL,
    brand VARCHAR2(100) NOT NULL,
    power INT NOT NULL
);

-- Pricings table
CREATE TABLE APPCAR_FLEET_RESPONSIBLE.PRICINGS
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    daily_price DECIMAL(10, 2) NOT NULL,
    kilometer_price DECIMAL(10, 2) NOT NULL,
    daily_penalty DECIMAL(10, 2) NOT NULL,
    deposit DECIMAL(10, 2) NOT NULL
);

-- Equipments table
CREATE TABLE APPCAR_FLEET_RESPONSIBLE.EQUIPMENTS
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100) NOT NULL
);

-- Models_equipments_pricings table
CREATE TABLE APPCAR_FLEET_RESPONSIBLE.MODELS_EQUIPMENTS_PRICINGS
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_equipment INT,
    id_model INT NOT NULL,
    id_pricing INT NOT NULL,
    FOREIGN KEY (id_equipment) REFERENCES APPCAR_FLEET_RESPONSIBLE.EQUIPMENTS (id),
    FOREIGN KEY (id_model) REFERENCES APPCAR_FLEET_RESPONSIBLE.MODELS (id),
    FOREIGN KEY (id_pricing) REFERENCES APPCAR_FLEET_RESPONSIBLE.PRICINGS (id)
);


 -- ==================== PART 3 ====================

-- Vehicles table
CREATE TABLE APPCAR_FLEET_RESPONSIBLE.VEHICLES
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    purchase_date DATE NOT NULL,
    purchase_price DECIMAL(10, 2) NOT NULL,
    kilometrage INT NOT NULL,
    id_model_equipment INT NOT NULL,
    id_state INT NOT NULL,
    FOREIGN KEY (id_state) REFERENCES APPCAR_ADMIN_APP.STATES (id),
    FOREIGN KEY (id_model_equipment) REFERENCES APPCAR_FLEET_RESPONSIBLE.MODELS_EQUIPMENTS_PRICINGS (id)
);

 -- ==================== PART 4 ====================

-- Bookings table
CREATE TABLE BOOKINGS
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    starting_date DATE NOT NULL,
    ending_date DATE NOT NULL,
    is_canceled INT NOT NULL CHECK (is_canceled IN ('1', '0')),
    is_running INT NOT NULL CHECK (is_running IN ('1', '0')),
    is_closed INT NOT NULL CHECK (is_closed IN ('1', '0')),
    id_customer INT NOT NULL,
    id_vehicle INT NOT NULL,
    FOREIGN KEY (id_customer) REFERENCES CUSTOMERS (id),
    FOREIGN KEY (id_vehicle) REFERENCES APPCAR_FLEET_RESPONSIBLE.VEHICLES (id)
);

-- Invoices table
CREATE TABLE INVOICES
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    total_price DECIMAL(10, 2) NOT NULL,
    delay_supplement DECIMAL(10, 2) NOT NULL,
    booking_price DECIMAL(10, 2) NOT NULL,
    distance_price DECIMAL(10, 2) NOT NULL,
    generated_date DATE NOT NULL,
    is_paid INT NOT NULL CHECK (is_paid IN ('1', '0')),
    id_booking INT NOT NULL,
    FOREIGN KEY (id_booking) REFERENCES BOOKINGS (id)
);

-- Check-in table
CREATE TABLE CHECK_IN
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    check_in_date TIMESTAMP NOT NULL,
    comments VARCHAR2(255),
    id_booking INT NOT NULL,
    FOREIGN KEY (id_booking) REFERENCES BOOKINGS (id)
);

-- Returns table
CREATE TABLE RETURNS
(
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    return_date TIMESTAMP NOT NULL,
    comments VARCHAR2(255),
    id_booking INT NOT NULL,
    FOREIGN KEY (id_booking) REFERENCES BOOKINGS (id)
);

COMMIT;

-- ==================== INSERT data ====================

INSERT INTO APPCAR_ADMIN_APP.USERS (id, name, surname, sex, birthdate, password, email)
VALUES (1, 'John', 'Doe', 'M', TO_DATE('1980-01-01', 'YYYY-MM-DD'), 'pass123', 'john.doe@example.com');
INSERT INTO APPCAR_ADMIN_APP.USERS (id, name, surname, sex, birthdate, password, email)
VALUES (2, 'Jane', 'Smith', 'F', TO_DATE('1985-02-02', 'YYYY-MM-DD'), 'pass456', 'jane.smith@example.com');
INSERT INTO APPCAR_ADMIN_APP.USERS (id, name, surname, sex, birthdate, password, email)
VALUES (3, 'Luck', 'Cena', 'M', TO_DATE('1966-01-12', 'YYYY-MM-DD'), 'pass789', 'luck123@example.com');
INSERT INTO APPCAR_ADMIN_APP.USERS (id, name, surname, sex, birthdate, password, email)
VALUES (4, 'Mario', 'Bross', 'M', TO_DATE('1999-12-01', 'YYYY-MM-DD'), 'pass101112', 'mario.bross@example.com');

INSERT INTO APPCAR_ADMIN_APP.CUSTOMERS (id, license, id_user) VALUES (1, 'ABC123', 3);
INSERT INTO APPCAR_ADMIN_APP.CUSTOMERS (id, license, id_user) VALUES (2, 'XYZ456', 4);

INSERT INTO APPCAR_HR_MANAGER.EMPLOYEES (id, department, id_user) VALUES (1, 'hr', 1);
INSERT INTO APPCAR_HR_MANAGER.EMPLOYEES (id, department, id_user) VALUES (2, 'commercial', 2);

INSERT INTO APPCAR_ADMIN_APP.STATES (id, name) VALUES (1, 'Available');
INSERT INTO APPCAR_ADMIN_APP.STATES (id, name) VALUES (2, 'Unavailable');
INSERT INTO APPCAR_ADMIN_APP.STATES (id, name) VALUES (3, 'Damaged');
INSERT INTO APPCAR_ADMIN_APP.STATES (id, name) VALUES (4, 'In maintenance');

INSERT INTO APPCAR_FLEET_RESPONSIBLE.MODELS (id, name, brand, power) VALUES (1, 'Model X', 'Tesla', 1800);
INSERT INTO APPCAR_FLEET_RESPONSIBLE.MODELS (id, name, brand, power) VALUES (2, 'Picasso', 'Citroen', 200);
INSERT INTO APPCAR_FLEET_RESPONSIBLE.MODELS (id, name, brand, power) VALUES (3, 'Porsche', 'Taycan', 1900);
INSERT INTO APPCAR_FLEET_RESPONSIBLE.MODELS (id, name, brand, power) VALUES (4, 'Porsche', 'Macan', 680);
INSERT INTO APPCAR_FLEET_RESPONSIBLE.MODELS (id, name, brand, power) VALUES (5, 'Peugeot', '3008', 300);

INSERT INTO APPCAR_FLEET_RESPONSIBLE.PRICINGS (id, daily_price, kilometer_price, daily_penalty, deposit)
VALUES (1, 550.00, 0.10, 200.00, 3500.00);
INSERT INTO APPCAR_FLEET_RESPONSIBLE.PRICINGS (id, daily_price, kilometer_price, daily_penalty, deposit)
VALUES (2, 400.00, 0.10, 200.00, 2500.00);
INSERT INTO APPCAR_FLEET_RESPONSIBLE.PRICINGS (id, daily_price, kilometer_price, daily_penalty, deposit)
VALUES (3, 100.00, 0.50, 40.00, 300.00);
INSERT INTO APPCAR_FLEET_RESPONSIBLE.PRICINGS (id, daily_price, kilometer_price, daily_penalty, deposit)
VALUES (4, 98.00, 0.50, 40.00, 500.00);

INSERT INTO APPCAR_FLEET_RESPONSIBLE.EQUIPMENTS (id, name) VALUES (1, 'GPS Navigation');
INSERT INTO APPCAR_FLEET_RESPONSIBLE.EQUIPMENTS (id, name) VALUES (2, 'Child Seat');
INSERT INTO APPCAR_FLEET_RESPONSIBLE.EQUIPMENTS (id, name) VALUES (3, 'Extra Luggage');
INSERT INTO APPCAR_FLEET_RESPONSIBLE.EQUIPMENTS (id, name) VALUES (4, 'Insurance Package');
INSERT INTO APPCAR_FLEET_RESPONSIBLE.EQUIPMENTS (id, name) VALUES (5, 'Roof Box');

INSERT INTO APPCAR_FLEET_RESPONSIBLE.MODELS_EQUIPMENTS_PRICINGS (id, id_equipment, id_model, id_pricing) VALUES (1, 1, 1, 1);
INSERT INTO APPCAR_FLEET_RESPONSIBLE.MODELS_EQUIPMENTS_PRICINGS (id, id_equipment, id_model, id_pricing) VALUES (2, 2, 2, 2);
INSERT INTO APPCAR_FLEET_RESPONSIBLE.MODELS_EQUIPMENTS_PRICINGS (id, id_equipment, id_model, id_pricing) VALUES (3, 3, 3, 3);
INSERT INTO APPCAR_FLEET_RESPONSIBLE.MODELS_EQUIPMENTS_PRICINGS (id, id_equipment, id_model, id_pricing) VALUES (4, 4, 4, 4);


INSERT INTO APPCAR_FLEET_RESPONSIBLE.VEHICLES (id, purchase_date, purchase_price, kilometrage, id_model_equipment, id_state)
VALUES (1, TO_DATE('2020-01-01', 'YYYY-MM-DD'), 40000.00, 10000, 1, 1);
INSERT INTO APPCAR_FLEET_RESPONSIBLE.VEHICLES (id, purchase_date, purchase_price, kilometrage, id_model_equipment, id_state)
VALUES (2, TO_DATE('2019-05-01', 'YYYY-MM-DD'), 35000.00, 15000, 2, 2);


INSERT INTO APPCAR_ADMIN_APP.BOOKINGS (id, starting_date, ending_date, is_canceled, is_running, is_closed, id_customer, id_vehicle)
VALUES (1, TO_DATE('2023-07-01', 'YYYY-MM-DD'), TO_DATE('2023-07-10', 'YYYY-MM-DD'), 0, 0, 1, 1, 1);
INSERT INTO APPCAR_ADMIN_APP.BOOKINGS (id, starting_date, ending_date, is_canceled, is_running, is_closed, id_customer, id_vehicle)
VALUES (2, TO_DATE('2023-07-02', 'YYYY-MM-DD'), TO_DATE('2023-07-11', 'YYYY-MM-DD'), 0, 0, 1, 2, 2);

INSERT INTO APPCAR_ADMIN_APP.INVOICES (id, total_price, delay_supplement, booking_price, distance_price, generated_date, is_paid, id_booking)
VALUES (1, 5500.00, 0.00, 5000.00, 500.00, TO_DATE('2023-07-10', 'YYYY-MM-DD'), 1, 1);


INSERT INTO APPCAR_ADMIN_APP.CHECK_IN (id, check_in_date, comments, id_booking) VALUES (1, TO_DATE('2023-07-01 08:23:12', 'YYYY-MM-DD  HH24:MI:SS'), 'No issues', 1);
INSERT INTO APPCAR_ADMIN_APP.CHECK_IN (id, check_in_date, comments, id_booking) VALUES (2, TO_DATE('2023-07-02 11:00:30', 'YYYY-MM-DD  HH24:MI:SS'), 'Scratch on left door',2);


INSERT INTO APPCAR_ADMIN_APP.RETURNS (id, return_date, comments, id_booking) VALUES (1, TO_DATE('2023-07-10 22:00:00', 'YYYY-MM-DD  HH24:MI:SS'), 'Returned on time',1);


COMMIT;



-- ==================== DROP tables ====================
-- Execute it as sys to be faster


--DROP TABLE APPCAR_ADMIN_APP.USERS CASCADE CONSTRAINTS;
--DROP TABLE APPCAR_FLEET_RESPONSIBLE.MODELS CASCADE CONSTRAINTS;
--DROP TABLE APPCAR_ADMIN_APP.CUSTOMERS CASCADE CONSTRAINTS;
--DROP TABLE APPCAR_FLEET_RESPONSIBLE.MODELS_EQUIPMENTS_PRICINGS CASCADE CONSTRAINTS;
--DROP TABLE APPCAR_ADMIN_APP.STATES CASCADE CONSTRAINTS;
--DROP TABLE APPCAR_FLEET_RESPONSIBLE.EQUIPMENTS CASCADE CONSTRAINTS;
--DROP TABLE APPCAR_FLEET_RESPONSIBLE.PRICINGS CASCADE CONSTRAINTS;
--DROP TABLE APPCAR_ADMIN_APP.INVOICES CASCADE CONSTRAINTS;
--DROP TABLE APPCAR_ADMIN_APP.BOOKINGS CASCADE CONSTRAINTS;
--DROP TABLE APPCAR_ADMIN_APP.CHECK_IN CASCADE CONSTRAINTS;
--DROP TABLE APPCAR_ADMIN_APP.RETURNS CASCADE CONSTRAINTS;
--DROP TABLE APPCAR_FLEET_RESPONSIBLE.VEHICLES CASCADE CONSTRAINTS;
--DROP TABLE APPCAR_HR_MANAGER.EMPLOYEES CASCADE CONSTRAINTS;