/*
 * Copyright 2006-2007 Queplix Corp.
 *
 * Licensed under the Queplix Public License, Version 1.1.1 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.queplix.com/solutions/commercial-open-source/queplix-public-license/
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 *
 */

package com.queplix.core.modules.eqlext.www;

import com.queplix.core.jxb.entity.types.SqlSType;
import com.queplix.core.utils.StringHelper;
import com.queplix.core.utils.SystemHelper;
import com.queplix.core.utils.sql.SqlWrapper;
import com.queplix.core.utils.sql.SqlWrapperFactory;
import com.queplix.core.utils.www.AbstractServlet;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Iterator;

/**
 * Executes any SQL queries.
 *
 * @author Andrey L. Baranov [ALB]
 * @version $Revision: 1.3 $ $Date: 2006/05/22 11:08:07 $
 */
public class SqlServlet
        extends AbstractServlet {

    // =============================================================== Constants

    private static final int MAX_RECORDS = 200;

    // =============================================================== Servlet API methods

    /* (non-Javadoc)
     * @see HTTPServlet#init(ServletConfig)
     */

    public void init(ServletConfig sc)
            throws ServletException {

        super.init(sc);

        /** @todo read parameters */
    }

    /* (non-Javadoc)
     * @see HTTPServlet#service(HttpServletRequest, HttpServletResponse)
     */
    public void service(HttpServletRequest request,
                        HttpServletResponse response)
            throws ServletException, IOException {

        // Check if the servlet is run from the localhost
        if(!request.getRemoteAddr().equalsIgnoreCase("127.0.0.1")) {
            throw new ServletException("Cannot use this servlet.");
        }

        // Check permissions.
        if(SystemHelper.isProductionMode()) {
            // [TN] {
            // check availability in production mode
            if(!new Boolean(this.getInitParameter("allowInProductionMode"))
                    .booleanValue()) {
                throw new ServletException(
                        "Cannot use this servlet in production mode.");
            }
            // [TN] }

        }

        String result = null;
        long page_size = -1;

        // Get parameters: SQL query and page size.
        request.setCharacterEncoding("UTF-8");
        String name = request.getParameter("name");
        String sql = request.getParameter("sql");
        String s = request.getParameter("pageSize");
        if(s != null) {
            page_size = Long.parseLong(s);
        }

        if(sql != null) {
            result = executeQuery(name, sql, page_size);
        } else {
            result = "<b>EMPTY</b>";
        }

        // Set HTTP headers.
        response.setContentType("text/html;charset=UTF-8");
        response.setHeader("Cache-Control", "no-cache");
        response.setHeader("Pragma", "no-cache");

        // Print data.
        PrintWriter out = response.getWriter();
        out.println("<html>");
        out.println("<body>");
        out.print("<form method=\"post\" accept-charset=\"UTF-8\"");
        if(page_size >= 0) {
            out.print("?pageSize=" + page_size);
        }
        out.print("\">");
        out.println("<table cellspacing=\"10\" style=\"width:100%\">");
        out.println(
                "<tr><td colspan=\"2\"><label>SqlWrapper:</label>&nbsp;&nbsp;<select name=\"name\">");
        for(Iterator it = SqlWrapperFactory.getSqlWrapperNames().iterator();
            it.hasNext();) {
            String sqlWrapperName = (String) it.next();
            if(sqlWrapperName.equals(name)) {
                out.print("<option selected=\"selected\" value=\"");
            } else {
                out.print("<option value=\"");
            }
            out.print(sqlWrapperName);
            out.print("\">");
            out.print(sqlWrapperName);
            out.println("</option>");
        }
        out.println("</select></td></tr>");
        out.println(
                "<tr><td><label>SQL:</label></td><td style=\"width:100%\">");
        out.print("<textarea name=\"sql\" style=\"width:100%;height:300\">");
        if(sql != null) {
            out.print(sql.trim());
        }
        out.println("</textarea>");
        out.println("</td></tr>");
        out.println("<tr><td><input type=\"submit\" value=\"Go\">");
        out.println("</td></tr>");
        out.println("<tr><td></td><td>");
        out.println(result);
        out.println("</td></tr>");
        out.println("</table>");
        out.println("</form>");
        out.println("</body>");
        out.println("</html>");

        // Ok.
        logger.DEBUG("Completed! Enc: " + response.getCharacterEncoding());
    }

    // ========================================================= Private methods

    //
    // Executes the given SQL query.
    //

    private String executeQuery(String sqlWrapperName, String sql,
                                long page_size) {

        // Initialization.
        SqlWrapper sqlWrapper = SqlWrapperFactory.getSqlWrapper(sqlWrapperName);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        StringBuffer sb = new StringBuffer();

        // Check page size.
        if(page_size < 0) {
            page_size = MAX_RECORDS;
        }

        try {
            String msg = "Sql Wrapper Name: " + sqlWrapperName
                    + ", SQL query (default): " + sql + "\n";
            System.out.print(msg);
            msg = "SQL query (Cp1251): " + sql + "\n";
            System.out.write(msg.getBytes("Cp1251"));
            msg = "SQL query (UTF-8): " + sql + "\n";
            System.out.write(msg.getBytes("UTF-8"));
            msg = "SQL query (CP866): " + sql + "\n";
            System.out.write(msg.getBytes("CP866"));
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        long time = System.currentTimeMillis();
        try {
            con = sqlWrapper.doConnection();

            System.out.println("Got connection: " + con.getClass().getName());

            ps = sqlWrapper.doPreparedStatement(con, sql);

            if(ps.execute()) {

                time = System.currentTimeMillis() - time;
                rs = ps.getResultSet();
                rsmd = rs.getMetaData();
                int cols = rsmd.getColumnCount();

                // Show description.
                sb.append("<br><label>").append("SQL: ").append(sql).append(
                        "</label><br>");
                sb.append("<br><label>[").append("PAGE SIZE: ").append(
                        page_size).append("]</label>");
                sb.append("<label>[").append("TIME: ").append(time).append(
                        "(ms)]</label><br>");
                sb.append("<table border=\"1\" style=\"width:100%\"><tr>");
                sb.append("<td>#</td>");
                for(int i = 1; i <= cols; i++) {
                    String val = rsmd.getColumnName(i);
                    sb.append("<td><b>").append(val).append("</b></td>");
                }
                sb.append("</tr>");

                // Show data.
                int count = 0;
                while(rs.next() && count < page_size) {
                    sb.append("<tr>");
                    sb.append("<td>").append(count + 1).append("</td>");
                    for(int i = 1; i <= cols; i++) {
                        int type = rsmd.getColumnType(i);
                        String val = getObject(sqlWrapper, rs, i, type);
                        sb.append("<td>").append(val).append("</td>");
                    }
                    sb.append("</tr>");
                    count++;
                }
                sb.append("</table>");

            }
        } catch (Exception ex) {
            ex.printStackTrace();
            sb = new StringBuffer();
            sb.append("<textarea style=\"width:100%\" name=\"error\">");
            sb.append(StringHelper.escape(ex.getMessage())).append(
                    "</textarea>");

        } finally {
            sqlWrapper.closeConnection(con, ps);
        }

        return sb.toString();

    }

    //
    // Retrieves an object from SQL ResultSet.
    //
    private String getObject(SqlWrapper sqlWrapper, ResultSet rs, int j,
                             int type)
            throws Exception {

        if(type < 0) {
            type = Types.VARCHAR;
        } else if(type == Types.CLOB) {
            // clob mapping
            type = Types.VARCHAR;
        }

        SqlSType sqlSType = sqlWrapper.typeMapping(type, 0);
        if(sqlSType == null) {
            // . not supported
            return "<pre>&lt;unknown&gt;</pre>";
        }

        int sql_type = sqlSType.getType();
        Object o;
        try {
            o = sqlWrapper.getParser(sql_type).getObject(rs, j);
        } catch (SQLException ex) {
            logger.ERROR("Cannot read value for column #" + j + "; type=" +
                    type + "; sql type=" + sql_type);
            throw ex;
        }

        if(o instanceof char[]) {
            return "<pre>" + escape(new String((char[]) o)) + "</pre>";
        } else {
            return "<pre>" + escape("" + o) + "</pre>";
        }
    }

    //
    // Escapes the string.
    //
    private String escape(String s) {

        if(StringHelper.isEmpty(s)) {
            return s;
        }

        StringBuffer sb = new StringBuffer();
        int size = s.length();
        for(int i = 0; i < size; i++) {
            char c = s.charAt(i);
            switch(c) {
                case '<':
                    sb.append("&lt;");
                    break;
                case '>':
                    sb.append("&gt;");
                    break;
                default:
                    if(c != 0) {
                        sb.append((char) c);
                    }
            }
        }

        return sb.toString();
    }

}
