package server;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.net.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


public class ServerTCPThread extends Thread {
    Socket mySocket;

    public ServerTCPThread(Socket socket) {
        super(); // konstruktor klasy Thread
        mySocket = socket;
    }


    public void run() // program wątku
    {
        try {

            if (checkDriver("com.mysql.jdbc.Driver"))
                System.out.println(" ... OK");
            else
                System.exit(1);
            Connection con = getConnection("jdbc:mysql://", "localhost", 3306, "root", "");
            Statement st = createStatement(con);
            Statement st2 = createStatement(con);
            Statement st3 = createStatement(con);
            // próba wybrania bazy
            if (executeUpdate(st, "USE java;") == 0)
                System.out.println("Baza wybrana");
            else
                System.out.println("Baza nie istnieje!");

            String sql = null;
            String sql2 = null;
            String sql3 = null;

            ResultSet result = null;
            ResultSet result2 = null;
            ResultSet result3 = null;

            BufferedReader in = new BufferedReader(new InputStreamReader(mySocket.getInputStream()));
            PrintWriter out = new PrintWriter(new OutputStreamWriter(mySocket.getOutputStream()));

            while(true)
            {
            String str = in.readLine();

            if(str.equals("workerLog")) {
                String id = in.readLine();
                String pass = in.readLine();
                boolean ans = false;

                sql = "select login, password from employee";
                result = executeQuery(st, sql);

                while(result.next())
                {
                    if(id.equals(result.getString("login")) && pass.equals(result.getString("password")))
                        ans = true;
                }

                if(ans)
                {
                    out.println("correct");
                    out.flush();
                    sql = "select first_name from employee where login='"+id+"'";
                    result = executeQuery(st, sql);
                    result.next();
                    out.println(result.getString("first_name"));
                    out.flush();
                }
                else {
                    out.println("incorrect");
                    out.flush();
                }
            }

            if(str.equals("studentLog")) {
                String id = in.readLine();
                String pass = in.readLine();
                boolean ans = false;

                sql = "select login, password from student";
                result = executeQuery(st, sql);

                while(result.next())
                {
                    if(id.equals(result.getString("login")) && pass.equals(result.getString("password")))
                        ans = true;
                }

                if(ans)
                {
                    out.println("correct");
                    out.flush();
                    sql = "select first_name from student where login='"+id+"'";
                    result = executeQuery(st, sql);
                    result.next();
                    out.println(result.getString("first_name"));
                    out.flush();
                }
                else {
                    out.println("incorrect");
                    out.flush();
                }
            }

            if(str.equals("searchStudent")) {
                String pesel = in.readLine();
                boolean ans = false;

                sql = "select * from student where PESEL='"+pesel+"'";
                sql2 = "select * from signed_exams_t where PESEL='"+pesel+"'";
                sql3 = "select * from signed_exams where PESEL='"+pesel+"'";

                result = executeQuery(st, sql);
                result2 = executeQuery(st2, sql2);
                result3 = executeQuery(st3, sql3);


                if(result.isBeforeFirst())
                {
                    result.next();
                    if(pesel.equals(result.getString("PESEL")))
                        ans = true;
                }
                else
                {
                    ans = false;
                }


                if(ans)
                {
                    out.println("correct");
                    out.flush();
                    out.println(result.getString("first_name"));
                    out.flush();
                    out.println(result.getString("last_name"));
                    out.flush();
                    out.println(result.getString("address"));
                    out.flush();


                    if(Integer.parseInt(result.getString("osk_course_status")) == 1)
                    {
                        out.println("1");
                        out.flush();
                    }
                    else
                    {
                        out.println("0");
                        out.flush();
                    }
                    if(result2.next())
                    {
                            out.println(result2.getString("date"));
                            out.flush();
                            out.println(result2.getString("hour"));
                            out.flush();
                        if(result2.getString("score") == null)
                            {
                                out.println("0");
                                out.flush();
                            }
                        else if(result2.getInt("score") < 92)
                            {
                                out.println("1");
                                out.flush();
                            }
                        else
                            {
                                out.println("2");
                                out.flush();
                            }
                    }
                    else
                    {
                        out.println("0");
                        out.flush();
                    }

                    if(result3.next())
                    {
                            out.println(result3.getString("date"));
                            out.flush();
                            out.println(result3.getString("hour"));
                            out.flush();
                    }
                    else
                    {
                        out.println("0");
                        out.flush();
                    }

                    if(Integer.parseInt(result.getString("pay_texam_status")) == 1)
                    {
                        out.println("1");
                        out.flush();
                    }
                    else
                    {
                        out.println("0");
                        out.flush();
                    }
                    if(Integer.parseInt(result.getString("pay_pexam_status")) == 1)
                    {
                        out.println("1");
                        out.flush();
                    }
                    else
                    {
                        out.println("0");
                        out.flush();
                    }

                }
                else
                {
                    out.println("incorrect");
                    out.flush();
                }
            }

            if(str.equals("searchExamPesel"))
            {
                String pesel = in.readLine();
                boolean ans = false;

                sql = "select * from signed_exams where pesel='"+pesel+"'";
                result = executeQuery(st, sql);

                if(result.isBeforeFirst())
                {
                    result.next();
                    if(pesel.equals(result.getString("PESEL")))
                        ans = true;
                }
                else
                {
                    ans = false;
                }

                if(ans)
                {
                    out.println("correct");
                    out.flush();

                    out.println(result.getInt("id"));
                    out.flush();
                    out.println(result.getString("date"));
                    out.flush();
                    out.println(result.getLong("PESEL"));
                    out.flush();
                    out.println(result.getString("first_name"));
                    out.flush();
                    out.println(result.getString("last_name"));
                    out.flush();
                    out.println(result.getInt("vehicle_id"));
                    out.flush();
                    out.println(result.getInt("examiner_id"));
                    out.flush();
                }

                else
                {
                    out.println("incorrect");
                    out.flush();
                }

            }

            if(str.equals("searchExamAll"))
            {

            }

            if(str.equals("searchExamFrom"))
            {

            }
            }
            //mySocket.close();

        } catch (Exception e) {
            System.err.println(e);
        }
    }


    @SuppressWarnings("deprecation")
    public static boolean checkDriver(String driver) {
        // LADOWANIE STEROWNIKA
        //System.out.print("Sprawdzanie sterownika:");
        try {
            Class.forName(driver).newInstance();
            return true;
        } catch (Exception e) {
            System.out.println("Blad przy ladowaniu sterownika bazy!");
            return false;
        }
    }

    public static Connection getConnection(String kindOfDatabase, String adres, int port, String userName, String password) {

        Connection conn = null;
        Properties connectionProps = new Properties();
        connectionProps.put("user", userName);
        connectionProps.put("password", password);
        try {
            conn = DriverManager.getConnection(kindOfDatabase + adres + ":" + port + "/",
                    connectionProps);
        } catch (SQLException e) {
            System.out.println("Błąd połączenia z bazą danych! " + e.getMessage() + ": " + e.getErrorCode());
            System.exit(2);
        }
        //System.out.println("Połączenie z bazą danych: ... OK");
        return conn;
    }


    private static Statement createStatement(Connection connection) {
        try {
            return connection.createStatement();
        } catch (SQLException e) {
            System.out.println("Błąd createStatement! " + e.getMessage() + ": " + e.getErrorCode());
            System.exit(3);
        }
        return null;
    }


    @SuppressWarnings("unused")
    private static void closeConnection(Connection connection, Statement s) {
        System.out.print("\nZamykanie polaczenia z bazą:");
        try {
            s.close();
            connection.close();
        } catch (SQLException e) {
            System.out
                    .println("Bląd przy zamykaniu polączenia z bazą! " + e.getMessage() + ": " + e.getErrorCode());;
            System.exit(4);
        }
        System.out.print(" zamknięcie OK");
    }


    private static ResultSet executeQuery(Statement s, String sql) {
        try {
            return s.executeQuery(sql);
        } catch (SQLException e) {
            System.out.println("Zapytanie nie wykonane! " + e.getMessage() + ": " + e.getErrorCode());
        }
        return null;
    }

    private static int executeUpdate(Statement s, String sql) {
        try {
            return s.executeUpdate(sql);
        } catch (SQLException e) {
            System.out.println("Zapytanie nie wykonane! " + e.getMessage() + ": " + e.getErrorCode());
        }
        return -1;
    }


    @SuppressWarnings("unused")
    private static void printDataFromQuery(ResultSet r) {
        ResultSetMetaData rsmd;
        try {
            rsmd = r.getMetaData();
            int numcols = rsmd.getColumnCount(); // pobieranie liczby kolumn
            // wyswietlanie nazw kolumn:
            for (int i = 1; i <= numcols; i++) {
                System.out.print("\t" + rsmd.getColumnLabel(i) + "\t|");
            }
            System.out
                    .print("\n____________________________________________________________________________\n");

            // wyswietlanie kolejnych rekordow:
            while (r.next()) {
                for (int i = 1; i <= numcols; i++) {
                    Object obj = r.getObject(i);
                    if (obj != null)
                        System.out.print("\t" + obj.toString() + "\t|");
                    else
                        System.out.print("\t");
                }
                System.out.println();
            }
        } catch (SQLException e) {
            System.out.println("Bląd odczytu z bazy! " + e.getMessage() + ": " + e.getErrorCode());
        }
    }

    public static void sqlGetDataByName(ResultSet r) {
        System.out.println("Pobieranie danych z wykorzystaniem nazw kolumn");
        try {
            ResultSetMetaData rsmd = r.getMetaData();
            int numcols = rsmd.getColumnCount();
            // Tytul tabeli z etykietami kolumn zestawow wynikow
            for (int i = 1; i <= numcols; i++) {
                System.out.print(rsmd.getColumnLabel(i) + "\t|\t");
            }
            System.out
            .print("\n____________________________________________________________________________\n");
            while (r.next()) {
                int size = r.getMetaData().getColumnCount();
                for(int i = 1; i <= size; i++){
                    switch(r.getMetaData().getColumnTypeName(i)){
                    case "INT":
                        System.out.print(r.getInt(r.getMetaData().getColumnName(i)) + "\t|\t");
                        break;
                    case "DATE":
                        System.out.print(r.getDate(r.getMetaData().getColumnName(i)) + "\t|\t");
                        break;
                    case "VARCHAR":
                        System.out.print(r.getString(r.getMetaData().getColumnName(i)) + "\t|\t");
                        break;
                    default:
                        System.out.print(r.getMetaData().getColumnTypeName(i));
                    }
                }
                System.out.println();
            }
        } catch (SQLException e) {
            System.out.println("Bląd odczytu z bazy! " + e.getMessage() + ": " + e.getErrorCode());
        }
    }

}