package jp.agentec.abook.abv.bl.data.dao;

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

import jp.agentec.abook.abv.bl.acms.type.SearchDivisionType;
import jp.agentec.abook.abv.bl.common.db.Cursor;
import jp.agentec.abook.abv.bl.common.db.SQLiteDatabase;
import jp.agentec.abook.abv.bl.common.log.Logger;
import jp.agentec.abook.abv.bl.dto.GroupDto;
import jp.agentec.adf.util.NumericUtil;
import jp.agentec.adf.util.StringUtil;

public class GroupDao extends AbstractDao {
	private static final String TAG = "GroupDao";
	
	private enum QueryType {GetAllGroups, GetRootGroups, GetGroups}

	/*package*/ GroupDao() {
	}

	@Override
	protected GroupDto convert(Cursor cursor) {
		GroupDto dto = new GroupDto();

		int column = cursor.getColumnIndex("group_relation_id");
		if (column != -1) {
			dto.groupRelationId = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("group_id");
		if (column != -1) {
			dto.groupId = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("parent_group_id");
		if (column != -1) {
			dto.parentGroupId = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("group_level");
		if (column != -1) {
			dto.groupLevel = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("group_name");
		if (column != -1) {
			dto.groupName = cursor.getString(column);
		}
		column = cursor.getColumnIndex("content_count");
		if (column != -1) {
			dto.contentCount = cursor.getInt(column);
		}
		column = cursor.getColumnIndex("display_count");
		if (column != -1) {
			dto.displayCount = cursor.getString(column);
		}
		column = cursor.getColumnIndex("group_path");
		if (column != -1) {
			dto.groupPath = cursor.getString(column);
		}
		column = cursor.getColumnIndex("user_group_flg");
		if (column != -1) {
			dto.userGroupFlg = toBool(cursor.getInt(column));
		}
		column = cursor.getColumnIndex("group_code");
		if (column != -1) {
			dto.groupCode = cursor.getString(column);
		}

		return dto;
	}

	public List<GroupDto> getAllGroups() {
		return rawQueryGetDtoList(generateGetGroupsQuery(QueryType.GetAllGroups, null, null, null, null, null, false), null, GroupDto.class);
	}

	//해당 콘텐츠 아이디를 가지고 있는 그룹정보
	public List<GroupDto> getExistContentGroup(long contentId) {
		return rawQueryGetDtoList("select * from m_group where group_relation_id in (select group_relation_id from r_content_group where content_id = " + contentId + ") order by group_id", null, GroupDto.class); 
	}
	
	private String generateGetGroupsQuery(QueryType queryType, int[] groupRelationids, int[] parentGroupIds, Boolean downloaded, String searchKeyword, SearchDivisionType searchDivisionType, boolean isOnlineSearched) {
		StringBuffer sql = new StringBuffer();

		switch (queryType) {
		/*
		 * GetAllGroupsとGetRootGroupsのSQLは使っていない。これらのSQLはContentDaoにある。
		 * もし、このSQLを使おうとしても、このままでは使わないので、充分検討すること。
		 */
			case GetAllGroups:
				//	AllGroups  グループ初期化用
				sql.append(" SELECT mg.group_id AS group_id ");
				sql.append("      , mg.group_relation_id AS group_relation_id ");
				sql.append("      , mg.group_level AS group_level ");
				sql.append("      , mg.parent_group_id AS parent_group_id ");
				sql.append("      , mg.group_name AS group_name ");
				sql.append("      , 0 AS content_count ");    //	初期化用であるため、コンテンツ数はいらない。
				sql.append("      , mg.group_code AS group_code ");
				sql.append("   FROM m_group AS mg ");
				break;
			case GetRootGroups:
				break;
//		case GetRootGroups:
//			//	RootGroup  ルートグループ
//			sql.append(" SELECT DISTINCT ");
//			sql.append("        mg.group_id AS group_id ");
//			sql.append("      , mg.parent_group_id AS parent_group_id ");
//			sql.append("      , mg.group_level AS group_level ");
//			sql.append("      , mg.group_name AS group_name ");
//			sql.append("      , ( ");
//			sql.append("         SELECT COUNT(1) AS content_count ");
//			sql.append("           FROM t_content ");
//
//			sql.append("          WHERE 1 = 1 ");
//			if (downloaded) {
//				sql.append("          AND downloaded_flg = 1 ");
//			}else if (searchDivisionType == null  && StringUtil.isNullOrWhiteSpace(searchKeyword) && !downloaded) {
//				sql.append("          AND updated_flg = 1 ");
//			}
//
//			sql.append("        ) AS content_count ");
//			sql.append("   FROM m_group AS mg ");
//			sql.append("  INNER JOIN r_content_group AS rcg ");
//			sql.append("     ON mg.group_relation_id = rcg.group_relation_id ");
//			sql.append("  WHERE mg.parent_group_id = 0 ");
//			break;
			case GetGroups:
				//	 Groups  特定グループ
				sql.append(" SELECT A.* FROM ( ");
				sql.append(" SELECT T1.group_relation_id ");
				sql.append("      , T1.group_id ");
				sql.append("      , T1.parent_group_id ");
				sql.append("      , T1.group_level ");
				sql.append("      , T1.group_name ");
				sql.append("      , T1.group_path ");
				sql.append("      , T1.group_code ");
				sql.append("      , SUM(T1.content_count) AS content_count ");
				sql.append("      , CASE WHEN SUM(T1.child_content_count) = 0 THEN ' (' || SUM(T1.content_count) || ') ' ");
				sql.append("             ELSE ' (' || SUM(T1.content_count) || '/' || (SUM(T1.content_count) + SUM(T1.child_content_count)) || ') ' ");
				sql.append("         END AS display_count ");
				sql.append("   FROM ( ");
				sql.append("                 SELECT mg2.group_relation_id ");
				sql.append("                      , mg2.group_id ");
				sql.append("                      , mg2.parent_group_id ");
				sql.append("                      , mg2.group_level ");
				sql.append("                      , mg2.group_name ");
				sql.append("                      , mg2.group_path ");
				sql.append("                      , mg2.group_code ");

				if (isOnlineSearched) {
					sql.append("                      , CASE WHEN mg2.group_id = mg1.group_id THEN COUNT(tcss.content_id) ");
					sql.append("                             ELSE 0 ");
					sql.append("                         END AS content_count ");
					sql.append("                      , CASE WHEN mg2.group_id = mg1.group_id THEN 0 ");
					sql.append("                             ELSE COUNT(tcss.content_id) ");
					sql.append("                         END AS child_content_count ");
				} else {
					//	検索対象によってカウント対象が異なる
					if (searchDivisionType != null && !StringUtil.isNullOrWhiteSpace(searchKeyword)) {
						switch (searchDivisionType) {
							case Title:
								sql.append("                      , CASE WHEN mg2.group_id = mg1.group_id THEN COUNT(tc2.content_id) ");
								sql.append("                             ELSE 0 ");
								sql.append("                         END AS content_count ");
								sql.append("                      , CASE WHEN mg2.group_id = mg1.group_id THEN 0 ");
								sql.append("                             ELSE COUNT(tc2.content_id) ");
								sql.append("                         END AS child_content_count ");
								break;
							case Tag:
								sql.append("                      , CASE WHEN mg2.group_id = mg1.group_id THEN COUNT(tct.content_id) ");
								sql.append("                             ELSE 0 ");
								sql.append("                         END AS content_count ");
								sql.append("                      , CASE WHEN mg2.group_id = mg1.group_id THEN 0 ");
								sql.append("                             ELSE COUNT(tct.content_id) ");
								sql.append("                         END AS child_content_count  ");
								break;
							case FullText:
								sql.append("                        , CASE WHEN mg2.group_id = mg1.group_id THEN COUNT(tcp.content_id) ");
								sql.append("                             ELSE 0 ");
								sql.append("                         END AS content_count ");
								sql.append("                      , CASE WHEN mg2.group_id = mg1.group_id THEN 0 ");
								sql.append("                             ELSE COUNT(tcp.content_id) ");
								sql.append("                         END AS child_content_count ");
								break;
							case Memo:
								break;
							default:
								break;
						}
					} else {
						sql.append("                      , CASE WHEN mg2.group_id = mg1.group_id THEN COUNT(rcg.content_id) ");
						sql.append("                             ELSE 0 ");
						sql.append("                         END AS content_count ");
						sql.append("                      , CASE WHEN mg2.group_id = mg1.group_id THEN 0 ");
						sql.append("                             ELSE COUNT(rcg.content_id) ");
						sql.append("                         END AS child_content_count ");
					}
				}
				sql.append("                   FROM m_group AS mg1 ");
				sql.append("                  INNER JOIN m_group AS mg2 ");
				sql.append("                     ON mg1.group_path LIKE mg2.group_path || '%' ");
				sql.append("                  INNER JOIN r_content_group AS rcg ");
				sql.append("                     ON mg1.group_relation_id = rcg.group_relation_id ");
				sql.append("                  INNER JOIN t_content AS tc ");
				sql.append("                     ON rcg.content_id = tc.content_id ");

				if (isOnlineSearched) {
					sql.append("                   LEFT OUTER JOIN t_content_server_searched AS tcss ");
					sql.append("                     ON tc.content_id = tcss.content_id ");
				} else if (downloaded != null) {
					if (downloaded) {
						sql.append("           AND tc.downloaded_flg = 1 ");
					} else if (searchDivisionType == null && StringUtil.isNullOrWhiteSpace(searchKeyword)) {
						// 新着画面にて検索すると、新着・ダウンロード済みの関係なく検索する。
						sql.append("          AND tc.updated_flg = 1 ");
					}
				}

				if (!isOnlineSearched) {
					//	検索対象
					if (searchDivisionType != null && !StringUtil.isNullOrWhiteSpace(searchKeyword)) {
						switch (searchDivisionType) {
							case Title:
								sql.append("                   LEFT OUTER JOIN t_content AS tc2 ");
								sql.append("                     ON tc.content_id = tc2.content_id ");
								sql.append("                    AND tc2.content_name LIKE ?  /* param */ ");
								break;
							case Tag:
								sql.append(" LEFT OUTER JOIN t_content_tag AS tct ");
								sql.append("    ON tc.content_id = tct.content_id ");
								sql.append("   AND tct.tag_name LIKE ?  /* param */ ");
								break;
							case FullText:
								sql.append(" LEFT OUTER JOIN ( ");
								sql.append("         SELECT content_id ");
								sql.append("           FROM t_content_page ");
								sql.append("          WHERE page_text LIKE ?  /* param */ ");
								sql.append("          GROUP BY content_id ");
								sql.append("       ) AS tcp ");
								sql.append("      ON tc.content_id = tcp.content_id ");
								break;
							case Memo:
								break;
							default:
								break;
						}
					}
				}

				sql.append("                  WHERE mg2.parent_group_id IN ");
				sql.append(generateInClause(parentGroupIds));
				sql.append("                  GROUP BY mg1.group_id, mg1.parent_group_id, mg1.group_level, mg2.group_relation_id, mg2.group_id, mg2.parent_group_id, mg2.group_level, mg2.group_name, mg2.group_path ");

				if (isOnlineSearched) {
					sql.append("                 HAVING (MAX(tc.updated_flg) = 1 AND COUNT(tc.content_id) > 0) OR COUNT(tcss.content_id) > 0 ");
				}

				sql.append("        ) AS T1 ");
				sql.append("  GROUP BY T1.group_relation_id, T1.group_id, T1.parent_group_id, T1.group_level, T1.group_name, T1.group_path ");
				sql.append(" ) AS A ");
				sql.append("                  INNER JOIN (SELECT * FROM m_group WHERE group_relation_id IN ");
				sql.append(generateInClause(groupRelationids));
				sql.append(" ) AS B ");
				sql.append("                    ON (A.group_path LIKE B.group_path || '%' AND A.group_level <= B.group_level + 1) ");
				sql.append(" GROUP BY A.group_relation_id, A.group_id, A.parent_group_id, A.group_level, A.group_name, A.content_count ");

				break;
			default:
				break;
		}

		Logger.v(TAG, "sql=%s", sql);

		return sql.toString();
	}

	public void insertGroup(int groupId, int parentGroupId, String groupName, int groupLevel, boolean userGroupFlg, String groupCode) {
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT OR IGNORE INTO m_group (group_id, parent_group_id, group_name, group_path, group_level, user_group_flg, group_code) ");
		sql.append(" VALUES (?,?,?,?,?,?,?) ");
		try {
			beginTransaction();
			String groupPath = createInsertGroupPath(groupId, parentGroupId, groupLevel);
			insert(sql.toString(), new Object[] { groupId, parentGroupId, groupName, groupPath, groupLevel, userGroupFlg, groupCode });
			commit();
		} catch (Exception e) {
			rollback();
			Logger.e("insertGroup failed.", e);
			throw new RuntimeException(e);
		}
		Logger.v(TAG, "sql=%s", sql);
	}

	private String createInsertGroupPath(int groupId, int parentGroupId, int groupLevel) {
		String groupPath;
		GroupDto parentGroupDto = getInsertGroupParent(parentGroupId, groupLevel);

		if (parentGroupDto != null) {
			groupPath = parentGroupDto.groupPath + "/" + groupId;
		} else {
			groupPath = "" + groupId;
		}

		return groupPath;
	}

	private GroupDto getInsertGroupParent(int parentGroupId, int groupLevel) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * ");
		sql.append("     FROM m_group ");
		sql.append("         WHERE group_id = ? ");
		sql.append("           AND group_level < ? ");
		sql.append("         ORDER BY group_relation_id DESC ");
		sql.append("         LIMIT 1 ");
		return rawQueryGetDto(sql.toString(), new String[] { "" + parentGroupId, "" + groupLevel }, GroupDto.class);
	}

	public void updateGroup(int groupId, int parentGroupId, String groupName, int groupLevel, boolean userGroupFlg, String groupCode) {
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE m_group ");
		sql.append("    SET group_name = ? ");
		sql.append("      , user_group_flg = ? ");
		sql.append("      , group_path = ? ");
		sql.append("      , group_code = ? ");
		sql.append("  WHERE group_id = ? ");
		sql.append("    AND parent_group_id = ? ");
		sql.append("    AND group_level = ? ");
		try {
			beginTransaction();
			String groupPath = createUpdateGroupPath(groupId, parentGroupId, groupLevel);
			update(sql.toString(), new Object[] { groupName, userGroupFlg, groupPath, groupCode, groupId, parentGroupId, groupLevel });
			commit();
		} catch (Exception e) {
			rollback();
			Logger.e("insertGroup failed.", e);
			throw new RuntimeException(e);
		}
		Logger.v(TAG, "sql=%s", sql);
	}

	private String createUpdateGroupPath(int groupId, int parentGroupId, int groupLevel) {
		String groupPath;
		GroupDto parentGroupDto = getUpdateGroupParent(groupId, parentGroupId, groupLevel);

		if (parentGroupDto != null) {
			groupPath = parentGroupDto.groupPath + "/" + groupId;
		} else {
			groupPath = "" + groupId;
		}

		return groupPath;
	}

	private GroupDto getUpdateGroupParent(int groupId, int parentGroupId, int groupLevel) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT b.group_path ");
		sql.append("   FROM m_group AS a ");
		sql.append("      , m_group AS b ");
		sql.append("  WHERE a.group_id = ? ");
		sql.append("    AND a.parent_group_id = ? ");
		sql.append("    AND a.group_level = ? ");
		sql.append("    AND b.group_id = a.parent_group_id ");
		sql.append("    AND b.group_level < a.group_level ");
		sql.append("    AND b.group_relation_id < a.group_relation_id ");
		sql.append("  ORDER BY b.group_relation_id DESC ");
		sql.append("  LIMIT 1 ");

		return rawQueryGetDto(sql.toString(), new String[] { "" + groupId, "" + parentGroupId, "" + groupLevel }, GroupDto.class);
	}

	public void deleteGroup(GroupDto dto, int defaultGroupId) {
		String[] keyValues = dto.getKeyValues();
		StringBuffer sql = new StringBuffer();

		//	削除しようとしているグループに属しているコンテンツが、他のグループにも属しているか確認するSQL
		//	他のグループに属している場合、削除しようとしているグループとの結びつきを削除
		//	他のグループには属していない場合、削除しようとしているグループとの結びつきをルートのグループとの結びつきに変更
		sql.append(" SELECT T1.content_id ");
		sql.append("      , CASE WHEN T2.downloaded_flg = 0 THEN 1 ");
		sql.append("             ELSE is_delete ");
		sql.append("         END is_delete ");
		sql.append("   FROM ( ");
		sql.append("         SELECT rcg1.content_id ");
		sql.append("              , CASE WHEN COUNT(rcg1.content_id) > 1 THEN 1 ");
		sql.append("                     ELSE 0 ");
		sql.append("                 END AS is_delete ");
		sql.append("           FROM r_content_group AS rcg1 ");
		sql.append("          INNER JOIN ( ");
		sql.append("                SELECT rcg.content_id ");
		sql.append("                 FROM r_content_group AS rcg ");
		sql.append("                INNER JOIN ( ");
		sql.append("                      SELECT group_relation_id ");
		sql.append("                        FROM m_group ");
		sql.append("                       WHERE group_id = ? ");
		sql.append("                         AND parent_group_id = ? ");
		sql.append("                         AND group_level = ? ");
		sql.append("                      ) AS mg ");
		sql.append("                   ON rcg.group_relation_id = mg.group_relation_id ");
		sql.append("                GROUP BY rcg.content_id ");
		sql.append("                ) AS rcg2 ");
		sql.append("             ON rcg1.content_id = rcg2.content_id ");
		sql.append("          GROUP BY rcg1.content_id ");
		sql.append("        ) AS T1 ");
		sql.append("      , t_content AS T2 ");
		sql.append("  WHERE T1.content_id = T2.content_id ");

		try {
			beginTransaction();

			SQLiteDatabase db = getDatabase();
			Cursor cursor = null;

			try {
				cursor = db.rawQuery(sql.toString(), keyValues);
				if (cursor != null && cursor.moveToFirst()) {
					List<Long> deleteContentIds = new ArrayList<Long>();
					List<Long> updateContentIds = new ArrayList<Long>();
	
					do {
						if (cursor.getShort(1) == 1) {
							deleteContentIds.add(cursor.getLong(0));
						} else {
							updateContentIds.add(cursor.getLong(0));
						}
					} while (cursor.moveToNext());
	
					if (deleteContentIds.size() > 0) {
						String whereClause = generateInClause(deleteContentIds);
	
						StringUtil.clear(sql);
	
						sql.append(" DELETE ");
						sql.append("   FROM r_content_group ");
						sql.append("  WHERE group_relation_id = ( ");
						sql.append("              SELECT group_relation_id ");
						sql.append("                FROM m_group ");
						sql.append("               WHERE group_id = ? ");
						sql.append("                 AND parent_group_id = ? ");
						sql.append("                 AND group_level = ? ");
						sql.append("             ) ");
						sql.append("    AND content_id IN ");
						sql.append(whereClause);
	
						execSql(sql.toString(), new Object[]{NumericUtil.parseInt(keyValues[0]),NumericUtil.parseInt(keyValues[1]),NumericUtil.parseInt(keyValues[2])});
					}
	
					if (updateContentIds.size() > 0) {
						String whereClause = generateInClause(updateContentIds);
	
						StringUtil.clear(sql);
	
						sql.append(" UPDATE r_content_group ");
						sql.append("    SET group_relation_id = ( ");
						sql.append("        SELECT group_relation_id ");
						sql.append("          FROM m_group ");
						sql.append("         WHERE parent_group_id = 0 ");
						sql.append("         LIMIT 1 ");
						sql.append("        ) ");
						sql.append("  WHERE content_id IN ");
						sql.append(whereClause);
	
						db.execSQL(sql.toString());
					}
				}
			} finally {
				if (cursor != null && !cursor.isClosed()) {
					try {
						cursor.close();
					}
					catch (Exception e) {}
                }
			}

			delete("m_group", "group_id=? AND parent_group_id=? AND group_level=?", keyValues);

			commit();
		} catch (Exception e) {
			rollback();
			Logger.e("deleteGroup failed.", e);
			throw new RuntimeException(e);
		} finally {
		}
	}
	
	public GroupDto getTopGroupInfo() {
		return rawQueryGetDto("select * from m_group where group_relation_id = 1", null, GroupDto.class);
	}
	
	public GroupDto getGroup(int groupId) {
		return rawQueryGetDto("select * from m_group where group_id = " + groupId, null, GroupDto.class);
	}
	
	public List<GroupDto> getGroupSpareList(int baseId, int parentId) {
		List<GroupDto> list;
		list = rawQueryGetDtoList("select * from m_group mg where (mg.group_id =  ?) UNION select * from m_group mg where (mg.parent_group_id =  ?)", new String[]{""+ baseId, ""+ parentId}, GroupDto.class);
		return list;
	}
	
	public List<GroupDto> getGroupChildList(int parentId) {
		List<GroupDto> list;
		list = rawQueryGetDtoList("select * from m_group mg where (mg.parent_group_id =  ?)", new String[]{""+ parentId}, GroupDto.class);
		return list;
	}
	
	public boolean isExistParent(int baseId) {
		return rawQueryGetInt("select * from m_group where parent_group_id = " + baseId, null) > 0;
	}

	public List<GroupDto> getUserGroups() {
		return rawQueryGetDtoList("select * from m_group where user_group_flg = 1", null, GroupDto.class);
	}
	
	/**
	 * ユーザが所属しているグループを返す（複数ある場合はどれか一つを返す。１グループに所属するサイネージで使用）
	 * 
	 * @return
	 */
	public Integer getGroupId() {
		List<GroupDto> userGroupList = getUserGroups();
		if (userGroupList.isEmpty()) {
			return null;
		}
		return userGroupList.get(0).groupId;
	}

}
