package sharin.sql.generator;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToOne;

import sharin.sql.Sql;
import sharin.util.PropertyUtils;

public class BasicSqlGenerator implements SqlGenerator {

    private static final Pattern propEntryPattern = Pattern
            .compile("^(-?)(?:([^.]*)\\.)?([^.]+)$");

    private static final Pattern directionPattern = Pattern.compile(
            "^(?:A|DE)SC$", Pattern.CASE_INSENSITIVE);

    private final EntityInfo entityInfo;

    private final Map<String, JoinInfo> joinInfoMap;

    public BasicSqlGenerator(EntityInfo entityInfo) {
        this(entityInfo, null);
    }

    public BasicSqlGenerator(EntityInfo entityInfo,
            Map<String, JoinInfo> joinInfoMap) {

        this.entityInfo = entityInfo;

        if (joinInfoMap == null) {
            joinInfoMap = new HashMap<String, JoinInfo>();

        } else {
            joinInfoMap = new HashMap<String, JoinInfo>(joinInfoMap);
        }

        for (Field field : entityInfo.getEntityClass().getDeclaredFields()) {

            if (Modifier.isStatic(field.getModifiers())) {
                continue;
            }

            if (field.isAnnotationPresent(ManyToOne.class)
                    || field.isAnnotationPresent(OneToOne.class)) {

                String targetTableAlias = field.getName();

                if (joinInfoMap.containsKey(targetTableAlias)) {
                    continue;
                }

                String sourceColumnName = null;
                JoinColumn joinColumn = field.getAnnotation(JoinColumn.class);

                if (joinColumn != null) {
                    sourceColumnName = joinColumn.name();
                }

                EntityInfo targetEntityInfo = new EntityInfo(field.getType());
                JoinInfo joinInfo = new JoinInfo(sourceColumnName,
                        targetEntityInfo, targetTableAlias);
                joinInfoMap.put(targetTableAlias, joinInfo);
            }
        }

        this.joinInfoMap = joinInfoMap;
    }

    public Sql countAll() {
        return countBySamples();
    }

    public Sql countBySample(Object sample) {
        return countBySamples(sample);
    }

    public Sql countBySamples(Object... samples) {
        StringBuilder textBuilder = new StringBuilder();
        textBuilder.append("SELECT COUNT(*) FROM ");
        textBuilder.append(entityInfo.getTableName());
        List<Object> paramList = new ArrayList<Object>();

        if (samples.length > 0) {
            appendWhereSamples(textBuilder, samples, false, paramList);
        }

        return new Sql(textBuilder.toString(), paramList.toArray());
    }

    public Sql selectAll(String propExpr, String orderExpr) {
        return selectBySamples(propExpr, orderExpr);
    }

    public Sql selectBySample(String propExpr, String orderExpr, Object sample) {
        return selectBySamples(propExpr, orderExpr, sample);
    }

    public Sql selectBySamples(String propExpr, String orderExpr,
            Object... samples) {

        StringBuilder textBuilder = new StringBuilder();
        Map<String, JoinInfo> propertyJoinMap = evaluatePropExpr(propExpr, true);
        Set<JoinInfo> joinInfoSet = appendSelectFrom(textBuilder,
                propertyJoinMap);
        List<Object> paramList = new ArrayList<Object>();

        if (samples.length > 0) {
            appendWhereSamples(textBuilder, samples, !joinInfoSet.isEmpty(),
                    paramList);
        }

        appendOrderBy(textBuilder, orderExpr, propertyJoinMap);
        return new Sql(textBuilder.toString(), paramList.toArray());
    }

    public Sql selectById(String propExpr, Object id) {
        return selectByIds(propExpr, null, id);
    }

    public Sql selectByIds(String propExpr, String orderExpr, Object... ids) {
        StringBuilder textBuilder = new StringBuilder();
        Map<String, JoinInfo> propertyJoinMap = evaluatePropExpr(propExpr, true);
        appendSelectFrom(textBuilder, propertyJoinMap);
        List<Object> paramList = new ArrayList<Object>();

        if (ids.length > 0) {
            appendWhereIds(textBuilder, ids, paramList);
        }

        appendOrderBy(textBuilder, orderExpr, propertyJoinMap);
        return new Sql(textBuilder.toString(), paramList.toArray());
    }

    public Sql insert(String propExpr, Object entity) {
        StringBuilder textBuilder = new StringBuilder();
        Map<String, JoinInfo> propertyJoinMap = evaluatePropExpr(propExpr,
                false);
        List<Object> paramList = new ArrayList<Object>();
        boolean appended = false;

        for (String propertyName : propertyJoinMap.keySet()) {
            Object param = PropertyUtils.getSimplePropertyValue(entity,
                    propertyName);
            paramList.add(param);

            if (!appended) {
                textBuilder.append("INSERT INTO ");
                textBuilder.append(entityInfo.getTableName());
                textBuilder.append(" (");
                appended = true;

            } else {
                textBuilder.append(", ");
            }

            textBuilder.append(entityInfo.getColumnName(propertyName));
        }

        textBuilder.append(") VALUES (");

        for (int i = 0; i < paramList.size(); i++) {

            if (i > 0) {
                textBuilder.append(", ");
            }

            textBuilder.append('?');
        }

        textBuilder.append(')');
        return new Sql(textBuilder.toString(), paramList.toArray());
    }

    public Sql updateAll(String propExpr, Object entity) {
        return updateBySamples(propExpr, entity);
    }

    public Sql updateBySample(String propExpr, Object entity, Object sample) {
        return updateBySamples(propExpr, entity, sample);
    }

    public Sql updateBySamples(String propExpr, Object entity,
            Object... samples) {

        StringBuilder textBuilder = new StringBuilder();
        Map<String, JoinInfo> propertyJoinMap = evaluatePropExpr(propExpr,
                false);
        List<Object> paramList = appendUpdateSet(textBuilder, propertyJoinMap
                .keySet(), entity);

        if (samples.length > 0) {
            appendWhereSamples(textBuilder, samples, false, paramList);
        }

        return new Sql(textBuilder.toString(), paramList.toArray());
    }

    public Sql updateById(String propExpr, Object entity, Object id) {
        return updateByIds(propExpr, entity, id);
    }

    public Sql updateByIds(String propExpr, Object entity, Object... ids) {
        StringBuilder textBuilder = new StringBuilder();
        Map<String, JoinInfo> propertyJoinMap = evaluatePropExpr(propExpr,
                false);
        List<Object> paramList = appendUpdateSet(textBuilder, propertyJoinMap
                .keySet(), entity);

        if (ids.length > 0) {
            appendWhereIds(textBuilder, ids, paramList);
        }

        return new Sql(textBuilder.toString(), paramList.toArray());
    }

    public Sql deleteAll() {
        return deleteBySamples();
    }

    public Sql deleteBySample(Object sample) {
        return deleteBySamples(sample);
    }

    public Sql deleteBySamples(Object... samples) {
        StringBuilder textBuilder = new StringBuilder();
        textBuilder.append("DELETE FROM ");
        textBuilder.append(entityInfo.getTableName());
        List<Object> paramList = new ArrayList<Object>();

        if (samples.length > 0) {
            appendWhereSamples(textBuilder, samples, false, paramList);
        }

        return new Sql(textBuilder.toString(), paramList.toArray());
    }

    public Sql deleteById(Object id) {
        return deleteByIds(id);
    }

    public Sql deleteByIds(Object... ids) {
        StringBuilder textBuilder = new StringBuilder();
        textBuilder.append("DELETE FROM ");
        textBuilder.append(entityInfo.getTableName());
        List<Object> paramList = new ArrayList<Object>();

        if (ids.length > 0) {
            appendWhereIds(textBuilder, ids, paramList);
        }

        return new Sql(textBuilder.toString(), paramList.toArray());
    }

    private Map<String, JoinInfo> evaluatePropExpr(String propExpr,
            boolean joined) {

        Map<String, JoinInfo> result = null;

        if (propExpr == null || propExpr.length() == 0) {
            propExpr = "*";
        }

        result = new LinkedHashMap<String, JoinInfo>();

        for (String propEntry : propExpr.trim().split("\\s*,\\s*")) {

            if (propEntry.length() == 0) {
                continue;
            }

            Matcher matcher = propEntryPattern.matcher(propEntry);

            if (!matcher.matches()) {
                continue;
            }

            String sign = matcher.group(1);
            String tableAlias = matcher.group(2);
            String simpleName = matcher.group(3);

            Map<String, JoinInfo> tempMap = result;

            if (sign.equals("-")) {
                tempMap = new LinkedHashMap<String, JoinInfo>();
            }

            if (tableAlias == null) {

                if (simpleName.equals("*")) {

                    for (String s : entityInfo.getPropertyNames()) {
                        tempMap.put(s, null);
                    }

                    if (joined) {

                        for (JoinInfo joinInfo : joinInfoMap.values()) {

                            for (String s : joinInfo.getTargetColumnAliases()) {

                                tempMap.put(s, joinInfo);
                            }
                        }
                    }

                } else {

                    if (entityInfo.hasColumnName(simpleName)) {
                        tempMap.put(simpleName, null);
                    }
                }

            } else {

                if (tableAlias.length() == 0) {

                    if (simpleName.equals("*")) {

                        for (String s : entityInfo.getPropertyNames()) {
                            tempMap.put(s, null);
                        }
                    }

                } else {
                    JoinInfo joinInfo = joinInfoMap.get(tableAlias);

                    if (joinInfo != null) {

                        if (simpleName.equals("*")) {

                            for (String s : joinInfo.getTargetColumnAliases()) {

                                tempMap.put(s, joinInfo);
                            }

                        } else {

                            if (joinInfo.hasTargetColumnName(simpleName)) {
                                tempMap.put(tableAlias + '.' + simpleName,
                                        joinInfo);
                            }
                        }
                    }
                }
            }

            if (tempMap != result) {

                for (String s : tempMap.keySet()) {
                    result.remove(s);
                }
            }
        }

        return result;
    }

    private Set<JoinInfo> appendSelectFrom(StringBuilder textBuilder,
            Map<String, JoinInfo> propertyJoinMap) {

        Set<JoinInfo> joinInfoSet = new LinkedHashSet<JoinInfo>();

        for (JoinInfo joinInfo : propertyJoinMap.values()) {

            if (joinInfo != null) {
                joinInfoSet.add(joinInfo);
            }
        }

        String tableName = entityInfo.getTableName();
        String tablePrefix = joinInfoSet.isEmpty() ? "" : tableName + '.';
        boolean appended = false;

        for (Map.Entry<String, JoinInfo> entry : propertyJoinMap.entrySet()) {

            if (!appended) {
                textBuilder.append("SELECT ");
                appended = true;

            } else {
                textBuilder.append(", ");
            }

            String propertyName = entry.getKey();
            JoinInfo joinInfo = entry.getValue();

            if (joinInfo != null) {
                String simpleName = propertyName.substring(joinInfo
                        .getTargetTableAlias().length() + 1);
                joinInfo.appendField(textBuilder, simpleName);

            } else {
                textBuilder.append(tablePrefix);
                textBuilder.append(entityInfo.getColumnName(propertyName));
                textBuilder.append(" AS \"");
                textBuilder.append(propertyName);
                textBuilder.append('"');
            }
        }

        textBuilder.append(" FROM ");
        textBuilder.append(tableName);

        for (JoinInfo joinInfo : joinInfoSet) {
            textBuilder.append(" ");
            joinInfo.appendJoin(textBuilder, tableName);
        }

        return joinInfoSet;
    }

    private void appendOrderBy(StringBuilder textBuilder, String orderExpr,
            Map<String, JoinInfo> propertyJoinMap) {

        if (orderExpr != null) {
            orderExpr = orderExpr.trim();

            if (orderExpr.length() > 0) {
                String[] orders = orderExpr.split(",");
                boolean appended = false;

                for (int i = 0; i < orders.length; i++) {
                    String[] ss = orders[i].trim().split("\\s+");
                    String propertyName = ss[0];

                    if (propertyJoinMap.containsKey(propertyName)) {

                        if (!appended) {
                            textBuilder.append(" ORDER BY ");
                            appended = true;

                        } else {
                            textBuilder.append(", ");
                        }

                        textBuilder.append('"');
                        textBuilder.append(propertyName);
                        textBuilder.append('"');

                        if (ss.length > 1) {

                            if (directionPattern.matcher(ss[1].toUpperCase())
                                    .matches()) {

                                textBuilder.append(' ');
                                textBuilder.append(ss[1]);
                            }
                        }
                    }
                }
            }
        }
    }

    private List<Object> appendUpdateSet(StringBuilder textBuilder,
            Set<String> propertyNameSet, Object entity) {

        List<Object> paramList = new ArrayList<Object>();
        boolean appended = false;

        for (String propertyName : propertyNameSet) {
            Object param = PropertyUtils.getSimplePropertyValue(entity,
                    propertyName);
            paramList.add(param);

            if (!appended) {
                textBuilder.append("UPDATE ");
                textBuilder.append(entityInfo.getTableName());
                textBuilder.append(" SET ");
                appended = true;

            } else {
                textBuilder.append(", ");
            }

            textBuilder.append(entityInfo.getColumnName(propertyName));
            textBuilder.append(" = ?");
        }

        return paramList;
    }

    private void appendWhereSamples(StringBuilder textBuilder,
            Object[] samples, boolean joined, List<Object> paramList) {

        boolean appended = false;

        for (Object sample : samples) {

            if (!appended) {
                textBuilder.append(" WHERE ");

                if (samples.length > 1) {
                    textBuilder.append('(');
                }

                appended = true;

            } else {
                textBuilder.append(") OR (");
            }

            appendWhereSample(textBuilder, sample, joined, paramList);
        }

        if (samples.length > 1) {
            textBuilder.append(')');
        }
    }

    private void appendWhereSample(StringBuilder textBuilder, Object sample,
            boolean joined, List<Object> paramList) {

        boolean appended = false;

        for (String propertyName : entityInfo.getPropertyNames()) {
            Object param = PropertyUtils.getSimplePropertyValue(sample,
                    propertyName);

            if (param != null) {
                paramList.add(param);

                if (!appended) {
                    appended = true;

                } else {
                    textBuilder.append(" AND ");
                }

                if (joined) {
                    textBuilder.append(entityInfo.getTableName());
                    textBuilder.append('.');
                }

                textBuilder.append(entityInfo.getColumnName(propertyName));
                textBuilder.append(" = ?");
            }
        }
    }

    private void appendWhereIds(StringBuilder textBuilder, Object[] ids,
            List<Object> paramList) {

        textBuilder.append(" WHERE ");
        textBuilder.append(entityInfo.getIdColumnName());

        if (ids.length == 1) {
            textBuilder.append(" = ?");
            paramList.add(ids[0]);

        } else {
            boolean appended = false;

            for (Object id : ids) {
                paramList.add(id);

                if (!appended) {
                    textBuilder.append(" IN (");
                    appended = true;

                } else {
                    textBuilder.append(", ");
                }

                textBuilder.append('?');
            }

            textBuilder.append(')');
        }
    }
}
