package cmn;

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

public class CmnDAO {

	private Connection conn;
	private ResultSet rs;
	
	public CmnDAO() {
		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 int getNext() {
		String SQL = "SELECT R_NO FROM REVIEW ORDER BY cmnID 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 double rvg(int pno) {
		String SQL = "SELECT AVG(RPOINT) FROM REVIEW WHERE P_NO = ?";
		try{
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, pno);
			return pstmt.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public int write(int pno, int pnum, int rpoint, String comm) {
		String SQL = "INSERT INTO REVIEW VALUES (?, ?, ?, SYSTIMESTAMP, ?, ?)";
		try{
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, getNext());
			pstmt.setInt(2, pno);
			pstmt.setInt(3, pnum);
			pstmt.setInt(4, rpoint);
			pstmt.setString(5, comm);
			return pstmt.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public ArrayList<Cmn> getList(int pno) {
		 String SQL = "SELECT * FROM REVIEW WHERE P_NO = ? ORDER BY R_NO";
		 ArrayList<Cmn> list = new ArrayList<Cmn>();
		 try {
			 PreparedStatement pstmt = conn.prepareStatement(SQL);
			 pstmt.setInt(1, pno);
			 rs = pstmt.executeQuery();
			 while (rs.next()) {
				 Cmn cmn = new Cmn();
				 cmn.setCno(rs.getInt(1));
				 cmn.setPno(rs.getInt(2));
				 cmn.setPnum(rs.getInt(3));
				 cmn.setDday(rs.getString(4));
				 cmn.setRpoint(rs.getInt(5));
				 cmn.setComm(rs.getString(6));
				 list.add(cmn);
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
		 return list;
	}
	
	/*public Cmn getCmn(int bbsID) {
		String SQL = "Select * FROM COMMENT WHERE bbsID = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, bbsID);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				Cmn cmn = new Cmn();
				cmn.setCmnID(rs.getInt(1));
				 cmn.setBbsID(rs.getInt(2));
				 cmn.setUserID(rs.getString(3));
				 cmn.setCmnDate(rs.getString(4));
				 cmn.setCmnContent(rs.getString(5));
				 return cmn;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}*/
	
	public String cmnNickname(String userID) {
		String SQL = "SELECT DISTINCT userNickname FROM USER JOIN COMMENT ON USER.userID = COMMENT.usrID 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;
	}
	
}