DROP DATABASE IF EXISTS Poliklinika;
CREATE DATABASE IF NOT EXISTS Poliklinika;
USE Poliklinika;

CREATE TABLE Pacienti
( 
  idP		INT  NOT NULL PRIMARY KEY, 
  krstne	VARCHAR(15),
  mesPrijem	INT
);

CREATE TABLE Lekari
( 
  idL		INT  NOT NULL PRIMARY KEY, 
  krstne	VARCHAR(15),
  spec		VARCHAR(20),
  datNar	DATETIME
);

CREATE TABLE Navstevy
( 
  idN		INT  NOT NULL PRIMARY KEY, 
--  idP		INT  NOT NULL  FOREIGN KEY REFERENCES Pacienti(idP), 
  idP		INT  NOT NULL, 
  idL		INT  NOT NULL, 
  den		DATETIME,
  poplatok	INT,
  FOREIGN KEY (idP) REFERENCES Pacienti(idP),
  FOREIGN KEY (idL) REFERENCES Lekari(idL)
);

CREATE TABLE Lieky
( 
  idLi		INT  NOT NULL PRIMARY KEY, 
  nazov     Varchar(20),
  cena		Decimal(6,2)
);

CREATE TABLE Recepty
( 
  idR		INT  NOT NULL PRIMARY KEY, 
  idLi		INT  NOT NULL, 
  FOREIGN KEY (idLi) REFERENCES Lieky(idLi)
);

INSERT Pacienti VALUES	(1, 'Adam',		10000 );
INSERT Pacienti VALUES	(2, 'Stefan',	9500 );
INSERT Pacienti VALUES	(3, 'Slavo',	8500 );
INSERT Pacienti VALUES	(4, 'Klara',	9000 );
INSERT Pacienti VALUES	(5, 'Zuzana',	35000 );
INSERT Pacienti VALUES	(6, 'Tana',		20000 );
INSERT Pacienti VALUES	(7, 'Mato',		28000 );
INSERT Pacienti VALUES	(8, 'Zoli',		32000 );
INSERT Pacienti VALUES	(9, 'Misko',	NULL );
INSERT Pacienti VALUES	(10,'Janka',	NULL );

INSERT Lekari	VALUES	(1, 'Oto',	'Ocny',		'1960.5.5'  );
INSERT Lekari	VALUES	(2, 'Zoli',	'Zubny',	'1961.11.14');
INSERT Lekari	VALUES	(3, 'Klara','Kardiolog','1980.2.15' );
INSERT Lekari	VALUES	(4, 'Zuzka','Zubny',	'1970.4.2'  );
INSERT Lekari	VALUES	(5, 'Imro',	'Interny',	'1956.11.9' );

INSERT Navstevy VALUES	(1, 1, 2, '2008.5.5', NULL );
INSERT Navstevy VALUES	(2, 2, 3, '2008.5.5', NULL );
INSERT Navstevy VALUES	(3, 6, 3, '2008.5.5', NULL);
INSERT Navstevy VALUES	(4, 4, 1, '2008.6.5', 200 );
INSERT Navstevy VALUES	(5, 5, 4, '2008.6.5', 500 );
INSERT Navstevy VALUES	(6, 7, 1, '2008.6.5', 200 );
INSERT Navstevy VALUES	(7, 6, 1, '2008.6.5', 500 );
INSERT Navstevy VALUES	(8, 8, 3, '2008.7.5', 900 );
INSERT Navstevy VALUES	(9, 2, 1, '2008.7.5', 200 );
INSERT Navstevy VALUES	(10,3, 3, '2008.7.5', 100 );
INSERT Navstevy VALUES	(11,6, 2, '2008.8.5', 700 );
INSERT Navstevy VALUES	(12,7, 2, '2008.8.5', 500 );
INSERT Navstevy VALUES	(13,6, 4, '2008.8.5', 800 );
INSERT Navstevy VALUES	(14,2, 1, '2008.9.5', NULL);
INSERT Navstevy VALUES	(15,3, 1, '2008.9.5', 200 );
INSERT Navstevy VALUES	(16,8, 1, '2008.9.5', 200 );
INSERT Navstevy VALUES	(17,9, 5, '2008.9.5', NULL);
INSERT Navstevy VALUES	(18,7, 1, '2008.10.5',300 );
INSERT Navstevy VALUES	(19,8, 4, '2008.10.5',800 );
INSERT Navstevy VALUES	(20,10,5, '2008.10.5',300 );
INSERT Navstevy VALUES	(21,1, 1, '2008.11.5',350 );
INSERT Navstevy VALUES	(22,6, 5, '2008.11.5',400 );

INSERT Lieky VALUES	
(1, "Acylpyrin", 1),
(2, "Panadol", 2),
(3, "Ibalgin", 3),
(4, "Zivocisne uhlie", 4),
(5, "Svedske kvapky", 5),
(6, "Brufein", 5),
(7, "Inzulin", 8),
(8, "XXX", 8),
(9, "YYY", 3),
(10, "ZZZ",2);

INSERT Recepty VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5),
(6, 6),
(7, 7),
(8, 7),
(9, 7),
(10, 6),
(11, 5),
(12, 4),
(13, 3),
(14, 2),
(15, 9);

SELECT * FROM Pacienti;
SELECT * FROM Lekari;
SELECT * FROM Navstevy;
SELECT * FROM Lieky;
SELECT * FROM Recepty;