def initialize_startup():
    """ Force DB tables create, in case no data is already found."""
    is_db_empty = False
    session = SA_SESSIONMAKER()
    inspector = reflection.Inspector.from_engine(session.connection())
    if len(inspector.get_table_names()) < 1:
        LOGGER.debug("Database access exception, maybe DB is empty")
        is_db_empty = True
    session.close()

    if is_db_empty:
        LOGGER.info("Initializing Database")
        if os.path.exists(cfg.DB_VERSIONING_REPO):
            shutil.rmtree(cfg.DB_VERSIONING_REPO)
        migratesqlapi.create(cfg.DB_VERSIONING_REPO, os.path.split(cfg.DB_VERSIONING_REPO)[1])
        _update_sql_scripts()
        migratesqlapi.version_control(cfg.DB_URL, cfg.DB_VERSIONING_REPO, version=cfg.DB_CURRENT_VERSION)
        session = SA_SESSIONMAKER()
        model.Base.metadata.create_all(bind=session.connection())
        session.commit()
        session.close()
        LOGGER.info("Database Default Tables created successfully!")
    else:
        _update_sql_scripts()
        migratesqlapi.upgrade(cfg.DB_URL, cfg.DB_VERSIONING_REPO, version=cfg.DB_CURRENT_VERSION)
        LOGGER.info("Database already has some data, will not be re-created!")
    return is_db_empty

def reset_database():
    """
    Remove all tables in DB.
    """
    LOGGER.warning("Your Database tables will be deleted.")
    try:
        session = SA_SESSIONMAKER()
        LOGGER.debug("Delete connection initiated.")
        inspector = reflection.Inspector.from_engine(session.connection())
        for table in inspector.get_table_names():
            try:
                LOGGER.debug("Removing:" + table)
                session.execute(text("DROP TABLE \"%s\" CASCADE" % table))
            except Exception:
                try:
                    session.execute(text("DROP TABLE %s" % table))
                except Exception as excep1:
                    LOGGER.error("Could no drop table %s", table)
                    LOGGER.exception(excep1)
        session.commit()
        LOGGER.info("Database was cleanup!")
    except Exception as excep:
        LOGGER.warning(excep)
    finally:
        session.close()

def _exec_update(boolean_value, logger):
    session = SA_SESSIONMAKER()
    try:
        logger.info("Executing Db update script 015...")
        session.execute(text("""UPDATE "MAPPED_TIME_SERIES_REGION_DATA" SET _region_mapping =
                        (SELECT dt.gid
                        FROM "MAPPED_REGION_MAPPING_DATA" rm, "DATA_TYPES" dt
                        WHERE dt.id = rm.id AND "MAPPED_TIME_SERIES_REGION_DATA"._connectivity= rm._connectivity);"""))

        session.execute(text("""UPDATE "MAPPED_TIME_SERIES_DATA" SET _has_surface_mapping = """ + boolean_value + """
                            WHERE
                                EXISTS (SELECT * FROM "DATA_TYPES" dt
                                        WHERE dt.id="MAPPED_TIME_SERIES_DATA".id AND dt.type in ('TimeSeriesSurface',
                                                'TimeSeriesEEG', 'TimeSeriesSEEG', 'TimeSeriesMEG'))
                            OR EXISTS (SELECT * from "MAPPED_TIME_SERIES_REGION_DATA" tr
                                    WHERE tr.id="MAPPED_TIME_SERIES_DATA".id AND tr._region_mapping is not NULL);"""))

        session.execute(text("""UPDATE "MAPPED_TIME_SERIES_DATA" SET _has_volume_mapping = """ + boolean_value + """
                            WHERE
                                EXISTS (SELECT * FROM "DATA_TYPES" dt
                                    WHERE dt.id="MAPPED_TIME_SERIES_DATA".id AND dt.type in ('TimeSeriesVolume'));"""))
        session.commit()
        logger.info("DB update script 015 committed.")
        return True

    except Exception, excep:
        logger.exception(excep)
        return False

def remove_visualizer_references():
    """
    As we removed an algorithm, remove left-overs.
    """

    LOGGER.info("Starting to remove references towards old viewer ....")

    session = SA_SESSIONMAKER()
    try:
        session.execute(text(
            """DELETE FROM "OPERATIONS" WHERE fk_from_algo IN
               (SELECT A.id FROM "ALGORITHMS" A, "ALGORITHM_GROUPS" AG
               WHERE  A.fk_algo_group = AG.id AND module = 'tvb.adapters.visualizers.cross_correlation'
                      AND classname = 'PearsonCorrelationCoefficientVisualizer');"""))

        session.execute(text(
            """DELETE FROM "WORKFLOW_VIEW_STEPS" WHERE fk_algorithm IN
               (SELECT A.id FROM "ALGORITHMS" A, "ALGORITHM_GROUPS" AG
               WHERE  A.fk_algo_group = AG.id AND module = 'tvb.adapters.visualizers.cross_correlation'
                      AND classname = 'PearsonCorrelationCoefficientVisualizer');"""))
        session.commit()
    except Exception as excep:
        LOGGER.exception(excep)
    finally:
        session.close()

    LOGGER.info("References removed.")

def change_algorithm(module, classname, new_module, new_class):
    """
    Change module and classname fields in ALGORITHM_GROUPS table.
    """
    session = SA_SESSIONMAKER()
    try:
        session.execute(text(
            """UPDATE "ALGORITHM_GROUPS"
               SET module = '""" + new_module + """', classname = '""" + new_class + """'
               WHERE module = '""" + module + """' AND classname = '""" + classname + """';"""))
        session.commit()
    except Exception, excep:
        LOGGER.exception(excep)

def _adapt_epileptor_simulations():
    """
    Previous Simulations on EpileptorWithPermitivity model, should be converted to use the Epileptor model.
    As the parameters from the two models are having different ranges and defaults, we do not translate parameters,
    we only set the Epileptor as model instead of EpileptorPermittivityCoupling, and leave the model params to defaults.
    """
    session = SA_SESSIONMAKER()
    epileptor_old = "EpileptorPermittivityCoupling"
    epileptor_new = "Epileptor"
    param_model = "model"

    try:
        all_ep_ops = session.query(model.Operation).filter(
            model.Operation.parameters.ilike('%"' + epileptor_old + '"%')).all()
        files_helper = FilesHelper()
        all_bursts = dict()

        for ep_op in all_ep_ops:
            try:
                op_params = parse_json_parameters(ep_op.parameters)
                if op_params[param_model] != epileptor_old:
                    LOGGER.debug("Skipping op " + str(op_params[param_model]) + " -- " + str(ep_op))
                    continue

                LOGGER.debug("Updating " + str(op_params))
                op_params[param_model] = epileptor_new
                ep_op.parameters = json.dumps(op_params, cls=MapAsJson.MapAsJsonEncoder)
                LOGGER.debug("New params:" + ep_op.parameters)
                files_helper.write_operation_metadata(ep_op)

                burst = dao.get_burst_for_operation_id(ep_op.id)
                if burst is not None:
                    LOGGER.debug("Updating burst:" + str(burst))
                    burst.prepare_after_load()
                    burst.simulator_configuration[param_model] = {'value': epileptor_new}
                    burst._simulator_configuration = json.dumps(burst.simulator_configuration,
                                                                cls=MapAsJson.MapAsJsonEncoder)
                    if not all_bursts.has_key(burst.id):
                        all_bursts[burst.id] = burst

            except Exception:
                LOGGER.exception("Could not process " + str(ep_op))

        session.add_all(all_ep_ops)
        session.add_all(all_bursts.values())
        session.commit()

    except Exception:
        LOGGER.exception("Could not update Simulation Epileptor Params")
    finally:
        session.close()

def downgrade(migrate_engine):
    """Operations to reverse the above upgrade go here."""
    meta.bind = migrate_engine

    table = meta.tables['MAPPED_SURFACE_DATA']
    drop_column(COLUMN_N1, table)
    drop_column(COLUMN_N2, table)
    drop_column(COLUMN_N3, table)

    session = SA_SESSIONMAKER()
    session.execute(text("""UPDATE "OPERATIONS" SET status='4-FINISHED' WHERE status = '5-FINISHED' """))
    session.execute(text("""UPDATE "OPERATIONS" SET status='3-STARTED' WHERE status = '4-PENDING' """))
    session.commit()
    session.close()

def downgrade(_migrate_engine):
    """Operations to reverse the above upgrade go here."""
    try:
        session = SA_SESSIONMAKER()
        session.execute(text("""UPDATE "BURST_CONFIGURATIONS" SET _simulator_configuration =
                                REPLACE(REPLACE(_simulator_configuration, "range_1", "first_range"),
                                                                          "range_2", "second_range");"""))
        session.execute(text("""UPDATE "OPERATIONS" SET parameters =
                                REPLACE(REPLACE(parameters, "range_1", "first_range"), "range_2", "second_range");"""))
        session.commit()
        session.close()
    except Exception as excep:
        ## This update is not critical. We can run even in case of error at update
        logger = get_logger(__name__)
        logger.exception(excep)

def downgrade(migrate_engine):
    """Operations to reverse the above upgrade go here."""
    meta.bind = migrate_engine

    table = meta.tables['DATA_TYPES_GROUPS']
    drop_column(COL_RANGES_1, table)
    drop_column(COL_RANGES_2, table)
    
    session = SA_SESSIONMAKER()
    
    session.execute(text("""UPDATE "OPERATIONS"
                               SET status = 
                                CASE
                                    WHEN status = '4-FINISHED' THEN 'FINISHED'
                                    WHEN status = '3-STARTED' THEN 'STARTED'
                                    WHEN status = '2-CANCELED' THEN 'CANCELED'
                                    ELSE 'ERROR'
                                END
                             WHERE status IN ('4-FINISHED', '2-CANCELED', '3-STARTED', '1-ERROR');"""))

def upgrade(_migrate_engine):
    """
    Upgrade operations go here.
    Don't create your own engine; bind migrate_engine to your metadata.
    """
    session = SA_SESSIONMAKER()
    session.execute(text("DROP TABLE \"MAPPED_STRUCTURAL_MRI_DATA\""))
    session.commit()
    session.close()

    def test_db_mapping(self):
        """ Test DB storage/retrieval of a simple traited attribute"""
        session = SA_SESSIONMAKER()
        model.Base.metadata.create_all(bind=session.connection())
        session.commit()
        session.close()
        
        # test data
        dikt = {'a': 6}
        tup = ('5', 9.348)
        dtype = numpy.dtype(float)
        json = {'a': 'asdf', 'b': {'23': '687568'}}

        test_inst = MappedTestClass()
        test_inst.dikt = copy.deepcopy(dikt)
        test_inst.tup = copy.deepcopy(tup)
        test_inst.dtype = copy.deepcopy(dtype)
        test_inst.json = copy.deepcopy(json)
        test_inst.set_operation_id(self.operation.id)
        test_inst = dao.store_entity(test_inst)

        test_inst = dao.get_generic_entity(MappedTestClass, test_inst.gid, 'gid')[0]
        self.assertEqual(test_inst.dikt, dikt)
        self.assertEqual(test_inst.tup, tup)
        self.assertEqual(test_inst.dtype, dtype)
        self.assertEqual(test_inst.json, json)

def upgrade(_migrate_engine):
    """
    Upgrade operations go here.
    Don't create your own engine; bind migrate_engine to your metadata.
    """
    try:
        session = SA_SESSIONMAKER()
        session.execute(text("""UPDATE "BURST_CONFIGURATIONS" SET _simulator_configuration =
                                REPLACE(REPLACE(_simulator_configuration, "first_range", "range_1"),
                                                                          "second_range", "range_2");"""))
        session.execute(text("""UPDATE "OPERATIONS" SET parameters =
                                REPLACE(REPLACE(parameters, "first_range", "range_1"), "second_range", "range_2");"""))
        session.commit()
        session.close()
    except Exception, excep:
        ## This update is not critical. We can run even in case of error at update
        logger = get_logger(__name__)
        logger.exception(excep)

def downgrade(migrate_engine):
    """Operations to reverse the above upgrade go here."""
    meta.bind = migrate_engine
    table1 = meta.tables['MAPPED_CONNECTIVITY_DATA']

    create_column(COL_OLD, table1)

    session = SA_SESSIONMAKER()
    session.execute(text("UPDATE \"MAPPED_CONNECTIVITY_DATA\" set _unidirectional=_undirected"))
    session.commit()
    session.close()

    drop_column(COL_NEW, table1)
    create_column(COL_NOSE_CORRECTION, table1)

def upgrade(migrate_engine):
    """
    Upgrade operations go here.
    Don't create your own engine; bind migrate_engine to your metadata.
    """
    meta.bind = migrate_engine

    table = meta.tables['MAPPED_SURFACE_DATA']
    create_column(COLUMN_N1, table)
    create_column(COLUMN_N2, table)
    create_column(COLUMN_N3, table)

    session = SA_SESSIONMAKER()
    session.execute(text("""UPDATE "OPERATIONS" SET status='5-FINISHED' WHERE status = '4-FINISHED' """))
    session.commit()
    session.close()

    def introspect(self, do_create):
        """
        Introspect a given module to: 
            - create tables for custom DataType;
            - populate adapter algorithms references. 
        """
        self.logger.debug("Introspection into module:" + self.module_name)
        module = __import__(self.module_name, globals(), locals(), ["__init__"])
        try:
            path_adapters = module.ADAPTERS
            self.path_types = module.DATATYPES_PATH
            self.removers_path = module.REMOVERS_PATH
            self.path_portlets = getattr(module, 'PORTLETS_PATH', [])
        except Exception as excep:
            self.logger.warning("Module " + self.module_name + " is not fully introspect compatible!")
            self.logger.warning(excep.message)
            return

        if do_create:
            self.logger.debug("Found Datatypes_Path=" + str(self.path_types))
            # DataTypes only need to be imported for adding to DB tables
            for path in self.path_types:
                self.__get_datatypes(path)

            session = SA_SESSIONMAKER()
            model.Base.metadata.create_all(bind=session.connection())
            session.commit()
            session.close()

            self.logger.debug("Found Adapters_Dict=" + str(path_adapters))
            for category_name in path_adapters:
                category_details = path_adapters[category_name]
                launchable = bool(category_details.get(LAUNCHABLE))
                rawinput = bool(category_details.get(RAWINPUT))
                display = bool(category_details.get(DISPLAYER))
                order_nr = category_details.get(ORDER, 999)
                category_instance = dao.filter_category(category_name, rawinput, display, launchable, order_nr)
                if category_instance is not None:
                    category_instance.last_introspection_check = datetime.datetime.now()
                    category_instance.removed = False
                else:
                    category_state = category_details.get(STATE, '')
                    category_instance = model.AlgorithmCategory(category_name, launchable, rawinput, display,
                                                                category_state, order_nr, datetime.datetime.now())
                category_instance = dao.store_entity(category_instance)
                for actual_module in path_adapters[category_name]['modules']:
                    self.__read_adapters(category_instance.id, actual_module)

            for path in self.path_portlets:
                self.__get_portlets(path)
        ### Register Remover instances for current introspected module
        removers.update_dictionary(self.get_removers_dict())

def upgrade(migrate_engine):
    """
    Upgrade operations go here.
    Don't create your own engine; bind migrate_engine to your metadata.
    """
    meta.bind = migrate_engine
    table1 = meta.tables['MAPPED_CONNECTIVITY_DATA']

    create_column(COL_NEW, table1)

    session = SA_SESSIONMAKER()
    session.execute(text("UPDATE \"MAPPED_CONNECTIVITY_DATA\" set _undirected=_unidirectional"))
    session.commit()
    session.close()

    drop_column(COL_OLD, table1)
    drop_column(COL_NOSE_CORRECTION, table1)

def upgrade(migrate_engine):
    """
    Upgrade operations go here.
    Don't create your own engine; bind migrate_engine to your metadata.
    """
    meta = MetaData(bind=migrate_engine)
    
    table = _prepare_table(meta, 'USER_PREFERENCES')
    table.c.user_id.alter(name='fk_user')
    
    table = _prepare_table(meta, 'BURST_CONFIGURATIONS')
    table.c.project_id.alter(name='fk_project')
    
    table = _prepare_table(meta, 'WORKFLOWS',)
    table.c.project_id.alter(name='fk_project')
    table.c.burst_id.alter(name='fk_burst')
    
    table = _prepare_table(meta, 'WORKFLOW_STEPS')
    table.c.workflow_id.alter(name='fk_workflow')
    table.c.algorithm_id.alter(name='fk_algorithm')
    table.c.resulted_op_id.alter(name='fk_operation')
    
    table = _prepare_table(meta, 'MAPPED_DATATYPE_MEASURE')
    table.c.analyzed_datatype.alter(name='_analyzed_datatype')
    
    ## Fix Lookup Table mapping.
    table = _prepare_table(meta, 'MAPPED_LOOK_UP_TABLE_DATA')
    create_column(COL_1, table)
    create_column(COL_2, table)
    create_column(COL_3, table)
    create_column(COL_4, table)
    create_column(COL_5, table)
    create_column(COL_6, table)
    create_column(COL_7, table)
    session = SA_SESSIONMAKER()
    session.execute(text('DELETE FROM "MAPPED_LOOK_UP_TABLE_DATA";'))
    session.execute(text('insert into "MAPPED_LOOK_UP_TABLE_DATA"(id, _equation, _number_of_values, _invdx, _xmax, _xmin, _df, _dx, _data) '
                         'select id, \'\', _number_of_values, _invdx, _xmax, _xmin, _df, _dx, _data from "MAPPED_NERF_TABLE_DATA";'))
    session.execute(text('insert into "MAPPED_LOOK_UP_TABLE_DATA"(id, _equation, _number_of_values, _invdx, _xmax, _xmin, _df, _dx, _data) '
                         'select id, \'\', _number_of_values, _invdx, _xmax, _xmin, _df, _dx, _data from "MAPPED_PSI_TABLE_DATA";'))
    session.commit()
    session.close()
        
    table = _prepare_table(meta, 'MAPPED_NERF_TABLE_DATA')
    table.drop()
    
    table = _prepare_table(meta, 'MAPPED_PSI_TABLE_DATA')
    table.drop()

def downgrade(migrate_engine):
    """Operations to reverse the above upgrade go here."""
    meta.bind = migrate_engine

    table = meta.tables['USERS']
    create_column(COLUMN_N1, table)
    table = meta.tables['BURST_CONFIGURATIONS']
    create_column(COLUMN_N2, table)
    table = meta.tables['OPERATIONS']
    alter_column(COLUMN_N3_NEW, table=table, name=COLUMN_N3_OLD.name)

    try:
        meta.bind = migrate_engine
        session = SA_SESSIONMAKER()
        session.execute(text("""UPDATE "DATA_TYPES" SET module='tvb.datatypes.surfaces' WHERE "type" = 'RegionMapping' """))
        session.execute(text("""UPDATE "DATA_TYPES" SET module='tvb.datatypes.surfaces' WHERE "type" = 'LocalConnectivity' """))
        session.execute(text("""UPDATE "DATA_TYPES" SET module='tvb.datatypes.surfaces' WHERE "type" = 'Cortex' """))
        session.commit()
        session.close()
    except Exception:
        LOGGER.exception("Cold not update datatypes")
        raise

def _adapt_simulation_monitor_params():
    """
    For previous simulation with EEG monitor, adjust the change of input parameters.
    """
    session = SA_SESSIONMAKER()

    param_connectivity = "connectivity"
    param_eeg_proj_old = "monitors_parameters_option_EEG_projection_matrix_data"
    param_eeg_proj_new = "monitors_parameters_option_EEG_projection"
    param_eeg_sensors = "monitors_parameters_option_EEG_sensors"
    param_eeg_rm = "monitors_parameters_option_EEG_region_mapping"

    try:
        all_eeg_ops = session.query(model.Operation).filter(
            model.Operation.parameters.ilike('%"' + param_eeg_proj_old + '"%')).all()
        files_helper = FilesHelper()
        all_bursts = dict()

        for eeg_op in all_eeg_ops:
            try:
                op_params = parse_json_parameters(eeg_op.parameters)
                LOGGER.debug("Updating " + str(op_params))
                old_projection_guid = op_params[param_eeg_proj_old]
                connectivity_guid = op_params[param_connectivity]

                rm = dao.get_generic_entity(RegionMapping, connectivity_guid, "_connectivity")[0]
                dt = dao.get_generic_entity(model.DataType, old_projection_guid, "gid")[0]

                if dt.type == 'ProjectionSurfaceEEG':
                    LOGGER.debug("Previous Prj is surface: " + old_projection_guid)
                    new_projection_guid = old_projection_guid
                else:
                    new_projection_guid = session.execute(text("""SELECT DT.gid
                            FROM "MAPPED_PROJECTION_MATRIX_DATA" PMO, "DATA_TYPES" DTO,
                                 "MAPPED_PROJECTION_MATRIX_DATA" PM, "DATA_TYPES" DT
                            WHERE DTO.id=PMO.id and DT.id=PM.id and PM._sensors=PMO._sensors and
                                  PM._sources='""" + rm._surface + """' and
                                  DTO.gid='""" + old_projection_guid + """';""")).fetchall()[0][0]
                    LOGGER.debug("New Prj is surface: " + str(new_projection_guid))

                sensors_guid = session.execute(text("""SELECT _sensors
                            FROM "MAPPED_PROJECTION_MATRIX_DATA"
                            WHERE id = '""" + str(dt.id) + """';""")).fetchall()[0][0]

                del op_params[param_eeg_proj_old]
                op_params[param_eeg_proj_new] = str(new_projection_guid)
                op_params[param_eeg_sensors] = str(sensors_guid)
                op_params[param_eeg_rm] = str(rm.gid)

                eeg_op.parameters = json.dumps(op_params, cls=MapAsJson.MapAsJsonEncoder)
                LOGGER.debug("New params:" + eeg_op.parameters)
                files_helper.write_operation_metadata(eeg_op)

                burst = dao.get_burst_for_operation_id(eeg_op.id)
                if burst is not None:
                    LOGGER.debug("Updating burst:" + str(burst))
                    burst.prepare_after_load()
                    del burst.simulator_configuration[param_eeg_proj_old]
                    burst.simulator_configuration[param_eeg_proj_new] = {'value': str(new_projection_guid)}
                    burst.simulator_configuration[param_eeg_sensors] = {'value': str(sensors_guid)}
                    burst.simulator_configuration[param_eeg_rm] = {'value': str(rm.gid)}
                    burst._simulator_configuration = json.dumps(burst.simulator_configuration,
                                                                cls=MapAsJson.MapAsJsonEncoder)
                    if not all_bursts.has_key(burst.id):
                        all_bursts[burst.id] = burst

            except Exception:
                LOGGER.exception("Could not process " + str(eeg_op))

        session.add_all(all_eeg_ops)
        session.add_all(all_bursts.values())
        session.commit()

    except Exception:
        LOGGER.exception("Could not update Simulation Monitor Params")
    finally:
        session.close()

def _transfer_projection_matrices():
    """
    Previous ProjectionRegionM/EEG objects should be Removed,
    and ProjectionSurfaceM/EEG should be transported into the new DB tables.
    """
    session = SA_SESSIONMAKER()
    LOGGER.info("Transferring Projections Surface ...")

    try:
        # Ony after SqlAlchemy finished initialization the new table MAPPED_PROJECTION_DATA exists
        session.execute(text("""INSERT into "MAPPED_PROJECTION_DATA" (id, _sources, _sensors, _projection_type)
                            SELECT PS.id, PM._sources, PM._sensors, 'projEEG'
                            FROM "MAPPED_PROJECTION_SURFACE_EEG_DATA" PS, "MAPPED_PROJECTION_MATRIX_DATA" PM
                            WHERE PM.id=PS.id;"""))

        session.execute(text("""INSERT into "MAPPED_PROJECTION_DATA" (id, _sources, _sensors, _projection_type)
                            SELECT PS.id, PM._sources, PM._sensors, 'projMEG'
                            FROM "MAPPED_PROJECTION_SURFACE_MEG_DATA" PS, "MAPPED_PROJECTION_MATRIX_DATA" PM
                            WHERE PM.id=PS.id;"""))

        session.execute(text("""DROP TABLE "MAPPED_PROJECTION_SURFACE_EEG_DATA";"""))
        session.execute(text("""DROP TABLE "MAPPED_PROJECTION_SURFACE_MEG_DATA";"""))
        session.execute(text("""DROP TABLE "MAPPED_PROJECTION_MATRIX_DATA";"""))

        LOGGER.info("Removing Projections Region ...")

        session.execute(text("""DELETE from "DATA_TYPES"
                            WHERE type in ('ProjectionRegionEEG', 'ProjectionRegionMEG');"""))
        session.commit()

    except Exception:
        LOGGER.exception("Could not update Projection references")

    finally:
        session.close()