-- --------------------------------------------------------

--
-- Table structure for table 'aaa_akcije'
--
DROP TABLE IF EXISTS aaa_akcije;
CREATE TABLE aaa_akcije (
  id SERIAL,
  naslov varchar(255) NOT NULL,
  uvod text NOT NULL,
  tekst text NOT NULL,
  PRIMARY KEY (id)
);

-- --------------------------------------------------------

--
-- Table structure for table 'aaa_info'
--
DROP TABLE IF EXISTS aaa_info;
CREATE TABLE aaa_info (
  id SERIAL,
  tekst text NOT NULL,
  PRIMARY KEY (id)
);

-- --------------------------------------------------------

--
-- Table structure for table 'aaa_korisnici'
--
DROP TABLE IF EXISTS aaa_korisnici;
CREATE TABLE aaa_korisnici (
  id SERIAL,
  korisnik varchar(10) NOT NULL,
  lozinka varchar(255) NOT NULL,
  rang SMALLINT NOT NULL,
  PRIMARY KEY (id)
);

-- --------------------------------------------------------

--
-- Table structure for table 'aaa_porudzbine'
--
DROP TABLE IF EXISTS aaa_porudzbine;
CREATE TABLE aaa_porudzbine (
  id SERIAL,
  datum date NOT NULL,
  komitent varchar(255) NOT NULL,
  placanje varchar(255) NOT NULL,
  dostava varchar(255) NOT NULL,
  adresa varchar(255) NOT NULL,
  status SMALLINT NOT NULL DEFAULT '0',
  PRIMARY KEY (id)
);

--
-- Table structure for table 'aaa_privremene_stavke'
--
DROP TABLE IF EXISTS aaa_privremene_stavke;
CREATE TABLE aaa_privremene_stavke (
  komitent varchar(255) NOT NULL,
  artikal varchar(255) NOT NULL,
  kolicina DOUBLE PRECISION NOT NULL
);

-- --------------------------------------------------------

--
-- Table structure for table 'aaa_proizvoddana'
--
DROP TABLE IF EXISTS aaa_proizvoddana;
CREATE TABLE aaa_proizvoddana (
  id SERIAL,
  sifra varchar(255) NOT NULL,
  PRIMARY KEY (id)
);

-- --------------------------------------------------------

--
-- Table structure for table 'aaa_sadrzaj'
--
DROP TABLE IF EXISTS aaa_sadrzaj;
CREATE TABLE aaa_sadrzaj (
  id SERIAL,
  naslov varchar(255) NOT NULL,
  tip varchar(255) NOT NULL,
  slug varchar(255) NOT NULL,
  tekst text NOT NULL,
  sort INTEGER DEFAULT '0',
  PRIMARY KEY (id)
);

-- --------------------------------------------------------

--
-- Table structure for table 'aaa_sakrivene'
--
DROP TABLE IF EXISTS aaa_sakrivene;
CREATE TABLE aaa_sakrivene (
  oznrobgr varchar(20) NOT NULL,
  PRIMARY KEY (oznrobgr)
);

-- --------------------------------------------------------

--
-- Table structure for table 'aaa_slajder'
--
DROP TABLE IF EXISTS aaa_slajder;
CREATE TABLE aaa_slajder (
  id SERIAL,
  naslov varchar(255) NOT NULL,
  tekst text NOT NULL,
  link text NOT NULL,
  PRIMARY KEY (id)
);

-- --------------------------------------------------------

--
-- Table structure for table 'aaa_stavke'
--
DROP TABLE IF EXISTS aaa_stavke;
CREATE TABLE aaa_stavke (
  id SERIAL,
  id_porudzbine INTEGER NOT NULL,
  artikal varchar(255) NOT NULL,
  kolicina DOUBLE PRECISION NOT NULL,
  cena DOUBLE PRECISION NOT NULL,
  PRIMARY KEY (id)
);

--
-- Table structure for table 'aaa_web_kupci'
--
DROP TABLE IF EXISTS aaa_web_kupci;
CREATE TABLE aaa_web_kupci (
  id SERIAL,
  ime varchar(255) NOT NULL,
  prezime varchar(255) NOT NULL,
  korisnik varchar(255) NOT NULL,
  lozinka varchar(255) NOT NULL,
  preduzece varchar(255) NOT NULL,
  pib varchar(255) NOT NULL,
  email varchar(255) NOT NULL,
  adresa varchar(255) NOT NULL,
  grad varchar(255) NOT NULL,
  pbroj varchar(255) NOT NULL,
  telefon varchar(255) NOT NULL,
  mobilni varchar(255) NOT NULL,
  PRIMARY KEY (id)
);

-- --------------------------------------------------------

--
-- Table structure for table 'aaa_web_naslovna'
--
DROP TABLE IF EXISTS aaa_web_naslovna;
CREATE TABLE aaa_web_naslovna (
  id SERIAL,
  artikal varchar(255) NOT NULL,
  PRIMARY KEY (id)
);

-- --------------------------------------------------------

--
-- Table structure for table 'aaa_web_newsletter'
--
DROP TABLE IF EXISTS aaa_web_newsletter;
CREATE TABLE aaa_web_newsletter (
  id SERIAL,
  email varchar(255) NOT NULL,
  vreme timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status SMALLINT NOT NULL DEFAULT '0',
  PRIMARY KEY (id)
);

-- --------------------------------------------------------

--
-- Table structure for table 'aaa_web_porudzbine'
--
DROP TABLE IF EXISTS aaa_web_porudzbine;
CREATE TABLE aaa_web_porudzbine (
  id SERIAL,
  datum timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  komitent INTEGER NOT NULL,
  placanje varchar(255) NOT NULL,
  dostava varchar(255) NOT NULL,
  napomena text NOT NULL,
  status SMALLINT NOT NULL DEFAULT '0',
  PRIMARY KEY (id)
);

-- --------------------------------------------------------

--
-- Table structure for table 'aaa_web_stavke'
--
DROP TABLE IF EXISTS aaa_web_stavke;
CREATE TABLE aaa_web_stavke (
  id SERIAL,
  id_porudzbine INTEGER NOT NULL,
  artikal varchar(255) NOT NULL,
  kolicina DOUBLE PRECISION NOT NULL,
  cena DOUBLE PRECISION NOT NULL,
  PRIMARY KEY (id)
);

--
-- Constraints for dumped tables
--

--
-- Constraints for table "aaa_sakrivene"
--
ALTER TABLE "aaa_sakrivene"
  ADD CONSTRAINT aaa_sakrivene_ibfk_1 FOREIGN KEY (oznrobgr) REFERENCES robnagrupa (oznrobgr) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table "aaa_stavke"
--
ALTER TABLE "aaa_stavke"
  ADD CONSTRAINT aaa_stavke_ibfk_1 FOREIGN KEY (id_porudzbine) REFERENCES aaa_porudzbine (id) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table "aaa_web_porudzbine"
--
ALTER TABLE "aaa_web_porudzbine"
  ADD CONSTRAINT aaa_web_porudzbine_ibfk_1 FOREIGN KEY (komitent) REFERENCES aaa_web_kupci (id) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table "aaa_web_stavke"
--
ALTER TABLE "aaa_web_stavke"
  ADD CONSTRAINT aaa_web_stavke_ibfk_1 FOREIGN KEY (id_porudzbine) REFERENCES aaa_web_porudzbine (id) ON DELETE CASCADE ON UPDATE CASCADE;



DROP VIEW IF EXISTS webartikal;
CREATE VIEW webartikal
AS
SELECT a.sfrproizvod AS sfrproizvod
	,a.bliziopis AS bliziopis
	,a.naziv AS naziv
	,a.jedmere AS jedmere
	,s.prodcena AS prodcenabezpdv
	,(s.prodcena + ((s.prodcena * t.stopa) / 100)) AS prodcenasapdv
	,(s.kolicina - s.alockolicina) AS raspkolicina
	,t.stopa AS stopapdv
	,a.opis AS opis
	,a.dobsifra AS dobsifra
	,a.standpak AS standpak
	,a.tezina AS tezina
	,a.oznrobgr AS oznrobgr
	,a.dobavljac AS dobavljac
	,a.proizvodjac AS proizvodjac
	,a.oznmodel AS oznmodel
	,a.duzina AS duzina
	,a.sirina AS sirina
	,a.visina AS visina
	,a.fi AS fi
	,a.katbroj AS katbroj
FROM (
	(
		(
			artikal a JOIN stanje s ON (
					(
						(a.sfrproizvod = s.sfrproizvod)
						AND (s.oznrj = ###)
						)
					)
			) LEFT JOIN tgartikal tga ON ((a.sfrproizvod = tga.sfrproizvod))
		) LEFT JOIN tarifnagrupa t ON (tga.ozntargr = t.ozntargr)
	);

-------------------------------------------------------------------------------------------------------------
DROP VIEW IF EXISTS aaawebporudzbina_stavke;
CREATE VIEW aaawebporudzbina_stavke
AS
SELECT aaa_web_stavke.id AS id
	,aaa_web_stavke.id_porudzbine AS id_porudzbine
	,aaa_web_stavke.artikal AS artikal
	,aaa_web_stavke.kolicina AS kolicina
	,aaa_web_stavke.cena AS cena
	,artikal.naziv AS naziv
FROM 
	aaa_web_stavke , artikal
	
WHERE (aaa_web_stavke.artikal = artikal.sfrproizvod);

-------------------------------------------------------------------------------------------------------------
DROP VIEW IF EXISTS aaawebporudzbine;
CREATE VIEW aaawebporudzbine
AS
SELECT aaa_web_porudzbine.id AS id
	,aaa_web_porudzbine.datum AS datum
	,aaa_web_porudzbine.komitent AS komitent
	,aaa_web_porudzbine.STATUS AS STATUS
	,sum((aaa_web_stavke.kolicina * aaa_web_stavke.cena)) AS iznos
FROM 
	aaa_web_porudzbine , aaa_web_stavke
	
WHERE (aaa_web_porudzbine.id = aaa_web_stavke.id_porudzbine)
GROUP BY aaa_web_porudzbine.id
	,aaa_web_porudzbine.datum
	,aaa_web_porudzbine.komitent;

-------------------------------------------------------------------------------------------------------------
DROP VIEW IF EXISTS aaawebporudzbine_imena;	
CREATE VIEW aaawebporudzbine_imena
AS
SELECT 	aaa_web_kupci.ime AS ime
	,aaa_web_kupci.prezime AS prezime
	,aaa_web_porudzbine.id AS id
	,aaa_web_porudzbine.datum AS datum
	,aaa_web_porudzbine.komitent AS komitent
	,aaa_web_porudzbine.STATUS AS STATUS
	,sum((aaa_web_stavke.kolicina * aaa_web_stavke.cena)) AS iznos
FROM 
	
		aaa_web_kupci , aaa_web_porudzbine
		 , aaa_web_stavke
	
WHERE (
		(aaa_web_kupci.id = aaa_web_porudzbine.komitent)
		AND (aaa_web_porudzbine.id = aaa_web_stavke.id_porudzbine)
		)
GROUP BY
	aaa_web_kupci.ime
	,aaa_web_kupci.prezime
	,aaa_web_porudzbine.id
	,aaa_web_porudzbine.datum
	,aaa_web_porudzbine.komitent;

-------------------------------------------------------------------------------------------------------------
DROP VIEW IF EXISTS webkupac;
CREATE VIEW webkupac
AS
SELECT k.sfrkomitent AS sfrkupac
	,k.naziv AS naziv
	,k.adresa AS adresa
	,k.postbroj AS postbroj
	,k.grad AS grad
	,k.matbroj AS matbroj
	,k.pib AS pib
	,k.email AS email
	,k.brfaxa AS brfaxa
	,k.kontakt AS kontakt
	,k.opis AS opis
	,username.value AS web_username
	,password.value AS web_password
	,rs.procenat AS proc_rabata
FROM 
	komitent k LEFT JOIN rabatnaskala rs ON ((k.sfrkomitent = rs.sfrkomitent))
				, komitentparameter dozvola
			,komitentparameter username
		, komitentparameter password
	
WHERE (
		(k.sfrkomitent = dozvola.sfrkomitent)
		AND (k.sfrkomitent = username.sfrkomitent)
		AND (k.sfrkomitent = password.sfrkomitent)
		AND (dozvola.idparameter = 'DOZVOLA')
		AND (username.idparameter = 'DOZVOLA_KORISNICKO_IME')
		AND (password.idparameter = 'DOZVOLA_LOZINKA')
		);

-------------------------------------------------------------------------------------------------------------		
DROP VIEW IF EXISTS webporudzbina_stavke;
CREATE VIEW webporudzbina_stavke
AS
SELECT aaa_stavke.id AS id
	,aaa_stavke.id_porudzbine AS id_porudzbine
	,aaa_stavke.artikal AS artikal
	,aaa_stavke.kolicina AS kolicina
	,aaa_stavke.cena AS cena
	,artikal.naziv AS naziv
FROM 
	aaa_stavke , artikal
	
WHERE (aaa_stavke.artikal = artikal.sfrproizvod);

-------------------------------------------------------------------------------------------------------------
DROP VIEW IF EXISTS webporudzbine;
CREATE VIEW webporudzbine
AS
SELECT aaa_porudzbine.id AS id
	,aaa_porudzbine.datum AS datum
	,aaa_porudzbine.komitent AS komitent
	,sum((aaa_stavke.kolicina * aaa_stavke.cena)) AS iznos
FROM 
	aaa_porudzbine , aaa_stavke
	
WHERE (aaa_porudzbine.id = aaa_stavke.id_porudzbine)
GROUP BY aaa_porudzbine.id
	,aaa_porudzbine.datum
	,aaa_porudzbine.komitent;

-------------------------------------------------------------------------------------------------------------	
DROP VIEW IF EXISTS webproizvodjac;
CREATE VIEW webproizvodjac
AS
SELECT DISTINCT komitent.sfrkomitent AS sfrkomitent
	,komitent.naziv AS naziv
FROM 
	komitent JOIN artikal ON ((komitent.sfrkomitent = artikal.proizvodjac))
	;

-------------------------------------------------------------------------------------------------------------
DROP VIEW IF EXISTS web_proizvodi_naslovna;
CREATE VIEW web_proizvodi_naslovna
AS
SELECT webartikal.sfrproizvod AS sfrproizvod
	,webartikal.bliziopis AS bliziopis
	,webartikal.naziv AS naziv
	,webartikal.jedmere AS jedmere
	,webartikal.prodcenabezpdv AS prodcenabezpdv
	,webartikal.prodcenasapdv AS prodcenasapdv
	,webartikal.raspkolicina AS raspkolicina
	,webartikal.stopapdv AS stopapdv
	,webartikal.opis AS opis
	,webartikal.dobsifra AS dobsifra
	,webartikal.standpak AS standpak
	,webartikal.tezina AS tezina
	,webartikal.oznrobgr AS oznrobgr
	,webartikal.dobavljac AS dobavljac
	,webartikal.proizvodjac AS proizvodjac
	,webartikal.oznmodel AS oznmodel
	,webartikal.duzina AS duzina
	,webartikal.sirina AS sirina
	,webartikal.visina AS visina
	,webartikal.fi AS fi
	,robnagrupa.naziv AS naziv_grupe
FROM 
	webartikal , robnagrupa
	
WHERE (
		webartikal.sfrproizvod IN (
			SELECT aaa_web_naslovna.artikal
			FROM aaa_web_naslovna
			)
		AND (webartikal.oznrobgr = robnagrupa.oznrobgr)
		)
ORDER BY webartikal.naziv;
