import java.sql.*;
import java.util.Scanner;

public class DatabaseProgram
{
    static final String USER = "STU21";
    static final String PASS = "teampontto";

    public static void main(String[] args)
    {
        Scanner reader = new Scanner(System.in);
        Connection conn = null;
        Statement stmt = null;

        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            System.out.println("JDBC Driver found!");
        }
        catch(ClassNotFoundException e)
        {
            System.out.println("JDBC Driver not found!");
            e.printStackTrace();
            return;
        }

        try
        {
            conn = DriverManager.getConnection("jdbc:oracle:thin:@toldb.oulu.fi:1521:toldb11", USER, PASS);
        }
        catch(SQLException e)
        {
            System.out.println("Database connection failed!");
            e.printStackTrace();
            return;
        }

        if (conn != null)
        {
            System.out.println("Database connection successful!");
            System.out.println("Available commands are -> 1: Show Items / 2: Show Customers:");
            int choice = reader.nextInt();
            System.out.println();

            if (choice == 1)
            {
                printItems(conn, stmt);
            }
            else if (choice == 2)
            {
                printCustomers(conn, stmt);
            }
            else
            {
                System.out.println("Invalid input!");
            }
        }
        else
        {
            System.out.println("Failed to make connection!");
        }

    }

    public static void printItems(Connection conn, Statement stmt)
    {
        try
        {
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT title, status FROM T_item";
            ResultSet rs = stmt.executeQuery(sql);

            while(rs.next())
            {
                String title = rs.getString("title");
                String status = rs.getString("status");
                System.out.println("Title: " + title);
                System.out.println("Status: " + status);
                System.out.println("--------------------");
            }

            rs.close();
            stmt.close();
            conn.close();
        }
        catch(SQLException se)
        {
            se.printStackTrace();
        }
    }

    public static void printCustomers(Connection conn, Statement stmt)
    {
        try
        {
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT name_fname, name_lname, ssn FROM T_customer";
            ResultSet rs = stmt.executeQuery(sql);

            while(rs.next())
            {
                String firstname = rs.getString("name_fname");
                String lastname = rs.getString("name_lname");
                String ssn = rs.getString("ssn");
                System.out.println("Name: " + firstname + " " + lastname);
                System.out.println("SSN: " + ssn);
                System.out.println("--------------------");
            }

            rs.close();
            stmt.close();
            conn.close();
        }
        catch(SQLException se)
        {
            se.printStackTrace();
        }
    }
}