import oracledb
import getpass

# default values for the host and database name
default_host = 'dbs3.cs.umb.edu:1521'
default_database = 'dbs3'

# read input arguments: username, pasword, db host, db name
hostname = input(f'Enter the hostname ({default_host}): ')
# if nothing is entered, use the default host - ignore whitespaces
if len(hostname.strip()) == 0:
    hostname = default_host

database = input(f'Enter the database ({default_database}): ')
# if nothing is entered, use the default database - ignore whitespaces
if len(database.strip()) == 0:
    database = default_database

username = input("Enter your Oracle username: ")

# getpass hides the user's input when typed
password = getpass.getpass("Enter your Oracle password: ")

# dsn is in the form of <hostname>/<database>.
# Construct the dsn, ensuring the right format.
dsn = hostname + database if hostname[-1] == '/' else hostname + '/' + database

print(f'Trying to connect to Oracle Server \"{dsn}\"')

# Establish a connection to DBMS
connection = oracledb.connect(user=username, password=password, dsn=dsn)
print('Acquired a connection to the DB ...')

# creates a cursor that will be needed to access the databases
curs = connection.cursor()

# Check all the tables of this user
print("\nTables created by this user:")
for table in curs.execute("SELECT table_name FROM user_tables"):
    print(table[0])

input('============================================================\nPress return to continue...')

# Drop the existing tables if they exist
tables = ['READS', 'ARTICLES', 'STUDENTS']
for table in tables:
    curs.execute(f"SELECT COUNT(*) FROM user_tables WHERE table_name = '{table}'")
    if curs.fetchone()[0] == 1:
        print(f"The table ({table}) exists.  Dropping table.")
        curs.execute(f"DROP TABLE {table} CASCADE CONSTRAINTS")
        print(f"{table} dropped.")
    else:
        print(f"The table ({table}) does not exist.")
input('============================================================\nPress return to continue...')

# Recreate the tables
print("Recreating the Students table..")
curs.execute("""
    CREATE TABLE Students (
        sid INT PRIMARY KEY,
        name VARCHAR(30) NOT NULL,
        city VARCHAR(30) NOT NULL,
        state VARCHAR(2) NOT NULL,
        age REAL NOT NULL,
        gpa REAL CHECK (gpa BETWEEN 1 AND 4) NOT NULL
    )
""")
print("Students table created.")

print("Recreating the Articles table..")
curs.execute("""
    CREATE TABLE Articles (
        aid INT PRIMARY KEY,
        title VARCHAR(50) NOT NULL,
        author VARCHAR(30) NOT NULL,
        pubyear INT NOT NULL
    )
""")
print("Articles table created.")

print("Recreating the Reads table..")
curs.execute("""
    CREATE TABLE Reads (
        aid INT NOT NULL,
        sid INT NOT NULL,
        rday DATE NOT NULL,
        PRIMARY KEY (aid, sid),
        FOREIGN KEY (aid) REFERENCES Articles(aid),
        FOREIGN KEY (sid) REFERENCES Students(sid)
    )
""")
print("Reads table created.")
input('============================================================\nPress return to continue...')

# Insert two values into each table
print("Inserting two values into Students table..")
curs.execute("INSERT INTO Students VALUES (1, 'Bob', 'Boston', 'MA', 21, 3.7)")
curs.execute("INSERT INTO Students VALUES (2, 'Christian', 'Goldsboro', 'NC', 27, 3.2)")

print("Inserting two values into Articles table..")
curs.execute("INSERT INTO Articles VALUES (101, 'Artificial Intelligence', 'Wei', 2015)")
curs.execute("INSERT INTO Articles VALUES (102, 'Algorithm Analysis', 'Haspel', 2024)")

print("Inserting two values into Reads table..")
curs.execute("INSERT INTO Reads VALUES (101, 1, DATE '2024-11-25')")
curs.execute("INSERT INTO Reads VALUES (102, 2, DATE '2024-10-31')")
input('============================================================\nPress return to continue...')

# Run the individual queries
print("Retrieving all articles:")
for row in curs.execute("SELECT * FROM Articles"):
    print(row)
input('============================================================\nPress return to continue...')

print("Retrieving all students:")
for row in curs.execute("SELECT * FROM Students"):
    print(row)
input('============================================================\nPress return to continue...')

print("Retrieving all records from Reads:")
for row in curs.execute("SELECT * FROM Reads"):
    print(row)
input('============================================================\nPress return to continue...')

# Commit the changes
print("Committing transaction:")
connection.commit()

# Close the connection
print("Closing connection.")
connection.close()