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

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

import jp.agentec.abook.abv.bl.acms.type.ContentSortingType;
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.data.SortDirection;
import jp.agentec.abook.abv.bl.dto.ContentBookmarkDto;
import jp.agentec.abook.abv.bl.dto.MydataDto;
import jp.agentec.adf.util.DateTimeFormat;
import jp.agentec.adf.util.DateTimeUtil;
import jp.agentec.adf.util.StringUtil;

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

	/*package*/ ContentBookmarkDao() {
	}

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

		int colnum = cursor.getColumnIndex("content_id");
		if (colnum != -1) {
			dto.contentId = cursor.getLong(colnum);
		}
		colnum = cursor.getColumnIndex("page_num");
		if (colnum != -1) {
			dto.pageNum = cursor.getInt(colnum);
		}
		colnum = cursor.getColumnIndex("page_text");
		if (colnum != -1) {
			dto.pageText = cursor.getString(colnum);
		}
		colnum = cursor.getColumnIndex("page_thumbnail_name");
		if (colnum != -1) {
			dto.pageThumbnailName = cursor.getString(colnum);
		}
		colnum = cursor.getColumnIndex("insert_date");
		if (colnum != -1) {
			dto.insertDate = DateTimeUtil.toDate(cursor.getString(colnum), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
		}
		colnum = cursor.getColumnIndex("update_date");
		if (colnum != -1) {
			dto.updateDate = DateTimeUtil.toDate(cursor.getString(colnum), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
		}
		colnum = cursor.getColumnIndex("delete_flg");
		if (colnum != -1) {
			dto.deleteFlg = toBool(cursor.getInt(colnum));
		}

		colnum = cursor.getColumnIndex("page_thumbnail_path");
		if (colnum != -1) {
			dto.pageThumbnailPath = cursor.getString(colnum);
		}
		colnum = cursor.getColumnIndex("content_name");
		if (colnum != -1) {
			dto.contentName = cursor.getString(colnum);
		}

		return dto;
	}

	protected MydataDto convertToMyData(Cursor cursor) {
		MydataDto dto = new MydataDto();
		
		int colnum = cursor.getColumnIndex("content_id");
		if (colnum != -1) {
			dto.contentId = cursor.getLong(colnum);
		}
		colnum = cursor.getColumnIndex("content_name");
		if (colnum != -1) {
			dto.contentName = cursor.getString(colnum);
		}
		colnum = cursor.getColumnIndex("content");
		if (colnum != -1) {
			dto.content = cursor.getString(colnum);
		}
		colnum = cursor.getColumnIndex("insert_date");
		if (colnum != -1) {
			dto.insertDate = DateTimeUtil.toDate(cursor.getString(colnum), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
		}
		colnum = cursor.getColumnIndex("page_num");
		if (colnum != -1) {
			dto.pageNum = cursor.getInt(colnum);
		}
		colnum = cursor.getColumnIndex("item");
		if (colnum != -1) {
			dto.item = cursor.getString(colnum);
		}
		colnum = cursor.getColumnIndex("favorite_flg");
		if (colnum != -1) {
			dto.favoriteFlg = toBool(cursor.getInt(colnum));
		}
		colnum = cursor.getColumnIndex("memo_id");
		if (colnum != -1) {
			dto.memoId = cursor.getString(colnum);
		}
		colnum = cursor.getColumnIndex("reading_count");
		if (colnum != -1) {
			dto.readingCount = cursor.getInt(colnum);
		}
		colnum = cursor.getColumnIndex("reading_date");
		if (colnum != -1) {
			dto.readingDate = DateTimeUtil.toDate(cursor.getString(colnum), "UTC", DateTimeFormat.yyyyMMddHHmmss_hyphen);
		}
		return dto;
	}

	public List<ContentBookmarkDto> getAllBookmarkList() {
		return rawQueryGetDtoList(generateGetBookmarkQuery(null, null), null, ContentBookmarkDto.class);
	}

	public List<ContentBookmarkDto> getBookmarkList(long contentId) {
		return rawQueryGetDtoList(generateGetBookmarkQuery(""+ contentId, null), new String[]{""+ contentId}, ContentBookmarkDto.class);
	}

	public boolean isBookmark(long contentId, int pgaeNum) {
		int count = rawQueryGetInt("select COUNT(content_id) AS content_count from t_content_bookmark where content_id=? AND page_num=?", new String[]{""+ contentId,""+ pgaeNum});
		return count > 0;
	}

	private String generateGetBookmarkQuery(String contentId , String pageNum) {
		StringBuffer sql = new StringBuffer();

		sql.append(" SELECT tcb.content_id AS content_id ");
		sql.append(" , tcb.page_num AS page_num  ");
		sql.append(" , tcb.page_text AS page_text ");
		sql.append(" , tcb.page_thumbnail_name AS page_thumbnail_name ");
		sql.append(" , tcb.insert_date AS insert_date ");
		sql.append(" , tcb.update_date AS update_date ");
		sql.append(" , tc.content_name AS content_name ");
		sql.append(" FROM t_content_bookmark  AS tcb ");
		sql.append(" INNER JOIN t_content AS tc ");
		sql.append(" ON tcb.content_id = tc.content_id  ");

		if (contentId != null && pageNum != null) {
			sql.append("WHERE tcb.content_id = ?  AND tcb.page_num = ? ");
		}else //noinspection VariableNotUsedInsideIf
			if (contentId != null ) {
			sql.append("WHERE tcb.content_id = ?  ");
		}

		sql.append(" ORDER BY tc.content_name , tcb.page_num  ");
		return sql.toString();
	}


	public void insertBookmark(ContentBookmarkDto dto) {
		insert("insert into t_content_bookmark (content_id, page_num, page_text, page_thumbnail_name, insert_date, update_date) values (?,?,?,?,?,?)", dto.getInsertValues());
	}

	public void deleteBookmark(long contentId, int pageNum) {
		delete("t_content_bookmark", "content_id=? AND page_num=?", new String[]{""+ contentId, ""+ pageNum});
	}

	public List<MydataDto> getAllMyDataList(ContentSortingType contentSortingType, SortDirection sortDirection, String[] contentTypes){
		StringBuffer sql = new StringBuffer();

		sql.append(" SELECT * ");
		sql.append("      ,  page_text AS content ");
		sql.append("      , 'bookmark' item ");
		sql.append(" FROM t_content_bookmark tcm ");
		sql.append(" INNER JOIN t_content tc");
		sql.append("     ON tcm.content_id = tc.content_id ");

		// コンテンツタイプ
		if (contentTypes != null && contentTypes.length > 0) {
			sql.append("    AND tc.content_type IN (");
			for (int i = 0; i < contentTypes.length; i++) {
				sql.append("\"");
				sql.append(contentTypes[i]);
				sql.append("\"");
				if (i != contentTypes.length - 1) {
					sql.append(",");
				}
			}
			// 下位互換のためPDF、空文字も入れておく
			sql.append(",\"PDF\",'') ");
		}

		if (contentSortingType != null) {
			switch (contentSortingType) {
			case ContentName:
				sql.append("  ORDER BY tc.content_name ");
				break;
			case ContentNameKana:
				sql.append("  ORDER BY tc.content_name_kana ");
				break;
			case DeliveryStartDate:
				sql.append("  ORDER BY tc.delivery_start_date ");
				break;
			case DownloadDate:
				sql.append("  ORDER BY tc.download_end_date ");
				break;
			case ReadingCount:
				sql.append("  ORDER BY tc.reading_count ");
				break;
			case ReadingDate:
				sql.append("  ORDER BY tc.reading_date ");
				break;
			case ContentSize:
				sql.append("  ORDER BY tc.content_size ");
				break;
			case ContentId:
				sql.append("  ORDER BY tc.contract_content_id ");
				break;
			default:
				sql.append("  ORDER BY tc.delivery_start_date ");
				sortDirection = SortDirection.DESC;
				break;
			}
		} else {
			sql.append("  ORDER BY tc.delivery_start_date ");
			sortDirection = SortDirection.DESC;
		}

		sql.append(StringUtil.Space);

		if (sortDirection != null) {
			sql.append(sortDirection.direction());
		} else {
			sql.append(SortDirection.DESC.direction());
		}

		sql.append(StringUtil.Space);

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

		return rawQueryGetMyDataDtoList(sql.toString(), null);

	}

	public List<MydataDto> getMyDataList(long contentId) {
		return rawQueryGetMyDataDtoList("select content_id, (select content_name from t_content where tcb.content_id = content_id) content_name, page_text AS content, insert_date, page_num, 'bookmark' item from t_content_bookmark tcb where content_id = ? order by content_name, tcb.page_num", new String[] { "" + contentId });
	}

	private List<MydataDto> rawQueryGetMyDataDtoList(String sql, String[] bindArgs) {
		SQLiteDatabase db = dbConn.getDatabase();
		Cursor cursor = null;
		try {
			List<MydataDto> list = new ArrayList<MydataDto>();
			if (Logger.isVerboseEnabled()) {
				Logger.v(TAG, "%s [%s]", sql, join(bindArgs));
			}
			cursor = db.rawQuery(sql, bindArgs);
			while (cursor.moveToNext()) {
				list.add(convertToMyData(cursor));
			}
			return list;
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
	}
}
