package adbms;

import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import oracle.jdbc.OracleDriver;
import oracle.jdbc.driver.OraclePreparedStatement;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class SimpleJdbcApplication {
    public static final String  DRIVER_TYPE     = "thin";
    public static final String  HOST_NAME       = "127.0.0.1";
    public static final int     PORT            = 1521;
    public static final String  DATABASE_SID    = "ralphdb";
    public static final String  USER_NAME       = "scott";
    public static final String  PASSWORD        = "tiger";
    public static final String  URL             = String.format("jdbc:oracle:%s:@%s:%d:%s", DRIVER_TYPE, HOST_NAME, PORT, DATABASE_SID);

    private static Connection       connection;
    private static Scanner          read;
    private static PreparedStatement    insertStatement;
    private static PreparedStatement    deleteStatement;
    private static PreparedStatement    updateStatement;
    private static PreparedStatement    displayStatement;

    static {
        try {
            read = new Scanner(System.in);
            DriverManager.registerDriver(new OracleDriver());
            connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            insertStatement = connection.prepareStatement("insert into Coders values( Person(?,?,?) , ? , ?)");
            deleteStatement = connection.prepareStatement("delete from Coders where person_.name=?");
            updateStatement = connection.prepareStatement("update Coders set person_.name=? where person_.name=?");
            displayStatement = connection.prepareStatement("select * from Coders");
        } catch (Exception x) {
            System.out.println(x.getLocalizedMessage());
        }
    }

    public static void main(String... args) throws SQLException {
        while (true) {
            System.out.println("Enter your choice.");
            System.out.println("1. Insert");
            System.out.println("2. Delete");
            System.out.println("2. Update");
            System.out.println("4. Display");
            System.out.println("5. Enter a direct query");
            System.out.println("6. Exit");
            switch (read.nextInt()) {
                case 1:
                    insertCoder();
                    break;
                case 2:
                    removeCoder();
                    break;
                case 3:
                    updateCoder();
                    break;
                case 4:
                    displayAllCoders();
                    break;
                case 5:
                    processDirectQuerry();
                    break;
                default:
                    System.exit(0);
            }
        }
    }

    private static void processDirectQuerry() throws SQLException {
        System.out.println("Enter the query.");
        String query = read.nextLine();
        connection.createStatement().execute(query);
    }

    private static void displayAllCoders() throws SQLException {
        ResultSet resultSet = displayStatement.executeQuery();
        System.out.println("Name\tAddress\tDate of birth\tDescription\tKnown Languages");
        while (resultSet.next()) {
            System.out.println(resultSet.getString("person_.name"));
            System.out.println(resultSet.getString("person_.address"));
            System.out.println(resultSet.getDate("person_.dateOfBirth"));
            Clob description = resultSet.getClob("description");
            System.out.println(description.getSubString(1L, (int) description.length()));
            ARRAY oraArray = ((OracleResultSet) resultSet).getARRAY("knownLanguages_");
            String[] languagesArray = (String[]) oraArray.getArray();
            for (String language : languagesArray) {
                System.out.print(language + " ");
            }
            System.out.println();
        }
    }

    private static void updateCoder() throws SQLException {
        System.out.println("Enter the name of the coder whose name is to be updated.");
        String originalName = read.nextLine();
        System.out.println("ENter the new name.");
        String newName = read.nextLine();
        updateStatement.setString(1, newName);
        updateStatement.setString(2, originalName);
        updateStatement.executeUpdate();
    }

    private static void removeCoder() throws SQLException {
        System.out.println("Enter the name of coder to be removed.");
        String name = read.nextLine();
        deleteStatement.setString(1, name);
        deleteStatement.executeUpdate();
    }

    private static void insertCoder() throws SQLException {
        System.out.println("Enter the name, address and date of birth.");
        String name = read.nextLine();
        String address = read.nextLine();
        String dateOfBirth = read.nextLine();
        System.out.println("Enter a brief description.");
        String description = read.nextLine();
        List<String> languagesList = new ArrayList<String>();
        System.out.println("Enter names of known languages. (At most 10.) Type 'done' when you're done.");
        String token = null;
        int i = 0;
        while (i < 10 && !(token = read.nextLine()).equalsIgnoreCase("done")) {
            languagesList.add(token);
        }
        insertStatement.setString(1, name);
        insertStatement.setString(2, address);
        insertStatement.setDate(3, Date.valueOf(dateOfBirth));
        insertStatement.setString(4, description);
        String[] languagesArray = (String[]) languagesList.toArray();
        ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("KnownLanguages", connection);
        ARRAY oraArray = new ARRAY(arrayDescriptor, connection, languagesArray);
        ((OraclePreparedStatement) insertStatement).setARRAY(5, oraArray);
        insertStatement.executeUpdate();
    }
}