package nlbean;

import java.io.*;
import java.util.*;
import jdbc.*;

public class MakeTestDB {

    static public void main(String [] args) {
        if (args.length < 1) {
            System.out.println("usage: 'java nlbean.MakeTestDB delete' to recreate database");
            System.out.println("usage: 'java nlbean.MakeTestDB add' to add test data to the database");
            System.out.println("usage: 'java nlbean.MakeTestDB list' to list the database");
            System.exit(1);
        }
        if (args[0].equalsIgnoreCase("delete")) {
            String s;
            /** NameTable */
            try {
                s = "Drop TABLE NameTable";
                s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            } catch (Exception e) { }
            s = "Create TABLE NameTable (name char(30), email char(35), " +
                "PhoneNumber char(30), misc char(40))";
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            System.out.println(s);
            /** products table */
            try {
                s = "Drop TABLE products";
                s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            } catch (Exception e) { }
            s = "Create TABLE products (ID LONG, productname char(40), " +
                "cost FLOAT)";
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            System.out.println(s);
            /** Employees table */
            try {
                s = "Drop TABLE Employees";
                s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            } catch (Exception e) { }
            s = "Create TABLE Employees (ID LONG, EmpName char(40), " +
                "HireDate char(20), Salary FLOAT)";
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            System.out.println(s);
        } else if (args[0].equalsIgnoreCase("add")) {
            /** Employees */
            String s =
                "INSERT into Employees VALUES (1, 'mark', '1994-1/-', 22000.0)";
            System.out.println(s);
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            s = "INSERT into Employees VALUES (2, 'carol', '1994-2-10', 23000.0)";
            System.out.println(s);
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");

            /** products */
            s = "INSERT into products VALUES (1, 'WebPix', 7.0)";
            System.out.println(s);
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            s = "INSERT into products VALUES (2, 'DBmaster', 19.0)";
            System.out.println(s);
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");

            /** NameTable */
            s = "INSERT into NameTable VALUES ('mark', 'markw@markwatson.com', '123 45678', 'test2')";
            System.out.println(s);
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            s = "INSERT into NameTable VALUES ('carol', 'carol@markwatson.com', '123 45678', 'test2')";
            System.out.println(s);
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");

        } else if (args[0].equalsIgnoreCase("list")) {
            String s;
            s = "Select Employees.EmpName from Employees";
            s = DBInterface.query(s, "jdbc:idb:database/nlbean.prp", "", "");
            System.out.println(s); 
            s = "Select * from products";
            s = DBInterface.query(s, "jdbc:idb:database/nlbean.prp", "", "");
            System.out.println(s); 
            s = "Select * from NameTable";
            s = DBInterface.query(s, "jdbc:idb:database/nlbean.prp", "", "");
            System.out.println(s); 
        } else {
            System.out.println("unknown command line argument: " + args[0]);
        }
    }
}