# Merge data feature:
# 1) List all data over given main query
# 2) Switchable views between data and merge rules
# 3) Merge view with functions to manipulate data
# Querys for debug reasons currently hardcoded

form = {{form:SE}}

# Initialize view mode: rules, merge
{
    sql = SELECT IF('{{viewMode:SE:alnumx}}' != '', '{{viewMode:SE:alnumx}}', IF('{{viewMode:UE}}' != '', '{{viewMode:UE}}', 'merge')) AS '_=viewMode'
}

# Prepare switch button for view mode
{
    sql = SELECT CONCAT('p:{{pageSlug:T}}?viewMode=', IF('{{viewMode:UE}}' = 'rules', 'merge|t:Data', 'rules|t:Rules'), '|s|b|G:glyphicon-refresh') AS '_link|viewModeBtn|_hide'
}

{
    sql = SELECT '{{action:SE}}' AS _action, '{{id1:S0}}' AS _id1, '{{id2:S0}}' AS _id2, '{{tableName:SE}}' AS _tableName, '{{column1:SE}}' AS _column1, '{{column2:SE}}' AS _column2
}

# Sip for update rule request
{
    sql = SELECT 'U:uid=mergeDataApi&mergeMode=updateRules|s|r:8' AS '_link|dataSipRules|_hide'
}

# Merge mode
{
    sql = SELECT '' FROM DUAL WHERE '{{viewMode:UE}}' = 'merge'

    # Prepare merge view variables
    {
        sql = SELECT '' FROM DUAL WHERE '{{action:RE}}' = 'mergeView'

        # Purge id and column if no data found
        {
            sql = SELECT '' FROM {{tableName:RE}} WHERE id = {{id1:R0}}
            altsql = SELECT 0 AS _id1, '' AS _column1
        }

        {
            sql = SELECT '' FROM {{tableName:RE}} WHERE id = {{id2:R0}}
            altsql = SELECT 0 AS _id2, '' AS _column2
        }
    }

    # Initialize view elements
    {
        sql = SELECT IF('{{id1:R0}}' = 0, 'AND 1 = 0', '') AS _notZeroQuery1
                    , IF('{{id2:R0}}' = 0, 'AND 1 = 0', '') AS _notZeroQuery2
                    , IF('{{id1:R0}}' = 0, 'btn-success', 'btn-default') AS _btnClass1
                    , IF('{{id2:R0}}' = 0, 'btn-success', 'btn-default') AS _btnClass2
                    , IF('{{id1:R0}}' = 0, '', '<option value="{{id1:R0}}">{{id1:R0}}</option>') AS _selectOption1
                    , IF('{{id2:R0}}' = 0, '', '<option value="{{id2:R0}}">{{id2:R0}}</option>') AS _selectOption2
                    , IF('{{id2:R0}}' = 0, 'col-md-10', 'col-md-5') AS _colmdData1
                    , IF('{{id2:R0}}' = 0, 'col-md-0 hidden', 'col-md-5') AS _colmdData2
                    , IF('{{id2:R0}}' = 0, ", 'Column2'", ", 'Column2'") AS _column2Header /*To early and currently not used*/
                    , IF('{{id2:R0}}' = 0, ", 'Key'", ", 'Key'") AS _columnKeyHeader /*To early and currently not used*/
    }

    # Initialize query selector
    {
        sql = SELECT id AS _grIdMergeQuery
                FROM Grp
                WHERE reference = 'qfq_merge_query'
    }

    {
        sql = SELECT IF('{{queryId:C0}}' = 0, grMergeQuery.id, '{{queryId:C0}}') AS _queryId, '' AS _querySelector
                FROM (SELECT 1) AS fake
                LEFT JOIN Grp AS grMergeQuery
                  ON grMergeQuery.grId = '{{grIdMergeQuery:R0}}'
                ORDER BY grMergeQuery.name
                LIMIT 1
    }

    # Initialize new and edit query buttons
    {
        sql = SELECT CONCAT('p:{{pageSlug:T}}?form=mergeQueryForm&r=0|s|b|N') AS '_link|newQueryBtn|_hide'
                    , CONCAT('p:{{pageSlug:T}}?form=mergeQueryForm&r={{queryId:R0}}|s|b|E'
                              , IF('{{queryId:R0}}' = 0, '|r:3', '')) AS '_link|editQueryBtn|_hide'
    }

    {
        sql = SELECT IF(grMergeQuery.id = '{{queryId:R0}}', 'selected', '') AS _selected
                     , grMergeQuery.id AS _curQueryId
                     , grMergeQuery.name AS _curQueryName
                FROM Grp AS grMergeQuery
                WHERE grMergeQuery.grId = '{{grIdMergeQuery:R0}}'
        altsql = SELECT CONCAT('<option selected>No merge query found</option>') AS _querySelector

        {
            sql = SELECT CONCAT('{{querySelector:RE}}', '<option {{selected:RE}} value="{{curQueryId:R0}}">{{curQueryName:RE}}</option>') AS _querySelector
        }
    }

    {
        sql = SELECT grMergeQuery.value AS _mergeDataQuery
                      , grMergeQuery.comment AS _mergeDataQueryNote
                FROM Grp AS grMergeQuery
                WHERE grMergeQuery.id = '{{queryId:R0}}'
    }

    {
        sql = SELECT CONCAT('<span>Query: </span><select class="btn btn-default" name="{{pageSlug:T}}_mergeView"
                            id="grMergeQueryId" title="Select query" onchange="var selValue = document.getElementById(\'grMergeQueryId\').value;
                                location.href = \'{{baseUrl:Y}}{{pageSlug:T}}?queryId=\' + selValue;">', '{{querySelector:RE}}', '</select>') AS _querySelector
                      , IF('{{mergeDataQueryNote:RE}}' = '', 'Query note empty', '{{mergeDataQueryNote:RE}}') AS _mergeDataQueryNote
                      , IF('{{mergeDataQueryNote:RE}}' = '', 'style="height: 1.5lh;"', '') AS _mergeDataQueryNoteHeight
    }

    {
        sql = SELECT 'IDs', 'Column1' {{columnKeyHeader:RE::-}} {{column2Header:RE::-}}
                FROM DUAL WHERE '{{action:RE}}' != 'mergeView'
        head = <div class="merge-window panel panel-info" data-base-url="{{baseUrl:Y}}" data-sip-rules="{{&dataSipRules:RE}}"><div class="panel-heading"><div style="display: inline-flex; align-items: center; margin-left: 10px;"><h2 style="margin: 0; height: fit-content; margin-right: 10px;">Merge: Data</h2>{{&viewModeBtn:RE::-}}</div></div><div class="panel-body"><div class="qfq-merge-query-box-selector">{{querySelector:RE::-}} {{&editQueryBtn:RE::-}} {{&newQueryBtn:RE::-}}</div><div class="qfq-merge-query-note"><textarea {{mergeDataQueryNoteHeight:RE::-}} readonly>{{mergeDataQueryNote:RE}}</textarea></div><table id="{{pageSlug:T}}-merge-data" class="table qfq-table-50 narrow-table tablesorter tablesorter-filter tablesorter-pager" data-tablesorter-config='{"theme":"bootstrap","widthFixed":true,"headerTemplate":"{content} {icon}","dateFormat":"ddmmyyyy","widgets":["uitheme","filter","saveSort","columnSelector","output"],"widgetOptions":{"filter_saveFilters":false, "saveSort":false,"filter_columnFilters":true,"filter_reset":".reset","filter_cssFilter":"form-control","columnSelector_mediaquery":false,"output_delivery":"download","output_saveFileName":"tableExport.csv","output_separator":";"} }'><thead>
        tail = </tbody></table></div></div>
        rbeg = <tr>
        rend = </tr></thead><tbody>
        fbeg = <th>
        fend = </th>

        {
            sql = {{mergeDataQuery:RE::-}}

            {
                sql = SELECT CONCAT('p:{{pageSlug:T}}?action=mergeView&id1={{id1:R0}}&id2={{id2:R0}}&tableName={{tableName:RE}}'
                                    , '&column1={{column1:RE}}&column2={{column2:RE}}|s|b|t:Merge ({{id1:R0}}'
                                    , IF('{{id2:R0}}' = 0, '', ',{{id2:R0}}'), ')') AS _link
                               , '{{column1:RE}}'
                               , '{{key:RE}}'
                               , '{{column2:RE}}'
                        FROM {{tableName:RE}} AS item
                        WHERE item.id = '{{id1:R0}}'
                rbeg = <tr>
                rend = </tr>
                fbeg = <td>
                fend = </td>
            }
        }
    }

    # Merge view
    {
        sql = SELECT '' FROM DUAL WHERE '{{action:RE}}' = 'mergeView'

        head = <div class="merge-window panel panel-info" data-base-url="{{baseUrl:Y}}" data-sip-rules="{{&dataSipRules:RE}}">
                  <div class="merge-window-top panel-heading">
                    <div class="merge-button-box pull-left">
                      <h2 class="merge-window-title">{{tableName:RE}}</h2>
                      <label for="primaryId">Primary ID (keep):</label>
                      <select name="primaryId" id="primaryId">{{selectOption1:RE::-}}{{selectOption2:RE::-}}</select>
                    </div>
                    <div class="merge-button-box pull-right"></div>
                    <div style="clear:both;"></div>
                  </div>
                  <div class="col-md-12 merge-body panel-body">
        tail =    </div>
                  <div style="clear:both;"></div>
                </div>

        # Create sips update and delete
        {
            sql = SELECT 'U:uid=mergeDataApi&queryType=update&primaryId={{id1:R0}}&mergeMode=merge|s|r:8' AS '_link|dataSipUpdate1|_hide'
                           , 'U:uid=mergeDataApi&queryType=delete&primaryId={{id1:R0}}&mergeMode=merge|s|r:8' AS '_link|dataSipDelete1|_hide'
                           , 'U:uid=mergeDataApi&queryType=update&primaryId={{id2:R0}}&mergeMode=merge|s|r:8' AS '_link|dataSipUpdate2|_hide'
                           , 'U:uid=mergeDataApi&queryType=delete&primaryId={{id2:R0}}&mergeMode=merge|s|r:8' AS '_link|dataSipDelete2|_hide'
                           , 'U:uid=mergeDataApi&mergeMode=deleteData&primaryTable={{tableName:RE}}&primaryId={{id1:R0}}|s|r:8' AS '_link|dataSipDeleteP|_hide'
                           , 'U:uid=mergeDataApi&mergeMode=deleteData&primaryTable={{tableName:RE}}&primaryId={{id2:R0}}|s|r:8' AS '_link|dataSipDeleteP2|_hide'
        }

        # Merge body head
        {
            sql = SELECT ''
            head = <div class='col-md-12 merge-body-head'>
            tail = </div><div class='col-md-12 merge-body-check-all'><div class="col-md-2"></div><div class="col-md-5"><input type="checkbox" checked name="checkAll" id="{{id1:R0}}"> <label for="checkAll">Check all</label></div><div class="col-md-5"><input type="checkbox" name="checkAll" id="{{id2:R0}}"> <label for="checkAll">Check all</label></div></div>

            # Empty column
            {
                sql = SELECT '<div class="col-md-2"></div>'
            }

            # Item 1 data
            {
                sql = SELECT '<div class="{{colmdData1:RE}} merge-table-data-head"><div><div><button class="btn {{btnClass1:RE}}" disabled="disabled" data-primary="{{id1:R0}}" data-sip="{{&dataSipDeleteP:RE}}" data-sip-update="{{&dataSipUpdate1:RE}}" data-sip-delete="{{&dataSipDelete1:RE}}"><b>Data ID: {{id1:R0}}</b> <i class="fas fa-trash-alt"></i></button></div><div>{{column1:RE}}</div></div></div>'
            }

            # Item 2 data
            {
                sql = SELECT '<div class="{{colmdData2:RE}} merge-table-data-head"><div><div><button class="btn {{btnClass2:RE}}" disabled="disabled" data-primary="{{id2:R0}}" data-sip="{{&dataSipDeleteP2:RE}}" data-sip-update="{{&dataSipUpdate2:RE}}" data-sip-delete="{{&dataSipDelete2:RE}}"><b>Data ID: {{id2:R0}}</b> <i class="fas fa-trash-alt"></i></button></div><div>{{column2:RE}}</div></div></div>'
            }
        }

        # Generate merge body tables. Iterate over all existing tables. Get data from merge table.
        {
            sql = SELECT grMergeRule.name AS _tableNameS
                       , grMergeRule.comment AS _comment
                       , grMergeRule.value3 AS _columnSearch
                       , grMergeRule.value AS _requiredQuery
                       , grMergeRule.value2 AS _columnShow
                       , '' AS _columnSearch1
                       , '' AS _columnSearch2
                       , '' AS _formattedColumns
                       , '' AS _foundInColumns1
                       , '' AS _foundInColumns2
                       , 0 AS _dataFound
                  FROM Grp AS grMergeRule, Grp AS grType
                  WHERE grMergeRule.grId = grType.id
                    AND grType.reference = 'qfq_merge_rule'

            # Initialize columnSearch. Example: pId, xId -> {{id1:R0}} = pId OR {{id1:R0}} = xId
            {
                sql = SELECT CONCAT('{{id1:R0}} = ', REPLACE('{{columnSearch:RE}}', ',', ' OR {{id1:R0}} = ')) AS _columnSearch1
            }

            {
                sql = SELECT CONCAT('{{id2:R0}} = ', REPLACE('{{columnSearch:RE}}', ',', ' OR {{id2:R0}} = ')) AS _columnSearch2
            }

            # Initialize columnShow list. Format from: id, title > "id: ", id, "<br>", "title: ", title
            {
                sql = SELECT build_dynamic_columns('{{columnShow:RE}}', '{{tableNameS:RE}}', '{{dbNameData:Y}}', NULL, NULL) AS _formattedColumns
            }

            {
                sql = SELECT build_dynamic_columns('{{columnSearch:RE}}', '{{tableNameS:RE}}', '{{dbNameData:Y}}', 'columnSearch', 'id1:R0') AS _foundInColumns1
            }

            {
                sql = SELECT build_dynamic_columns('{{columnSearch:RE}}', '{{tableNameS:RE}}', '{{dbNameData:Y}}', 'columnSearch', 'id2:R0') AS _foundInColumns2
            }

            # Data found flag
            {
                sql = SELECT 1 AS _dataFound
                       FROM {{tableNameS:RE}}
                       WHERE (({{columnSearch1:RE}}) {{notZeroQuery1:RE}}
                         OR ({{columnSearch2:RE}}) {{notZeroQuery2:RE}})
                         {{requiredQuery:RE::-}}
                       LIMIT 1
            }

            # Data from id1
            {
                sql = SELECT CONCAT('<div class="merge-table-wrap" title="', {{foundInColumns1:RE::-}}, '"><div class="merge-table-inline-wrap bg-success"><div class="merge-table-checkbox"><input type="checkbox" checked data-id="{{id1:R0}}" data-table="{{tableNameS:RE}}" data-columns="{{columnSearch:RE}}"  id="', id, '" data-column-found="', {{foundInColumns1:RE::-}}, '"></div><div class="merge-table-content merge-table-content-closed">', {{formattedColumns:RE::-}},'</div></div><div class="merge-table-open btn btn-block"><i class="fas fa-angle-double-down"></i></div></div>')
                        FROM {{tableNameS:RE}}
                        WHERE ({{columnSearch1:RE}}) {{notZeroQuery1:RE}} {{requiredQuery:RE::-}}
                head = <div class="col-md-12 merge-body-table"><div class="col-md-2"><b><h2 style="margin: 0;">{{tableNameS:RE}}</h2></b><i>column search: {{columnSearch:RE}}</i><br>{{comment:RE}}</div><div class="{{colmdData1:RE}} merge-table-data">
                tail = </div>

                altsql = SELECT '<div class="col-md-12 merge-body-table"><div class="col-md-2"><b><h2 style="margin: 0;">{{tableNameS:RE}}</h2></b><i>column search: {{columnSearch:RE}}</i><br>{{comment:RE}}</div><div class="{{colmdData1:RE}} merge-table-data"></div>'
                           FROM DUAL WHERE {{dataFound:R0}}
            }

            # Data from id2
            {
                sql = SELECT CONCAT('<div class="merge-table-wrap" title="', {{foundInColumns2:RE::-}}, '"><div class="merge-table-inline-wrap bg-danger"><div class="merge-table-checkbox"><input type="checkbox" data-id="{{id2:R0}}" data-table="{{tableNameS:RE}}" data-columns="{{columnSearch:RE}}" id="', id, '" data-column-found="', {{foundInColumns2:RE::-}}, '"></div><div class="merge-table-content merge-table-content-closed">', {{formattedColumns:RE::-}}, '</div></div><div class="merge-table-open btn btn-block"><i class="fas fa-angle-double-down"></i></div></div>')
                        FROM {{tableNameS:RE}}
                        WHERE ({{columnSearch2:RE}}) {{notZeroQuery2:RE}} {{requiredQuery:RE::-}}
                head = <div class="{{colmdData2:RE}} merge-table-data">
                tail = </div></div>

                altsql = SELECT '<div class="{{colmdData2:RE}} merge-table-data"></div></div>'
                           FROM DUAL WHERE {{dataFound:R0}}
            }

        }

        # Merge body footer
        {
            sql = SELECT '<div class="col-md-12 merge-body-footer"><div class="merge-body-footer-button-box"><button class="btn btn-default" id="merge-window-btn">Merge</button></div></div>'
        }
    }
}

# Rules mode
{
    sql = SELECT '' FROM DUAL WHERE '{{viewMode:UE}}' = 'rules'

    {
        sql = SELECT 'p:{{pageSlug:T}}?form=mergeRuleForm&r=0|s|b|N' AS '_link|newRuleRecordBtn|_hide'
    }

    {
        sql = SELECT '{{&newRuleRecordBtn:RE}}', 'Tablename', 'Column search', 'Comment', 'Required query', 'Column show', 'Enabled'
        head = <div class="merge-window panel panel-info" data-base-url="{{baseUrl:Y}}" data-sip-rules="{{&dataSipRules:RE}}"><div class="panel-heading"><div style="display: inline-flex; align-items: center; margin-left: 10px;"><h2 style="margin: 0; height: fit-content; margin-right: 10px;">Merge: Rules</h2>{{&viewModeBtn:RE::-}} <button class="btn btn-default" id="merge-window-update-rule-btn">Create Rules</button></div></div><div class="panel-body"><input type="hidden" data-sip-rules="{{&dataSipRules:RE}}"><table id="{{pageSlug:T}}-merge-data-rules" class="table qfq-table-50 narrow-table tablesorter tablesorter-filter tablesorter-pager tablesorter-column-selector" data-tablesorter-config='{"theme":"bootstrap","widthFixed":true,"headerTemplate":"{content} {icon}","dateFormat":"ddmmyyyy","widgets":["uitheme","filter","saveSort","columnSelector","output"],"widgetOptions":{"filter_saveFilters":false, "saveSort":false,"filter_columnFilters":true,"filter_reset":".reset","filter_cssFilter":"form-control","columnSelector_mediaquery":false,"output_delivery":"download","output_saveFileName":"tableExport.csv","output_separator":";"} }'><thead>
        tail = </tbody></table></div></div>
        rbeg = <tr>
        rend = </tr></thead><tbody>
        fbeg = <th>
        fend = </th>

        {
            sql = SELECT CONCAT('p:{{pageSlug:T}}?form=mergeRuleForm&r=', grMergeRule.id, '|s|b|E|t:', grMergeRule.id) AS _link
                         , grMergeRule.name /*table name*/
                         , grMergeRule.value3 /*columnSearch*/
                         , grMergeRule.comment
                         , grMergeRule.value /*required query*/
                         , grMergeRule.value2 /*column show*/
                         , IF(grMergeRule.enabled = 'yes', '<i class="fas fa-check"></i>', '')
                    FROM Grp AS grMergeRule, Grp AS grType
                    WHERE grMergeRule.grId = grType.id
                      AND grType.reference = 'qfq_merge_rule'
            rbeg = <tr>
            rend = </tr>
            fbeg = <td>
            fend = </td>
        }
    }
}
