package cs160.excel;

import java.sql.*;

public class ExcelSchoolDemo
{
    private static final String DRIVER =
        "com.nilostep.xlsql.jdbc.xlDriver";
    private static final String URL =
        "jdbc:nilostep:excel:G:\\programs2006\\ExcelDemo";

    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";

    private Connection conn;
    private PreparedStatement classesTaughtBy;
    private PreparedStatement studentsOf;
    private PreparedStatement classBySubject;
    private PreparedStatement insertStudent;
    private PreparedStatement studentById;
    private PreparedStatement deleteStudent;
    private PreparedStatement insertLink;
    private PreparedStatement deleteLink;

    public ExcelSchoolDemo()
    {
        try {
            openConnection();

            classesTaughtBy = conn.prepareStatement(
                "SELECT class_code, subject " +
                "FROM \"school.teacher\", " +
                "     \"school.class\" " +
                "WHERE teacher_last = ? " +
                "AND teacher_first = ? " +
                "AND teacher_id = class_teacher_id");

            studentsOf = conn.prepareStatement(
                "SELECT class_code, subject, " +
                "       student_id, student_last, " +
                "       student_first " +
                "FROM \"school.student\", " +
                "     \"school.teacher\", " +
                "     \"school.class\", " +
                "     \"school.student_class\" " +
                "WHERE teacher_last = ? " +
                "AND teacher_first = ? " +
                "AND class_teacher_id = teacher_id " +
                "AND class_code = link_class_code " +
                "AND student_id = link_student_id");

            classBySubject = conn.prepareStatement(
                "SELECT class_code, class_teacher_id, " +
                "       subject, room " +
                "FROM \"school.class\" " +
                "WHERE subject = ?");

            studentById = conn.prepareStatement(
                "SELECT student_id, student_last, " +
                "       student_first " +
                "FROM \"school.student\" " +
                "WHERE student_id = ?");

            insertStudent = conn.prepareStatement(
                "INSERT INTO \"school.student\" " +
                "VALUES (?,?,?)");

            deleteStudent = conn.prepareStatement(
                "DELETE FROM \"school.student\" " +
                "WHERE student_id = ?");

            insertLink = conn.prepareStatement(
                "INSERT INTO \"school.student_class\" " +
                "VALUES (?,?)");

            deleteLink = conn.prepareStatement(
                "DELETE FROM \"school.student_class\" " +
                "WHERE link_student_id = ?");
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private void openConnection()
        throws Exception
    {
        Class.forName(DRIVER).newInstance();
        conn = DriverManager.getConnection(
                         URL, USERNAME, PASSWORD);
    }

    private ResultSet classesTaughtBy(String last,
                                      String first)
        throws SQLException
    {
        // Set query parameters.
        classesTaughtBy.setString(1, last);
        classesTaughtBy.setString(2, first);

        // Do the query.
        return classesTaughtBy.executeQuery();
    }

    private ResultSet studentsOf(String last, String first)
        throws SQLException
    {
        // Set query parameters.
        studentsOf.setString(1, last);
        studentsOf.setString(2, first);

        // Do the query.
        return studentsOf.executeQuery();
    }

    private ResultSet classBySubject(String subject)
        throws SQLException
    {
        // Set query parameters.
        classBySubject.setString(1, subject);

        // Do the query.
        return classBySubject.executeQuery();
    }

    private ResultSet studentById(int id)
        throws SQLException
    {
        // Set query parameters.
        studentById.setInt(1, id);

        // Do the query.
        return studentById.executeQuery();
    }

    private void insertStudent(int id,
                               String last, String first)
        throws SQLException
    {
        // Set query parameters.
        insertStudent.setInt(1, id);
        insertStudent.setString(2, last);
        insertStudent.setString(3, first);

        // Do the insert.
        insertStudent.execute();
    }

    private boolean deleteStudent(int id)
        throws SQLException
    {
        // Find the student.
        ResultSet rs = studentById(id);
        if (!rs.next()) return false;

        // Set query parameters.
        deleteStudent.setInt(1, id);

        // Do the delete.
        deleteStudent.execute();
        deleteLink(id);

        return true;
    }

    private void insertLink(int studentId, int classCode)
        throws SQLException
    {
        // Set query parameters.
        insertLink.setInt(1, studentId);
        insertLink.setInt(2, classCode);

        // Do the insert.
        insertLink.execute();
    }

    private void deleteLink(int studentId)
        throws SQLException
    {
        // Set query parameters.
        deleteLink.setInt(1, studentId);

        // Do the delete.
        deleteLink.execute();
    }

    private void show(String last, String first)
    {
        try {
            System.out.println("\nThe classes taught by " +
                               first + " " + last + ":\n");
            print(classesTaughtBy(last, first));

            System.out.println("\nThe students taught by " +
                               first + " " + last + ":\n");
            print(studentsOf(last, first));
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private void add(int id, String last, String first,
                     String subject)
    {
        try {
            System.out.print("\nAdding student " + id +
                             ": " + first + " " + last +
                             " in subject " + subject +
                             "...");

            // Find the class by subject.
            ResultSet rs = classBySubject(subject);
            int classCodeToTake =
                    rs.next() ? rs.getInt("class_code") : 0;

            // Insert the student.
            insertStudent(id, last, first);

            // Insert the student-class link.
            if (classCodeToTake > 0) {
                insertLink(id, classCodeToTake);
            }

            System.out.println("added.");
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    private void delete(int id)
    {
        try {
            System.out.print("\nDeleting student " +
                             id + "...");

            // Delete the student.
            if (deleteStudent(id)) {
                System.out.println("deleted.");
            }
            else {
                System.out.println("not found.");
            }
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    private void print(ResultSet rs)
        throws Exception
    {
        ResultSetMetaData rsmd = rs.getMetaData();
        int colCount = rsmd.getColumnCount();

        // Print values.
        while (rs.next()) {
            for (int i = 0; i < colCount; ++i) {
                String value = rs.getString(i + 1);
                System.out.print(value + "\t");
            }
            System.out.println();
        }
    }

    private void closeConnection()
    {
        if (conn != null) {
            try {
                conn.close();
            }
            catch (SQLException ignore) {}
        }
    }

    public static void main(String[] args)
    {
        String arg = args[0];
        ExcelSchoolDemo demo = new ExcelSchoolDemo();

        String teacherLast  = "Lane";
        String teacherFirst = "John";

        int studentId = 1999;
        String studentLast  = "Zelkowitsky";
        String studentFirst = "Katerina";

        String subject = "Software engineering";

        if (arg.equalsIgnoreCase("-show")) {
            demo.show(teacherLast, teacherFirst);
        }
        else if (arg.equalsIgnoreCase("-add")) {
            demo.add(studentId, studentLast,
                     studentFirst, subject);
        }
        else if (arg.equalsIgnoreCase("-delete")) {
            demo.delete(studentId);
        }

        demo.closeConnection();
    }
}