package imageboard.dao;

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

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

import board.dto.BoardDTO;
import imageboard.dto.ImageboardDTO;

public class ImageboardDAO {
	private Connection con;
	private PreparedStatement pstmt;
	private ResultSet res;
	private DataSource ds;
	
	public ImageboardDAO() {
		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();
		}
	}
	// streamClose end
	
	
	// 데이터 넣기
	public int imageboardWrite(ImageboardDTO dto) {
		int su = 0;
		try {
			String sql = "insert into imageboard values(seq_imageboard.nextval,?,?,?,?,?,?,sysdate)";
			con = ds.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getImageId());
			pstmt.setString(2, dto.getImageName());
			pstmt.setInt(3, dto.getImagePrice());
			pstmt.setInt(4, dto.getImageQty());
			pstmt.setString(5, dto.getImageContent());
			pstmt.setString(6, dto.getImageFile());
			su=pstmt.executeUpdate();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			streamClose(res, pstmt, con);
		}
		return su;
	}
	// insert() end
	
	// 글목록
	public ArrayList<ImageboardDTO> imageboardList(int startNum, int lastNum){
		ArrayList<ImageboardDTO> list = new ArrayList<>();
		ImageboardDTO dto = null;
		
		try {
			String sql = "select * from" +
					"(select rownum rn, tt.* from " +
					"(select * from imageboard 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 ImageboardDTO();
				dto.setSeq(res.getInt("seq"));
				dto.setImageId(res.getString("imageId"));
				dto.setImageName(res.getString("imageName"));
				dto.setImagePrice(res.getInt("imagePrice"));
				dto.setImageQty(res.getInt("imageQty"));
				dto.setImageContent(res.getString("imageContent"));
				dto.setImageFile(res.getString("imageFile"));
				dto.setLogtime(res.getString("logtime"));
				list.add(dto);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			streamClose(res, pstmt, con);
		}
		
		
		return list;
	}
	// imageboardList() end
	
	// 페이징
	public int getTotalArticle() {
		int total = 0;
		try {
			String sql = "select count(*) from imageboard";
			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 ImageboardDTO imageboardView(int seq) {
		ImageboardDTO dto=null;
		try {
			String sql = "select * from imageboard where seq=?";
			con= ds.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, seq);
			res = pstmt.executeQuery();
			if(res.next()) {
				dto=new ImageboardDTO();
				dto.setSeq(res.getInt("seq"));
				dto.setImageId(res.getString("imageId"));
				dto.setImageName(res.getString("imageName"));
				dto.setImagePrice(res.getInt("imagePrice"));
				dto.setImageQty(res.getInt("imageQty"));
				dto.setImageContent(res.getString("imageContent"));
				dto.setImageFile(res.getString("imageFile"));
				dto.setLogtime(res.getString("logtime"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			streamClose(res, pstmt, con);
		}
		return dto;
	}
	// imageboardView() end
	
	
	// 글 수정
	public int modifyimageboard(ImageboardDTO dto) {
		int su = 0;
		try {
			String sql = null;
			if(dto.getImageFile()== null) {
				sql="update imageboard set imageId=?, imageName=?. imagePrice=?. imageQty=?. imageContent=?. imageFile=? where seq=?";
			} else {
				sql="update imageboard set imageid=?, imagename=?, imageprice=?, imageqty=?, imagecontent=? where seq=?";
			}
			con = ds.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getImageId());
			pstmt.setString(2, dto.getImageName());
			pstmt.setInt(3, dto.getImagePrice());
			pstmt.setInt(4, dto.getImageQty());
			pstmt.setString(5, dto.getImageContent());
			pstmt.setInt(6, dto.getSeq());
			if(dto.getImageFile()== null) {
				pstmt.setString(6, dto.getImageFile());
				pstmt.setInt(7, 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 deleteimageboard(int seq) {
		int su = 0;
		try {
			String sql = "delete from imageboard 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 
}