package be.xios.teacherapp.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;

import javax.swing.JList;

import be.xios.teacherapp.ui.BrowseModify;
import be.xios.teacherapp.variables.classes.Company;
import be.xios.teacherapp.variables.classes.Contact;
import be.xios.teacherapp.variables.classes.Coordinator;
import be.xios.teacherapp.variables.classes.Promotor;
import be.xios.teacherapp.variables.classes.Student;
import be.xios.teacherapp.variables.classes.Super;

public class SqlSend {
    private final String DRIVERSTR = "oracle.jdbc.driver.OracleDriver";
    private final String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:MINIDB";

    private Connection connection = null;
    private ResultSet resultSet = null;

    private PreparedStatement getAllStudents;
    private PreparedStatement getAllStudentsWithApplication;
    private PreparedStatement getAllInternships;
    private PreparedStatement getAllCompanies;
    private PreparedStatement getAllCoordinators;
    private PreparedStatement getAllContacts;
    private PreparedStatement getAllPromotors;
    private PreparedStatement getAllStudentTags;
    private PreparedStatement getAllCompanyTags;
    private PreparedStatement getAllTags;
    private PreparedStatement getAllEvaluations;
    private PreparedStatement getAllAcceptedStudents;

    private PreparedStatement getStudent;
    private PreparedStatement getInternship;
    private PreparedStatement getCompany;
    private PreparedStatement getCoordinator;
    private PreparedStatement getContact;
    private PreparedStatement getPromotor;
    private PreparedStatement getStudentTag;
    private PreparedStatement getCompanyTag;
    private PreparedStatement getTag;
    private PreparedStatement getEvaluation;

    public SqlSend() {
        try {
            Class.forName(DRIVERSTR);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(DATABASE_URL, "student", "xios");
            resultSet = connection.createStatement().executeQuery("SELECT student_id FROM student");
            createGetAllStatements();
            createGetSingleStatements();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void createGetAllStatements() throws SQLException{
        getAllStudents = connection.prepareStatement(
                "SELECT student_id, first_name || ' ' || last_name " +
                "FROM person p, student s " +
                "WHERE p.person_id = s.person_id");
        getAllStudentsWithApplication = connection.prepareStatement(
                "SELECT student_id, first_name || ' ' || last_name " +
                "FROM student, person " +
                "WHERE student.person_id = person.person_id " +
                    "AND preliminary_choice IS NOT NULL");
        getAllInternships = connection.prepareStatement(
                "SELECT internship.internship_id, q1.name || ' ' || internship.name " +
                "FROM internship, (SELECT name, company_id FROM company) q1 " +
                "WHERE q1.company_id = internship.company_id");
        getAllCompanies = connection.prepareStatement(
                "SELECT company_id, name " +
                "FROM company");
        getAllCoordinators = connection.prepareStatement(
                "SELECT coordinator_id, first_name || ' ' || last_name " +
                "FROM person p, coordinator c " +
                "WHERE p.person_id = c.person_id");
        getAllContacts = connection.prepareStatement(
                "SELECT contact_id, first_name || ' ' || last_name " +
                "FROM person p, contact c " +
                "WHERE p.person_id = c.person_id");
        getAllPromotors = connection.prepareStatement(
                "SELECT promotor_id, first_name || ' ' || last_name " +
                "FROM person p, promotor pr " +
                "WHERE p.person_id = pr.person_id");
        getAllStudentTags = connection.prepareStatement(
                "SELECT t.tag_id, t.tag_name " +
                "FROM student_tags s, tags t " +
                "WHERE student_id = ? " +
                    "AND s.tag_id = t.tag_id");
        getAllCompanyTags = connection.prepareStatement(
                "SELECT company.company_id, tags.tag_id, name " +
                "FROM company, company_tags, tags " +
                "WHERE company.company_id = company_tags.company_id " +
                    "AND company_tags.tag_id = tags.tag_id");
        getAllTags = connection.prepareStatement(
                "SELECT tag_id, tag_name " +
                "FROM tags");
        getAllEvaluations = connection.prepareStatement(
                "SELECT internship_id, first_name || ' ' || last_name || ' ' || evaluation.create_date " +
                "FROM person, student, internship, evaluation " +
                "WHERE person.person_id = student.person_id " +
                    "AND student.student_id = internship.student_id " +
                    "AND internship.internship_id = evaluation.internship_id");
        getAllAcceptedStudents = connection.prepareStatement("SELECT student.student_id, first_name, last_name " +
                " FROM student " +
                " LEFT JOIN person ON student.person_id = person.person_id " +
                " LEFT JOIN internship ON internship.student_id = student.student_id " +
                " WHERE internship.student_id is not null AND student.preliminary_choice is null");
    }

    private void createGetSingleStatements() throws SQLException {
        getStudent = connection.prepareStatement(
                "SELECT  s.student_id, p.first_name, p.last_name, p.address, p.country, p.gsm, p.email, p.title, s.ssn, s.birth_date, post.pc, post.city " +
                "FROM person p " +
                    "LEFT JOIN student s ON s.person_id = p.person_id " +
                    "LEFT JOIN postalcode post ON p.pc_id = post.pc_id " +
                "WHERE s.student_id = ?");
        getInternship = connection.prepareStatement(
                "SELECT internship_id, i.name, create_date, start_date, end_date, position_filled, " +
                    "p1.first_name || ' ' || p1.last_name AS Student, " +
                    "p2.first_name || ' ' || p2.last_name AS Coordinator, " +
                    "p3.first_name || ' ' || p3.last_name AS Contact, " +
                    "p4.first_name || ' ' || p4.last_name AS Promotor " +
                "FROM internship i, student s, person p1, coordinator c, person p2, contact co, person p3, promotor pr, person p4, company com " +
                "WHERE internship_id = ? " +
                    "AND i.student_id = s.student_id " +
                    "AND s.person_id = p1.person_id " +
                    "AND i.coordinator_id = c.coordinator_id " +
                    "AND c.person_id = p2.person_id " +
                    "AND i.contact_id = co.contact_id " +
                    "AND co.person_id = p3.person_id " +
                    "AND i.promotor_id = pr.promotor_id " +
                    "AND pr.person_id = p4.person_id " +
                    "AND i.company_id = com.company_id");
        getCompany = connection.prepareStatement(
                "SELECT name, address, p.pc || ' ' || p.city AS pc, email, phone, url " +
                "FROM company c, postalcode p " +
                "WHERE company_id = ?" +
                    "AND c.pc_id = p.pc_id");
        getCoordinator = connection.prepareStatement(
                "SELECT p.first_name || ' ' || p.last_name AS FullName, course_name " +
                "FROM coordinator c, person p " +
                "WHERE coordinator_id = ?" +
                    "AND p.person_id = c.person_id");
        getContact = connection.prepareStatement(
                "SELECT p.first_name || ' ' || p.last_name AS FullName, co.name " +
                "FROM contact c, person p, company co " +
                "WHERE contact_id = ? " +
                    "AND p.person_id = c.person_id " +
                    "AND c.company_id = co.company_id");
        getPromotor = connection.prepareStatement(
                "SELECT p.first_name || ' ' || p.last_name AS FullName, c.name " +
                "FROM promotor pr, person p, company c " +
                "WHERE promotor_id = ? " +
                    "AND p.person_id = pr.person_id " +
                    "AND pr.company_id = c.company_id");
        getStudentTag = connection.prepareStatement(
                "SELECT * " +
                "FROM student_tags " +
                "WHERE tag_id = ? " +
                    "AND person_id = ? ");
        getCompanyTag = connection.prepareStatement(
                "SELECT * " +
                "FROM company_tags " +
                "WHERE tag_id = ? " +
                    "AND company_id = ?");
        getTag = connection.prepareStatement(
                "SELECT * " +
                "FROM tags " +
                "WHERE tag_id = ?");
        getEvaluation = connection.prepareStatement(
                "SELECT * " +
                "FROM evaluation " +
                "WHERE evaluation_id = ?");
    }

    private void fillList (JList list) {
        String[][] temp = getData();
        ArrayList<Super> tempData = new ArrayList<Super>();
        for (int i = 0; i < temp.length; i++) {
            tempData.add(new Super(Integer.parseInt(temp[i][0])));
            tempData.get(tempData.size() - 1).setFullName(temp[i][1]);
        }
        list.setListData(tempData.toArray());
    }

    private String[][] getData () {
        ArrayList<String[]> returnData = new ArrayList<String[]>();
        try {
            ResultSetMetaData metaData = resultSet.getMetaData();
            int numberOfColumns = metaData.getColumnCount();
            while (resultSet.next()) {
                String[] temp = new String[numberOfColumns];
                for (int i = 1; i <= numberOfColumns; i++) {
                    temp[i - 1] = resultSet.getObject(i).toString();
                }
                returnData.add(temp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //uncomment to print array
        //System.out.println(Arrays.deepToString(returnData.toArray()));
        return returnData.toArray(new String[returnData.size()][]);
    }

    public void getAllStudents(JList list) {
        try {
            resultSet = getAllStudents.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        fillList(list);
    }

    public void getAllStudentsWithApplication(JList list) {
        try {
            resultSet = getAllStudentsWithApplication.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        fillList(list);
    }

    public void getAllInternships(JList list) {
        try {
            resultSet = getAllInternships.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        fillList(list);
    }

    public void getAllCompanies(JList list) {
        try {
            resultSet = getAllCompanies.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        fillList(list);
    }

    public void getAllCoordinators(JList list) {
        try {
            resultSet = getAllCoordinators.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        fillList(list);
    }

    public void getAllContacts(JList list) {
        try {
            resultSet = getAllContacts.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        fillList(list);
    }

    public void getAllPromotors(JList list) {
        try {
            resultSet = getAllPromotors.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        fillList(list);
    }

    public void getAllAcceptedStudents(JList list) {
        try {
            resultSet = getAllAcceptedStudents.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        fillList(list);
    }

    public void getAllStudentTags(int stu_id, BrowseModify r) {
        try {
            getAllStudentTags.setInt(1, stu_id);
            resultSet = getAllStudentTags.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void getStudent(int stu_id, BrowseModify r){
        try {
            getStudent.setInt(1, stu_id);
            resultSet = getStudent.executeQuery();
            resultSet.next();

            ResultSetMetaData metaData = resultSet.getMetaData();
            int numberOfColumns = metaData.getColumnCount();
            String [] a = new String[numberOfColumns];
            for (int i = 0; i < numberOfColumns; i++) {
                a[i] = (resultSet.getObject(i+1) == null)? "":resultSet.getObject(i+1).toString();
            }
            r.setPrVnaam(a[1]);
            r.setPrFnaam(a[2]);
            r.setPrAdress(a[3]);
            r.setPrCountry(a[4]);
            r.setPrGsm(a[5]);
            r.setPrEmail(a[6]);
            r.setPrTitle(a[7]);
            r.setStuSsn(a[8]);
            r.setStuBirthdate(a[9]);
            r.setPrPostC(a[10]);
            r.validate();
            //uncomment to print student array
            //System.out.println(Arrays.toString(a));
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public void getCoordinator(int co_id, BrowseModify r) {
        try {
            getCoordinator.setInt(1, co_id);
            resultSet = getCoordinator.executeQuery();
            resultSet.next();

            ResultSetMetaData metaData = resultSet.getMetaData();
            int numberOfColumns = metaData.getColumnCount();
            String [] a = new String[numberOfColumns];
            for (int i = 0; i < numberOfColumns; i++) {
                a[i] = (resultSet.getObject(i+1) == null)? "":resultSet.getObject(i+1).toString();
            }
            //TODO: set textfields
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void shutDownConnection () {
        try {
            resultSet.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}