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:mysql://localhost:3306/JSP?useSSL=false&serverTimezone=UTC";	//ssl 연결을 미지원하기에 false설정이 반드시 필요하다.
			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 MAX(comment_number) FROM COMMIT";
	    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(int bbsID, String cmnContent) {
		String SQL = "INSERT INTO COMMIT VALUES (?, ?, ?, ?)";
		try{
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, getNext());
			pstmt.setInt(2, bbsID);
			pstmt.setString(3, getDate());
			pstmt.setString(4, cmnContent);
			return pstmt.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public ArrayList<Cmn> getList(int bbsID) {
		 String SQL = "SELECT * FROM COMMIT WHERE bbs_id = ? ORDER BY `comment_number`";
		 ArrayList<Cmn> list = new ArrayList<Cmn>();
		 try {
			 PreparedStatement pstmt = conn.prepareStatement(SQL);
			 pstmt.setInt(1, bbsID);
			 rs = pstmt.executeQuery();
			 while (rs.next()) {
				 Cmn cmn = new Cmn();
				 cmn.setComment_number(rs.getInt(1));
				 cmn.setBbs_id(rs.getInt(2));
				 cmn.setDate(rs.getString(3));
				 cmn.setContent(rs.getString(4));
				 list.add(cmn);
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
		 return list;
	}
	
	public Cmn getCmn(int bbsID) {
		String SQL = "Select * FROM COMMIT WHERE bbs_id = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, bbsID);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				Cmn cmn = new Cmn();
				cmn.setComment_number(rs.getInt(1));
				 cmn.setBbs_id(rs.getInt(2));
				 cmn.setDate(rs.getString(3));
				 cmn.setContent(rs.getString(4));
				 return cmn;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
}