class Column():
    def __init__(self, name: str, type: str = 'text', tbl: 'Table' = None, default_value = '', pk = False, unique = False, indexed = False, nullable = True, fhir_name = '') -> None:
        name = name.strip()
        self.name = name.strip().lower()
        if not isinstance(type, str):
            if type == 23:
                type = 'integer'
            else:
                type = 'text'
        self.type = type.strip()
        #todo length
        self.length = ''
        self.table = tbl
        self.is_key = pk
        self.is_unique = unique
        self.is_indexed = indexed
        self.nullable = nullable
        self.default_value = default_value
        self.fhir_name = fhir_name

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

    def __repr__(self) -> str:
        return "{} ({})".format(self.name.lower(), self.type.lower())

    @staticmethod
    def clear_name(name: str) -> str:
        name = name.replace('(', '').replace(')', '')
        name = name.replace('?', '')
        name = name.replace('-', '')
        name = name.replace(',', '')
        name = name.replace('.', '')
        name = name.replace(':', '')
        name = name.replace('"', '')
        name = name.replace(' ', '_')
        name = name.replace('/', '_')
        name = name.lower()
        return name

    def get_table(self):
        return self.table

    def __eq__(self, other):
        # zet om in sql
        return Condition(self.name, '=', other, table=self.table)

    def __ne__(self, other):
        # zet om in sql
        return Condition(self.name, '!=', other, table=self.table)
        # return Condition('({} != {})'.format(self.name,other))

    def __gt__(self, other):
        # zet om in sql
        return Condition(self.name, '>', other, table=self.table)
        # return Condition('({} > {})'.format(self.name,other))

    def __ge__(self, other):
        # zet om in sql
        return Condition(self.name, '>=', other, table=self.table)
        # return Condition('({} >= {})'.format(self.name,other))

    def __lt__(self, other):
        # zet om in sql
        return Condition(self.name, '<', other, table=self.table)
        # return Condition('({} < {})'.format(self.name,other))

    def __le__(self, other):
        # zet om in sql
        return Condition(self.name, '<=', other, table=self.table)
        # return Condition('({} <= {})'.format(self.name,other))

    def is_in(self, item):
        return Condition(self.name, 'in', item, table=self.table)

    def between(self, item1, item2):
        item = '{} AND {}'.format(item1, item2)
        return Condition(self.name, 'between', item, table=self.table)

    def join(self, item):
        sql = self.name + '||' + item.name
        if isinstance(item, (list, tuple)):
            sql = self.name + '||' + '||'.join(item)
        return SqlSnippet(sql, table=self.table) 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 Database():
    """Deze class representeert een database. Maakt gebruik van sql alchemy. Geef een sqlalchemy connection string mee om te initieren::

        db = Database('postgresql://user:pwd@localhost:5432/db')
        db.execute("INSERT (id, veld) INTO table VALUES (1, 'waarde')")
        """
    def __init__(self, conn_string: str = '', default_schema: str = 'public') -> None:
        self.engine = create_engine(conn_string)
        conn_string_parts = conn_string.split('/')
        self.name = conn_string_parts[-1]  # type: str
        self.default_schema = Schema(default_schema, self)
        self.driver = DBDrivers.POSTGRESS #type: str
        self.reflected_schemas = {} #type: Dict[str, Schema]

    def reflect_schemas(self):
        """via sqlalchemy inspector worden de schema-namen in de database opgehaald. Hier worden schema objecten van gemaakt.
        """
        self.reflected_schemas = {}
        inspector = reflection.Inspector.from_engine(self.engine)
        schema_names = inspector.get_schema_names()
        for schema_name in schema_names:
            self.reflected_schemas[schema_name] = Schema(schema_name, self)

    def execute(self, sql: str, log_message: str='') -> int:
        """
        voert sql uit (insert, update, delete enz)
        :param sql: geldige sql string
        :param log_message: indien database logging bevat wordt er gelogd
        :return: executed rowcount
        """
        self.log('-- ' + log_message.upper())
        self.log( sql)

        start = time.time()
        connection = self.engine.raw_connection()
        cursor = connection.cursor()
        cursor.execute(sql)
        connection.commit()

        rowcount = cursor.rowcount
        self.log('-- duur: ' + str(time.time() - start) +  '; aantal rijen:' + str(cursor.rowcount))
        self.log('-- =============================================================')
        cursor.close()
        return rowcount



    def execute_returning(self, sql: str, log_message: str = ''):
        """Geeft rijen terug"""
        self.log('-- ' + log_message.upper())
        self.log(sql)

        start = time.time()
        connection = self.engine.raw_connection()
        cursor = connection.cursor()
        cursor.execute(sql)
        connection.commit()

        result = cursor.fetchall()
        self.log('-- duur: ' + str(time.time() - start) + '; aantal rijen:' + str(cursor.rowcount))
        self.log('-- =============================================================')
        cursor.close()
        return result

    def execute_read(self, sql, log_message='') -> List[List[Any]]:
        """Geeft rijen terug. Zelfde als execute_returning, maar dan met dict cursor"""
        self.log('-- ' + log_message.upper())
        self.log(sql)

        start = time.time()
        connection = self.engine.raw_connection()
        cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor)
        cursor.execute(sql)
        result = cursor.fetchall()

        self.log('-- duur: ' + str(time.time() - start) + '; aantal rijen:' + str(cursor.rowcount))
        self.log('-- =============================================================')
        cursor.close()
        return result

    def confirm_execute(self, sql: str, log_message: str='', ask_confirm = True) -> None:
        """Vraagt de gebruiker via cmd prompt om een wijziging in de db te confirmen.
        Toets j of y voor bevestiging"""
        if ask_confirm:
            sql_sliced = sql
            if len(sql) > 50:
                sql_sliced = sql[:50] + '...'
            else:
                sql_sliced = sql
            result = input('{}\r\nWil je de volgende wijzigingen aanbrengen in de database?\r\n{}\r\n'.format(log_message.upper(), sql_sliced))
            if result.strip().lower()[:1] == 'j' or result.strip().lower()[:1] ==  'y':
                self.execute(sql, log_message)
            else:
                raise Exception('afgebroken')
        else:
            self.execute(sql, log_message)

    def log(self, msg: str) -> None:
        """override om te implementeren"""
        pass class View(Table):
    def __init__(self, name: str, schema: 'Schema', db: 'Database') -> None:
        super().__init__(name, schema, db)
        self.sql = '' 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 DbFunction:
    def __init__(self, *args):
        self.name = camelcase_to_underscores(self.__class__.__name__)
        self.func_params = []
        for arg in args:
            self.func_params.append(arg)
        self.sql_body = ''
        self.return_type = ''
        self.schema = None
        self.lang = 'PLPGSQL'

    def get_complete_name(self):
        func_args = ''
        for func_param in self.func_params:
            func_args += '{} {}, '.format(func_param.name, func_param.type)
        func_args = func_args[:-2]
        params = {'schema': self.schema.name, 'name': self.name, 'args': func_args}
        return '{schema}.{name}({args})'.format(**params)

    def to_create_sql(self):
        func_params = ''
        for func_param in self.func_params:
            func_params += '{} {}, '.format(func_param.name, func_param.type)
        func_params = func_params[:-2]
        params = {'schema': self.schema.name, 'name': self.name, 'params': func_params, 'return_type': self.return_type, 'sql_body': self.sql_body, 'lang': self.lang}

        sql = """CREATE OR REPLACE FUNCTION {schema}.{name}({params})
    RETURNS {return_type}
      AS
      $BODY$
        {sql_body}
      $BODY$
    LANGUAGE {lang} VOLATILE;""".format(**params)
        return sql

    def get_sql(self, alias=''):
        params = self.__dict__
        fields = ''
        for param in self.func_params:
            if alias:
                fields += '{}.{},'.format(alias, param.calling_field)
            else:
                fields += '{},'.format(param.calling_field)
        fields = fields[:-1]
        params['fields'] = fields
        sql = "{schema}.{name}({fields})".format(**params)
        return sql

    def parse_args(self, args_str):
        args = args_str.split(',')
        self.func_params = []
        for arg in args:
            arg = arg.strip()
            arg_name = arg.split(' ')[0]
            arg_type = arg.split(' ')[1]
            self.func_params.append(DbFunctionParameter(arg_name, arg_type))

    def parse_sql_body(self, sql_body=''):
        start = sql_body.find('$function$') + len('$function$')
        end = sql_body.rfind('$function$')
        body = sql_body[start:end]
        self.sql_body = body

    def get_stripped_body(self):
        body = self.sql_body
        body = body.replace('  ', '')
        body = body.replace('  ', '')
        body = body.replace('\t', '')
        body = body.replace('\r\n', '')
        body = body.replace('\n', '')
        if not body.startswith('DECLARE'):
            body = 'DECLARE' + body
        return body class Dwh(Database):
    """De datawarehouse database. Binnen deze database meerdere schema's."""
    def __init__(self, config: Dict[str, str] = {}) -> None:
        self.config = config  # type: Dict[str, str]
        if 'conn_dwh' in self.config:
            conn_string = self.config['conn_dwh']  # type: str
            super().__init__(conn_string)
        self.schemas = {}
        self.schemas['dv'] = Schema('dv', self, DwhLayerTypes.DV)  # type: Schema
        self.schemas['valset'] = Schema('valset', self, DwhLayerTypes.VALSET)  # type: Schema
        self.schemas['sys'] = Schema('sys', self, DwhLayerTypes.SYS)  # type: Schema

    def get_or_create_sor_schema(self, name) -> 'Schema':
        if name in  self.schemas:
            return self.schemas[name]
        else:
            sor = Schema(name, self, DwhLayerTypes.SOR)
            self.schemas[name] = sor
        return sor

    def get_schema(self, name=''):
        found = None
        if name in self.schemas:
            found = self.schemas[name]
        return found

    def set_schema(self, name, layer_type = DwhLayerTypes.DV):
        schema = Schema(name, self, layer_type)
        self.schemas[name] = schema

    @property
    def sors(self):
        return {k:v for k,v in self.schemas.items() if v.schema_type == DwhLayerTypes.SOR}

    @property
    def sys(self):
        return self.get_schema('sys')

    @property
    def dv(self):
        return self.get_schema('dv')

    @property
    def rdv(self):
        return self.get_schema('rdv')

    @property
    def valset(self):
        return self.get_schema('valset')

    def get_or_create_datamart_schema(self, dm_name: str) -> 'Schema':
        if dm_name in self.schemas:
            return self.schemas[dm_name]
        else:
            dm = Schema(dm_name, self, DwhLayerTypes.DM)
            self.schemas[dm_name] = dm
            return dm

    def create_schemas_if_not_exists(self, schema_name: str ='') -> None:
        self.reflect_schemas()
        for schema_name in self.schemas.keys():
            if not schema_name in self.reflected_schemas:
                sql = """CREATE SCHEMA {};""".format(schema_name)
                self.confirm_execute(sql, 'nieuw ' + schema_name + ' schema aanmaken')
        return
        if schema_name and not schema_name in self.reflected_schemas:
            sql = """CREATE SCHEMA {};""".format(schema_name)
            self.confirm_execute(sql, 'nieuw schema aanmaken')
            sor = Sor(schema_name, self)
            self.schemas[schema_name] = sor
        dv_schemas = {k:v for k,v in self.schemas.items() if v.schema_type == DwhLayerTypes.DV}
        for dv_schema_name in dv_schemas.keys():
            if not dv_schema_name in self.reflected_schemas:
                sql = """CREATE SCHEMA {};""".format(dv_schema_name)
                self.confirm_execute(sql, 'nieuw ' + dv_schema_name + ' schema aanmaken')
        if not self.sys.name in self.reflected_schemas:
            sql = """CREATE SCHEMA {};""".format(self.sys.name)
            self.confirm_execute(sql, 'nieuw sys schema aanmaken')
            sql = self.get_create_sys_ddl()
            self.confirm_execute(sql, 'nieuwe sys tabellen aanmaken')

    def get_create_sys_ddl(self) -> str:
        # ivm cross import reference conficts staat deze sql hier en niet in ddl.py
        sql = """
        CREATE TABLE sys.runs
        (
          runid numeric(8,2) NOT NULL,
          rundate timestamp without time zone NOT NULL DEFAULT now(),
          finish_date timestamp without time zone,
          exceptions boolean,
          sor_versions text,
          dv_version numeric(8,2),
          pyelt_version text,
          CONSTRAINT "PK" PRIMARY KEY (runid)
        )
        WITH (
          OIDS=FALSE
        );

        CREATE TABLE sys.currentversion
        (
          schemaname text NOT NULL,
          version numeric(8,2) NOT NULL,
          date timestamp without time zone,
          CONSTRAINT currentversion_pkey PRIMARY KEY (schemaname, version),
          CONSTRAINT currentversion_schemaname_key UNIQUE (schemaname)
        )
        WITH (
          OIDS=FALSE
        );
        INSERT INTO sys.runs (runid, rundate) VALUES (0, now());
        """ #type: str

        return sql

    def get_layer_version(self, layer_name) -> None:
        sql = """SELECT version FROM sys.currentversion WHERE schemaname = '{}'""".format(layer_name)
        rows = self.execute_read(sql, 'get version')
        if len(rows) > 0:
            return float(rows[0][0])
        else:
            return None

    def increase_layer_version(self, layer_name) -> None:
        old_version_number = self.get_layer_version(layer_name)
        if old_version_number:
            new_version_number = old_version_number + 0.01
            sql = """UPDATE sys.currentversion SET version = {} WHERE schemaname = '{}'""".format(new_version_number, layer_name)
            self.execute(sql, 'update version')
        else:
            new_version_number = 1.0
            sql = """INSERT INTO sys.currentversion (schemaname, version, date) VALUES ('{}', 1, now());""".format(layer_name)
            self.execute(sql, 'insert version')
        return new_version_number

    def confirm_execute(self, sql: str, log_message: str='') -> None:
        ask_confirm = False
        if 'ask_confirm_on_db_changes' in self.config:
            ask_confirm = self.config['ask_confirm_on_db_changes']
        else:
            ask_confirm = 'debug' in self.config and self.config['debug']
        super().confirm_execute(sql, log_message, ask_confirm) 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 DvValueset(AbstractOrderderTable):
    __dbschema__ = 'valset'
    _id = Columns.SerialColumn()
    _runid = Columns.FloatColumn()
    _source_system = Columns.TextColumn()
    _valid = Columns.BoolColumn()
    _validation_msg = Columns.TextColumn()
    _insert_date = Columns.DateTimeColumn()
    _finish_date = Columns.TextColumn()
    _active = Columns.BoolColumn(default_value=True, indexed=True)
    _revision = Columns.IntColumn()
    valueset_naam = Columns.TextColumn(unique=True)
    code = Columns.TextColumn(unique=True, indexed=True)
    omschrijving = Columns.TextColumn() class PyeltException(Exception):
    def __init__(self, err, err_msg = ''):
        super().__init__(err) class BaseTableMapping(BaseMapping):
    def __init__(self, source: Any, target: Union[str, 'Table'], filter: str = '') -> None:
        super().__init__(source,target)
        #filter voor ophalen van data uit source
        self.filter = filter #type: str
        self.field_mappings = [] #type: List[FieldMapping]
        self.key_mappings = [] #type: List[str]
        self.validations = [] #type: List[Validation]
        #type: List[Union[ConstantValue, FieldTransformation, Column, str]]

    def map_field(self, source: str, target: str = '', transform_func: 'FieldTransformation'=None, ref: str = '') -> None:
        if source and target:
            field_mapping = FieldMapping(source, target, transform_func, ref = ref)
            self.field_mappings.append(field_mapping)

    # def parse_mapping_str(self, input_str: str) -> Tuple[str, str]:
    #     """input str in format
    #     'source          =>      target  [type]'"""
    #     source = input_str.split('=>')[0].strip()
    #     target = input_str.split('=>')[1].strip()
    #     return source, target

    def get_source_fields(self, alias: str='') -> str:
        fields = '' #type: str
        for field_map in self.field_mappings:
            if 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)
            #voorkom eventuele dubbele veldnamen bij hybrid sats
            if field not in fields:
                fields += field
        fields = fields[:-1]
        return fields

    def get_target_fields(self, alias:str='') -> str:
        fields = []  # type: str
        for field_map in self.field_mappings:
            if not alias:
                field = '{}'.format(field_map.target)
            else:
                field = '{}.{}'.format(alias, field_map.target)
            #voorkom eventuele dubbele veldnamen bij hybrid sats
            if field not in fields:
                fields.append(field)
        # fields = fields[:-1]
        return ','.join(fields)

    def get_fields_compare(self, source_alias: str='', target_alias: str='') -> str:
        fields_compare = '' #type: str
        for field_map in self.field_mappings:
            if isinstance(field_map.source, ConstantValue):
                fields_compare += "{1} != COALESCE({0}.{2}, '') OR ".format(target_alias, field_map.source, field_map.target)
            elif isinstance(field_map.source, FieldTransformation):
                source = field_map.source.get_sql(source_alias)
                fields_compare += "{0} != COALESCE({1}.{2}, '') OR ".format(source, target_alias, field_map.target)
            elif '(' in field_map.source.name and target_alias:
                # source bevat functie: geen alias toevoegen
                fields_compare += "{1} != COALESCE({0}.{2}, '') OR ".format(target_alias, field_map.source, field_map.target)
            elif source_alias and target_alias:
                fields_compare += "COALESCE({0}.{2}, '') != COALESCE({1}.{3}, '') OR ".format(source_alias, target_alias, field_map.source, field_map.target)
            elif source_alias and not target_alias:
                fields_compare += "COALESCE({0}.{2} != COALESCE({2}, '') OR ".format(source_alias, field_map.source, field_map.target)
            elif target_alias and not source_alias:
                fields_compare += "COALESCE({1} != COALESCE({0}.{2}, '') OR ".format(target_alias, field_map.source, field_map.target)
            else:
                fields_compare += "COALESCE({0} != COALESCE({1}, '') OR ".format(field_map.source, field_map.target)
        fields_compare = fields_compare[:-3]
        return fields_compare

    # def get_keys(self, alias=''):
    #     if not alias:
    #         return ','.join(self.keys)
    #     else:
    #         keys = ''
    #         for key in self.keys:
    #             keys += '{}.{},'.format(alias, key)
    #             keys = keys[:-1]
    #         return keys
    #
    # def get_keys_compare(self, source_alias='', target_alias=''):
    #     keys_compare = ''
    #     for key in self.keys:
    #         keys_compare += '{0}.{2} = {1}.{2} AND '.format(source_alias, target_alias, key)
    #     keys_compare = keys_compare[:-4]
    #     return keys_compare class FieldMapping(BaseMapping):
    def __init__(self, source: Union[str, 'Column', 'FieldTransformation'], target: Union[str, 'Column'], transform_func: 'FieldTransformation' = None, ref: str = '') -> None:
        source_col = None #type: Union[FieldTransformation, Column]
        target_col = None #type: Column
        if isinstance(source, str):
            if " " in source or ('(' in source and ')' in source):
                source_col = FieldTransformation(name=source, sql=source)
            else:
                source_col = Column(source)
        elif isinstance(source, DbFunction):
            source_col = source
        else:
            source_col = source
        if transform_func:
            transform_func.field_name = source_col.name
            source_col = transform_func
        if isinstance(target, str):
            target_col = Column(target)
        else:
            target_col = target
        super().__init__(source_col, target_col)
        self.transformation = transform_func
        self.source_type = '' #type: str
        self.target_type = '' #type: str
        self.ref = ref #type: str 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 FieldTransformation():
    def __init__(self, name: str = '', sql: str = '') -> None:
        self.name = name #type: str
        self.field_name = 'id' #type: str
        self.table = ''
        self.descr = '' #type: str
        self.filter = '' #type: str
        # self.type = FieldTransformationType.INLINE #type: str
        self.steps = {} #type: Dict[int, FieldTransformStep]
        if sql:
            # self.parse_sql(sql)
            step = self.new_step(sql)

    def get_table(self):
        return self.table

    def parse_sql(self, sql: str):
        pos_start = sql.find('(')
        pos_end = sql.rfind(')')
        func_name = sql[:pos_start]
        func_inner = sql[pos_start + 1:pos_end]
        step = self.new_step(func_inner)
        self.steps[step.sort_order] = step

    def new_step(self, sql: str) -> 'FieldTransformStep':
        step = FieldTransformStep(sql=sql)
        step.sort_order = len(self.steps) + 1
        self.steps[step.sort_order] = step
        return step

    def get_sql(self, alias: str='')->str:
        sql = ''
        index = 0
        steps = sorted(self.steps.values(), key = lambda x: x.sort_order)
        for step in steps:
            # step_sql = step.sql
            step_sql = step.get_sql(alias)
            step_sql = step_sql.replace(self.field_name, "{fld}")
            if (index > 0):
                if '{fld}' in step_sql:
                    sql = step_sql.replace("{fld}", sql)
                else:
                    sql = step_sql.replace("{step" + str(index) + "}", sql)
            else:
                 sql = step_sql
                 sql = sql.replace("{fld}", self.field_name)
            index += 1
        return sql

    def __repr__(self):
        return self.get_sql('')
import inspect
from collections import OrderedDict
from typing import Union, List, Dict
from pyelt.datalayers.database import Column, Table, Database, View, Schema, DbFunction
from pyelt.datalayers.dv import * # HubEntity, Link, HybridSat, LinkReference, Sat, DynamicLinkReference, DvValueset
from pyelt.datalayers.dwh import Dwh
from pyelt.datalayers.sor import SorTable, SorQuery
from pyelt.datalayers.valset import DvValueset
from pyelt.helpers.exceptions import PyeltException
from pyelt.mappings.base import BaseTableMapping, FieldMapping, ConstantValue
from pyelt.mappings.transformations import FieldTransformation
        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 KeyMapping():
    def __init__(self, source, target):
        self.source = source
        self.target = target
        self.sat = target.get_table()

class EntityViewToEntityMapping(SorToEntityMapping):
    def __init__(self, source, target, filter: str = '') -> None:
        super().__init__(source, target, filter)
        self.view = source

class SorSatMapping(BaseTableMapping):
    def __init__(self, source: str, target: Sat, type: str = '') -> None:
        super().__init__(source, target)
        if type:
            self.name += '(' + type + ')'
        self.sor_table = source
        self.sat = target


    def map_field(self, source: Union[str, 'ConstantValue'], target: 'Column', transform_func: 'FieldTransformation'=None, ref: str = '') -> None:
        # automatisch cast naar type toevoegen
        if target.type.lower() != 'text' and isinstance(source, str) and not '(' in source and not ')' in source and not '::' in source:
            source += '::' + target.type
        super().map_field(source, target, transform_func, ref = ref)

    def get_source_fields(self, alias: str = '')-> str:
        if not alias: alias = 'hstg'
        is_hybrid_sat = self.target.__base__ == HybridSat
        # return super().get_source_fields(alias)
        fields = ''  # type: 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 is_hybrid_sat and field not in fields:
                fields += field
            else:
                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_sat_fields(self) -> str:
        # return super().get_target_fields()
        fields = []  # type: str
        #json velden willen we helemaal als laatste omdat ze in get_source_fields ook als laatste komen te staan
        json_fields = []# type: str
        for field_map in self.field_mappings:

            field = '{}'.format(field_map.target)
            # voorkom eventuele dubbele veldnamen bij hybrid sats
            if field_map.target.type == 'jsonb':
                if field not in json_fields:
                    json_fields.append(field)
            elif field not in fields:
                fields.append(field)
        # fields = fields[:-1]
        return_sql = ','.join(fields)
        if json_fields:
            return_sql += "," + ','.join(json_fields)
        return return_sql

    def get_fields_compare(self, source_alias: str='', target_alias:str = '') -> str:
        if not source_alias: source_alias = 'hstg'
        if not target_alias: target_alias = 'sat'
        return super().get_fields_compare(source_alias, target_alias)


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: