drop table prof_term_za_kons;
create table prof_term_za_kons
(
    ptk_id serial primary key,
    termin timestamp not null,
    p_id   integer   not null,
    CONSTRAINT fk_prof_term_za_kons_profesori FOREIGN KEY (p_id) REFERENCES profesori (p_id)
);

insert into prof_term_za_kons(ptk_id, p_id, termin)
values (0, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'),
        '2022-12-12 10:00:00'),
       (1, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'),
        '2022-12-15 10:00:00'),
       (2, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'sasho.gramatikov'),
        '2022-12-10 9:30:00');

drop table dava_reakcija cascade;
create table dava_reakcija
(
    dr_id    serial primary key,
    p_id     integer not null,
    o_id     integer not null,
    reakcija boolean not null,
    constraint fk_dava_reakcija_profesori foreign key (p_id)
        references profesori (p_id),
    constraint fk_dava_reakcija_odgovori foreign key (o_id)
        references odgovori (o_id)
);

insert into dava_reakcija(dr_id, p_id, o_id, reakcija)
values (0, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'),
        (select o.o_id
         from odgovori as o
         where o.o_sodrzina =
               'Dobar nacin za cuvanje datoteki e ako se koristi atribut od tip text koj ke sodrzi pateka do dokumentot'),
        true),
       (1, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'),
        (select o.o_id
         from odgovori as o
         where o.o_sodrzina =
               'Za vrakanje na prvicnata sostojba treba da se koristi rollback komandata'),
        true),
       (2, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'goran.velinov'),
        (select o.o_id
         from odgovori as o
         where o.o_sodrzina =
               'Dobar nacin za cuvanje datoteki e ako se koristi atribut od tip text koj ke sodrzi pateka do dokumentot'),
        true);

drop table e_oznaceno_so;
create table e_oznaceno_so
(
    os_id  serial primary key,
    pra_id integer not null,
    ka_id  integer not null,
    constraint fk_e_oznaceno_so_prasanja foreign key (pra_id)
        references prasanja (pra_id),
    constraint fk_e_oznaceno_so_kategorii foreign key (ka_id)
        references kategorii (ka_id)
);

insert into e_oznaceno_so(os_id, pra_id, ka_id)
values (0, (select pra.pra_id
            from prasanja as pra
            where pra.pra_naslov = 'Tip na podatok za datoteki vo PostgreSQL'),
        (select ka.ka_id from kategorii as ka where ka.ka_ime = 'Auditoriski vezbi')),
       (1, (select pra.pra_id
            from prasanja as pra
            where pra.pra_naslov = 'Tip na podatok za datoteki vo PostgreSQL'),
        (select ka.ka_id from kategorii as ka where ka.ka_ime = 'Laboratoriski vezbi')),
       (2, (select pra.pra_id from prasanja as pra where pra.pra_naslov = 'Kako da ponistam transakcija'),
        (select ka.ka_id from kategorii as ka where ka.ka_ime = 'Predavanja')),
       (3, (select pra.pra_id
            from prasanja as pra
            where pra.pra_naslov = 'Ne mozam da pristapam do delot za rabota so bazi vo IntelliJ'),
        (select ka.ka_id from kategorii as ka where ka.ka_ime = 'Auditoriski vezbi'));


drop table e_prof_na;
create table e_prof_na
(
    pn_id serial primary key,
    p_id  integer not null,
    ku_id integer not null,
    constraint fk_e_prof_na_profesori foreign key (p_id)
        references profesori (p_id),
    constraint fk_e_prof_na_kursevi foreign key (ku_id)
        references kursevi (ku_id)
);

insert into e_prof_na(pn_id, p_id, ku_id)
values (0, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'vangel.ajanovski'),
        (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - VA')),
       (1, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'sasho.gramatikov'),
        (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Veb programiranje')),
       (2, (select p.p_id from profesori as p where p.p_korisnicko_ime = 'goran.velinov'),
        (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - GV'));

drop table e_vo_vrska_so;
create table e_vo_vrska_so
(
    vs_id  serial primary key,
    pra_id integer not null,
    m_id   integer not null,
    constraint fk_e_vp_vrska_so_prasanja foreign key (pra_id)
        references prasanja (pra_id),
    constraint fk_e_vp_vrska_so_materijali foreign key (m_id)
        references materijali (m_id)
);

insert into e_vo_vrska_so(vs_id, pra_id, m_id)
values (0, (select pra.pra_id from prasanja as pra where pra.pra_naslov = 'Kako da ponistam transakcija'),
        (select m.m_id from materijali as m where m.m_ime = 'Database System Concepts')),
       (1, (select pra.pra_id
            from prasanja as pra
            where pra.pra_naslov = 'Ne mozam da pristapam do delot za rabota so bazi vo IntelliJ'),
        (select m.m_id from materijali as m where m.m_ime = 'Spring Data Persistence')),
       (2, (select pra.pra_id
            from prasanja as pra
            where pra.pra_naslov = 'Tip na podatok za datoteki vo PostgreSQL'),
        (select m.m_id from materijali as m where m.m_ime = 'Database System Concepts'));
       
drop table moze_da_slusa;
create table moze_da_slusa
(
    mds_id serial primary key,
    s_id   integer not null,
    ku_id  integer not null,
    constraint fk_moze_da_slusa_studenti foreign key (s_id)
        references studenti (s_id),
    constraint fk_moze_da_slusa_kursevi foreign key (ku_id)
        references kursevi (ku_id)
);

insert into moze_da_slusa(mds_id, s_id, ku_id)
values (0, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'angel.nasev'),
        (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - VA')),
       (1, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'angel.nasev'),
        (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Veb programiranje')),
       (2, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'lina.nakova'),
        (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - VA')),
       (3, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'lina.nakova'),
        (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Veb programiranje')),
       (4, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'katerina.smileska'),
        (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Bazi na podatoci - GV')),
       (5, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'katerina.smileska'),
        (select ku.ku_id from kursevi as ku where ku.ku_ime = 'Veb programiranje'));

drop table reagira_na;
create table reagira_na
(
    rn_id    serial primary key,
    s_id     integer not null,
    o_id     integer not null,
    reakcija boolean not null,
    constraint fk_reagira_na_studenti foreign key (s_id)
        references studenti (s_id),
    constraint fk_reagira_na_odgovori foreign key (o_id)
        references odgovori (o_id)
);

insert into reagira_na(rn_id, s_id, o_id, reakcija)
values (0, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'angel.nasev'),
        (select o.o_id
         from odgovori as o
         where o.o_sodrzina =
               'Ovaa funkcionalnost e dostapna samo kaj IntelliJ Ultimate. Mozete da go koristite besplatno so vnesuvanje na vasiot studentski mail'),
        false),
       (1, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'lina.nakova'),
        (select o.o_id
         from odgovori as o
         where o.o_sodrzina =
               'Ovaa funkcionalnost e dostapna samo kaj IntelliJ Ultimate. Mozete da go koristite besplatno so vnesuvanje na vasiot studentski mail'),
        true),
       (2, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'angel.nasev'),
        (select o.o_id
         from odgovori as o
         where o.o_sodrzina =
               'Dobar nacin za cuvanje datoteki e ako se koristi atribut od tip text koj ke sodrzi pateka do dokumentot'),
        true),
       (3, (select s.s_id from studenti as s where s.s_korisnicko_ime = 'katerina.smileska'),
        (select o.o_id
         from odgovori as o
         where o.o_sodrzina =
               'Za vrakanje na prvicnata sostojba treba da se koristi rollback komandata'),
        true);

drop table materijali cascade;
create table materijali
(
    m_id  serial,
    m_ime varchar(150) not null,
    p_id  integer      not null,
    ka_id integer,
    ku_id integer      not null,
    constraint pk_materijali primary key (m_id),
    constraint fk_materijali_profesori foreign key (p_id)
        references profesori (p_id),
    constraint fk_materijali_kategorii foreign key (ka_id)
        references kategorii (ka_id),
    constraint fk_materijali_kursevi foreign key (ku_id)
        references kursevi (ku_id)
);

