# API merge data requests
# 1) Merge: Update tables with new primary id
# 2) Delete data: Delete linked data records
# 3) Delete primary: Delete primary record
# 4) Rules: Execute auto update for merge rules

render = api

{
    sql = SELECT '' FROM DUAL WHERE '{{mergeMode:SE:alnumx}}' = 'merge'

    # Handle update and delete of non primary data
    {
        sql = SELECT '' FROM DUAL WHERE '{{queryType:SE:alnumx}}' = 'update'

        # Build SET statement with given columnSearch as a list. pIdHead, pIdSupervisor =>   pIdHead = '{{primaryId:S0}}', pIdSupervisor = '{{primaryId:S0}}'
        {
            sql = SELECT build_dynamic_columns('{{columnSearch:CE:alnumx}}', NULL, NULL, 'setQuery', NULL) AS _setQuery
        }
        {
            sql = SELECT CONCAT({{columnSearch:CE:alnumx}}) AS _previousIds FROM {{tableName:CE:alnumx}} WHERE FIND_IN_SET(id, '{{columnIdList:CE:alnumx}}')
        }
        {
            sql = UPDATE {{tableName:CE:alnumx}} SET {{setQuery:RE::-}} WHERE FIND_IN_SET(id, '{{columnIdList:CE:alnumx}}')
        }
        {
            sql = SELECT CONCAT("echo 'Merge triggered by: {{feUser:TE}} - UPDATE {{tableName:CE:alnumx}} SET {{setQuery:RE::-}} WHERE FIND_IN_SET(id, `{{columnIdList:CE:alnumx}}`) - Previous id(s): {{previousIds:RE}}' >> {{mergeLog:Y}}") AS _exec
        }
    }

    {
        sql = DELETE FROM {{tableName:CE:alnumx}} WHERE FIND_IN_SET(id, '{{columnIdList:CE:alnumx}}') AND '{{queryType:SE:alnumx}}' = 'delete'
    }

    {
        sql = SELECT CONCAT("echo 'Delete triggered by: {{feUser:TE}} - DELETE FROM {{tableName:CE:alnumx}} WHERE FIND_IN_SET(id, `{{columnIdList:CE:alnumx}}`) AND `{{queryType:SE:alnumx}}` = `delete`' >> {{mergeLog:Y}}") AS _exec
             FROM DUAL WHERE '{{queryType:SE:alnumx}}' = 'delete'
    }

    {
        sql = SELECT '{{primaryId:S0}}', ',{{columnSearch:CE:alnumx}}', ',{{tableName:CE:alnumx}},', '"{{columnIdList:CE:alnumx}}",', '{{queryType:SE:alnumx}},', NOW()
                   , 'Content-Type: application/json\ncustom-header: fooBar' AS _apiResponseHeader
    }
}

# Update rules
{
    sql = SELECT '' FROM DUAL WHERE '{{mergeMode:SE:alnumx}}' = 'updateRules'

    {
        sql = CALL auto_insert_merge_rules('{{dbNameData:Y}}')
    }

    {
        sql = SELECT CONCAT("echo 'Update rules triggered by: {{feUser:TE}} - CALL auto_insert_merge_rules(`{{dbNameData:Y}}`)' >> {{mergeLog:Y}}") AS _exec
    }

    {
        sql = SELECT 'Create new Merge data: {{dbNameData:Y}},', NOW()
                   , 'Content-Type: application/json\ncustom-header: fooBar' AS _apiResponseHeader
    }
}

# Delete primary data
{
    sql = SELECT '' FROM DUAL WHERE '{{mergeMode:SE:alnumx}}' = 'deleteData'

    {
        sql = DELETE FROM {{primaryTable:SE:alnumx}} WHERE id = '{{primaryId:S0}}'
    }

    {
        sql = SELECT CONCAT("echo 'Delete primary data triggered by: {{feUser:TE}} - DELETE FROM {{primaryTable:SE:alnumx}} WHERE id = `{{primaryId:S0}}`' >> {{mergeLog:Y}}") AS _exec
    }

    {
        sql = SELECT 'Deleted data ID from {{primaryTable:SE:alnumx}}: {{primaryId:S0}},', NOW()
                 , 'Content-Type: application/json\ncustom-header: fooBar' AS _apiResponseHeader
    }
}