package com.everythingflex.employeeDirectory.utils
{
    import com.adobe.cairngorm.control.CairngormEventDispatcher;
    import com.everythingflex.employeeDirectory.control.GetUserIdEvent;
    import com.everythingflex.employeeDirectory.dao.EmployeeDAO;
    import com.everythingflex.employeeDirectory.dao.PreferencesDAO;
    import com.everythingflex.employeeDirectory.model.ModelLocator;

    import flash.data.SQLResult;
    import flash.data.SQLStatement;
    import flash.events.SQLErrorEvent;
    import flash.events.SQLEvent;
    import flash.filesystem.File;

    public class CreateTables
    {

        private var model:ModelLocator = ModelLocator.getInstance();

        private var employeeDAO:EmployeeDAO = new EmployeeDAO();
        private var preferencesDAO:PreferencesDAO = new PreferencesDAO();

        private var createEmployeesSQL:SQLStatement;
        private var createPreferencesSQL:SQLStatement;

        // check for existance of local database and either open existing or create new tables
        public function init():void{
            var dbFile:File = File.applicationStorageDirectory.resolve("EmployeeDirectory.db");
            if(dbFile.exists) {
                model.db.addEventListener(SQLEvent.OPEN, statusHandler);
                model.db.open(dbFile);
            } else {
                model.db.addEventListener(SQLEvent.OPEN, newDatabaseHandler);
                model.db.open(dbFile);
            }
        }

        // get local data
        private function statusHandler(event:SQLEvent):void {
            employeeDAO.getEmployees();
            preferencesDAO.getUserId();
        }

        //  create new tables
        private function newDatabaseHandler(event:SQLEvent):void {
            createEmployeesTable();
            createPreferencesTable();
        }

        // create Employees table
        private function createEmployeesTable():void{
            var sqlText:String = "CREATE TABLE Employees(EMAIL TEXT, EMPLOYEE_ID INTEGER PRIMARY KEY, FIRST_NAME TEXT, JOB_TITLE TEXT, LAST_NAME TEXT, USER_ID INTEGER)";
            createEmployeesSQL = new SQLStatement();
            createEmployeesSQL.sqlConnection = model.db;
            createEmployeesSQL.addEventListener(SQLEvent.RESULT, createEmployeesTableResult);
            createEmployeesSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
            createEmployeesSQL.text = sqlText;
            createEmployeesSQL.execute();
            model.log +=  sqlText + " \n";
        }

        private function createEmployeesTableResult(event:SQLEvent):void{
            model.log += "Employees table created \n";
        }

        // Create Preferences table
        private function createPreferencesTable():void{
            var sqlText:String = "CREATE TABLE Preferences(USER_ID INTEGER)";
            createPreferencesSQL = new SQLStatement();
            createPreferencesSQL.sqlConnection = model.db;
            createPreferencesSQL.addEventListener(SQLEvent.RESULT, createPreferencesTableResult);
            createPreferencesSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
            createPreferencesSQL.text = sqlText;
            createPreferencesSQL.execute();
            model.log +=  sqlText + " \n";
        }

        // get a new unique userId from remote server
        private function createPreferencesTableResult(event:SQLEvent):void{
            model.log += "Preferences table created \n";
            var getUserIdEvent : GetUserIdEvent = new GetUserIdEvent();
            CairngormEventDispatcher.getInstance().dispatchEvent( getUserIdEvent );
        }

    }
}