Index: database_scripts/kreiranje_v2.sql
===================================================================
--- database_scripts/kreiranje_v2.sql	(revision f094f6c409345b74661afd84bd147a6a076a8967)
+++ database_scripts/kreiranje_v2.sql	(revision f094f6c409345b74661afd84bd147a6a076a8967)
@@ -0,0 +1,225 @@
+drop table if exists users_table cascade;
+drop table if exists client cascade;
+drop table if exists delivery_man cascade;
+drop table if exists warehouseman cascade;
+drop table if exists administrator cascade;
+drop table if exists review cascade;
+drop table if exists repair cascade;
+drop table if exists repair_shop cascade;
+drop table if exists car_manufacturer cascade;
+drop table if exists car cascade;
+drop table if exists car_sample cascade;
+drop table if exists service_book cascade;
+drop table if exists order_table cascade;
+drop table if exists part_manufacturer cascade;
+drop table if exists part cascade;
+drop table if exists delivery cascade;
+drop table if exists warehouse cascade;
+drop table if exists price cascade;
+drop table if exists category cascade;
+drop table if exists part_is_from_category cascade;
+drop table if exists repair_shop_is_authorized_for_car_make cascade;
+drop table if exists order_contains_part cascade;
+drop table if exists part_is_in_stock_in_warehouse cascade;
+drop table if exists part_is_appropriate_for_car cascade;
+
+create table users_table(
+	ID_user serial primary key,
+	username varchar(100) unique not null,
+	email varchar(100) unique not null,
+	name_user varchar(100) not null,
+	password_user varchar(100) not null,
+	user_created_on timestamp default now(),
+	phone_number varchar(100),
+	ID_administrator integer
+	-- constraint for fk_user_by_administrator added later
+
+);
+
+create table client(
+	ID_user integer primary key,
+	constraint fk_client_is_user foreign key (ID_user) references users_table(ID_user)
+);
+
+create table delivery_man(
+	ID_user integer primary key,
+	employed_from date not null,
+	constraint fk_delivery_man_is_user foreign key (ID_user) references users_table(ID_user)
+);
+
+create table warehouseman(
+	ID_user integer primary key,
+	employed_from date not null,
+	ID_warehouse integer not null,
+	constraint fk_warehouseman_is_user foreign key (ID_user) references users_table(ID_user)
+	-- contraint for fk_warehouseman_warehouse added later
+);
+
+create table administrator(
+	ID_user integer primary key,
+	authorized_from date not null,
+	authorized_till date not null,
+	constraint fk_administrator_is_user foreign key (ID_user) references users_table(ID_user)
+);
+
+alter table users_table add constraint fk_user_by_administrator foreign key (ID_administrator)
+    references administrator(ID_user);
+   
+create table warehouse(
+	ID_warehouse serial primary key,
+	warehouse_location varchar(100) not null
+);
+
+alter table warehouseman add constraint fk_warehouseman_warehouse foreign key (ID_warehouse)
+    references warehouse(ID_warehouse);
+    
+create table car_manufacturer(
+	ID_car_manufacturer serial primary key,
+	cm_name varchar(100) not null,
+	cm_country varchar(100)
+);
+
+create table car(
+	ID_car serial primary key, 
+	in_production_since integer not null, 
+	in_production_till integer not null, 
+	car_type varchar(100) not null, 
+	ID_car_manufacturer integer not null,
+	constraint fk_car_car_manufacturer foreign key (ID_car_manufacturer) references car_manufacturer(ID_car_manufacturer)
+);
+
+create table car_sample(
+	vin integer primary key,
+	year_of_production integer not null, 
+	engine_power integer not null, 
+	displacement integer not null, 
+	fuel_type varchar(100) not null, 
+	km_driven integer not null, 
+	ID_user integer not null, 
+	ID_car integer not null,
+	constraint fk_car_sample_user foreign key (ID_user) references client(ID_user),
+	constraint fk_car_sample_car foreign key (ID_car) references car(ID_car)
+);
+
+create table service_book(
+	ID_service_book serial primary key,
+	sb_created_on timestamp default now(),
+	vin integer not null,
+	constraint fk_service_book_car_sample foreign key (vin) references car_sample(vin)
+);
+
+create table repair_shop(
+	ID_repair_shop serial primary key, 
+	rs_name varchar(100) not null, 
+	rs_location varchar(100) not null, 
+	rs_phone_number varchar(100) not null
+);
+
+create table repair(
+	ID_repair serial primary key,
+	vin integer not null,
+	ID_repair_shop integer not null,
+	ID_service_book integer,
+	constraint fk_repair_car_sample foreign key (vin) references car_sample(vin),
+	constraint fk_repair_repair_shop foreign key (ID_repair_shop) references repair_shop(ID_repair_shop),
+	constraint fk_repair_service_book foreign key (ID_service_book) references service_book(ID_service_book)
+);
+
+create table review(
+	ID_review serial primary key,
+	review_rating integer not null,
+	review_comment varchar(300),
+	ID_repair integer not null,
+	ID_user integer not null,
+	constraint fk_review_repair foreign key (ID_repair) references repair(ID_repair),
+	constraint fk_review_user foreign key (ID_user) references client(ID_user)
+);
+
+create table order_table(
+	ID_order serial primary key,
+	order_status varchar(100) not null,
+	order_date timestamp default now(),
+	ID_user integer not null,
+	constraint fk_order_user foreign key (ID_user) references client(ID_user)
+);
+
+create table part_manufacturer(
+	ID_part_manufacturer serial primary key,
+	pm_name varchar(100) not null,
+	pm_location varchar(100)
+);
+
+create table part(
+	ID_part serial primary key,
+	part_name varchar(100) not null,
+	part_description varchar(300),
+	ID_part_manufacturer integer not null,
+	constraint fk_part_part_manufacturer foreign key(ID_part_manufacturer) references part_manufacturer(ID_part_manufacturer)
+);
+
+create table delivery(
+	ID_delivery serial primary key,
+	delivery_status varchar(100) not null,
+	delivery_address varchar(100) not null,
+	ID_user integer not null,
+	ID_order integer not null,
+	constraint fk_delivered_by_deliver_man foreign key(ID_user) references delivery_man(ID_user),
+	constraint fk_delivery_for_order foreign key(ID_order) references order_table(ID_order)
+);
+
+create table price(
+	ID_price serial primary key,
+	amount integer not null,
+	price_from date not null,
+	price_till date,
+	ID_part integer not null,
+	constraint fk_price_for_part foreign key(ID_part) references part(ID_part),
+	constraint ck_amount_gt_0 check (amount>0)
+);
+
+create table category(
+	ID_category serial primary key,
+	category_name varchar(100) not null,
+	ID_parent_category integer,
+	constraint fk_category_parentcategory foreign key (ID_parent_category) references category(ID_category)
+);
+
+create table part_is_from_category(
+	ID_part integer not null,
+	ID_category integer not null,
+	constraint fk_part_is_from_category_part foreign key (ID_part) references part(ID_part),
+	constraint fk_part_is_from_category_category foreign key (ID_category) references category(ID_category)
+
+);
+
+create table repair_shop_is_authorized_for_car_make(
+	ID_repair_shop integer not null,
+	ID_car_manufacturer integer not null,
+	constraint fk_repair_shop_is_authorized_for_car_make_repair_shop foreign key (ID_repair_shop) references repair_shop(ID_repair_shop),
+	constraint fk_repair_shop_is_authorized_for_car_make_car_manufacturer foreign key (ID_car_manufacturer) references car_manufacturer(ID_car_manufacturer)
+);
+
+create table order_contains_part(
+	ID_part integer not null,
+	ID_order integer not null,
+	quantity_order integer not null,
+	constraint fk_order_contains_part_part foreign key (ID_part) references part(ID_part),
+	constraint fk_order_contains_part_order foreign key (ID_order) references order_table(ID_order),
+	constraint ck_quantity_order check (quantity_order>0)
+);
+
+create table part_is_in_stock_in_warehouse(
+	ID_part integer not null,
+	ID_warehouse integer not null,
+	quantity_warehouse integer not null,
+	constraint fk_part_is_in_stock_in_warehouse_part foreign key (ID_part) references part(ID_part),
+	constraint fk_part_is_in_stock_in_warehouse_warehouse foreign key (ID_warehouse) references warehouse(ID_warehouse),
+	constraint ck_quantity_warehouse check (quantity_warehouse>0)
+);
+
+create table part_is_appropriate_for_car(
+	ID_part integer not null,
+	ID_car integer not null,
+	constraint fk_part_is_appropriate_for_car_part foreign key (ID_part) references part(ID_part),
+	constraint fk_part_is_appropriate_for_car_car foreign key (ID_car) references car(ID_car)
+);
Index: database_scripts/polnenje_v2.sql
===================================================================
--- database_scripts/polnenje_v2.sql	(revision f094f6c409345b74661afd84bd147a6a076a8967)
+++ database_scripts/polnenje_v2.sql	(revision f094f6c409345b74661afd84bd147a6a076a8967)
@@ -0,0 +1,148 @@
+insert into project.users_table (username,email,name_user,password_user) values
+('andrejtodorovski5','andrejtodorovski5@gmail.com','Andrej Todorovski','andrejpassword'),
+('stefanmileski4','stefanmileski4@gmail.com','Stefan Mileski','stefanpassword'),
+('hristijansazdovski3','hristijansazdovski3@gmail.com','Hristijan Sazdovski','hristijanpassword'),
+('bojantrpeski2','bojantrpeski2@gmail.com','Bojan Trpeski','bojanpassword'),
+('darkosasanski1','darkosasanski1@gmail.com','Darko Sasanski','darkopassword'),
+('denicakjorvezir','denicakjorvezir@gmail.com','Denica Kjorvezir','denicapassword'),
+('deliveryman1','deliveryman1@gmail.com','Petko Petkovski','petko123'),
+('deliveryman2','deliveryman2@gmail.com','Marko Markovski','marko123'),
+('deliveryman3','deliveryman3@gmail.com','Petar Petrovski','petar123'),
+('warehouseman1','warehouseman1@gmail.com','Viktor Petrovski','viktor123'),
+('warehouseman2','warehouseman2@gmail.com','Vlatko Petrovski','vlatko123'),
+('warehouseman3','warehouseman3@gmail.com','Vido Petrovski','vido123');
+
+insert into project.users_table (username,email,name_user,password_user,phone_number) values
+('stefanmileski5','stefanmileski5@gmail.com','Stefan Mileski','stefanklient','070 123 456');
+
+insert into project.administrator (id_user,authorized_from, authorized_till) values (1,'2022-01-01','2024-01-01'),(2,'2022-01-01','2025-01-01'),(3,'2022-01-01','2026-01-01');
+
+insert into project.client (id_user) values (13),(4),(5),(6);
+
+insert into project.delivery_man (id_user,employed_from) values (7,now()),(8,now()),(9,now());
+
+insert into project.warehouse (warehouse_location) values ('Radishani'),('Kozle'),('Vizbegovo');
+
+insert into project.warehouseman (id_user,employed_from,id_warehouse) values (10,now(),1),(11,now(),2),(12,now(),3);
+
+insert into project.part_manufacturer (pm_name,pm_location) values
+('Bosch','Germany'),
+('Continental','Belgium'),
+('Mahle','Netherlands');
+
+insert into project.car_manufacturer (cm_name,cm_country) values
+('Volkswagen','Germany'),
+('Fiat','Italy'),
+('Ford','USA'),
+('Toyota','Japan'),
+('Hyndai','Korea');
+
+insert into project.repair_shop (rs_name,rs_location,rs_phone_number) values
+('Toyota Service','Partizanska','070 123 456'),
+('Volkswagen Service','Ilinenska','075 876 543'),
+('Fiat Service','Vodnjanska','078 555 666'),
+('Hyndai Service','Boris Trajkovski','075 500 000');
+
+insert into project.car(in_production_since,in_production_till,car_type,id_car_manufacturer) values
+(2001,2004,'Golf 4',1),
+(2006,2009,'Punto',2),
+(2004,2006,'Golf 5',1),
+(2000,2008,'Fiesta',3),
+(1995,2005,'Coupe',5);
+
+insert into project.car_sample(vin,year_of_production, engine_power, displacement, fuel_type, km_driven, ID_user, ID_car) values
+(1111,2008,65,1200,'Diesel',120000,4,4),
+(2222,2010,80,1400,'Diesel',150000,5,4),
+(3333,2016,65,1200,'Petrol/Gas',65000,6,2),
+(4444,1997,140,2000,'Petrol/Gas',185000,13,5);
+
+insert into project.part(part_name, id_part_manufacturer) values
+('Engine G4GF',1),
+('Wheel 20CM',2),
+('Shift 6GEARS',3),
+('Radio WITH AUX',1),
+('Rear Bumper FOR VW GOLF 4',2),
+('Headlight FOR PUNTO',3);
+
+insert into project.category(category_name) values ('Brakes'),('Cooling System'),('Electrical'),('Engine components'),('Exterior'),('Filters');
+
+insert into project.order_table(order_status,order_date,id_user) values 
+('created',now(),4),
+('created',now(),5),
+('created',now(),6),
+('created',now(),13);
+
+insert into project.service_book (vin) values (1111),(2222),(3333),(4444);
+
+insert into project.delivery (delivery_status, delivery_address,id_user,id_order) values 
+('in progress','Aerodrom',7,3),
+('finished','Pintija',8,1),
+('in progress','Kisela Voda',9,2),
+('in progress','Kozle',8,4);
+
+insert into project.price (amount, price_from, id_part) values 
+(1000,now(),2),
+(2000,now(),3),
+(3000,now(),1),
+(1500,now(),4),
+(3500,now(),5),
+(300,now(),6);
+
+insert into project.repair (vin, id_repair_shop, id_service_book) values (1111,3,1),(2222,2,2),(3333,1,3),(4444, 2, 4);
+insert into project.repair (vin, id_repair_shop, id_service_book) values (1111,2,1)
+
+insert into project.review (review_rating, id_repair, id_user) values (10, 1, 4), (6, 2, 5), (8, 3, 6), (9, 4, 13);
+insert into project.review (review_rating, id_repair, id_user) values (7, 5, 4)
+
+insert into project.part_is_from_category (id_part, id_category) values (5,5), (1,4), (6,5);
+
+insert into project.repair_shop_is_authorized_for_car_make (id_repair_shop, id_car_manufacturer) values (1,4),(2,1),(3,2),(4,5);
+
+insert into project.order_contains_part (id_order, id_part, quantity_order) values (1,3,1),(2,4,2),(3,1,2),(4,6,1);
+
+insert into project.part_is_in_stock_in_warehouse (id_part,id_warehouse,quantity_warehouse) values (1,2,100), (2,1,150), (3,1,50), (4,3,75),(5,1,30),(6,2,70);
+
+insert into project.part_is_appropriate_for_car (id_part, id_car) values (1,1),(1,2),(1,3),(2,1),(2,4),(2,5),(5,1),(4,1),(4,2),(4,3),(4,4),(4,5),(3,5),(6,2);
+
+
+
+select p.part_name, project.repair.vin,id_repair_shop  from project.repair
+join project.car_sample cs on cs.vin = project.repair.vin
+join project.order_table ot on ot.id_user = cs.id_user 
+join project.order_contains_part ocp on ocp.id_order = ot.id_order 
+join project.part p on p.id_part = ocp.id_part 
+where cs.id_user = 4
+
+select p.part_name, c.car_type from project.part p 
+join project.part_is_appropriate_for_car piafc on piafc.id_part = p.id_part 
+join project.car c on c.id_car = piafc.id_car 
+join project.part_is_from_category pifc on pifc.id_part = p.id_part
+join project.category c2 on c2.id_category = pifc.id_category 
+where c.car_type = 'Punto'
+and 
+c2.category_name = 'Exterior'
+
+select ot.order_date , p.part_name , p2.amount , ocp.quantity_order , ot.order_status  from project.order_table ot 
+join project.order_contains_part ocp on ocp.id_order = ot.id_order 
+join project.part p on p.id_part = ocp.id_part 
+join project.price p2 on p2.id_part = p.id_part 
+where ot.id_user = 4
+
+select r.review_rating, r.review_comment, rs.rs_name, rs.rs_location  from project.review r 
+join project.repair r2 on r2.id_repair = r.id_repair 
+join project.repair_shop rs on rs.id_repair_shop = r2.id_repair_shop 
+where r.id_user = 4
+
+select part_name from project.part
+
+drop view project.repair_shop_reviews_summary
+
+create or replace view project.repair_shop_reviews_summary as
+select rs.id_repair_shop as rsid , rs.rs_name as rsname , count(r2.review_rating) as reviewcount ,avg(r2.review_rating)::float as reviewaverage
+from project.repair_shop rs 
+left join project.repair r on r.id_repair_shop = rs.id_repair_shop 
+left join project.review r2 on r2.id_repair = r.id_repair 
+group by rs.id_repair_shop 
+
+select * from project.repair_shop_reviews_summary
+