/**
  @file
  @brief migration script to move from v5 to v6.5 of data controller

**/

%let dclib=YOURDCLIB;

libname &dclib "/your/dc/path";

/**
  * Change 1
  * New MPE_SUBMIT table
  */
proc sql;
create table &dclib..mpe_xlmap_rules(
        tx_from num not null,
        XLMAP_ID char(32) not null,
        XLMAP_RANGE_ID char(32) not null,
        XLMAP_SHEET char(32) not null,
        XLMAP_START char(1000) not null,
        XLMAP_FINISH char(1000),
        tx_to num not null,
    constraint pk_mpe_xlmap_rules
        primary key(tx_from,XLMAP_ID,XLMAP_RANGE_ID));

create table &dclib..MPE_XLMAP_DATA(
        LOAD_REF char(32) not null,
        XLMAP_ID char(32) not null,
        XLMAP_RANGE_ID char(32) not null,
        ROW_NO num not null,
        COL_NO num not null,
        VALUE_TXT char(4000),
    constraint pk_MPE_XLMAP_DATA
      primary key(LOAD_REF, XLMAP_ID, XLMAP_RANGE_ID, ROW_NO, COL_NO));

create table &dclib..mpe_xlmap_info(
        tx_from num not null,
        XLMAP_ID char(32) not null,
        XLMAP_DESCRIPTION char(1000) not null,
        XLMAP_TARGETLIBDS char(41) not null,
        tx_to num not null,
    constraint pk_mpe_xlmap_info
        primary key(tx_from,XLMAP_ID));


/* add mpe_tables entries */
  insert into &dclib..mpe_tables
    set tx_from=0
      ,tx_to='31DEC5999:23:59:59'dt
      ,libref="&dclib"
      ,dsn='MPE_XLMAP_INFO'
      ,num_of_approvals_required=1
      ,loadtype='TXTEMPORAL'
      ,var_txfrom='TX_FROM'
      ,var_txto='TX_TO'
      ,buskey='XLMAP_ID'
      ,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
      ,post_edit_hook='services/hooks/mpe_xlmap_info_postedit'
    ;
  insert into &dclib..mpe_tables
    set tx_from=0
      ,tx_to='31DEC5999:23:59:59'dt
      ,libref="&dclib"
      ,dsn='MPE_XLMAP_RULES'
      ,num_of_approvals_required=1
      ,loadtype='TXTEMPORAL'
      ,var_txfrom='TX_FROM'
      ,var_txto='TX_TO'
      ,buskey='XLMAP_ID XLMAP_RANGE_ID'
      ,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
      ,post_edit_hook='services/hooks/mpe_xlmap_rules_postedit'
    ;
  insert into &dclib..mpe_tables
    set tx_from=0
      ,tx_to='31DEC5999:23:59:59'dt
      ,libref="&dclib"
      ,dsn='MPE_XLMAP_DATA'
      ,num_of_approvals_required=1
      ,loadtype='UPDATE'
      ,buskey='LOAD_REF XLMAP_ID XLMAP_RANGE_ID ROW_NO COL_NO'
      ,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
    ;