DatabaseCreation: MajStore_DDL.sql

--user management and roles ---------------------------------------------------------------------------------------------------------
create table Permission (
    permission_id serial primary key,
    permission_name text not null unique ,
    description text
);

create table Role (
    role_id serial primary key,
    role_name text not null unique
);

create table Role_Permission (
    role_permission_id serial primary key,
    role_id int,
    permission_id int,
    constraint fk_role foreign key (role_id) references Role(role_id),
    constraint fk_permission foreign key (permission_id) references Permission(permission_id)
);

create table User_Role (
    user_role_id serial primary key,
    role_id int,
    user_id int,
    constraint fk_role foreign key (role_id) references Role(role_id),
    constraint fk_user foreign key (user_id) references "User"(user_id),
    unique (user_id, role_id)
);

-- user ------------------------------------------------------------------------------------------------------------------------

create table "User" (
    user_id serial primary key,
    first_name text not null,
    last_name text not null,
    username text not null unique,
    email text not null,
    password_hash text not null unique,
    constraint chk_email check (email like '%@%')
);

create table Notification (
    notification_id serial primary key,
    message text not null,
    is_read bool not null,
    created_at timestamp not null ,
    user_id int,
    constraint fk_user foreign key (user_id) references "User"(user_id)
);

create table Location (
    location_id serial primary key,
    city text not null,
    region text not null,
    address text not null
);

-- worker ------------------------------------------------------------------------------------------------------------------------
 -- dali tuka fk_calendar ili dovolno e vo calendar
create table Worker (
    worker_id serial primary key,
    bio text,
    works_remote bool not null,
    location_id int,
    user_id int,
    calendar_id int,
    constraint fk_location foreign key (location_id) references Location(location_id),
    constraint fk_user foreign key (user_id) references "User"(user_id),
    constraint fk_calendar foreign key (calendar_id) references Calendar(calendar_id)
);

create table Calendar (
    calendar_id serial primary key,
    start_time timestamp not null,
    end_time timestamp not null,
    worker_id int,
    constraint fk_worker foreign key (worker_id) references Worker(worker_id),
    constraint chk_valid_dates CHECK (start_time < end_time)
);

create table Calendar_Event (
    event_id serial primary key,
    title text not null,
    description text,
    start_time timestamp not null,
    end_time timestamp not null,
    calendar_id int,
    constraint fk_calendar foreign key (calendar_id) references Calendar(calendar_id),
    constraint chk_valid_dates CHECK (start_time < end_time)
);

create table Certificate (
    certificate_id serial primary key,
    certificate_name text not null,
    issuer text not null,
    worker_id int,
    constraint fk_worker foreign key (worker_id) references Worker(worker_id)
);

create table Specialty (
    specialty_id serial primary key,
    specialty_name text not null unique
);

create table Worker_Specialty (
    worker_id int,
    specialty_id int,
    constraint fk_worker foreign key (worker_id) references Worker(worker_id),
    constraint fk_specialty foreign key (specialty_id) references Specialty(specialty_id)
);

-- post ------------------------------------------------------------------------------------------------------------------------
create table Post_Status (
    status_id serial primary key,
    status_name text not null unique
);

create table Post (
    post_id serial primary key,
    title text not null,
    description text,
    created_at timestamp,
    user_id int,
    location_id int,
    status_id int,
    constraint fk_user foreign key (user_id) references "User"(user_id),
    constraint fk_location foreign key (location_id) references Location(location_id),
    constraint fk_status foreign key (status_id) references Post_Status(status_id),
    constraint chk_created_at check  (created_at <= now())
);

create table Post_Image (
    image_id serial primary key,
    image_url text not null,
    post_id int,
    constraint fk_post foreign key (post_id) references Post(post_id)
);

create table Post_Specialty (
    post_id int,
    specialty_id int,
    constraint fk_post foreign key (post_id) references Post(post_id),
    constraint fk_specialty foreign key (specialty_id) references Specialty(specialty_id)
);

-- application ------------------------------------------------------------------------------------------------------------------------
create table Application_Status (
    status_id serial primary key,
    status_name text not null unique
);

create table Application (
    application_id serial primary key,
    message text,
    needed_time time,
    expected_price float,
    created_at timestamp not null ,
    worker_id int,
    post_id int,
    status_id int,
    constraint fk_post foreign key (post_id) references Post(post_id),
    constraint fk_worker foreign key (worker_id) references Worker(worker_id),
    constraint fk_status foreign key (status_id) references Application_Status(status_id),
    constraint chk_needed_time check (needed_time > '00:00:00')
);

-- reviews and ratings ------------------------------------------------------------------------------------------------------------------------

create table Rating_Per_Specialty (
    rating_per_specialty_id serial primary key,
    rating_avg float,
    ranking_score int,
    worker_id int,
    specialty_id int,
    constraint fk_worker foreign key (worker_id) references Worker(worker_id),
    constraint fk_specialty foreign key (specialty_id) references Specialty(specialty_id)
);

create table Review (
    review_id serial primary key,
    grade int not null,
    created_at timestamp not null,
    reviewer_id int,
    rating_per_specialty_id int,
    constraint fk_reviewer foreign key (reviewer_id) references "User"(user_id),
    constraint fk_rating_specialty foreign key (rating_per_specialty_id) references Rating_Per_Specialty(rating_per_specialty_id),
    constraint chk_grade check (grade>=0 and grade <=5)
);

create table Review_Comment (
    comment_id serial primary key,
    review_id int,
    constraint fk_review foreign key (review_id) references Review(review_id)
);


-- payments and premium workers ------------------------------------------------------------------------------------------------------------------------
create table Payment_Method (
    method_id serial primary key,
    method_name text not null unique,
    max_amount float not null,
    tax float not null,
    constraint chk_tax check  (tax >= 0 and tax <= 1)
);
-- foreign keys da se PK ili da ima vesticki?????
create table Worker_Method (
    worker_method_id serial primary key,
    worker_id int,
    method_id int,
    constraint fk_worker foreign key (worker_id) references Worker(worker_id),
    constraint fk_method foreign key (method_id) references Payment_Method(method_id),
    unique (worker_id, method_id)
);

create table Payment_Status (
    status_id serial primary key,
    status_name text not null unique
);

create table Payment (
    payment_id serial primary key,
    amount float not null,
    payment_date timestamp not null,
    worker_method_id int,
    payment_status int,
    constraint fk_worker_method foreign key (worker_method_id) references Worker_Method(worker_method_id),
    constraint fk_status foreign key (worker_method_id) references Payment_Status(status_id)
);

create table Premium_Plan (
    plan_id serial primary key,
    name text not null unique,
    price float not null,
    duration_days int not null,
    description text not null,
    constraint chk_price check (price>=0),
    constraint chk_duration_days check (duration_days>0)
);

create table Premium_Status (
    status_id serial primary key,
    status_name text not null unique
);

create table Premium_Worker (
    premium_id serial primary key,
    start_date timestamp not null,
    end_date timestamp not null,
    status_id int,
    plan_id int,
    worker_id int,
    payment_id int,
    constraint fk_status foreign key (status_id) references Premium_Status(status_id),
    constraint fk_worker foreign key (worker_id) references Worker(worker_id),
    constraint fk_plan foreign key (plan_id) references Premium_Plan(plan_id),
    constraint fk_payment foreign key (payment_id) references Payment(payment_id),
    constraint chk_valid_dates CHECK (start_date < end_date)
);