CREATE TABLE Korisnik
(
    ID_korisnik INTEGER PRIMARY KEY NOT NULL,
    Ime char(20) NOT NULL,
    Prezime char(30) NOT NULL,
    Email char(50) NOT NULL,
    PASSWORD char(20) NOT NULL,
    Korisnicko_ime char(15) NOT NULL,
    Drzava varchar(20),
    Grad varchar(20),
    ZIP INTEGER,
    Ime_ulica char(50),
    Broj_ulica INTEGER,
    Broj_apartman INTEGER
);

CREATE TABLE Koshnica
(
ID_koshnica INTEGER PRIMARY KEY NOT NULL,
Kolichina INTEGER,
Cena_koshnica INTEGER,
ID_korisnik INTEGER REFERENCES Korisnik(ID_korisnik)
);

CREATE TABLE Popust
(
ID_kupon INTEGER PRIMARY KEY NOT NULL,
popust_procenti INTEGER
);

CREATE TABLE Naplata
(
ID_naplata INTEGER PRIMARY KEY NOT NULL,
Nacin_naplata char(20) NOT NULL,
ID_korisnik INTEGER REFERENCES Korisnik(ID_korisnik),
ID_koshnica INTEGER REFERENCES Koshnica(ID_koshnica),
ID_kupon INTEGER REFERENCES Popust(ID_kupon)
);

CREATE TABLE IzdavackaKukja
(
Ime_izdavac char(20)PRIMARY KEY NOT NULL,
Tel_broj_izdavac INTEGER NOT NULL,
Godina_formiranje INTEGER NOT NULL,
Web_izdavac char(30)
);

CREATE TABLE Kniga
(
ID_kniga INTEGER PRIMARY KEY NOT NULL,
Naslov char(60) NOT NULL,
Godina_izdavanje INTEGER NOT NULL,
Cena INTEGER NOT NULL,
Zanr varchar(40) NOT NULL,
Br_prodadeni INTEGER,
Br_strani INTEGER NOT NULL,
Jazik char(10),
Tip_korica CHAR (5) NOT NULL,
Format char(20) not NULL,
Ime_izdavac char(20) REFERENCES IzdavackaKukja(Ime_izdavac)
);

CREATE TABLE Zelbi
(
ID_zelbi INTEGER PRIMARY KEY NOT NULL,
Naslov_knigi char(50) NOT NULL,
ID_korisnik INTEGER REFERENCES Korisnik(ID_korisnik)
);

CREATE TABLE Avtor
(
ID_avtor INTEGER PRIMARY KEY NOT NULL,
Ime_avtor char(20) NOT NULL,
Prezime_avtor char(30) NOT NULL,
Web_avtor CHAR (30)
);

CREATE TABLE Zaliha
(
ID_zaliha INTEGER PRIMARY KEY NOT NULL,
Hartiena_forma INTEGER,
Elektronska_forma INTEGER NOT NULL,
Vkupno_knigi INTEGER NOT NULL,
ID_kniga INTEGER REFERENCES Kniga(ID_kniga)
);


CREATE TABLE Napishana_od
(
ID_kniga INTEGER REFERENCES Kniga(ID_kniga),
ID_avtor INTEGER REFERENCES Avtor(ID_avtor)
);

CREATE TABLE Lista_zelbi
(
ID_zelbi INTEGER REFERENCES Zelbi(ID_zelbi),
ID_kniga INTEGER REFERENCES Kniga(ID_kniga)
);

CREATE TABLE Koshnica_zaliha
(
ID_koshnica INTEGER REFERENCES Koshnica(ID_koshnica),
ID_zaliha INTEGER REFERENCES Zaliha(ID_zaliha)
);

CREATE TABLE Privilegiran
(
ID_korisnik INTEGER REFERENCES Korisnik(ID_korisnik),
ID_privilegiran INTEGER
);

INSERT INTO Korisnik VALUES(001,'Aleksandar','Babic','aco-babic@hotmail.com','111111','137009','Makedonija','Skopje',1000,'Fjodor Dostoevski',26,22);
INSERT INTO Korisnik VALUES(002,'Despina','Klisarova','despina_klisarova@hotmail.com','222222','148002','Makedonija','Skopje',1000,'Branislav Njusic',8,12);
INSERT INTO Korisnik VALUES(003,'Marija','Pakovska','marija.pakovska@yahoo.com','333333','137007','Makedonija','Skopje',1000,'Karposhovo vostanie',4,5);
INSERT INTO Korisnik VALUES(004,'Bojan','Babic','bojan-babic@hotmail.com','2345tew34','EQRTG','Makedonija','Veles',2500,'Goce delcev',10,52);
INSERT INTO Korisnik VALUES(005,'Ivana','Ivanovska','ivana_ivanovska@yahoo.com','ivarqegh','iviva','Makedonija','Kicevo',6250,'Bulevar osloboduvanje',14,42);
INSERT INTO Korisnik VALUES(006,'Lina','Trajkoska','lna_trajkoska@hotmail.com','QE_@1DGTrcle_-ffg','The Mistress','Makedonija','Kicevo',6250,'Aco Shopov',32,79);




INSERT INTO Koshnica VALUES(1999,1,199,001);
INSERT INTO Koshnica VALUES(2000,0,0,002);
INSERT INTO Koshnica VALUES(3999,2,540,003);

INSERT INTO Popust VALUES(56789,30);
INSERT INTO Popust VALUES(67890,30);
INSERT INTO Popust VALUES(87654,20);

INSERT INTO Naplata VALUES(111,'transakcija',001,1999,56789);
INSERT INTO Naplata VALUES(121,'transakcija',002,2000,67890);
INSERT INTO Naplata VALUES(212,'transakcija',003,3999,87654);

INSERT INTO IzdavackaKukja VALUES('Toper',078123456,2011,'toper.com.mk');
INSERT INTO IzdavackaKukja VALUES('Sakam knigi',077123456,2010,'sakamknigi.com.mk');
INSERT INTO IzdavackaKukja VALUES('Tri',071123456,2012,'tri.com.mk');

INSERT INTO Kniga VALUES(98765,'Vo eden moment',2016,199,'Roman',0,302,'makedonski','meka','Elektronska','Toper');
INSERT INTO Kniga VALUES(99765,'Terapija',2016,380,'Triler',0,262,'makedonski','meka','Hartiena','Sakam knigi');
INSERT INTO Kniga VALUES(98965,'Magla',2015,199,'Misterija',0,96,'makedonski','meka','Elektronska','Tri');

INSERT INTO Zelbi VALUES(555,'Vo eden moment',001);
INSERT INTO Zelbi VALUES(666,'Terapija',002);
INSERT INTO Zelbi VALUES(777,'Magla',003);

INSERT INTO Avtor VALUES(34567, 'Gijom', 'Miso', '0');
INSERT INTO Avtor VALUES(45678, 'Sebastijan', 'Ficek', '0');
INSERT INTO Avtor VALUES(56789, 'Paola', 'Barbato', '0');

INSERT INTO Zaliha VALUES(999,10,1000,1010,98765);
INSERT INTO Zaliha VALUES(899,45,1000,1045,99765);
INSERT INTO Zaliha VALUES(799,34,1000,1034,98965);

INSERT INTO Napishana_od VALUES(98765,34567);
INSERT INTO Napishana_od VALUES(99765,45678);
INSERT INTO Napishana_od VALUES(98965,56789);

INSERT INTO Lista_zelbi VALUES(555,98765);
INSERT INTO Lista_zelbi VALUES(666,99765);
INSERT INTO Lista_zelbi VALUES(777,98965);

INSERT INTO Koshnica_zaliha VALUES(1999,999);
INSERT INTO Koshnica_zaliha VALUES(2000,899);
INSERT INTO Koshnica_zaliha VALUES(3999,799);

INSERT INTO Privilegiran VALUES(001,22334);
INSERT INTO Privilegiran VALUES(002,33445);
INSERT INTO Privilegiran VALUES(003,44556);