
CREATE TABLE IF NOT EXISTS `ivc_rooms` (
  `ir_id` int(11) NOT NULL AUTO_INCREMENT,
  `ir_type` varchar(30) NOT NULL,  
  `ir_descr` varchar(255) NOT NULL,  
  `ir_user` varchar(20) NOT NULL,  
  PRIMARY KEY (`ir_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ivc_products` (
  `ip_id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_code` varchar(10) NOT NULL,  
  `ip_name` varchar(30) NOT NULL,  
  `ip_price` decimal(10,2),
  `ip_program` int(2) NOT NULL,  
  `ip_night` int(2) NOT NULL,  
  `ip_maint` decimal(6,2) NOT NULL,  
  `ip_room` int(11) NOT NULL,  
  `ip_user` varchar(20) NOT NULL,  
  PRIMARY KEY (`ip_id`),
  INDEX (ip_room),
	FOREIGN KEY (ip_room)
                              REFERENCES ivc_rooms(ir_id) 
							  ON DELETE RESTRICT
  
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ivc_members` (
  `im_id` int(11) NOT NULL AUTO_INCREMENT,
  `im_formno` varchar(10) NOT NULL,
  `im_scheme` int(11) NOT NULL,
  `im_category` varchar(15) NOT NULL,
  `im_name` varchar(50) NOT NULL,
  `im_title` int(11) NOT NULL,
  `im_cardno` varchar(10) NOT NULL,
  `im_cardname` varchar(50) NOT NULL,
  `im_nominee` varchar(50) NOT NULL,
  `im_nomtitle` int(11) NOT NULL,      
  `im_ic` varchar(20) NOT NULL,
  `im_dob` date not null,
  `im_nationality` varchar(10) NOT NULL,
  `im_foreignspecify` varchar(50) NOT NULL,
  `im_marital` int(11) NOT NULL,
  `im_language` int(11) NOT NULL,
  `im_langaugespecify` varchar(50) NOT NULL,
  `im_addr1` varchar(50) NOT NULL,             
  `im_addr2` varchar(50) NOT NULL,             
  `im_addr3` varchar(50) NOT NULL,             
  `im_postcode` varchar(15) NOT NULL,                       
  `im_country` varchar(100) NOT NULL,                       
  `im_maddr1` varchar(50) NOT NULL,             
  `im_maddr2` varchar(50) NOT NULL,             
  `im_maddr3` varchar(50) NOT NULL,             
  `im_mpostcode` varchar(15) NOT NULL,                       
  `im_mcountry` varchar(100) NOT NULL,                       
  `im_phome` varchar(20) NOT NULL,
  `im_mobile` varchar(20) NOT NULL,
  `im_fax` varchar(20) NOT NULL, 
  `im_email` varchar(50) NOT NULL, 
  `im_occupation` varchar(50) NOT NULL, 
  `im_income` int(11) NOT NULL,  
  `im_joindate` date NOT NULL,
  `im_business` varchar(100) NOT NULL,         
  `im_nature` varchar(100) NOT NULL,         
  `im_offaddr1` varchar(50) NOT NULL,             
  `im_offaddr2` varchar(50) NOT NULL,             
  `im_offaddr3` varchar(50) NOT NULL,             
  `im_offpostcode` varchar(15) NOT NULL,                       
  `im_website` varchar(100) NOT NULL,             
  `im_officephone` varchar(20) NOT NULL,             
  `im_officeext` varchar(5) NOT NULL,             
  `im_officefax` varchar(20) NOT NULL,             
  `im_nominee1` varchar(50) NOT NULL,             
  `im_designation1` varchar(50) NOT NULL,             
  `im_nomineehome1` varchar(20) NOT NULL,
  `im_nomineemobile1` varchar(20) NOT NULL,
  `im_nomineefax1` varchar(20) NOT NULL, 
  `im_nomineeemail1` varchar(50) NOT NULL,    
  `im_nominee2` varchar(50) NOT NULL,             
  `im_designation2` varchar(50) NOT NULL,             
  `im_nomineehome2` varchar(20) NOT NULL,
  `im_nomineemobile2` varchar(20) NOT NULL,
  `im_nomineefax2` varchar(20) NOT NULL, 
  `im_nomineeemail2` varchar(50) NOT NULL,    
  `im_spouse` varchar(50) NOT NULL,             
  `im_sptitle` int(11) NOT NULL,             
  `im_spic` varchar(20) NOT NULL,                        
  `im_spdob` date NOT NULL,                          
  `im_spnationality` varchar(10) NOT NULL,
  `im_spforeignspecify` varchar(50) NOT NULL,
  `im_spoccupation` varchar(50) NOT NULL,
  `im_sphome` varchar(20) NOT NULL,
  `im_spmobile` varchar(20) NOT NULL,
  `im_spfax` varchar(20) NOT NULL, 
  `im_spemail` varchar(50) NOT NULL, 
  `im_user` varchar(20) NOT NULL,    
  `im_active` varchar(3) NOT NULL,    
  `im_userid` varchar(20) NOT NULL,    
  `im_pwd` varchar(255) NOT NULL,    
  `im_expired` date NOT NULL,
  `im_freemaint` int(2) NOT NULL,    
  `im_complementary` varchar(255) NOT NULL,    
  `im_entnight` int(2) NOT NULL,      
            
  PRIMARY KEY (`im_id`),
  
  INDEX (im_scheme),
	FOREIGN KEY (im_scheme)
                              REFERENCES ivc_products(ip_id) 
							  ON DELETE RESTRICT
  
) ENGINE=InnoDB;



CREATE TABLE IF NOT EXISTS `ivc_reservation` (
  `irs_id` int(11) NOT NULL AUTO_INCREMENT,
  `irs_memberno` varchar(20) NOT NULL,
  `irs_for` varchar(10) NOT NULL,
  `irs_guestname` varchar(50) NOT NULL,
  `irs_guestic` varchar(20) NOT NULL,
  `irs_checkin1` date NOT NULL,
  `irs_checkout1` date NOT NULL,
  `irs_room1` int(2) NOT NULL,
  `irs_adult1` int(2) NOT NULL,
  `irs_child1` int(2) NOT NULL,
  `irs_checkin2` date NOT NULL,
  `irs_checkout2` date NOT NULL,
  `irs_room2` int(2) NOT NULL,
  `irs_adult2` int(2) NOT NULL,
  `irs_child2` int(2) NOT NULL,
  `irs_checkin3` date NOT NULL,
  `irs_checkout3` date NOT NULL,
  `irs_room3` int(2) NOT NULL,
  `irs_adult3` int(2) NOT NULL,
  `irs_child3` int(2) NOT NULL,
  `irs_confirm_choice` int(1) NOT NULL,
  `irs_confirm_night` int(2) NOT NULL,
  `irs_usertype` char(1) NOT NULL,
  `irs_userid` varchar(20) NOT NULL,
  `irs_created` date NOT NULL,
  `irs_remark` text NOT NULL,
  `irs_status` char(1) NOT NULL DEFAULT 'N',
  `irs_cancelby` varchar(20) NOT NULL,
  `irs_cancel_remark` text NOT NULL,
  `irs_guestcontact` varchar(20) NOT NULL,
  `irs_guestemail` varchar(50) NOT NULL,
  PRIMARY KEY (`irs_id`)
) ENGINE=InnoDB  

CREATE TABLE IF NOT EXISTS `ivc_upgrade_members` (
  `ium_id` int(11) NOT NULL AUTO_INCREMENT,
  `ium_memberno` varchar(10) NOT NULL,
  `ium_newscheme` int(11) NOT NULL,
  `ium_newfreemaint` int(2) NOT NULL,    
  `ium_newcomplementary` varchar(255) NOT NULL,    
  `ium_orgscheme` int(11) NOT NULL,
  `ium_orgfreemaint` int(2) NOT NULL,    
  `ium_orgcomplementary` varchar(255) NOT NULL,    
  `ium_orgentnight` int(2) NOT NULL,      
  `ium_user` varchar(20) NOT NULL,    
  `ium_date` date,    
              
  PRIMARY KEY (`ium_id`),
  
  INDEX (ium_memberno),
	FOREIGN KEY (ium_memberno)
                              REFERENCES ivc_members(im_cardno) 
							  ON DELETE RESTRICT
  
) ENGINE=InnoDB;

