//get a menu page to create, update, delete, view and exit db

import java.sql.*;
import java.util.*;
public class ViewTest2
{
	public static void main(String[] args)
	{
		boolean b = true;
		while (b == true)
		{


		//Create an object of controller class
		Controller ctrl = new Controller(); // invokes the default constructor from Controller class


		System.out.println(" ");
		System.out.println(" ");
		System.out.println("***** Menu *****");
		System.out.println(" ");
		System.out.println("1. Create Record");
		System.out.println(" ");
		System.out.println("2. Update Record");
		System.out.println(" ");
		System.out.println("3. Delete Record");
		System.out.println(" ");
		System.out.println("4. View Customer Details");
		System.out.println(" ");
		System.out.println("5. Exit Menu");
		System.out.println(" ");
		System.out.println(" ");


			Scanner sc1 = new Scanner(System.in);
			System.out.println("Please select one of the options: ");
			int a = sc1.nextInt();


			if (a == 1){
				try
				{
					//step 1
			Class.forName("com.mysql.jdbc.Driver");

			//step 2
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost/samdb","root","");

			//step 3
			//Statement st = con.createStatement();

			PreparedStatement pst = con.prepareStatement("insert into customer values(?,?,?)");

			Scanner sc = new Scanner(System.in);

			System.out.println("Enter customer id: ");

			int id = Integer.parseInt(sc.nextLine());
			System.out.println("Enter Customer Name: ");
			String name = sc.nextLine();
			System.out.println("Enter Customer Address: ");
			String address = sc.nextLine();

			pst.setInt(1,id);
			pst.setString(2,name);
			pst.setString(3,address);

			int i = pst.executeUpdate();

			if(i>0)
			{
				System.out.println(i+" record created");
			}
			else
			{
				System.out.println("Error");
			}
			con.close();

			//step 4
			//st.execute("create table customer2(customer_id int,customer_name varchar(30),address varchar(30))");
			//System.out.println("table created");

			//step 5
			//st.close();
			//con.close();

		}
		catch (Exception e)
		{
			System.out.println(e);
		}
			}
		else if (a == 2)
		{
			try
		{
			//step 1
			Class.forName("com.mysql.jdbc.Driver");


			Connection con = DriverManager.getConnection("jdbc:mysql://localhost/alydb","root","");

			//step 3
			//Statement st = con.createStatement();

			PreparedStatement pst = con.prepareStatement("UPDATE customer SET customer_name = ?, address = ? where customer_id = ?");

			Scanner sc2 = new Scanner(System.in);

			//Step 3

			System.out.println("Enter customer id to Update: ");

			int id = Integer.parseInt(sc2.nextLine());
			System.out.println("Enter New Customer Name: ");
			String name = sc2.nextLine();
			System.out.println("Enter New Customer Address: ");
			String address = sc2.nextLine();


			pst.setString(1,name);
			pst.setString(2,address);
			pst.setInt(3,id);

			//Step 4

			int i = pst.executeUpdate();

			if(i>0)
			{
				System.out.println(i+" record updated");
			}
			else
			{
				System.out.println("Error");
			}
			con.close();


		}
		catch (Exception e)
		{
			System.out.println(e);
		}
		}

		else if (a == 3)
		{
			try
		{
			//step 1
			Class.forName("com.mysql.jdbc.Driver");


			Connection con = DriverManager.getConnection("jdbc:mysql://localhost/alydb","root","");

			//step 3
			//Statement st = con.createStatement();

			PreparedStatement pst = con.prepareStatement("DELETE from customer where customer_id = ?");

			Scanner sc = new Scanner(System.in);

			//Step 3

			System.out.println("Enter customer id to delete: ");

			int id = Integer.parseInt(sc.nextLine());

			pst.setInt(1,id);

			//Step 4

			int i = pst.executeUpdate();

			if(i>0)
			{
				System.out.println(i+" record deleted");
			}
			else
			{
				System.out.println("Error");
			}
			con.close();


		}
		catch (Exception e)
		{
			System.out.println(e);
		}
		}

		else if (a == 4)
		{
			//Create an object of controller class
		Controller ctrl1 = new Controller(); // invokes the default constructor from Controller class

		ArrayList<Customer> customerList = ctrl1.getAllCustomerDetails();

		// for-each loop
		System.out.println("***********************************************");
		for (Customer c : customerList)
		{
			System.out.println("Customer ID : "+c.getId());
			System.out.println("Customer Name : "+c.getName());
			System.out.println("Customer Address : "+c.getAddress());

			System.out.println("*******************************************");
		}
		}

		else if (a == 5)
		{
			System.out.println(" ");
			System.out.println("Goodbye!");
			System.out.println(" ");
			b = false;
		}

		else
			{
				System.out.println(" ");
				System.out.println("Incorrect input, Please try again!");
				System.out.println(" ");
			}
		}
	}
}