package bbs;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.io.File;

public class BbsDAO {

	private Connection conn;
	private ResultSet rs;
	
	public BbsDAO() {
		try {
			String dbURL = "jdbc:mysql://localhost:3306/JSP?useSSL=false&serverTimezone=UTC";
			String dbID = "root";
			String dbPassword = "-";
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(dbURL, dbID, dbPassword);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public String getDate() {
		String SQL = "SELECT NOW()";
		try{

			PreparedStatement pstmt = conn.prepareStatement(SQL);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getString(1);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return ""; //db오류
	}
	
	public int getNext() {
		String SQL = "SELECT bbsID FROM BBS ORDER BY bbsID DESC";
		try{

			PreparedStatement pstmt = conn.prepareStatement(SQL);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1) + 1;
			}
			return 1; //현재가 첫 번째 게시글이 ㄴ경우
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public int ngetNext() {
		String SQL = "SELECT bbsID FROM BBS WHERE bbstype != 3 ORDER BY bbsID DESC";
		try{

			PreparedStatement pstmt = conn.prepareStatement(SQL);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1) + 1;
			}
			return 1; //현재가 첫 번째 게시글이 ㄴ경우
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public int cgetNext() {
		String SQL = "SELECT bbsID FROM BBS WHERE bbstype = 1 ORDER BY bbsID DESC";
		try{

			PreparedStatement pstmt = conn.prepareStatement(SQL);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1) + 1;
			}
			return 1; //현재가 첫 번째 게시글이 ㄴ경우
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public int sgetNext() {
		String SQL = "SELECT bbsID FROM BBS WHERE bbstype = 2 ORDER BY bbsID DESC";
		try{

			PreparedStatement pstmt = conn.prepareStatement(SQL);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1) + 1;
			}
			return 1; //현재가 첫 번째 게시글이 ㄴ경우
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public int bgetNext() {
		String SQL = "SELECT bbsID FROM BBS WHERE bbstype = 3 ORDER BY bbsID DESC";
		try{

			PreparedStatement pstmt = conn.prepareStatement(SQL);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1) + 1;
			}
			return 1; //현재가 첫 번째 게시글이 ㄴ경우
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	
	public int write(String bbsTitle, String bbsSub, String bbsIn, int bbsType, String bbspic) {
		String SQL = "INSERT INTO bbs VALUES (?, ?, ?, ?, ?, ?)";
		try{
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			int nextNum = getNext();
			pstmt.setInt(1, nextNum);
			pstmt.setString(2, getDate());
			pstmt.setString(3, bbsTitle);
			pstmt.setString(4, bbsSub);
			pstmt.setString(5, bbsIn);
			pstmt.setInt(6, bbsType);
			pstmt.executeUpdate();
			SQL = "INSERT INTO pictures VALUES (?, ?)";
			try {
				pstmt = conn.prepareStatement(SQL);
				pstmt.setString(1, bbspic);
				pstmt.setInt(2, nextNum);
			}catch (Exception e) {
				e.printStackTrace();
			}
			return pstmt.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	
	
	public ArrayList<Bbs> getList(int pageNumber) {	//관리자 전체게시판용
		 String SQL = "SELECT * FROM BBS WHERE bbsID < ? ORDER BY `bbsID` DESC LIMIT 12";
		 ArrayList<Bbs> list = new ArrayList<Bbs>();
		 try {
			 PreparedStatement pstmt = conn.prepareStatement(SQL);
			 pstmt.setInt(1, getNext() - (pageNumber -1) * 12 );
			 rs = pstmt.executeQuery();
			 while (rs.next()) {
				 Bbs bbs = new Bbs();
				 bbs.setBbsID(rs.getInt(1));
				 bbs.setDate(rs.getString(2));
				 bbs.setBbsTitle(rs.getString(3));
				 bbs.setBbsSub(rs.getString(4));
				 bbs.setBbsIn(rs.getString(5));
				 bbs.setBbsType(rs.getInt(6));
				 String psql = "SELECT panme FROM pictures WHERE bbsID = ?";
				 try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getBbsID());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setPicture(ex.getString(1));
					 } else {
		                    bbs.setPicture(null); // 해당하는 레코드가 없을 경우 null로 설정
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
				 list.add(bbs);
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
		 return list;
	}
	
	public ArrayList<Bbs> ngetList(int pageNumber) {	//일반유저 전체게시판용
		 String SQL = "SELECT * FROM BBS WHERE bbsID < ? AND bbstype != 3 ORDER BY `bbsID` DESC LIMIT 12";
		 ArrayList<Bbs> list = new ArrayList<Bbs>();
		 try {
			 PreparedStatement pstmt = conn.prepareStatement(SQL);
			 pstmt.setInt(1, ngetNext() - (pageNumber -1) * 12 );
			 rs = pstmt.executeQuery();
			 while (rs.next()) {
				 Bbs bbs = new Bbs();
				 bbs.setBbsID(rs.getInt(1));
				 bbs.setDate(rs.getString(2));
				 bbs.setBbsTitle(rs.getString(3));
				 bbs.setBbsSub(rs.getString(4));
				 bbs.setBbsIn(rs.getString(5));
				 bbs.setBbsType(rs.getInt(6));
				 String psql = "SELECT panme FROM pictures WHERE bbsID = ?";
				 try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getBbsID());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setPicture(ex.getString(1));
					 } else {
		                    bbs.setPicture(null); // 해당하는 레코드가 없을 경우 null로 설정
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
				 list.add(bbs);
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
		 return list;
	}
	
	public ArrayList<Bbs> cgetList(int pageNumber) {	//코딩게시판용
		 String SQL = "SELECT * FROM BBS WHERE bbsID < ? AND bbstype = 1 ORDER BY `bbsID` DESC LIMIT 12";
		 ArrayList<Bbs> list = new ArrayList<Bbs>();
		 try {
			 PreparedStatement pstmt = conn.prepareStatement(SQL);
			 pstmt.setInt(1, cgetNext() - (pageNumber -1) * 12 );
			 rs = pstmt.executeQuery();
			 while (rs.next()) {
				 Bbs bbs = new Bbs();
				 bbs.setBbsID(rs.getInt(1));
				 bbs.setDate(rs.getString(2));
				 bbs.setBbsTitle(rs.getString(3));
				 bbs.setBbsSub(rs.getString(4));
				 bbs.setBbsIn(rs.getString(5));
				 bbs.setBbsType(rs.getInt(6));
				 String psql = "SELECT panme FROM pictures WHERE bbsID = ?";
				 try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getBbsID());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setPicture(ex.getString(1));
					 } else {
		                    bbs.setPicture(null); // 해당하는 레코드가 없을 경우 null로 설정
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
				 list.add(bbs);
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
		 return list;
	}
	
	public ArrayList<Bbs> sgetList(int pageNumber) {	//잡담게시판용
		 String SQL = "SELECT * FROM BBS WHERE bbsID < ? AND bbstype = 2 ORDER BY `bbsID` DESC LIMIT 12";
		 ArrayList<Bbs> list = new ArrayList<Bbs>();
		 try {
			 PreparedStatement pstmt = conn.prepareStatement(SQL);
			 pstmt.setInt(1, sgetNext() - (pageNumber -1) * 12 );
			 rs = pstmt.executeQuery();
			 while (rs.next()) {
				 Bbs bbs = new Bbs();
				 bbs.setBbsID(rs.getInt(1));
				 bbs.setDate(rs.getString(2));
				 bbs.setBbsTitle(rs.getString(3));
				 bbs.setBbsSub(rs.getString(4));
				 bbs.setBbsIn(rs.getString(5));
				 bbs.setBbsType(rs.getInt(6));
				 String psql = "SELECT panme FROM pictures WHERE bbsID = ?";
				 try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getBbsID());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setPicture(ex.getString(1));
					 } else {
		                    bbs.setPicture(null); // 해당하는 레코드가 없을 경우 null로 설정
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
				 list.add(bbs);
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
		 return list;
	}
	
	public ArrayList<Bbs> bgetList(int pageNumber) {	//비밀게시판용
		 String SQL = "SELECT * FROM BBS WHERE bbsID < ? AND bbstype = 3 ORDER BY `bbsID` DESC LIMIT 12";
		 ArrayList<Bbs> list = new ArrayList<Bbs>();
		 try {
			 PreparedStatement pstmt = conn.prepareStatement(SQL);
			 pstmt.setInt(1, bgetNext() - (pageNumber -1) * 12 );
			 rs = pstmt.executeQuery();
			 while (rs.next()) {
				 Bbs bbs = new Bbs();
				 bbs.setBbsID(rs.getInt(1));
				 bbs.setDate(rs.getString(2));
				 bbs.setBbsTitle(rs.getString(3));
				 bbs.setBbsSub(rs.getString(4));
				 bbs.setBbsIn(rs.getString(5));
				 bbs.setBbsType(rs.getInt(6));
				 String psql = "SELECT panme FROM pictures WHERE bbsID = ?";
				 try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getBbsID());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setPicture(ex.getString(1));
					 } else {
		                    bbs.setPicture(null); // 해당하는 레코드가 없을 경우 null로 설정
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
				 list.add(bbs);
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
		 return list;
	}
	
	public boolean nextPage(int pageNumber) {	//관리자 전체게시판용
		String SQL = "SELECT * FROM BBS WHERE bbsID < ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, getNext() - (pageNumber - 1) * 10);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public boolean nnextPage(int pageNumber) {	//일반유저 전체게시판용
		String SQL = "SELECT * FROM BBS WHERE bbsID < ? AND bbstype != 3";
		try {
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, ngetNext() - (pageNumber - 1) * 10);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public boolean cnextPage(int pageNumber) {	//코딩게시판 전용
		String SQL = "SELECT * FROM BBS WHERE bbsID < ? AND bbstype = 1";
		try {
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, cgetNext() - (pageNumber - 1) * 10);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public boolean snextPage(int pageNumber) {	//잡담게시판 전용
		String SQL = "SELECT * FROM BBS WHERE bbsID < ? AND bbstype = 2";
		try {
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, sgetNext() - (pageNumber - 1) * 10);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public boolean bnextPage(int pageNumber) {	//비밀게시판 전용
		String SQL = "SELECT * FROM BBS WHERE bbsID < ? bbstype = 3";
		try {
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, bgetNext() - (pageNumber - 1) * 10);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public Bbs getBbs(int bbsID) {
		String SQL = "Select * FROM BBS WHERE bbsID = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, bbsID);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				Bbs bbs = new Bbs();
				 bbs.setBbsID(rs.getInt(1));
				 bbs.setDate(rs.getString(2));
				 bbs.setBbsTitle(rs.getString(3));
				 bbs.setBbsSub(rs.getString(4));
				 bbs.setBbsIn(rs.getString(5));
				 bbs.setBbsType(rs.getInt(6));
				 String psql = "SELECT panme FROM pictures WHERE bbsID = ?";
				 try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getBbsID());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setPicture(ex.getString(1));

							return bbs;
					 } else {
		                    bbs.setPicture(null);
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
			 } 
		 } catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public int update(int bbsID, String bbsTitle,String bbsSub, String bbsIn) {
		String SQL = "UPDATE BBS SET bbsTitle = ?, bbsSub = ?, bbsIn =? WHERE bbsID = ?";
		try{
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setString(1, bbsTitle);
			pstmt.setString(2, bbsSub);
			pstmt.setString(3, bbsIn);
			pstmt.setInt(4, bbsID);
			return pstmt.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public int delete(int bbsID) {
		String SQL = "Delete from BBS WHERE bbsID = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, bbsID);
			return pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //DB오류
	}
	
	
	public ArrayList<Bbs> getIndex(int pageNumber, String Search ) {
		 String SQL = "SELECT * FROM BBS WHERE bbsID < ? AND bbsTitle LIKE ? ORDER BY `bbsID` DESC";
		 ArrayList<Bbs> list = new ArrayList<Bbs>();
		 try {
			 PreparedStatement pstmt = conn.prepareStatement(SQL);
			 pstmt.setInt(1, getNext() - (pageNumber -1) * 10 );
			 pstmt.setString(2, "%"+Search+"%");
			 rs = pstmt.executeQuery();
			 while (rs.next()) {
				 Bbs bbs = new Bbs();
				 bbs.setBbsID(rs.getInt(1));
				 bbs.setDate(rs.getString(2));
				 bbs.setBbsTitle(rs.getString(3));
				 bbs.setBbsSub(rs.getString(4));
				 bbs.setBbsIn(rs.getString(5));
				 bbs.setBbsType(rs.getInt(6));
				 String psql = "SELECT panme FROM pictures WHERE bbsID = ?";
				 try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getBbsID());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setPicture(ex.getString(1));
					 } else {
		                    bbs.setPicture(null); // 해당하는 레코드가 없을 경우 null로 설정
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
				 list.add(bbs);
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
		 return list;
	}
	
	public ArrayList<Bbs> ngetIndex(int pageNumber, String Search ) {
		 String SQL = "SELECT * FROM BBS WHERE bbsID < ? AND bbsTitle LIKE ? AND bbstype != 3 ORDER BY `bbsID` DESC";
		 ArrayList<Bbs> list = new ArrayList<Bbs>();
		 try {
			 PreparedStatement pstmt = conn.prepareStatement(SQL);
			 pstmt.setInt(1, ngetNext() - (pageNumber -1) * 10 );
			 pstmt.setString(2, "%"+Search+"%");
			 rs = pstmt.executeQuery();
			 while (rs.next()) {
				 Bbs bbs = new Bbs();
				 bbs.setBbsID(rs.getInt(1));
				 bbs.setDate(rs.getString(2));
				 bbs.setBbsTitle(rs.getString(3));
				 bbs.setBbsSub(rs.getString(4));
				 bbs.setBbsIn(rs.getString(5));
				 bbs.setBbsType(rs.getInt(6));
				 String psql = "SELECT panme FROM pictures WHERE bbsID = ?";
				 try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getBbsID());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setPicture(ex.getString(1));
					 } else {
		                    bbs.setPicture(null); // 해당하는 레코드가 없을 경우 null로 설정
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
				 list.add(bbs);
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
		 return list;
	}
	
}