/** 
 *   Syed H. Hasan <haroon@cs.columbia.edu>
 *   Make sure JDBC driver is installed
 *   Run this file once to create the 3 tables.
 **/

import java.sql.*;

public class CreateTables
{
    public static void main (String[] args)
    {
        Connection conn = null;

        try
        {   
	    // Modify DB Details here (DB Name = 'vistaseek').
            
            /*
            String userName = "root";
            String password = "root";
            String url = "jdbc:mysql://localhost/vistaseek";
            */

            String userName = "class";
            String password = "sip";
            String url = "jdbc:mysql://cisalpino.cs.columbia.edu/sip";

            // Find the driver and establish connection
            try
            {
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		}
		catch(Exception ee)
		{
		System.out.println("Driver not loaded");
		}

            conn = DriverManager.getConnection(url, userName, password);
            System.out.println("Database connection established");

            //Create tables here
            createBadges(conn);
            createRoomTags(conn);
            createLocationInfo(conn);

        }
        catch (Exception e)
        {
            System.err.println ("Cannot connect to database server");
        }
        finally
        {
            if (conn != null)
            {
            	try
                {
                    conn.close();
                    System.out.println ("Database connection terminated");
                }
                catch (Exception e){}
            }
        }
    }//main

    // Create the table with badges and matching names
    public static void createBadges(Connection conn)
    {
        try
        {
            Statement s = conn.createStatement();
	    int count=0;
	
            // Send these queries to MySQL
	    s.executeUpdate("DROP TABLE IF EXISTS badges");
	    s.executeUpdate("CREATE TABLE badges ("
                          + "badge_id INT UNSIGNED NOT NULL,"
                          + "PRIMARY KEY (badge_id),"
                          + "name VARCHAR(40))");
            
            // 30 and 31 MIGHT be badge numbers, didn't test yet
            count = s.executeUpdate ("INSERT INTO badges (badge_id, name)"
				   + " VALUES"
                                   + "(30, 'John Doe'),"
				   + "(31, 'Jane Doe')");

            s.close ();
	    System.out.println (count + " rows of 'badges' were inserted");
            }
            catch (Exception e)
            {
                System.err.println ("Cannot create table 'badges'");
            }
	} //method: createBadges

	// Create the table with rooms and matching tags
	public static void createRoomTags(Connection conn)
	{
            try
            {
                Statement s = conn.createStatement();
                int count=0;

                s.executeUpdate("DROP TABLE IF EXISTS room_tags");
                s.executeUpdate("CREATE TABLE room_tags ("
                              + "tag_id INT UNSIGNED NOT NULL,"
                              + "PRIMARY KEY (tag_id),"
                              + "room VARCHAR(40))");
                
                // 58 and 59 MIGHT be room tag numbers, need to check
                count = s.executeUpdate ("INSERT INTO room_tags (tag_id, room)"
                                      + " VALUES"
                                      + "(58, 'IRT Lab'),"
                                      + "(59, 'Room 613')");

	         s.close ();
		 System.out.println (count + " rows of 'room_tags' were inserted");
             }
             catch (Exception e)
             {
                 System.err.println ("Cannot create table 'room_tags'");
             }
	} //method: createRoomTags

	// Create the table which matches badges to tags
        public static void createLocationInfo(Connection conn)
        {
            try
	    {
                Statement s = conn.createStatement();
	        int count=0;

                s.executeUpdate("DROP TABLE IF EXISTS location_info");
                s.executeUpdate("CREATE TABLE location_info ("
                              + "badge_id INT UNSIGNED NOT NULL,"
                              + "tag_id INT UNSIGNED NOT NULL,"
                              + "PRIMARY KEY (badge_id),"
                              + "status VARCHAR(10))");

		// These values will be constantly updated by UpdateDB.java
                count = s.executeUpdate ("INSERT INTO location_info (badge_id, tag_id, status)"
                                       + " VALUES"
                                       + "(30, 59, 'no tag'),"
                                       + "(31, 58, 'no tag')");

                s.close ();
                System.out.println (count + " rows of 'location_info' were inserted");
            }
            catch (Exception e)
            {
                System.err.println ("Cannot create table 'location_info'");
            }
        } //method: createLocationInfo

}//CreateTables.java