/**
 * Copyright (C) 2006 - present David Bulmore
 * All Rights Reserved.
 *
 * This file is part of Easy Java Persistence.
 *
 * EJP is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 * FITNESS FOR A PARTICULAR PURPOSE. See the accompanying license
 * for more details.
 *
 * You should have received a copy of the license along with EJP; if not,
 * go to http://www.EasierJava.com and download the latest version.
 */

package ejp.examples;

import ejp.DatabaseException;
import ejp.DatabaseManager;

/**
 * Creates a simple in-memory HSQL/H2 database for testing/example purposes.
 */
public class CreateDatabase 
  {
    public static DatabaseManager getDatabase() throws DatabaseException
      {
        return getDatabase(null);
      }
    
    public static DatabaseManager getDatabase(String filename) throws DatabaseException
      {
        DatabaseManager dbm;
        
        if (filename != null)
          dbm = DatabaseManager.getDatabaseManager("ejp", 5, "org.hsqldb.jdbcDriver", "jdbc:hsqldb:file:" + filename, "sa", "");
        else
          dbm = DatabaseManager.getDatabaseManager("ejp", 5, "org.hsqldb.jdbcDriver", "jdbc:hsqldb:mem:ejp_example", "sa", "");

        createHSQLDatabase(dbm);
        
        return dbm;
      }
    
    public static void createHSQLDatabase(DatabaseManager dbm) throws DatabaseException
      {
        dbm.executeUpdate("CREATE MEMORY TABLE IMAGES(" +
                "IMAGE_ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY," +
                "IMAGE_DATA BLOB);");
        
        dbm.executeUpdate("CREATE MEMORY TABLE CUSTOMERS(" +
                "CUSTOMER_ID VARCHAR(40) NOT NULL PRIMARY KEY," +
                "PASSWORD VARCHAR(40) NOT NULL," +
                "FIRST_NAME VARCHAR(40) NOT NULL," +
                "LAST_NAME VARCHAR(60) NOT NULL," +
                "COMPANY_NAME VARCHAR(60)," +
                "EMAIL VARCHAR(255)," +
                "CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");

        dbm.executeUpdate("CREATE MEMORY TABLE SUPPORT(" +
                "SUPPORT_ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY," +
                "CUSTOMER_ID VARCHAR(40)," +
                "CODE VARCHAR(10)," +
                "STATUS VARCHAR(20)," +
                "PHONE VARCHAR(20)," +
                "EMAIL VARCHAR(255)," +
                "REQUEST VARCHAR(255)," +
                "CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
                "FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID) ON DELETE CASCADE ON UPDATE CASCADE)");

        dbm.executeUpdate("CREATE MEMORY TABLE ORDERS(" +
                "ORDER_ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY," +
                "CUSTOMER_ID VARCHAR(40) NOT NULL," +
                "PRODUCT VARCHAR(40)," +
                "QUANTITY INTEGER," +
                "PRICE DOUBLE," +
                "STATUS VARCHAR(20)," +
                "CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
                "FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID) ON DELETE CASCADE ON UPDATE CASCADE)");
      }
  }