package board.dao;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import board.dto.BoardDTO;

public class BoardDAO {
	private Connection con;
	private PreparedStatement pstmt;
	private ResultSet res;
	private DataSource ds;
	
	public BoardDAO() {
		try {
			// InitialContext 객체 생성 후 설정된 정보 get
			Context context = new InitialContext();
			
			// Context 클래스의 lookup() 메서드는 'java:comp/env/jdbc/oracle'을 가지고 DataSource 객체를 구함.
			// - lookup() 메서드를 사용해서 naming 서비스에서 자원 검색.
			// - JNDI의 이름은 'java:comp/env/'에 등록되어 있음.
			
			ds = (DataSource) context.lookup("java:comp/env/jdbc/oracle");
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	// db 통로
	public void streamClose(ResultSet res, PreparedStatement pstmt, Connection con) {
		try {
			if(res != null) res.close();
			if(pstmt != null) pstmt.close();
			if(con != null) con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	// streamClase end
	
	// 글쓰기
	public int write(BoardDTO dto) {
		int su = 0;
		int hit = 1;
		try {
			String sql = "insert into board values(board_seq.nextval,?,?,?,?,?,sysdate)";
			con = ds.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getId());
			pstmt.setString(2, dto.getName());
			pstmt.setString(3, dto.getSubject());
			pstmt.setString(4, dto.getContent());
			pstmt.setInt(5, hit);
			su=pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			streamClose(res,pstmt,con);
		}
		return su;
	}
	// insert() end
	
	// 글목록
	public ArrayList<BoardDTO> BoardList(int startNum, int lastNum){
		ArrayList<BoardDTO> list = new ArrayList<>();
		BoardDTO dto = null;
		
		try {
			String sql = "select seq, id, name, subject, content, hit, to_char(logtime, 'YYYY.MM.DD') as logtime from " +
					"(select rownum rn, tt.* from " +
					"(select * from board order by seq desc) tt)" + "where rn>=? and rn<=?";
			con = ds.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, startNum);
			pstmt.setInt(2, lastNum);
			res = pstmt.executeQuery();
			
			while(res.next()){
				dto = new BoardDTO();
				dto.setSeq(res.getInt("seq"));
				dto.setId(res.getString("id"));
				dto.setName(res.getString("name"));
				dto.setSubject(res.getString("subject"));
				dto.setContent(res.getString("content"));
				dto.setHit(res.getInt("hit"));
				dto.setLogtime(res.getString("logtime"));
				list.add(dto);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			streamClose(res, pstmt, con);
		}
		
		
		return list;
	}
	// BoardList() end
	
	// 페이징
	public int getTotalArticle() {
		int total = 0;
		try {
			String sql = "select count(*) from board";
			con= ds.getConnection();
			pstmt=con.prepareStatement(sql);
			res = pstmt.executeQuery();
			if(res.next()) {
				total=res.getInt(1); // 조회된 컬럼 값.
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			streamClose(res, pstmt, con);
		}
		return total;
	}
	// getTotalArticle() end
	 
	// 글 보기
	public BoardDTO boardView(int seq) {
		PreparedStatement pstmt1=null;
		BoardDTO dto=null;
		try {
			String sql = "select * from board where seq=?";
			con= ds.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, seq);
			res = pstmt.executeQuery();
			if(res.next()) {
				dto=new BoardDTO();
				dto = boardset(res);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			streamClose(res, pstmt, con);
			try {
				if(pstmt1 != null) pstmt1.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return dto;
	}
	// boardView() end 
	
	// board 전부 set
	public BoardDTO boardset(ResultSet res) throws SQLException {
		BoardDTO dto=new BoardDTO();
		dto.setSeq(res.getInt("seq"));
		dto.setName(res.getString("name"));
		dto.setId(res.getString("id"));
		dto.setSubject(res.getString("subject"));
		dto.setContent(res.getString("content"));
		dto.setHit(res.getInt("hit"));
		return dto;
	}
	// boardset() end
	
	// 조회수
	public void updatehit(int seq) {
		try {
			String sql = "update board set hit=hit+1 where seq=?";
			con= ds.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, seq);
			pstmt.executeUpdate();
		} catch (Exception e) {
				e.printStackTrace();
		} finally {
			streamClose(res, pstmt, con);
		}
	}
	// updatehit end
	
	// 글 수정
	public int modifyboard(BoardDTO dto) {
		int su = 0;
		try {
			String sql = "update board set subject=?, content=? where seq=?";
			con = ds.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getSubject());
			pstmt.setString(2, dto.getContent());
			pstmt.setInt(3, dto.getSeq());
			su=pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if(con!=null) con.close();
				if(pstmt!=null) pstmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return su;
	}
	// modifyboard() end
	
	// 글 삭제
	public int boarddelete(int seq) {
		int su = 0;
		try {
			String sql = "delete from board where seq=?";
			con= ds.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, seq);
			su = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			streamClose(res, pstmt, con);
		}
		return su;
	}
	// boarddelete() end 
}