import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.PreparedStatement;
import java.io.*;
import java.util.*;


public class Notebook 
{
    /* the default framework is embedded*/
    public String framework = "embedded";
    //public String driver = "org.apache.derby.jdbc.EmbeddedDriver";//derby10
    //public String protocol = "jdbc:derby:";//derby10
    public String driver = "com.ibm.db2j.jdbc.DB2jDriver";
	public String protocol = "jdbc:db2j:";
	
	
    public static void main(String[] args) 
    {
		
		new Notebook().go(args);
		
    }
	
    void go(String args[]) 
    {
		/* parse the arguments to determine which framework is desired*/
		parseArguments(args);
		
        System.out.println("Notebook starting in " + framework + " mode.");	
        
        try 
        {
            
            
            Class.forName(driver).newInstance();
            System.out.println("Loaded the appropriate driver.");
            Connection conn = DriverManager.getConnection(protocol + "D:/STUDY/data/Notebook1;create=true");
            System.out.println("Connected to and created database Notebook");
      
            conn.setAutoCommit(false);

			
            
            Statement s = conn.createStatement();
			
			
			
			
			//************************
            //CREATING TABLE SUPPLIER
            //************************
            
           s.executeUpdate("CREATE TABLE Supplier(SuppID int,SuppName VARCHAR(20), City VARCHAR(20), Postcode VARCHAR(20), PRIMARY KEY(SuppID) )");
            System.out.println("Created table Supplier");
           
			//***********************
            //CREATING TABLE NOTEBOOK
            //***********************
           s.executeUpdate("CREATE TABLE Notebook(SuppID int, Brand VARCHAR(20), Type VARCHAR(20), NbID int, PRIMARY KEY(NbID), FOREIGN KEY(SuppID) REFERENCES Supplier(SuppID))");
            System.out.println("Created table Notebook");
           
            //************************
            //CREATING TABLE REPAIR
            //************************
          
           s.executeUpdate("CREATE TABLE Repair(Type VARCHAR(20), RQuantity int, PRIMARY KEY(Type))");
            System.out.println("Created table Repair");
         
            
            //************************
            //CREATING TABLE Sale
            //************************
            
           s.executeUpdate("CREATE TABLE Sale(NbID int, Type VARCHAR(20), SQuantity int, Unitprice int, totalprice int, PRIMARY KEY(NbID), FOREIGN KEY(NbID) REFERENCES Notebook(NbID), FOREIGN KEY(Type) REFERENCES Repair(Type))");
            System.out.println("Created table Sale");
         
          
         
            
            System.out.println();
            
            //Create prepared statement 
           String psq1="INSERT INTO Supplier VALUES(?,?,?,?)"; 
           PreparedStatement ps1=conn.prepareStatement(psq1);
           
           //supplier data
            String Supplierline; // Data from text file to be read one line at a time
            String[] Suppliertokens; // line will be parsed into an array of Strings
            System.out.println("Inserting data from text file");
            String Supplier_data = "D:/STUDY/data/Notebook/Supplier_data.txt";
            File SupplierFile = new File(Supplier_data);
            FileReader SupplierInf = new FileReader(SupplierFile);
            BufferedReader SupplierInb = new BufferedReader(SupplierInf);
            System.out.println("Supplier data"); 
            Supplierline = SupplierInb.readLine(); // read a line
            while ( (Supplierline != null) ) {
            System.out.println(Supplierline);
            Suppliertokens = Supplierline.split("\\s"); 
            ps1.setFloat(1, Float.parseFloat(Suppliertokens[0]));
            ps1.setString(2, Suppliertokens[1]); 
            ps1.setString(3, Suppliertokens[2]); 
            ps1.setInt(4, (int)Float.parseFloat(Suppliertokens[3]));
            ps1.execute(); // execute the prepared statement
            ps1.clearParameters();
            Supplierline=SupplierInb.readLine(); //read next line
            }
          
            conn.commit();
            SupplierInb.close();
            SupplierInf.close();
           
           //Create prepared statement  
            String psq="INSERT INTO Notebook VALUES(?,?,?,?)";
            PreparedStatement ps=conn.prepareStatement(psq);
       
           //Notebook data
            String Notebookline; // Data from text file to be read one line at a time
            String[] Notebooktokens; // line will be parsed into an array of Strings
            System.out.println("Inserting data from text file");
            String Notebook_data = "D:/STUDY/data/Notebook/Notebook_data.txt";
            File NotebookFile = new File(Notebook_data);
            FileReader NotebookInf = new FileReader(NotebookFile);
            BufferedReader NotebookInb = new BufferedReader(NotebookInf);
            System.out.println("Notebook data"); 
            Notebookline = NotebookInb.readLine(); // read a line
            while ( (Notebookline != null) ) {
            System.out.println(Notebookline);
            Notebooktokens = Notebookline.split("\\s"); 
            ps.setFloat(1, Float.parseFloat(Notebooktokens[0]));
            ps.setString(2, Notebooktokens[1]); 
            ps.setString(3, Notebooktokens[2]); 
            ps.setInt(4, (int)Float.parseFloat(Notebooktokens[3]));
            ps.execute(); // execute the prepared statement
            ps.clearParameters();
            Notebookline=NotebookInb.readLine(); //read next line
            }
          
            conn.commit();
            NotebookInb.close();
            NotebookInf.close();
            
            //Create prepared statement 
           String psq3="INSERT INTO Repair VALUES(?,?)"; 
           PreparedStatement ps3=conn.prepareStatement(psq3);
           
           //Repair data
            String Repairline; // Data from text file to be read one line at a time
            String[] Repairtokens; // line will be parsed into an array of Strings
            System.out.println("Inserting data from text file");
            String Repair_data = "D:/STUDY/data/Notebook/repair_data.txt";
            File RepairFile = new File(Repair_data);
            FileReader RepairInf = new FileReader(RepairFile);
            BufferedReader RepairInb = new BufferedReader(RepairInf);
            System.out.println("Repair data"); 
            Repairline = RepairInb.readLine(); // read a line
            while ( (Repairline != null) ) {
            System.out.println(Repairline);
            Repairtokens = Repairline.split("\\s"); 
            ps3.setString(1, Repairtokens[0]); 
            ps3.setInt(2, (int)Float.parseFloat(Repairtokens[1]));
            ps3.execute(); // execute the prepared statement
            ps3.clearParameters();
            Repairline=RepairInb.readLine(); //read next line
            }
          
            conn.commit();
            RepairInb.close();
            RepairInf.close();
            
            
          //Create prepared statement  
           String psq2="INSERT INTO Sale VALUES(?,?,?,?,?)"; 
           PreparedStatement ps2=conn.prepareStatement(psq2); 
             
           //Sale data
            String Saleline; // Data from text file to be read one line at a time
            String[] Saletokens; // line will be parsed into an array of Strings
            System.out.println("Inserting data from text file");
            String Sale_data = "D:/STUDY/data/Notebook/Sale_data.txt";
            File SaleFile = new File(Sale_data);
            FileReader SaleInf = new FileReader(SaleFile);
            BufferedReader SaleInb = new BufferedReader(SaleInf);
            System.out.println("Sale data"); 
            Saleline = SaleInb.readLine(); // read a line