package CafeDatabase;

import java.util.Scanner;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class CafeDb {

	Scanner scan = new Scanner(System.in);

	String DRIVER1 = "org.mariadb.jdbc.Driver";
	String DBURL = "jdbc:mysql://localhost:3306/cafe";
	String USERID = "root";
	String PW = "";
	Statement st = null;
	ResultSet rs = null;
	Connection con = null;
	PreparedStatement ps = null;

	public CafeDb(){
		try{
			Class.forName(DRIVER1);
			con = DriverManager.getConnection(DBURL, USERID, PW);
			System.out.println("DB 성공적 연결");

			/*
			stmt = mydb.createStatement();
			String query = "SELECT * FROM cafeInfo where name like '%" + key + "%'";
			rs = stmt.executeQuery(query);
			*/
		} catch(Exception e){
			e.printStackTrace();
		}
	}

	public void dbClose(){
		try{
			if(rs != null)
				rs.close();
			if(st != null)
				st.close();
		} catch(SQLException e){
			e.printStackTrace();
		}
	}

	public int cafeInfoInsert(CafeStorage cs){
		int result = 0;

		try{
			String query = "INSERT INTO cafeInfo values(?, ?, ?, ?, ?, ?, ?)";

			ps = con.prepareStatement(query);
			ps.setString(1, cs.getId());
			ps.setString(2, cs.getName());
			ps.setString(3, cs.getPw());
			ps.setString(4, cs.getTell());
			ps.setString(5, cs.getIntro());
			ps.setString(6, cs.getAddress());
			ps.setInt(7, cs.getTableNum());

			result = ps.executeUpdate();
		} catch(Exception e){
			System.out.println(e + "=> cafeInfo insert fail");
		}finally{
			dbClose();
		}

		return result;
	}

	public ArrayList<CafeStorage> getCafeInfo(String field, String key){

		ArrayList<CafeStorage> list = new ArrayList<>();

		try{
			String query = "SELECT * FROM cafeInfo where " + field + " like '%" + key + "%'";
			st = con.createStatement();
			rs = st.executeQuery(query);

			while(rs.next()){
				CafeStorage c = new CafeStorage();

				c.setId(rs.getString(1));
				c.setName(rs.getString(2));
				c.setPw(rs.getString(3));
				c.setTell(rs.getString(4));
				c.setIntro(rs.getString(5));
				c.setAddress(rs.getString(6));
				c.setTableNum(rs.getInt(7));

				list.add(c);
			}
		} catch(Exception e){
			System.out.println(e + "=> get cafeInfo list fail");
		} finally{
			dbClose();
		}

		return list;
	}
}