Garage Parking Project
===

[TOC]

Model Entity Relationship
==

Getting Started
===

## Migrations

CREATE TABLE customers(
    id INTEGER NOT NULL,
    name VARCHAR2(100) NOT NULL,
    CONSTRAINT customers_id_pk PRIMARY KEY(id)
);

CREATE TABLE vehicules(
    id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    brand VARCHAR2(100) NOT NULL,
    model VARCHAR2(100) NOT NULL,
    plate VARCHAR2(100) NOT NULL,
    CONSTRAINT vehicules_id_pk PRIMARY KEY(id),
    CONSTRAINT vehicules_customer_fk FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT vehicules_plate_uk UNIQUE (plate)
);

CREATE TABLE lots(
    id INTEGER NOT NULL,
    code VARCHAR2(100) NOT NULL,
    busy NUMBER NOT NULL,
    CONSTRAINT lots_id_pk PRIMARY KEY(id),
    CONSTRAINT lots_busy_chk CHECK (busy in (0, 1)),
    CONSTRAINT lots_code_uk UNIQUE (code)
);

CREATE TABLE timings(
    id INTEGER NOT NULL,
    vehicule_id INTEGER NOT NULL,
    lot_id INTEGER NOT NULL,
    start_at TIMESTAMP NULL,
    end_at TIMESTAMP NULL,
    status VARCHAR2(100) NOT NULL,
    booked_at DATE NULL,
    paid NUMBER NULL,
    amount NUMBER NULL,
    CONSTRAINT timings_id_pk PRIMARY KEY(id),
    CONSTRAINT timings_vehicule_fk FOREIGN KEY (vehicule_id) REFERENCES vehicules(id),
    CONSTRAINT timings_lots_fk FOREIGN KEY (lot_id) REFERENCES lots(id),
    CONSTRAINT timings_status_chk CHECK (status in ('RESERVED', 'TAKEN', 'CANCELLED')),
    CONSTRAINT timings_paid_chk CHECK (paid in (0, 1))
);

CREATE TABLE sales_report(
    id INTEGER NOT NULL,
    day VARCHAR2(100) NOT NULL,
    total NUMBER NOT NULL,
    CONSTRAINT sr_id_pk PRIMARY KEY(id)
);

## Seeds

INSERT INTO customers VALUES(1, 'Ada Ramos');
INSERT INTO customers VALUES(2, 'Mario Contreras');
INSERT INTO customers VALUES(3, 'Sukhkeet Singh');

INSERT INTO vehicules VALUES(1, 1, 'Tesla', '2021', 'E71 TZD');
INSERT INTO vehicules VALUES(2, 1, 'BMW', '2020', 'AAA 125');
INSERT INTO vehicules VALUES(3, 2, 'Chevrolet', '2017', 'ABC 123');
INSERT INTO vehicules VALUES(4, 3, 'Ford', '2020', 'X1X 2Y2');

## Triggers

CREATE OR REPLACE TRIGGER before_update_timings
BEFORE UPDATE ON timings
FOR EACH ROW
BEGIN
    IF :OLD.start_at IS NOT NULL AND :OLD.start_at != :NEW.start_at THEN
        RAISE_APPLICATION_ERROR(-20000, 'ERR409: THE PARKING LOT CAN NOT BE UPDATED');
    END IF;
END;

CREATE OR REPLACE TRIGGER after_update_timings
AFTER INSERT OR UPDATE ON timings
FOR EACH ROW   
BEGIN
    IF :NEW.STATUS ='CANCELLED' THEN
        UPDATE lots SET busy = 0 WHERE id = :NEW.lot_id;
    ELSIF :NEW.STATUS ='RESERVED' OR :NEW.STATUS ='TAKEN' THEN 
        UPDATE lots SET busy = 1 WHERE id = :NEW.lot_id;
    END IF;
    
    IF :NEW.PAID = 1 THEN
        UPDATE lots SET busy = 0 WHERE id = :NEW.lot_id;
    END IF;
END;

## Sequences

CREATE SEQUENCE lots_id_seq 
    INCREMENT BY 1
    START WITH 1;

CREATE SEQUENCE timings_id_seq 
    INCREMENT BY 1
    START WITH 1;

CREATE SEQUENCE sales_report_id_seq 
    INCREMENT BY 1
    START WITH 1;

Features
===

## Establish parking lots

CREATE OR REPLACE PROCEDURE parking_lot_set (in_code IN VARCHAR2)
IS
   l_status NUMBER := 0;
BEGIN
    INSERT INTO lots VALUES(lots_id_seq.NEXTVAL, LOWER(in_code), l_status);

    DBMS_OUTPUT.PUT_LINE('THE PARKING LOTS HAVE BEEN SET');

END;

## Parking lot availables

CREATE OR REPLACE FUNCTION parking_lot_released (in_code IN VARCHAR2)
RETURN NUMBER
IS
    out_available NUMBER;
BEGIN
    SELECT busy INTO out_available FROM lots WHERE LOWER(code) = LOWER(in_code);
    RETURN out_available;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE;
END;

## Booking a parking lot

CREATE OR REPLACE PROCEDURE parking_lot_booking (in_plate IN VARCHAR2, in_code IN VARCHAR2, in_booked_at IN VARCHAR2)
IS
    l_vehicule_id vehicules.id%TYPE;
    l_lot_id lots.id%TYPE;
BEGIN
    SELECT id INTO l_vehicule_id FROM vehicules WHERE LOWER(plate) = LOWER(in_plate);
    
    SELECT id INTO l_lot_id FROM lots WHERE LOWER(code) = LOWER(in_code);
    
    INSERT INTO timings (id, vehicule_id, lot_id, status, booked_at)
        VALUES (timings_id_seq.NEXTVAL, l_vehicule_id, l_lot_id, 'RESERVED', TO_DATE(in_booked_at, 'YYYY-MM-DD'));
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('ERR404: DATA NOT FOUND');
END;

## Cancelling a booking

CREATE OR REPLACE PROCEDURE parking_lot_cancel (in_timing_id IN VARCHAR2)
IS 
BEGIN
    
    UPDATE timings SET status = 'CANCELLED' WHERE id = in_timing_id;
    

    DBMS_OUTPUT.PUT_LINE('THE PARKING LOT HAS BEEN CANCELLED');

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('ERR404: DATA NOT FOUND');
END;

## Entering the parking lot

CREATE OR REPLACE PROCEDURE parking_lot_entry (in_plate IN VARCHAR2, in_code IN VARCHAR2)
IS
    l_vehicule_id vehicules.id%TYPE;
    l_lot_id lots.id%TYPE;
BEGIN
    SELECT id INTO l_vehicule_id FROM vehicules WHERE LOWER(plate) = LOWER(in_plate);
    
    SELECT id INTO l_lot_id FROM lots WHERE LOWER(code) = LOWER(in_code);

    UPDATE timings SET status = 'TAKEN', start_at = SYSDATE WHERE vehicule_id = l_vehicule_id AND lot_id = l_lot_id;
    IF sql%notfound THEN
        INSERT INTO timings (id, vehicule_id, lot_id, start_at, status)
        VALUES (timings_id_seq.NEXTVAL, l_vehicule_id, l_lot_id, SYSDATE, 'TAKEN');
    END IF;

    DBMS_OUTPUT.PUT_LINE('THE PARKING LOT HAS BEEN TAKEN');

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('ERR404: DATA NOT FOUND');
END;

## Pay the used timing

**Function to get the minutes spend into the parking lot**

CREATE OR REPLACE FUNCTION calculate_minutes(in_start_at IN VARCHAR2, in_end_at IN VARCHAR2)
RETURN NUMBER
IS
    out_minutes NUMBER;
BEGIN
    out_minutes := (TO_DATE(in_start_at, 'YYYY-MM-DD HH24:MI') - TO_DATE(in_end_at, 'YYYY-MM-DD HH24:MI')) * 24 * 60;
    RETURN out_minutes;
END;

CREATE OR REPLACE PROCEDURE payment_process (in_plate IN VARCHAR2)
IS
    l_vehicule_id vehicules.id%TYPE;
    l_timing_id timings.id%TYPE;
    
    l_price NUMBER := 2.50;
    l_minutes NUMBER;
    l_end_at DATE := SYSDATE;
    
    ex_start_date_null EXCEPTION;
BEGIN
    SELECT id INTO l_vehicule_id FROM vehicules WHERE LOWER(plate) = LOWER(in_plate);
    
    SELECT 
        id, 
        calculate_minutes(
            TO_CHAR(start_at, 'YYYY-MM-DD HH24:MI'), 
            TO_CHAR(l_end_at, 'YYYY-MM-DD HH24:MI')
        )
        INTO l_timing_id, l_minutes 
    FROM timings WHERE vehicule_id = l_vehicule_id;
    
    IF l_minutes IS NULL THEN
        RAISE ex_start_date_null;
    ELSE
        UPDATE timings SET 
            amount = l_minutes * l_price,
            paid = 1,
            end_at = l_end_at
        WHERE id = l_timing_id;
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('ERR404: DATA NOT FOUND');
    
    WHEN ex_start_date_null THEN
        DBMS_OUTPUT.PUT_LINE('ERR409: START AT IS NULL');
END;

Cursor
===

## Getting a Sales Report per day

Write a procedure consolidate the sales per day and insert into the entity related

CREATE OR REPLACE PROCEDURE update_report (in_start_at IN VARCHAR2)
IS
    c_amount timings.amount%TYPE;
    l_total timings.amount%TYPE := 0;
    
    CURSOR c_report IS
        SELECT amount FROM timings WHERE TO_DATE(TO_CHAR(start_at, 'YYYY-MM-DD'), 'YYYY-MM-DD') = TO_DATE(in_start_at, 'YYYY-MM-DD');
BEGIN
    OPEN c_report; 
	   LOOP 
	   FETCH c_report into c_amount; 
	      EXIT WHEN c_report%notfound; 
	      l_total := l_total + c_amount;
	   END LOOP; 
   CLOSE c_report;
   
   INSERT INTO sales_report VALUES(sales_report_id_seq.NEXTVAL, in_start_at, l_total);
END;

References
===

**References:**

* [Naming Conventions for PL/SQL](https://trivadis.github.io/plsql-and-sql-coding-guidelines/v4.0/2-naming-conventions/naming-conventions/)
* [HTTP Status Codes](https://www.restapitutorial.com/httpstatuscodes.html)