sequelize#literal TypeScript Examples

export function createInterpreter(interpreters: Record<string, SqlOperator<any>>) {
  const interpretSQL = createSqlInterpreter(interpreters);

  return (condition: Condition, Model: ModelType) => {
    const dialect = Model.sequelize!.getDialect() as keyof typeof dialects;
    const options = dialects[dialect];

    if (!options) {
      throw new Error(`Unsupported database dialect: ${dialect}`);
    }

    const [sql, params, joins] = interpretSQL(condition, options, Model);
    return {
      include: joins.map(association => ({ association, required: true })),
      where: literal(Utils.format([sql, ...(params as string[])], dialect)),
    };
  };
}

/* sample search request
query { search(
    requests: [
        {
            entity: ITEM, 
            offset: 0, 
            limit: 100,
            order: [["id", "ASC"]], - optional
            where: {typeId: 2, values: { attr1: { OP_ne: "attr1"}}} - optional
        }]
    ) {
    responses {
        ... on ItemsSearchResponse {
            count
            rows {
                id
                identifier
                name
            }
        }
    }}} 
    
    more where: {updatedAt: { OP_gt: "2020-04-3 12:33:16"}}

    more where (join with relations):
    {"OP_or": [
{ "sourceRelation___relationId": 1 },
{ "targetRelation___relationId": 1 }]}
*/

function replaceOperations(obj: any) {
    let include = []
    let sourceRelation = false
    let targetRelation = false
    let sourceItem = false
    let targetItem = false
    for (const prop in obj) {
        let value = obj[prop]

        if (typeof value === 'string' && value.startsWith('###:')) {
            value = literal(value.substring(4))
        }

        if (typeof value === 'string' && value.startsWith('#DAY#')) {
            const tst = value.substring(5)
            const days = parseInt(tst)
            if (days != NaN) value = moment().startOf('day').add(days, 'days').utc().format()
        }

        if (typeof value === 'string' && value.startsWith('#HOUR#')) {
            const tst = value.substring(6)
            const hours = parseInt(tst)
            if (hours != NaN) value = moment().add(hours, 'hours').utc().format()
        }

        if (typeof value === 'string' && value.startsWith('#MIN#')) {
            const tst = value.substring(5)
            const min = parseInt(tst)
            if (min != NaN) value = moment().add(min, 'minutes').utc().format()
        }

        if (prop.startsWith('OP_')) {
            const operation = prop.substr(3)
            delete obj[prop]
            obj[Symbol.for(operation)] = value
        }

        if (prop === 'include' && Array.isArray(value)) {
            include = value
            delete obj[prop]
            fillInclude(include)
        }

        if (prop !== 'include' && value === Object(value)) {
            replaceOperations(value)
        }
    }
    return include
}

/*

mutation { import(
    config: {
        mode: CREATE_UPDATE
        errors: PROCESS_WARN
    },
    items: [
        {
            identifier: "itemSa1",
            parentIdentifier: "itemLevel1",
            typeIdentifier: "sa1",
            name: {ru:"Продукт1"},
            values: {
                attr1: "aaa"
                attr2: {ru: "test"}
            }
        }]
    ) {
    items {
	  identifier
	  result
	  id
	  errors { code message }
	  warnings { code message }
	}}}

*/

export async function importItem(context: Context, config: IImportConfig, item: IItemImportRequest): Promise<ImportResponse> {
    const result = new ImportResponse(item.identifier)

    if (!item.identifier || !/^[A-Za-z0-9_-]*$/.test(item.identifier)) {
        result.addError(ReturnMessage.WrongIdentifier)
        result.result = ImportResult.REJECTED
        return result
    }

    try {
        if (item.delete) {
            const data = await Item.applyScope(context).findOne({where: { identifier: item.identifier } })
            if (!data) {
                result.addError(ReturnMessage.ItemNotFound)
                result.result = ImportResult.REJECTED
            } else {
                if (!context.canEditItem(data)) {
                    result.addError(ReturnMessage.ItemNoAccess)
                    result.result = ImportResult.REJECTED
                }

                const mng = ModelsManager.getInstance().getModelManager(context.getCurrentUser()!.tenantId)
                // check Roles
                const tst1 = mng.getRoles().find(role => role.itemAccess.fromItems.includes(data.id))
                // check Attributes
                // const tst2 = await Attribute.applyScope(context).findOne({where: {visible: { [Op.contains]: data.id}}})
                const tst2:any = await Attribute.applyScope(context).findOne({where: literal("visible @> '"+data.id+"'") })
                if (tst1 || tst2) {
                    result.addError(ReturnMessage.ItemDeleteFailed)
                    result.result = ImportResult.REJECTED
                    return result
                }
                // check children
                const cnt:any = await sequelize.query('SELECT count(*) FROM items where "deletedAt" IS NULL and "tenantId"=:tenant and path~:lquery', {
                    replacements: { 
                        tenant: context.getCurrentUser()!.tenantId,
                        lquery: data.path + '.*{1}',
                    },
                    plain: true,
                    raw: true,
                    type: QueryTypes.SELECT
                })
                const childrenNumber = parseInt(cnt.count)
                if (childrenNumber > 0) {
                    result.addError(ReturnMessage.ItemDeleteFailedChildren)
                    result.result = ImportResult.REJECTED
                    return result
                }
                // check relations
                const num = await ItemRelation.applyScope(context).count({
                    where: {
                        [Op.or]: [{itemId: data.id}, {targetId: data.id}]
                    },
                })
                if (num > 0) {
                    result.addError(ReturnMessage.ItemDeleteFailedRelations)
                    result.result = ImportResult.REJECTED
                    return result
                }

                data.updatedBy = context.getCurrentUser()!.login

                if (!item.skipActions) await processItemActions(context, EventType.BeforeDelete, data, "", "", null, null, true)

                const oldIdentifier = item.identifier
                data.identifier = item.identifier + '_d_' + Date.now() 
                await sequelize.transaction(async (t) => {
                    await data.save({transaction: t})
                    await data.destroy({transaction: t})
                })

                if (!item.skipActions) await processItemActions(context, EventType.AfterDelete, data, "", "", null, null, true)

                if (audit.auditEnabled()) {
                    const itemChanges: ItemChanges = {
                        typeIdentifier: data.typeIdentifier,
                        parentIdentifier: data.parentIdentifier,
                        name: data.name,
                        values: data.values
                    }
                    audit.auditItem(ChangeType.DELETE, data.id, oldIdentifier, {deleted: itemChanges}, context.getCurrentUser()!.login, data.updatedAt)
                }
    
                result.result = ImportResult.DELETED
            }
            return result
        }

        let data: Item | null = await Item.applyScope(context).findOne({where: { identifier: item.identifier } })
        if (config.mode === ImportMode.CREATE_ONLY) {
            if (data) {
                result.addError(ReturnMessage.ItemExist)
                result.result = ImportResult.REJECTED
                return result
            }
        } else if (config.mode === ImportMode.UPDATE_ONLY) {
            if (!data) {
                result.addError(ReturnMessage.ItemNotFound)
                result.result = ImportResult.REJECTED
                return result
            }
        }

        const mng = ModelsManager.getInstance().getModelManager(context.getCurrentUser()!.tenantId)

        if (!data) {
            // create
            const type = checkType(item, result, mng)
            if (result.result) return result
    
            let parent = await checkParent(item, result, mng, context)
            if (result.result) return result
    
            const results:any = await sequelize.query("SELECT nextval('items_id_seq')", { 
                type: QueryTypes.SELECT
            });
            const id = (results[0]).nextval

            let path:string
            if (parent) {
                path = parent.path + "." + id
            } else {
                path = '' + id
            }
    
            if (!context.canEditItem2(type!.getValue().id, path)) {
                result.addError(ReturnMessage.ItemNoAccess)
                result.result = ImportResult.REJECTED
                return result
            }

            const data = await Item.build ({
                id: id,
                path: path,
                identifier: item.identifier,
                tenantId: context.getCurrentUser()!.tenantId,
                createdBy: context.getCurrentUser()!.login,
                updatedBy: context.getCurrentUser()!.login,
                name: item.name,
                typeId: type!.getValue().id,
                typeIdentifier: type!.getValue().identifier,
                parentIdentifier: parent ? parent.identifier : "",
                values: null,
                fileOrigName: '',
                storagePath: '',
                mimeType: ''
            })

            if (!item.values) item.values = {}
            if (!item.skipActions) await processItemActions(context, EventType.BeforeCreate, data, item.parentIdentifier, item.name, item.values, item.channels, true)

            filterEditChannels(context, item.channels)
            checkSubmit(context, item.channels)

            filterValues(context.getEditItemAttributes2(type!.getValue().id, path), item.values)
            try {
                checkValues(mng, item.values)
            } catch (err: any) {
                result.addError(new ReturnMessage(0, err.message))
                result.result = ImportResult.REJECTED
                return result
            }

            data.values = item.values
            data.channels = item.channels

            await sequelize.transaction(async (t) => {
                await data.save({transaction: t})
            })

            if (!item.skipActions) await processItemActions(context, EventType.AfterCreate, data, item.parentIdentifier, item.name, item.values, item.channels, true)

            if (audit.auditEnabled()) {
                const itemChanges: ItemChanges = {
                    typeIdentifier: data.typeIdentifier,
                    parentIdentifier: data.parentIdentifier,
                    name: data.name,
                    values: data.values
                }
                audit.auditItem(ChangeType.CREATE, data.id, item.identifier, {added: itemChanges}, context.getCurrentUser()!.login, data.createdAt)
            }

            result.id = ""+data.id
            result.result = ImportResult.CREATED
        } else {
            // update
            if ((item.name || item.values) && !context.canEditItem(data)) {
                result.addError(ReturnMessage.ItemNoAccess)
                result.result = ImportResult.REJECTED
                return result
            }

            let itemDiff: AuditItem = {added:{}, changed:{}, old:{}, deleted: {}}
            if (item.typeIdentifier) {
                const type = checkType(item, result, mng)
                if (result.result) return result
                
                if (data.typeId !== type!.getValue().id) {
                    if (audit.auditEnabled()) {
                        itemDiff.changed!.typeIdentifier = type!.getValue().identifier
                        itemDiff.old!.typeIdentifier = data.typeIdentifier
                    }
                    data.typeId = type!.getValue().id
                    data.typeIdentifier = type!.getValue().identifier
                }
            } else {
                item.typeIdentifier = data.typeIdentifier
            }

            if (!item.values) item.values = {}
            if (!item.skipActions) await processItemActions(context, EventType.BeforeUpdate, data, item.parentIdentifier, item.name, item.values, item.channels, true)

            if (item.parentIdentifier && data.parentIdentifier !== item.parentIdentifier) {
                let parent = await checkParent(item, result, mng, context)
                if (result.result) return result

                if (audit.auditEnabled()) {
                    itemDiff.changed!.parentIdentifier = item.parentIdentifier
                    itemDiff.old!.parentIdentifier = data.parentIdentifier
                }

                let newPath: string
                if (parent) {
                    newPath = parent.path+"."+data.id
                } else {
                    newPath = ""+data.id
                }
                if (newPath !== data.path) {
                    // check children
                    const cnt: any = await sequelize.query('SELECT count(*) FROM items where "deletedAt" IS NULL and "tenantId"=:tenant and path~:lquery', {
                        replacements: {
                            tenant: context.getCurrentUser()!.tenantId,
                            lquery: data.path + '.*{1}',
                        },
                        plain: true,
                        raw: true,
                        type: QueryTypes.SELECT
                    })
                    const childrenNumber = parseInt(cnt.count)
                    if (childrenNumber > 0) { //move subtree
                        await sequelize.query('update items set path = text2ltree(:parentPath) || subpath(path,:level) where path <@ :oldPath and "tenantId"=:tenant', {
                            replacements: { 
                                tenant: context.getCurrentUser()!.tenantId,
                                oldPath: data.path,
                                parentPath: parent ? parent.path : '',
                                level: data.path.split('.').length - 1
                            },
                            plain: true,
                            raw: true,
                            type: QueryTypes.UPDATE
                        })
                    } else { // move leaf
                        data.path = newPath
                    }
                    data.parentIdentifier = parent ? parent.identifier : ""
                }
            }

            if (item.name) {
                if (audit.auditEnabled()) {
                    const nameDiff: AuditItem = diff({name:data.name}, {name:item.name})
                    itemDiff.added = {...itemDiff.added, ...nameDiff.added}
                    itemDiff.changed = {...itemDiff.changed, ...nameDiff.changed}
                    itemDiff.old = {...itemDiff.old, ...nameDiff.old}
                }
                data.name = {...data.name, ...item.name}
            }

            filterEditChannels(context, item.channels)
            checkSubmit(context, item.channels)
            filterValues(context.getEditItemAttributes(data), item.values)
            try {
                checkValues(mng, item.values)
            } catch (err:any) {
                result.addError(new ReturnMessage(0, err.message))
                result.result = ImportResult.REJECTED
                return result
            }

            if (audit.auditEnabled()) {
                const valuesDiff: AuditItem = diff({values:data.values}, {values:item.values})
                itemDiff.added = {...itemDiff.added, ...valuesDiff.added}
                itemDiff.changed = {...itemDiff.changed, ...valuesDiff.changed}
                itemDiff.old = {...itemDiff.old, ...valuesDiff.old}
            }

            data.values = mergeValues(item.values, data.values)
            data.channels = mergeValues(item.channels, data.channels)

            data.updatedBy = context.getCurrentUser()!.login
            await sequelize.transaction(async (t) => {
                await data!.save({transaction: t})
            })

            if (!item.skipActions) await processItemActions(context, EventType.AfterUpdate, data, item.parentIdentifier, item.name, item.values, item.channels, true)

            if (audit.auditEnabled()) {
                if (!isObjectEmpty(itemDiff!.added) || !isObjectEmpty(itemDiff!.changed) || !isObjectEmpty(itemDiff!.deleted)) audit.auditItem(ChangeType.UPDATE, data.id, item.identifier, itemDiff!, context.getCurrentUser()!.login, data.updatedAt)
            }

            result.id = ""+data.id
            result.result = ImportResult.UPDATED
        }
    } catch (error) {
        result.addError(new ReturnMessage(0, ""+error))
        result.result = ImportResult.REJECTED
        logger.error(error)
    }
    return result
}

/*
mutation { import(
    config: {
        mode: CREATE_UPDATE
        errors: PROCESS_WARN
    },
    relations: [
        {
            delete: false
            identifier: "tst",
            name: {ru: "test relation"},
            sources: ["level1_2"],
            targets: ["other1_2"],
            child: false,
            multi: true
        }
    ]
    ) {
    relations {
	  identifier
	  result
	  id
	  errors { code message }
	  warnings { code message }
    }}}
*/
export async function importRelation(context: Context, config: IImportConfig, relation: IRelationImportRequest): Promise<ImportResponse> {
    const result = new ImportResponse(relation.identifier)

    if (!relation.identifier || !/^[A-Za-z0-9_-]*$/.test(relation.identifier)) {
        result.addError(ReturnMessage.WrongIdentifier)
        result.result = ImportResult.REJECTED
        return result
    }

    try {
        const mng = ModelsManager.getInstance().getModelManager(context.getCurrentUser()!.tenantId)
        if (relation.delete) {
            const data = mng.getRelationByIdentifier(relation.identifier)
            if (!data) {
                result.addError(ReturnMessage.RelationNotFound)
                result.result = ImportResult.REJECTED
            } else {
                // check Attributes
                // const tst2 = await Attribute.applyScope(context).findOne({where: {relations: { [Op.contains]: data.id}}})
                const tst2 = await Attribute.applyScope(context).findOne({where: literal("relations @> '"+data.id+"'")})
                // check ItemRelations
                const tst1 = await ItemRelation.applyScope(context).findOne({where: {relationId: data.id}})
                // check Roles
                const tst3 = mng.getRoles().find(role => role.relAccess.relations.includes(data.id));
                if (tst1 || tst2 || tst3) {
                    result.addError(ReturnMessage.RelationCanNotDelete)
                    result.result = ImportResult.REJECTED
                    return result
                }

                data.updatedBy = context.getCurrentUser()!.login
                data.identifier = data.identifier + '_d_' + Date.now() 
                await sequelize.transaction(async (t) => {
                    await data!.save({transaction: t})
                    await data!.destroy({transaction: t})
                })
    
                const idx = mng.getRelations().findIndex( (rel) => rel.id === data.id)    
                mng.getRelations().splice(idx, 1)
                    
                result.result = ImportResult.DELETED
            }
            return result
        }

        const data = mng.getRelationByIdentifier(relation.identifier)
        if (config.mode === ImportMode.CREATE_ONLY) {
            if (data) {
                result.addError(ReturnMessage.RelationExist)
                result.result = ImportResult.REJECTED
                return result
            }
        } else if (config.mode === ImportMode.UPDATE_ONLY) {
            if (!data) {
                result.addError(ReturnMessage.RelationNotFound)
                result.result = ImportResult.REJECTED
                return result
            }
        }        

        if (!data) {
            // create
            let sources = checkSources(relation, mng, result)
            if (result.result) return result

            let targets = checkTargets(relation, mng, result)
            if (result.result) return result

            const data = await sequelize.transaction(async (t) => {
                return await Relation.create ({
                    identifier: relation.identifier,
                    tenantId: context.getCurrentUser()!.tenantId,
                    createdBy: context.getCurrentUser()!.login,
                    updatedBy: context.getCurrentUser()!.login,
                    name: relation.name,
                    sources: sources,
                    targets: targets,
                    child: relation.child || false,
                    multi: relation.multi || false,
                    options: relation.options ?  relation.options : [],
                    order: relation.order || 0,
                }, {transaction: t})
            })
            mng.getRelations().push(data)

            result.id = ""+data.id
            result.result = ImportResult.CREATED
        } else {
            // update
            let sources = checkSources(relation, mng, result)
            if (result.result) return result

            let targets = checkTargets(relation, mng, result)
            if (result.result) return result

            if (relation.name) data.name = relation.name
            if (relation.child != null) data.child = relation.child
            if (relation.multi != null) data.multi = relation.multi
            if (relation.options != null) data.options = relation.options
            if (relation.sources) data.sources = sources
            if (relation.targets) data.targets = targets
            if (relation.order != null) data.order = relation.order
            data.updatedBy = context.getCurrentUser()!.login
            await sequelize.transaction(async (t) => {
                await data.save({transaction: t})
            })

            result.id = ""+data.id
            result.result = ImportResult.UPDATED
        } 
    } catch (error) {
        result.addError(new ReturnMessage(0, ""+error))
        result.result = ImportResult.REJECTED
        logger.error(error)
    }

    return result
}

/*

mutation { import(
    config: {
        mode: CREATE_UPDATE
        errors: PROCESS_WARN
    },
    roles: [
        {
        delete: false,
        name: "Администратор2",
        itemAccess: {access:0, valid:[], groups:[], fromItems:[]},
        configAccess: {lovs:2,roles:2,types:2,users:2,languages:2,relations:2,attributes:2},
        identifier: "admin2",
        relAccess: {access:0,relations:[],groups:[]},
        }
    ]
    ) {
    roles {
        identifier
        result
        id
        errors { code message }
        warnings { code message }
}}}

*/

export async function importRole(context: Context, config: IImportConfig, role: IRoleImportRequest): Promise<ImportResponse> {
    const result = new ImportResponse(role.identifier)

    if (!role.identifier || !/^[A-Za-z0-9_-]*$/.test(role.identifier)) {
        result.addError(ReturnMessage.WrongIdentifier)
        result.result = ImportResult.REJECTED
        return result
    }

    try {
        if (role.identifier === 'admin') {
            result.addError(ReturnMessage.RoleAdminCanNotBeUpdated)
            result.result = ImportResult.REJECTED
            return result
        }

        const mng = ModelsManager.getInstance().getModelManager(context.getCurrentUser()!.tenantId)
        const idx = mng.getRoles().findIndex(elem => elem.identifier === role.identifier)
        if (role.delete) {
            if (idx === -1) {
                result.addError(ReturnMessage.RoleNotFound)
                result.result = ImportResult.REJECTED
            } else {
                const data = mng.getRoles()[idx]
                if (data.identifier === 'admin') {
                    result.addError(ReturnMessage.RoleAdminCanNotBeUpdated)
                    result.result = ImportResult.REJECTED
                    return result
                }
    
                // check Users
                // const tst1 = await User.applyScope(context).findOne({where: {roles: { [Op.contains]: data.id}}})
                const tst1 = await User.applyScope(context).findOne({where: literal("roles @> '"+data.id+"'")})
                if (tst1) {
                    result.addError(ReturnMessage.RoleDeleteFailed)
                    result.result = ImportResult.REJECTED
                    return result
                }

                data.updatedBy = context.getCurrentUser()!.login
                data.identifier = role.identifier + '_d_' + Date.now() 
                await sequelize.transaction(async (t) => {
                    await data.save({transaction: t})
                    await data.destroy({transaction: t})
                })
    
                mng.getRoles().splice(idx, 1)
                mng.getUsers().forEach(wrapper => {
                    const idx = wrapper.getRoles().findIndex(r => r.id === data.id)
                    if (idx !== -1) wrapper.getRoles().splice(idx, 1)
                })
                                
                result.result = ImportResult.DELETED
            }
            return result
        }

        if (config.mode === ImportMode.CREATE_ONLY) {
            if (idx !== -1) {
                result.addError(ReturnMessage.RoleExist)
                result.result = ImportResult.REJECTED
                return result
            }
        } else if (config.mode === ImportMode.UPDATE_ONLY) {
            if (idx === -1) {
                result.addError(ReturnMessage.RoleNotFound)
                result.result = ImportResult.REJECTED
                return result
            }
        }        

        if (idx === -1) {
            // create
            let relationIds = checkRelations(role.relAccess.relations, mng, result)
            if (result.result) return result
            let groups = checkGroups(role.relAccess.groups, mng, result)
            if (result.result) return result
            const relAccess = {access: role.relAccess.access || 0, relations: relationIds, groups: groups}

            let valid = checkValid(role.itemAccess.valid, mng, result)
            if (result.result) return result
            let groups2 = checkGroups(role.itemAccess.groups, mng, result)
            if (result.result) return result
            let fromItems = await checkFromItems(role.itemAccess.fromItems, context, result)
            if (result.result) return result
            const itemAccess = { valid: valid, fromItems: fromItems, access: role.itemAccess.access || 0, groups: groups2 }

            const data = await sequelize.transaction(async (t) => {
                return await Role.create ({
                    identifier: role.identifier,
                    tenantId: context.getCurrentUser()!.tenantId,
                    createdBy: context.getCurrentUser()!.login,
                    updatedBy: context.getCurrentUser()!.login,
                    name: role.name || '',
                    configAccess: role.configAccess || { types: 0, attributes: 0, relations: 0, users: 0, roles: 0, languages: 0 },
                    relAccess: relAccess,
                    itemAccess: itemAccess,
                    otherAccess: role.otherAccess || { audit: false, search: false, exportXLS: false, exportCSV: false, importXLS: false, searchRelations: false, exportRelationsXLS: false, importRelationsXLS: false },
                    options: role.options ?  role.options : []
                }, {transaction: t})
            })

            mng.getRoles().push(data);

            result.id = ""+data.id
            result.result = ImportResult.CREATED
        } else {
            // update
            const data = mng.getRoles()[idx]

            if (role.name) data.name = role.name
            if (role.configAccess) data.configAccess = role.configAccess
            if (role.relAccess) {
                let relationIds = checkRelations(role.relAccess.relations, mng, result)
                if (result.result) return result
                let groups = checkGroups(role.relAccess.groups, mng, result)
                if (result.result) return result
                const relAccess = {access: role.relAccess.access || 0, relations: relationIds, groups: groups}
        
                data.relAccess = relAccess
            }
            if (role.itemAccess) {
                let valid = checkValid(role.itemAccess.valid, mng, result)
                if (result.result) return result
                let groups2 = checkGroups(role.itemAccess.groups, mng, result)
                if (result.result) return result
                let fromItems = await checkFromItems(role.itemAccess.fromItems, context, result)
                if (result.result) return result
                const itemAccess = { valid: valid, fromItems: fromItems, access: role.itemAccess.access || 0, groups: groups2 }
                
                data.itemAccess = itemAccess
            }
            if (role.otherAccess) data.otherAccess = role.otherAccess
            if (role.options != null) data.options = role.options
            data.updatedBy = context.getCurrentUser()!.login
            await sequelize.transaction(async (t) => {
                await data.save({transaction: t})
            })

            result.id = ""+data.id
            result.result = ImportResult.UPDATED
        } 
    } catch (error) {
        result.addError(new ReturnMessage(0, ""+error))
        result.result = ImportResult.REJECTED
        logger.error(error)
    }

    return result
}

/*
mutation { import(
    config: {
        mode: CREATE_UPDATE
        errors: PROCESS_WARN
    },
    types: [
        {
            delete: false
            identifier: "tst",
            parentIdentifier: "",
            name: {ru: "test type2"}
            icon: "folder",
            iconColor: "red"
        }
    ]
    ) {
    types {
	  identifier
	  result
	  id
	  errors { code message }
	  warnings { code message }
    }}}
    
*/
export async function importType(context: Context, config: IImportConfig, type: ITypeImportRequest): Promise<ImportResponse> {
    const result = new ImportResponse(type.identifier)

    if (!type.identifier || !/^[A-Za-z0-9_-]*$/.test(type.identifier)) {
        result.addError(ReturnMessage.WrongIdentifier)
        result.result = ImportResult.REJECTED
        return result
    }

    try {
        const mng = ModelsManager.getInstance().getModelManager(context.getCurrentUser()!.tenantId)
        if (type.delete) {
            const typeNode = mng.getTypeByIdentifier(type.identifier)
            if (!typeNode) {
                result.addError(ReturnMessage.TypeNotFound)
                result.result = ImportResult.REJECTED
            } else {
                if (typeNode.getChildren().length > 0) {
                    result.addError(ReturnMessage.TypeDeleteFailed)
                    result.result = ImportResult.REJECTED
                } else {
                    const parentNode = typeNode.getParent()!
                    parentNode.deleteChild(typeNode)
        
                    const type:Type = typeNode.getValue()

                    const nId = type.id
                    // check Roles
                    const tst4 = mng.getRoles().find(role => role.itemAccess.valid.includes(nId))
                    // check Relations
                    //const tst3 = await Relation.applyScope(context).findOne({
                    //    where: {[Op.or]: [{sources: { [Op.contains]: nId}}, {targets: { [Op.contains]: nId}}]}
                    //})
                    const tst3 = await Relation.applyScope(context).findOne({
                        where: {[Op.or]: [literal("sources @> '"+nId+"'"), literal("targets @> '"+nId+"'")]}
                    })
                    // check Attributes
                    // const tst2 = await Attribute.applyScope(context).findOne({where: {valid: { [Op.contains]: nId}}})
                    const tst2 = await Attribute.applyScope(context).findOne({where: literal("valid @> '"+nId+"'")})
                    // check Items
                    const tst1 = await Item.applyScope(context).findOne({where: {typeId: nId}})
                    // check Linked types
                    const tst5 = mng.getTypeByLinkId(nId)

                    if (tst1 || tst2 || tst3 || tst4 || tst5) {
                        result.addError(ReturnMessage.TypeCanNotDelete)
                        result.result = ImportResult.REJECTED
                        return result
                    }

                    type.updatedBy = context.getCurrentUser()!.login
                    type.identifier = type.identifier + '_d_' + Date.now() 
                    await sequelize.transaction(async (t) => {
                        await type.save({transaction: t})
                        await type.destroy({transaction: t})
                    })
        
                    result.result = ImportResult.DELETED
                }
            }
            return result
        }

        const typeNode = mng.getTypeByIdentifier(type.identifier)
        if (config.mode === ImportMode.CREATE_ONLY) {
            if (typeNode) {
                result.addError(ReturnMessage.TypeExist)
                result.result = ImportResult.REJECTED
                return result
            }
        } else if (config.mode === ImportMode.UPDATE_ONLY) {
            if (!typeNode) {
                result.addError(ReturnMessage.TypeNotFound)
                result.result = ImportResult.REJECTED
                return result
            }
        }        

        if (!typeNode) {
            // create
            let parentNode: TreeNode<any> | null = null
            if (type.parentIdentifier) {
                parentNode = mng.getTypeByIdentifier(type.parentIdentifier)
                if (!parentNode) {
                    result.addError(ReturnMessage.TypeParentNotFound)
                    result.result = ImportResult.REJECTED
                    return result
                }
            }

            let link:Type
            if (type.linkIdentifier) {
                link = mng.getTypeByIdentifier(type.linkIdentifier)?.getValue()
                if (!link) {
                    result.addError(ReturnMessage.TypeLinkNotFound)
                    result.result = ImportResult.REJECTED
                    return result
                }
            }

            const results:any = await sequelize.query("SELECT nextval('types_id_seq')", { 
                type: QueryTypes.SELECT
            });
            const newId = (results[0]).nextval

            let path = '' + newId
            let parentId = null
            if (parentNode) {
                parentId = parentNode?.getValue().id
                while(parentNode != mng.getRoot()) {
                    path = parentNode!.getValue().id + '.' + path
                    parentNode = parentNode!.getParent()
                }
            }

            let images = checkRelations(type, mng, result)
            if (result.result) return result

            let mainImageId = 0
            if (type.mainImage) {
                const tst = mng.getRelationByIdentifier(type.mainImage)
                if (!tst) {
                    result.addWarning(ReturnMessage.RelationNotFound)
                } else {
                    mainImageId = tst.id;
                }
            }

            const data = await sequelize.transaction(async (t) => {
                return await Type.create ({
                    id: newId,
                    path: path,
                    identifier: type.identifier,
                    icon: type.icon,
                    iconColor: type.iconColor,
                    tenantId: context.getCurrentUser()!.tenantId,
                    createdBy: context.getCurrentUser()!.login,
                    updatedBy: context.getCurrentUser()!.login,
                    name: type.name || null,
                    link: link ? link.id : 0,
                    file: type.file != null ? type.file : false,
                    mainImage: mainImageId,
                    images: images,
                    options: type.options ?  type.options : []
                }, {transaction: t})
            })
            mng.addType(parentId, data)

            result.id = ""+data.id
            result.result = ImportResult.CREATED
        } else {
            // update
            if (type.parentIdentifier) {
                result.addWarning(ReturnMessage.TypeUpdateParent);
                if (config.errors == ErrorProcessing.WARN_REJECTED) {
                    result.result = ImportResult.REJECTED
                    return result
                }
            }

            if (type.linkIdentifier) {
                result.addWarning(ReturnMessage.TypeUpdateLink);
                if (config.errors == ErrorProcessing.WARN_REJECTED) {
                    result.result = ImportResult.REJECTED
                    return result
                }
            }

            const data: Type = typeNode.getValue()
            if (type.name) data.name = type.name
            if (type.icon) data.icon = type.icon
            if (type.iconColor) data.iconColor = type.iconColor
            if (type.file != null) data.file = type.file

            if (type.images) {
                data.images = checkRelations(type, mng, result)
                if (result.result) return result
            }

            if (type.mainImage) {
                const tst = mng.getRelationByIdentifier(type.mainImage)
                if (!tst) {
                    result.addWarning(ReturnMessage.RelationNotFound)
                } else {
                    data.mainImage = tst.id;
                }
            }
            if (type.options != null) data.options = type.options

            data.updatedBy = context.getCurrentUser()!.login
            await sequelize.transaction(async (t) => {
                await data.save({transaction: t})
            })

            result.id = ""+data.id
            result.result = ImportResult.UPDATED
        } 
    } catch (error) {
        result.addError(new ReturnMessage(0, ""+error))
        result.result = ImportResult.REJECTED
        logger.error(error)
    }

    return result
}

export async function importUser(context: Context, config: IImportConfig, user: IUserImportRequest): Promise<ImportResponse> {
    const result = new ImportResponse(user.login)

    if (!user.login || !/^[@.A-Za-z0-9_]*$/.test(user.login)) {
        result.addError(ReturnMessage.WrongLogin)
        result.result = ImportResult.REJECTED
        return result
    }

    try {
        const mng = ModelsManager.getInstance().getModelManager(context.getCurrentUser()!.tenantId)
        const idx = mng.getUsers().findIndex(elem => elem.getUser().login === user.login)
        if (user.delete) {
            if (idx === -1) {
                result.addError(ReturnMessage.UserNotFound)
                result.result = ImportResult.REJECTED
            } else {
                const data = mng.getUsers()[idx].getUser()
                const wrapper = mng.getUsers()[idx]
                const adminRole = wrapper.getRoles().find(role => role.identifier === 'admin')
                if (adminRole) {
                    // check that we has another user with admin role
                    // const tst:User = await User.findOne({where: {id: {[Op.ne]:data.id}, roles: {[Op.contains]: adminRole.id}}})
                    const tst = await User.findOne({where: {[Op.and]:[{id: {[Op.ne]:data.id}}, literal("roles @> '"+adminRole.id+"'")]}})
                    if (!tst) {
                        result.addError(ReturnMessage.UserDeleteFailed)
                        result.result = ImportResult.REJECTED
                        return result
                    }
                }
    
                data.updatedBy = context.getCurrentUser()!.login
                data.login = data.login + '_d_' + Date.now() 
                await sequelize.transaction(async (t) => {
                    await data.save({transaction: t})
                    await data.destroy({transaction: t})
                })
    
                mng.getUsers().splice(idx, 1)
                                
                result.result = ImportResult.DELETED
            }
            return result
        }

        if (config.mode === ImportMode.CREATE_ONLY) {
            if (idx !== -1) {
                result.addError(ReturnMessage.UserExist)
                result.result = ImportResult.REJECTED
                return result
            }
        } else if (config.mode === ImportMode.UPDATE_ONLY) {
            if (idx === -1) {
                result.addError(ReturnMessage.UserNotFound)
                result.result = ImportResult.REJECTED
                return result
            }
        }        

        if (idx === -1) {
            // create
            const tst = await User.findOne({where: {login: user.login}})
            if (tst) {
                result.addError(ReturnMessage.UserExist)
                result.result = ImportResult.REJECTED
                return result
            }

            let roleIds = checkRoles(user.roles, mng, result)
            if (result.result) return result

            const data = await sequelize.transaction(async (t) => {
                return await User.create({
                    tenantId: context.getCurrentUser()!.tenantId,
                    createdBy: context.getCurrentUser()!.login,
                    updatedBy: '',
                    login: user.login,
                    name: user.name,
                    password: await bcrypt.hash("password", 10),
                    email: user.email,
                    roles: roleIds,
                    props: user.props || {},
                    options: user.options ?  user.options : []
                  }, {transaction: t});
            })

            const userRoles = roleIds.map((roleId: number) => mng!.getRoles().find(role => role.id === roleId)!)
            mng.getUsers().push(new UserWrapper(data, userRoles));

            result.id = ""+data.id
            result.result = ImportResult.CREATED
        } else {
            // update
            const wrapper = mng.getUsers()[idx]
            const data = wrapper.getUser()

            if (user.name) data.name = user.name
            if (user.email) data.email = user.email
            if (user.props) data.props = user.props

            if (user.roles) {
                let roleIds = checkRoles(user.roles, mng, result)
                if (result.result) return result
                data.roles = roleIds

                const userRoles = roleIds.map((roleId: number) => mng!.getRoles().find(role => role.id === roleId)!)
                wrapper.setRoles(userRoles)
            }

            if (user.options != null) data.options = user.options
            data.updatedBy = context.getCurrentUser()!.login
            await sequelize.transaction(async (t) => {
                await data.save({transaction: t})
            })

            result.id = ""+data.id
            result.result = ImportResult.UPDATED
        } 
    } catch (error) {
        result.addError(new ReturnMessage(0, ""+error))
        result.result = ImportResult.REJECTED
        logger.error(error)
    }

    return result
}