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

/**
 * @author student
 *
 * To change the template for this generated type comment go to
 * 
 */
import java.sql.*;
import java.io.*;
import java.util.*;
/**
 * @author student
 *
 * This class is being used for creating tables in the application 
 * database and intitialising the stock table with default stocks.
 * 
 */
public class CreateTables {

	/**
	 * This method is being used for creation of the tables relating to 
	 * stock trading platform.
	 * @param args arguments, not used.
	 * @return void
	 */
	public static void main(String[] args) {
		try {
			Properties p = new Properties();
			p.load(new FileInputStream("stocktrading.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 Stock Trading database.");

			Statement s1 = con.createStatement();

			s1.execute("DROP TABLE IF EXISTS user");

			String sql1 =
				"CREATE TABLE user ("
					+ "UserName CHAR(8) NOT NULL,"
					+ "PRIMARY KEY(UserName),"
					+ "NcbId INT UNSIGNED NOT NULL,"
					+ "Type CHAR(20) NOT NULL,"
					+ "Account DECIMAL(10,2));";
			s1.executeUpdate(sql1);

			System.out.println("Created user Table.");
			//Statement s2 = con.createStatement();
			s1.execute("DROP TABLE IF EXISTS stock");

			String sql2 =
				"CREATE TABLE stock ("
					+ "StockCode VARCHAR(4) NOT NULL,"
					+ "PRIMARY KEY (StockCode),"
					+ "CompanyName CHAR(40),"
					+ "WeekHigh DECIMAL(10,2),"
					+ "WeekLow DECIMAL(10,2),"
					+ "BaseValue DECIMAL(10,2),"
					+ "Eps DECIMAL(10,2),"
					+ "Dps DECIMAL(10,2));";
			s1.execute(sql2);

			System.out.println("Created stock Table.");
			//Statement s3 = con.createStatement();
			s1.execute("DROP TABLE IF EXISTS stockOwnership");

			String sql3 =
				"CREATE TABLE stockOwnership ("
					+ "StockOwnershipId INT UNSIGNED NOT NULL AUTO_INCREMENT,"
					+ "PRIMARY KEY (stockOwnershipId),"
					+ "UserName VARCHAR(8) NOT NULL,"
					+ "StockCode VARCHAR(4) NOT NULL,"
					+ "TotalQty INT UNSIGNED,"
					+ "TotalPrice DECIMAL(10,2),"
					+ "LastPurchaseDate DATETIME,"
					+ "LastPurchaseAmount DECIMAL(10,2),"
					+ "LastPurchaseQty INT UNSIGNED,"
					+ "FOREIGN KEY (UserName) "
					+ "REFERENCES user (UserName), "
					+ "FOREIGN KEY (StockCode) "
					+ "REFERENCES stock (StockCode));";

			s1.execute(sql3);

			System.out.println("Created stockOwnership Table.");
			//Statement s4 = con.createStatement();

			s1.execute("DROP TABLE IF EXISTS stockInterested");

			String sql4 =
				"CREATE TABLE stockInterested ("
					+ "StockInterestedId 	INT UNSIGNED NOT NULL AUTO_INCREMENT,"
					+ "PRIMARY KEY (stockInterestedId),"
					+ "UserName VARCHAR(8) NOT NULL,"
					+ "StockCode VARCHAR(4) NOT NULL,"
					+ "FOREIGN KEY (UserName) "
					+ "REFERENCES user (UserName), "
					+ "FOREIGN KEY (StockCode) "
					+ "REFERENCES stock (StockCode));";

			s1.execute(sql4);

			System.out.println("Created stockInterested Table.");

			String sql5 =
				"INSERT INTO stock (StockCode,CompanyName,WeekHigh,WeekLow,BaseValue,Eps,Dps) VALUES "
					+ "('AADK','Aardvark Inc',60.00,40.00,50.00,2.50,1.50),"
					+ "('APSI','Astro Physicists Inc',60.00,30.00,45.00,2.10,1.30),"
					+ "('BBPP','Beeple Ltd',50.00,20.00,40.00,2.00,1.50),"
					+ "('DPNA','Daily Planet News',70.00,10.00,35.00,1.40,1.40),"
					+ "('MMWP','Mitchell Wasp Ltd',50.00,20.00,30.00,1.30,1.10),"
					+ "('TJBL','The JukeBox Ltd',40.00,10.00,25.00,1.00,1.10),"
					+ "('VVRT','Velocity Rockets Ltd',40.00,1.00,20.00,1.00,1.30),"
					+ "('MSKI','Movers&Shakers Inc',30.00,10.00,15.00,1.10,0.40),"
					+ "('HCBI','Honeycomb Inc',10.00,1.00,10.00,0.50,0.10),"
					+ "('SSSL','SouthernSoft Ltd',10.00,1.00,5.00,0.25,0.10);";

			s1.execute(sql5);

			System.out.println("Inserted values into stock table.");
			s1.close();
		} catch (Exception e) {
			System.out.println("Database Exception : " + e.toString());
		}
	}
}