# ************************************************************
# Sequel Pro SQL dump
# Version 4096
#
# http://www.sequelpro.com/
# http://code.google.com/p/sequel-pro/
#
# Host: 127.0.0.1 (MySQL 5.6.19)
# Database: event_microsite
# Generation Time: 2015-01-28 13:28:08 +0000
# ************************************************************




# Dump of table Accomodation
# ------------------------------------------------------------


CREATE TABLE `Accomodation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `Accomodation` (`id`, `name`, `address`, `url`)
VALUES
	(1,'Corvin Hotel Budapest','H-1094 Budapest, Angyal utca 31.',''),
	(2,'Hotel Sissi Budapest***','H-1094 Budapest, Angyal utca 33.',''),
	(3,'Hotel Thomas Budapest***','H-1094 Budapest, Liliom utca 44.',''),
	(4,'Leonardo Hotel Budapest****','1094 Budapest, Tompa u. 30-34.',''),
	(5,'Hotel Palazzo Zichy****','1088 Budapest, Lőrinc pap tér 2.',''),
	(6,'Four Points by Sheraton','6000 Kecskemét, Izsáki út 6.',' www.fourpointskecskemet.hu'),
	(7,'Sport Hotel','6000 Kecskemét, Izsáki út 15.',' www.sporthotelkecskemet.hu');



# Dump of table Dining
# ------------------------------------------------------------


CREATE TABLE `Dining` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `price` decimal(10,0) NOT NULL,
  `dates` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:simple_array)',
  `event_id` int(11) DEFAULT NULL,
  `dining_type_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_57E3EA871F7E88B` (`event_id`),
  KEY `IDX_57E3EA845CD4C1` (`dining_type_id`),
  CONSTRAINT `FK_57E3EA845CD4C1` FOREIGN KEY (`dining_type_id`) REFERENCES `DiningType` (`id`),
  CONSTRAINT `FK_57E3EA871F7E88B` FOREIGN KEY (`event_id`) REFERENCES `Event` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `Dining` (`id`, `price`, `dates`, `event_id`, `dining_type_id`)
VALUES
	(1,3900,'2015-04-24,2015-04-25',2,2),
	(2,3900,'2015-04-23',2,3),
	(3,6500,'2015-04-24',2,3);



# Dump of table DiningType
# ------------------------------------------------------------


CREATE TABLE `DiningType` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `DiningType` (`id`, `name`)
VALUES
	(1,'Ebéd'),
	(2,'Svédasztalos ebéd'),
	(3,'Vacsora'),
	(4,'Gálavacsora');



# Dump of table Event
# ------------------------------------------------------------


CREATE TABLE `Event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `start` date NOT NULL,
  `end` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `Event` (`id`, `name`, `start`, `end`)
VALUES
	(1,'espcr','2015-06-19','2015-06-20'),
	(2,'mgyaitt','2015-05-24','2015-05-25');



# Dump of table EventRegistration
# ------------------------------------------------------------


CREATE TABLE `EventRegistration` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `firstname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `lastname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `institution` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `country` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `fax` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `postal` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `registrant_type_id` int(11) DEFAULT NULL,
  `regnumber` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `event_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_A10E2FDBA76ED395` (`user_id`),
  KEY `IDX_A10E2FDBDF376EF8` (`registrant_type_id`),
  KEY `IDX_A10E2FDB71F7E88B` (`event_id`),
  CONSTRAINT `FK_A10E2FDB71F7E88B` FOREIGN KEY (`event_id`) REFERENCES `Event` (`id`),
  CONSTRAINT `FK_A10E2FDBA76ED395` FOREIGN KEY (`user_id`) REFERENCES `SiteUser` (`id`),
  CONSTRAINT `FK_A10E2FDBDF376EF8` FOREIGN KEY (`registrant_type_id`) REFERENCES `RegistrantType` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `EventRegistration` (`id`, `user_id`, `firstname`, `lastname`, `title`, `institution`, `country`, `address`, `phone`, `fax`, `email`, `city`, `postal`, `registrant_type_id`, `regnumber`, `event_id`)
VALUES
	(1,1,'kiss','Bél','Mr.','qwrfwerf0','AF','werfwerfwer','12341234',NULL,'werfw@sdfvsd.hu','werfgwerfgwerf','24',NULL,NULL,NULL),
	(2,1,'kiss','Bél','Mr.','qwrfwerf0','AF','werfwerfwer','12341234',NULL,'werfw@sdfvsd.hu','werfgwerfgwerf','24',NULL,NULL,NULL),
	(3,1,'kiss','Bél','Mr.','qwrfwerf0','AF','werfwerfwer','12341234',NULL,'werfw@sdfvsd.hu','werfgwerfgwerf','24',NULL,NULL,NULL),
	(4,1,'kiss','Bél','Mr.','qwrfwerf0','AF','werfwerfwer','12341234',NULL,'werfw@sdfvsd.hu','werfgwerfgwerf','24',NULL,NULL,NULL),
	(5,2,'Oláh','Gergely','Mr.','qwrfwerf0','AL','werfwerfwer','+36709439436',NULL,'olah.gergely@pixeloid.hu','werfgwerfgwerf','24',NULL,NULL,NULL);



# Dump of table Presentation
# ------------------------------------------------------------


CREATE TABLE `Presentation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `body` longtext COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



# Dump of table RegistrantType
# ------------------------------------------------------------


CREATE TABLE `RegistrantType` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `price_before` decimal(10,0) NOT NULL,
  `price_after` decimal(10,0) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `RegistrantType` (`id`, `name`, `price_before`, `price_after`)
VALUES
	(1,'Orvos',17000,20000),
	(2,'Szakdolgozó',9000,12000),
	(3,'Rezidens, nyugdíjas',10000,13000),
	(4,'Kísérő',10000,13000),
	(5,'Kiállító',20000,23000);



# Dump of table RegistrantType_Event
# ------------------------------------------------------------


CREATE TABLE `RegistrantType_Event` (
  `registranttype_id` int(11) NOT NULL,
  `event_id` int(11) NOT NULL,
  PRIMARY KEY (`registranttype_id`,`event_id`),
  KEY `IDX_24537BD19D497D04` (`registranttype_id`),
  KEY `IDX_24537BD171F7E88B` (`event_id`),
  CONSTRAINT `FK_24537BD171F7E88B` FOREIGN KEY (`event_id`) REFERENCES `Event` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_24537BD19D497D04` FOREIGN KEY (`registranttype_id`) REFERENCES `RegistrantType` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



# Dump of table Room
# ------------------------------------------------------------


CREATE TABLE `Room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) DEFAULT NULL,
  `roomtype_id` int(11) DEFAULT NULL,
  `accomodation_id` int(11) DEFAULT NULL,
  `price` double NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_D2ADFEA571F7E88B` (`event_id`),
  KEY `IDX_D2ADFEA57D31ADD1` (`roomtype_id`),
  KEY `IDX_D2ADFEA5FD70509C` (`accomodation_id`),
  CONSTRAINT `FK_D2ADFEA5FD70509C` FOREIGN KEY (`accomodation_id`) REFERENCES `Accomodation` (`id`),
  CONSTRAINT `FK_D2ADFEA571F7E88B` FOREIGN KEY (`event_id`) REFERENCES `Event` (`id`),
  CONSTRAINT `FK_D2ADFEA57D31ADD1` FOREIGN KEY (`roomtype_id`) REFERENCES `RoomType` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `Room` (`id`, `event_id`, `roomtype_id`, `accomodation_id`, `price`)
VALUES
	(1,2,1,6,18000),
	(2,2,2,6,11000),
	(3,2,1,7,12000),
	(4,2,2,7,7000),
	(5,2,3,7,9000),
	(6,2,4,7,6000),
	(7,2,5,7,11000),
	(8,2,6,7,7500),
	(9,2,7,7,6500),
	(10,2,8,7,3800),
	(11,1,2,7,3800),
	(12,1,4,7,3800);



# Dump of table RoomReservation
# ------------------------------------------------------------


CREATE TABLE `RoomReservation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_registration_id` int(11) DEFAULT NULL,
  `room_id` int(11) DEFAULT NULL,
  `persons` smallint(6) NOT NULL,
  `check_in` date DEFAULT NULL,
  `check_out` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_1B2878784AEEEB73` (`event_registration_id`),
  KEY `IDX_1B28787854177093` (`room_id`),
  CONSTRAINT `FK_1B28787854177093` FOREIGN KEY (`room_id`) REFERENCES `Room` (`id`),
  CONSTRAINT `FK_1B2878784AEEEB73` FOREIGN KEY (`event_registration_id`) REFERENCES `EventRegistration` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



# Dump of table RoomType
# ------------------------------------------------------------


CREATE TABLE `RoomType` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `RoomType` (`id`, `name`)
VALUES
	(1,'Egyágyas szoba'),
	(2,'Kétágyas szoba'),
	(3,'Apartman 2 fő részére'),
	(4,'Apartman 3 fő részére'),
	(5,'Lakosztály 2 fő részére'),
	(6,'Lakosztály 3 fő részére'),
	(7,'Lakosztály 4 fő részére'),
	(8,'Pótágy');




