1-) create tables.sql

begin
-- create tables
create table gs1_holder (
    id                             number generated by default on null as identity 
                                   constraint gs1_holder_id_pk primary key,
    firstname                      varchar2(80 char),
    surname                        varchar2(80 char),
    email                          varchar2(320 char)
)
;

create table gs1_certtype (
    id                             number generated by default on null as identity 
                                   constraint gs1_certtype_id_pk primary key,
    master                         number
                                   constraint gs1_certtype_master_fk
                                   references gs1_certtype on delete cascade,
    title                          varchar2(120 char),
    certdesc                       varchar2(4000 char),
    duration_years                 number,
    version                        number,
    valid_from                     date,
    valid_to                       date,
    learning_material              blob,
    mimetype                       varchar2(100 char),
    fname                          varchar2(100 char),
    cdate                          date,
    mdate                          date
)
;

-- table index
create index gs1_certtype_i1 on gs1_certtype (master);

create table gs1_orgtype (
    id                             number generated by default on null as identity 
                                   constraint gs1_orgtype_id_pk primary key,
    abreviation                    varchar2(3 char) constraint gs1_orgtype_abreviation_ck
                                   check (abreviation in ('GS1','COM','HE','VOC')),
    name                           varchar2(100 char)
)
;

create table gs1_organization (
    id                             number generated by default on null as identity 
                                   constraint gs1_organization_id_pk primary key,
    master                         number
                                   constraint gs1_organization_master_fk
                                   references gs1_organization on delete cascade,
    orgtype                        number
                                   constraint gs1_organization_orgtype_fk
                                   references gs1_orgtype on delete cascade,
    name                           varchar2(120 char),
    address                        varchar2(500 char),
    country                        varchar2(100 char),
    city                           varchar2(100 char),
    member_since                   date
)
;

-- table index
create index gs1_organization_i1 on gs1_organization (master);
create index gs1_organization_i112 on gs1_organization (orgtype);

create table gs1_assignment (
    id                             number generated by default on null as identity 
                                   constraint gs1_assignment_id_pk primary key,
    title                          varchar2(300 char),
    summary                        varchar2(4000 char),
    sup_material                   blob,
    mimetype                       varchar2(100 char),
    fname                          varchar2(100 char),
    cdate                          date,
    mdate                          date
)
;

create table gs1_certificate (
    id                             number generated by default on null as identity 
                                   constraint gs1_certificate_id_pk primary key,
    certtype                       number
                                   constraint gs1_certificate_certtype_fk
                                   references gs1_certtype on delete cascade,
    holder                         number
                                   constraint gs1_certificate_holder_fk
                                   references gs1_holder on delete cascade,
    issue_date                     date,
    expiration_date                date
)
;

-- table index
create index gs1_certificate_i1 on gs1_certificate (certtype);
create index gs1_certificate_i172 on gs1_certificate (holder);

create table gs1_role (
    id                             number generated by default on null as identity 
                                   constraint gs1_role_id_pk primary key,
    org                            number
                                   constraint gs1_role_org_fk
                                   references gs1_organization on delete cascade,
    assignment                     number
                                   constraint gs1_role_assignment_fk
                                   references gs1_assignment on delete cascade,
    name                           varchar2(7 char) constraint gs1_role_name_ck
                                   check (name in ('GS1','SCHOOL','COMPANY'))
)
;

-- table index
create index gs1_role_i1 on gs1_role (assignment);
create index gs1_role_i222 on gs1_role (org);

create table gs1_mentor (
    id                             number generated by default on null as identity 
                                   constraint gs1_mentor_id_pk primary key,
    org                            number
                                   constraint gs1_mentor_org_fk
                                   references gs1_organization on delete cascade,
    firstname                      varchar2(80 char),
    surname                        varchar2(80 char),
    phone_number                   varchar2(15 char),
    email                          varchar2(320 char)
)
;

-- table index
create index gs1_mentor_i1 on gs1_mentor (org);

create table gs1_ao (
    id                             number generated by default on null as identity 
                                   constraint gs1_ao_id_pk primary key,
    orgn                           number
                                   constraint gs1_ao_orgn_fk
                                   references gs1_organization on delete cascade,
    assignment                     number
                                   constraint gs1_ao_assignment_fk
                                   references gs1_assignment on delete cascade
)
;

-- table index
create index gs1_ao_i1 on gs1_ao (assignment);
create index gs1_ao_i302 on gs1_ao (orgn);

create table gs1_issuer (
    id                             number generated by default on null as identity 
                                   constraint gs1_issuer_id_pk primary key,
    certi                          number
                                   constraint gs1_issuer_certi_fk
                                   references gs1_certtype on delete cascade,
    orgn                           number
                                   constraint gs1_issuer_orgn_fk
                                   references gs1_organization on delete cascade,
    agreement_open                 date,
    agreement_close                date
)
;

-- table index
create index gs1_issuer_i1 on gs1_issuer (certi);
create index gs1_issuer_i352 on gs1_issuer (orgn);

create table gs1_ma (
    id                             number generated by default on null as identity 
                                   constraint gs1_ma_id_pk primary key,
    mentor                         number
                                   constraint gs1_ma_mentor_fk
                                   references gs1_mentor on delete cascade,
    assignment                     number
                                   constraint gs1_ma_assignment_fk
                                   references gs1_assignment on delete cascade
)
;

-- table index
create index gs1_ma_i1 on gs1_ma (assignment);
create index gs1_ma_i402 on gs1_ma (mentor);

  CREATE TABLE "GS1_HA" 
   (	"ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"HOLDER" NUMBER, 
	"ASSIGNMENT" NUMBER, 
	 CONSTRAINT "GS1_HA_ID_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   )  DEFAULT COLLATION "USING_NLS_COMP" ;

  ALTER TABLE "GS1_HA" ADD CONSTRAINT "GS1_HA_HOLDER_FK" FOREIGN KEY ("HOLDER")
	  REFERENCES "GS1_HOLDER" ("ID") ON DELETE CASCADE ENABLE;
  ALTER TABLE "GS1_HA" ADD CONSTRAINT "GS1_HA_ASSIGNMENT_FK" FOREIGN KEY ("ASSIGNMENT")
	  REFERENCES "GS1_ASSIGNMENT" ("ID") ON DELETE CASCADE ENABLE;

  CREATE INDEX "GS1_HA_I1" ON "GS1_HA" ("ASSIGNMENT") 
  ;

  CREATE INDEX "GS1_HA_I42" ON "GS1_HA" ("HOLDER") 
  ;
end;