package proxy;

import java.sql.*;

public class TablesManager {

	public static final String publisher = "publisher";

	public static final String customer = "customer";

	public static final String orders = "orders";

	public static final String fulfilledOrder = "fulfilledOrder";

	public static final String category = "category";
	
	public static final String subCategory = "subCategory";

	public static final String promotion = "promotion";

	public static final String book = "book";

	public static final String invoice = "invoice";

	public static final String promotionList = "promotionList";

	private static final String[] tables = { publisher, customer, orders,
		fulfilledOrder, category, subCategory,promotion, book, invoice, promotionList };
	
	private static final int[] argLength ={3,10,5,4,2,3,3,10,4,2};

	public static void main(String[] args) throws ClassNotFoundException,
			SQLException {
		/*
		 * String url = "jdbc:mysql://localhost:3306/";
		 * Class.forName("com.mysql.jdbc.Driver"); Connection dbCon =
		 * DriverManager.getConnection(url, MysqlServer.userName,
		 * MysqlServer.password); Statement stmt = dbCon.createStatement();
		 * TablesManager.createTables(stmt);
		 */
	}

	public static void createTables(Statement stmt) {

		try {
			stmt.addBatch("DROP DATABASE IF EXISTS Bookstore;");
			stmt.addBatch("CREATE DATABASE Bookstore;");
			stmt.addBatch("USE Bookstore;");
			stmt.addBatch("DROP TABLE IF EXISTS publisher;");
			stmt.addBatch("DROP TABLE IF EXISTS customer;");
			stmt.addBatch("DROP TABLE IF EXISTS orders;");
			stmt.addBatch("DROP TABLE IF EXISTS fulfilledOrder;");
			stmt.addBatch("DROP TABLE IF EXISTS category;");
			stmt.addBatch("DROP TABLE IF EXISTS subCategory;");
			stmt.addBatch("DROP TABLE IF EXISTS promotion;");
			stmt.addBatch("DROP TABLE IF EXISTS book;");
			stmt.addBatch("DROP TABLE IF EXISTS invoice;");
			stmt.addBatch("DROP TABLE IF EXISTS promotionList;");
			stmt.executeBatch();

			stmt.addBatch("CREATE TABLE publisher"
					+ "(PID               char(6) NOT NULL,"
					+ " name              varchar(50),"
					+ " address           varchar(50),"
					+ " PRIMARY KEY (PID));");

			stmt.addBatch("CREATE TABLE customer"
					+ "(CID               char(7) NOT NULL,"
					+ " Cname             varchar(16) NOT NULL,"
					+ " Cpassword         varchar(16) NOT NULL,"
					+ " RealName          varchar(30),"
					+ " MailingAddr       varchar(100),"
					+ " creditCardNum     char(10),"
					+ " ExpirationDate	  date,"
					+ " EMailAddr         varchar(50),"
					+ "  PRIMARY KEY (CID));");
			//stmt.executeBatch();

			stmt
					.addBatch("CREATE TABLE orders"
							+ "(Ono		        char(8) NOT NULL,"
							+ " CID				char(7) NOT NULL,"
							+ " shipment	    varchar(30) CHECK VALUE IN ('next day delivery','regular post'),"
							+ " shippingDate    date,"
							+ " dateArrived	    date,"
							+ " PRIMARY KEY (Ono),"
							+ " FOREIGN KEY (CID) REFERENCES customer(CID) on delete cascade);");

			stmt
					.addBatch("CREATE TABLE fulfilledOrder"
							+ "(TrackNum	          char(8) NOT NULL,"
							+ " Ono		              char(8),"
							+ " shipmentDate          date,"
							+ " ExpectedDeliveryDate  date,"
							+ " PRIMARY KEY (TrackNum),"
							+ " FOREIGN KEY (Ono) REFERENCES orders(Ono) on delete cascade);");
			//stmt.executeBatch();

			stmt.addBatch("CREATE TABLE category"
					+ "(Cno		         char(5) NOT NULL,"
					+ " Cname		     varchar(15)," + "  PRIMARY KEY (Cno));");

			stmt
					.addBatch("CREATE TABLE subCategory"
							+ "(SCno		       char(7) NOT NULL,"
							+ " SCname             char(50) NOT NULL,"
							+ " Cno 		       char(5) NOT NULL,"
							+ " PRIMARY KEY(SCno),"
							+ " FOREIGN KEY (Cno) REFERENCES category(Cno) on delete cascade on update cascade);");
			//stmt.executeBatch();

			stmt
					.addBatch("CREATE TABLE promotion"
							+ "(Pno		        char(6) NOT NULL,"
							+ " discount	    numeric(3,2)  NOT NULL CHECK VALUE between 0 and 1,"
							+ " duration	    date," + "  PRIMARY KEY (Pno));");

			stmt
					.addBatch("CREATE TABLE book"
							+ "(BID               char(8) NOT NULL,"
							+ " author            char(25),"
							+ " title             varchar(130),"
							+ " isbn              char(8),"
							+ " PID               char(6),"
							+ " edition 	        char(8),"
							+ " publicationDate   date,"
							+ " price             numeric(5,2) NOT NULL,"
							+ " description      varchar(200),"
							+ " SCno		           char(7),"
							+ " PRIMARY KEY (BID),"
							+ " FOREIGN KEY (PID) REFERENCES publisher(PID) on delete set null on update cascade,"
							+ " FOREIGN KEY (SCno) REFERENCES subCategory(SCno)  on delete restrict on update cascade);");
			//stmt.executeBatch();

			stmt
					.addBatch("CREATE TABLE invoice"
							+ "(Ono		           char(8) NOT NULL,"
							+ " BID                char(8) NOT NULL,"
							+ " qty		           int unsigned NOT NULL,"
							+ " billedPrice        numeric(8,2) NOT NULL,"
							+ " PRIMARY KEY (Ono,BID),"
							+ " FOREIGN KEY (Ono) REFERENCES orders(Ono) on delete cascade,"
							+ " FOREIGN KEY (BID) REFERENCES book(BID) on delete cascade on update cascade);");

			stmt
					.addBatch("CREATE TABLE promotionList"
							+ "(Pno		           char(6) NOT NULL,"
							+ " BID                char(8) NOT NULL,"
							+ " PRIMARY KEY(Pno,BID),"
							+ " FOREIGN KEY (Pno) REFERENCES promotion(Pno) on delete cascade,"
							+ " FOREIGN KEY (BID) REFERENCES book(BID) on delete cascade on update cascade);");
			stmt.executeBatch();
			

			PublisherManager.insertPublishers(stmt);

			CustomerManager.insertCustomers(stmt);
			OrdersManager.insertOrders(stmt);
			FulfilledOrderManager.insertFulfilledOrders(stmt);

			CategoryManager.insertCategories(stmt);
			SubCatagoryManager.insertSubCategories(stmt);

			PromotionManager.insertPromotions(stmt);

			BookManager.insertBooks(stmt);

			InvoiceManager.insertInvoices(stmt);
			PromotionListManager.insertPromotionLists(stmt);
			
		} catch (BatchUpdateException b) {
			System.err.println("BatchUpdateException from createTables: ");
			System.err.println("Message:  " + b.getMessage());
			System.err.println("Vendor:  " + b.getErrorCode());
			System.err.print("Update counts:  ");
			int[] updateCounts = b.getUpdateCounts();
			for (int i = 0; i < updateCounts.length; i++) {
				System.err.print(updateCounts[i] + "   ");
			}
			System.err.println("");

		} catch (SQLException ex) {
			System.err.println("SQLException from createTables: ");
			System.err.println("Message:  " + ex.getMessage());
			System.err.println("Vendor:  " + ex.getErrorCode());
		}
	}

}