package com.fitbank.schemautils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.StringWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import javax.swing.JOptionPane;
import javax.swing.UIManager;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;

import com.fitbank.util.Servicios;

public final class FetchSchema {

    private final static String _ = "\t";

    private final static String __ = "\t\t";

    private final static String ___ = "\t\t\t";

    private final PreparedStatement pstmtTablas;

    private final PreparedStatement pstmtCampos;

    private final PreparedStatement pstmtPadres;

    private final PreparedStatement pstmtHijas;

    public static void main(String[] args) throws Exception {
        UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());

        Connection conn = getConnection();

        FetchSchema fs = new FetchSchema(conn);
        fs.fetchSchema();
        fs.close();

        conn.close();
    }

    private static Connection getConnection() throws Exception {
        String conexion = "jdbc:oracle:thin:@";
        String usuario = "";
        String clave = "";

        String[] rotulos = { "Host Name", "JDBC_Port", "SID", "Username",
            "Password" };
        String[] valores = { "192.168.1.7", "1521", "fitbank", "fitdos",
            "fitdos" };
        for (int i = 0; i < rotulos.length; i++) {
            String valor = JOptionPane.showInputDialog(String.format(
                    "< %s > %s\n\nIngrese %s:", conexion + (i < 3
                    ? " . . ." : ""), !usuario.equals("") ? "\nUsername: "
                    + usuario : "", rotulos[i]), valores[i]);

            if (valor != null) {
                if (i < 3) {
                    conexion += valor + (i == 2 ? "" : ":");
                } else if (i == 3) {
                    usuario = valor;
                } else {
                    clave = valor;
                }
            } else {
                System.out.println(
                        "Se ingreso un dato nulo o no válido, se suspende proceso.");
                return null;
            }
        }

        System.out.print("Conectando < " + conexion + " > . . . ");

        try {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

            return DriverManager.getConnection(conexion, usuario, clave);

        } catch (Exception e) {
            System.out.println("ERROR: La conexión falló");
            throw e;
        }
    }

    private FetchSchema(Connection conn) throws SQLException {
        pstmtTablas = conn.prepareStatement("select distinct tablabase, to_number(substr(lpad(ccampo, 8, '0'), 0, 5)) ctabla, comments"
                + " from tcamposconsultablesid t"
                + " join user_tab_comments c on c.table_name=t.tablabase"
                + " order by tablabase");

        pstmtCampos = conn.prepareStatement("select t.ccampo ccampo, campobase, tipodatobase, primarykey, mandatorio, valordefault, descripcion"
                + " from tcamposconsultablesid tid"
                + " join tcamposconsultables t on tid.ccampo = t.ccampo"
                + " where tablabase=?"
                + "   and fhasta = to_timestamp('2999-12-31','yyyy-mm-dd')"
                + " order by ccampo");

        pstmtPadres = conn.prepareStatement("select distinct p.table_name"
                + " from user_constraints h, user_constraints p"
                + " where h.table_name = upper(?)"
                + "   and h.r_constraint_name = p.constraint_name"
                + "   and h.r_owner = p.owner"
                + "   and h.constraint_name in"
                + "       (select constraint_name from user_cons_columns"
                + "        where constraint_name = h.constraint_name"
                + "          and column_name = ?)");

        pstmtHijas = conn.prepareStatement("select distinct h.table_name"
                + " from user_constraints p, user_constraints h"
                + " where p.table_name = upper(?)"
                + "   and p.constraint_name = h.r_constraint_name"
                + "   and p.owner = h.r_owner"
                + "   and p.constraint_name in"
                + "       (select constraint_name from user_cons_columns"
                + "        where constraint_name = p.constraint_name"
                + "          and column_name = ?)");
    }

    public void close() throws SQLException {
        pstmtTablas.close();
        pstmtCampos.close();
        pstmtPadres.close();
        pstmtHijas.close();
    }

    public void fetchSchema() throws Exception {
        Writer res = new StringWriter();

        res.append("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n");
        res.append("<isladoc>\n");

        processTables(res);

        res.append("</isladoc>");

        res.close();

        String file = Servicios.getResource("com/fitbank/schemautils/schema.xml");
        FileOutputStream fos = new FileOutputStream(file);
        IOUtils.write(res.toString(), fos, "UTF-8");
        fos.close();

        System.out.println("");
        System.out.println("Proceso terminado");
    }

    private void processTables(Writer res) throws SQLException, IOException {
        ResultSet rsetNombresTabla = pstmtTablas.executeQuery();

        int count = 0;
        while (rsetNombresTabla.next()) {
            final String codigoTabla = rsetNombresTabla.getString("ctabla");
            final String tablaBase = rsetNombresTabla.getString("tablabase");
            final String descTabla = StringUtils.defaultIfEmpty(rsetNombresTabla.getString("comments"), ".");

            System.out.println("Procesando tabla " + tablaBase + " (" + (++count) + ")...");

            res.append(_ + "<tabla>\n");
            res.append(__ + "<tnum>" + StringUtils.leftPad(codigoTabla, 3, '0') + "</tnum>\n");
            res.append(__ + "<tnom>" + tablaBase + "</tnom>\n");
            res.append(__ + "<tdes>" + StringEscapeUtils.escapeXml(descTabla) + "</tdes>\n");

            processFields(tablaBase, res);

            res.append(_ + "</tabla>\n");
        }

        rsetNombresTabla.close();
    }

    private void processFields(String tablaBase, Writer res) throws IOException,
            SQLException {
        pstmtCampos.setString(1, tablaBase);
        ResultSet rsetCamposTabla = pstmtCampos.executeQuery();

        System.out.print("Procesando campos");
        while (rsetCamposTabla.next()) {
            System.out.print(".");
            String codigoCampo = rsetCamposTabla.getString("ccampo");
            String campoBase = rsetCamposTabla.getString("campobase");
            String tipoDato = rsetCamposTabla.getString("tipodatobase");
            String primaryKey = rsetCamposTabla.getString("primarykey");
            String mandatorio = rsetCamposTabla.getString("mandatorio");
            String defaultValue = rsetCamposTabla.getString("valordefault");
            String desCampo = rsetCamposTabla.getString("descripcion");

            List<String> padres = getParentTables(tablaBase, campoBase);
            List<String> hijas = getChildTables(tablaBase, campoBase);

            res.append(__ + "<campo>\n");
            res.append(___ + "<cnum>" + codigoCampo.substring(codigoCampo.length() - 3) + "</cnum>\n");
            res.append(___ + "<cnom>" + campoBase + "</cnom>\n");
            res.append(___ + "<cdes>" + StringEscapeUtils.escapeXml(desCampo) + "</cdes>\n");
            res.append(___ + "<tipd>" + tipoDato + "</tipd>\n");

            if ("1".equals(primaryKey)) {
                res.append(___ + "<prim>" + primaryKey + "</prim>\n");
            }
            if ("1".equals(mandatorio)) {
                res.append(___ + "<mand>" + mandatorio + "</mand>\n");
            }
            if (StringUtils.isNotBlank(defaultValue)) {
                res.append(___ + "<defv>" + defaultValue + "</defv>\n");
            }
            if (!padres.isEmpty()) {
                res.append(___ + "<tpad>" + StringUtils.join(padres, ",") + "</tpad>\n");
            }
            if (!hijas.isEmpty()) {
                res.append(___ + "<hija>" + StringUtils.join(hijas, ",") + "</hija>\n");
            }

            res.append(__ + "</campo>\n");
        }
        System.out.print("\n");

        rsetCamposTabla.close();
    }

    private List<String> getChildTables(String tablaBase, String campoBase)
            throws SQLException {
        pstmtHijas.setString(1, tablaBase);
        pstmtHijas.setString(2, campoBase);

        ResultSet rsetReferencias = pstmtHijas.executeQuery();

        List<String> hijas = new LinkedList<String>();
        while (rsetReferencias.next()) {
            if (!rsetReferencias.getString("table_name").equals("")) {
                hijas.add(rsetReferencias.getString("table_name"));
            }
        }

        rsetReferencias.close();

        return hijas;
    }

    private List<String> getParentTables(String tablaBase, String campoBase)
            throws SQLException {
        pstmtPadres.setString(1, tablaBase);
        pstmtPadres.setString(2, campoBase);
        ResultSet rsetReferencias = pstmtPadres.executeQuery();

        List<String> padres = new LinkedList<String>();

        while (rsetReferencias.next()) {
            if (!rsetReferencias.getString("table_name").equals("")) {
                padres.add(rsetReferencias.getString("table_name"));
            }
        }

        rsetReferencias.close();

        return padres;
    }

}