CREATE TABLE IF NOT EXISTS kategorije (
id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
kategorija VARCHAR(50),
opis VARCHAR(255),
PRIMARY KEY(id))
CHARACTER SET utf8
COLLATE utf8_general_ci
ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS artikli (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
naziv VARCHAR(50),
cijena DOUBLE(10,2) UNSIGNED,
kategorija_id MEDIUMINT(8) UNSIGNED NOT NULL,
PRIMARY KEY(id),
INDEX (kategorija_id),
FOREIGN KEY(kategorija_id) REFERENCES kategorije(id)
ON DELETE RESTRICT)
CHARACTER SET utf8
COLLATE utf8_general_ci
ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS musterije (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ime VARCHAR(50),
prezime VARCHAR(100),
email VARCHAR(100),
adresa VARCHAR(255),
grad VARCHAR(100),
drzava VARCHAR(100),
PRIMARY KEY(id),
INDEX USING BTREE (ime),
INDEX USING BTREE (prezime),
UNIQUE INDEX USING BTREE (email))
CHARACTER SET utf8
COLLATE utf8_general_ci
ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS korpe (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
musterija_id INT UNSIGNED,
PRIMARY KEY(id),
INDEX (musterija_id),
FOREIGN KEY (musterija_id) REFERENCES musterije(id)
ON DELETE CASCADE)
ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS narudzbe (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
artikl_id INT UNSIGNED,
korpa_id INT UNSIGNED,
kolicina MEDIUMINT UNSIGNED,
PRIMARY KEY(id),
INDEX (artikl_id),
INDEX (korpa_id),
FOREIGN KEY (artikl_id) REFERENCES artikli(id)
ON DELETE CASCADE,
FOREIGN KEY (korpa_id) REFERENCES korpe (id)
ON DELETE CASCADE)
ENGINE=InnoDB;