/*
 * 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 classes;

import beans.Persona;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.http.HttpSession;

/**
 *
 * @author Luser
 */
public class Validate {

    private static Validate instance;

    private static Connection connection;

    private Validate() {
        try {
            //loading drivers for mysql
//            Class.forName("com.mysql.jdbc.Driver");
            Class.forName("org.mariadb.jdbc.Driver");
            //creating connection with the database
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/dbchaol", "dbChaol", "uChaolX");
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static Validate getInstance() {
        if (instance == null) {
            instance = new Validate();
        }
        return instance;
    }

//   public boolean checkUser(String email, String pass) {
    public int checkUser(String email, String pass) {
        int st = -100;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("select * from tmlogin where cmloginuser=? and cmloginpass=?");
            ps.setString(1, email);
            ps.setString(2, pass);
            rs = ps.executeQuery();

            if (rs.next()) {
                st = rs.getInt("imloginid");
            }

        } catch (Exception e) {
            e.printStackTrace();
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, ex);
                }
            }

        }
        return st;
    }

    public int checkUserClient(String email, String pass) {
        int st = -100;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
//         PreparedStatement ps = connection.prepareStatement("select * from tcatcli where ccatcliemail=? and ccatclipass=?");
            ps = connection.prepareStatement("select * from tcatcli where ccatclicodigo=? and ccatclipass=?");
            ps.setString(1, email);
            ps.setString(2, pass);
            rs = ps.executeQuery();
            if (rs.next()) {
                st = rs.getInt("icatcliid");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            System.out.println("==================1Cerrando Conexion.........");
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            /*            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            } */
        }
        return st;
    }

    public int checkUserChofer(String email, String pass) {
        int st = -100;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("select * from tcatchof where ccatchofcodigo=? and ccatchofpass=?");
            ps.setString(1, email);
            ps.setString(2, pass);
            rs = ps.executeQuery();
            if (rs.next()) {
                st = rs.getInt("icatchofid");
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
            Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            System.out.println("==================1Cerrando Conexion.........");
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            /*            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            } */
        }
        return st;
    }

    public int checkUserTransportista(String email, String pass) {
        int st = -100;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("select * from tcattransp where ccattranspnomuser=? and ccattranspcontasena=?");
            ps.setString(1, email);
            ps.setString(2, pass);
            rs = ps.executeQuery();
            if (rs.next()) {
                st = rs.getInt("icattranspid");
            }

        } catch (SQLException ex) {
            System.out.println("===Transportista Error ServletLogin catch");
            ex.printStackTrace();
            Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                System.out.println("==================1Cerrando Conexion.........");
                if (rs != null) {
                    try {
                        System.out.println("===Transportista finally ServletLogin Close RS");
                        rs.close();
                    } catch (SQLException ex) {
                        Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
                if (ps != null) {
                    try {
                        System.out.println("===Transportista finally ServletLogin Close PS");
                        ps.close();
                    } catch (SQLException ex) {
                        Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
                if (connection.isClosed()) {
                    createInstance();
                }

                /*            if (connection != null) {
                try {
                connection.close();
                } catch (SQLException ex) {
                ex.printStackTrace();
                }
                } */
            } catch (SQLException ex) {
                Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return st;
    }

    public void createInstance() {
        instance = new Validate();
    }

    public String cargaConfiguracionInicialEmailDueno() {
        String email1 = "";
        Properties prop = new Properties();
        InputStream input = null;
        String wnomfile = "dbChaol.properties";
        try {
            //aqui debemos checar r si el archivo existe en caso contrario crearlo
            File archivo = new File(wnomfile);
            BufferedWriter bw;
            if (archivo.exists()) {
                input = new FileInputStream(wnomfile);
                // load a properties file
                prop.load(input);
                // get the property value and print it out
                System.out.println("PUERTOOOO:" + prop.getProperty("dbport2"));
                email1 = prop.getProperty("email1");
            } else {
                System.out.println("== Error Archivo no existe!!! " + wnomfile);
            }
//		input = new FileInputStream("config.properties");
        } catch (IOException ex) {
            ex.printStackTrace();
        } finally {
            if (input != null) {
                try {
                    input.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return email1;
    }

    public void insertPersonal(String codigo, String nombre, Date fechanacimiento) {
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("insert into tcat (codigo, nombre, fechnac) values (?,?,?)");
            ps.setString(1, codigo);
            ps.setString(2, nombre);
            ps.setDate(3, new java.sql.Date(fechanacimiento.getTime()));
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();

            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException exx) {
                    Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, exx);
                }
            }
        }
    }

    public List<Persona> getLista() {
        List<Persona> lista = new ArrayList<>();
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("select * from tcat");
            rs = ps.executeQuery();

            while (rs.next()) {
                Persona persona = new Persona(rs.getString("codigo"), rs.getString("nombre"), rs.getDate("fechnac"));
                lista.add(persona);
            }

        } catch (Exception e) {
            e.printStackTrace();
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Validate.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return lista;
    }

}