package jp.sourceforge.shovel.dao.impl;

import java.util.ArrayList;
import java.util.List;

import jp.sourceforge.shovel.dao.IFriendshipCustomDao;
import jp.sourceforge.shovel.entity.IFriendship;
import jp.sourceforge.shovel.util.ShovelUtil;

import org.seasar.dao.DaoMetaDataFactory;
import org.seasar.dao.impl.AbstractDao;

public class FriendshipCustomDaoImpl extends AbstractDao implements IFriendshipCustomDao {
    public FriendshipCustomDaoImpl(DaoMetaDataFactory daoMetaDataFactory) {
        super(daoMetaDataFactory);
    }
    
    public IFriendship[] searchFollowing(long activeId, String[] keywords, int offset, int limit) {
        StringBuilder sqlBuilder = new StringBuilder();
        List<Object> argList = new ArrayList<Object>();

        argList.add(activeId);
        sqlBuilder.append("SELECT\n");
        sqlBuilder.append("    a.*\n");
        sqlBuilder.append("FROM\n");
        sqlBuilder.append("    friendships AS a,\n");
        sqlBuilder.append("    users AS b,\n");
        sqlBuilder.append("    users AS c\n");
        sqlBuilder.append("WHERE\n");
        sqlBuilder.append("    a.activeId=b.userId AND\n");
        sqlBuilder.append("    a.passiveId=c.userId AND\n");
        sqlBuilder.append("    a.accept=true AND\n");
        sqlBuilder.append("    a.activeId=? AND\n");
        sqlBuilder.append("    a.removedTime=0 AND\n");
        sqlBuilder.append("    b.remove=false AND\n");
        sqlBuilder.append("    c.remove=false");
        if (keywords != null && keywords.length > 0) {
            sqlBuilder.append(" AND\n");
            for (int i = 0; i < keywords.length; i++) {
                sqlBuilder.append("    c.foreignKey LIKE ?");
                if (i + 1 < keywords.length) {
                    sqlBuilder.append(" AND");
                }
                sqlBuilder.append("\n");
                keywords[i] = ShovelUtil.escape(keywords[i]);
                argList.add("%" + keywords[i] + "%");
            }
        } else {
            sqlBuilder.append("\n");
        }
        sqlBuilder.append("ORDER BY\n");
        sqlBuilder.append("    a.friendshipId DESC\n");
        sqlBuilder.append("LIMIT ?,?");
        argList.add(offset);
        argList.add(limit);

        Object[] args = argList.toArray(new Object[argList.size()]);
        return (IFriendship[])getEntityManager().findArray(sqlBuilder.toString(), args);
    }
    public IFriendship[] searchReciprocalFollow(long userId, String[] keywords, int offset, int limit) {
        StringBuilder sqlBuilder = new StringBuilder();
        List<Object> argList = new ArrayList<Object>();
        
        argList.add(userId);
        sqlBuilder.append("SELECT\n");
        sqlBuilder.append("    a.*\n");
        sqlBuilder.append("FROM\n");
        sqlBuilder.append("    (friendships AS a LEFT JOIN\n");
        sqlBuilder.append("     friendships AS b ON a.activeId=b.passiveId AND a.passiveId=b.activeId) INNER JOIN\n");
        sqlBuilder.append("    users AS c ON a.passiveId=c.userId\n");
        sqlBuilder.append("WHERE\n");
        sqlBuilder.append("    a.accept=true AND\n");
        sqlBuilder.append("    a.activeId=? AND\n");
        sqlBuilder.append("    a.removedTime=0 AND\n");
        sqlBuilder.append("    b.removedTime=0 AND\n");
        sqlBuilder.append("    c.remove=false");
        if (keywords != null && keywords.length > 0) {
            sqlBuilder.append(" AND\n");
            for (int i = 0; i < keywords.length; i++) {
                sqlBuilder.append("    c.foreignKey LIKE ?");
                if (i + 1 < keywords.length) {
                    sqlBuilder.append(" AND");
                }
                sqlBuilder.append("\n");
                keywords[i] = ShovelUtil.escape(keywords[i]);
                argList.add("%" + keywords[i] + "%");
            }
        } else {
            sqlBuilder.append("\n");
        }
        sqlBuilder.append("ORDER BY\n");
        sqlBuilder.append("    a.friendshipId DESC\n");
        sqlBuilder.append("LIMIT ?,?");
        argList.add(offset);
        argList.add(limit);

        Object[] args = argList.toArray(new Object[argList.size()]);
        return (IFriendship[])getEntityManager().findArray(sqlBuilder.toString(), args);
    }
    public IFriendship[] searchFollowingOnly(long activeId, String[] keywords, int offset, int limit) {
        StringBuilder sqlBuilder = new StringBuilder();
        List<Object> argList = new ArrayList<Object>();
        
        argList.add(activeId);
        String condition;
        if (keywords != null && keywords.length > 0) {
            for (int i = 0; i < keywords.length; i++) {
                sqlBuilder.append("    c.foreignKey LIKE ?");
                if (i + 1 < keywords.length) {
                    sqlBuilder.append(" AND");
                }
                sqlBuilder.append("\n");
                keywords[i] = ShovelUtil.escape(keywords[i]);
                argList.add("%" + keywords[i] + "%");
            }
            sqlBuilder.append(" AND\n");
            condition = sqlBuilder.toString();
        } else {
            condition = "\n";
        }
        
        sqlBuilder.append("(\n");
        sqlBuilder.append("SELECT\n");
        sqlBuilder.append("    a.*\n");
        sqlBuilder.append("FROM\n");
        sqlBuilder.append("    (friendships AS a LEFT JOIN\n");
        sqlBuilder.append("     friendships AS b ON a.activeId=b.passiveId AND a.passiveId=b.activeId) INNER JOIN\n");
        sqlBuilder.append("     users AS c ON a.passiveId=c.userId\n");
        sqlBuilder.append("WHERE\n");
        sqlBuilder.append("    a.accept=true AND\n");
        sqlBuilder.append("    a.activeId=? AND\n");
        sqlBuilder.append("    a.removedTime=0 AND\n");
        sqlBuilder.append("    b.friendshipId IS NULL AND\n");
        sqlBuilder.append("    c.remove=false");
        sqlBuilder.append(condition);
        sqlBuilder.append(") UNION ALL (\n");
        sqlBuilder.append("SELECT\n");
        sqlBuilder.append("    a.*\n");
        sqlBuilder.append("FROM\n");
        sqlBuilder.append("    (friendships AS a LEFT JOIN\n");
        sqlBuilder.append("     friendships AS b ON a.activeId=b.passiveId AND a.passiveId=b.activeId) INNER JOIN\n");
        sqlBuilder.append("     users AS c ON a.passiveId=c.userId\n");
        sqlBuilder.append("WHERE\n");
        sqlBuilder.append("    a.accept=true AND\n");
        sqlBuilder.append("    a.activeId=? AND\n");
        sqlBuilder.append("    a.removedTime=0 AND\n");
        sqlBuilder.append("    b.removedTime>0 AND\n");
        sqlBuilder.append("    c.remove=false");
        sqlBuilder.append(condition);
        sqlBuilder.append(") ORDER BY friendshipId DESC\n");
        sqlBuilder.append("LIMIT ?,?");
        argList.addAll(argList.subList(0, argList.size()));
        argList.add(offset);
        argList.add(limit);

        Object[] args = argList.toArray(new Object[argList.size()]);
        return (IFriendship[])getEntityManager().findArray(sqlBuilder.toString(), args);
    }
    public IFriendship[] searchFollowersOnly(long passiveId, String[] keywords, int offset, int limit) {
        StringBuilder sqlBuilder = new StringBuilder();
        List<Object> argList = new ArrayList<Object>();
        argList.add(passiveId);
        
        String condition;
        if (keywords != null && keywords.length > 0) {
            for (int i = 0; i < keywords.length; i++) {
                sqlBuilder.append("    c.foreignKey LIKE ?");
                if (i + 1 < keywords.length) {
                    sqlBuilder.append(" AND");
                }
                sqlBuilder.append("\n");
                keywords[i] = ShovelUtil.escape(keywords[i]);
                argList.add("%" + keywords[i] + "%");
            }
            sqlBuilder.append(" AND\n");
            condition = sqlBuilder.toString();
        } else {
            condition = "\n";
        }
        
        sqlBuilder.append("(\n");
        sqlBuilder.append("SELECT\n");
        sqlBuilder.append("    a.*\n");
        sqlBuilder.append("FROM\n");
        sqlBuilder.append("    (friendships AS a LEFT JOIN\n");
        sqlBuilder.append("     friendships AS b ON a.activeId=b.passiveId AND a.passiveId=b.activeId) INNER JOIN\n");
        sqlBuilder.append("     users AS c ON a.activeId=c.userId\n");
        sqlBuilder.append("WHERE\n");
        sqlBuilder.append("    a.passiveId=? AND\n");
        sqlBuilder.append("    a.accept=true AND\n");
        sqlBuilder.append("    a.removedTime=0 AND\n");
        sqlBuilder.append("    b.friendshipId IS NULL AND\n");
        sqlBuilder.append("    c.remove=false");
        sqlBuilder.append(condition);
        sqlBuilder.append(") UNION ALL (\n");
        sqlBuilder.append("SELECT\n");
        sqlBuilder.append("    a.*\n");
        sqlBuilder.append("FROM\n");
        sqlBuilder.append("    (friendships AS a LEFT JOIN\n");
        sqlBuilder.append("     friendships AS b ON a.activeId=b.passiveId AND a.passiveId=b.activeId) INNER JOIN\n");
        sqlBuilder.append("     users AS c ON a.activeId=c.userId\n");
        sqlBuilder.append("WHERE\n");
        sqlBuilder.append("    a.passiveId=? AND\n");
        sqlBuilder.append("    a.accept=true AND\n");
        sqlBuilder.append("    a.removedTime=0 AND\n");
        sqlBuilder.append("    b.removedTime>0 AND\n");
        sqlBuilder.append("    c.remove=false");
        sqlBuilder.append(condition);
        sqlBuilder.append(") ORDER BY friendshipId DESC\n");
        sqlBuilder.append("LIMIT ?,?");
        argList.addAll(argList.subList(0, argList.size()));
        argList.add(offset);
        argList.add(limit);

        Object[] args = argList.toArray(new Object[argList.size()]);
        return (IFriendship[])getEntityManager().findArray(sqlBuilder.toString(), args);
    }
}
