# SQL Manager 2005 for MySQL 3.6.5.1
# ---------------------------------------
# Host     : localhost
# Port     : 3306
# Database : eqdb


SET FOREIGN_KEY_CHECKS=0;

CREATE DATABASE `eqdb`
    CHARACTER SET 'utf8'
    COLLATE 'utf8_general_ci';

#
# Structure for the `akcija` table : 
#

CREATE TABLE `akcija` (
  `akcija_id` int(11) NOT NULL AUTO_INCREMENT,
  `naziv` varchar(200) NOT NULL,
  PRIMARY KEY (`akcija_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

#
# Structure for the `drzava` table : 
#

CREATE TABLE `drzava` (
  `drzava_id` int(11) NOT NULL AUTO_INCREMENT,
  `naziv` varchar(200) NOT NULL,
  PRIMARY KEY (`drzava_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_history` table : 
#

CREATE TABLE `eqdb_history` (
  `History_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Device_ID` int(11) NOT NULL,
  `Action_ID` int(11) NOT NULL,
  `When_Added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Who_Added` int(11) NOT NULL,
  `Detalis` text,
  PRIMARY KEY (`History_ID`),
  KEY `eqdb_history_fk` (`Action_ID`),
  KEY `Who_Added` (`Who_Added`),
  CONSTRAINT `eqdb_history_fk` FOREIGN KEY (`Action_ID`) REFERENCES `eqdb_action` (`Action_ID`),
  CONSTRAINT `eqdb_history_fk1` FOREIGN KEY (`Who_Added`) REFERENCES `eqdb_user` (`User_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Structure for the `grad` table : 
#

CREATE TABLE `grad` (
  `grad_id` int(11) NOT NULL AUTO_INCREMENT,
  `drzava_id` int(11) NOT NULL,
  `naziv` varchar(200) NOT NULL,
  PRIMARY KEY (`grad_id`),
  KEY `drzava_id` (`drzava_id`),
  CONSTRAINT `grad_fk` FOREIGN KEY (`drzava_id`) REFERENCES `drzava` (`drzava_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

#
# Structure for the `uredjaj` table : 
#

CREATE TABLE `uredjaj` (
  `uredjaj_id` int(11) NOT NULL AUTO_INCREMENT,
  `naziv` varchar(200) NOT NULL,
  `grad_id` int(11) NOT NULL COMMENT 'trenutna lokacija. id grada iz tabele grad',
  `lice_id` int(11) NOT NULL COMMENT 'trenutno odgovorno lice. id lica iz tabele lice',
  `status_id` int(11) NOT NULL COMMENT 'stanje u kom se uredjaj nalazi:\r\nmoze biti: dostupan, na putu, vrca se sa puta, nestao, isporucen',
  PRIMARY KEY (`uredjaj_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

#
# Structure for the `lice` table : 
#

CREATE TABLE `lice` (
  `lice_id` int(11) NOT NULL AUTO_INCREMENT,
  `ime` varchar(200) NOT NULL,
  `prezime` varchar(200) NOT NULL,
  PRIMARY KEY (`lice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

#
# Structure for the `istorija` table : 
#

CREATE TABLE `istorija` (
  `istorija_id` int(11) NOT NULL AUTO_INCREMENT,
  `uredjaj_id` int(11) NOT NULL,
  `grad_id` int(11) NOT NULL,
  `lice_id` int(11) NOT NULL,
  `akcija_id` int(11) NOT NULL,
  `datum` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`istorija_id`),
  KEY `uredjaj_id` (`uredjaj_id`),
  KEY `grad_id` (`grad_id`),
  KEY `lice_id` (`lice_id`),
  KEY `akcija_id` (`akcija_id`),
  CONSTRAINT `istorija_fk3` FOREIGN KEY (`akcija_id`) REFERENCES `akcija` (`akcija_id`),
  CONSTRAINT `istorija_fk` FOREIGN KEY (`uredjaj_id`) REFERENCES `uredjaj` (`uredjaj_id`),
  CONSTRAINT `istorija_fk1` FOREIGN KEY (`grad_id`) REFERENCES `grad` (`grad_id`),
  CONSTRAINT `istorija_fk2` FOREIGN KEY (`lice_id`) REFERENCES `lice` (`lice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Structure for the `prijava` table : 
#

CREATE TABLE `prijava` (
  `prijava_id` int(11) NOT NULL AUTO_INCREMENT,
  `uredjaj_id` int(11) NOT NULL COMMENT 'id uredjaja iz tabele uredjaj koji je prijavljen za putovanje',
  `grad_id` int(11) NOT NULL COMMENT 'id grada iz tabele grad predstavlja lokcaiju na koju uredjaj treba da stigne',
  `lice_id` int(11) NOT NULL COMMENT 'id lica iz tabele lice je lice koje treba da preuzme uredjaj na prijavljenoj lokaciji(ne zaboravite da izmedju ima lica koja transferuju uredjaje)',
  `datum_prispeca` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '0 - zavrsena, 1 - aktivna',
  PRIMARY KEY (`prijava_id`),
  KEY `uredjaj_id` (`uredjaj_id`),
  KEY `grad_id` (`grad_id`),
  KEY `lice_id` (`lice_id`),
  CONSTRAINT `prijava_fk` FOREIGN KEY (`uredjaj_id`) REFERENCES `uredjaj` (`uredjaj_id`),
  CONSTRAINT `prijava_fk1` FOREIGN KEY (`grad_id`) REFERENCES `grad` (`grad_id`),
  CONSTRAINT `prijava_fk2` FOREIGN KEY (`lice_id`) REFERENCES `lice` (`lice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

#
# Structure for the `rezervacija` table : 
#

CREATE TABLE `rezervacija` (
  `rezervacija_id` int(11) NOT NULL AUTO_INCREMENT,
  `uredjaj_id` int(11) NOT NULL,
  `datum_od` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `datum_do` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lice_id` int(11) NOT NULL,
  PRIMARY KEY (`rezervacija_id`),
  KEY `uredjaj_id` (`uredjaj_id`),
  KEY `lice_id` (`lice_id`),
  CONSTRAINT `rezervacija_fk1` FOREIGN KEY (`lice_id`) REFERENCES `lice` (`lice_id`),
  CONSTRAINT `rezervacija_fk` FOREIGN KEY (`uredjaj_id`) REFERENCES `uredjaj` (`uredjaj_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#
# Structure for the `status` table : 
#

CREATE TABLE `status` (
  `status_id` int(11) NOT NULL AUTO_INCREMENT,
  `naziv` varchar(200) NOT NULL,
  PRIMARY KEY (`status_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

#
# Structure for the `transfer` table : 
#

CREATE TABLE `transfer` (
  `transfer_id` int(11) NOT NULL AUTO_INCREMENT,
  `prijava_id` int(11) NOT NULL,
  `lice_id` int(11) NOT NULL,
  PRIMARY KEY (`transfer_id`),
  KEY `prijava_id` (`prijava_id`),
  KEY `lice_id` (`lice_id`),
  CONSTRAINT `transfer_fk1` FOREIGN KEY (`lice_id`) REFERENCES `lice` (`lice_id`),
  CONSTRAINT `transfer_fk` FOREIGN KEY (`prijava_id`) REFERENCES `prijava` (`prijava_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

#
# Data for the `akcija` table  (LIMIT 0,500)
#

INSERT INTO `akcija` (`akcija_id`, `naziv`) VALUES 
  (1,'uredjaj dodat u bazu'),
  (2,'uredjaj izmenjen'),
  (3,'uredjaj prijavljen'),
  (4,'uredjaj krenuo na put'),
  (5,'uredjaj stigao na prijavljeno mesto'),
  (6,'uredjaj se vraca sa puta');

COMMIT;

#
# Data for the `drzava` table  (LIMIT 0,500)
#

INSERT INTO `drzava` (`drzava_id`, `naziv`) VALUES 
  (1,'Srbija'),
  (2,'Francuska');

COMMIT;

#
# Data for the `grad` table  (LIMIT 0,500)
#

INSERT INTO `grad` (`grad_id`, `drzava_id`, `naziv`) VALUES 
  (1,1,'Beograd'),
  (2,1,'Novi Sad'),
  (3,2,'Pariz');

COMMIT;

#
# Data for the `uredjaj` table  (LIMIT 0,500)
#

INSERT INTO `uredjaj` (`uredjaj_id`, `naziv`, `grad_id`, `lice_id`, `status_id`) VALUES 
  (1,'Uredjaj 1',1,1,1),
  (2,'Uredjaj 2',1,1,2),
  (3,'Uredjaj 3',2,2,2);

COMMIT;

#
# Data for the `lice` table  (LIMIT 0,500)
#

INSERT INTO `lice` (`lice_id`, `ime`, `prezime`) VALUES 
  (1,'Miodrag','Lapcevic'),
  (2,'Bogdan','Kecman'),
  (3,'Petar','Petrovic');

COMMIT;

#
# Data for the `prijava` table  (LIMIT 0,500)
#

INSERT INTO `prijava` (`prijava_id`, `uredjaj_id`, `grad_id`, `lice_id`, `datum_prispeca`, `status`) VALUES 
  (1,2,2,2,'2011-08-13 09:47:00',1),
  (2,3,2,3,'2011-09-13 09:47:19',1);

COMMIT;

#
# Data for the `rezervacija` table  (LIMIT 0,500)
#

INSERT INTO `rezervacija` (`rezervacija_id`, `uredjaj_id`, `datum_od`, `datum_do`, `lice_id`) VALUES 
  (1,1,'2011-07-13 09:48:20','2011-08-13 09:48:23',2);

COMMIT;

#
# Data for the `status` table  (LIMIT 0,500)
#

INSERT INTO `status` (`status_id`, `naziv`) VALUES 
  (1,'dostupan'),
  (2,'na putu'),
  (3,'vraca se sa puta'),
  (4,'nestao'),
  (5,'isporucen');

COMMIT;

#
# Data for the `transfer` table  (LIMIT 0,500)
#

INSERT INTO `transfer` (`transfer_id`, `prijava_id`, `lice_id`) VALUES 
  (1,1,1),
  (2,1,2);

COMMIT;