create table LOGTIME(
 N      number(*, 0) not null primary key,
 DT     date,
 EV_N   number(*, 0),
 POBJ_N number(*, 0),
 CLI_N  number(*, 0)
);

create index LOG_DT on LOGTIME (DT);

create TABLE CLITYPES(
 CLI_KIND  number(*, 0) not null primary key,
 KIND_OPIS varchar2(100)
);

create table DEPARTM(
 CODE       number(*, 0) not null primary key,
 KOD_DEP    varchar2(10),
 NAIM_K     varchar2(64),
 NAIM_D     varchar2(255),
 PAR        number(*, 0),
 DATE_START DATE,
 DATE_END   DATE,
 REP        char(1) default 0
);

create table JOBS(
 CODE       number(*, 0) not null primary key,
 NAIM_K     varchar2(64),
 NAIM_D     varchar2(255),
 DATE_START DATE,
 DATE_END   DATE
);

create sequence DJ_GEN start with 1;

create table BRIGTAB(
 CODE    number(*, 0) not null primary key,
 KOD     varchar2(8),
 NAIM    varchar2(50),
 COD_DEP number(*, 0) not null
);

create sequence BRIG_GEN start with 1;

create table CLITAB(
 N        number(*, 0) not null primary key,
 CODE     number(*, 0),
 NUMBER_  varchar2(32),
 NUM_KD   varchar2(8),
 F_NAME   varchar2(64),
 S_NAME   varchar2(64),
 M_NAME   varchar2(64),
 CLI_KIND number(*, 0),
 RAZR     varchar2(8),
 P_OPL    varchar2(8),
 COD_DEP  number(*, 0),
 COD_JOB  number(*, 0),
 COD_BRIG number(*, 0),
 VIP      char(1)
);

create index CLI_FNAME on CLITAB (F_NAME);

create index CLI_NMB on CLITAB (NUMBER_);

create index CLITAB_CODE on CLITAB (CODE);

create index CLI_DEP on CLITAB(COD_DEP);

create table CLI_HISTORY(
 CODE     number(*, 0) not null primary key,
 CLI_N    number(*, 0) not null,
 DAT      date not null,
 COD_DEP  number(*, 0),
 COD_JOB  number(*, 0),
 COD_BRIG number(*, 0),
 COD_EV   number(2, 0) not null,
 NAKAZ    varchar2(50)
);

create sequence HISTORY_GEN start with 1;

create index CLI_HIST on CLI_HISTORY(CLI_N);

create table SHABL(
 CODE   number(*, 0) not null primary key,
 NAIM_K varchar2(50),
 PERIOD number(3, 0),
 R_IN   number(4, 0),
 P_IN   number(4, 0),
 R_OUT  number(4, 0),
 P_OUT  number(4, 0),
 MAX_AB number(4, 0)
);

create sequence SH_GEN start with 1;

create table SMENA(
 CODE   number(*, 0) not null primary key,
 NAIM_K varchar2(50),
 COD_SH number(*, 0) not null constraint fk_smen references SHABL(CODE) on delete cascade
);

create sequence SMEN_GEN start with 1;

create or replace trigger TR_SMENA_CODE
BEFORE INSERT on SMENA
FOR EACH ROW
BEGIN
 select SMEN_GEN.NEXTVAL into :new.CODE from DUAL;
END;
/

create table SH_FIELD(
 CODE      number(*, 0) not null primary key,
 COD_SMEN  number(*, 0) not null constraint fk_shsm references SMENA(CODE) on delete cascade,
 NPP       number(*, 0) not null,
 TIME_I    date,
 TIME_ALL  date,
 TIME_DIN1 date,
 TIME_DIN2 date
);

create sequence SHF_GEN start with 1;

create or replace trigger TR_SHF_CODE
BEFORE INSERT on SH_FIELD
FOR EACH ROW
BEGIN
 select SHF_GEN.NEXTVAL into :new.CODE from DUAL;
END;
/

create table KONTUR(
 CODE number(*, 0) not null primary key,
 NAIM varchar2(50)
);

create sequence KONT_GEN start with 1;

create table D_IN_KONT(
 COD_KONT number(*, 0) not null constraint fk_dkont references KONTUR(CODE) on delete cascade,
 SNS_N    number(*, 0) not null,
 constraint Kont_Door unique (COD_KONT,SNS_N)
);

create table GRAPHS(
 CODE      number(*, 0) not null primary key,
 CLI_N     number(*, 0),
 MON       date,
 COD_KONT  number(*, 0) not null constraint fk_grkont references KONTUR(CODE),
 COD_SHABL number(*, 0) constraint fk_grsh references SHABL(CODE),
 NORM_H    number(*, 0),
 NORM_M    number(*, 0)
);

create index GR_CLI on GRAPHS(CLI_N);

create sequence GR_GEN start with 1;

create table TIMETABLE(
 CODE      number(*, 0) not null primary key,
 COD_GRAF  number(*, 0) constraint fk_GRAPH references GRAPHS(CODE) on delete cascade,
 DAT       date,
 TIME_I    date,
 TIME_ALL  date,
 TIME_DIN1 date,
 TIME_DIN2 date,
 R_IN      number(4, 0),
 P_IN      number(4, 0),
 R_OUT     number(4, 0),
 P_OUT     number(4, 0),
 MAX_AB    number(4, 0),
 COD_SMEN  number(*, 0) constraint fk_ttsm references SMENA(CODE)
);

create index TT_GR on TIMETABLE(COD_GRAF);

create sequence TT_GEN start with 1;

create or replace trigger TR_TT_CODE
BEFORE INSERT on TIMETABLE
FOR EACH ROW
BEGIN
 select TT_GEN.NEXTVAL into :new.CODE from DUAL;
END;
/

create table BUFER(
 USER_ID   number(*, 0),
 NPP       number(*, 0),
 TIME_I    date,
 TIME_ALL  date,
 TIME_DIN1 date,
 TIME_DIN2 date,
 DAT       date,
 R_IN      number(4, 0),
 P_IN      number(4, 0),
 R_OUT     number(4, 0),
 P_OUT     number(4, 0),
 MAX_AB    number(4, 0),
 COD_SMEN  number(*, 0)
);

create sequence ID_GEN start with 1;

create TABLE HLDTAB(
 N     number(*, 0) not null primary key,
 M     number(2, 0) not null,
 D     number(2, 0) not null,
 OPIS  varchar2(32)
);

create sequence HLDTAB_GEN start with 10;

create or replace trigger TR_HLDTAB_N
BEFORE INSERT on HLDTAB
FOR EACH ROW
BEGIN
 select HLDTAB_GEN.NEXTVAL into :new.N from DUAL;
END;
/

create table FORMT13(
 CODE  number(*, 0) not null primary key,
 NAME  varchar2(100),
 NUM   number(*, 0)
);

create table TIPE_ABS(
 CODE    number(*, 0) not null primary key,
 NAIM_K  varchar2(50),
 NAIM_D  varchar2(255),
 WTIME   char(1),
 WDAY    char(1) default 0,
 COLT13  number(*, 0)
);

create sequence T_AB_GEN start with 15;

create table ABSENCE(
 CODE    number(*, 0) not null primary key,
 CLI_N   number(*, 0) not null,
 COD_ABS number(*, 0) not null constraint fk_abt references TIPE_ABS(CODE) on delete cascade,
 DAT_N   date not null,
 DAT_K   date not null,
 COMM    varchar2(255)
);

create sequence AB_GEN start with 1;

create index CLI_ABS on ABSENCE(CLI_N);

create or replace trigger TR_ABSENCE_CODE
BEFORE INSERT on ABSENCE
FOR EACH ROW
BEGIN
 select AB_GEN.NEXTVAL into :new.CODE from DUAL;
END;
/

create table CLIGROUP(
 CODE  number(*, 0) not null primary key,
 NAIM  varchar2(255)
);

create table CLI_IN_GR(
 COD_GR  number(*, 0) not null constraint fk_cligr references CLIGROUP(CODE) on delete cascade,
 CLI_N   number(*, 0) not null
);

create sequence CLIGR_GEN start with 1;

create index CLIINGR on CLI_IN_GR(CLI_N);

create or replace trigger TR_CLIGR_CODE
BEFORE INSERT on CLIGROUP
FOR EACH ROW
BEGIN
 select CLIGR_GEN.NEXTVAL into :new.CODE from DUAL;
END;
/

create TABLE HIST_PCARD(
 HIST_ID      number(*, 0) not null primary key,
 USER_ID      number(*, 0) not null,
 CLI_N        number(*, 0),
 DATE_START   DATE,
 CARD_STS     number(1, 0),
 DATE_END     DATE,
 REASON       varchar2(70),
 FIO_OPER     varchar2(70),
 FIO_OPER_END varchar2(70)
);
create index CHIST_ID on HIST_PCARD (USER_ID, DATE_START);

create sequence GEN_HIST_PCARD start with 1;

create sequence GEN_CLITAB_ID start with 1;

create table TIMEKEEPERS(
 CODE     number(*, 0) not null primary key,
 F_NAME   varchar2(64),
 S_NAME   varchar2(64),
 M_NAME   varchar2(64),
 NICKNAME varchar2(25) not null unique,
 PRIV     varchar2(25),
 PASSW    varchar2(20),
 DEP      varchar2(1000)
);

insert into TIMEKEEPERS(CODE,NICKNAME,PRIV,PASSW,DEP) values (1,'Старший табельщик','ALL','','ALL');

create sequence TKEEPERS_GEN start with 2;

create table INSP_PARAMS(
 TFIRE          number(*, 0),
 LASTRELEASE    number(3, 0),
 LASTBUILD      number(*, 0),
 DOUPDATE       char(1),
 NEWVERSIONPASS varchar2(255)
);

insert into INSP_PARAMS (TFIRE, LASTRELEASE, LASTBUILD, DOUPDATE) values (4,1,1,0);

create table ADDWORKTIME(
 FNIGHT char(1),
 NBEG   date,
 NALL   date,
 FEVEN  char(1),
 EBEG   date,
 EALL   date,
 ENAME  varchar2(40)
);

insert into ADDWORKTIME (ENAME,FNIGHT,FEVEN) values ('',1,1);

create table OUTTERRITORY(
 CODE       number(*, 0) not null primary key,
 CLI_N      number(*, 0) not null,
 DATE_START date not null,
 DATE_END   date
);

create sequence OUTTERR_GEN start with 1;

create table SUMMERTIME(
 DAT    date not null unique,
 UPDOWN number(1, 0)
);

create table NEWWEEKEND(
 DAT date not null unique
);

create table NEWWORKDAY(
 DAT date not null unique
);

commit;

insert into FORMT13 (CODE, NAME, NUM) values (10, 'командировка', 1);
insert into FORMT13 (CODE, NAME, NUM) values (11, 'очередной отпуск', 2);
insert into FORMT13 (CODE, NAME, NUM) values (12, 'учебный отпуск', 3);
insert into FORMT13 (CODE, NAME, NUM) values (13, 'гос. обязанности', 4);
insert into FORMT13 (CODE, NAME, NUM) values (14, 'больничный', 5);

insert into TIPE_ABS (CODE, NAIM_K, NAIM_D, COLT13) values (1, 'ОО', 'очередной отпуск', 11);
insert into TIPE_ABS (CODE, NAIM_K, NAIM_D, COLT13) values (2, 'УО', 'учебный отпуск', 12);
insert into TIPE_ABS (CODE, NAIM_K, NAIM_D, WTIME, COLT13) values (3, 'К', 'командировка', '1', 10);
insert into TIPE_ABS (CODE, NAIM_K, NAIM_D, COLT13) values (4, 'ГО', 'гос. обязанности',13);
insert into TIPE_ABS (CODE, NAIM_K, NAIM_D, COLT13) values (5, 'Б', 'больничный', 14);

insert into HLDTAB (N, M, D, OPIS) values (1, 1, 1, 'Новый Год');
insert into HLDTAB (N, M, D, OPIS) values (2, 1, 7, 'Рождество');
insert into HLDTAB (N, M, D, OPIS) values (3, 3, 8, 'Международный женский день');
insert into HLDTAB (N, M, D, OPIS) values (4, 5, 1, 'День солидарности трудящихся');
insert into HLDTAB (N, M, D, OPIS) values (5, 5, 2, 'День солидарности трудящихся');
insert into HLDTAB (N, M, D, OPIS) values (6, 5, 9, 'День Победы');

commit;
/