DROP TABLE charging_condo;

DROP TABLE charging_facility;

DROP TABLE condo_facility;

CREATE TABLE IF NOT EXISTS `facility` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `condo_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `booking_advance_period` int(11) NOT NULL,
  `booking_advance_period_type` varchar(1) NOT NULL,
  `maximum_period_selection` int(11) DEFAULT NULL,
  `deposit` decimal(10,2) NOT NULL,
  `rental` decimal(10,2) NOT NULL,
  `no_of_guest` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `facility` (`id`, `condo_id`, `name`, `booking_advance_period`, `booking_advance_period_type`, `maximum_period_selection`, `deposit`, `rental`, `no_of_guest`) VALUES
(1, 1, 'Community Hall', 1, 'w', NULL, 50.00, 100.90, 80),
(2, 1, 'Multi-Purpose Hall', 1, 'w', NULL, 0.00, 0.00, 50),
(3, 1, 'Alfesco Dining (BBQ)', 1, 'd', NULL, 0.00, 0.00, 20),
(4, 1, 'Games room 1 - Snooker', 1, 'd', 1, 0.00, 0.00, NULL),
(5, 1, 'Games room 2 - Pool 1 & Pool 2', 1, 'd', 1, 0.00, 0.00, NULL),
(6, 1, 'Audio Room 1 & 2', 1, 'd', 1, 0.00, 0.00, NULL);

CREATE TABLE IF NOT EXISTS `facility_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `facility_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `facility_item` (`id`, `facility_id`, `item_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(4, 3, 3),
(5, 2, 1),
(6, 2, 2);
 
CREATE TABLE IF NOT EXISTS `facility_period` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `facility_id` int(11) NOT NULL,
  `period` varchar(50) NOT NULL,
  `time_from` varchar(11) NOT NULL,
  `time_to` varchar(11) NOT NULL,
  `deposit` decimal(10,2) NOT NULL,
  `rental` decimal(10,2) NOT NULL,
  `ordering` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `facility_period` (`id`, `facility_id`, `period`, `time_from`, `time_to`, `deposit`, `rental`, `ordering`) VALUES
(1, 1, '9AM - 3PM', '09:00 AM', '03:00 PM', 200.00, 400.00, 1),
(2, 1, '5PM - 11PM', '05:00 PM', '11:00 PM', 200.00, 400.00, 2),
(3, 1, '9AM - 11PM', '09:00 AM', '11:00 PM', 400.00, 1000.00, 3);

CREATE TABLE IF NOT EXISTS `item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `condo_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `quantity` int(11) NOT NULL,
  `deposit_per_item` decimal(10,2) NOT NULL,
  `rental_per_item` decimal(10,2) NOT NULL,
  `status` varchar(1) NOT NULL DEFAULT 'A',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `item` (`id`, `condo_id`, `name`, `quantity`, `deposit_per_item`, `rental_per_item`, `status`) VALUES
(1, 1, 'Table', 10, 50.00, 100.00, 'A'),
(2, 1, 'Chair', 80, 0.50, 1.00, 'A'),
(3, 1, 'BBQ Grill', 2, 50.00, 100.00, 'A');