-- phpMyAdmin SQL Dump
-- version 4.7.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Aug 24, 2018 at 10:48 PM
-- Server version: 5.7.19
-- PHP Version: 7.1.9

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `bgdwalkingtours`
--
CREATE DATABASE IF NOT EXISTS `bgdwalkingtours` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `bgdwalkingtours`;

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

--
-- Table structure for table `departures`
--

DROP TABLE IF EXISTS `departures`;
CREATE TABLE IF NOT EXISTS `departures` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `tour_guide_id` int(12) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tour_guide_id` (`tour_guide_id`,`date`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `departures`
--

INSERT INTO `departures` (`id`, `tour_guide_id`, `date`) VALUES
(1, 1, '2018-08-17'),
(2, 1, '2018-08-24'),
(3, 4, '2018-08-27'),
(4, 4, '2018-08-29'),
(9, 5, '2018-09-02'),
(10, 7, '2018-08-28'),
(5, 10, '2018-08-30'),
(7, 12, '2018-08-25'),
(6, 12, '2018-09-01'),
(8, 12, '2018-09-08');

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

--
-- Table structure for table `reservations`
--

DROP TABLE IF EXISTS `reservations`;
CREATE TABLE IF NOT EXISTS `reservations` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `departure_id` int(12) NOT NULL,
  `client` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `departure_id` (`departure_id`,`client`),
  KEY `fk_client_reservation` (`client`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `reservations`
--

INSERT INTO `reservations` (`id`, `departure_id`, `client`) VALUES
(1, 1, 'jeca'),
(4, 1, 'luka'),
(3, 1, 'maja'),
(2, 1, 'pera'),
(5, 1, 'taca'),
(7, 2, 'igor'),
(6, 2, 'taca'),
(17, 4, 'paja'),
(18, 4, 'taca'),
(20, 5, 'igor'),
(19, 5, 'marko'),
(8, 7, 'aca'),
(9, 7, 'igor'),
(10, 7, 'jeca'),
(11, 7, 'luka'),
(12, 7, 'maja'),
(13, 7, 'marko'),
(14, 7, 'taca'),
(16, 10, 'luka'),
(15, 10, 'maja');

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

--
-- Table structure for table `tours`
--

DROP TABLE IF EXISTS `tours`;
CREATE TABLE IF NOT EXISTS `tours` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `description` text NOT NULL,
  `meeting_point` varchar(150) NOT NULL,
  `min_people` int(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tours`
--

INSERT INTO `tours` (`id`, `name`, `description`, `meeting_point`, `min_people`) VALUES
(1, 'Downtown Tour', 'Tura po centru grada!', 'Trg republike', 4),
(2, '20th Century Tour', 'Tura koja se fokusira na objekte i dogadjaje iz 20-tog veka.', 'Trg republike', 3),
(3, 'Zemun Tour', 'Tura fokusirana na Zemun i njegove znacajnosti i istoriju.', 'Hotel Jugoslavija', 3),
(4, 'Communist tour', 'Tura doba komunizma! Tito i pioniri...', 'Trg republike', 5),
(5, 'Belgrade Pub Crawl', 'Need we say more? :)', 'Trg republike', 4),
(6, 'Orthodox Heritage Tour', 'Obilazak crkvi i prica o njihovom uticaju na kulturu i obicaje', 'Trg republike', 3),
(7, 'Ottoman Heritage Tour', 'Prica o uticaju Otomanskog carstva na Srbiju i obilazak znacajnih objekata iz tog perioda', 'Trg republike', 4);

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

--
-- Table structure for table `tour_guides`
--

DROP TABLE IF EXISTS `tour_guides`;
CREATE TABLE IF NOT EXISTS `tour_guides` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `tour_id` int(12) NOT NULL,
  `guide` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tour_id` (`tour_id`,`guide`),
  KEY `fk_guide` (`guide`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tour_guides`
--

INSERT INTO `tour_guides` (`id`, `tour_id`, `guide`) VALUES
(1, 1, 'ana'),
(2, 1, 'paja'),
(3, 1, 'voja'),
(4, 2, 'paja'),
(5, 2, 'tica'),
(6, 3, 'tica'),
(7, 3, 'voja'),
(8, 4, 'paja'),
(9, 5, 'ana'),
(10, 5, 'paja'),
(12, 5, 'tica'),
(11, 5, 'voja'),
(13, 6, 'paja'),
(14, 7, 'paja'),
(15, 7, 'tica');

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

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `type` tinyint(1) NOT NULL COMMENT '0 - vodic, 1 - klijent',
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table containing information about users of the systems.';

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`username`, `password`, `type`, `first_name`, `last_name`) VALUES
('aca', 'aca123', 1, 'Aleksandar', 'Aleksic'),
('ana', 'ana123', 0, 'Ana', 'Jelic'),
('igor', 'igor123', 1, 'Igor', 'Igic'),
('jeca', 'jeca123', 1, 'Jelena', 'Jelic'),
('luka', 'luka123', 1, 'Luka', 'Lukic'),
('maja', 'maja123', 1, 'Marija', 'Maric'),
('marko', 'marko123', 1, 'Marko', 'Markovic'),
('paja', 'paja123', 0, 'Pavle', 'Pavlovic'),
('pera', 'pera123', 1, 'Petar', 'Petrovic'),
('taca', 'taca123', 1, 'Tamara', 'Petrovic'),
('tica', 'tica123', 0, 'Tijana', 'Tinic'),
('voja', 'voja123', 0, 'Vojin', 'Milenkovic');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `departures`
--
ALTER TABLE `departures`
  ADD CONSTRAINT `fk_tour_guide_departure` FOREIGN KEY (`tour_guide_id`) REFERENCES `tour_guides` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `reservations`
--
ALTER TABLE `reservations`
  ADD CONSTRAINT `fk_client_reservation` FOREIGN KEY (`client`) REFERENCES `users` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_departure_reservation` FOREIGN KEY (`departure_id`) REFERENCES `departures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tour_guides`
--
ALTER TABLE `tour_guides`
  ADD CONSTRAINT `fk_guide` FOREIGN KEY (`guide`) REFERENCES `users` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_tour` FOREIGN KEY (`tour_id`) REFERENCES `tours` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;