---

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 checkGroupName(p_group_name in varchar2) return varchar2 is
  begin
    if p_group_name is null or trim(p_group_name) is null then
      return ERROR||': groupName не задан или пустой';
    end if;
    if p_group_name <> trim(p_group_name) then
      return ERROR||': groupName содержит пробелы по краям';
    end if;
    return 'ok';
  end;
  -- -------------------------
  function checkEmail(p_email in varchar2) return varchar2 is
  begin
    if p_email is null or trim(p_email) is null then
      return ERROR||': emailAddress не задан';
    end if;
    if not regexp_like(trim(p_email),
                       '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') then
      return ERROR||': emailAddress имеет неверный формат';
    end if;
    return 'ok';
  end;
  -- -------------------------
  function checkName(p_name in varchar2) return varchar2 is
  begin
    if p_name is null or trim(p_name) is null then
      return ERROR||': имя/фамилия не заданы';
    end if;
    return 'ok';
  end;
  -- -------------------------
  function checkIsInactive(p_is_inactive in varchar2) return varchar2 is
  begin
    if p_is_inactive is null then
      return 'ok';
    end if;
    if upper(trim(p_is_inactive)) not in ('Y','N') then
      return ERROR||': isInactive допускает только Y/N или NULL';
    end if;
    return 'ok';
  end;
  -- -------------------------
  function checkDates(p_valid_from in varchar2,
                      p_valid_to   in varchar2) return varchar2 is
    l_from date;
    l_to   date;
  begin
    if p_valid_from is null or trim(p_valid_from) is null then
      return ERROR||': validFrom не задан';
    end if;
    if p_valid_to is null or trim(p_valid_to) is null then
      return ERROR||': validTo не задан';
    end if;
    begin
      l_from := to_date(p_valid_from, 'YYYY-MM-DD');
      l_to   := to_date(p_valid_to,   'YYYY-MM-DD');
    exception
      when others then
        return ERROR||': неверный формат даты (ожидается YYYY-MM-DD)';
    end;
    if l_from > l_to then
      return ERROR||': validFrom больше validTo';
    end if;
    return 'ok';
  end;
  -- -------------------------
  function checkDuplicatesInFile return boolean is
    l_query  clob;
    l_dup_g  varchar2(4000);
    l_dup_e  varchar2(4000);
    l_cnt    pls_integer;
    l_found  boolean := false;
    c        sys_refcursor;
  begin
    l_query :=
      'select lower("groupName"), lower("emailAddress"), count(*) '||
      '  from ('||xxmdg.mdgControl.parsedContentQuery||') '||
      ' group by lower("groupName"), lower("emailAddress") '||
      'having count(*) > 1';

    open c for l_query;
    loop
      fetch c into l_dup_g, l_dup_e, l_cnt;
      exit when c%notfound;
      l_found := true;
      importError(
        ERROR||': дубликат в файле (groupName, emailAddress): '||
        l_dup_g||' / '||l_dup_e||' (встречается '||l_cnt||' раз)');
    end loop;
    close c;

    return not l_found;

  exception
    when column_not_exist then
      importError('One of columns ("groupName","emailAddress") not exists in dictionary.');
      return false;
    when others then
      importError('Unexpected error during checkDuplicatesInFile: '||sqlerrm);
      return false;
  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_mailing_groups_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_mailing_groups_excel%rowtype;
      l_check varchar2(4000);

      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."groupName"    := trim(strValue('groupName'));
      l_row."emailAddress" := trim(strValue('emailAddress'));
      l_row."firstName"    := trim(strValue('firstName'));
      l_row."lastName"     := trim(strValue('lastName'));
      l_row."isInactive"   := upper(trim(strValue('isInactive')));
      l_row."validFrom"    := strValue('validFrom');
      l_row."validTo"      := strValue('validTo');

      -- Проверки полей
      l_check := checkGroupName(l_row."groupName");
      if l_check <> 'ok' then
        raise_application_error(-20001, l_check);
      end if;

      l_check := checkEmail(l_row."emailAddress");
      if l_check <> 'ok' then
        raise_application_error(-20001, l_check);
      end if;

      l_check := checkName(l_row."firstName");
      if l_check <> 'ok' then
        raise_application_error(-20001, l_check||' (firstName)');
      end if;

      l_check := checkName(l_row."lastName");
      if l_check <> 'ok' then
        raise_application_error(-20001, l_check||' (lastName)');
      end if;

      l_check := checkIsInactive(l_row."isInactive");
      if l_check <> 'ok' then
        raise_application_error(-20001, l_check);
      end if;

      l_check := checkDates(l_row."validFrom", l_row."validTo");
      if l_check <> 'ok' then
        raise_application_error(-20001, l_check);
      end if;

      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('groupName')    ||', '||
          quoted('emailAddress') ||', '||
          quoted('firstName')    ||', '||
          quoted('lastName')     ||', '||
          quoted('isInactive')   ||', '||
          quoted('validFrom')    ||', '||
          quoted('validTo');

      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('groupName'),
            l_columns_data('emailAddress'),
            l_columns_data('firstName'),
            l_columns_data('lastName'),
            l_columns_data('isInactive'),
            l_columns_data('validFrom'),
            l_columns_data('validTo')
            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_mailing_groups_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_mailing_groups_excel;

    -- 1) Дубли по всему файлу — одним запросом
    if not checkDuplicatesInFile then
      return false;
    end if;

    -- 2) Построчная обработка с проверками полей
    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_mailing_groups_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_mailing_groups_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_mailing_groups_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;
/

create or replace package MDG_MAILING_GROUPS_PKG is
  DOCUMENT_NAME constant varchar2(100) := 'MDG_MAILING_GROUPS';

  procedure enableDebug;
  procedure disableDebug;
  procedure importFromXLSX;

  -- Диагностика (публичные — для юнит-тестов)
  function checkGroupName(p_group_name in varchar2) return varchar2;
  function checkEmail(p_email in varchar2) return varchar2;
  function checkName(p_name in varchar2) return varchar2;
  function checkIsInactive(p_is_inactive in varchar2) return varchar2;
  function checkDates(p_valid_from in varchar2,
                      p_valid_to   in varchar2) return varchar2;
end;
/