#!/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 Customer(
		CUSTOMER_ID NUMBER(10),
		CUSTOMER_NAME Varchar2(50),
		PHONE NUMBER(10),
		LOGIN Varchar2(50),
		LOGIN_PASSWORD Varchar2(50),
		STREET_NAME Varchar2(50),
		CITY Varchar2(50),
		POSTALCODE VARCHAR2(6),
		PAYMENTINFO Number(20),
		ORDER_DATE Number(10),
		);
		CREATE TABLE EMPLOYEE(
		Employee_ID NUMBER(10),
		EMPLOYEE_NAME VARCHAR2(50),
		EMPLOYEE_PHONENUMBER NUMBER(10),
		EMPLOYEE_CITY VARCHAR2(50),
		EMPLOYEE_STREETNAME VARCHAR2(50),
		EMPLOYEE_POSTALCODE NUMBER(6),
		EMPLOYEE_POSITION VARCHAR2(25),
		EMPLOYEE_SALARY NUMBER();
		);
		CREATE TABLE INGREDIENTS(
		INGREDIENTID Number(10),
		COFFEETYPE VARCHAR2(50),
		RATIOMILK NUMBER(2),
		RATIOSUGAR NUMBER(2),
		RATIOCREAM NUMBER(2),
		RATIOCOFFEE(2),
		TEETYPE VARCHAR2(50),
		);
		CREATE TABLE ORDERS(
		CUSTOMERID NUMBER REFERENCES Customer(CUSTOMER_ID)
		);
		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));

		CREAT TABLE ADMINSMACHINE(
		MACHINE_ID NUMBER references VENDING_MACHINE(Machine_ID),
		ASSIGNED_EMPLOYEE references MEPLOYEE(EMPLOYEE_ID)
		);
		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 Customer;
		DROP TABLE Employee;
		DROP TABLE INGREDIENTS;
		DROP TABLE ORDERS;
		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