-- Create table
create table TIMEBLOCKPERDEPARTMENT
(
  plant_code       VARCHAR2(6) not null,
  department_code  VARCHAR2(6) not null,
  timeblock_number NUMBER(10) not null,
  creationdate     DATE not null,
  shift_number     NUMBER(10) not null,
  description      VARCHAR2(30),
  mutationdate     DATE not null,
  mutator          VARCHAR2(20) not null,
  starttime1       DATE,
  endtime1         DATE,
  starttime2       DATE,
  endtime2         DATE,
  starttime3       DATE,
  endtime3         DATE,
  starttime4       DATE,
  endtime4         DATE,
  starttime5       DATE,
  endtime5         DATE,
  starttime6       DATE,
  endtime6         DATE,
  starttime7       DATE,
  endtime7         DATE
)
tablespace ABS_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TIMEBLOCKPERDEPARTMENT
  add constraint XPKTIMEBLOCKPERDEPARTMENT primary key (PLANT_CODE, DEPARTMENT_CODE, SHIFT_NUMBER, TIMEBLOCK_NUMBER)
  using index 
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TIMEBLOCKPERDEPARTMENT
  add constraint TIMEBLOCKPERDEP_DEPARTMENT foreign key (PLANT_CODE, DEPARTMENT_CODE)
  references DEPARTMENT (PLANT_CODE, DEPARTMENT_CODE);
alter table TIMEBLOCKPERDEPARTMENT
  add constraint TIMEBLOCKPERDEP_SHIFT foreign key (PLANT_CODE, SHIFT_NUMBER)
  references SHIFT (PLANT_CODE, SHIFT_NUMBER);



-- Create table
create table TIMEBLOCKPEREMPLOYEE
(
  plant_code       VARCHAR2(6) not null,
  employee_number  NUMBER(10) not null,
  shift_number     NUMBER(10) not null,
  creationdate     DATE not null,
  timeblock_number NUMBER(10) not null,
  mutationdate     DATE not null,
  description      VARCHAR2(30),
  starttime1       DATE,
  endtime1         DATE,
  mutator          VARCHAR2(20) not null,
  starttime2       DATE,
  endtime2         DATE,
  starttime3       DATE,
  endtime3         DATE,
  starttime4       DATE,
  endtime4         DATE,
  starttime5       DATE,
  endtime5         DATE,
  starttime6       DATE,
  endtime6         DATE,
  starttime7       DATE,
  endtime7         DATE
)
tablespace ABS_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TIMEBLOCKPEREMPLOYEE
  add constraint XPKTIMEBLOCKPEREMPLOYEE primary key (EMPLOYEE_NUMBER, PLANT_CODE, SHIFT_NUMBER, TIMEBLOCK_NUMBER)
  using index 
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TIMEBLOCKPEREMPLOYEE
  add constraint TIMEBLOCKPEREMP_EMPLOYEE foreign key (EMPLOYEE_NUMBER)
  references EMPLOYEE (EMPLOYEE_NUMBER);
alter table TIMEBLOCKPEREMPLOYEE
  add constraint TIMEBLOCKPEREMP_SHIFT foreign key (PLANT_CODE, SHIFT_NUMBER)
  references SHIFT (PLANT_CODE, SHIFT_NUMBER);


-- Create table
create table TIMEBLOCKPERSHIFT
(
  shift_number     NUMBER(10) not null,
  plant_code       VARCHAR2(6) not null,
  creationdate     DATE not null,
  timeblock_number NUMBER(10) not null,
  mutationdate     DATE not null,
  description      VARCHAR2(30) not null,
  mutator          VARCHAR2(20) not null,
  starttime1       DATE,
  endtime1         DATE,
  starttime2       DATE,
  endtime2         DATE,
  starttime3       DATE,
  endtime3         DATE,
  starttime4       DATE,
  endtime4         DATE,
  starttime5       DATE,
  endtime5         DATE,
  starttime6       DATE,
  endtime6         DATE,
  starttime7       DATE,
  endtime7         DATE
)
tablespace ABS_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TIMEBLOCKPERSHIFT
  add constraint XPKTIMEBLOCKPERSHIFT primary key (PLANT_CODE, SHIFT_NUMBER, TIMEBLOCK_NUMBER)
  using index 
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TIMEBLOCKPERSHIFT
  add constraint TIMEBLOCKPERSHIFT_SHIFT foreign key (PLANT_CODE, SHIFT_NUMBER)
  references SHIFT (PLANT_CODE, SHIFT_NUMBER);
  
-- Create table
create table EMPLOYEEPLANNING
(
  plant_code            VARCHAR2(6) not null,
  employeeplanning_date DATE not null,
  shift_number          NUMBER(10) not null,
  department_code       VARCHAR2(6) not null,
  workspot_code         VARCHAR2(6) not null,
  employee_number       NUMBER(10) not null,
  scheduled_timeblock_1 VARCHAR2(1),
  scheduled_timeblock_2 VARCHAR2(1),
  scheduled_timeblock_3 VARCHAR2(1),
  scheduled_timeblock_4 VARCHAR2(1),
  creationdate          DATE not null,
  mutationdate          DATE not null,
  mutator               VARCHAR2(20) not null
)
tablespace ABS_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 5M
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index XIE1EMPLOYEEPLANNING on EMPLOYEEPLANNING (EMPLOYEEPLANNING_DATE)
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 3M
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table EMPLOYEEPLANNING
  add constraint XPKEMPLOYEEPLANNING primary key (PLANT_CODE, EMPLOYEEPLANNING_DATE, SHIFT_NUMBER, DEPARTMENT_CODE, WORKSPOT_CODE, EMPLOYEE_NUMBER)
  using index 
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 5M
    next 1M
    minextents 1
    maxextents unlimited
  );