package bbs;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class BbsDAO {

	private Connection conn;
	private ResultSet rs;
	
	public BbsDAO() {
		try {
			String dbURL = "jdbc:oracle:thin:@localhost:1521:ORCL";
			String dbID = "-";
			String dbPassword = "-";
			Class.forName("oracle.jdbc.driver.OracleDriver");
			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 PRO_NUM FROM PRODUCT ORDER BY PRO_NUM 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 getrNext() {
		String SQL = "SELECT PRO_NUM FROM PRODUCT WHERE CONDITION != 3 ORDER BY PRO_NUM 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 void doPost() { try { // 서버 소켓 생성 및 클라이언트 연결 대기 ServerSocket
	 * serverSocket = new ServerSocket(8080);
	 * System.out.println("Server started. Waiting for clients...");
	 * 
	 * while (true) { Socket clientSocket = serverSocket.accept();
	 * System.out.println("Client connected: " +
	 * clientSocket.getInetAddress().getHostAddress());
	 * 
	 * // 클라이언트 요청 처리 handleClient(clientSocket); } } catch (IOException e) {
	 * e.printStackTrace(); } }
	 * 
	 * private void handleClient(Socket clientSocket) { try ( BufferedReader reader
	 * = new BufferedReader(new InputStreamReader(clientSocket.getInputStream()));
	 * PrintWriter writer = new PrintWriter(clientSocket.getOutputStream(), true) )
	 * { // 클라이언트에서 전송한 데이터 읽기 String requestData = reader.readLine();
	 * System.out.println("Received from client: " + requestData);
	 * 
	 * // 받은 데이터를 이용한 작업 수행 String[] params = requestData.split("&"); int remaining
	 * = Integer.parseInt(params[0].split("=")[1]); int pro_num =
	 * Integer.parseInt(params[1].split("=")[1]);
	 * 
	 * uporder(remaining, pro_num);
	 * 
	 * // 클라이언트에게 응답 보내기 (예: "Success" 또는 응답 코드) writer.println("Success");
	 * 
	 * System.out.println("Client disconnected: " +
	 * clientSocket.getInetAddress().getHostAddress()); clientSocket.close(); }
	 * catch (IOException e) { e.printStackTrace(); } }
	 */

	
	public void uporder(int remaining, int pro_num) {
		String SQL = "UPDATE PRODUCT SET REMAINING = ? WHERE PRO_NUM = ?";
		try {PreparedStatement pstmt = conn.prepareStatement(SQL);
		pstmt.setInt(1, remaining + 10);
		pstmt.setInt(2, pro_num);
		pstmt.executeUpdate();
		return;
		}catch (Exception e) {
			e.printStackTrace();
			return;
		}
	}
	
	public void  buying(int remaining, int count, int pro_num) {
		String SQL = "UPDATE PRODUCT SET REMAINING = ? WHERE PRO_NUM = ?";
		try {PreparedStatement pstmt = conn.prepareStatement(SQL);
		pstmt.setInt(1, remaining - count);
		pstmt.setInt(2, pro_num);
		pstmt.executeUpdate();
		return;
		}catch (Exception e) {
			e.printStackTrace();
			return;
		}
	}
	
	public int write(String kind, String brand, String pro_name, int price, int real_price, String ex, String pro_same, String filename, String filename1) {
		String SQL = "INSERT INTO PRODUCT VALUES (?, ?, ?, ?, ?, ?, ?, 10, SYSTIMESTAMP, ?, ?)";
		try{
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			int num = getNext();
			pstmt.setInt(1, num);
			pstmt.setString(2, kind);
			pstmt.setString(3, brand);
			pstmt.setString(4, pro_name);
			pstmt.setInt(5, price);
			pstmt.setInt(6, real_price);
			pstmt.setString(7, ex);
			pstmt.setInt(8, 0);
			pstmt.setString(9, pro_same);
			pstmt.executeUpdate();
			SQL = "INSERT INTO PICTURES VALUES (?, ?)";
			try {
				pstmt = conn.prepareStatement(SQL);
				pstmt.setString(1, filename);
				pstmt.setInt(2, num);
				pstmt.executeUpdate();
				SQL = "INSERT INTO EXPICTURES VALUES (?, ?)";
				try {
					pstmt = conn.prepareStatement(SQL);
					pstmt.setString(1, filename1);
					pstmt.setInt(2, num);
					return pstmt.executeUpdate();
				}catch (Exception e) {
					e.printStackTrace();
				}
			}catch (Exception e) {
				e.printStackTrace();
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public ArrayList<Bbs> getType(){
		String SQL = "Select DISTINCT PRO_SAME FROM PRODUCT";
		ArrayList<Bbs> list = new ArrayList<Bbs>();
		try {
			 PreparedStatement pstmt = conn.prepareStatement(SQL);
			 rs = pstmt.executeQuery();
			 while (rs.next()) {
				 Bbs bbs = new Bbs();
				 bbs.setPro_same(rs.getString(1));
				 list.add(bbs);
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
		 return list;
	}
	
	public ArrayList<Bbs> getSame(int pnum){
		String SQL = "Select PRO_NAME FROM PRODUCT WHERE PRO_SAME = (SELECT PRO_SAME FROM PRODUCT WHERE PRO_NUM = ?) AND CONDITION != 3";
		ArrayList<Bbs> list = new ArrayList<Bbs>();
		try {
			 PreparedStatement pstmt = conn.prepareStatement(SQL);
			 pstmt.setInt(1, pnum);
			 rs = pstmt.executeQuery();
			 while (rs.next()) {
				 Bbs bbs = new Bbs();
				 bbs.setPro_name(rs.getString(1));
				 list.add(bbs);
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
			 return null;
		 }
		 return list;
	}
	
	public int getpnum(String pname) {
		String SQL = "Select PRO_NUM FROM PRODUCT WHERE PRO_NAME = ?";
		int num;
		try {
			 PreparedStatement pstmt = conn.prepareStatement(SQL);
			 pstmt.setString(1, pname);
			 rs = pstmt.executeQuery();
			 while (rs.next()) {
				 num = rs.getInt(1);
				 return num;
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
			
		 }
		 return -1;
	}
	
	public ArrayList<Bbs> getmList(int pageNumber) {
	    int recordsPerPage = 12;
	    int offset = (pageNumber - 1) * recordsPerPage;

	    String SQL = "SELECT * FROM PRODUCT WHERE PRO_NUM < ? AND CONDITION != 3 AND PRO_NUM >= 0 ORDER BY REMAINING, PRO_NUM DESC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
	    ArrayList<Bbs> list = new ArrayList<Bbs>();

	    try {
	        PreparedStatement pstmt = conn.prepareStatement(SQL);
	        pstmt.setInt(1, getrNext());
	        pstmt.setInt(2, offset);
	        pstmt.setInt(3, recordsPerPage);
	        
	        rs = pstmt.executeQuery();

	        while (rs.next()) {
	            Bbs bbs = new Bbs();
	            bbs.setPro_num(rs.getInt(1));
	            bbs.setPro_kind(rs.getString(2));
	            bbs.setBrand(rs.getString(3));
	            bbs.setPro_name(rs.getString(4));
	            bbs.setPrice(rs.getInt(5));
	            bbs.setReal_price(rs.getInt(6));
	            bbs.setEx(rs.getString(7));
	            bbs.setRemaining(rs.getInt(8));
	            bbs.setPro_start(rs.getString(9));
	            bbs.setCondition(rs.getInt(10));
	            bbs.setPro_same(rs.getString(11));
	            String psql = "SELECT PNAME FROM PICTURES WHERE PNUM = ?";
	            try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getPro_num());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setP1(ex.getString(1));
						 psql = "SELECT PNAME FROM EXPICTURES WHERE PNUM = ?";
						 try {
							 pstmtp = conn.prepareStatement(psql);
							 pstmtp.setInt(1, bbs.getPro_num());
							 ex = pstmtp.executeQuery();
							 if (ex.next()) {
								 bbs.setP2(ex.getString(1));
							 } else {
				                    bbs.setP2(null); // 해당하는 레코드가 없을 경우 null로 설정
				                }
						 }catch (Exception e) {
							 e.printStackTrace();
						 }
					 } else {
		                    bbs.setP1(null); // 해당하는 레코드가 없을 경우 null로 설정
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
	            list.add(bbs);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return list;
	}
	
	public ArrayList<Bbs> getList(int pageNumber) {
	    int recordsPerPage = 12;
	    int offset = (pageNumber - 1) * recordsPerPage;

	    String SQL = "SELECT * FROM PRODUCT WHERE PRO_NUM < ? AND CONDITION != 3 ORDER BY PRO_NUM DESC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
	    ArrayList<Bbs> list = new ArrayList<Bbs>();

	    try {
	        PreparedStatement pstmt = conn.prepareStatement(SQL);
	        pstmt.setInt(1, getrNext());
	        pstmt.setInt(2, offset);
	        pstmt.setInt(3, recordsPerPage);
	        
	        rs = pstmt.executeQuery();

	        while (rs.next()) {
	            Bbs bbs = new Bbs();
	            bbs.setPro_num(rs.getInt(1));
	            bbs.setPro_kind(rs.getString(2));
	            bbs.setBrand(rs.getString(3));
	            bbs.setPro_name(rs.getString(4));
	            bbs.setPrice(rs.getInt(5));
	            bbs.setReal_price(rs.getInt(6));
	            bbs.setEx(rs.getString(7));
	            bbs.setRemaining(rs.getInt(8));
	            bbs.setPro_start(rs.getString(9));
	            bbs.setCondition(rs.getInt(10));
	            bbs.setPro_same(rs.getString(11));
	            String psql = "SELECT PNAME FROM PICTURES WHERE PNUM = ?";
	            try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getPro_num());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setP1(ex.getString(1));
						 psql = "SELECT PNAME FROM EXPICTURES WHERE PNUM = ?";
						 try {
							 pstmtp = conn.prepareStatement(psql);
							 pstmtp.setInt(1, bbs.getPro_num());
							 ex = pstmtp.executeQuery();
							 if (ex.next()) {
								 bbs.setP2(ex.getString(1));
							 } else {
				                    bbs.setP2(null); // 해당하는 레코드가 없을 경우 null로 설정
				                }
						 }catch (Exception e) {
							 e.printStackTrace();
						 }
					 } else {
		                    bbs.setP1(null); // 해당하는 레코드가 없을 경우 null로 설정
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
	            list.add(bbs);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return list;
	}
	
	public ArrayList<Bbs> getsList(String ss) {
	    String SQL = "SELECT * FROM PRODUCT WHERE PRO_NAME LIKE ? AND CONDITION != 3 ORDER BY PRO_NUM DESC";
	    ArrayList<Bbs> list = new ArrayList<Bbs>();

	    try {
	        PreparedStatement pstmt = conn.prepareStatement(SQL);
	        pstmt.setString(1, "%"+ss+"%");
	        
	        rs = pstmt.executeQuery();

	        while (rs.next()) {
	            Bbs bbs = new Bbs();
	            bbs.setPro_num(rs.getInt(1));
	            bbs.setPro_kind(rs.getString(2));
	            bbs.setBrand(rs.getString(3));
	            bbs.setPro_name(rs.getString(4));
	            bbs.setPrice(rs.getInt(5));
	            bbs.setReal_price(rs.getInt(6));
	            bbs.setEx(rs.getString(7));
	            bbs.setRemaining(rs.getInt(8));
	            bbs.setPro_start(rs.getString(9));
	            bbs.setCondition(rs.getInt(10));
	            bbs.setPro_same(rs.getString(11));
	            String psql = "SELECT PNAME FROM PICTURES WHERE PNUM = ?";
	            try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getPro_num());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setP1(ex.getString(1));
						 psql = "SELECT PNAME FROM EXPICTURES WHERE PNUM = ?";
						 try {
							 pstmtp = conn.prepareStatement(psql);
							 pstmtp.setInt(1, bbs.getPro_num());
							 ex = pstmtp.executeQuery();
							 if (ex.next()) {
								 bbs.setP2(ex.getString(1));
							 } else {
				                    bbs.setP2(null); // 해당하는 레코드가 없을 경우 null로 설정
				                }
						 }catch (Exception e) {
							 e.printStackTrace();
						 }
					 } else {
		                    bbs.setP1(null); // 해당하는 레코드가 없을 경우 null로 설정
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
	            list.add(bbs);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return list;
	}
	
	public ArrayList<Bbs> getstList() {
	    String SQL = "SELECT * FROM PRODUCT WHERE PRO_KIND = '탁구대' AND CONDITION != 3 ORDER BY PRO_NUM DESC";
	    ArrayList<Bbs> list = new ArrayList<Bbs>();

	    try {
	        PreparedStatement pstmt = conn.prepareStatement(SQL);
	        
	        rs = pstmt.executeQuery();

	        while (rs.next()) {
	            Bbs bbs = new Bbs();
	            bbs.setPro_num(rs.getInt(1));
	            bbs.setPro_kind(rs.getString(2));
	            bbs.setBrand(rs.getString(3));
	            bbs.setPro_name(rs.getString(4));
	            bbs.setPrice(rs.getInt(5));
	            bbs.setReal_price(rs.getInt(6));
	            bbs.setEx(rs.getString(7));
	            bbs.setRemaining(rs.getInt(8));
	            bbs.setPro_start(rs.getString(9));
	            bbs.setCondition(rs.getInt(10));
	            bbs.setPro_same(rs.getString(11));
	            String psql = "SELECT PNAME FROM PICTURES WHERE PNUM = ?";
	            try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getPro_num());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setP1(ex.getString(1));
						 psql = "SELECT PNAME FROM EXPICTURES WHERE PNUM = ?";
						 try {
							 pstmtp = conn.prepareStatement(psql);
							 pstmtp.setInt(1, bbs.getPro_num());
							 ex = pstmtp.executeQuery();
							 if (ex.next()) {
								 bbs.setP2(ex.getString(1));
							 } else {
				                    bbs.setP2(null); // 해당하는 레코드가 없을 경우 null로 설정
				                }
						 }catch (Exception e) {
							 e.printStackTrace();
						 }
					 } else {
		                    bbs.setP1(null); // 해당하는 레코드가 없을 경우 null로 설정
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
	            list.add(bbs);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return list;
	}
	
	public ArrayList<Bbs> getrList() {
	    String SQL = "SELECT * FROM PRODUCT WHERE PRO_KIND LIKE '%러버%' AND CONDITION != 3 ORDER BY PRO_NUM DESC";
	    ArrayList<Bbs> list = new ArrayList<Bbs>();

	    try {
	        PreparedStatement pstmt = conn.prepareStatement(SQL);
	        
	        rs = pstmt.executeQuery();

	        while (rs.next()) {
	            Bbs bbs = new Bbs();
	            bbs.setPro_num(rs.getInt(1));
	            bbs.setPro_kind(rs.getString(2));
	            bbs.setBrand(rs.getString(3));
	            bbs.setPro_name(rs.getString(4));
	            bbs.setPrice(rs.getInt(5));
	            bbs.setReal_price(rs.getInt(6));
	            bbs.setEx(rs.getString(7));
	            bbs.setRemaining(rs.getInt(8));
	            bbs.setPro_start(rs.getString(9));
	            bbs.setCondition(rs.getInt(10));
	            bbs.setPro_same(rs.getString(11));
	            String psql = "SELECT PNAME FROM PICTURES WHERE PNUM = ?";
	            try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getPro_num());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setP1(ex.getString(1));
						 psql = "SELECT PNAME FROM EXPICTURES WHERE PNUM = ?";
						 try {
							 pstmtp = conn.prepareStatement(psql);
							 pstmtp.setInt(1, bbs.getPro_num());
							 ex = pstmtp.executeQuery();
							 if (ex.next()) {
								 bbs.setP2(ex.getString(1));
							 } else {
				                    bbs.setP2(null); // 해당하는 레코드가 없을 경우 null로 설정
				                }
						 }catch (Exception e) {
							 e.printStackTrace();
						 }
					 } else {
		                    bbs.setP1(null); // 해당하는 레코드가 없을 경우 null로 설정
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
	            list.add(bbs);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return list;
	}
	
	public ArrayList<Bbs> getbList() {
	    String SQL = "SELECT * FROM PRODUCT WHERE PRO_KIND LIKE '%블레이드%' AND CONDITION != 3 ORDER BY PRO_NUM DESC";
	    ArrayList<Bbs> list = new ArrayList<Bbs>();

	    try {
	        PreparedStatement pstmt = conn.prepareStatement(SQL);
	        
	        rs = pstmt.executeQuery();

	        while (rs.next()) {
	            Bbs bbs = new Bbs();
	            bbs.setPro_num(rs.getInt(1));
	            bbs.setPro_kind(rs.getString(2));
	            bbs.setBrand(rs.getString(3));
	            bbs.setPro_name(rs.getString(4));
	            bbs.setPrice(rs.getInt(5));
	            bbs.setReal_price(rs.getInt(6));
	            bbs.setEx(rs.getString(7));
	            bbs.setRemaining(rs.getInt(8));
	            bbs.setPro_start(rs.getString(9));
	            bbs.setCondition(rs.getInt(10));
	            bbs.setPro_same(rs.getString(11));
	            String psql = "SELECT PNAME FROM PICTURES WHERE PNUM = ?";
	            try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getPro_num());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setP1(ex.getString(1));
						 psql = "SELECT PNAME FROM EXPICTURES WHERE PNUM = ?";
						 try {
							 pstmtp = conn.prepareStatement(psql);
							 pstmtp.setInt(1, bbs.getPro_num());
							 ex = pstmtp.executeQuery();
							 if (ex.next()) {
								 bbs.setP2(ex.getString(1));
							 } else {
				                    bbs.setP2(null); // 해당하는 레코드가 없을 경우 null로 설정
				                }
						 }catch (Exception e) {
							 e.printStackTrace();
						 }
					 } else {
		                    bbs.setP1(null); // 해당하는 레코드가 없을 경우 null로 설정
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
	            list.add(bbs);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return list;
	}
	
	public int getcon(int bbsid) {
		String SQL = "SELECT * FROM BBS WHERE bbsID < ? AND bbsAvailable = 1";
		try {
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, bbsid);
			rs = pstmt.executeQuery();
			int cons = -1;
			if(rs.next()) {
				
				cons = rs.getInt(1);
				return cons;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1;
	}
	
	/*public boolean nextPage(int pageNumber) {
		String SQL = "SELECT * FROM BBS WHERE bbsID < ? AND bbsAvailable = 1";
		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 Bbs getBbs(int pnum) {
		String SQL = "Select * FROM PRODUCT WHERE PRO_NUM = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, pnum);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				Bbs bbs = new Bbs();
	            bbs.setPro_num(rs.getInt(1));
	            bbs.setPro_kind(rs.getString(2));
	            bbs.setBrand(rs.getString(3));
	            bbs.setPro_name(rs.getString(4));
	            bbs.setPrice(rs.getInt(5));
	            bbs.setReal_price(rs.getInt(6));
	            bbs.setEx(rs.getString(7));
	            bbs.setRemaining(rs.getInt(8));
	            bbs.setPro_start(rs.getString(9));
	            bbs.setCondition(rs.getInt(10));
	            bbs.setPro_same(rs.getString(11));
	            String psql = "SELECT PNAME FROM PICTURES WHERE PNUM = ?";
	            try {
					 PreparedStatement pstmtp = conn.prepareStatement(psql);
					 pstmtp.setInt(1, bbs.getPro_num());
					 ResultSet ex = pstmtp.executeQuery();
					 if (ex.next()) {
						 bbs.setP1(ex.getString(1));
						 psql = "SELECT PNAME FROM EXPICTURES WHERE PNUM = ?";
						 try {
							 pstmtp = conn.prepareStatement(psql);
							 pstmtp.setInt(1, bbs.getPro_num());
							 ex = pstmtp.executeQuery();
							 if (ex.next()) {
								 bbs.setP2(ex.getString(1));
						            return bbs;
							 } else {
				                    bbs.setP2(null); // 해당하는 레코드가 없을 경우 null로 설정
				                }
						 }catch (Exception e) {
							 e.printStackTrace();
						 }
					 } else {
		                    bbs.setP1(null); // 해당하는 레코드가 없을 경우 null로 설정
		                }
				 }catch (Exception e) {
					 e.printStackTrace();
				 }
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return null;
	}
	
	public int update(String kind, String brand, String pro_name, int price, int real_price, String ex, String pro_same, int pro_num) {
		String SQL = "UPDATE PRODUCT SET PRO_KIND = ?, BRAND = ?, PRO_NAME = ?, PRICE = ?, REAL_PRICE = ?, PRO_EX = ?, PRO_SAME =? WHERE PRO_NUM = ?";
		try{
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setString(1, kind);
			pstmt.setString(2, brand);
			pstmt.setString(3, pro_name);
			pstmt.setInt(4, price);
			pstmt.setInt(5, real_price);
			pstmt.setString(6, ex);
			pstmt.setString(7, pro_same);
			pstmt.setInt(8, pro_num);
			return pstmt.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public int delete(int bbsID) {
		String SQL = "UPDATE PRODUCT SET CONDITION = 3 WHERE PRO_NUM = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, bbsID);
			return pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //DB오류
	}
	
	public int getTotalRecords() {
	    String SQL = "SELECT COUNT(*) FROM PRODUCT WHERE CONDITION != 3";
	    try {
	        PreparedStatement pstmt = conn.prepareStatement(SQL);
	        rs = pstmt.executeQuery();
	        if (rs.next()) {
	            return rs.getInt(1);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return 0;
	}

	
	/*public String bbsNickname(String userID) {
		String SQL = "SELECT DISTINCT userNickname FROM USER JOIN BBS ON USER.userID = BBS.userID WHERE USER.userID = ? ";
		try {
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setString(1, userID);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				String userNickname = new String();
				 userNickname = rs.getString("userNickname");
				 return userNickname;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}*/
	
	/*public ArrayList<Bbs> getIndex(int pageNumber, String Search ) {
		 String SQL = "SELECT * FROM BBS WHERE bbsID < ? AND bbsAvailable = 1 AND bbsTitle LIKE ? ORDER BY `bbsID` DESC LIMIT 10";
		 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.setBbsTitle(rs.getString(2));
				 bbs.setUserID(rs.getString(3));
				 bbs.setBbsDate(rs.getString(4));
				 bbs.setBbsContent(rs.getString(5));
				 bbs.setBbsAvailable(rs.getInt(6));
				 list.add(bbs);
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
		 return list;
	}*/
	
}
