/*
 * Created on Sep 10, 2003
 *
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
package DatabaseCommunication;


import java.sql.*;
import java.io.*;
import java.util.*;

/**
 * @author student
 *
 * This main class is being used for the creation of the application 
 * Database  * with the default initial set of coffees in the coffee 
 * table.
 * 
 */
public class CreateTables {
		
	/**
	 * This method is being used for the generation of the tables.
	 * @param args 
	 * @return void
	 */
	public static void main(String[] args) {
		try{
			Properties p = new Properties ();
			p.load(new FileInputStream("configuration.txt"));		
			String url = p.getProperty("MYSQLURL");
			Class.forName("com.mysql.jdbc.Driver").newInstance();	
			Connection con = DriverManager.getConnection(url,"root", "password");
			System.out.println("Now connected to the Coffee database.");
			
			
			Statement s1 = con.createStatement();
			
			s1.execute("DROP TABLE IF EXISTS customer");
			
			//create the customer table.
			String sql1 = "CREATE TABLE customer (" +
				"UserName CHAR(8) NOT NULL," +
				"PRIMARY KEY(UserName)," +
				"PreBillingAmount DECIMAL(10,2)," +
				"Account DECIMAL(10,2));";
			s1.executeUpdate(sql1);
			
			System.out.println("Created customer Table.");
			
			s1.execute("DROP TABLE IF EXISTS coffee");
			//create the coffee table.
			String sql2 = "CREATE TABLE coffee (" +
				"ItemCode VARCHAR(5) NOT NULL," +
				"PRIMARY KEY (ItemCode)," +
				"Type CHAR(40)," +
				"Size CHAR(10),"+
				"Price DECIMAL(10,2),"+
				"Description CHAR(50));";
				
			s1.execute(sql2);
			
			System.out.println("Created coffee Table.");
			
			s1.execute("DROP TABLE IF EXISTS coffeepreference");
			//create the coffeepreference table.
			String sql3 = "CREATE TABLE coffeepreference (" +
				"CoffeePreferenceId INT UNSIGNED NOT NULL AUTO_INCREMENT," +
				"PRIMARY KEY (CoffeePreferenceId)," +
				"UserName VARCHAR(8) NOT NULL," +
				"ItemCode VARCHAR(5) NOT NULL,"+
				"Quantity INT UNSIGNED,"+
				"DeliveryHour CHAR(2)," +
				"DeliveryMinute CHAR(2),"+
				"FOREIGN KEY (UserName) " +
				"REFERENCES customer (UserName), " +
				"FOREIGN KEY (ItemCode) " +
				"REFERENCES coffee (ItemCode));";
			
			s1.execute(sql3);

			System.out.println("Created coffeepreference Table.");
			
			//insert default values into the coffee table.			
			String sql5 = "INSERT INTO coffee (ItemCode,Type,Size,Price,Description) VALUES " +
			"('1000S','Caffe Latte','small',2.80,'Caffe Latte - small $2.80'),"+
			"('1000M','Caffe Latte','medium',3.30,'Caffe Latte - medium $3.30'),"+			
			"('1000L','Caffe Latte','large',3.60,'Caffe Latte - large $3.60'),"+
			"('1001S','Cappuccino','small',2.50,'Cappuccino - small $2.50'),"+			
			"('1001M','Cappuccino','medium',3.50,'Cappuccino - medium $3.50'),"+
			"('1001L','Cappuccino','large',4.50,'Cappuccino - large $4.50'),"+			
			"('1002S','Espresso','small',2.50,'Espresso - small $2.50'),"+
			"('1002M','Espresso','medium',3.50,'Espresso - medium $3.50')," +
			"('1002L','Espresso','large',4.50,'Espresso - large $4.50'),"+
			"('1003S','Iced Cafe Latte','small',2.50,'Iced Cafe Latte - small $2.50'),"+
			"('1003M','Iced Cafe Latte','medium',3.50,'Iced Cafe Latte - medium $3.50'),"+
			"('1003L','Iced Cafe Latte','large',4.50,'Iced Cafe Latte - large $4.50'),"+
			"('1004S','Mocha','small',2.50,'Mocha - small $2.50'),"+
			"('1004M','Mocha','medium',3.50,'Mocha - medium $3.50'),"+
			"('1004L','Mocha','large',4.50,'Mocha - large $4.50');";
	
			s1.execute(sql5);
			
			System.out.println("Inserted values into coffee table.");
			s1.close();
		}catch(Exception e){
			System.out.println("Database Exception : "+e.toString());
		}
	}
}