package webchurch.push.dao;

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

import webchurch.push.model.PushDTO;
import webchurch.push.model.PushId;
import webchurch.push.util.Connector;
import webchurch.push.util.PushName;

public class CopyOfPushDAOImpl implements PushDAO{
	private Connection conn = Connector.getConnection();

	public CopyOfPushDAOImpl() {
		super();
		// TODO Auto-generated constructor stub
	}

	public List<String> getAllApiKey(){
		List<String> apiList = new ArrayList<String>();
		String sql = "select andro_push, iphone_push from newchurch_info where andro_push is not null";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				apiList.add("android : " + rs.getString(PushName.GOOGLE_KEY));
				apiList.add("IOS : " + rs.getString(PushName.APPLE_KEY));
			}
		}catch(Exception e){
			
		}
		return apiList;
	}
	
	@Override
	public String getApiKey(String communityId, String type) {
		// TODO Auto-generated method stub
		String sql = "select andro_push, iphone_push from newchurch_info where communityid = ?";
		String apiKey = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, communityId);
			rs = pstmt.executeQuery();
			if(rs.next()){
				if("A".equals(type)){
					apiKey = rs.getString("andro_push");
				}else{
					apiKey = rs.getString("iphone_push");
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			try {
				rs.close();
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
			return apiKey;	
		}
	}
	
	@Override
	public String getCertName(PushDTO dto) {
		// TODO Auto-generated method stub
		String sql = "select directory from ioc.dbo.com where communityId = ?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getCommunityId());
			rs = pstmt.executeQuery();
			if(rs.next()){
				return rs.getString("directory");
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
	
	

	@Override
	public List<String> getIds(PushDTO dto, String type) {
		// TODO Auto-generated method stub
		List<String> list = new ArrayList<String>();
		String sql = "select pd.deviceId from IOC.DBO.Push_Device as pd left outer join IOC.DBO.push_set as ps on pd.pid = ps.pid where pd.isPush = 1 and pd.type = ? ";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		if(null != dto.getBoard_idx() && null != dto.getBoard_type() && !"".equals(dto.getBoard_type()) && !"".equals(dto.getBoard_idx())){
			sql += " and ps.pid = pd.pid and ps.board = " + dto.getBoard_idx() + " and ps.board_idx = " + dto.getBoard_idx();
		}
		System.out.println("Query : " + sql);
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, type);
			rs = pstmt.executeQuery();
			int i = 0;
			while(rs.next()){
				list.add(rs.getString(1));
				System.out.println(i++);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println(e.getMessage());
		} finally{
			try {
				rs.close();
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public List<String> removeIds(List<String> list) {
		// TODO Auto-generated method stub
		String sql = "delete from IOC.DBO.Push_Device where deviceId = ?";
		int count = 0;
		PreparedStatement pstmt = null;
		try{
			for (String str : list) {
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, str);
				int result = pstmt.executeUpdate();
				if(result == 1){
					str += " is removed";
					count += 1;
				}else{
					str += " is not removed";
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public List<PushId> updateIds(List<PushId> ids) {
		// TODO Auto-generated method stub
		List<String> list = new ArrayList<String>();
		for(PushId pi : ids){
			list.add(pi.getAfterId());
		}
		this.removeIds(list);
		String sql = "update IOC.DBO.Push_Device set deviceId = ? where deviceId = ?";
		PreparedStatement pstmt = null;
		int count = 0;
		try{
			for(PushId pi : ids){
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, pi.getAfterId());
				pstmt.setString(2, pi.getBeforeId());
				int result = pstmt.executeUpdate();
				if(result == 1){
					pi.setChanged(true);
					count += 1;
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ids;
	}

	@Override
	public String badgeClear(PushDTO dto) {
		// TODO Auto-generated method stub
		String strResult = "is not clear";
		String sql = "update update IOC.DBO.Push_Device set badge = ? where deviceId = ?";
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, 0);
			pstmt.setString(2, dto.getDeviceId());
			int result = pstmt.executeUpdate();
			if (result == 1) {
				strResult = "is clear";
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return strResult;
	}

	@Override
	public String execIOSBadge(List<String> list) {
		// TODO Auto-generated method stub
		String strResult = "";
		String sql = "update update IOC.DBO.Push_Device set badge = badge + 1 where deviceId = ?";
		PreparedStatement pstmt = null;
		try{
			int sucNum = 0;
			pstmt = conn.prepareStatement(sql);
			for(String str : list){
				pstmt.setString(1, str);
				if(1 == pstmt.executeUpdate()){
					sucNum++;
				}
			}
			strResult += sucNum;
			strResult += " 개 뱃지 설정.";
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				pstmt.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		return strResult;
	}

	@Override
	public String insertDevice(PushDTO dto) {
		// TODO Auto-generated method stub
		String type = dto.getType();
		String deviceId = dto.getDeviceId();
		String communityId = dto.getCommunityId();
		if("Android".equals(type)){
			type = "A";
		}else if("IOS".equals(type)){
			type = "I";
		}
		String sql = "insert into IOC.DBO.Push_Device (deviceId, communityId, type) values ('"+deviceId+"', '"+communityId+"', '"+type+"')";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			int result = pstmt.executeUpdate();
			if(result == 1){
				return "insert success";
			}else if(result == 0){
				return "insert error";
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			return "error";
		}
	}

	@Override
	public String insertLog(PushDTO dto, String id, String ip) {
		// TODO Auto-generated method stub
		String sql = "insert into IOC.DBO.Push_Log (id, communityid, msg, link, senddate, ip) values (?, ?, ?, ?, getdate(), ?)";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, dto.getCommunityId());
			pstmt.setString(3, dto.getMsg());
			pstmt.setString(4, dto.getLink());
			pstmt.setString(5, ip);
			int result = pstmt.executeUpdate();
			if(result == 1){
				return "insert success";
			}else if(result == 0){
				return "insert error";
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			return "error";
		}
	}
}