package pizza.dao;

import org.hibernate.*;
import pizza.persistence.HibernateUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSetMetaData;
//use static import to simplify use of constants--
import static pizza.dao.DBConstants.*;

public class DbDAO {

	private Connection connection; // only used in initDB

	private String dbType;

	// This call creates new or finds old Hibernate Session for this thread
	public Session getSession() {
		// Here we avoid maintaining a Hibernate Session variable--
		// See hibernate.cfg.xml for relevant setup:
		// With the JDBCTransactionFactory (good for single-DB, non-JTA case) and 
		// hibernate.current_session_context_class=thread, Hibernate attaches
		// its Session to the current thread for us, and Transaction to Session
		// Also, at commit time Hibernate does a Session flush for us.		
		return HibernateUtil.getSessionFactory().getCurrentSession();
	}

	public DbDAO()
	{
        String hibernateDialect = HibernateUtil.getHibernateDialect();         
        if (hibernateDialect.contains("Oracle"))
        	dbType = "oracle";
        else if (hibernateDialect.contains("HSQL"))
        	dbType = "hsql";
        //there are 2 subtypes, MySQLInnoDB and MySQLMyISAM, lumped here--
        else if (hibernateDialect.contains("MySQL"))
        	dbType = "mysql";
        else throw new RuntimeException("DAO does not support this hibernate dialect: " + hibernateDialect);
	}

	public String getDbType() {
		return dbType;
	}


	public Connection getConnection() {
		return connection;
	}

	public void initializeDb() {
		// drop tables with FK cols before the tables they refer to
		try {
		connection = getSession().connection(); // JDBC connection from
		// Hibernate session
		dropTable(TOPPING_ORDER_TABLE);
		dropTable(TOPPING_TABLE);
		dropTable(ORDER_TABLE);
		dropTable(PIZZA_SIZE_TABLE);
		dropTable(SYS_TIME_TABLE);

		// Oracle auto-generated ids are based on sequences
		if (dbType.equals("oracle"))
		    dropOracleSequence();

		if (dbType.equals("oracle"))
		    createOracleSequence();

		// create tables that are ref'd by FK's before the tables with the FK
		// cols
		createToppingTable();
		createPizzaSizeTable();
		createOrderTable();
		createToppingOrderTable();
		createSysTimeTable();
		connection = null; // don't use this again!
		} catch (Exception e) {
			// wrap the SQLException up, to be more like ordinary Hibernate
			// in not throwing checked exceptions
			throw new RuntimeException("initialize DB failed" + e.getMessage(), e);
		}
	}

	// Create table methods.
	// We concentrate the platform dependence in one overall createTable method
	// It requires us to specify the foreign key constraints separately since
	// they need special handling for mysql
	// Each FK constraint needs to be the full form
	// foreign key (column1) references table(column2)
	// in order to work for both Oracle and MySql (because of mysql
	// non-standardness)
	private void createOrderTable() throws SQLException {
		String[] foreignKeyRef = { "(size_id) references " + PIZZA_SIZE_TABLE
				+ "(id)" };
		createTable(true /* gen'd id */, "room_number integer not null, "
				+ "size_id integer not null, "
				+ "day integer not null, status integer not null", ORDER_TABLE,
				"id", foreignKeyRef, null);
	}

	private void createPizzaSizeTable() throws SQLException {
		createTable(true /* gen'd id */, "size_name varchar("
				+ MAX_SIZE_STR_SIZE + ") not null", PIZZA_SIZE_TABLE, "id",
				null, "unique (size_name)");
	}

	private void createSysTimeTable() throws SQLException {
		createTable(false, "id integer, current_day integer not null, "
				+ "last_report integer not null", SYS_TIME_TABLE, "id", null, null);
		Statement stmt = connection.createStatement();
		try {
			stmt.execute("insert into " + SYS_TIME_TABLE + " values(1, 1, 0)");
		} finally {
			stmt.close();
		}
	}

	private void createToppingOrderTable() throws SQLException {
		String[] foreignKeyRef = {
				"(order_id) references " + ORDER_TABLE + "(id)",
				"(topping_id) references " + TOPPING_TABLE + "(id)" };
		createTable(false,
				"order_id integer not null, topping_id integer not null",
				TOPPING_ORDER_TABLE, "order_id, topping_id", foreignKeyRef,
				null);
	}

	private void createToppingTable() throws SQLException {
		createTable(true /* gen'd id */, "topping_name varchar("
				+ MAX_TOPPINGS_STR_SIZE + ") not null", TOPPING_TABLE, "id",
				null, "unique(topping_name)");
	}

	// creating a table with foreign key constraints is complicated by
	// the non-standard behavior of mysql. It requires us to create
	// an index on the FK column after the basic create table is done
	// and then do an alter table to add the constraint.
	private void createTable(boolean generateId, String colDecl,
			String tableName, String primaryKey, String[] foreignKeyRef,
			String constraints) throws SQLException {
		Statement stmt = connection.createStatement();
		try {
			String idGenDecl = ""; // put any non-gen'd ids in colDecl
			if (generateId) {
				idGenDecl += "id ";
				if (dbType.equals("hsql"))
					idGenDecl += HSQL_GENERATED_ID_TYPE;
				else if (dbType.equals("oracle"))
					idGenDecl += ORACLE_GENERATED_ID_TYPE;
				else if (dbType.equals("mysql"))
					idGenDecl += MYSQL_GENERATED_ID_TYPE;
				else throw new RuntimeException("unknown dbType" + dbType);
				idGenDecl += " , ";
			}
			String statement = "create table " + tableName + "(" + idGenDecl
					+ colDecl;
			if (primaryKey != null) {
				statement += ", primary key(" + primaryKey + ")";
				if (foreignKeyRef != null && !dbType.equals("mysql")) {
					for (int i = 0; i < foreignKeyRef.length; i++) {
						statement += ", foreign key" + foreignKeyRef[i];
					}
				}
			}
			if (constraints != null) {
				statement += ", " + constraints;
			}
			statement += ")";
			if (dbType.equals("mysql")) {
				statement += "TYPE = InnoDB"; // this type supports
				// transactions in mySQL
			} else if (dbType.equals("oracle")) {
				statement += "INITRANS 3"; // required for serializable
				// transactions in Oracle
			}
			stmt.execute(statement);
			// System.out.println(statement);
			// For mysql, need to create index and then alter table to get FK
			// constraint working
			if (dbType.equals("mysql") && foreignKeyRef != null) {
				for (int i = 0; i < foreignKeyRef.length; i++) {
					int colNameEnd = foreignKeyRef[i].indexOf(")");
					String colName = foreignKeyRef[i].substring(1, colNameEnd);
					statement = "create index " + tableName + "_ix" + i
							+ " on " + tableName + "(" + colName + ")";
					// System.out.println(statement);
					stmt.execute(statement);
					statement = "alter table " + tableName + " add constraint "
							+ tableName + "_fk_" + i;
					statement += " foreign key " + foreignKeyRef[i];
					// System.out.println(statement);
					stmt.execute(statement);
					// System.out.println("done");
				}
			}
		} finally {
			stmt.close();
		}
	}

    // Simplest setup for Oracle id generation: one sequence for everything
    private void createOracleSequence() throws SQLException
    {
	Statement stmt = connection.createStatement();
	try {
	    String statement = "create sequence hibernate_sequence";
	    stmt.execute(statement);
	} finally {
	    stmt.close();
	}
    }
    private void dropOracleSequence() throws SQLException {
	Statement stmt = connection.createStatement();
	try {
	    stmt.execute("drop sequence hibernate_sequence");
	} catch (SQLException e) {
	} finally {
	    stmt.close();
	}
    }


	// for debugging: print any table, getting column names via MetaData JDBC
	// calls
	public void printTable(String tableName) throws SQLException {
		Statement stmt = connection.createStatement();
		System.out.println("Table " + tableName);
		System.out.println("-------------------");

		try {
			try {
				ResultSet rset = stmt
						.executeQuery("select * from " + tableName);
				ResultSetMetaData meta = rset.getMetaData();
				for (int i = 1; i <= meta.getColumnCount(); i++) {
					String colName = meta.getColumnLabel(i);
					System.out.print(colName + " ");
				}
				System.out.println();
				while (rset.next()) {
					for (int i = 1; i <= meta.getColumnCount(); i++)
						System.out.print(rset.getString(i) + "  ");
					System.out.println();
				}
				System.out.println("================================");
			} catch (SQLException e) {
			}
		} finally {
			stmt.close();
		}
	}

	// for debugging
	public void printTables() {
		try {
			printTable(SYS_TIME_TABLE);
			printTable(ORDER_TABLE);
			printTable(PIZZA_SIZE_TABLE);
			printTable(TOPPING_TABLE);
			printTable(TOPPING_ORDER_TABLE);
		} catch (SQLException e) {
			System.out.println("error in printTables: " + e);
		} finally {
		}
	}

	private void dropTable(String tableName) throws SQLException {
		Statement stmt = connection.createStatement();
		try {
			stmt.execute("drop table " + tableName);
		} catch (SQLException e) {
			// nothing here (but can throw in createStatement or close)
		} finally {
			stmt.close();
		}
	}

	public void startTransaction() {
		getSession().beginTransaction();
	}

	public void commitTransaction() {
		getSession().getTransaction().commit(); // this closes the Session
	}

	public void rollbackTransaction() {
		getSession().getTransaction().rollback();
	}

	// If the caller has already seen an exception, they probably
	// don't want to handle a failing rollback, so they can use this.
	// Then the caller should issue its own exception, for example,
	// with the firstException set as the cause of its own exception
	public void rollbackAfterException() {
		try {
			rollbackTransaction();
		} catch (Exception e) {
			// discard secondary exception--probably server can't be reached
		}
	}
}