/*
 * Create two table that will hold data about coffees and their suppliers.
 */

import java.sql.*;
import java.io.*;
public class CreateCoffeesAndSuppliers {

  public static void main(String args[])  throws SQLException, IOException {
    // Load the Oracle JDBC driver
    try {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

      // Get UserName/Password
      BufferedReader inp   = new BufferedReader(
                                                new InputStreamReader(System.in));
      System.out.print("Enter User Name:");
      String user = inp.readLine();
      System.out.print("Enter Password:");
      String password = inp.readLine();

      // Connect to the database.
      // "ora" is the machine with the oracle server.
      // SQL Listener is listening on port 1521.
      // The instance of the database is "csdb".
            Connection con =
              DriverManager.getConnection ("jdbc:oracle:thin:" +
                                           user.trim()+"/"+password.trim()+
                                           "@ora.oswego.edu:1521:csdb");

            //Create the supplier table
            String createString = "create table SUPPLIERS " +
              "(SUP_ID int CONSTRAINT supp_pk PRIMARY KEY, " +
              "SUP_NAME varchar(40), " +
              "STREET varchar(40), " +
              "CITY varchar(20), " +
              "STATE char(2), ZIP char(5))";
            Statement stmt = con.createStatement();
            stmt.executeUpdate(createString);

            // Create the Coffees tabel.
            createString = "create table COFFEES " +
              "(COF_NAME varchar(32) CONSTRAINT coff_pk PRIMARY KEY, " +
              "SUP_ID int CONSTRAINT supp_fk REFERENCES SUPPLIERS (SUP_ID), " +
              "PRICE float, " +
              "SALES int, " +
              "TOTAL int)";
            stmt.executeUpdate(createString);

            stmt.close();
            con.close();

    } catch(SQLException ex) {
      System.err.println("SQLException: " + ex.getMessage());
    }
  }
}