---

create or replace package body MDG_MAILING_GROUPS_PKG is
  BULK_LIMIT  constant pls_integer := 1000;
  ERROR       constant varchar2(6) := 'Error';
  -- -------------------------
  g_debug boolean := false;
  -- -------------------------
  column_not_exist exception;
  pragma exception_init(column_not_exist, -904);
  -- -------------------------
  function fn(p in varchar2) return varchar2 is
  begin
    return sys_context('USERENV','CURRENT_SCHEMA')||'.'||$$plsql_unit||'.'||p;
  end;

  function quoted(p in varchar2) return varchar2 is
  begin
    return '"'||p||'"';
  end;
  -- -------------------------
  procedure debug(p_msg in varchar2) is
  begin
    if g_debug then
      dbms_output.put_line(p_msg);
    end if;
  end;
  -- -------------------------
  procedure enableDebug  is begin g_debug := true;  end;
  procedure disableDebug is begin g_debug := false; end;
  -- -------------------------
  procedure importStage(p_stage varchar2) is
  begin
    debug('Stage: '||p_stage);
    xxmdg.mdgControl.importStage(p_stage);
  end;
  -- -------------------------
  procedure importError(p_message varchar2, p_key varchar2 := null) is
  begin
    debug(ERROR||' message: '||p_message);
    debug(ERROR||' key: '||p_key);
    xxmdg.mdgControl.importError(p_message, p_key);
  end;
  -- -------------------------
  function checkProductGroup9(p_group9 in varchar2) return varchar2 is
    l_cnt pls_integer;
  begin
    if p_group9 is null then
      return ERROR||': productGroup9 не найдена';
    end if;

    select count(*)
      into l_cnt
      from xxmdg."productNormal" t
     where t."productGroup9" = p_group9;

    if l_cnt = 0 then
      return ERROR||': productGroup9 не найдена';
    end if;

    return 'ok';
  end;
  -- -------------------------
  function loadExcelData return boolean is
    subtype column_value_t is varchar2(4000);
    subtype column_name_t  is varchar2(4000);
    type columnTable  is table of column_value_t;
    type rowIdTable   is table of pls_integer;
    type columnsTable is table of columnTable index by column_name_t;
    type loadTable    is table of mdg_aspect_to_risk_group_mapping_excel%rowtype;

    l_cr           pls_integer;
    l_cv           varchar2(4000);
    l_columns_data columnsTable;
    l_row_ids      rowIdTable;
    l_load_table   loadTable := loadTable();
    l_is_processed boolean := false;

    parseCursor    sys_refcursor;

    function toStr(p_s varchar2) return varchar2 is
    begin
      l_cv := p_s;
      return p_s;
    end;
    -- -------------------------
    function appendToLoad(p_i pls_integer) return boolean is
      l_row mdg_aspect_to_risk_group_mapping_excel%rowtype;

      function strValue(p_col varchar2) return varchar2 is
      begin
        return nvl(toStr(l_columns_data(p_col)(p_i)), null);
      end;
    begin
      l_cr := l_row_ids(p_i);
      debug('row: '||l_cr);

      l_row."productGroupNine" := strValue('productGroupNine');
      if checkProductGroup9(l_row."productGroupNine") <> 'ok' then
        raise_application_error(-20001, ERROR||': productGroup9 не найдена');
      end if;

      l_row."productClassLimits" := strValue('productClassLimits');

      l_load_table.extend;
      l_load_table(l_load_table.last) := l_row;
      return true;

    exception
      when others then
        importError('On row '||l_cr||' value "'||l_cv||'" error: '||sqlerrm);
        return false;
    end;
    -- -------------------------
    function openCursor return boolean is
      l_base_query  clob;
      l_query       clob;
      l_add_columns varchar2(32767);
    begin
      l_base_query := xxmdg.mdgControl.parsedContentQuery;

      l_add_columns :=
          quoted('productGroupNine')  ||', '||
          quoted('productClassLimits');

      l_query := 'select row_id,'||l_add_columns||' from ('||l_base_query||')';
      open parseCursor for l_query;
      return true;
    exception
      when column_not_exist then
        importError('One of columns ('||l_add_columns||') not exists in dictionary.');
        return false;
      when others then
        importError('Unexpected error during openCursor: '||sqlerrm);
        return false;
    end;
    -- -------------------------
    procedure fetchCursor is
    begin
      fetch parseCursor bulk collect into
            l_row_ids,
            l_columns_data('productGroupNine'),
            l_columns_data('productClassLimits')
            limit BULK_LIMIT;
    end;
    -- -------------------------
    function processCursor return boolean is
      l_row_appended boolean;
      l_error_exists boolean := false;
    begin
      for i in 1..l_row_ids.count loop
        l_row_appended := appendToLoad(i);
        if not l_row_appended then
          l_error_exists := true;
        end if;
      end loop;

      if not l_error_exists then
        forall j in 1..l_load_table.count
          insert into mdg_aspect_to_risk_group_mapping_excel values l_load_table(j);
      end if;

      l_load_table.delete;
      return not l_error_exists;
    end;

  begin
    importStage('Preprocessing of parsed data.');
    savepoint before_mdg_aspect_to_risk_group_mapping_excel;

    if not openCursor then
      return false;
    end if;

    loop
      fetchCursor;
      exit when l_row_ids.count = 0;
      l_is_processed := processCursor;
    end loop;

    close parseCursor;
    return l_is_processed;

  exception
    when others then
      rollback to before_mdg_aspect_to_risk_group_mapping_excel;
      importError('Unexpected error during loadExcelData: '||sqlerrm);
      return false;
  end;
  -- -------------------------
  procedure updateDictionary is
    l_batch_id etl.batchId;
  begin
    importStage('Loading data to document '||DOCUMENT_NAME);
    l_batch_id := etl.startBatch('Update '||DOCUMENT_NAME||' (Excel data)',
                                 etl.BATCH_BLOCK_UPDATE);
    etl.lockDocumentBy(DOCUMENT_NAME, l_batch_id);
    etl.updateWarehouse(
      p_batch_id           => l_batch_id,
      p_document_name      => DOCUMENT_NAME,
      p_source_dsn         => 'xxmdg.mdg_aspect_to_risk_group_mapping_excel',
      p_update_mode        => etl.UPDATE_REPLACE
    );
    etl.finalizeBatch(l_batch_id);
    xxmdg.xxmdg_control_pkg.saveImportBatch(l_batch_id);
    commit;
    etl.waitBatch(l_batch_id);
    importStage('File uploaded (merge)');
  end;
  -- -------------------------
  procedure fullUpdateDictionary is
    l_batch_id etl.batchId;
  begin
    importStage('Loading data to document '||DOCUMENT_NAME||' (replace)');
    l_batch_id := etl.startBatch('Full update '||DOCUMENT_NAME||' (Excel data)',
                                 etl.BATCH_BLOCK_UPDATE);
    etl.lockDocumentBy(DOCUMENT_NAME, l_batch_id);
    etl.updateWarehouse(
      p_batch_id          => l_batch_id,
      p_document_name     => DOCUMENT_NAME,
      p_source_dsn        => 'xxmdg.mdg_aspect_to_risk_group_mapping_excel',
      p_destination_where => '1=1',
      p_update_mode       => etl.UPDATE_REPLACE
    );
    etl.finalizeBatch(l_batch_id);
    xxmdg.xxmdg_control_pkg.saveImportBatch(l_batch_id);
    commit;
    etl.waitBatch(l_batch_id);
    importStage('File uploaded (replace)');
  end;
  -- -------------------------
  procedure importFromXLSX is
  begin
    if loadExcelData then
      if xxmdg.xxmdg_control_pkg.fullUpdate then
        fullUpdateDictionary;
      else
        updateDictionary;
      end if;
    end if;
  end;
  -- -------------------------
end;
/