#!/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 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),
		PRIMARY KEY(CUSTOMER_ID)
		);
		CREATE TABLE EMPLOYEE(
		Employee_ID NUMBER(10) PRIMARY KEY,
		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 Varchar2(25)
		);
		CREATE TABLE INGREDIENTS(
		INGREDIENTID Number(10) PRIMARY KEY,
		COFFEETYPE VARCHAR2(50),
		RATIOMILK NUMBER(2),
		RATIOSUGAR NUMBER(2),
		RATIOCREAM NUMBER(2),
		RATIOCOFFEE NUMBER(2),
		TEETYPE VARCHAR2(50)
		);
		CREATE TABLE ORDERS(
		CUSTOMERID NUMBER REFERENCES Customer(CUSTOMER_ID)
		);
		CREATE TABLE Supplier(
			SUPPLIER_NAME VARCHAR2(25) PRIMARY KEY,
			SUPPLIER_PHONENUMBER NUMBER(10),
			SUPPLIER_PRICE NUMBER(10),
			SUPPLIER_PRODUCT VARCHAR2(12)
			);

        	CREATE TABLE Supplies(
			INGREDIENT_ID NUMBER references INGREDIENTS(INGREDIENTID),
			QUANTITY NUMBER(2),
			SUPPLIER_NAME VARCHAR2(25) references Supplier(SUPPLIER_NAME)
			);

        	CREATE TABLE Vending_Machine(
			MACHINEID NUMBER(10) PRIMARY KEY,
			MACHINELOCATION NUMBER(6),
			MACHINESTATUS NUMBER(1),
			MACHINEORDERDATE NUMBER(8),
			MACHINEORDERID NUMBER REFERENCES Customer(CUSTOMER_ID)
			);

        	CREATE TABLE Website(
			WEBSITE_URL VARCHAR2(50) PRIMARY KEY,
			ORDERDATE NUMBER(8),
			ORDERID NUMBER REFERENCES Customer(CUSTOMER_ID),
			PRICE VARCHAR2(20),
			QUANTITY NUMBER(2),
			WEBSITESTATUS NUMBER(5),
			ADMINISTRATOR VARCHAR2(20)
			);
		CREATE TABLE ADMINSMACHINE(
		MACHINE_ID NUMBER references VENDING_MACHINE(Machine_ID),
		ASSIGNED_EMPLOYEE NUMBER references EMPLOYEE(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