/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package tcpserver;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;

import java.net.ServerSocket;
import java.net.Socket;
import java.net.SocketException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashSet;
import java.util.StringTokenizer;

/**
 *
 * @author Mariusz
 */
public class TCPServer implements Runnable {

    /**
     * @param args the command line arguments
     * @throws java.io.IOException
     */

    public String getMessage(String login){
        String result="";
        String id=String.valueOf(getLoginId(login));

        String sSQL = " SELECT LOGIN, DATE, BODY FROM  messages, users WHERE " ;
               sSQL+= " id_from =" + id + "AND users.id = messages.id_to ORDER BY date";

        Statement st;
        Connection conn;
        ResultSet rs;

        try {
            conn = DriverManager.getConnection("jdbc:derby:db;create=true");
            st = conn.createStatement();
            // wstaw llogin do bazy
            rs = st.executeUpdate(sSQL);

            result += rs.getString(1)+" " +rs.getString(2)+" "+rs.getString(3);

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






        return result;
    }
    public int saveMessage(String login, String sendTo, String message){

        String id_from = String.valueOf(getLoginId(login));
        String id_to = String.valueOf(getLoginId(sendTo));

        Statement st;
        Connection conn;
        ResultSet rs;
        String d = simpleDateHere.forma(new Date());


        String sSQL = "INSERT INTO messages (id_from, id_to, mdate, body)";
               sSQL +="VALUES ( " + id_from + ", " +id_to + ", "+ d + " + message +")";

        try {
            conn = DriverManager.getConnection("jdbc:derby:db;create=true");
            st = conn.createStatement();
            // wstaw llogin do bazy
            st.executeUpdate(sSQL);

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

        return 0;
    }

    public int registerLogin(String login){

        int id=getLoginId(login);

        Statement st;
        Connection conn;
        ResultSet rs;


        System.out.println("registerLogin: id="+ String.valueOf(id)) ;

        if (id==0){
           System.out.println("registerLogin: "+ login) ;
            try {
                conn = DriverManager.getConnection("jdbc:derby:db;create=true");
                st = conn.createStatement();
                // wstaw llogin do bazy
                st.executeUpdate("INSERT INTO users (login) VALUES ('" + login+"')");

            }
            catch (SQLException ex) {
                System.out.println("SQLException : "+ ex) ;
                id=0;
            }
        }
        else id=1;
        return id;

    }



    public int getLoginId(String login){

        Statement st;
        Connection conn;
        ResultSet rs;
        int id=0;

        try {
            conn = DriverManager.getConnection("jdbc:derby:db;create=true");
            st = conn.createStatement();
            rs = st.executeQuery("SELECT id FROM users WHERE login='"+login+"'");

            if (rs.next()){
                id =rs.getInt("id");

            }
        }
        catch (SQLException ex) {

        }

        return id;
    }



    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {

        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

        Connection conn = DriverManager.getConnection("jdbc:derby:db;create=true");

        Statement st = conn.createStatement();

        try {
            st.execute(
            "CREATE TABLE users (" +
            "id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
            "login VARCHAR(24) NOT NULL)"
            );
            st.executeUpdate("INSERT INTO users (login) VALUES ('Szymon')");
        }
        catch (SQLException ex) {
        }


        try {
            st.execute(
            "CREATE TABLE messages (" +
            "id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
            "id_from INTEGER NOT NULL, " +
            "id_to INTEGER NOT NULL, " +
            "date DATE NOT NULL, " +
            "body VARCHAR(2048)"+
            ")"
            );

        }
        catch (SQLException ex) {
            System.out.println("CREATE TABLE messages: " + ex);
        }

        ResultSet rs = st.executeQuery("SELECT id,login FROM users ORDER BY login");
        while(rs.next()) {
            System.out.println(rs.getInt("id") + ";" + rs.getString("login"));
        }
        ServerSocket ssock = new ServerSocket(8888);
        for(;;) {
            Socket sock = ssock.accept();
            TCPServer client = new TCPServer(sock);
            clients.add(client);
            new Thread(client).start();
        }
    }
    private static HashSet<TCPServer> clients = new HashSet<>();
    private Socket sock;
    private String login = null;
    private String sendTo = null;
    private PrintWriter out = null;

    private TCPServer(Socket sock) throws IOException {
        this.sock = sock;
        out = new PrintWriter(sock.getOutputStream(), true);
    }

    @Override
    public void run() {
        try {

	BufferedReader in = new BufferedReader(new InputStreamReader(sock.getInputStream()));
        for(;;) {
            String s = null;
            try {
                s = in.readLine();
            } catch(SocketException e) {
                break;
            }
            if(s == null) break;
            /*
                interpretation of a command/data sent from clients
            */
            if(s.charAt(0) == '/') {
                // out.println("You entered a command " + s);
                StringTokenizer st = new StringTokenizer(s);
                String cmd = st.nextToken();
                switch(cmd) {
                    case "/login":
                        // zalogować się mozna gdy login jest juz w bazie zarejestrowany
                        login = st.nextToken();
                        if (getLoginId(login)!=0) {
                            System.out.println("/login: " + login);
                            out.println("OK " + login);
                        }
                        else {
                            out.println(login + " not registered");
                            login=null;

                        }

                        break;
                    case "/whoami":
                        if(login != null)
                            out.println(login);
                        else
                            out.println("You are not logged in");
                        break;
                    case "/to":
                        if(login != null) {
                            sendTo = st.nextToken();
                            out.println("OK " + login + " -> " + sendTo);
                        } else
                            out.println("You are not logged in");
                        break;
                    case "/exit":
                        sock.close();
                        clients.remove(this);
                        return;
                    case "/who":

                        int nli = 0;
                        for(TCPServer client: clients) {
                            if(client.login != null) {
                                out.println(client.login + " -> " + (client.sendTo != null ? client.sendTo : ""));
                            }
                            else
                                nli++;
                        }
                        out.println("not logged in: " + nli);



                        break;
                    case "/reg":
                        // zarejestrowanie urzytkownika w rejestrze
                        //      sprawdzenie czy user jest w bazie
                        //      jezeli nie to insert user do bazy danych i zwroc jego id
                        //      jezeli jest to zwroc jego id
                        // + procedurea sprawdzająca
                        // + metoda check user zraza null gdy nie ma usera
                        // dopuszczenie w logowaniu tylko zarejestrowanych
                        // run now !!


                        // tableka na bufor
                        // |id_msg|id_from|id_to|msg_body|date|

                        login = st.nextToken();

                        if (registerLogin(login) ==0)
                               out.println("new login: "+login +" registered!");
                        else out.println(login +" already registered!");
                        login = null;
                        break;

                    default:
                        out.println("Unknown command " + cmd);
                }
            } else {
                if(login != null) {
                    // out.println("You entered data [" + s + "]");
                    int nrec = 0;
                    for(TCPServer client: clients) {
                        if(client.login != null && client.login.equals(sendTo)) {
                            client.out.println(login + " says: " + s);
                            nrec++;
                        }
                        else
                            // nie zalogowany odbiorca
                            // sprawdzanie czy jest w bazie login sendTo
                            if (getLoginId(sendTo) != 0 ) {
                                out.println(sendTo +" niezalogowany - zapisano wiadomość na później");
                                saveMessage(login,sendTo,s);
                            }
                    }

                    out.println("messages sent: " + nrec+ " " +s);
                } else {
                    out.println("You have to log in first");
                }
            }
        }
        } catch(IOException e) {}
        clients.remove(this);
    }

    @Override
    public String toString() {
        return sock + " [" + (login != null ? (login + "->" + (sendTo != null ? sendTo : "")) : "") + "]";
    }
}