/**
 * blancoDbツール <br>
 * 仮想表のためのXMLを自動生成するためのユーティリティプログラム <br>
 * 
 * @author Tosiki Iga
 */

package blanco.db.helper.seed;

import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.CDATASection;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

import blanco.commons.util.BlancoNameAdjuster;
import blanco.db.BlancoDbTableMeta2Xml;

/**
 * 仮想表をリストアップするためのユーティリティクラス。 <br>
 * TODO: 仮想表に空白付き文字が利用されている場合、適切に動作しない問題がわかっている。
 */
public class BlancoDbHelperViewXmlGen {
    public static final String XML_OUTPUT_FILENAME = "sql/blanco/blancoDbHelperViewXmlGenResult.xml";

    public static final String JDBC_STRING = "com.microsoft.jdbc.sqlserver.SQLServerDriver";

    public static final String JDBC_URL = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=Northwind";

    public static final String JDBC_USERNAME = "sa";

    public static final String JDBC_PASSWORD = "password";

    public static final void main(String[] args) {
        Connection conn = null;
        try {
            Class.forName(JDBC_STRING);
            conn = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME,
                    JDBC_PASSWORD);
            final DatabaseMetaData metadata = conn.getMetaData();
            enumTables(metadata);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            System.out.println("JDBCドライバのロードに失敗しました.:" + ex.toString());
            ex.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    public static final void enumTables(final DatabaseMetaData metadata)
            throws SQLException {
        ResultSet resultSet = null;

        final DocumentBuilderFactory documentFactory = DocumentBuilderFactory
                .newInstance();
        DocumentBuilder documentBuilder = null;
        try {
            documentBuilder = documentFactory.newDocumentBuilder();
        } catch (ParserConfigurationException ex1) {
            System.out.println("ドキュメント作成時に例外が発生しました.:" + ex1.toString());
            ex1.printStackTrace();
            return;
        }
        final Document document = documentBuilder.newDocument();
        final Element eleRoot = document.createElement("blanco-db");
        document.appendChild(eleRoot);

        try {
            resultSet = metadata.getTables(null, null, null, null);
            for (; resultSet.next();) {
                String tableTyle = resultSet.getString("TABLE_TYPE");
                // タイプがVIEWであるもののみ扱う。
                if (tableTyle.equals("VIEW")) {
                    final String tableCat = resultSet.getString("TABLE_CAT");
                    final String tableSchem = resultSet
                            .getString("TABLE_SCHEM");
                    final String tableName = resultSet.getString("TABLE_NAME");

                    System.out.println(tableName + " (" + tableCat + ","
                            + tableSchem + ")");

                    final Element eleQuery = document
                            .createElement("deprecated");
                    eleRoot.appendChild(eleQuery);
                    eleQuery.setAttribute("name", "Simple"
                            + BlancoNameAdjuster.toClassName(tableName));

                    // パラメータはVIEWの場合には基本的に存在しない

                    enumColumns(metadata, tableCat, tableSchem, tableName,
                            document, eleQuery);
                }
            }
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
        }

        OutputStream outStream = null;
        try {
            outStream = new BufferedOutputStream(new FileOutputStream(
                    XML_OUTPUT_FILENAME));
            final TransformerFactory tf = TransformerFactory.newInstance();
            final Transformer transformer = tf.newTransformer();
            transformer.setOutputProperty("encoding", "UTF-8");
            transformer.setOutputProperty("standalone", "yes");
            transformer.setOutputProperty("indent", "yes");
            transformer.setOutputProperty(
                    "{http://xml.apache.org/xslt}indent-amount", "2");
            transformer.transform(new DOMSource(document), new StreamResult(
                    outStream));
            outStream.flush();
            outStream.close();
            outStream = null;
        } catch (TransformerException ex) {
            System.out.println("XMLドキュメント保存時に変換例外が発生しました.:" + ex.toString());
            ex.printStackTrace();
            return;
        } catch (IOException ex3) {
            System.out.println("XMLドキュメント保存時に入出力例外が発生しました.:" + ex3.toString());
            ex3.printStackTrace();
            return;
        } finally {
            if (outStream != null) {
                try {
                    outStream.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    public static final void enumColumns(final DatabaseMetaData metadata,
            final String tableCat, final String tableSchem,
            final String tableName, final Document document,
            final Element eleQuery) throws SQLException {

        final Element query = document.createElement("query");
        eleQuery.appendChild(query);

        final StringBuffer sql = new StringBuffer();

        ResultSet resultSetColumns = null;
        try {
            resultSetColumns = metadata.getColumns(tableCat, tableSchem,
                    tableName, null);
            sql.append("SELECT ");
            for (int iColumn = 1; resultSetColumns.next(); iColumn++) {
                System.out
                        .println("  [No."
                                + resultSetColumns.getInt("ORDINAL_POSITION")
                                + "] カラム名:"
                                + resultSetColumns.getString("COLUMN_NAME"));

                if (iColumn != 1) {
                    sql.append(", ");
                }
                sql.append(resultSetColumns.getString("COLUMN_NAME"));

                switch (resultSetColumns.getShort("DATA_TYPE")) {
                case java.sql.Types.CHAR:
                    System.out.println("    CHAR");
                    break;
                case java.sql.Types.VARCHAR:
                    System.out.println("    VARCHAR");
                    break;
                case java.sql.Types.LONGVARCHAR:
                    System.out.println("    LONGVARCHAR");
                    break;

                case java.sql.Types.DATE:
                    System.out.println("    DATE");
                    break;
                case java.sql.Types.TIMESTAMP:
                    System.out.println("    TIMESTAMP");
                    break;
                case java.sql.Types.DECIMAL:
                    System.out.println("    DECIMAL");
                    break;
                case java.sql.Types.INTEGER:
                    System.out.println("    INTEGER");
                    break;
                case java.sql.Types.SMALLINT:
                    System.out.println("    SMALLINT");
                    break;

                default:
                    System.out.println("    サポート外のタイプ:"
                            + resultSetColumns.getShort("DATA_TYPE"));
                    break;
                }
                System.out.println("    TYPE_NAME:"
                        + resultSetColumns.getString("TYPE_NAME"));
                System.out.println("    COLUMN_SIZE:"
                        + resultSetColumns.getInt("COLUMN_SIZE"));

                System.out.println("    DECIMAL_DIGITS:"
                        + resultSetColumns.getInt("DECIMAL_DIGITS"));
                System.out.println("    CHAR_OCTET_LENGTH:"
                        + resultSetColumns.getInt("CHAR_OCTET_LENGTH"));

                switch (resultSetColumns.getInt("NULLABLE")) {
                case DatabaseMetaData.columnNoNulls:
                    System.out.println("    非NULL");
                    break;
                case DatabaseMetaData.columnNullable:
                    System.out.println("    NULLを許可");
                    break;
                case DatabaseMetaData.columnNullableUnknown:
                    System.out.println("    NULL不明");
                    break;
                }

                final String strDefaultValue = resultSetColumns
                        .getString("COLUMN_DEF");
                if (strDefaultValue != null) {
                    System.out.println("    デフォルト値:" + strDefaultValue);
                }
            }
        } finally {
            if (resultSetColumns != null) {
                resultSetColumns.close();
            }
        }
        sql.append(" FROM " + BlancoDbTableMeta2Xml.escapeSqlName(tableName));
        final CDATASection cdata = document.createCDATASection(sql.toString());
        query.appendChild(cdata);

    }
}