RelationalDesign: kreiranje4.sql

set
search_path = project;
--Create DB Shema
--drop schema if exists project cascade;

--create schema project;

drop table if exists cities cascade;
drop table if exists vet_centers cascade;
drop table if exists jobs cascade;
drop table if exists blog_post_for_consultations cascade;
drop table if exists blog_post_answers cascade;
drop table if exists pet_cares cascade;
drop table if exists type_of_pets cascade;
drop table if exists pets cascade;
drop table if exists pet_galery cascade;
drop table if exists reports cascade;
drop table if exists diagnostics cascade;
drop table if exists manufacturers cascade;
drop table if exists medecines cascade;
drop table if exists pet_status cascade;
drop table if exists therapy cascade;
drop table if exists breeds cascade;
drop table if exists products cascade;
drop table if exists orders cascade;
drop table if exists pets_visit_vet_centers cascade;
drop table if exists products_there_are_for_typepets cascade;
drop table if exists product_are_made_orders cascade;
--drop table if exists products_available_type_of_pets cascade;
drop table if exists therapy_takes_pets cascade;
drop table if exists diagnostics_established_pets cascade;
drop table if exists users cascade;
drop table if exists roles cascade;
--Create Tables
create table cities(
 ID SERIAL,
 name varchar(50),
 --adress varchar(50),
 --phoneNumber varchar(20),
     constraint pk_cities primary key (ID)
);

create table vet_centers(
 ID SERIAL,
 name varchar(50),
 description varchar(150),
 adress varchar(50),
 phoneNumber varchar(15),
 latitude decimal,
 longitude decimal,
 workingHours varchar(50),
     constraint pk_vetcen primary key (ID),
     citiesID bigint not null,
constraint fk_cities foreign key (citiesID) references cities(ID)
);

create table jobs(
 ID SERIAL,
 description varchar(50),
 predictedSalery varchar(30),
     constraint pk_jjobs primary key (ID),
     vetCentersID bigint not null,
constraint fk_vetCenters foreign key ( vetCentersID) references vet_centers(ID)
    
);

create table roles(
 ID SERIAL,
 type varchar(15) not null unique,
     constraint pk_roles primary key (ID)
);

 create table users(
 ID SERIAL,
 name varchar(30) not null,
 lastname varchar(30) not null,
 email varchar(30) not null unique,
 password varchar(256) not null unique,
 number varchar(15) not null unique,
 role_id bigint not null,
 jobs_id bigint default null,
  constraint pk_users primary key (ID),
  constraint fk_roles foreign key (role_id) references roles(ID),
  constraint fk_jobs foreign key (jobs_id) references jobs(ID)
  
);

 create table blog_post_for_consultations(
ID SERIAL,
date_askes date,
title varchar(30),
description varchar(150),
users_id bigint not null,
constraint pk_blogPFC primary key (ID),
constraint fk_users foreign key (users_id) references users(ID)
);
create table blog_post_answers(
ID SERIAL,
parent_id bigint,
reply varchar(150),
blogPostConsID bigint not null,
usersID bigint not null,
constraint pk_blogPA primary key (ID),
constraint fk_blogBPFC foreign key (blogPostConsID) references blog_post_for_consultations(ID),
constraint fk_users foreign key (usersID) references users(ID)
);

create table pet_cares(
ID SERIAL,
title varchar(50),
description varchar(150),
dateending date not null,
constraint pk_petCares primary key (ID),
usersID bigint not null,
vetcentersID bigint not null,
constraint fk_blogBPFC foreign key (usersID) references users(ID),
constraint fk_vetCenters foreign key (vetcentersID) references vet_centers(ID)
);

create table type_of_pets(
 ID SERIAL,
 description varchar(30),
 name varchar(30),
     constraint pk_typeOfPets primary key (ID)
     
);

create table pets(
 ID SERIAL,
 color varchar(20),
 description varchar(50),
 dateOfBirthday date,
     constraint pk_pets primary key (ID),
     usersID bigint not null,
     typeOfPetsID bigint not null,
     constraint fk_typeOfPets foreign key (typeOfPetsID) references type_of_pets(ID),
    constraint fk_users foreign key (usersID) references users(ID)
);

create table pet_galery(
 ID SERIAL,
 picture varchar(1000),
     constraint pk_petGalery primary key (ID),
     petsID bigint not null,
    constraint fk_pets foreign key (petsID) references pets(ID)
);
create table reports(
 ID SERIAL,
 description varchar(150),
     constraint pk_reports primary key (ID),
     usersID bigint not null,
     petsID bigint not null,
    constraint fk_users foreign key (usersID) references users(ID),
      constraint fk_pets foreign key (petsID) references pets(ID)
);

create table diagnostics(
 ID SERIAL,
 description varchar(200),
     constraint pk_diagnostics primary key (ID),
     usersID bigint not null,
    constraint fk_users foreign key (usersID) references users(ID)
);

create table manufacturers(
 ID SERIAL,
 name varchar(50) not null,
 description varchar(200),
 city varchar(50),
 state varchar(50),
     constraint pk_manufacturers primary key (ID)
      
); 

create table medecines(
 ID SERIAL,
 name varchar(50) not null,
 description varchar(200),
     constraint pk_medecines primary key (ID),
     diagnosticsID bigint not null,
     manufacturersID bigint not null,
    constraint fk_diagnostics foreign key (diagnosticsID) references diagnostics(ID),
    constraint fk_manufacturers foreign key (manufacturersID) references manufacturers(ID)
);

create table pet_status(
 ID SERIAL,
 type varchar(30),
 node varchar(200),
     constraint pk_pet_status primary key (ID)
); 

create table therapy(
 ID SERIAL,
 description varchar(150),
 appoitmentDate date,
     constraint pk_therapy primary key (ID),
     diagnosticsID bigint not null,
     pet_statusID bigint not null,
     constraint fk_pet_status foreign key (pet_statusID) references pet_status(ID),
    constraint fk_diagnostics foreign key (diagnosticsID) references diagnostics(ID)
);

create table breeds(
ID SERIAL,
name varchar(50),
constraint pk_breeds primary key(ID),
constraint fk_type_of_pets foreign key (ID) references type_of_pets(ID) on delete cascade
);

create table products(
 ID SERIAL,
 name varchar(50),
 description varchar(150),
 price varchar(50),
 isActive bool,
 dateAdded date,
 category varchar(50),
     constraint pk_products primary key (ID)
); 

create table orders(
ID SERIAL,
quantity int,
constraint pk_orders primary key(ID)
);


create table pets_visit_vet_centers(
id_pets bigint not null,
id_vet_centers bigint not null,
constraint pk_pets_visit_vet_centers primary key (id_pets, id_vet_centers),
constraint fk_pet_visit_vet_centers_pet foreign key (id_pets) references pets(ID) on delete cascade,
constraint fk_pet_visit_vet_centers_vetcenters foreign key (id_vet_centers) references vet_centers(ID) on delete cascade
);


create table product_are_made_orders(
id_products bigint not null,
id_orders bigint not null,
constraint pk_product_are_made_orders primary key (id_products, id_orders),
constraint fk_product_are_made_orders_product foreign key (id_products) references products(ID) on delete cascade,
constraint fk_product_are_made_orders_orders foreign key (id_orders) references orders(ID) on delete cascade
);



create table therapy_takes_pets(
id_therapy bigint not null,
id_pets bigint not null,
constraint pk_therapy_takes_pets primary key (id_therapy, id_pets),
constraint fk_therapy_takes_pets_pets foreign key (id_pets) references pets(ID) on delete cascade,
constraint fk_therapy_takes_pets_therapy foreign key (id_therapy) references therapy(ID) on delete cascade
);

ALTER TABLE users
ALTER COLUMN number TYPE varchar(40);

ALTER TABLE users
ALTER COLUMN password DROP NOT NULL;
  
ALTER TABLE pet_cares 
ALTER COLUMN dateending type timestamp;



-- ПОСЛЕДНИ ИЗМЕНИ
ALTER TABLE "diagnostics"  
add column vetcenterID bigint,
add constraint fk_vetcenter foreign key (vetcenterID) references vet_centers(ID);

create table diagnostics_established_pets(
id_diagnostics bigint not null,
id_pets bigint not null,
constraint pk_diagnostics_established_pets primary key (id_diagnostics, id_pets),
constraint fk_diagnostics_established_pets foreign key (id_pets) references pets(ID) on delete cascade,
constraint fk_diagnostics_established_therapy foreign key (id_diagnostics) references diagnostics(ID) on delete cascade
);


ALTER TABLE pets  
add column petstatusid bigint,
add constraint fk_petstatus foreign key (petstatusid) references pet_status(ID);

create table products_there_are_for_typepets(
id_typeofpets bigint not null,
id_products bigint not null,
constraint pk_products_there_are_for_typepets primary key (id_typeofpets, id_products),
constraint fk_products_there_are_for_type foreign key (id_typeofpets) references type_of_pets(ID) on delete cascade,
constraint fk_products_there_are_for_products foreign key (id_products) references products(ID) on delete cascade
);

ALTER TABLE blog_post_answers
ADD COLUMN date_answers TIMESTAMP DEFAULT NOW();



alter table pets 
add column name varchar(50);

ALTER TABLE type_of_pets 
RENAME COLUMN name TO kind_of_pet;

alter table pet_cares  
add column start_date timestamp;

alter table products 
add column available_quantity integer;

ALTER TABLE orders  
add column userid bigint,
add constraint fk_user foreign key (userid) references users(ID);

ALTER TABLE pets  
add column petcares bigint,
add constraint petcares foreign key (petcares) references pet_cares(ID);