class Table():
    def __init__(self, name: str, schema: 'Schema' = None, db: 'Database' = None) -> None:
        self.name = name #type: str
        self.schema = schema #type: Schema
        # sommige bron databases hebben geen schema, maar alleen db
        self.db = db
        if schema:
            self.db = schema.db  # type: Database
        self.key_names = [] #type: List[str]
        self.columns = [] #type: List[Column]
        self.indexes = {} #type: Dict[str, str]
        self.constraints = {} #type: Dict[str, str]
        self.is_reflected = False #type: bool
        self.type = '' #type: str

    def __str__(self) -> str:
        return self.name

    def set_primary_key(self, key_names:List[str]=[]) -> None:
        key_names = [key.lower() for key in key_names]
        if not self.is_reflected:
            self.reflect()
        self.key_names = key_names
        for col in self.columns:
            col.is_key = (col.name.lower() in key_names)

    def primary_keys(self) -> List[str]:
        return self.key_names

    def field_names(self) -> List[str]:
        return [col.name for col in self.columns]

    def reflect(self) -> None:
        self.columns = [] #type: List[Column]
        inspector = reflection.Inspector.from_engine(self.db.engine)
        columns = inspector.get_columns(self.name, self.schema.name)
        pks = inspector.get_primary_keys(self.name, self.schema.name)
        indexes = inspector.get_indexes(self.name, self.schema.name)
        pk_constraint = inspector.get_pk_constraint(self.name, self.schema.name)
        unique_constraints = inspector.get_unique_constraints(self.name, self.schema.name)
        fks = inspector.get_foreign_keys(self.name, self.schema.name)
        self.key_names = []
        for col in columns:
            col = Column(col['name'], col['type'], self)
            col.is_key = (col.name in pks)
            if col.is_key:
                self.key_names.append(col.name)
            self.columns.append(col)
        for index in indexes:
            self.indexes[index['name']] = index
        if pk_constraint:
            self.constraints[pk_constraint['name']] = pk_constraint
        for unique_constraint in unique_constraints:
            self.constraints[unique_constraint['name']] = unique_constraints
        for fk in fks:
            self.constraints[fk['name']] = fk
        self.is_reflected = True

    def __contains__(self, item: Union[str, 'Column']) -> bool:
        item_name = str(item)
        if isinstance(item, Column):
            item_name = item.name
        for col in self.columns:
            if item_name.lower()[:63] == col.name.lower():
                return True
        for name in self.indexes:
            #name van indexes in posgres mag maar max 63 posities lang zijn
            if item_name[:63] == name:
                return True
        for name in self.constraints:
            # name van constraints in posgres mag maar max 63 posities lang zijn
            if item_name[:63] == name:
                return True
        return False class Schema():
    """Database schema"""
    def __init__(self, name: str, db: 'Database', schema_type: str = '') -> None:
        self.name = name  #type: str
        self.db = db  #type: Database
        self.schema_type = schema_type
        self.tables = {} #type: Dict[str, Table]
        self.views = {} #type: Dict[str, View]
        self.functions = {}  # type: Dict[str, DbFunction]
        self.is_reflected = False
        self.version = 1.0

    def reflect(self):
        self.tables = {}  # type: Dict[str, Table]
        self.views = {}  # type: Dict[str, View]
        self.functions = {}  # type: Dict[str, DbFunction]

        inspector = reflection.Inspector.from_engine(self.db.engine)
        table_names = inspector.get_table_names(self.name)
        view_names = inspector.get_view_names(self.name)
        for view_name in view_names:
            cols = inspector.get_columns(view_name, self.name)
            self.views[view_name] = inspector.get_view_definition(view_name, self.name)

        self.functions = self.reflect_functions()

        tmp_meta = MetaData()
        tmp_meta.reflect(bind=self.db.engine, schema=self.name)
        for sa_tbl in tmp_meta.sorted_tables:
            tbl = Table(sa_tbl.name, self)
            for sa_col in sa_tbl.columns:
                if isinstance(sa_col.type, NullType):
                    col_type = 'custom'
                else:
                    col_type = str(sa_col.type)
                col = Column(sa_col.name, col_type, tbl)
                col.is_key = sa_col.primary_key
                tbl.columns.append(col)
            self.tables[tbl.name] = tbl
            for sa_idx in sa_tbl.indexes:
                tbl.indexes[sa_idx.name] = sa_idx.name
            for sa_constraint in sa_tbl.constraints:
                tbl.constraints[sa_constraint.name] = sa_constraint.name
            for sa_constraint in sa_tbl.foreign_key_constraints:
                tbl.constraints[sa_constraint.name] = sa_constraint.name
        self.is_reflected = True

    def reflect_functions(self) -> Dict[str, 'DbFunction']:
        self.functions = {}
        sql = """SELECT n.nspname AS schema_name
      ,p.proname AS function_name
      ,pg_get_function_arguments(p.oid) AS args
      ,pg_get_functiondef(p.oid) AS func_def
      ,pg_get_function_result(p.oid) AS return_type
FROM   (SELECT oid, * FROM pg_proc p WHERE NOT p.proisagg) p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = '{}'""".format(self.name)
        for row in self.db.engine.execute(sql):
            db_func = DbFunction()
            db_func.name = row['function_name']
            db_func.parse_args(row['args'])
            db_func.return_type = row['return_type']
            db_func.parse_sql_body(row['func_def'])
            db_func.schema = self
            complete_name = db_func.get_complete_name()
            self.functions[complete_name] = db_func
        return self.functions

    def __contains__(self, item: str) -> bool:
        if isinstance(item, str):
            for name in self.tables:
                if item == name:
                    return True
            for name in self.views:
                if item == name:
                    return True
            for name in self.functions:
                if item == name:
                    return True
        return False class HybridSat(Sat):
    """Heeft extra type column als onderdeel van de sleutel"""
    class Types():
        pass
    type = Columns.TextColumn(pk=True)

    @classmethod
    def cls_get_types(cls) -> List[str]:
        types = []
        for key, val in cls.Types.__dict__.items():
            if not key.startswith('__'):
                types.append(val)
        return types class SorTable(Table):
    def __init__(self, name: str, schema: 'Schema' = None) -> None:
        super().__init__(name, schema) class SorQuery(Table):
    def __init__(self, sql: str, schema: 'Schema' = None, name: str = '') -> None:
        super().__init__(name, schema)
        self.sql = sql
        self.main_table = ''
        self.update_query_fk_hub = '' #bevat een update statement om de fk naar de hub te updaten

    def get_main_table(self):
        if not self.main_table:
            #parse. We nemen de eerste tabel in de FROM als de hoofdtabel. Deze hoofdtabel krijgt een fk naar de hub
            sql = self.sql.upper().replace('  ', ' ')
            pos_start = sql.index(' FROM ') + len(' FROM ')
            pos_end = sql.index(' ', pos_start)
            main_table = sql[pos_start:pos_end].strip().lower()
            if '.' in main_table:
                main_table = main_table.split('.')[1]
            self.main_table = main_table
            self.name = main_table
        return self.main_table

    def reflect(self):
        self.columns = []
        cursor = self.db.engine.raw_connection().cursor()

        sql = self.sql + ' OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY'

        result = self.db.engine.execute(sql)
        cursor = result.cursor
        for sa_col in cursor.description:
            col = Column(sa_col.name, sa_col.type_code)
            self.columns.append(col)
        self.is_reflected = True

    def reflect_2(self) -> None:
        self.columns = []  # type: List[Column]
        inspector = reflection.Inspector.from_engine(self.db.engine)
        columns = inspector.cls_get_columns(self.name, self.schema.name)
        pks = inspector.get_primary_keys(self.name, self.schema.name)
        indexes = inspector.get_indexes(self.name, self.schema.name)
        self.key_names = []
        for col in columns:
            # col = Column(col['name'], str(col['type']), self)
            col = Column(col['name'], col['type'], self)
            col.is_key = (col.name in pks)
            if col.is_key:
                self.key_names.append(col.name)
            # if self.key_names:
            #     col.is_key = (col.name in self.key_names)
            self.columns.append(col)
        for index in indexes:
            self.indexes[index['name']] = index
        self.is_reflected = True

        #     self.name = name
        #
        # def __str__(self):
        #     return self.name class ConstantValue:
    def __init__(self, value, type: str = 'text', alias: str = '') -> None:
        self.value = value
        self.type = type

    def __str__(self) -> str:
        if isinstance(self.value, str) and self.type == 'text':
            if "'" in self.value:
                return "{}".format(self.value)
            else:
                return "'{}'".format(self.value)
        else:
            return "{}".format(self.value) class SorToEntityMapping(BaseTableMapping):
    def __init__(self, source: str, target: HubEntity, sor: Schema = None, filter: str = '', type: str = '') -> None:
        # target.cls_init_sats()

        # if not sor:
        #     sor_name = source.split('.')[0]
        #     from pyelt.pipeline import Pipeline
        #     p = Pipeline()
        #     db = p.dwh
        #     sor = db.get_sor_schema(sor_name)
        if isinstance(source, str) and 'SELECT' in source.upper() and ' ' in source:
            source = SorQuery(source, sor)
        elif isinstance(source, str):
            source = SorTable(source, sor)
        if not sor and source is SorTable:
            raise Exception('Je moet een sor laag opgeven bij aanmaken van de SorToEntityMapping')
        # target.init_cls()
        super().__init__(source, target, filter)
        self.entity = target
        self.type = type
        if not type and not target.__base__ == HubEntity:
            self.type = target.__subtype__
        self.sat_mappings = {} #type: Dict[str, SorSatMapping]
        self.bk_mapping = None #type: str
        self.key_mappings = []  # type: str

    def map_field(self, source: Union[str, 'ConstantValue', list, dict], target: 'Column' = None, transform_func: 'FieldTransformation'=None, ref: str = '', type: str = '') -> None:
        if not source:
            return
        if not target:
            return
        if target.name == 'bk':
            return self.map_bk(source)
        sat = target.get_table()

        sat_name = sat.cls_get_name()
        sat_mapping = SorSatMapping(self.source, sat, type)
        if not sat_mapping.name in self.sat_mappings:
            self.sat_mappings[sat_mapping.name] = sat_mapping
        else:
            sat_mapping = self.sat_mappings[sat_mapping.name]
        sat_mapping.map_field(source, target, transform_func, ref)
        sat_mapping.type = type
        # field_mapping = FieldMapping(source, target, transform_func, ref = ref)
        # self.field_mappings.append(field_mapping)

    def map_bk(self, source: Union[str,List[str]]) -> None:
        if isinstance(source, list):
            source = " || '.' ||".join(source)
        self.bk_mapping = source

    def map_keys_in_sat(self, source: Union[str, List[str]], target: Union['Column', List['Column']]):
        """In plaats van een bk_mapping kun je ook mappen op een veld combinatie in een reeds bestaande sat.

        Voorwaarde is dat er reeds een gevulde hub met een sat is, die vanuit een andere bron is gevuld.

        Denk hierbij ook aan een zogenaamde keys_sat: een sat met alleen sleutel info vanuit de verschillende bronnen"""
        if isinstance(source, list):
            for index in range(len(source)):
                self.key_mappings.append(KeyMapping(source[index], target[index]))



    def map_fixed_value(self, fixed_value: str, target_field: 'Column' = None) -> None:
        source_field = ConstantValue("'{}'".format(fixed_value))
        self.map_field(source_field, target_field) class SorToLinkMapping(BaseTableMapping):
    auto_generated_sor_fk = 'auto'

    def __init__(self, source: Union[str, 'Table', 'View'], target: 'LinkEntity', sor: Schema, filter: str = '', type: str = '') -> None:
        if isinstance(source, str):
            source = SorTable(source, sor)
        super().__init__(source, target, filter)
        self.sor_table_name = str(source)
        if isinstance(type, str):
            self.type = type
        elif type == HubEntity:
            self.type = type.__subtype__
        self.target = target
        # self.target.cls_init()
        self.hubs = {} #type: Dict[str, Table]
        self.sat_mappings = {}  # type: Dict[str, Sat]

    def get_fk_name(self, entity_cls):
        for prop_name, entity in self.target.__dict__.items():
            if entity == entity_cls or entity == entity_cls.__base__:
                fk = """fk_{}""".format(entity.cls_get_hub_name())
                if prop_name.lower() != entity.__name__.lower():
                    fk = """fk_{}_{}""".format(prop_name.lower(), entity.cls_get_hub_name())
                return fk
        raise Exception('Entity niet gevonden in Link')

    def map_bk(self, source_bk, target_link_ref: Union[LinkReference, DynamicLinkReference], join: str = '', type: Union[str, 'HubEntity'] = ''):
        if not (isinstance(target_link_ref, LinkReference) or isinstance(target_link_ref, DynamicLinkReference)):
            raise PyeltException('Link moet verwijzen naar veld van type LinkReference of DynamicLinkReference')

        if isinstance(target_link_ref, LinkReference):
            hub_name = target_link_ref.hub.__dbname__
            hub = target_link_ref.hub

        elif isinstance(target_link_ref, DynamicLinkReference):
            # type verwijst hier naar de entity die dynamisch gelinkt wordt in de fk
            if not type or isinstance(type, str):
                raise PyeltException('Bij DynamicLinkReference moet type verwijzen naar een afgeleide van HubEntity')
            hub_name = type.cls_get_hub_name()
            hub = type.cls_get_hub()

            self.type = type.__name__.lower()
        hub_alias = target_link_ref.fk.replace('fk_', '')

        source_field = '_id' #type: str
        source_col = Column(source_field, 'integer', tbl=hub) #type: Column
        target_fk = target_link_ref.fk
        target_col = Column(target_fk, 'integer', tbl=hub)
        field_mapping = LinkFieldMapping(source_col, target_col)
        field_mapping.join = join
        field_mapping.source_alias = hub_alias
        if isinstance(source_bk, list):
            source_bk = "|| '.' || ".join(source_bk)
        field_mapping.bk = source_bk
        self.hubs[target_fk] = hub
        self.field_mappings.append(field_mapping)

    def map_sor_fk(self, source_fk: str = '', target_link_ref: Union[LinkReference, DynamicLinkReference] = None, join: str = '', type: Union[str, 'HubEntity'] = ''):
        if not (isinstance(target_link_ref, LinkReference) or isinstance(target_link_ref, DynamicLinkReference)):
            raise PyeltException('Link moet verwijzen naar veld van type LinkReference of DynamicLinkReference')
        if isinstance(target_link_ref, LinkReference):
            hub_name = target_link_ref.hub.__dbname__
            hub = target_link_ref.hub
            # if not type and target_link_ref.sub_entity_type != hub_name.replace('_hub', ''):
            #     type = target_link_ref.sub_entity_type
            # if not type and not target_link_ref.hub.__base__ == HubEntity:
            #     type = target_link_ref.hub.__dbname__.replace('_hub', '')

            if not source_fk or source_fk == SorToLinkMapping.auto_generated_sor_fk:
                # source_fk = target_link_ref.fk
                source_fk = 'fk_{}{}'.format(target_link_ref.sub_entity_type, hub_name)
        elif isinstance(target_link_ref, DynamicLinkReference):
            # type verwijst hier naar de entity die dynamisch gelinkt wordt in de fk
            if not type or isinstance(type, str):
                raise PyeltException('Bij DynamicLinkReference moet type verwijzen naar een afgeleide van HubEntity')
            hub = type.cls_get_hub()
            self.type = type.__name__.lower()
        source_col = Column(source_fk, 'integer', tbl=self.sor_table_name)

        target_fk = target_link_ref.fk
        target_col = Column(target_fk, 'integer', tbl=hub)
        field_mapping = LinkFieldMapping(source_col, target_col)
        field_mapping.join = join
        self.hubs[target_fk] = hub
        self.field_mappings.append(field_mapping)

    def map_entity(self, target_link_ref: Union[LinkReference, DynamicLinkReference] = None, bk: Union[List[str], str] = '', join: str = '', type: Union[str, 'HubEntity'] = ''):
        if isinstance(target_link_ref, DynamicLinkReference) and not bk:
            hub_name = type.cls_get_hub_name()
            sor_fk = 'fk_{}'.format(hub_name)
            self.map_sor_fk(sor_fk, target_link_ref, join, type)
        elif bk:
            self.map_bk(bk, target_link_ref, join, type)
        else:
            self.map_sor_fk('', target_link_ref, join, type)

    # def map_dynamic_entity(self, source_entity: HubEntity, target_link_ref: DynamicLinkReference,  type: str = '', bk: str = '', join: str = ''):
    #     if bk:
    #         self.map_bk(bk, target_link_ref, join, type)
    #     else:
    #         hub_name = target_link_ref.entity_cls.get_hub_name()
    #         sor_fk = 'fk_{}{}'.format(type, hub_name)
    #         type = source_entity
    #         self.map_sor_fk(sor_fk, target_link_ref, join, type)

    def map_field(self, source: Union[str, 'ConstantValue'], target: 'Column' = None, transform_func: 'FieldTransformation' = None, ref: str = '', type: str = '') -> None:
        if not target:
            return
        sat = target.get_table()
        sat_name = sat.cls_get_name()
        sat_mapping = SorSatMapping(self.source, sat, type)
        if not sat_mapping.name in self.sat_mappings:
            self.sat_mappings[sat_mapping.name] = sat_mapping
        else:
            sat_mapping = self.sat_mappings[sat_mapping.name]
        sat_mapping.map_field(source, target, transform_func, ref)
        sat_mapping.type = type

    def map_entity_old(self, entity_cls: HubEntity,  bk: str = '', join: str = '', type: str = ''):
        # is_hybrid = False
        hub_name = entity_cls.cls_get_hub_name()
        hub = entity_cls.cls_get_hub()

        if bk:
            source_field = '_id' #type: str
            source_col = Column(source_field, 'integer', tbl=hub) #type: Column
        # elif is_hybrid:
        #     source_field = 'fk_{}{}'.format(type, hub_name)
        #     source_col = Column(source_field, 'integer', tbl=self.sor_table)
        else:
            # source_field = 'fk_' + hub_name
            source_field = 'fk_{}{}'.format(type, hub_name)
            source_col = Column(source_field, 'integer', tbl=self.sor_table_name)

        # target_field = '_fk_' + hub_name #type: str
        target_field = 'fk_{}'.format(hub_name)
        target_field = self.get_fk_name(entity_cls)
        # if target_field in self.hubs:
        #     target_field = '_fk_parent_' + self.type + hub_name
        target_col = Column(target_field, 'integer', tbl=hub)
        field_mapping = LinkFieldMapping(source_col, target_col)
        field_mapping.bk = bk
        field_mapping.join = join
        self.hubs[target_field] = hub
        self.field_mappings.append(field_mapping)

    # def map_source(self, source, target: 'HubEntity'):
    #     source_col = Column(source, 'integer', tbl=self.sor_table)
    #     target_field = """_fk_source_{}""".format(target.get_hub_name())
    #     target_col = Column(target_field, 'integer', tbl=target.hub)
    #     field_mapping = LinkFieldMapping(source_col, target_col)
    #     self.hubs[target_field] = target.hub
    #     self.field_mappings.append(field_mapping)
    #
    # def map_target(self, source, target: 'HubEntity'):
    #     source_col = Column(source, 'integer', tbl=self.sor_table)
    #     target_field = """_fk_target_{}""".format(target.get_hub_name())
    #     target_col = Column(target_field, 'integer', tbl=target.hub)
    #     field_mapping = LinkFieldMapping(source_col, target_col)
    #     # field_mapping.bk = bk
    #     # field_mapping.join = join
    #     self.hubs[target_field] = target.hub
    #     self.field_mappings.append(field_mapping)
    #
    #     # source_col = Column(source, 'integer', tbl=self.sor_table)
    #     # target_field = target.__qualname__
    #     # mro = target.__mro__
    #     # currentframe = inspect.currentframe()
    #     # fs = inspect.getouterframes(currentframe)
    #     # target_field = self.get_fk_name(target)
    #     # # if target_field in self.hubs:
    #     # #     target_field = '_fk_parent_' + self.type + hub_name
    #     # target_col = Column(target_field, 'integer', tbl=target.hub)
    #     # field_mapping = LinkFieldMapping(source_col, target_col)
    #     # # field_mapping.bk = bk
    #     # # field_mapping.join = join
    #     # self.hubs[target_field] = target.hub
    #     # self.field_mappings.append(field_mapping) class SorToValueSetMapping(BaseTableMapping):
    def __init__(self, source: str, target: DvValueset, sor: Schema = None ) -> None:
        if isinstance(source, str) and 'SELECT' in source.upper() and ' ' in source:
            source = SorQuery(source, sor)
        elif isinstance(source, str):
            source = SorTable(source, sor)
        super().__init__(source, target)
        self.sor_table = source
        self.valueset = target

    def get_source_fields(self, alias: str = '')-> str:
        if not alias: alias = 'hstg'
        fields = ''  # type: List[str]
        target_json_dict = OrderedDict()
        for field_map in self.field_mappings:
            if field_map.target.type == 'jsonb':
                if field_map.target.name not in target_json_dict:
                    target_json_dict[field_map.target.name] = []
                target_json_dict[field_map.target.name].append(field_map)
            elif isinstance(field_map.source, ConstantValue):
                field = '{},'.format(field_map.source)
            elif isinstance(field_map.source, FieldTransformation):
                # field = '{},'.format(field_map.source.get_sql(alias))
                field = '{},'.format(field_map.source.get_sql())
            elif isinstance(field_map.source, DbFunction):
                # field = '{},'.format(field_map.source.get_sql(alias))
                field = '{},'.format(field_map.source.get_sql(alias))
            elif not alias:
                field = '{},'.format(field_map.source)
            else:
                field = '{}.{},'.format(alias, field_map.source)
            if '[]' in field_map.target.type:
                #array velden
                #eerst komma eraf halen
                field = field[:-1]
                field = "'{" + field + "}',"
            # voorkom eventuele dubbele veldnamen bij hybrid sats
            if field not in fields:
                fields += field

        for name, value in target_json_dict.items():
            sql_snippet = """json_build_object("""
            for field_map in value:
                sql_snippet += """'{0}', {0}, """.format(field_map.source.name)
            sql_snippet = sql_snippet[:-2] + ')::jsonb,'
            fields += sql_snippet
        fields = fields[:-1]
        return fields

    def get_target_fields(self) -> str:
        fields = []
        #json velden willen we helemaal als laatste omdat ze in get_source_fields ook als laatste komen te staan
        json_fields = []
        for field_map in self.field_mappings:
            field = '{}'.format(field_map.target)
            if field_map.target.type == 'jsonb':
                if field not in json_fields:
                    json_fields.append(field)
            elif field not in fields:
                fields.append(field)
        return_sql = ','.join(fields)
        if json_fields:
            return_sql += "," + ','.join(json_fields)
        return return_sql class DvValidation(Validation):
    def set_condition(self, field: Union[Column, str], operator: str = '=', sql_value="''"):
        if isinstance(field, str):
            self.sql_condition = field
        elif isinstance(field, Condition):
            self.sql_condition = field.name
            self.table = field.table
        else:
            params = {'field': field.name, 'operator': operator, 'sql_value': sql_value}
            self.table = field.table
            if field.name == 'bk':
                pass
            self.sql_condition = "{field} {operator} {sql_value}".format(**params) class SorValidation(Validation):
    def __init__(self, tbl: 'Table' = None, schema: 'Schema' = None) -> None:
        super().__init__(tbl, schema)
        self.check_for_duplicate_keys = []


    def set_check_for_duplicate_keys(self, keys):
        if isinstance(keys, str):
            self.check_for_duplicate_keys = [keys]
        else:
            self.check_for_duplicate_keys = keys

    def get_keys(self):
        keys = ','.join(self.check_for_duplicate_keys)
        return keys class SourceTable(Table):
    def __init__(self, name, schema, db, alias=''):
        super().__init__(name, schema, db)
        self.alias = alias

    def to_csv(self, path='', md5_only=False, filter='', ignore_fields=[], debug=False):
        path = self.db.get_or_create_datatransfer_path()
        if not self.alias:
            self.alias = self.name
        file_name = '{}/{}.csv'.format(path, self.alias)
        if md5_only:
            file_name = '{}/{}_hash.csv'.format(path, self.name)

        head = [col.name for col in self.columns if col.name not in ignore_fields]
        if md5_only:
            head = self.key_names + ['_hash']
        with open(file_name, 'w', newline='', encoding='utf8') as fp:
            csv_writer = csv.writer(fp, delimiter=';')
            csv_writer.writerow(head)
            data = self.load(md5_only=md5_only, filter=filter, ignore_fields=ignore_fields, debug=debug)
            csv_writer.writerows(data)
        return file_name

    def load(self, md5_only=False, filter='', ignore_fields=[], debug=False):
        rows = []
        field_names = [col.name for col in self.columns if col.name not in ignore_fields]
        field_names_str = ','.join(field_names)

        if md5_only:
            key_sql = ','.join(self.primary_keys())
            concat_fields_sql = '||'.join(field_names)
            if self.db.driver == DBDrivers.ORACLE:
                # sql = """SELECT {2}, RAWTOHEX(UTL_RAW.CAST_TO_RAW(sys.dbms_obfuscation_toolkit.md5(INPUT_STRING => {3}))) as hash
                # FROM {0}.{1} """.format(self.schema.name, self.name, key_sql, concat_fields_sql)
                field_names_str = """{0}, RAWTOHEX(UTL_RAW.CAST_TO_RAW(sys.dbms_obfuscation_toolkit.md5(INPUT_STRING => {1}))) as hash""".format(key_sql, concat_fields_sql)
            elif self.db.driver == DBDrivers.POSTGRESS:
                concat_fields_sql = ''
                for field in field_names:
                    concat_fields_sql += "coalesce({}::text, '')".format(field) + '||'
                concat_fields_sql = concat_fields_sql[:-2]
                # sql = """SELECT {2}, md5({3}) as hash
                # FROM {0}.{1} """.format(self.schema.name, self.name, key_sql, concat_fields_sql)
                # elif self.db.driver == DBDrivers.SQLSERVER:
                #     concat_fields_sql = ','.join(self.field_names())
                #     sql = """SELECT {2}, CONVERT(NVARCHAR(32), HashBytes('MD5', CONCAT({3})), 2) as hash
                #     FROM {0}.{1} """.format(self.schema.name, self.name, key_sql, concat_fields_sql)
                field_names_str = """{0}, MD5({1}) as hash""".format(key_sql, concat_fields_sql)

            elif self.db.driver == DBDrivers.SQLSERVER:
                concat_fields_sql = ''
                for field in self.field_names():
                    concat_fields_sql += "rtrim(coalesce(convert(varchar(max),  {}".format(field) + "), ''))+"
                concat_fields_sql = concat_fields_sql[:-1]
                # sql = """SELECT {2}, CONVERT(NVARCHAR(32), HashBytes('MD5', {3} ), 2) as hash
                #     FROM {0}.{1} """.format(self.schema.name, self.name, key_sql, concat_fields_sql)
                field_names_str = """{0}, CONVERT(NVARCHAR(32), HashBytes('MD5', {1}), 2) as hash""".format(key_sql, concat_fields_sql)

        if isinstance(self, SourceQuery):
            sql = """SELECT {} FROM ({}) AS {}""".format(field_names_str, self.sql, self.name)
            if self.db.driver == DBDrivers.ORACLE:
                sql = """SELECT {} FROM ({}) {}""".format(field_names_str, self.sql, self.name)
        else:
            sql = """SELECT {} FROM {}.{}""".format(field_names_str, self.schema.name, self.name)

        if filter:
            filter = filter.replace('WHERE', '')
            sql += ' WHERE ' + filter
        # print(sql)
        if debug:
            if self.db.driver == DBDrivers.SQLSERVER:
                sql = sql.replace('SELECT', 'SELECT TOP 100')
            else:
                sql += " OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY"

        result = self.db.engine.execute(sql)
        for row in result:
            rows.append(row)
        return rows class SourceQuery(SourceTable):
    def __init__(self, db, sql, name):
        super().__init__(name, '', db)
        self.alias = name
        self.db = db
        if sql.strip().endswith(';'):
            sql = sql.strip()[:-1]
        self.sql = sql
        # self.name = 'query'
        self.columns = []


    def reflect(self):
        self.columns = []
        cursor = self.db.engine.raw_connection().cursor()
        if self.db.driver == DBDrivers.ORACLE:
            sql = self.sql + ' OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY'
        elif self.db.driver == DBDrivers.SQLSERVER:
            sql = self.sql_to_top_select(self.sql, 1)
        elif self.db.driver == DBDrivers.POSTGRESS:
            sql = self.sql + ' OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY'
        # sql = self.sql + ' AND 1=0'
        # cursor.execute(sql)

        result = self.db.engine.execute(sql)
        cursor = result.cursor
        for sa_col in cursor.description:
            if self.db.driver == DBDrivers.ORACLE:
                col = Column(sa_col[0], oracle_type_to_postgres_type(str(sa_col[1])))
            elif self.db.driver == DBDrivers.SQLSERVER:
                col = Column(sa_col[0], sqlserver_type_to_postgres_type(str(sa_col[1])))
            else:
                col = Column(sa_col.name, sa_col.type_code)
            self.columns.append(col)
        self.is_reflected = True

    def load2(self, md5_only=False, filter='', ignore_fields=[], debug=False):
        rows = []
        sql = self.sql

        if filter:
            if 'where' in sql.lower():
                sql += ' AND ' + filter
            else:
                sql += ' WHERE ' + filter

        # if 'debug' in pyelt_config and pyelt_config['debug']:
        if debug:
            if self.db.driver == DBDrivers.SQLSERVER:
                sql = self.sql_to_top_select(self.sql, 100)
            else:
                sql += " OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY"

        result = self.db.engine.execute(sql)
        for row in result:
            rows.append(row)
        return rows

    def sql_to_top_select(self, sql, top=1):
        "Voor SQL SERVER"
        # eerst mooie string van maken
        sql = sql.replace('\t', ' ')
        sql = sql.replace('\n', ' ')
        sql = sql.replace('\r', ' ')
        sql = sql.replace('  ', ' ')
        sql = sql.strip().lower()

        if sql.startswith('select distinct top') or sql.startswith('select top'):
            # als er al aan het begin een top instaat: niets doen. Wees je bewust van subqueries
            pass
        elif sql.startswith('select distinct'):
            # allern begin van sql vervangen, wees je bewust van subqueries
            sql = 'select distinct top ' + str(top) + sql[15:]
        elif sql.startswith('select'):
            sql = 'select top ' + str(top) + sql[6:]

        return sql class File():
    def __init__(self, file_name):
        self.name = file_name[file_name.rfind('/') + 1:]
        self.file_name = file_name
        self.key_names = []
        self.columns = []
        self.is_reflected = False
        self.encoding = 'utf8'

    def __repr__(self):
        return self.name

    def set_primary_key(self, key_names=[]):
        key_names = [key.lower() for key in key_names]
        if not self.is_reflected:
            self.reflect()
        self.key_names = key_names
        for col in self.columns:
            col.is_key = (col.name in key_names)

    def primary_keys(self):
        return self.key_names  # [col.name for col in self.columns if col.is_key]

    def field_names(self):
        return [col.name for col in self.columns]

    def reflect(self):
        pass

    def get_header(self):
        if not self.is_reflected:
            self.reflect()
        return self.columns class CsvFile(File):
    def __init__(self, file_name, **kwargs):
        super().__init__(file_name )
        self.quote = '"'
        self.delimiter = ';'
        self.file_kwargs = {}
        self.csv_kwargs = {}
        self.csv_kwargs['delimiter'] = self.delimiter
        for k,v in kwargs.items():
            if k == 'encoding':
                self.file_kwargs[k] = v
                if v == 'utf-8-sig':
                    v = 'utf8'
                self.encoding = v
            elif k == 'delimiter':
                self.csv_kwargs[k] = v
                self.delimiter = v
            elif k == 'quote':
                self.quote = v
        self.kwargs = kwargs

    def reflect(self):
        # lees eerste regel
        with open(self.file_name, 'r', **self.file_kwargs) as csvfile:
            reader = csv.reader(csvfile, **self.csv_kwargs)
            column_names = next(reader, None)
            for name in column_names:
                name = Column.clear_name(name)
                col = Column(name.lower())
                self.columns.append(col)
        self.is_reflected = True class Logger:
    def __init__(self):
        self.logger = None #type: logging.logger
        self.start_time = datetime.now()  # type: datetime.datetime
        self.last_start_time = datetime.now()  # type: datetime.datetime
        self.errors = [] #type: List[str]
        self.to_console = True  # type: bool
        self.filename = ''  # type: str

    @staticmethod
    def create_logger(logger_type: str = LoggerTypes.MAIN, runid: float = 0, configs={}, to_console: bool = True, filename_args = '') -> 'Logger':
        logger = logging.getLogger(logger_type)
        # create file
        path, filename = Logger.__create_path_and_filename_by_type(logger_type, runid, configs, filename_args)
        if len(logger.handlers) == 0:
            # create formatter
            if logger_type == LoggerTypes.MAIN:
                formatter = logging.Formatter('%(asctime)s - %(message)s')
            else:
                formatter = logging.Formatter('%(message)s')

            fileHandler = logging.FileHandler(path + filename)
            fileHandler.setFormatter(formatter)
            logger.addHandler(fileHandler)
            # if to_console:
            #     consoleHandler = logging.StreamHandler()
            #     consoleHandler.setFormatter(formatter)
            #     logger.addHandler(consoleHandler)
            logger.setLevel(logging.INFO)
            # logger.errors = []
        log_obj = Logger()
        log_obj.logger = logger
        log_obj.start_time = datetime.now()
        log_obj.last_start_time = datetime.now()
        log_obj.errors = []  # logger.errors
        log_obj.to_console = to_console
        if 'log_to_console' in configs:
            log_obj.to_console = configs['log_to_console']
        log_obj.filename = filename
        log_obj.config = configs
        return log_obj

    @staticmethod
    def __create_path_and_filename_by_type(logger_type: str = LoggerTypes.MAIN, runid=0.00, configs={}, filename_args = ''):
        import os
        from main import get_root_path
        path = get_root_path() + configs['log_path']
        if logger_type == LoggerTypes.SQL and 'sql_log_path' in configs:
            path = get_root_path() + configs['sql_log_path']
        if logger_type == LoggerTypes.DDL and 'ddl_log_path' in configs:
            path = get_root_path() + configs['ddl_log_path']
        if not os.path.exists(path):
            os.makedirs(path)
        filename = 'LOG {0:%Y-%m-%d %H.%M.%S} {1}.log'.format(datetime.now(), logger_type)
        if runid:
            filename = 'LOG {1:%Y-%m-%d %H.%M.%S} RUN{0:07.2f} {2}.log'.format(runid, datetime.now(), logger_type)
        if filename_args:
            filename = filename.replace('.log', '_' + filename_args + '.log')

        return path, filename

    def log_simple(self, msg: str) -> None:
        self.logger.info(self.strip_formatting_tags(msg))
        if self.to_console:
            Logger.pprint(msg)

    def log(self, descr: str, last_start_time: datetime = None, rowcount: int = -1, newline: bool = False, indent_level=0) -> None:
        if not last_start_time:
            last_start_time = self.last_start_time
        newline_str = '' #type: str
        if newline:
            newline_str = '\r\n'

        end_time = datetime.now()
        global_time_descr = ''
        if last_start_time:
            global_elapsed_time = end_time - self.start_time
            elapsed_time_since_last_log = end_time - last_start_time
            global_time_descr = self.time_descr(global_elapsed_time)

        if descr:
            if indent_level >= 4:
                descr = '-' + descr
            for i in range(indent_level):
                descr = ' ' + descr
            for i in range(len(self.strip_formatting_tags(descr)), 60):
                # uitvullen tot 50 positities
                descr += ' '
                # divmod(elapsedTime.total_seconds(), 60)
        if descr and self.logger:
            if global_time_descr:
                if rowcount >= 0:
                    descr = '{0} <lightgray>executed on {1} rows in {2} ({3} since start) {4}</>'.format(descr, rowcount, self.time_descr(elapsed_time_since_last_log), global_time_descr, newline_str)
                else:
                    descr = '{0} <lightgray>executed in {1} ({2} since start) {3}</>'.format(descr, self.time_descr(elapsed_time_since_last_log), global_time_descr, newline_str)
            else:
                descr = '{}{}'.format(descr, newline_str)
        self.logger.info(self.strip_formatting_tags(descr))
        if self.to_console:
            Logger.pprint(descr)
        self.last_start_time = end_time

    def log_error(self, log_msg, sql='', err_msg= '', ex = None):
        if ex:
            for arg in ex.args:
                err_msg += str(arg)
        while err_msg.endswith('\n'):
            err_msg = err_msg[:-1]
        msg = """
<red>==========================================================================
ERROR tijdens : {}

SQL: {}

ERROR: {}
==========================================================================</>""".format(log_msg, sql, err_msg)
        if 'on_errors' in self.config and self.config['on_errors'] == 'throw':
            raise Exception(msg)
        else:

            self.logger.error(self.strip_formatting_tags(msg))
            self.errors.append(msg)
            if self.to_console:
                Logger.pprint(msg)
                # even wachten opdat de log-msg niet verward raakt door de foutmelding
                sleep(0.1)


    def time_descr(self, elapsed_time) -> str:
        sec = elapsed_time.total_seconds()
        time_descr = '{0:.3f} seconds'.format(sec)

        if sec > 60:
            import math
            min = math.floor(sec / 60)
            rest_sec = sec - min * 60
            time_descr = '{0} minutes and {1:.3f} seconds'.format(min, rest_sec)
        return time_descr

    @staticmethod
    def pprint(value):
        """Maakt het mogelijk in kleur te printen in de console
        Gebruik html-achtige tags zoals: <red>rood</>
        """
        if isinstance(value, list):
            for val in value:
                Logger.pprint(val)
        elif isinstance(value, str):
            value = value.replace('<br>', '\r\n')
            value = value.replace('<\br>', '\r\n')
            value = value.replace('<b>', ConsoleColors.BOLD)
            value = value.replace('<u>', ConsoleColors.UNDERLINE)
            value = value.replace('<red>', ConsoleColors.RED)
            value = value.replace('<gray>', ConsoleColors.GRAY)
            value = value.replace('<lightgray>', ConsoleColors.LIGHTGRAY)
            value = value.replace('<yellow>', ConsoleColors.YELLOW)
            value = value.replace('<blue>', ConsoleColors.BLUE)
            value = value.replace('<green>', ConsoleColors.GREEN)
            value = value.replace('<purle>', ConsoleColors.PURPLE)
            value = value.replace('<cyan>', ConsoleColors.CYAN)
            value = value.replace('<darkcyan>', ConsoleColors.DARKCYAN)
            value = value.replace('<filledred>', ConsoleColors.FILLED_RED)
            value = value.replace('</>', ConsoleColors.END)
            print(value)
        else:
            print(value)

    def strip_formatting_tags(self, value):
        value = value.replace('<br>', '\r\n')
        value = value.replace('<\br>', '\r\n')
        value = value.replace('<b>', '')
        value = value.replace('<u>', '')
        value = value.replace('<red>', '')
        value = value.replace('<gray>', '')
        value = value.replace('<lightgray>', '')
        value = value.replace('<yellow>', '')
        value = value.replace('<blue>', '')
        value = value.replace('<green>', '')
        value = value.replace('<purle>', '')
        value = value.replace('<cyan>', '')
        value = value.replace('<darkcyan>', '')
        value = value.replace('<filledred>', '')
        value = value.replace('</>', '')
        value = value.replace('\ufeff', '')
        return value

    def test_show_all_colors(self):
        for i in range(120):
            s = '\033[{}mDIT IS FORMAT. \033[0mDit niet.'.format(i)
            print(i, s) class LoggerTypes:
    MAIN = 'MAIN'
    SQL = 'SQL'
    DDL = 'DDL'
    ERROR = 'ERROR'
    PULL_DATA = 'PULL_DATA' class BaseProcess():
    def __init__(self, owner: 'Pipe'):
        import pyelt.pipeline
        if isinstance(owner, pyelt.pipeline.Pipe):
            self.pipe = owner
            self.pipeline = self.pipe.pipeline
        elif isinstance(owner, pyelt.pipeline.Pipeline):
            self.pipeline = owner
        self.dwh = self.pipeline.dwh
        self.runid = self.pipeline.runid
        self.logger = self.pipeline.logger
        self.sql_logger = self.pipeline.sql_logger

    def execute(self, sql: str, log_message: str='') -> None:
        self.sql_logger.log_simple(sql + '\r\n')
        try:
            rowcount = self.dwh.execute(sql, log_message)
            self.logger.log(log_message, rowcount=rowcount, indent_level=5)
        except Exception as err:
            if 'on_errors' in self.dwh.config and self.dwh.config['on_errors'] == 'throw':
                raise Exception(err, sql, log_message)
            else:
                self.logger.log_error(log_message, sql, err.args[0])

    def execute_read(self, sql: str, log_message: str='') -> List[List[Any]]:
        self.sql_logger.log_simple(sql + '\r\n')
        result = []
        try:
            result = self.dwh.execute_read(sql, log_message)

            self.logger.log(log_message, indent_level=5)
        except Exception as err:
            self.logger.log_error(log_message, sql, err.args[0])
            raise Exception(err)
            # if 'on_errors' in self.dwh.pyelt_config and self.dwh.pyelt_config['on_errors'] == 'throw':
            #     raise Exception(err)
            # else:
            #     print(err.args)
            #     print(sql)
            #     raise Exception(err)
        finally:
            return result

    def execute_without_commit(self, sql: str, log_message: str=''):
        self.sql_logger.log_simple(sql + '\r\n')

        try:
            self.dwh.execute_without_commit(sql, log_message)

            self.logger.log(log_message, indent_level=5)
        except Exception as err:
            self.logger.log_error(log_message, sql, err.args[0])
            raise Exception(err)

    def _get_fixed_params(self) -> Dict[str, Any]:
        params = {}
        params['runid'] = self.runid
        return params
from typing import Any, Dict
from pyelt.datalayers.database import Table, Schema
from pyelt.datalayers.dv import HybridSat
from pyelt.datalayers.sor import SorTable, SorQuery
from pyelt.mappings.base import ConstantValue
from pyelt.mappings.sor_to_dv_mappings import SorToEntityMapping, SorToLinkMapping, SorToValueSetMapping
from pyelt.mappings.validations import DvValidation, SorValidation
from pyelt.sources.databases import SourceTable, SourceQuery
from pyelt.sources.files import File, CsvFile
from pyelt.helpers.pyelt_logging import Logger, LoggerTypes
from pyelt.process.base import BaseProcess
                        where_sql += 'dv.{}.bk = {} AND '.format(join_tbl, field_mapping.bk)
                # from_sql = from_sql[:-1]
                where_sql = where_sql[:-5]

                params['from'] = from_sql
                params['where'] = where_sql

                sql = """UPDATE {sor}.{sor_table} hstg SET fk_{type}{link} = link._id
FROM {dv_schema}.{link} link {from}
WHERE {fks_compare} AND {where}
AND hstg._valid AND {filter};""".format(
                    **params)

                self.execute(sql, 'update fk_link in sor table')

            for sat_mappings in mappings.sat_mappings.values():
                self.__sor_to_sat(params, sat_mappings)

            #deletes
            link_entity = mappings.target #type: LinkEntity
            if link_entity.cls_has_record_status_sat():
                params['record_satus_sat'] = link_entity.cls_get_record_status_sat().__dbname__
                sql = """INSERT INTO {dv_schema}.{record_satus_sat} (_id, _runid, _source_system, _insert_date, deleted)
                            SELECT _id, {runid}, '{source_system}', now(), now()
                            FROM {dv_schema}.{link}
WHERE _id NOT IN (SELECT _id FROM {dv_schema}.{record_satus_sat}) AND
      ({target_fks}) IN (SELECT {source_fks}
                            FROM sor_test_system.traject_hstage hstg
                              {join}
  WHERE hstg._deleted_runid = {runid})""".format(**params)
                self.execute(sql, 'update deleted records')




            self.logger.log('  FINISH {}'.format(mappings))
        except Exception as ex:
            self.logger.log_error(mappings.name, err_msg=ex.args[0])

    def __get_link_source_fks(self, mappings: 'SorToLinkMapping'):
        fks = ''
        for field_mapping in mappings.field_mappings:
            if field_mapping.source.table == mappings.sor_table_name:
                fks += '{}, '.format(field_mapping.source)
                # fks += 'hstg.{}, '.format(field_mapping.source)
            else:
                # fks += '{}.{}, '.format(field_mapping.source.table, field_mapping.source)
                fks += '{}.{}, '.format(field_mapping.source_alias, field_mapping.source)
        fks = fks[:-2]
        return fks

    def __get_link_source_fks_is_not_null(self, mappings: 'SorToLinkMapping'):
        fks = ''
        for field_mapping in mappings.field_mappings:
            if field_mapping.source.table == mappings.sor_table_name:
                fks += '{} IS NOT NULL AND '.format(field_mapping.source)
            else:
                fks += '{}.{} IS NOT NULL AND '.format(field_mapping.source_alias, field_mapping.source)
        fks = fks[:-5]
        return fks

    def __get_link_target_fks(self, mappings: 'SorToLinkMapping'):
        fks = ''
        for field_mapping in mappings.field_mappings:
            fks += '{}, '.format(field_mapping.target)
        fks = fks[:-2]
        return fks

    def __get_link_fks_compare(self, mappings: 'SorToLinkMapping', source_alias='', target_alias=''):
        if not source_alias: source_alias = 'hstg'
        if not target_alias: target_alias = 'link'
        fks_compare = ''
        for field_mapping in mappings.field_mappings:
            if isinstance(field_mapping.source, ConstantValue):
                fks_compare += 'COALESCE({0}, 0) = COALESCE({1}.{2}, 0) AND '.format(field_mapping.source, target_alias, field_mapping.target)
            elif field_mapping.is_view_mapping:
                fks_compare += 'COALESCE({0}.{1}, 0) = COALESCE({2}.{3}, 0) AND '.format(field_mapping.source.table, field_mapping.source, target_alias, field_mapping.target)
            else:
                source_alias = self.__get_link_alias_of_source_tbl(field_mapping, fks_compare)
                fks_compare += 'COALESCE({0}.{2}, 0) = COALESCE({1}.{3}, 0) AND '.format(source_alias, target_alias, field_mapping.source, field_mapping.target)
        fks_compare = fks_compare[:-4]
        return fks_compare

    def __get_link_join(self, mappings, schema_name='sor'):
        #todo robuuster maken met aliassen en ands
        join_sql = ''
        for field_mapping in mappings.field_mappings:
            join = field_mapping.join
            bk = field_mapping.bk
            if join:
                join_tbl = field_mapping.get_source_table()
                if field_mapping.is_view_mapping:
                    schema_name = 'dv'
                    join_sql += ' INNER JOIN {}.{} ON {} \r\n'.format(schema_name, join_tbl, join)
                else:
                    join_alias = self.__get_link_alias_of_source_tbl(field_mapping, join_sql)
                    join = join.replace(join_tbl + '.', join_alias + '.')
                    join_sql += ' INNER JOIN {}.{} {} ON {} \r\n'.format(schema_name, join_tbl, join_alias, join)
            elif bk:
                join_tbl = field_mapping.get_source_table()
                join_alias = self.__get_link_alias_of_source_tbl(field_mapping, join_sql)
                join_sql += ' LEFT JOIN dv.{0} AS {1} ON {1}.bk = {2} \r\n'.format(join_tbl, join_alias, bk)
        return join_sql

    def __get_link_alias_of_source_tbl(self, field_mapping, total_sql, default_alias='hstg'):
        #bij een join in de sql moet elke bron tabel een eigen alias krijgen
        #dat wordt hier bepaald
        alias = default_alias
        if field_mapping.join:
            join_tbl = field_mapping.get_source_table()
            alias = join_tbl[:3] + '_hstg'
            index = 1
            while alias in total_sql:
                alias = alias + str(index)
                index += 1
        if field_mapping.bk:
            # join_tbl = field_mapping.get_source_table()
            alias = field_mapping.source_alias
        return alias


    def sor_to_valuesets(self, mappings: SorToValueSetMapping):