CREATE TABLE employees
(employee_id NUMBER(5) CONSTRAINT emp_id_pk PRIMARY KEY,
name VARCHAR2(15) NOT NULL,
last_name VARCHAR2(20) NOT NULL,
email VARCHAR2(35) UNIQUE,
phone_number VARCHAR2(11) UNIQUE);

CREATE TABLE rewards_history
(reward_history_id NUMBER(5) CONSTRAINT rwd_his_id_pk PRIMARY KEY,
reward_per_hour NUMBER(4,2) CHECK(reward_per_hour > 10.00) NOT NULL,
hours_worked NUMBER(3),
bonus NUMBER(6,2) DEFAULT(0.00),
full_reward AS(reward_per_hour*hours_worked) CHECK (full_reward > 1500),
reward_in_month DATE,
employee_id CONSTRAINT rwd_his_emp_fk REFERENCES employees(employee_id));

CREATE TABLE jobs_history
(job_id NUMBER(5) CONSTRAINT jobs_his_pk PRIMARY KEY,
hire_date DATE,
release_date DATE,
used_daysoff NUMBER(2),
employee_id CONSTRAINT jobs_his_emp_fk REFERENCES employees(employee_id),
CONSTRAINT jobs_hire_date_chk CHECK(hire_date < release_date));

CREATE TABLE clients
(client_id NUMBER(5) CONSTRAINT clients_id_pk PRIMARY KEY,
name VARCHAR2(15) NOT NULL,
last_name VARCHAR2(20) NOT NULL,
email VARCHAR2(35) UNIQUE,
phone_number VARCHAR2(11) UNIQUE);

CREATE TABLE books
(book_id NUMBER(5) CONSTRAINT books_id_pk PRIMARY KEY,
title VARCHAR2(25) NOT NULL,
puthor VARCHAR2(25) NOT NULL,
release_date DATE,
publishing_pouse VARCHAR2(25),
category VARCHAR2(25));

CREATE TABLE borrows
(borrow_id NUMBER(5) CONSTRAINT borrows_id_pk PRIMARY KEY,
client_id CONSTRAINT borrows_clients_fk REFERENCES clients(client_id),
book_id CONSTRAINT borrows_books_fk REFERENCES books(book_id),
borrow_date DATE NOT NULL,
return_date DATE,
CONSTRAINT borrows_date_chk CHECK(borrow_date < return_date));

CREATE TABLE addresses
(address_id NUMBER(5) CONSTRAINT addresses_id_pk PRIMARY KEY,
postal_code VARCHAR2(6) DEFAULT('00-000') NOT NULL,
city VARCHAR2(20) NOT NULL,
street VARCHAR2(30) NOT NULL,
house_number VARCHAR2(5) NOT NULL,
employee_id CONSTRAINT addresses_emp_fk REFERENCES employees(employee_id),
client_id CONSTRAINT addresses_clients_fk REFERENCES clients(client_id));

INSERT INTO employees
VALUES
(1, 'Michal', 'Galareta', 'm.galareta@biblioteka.pl', 112233445);

INSERT INTO employees
VALUES
(2, 'Jan', 'Bogdan', 'bogdan.jan@biblioteka.pl', 897132576);

INSERT INTO employees
VALUES
(3, 'Robert', 'Malinowski', 'malin97@biblioteka.pl', 543699786);

INSERT INTO employees
VALUES
(4, 'Jacek', 'Gorski', 'g.jacynty@biblioteka.pl', 223645142);

INSERT INTO employees
VALUES
(5, 'Amadeusz', 'Ostrowski', 'ostros1992@biblioteka.pl', 556324533);

INSERT INTO employees
VALUES
(6, 'Marek', 'Przybysz', 'm_przybysz@biblioteka.pl', 887998243);

INSERT INTO employees
VALUES
(7, 'Miroslaw', 'Klakier', 'm.klakier@biblioteka.pl', 334978213);



INSERT INTO clients
VALUES
(1, 'Agnieszka', 'Malysz', 'skacz_adam@gmail.com', 298533124);

INSERT INTO clients
VALUES
(2, 'Katarzyna', 'Zbigniew', 'zbychu12@wp.pl', 502997834);

INSERT INTO clients
VALUES
(3, 'Janusz', 'Polak', 'p.janusz@onet.pl', 578253102);

INSERT INTO clients
VALUES
(4, 'Alina', 'Smorag', 's.alina33@gmail.com', 702133554);

INSERT INTO clients
VALUES
(5, 'Karol', 'Burger', 'burger.karol@gmail.com', 502748983);

INSERT INTO clients
VALUES
(6, 'Marcin', 'Kowal', 'kowalek97@wp.pl', 902997773);

INSERT INTO clients
VALUES
(7, 'Anna', 'Preis', 'preis.anna@wp.pl', 536307968);




INSERT INTO addresses
VALUES
(1, '05-199', 'Warszawa', 'Aleja 3 Pulku', '3a', 1, NULL);

INSERT INTO addresses
VALUES
(2, '05-199', 'Warszawa', 'Konstytucji', '24/3', 2, NULL);

INSERT INTO addresses
VALUES
(3, '05-189', 'Marki', 'Wyzwolenia', 21, 3, NULL);

INSERT INTO addresses
VALUES
(4, '05-155', 'Warszawa', 'Zwyciestwa', '21b/3', 4, NULL);

INSERT INTO addresses
VALUES
(5, '05-172', 'Warszawa', 'Listopadowa', '7/10', 5, NULL);

INSERT INTO addresses
VALUES
(6, '05-189', 'Marki', 'Orzechowa', '12/4', 6, NULL);

INSERT INTO addresses
VALUES
(7, '05-190', 'Warszawa', 'Miodowa', '3/7', 7, NULL);


INSERT INTO addresses
VALUES
(8, '19-200', 'Grajewo', 'Koszarowa', '21a', NULL, 1);

INSERT INTO addresses
VALUES
(9, '15-300', 'Elk', 'Kochanowskiego', 12, NULL, 2);

INSERT INTO addresses
VALUES
(10, '12-158', 'Bialystok', 'Pogodna', '12/7', NULL, 3);

INSERT INTO addresses
VALUES
(11, '11-255', 'Szczecin', 'Pomorska', 15, NULL, 4);

INSERT INTO addresses
VALUES
(12, '13-505', 'Wroclaw', 'Wroclawska' , '3/6', NULL, 5);

INSERT INTO addresses
VALUES
(13, '60-223', 'Rzeszow', 'Slodka', '5a/3', NULL, 6);

INSERT INTO addresses
VALUES
(14, '24-332', 'Katowice', 'Polna', '10', NULL, 7);