package CafeDatabase;

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;
import java.util.Scanner;

public class CafeTableDb {

	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 CafeTableDb(){
		try{
			Class.forName(DRIVER1);
			con = DriverManager.getConnection(DBURL, USERID, PW);
			System.out.println("DB 성공적 연결");

		} 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 cafeTableInfoInsert(CafeTableStorage cts, int tableNum){
		int result = 0;

		try{
			for(int i = 0; i < tableNum; i++){
				String query = "INSERT INTO cafeTable values(?, ?)";

				ps = con.prepareStatement(query);
				ps.setString(1, cts.getId());
				ps.setInt(3, 0);

				result = ps.executeUpdate();
			}


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

		return result;
	}

	public int seqInitalize (){
		String query = "alter table cafeTable auto_increment=1";
		int result = 0;

		try {
			ps = con.prepareStatement(query);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e + "=> sequence initialize fail");
		}

		return result;
	}

public ArrayList<CafeTableStorage> getCafeInfo(String key){

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

		try{
			String query = "SELECT * FROM cafeTable where ID = " + key;
			st = con.createStatement();
			rs = st.executeQuery(query);

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

				c.setId(rs.getString(1));
				c.setTableSitu(rs.getInt(3));

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

		return list;
	}
}