--------------------------------------------------------
--  DDL for Package Body PLANTA_CONSTRAINTS
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "PLANTA_CONSTRAINTS" 
as

type cnameArray is table of user_cons_columns.column_name%type
                   index by binary_integer;

sql_stmt           varchar2(32767);
use_dbms_output    boolean default FALSE;
preserve_rowid     boolean default TRUE;


procedure add( s in varchar2 )
is
begin
    if ( use_dbms_output ) then
        dbms_output.put_line( '    ' || s );
    else
        dbms_output.put_line( '    ' || s );
        sql_stmt := sql_stmt || chr(10) || s;
    end if;
end add;


procedure execute_immediate
as
    exec_cursor     integer default dbms_sql.open_cursor;
    rows_processed  number  default 0;
begin
    if ( use_dbms_output ) then
        dbms_output.put_line( '/' );
    else
        dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
        rows_processed := dbms_sql.execute(exec_cursor);
        dbms_output.put_line( '/' );
    end if;
    dbms_sql.close_cursor( exec_cursor );
    sql_stmt := NULL;
exception
    when others then
      dbms_output.put_line('Error while executing or parsing this statement:' || chr(10));
      dbms_output.put_line(sql_stmt);
      dbms_output.put_line(SQLERRM(SQLCODE())||chr(10));
      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      sql_stmt := NULL;
      raise;
end;


procedure get_dt_name
  ( p_table_id            in     dt415.di000265%type,
    p_table_sqlname          out user_constraints.table_name%type )
is
begin
    select name
    into p_table_sqlname
    from planta_tables
    where id = p_table_id;

exception
    when no_data_found then
        dbms_output.put_line('Could not find DT # '||p_table_id||' hence cannot return proper DB table name.' || chr(9));
        Null;
end get_dt_name;


procedure drop_legacy_index
  ( p_table_name      in     user_constraints.table_name%type,
    p_table_id        in     dt415.di000265%type,
    p_relation_name   in     dt416.di000951%type,
    p_prefix          in     varchar2 )
is
    l_index_name      user_constraints.constraint_name%type default p_prefix||p_relation_name||'DT'||p_table_id;
begin
    if l_index_name is not null then
        sql_stmt := NULL;

        for idx in (
            select index_name, table_name
            from user_indexes
            where table_name = p_table_name
            and index_name = l_index_name
        ) loop
            add('drop index "'||idx.index_name||'"');
            execute_immediate;
        end loop;
    end if;
exception
    when no_data_found then
        null;
end drop_legacy_index;


procedure drop_key_index
  ( p_table_name          in     user_indexes.table_name%type,
    p_index_name          in     user_indexes.index_name%type )
is
begin
    if p_index_name is not null then
        sql_stmt := NULL;

        for key_idx in (
            select index_name, table_name
            from user_indexes
            where (
                table_name = p_table_name and
                index_name = p_index_name
            )
        ) loop
            add('drop index "'||key_idx.index_name||'"');
            execute_immediate;
        end loop;
    end if;
exception
    when no_data_found then
        null;
end drop_key_index;


procedure drop_key_constraints
  ( p_constraint_type     in     user_constraints.constraint_type%type )
is
begin
    for c in (
        select
            table_name,
            constraint_name,
            index_name
        from user_constraints super
        where constraint_type = p_constraint_type
        and exists (
            select 1
            from planta_tables sub
            where sub.name = super.table_name
        )
    ) loop
        sql_stmt := NULL;
        add('alter table "'||c.table_name||'"');
        add('  drop constraint "'||c.constraint_name||'"');
        execute_immediate;

        drop_key_index(c.table_name, c.index_name);

    end loop;

exception
    when no_data_found then
        null;
end drop_key_constraints;


procedure drop_all_pk_constraints
is
    l_table_name user_constraints.table_name%type;
begin
    dbms_output.enable(1000000);

    drop_key_constraints('P');

    for r in (
      select name, table_id, r_table_id
      from planta_relations
      where relation = '1:1'
      and r_table_id = table_id
      and recursion = '0'
    ) loop
      get_dt_name(r.table_id, l_table_name);
      drop_legacy_index(l_table_name, r.table_id, r.name, 'P');
    end loop;

end drop_all_pk_constraints;


procedure drop_all_fk_constraints
is
    l_table_name user_constraints.table_name%type;
begin
    dbms_output.enable(1000000);

    drop_key_constraints('R');

    for r in (
      select name, table_id, r_table_id
      from planta_relations
      where not (
        relation = '1:1'
        and r_table_id = table_id
        and recursion = '0'
      )

    ) loop
      get_dt_name(r.table_id, l_table_name);
      drop_legacy_index(l_table_name, r.table_id, r.name, 'S');
    end loop;

end drop_all_fk_constraints;


procedure create_pk_constraint
  ( p_table_name          in     user_constraints.table_name%type,
    p_pkey_names          in     cnameArray )
is
    l_constraint_name            user_constraints.constraint_name%type;
    l_comma                      char(1) default ' ';
begin
    l_constraint_name := p_table_name||'_PK';

    drop_key_index(p_table_name, l_constraint_name);

    sql_stmt := NULL;
    add('alter table "'||p_table_name||'"');
    add('    add constraint "'||l_constraint_name||'" primary key (');

    l_comma := ' ';
    for i in 1 .. 16
    loop
        begin
            add('    '||l_comma||'"'||p_pkey_names(i)||'"');
            l_comma := ',';
        exception
            when no_data_found then exit;
        end;
    end loop;
    add(')');
    execute_immediate;

end create_pk_constraint;


procedure create_fk_constraint
  ( p_table_name          in     user_constraints.table_name%type,
    p_index_table_name    in     user_constraints.table_name%type,
    p_fkey_names          in     cnameArray,
    p_index_pkey_names    in     cnameArray,
    p_recursion_type      in     dt416.di000374%type,
    p_relation_name       in     dt416.di000951%type )
is
    l_constraint_name            user_constraints.constraint_name%type;
    l_comma                      char(1) default ' ';
begin
    l_constraint_name := p_table_name||'_FK_'||p_index_table_name||'_r'||p_recursion_type||'_'||p_relation_name;

    drop_key_index(p_table_name, l_constraint_name);

    sql_stmt := NULL;
    add('alter table "'||p_table_name||'"');
    add('add constraint "'||l_constraint_name||'" foreign key (');

    l_comma := ' ';
    for i in 1 .. 16
    loop
        begin
            add('    '||l_comma||'"'||p_fkey_names(i)||'"');
            l_comma := ',';
        exception
            when no_data_found then exit;
        end;
    end loop;

    add(') references "'||p_index_table_name||'" (');

    l_comma := ' ';
    for i in 1 .. 16
    loop
        begin
            add('    '||l_comma||'"'||p_index_pkey_names(i)||'"');
            l_comma := ',';
        exception
            when no_data_found then exit;
        end;
    end loop;
    
    add(')');

    if p_recursion_type = 2 then
        add(' on delete set null');
    end if;

    add(' deferrable initially immediate');

    execute_immediate;

exception
    when others then
        Null;
end create_fk_constraint;


procedure get_all_key_columns
  ( p_table_id            in     dt415.di000265%type,
    p_index_table_id      in     dt415.di000265%type,
    p_first_key_di        in     dt412.di000238%type,
    p_pkey_names             out cnameArray )
is
begin
    declare i number := 1;

    begin
        for di in (
            select name
            from planta_columns c
            where c.table_id = p_table_id
            and exists (
                select 1 from planta_relations r
                where r.table_id = p_table_id
                and r.r_table_id = p_index_table_id
                and r.first_key_di = p_first_key_di
                and c.db_pos between r.first_db_pos and r.last_db_pos
            )
            order by
                c.db_pos asc
        ) loop
            p_pkey_names( i ) := di.name;
            i := i + 1;
        end loop;
    end;

exception
    when no_data_found then
        null;

end get_all_key_columns;


procedure get_fk_columns
  ( p_table_id            in     dt415.di000265%type,
    p_index_table_id      in     dt415.di000265%type,
    p_first_key_di        in     dt412.di000238%type,
    p_pkey_names             out cnameArray )
is
begin
    get_all_key_columns(p_table_id, p_index_table_id, p_first_key_di, p_pkey_names);
end get_fk_columns;


procedure get_fk_nulls_unused_in_pk
  ( p_table_id            in     dt415.di000265%type,
    p_index_table_id      in     dt415.di000265%type,
    p_first_key_di        in     dt412.di000238%type,
    p_pkey_names             out cnameArray )
is
begin
    declare i number := 1;

    begin
        for di in (
            select name
            from planta_fk_nulls_unused_by_pk c
            where c.table_id = p_table_id
            and exists (
                select 1 from planta_fks fk
                where fk.table_id = p_table_id
                and fk.r_table_id = p_index_table_id
                and fk.first_key_di = p_first_key_di
                and c.db_pos between fk.first_db_pos and fk.last_db_pos
            )
            order by
                c.db_pos asc
        ) loop
            p_pkey_names( i ) := di.name;
            i := i + 1;
        end loop;
    end;

exception
    when no_data_found then
        null;

end get_fk_nulls_unused_in_pk;


procedure get_pk_columns
  ( p_table_id            in     dt415.di000265%type,
    p_pkey_names             out cnameArray )
is
    l_first_key_di               dt416.di000278%type;
begin
    select first_key_di
    into l_first_key_di
    from planta_relations
    where table_id = p_table_id
    and relation = '1:1'
    and r_table_id = table_id
    and recursion = '0';

    get_all_key_columns(p_table_id, p_table_id, l_first_key_di, p_pkey_names);
exception
    when no_data_found then
        dbms_output.put_line('Could not find PK for DT#'||p_table_id||' hence cannot create list of key DIs.');
end get_pk_columns;


procedure build_fk_constraint
  ( p_table_name          in     user_constraints.table_name%type,
    p_table_id            in     dt415.di000265%type,
    p_index_table_name    in     user_constraints.table_name%type,
    p_index_table_id      in     dt415.di000265%type,
    p_first_key_di        in     dt412.di000238%type,
    p_recursion_type      in     dt416.di000374%type,
    p_relation_name       in     dt416.di000951%type )
is
    l_fkey_names                 cnameArray;
    l_index_pkey_names           cnameArray;
begin
    get_fk_columns(p_table_id, p_index_table_id, p_first_key_di, l_fkey_names);
    get_pk_columns(p_index_table_id, l_index_pkey_names);
    create_fk_constraint(
        p_table_name,
        p_index_table_name,
        l_fkey_names, l_index_pkey_names,
        p_recursion_type, p_relation_name);
end build_fk_constraint;


procedure build_fk_constraints
  ( p_table_name          in     dt415.di000216%type,
    p_table_id            in     dt415.di000265%type )
is
    l_r_table_name               user_constraints.table_name%type;
begin
    dbms_output.put_line(chr(10)||chr(10)||'-- adding FK constraints for DT #'||p_table_id||' a.k.a. '||p_table_name||chr(10));

    for fk in (
        select
            r_table_id,
            first_key_di,
            recursion,
            name
        from planta_relations
        where table_id = p_table_id
        and not (
            relation = '1:1'
            and r_table_id = table_id
            and recursion = '0'
            )
    ) loop
        dbms_output.enable(1000000);
        get_dt_name(fk.r_table_id, l_r_table_name);
        build_fk_constraint(p_table_name, p_table_id,
            l_r_table_name, fk.r_table_id,
            fk.first_key_di, fk.recursion, fk.name);
    end loop;
end build_fk_constraints;


procedure build_pk_constraint
  ( p_table_name          in     dt415.di000216%type,
    p_table_id            in     dt415.di000265%type )
is
    l_pkey_names                 cnameArray;
begin
    dbms_output.put_line(chr(10)||chr(10)||'-- adding PK constraint for DT #'||p_table_id||' a.k.a. '||p_table_name||chr(10));
    get_pk_columns(p_table_id, l_pkey_names);
    create_pk_constraint(p_table_name, l_pkey_names);
end build_pk_constraint;


procedure on_all_tables
  ( p_preserve_rowid      in     boolean default TRUE,
    p_use_dbms_output     in     boolean default FALSE )
is
    p_table_name                 VARCHAR(28); -- we need space for the padding

begin
    use_dbms_output := p_use_dbms_output;
    preserve_rowid  := p_preserve_rowid;

    -- populate temporary tables with ALL planta tables and their surroundings for cleansing
    planta_data_dictionary.load_all;

    -- clean out previous FKs and PKs
    drop_all_fk_constraints;
    drop_all_pk_constraints;

    -- re-populate temporary tables with ENABLED planta tables and their surroundings for rebuilding
    planta_data_dictionary.load_enabled;

    -- create PKs and FKs from scratch according to DT 416
    for t in (
        SELECT
            id,
            name
        FROM planta_tables
        ORDER BY
            id asc
    ) loop
        dbms_output.enable(1000000);
        build_pk_constraint( t.name, t.id );
    end loop;

    for t in (
        SELECT
            id,
            name
        FROM planta_tables
        ORDER BY
            id asc
    ) loop
        dbms_output.enable(1000000);
        build_fk_constraints( t.name, t.id );
    end loop;

    planta_data_dictionary.clear;

end on_all_tables;


--


procedure disable_not_null_constraints
is
begin
    for pk_di in (
        select t.name table_, c.name column_
        from planta_tables t
        inner join planta_fk_nulls_unused_by_pk c
        on t.id = c.table_id
    ) loop
        planta_helpers.disable_not_null_constraint(pk_di.table_, pk_di.column_);
    end loop;
end disable_not_null_constraints;


procedure get_dummy_value
  ( p_table_name          in     user_constraints.table_name%type,
    p_column_name         in     user_cons_columns.column_name%type,
    p_dummy_value         in out char )
is
begin
  select (
    case
      when data_type = 'VARCHAR2' then
        '-'
      when data_type = 'NUMBER' then
        '0'
      else
        null
      end
  ) as dummy_value
  into p_dummy_value
  from user_tab_columns
  where table_name = p_table_name
  and column_name = p_column_name;
end get_dummy_value;


procedure clean_out_dummy_values
  ( p_table_name          in     user_constraints.table_name%type,
    p_index_table_name    in     user_constraints.table_name%type,
    p_fkey_names          in     cnameArray,
    p_all_fkey_names      in     cnameArray,
    p_index_pkey_names    in     cnameArray,
    p_recursion_type      in     dt416.di000374%type,
    p_relation_name       in     dt416.di000951%type )
is
    l_comma                      char(1) default ' ';
    l_and                        char(8) default 'where';
    l_dummy_value                char(1) default NULL;
begin
    sql_stmt := NULL;
    add('update "'||p_table_name||'" child set ');

    l_comma := ' ';
    for i in 1 .. 16
    loop
        begin
            add('   '||l_comma||'"'||p_fkey_names(i)||'" = null');
            l_comma := ',';
        exception
            when no_data_found then exit;
        end;
    end loop;

    add('where not exists (');
    add('    select 1 from "'||p_index_table_name||'" parent');

    l_and := 'where';
    for i in 1 .. 16
    loop
        begin
            get_dummy_value(p_index_table_name, p_index_pkey_names(i), l_dummy_value);
            add('    '||l_and||'parent."'||p_index_pkey_names(i)||'" = child."'||p_all_fkey_names(i)||'"');
            l_and := 'and';
            add('    and not parent."'||p_index_pkey_names(i)||'" = '''||l_dummy_value||''' ');
        exception
            when no_data_found then exit;
        end;
    end loop;

    add(')');
    execute_immediate;

exception
    when others then
        Null;

end clean_out_dummy_values;


procedure clean_out_dummy_reference
  ( p_table_name          in     user_constraints.table_name%type,
    p_table_id            in     dt415.di000265%type,
    p_index_table_name    in     user_constraints.table_name%type,
    p_index_table_id      in     dt415.di000265%type,
    p_first_key_di        in     dt412.di000238%type,
    p_recursion_type      in     dt416.di000374%type,
    p_relation_name       in     dt416.di000951%type )
is
    l_fkey_names                 cnameArray;
    l_all_fkey_names             cnameArray;
    l_pkey_names                 cnameArray;
    l_index_pkey_names           cnameArray;
begin
    get_fk_nulls_unused_in_pk(p_table_id, p_index_table_id, p_first_key_di, l_fkey_names);
    get_fk_columns(p_table_id, p_index_table_id, p_first_key_di, l_all_fkey_names);
    get_pk_columns(p_index_table_id, l_index_pkey_names);
    clean_out_dummy_values(
        p_table_name,
        p_index_table_name,
        l_fkey_names, l_all_fkey_names, l_index_pkey_names,
        p_recursion_type, p_relation_name);
end clean_out_dummy_reference;


procedure clean_out_dummy_references
  ( p_table_name          in     dt415.di000216%type,
    p_table_id            in     dt415.di000265%type )
is
    l_r_table_name               user_constraints.table_name%type;
begin
    dbms_output.put_line(chr(10)||chr(10)||'-- null-ing empty FK references for DT #'||p_table_id||' a.k.a. '||p_table_name||chr(10));

    for fk in (
        select
            r_table_id,
            first_key_di,
            recursion,
            name
        from planta_null_fks r
        where table_id = p_table_id
        and exists (
            select 1
            from planta_fk_nulls_unused_by_pk c
            where c.table_id = r.table_id
            and c.db_pos between r.first_db_pos and r.last_db_pos
        )
    ) loop
        dbms_output.enable(1000000);
        get_dt_name(fk.r_table_id, l_r_table_name);
        clean_out_dummy_reference(p_table_name, p_table_id,
            l_r_table_name, fk.r_table_id,
            fk.first_key_di, fk.recursion, fk.name);
    end loop;
end clean_out_dummy_references;


procedure clean_out_dummy_record
  ( p_table_name          in     user_constraints.table_name%type,
    p_pkey_names          in     cnameArray )
is
    l_tmp                        user_cons_columns.column_name%type;
    l_dummy_value                char(1) default NULL;
    l_comma                      char(1) default ' ';
    l_and                        char(8) default 'where';
begin
    sql_stmt := NULL;
    add('delete from "'||p_table_name||'"');

    for i in 1 .. 16
    loop
        begin
            get_dummy_value(p_table_name, p_pkey_names(i), l_dummy_value);
            add( l_and||'"'||p_pkey_names(i)||'" = '''||l_dummy_value||'''' );
            l_and:= 'and ';
        exception
            when no_data_found then
              exit;
        end;
    end loop;
    execute_immediate;

exception
  when others then
    null;

end clean_out_dummy_record;


procedure clean_out_dummy
  ( p_table_name          in     dt415.di000216%type,
    p_table_id            in     dt415.di000265%type )
is
    l_pkey_names                 cnameArray;
begin
    dbms_output.put_line(chr(10)||chr(10)||'-- deleting dummy record for DT #'||p_table_id||' a.k.a. '||p_table_name||chr(10));
    get_pk_columns(p_table_id, l_pkey_names);
    clean_out_dummy_record(p_table_name, l_pkey_names);
end clean_out_dummy;


procedure remove_dummy_records
is
    p_table_name                 VARCHAR(28); -- we need space for the padding

begin
    use_dbms_output := FALSE;
    preserve_rowid  := TRUE;

    -- populate temporary tables with ALL planta tables and their surroundings for cleansing
    planta_data_dictionary.load_all;

    disable_not_null_constraints;

    for t in (
        SELECT
            id,
            name
        FROM planta_tables
        ORDER BY
            id asc
    ) loop
        dbms_output.enable(1000000);
        clean_out_dummy_references( t.name, t.id );
    end loop;

    execute immediate 'set constraints all deferred';

    for t in (
        SELECT
            id,
            name
        FROM planta_tables
        -- leaving this out won't hurt since it would remove
        -- a dummy from a table where none should exist
        -- WHERE has_dummy = 1
        ORDER BY
            id asc
    ) loop
        dbms_output.enable(1000000);
        clean_out_dummy( t.name, t.id );
    end loop;

    execute immediate 'set constraints all immediate';

    planta_data_dictionary.clear;

end remove_dummy_records;

end planta_constraints;
