#!/bin/bash

DBUSER='chyuen'
DBPASSWORD='12242466'
DB='oracle.scs.ryerson.ca'

num=0
table_name=""
data=""
echo "Enter 1. to create SQL tables"
echo "Enter 2. to drop SQL tables"
echo "Enter 3. to add data into a SQL table"
echo "Enter 4. to view SQL tables"
echo "Enter 5. exit"
read num


while [ $num -ne 5 ]
do
    if [ $num -eq 1 ]
    then
        echo "You entered $num"
        sqlplus64 "chyuen/12242466@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=oracle.scs.ryerson.ca)(Port=1521))(CONNECT_DATA=(SID=orcl)))" << EOF
        	CREATE TABLE Supplier(
			SUPPLIER_NAME VARCHAR2(25),
			SUPPLIER_PHONENUMBER NUMBER(10),
			SUPPLIER_PRICE NUMBER(10),
			SUPPLIER_PRODUCT VARCHAR2(12));
        	CREATE TABLE Supplies(
			INGREDIENT_ID NUMBER(12),
			QUANTITY NUMBER(2),
			SUPPLIER_NAME VARCHAR2(50));
        	CREATE TABLE Vending_Machine(
			MACHINEID NUMBER(10),
			MACHINELOCATION NUMBER(6),
			MACHINESTATUS NUMBER(1),
			MACHINEORDERDATE NUMBER(8),
			MACHINEORDERID NUMBER(10));
        	CREATE TABLE Website(
			WEBSITE_URL VARCHAR2(50),
			ORDERDATE NUMBER(8),
			ORDERID NUMBER(10),
			PRICE VARCHAR2(20),
			QUANTITY NUMBER(2),
			WEBSITESTATUS NUMBER(5),
			ADMINISTRATOR VARCHAR2(20));
        exit;
EOF
    fi
    if [ $num -eq 2 ]
    then
        echo "Your entered $num"
            sqlplus64 "chyuen/12242466@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=oracle.scs.ryerson.ca)(Port=1521))(CONNECT_DATA=(SID=orcl)))" << EOF2
        DROP TABLE Supplier;
        DROP TABLE Supplies;
        DROP TABLE Vending_Machine;
        DROP TABLE Website;
        exit;
EOF2
    fi
    if [ $num -eq 3 ]
    then
        echo "Insert the table you want to add data to"
        read table_name
        echo "What do you want to add in $table_name?"
        read data
                sqlplus64 "chyuen/12242466@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=oracle.scs.ryerson.ca)(Port=1521))(CONNECT_DATA=(SID=orcl)))" << EOF
        INSERT INTO $table_name Values ('$data');
        exit;
EOF

    fi
    echo "Enter 1. to create SQL tables"
    echo "Enter 2. to drop SQL tables"
    echo "Enter 3. to add data into a SQL table"
    echo "Enter 4. to view SQL tables"
    echo "Enter 5. exit"
    read num

    if [ $num -eq 4 ]
    then
        echo "Which table do you want to view?"
        read table_name
                sqlplus64 "chyuen/12242466@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=oracle.scs.ryerson.ca)(Port=1521))(CONNECT_DATA=(SID=orcl)))" << EOF
        SELECT * FROM $table_name;
        exit;
EOF
    fi
done