--------------------------------------------------------
--  DDL for Package Body PLANTA_DATA_DICTIONARY
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "PLANTA_DATA_DICTIONARY" 
as

procedure clear
is
begin
    planta_tmp_tables.truncate_;
end clear;


-- temp tables need to be filled in the order we intend to retrieve data later
procedure load_all_tables
is
begin
    execute immediate 'truncate table planta_tables';
    insert into planta_tables
        select
            di000265 id,
            di000216 name,
            nvl(di004269, 0) has_dummy
        from dt415 pl_dt
        where not pl_dt.di000216 = '400'
        -- ensure table exists and is not a view
        and exists (
            select 1
            from user_tables ut
            where ut.table_name = pl_dt.di000216
        )
        and not exists (
            select 1
            from user_mviews uv
            where uv.owner = user
            and uv.mview_name = pl_dt.di000216
        ) order by di000216;
end load_all_tables;


procedure load_enabled_tables
is
begin
    execute immediate 'truncate table planta_tables';
    insert into planta_tables
        select
            di000265 id,
            di000216 name,
            nvl(di004269, 0) has_dummy
        from dt415 pl_dt
        where not pl_dt.di000216 = '400'
        -- ensure table exists and is not a view
        and exists (
            select 1
            from user_tables ut
            where ut.table_name = pl_dt.di000216
        )
        and not exists (
            select 1
            from user_mviews uv
            where uv.owner = user
            and uv.mview_name = pl_dt.di000216
        )
        -- ensure table being enabled
        and pl_dt.enabled = '1'
        and exists (
            select 1
            from dt420 pl_schema
            where pl_schema.di000289 = pl_dt.di000266
            and pl_schema.enabled = '1'
        ) order by di000216;
end load_enabled_tables;


procedure load_columns
is
begin
    execute immediate 'truncate table planta_columns';
    insert into planta_columns
        select
            di000238 id,
            di000215 name,
            di000239 table_id,
            nvl(di000241, 0) db_pos,
            nvl(di002461, 0) sql_flags
        from dt412 di
        where di000275 = '0'
        and enabled = '1'
        and nvl(di002461, 0) < 4
        and exists (
            select 1
            from planta_tables dt
            where dt.id = di.di000239
        ) order by di000239, di000241;
end load_columns;


procedure load_relations
is
begin
    execute immediate 'truncate table planta_relations';

    insert into planta_relations
        select
            di000389 table_id,
            di000277 r_table_id,
            di000279 relation,
            di000374 recursion,
            di000951 name,
            di000278 first_key_di,
            di000363 last_key_di,
            ( select c1.db_pos from planta_columns c1
              where c1.id = relation.di000278 -- first_key_di
            ) as first_db_pos,
            ( select c2.db_pos from planta_columns c2
              where c2.id = relation.di000363 -- last_key_di
            ) as last_db_pos
        from dt416 relation
        where (relation.di000277 != '--' AND relation.DI000277 IS NOT NULL)
        and exists (
            select 1
            from planta_tables dt
            where dt.id = relation.di000389
        )
        and exists (
            select 1
            from planta_tables dt
            where dt.id = relation.di000277
        )
        and exists (
            select 1
            from planta_columns di
            where di.id = relation.di000278
        )
        and exists (
            select 1
            from planta_columns di
            where di.id = relation.di000363
        ) order by di000389, di000277, di000278;
end load_relations;


procedure create_views
is
begin
    planta_tmp_tables.create_;

    execute immediate '
    create or replace view planta_alias as
    select
        id,
        name,
        table_id, (
            select db_pos from planta_columns c2
            where c2.table_id = c1.table_id
            and c2.name = c1.name
            and c2.sql_flags < ''2''
        ) as db_pos,
        sql_flags
    from planta_columns c1
    order by c1.table_id asc, c1.db_pos asc';



    execute immediate '
    create or replace view planta_pks as
    select * from planta_relations r
    where ( -- PK by definition:
        r.r_table_id = r.table_id
        and r.relation = ''1:1''
        and r.recursion = ''0''
    )
    order by r.table_id, r.r_table_id, r.first_key_di';

    execute immediate '
    create or replace view planta_fks as
    select * from planta_relations r
    where not ( -- PK definition negated
        r.r_table_id = r.table_id
        and r.relation = ''1:1''
        and r.recursion = ''0''
    )
    order by r.table_id, r.r_table_id, r.first_key_di';

    -- null can occur in FK columns where the foreign table used to have dummy records
    execute immediate '
    create or replace view planta_null_fks as
    select * from planta_fks fk
    where exists (
      select 1 from planta_tables t
      where t.id = fk.r_table_id
      and t.has_dummy = 1
    )
    order by fk.table_id, fk.r_table_id, fk.first_key_di';



    execute immediate '
    create or replace view planta_pk_columns as
    select * from planta_columns c
    where exists (
        select 1 from planta_pks pk
        where c.table_id = pk.table_id
        and c.db_pos between pk.first_db_pos and pk.last_db_pos
    ) order by c.table_id asc, c.db_pos asc';

    execute immediate '
    create or replace view planta_fk_columns as
    select * from planta_columns c
    where exists (
        select 1 from planta_fks fk
        where c.table_id = fk.table_id
        and c.db_pos between fk.first_db_pos and fk.last_db_pos
    ) order by c.table_id asc, c.db_pos asc';

    -- null can occur in FK columns where the foreign table used to have dummy records
    execute immediate '
    create or replace view planta_fk_nulls as
    select * from planta_columns c
    where exists (
        select 1 from planta_null_fks fk
        where c.table_id = fk.table_id
        and c.db_pos between fk.first_db_pos and fk.last_db_pos
    ) order by c.table_id asc, c.db_pos asc';



    execute immediate '
    create or replace view planta_pk_columns_unused_by_fk as
    select * from planta_pk_columns pk
    where not exists (
        select 1 from planta_fk_columns fk
        where fk.table_id = pk.table_id
        and fk.name = pk.name
    ) order by pk.table_id asc, pk.db_pos asc';

    execute immediate '
    create or replace view planta_fk_columns_unused_by_pk as
    select * from planta_fk_columns fk
    where not exists (
        select 1 from planta_pk_columns pk
        where pk.table_id = fk.table_id
        and pk.name = fk.name
    ) order by fk.table_id asc, fk.db_pos asc';

    -- null can occur in FK columns where the foreign table used to have dummy records
    execute immediate '
    create or replace view planta_fk_nulls_unused_by_pk as
    select * from planta_fk_nulls fk
    where not exists (
        select 1 from planta_pk_columns pk
        where pk.table_id = fk.table_id
        and pk.name = fk.name
    ) order by fk.table_id asc, fk.db_pos asc';



end create_views;

procedure load_all
is
begin
    clear;
    load_all_tables;
    load_columns;
    load_relations;
end load_all;


procedure load_enabled
is
begin
    clear;
    load_enabled_tables;
    load_columns;
    load_relations;
end load_enabled;


end planta_data_dictionary;
