package com.runwit.ebookstore.services.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.runwit.ebookstore.model.BugStatusModel;
import com.runwit.ebookstore.model.GroupModel;
import com.runwit.ebookstore.services.IGroupServices;

public class GroupsDAO extends DbDAO implements IGroupServices {

	@Override
	public Object mapRowToModel(ResultSet rs) throws SQLException {
		
		GroupModel model = new GroupModel(
				rs.getInt("groupid"), 
				rs.getString("name"),
				null
				);

		return model;
	}
	
	public boolean createGroup(GroupModel model) {
		String sql = "insert into groups(name) values(?)";
		String querySql = "select max(groupid) from groups";
		String sql2 = "insert into groupstatus(groupId, bsid) values(?,?)";
		
		try
		{
			openConnection();
			conn.setAutoCommit(false);
			openPreparedStatement(sql);
			pstmt.setString(1, model.getName());
			int iRet = pstmt.executeUpdate();
			closePreparedStatement();
			openPreparedStatement(querySql);
			rs  = pstmt.executeQuery();
			if(rs.next()) {
				model.setGroupId(rs.getInt(1));
			}
			closeResultSet();
			closePreparedStatement();
			List sList = model.getStatusList();
			if(iRet == 1) {
				openPreparedStatement(sql2);
				for(int i=0; i<sList.size(); i++) {
					pstmt.setInt(1, model.getGroupId());
					pstmt.setInt(2, ((BugStatusModel)(sList.get(i))).getBsid());
					pstmt.addBatch();
				}
				pstmt.executeBatch();
			}
			conn.commit();
			return true;
		}catch(SQLException ex) {
			ex.printStackTrace();
			return false;
		}
		finally{
			closePreparedStatement();
			closeConnection();
		}
	}
	
	public boolean setGroupById(GroupModel model) {
		String sql = "update groups set name=? where groupid=?";
		String delSql = "delete from groupstatus where groupid=?";
		String sql2 = "insert into groupstatus(groupId, bsid) values(?,?)";
		
		try
		{
			openConnection();
			conn.setAutoCommit(false);
			openPreparedStatement(sql);
			pstmt.setString(1, model.getName());
			pstmt.setInt(2, model.getGroupId());
			int iRet = pstmt.executeUpdate();
			closePreparedStatement();
			openPreparedStatement(delSql);
			pstmt.setInt(1, model.getGroupId());
			pstmt.executeUpdate();
			closePreparedStatement();
			List sList = model.getStatusList();
			if(iRet == 1) {
				openPreparedStatement(sql2);
				for(int i=0; i<sList.size(); i++) {
					pstmt.setInt(1, model.getGroupId());
					pstmt.setInt(2, ((BugStatusModel)(sList.get(i))).getBsid());
					pstmt.addBatch();
				}
				pstmt.executeBatch();
				closePreparedStatement();
			}
			conn.commit();
			return true;
		}catch(SQLException ex) {
			ex.printStackTrace();
			return false;
		}
		finally{
			closeConnection();
		}
	}
	
	public boolean removeGroup(GroupModel model) {
		String sql1 = "delete from groupstatus where groupid=" + model.getGroupId();
		String sql2 = "delete from groups where groupid=" + model.getGroupId();
		try
		{
			openConnection();
			openStatement();
			stmt.addBatch(sql1);
			stmt.addBatch(sql2);
			stmt.executeBatch();
			
			return true;
			
		}catch(SQLException ex) {
			ex.printStackTrace();
			return false;
		}
		finally {
			closeStatement();
			closeConnection();
		}
	}
	
	private List listGroupsBySql(String sql) {
		List groups = new ArrayList();
		try {
			openConnection();
			openStatement();
			DebugUtil.printSql(sql);
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				GroupModel groupModel = (GroupModel)mapRowToModel(rs);
				groups.add(groupModel);
				
			}
			closeResultSet();
			for(int i=0; i<groups.size(); i++) {
				try{
					rs = stmt
							.executeQuery("select bugstatus.* from bugstatus, groupstatus where bugstatus.bsid=groupstatus.bsid and groupid="
									+ ((GroupModel) groups.get(i)).getGroupId());
					while(rs.next()) {
						BugStatusModel bsModel = new BugStatusModel(rs
								.getInt("bsid"), rs.getString("name"), rs
								.getString("remark"));
						((GroupModel)groups.get(i)).getStatusList().add(bsModel);
					}
				}catch(SQLException ex) {
					ex.printStackTrace();
				}finally{
					closeResultSet();
				}
			}

		}catch(SQLException ex) {
			ex.printStackTrace();
		}
		finally {
			closeStatement();
			closeConnection();
		}
		
		return groups;

	}
	
	
	public List listGroups() {
		
		return listGroupsBySql("select * from groups");

	}
	
	public GroupModel getModel(int groupId) {
		
		List models = listGroupsBySql("select * from groups where groupid="+groupId);
		if(models != null && models.size() > 0) 
			return (GroupModel)models.get(0);
		
		return null;
	}


}