package org.jfox.dao;

import java.io.InputStream;
import java.io.Reader;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import org.jfox.dao.mapping.AsciiStream;
import org.jfox.dao.mapping.AsciiStreamColumnMapping;
import org.jfox.dao.mapping.BigDecimalColumnMapping;
import org.jfox.dao.mapping.BinaryStreamColumnMapping;
import org.jfox.dao.mapping.BlobColumnMapping;
import org.jfox.dao.mapping.BooleanColumnMapping;
import org.jfox.dao.mapping.ByteArrayColumnMapping;
import org.jfox.dao.mapping.ByteColumnMapping;
import org.jfox.dao.mapping.CharacterStreamColumnMapping;
import org.jfox.dao.mapping.ClobColumnMapping;
import org.jfox.dao.mapping.ColumnMapping;
import org.jfox.dao.mapping.DateColumnMapping;
import org.jfox.dao.mapping.DoubleColumnMapping;
import org.jfox.dao.mapping.FloatColumnMapping;
import org.jfox.dao.mapping.IntColumnMapping;
import org.jfox.dao.mapping.LongColumnMapping;
import org.jfox.dao.mapping.ObjectColumnMapping;
import org.jfox.dao.mapping.RefColumnMapping;
import org.jfox.dao.mapping.ShortColumnMapping;
import org.jfox.dao.mapping.StringColumnMapping;
import org.jfox.dao.mapping.TableMapping;
import org.jfox.dao.mapping.TimeColumnMapping;
import org.jfox.dao.mapping.TimestampColumnMapping;
import org.jfox.dao.mapping.URLColumnMapping;
import org.jfox.framework.util.Methods;

/**
 * fill object data from DB
 * persistent data to DB
 *
 * @author <a href="mailto:yangyong@ufsoft.com.cn">Young Yang</a>
 */

public class MappingUtil {

    public static Object fetchByPrimaryKey(TableMapping mapping, Object primaryKey, String sql, Connection conn) throws DAOException {
        PreparedStatement pst = null;

        try {
            pst = conn.prepareStatement(sql);
            mapping.getColumnMapping(mapping.getPrimaryKeyColumnName()).doFillStatement(pst, primaryKey, 1);
            ResultSet rset = pst.executeQuery();
            if(!rset.next()) {
                return null;
            }
            return fetch(mapping, rset);
        }
        catch(Exception e) {
            throw new DAOException("Error fetching object by primary key (" + primaryKey + ") using SQL(" +
                    sql + ") and object mapping (" + mapping + ")");
        }
        finally {
            DAOUtil.close(pst);
        }
    }

    public static Object fetch(TableMapping mapping, String sql, Connection conn) throws DAOException {
        ResultSet rset = null;
        Object persistentObject = null;
        try {
            Statement stm = conn.createStatement();
            rset = stm.executeQuery(sql);
            if(!rset.next()) {
                return null;
            }
            persistentObject = fetch(mapping, rset);
            return persistentObject;
        }
        catch(SQLException e) {
            throw new DAOException("Error fetching object from PreparedStatement", e);
        }
        finally {
            DAOUtil.close(rset);
        }
    }

    private static Object fetch(TableMapping mapping, ResultSet rset) throws DAOException {
        Object persistentObject = newInstance(mapping.getMappingClass());

        try {
            for(int i = 1, n = rset.getMetaData().getColumnCount(); i <= n; i++) {
                String columnName = rset.getMetaData().getColumnName(i);
                ColumnMapping fieldMapping = mapping.getColumnMapping(columnName);
                if(fieldMapping != null && !fieldMapping.isDisableSet()) {
                    fieldMapping.fillObject(persistentObject, rset);
                }
            }
        }
        catch(SQLException e) {
            throw new DAOException("Error populating persistent object with values", e);
        }

        return persistentObject;
    }

    public static List fetchBatchByPrimaryKeys(TableMapping mapping, Object[] primaryKeys, String sql, Connection conn) throws DAOException {
        if(primaryKeys== null || primaryKeys.length == 0) {
            return new ArrayList();
        }
        List list = new ArrayList();
        PreparedStatement statement = null;

        try {
            statement = conn.prepareStatement(sql);

            for(int i=0; i<primaryKeys.length; i++){
                Object primaryKey = primaryKeys[i];
                mapping.getColumnMapping(mapping.getPrimaryKeyColumnName()).doFillStatement(statement, primaryKey, 1);
                ResultSet rset = statement.executeQuery();
                if(rset.next()){
                    list.add(fetch(mapping,rset));
                }
            }
            return list;
        }
        catch(Exception e) {
            throw new DAOException("Error reading objects by primary keys (" + primaryKeys + ") using SQL(" +
                    sql + ") and object mapping (" + mapping + ")", e);
        }
        finally {
            DAOUtil.close(statement);
        }
    }

    private static List fetchBatch(TableMapping mapping, ResultSet rset) throws DAOException {
        List list = new ArrayList();
        try {
            //change for MS SQL Server Driver compatibility :-(
            if(rset.getRow() > 0) {
                list.add(fetch(mapping, rset));
            }

            while(rset.next()) {
                list.add(fetch(mapping, rset));
            }
            return list;
        }
        catch(SQLException e) {
            throw new DAOException("Error fetching list of objects from ResultSet", e);
        }
    }

    public static List fetchBatch(TableMapping mapping, String sql, Connection conn) throws DAOException {
        Statement statement = null;
        try {
            statement = conn.createStatement();
            ResultSet rset = statement.executeQuery(sql);
            return fetchBatch(mapping, rset);
        }
        catch(SQLException e) {
            throw new DAOException("Error creating statement to read list of objects from.", e);
        }
        finally {
            DAOUtil.close(statement);
        }
    }

    public static int insert(TableMapping mapping, Object pObject, String sql, Connection conn) throws DAOException {
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            fillStatements(mapping, pObject, pst);
            return pst.executeUpdate();
        }
        catch(SQLException e) {
            throw new DAOException("Error inserting pObject into database. Object was: (" +
                    pObject.toString() + ")\nSql: " + sql, e);
        }
        finally {
            DAOUtil.close(pst);
        }
    }

    public static int[] insertBatch(TableMapping mapping, Object[] pObjects, String sql, Connection conn) throws DAOException {
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            for(int i=0; i<pObjects.length; i++){
                Object object = pObjects[i];
                fillStatements(mapping, object, pst);
                pst.addBatch();

            }
            return pst.executeBatch();
        }
        catch(SQLException e) {
            throw new DAOException("Error batch inserting pObjects in database. Objects were: (" +
                    pObjects.toString() + ")\nSql: " + sql, e);
        }
        finally {
            DAOUtil.close(pst);
        }
    }

    public static int update(TableMapping mapping, Object pObject, String sql, Connection conn) throws DAOException {
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            int parameterCount = fillStatements(mapping, pObject, pst);
            mapping.getColumnMapping(mapping.getPrimaryKeyColumnName()).fillStatement(pst, pObject, parameterCount);
            return pst.executeUpdate();
        }
        catch(SQLException e) {
            throw new DAOException("Error updating pObject in database. Object was: (" +
                    pObject.toString() + ")\nSql: " + sql, e);
        }
        finally {
            DAOUtil.close(pst);
        }
    }

    public static int updateByPrimaryKey(TableMapping mapping, Object pObject, Object originPrimaryKey, String sql, Connection conn) throws DAOException {
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            int parameterCount = fillStatements(mapping, pObject, pst);
            ColumnMapping pkComColumnMapping = mapping.getColumnMapping(mapping.getPrimaryKeyColumnName());
            pkComColumnMapping.doFillStatement(pst, originPrimaryKey, parameterCount);
            return pst.executeUpdate();
        }
        catch(Exception e) {
            throw new DAOException("Error updating pObject in database. Object was: (" +
                    pObject.toString() + ")\nSql: " + sql, e);
        }
        finally {
            DAOUtil.close(pst);
        }
    }

    public static int[] updateBatch(TableMapping mapping, Object[] pObjects, String sql, Connection conn) throws DAOException {
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            for(int i=0; i<pObjects.length; i++){
                Object object = pObjects[i];
                int parameterCount = fillStatements(mapping, object, pst);
                mapping.getColumnMapping(mapping.getPrimaryKeyColumnName()).fillStatement(pst, object, parameterCount);
                pst.addBatch();

            }
            return pst.executeBatch();
        }
        catch(SQLException e) {
            throw new DAOException("Error batch updating objects in database. Objects were: (" +
                    Arrays.asList(pObjects) + ")\nSql: " + sql, e);
        }
        finally {
            DAOUtil.close(pst);
        }
    }

    public static int[] updateBatch(TableMapping mapping, Collection objects, Collection oldPrimaryKeys, String sql, Connection conn) throws DAOException {
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            Iterator objectIterator = objects.iterator();
            Iterator oldPrimaryKeyIterator = oldPrimaryKeys.iterator();

            while(objectIterator.hasNext()) {
                Object object = objectIterator.next();
                Object oldPrimaryKey = oldPrimaryKeyIterator.next();
                int parameterCount = fillStatements(mapping, object, pst);
                mapping.getColumnMapping(mapping.getPrimaryKeyColumnName()).fillStatement(pst, oldPrimaryKey, parameterCount);
                pst.addBatch();
            }
            return pst.executeBatch();
        }
        catch(SQLException e) {
            throw new DAOException("Error batch updating objects in database. Objects were: (" +
                    objects.toString() + ")\nOld Primary Keys were: (" + oldPrimaryKeys + ")" +
                    "\nSql: " + sql, e);
        }
        finally {
            DAOUtil.close(pst);
        }
    }

    public static int delete(TableMapping mapping, Object object, String sql, Connection conn) throws DAOException {
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            mapping.getColumnMapping(mapping.getPrimaryKeyColumnName()).fillStatement(pst, object, 1);
            return pst.executeUpdate();
        }
        catch(Exception e) {
            throw new DAOException("Error deleting object: " + object + ", using method:\n" + mapping, e);
        }
        finally {
            DAOUtil.close(pst);
        }
    }

    public static int[] deleteBatch(TableMapping mapping, Object[] pObjects, String sql, Connection conn) throws DAOException {
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);

            for(int i=0; i<pObjects.length; i++){
                Object object = pObjects[i];
                mapping.getColumnMapping(mapping.getPrimaryKeyColumnName()).fillStatement(pst, object, 1);
                pst.addBatch();
            }

            return pst.executeBatch();
        }
        catch(Exception e) {
            throw new DAOException("Error batch deleting objects in database. Objects were: (" +
                    Arrays.asList(pObjects) + ")\nSql: " + sql, e);
        }
        finally {
            DAOUtil.close(pst);
        }
    }

    public static int deleteBySpecificPrimaryKey(TableMapping mapping, Object primaryKey, String sql, Connection conn) throws DAOException {
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            mapping.getColumnMapping(mapping.getPrimaryKeyColumnName()).doFillStatement(pst, primaryKey, 1);
            return pst.executeUpdate();
        }
        catch(Exception e) {
            throw new DAOException("Error deleting object by primary key: " + primaryKey + ", using method:\n" + mapping, e);
        }
        finally {
            DAOUtil.close(pst);
        }
    }

    public static int[] deleteBatchByPrimaryKeys(TableMapping mapping, Object[] primaryKeys, String sql, Connection conn) throws DAOException {
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            for(int i=0; i<primaryKeys.length; i++){
                Object primaryKey = primaryKeys[i];
                mapping.getColumnMapping(mapping.getPrimaryKeyColumnName()).doFillStatement(pst, primaryKey, 1);
                pst.addBatch();
            }
            return pst.executeBatch();
        }
        catch(Exception e) {
            throw new DAOException("Error batch deleting objects in database. Primary keys were: (" +
                    primaryKeys.toString() + ")\nSql: " + sql, e);
        }
        finally {
            DAOUtil.close(pst);
        }
    }

    private static Object newInstance(Class pClass) throws DAOException {
        try {
            return pClass.newInstance();
        }
        catch(InstantiationException e) {
            throw new DAOException("Error occurred when trying to create " +
                    "instance of class " + pClass.getName() + " (using no-argument constructor)", e);
        }
        catch(IllegalAccessException e) {
            throw new DAOException("Error occurred when trying to create " +
                    "instance of class " + pClass.getName() + " (using no-argument constructor)", e);
        }
    }

    /**
     * fill all columns value to statements
     * @param mapping
     * @param pObject
     * @param pst
     * @return
     * @throws SQLException
     * @throws DAOException
     */
    private static int fillStatements(TableMapping mapping, Object pObject, PreparedStatement pst) throws SQLException, DAOException {
        Iterator iterator = mapping.getColumnMappings().iterator();
        int i = 1;
        while(iterator.hasNext()) {
            ColumnMapping fieldMapping = (ColumnMapping) iterator.next();
            if(!fieldMapping.isDisableGet() && !fieldMapping.isAutoEvaluated()) {
                fieldMapping.fillStatement(pst, pObject, i);
                i++;
            }
        }
        return i;
    }

    public static TableMapping mapTable(Class pClass, String table, Connection conn) throws DAOException {
        TableMapping tm = new TableMapping(table, pClass);
        Method[] methods = pClass.getMethods();
        for(int i = 0; i < methods.length; i++) {
            Method method = methods[i];
            ColumnMapping cm = mapColumn(table, method, conn);

            if(cm != null) { // has mapping column
                String columnName = cm.getColumnName();
                // already mapped, add setter or getter
                if(tm.isColumnMapped(columnName)) {
                    if(cm.getColumnType() != tm.getColumnMapping(columnName).getColumnType()) {
                        throw new DAOException("current column mapping type " + tm.getColumnMapping(columnName).getColumnType().getName()
                                + " not same with origin type " + cm.getColumnType().getName() + ", column name is " + columnName);
                    }
                    if(cm.getGetterMethod() != null) {
                        tm.getColumnMapping(columnName).setGetterMethod(cm.getGetterMethod());
                    }
                    if(cm.getSetterMethod() != null) {
                        tm.getColumnMapping(columnName).setSetterMethod(cm.getSetterMethod());
                    }
                }
                else { // first map
                    tm.addColumnMapping(cm);
                }
            }

        }

        // determine primary key
        String pkName = DAOUtil.getPrimaryKeyColumnName(table,null,conn);
        if(pkName != null){
            tm.setPrimaryKeyColumnName(pkName);
            tm.getColumnMapping(pkName).setPrimaryKey(true);
        }
        return tm;
    }

    public static ColumnMapping mapColumn(String table, Method method, Connection conn) throws DAOException {
        if(Methods.isGetMethod(method)) {
            if(method.getName().equals("getClass")) return null;
            String columnName = DAOUtil.getColumnName(method, table, conn);
            Class columnType = method.getReturnType();
            ColumnMapping cm = createColumnMapping(columnName, columnType);
            cm.setGetterMethod(method);
            return cm;
        }
        else if(Methods.isSetMethod(method)) {
            String columnName = DAOUtil.getColumnName(method, table, conn);
            Class columnType = method.getParameterTypes()[0];
            ColumnMapping cm = createColumnMapping(columnName, columnType);
            cm.setSetterMethod(method);
            return cm;
        }
        else {
            return null;
        }
    }

    private static ColumnMapping createColumnMapping(String columnName, Class columnType) {
        if(Array.class.equals(columnType)) {
            return null;
        }
        else if(AsciiStream.class.equals(columnType)) {
            return new AsciiStreamColumnMapping(columnName, columnType);
        }
        else if(Boolean.class.equals(columnType) || boolean.class.equals(columnType)) {
            return new BooleanColumnMapping(columnName, columnType);
        }
        else if(Byte.class.equals(columnType) || byte.class.equals(columnType)) {
            return new ByteColumnMapping(columnName, columnType);
        }
        else if(Byte[].class.equals(columnType) || byte[].class.equals(columnType)) {
            return new ByteArrayColumnMapping(columnName, columnType);
        }
        else if(Double.class.equals(columnType) || double.class.equals(columnType)) {
            return new DoubleColumnMapping(columnName, columnType);
        }
        else if(Float.class.equals(columnType) || float.class.equals(columnType)) {
            return new FloatColumnMapping(columnName, columnType);
        }
        else if(Integer.class.equals(columnType) || int.class.equals(columnType)) {
            return new IntColumnMapping(columnName, columnType);
        }
        else if(Long.class.equals(columnType) || long.class.equals(columnType)) {
            return new LongColumnMapping(columnName, columnType);
        }
        else if(Short.class.equals(columnType) || short.class.equals(columnType)) {
            return new ShortColumnMapping(columnName, columnType);
        }
        else if(BigDecimal.class.equals(columnType)) {
            return new BigDecimalColumnMapping(columnName, columnType);
        }
        else if(InputStream.class.equals(columnType)) {
            return new BinaryStreamColumnMapping(columnName, columnType);
        }
        else if(Blob.class.equals(columnType)) {
            return new BlobColumnMapping(columnName, columnType);
        }
        else if(Reader.class.equals(columnType)) {
            return new CharacterStreamColumnMapping(columnName, columnType);
        }
        else if(Clob.class.equals(columnType)) {
            return new ClobColumnMapping(columnName, columnType);
        }
        else if(Date.class.equals(columnType)) {
            return new DateColumnMapping(columnName, columnType);
        }
        else if(Object.class.equals(columnType)) {
            return new ObjectColumnMapping(columnName, columnType);
        }
        else if(Ref.class.equals(columnType)) {
            return new RefColumnMapping(columnName, columnType);
        }
        else if(String.class.equals(columnType)) {
            return new StringColumnMapping(columnName, columnType);
        }
        else if(Time.class.equals(columnType)) {
            return new TimeColumnMapping(columnName, columnType);
        }
        else if(Timestamp.class.equals(columnType)) {
            return new TimestampColumnMapping(columnName, columnType);
        }
        else if(URL.class.equals(columnType)) {
            return new URLColumnMapping(columnName, columnType);
        }
        else {
            return null;
        }

    }

    public static void main(String[] args) {

    }
}

