CREATE DATABASE sportska_oprema;
USE sportska_oprema;
CREATE TABLE snabdevaci(id INT(4) AUTO_INCREMENT NOT NULL,
						naziv VARCHAR(20) NOT NULL,
						mesto VARCHAR(20) NOT NULL,
						telefon VARCHAR(20) NOT NULL,
						adresa VARCHAR(100) NOT NULL,
						CONSTRAINT snabdevaci_pk PRIMARY KEY(id));

INSERT INTO snabdevaci (id, naziv, mesto, telefon, adresa)
		VALUES (NULL, 'Nike', 'Petrovac', '+3816767676', 'Urosa Mrnjavcevica 67'),
		(NULL, 'Adidas', 'Stari Mlin', '+3816868686', 'Veljka Popovica 68'),
		(NULL, 'Puma', 'Banovo brdo', '+3816969696', 'Nikolic Teodore 69');

CREATE TABLE oprema(id INT(4) AUTO_INCREMENT NOT NULL,
					naziv VARCHAR(20) NOT NULL,
					vrsta VARCHAR(20) NOT NULL,
					nabavna_cena INT NOT NULL,
					prodajna_cena INT NOT NULL,
					stanje INT NOT NULL,
					id_snabdevaca INT(4) NOT NULL,
					CONSTRAINT oprema_pk PRIMARY KEY(id),
					CONSTRAINT snabdevaci_oprema_fk FOREIGN KEY(id_snabdevaca) REFERENCES snabdevaci(id));

INSERT INTO oprema (id, naziv, vrsta, nabavna_cena, prodajna_cena, stanje, id_snabdevaca)
	VALUES (NULL, 'AirMax 1', 'Patike', '6000', '16000', '1000', '1'),
	(NULL, 'AirMax 90', 'Patike', '8000', '19000', '650', '1'),
	(NULL, 'Huarache', 'Patike', '8000', '17000', '500', '1'),
	(NULL, 'Power UV Polo Majica', 'Odeca', '2000', '6000', '150', '1'),
	(NULL, 'Sphere woven pant', 'Odeca', '5000', '15000', '300', '1'),
	(NULL, 'Linear Team Bag', 'Torbe', '2000', '4000', '270', '2'),
	(NULL, 'Linear Gymsack', 'Torbe', '500', '1500', '400', '2'),
	(NULL, 'Alphaskin Sports Bra', 'Odeca', '1000', '7000', '550', '2'),
	(NULL, 'Celeb Jacket', 'Odeca', '3000', '14000', '140', '2'),
	(NULL, 'Nova 14 Short Mens', 'Odeca', '1300', '5000', '450', '2'),
	(NULL, 'LFS TSUGI KORI', 'Patike', '3000', '18000', '340', '3'),
	(NULL, 'TS DRES SERBIA HOME', 'Odeca', '1500', '9000', '335', '3'),
	(NULL, 'TS KOPACKE EVOPOWER', 'Patike', '6000', '25000', '650', '3'),
	(NULL, 'LFS ARCHIVE', 'Odeca', '1600', '4800', '110', '3'),
	(NULL, 'PRIME LARGE SHOPPER', 'Torbe', '600', '4400', '100', '3');

CREATE VIEW proizvodi(Naziv, Kategorija, Nabavna_cena, Prodajna_cena, Stanje, Snabdevac)
	AS SELECT o.naziv, vrsta, nabavna_cena, prodajna_cena, stanje, s.naziv
	FROM oprema o, snabdevaci s
	WHERE s.id = o.id_snabdevaca;

CREATE VIEW dobavljaci(Naziv, Telefon, Adresa)
	AS SELECT naziv, telefon, CONCAT(adresa, ', ', mesto)
	FROM snabdevaci;