-- create tables
create table inv_location (
    id                            number         generated by default on null as identity
                                                 constraint inv_location_pk primary key,
    name                          varchar2(255)  not null,
    description                   varchar2(4000),
    created_on                    date           not null,
    created_by                    varchar2(255)  not null,
    updated_on                    date           not null,
    updated_by                    varchar2(255)  not null,
    row_version                   integer        not null )
/

create table inv_product (
    id                            number         generated by default on null as identity
                                                 constraint inv_product_pk primary key,
    name                          varchar2(255)  not null,
    description                   varchar2(4000),
    created_on                    date           not null,
    created_by                    varchar2(255)  not null,
    updated_on                    date           not null,
    updated_by                    varchar2(255)  not null,
    row_version                   integer        not null )
/

create table inv_stock (
    id                            number         generated by default on null as identity
                                                 constraint inv_stock_pk primary key,
    location_id                   number         not null
                                                 constraint inv_location_fk
                                                 references inv_location ( id )
                                                 on delete cascade,
    product_id                    number         not null
                                                 constraint inv_product_fk
                                                 references inv_product ( id )
                                                 on delete cascade,
    quantity                      number         not null,
    created_on                    date           not null,
    created_by                    varchar2(255)  not null,
    updated_on                    date           not null,
    updated_by                    varchar2(255)  not null,
    row_version                   integer        not null )
/

-- table index
create index inv_location_a_indx_1 on inv_location ( name )
/

create index inv_product_a_indx_1 on inv_product ( name )
/

create index inv_stock_a_indx_1 on inv_stock ( location_id )
/

create index inv_stock_a_indx_2 on inv_stock ( product_id )
/

-- triggers
create or replace trigger inv_location_biu
    before insert or update
    on inv_location
    for each row
begin
    :new.updated_on := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    if inserting then
        :new.row_version := 1;
        :new.created_on := :new.updated_on;
        :new.created_by := :new.updated_by;
    elsif updating then
        :new.row_version := nvl(:old.row_version, 0) + 1;
    end if;
end inv_location_biu;
/

create or replace trigger inv_product_biu
    before insert or update
    on inv_product
    for each row
begin
    :new.updated_on := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    if inserting then
        :new.row_version := 1;
        :new.created_on := :new.updated_on;
        :new.created_by := :new.updated_by;
    elsif updating then
        :new.row_version := nvl(:old.row_version, 0) + 1;
    end if;
end inv_product_biu;
/

create or replace trigger inv_stock_biu
    before insert or update
    on inv_stock
    for each row
begin
    :new.updated_on := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    if inserting then
        :new.row_version := 1;
        :new.created_on := :new.updated_on;
        :new.created_by := :new.updated_by;
    elsif updating then
        :new.row_version := nvl(:old.row_version, 0) + 1;
    end if;
end inv_stock_biu;
/