import java.sql.*;
import java.io.IOException;
import java.net.Socket;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;

public class ServerThread extends Thread{

    Socket soc;
    InputStream iStream;
    OutputStream oStream;
    ObjectInputStream sIn;
    ObjectOutputStream sOut;
    Studente pDaClient;

    //variabili login
    String userId;
    String password;

    //variabili soggetto
    String metodo;
    String nominativo;
    String via;
    String civico;
    String CAP;
    String citta;
    String codiceFiscale;
    String partitaIva;
    String nomeRapp;
    String cognomeRapp;
    String cfRapp;
    int idAttivita;



    public ServerThread(Socket s){
        soc = s;
    }

	public void run(){
        // apertura canale di comunicazione
        System.out.println(getName() + ": apertura canale di comunicazione...");

        try{
            iStream = soc.getInputStream();
            oStream = soc.getOutputStream();
            sIn = new ObjectInputStream(iStream);
            sOut = new ObjectOutputStream(oStream);
            System.out.println(getName()+ ": ...canale di comunicazione aperto.");

            // legge username e password
            userId = (String)sIn.readObject();
            password = (String)sIn.readObject();



            System.out.println(getName() + ": Ricevute credenziali di accesso.");
            // controlliamo le credenziali
            try {
    			Class.forName("com.mysql.jdbc.Driver");
    			System.out.println("Caricamento Diver");
    		}catch(ClassNotFoundException e) {

    		}

    		String url = "jdbc:mysql://localhost:3306/scrum?user=root&password=";

    		System.out.println("Connessione al database in corso...");
    		try (Connection connection = DriverManager.getConnection(url)) {
    		    System.out.println("Database connesso!");
    		    Statement stm = connection.createStatement();
    		    //controllo credenziali

    		    ResultSet rs = stm.executeQuery("SELECT idStudente, password FROM studente");

    		    while (rs.next()) {
    		    	if(rs.getString("idStudente").equals(userId) && rs.getString("password").equals(password)) {
    		    		System.out.println("Benvenuto, " + userId + ", sei uno studente ");
    		    		sOut.writeObject("stud");
    		    		sOut.flush();

    		    	}
    		    }
    		    connection.close();
    		    } catch (SQLException e) {
        		  throw new IllegalStateException("Impossibile connettersi al database!", e);
        		}

    		    try (Connection connection = DriverManager.getConnection(url)) {
        		    System.out.println("Database connesso!");

        		    Statement stm = connection.createStatement();
        		    ResultSet rs = stm.executeQuery("SELECT idAmministratore, password FROM amministratore");

	    		while (rs.next()) {
    		    	if(rs.getString("idAmministratore").equals(userId) && rs.getString("password").equals(password)) {
    		    		System.out.println("Benvenuto, " + userId + ", sei un amministratore");
    		    		sOut.writeObject("amm");
    		    		sOut.flush();

    		    	}else {
    		    		sOut.writeObject("Errore");
    		    	}
	    		}
	    		connection.close();
    		    } catch (SQLException e) {
        		  throw new IllegalStateException("Impossibile connettersi al database!", e);
        		}
	    		// legge dati soggetto
	    		metodo = (String)sIn.readObject();
	            nominativo = (String)sIn.readObject();
	            via = (String)sIn.readObject();
	            civico = (String)sIn.readObject();
	            CAP =  (String)sIn.readObject();
	            citta =  (String)sIn.readObject();
	            codiceFiscale = (String)sIn.readObject();
	            partitaIva =  (String)sIn.readObject();
	            nomeRapp =  (String)sIn.readObject();
	            cognomeRapp =  (String)sIn.readObject();
	            cfRapp = (String)sIn.readObject();


	    		// 	Query per inserire dati soggetto
	            try (Connection connection = DriverManager.getConnection(url)) {
        		    System.out.println("Database connesso!");

        		    Statement stm = connection.createStatement();
	            if(metodo.equals("insert")) {
	    		stm.executeUpdate("INSERT INTO soggetto (Nominativo,via,civico,CAP,città,codiceFiscale,partitaIva,nomeRapp,cognomeRapp,cfRapp) VALUES('"+ nominativo + "','" + via +"','" + civico +"','"+ CAP + "','"+ citta + "','" + codiceFiscale + "','" + partitaIva + "','" + nomeRapp + "','" + cognomeRapp + "','" + cfRapp + "');");

	    		}
	            connection.close();
    		    } catch (SQLException e) {
        		  throw new IllegalStateException("Impossibile connettersi al database!", e);
        		}
	            try (Connection connection = DriverManager.getConnection(url)) {
        		    System.out.println("Database connesso!");

        		    Statement stm = connection.createStatement();
	    		if(metodo.equals("modifica")) {
	    		idAttivita = Integer.parseInt((String)sIn.readObject());
	    		String query = "UPDATE soggetto SET Nominativo = '"+ nominativo + "',via = '"+ via +"', civico = '" +civico +"',CAP = '" + CAP + "',città = '" + citta + "',codiceFiscale = '"+ codiceFiscale + "',partitaIva = '" +partitaIva+"',nomeRapp = '" + nomeRapp + "',cognomeRapp = '" + cognomeRapp + "',cfRapp = '" + cfRapp + "' WHERE idAttività = " + idAttivita;
                stm.executeUpdate(query);
	    		}
	    		connection.close();
	            } catch (SQLException e) {
	            	throw new IllegalStateException("Impossibile connettersi al database!", e);
	            }






        } catch(IOException | ClassNotFoundException e){
            System.out.println(e);
        }



        }

    }