Логички и физички дизајн - Креирање база податоци (со SQL DDL): fit.sql

drop schema if exists project cascade;
create schema project;

drop table if exists Rezervacija ;
drop table if exists Ocenka;
drop table if exists Termin;
drop table if exists Tip;
drop table if exists Trening;
drop table if exists Fitnes_instruktor;
drop table if exists Klient cascade;
drop table if exists Sopstvenik;
drop table if exists Fitnes_Centar;
drop table if exists Korisnici cascade;


create table Korisnici (
        user_id int primary key,
        username varchar(50),
        password varchar(50)
        );


create table Fitnes_Centar (
    ime varchar(100) not null,
    tel_br varchar(15),
    lokacija varchar(150)
);


create table Klient (
    user_id_klient int primary key, 
    ime varchar(50) not null,
    prezime varchar(50) not null,
    email varchar(100) unique not null, 
    tel_broj varchar(15),
    constraint fk20 foreign key (user_id_klient) references Korisnici(user_id)
);


create table Sopstvenik (
    user_id_sopstvenik int primary key,
    username varchar(30),
    ime varchar(100) not null,
    prezime varchar(100) not null,
    tel_br varchar(15),
    email varchar(150),
    constraint fk1 foreign key (user_id_sopstvenik) references Korisnici(user_id)
);
create table Fitnes_Instruktor (
    user_id_instruktor int primary key,
    ime varchar(100) not null,
    prezime varchar(100) not null,
    kratka_bio text,
    rab_iskustvo int,
    tel_broj varchar(15),
    email varchar(150),
    constraint fk2 foreign key (user_id_instruktor) references Korisnici(user_id)
);

create table Trening (
    trening_id int primary key,
    tip varchar(50) not null,
    user_id_instruktor_trening int not null,
    constraint fk4 foreign key (user_id_instruktor_trening) references Fitnes_Instruktor(user_id_instruktor)
);

create table Tip (
    trening_id_tip int primary key,
    individualen varchar(20) not null,
    grupen varchar(20) not null,
    constraint fk5 foreign key (trening_id_tip) references Trening(trening_id)
);

create table Termin (
    termin_id int,
    trening_id_termin int not null,
    user_id_klient_termin int,
    pocetok timestamp not null,
    vremetraenje int,
    primary key (termin_id, trening_id_termin, user_id_klient_termin),
    constraint fk6 foreign key (trening_id_termin) references Trening(trening_id),
    constraint fk7 foreign key (user_id_klient_termin) references Klient(user_id_klient)
);

create table Rezervacija (
    termin_id int not null,
    trening_id_rez int not null,
    user_id_klient_rez int not null,
    primary key (termin_id, trening_id_rez, user_id_klient_rez),
    constraint fk10 foreign key (termin_id, trening_id_rez, user_id_klient_rez) references Termin(termin_id, trening_id_termin, user_id_klient_termin)
);

create table Ocenka (
    trening_id_o int not null,
    user_id_klient_o int not null,
    vrednost int not null,
    komentar text,
    primary key(trening_id_o, user_id_klient_o),
    constraint fk11 foreign key (trening_id_o) references Trening(trening_id),
    constraint fk12 foreign key (user_id_klient_o) references Klient(user_id_klient)
);




INSERT INTO Fitnes_Centar (ime, tel_br, lokacija)
VALUES
('Fitnes Center 1', '123456789', 'Location 1');


insert into Korisnici (user_id, username, password) values
(1, 'client1', 'pass1'),
(2, 'client2', 'pass2'),
(3, 'client3', 'pass3'),
(4, 'client4', 'pass4'),
(5, 'client5', 'pass5'),
(6, 'client6', 'pass6'),
(7, 'client7', 'pass7'),
(8, 'client8', 'pass8'),
(9, 'client9', 'pass9'),
(10, 'client10', 'pass10'),
(11, 'instructor1', 'pass11'),
(12, 'instructor2', 'pass12'),
(13, 'instructor3', 'pass13'),
(14, 'instructor4', 'pass14'),
(15, 'instructor5', 'pass15'),
(16, 'instructor6', 'pass16'),
(17, 'instructor7', 'pass17'),
(18, 'instructor8', 'pass18'),
(19, 'instructor9', 'pass19'),
(20, 'owner', 'ownerpass');

insert into Klient (user_id_klient, ime, prezime, email, tel_broj) values
(1, 'John', 'Doe', 'john.doe@email.com', '0712345678'),
(2, 'Jane', 'Smith', 'jane.smith@email.com', '0712345679'),
(3, 'Alice', 'Johnson', 'alice.johnson@email.com', '0712345680'),
(4, 'Bob', 'Williams', 'bob.williams@email.com', '0712345681'),
(5, 'Charlie', 'Brown', 'charlie.brown@email.com', '0712345682'),
(6, 'David', 'Davis', 'david.davis@email.com', '0712345683'),
(7, 'Eve', 'Miller', 'eve.miller@email.com', '0712345684'),
(8, 'Frank', 'Wilson', 'frank.wilson@email.com', '0712345685'),
(9, 'Grace', 'Moore', 'grace.moore@email.com', '0712345686'),
(10, 'Hannah', 'Taylor', 'hannah.taylor@email.com', '0712345687');


insert into Sopstvenik (user_id_sopstvenik, username, ime, prezime, tel_br, email) values
(20, 'owner', 'Michael', 'Jordan', '0712345690', 'michael.jordan@email.com');

insert into Fitnes_Instruktor (user_id_instruktor, ime, prezime, kratka_bio, rab_iskustvo, tel_broj, email) values
(11, 'Samuel', 'Lee', 'Experienced in strength training', 5, '0712345691', 'samuel.lee@email.com'),
(12, 'Oliver', 'Adams', 'Specialist in cardio workouts', 4, '0712345692', 'oliver.adams@email.com'),
(13, 'Lily', 'Clark', 'Expert in pilates and yoga', 6, '0712345693', 'lily.clark@email.com'),
(14, 'George', 'Lewis', 'Strength coach', 7, '0712345694', 'george.lewis@email.com'),
(15, 'Nancy', 'Walker', 'Yoga instructor', 3, '0712345695', 'nancy.walker@email.com'),
(16, 'Daniel', 'Harris', 'Cardio and flexibility expert', 4, '0712345696', 'daniel.harris@email.com'),
(17, 'Jessica', 'Young', 'Pilates and injury recovery expert', 6, '0712345697', 'jessica.young@email.com'),
(18, 'Michael', 'King', 'Trainer for muscle building', 8, '0712345698', 'michael.king@email.com'),
(19, 'Sarah', 'Scott', 'Expert in group fitness', 5, '0712345699', 'sarah.scott@email.com');

insert into Trening (trening_id, tip, user_id_instruktor_trening) values
(1, 'Individualen', 11),
(2, 'Grupen', 12),
(3, 'Individualen', 13),
(4, 'Individualen', 14),
(5, 'Grupen', 15),
(6, 'Grupen', 16),
(7, 'Individualen', 17),
(8, 'Grupen', 18),
(9, 'Individualen', 19);

insert into Tip (trening_id_tip, individualen, grupen) values
(1, 'Yes', 'No'),
(2, 'No', 'Yes'),
(3, 'Yes', 'No'),
(4, 'Yes', 'No'),
(5, 'No', 'Yes'),
(6, 'No', 'Yes'),
(7, 'Yes', 'No'),
(8, 'No', 'Yes'),
(9, 'Yes', 'No');

insert into Termin (termin_id, trening_id_termin, user_id_klient_termin, pocetok, vremetraenje) values
(1, 1, 1, '2025-01-30 08:00:00', 60),
(2, 2, 2, '2025-01-30 09:00:00', 60),
(3, 3, 3, '2025-01-30 10:00:00', 60),
(4, 4, 4, '2025-01-30 11:00:00', 60),
(5, 5, 5, '2025-01-30 12:00:00', 60),
(6, 6, 6, '2025-01-30 13:00:00', 60),
(7, 7, 7, '2025-01-30 14:00:00', 60),
(8, 8, 8, '2025-01-30 15:00:00', 60),
(9, 9, 9, '2025-01-30 16:00:00', 60);


insert into Rezervacija (termin_id, trening_id_rez, user_id_klient_rez) values
(1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 4, 4),
(5, 5, 5),
(6, 6, 6),
(7, 7, 7),
(8, 8, 8),
(9, 9, 9);

insert into Ocenka (user_id_instruktor_o, user_id_klient_o, vrednost, komentar) values
(11, 1, 5, 'Great instructor!'),
(12, 2, 4, 'Good workout, could be improved'),
(13, 3, 5, 'Amazing session, very calming'),
(14, 4, 4, 'It was good'),
(15, 5, 5, 'Intense and fun!'),
(16, 6, 3, 'It was okay'),
(17, 7, 5, 'Loved the class!'),
(18, 8, 4, 'It was great, good energy'),
(19, 9, 5, 'Session was tough but amazing');