package org.jfox.dao;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.jfox.framework.util.Classes;
import org.jfox.framework.util.Methods;

/**
 * @author <a href="mailto:yangyong@ufsoft.com.cn">Young Yang</a>
 */

public class DAOUtil {

    public static String getTableName(Class mappingClass, String databaseName, Connection conn) throws DAOException {
        List correctNames = new ArrayList();
        String[] guessNames = multiTableNames(mappingClass);
        try {
            for(int i = 0; i < guessNames.length; i++) {
                String name = guessNames[i];
                // get tables ignore case
                ResultSet rset = conn.getMetaData().getTables(databaseName, null, name, null);
                while(rset.next()) {
                    String realName = rset.getString(3);
                    if(name.equalsIgnoreCase(realName)) {
                        if(!correctNames.contains(realName)) {
                            correctNames.add(realName);
                        }
                    }
                }
                close(rset);
            }
        }
        catch(SQLException e) {
            throw new DAOException("Could not read meta data for database " + databaseName, e);
        }

        if(correctNames.size() == 1) {
            return (String) correctNames.get(0);
        }
        else if(correctNames.size() > 1) { // more than one
            throw new DAOException("More than one table in database " + databaseName
                    + " matched the mapping class: " + mappingClass.getName()
                    + ", Matched tables are: " + correctNames
                    + ", Guessed table names are: " + guessNames);

        }
        else { // empty
            throw new DAOException("No tables in database " + databaseName
                    + " matched the mapping class: " + mappingClass.getName()
                    + ", Matched tables are: " + correctNames
                    + ", Guessed table names are: " + guessNames);
        }
    }

    public static String getColumnName(Method method, String tableName, Connection conn) throws DAOException {
        if(Methods.isGetMethod(method) || Methods.isSetMethod(method)) {
            String[] names = multiNames(method.getName().substring(3, method.getName().length()));

            List correctNames = new ArrayList();
            try {
                for(int i=0; i<names.length; i++){
                    String name = names[i];
                    ResultSet rset = conn.getMetaData().getColumns(null, null, tableName, name);
                    while(rset.next()) {
                        if(name.equals(rset.getString(4))) {
                            if(!correctNames.contains(name)) {
                                correctNames.add(name);
                            }
                        }
                    }
                    close(rset);

                }
            }
            catch(SQLException e) {
                throw new DAOException("Could not read database meta data for table " + tableName, e);
            }

            if(correctNames.size() == 1) {
                return (String) correctNames.get(0);
            }
            else if(correctNames.size() > 1) {
                throw new DAOException("More than one method in table " + tableName
                        + " matched the guessed names: " + names.toString()
                        + ". \nMatching fields were: " + correctNames.toString());
            }
            else {
                throw new DAOException("No column in table " + tableName + " match method " + method.getName());
            }

        }
        else {
            return method.getName();
        }
    }

    public static String getPrimaryKeyColumnName(String table, String databaseName, Connection conn) throws DAOException {
        ResultSet result = null;
        try {
            result = conn.getMetaData().getPrimaryKeys(null, databaseName, table);

            if(result.next()) {
                return result.getString(4);
            }
        }
        catch(SQLException e) {
            throw new DAOException("Error determining primary key for table " + table, e);
        }
        finally {
            close(result);
        }

        return null;
    }

    public static void close(Connection conn) {
        try {
            if(conn != null) {
                conn.close();
            }
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }

    public static void close(Statement statement) {
        try {
            if(statement != null) {
                statement.close();
            }
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }

    public static void close(ResultSet rset) {
        try {
            if(rset != null) rset.close();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }

    private static String[] multiTableNames(Class mappingClass) {
        return multiNames(Classes.classNameWithoutPackage(mappingClass));
    }

    /**
     * EmployeeInfo.class => {employeeinfo, eployee_info}
     *
     * @param orginName
     * @return
     */
    public static String[] multiNames(String orginName) {
        List names = new ArrayList();
        names.add(orginName);

        if(!names.contains(orginName.toLowerCase())) {
            names.add(orginName.toLowerCase());
        }
        if(!names.contains(orginName.toUpperCase())) {
            names.add(orginName.toUpperCase());
        }

        StringBuffer underScoreClassName = new StringBuffer();
        for(int i = 0; i < orginName.length(); i++) {
            char c = orginName.charAt(i);
            if(i == 0) {
                underScoreClassName.append(c);
            }
            else {
                if(Character.isUpperCase(c)) {
                    if(Character.isLowerCase(orginName.charAt(i - 1))) {
                        underScoreClassName.append("_").append(c);
                    }
                    else if(orginName.length() > (i + 1) && Character.isLowerCase(orginName.charAt(i + 1))) {
                        underScoreClassName.append("_").append(c);
                    }
                    else {
                        underScoreClassName.append(c);
                    }
                }
                else {
                    underScoreClassName.append(c);
                }
            }
        }

        if(!underScoreClassName.toString().equalsIgnoreCase(orginName)) {
            if(!names.contains(underScoreClassName.toString())) {
                names.add(underScoreClassName.toString());
            }
            if(!names.contains(underScoreClassName.toString().toLowerCase())) {
                names.add(underScoreClassName.toString().toLowerCase());
            }
            if(!names.contains(underScoreClassName.toString().toUpperCase())) {
                names.add(underScoreClassName.toString().toUpperCase());
            }
        }
        return (String[])names.toArray(new String[names.size()]);
    }

}

