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.CategoryDto;
import jp.agentec.adf.util.StringUtil;

public class CategoryDao extends AbstractDao {
    private static final String TAG = "CategoryDao";

    private enum QueryType {GetAllCategories, GetRootCategories, GetCategories}


    /*package*/ CategoryDao() {
    }

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

        int column = cursor.getColumnIndex("category_relation_id");
        if (column != -1) {
            dto.categoryRelationId = cursor.getInt(column);
        }
        column = cursor.getColumnIndex("category_id");
        if (column != -1) {
            dto.categoryId = cursor.getInt(column);
        }
        column = cursor.getColumnIndex("parent_category_id");
        if (column != -1) {
            dto.parentCategoryId = cursor.getInt(column);
        }
        column = cursor.getColumnIndex("category_name");
        if (column != -1) {
            dto.categoryName = 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("disp_order");
        if (column != -1) {
            dto.dispOrder = cursor.getShort(column);
        }
        column = cursor.getColumnIndex("category_path");
        if (column != -1) {
            dto.categoryPath = cursor.getString(column);
        }

        return dto;
    }

    public List<CategoryDto> getAllCategories() {
        return rawQueryGetDtoList(generateGetCategoriesQuery(QueryType.GetAllCategories, null, null, null, null, false), null, CategoryDto.class);
    }

    //해당 콘텐츠 아이디를 가지고 있는 장르정보
    public List<CategoryDto> getExistContentCategory(long contentId) {
        return rawQueryGetDtoList("select * from m_category where category_relation_id in (select category_relation_id from r_content_category where content_id = " + contentId + ") order by category_id", null, CategoryDto.class);
    }

    private String generateGetCategoriesQuery(QueryType queryType, int[] parentCategoryId, Boolean downloaded, String searchKeyword, SearchDivisionType searchDivisionType, boolean isOnlineSearched) {
        StringBuffer sql = new StringBuffer();

        switch (queryType) {
            case GetAllCategories:
                //	AllCategories  ジャンル初期化用
                sql.append(" SELECT mc.category_id AS category_id ");
                sql.append("      , mc.parent_category_id AS parent_category_id ");
                sql.append("      , mc.category_name AS category_name ");
                sql.append("      , 0 AS content_count ");    //	初期化用であるため、コンテンツ数はいらない。
                sql.append("   FROM m_category AS mc ");
                sql.append("   ORDER BY mc.disp_order, mc.category_name");
                break;
//		case GetRootCategories:
//			//	RootCategories  ルートジャンル
//			sql.append(" SELECT DISTINCT ");
//			sql.append("        mc.category_id AS category_id ");
//			sql.append("      , mc.parent_category_id AS parent_category_id ");
//			sql.append("      , mc.category_name AS category_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_category AS mc ");
//			sql.append("  INNER JOIN r_content_category AS rcg ");
//			sql.append("     ON mc.category_relation_id = rcg.category_relation_id ");
//			sql.append("  WHERE mc.parent_category_id = 0 ");
//			sql.append("  ORDER BY mc.disp_order, mc.category_name");
//			break;
            case GetCategories:
                //	 Categories  特定ジャンル
                sql.append(" SELECT T1.category_id ");
                sql.append("      , T1.parent_category_id ");
                sql.append("      , T1.category_name ");
                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 mc2.category_id ");
                sql.append("                      , mc2.parent_category_id ");
                sql.append("                      , mc2.disp_order ");
                sql.append("                      , mc2.category_name ");
                if (isOnlineSearched) {
                    sql.append("                      , CASE WHEN mc2.category_id = mc1.category_id THEN COUNT(tcss.content_id) ");
                    sql.append("                             ELSE 0 ");
                    sql.append("                         END AS content_count ");
                    sql.append("                      , CASE WHEN mc2.category_id = mc1.category_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 mc2.category_id = mc1.category_id THEN COUNT(tc2.content_id) ");
                                sql.append("                             ELSE 0 ");
                                sql.append("                         END AS content_count ");
                                sql.append("                      , CASE WHEN mc2.category_id = mc1.category_id THEN 0 ");
                                sql.append("                             ELSE COUNT(tc2.content_id) ");
                                sql.append("                         END AS child_content_count ");
                                break;
                            case Tag:
                                sql.append("                      , CASE WHEN mc2.category_id = mc1.category_id THEN COUNT(tct.content_id) ");
                                sql.append("                             ELSE 0 ");
                                sql.append("                         END AS content_count ");
                                sql.append("                      , CASE WHEN mc2.category_id = mc1.category_id THEN 0 ");
                                sql.append("                             ELSE COUNT(tct.content_id) ");
                                sql.append("                         END AS child_content_count ");
                                break;
                            case FullText:
                                sql.append("                      , CASE WHEN mc2.category_id = mc1.category_id THEN COUNT(tcp.content_id) ");
                                sql.append("                             ELSE 0 ");
                                sql.append("                         END AS content_count ");
                                sql.append("                      , CASE WHEN mc2.category_id = mc1.category_id THEN 0 ");
                                sql.append("                             ELSE COUNT(tcp.content_id) ");
                                sql.append("                         END AS child_content_count ");
                                break;
                            case Memo:
                            default:
                                break;
                        }
                    } else {
                        sql.append("                      , CASE WHEN mc2.category_id = mc1.category_id THEN COUNT(rcc.content_id) ");
                        sql.append("                             ELSE 0 ");
                        sql.append("                         END AS content_count ");
                        sql.append("                      , CASE WHEN mc2.category_id = mc1.category_id THEN 0 ");
                        sql.append("                             ELSE COUNT(rcc.content_id) ");
                        sql.append("                         END AS child_content_count ");
                    }
                }
                sql.append("                   FROM m_category AS mc1 ");
                sql.append("                  INNER JOIN m_category AS mc2 ");
                sql.append("                     ON mc1.category_path = mc2.category_path ");
                sql.append("                     OR mc1.category_path LIKE mc2.category_path || '/%' ");
                sql.append("                  INNER JOIN r_content_category AS rcc ");
                sql.append("                     ON mc1.category_relation_id = rcc.category_relation_id ");
                sql.append("                  INNER JOIN t_content AS tc ");
                sql.append("                     ON rcc.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:
                            default:
                                break;
                        }
                    }
                }

                sql.append("                  WHERE mc2.parent_category_id IN ");
                sql.append(generateInClause(parentCategoryId));
                sql.append("                  GROUP BY mc1.category_id, mc1.parent_category_id, mc2.category_id, mc2.parent_category_id, mc2.disp_order, mc2.category_name ");

                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.category_id, T1.parent_category_id, T1.category_name ");
                sql.append("  ORDER by T1.parent_category_id, T1.disp_order, T1.category_name ");

                break;
            case GetRootCategories:
            default:
                break;
        }

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

        return sql.toString();
    }

    public void insertCategory(int categoryId, int parentCategoryId, int disp_order, String categoryName) {
        StringBuffer sql = new StringBuffer();
        sql.append(" INSERT INTO m_category (category_id, parent_category_id, category_name, disp_order, category_path) ");
        sql.append(" VALUES (?,?,?,?,?) ");

        try {
            beginTransaction();
            String categoryPath = createCategoryPath(categoryId, parentCategoryId);
            insert(sql.toString(), new Object[]{categoryId, parentCategoryId, categoryName, disp_order, categoryPath});
            commit();
        } catch (Exception e) {
            rollback();
            Logger.e("insertCategory failed.", e);
            throw new RuntimeException(e);
        }

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

    public void updateCategory(int categoryId, int parentCategoryId, int disp_order, String categoryName, int oldParentCategoryId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" UPDATE m_category ");
        sql.append("    SET parent_category_id = ? ");
        sql.append("      , disp_order = ? ");
        sql.append("      , category_name = ? ");
        sql.append("      , category_path = ? ");
        sql.append("  WHERE category_id = ? ");
        sql.append("    AND parent_category_id = ? ");

        try {
            beginTransaction();
            String categoryPath = createCategoryPath(categoryId, parentCategoryId);
            update(sql.toString(), new Object[]{parentCategoryId, disp_order, categoryName, categoryPath, categoryId, oldParentCategoryId});
            commit();
        } catch (Exception e) {
            rollback();
            Logger.e("updateCategory failed.", e);
            throw new RuntimeException(e);
        }

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

    private String createCategoryPath(int categoryId, int parentCategoryId) {
        String categoryPath;
        CategoryDto parentCategoryDto = getCategory(parentCategoryId);
        if (parentCategoryDto != null) {
            // 親が存在する場合、自分のIDを親のパスと連結したものをパスとする
            categoryPath = parentCategoryDto.categoryPath + "/" + categoryId;
        } else {
            // 親が存在しない場合、自分のIDをパスとする
            categoryPath = "" + categoryId;
        }
        return categoryPath;
    }


    public void regenerateCategoryPath() {
        execSql("UPDATE m_category SET category_path = category_id WHERE parent_category_id = 0 ");

        String selectSql = " SELECT category_path, category_id FROM m_category WHERE parent_category_id = ? ";
        String updateSql = " UPDATE m_category SET category_path = ? || '/' || category_id  WHERE parent_category_id = ? ";

        updateCategoryPath(selectSql, updateSql, "0");
    }

    private void updateCategoryPath(String selectSql, String updateSql, String parentCategoryId) {
        Cursor cursor = null;

        try {
            SQLiteDatabase db = getDatabase();
            cursor = db.rawQuery(selectSql, new String[]{parentCategoryId});

            if (cursor.moveToFirst()) {
                String[] updateArgs = new String[2];

                do {
                    updateArgs[0] = cursor.getString(0);    //	category_path
                    updateArgs[1] = cursor.getString(1);    //	category_id

                    db.execSQL(updateSql, updateArgs);

                    updateCategoryPath(selectSql, updateSql, cursor.getString(1));
                } while (cursor.moveToNext());
            }
        } finally {
            if (cursor != null && !cursor.isClosed()) {
                try {
                    cursor.close();
                } catch (Exception e) {
                }
            }
        }
    }


    public void deleteCategory(CategoryDto dto, int defaultCategoryId) {
        String[] keyValues = dto.getKeyValues();
        int categoryRelationId;
        StringBuffer sql = new StringBuffer();

        //	削除しようとしているグループに属しているコンテンツが、他のグループにも属しているか確認するSQL
        //	他のグループに属している場合、削除しようとしているグループとの結びつきを削除
        //	他のグループには属していない場合、削除しようとしているグループとの結びつきをルートのグループとの結びつきに変更
        sql.append(" SELECT rcg1.content_id ");
        sql.append("      , rcg1.category_relation_id ");
        sql.append("   FROM r_content_category AS rcg1 ");
        sql.append("  INNER JOIN ( ");
        sql.append("        SELECT rcg.content_id ");
        sql.append("             , rcg.category_relation_id ");
        sql.append("         FROM r_content_category AS rcg ");
        sql.append("        INNER JOIN ( ");
        sql.append("                    SELECT category_relation_id ");
        sql.append("                      FROM m_category ");
        sql.append("                     WHERE category_id = ? ");
        sql.append("                       AND parent_category_id = ? ");
        sql.append("                   ) AS mc ");
        sql.append("           ON rcg.category_relation_id = mc.category_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 ");

        SQLiteDatabase db = getDatabase();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql.toString(), keyValues);
            if (cursor != null && cursor.moveToFirst()) {
                List<Long> deleteAllContentIds = new ArrayList<Long>();

                do {
                    deleteAllContentIds.add(cursor.getLong(0));
                    categoryRelationId = cursor.getInt(1);
                } while (cursor.moveToNext());

                // r_content_category テーブルから新ユーザが所属していないグループのデータを削除
                if (deleteAllContentIds.size() > 0) {
                    StringUtil.clear(sql);

                    sql.append(" DELETE FROM r_content_category ");
                    sql.append("  WHERE category_relation_id = ");
                    sql.append(categoryRelationId);
                    sql.append("  AND content_id IN (");
                    for (int i = 0; i < deleteAllContentIds.size(); i++) {
                        sql.append("'" + deleteAllContentIds.get(i) + "'");

                        if (i < deleteAllContentIds.size() - 1) {
                            sql.append(", ");
                        }
                    }

                    sql.append(") ");
                    execSql(sql.toString());
                }
            }
        } finally {
            if (cursor != null && !cursor.isClosed()) {
                try {
                    cursor.close();
                } catch (Exception e) {
                }
            }
        }

        // Mcategoryテーブルから所属していないグループ情報を削除
        delete("m_category", "category_id=? AND parent_category_id=?", keyValues);
    }

    public CategoryDto getTopCategoryInfo() {
        return rawQueryGetDto("select * from m_category where category_relation_id = 1", null, CategoryDto.class);
    }

    public CategoryDto getCategory(int categoryId) {
        return rawQueryGetDto("select * from m_category where category_id = " + categoryId, null, CategoryDto.class);
    }

    public List<CategoryDto> getCategorySpareList(int baseId, int parentId) {
        List<CategoryDto> list;
        list = rawQueryGetDtoList("select * from m_category mc where (mc.category_id =  ?) UNION select * from m_category mc where (mc.parent_category_id =  ?)", new String[]{"" + baseId, "" + parentId}, CategoryDto.class);
        return list;
    }

    public List<CategoryDto> getCategoryChildList(int parentId) {
        List<CategoryDto> list;
        list = rawQueryGetDtoList("select * from m_category mc where (mc.parent_category_id =  ?) order by category_name", new String[]{"" + parentId}, CategoryDto.class);
        return list;
    }

    public boolean isExistParent(int baseId) {
        return rawQueryGetInt("select * from m_category where parent_category_id = " + baseId, null) > 0;
    }
}