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.ContentMemoDto;
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 ContentMemoDao extends AbstractDao {
	private static final String TAG = "ContentMemoDao";

	/*package*/ ContentMemoDao() {
	}

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

		int colnum = cursor.getColumnIndex("content_id");
		if (colnum != -1) {
			dto.contentId = cursor.getLong(colnum);
		}
		colnum = cursor.getColumnIndex("memo_id");
		if (colnum != -1) {
			dto.memoId = cursor.getString(colnum);
		}
		colnum = cursor.getColumnIndex("page_num");
		if (colnum != -1) {
			dto.pageNum = cursor.getInt(colnum);
		}
		colnum = cursor.getColumnIndex("memo");
		if (colnum != -1) {
			dto.memo = cursor.getString(colnum);
		}
		colnum = cursor.getColumnIndex("axis_x");
		if (colnum != -1) {
			dto.axisX = cursor.getInt(colnum);
		}
		colnum = cursor.getColumnIndex("axis_y");
		if (colnum != -1) {
			dto.axisY = cursor.getInt(colnum);
		}
		colnum = cursor.getColumnIndex("copy_flg");
		if (colnum != -1) {
			dto.copyFlg = toBool(cursor.getInt(colnum));
		}
		colnum = cursor.getColumnIndex("del_flg");
		if (colnum != -1) {
			dto.delFlg = toBool(cursor.getInt(colnum));
		}
		colnum = cursor.getColumnIndex("insert_date");
		if (colnum != -1) {
			dto.insertDateStr = cursor.getString(colnum);
			dto.insertDate = DateTimeUtil.toDate(dto.insertDateStr, DateTimeFormat.yyyyMMddHHmmssSSS_hyphen);
		}
		colnum = cursor.getColumnIndex("update_date");
		if (colnum != -1) {
			dto.updateDateStr = cursor.getString(colnum);
			dto.updateDate = DateTimeUtil.toDate(dto.updateDateStr, DateTimeFormat.yyyyMMddHHmmssSSS_hyphen);
		}

		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<ContentMemoDto> getMemoList(long contentId, int pageNum) {
		return rawQueryGetDtoList("select * from t_content_memo where content_id=? AND page_num=? order by page_num", // TODO: page_numで絞り込んでいるのにorder by 不要
				new String[]{""+ contentId, ""+ pageNum}, ContentMemoDto.class);
	}

	public ContentMemoDto getCopiedMemo() {
		return rawQueryGetDto("select * from t_content_memo where copy_flg=1", null, ContentMemoDto.class);
	}

	public List<ContentMemoDto> getMemoList(long contentId) {
		return rawQueryGetDtoList("select * from t_content_memo where content_id=? order by page_num", new String[]{""+ contentId}, ContentMemoDto.class);
	}
	
	public List<ContentMemoDto> getAllMemoList() {
		return rawQueryGetDtoList("select * from t_content_memo", null, ContentMemoDto.class);
	}

	public void clearCopiedMemo() {
		update("update t_content_memo set copy_flg=0, del_flg=0", null);
	}

	public boolean setCopiedMemo(ContentMemoDto dto) {
		boolean result = false;

		beginTransaction();

		try {
//			update("update t_content_memo set copy_flg=0, del_flg=0", null);
			clearCopiedMemo();
			result = updateMemo(dto);
			commit();
		} catch (Exception e) {
			rollback();
		}

		return result;
	}


	public void insertMemo(ContentMemoDto dto) {
		insert("insert into t_content_memo (content_id, memo_id, page_num, memo, axis_x, axis_y, copy_flg, del_flg, insert_date, update_date) values (?,?,?,?,?,?,?,?,?,?)", dto.getInsertValues());
	}

	public void insertMemos(List<ContentMemoDto> dtoList) {
		insert("insert into t_content_memo (content_id, memo_id, page_num, memo, axis_x, axis_y, copy_flg, del_flg, insert_date, update_date) values (?,?,?,?,?,?,?,?,?,?)", dtoList);
	}

	public void deleteMemo(long contentId, int pageNum, String memoId) {
		delete("t_content_memo", "content_id=? and page_num=? and memo_id=?", new String[]{""+ contentId, ""+ pageNum, ""+ memoId});
	}

	public void deleteMemo(ContentMemoDto dto) {
		delete("t_content_memo", "content_id=? and page_num=? and axis_x=? and axis_y=?", dto.getKeyValues());
	}

	public boolean updateMemo(ContentMemoDto dto) {
		long count = update("update t_content_memo set memo=?, copy_flg=?, del_flg=?, update_date=? where content_id=? and page_num=? and axis_x=? and axis_y=?", dto.getUpdateValues());
		return (count > 0);
	}

	public boolean moveMemo(ContentMemoDto dto, int page_num) {
		long count = update("update t_content_memo set page_num=? where content_id=? and page_num=? and axis_x=? and axis_y=?", new Object[]{page_num, dto.contentId, dto.pageNum, dto.axisX, dto.axisY});
		return (count > 0);
	}
	
	public void restoreMemo(ContentMemoDto dto) {
		ContentMemoDto sameMemo;
		sameMemo = rawQueryGetDto("select * from t_content_memo where memo_id = "+ "\"" + dto.memoId + "\"" , null, ContentMemoDto.class);
		
		if (sameMemo != null) {
			// 同じIDのメモが存在する場合、サーバ側の日付が最新の場合にのみ更新する
			if (sameMemo.updateDate.before(dto.updateDate)) {
				updateMemo(dto);
			}
		} else {
			String[] args = new String[]{"" + dto.contentId, "" + dto.pageNum, "" + dto.axisX, "" + dto.axisY};
			int count = rawQueryGetInt("select Count(content_id) from t_content_memo where content_id=? and page_num=? and axis_x=? and axis_y=?", args);
			if (count == 0) {
				dto.insertDate = dto.updateDate;
				insertMemo(dto);
			}
		}
	}

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

		sql.append(" SELECT * ");
		sql.append("      , memo AS content ");
		sql.append("      , 'memo' item ");
		
		sql.append(" FROM t_content_memo 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 tcm.content_id = content_id) content_name, memo AS content, insert_date, page_num, memo_id, 'memo' item from t_content_memo tcm where content_id = ? order by content_name, tcm.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();
			}
		}
	}
}
