CREATE TABLE `stav_pracovnika` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`nazov` VARCHAR(255) NOT NULL,
	`poradie` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

INSERT INTO `stav_pracovnika` (`id`, `nazov`, `poradie`) VALUES (1, 'Nový', 10);
INSERT INTO `stav_pracovnika` (`id`, `nazov`, `poradie`) VALUES (2, 'Na spracovanie', 20);
INSERT INTO `stav_pracovnika` (`id`, `nazov`, `poradie`) VALUES (3, 'Aktívny', 30);
INSERT INTO `stav_pracovnika` (`id`, `nazov`, `poradie`) VALUES (4, 'Archivovaný', 40);

CREATE TABLE `faktor_pri_praci` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`nazov` VARCHAR(255) NOT NULL,
	`poradie` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
	COLLATE='utf8_general_ci'
	ENGINE=InnoDB
;

INSERT INTO `faktor_pri_praci` (`id`, `nazov`, `poradie`) VALUES (1, 'Hluk', 10);
INSERT INTO `faktor_pri_praci` (`id`, `nazov`, `poradie`) VALUES (2, 'Vibrácie', 20);
INSERT INTO `faktor_pri_praci` (`id`, `nazov`, `poradie`) VALUES (3, 'Ionizačné žiarenie', 30);
INSERT INTO `faktor_pri_praci` (`id`, `nazov`, `poradie`) VALUES (4, 'Elektromagnetické žiarenie', 40);
INSERT INTO `faktor_pri_praci` (`id`, `nazov`, `poradie`) VALUES (5, 'Ultrafialové žiarenie', 50);
INSERT INTO `faktor_pri_praci` (`id`, `nazov`, `poradie`) VALUES (6, 'Infračervené žiarenie', 60);
INSERT INTO `faktor_pri_praci` (`id`, `nazov`, `poradie`) VALUES (7, 'Laserové žiarenie', 70);
INSERT INTO `faktor_pri_praci` (`id`, `nazov`, `poradie`) VALUES (8, 'Tepelno-vlkostná mikroklíma', 80);
INSERT INTO `faktor_pri_praci` (`id`, `nazov`, `poradie`) VALUES (9, 'Chemický faktor', 90);
INSERT INTO `faktor_pri_praci` (`id`, `nazov`, `poradie`) VALUES (10, 'Karcinogénny a mutagénny faktor', 100);
INSERT INTO `faktor_pri_praci` (`id`, `nazov`, `poradie`) VALUES (11, 'Biologický faktor', 110);
INSERT INTO `faktor_pri_praci` (`id`, `nazov`, `poradie`) VALUES (12, 'Fyzická záťaž', 120);
INSERT INTO `faktor_pri_praci` (`id`, `nazov`, `poradie`) VALUES (13, 'Psychická pracovná záťaž', 130);
INSERT INTO `faktor_pri_praci` (`id`, `nazov`, `poradie`) VALUES (14, 'Zvýšený tlak vzduchu', 140);

CREATE TABLE `kategoria_prace` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`nazov` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`id`)
)
	COLLATE='utf8_general_ci'
	ENGINE=InnoDB
;

INSERT INTO `kategoria_prace` (`id`, `nazov`) VALUES (1, '1');
INSERT INTO `kategoria_prace` (`id`, `nazov`) VALUES (2, '2');
INSERT INTO `kategoria_prace` (`id`, `nazov`) VALUES (3, '3');
INSERT INTO `kategoria_prace` (`id`, `nazov`) VALUES (4, '4');


CREATE TABLE `profesia` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`nazov` VARCHAR(255) NOT NULL,
	`poradie` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
	COLLATE='utf8_general_ci'
	ENGINE=InnoDB
;

INSERT INTO `profesia` (`id`, `nazov`, `poradie`) VALUES (1, 'Zvárač', 10);
INSERT INTO `profesia` (`id`, `nazov`, `poradie`) VALUES (2, 'Vodič', 20);
INSERT INTO `profesia` (`id`, `nazov`, `poradie`) VALUES (3, 'Obsluha kotla I. až V. triedy', 30);

CREATE TABLE `pracovnik` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`nazov` VARCHAR(255) NULL DEFAULT NULL,
	`priezvisko` VARCHAR(255) NULL DEFAULT NULL,
	`meno` VARCHAR(255) NULL DEFAULT NULL,
	`email` VARCHAR(255) NULL DEFAULT NULL,
	`visibility_type` TINYINT(1) UNSIGNED NULL DEFAULT NULL,
	`stav_pracovnika_id` INT(11) UNSIGNED NULL DEFAULT NULL,
	`datum_narodenia` DATE NULL DEFAULT NULL,
	`datum_nastupu` DATE NULL DEFAULT NULL,
	`obchodny_partner_id` INT(11) UNSIGNED NULL DEFAULT NULL,
	`prevadzka_id` INT(11) UNSIGNED NULL DEFAULT NULL,
	`pracovisko` VARCHAR(255) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `pracovnik_stav_pracovnika_id_FK` (`stav_pracovnika_id`),
	INDEX `pracovnik_obchodny_partner_id_FK` (`obchodny_partner_id`),
	INDEX `pracovnik_prevadzka_id_FK` (`prevadzka_id`),
	CONSTRAINT `pracovnik_obchodny_partner_id_FK` FOREIGN KEY (`obchodny_partner_id`) REFERENCES `obchodny_partner` (`id`),
	CONSTRAINT `pracovnik_prevadzka_id_FK` FOREIGN KEY (`prevadzka_id`) REFERENCES `majetok` (`id`),
	CONSTRAINT `pracovnik_stav_pracovnika_id_FK` FOREIGN KEY (`stav_pracovnika_id`) REFERENCES `stav_pracovnika` (`id`)
)
	COLLATE='utf8_general_ci'
	ENGINE=InnoDB
;

CREATE TABLE `pracovnik_pozicia` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`nazov` VARCHAR(255) NULL DEFAULT NULL,
	`pracovnik_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`profesia_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`zaciatok` DATE NULL DEFAULT NULL,
	`koniec` DATE NULL DEFAULT NULL,
	`created_at` DATETIME NULL DEFAULT NULL,
	`updated_at` DATETIME NULL DEFAULT NULL,
	`created_by` INT(10) UNSIGNED NULL DEFAULT NULL,
	`visibility_type` TINYINT(1) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `pracovnik_pozicia_pracovnik_id_FK` (`pracovnik_id`),
	INDEX `pracovnik_pozicia_created_by_FK` (`created_by`),
	INDEX `pracovnik_pozicia_profesia_id_FK` (`profesia_id`),
	CONSTRAINT `pracovnik_pozicia_created_by_FK` FOREIGN KEY (`created_by`) REFERENCES `zamestnanec` (`id`),
	CONSTRAINT `pracovnik_pozicia_pracovnik_id_FK` FOREIGN KEY (`pracovnik_id`) REFERENCES `pracovnik` (`id`),
	CONSTRAINT `pracovnik_pozicia_profesia_id_FK` FOREIGN KEY (`profesia_id`) REFERENCES `profesia` (`id`)
)
	COLLATE='utf8_general_ci'
	ENGINE=InnoDB
;

CREATE TABLE `pracovnik_kategoria_prace` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`pracovnik_pozicia_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`faktor_pri_praci_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`kategoria_prace_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`namerane_hodnoty` TEXT NULL,
	`created_at` DATETIME NULL DEFAULT NULL,
	`updated_at` DATETIME NULL DEFAULT NULL,
	`created_by` INT(10) UNSIGNED NULL DEFAULT NULL,
	`visibility_type` TINYINT(1) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `pracovnik_kategoria_prace_pracovnik_pozicia_id_FK` (`pracovnik_pozicia_id`),
	INDEX `pracovnik_kategoria_prace_created_by_FK` (`created_by`),
	INDEX `pracovnik_kategoria_prace_kategoria_prace_id_FK` (`kategoria_prace_id`),
	INDEX `pracovnik_kategoria_prace_faktor_pri_praci_id_FK` (`faktor_pri_praci_id`),
	CONSTRAINT `pracovnik_kategoria_prace_created_by_FK` FOREIGN KEY (`created_by`) REFERENCES `zamestnanec` (`id`),
	CONSTRAINT `pracovnik_kategoria_prace_faktor_pri_praci_id_FK` FOREIGN KEY (`faktor_pri_praci_id`) REFERENCES `faktor_pri_praci` (`id`),
	CONSTRAINT `pracovnik_kategoria_prace_kategoria_prace_id_FK` FOREIGN KEY (`kategoria_prace_id`) REFERENCES `kategoria_prace` (`id`),
	CONSTRAINT `pracovnik_kategoria_prace_pracovnik_pozicia_id_FK` FOREIGN KEY (`pracovnik_pozicia_id`) REFERENCES `pracovnik_pozicia` (`id`)
)
	COLLATE='utf8_general_ci'
	ENGINE=InnoDB
;

CREATE TABLE `typ_skolenia` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`nazov` VARCHAR(255) NULL DEFAULT NULL,
	`code` VARCHAR(255) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
	COLLATE='utf8_general_ci'
	ENGINE=InnoDB
;

INSERT INTO `typ_skolenia` (`id`, `nazov`, `code`) VALUES (1, 'Lekárska preventívna prehliadka', 'LPP');
INSERT INTO `typ_skolenia` (`id`, `nazov`, `code`) VALUES (2, 'Rekondičný pobyt', 'RP');

CREATE TABLE `skolenie` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`nazov` VARCHAR(255) NULL DEFAULT NULL,
	`datum` DATE NULL DEFAULT NULL,
	`pracovnik_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`typ_skolenia_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`popis` TEXT NULL,
	`created_at` DATETIME NULL DEFAULT NULL,
	`updated_at` DATETIME NULL DEFAULT NULL,
	`created_by` INT(10) UNSIGNED NULL DEFAULT NULL,
	`visibility_type` TINYINT(1) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `skolenie_pracovnik_id_FK` (`pracovnik_id`),
	INDEX `skolenie_typ_skolenia_id_FK` (`typ_skolenia_id`),
	INDEX `skolenie_created_by_FK` (`created_by`),
	CONSTRAINT `skolenie_created_by_FK` FOREIGN KEY (`created_by`) REFERENCES `zamestnanec` (`id`),
	CONSTRAINT `skolenie_pracovnik_id_FK` FOREIGN KEY (`pracovnik_id`) REFERENCES `pracovnik` (`id`),
	CONSTRAINT `skolenie_typ_skolenia_id_FK` FOREIGN KEY (`typ_skolenia_id`) REFERENCES `typ_skolenia` (`id`)
)
	COLLATE='utf8_general_ci'
	ENGINE=InnoDB
;

CREATE TABLE `pokrytie_skolenia` (
	`pracovnik_kategoria_prace_id` INT(10) UNSIGNED NOT NULL,
	`skolenie_id` INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY (`pracovnik_kategoria_prace_id`, `skolenie_id`),
	INDEX `pokrytie_skolenia_pracovnik_skolenie_id_FK` (`skolenie_id`),
	CONSTRAINT `pokrytie_skolenia_pracovnik_kategoria_prace_id_FK` FOREIGN KEY (`pracovnik_kategoria_prace_id`) REFERENCES `pracovnik_kategoria_prace` (`id`),
	CONSTRAINT `pokrytie_skolenia_pracovnik_skolenie_id_FK` FOREIGN KEY (`skolenie_id`) REFERENCES `skolenie` (`id`)
)
	COLLATE='utf8_general_ci'
	ENGINE=InnoDB
;

UPDATE `menu` SET `nazov`='Zamestnanci - GAJOS' WHERE  `id`=21000;