package tripletas;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Rommel Augusto Gutiérrez Roa (ragutierrez)
 */
public class Tripletas {

    DBConnection conn = new DBConnection();
    DBConnection conn2 = new DBConnection();

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {

        Tripletas obj = new Tripletas();
        Object[][] tablaIds = obj.columnasIdTabla();
//        obj.imprimirMatriz(tablaIds);

        obj.obtenerTripletas(tablaIds);
    }

    public Object[][] columnasIdTabla() {
        conn.initDatosLectura();
        conn.setSchema("infodoc");
        conn.crear();
        ResultSet rs = null;
        Object[][] datosTablas = null;
//        conn = new DBConnection();

        int numColumnas, numFilas;

        try {

            rs = conn.query("SELECT DISTINCT table_schema, table_name,column_name, column_comment "
                    + "FROM INFORMATION_SCHEMA.COLUMNS "
                    + "WHERE COLUMN_KEY = 'PRI' "
                    + "AND table_schema='" + conn.getSchema() + "' "
                    + "ORDER BY table_schema");

            numColumnas = rs.getMetaData().getColumnCount();
            rs.last();
            numFilas = rs.getRow();

            datosTablas = new Object[numFilas][numColumnas];

            rs.beforeFirst();
            int fila = -1;
            while (rs.next()) {
                fila++;
                for (int i = 0; i < numColumnas; i++) {
                    datosTablas[fila][i] = rs.getObject(i + 1);
                }
            }
        } catch (Exception e) {
            System.out.println("Ha ocurrido el siguiente error:\n" + e);
        }
        conn.destruir();
        return datosTablas;
    }

    public void obtenerTripletas(Object[][] tablaIds) {
        try {
            //        String tripletas = "";

            conn2.initDatosEscritura();
            conn2.setSchema("tripletas_db");
            conn2.crear();
            conn2.insertUpdate("delete from datos_tripletas");
            conn2.insertUpdate("ALTER TABLE datos_tripletas AUTO_INCREMENT=0");
//            conn2.destruir();

            String consulta;
            ResultSet rs;
            int columnas;

            for (int i = 0; i < tablaIds.length; i++) { // 2; i++) {//
                conn.initDatosLectura();
                conn.setSchema(tablaIds[i][0].toString());
                //                    System.out.println(conn.getUrl());
                //                    System.out.println(conn.getUser());
                //                    System.out.println(conn.getPassword());
                conn.crear();

                consulta = "SELECT * FROM " + tablaIds[i][1] + ";";
                rs = conn.query(consulta);
                columnas = rs.getMetaData().getColumnCount();
                String insert;
                while (rs.next()) {
                    for (int j = 1; j <= columnas; j++) {
                        if (i == 1) {
//                            System.out.print("<");
//                            System.out.print(tablaIds[i][1].toString() + "_" + rs.getObject(tablaIds[i][2].toString()));
//                            System.out.print(">");
//                            System.out.print("<");
//                            System.out.print(tablaIds[i][1].toString() + "_" + rs.getMetaData().getColumnLabel(j).toString());
//                            System.out.print(">");
//                            System.out.print("<");
//                            System.out.print(rs.getObject(j).toString());
//                            System.out.print(">");
//                            System.out.println("");
//
                            insert = "INSERT INTO datos_tripletas(log,sujeto,predicado,objeto) "
                                    + "values('" + tablaIds[i][1].toString() + "|" + rs.getString(tablaIds[i][2].toString()) + "|" + rs.getMetaData().getColumnLabel(j).toString() + "',"
                                    + "'" + rs.getString(tablaIds[i][2].toString()) + "',"
                                    + "'" + rs.getMetaData().getColumnLabel(j).toString() + "',"
                                    + "'" + rs.getString(j).trim().replaceAll("'", "\'").replaceAll("\n", "\\n") + "');";

                            insertar(insert);
                        }
                    }
                }

                conn.destruir();

                System.out.println("");
            }

            //        return tripletas;
        } catch (SQLException ex) {
            Logger.getLogger(Tripletas.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void insertar(String insert) {
        try {
//            conn2.crear();
            if (conn2.isClosed()) {
                conn2.destruir();
                conn2.initDatosEscritura();
                conn2.setSchema("tripletas_db");
                conn2.crear();
            }

            System.out.println(insert);

            if (!conn2.insertUpdate(insert)) {
                System.out.println("ERROR al insertar");
            }
//            conn2.destruir();
        } catch (SQLException ex) {
            conn2.destruir();
            Logger.getLogger(Tripletas.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void imprimirMatriz(Object[][] tablaIds) {
        for (int i = 0; i < tablaIds.length; i++) {
            for (int j = 0; j < tablaIds[i].length; j++) {
                System.out.print(tablaIds[i][j] + "\t");
            }
            System.out.println("");
        }

    }
}


public class DBConnection {

    private String schema = ""; // Nombre del esquema que al cual se va a hacer la consulta
    private String user;
    private String password;
    private String ipServer;
    private String url;
    Connection conn = null;
    Statement stm;

    /**
     * Constructor de DbConnection
     */
    public DBConnection() {
    }

    public void crear() {
        try {
            //obtenemos el driver de para mysql
            Class.forName("com.mysql.jdbc.Driver");
            //obtenemos la conexión
            conn = DriverManager.getConnection(getUrl(), getUser(), getPassword());
            if (conn != null) {
                System.out.println("Conección a base de datos " + getSchema() + " OK");
            }
        } catch (SQLException e) {
            System.out.println("La conección a base de datos " + getSchema() + " FALLÓ");
            System.out.println(e);
        } catch (ClassNotFoundException e) {
            System.out.println("No se encuentra la clase: com.mysql.jdbc.Driver");
            System.out.println(e);
        }
    }

    public void initDatosEscritura() {
        try {
            //Entrada
            BufferedReader bufferedReader = new BufferedReader(new FileReader("ArchivoEscritura.txt"));
            //Buscar si existe una palabra
            String line = "";
            while ((line = bufferedReader.readLine()) != null) {
                if ((line.split("="))[0].equals("user")) {
//                    System.out.println("user-->" + (line.split("="))[1]);
                    user = (line.split("="))[1];
                }
                if ((line.split("="))[0].equals("password")) {
//                    System.out.println("password-->" + (line.split("="))[1]);
                    password = (line.split("="))[1];
                }
                if ((line.split("="))[0].equals("ipServer")) {
//                    System.out.println("ipServer-->" + (line.split("="))[1]);
                    ipServer = (line.split("="))[1];
                }
            }
            url = "jdbc:mysql://" + ipServer + "/";
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public void initDatosLectura() {
        try {
            //Entrada
            BufferedReader bufferedReader = new BufferedReader(new FileReader("ArchivoLectura.txt"));
            //Buscar si existe una palabra
            String line = "";
            while ((line = bufferedReader.readLine()) != null) {
                if ((line.split("="))[0].equals("user")) {
//                    System.out.println("user-->" + (line.split("="))[1]);
                    user = (line.split("="))[1];
                }
                if ((line.split("="))[0].equals("password")) {
//                    System.out.println("password-->" + (line.split("="))[1]);
                    password = (line.split("="))[1];
                }
                if ((line.split("="))[0].equals("ipServer")) {
//                    System.out.println("ipServer-->" + (line.split("="))[1]);
                    ipServer = (line.split("="))[1];
                }
            }
            url = "jdbc:mysql://" + ipServer + "/";
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public void destruir() {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                System.out.println("Ha ocurrido la siguiente excepcion: \n" + e);
            }
        }
    }

    public boolean isClosed() throws SQLException{
        return conn.isClosed();
    }

    /**
     * @param querySelect a SELECT query to the Database
     * @return a <code>ResultSet</code> with the data
     * @throws SQLException
     */
    public ResultSet query(String querySelect) throws SQLException {

//        System.out.print("El SQL enviado es: ");
//        System.out.println(querySelect);
//        System.out.println("");

        ResultSet rs;
        stm = conn.createStatement();

        rs = stm.executeQuery(querySelect);

        return rs;
    }

    public boolean insertUpdate(String insertUpdate) throws SQLException {

        stm = conn.createStatement();
        if (stm.executeUpdate(insertUpdate) != 0) {
            return true;
        } else {
            return false;
        }
    }

    public void cerrar(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
                System.out.println("ResultSet cerrado");
            } catch (Exception e) {
                System.out.println("No es posible cerrar la Conexion");
            }
        }
    }

    /**
     * @return the schema
     */
    public String getSchema() {
        return schema;
    }

    /**
     * @param schema the schema to set
     */
    public void setSchema(String schema) {
        this.schema = schema;
    }

    /**
     * @return the user
     */
    public String getUser() {
        return user;
    }

    /**
     * @return the password
     */
    public String getPassword() {
        return password;
    }

    /**
     * @return the ipServer
     */
    public String getIpServer() {
        return ipServer;
    }

    /**
     * @return the url
     */
    public String getUrl() {
        if (!getSchema().isEmpty()) {
            return "jdbc:mysql://" + ipServer + "/" + getSchema() + "?zeroDateTimeBehavior=convertToNull&autoReconnect=true";
        } else {
            return "jdbc:mysql://" + ipServer + "/?zeroDateTimeBehavior=convertToNull&autoReconnect=true";
        }
    }
}