DROP TABLE IF EXISTS `medsysmagic`.`countries`;
CREATE TABLE  `medsysmagic`.`countries` (
  `country_id` double DEFAULT NULL,
  `country` varchar(768) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`ec_apps_messages`;
CREATE TABLE  `medsysmagic`.`ec_apps_messages` (
  `apps_msg_id` int(11) NOT NULL AUTO_INCREMENT,
  `msg_code` varchar(5) CHARACTER SET utf8 NOT NULL,
  `app_code` varchar(5) CHARACTER SET utf8 NOT NULL,
  `language_id` int(11) DEFAULT NULL,
  `msg_text` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `msg_desc` varchar(4000) CHARACTER SET utf8 DEFAULT NULL,
  `msg_sev` int(11) DEFAULT NULL,
  PRIMARY KEY (`apps_msg_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`ec_apps_tables`;
CREATE TABLE  `medsysmagic`.`ec_apps_tables` (
  `apps_table_id` int(11) NOT NULL AUTO_INCREMENT,
  `table_name` varchar(30) DEFAULT NULL,
  `apps_code` varchar(5) DEFAULT NULL,
  `language_id` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`apps_table_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`ec_apps_tables_description`;
CREATE TABLE  `medsysmagic`.`ec_apps_tables_description` (
  `apps_table_desc_id` int(11) NOT NULL,
  `apps_table_id` int(11) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `column_name` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
  `column_text` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `column_hint` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `data_type` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `attribute_type` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `data_length` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `is_display` int(1) DEFAULT NULL,
  `is_required` int(1) DEFAULT NULL,
  `is_multi_value` int(1) DEFAULT NULL,
  `object_to_call` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `multi_value_attribute` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `attribute_mask` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `is_searchable` int(11) DEFAULT NULL,
  `search_seq` int(11) DEFAULT NULL,
  `css_id` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `css_class` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`apps_table_desc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`ec_attribute_category`;
CREATE TABLE  `medsysmagic`.`ec_attribute_category` (
  `attribute_category_id` int(11) NOT NULL AUTO_INCREMENT,
  `attribute_category_desc` varchar(100) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`attribute_category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`ec_attribute_values`;
CREATE TABLE  `medsysmagic`.`ec_attribute_values` (
  `attribute_value_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_code` int(11) DEFAULT NULL,
  `attribute_id` int(11) DEFAULT NULL,
  `attribute_value_desc` tinytext,
  `attribute_value` varchar(200) DEFAULT NULL,
  `top_padding` int(11) DEFAULT NULL,
  `left_padding` int(11) DEFAULT NULL,
  `attribute_height` int(11) DEFAULT NULL,
  `attribute_width` int(11) DEFAULT NULL,
  `attribute_style` varchar(20) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`attribute_value_id`),
  KEY `fk_ec_attr_val_comp_attr_id` (`company_code`,`attribute_id`)
) ENGINE=MyISAM AUTO_INCREMENT=199 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`ec_companies`;
CREATE TABLE  `medsysmagic`.`ec_companies` (
  `company_code` int(11) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(150) NOT NULL,
  `logo_image_path` varchar(200) DEFAULT NULL,
  `company_name_image` varchar(200) DEFAULT NULL,
  `header_image_path` varchar(200) DEFAULT NULL,
  `background_color` varchar(20) DEFAULT NULL,
  `background_center_color` varchar(20) DEFAULT NULL,
  `page_width` int(4) NOT NULL DEFAULT '776',
  `search_bg_color` varchar(20) DEFAULT NULL,
  `cat_text_color` varchar(20) DEFAULT NULL,
  `cat_text_mouseover_color` varchar(20) DEFAULT NULL,
  `left_bar_color` varchar(20) DEFAULT NULL,
  `right_bar_color` varchar(20) DEFAULT NULL,
  `left_bar_text` varchar(20) NOT NULL,
  `right_bar_text` varchar(20) NOT NULL,
  `cat_box_color` varchar(20) NOT NULL,
  `search_text_option1` varchar(40) DEFAULT NULL,
  `search_text_option1_color` varchar(20) DEFAULT NULL,
  `search_text_option1_link` varchar(200) DEFAULT NULL,
  `search_text_option2` varchar(40) DEFAULT NULL,
  `search_text_option2_color` varchar(20) DEFAULT NULL,
  `search_text_option2_link` varchar(200) DEFAULT NULL,
  `title_text` varchar(200) DEFAULT NULL,
  `footer_text` varchar(200) DEFAULT NULL,
  `prod_disp_top_bar` varchar(200) DEFAULT NULL,
  `home_page_id` int(5) DEFAULT NULL,
  `right_bar_width` int(5) DEFAULT NULL,
  `left_bar_width` int(5) DEFAULT NULL,
  `space_bw_category` int(5) DEFAULT NULL,
  `category_bg_color` varchar(20) DEFAULT NULL,
  `category_bg_image_path` varchar(200) DEFAULT NULL,
  `space_bw_special_offer` int(5) DEFAULT NULL,
  `special_offer_bg_color` varchar(20) DEFAULT NULL,
  `special_offer_bg_image_path` varchar(200) DEFAULT NULL,
  `right_bar_top_image` varchar(200) DEFAULT NULL,
  `right_bar_bottom_image` varchar(200) DEFAULT NULL,
  `left_bar_top_image` varchar(200) DEFAULT NULL,
  `left_bar_bottom_image` varchar(200) DEFAULT NULL,
  `prod_top_disp_bar_path` varchar(200) DEFAULT NULL,
  `prod_bottom_disp_bar_path` varchar(200) DEFAULT NULL,
  `web_bg_image_path` varchar(200) DEFAULT NULL,
  `body_bg_image_path` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`company_code`),
  KEY `ec_companies_I_1` (`company_code`),
  CONSTRAINT `FK_ec_companies` FOREIGN KEY (`company_code`) REFERENCES `ec_company_apps_tables` (`company_apps_table_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`ec_company_apps_tables`;
CREATE TABLE  `medsysmagic`.`ec_company_apps_tables` (
  `company_apps_table_id` int(11) NOT NULL AUTO_INCREMENT,
  `apps_table_id` int(11) NOT NULL,
  `company_code` int(11) NOT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`company_apps_table_id`),
  KEY `comp_apps_tabs_apps_tab_id_fk` (`apps_table_id`),
  CONSTRAINT `ec_company_apps_tables_ibfk_1` FOREIGN KEY (`apps_table_id`) REFERENCES `ec_apps_tables` (`apps_table_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`ec_company_attributes`;
CREATE TABLE  `medsysmagic`.`ec_company_attributes` (
  `company_code` int(11) NOT NULL,
  `attribute_id` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`company_code`,`attribute_id`),
  KEY `fk_comp_attr_id` (`attribute_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`ec_setup_attributes`;
CREATE TABLE  `medsysmagic`.`ec_setup_attributes` (
  `attribute_id` int(11) NOT NULL AUTO_INCREMENT,
  `attribute_category_id` int(11) NOT NULL,
  `attribute_desc` tinytext NOT NULL,
  `attribute_name` varchar(100) DEFAULT NULL,
  `stored_location_attribute` varchar(200) DEFAULT NULL,
  `attribute_type` varchar(30) DEFAULT NULL,
  `attribute_datatype` varchar(30) DEFAULT NULL,
  `is_template_base` int(1) NOT NULL,
  `web_template_id` int(11) DEFAULT NULL,
  `is_multi_value` int(1) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`attribute_id`),
  KEY `fk_setup_attr_template_id` (`web_template_id`),
  KEY `fk_ec_setup_attr_category_id` (`attribute_category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=186 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`ec_user_attributes`;
CREATE TABLE  `medsysmagic`.`ec_user_attributes` (
  `company_code` int(11) NOT NULL,
  `attribute_id` int(11) NOT NULL,
  `login_id` varchar(30) NOT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`company_code`,`attribute_id`,`login_id`),
  KEY `fk_ec_user_attr_login_id` (`login_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`ec_web_template_sections`;
CREATE TABLE  `medsysmagic`.`ec_web_template_sections` (
  `template_section_id` int(11) NOT NULL AUTO_INCREMENT,
  `web_template_id` int(11) DEFAULT NULL,
  `template_section_type` varchar(1) DEFAULT NULL,
  `panel_id` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `css_file_path` varchar(200) DEFAULT NULL,
  `js_file_path` varchar(200) DEFAULT NULL,
  `is_display_prod_search` int(1) NOT NULL DEFAULT '0',
  `max_section` int(11) DEFAULT NULL,
  `section_height` int(11) DEFAULT NULL,
  `left_section_width` int(11) DEFAULT NULL,
  `middle_section_width` int(11) DEFAULT NULL,
  `right_section_width` int(11) DEFAULT NULL,
  `left_section_style_id` int(11) DEFAULT NULL,
  `left_section_html_cmd` varchar(4000) DEFAULT NULL,
  `left_section_animation_delay` varchar(10) DEFAULT NULL,
  `middle_section_style_id` int(11) DEFAULT NULL,
  `middle_section_html_cmd` varchar(4000) DEFAULT NULL,
  `middle_section_animation_delay` varchar(10) DEFAULT NULL,
  `right_section_style_id` int(11) DEFAULT NULL,
  `right_section_html_cmd` varchar(400) DEFAULT NULL,
  `right_section_animation_delay` varchar(20) DEFAULT NULL,
  `page_button_images_section` varchar(1) DEFAULT NULL,
  `right_section_banner_id` int(11) DEFAULT NULL,
  `middle_section_banner_id` int(11) DEFAULT NULL,
  `left_section_banner_id` int(11) DEFAULT NULL,
  `left_banner_animation_delay` varchar(10) DEFAULT NULL,
  `middle_banner_animation_delay` varchar(10) DEFAULT NULL,
  `right_banner_animation_delay` varchar(10) DEFAULT NULL,
  `is_active` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`template_section_id`),
  UNIQUE KEY `pk_ec_web_template_sections` (`template_section_id`),
  KEY `fk_ec_panel_id` (`panel_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`ec_web_templates`;
CREATE TABLE  `medsysmagic`.`ec_web_templates` (
  `web_template_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_template_id` int(11) DEFAULT NULL,
  `web_template_name` varchar(100) DEFAULT NULL,
  `web_template_abrv` varchar(10) DEFAULT NULL,
  `web_template_width` int(11) DEFAULT NULL,
  `web_template_height` int(11) DEFAULT NULL,
  `template_category_id` int(11) DEFAULT NULL,
  `web_template_type` varchar(1) DEFAULT NULL,
  `create_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`web_template_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`ec_web_templates_events`;
CREATE TABLE  `medsysmagic`.`ec_web_templates_events` (
  `template_event_id` int(11) NOT NULL,
  `company_code` int(11) NOT NULL,
  `web_template_id` int(11) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `create_date` date DEFAULT NULL,
  PRIMARY KEY (`template_event_id`,`company_code`),
  KEY `fk_web_temp_event_company_code` (`company_code`),
  KEY `fk_ec_web_temp_event_temp_id` (`web_template_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_attribute_values`;
CREATE TABLE  `medsysmagic`.`mc_attribute_values` (
  `attribute_value_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_code` int(11) DEFAULT NULL,
  `attribute_id` int(11) DEFAULT NULL,
  `attribute_value_desc` tinytext,
  `attribute_value` varchar(200) DEFAULT NULL,
  `top_padding` int(11) DEFAULT NULL,
  `left_padding` int(11) DEFAULT NULL,
  `attribute_height` int(11) DEFAULT NULL,
  `attribute_width` int(11) DEFAULT NULL,
  `attribute_style` varchar(20) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`attribute_value_id`),
  KEY `fk_ec_attr_val_comp_attr_id` (`company_code`,`attribute_id`)
) ENGINE=MyISAM AUTO_INCREMENT=166 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_background_images`;
CREATE TABLE  `medsysmagic`.`mc_background_images` (
  `background_image_id` int(11) NOT NULL AUTO_INCREMENT,
  `background_image_desc` varchar(40) DEFAULT NULL,
  `background_image_path` varchar(200) DEFAULT NULL,
  `is_active` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`background_image_id`),
  UNIQUE KEY `pk_ec_background_images` (`background_image_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_banners`;
CREATE TABLE  `medsysmagic`.`mc_banners` (
  `banner_id` int(11) NOT NULL AUTO_INCREMENT,
  `banner_name` varchar(20) DEFAULT NULL,
  `banner_desc` blob,
  `banner_type` varchar(2) DEFAULT NULL,
  `banner_location` varchar(1) DEFAULT NULL,
  `banner_link_path` varchar(200) DEFAULT NULL,
  `banner_height` varchar(10) DEFAULT NULL,
  `banner_width` varchar(10) DEFAULT NULL,
  `css_file_path` varchar(200) DEFAULT NULL,
  `js_file_path` varchar(200) DEFAULT NULL,
  `prod_thumbnail_path` varchar(200) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`banner_id`),
  UNIQUE KEY `pk_ec_banners` (`banner_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_banners_special_offers`;
CREATE TABLE  `medsysmagic`.`mc_banners_special_offers` (
  `banner_id` int(11) NOT NULL,
  `offer_id` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`banner_id`,`offer_id`),
  UNIQUE KEY `pk_ec_banners_special_offers` (`banner_id`,`offer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_bg_music`;
CREATE TABLE  `medsysmagic`.`mc_bg_music` (
  `bg_music_id` int(11) NOT NULL AUTO_INCREMENT,
  `bg_music_list_name` varchar(50) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`bg_music_id`),
  UNIQUE KEY `pk_ec_bg_music` (`bg_music_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_bg_music_list`;
CREATE TABLE  `medsysmagic`.`mc_bg_music_list` (
  `bg_music_list_id` int(11) NOT NULL AUTO_INCREMENT,
  `bg_music_id` int(11) DEFAULT NULL,
  `bg_music_file_path` varchar(200) DEFAULT NULL,
  `bg_music_file_desc` varchar(50) DEFAULT NULL,
  `play_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`bg_music_list_id`),
  UNIQUE KEY `pk_ec_bg_music_list` (`bg_music_list_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_billing_info`;
CREATE TABLE  `medsysmagic`.`mc_billing_info` (
  `billing_info_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `sno` int(11) DEFAULT NULL,
  `title` varchar(4) DEFAULT NULL,
  `first_name` varchar(20) DEFAULT NULL,
  `middle_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `street_address` varchar(50) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  `state_code` varchar(20) DEFAULT NULL,
  `zip_code` varchar(10) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `cc_no` varchar(20) DEFAULT NULL,
  `sec_code` varchar(3) DEFAULT NULL,
  `exp_month` int(11) DEFAULT NULL,
  `exp_year` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`billing_info_id`),
  KEY `fk_billing_info_cust_id` (`customer_id`),
  KEY `fk_billing_info_country_id` (`country_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_border_images`;
CREATE TABLE  `medsysmagic`.`mc_border_images` (
  `border_image_id` int(11) NOT NULL AUTO_INCREMENT,
  `border_image_desc` varchar(40) DEFAULT NULL,
  `border_image_type` varchar(1) DEFAULT NULL,
  `border_image_path` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`border_image_id`),
  UNIQUE KEY `pk_ec_border_images` (`border_image_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_bullets_images`;
CREATE TABLE  `medsysmagic`.`mc_bullets_images` (
  `bullet_image_id` int(11) NOT NULL AUTO_INCREMENT,
  `bullet_image_desc` varchar(50) DEFAULT NULL,
  `bullet_image_path` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`bullet_image_id`),
  UNIQUE KEY `pk_ec_bullets_images` (`bullet_image_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_categories`;
CREATE TABLE  `medsysmagic`.`mc_categories` (
  `category_code` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(30) NOT NULL,
  `category_desc` varchar(255) DEFAULT NULL,
  `parent_category_code` int(11) DEFAULT NULL,
  `display_seq` int(11) NOT NULL,
  `category_level` int(4) NOT NULL DEFAULT '1',
  `is_active` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`category_code`),
  KEY `ec_categories_FI_1` (`parent_category_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_category_banners`;
CREATE TABLE  `medsysmagic`.`mc_category_banners` (
  `category_code` int(11) NOT NULL,
  `banner_id` int(11) NOT NULL,
  `category_image_path` varchar(20) DEFAULT NULL,
  `category_image_text` varchar(20) DEFAULT NULL,
  `category_image_link` varchar(200) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`category_code`,`banner_id`),
  UNIQUE KEY `pk_ec_category_banners` (`category_code`,`banner_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_category_images`;
CREATE TABLE  `medsysmagic`.`mc_category_images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_code` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `flash_image_path` varchar(200) DEFAULT NULL,
  `flash_image_type` varchar(4) DEFAULT NULL,
  `is_active` int(11) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `ec_category_images_I_1` (`id`),
  KEY `ec_category_images_FI_1` (`category_code`),
  CONSTRAINT `ec_category_images_FK_1` FOREIGN KEY (`category_code`) REFERENCES `mc_categories` (`category_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_comapany_domain`;
CREATE TABLE  `medsysmagic`.`mc_comapany_domain` (
  `company_code` double DEFAULT NULL,
  `domain_name` varchar(150) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_companies`;
CREATE TABLE  `medsysmagic`.`mc_companies` (
  `company_code` int(11) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(150) NOT NULL,
  `logo_image_path` varchar(200) DEFAULT NULL,
  `company_name_image` varchar(200) DEFAULT NULL,
  `header_image_path` varchar(200) DEFAULT NULL,
  `background_color` varchar(20) DEFAULT NULL,
  `background_center_color` varchar(20) DEFAULT NULL,
  `page_width` int(4) NOT NULL DEFAULT '776',
  `search_bg_color` varchar(20) DEFAULT NULL,
  `cat_text_color` varchar(20) DEFAULT NULL,
  `cat_text_mouseover_color` varchar(20) DEFAULT NULL,
  `left_bar_color` varchar(20) DEFAULT NULL,
  `right_bar_color` varchar(20) DEFAULT NULL,
  `left_bar_text` varchar(20) NOT NULL,
  `right_bar_text` varchar(20) NOT NULL,
  `cat_box_color` varchar(20) NOT NULL,
  `search_text_option1` varchar(40) DEFAULT NULL,
  `search_text_option1_color` varchar(20) DEFAULT NULL,
  `search_text_option1_link` varchar(200) DEFAULT NULL,
  `search_text_option2` varchar(40) DEFAULT NULL,
  `search_text_option2_color` varchar(20) DEFAULT NULL,
  `search_text_option2_link` varchar(200) DEFAULT NULL,
  `title_text` varchar(200) DEFAULT NULL,
  `footer_text` varchar(200) DEFAULT NULL,
  `prod_disp_top_bar` varchar(200) DEFAULT NULL,
  `home_page_id` int(5) DEFAULT NULL,
  `right_bar_width` int(5) DEFAULT NULL,
  `left_bar_width` int(5) DEFAULT NULL,
  `space_bw_category` int(5) DEFAULT NULL,
  `category_bg_color` varchar(20) DEFAULT NULL,
  `category_bg_image_path` varchar(200) DEFAULT NULL,
  `space_bw_special_offer` int(5) DEFAULT NULL,
  `special_offer_bg_color` varchar(20) DEFAULT NULL,
  `special_offer_bg_image_path` varchar(200) DEFAULT NULL,
  `right_bar_top_image` varchar(200) DEFAULT NULL,
  `right_bar_bottom_image` varchar(200) DEFAULT NULL,
  `left_bar_top_image` varchar(200) DEFAULT NULL,
  `left_bar_bottom_image` varchar(200) DEFAULT NULL,
  `prod_top_disp_bar_path` varchar(200) DEFAULT NULL,
  `prod_bottom_disp_bar_path` varchar(200) DEFAULT NULL,
  `web_bg_image_path` varchar(200) DEFAULT NULL,
  `body_bg_image_path` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`company_code`),
  KEY `ec_companies_I_1` (`company_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_attribute_category`;
CREATE TABLE  `medsysmagic`.`mc_company_attribute_category` (
  `company_code` int(11) NOT NULL,
  `attribute_category_id` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`company_code`,`attribute_category_id`),
  KEY `fk_comp_attribute_category_id` (`attribute_category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_attributes`;
CREATE TABLE  `medsysmagic`.`mc_company_attributes` (
  `company_code` int(11) NOT NULL,
  `attribute_id` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`company_code`,`attribute_id`),
  KEY `fk_comp_attr_id` (`attribute_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_background_images`;
CREATE TABLE  `medsysmagic`.`mc_company_background_images` (
  `company_code` int(11) NOT NULL,
  `background_image_id` int(11) NOT NULL,
  PRIMARY KEY (`company_code`,`background_image_id`),
  UNIQUE KEY `pk_ec_company_background_image` (`company_code`,`background_image_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_banners`;
CREATE TABLE  `medsysmagic`.`mc_company_banners` (
  `company_code` int(11) NOT NULL,
  `banner_id` int(11) NOT NULL,
  PRIMARY KEY (`company_code`,`banner_id`),
  UNIQUE KEY `pk_ec_company_banners` (`company_code`,`banner_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_bg_music`;
CREATE TABLE  `medsysmagic`.`mc_company_bg_music` (
  `company_code` int(11) NOT NULL,
  `bg_music_id` int(11) NOT NULL,
  PRIMARY KEY (`company_code`,`bg_music_id`),
  UNIQUE KEY `pk_ec_company_bg_music` (`company_code`,`bg_music_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_border_images`;
CREATE TABLE  `medsysmagic`.`mc_company_border_images` (
  `border_image_id` int(11) NOT NULL,
  `company_code` int(11) NOT NULL,
  PRIMARY KEY (`company_code`,`border_image_id`),
  KEY `ec_company_order_images_FI_2` (`company_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_bullets_images`;
CREATE TABLE  `medsysmagic`.`mc_company_bullets_images` (
  `company_code` int(11) NOT NULL,
  `bullet_image_id` int(11) NOT NULL,
  PRIMARY KEY (`company_code`,`bullet_image_id`),
  UNIQUE KEY `pk_ec_company_bullets_images` (`company_code`,`bullet_image_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_category`;
CREATE TABLE  `medsysmagic`.`mc_company_category` (
  `company_code` int(11) NOT NULL,
  `category_code` int(11) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `ec_company_category_FI_1` (`company_code`),
  KEY `ec_company_category_FI_2` (`category_code`),
  CONSTRAINT `ec_company_category_FK_1` FOREIGN KEY (`company_code`) REFERENCES `mc_companies` (`company_code`),
  CONSTRAINT `ec_company_category_FK_2` FOREIGN KEY (`category_code`) REFERENCES `mc_categories` (`category_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_coupons`;
CREATE TABLE  `medsysmagic`.`mc_company_coupons` (
  `company_code` int(11) NOT NULL,
  `coupon_id` int(11) NOT NULL,
  PRIMARY KEY (`company_code`,`coupon_id`),
  KEY `fk_company_coupons_coupon_id` (`coupon_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_currency`;
CREATE TABLE  `medsysmagic`.`mc_company_currency` (
  `company_code` int(11) NOT NULL,
  `currency_id` int(11) NOT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`company_code`,`currency_id`),
  KEY `fk_company_currency_curr_id` (`currency_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_customers`;
CREATE TABLE  `medsysmagic`.`mc_company_customers` (
  `company_code` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  PRIMARY KEY (`company_code`,`customer_id`),
  KEY `fk_company_customers_cust_id` (`customer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_facility`;
CREATE TABLE  `medsysmagic`.`mc_company_facility` (
  `company_facility_id` int(11) NOT NULL AUTO_INCREMENT,
  `facility_id` int(11) NOT NULL,
  `company_code` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_default` int(1) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`company_facility_id`),
  KEY `cust_facilty_facilty_id_fk` (`facility_id`),
  CONSTRAINT `mc_company_facility_ibfk_1` FOREIGN KEY (`facility_id`) REFERENCES `mc_facility` (`facility_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_flash_images`;
CREATE TABLE  `medsysmagic`.`mc_company_flash_images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_code` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `flash_image_path` varchar(200) DEFAULT NULL,
  `is_active` int(11) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `ec_company_flash_images_I_1` (`id`),
  KEY `ec_company_flash_images_FI_1` (`company_code`),
  CONSTRAINT `ec_company_flash_images_FK_1` FOREIGN KEY (`company_code`) REFERENCES `mc_companies` (`company_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_gift_cards`;
CREATE TABLE  `medsysmagic`.`mc_company_gift_cards` (
  `company_code` int(11) NOT NULL,
  `gift_card_id` int(11) NOT NULL,
  PRIMARY KEY (`company_code`,`gift_card_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_image_corners`;
CREATE TABLE  `medsysmagic`.`mc_company_image_corners` (
  `image_corner_id` int(11) NOT NULL,
  `company_code` int(11) NOT NULL,
  PRIMARY KEY (`image_corner_id`,`company_code`),
  UNIQUE KEY `pk_ec_company_image_corners` (`image_corner_id`,`company_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_languages`;
CREATE TABLE  `medsysmagic`.`mc_company_languages` (
  `company_code` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `display_seq` varchar(20) DEFAULT NULL,
  `is_active` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`company_code`,`language_id`),
  UNIQUE KEY `pk_ec_company_languages` (`company_code`,`language_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_locations`;
CREATE TABLE  `medsysmagic`.`mc_company_locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `location_id` int(11) DEFAULT NULL,
  `company_code` int(11) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_orders`;
CREATE TABLE  `medsysmagic`.`mc_company_orders` (
  `company_code` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  PRIMARY KEY (`company_code`,`order_id`),
  KEY `fk_company_orders_order_id` (`order_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_page_button_images`;
CREATE TABLE  `medsysmagic`.`mc_company_page_button_images` (
  `company_code` int(11) NOT NULL,
  `page_id` int(11) NOT NULL,
  `button_image_id` int(11) NOT NULL,
  PRIMARY KEY (`company_code`,`page_id`,`button_image_id`),
  UNIQUE KEY `pk_ec_company_page_button_imag` (`company_code`,`page_id`,`button_image_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_page_detail`;
CREATE TABLE  `medsysmagic`.`mc_company_page_detail` (
  `company_code` double DEFAULT NULL,
  `page_id` double DEFAULT NULL,
  `display_seq` double DEFAULT NULL,
  `page_text` blob,
  `page_image_path` varchar(600) DEFAULT NULL,
  `thumbnails_path` varchar(600) DEFAULT NULL,
  `download_file_text` varchar(300) DEFAULT NULL,
  `download_file_link` varchar(300) DEFAULT NULL,
  `email_address` varchar(150) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_pages`;
CREATE TABLE  `medsysmagic`.`mc_company_pages` (
  `page_id` double DEFAULT NULL,
  `company_code` int(11) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(11) DEFAULT NULL,
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_panels`;
CREATE TABLE  `medsysmagic`.`mc_company_panels` (
  `company_code` int(11) NOT NULL,
  `panel_id` int(11) NOT NULL,
  PRIMARY KEY (`company_code`,`panel_id`),
  UNIQUE KEY `pk_ec_company_panels` (`company_code`,`panel_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_payment_methods`;
CREATE TABLE  `medsysmagic`.`mc_company_payment_methods` (
  `payment_method_id` int(11) NOT NULL,
  `company_code` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `api_cmd` varchar(4000) DEFAULT NULL,
  `payment_method_icon_path` varchar(200) DEFAULT NULL,
  `user_name` varchar(30) DEFAULT NULL,
  `pass_code` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`payment_method_id`,`company_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_prod_quality`;
CREATE TABLE  `medsysmagic`.`mc_company_prod_quality` (
  `company_code` int(11) NOT NULL,
  `quality_id` int(11) NOT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`company_code`,`quality_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_product_colors`;
CREATE TABLE  `medsysmagic`.`mc_company_product_colors` (
  `company_code` int(11) NOT NULL,
  `color_id` int(11) NOT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`company_code`,`color_id`),
  KEY `fk_company_prod_color_id` (`color_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_product_images`;
CREATE TABLE  `medsysmagic`.`mc_company_product_images` (
  `comp_prod_image_id` int(11) NOT NULL,
  `prod_image_id` int(11) NOT NULL,
  `prod_code` int(11) NOT NULL,
  `company_code` int(11) NOT NULL,
  `color_id` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`comp_prod_image_id`),
  KEY `fk_comp_prod_image_id` (`prod_image_id`),
  KEY `fk_comp_prod_img_color_id` (`company_code`,`color_id`),
  KEY `fk_comp_prod_images_prod_cd` (`prod_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_product_type`;
CREATE TABLE  `medsysmagic`.`mc_company_product_type` (
  `company_code` int(11) NOT NULL,
  `prod_type_id` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`company_code`,`prod_type_id`),
  KEY `fk_comp_prod_type_id` (`prod_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_questions`;
CREATE TABLE  `medsysmagic`.`mc_company_questions` (
  `company_question_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_code` int(11) NOT NULL,
  `mc_question_id` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `condition_alert` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`company_question_id`),
  KEY `comp_quest_quest_id_fk` (`mc_question_id`),
  CONSTRAINT `mc_company_questions_ibfk_1` FOREIGN KEY (`mc_question_id`) REFERENCES `mc_questions` (`mc_question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_search_images`;
CREATE TABLE  `medsysmagic`.`mc_company_search_images` (
  `company_code` int(11) NOT NULL,
  `search_image_id` int(11) NOT NULL,
  PRIMARY KEY (`company_code`,`search_image_id`),
  UNIQUE KEY `pk_ec_company_search_images` (`company_code`,`search_image_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_seo`;
CREATE TABLE  `medsysmagic`.`mc_company_seo` (
  `company_seo_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_code` int(11) NOT NULL,
  `seo_tag_cmd` varchar(500) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`company_seo_id`),
  KEY `fk_company_seo_comp_id` (`company_code`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_shipping_methods`;
CREATE TABLE  `medsysmagic`.`mc_company_shipping_methods` (
  `shipping_method_id` int(11) NOT NULL,
  `company_code` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `api_cmd` varchar(4000) DEFAULT NULL,
  `shipping_method_icon_path` varchar(200) DEFAULT NULL,
  `user_name` varchar(50) DEFAULT NULL,
  `pass_code` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`shipping_method_id`,`company_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_size`;
CREATE TABLE  `medsysmagic`.`mc_company_size` (
  `company_code` int(11) NOT NULL,
  `size_id` int(11) NOT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`company_code`,`size_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_special_offer`;
CREATE TABLE  `medsysmagic`.`mc_company_special_offer` (
  `offer_id` int(11) NOT NULL,
  `company_code` int(11) NOT NULL,
  PRIMARY KEY (`offer_id`,`company_code`),
  KEY `ec_company_special_offer_FI_2` (`company_code`),
  CONSTRAINT `ec_company_special_offer_FK_1` FOREIGN KEY (`offer_id`) REFERENCES `mc_special_offers` (`offer_id`),
  CONSTRAINT `ec_company_special_offer_FK_2` FOREIGN KEY (`company_code`) REFERENCES `mc_companies` (`company_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_users`;
CREATE TABLE  `medsysmagic`.`mc_company_users` (
  `login_id` varchar(30) NOT NULL,
  `company_code` int(11) NOT NULL,
  PRIMARY KEY (`login_id`,`company_code`),
  KEY `ec_company_users_FI_2` (`company_code`),
  CONSTRAINT `ec_company_users_FK_1` FOREIGN KEY (`login_id`) REFERENCES `mc_users` (`login_id`),
  CONSTRAINT `ec_company_users_FK_2` FOREIGN KEY (`company_code`) REFERENCES `mc_companies` (`company_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_company_web_options`;
CREATE TABLE  `medsysmagic`.`mc_company_web_options` (
  `company_code` int(11) NOT NULL,
  `login_id` varchar(30) NOT NULL,
  `web_option_id` int(11) NOT NULL,
  PRIMARY KEY (`company_code`,`login_id`,`web_option_id`),
  KEY `ec_company_web_options_FI_2` (`login_id`),
  KEY `ec_company_web_options_FI_3` (`web_option_id`),
  CONSTRAINT `ec_company_web_options_FK_1` FOREIGN KEY (`company_code`) REFERENCES `mc_companies` (`company_code`),
  CONSTRAINT `ec_company_web_options_FK_2` FOREIGN KEY (`login_id`) REFERENCES `mc_users` (`login_id`),
  CONSTRAINT `ec_company_web_options_FK_3` FOREIGN KEY (`web_option_id`) REFERENCES `mc_web_options` (`web_option_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_corner_images`;
CREATE TABLE  `medsysmagic`.`mc_corner_images` (
  `image_corner_id` int(11) NOT NULL AUTO_INCREMENT,
  `image_name` varchar(20) DEFAULT NULL,
  `image_corner_path` varchar(200) DEFAULT NULL,
  `corner_type` varchar(1) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`image_corner_id`),
  UNIQUE KEY `pk_ec_corner_images` (`image_corner_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_countries`;
CREATE TABLE  `medsysmagic`.`mc_countries` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(20) DEFAULT NULL,
  `country_abrv` varchar(5) DEFAULT NULL,
  `is_active` int(11) DEFAULT '1',
  PRIMARY KEY (`country_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_country_base_language`;
CREATE TABLE  `medsysmagic`.`mc_country_base_language` (
  `country_id` int(11) NOT NULL,
  `company_code` int(11) DEFAULT NULL,
  `language_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`country_id`),
  UNIQUE KEY `pk_ec_country_base_language` (`country_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_coupon_products`;
CREATE TABLE  `medsysmagic`.`mc_coupon_products` (
  `coupon_product_id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_code` int(11) DEFAULT NULL,
  `company_code` int(11) DEFAULT NULL,
  `coupon_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`coupon_product_id`),
  KEY `fk_coupon_products_prod_id` (`prod_code`),
  KEY `fk_coupon_prod_comp_cd_coup_id` (`company_code`,`coupon_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_coupon_shipping`;
CREATE TABLE  `medsysmagic`.`mc_coupon_shipping` (
  `coupon_shipping_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_code` int(11) DEFAULT NULL,
  `coupon_id` int(11) DEFAULT NULL,
  `shipping_method_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`coupon_shipping_id`),
  KEY `fk_coupon_shipping_comp_coupon` (`company_code`,`coupon_id`),
  KEY `fk_coupon_shipping_method_id` (`shipping_method_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_coupon_value`;
CREATE TABLE  `medsysmagic`.`mc_coupon_value` (
  `coupon_value_id` int(11) NOT NULL AUTO_INCREMENT,
  `currency_id` int(11) DEFAULT NULL,
  `coupon_id` int(11) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `coupon_value` int(11) DEFAULT NULL,
  `coupon_image_path` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`coupon_value_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_coupons`;
CREATE TABLE  `medsysmagic`.`mc_coupons` (
  `coupon_id` int(11) NOT NULL AUTO_INCREMENT,
  `coupon_code` varchar(20) DEFAULT NULL,
  `coupon_desc` varchar(200) DEFAULT NULL,
  `coupon_value_type` varchar(1) DEFAULT NULL,
  `promotion_type` varchar(20) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `min_order_amount` int(11) DEFAULT NULL,
  `max_use` int(11) DEFAULT NULL,
  `cust_max_use` int(11) DEFAULT NULL,
  `is_allow_multi_coupon` int(11) DEFAULT '0',
  `is_expired` int(1) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`coupon_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_coupons_used`;
CREATE TABLE  `medsysmagic`.`mc_coupons_used` (
  `coupon_used_id` int(11) NOT NULL,
  `coupon_id` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `coupon_used` int(11) DEFAULT NULL,
  PRIMARY KEY (`coupon_used_id`),
  KEY `fk_coupon_used_cust_id` (`customer_id`),
  KEY `fk_coupon_used_coupon_id` (`coupon_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_currency`;
CREATE TABLE  `medsysmagic`.`mc_currency` (
  `currency_id` int(11) NOT NULL,
  `country_name` varchar(40) DEFAULT NULL,
  `currency_name` varchar(50) DEFAULT NULL,
  `currency_abrv` varchar(10) DEFAULT NULL,
  `currency_symbol` varchar(20) DEFAULT NULL,
  `is_active` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`currency_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_cust_cart`;
CREATE TABLE  `medsysmagic`.`mc_cust_cart` (
  `cart_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_code` int(11) NOT NULL,
  `currency_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `cart_date` date DEFAULT NULL,
  `cart_name` varchar(20) DEFAULT NULL,
  `s_id` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`cart_id`),
  KEY `fk_cust_cart_comp_curr_id` (`company_code`,`currency_id`),
  KEY `fk_cust_cart_comp_cust_id` (`company_code`,`customer_id`)
) ENGINE=MyISAM AUTO_INCREMENT=145 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_cust_cart_detail`;
CREATE TABLE  `medsysmagic`.`mc_cust_cart_detail` (
  `ec_cust_cart_detail_id` int(11) NOT NULL AUTO_INCREMENT,
  `cart_id` int(11) DEFAULT NULL,
  `sno` int(11) DEFAULT NULL,
  `prod_code` int(11) DEFAULT NULL,
  `prod_qty` int(11) DEFAULT NULL,
  `prod_rate` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  `size_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`ec_cust_cart_detail_id`),
  KEY `fk_cust_cart_detail_prod_code` (`prod_code`),
  KEY `fk_cust_cart_detail_cart_id` (`cart_id`)
) ENGINE=MyISAM AUTO_INCREMENT=263 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_cust_comp_account`;
CREATE TABLE  `medsysmagic`.`mc_cust_comp_account` (
  `customer_acc_id` int(11) NOT NULL,
  `company_code` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `cust_code` varchar(20) DEFAULT NULL,
  `opening_bal` int(11) DEFAULT NULL,
  `closing_bal` int(11) DEFAULT NULL,
  PRIMARY KEY (`customer_acc_id`),
  KEY `fk_cust_account_comp_cust_id` (`company_code`,`customer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_customer_coupns`;
CREATE TABLE  `medsysmagic`.`mc_customer_coupns` (
  `coupon_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`coupon_id`,`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_customer_dependent`;
CREATE TABLE  `medsysmagic`.`mc_customer_dependent` (
  `customer_dependent_id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) DEFAULT NULL,
  `customer_id` int(11) NOT NULL,
  `dispaly_seq` int(11) DEFAULT NULL,
  `primary_cust_id` int(11) NOT NULL,
  `relationship_id` int(11) NOT NULL,
  `created_date` date DEFAULT NULL,
  `last_updated_date` date DEFAULT NULL,
  `last_updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`customer_dependent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_customer_history`;
CREATE TABLE  `medsysmagic`.`mc_customer_history` (
  `cust_hist_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_code` int(11) DEFAULT NULL,
  `facility_id` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `hist_rec_date` date DEFAULT NULL,
  PRIMARY KEY (`cust_hist_id`),
  KEY `cust_hist_facility_id_fk` (`facility_id`),
  KEY `cust_hist_comp_cd_fk` (`company_code`),
  CONSTRAINT `mc_customer_history_ibfk_1` FOREIGN KEY (`facility_id`) REFERENCES `mc_facility` (`facility_id`),
  CONSTRAINT `mc_customer_history_ibfk_2` FOREIGN KEY (`company_code`) REFERENCES `ec_companies` (`company_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_customer_history_detail`;
CREATE TABLE  `medsysmagic`.`mc_customer_history_detail` (
  `cust_hist_detail_id` int(11) NOT NULL AUTO_INCREMENT,
  `cust_hist_id` int(11) DEFAULT NULL,
  `mc_question_id` int(11) DEFAULT NULL,
  `cust_answer` int(1) DEFAULT NULL,
  `cust_comments` varchar(500) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  PRIMARY KEY (`cust_hist_detail_id`),
  KEY `cust_hist_dtl_question_id_fk` (`mc_question_id`),
  KEY `cust_hist_dtl_cust_hist_id_fk` (`cust_hist_id`),
  CONSTRAINT `mc_customer_history_detail_ibfk_1` FOREIGN KEY (`mc_question_id`) REFERENCES `mc_questions` (`mc_question_id`),
  CONSTRAINT `mc_customer_history_detail_ibfk_2` FOREIGN KEY (`cust_hist_id`) REFERENCES `mc_customer_history` (`cust_hist_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_customer_treatment`;
CREATE TABLE  `medsysmagic`.`mc_customer_treatment` (
  `customer_treatment_id` int(11) NOT NULL AUTO_INCREMENT,
  `specialist_id` int(11) DEFAULT NULL,
  `treatment_date` date DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `comments` varchar(4000) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`customer_treatment_id`),
  KEY `cust_treat_specialist_id_fk` (`specialist_id`),
  CONSTRAINT `mc_customer_treatment_ibfk_1` FOREIGN KEY (`specialist_id`) REFERENCES `mc_specialist` (`specialist_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_customer_treatment_detail`;
CREATE TABLE  `medsysmagic`.`mc_customer_treatment_detail` (
  `customer_treatment_detail_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_treatment_id` int(11) DEFAULT NULL,
  `treatment_id` int(11) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  PRIMARY KEY (`customer_treatment_detail_id`),
  KEY `cust_treat_detail_ctreat_id_fk` (`customer_treatment_id`),
  KEY `cust_treat_detail_treat_id_fk` (`treatment_id`),
  CONSTRAINT `mc_customer_treatment_detail_ibfk_1` FOREIGN KEY (`customer_treatment_id`) REFERENCES `mc_customer_treatment` (`customer_treatment_id`),
  CONSTRAINT `mc_customer_treatment_detail_ibfk_2` FOREIGN KEY (`treatment_id`) REFERENCES `mc_treatment` (`treatment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_customers`;
CREATE TABLE  `medsysmagic`.`mc_customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_type` char(3) DEFAULT NULL,
  `cust_code` varchar(20) DEFAULT NULL,
  `title` varchar(3) DEFAULT NULL,
  `first_name` varchar(20) DEFAULT NULL,
  `middle_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `gender` varchar(1) DEFAULT NULL,
  `home_phone` varchar(10) DEFAULT NULL,
  `cell_phone` varchar(20) DEFAULT NULL,
  `email_address` varchar(50) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `cust_pin_code` varchar(4) DEFAULT NULL,
  `blog_image_path` varchar(200) DEFAULT NULL,
  `comments` varchar(20) DEFAULT NULL,
  `rec_newsltr` char(3) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`customer_id`)
) ENGINE=MyISAM AUTO_INCREMENT=85 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_days`;
CREATE TABLE  `medsysmagic`.`mc_days` (
  `day_id` int(11) NOT NULL AUTO_INCREMENT,
  `day_display_name` varchar(20) DEFAULT NULL,
  `day_eng_name` varchar(20) DEFAULT NULL,
  `dispaly_seq` int(11) DEFAULT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`day_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_distributor`;
CREATE TABLE  `medsysmagic`.`mc_distributor` (
  `distributor_id` int(11) NOT NULL AUTO_INCREMENT,
  `distributor_name` varchar(200) DEFAULT NULL,
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `street_adress` varchar(50) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  `state_code` varchar(20) DEFAULT NULL,
  `zip_code` varchar(10) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`distributor_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_domain`;
CREATE TABLE  `medsysmagic`.`mc_domain` (
  `domain_id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_name` varchar(150) DEFAULT NULL,
  `email` varchar(50) NOT NULL,
  `first_name` varchar(60) DEFAULT NULL,
  `last_name` varchar(60) DEFAULT NULL,
  `phone_no` varchar(20) DEFAULT NULL,
  `cell_no` varchar(60) DEFAULT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`domain_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_domain_web_templates`;
CREATE TABLE  `medsysmagic`.`mc_domain_web_templates` (
  `domain_id` int(11) NOT NULL,
  `web_template_id` int(11) NOT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`domain_id`,`web_template_id`),
  UNIQUE KEY `pk_ec_domain_web_templates` (`domain_id`,`web_template_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_facility`;
CREATE TABLE  `medsysmagic`.`mc_facility` (
  `facility_id` int(11) NOT NULL,
  `company_code` int(11) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `facility_name` varchar(100) DEFAULT NULL,
  `address_line1` varchar(100) DEFAULT NULL,
  `address_line2` varchar(100) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `state` varchar(20) DEFAULT NULL,
  `phone01` varchar(20) DEFAULT NULL,
  `phone02` varchar(20) DEFAULT NULL,
  `email_address` varchar(100) DEFAULT NULL,
  `web_link` varchar(50) DEFAULT NULL,
  `time_zone` varchar(5) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `last_updated_date` datetime DEFAULT NULL,
  `last_updated_by` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`facility_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

DROP TABLE IF EXISTS `medsysmagic`.`mc_facility_holiday_calendar`;
CREATE TABLE  `medsysmagic`.`mc_facility_holiday_calendar` (
  `facility_id` int(11) NOT NULL,
  `calendar_id` int(11) NOT NULL,
  PRIMARY KEY (`facility_id`,`calendar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_facility_timing`;
CREATE TABLE  `medsysmagic`.`mc_facility_timing` (
  `facility_time_id` int(11) NOT NULL,
  `facility_id` int(11) DEFAULT NULL,
  `day_id` int(11) DEFAULT NULL,
  `time_form` varchar(20) DEFAULT NULL,
  `time_to` varchar(20) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `valid_from` date DEFAULT NULL,
  `valid_to` date DEFAULT NULL,
  `comments` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  `created_date` date DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `last_updated_date` date DEFAULT NULL,
  `last_updated_by` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`facility_time_id`),
  KEY `facility_time_day_id_fk` (`day_id`),
  KEY `facility_timing_facility_id_fk` (`facility_id`),
  CONSTRAINT `mc_facility_timing_ibfk_1` FOREIGN KEY (`day_id`) REFERENCES `mc_days` (`day_id`),
  CONSTRAINT `mc_facility_timing_ibfk_2` FOREIGN KEY (`facility_id`) REFERENCES `mc_facility` (`facility_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_facility_treatment`;
CREATE TABLE  `medsysmagic`.`mc_facility_treatment` (
  `facility_treatment_id` int(11) NOT NULL AUTO_INCREMENT,
  `facility_id` int(11) NOT NULL,
  `treatment_id` int(11) NOT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`facility_treatment_id`),
  KEY `facility_treat_treatment_id_fk` (`treatment_id`),
  KEY `facility_treat_faclity_id_fk` (`facility_id`),
  CONSTRAINT `mc_facility_treatment_ibfk_1` FOREIGN KEY (`treatment_id`) REFERENCES `mc_treatment` (`treatment_id`),
  CONSTRAINT `mc_facility_treatment_ibfk_2` FOREIGN KEY (`facility_id`) REFERENCES `mc_facility` (`facility_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_general_banners`;
CREATE TABLE  `medsysmagic`.`mc_general_banners` (
  `general_banner_id` int(11) NOT NULL AUTO_INCREMENT,
  `banner_id` int(11) DEFAULT NULL,
  `general_banner_desc` varchar(4000) DEFAULT NULL,
  `banner_image_path` varchar(200) DEFAULT NULL,
  `banner_image_text` varchar(100) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`general_banner_id`),
  UNIQUE KEY `pk_ec_general_banners` (`general_banner_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_gift_card_type`;
CREATE TABLE  `medsysmagic`.`mc_gift_card_type` (
  `gift_card_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `gift_card_name` varchar(100) DEFAULT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`gift_card_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_gift_cards`;
CREATE TABLE  `medsysmagic`.`mc_gift_cards` (
  `gift_card_id` int(11) NOT NULL AUTO_INCREMENT,
  `gift_card_type_id` int(11) DEFAULT NULL,
  `sno` int(11) DEFAULT NULL,
  `special_message` varchar(100) DEFAULT NULL,
  `gift_card_num` varchar(20) DEFAULT NULL,
  `gift_card_pin` varchar(20) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `gift_card_value` int(11) DEFAULT NULL,
  `expire_date` date DEFAULT NULL,
  PRIMARY KEY (`gift_card_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_group_companies`;
CREATE TABLE  `medsysmagic`.`mc_group_companies` (
  `group_id` int(11) NOT NULL,
  `company_code` int(11) NOT NULL,
  PRIMARY KEY (`group_id`,`company_code`),
  UNIQUE KEY `pk_ec_group_companies` (`group_id`,`company_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_groups`;
CREATE TABLE  `medsysmagic`.`mc_groups` (
  `group_id` int(11) NOT NULL AUTO_INCREMENT,
  `group_name` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`group_id`),
  UNIQUE KEY `pk_ec_groups` (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_holiday_calendar_detail`;
CREATE TABLE  `medsysmagic`.`mc_holiday_calendar_detail` (
  `calendar_detail_id` int(11) NOT NULL AUTO_INCREMENT,
  `calendar_id` int(11) DEFAULT NULL,
  `calendar_date` char(18) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`calendar_detail_id`),
  KEY `holidat_cal_cale_detail_id_fk` (`calendar_id`),
  CONSTRAINT `mc_holiday_calendar_detail_ibfk_1` FOREIGN KEY (`calendar_id`) REFERENCES `mc_holidays_calendar` (`calendar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_holidays_calendar`;
CREATE TABLE  `medsysmagic`.`mc_holidays_calendar` (
  `calendar_id` int(11) NOT NULL AUTO_INCREMENT,
  `clander_name` varchar(40) DEFAULT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`calendar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_languages`;
CREATE TABLE  `medsysmagic`.`mc_languages` (
  `language_id` int(11) NOT NULL AUTO_INCREMENT,
  `language_abrv` varchar(5) DEFAULT NULL,
  `language_name` varchar(100) DEFAULT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`language_id`),
  UNIQUE KEY `pk_ec_languages` (`language_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_locations`;
CREATE TABLE  `medsysmagic`.`mc_locations` (
  `location_id` int(11) NOT NULL AUTO_INCREMENT,
  `location_name` varchar(50) DEFAULT NULL,
  `address_line1` varchar(50) DEFAULT NULL,
  `address_line2` varchar(50) DEFAULT NULL,
  `state` varchar(20) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  `country` varchar(20) DEFAULT NULL,
  `phone_no1` varchar(20) DEFAULT NULL,
  `phone_no2` varchar(20) DEFAULT NULL,
  `fax_no` varchar(20) DEFAULT NULL,
  `email_address` varchar(50) DEFAULT NULL,
  `thumblink_image_path` varchar(200) DEFAULT NULL,
  `large_size_image_path` varchar(200) DEFAULT NULL,
  `is_active` int(11) DEFAULT '1',
  PRIMARY KEY (`location_id`),
  KEY `ec_company_locations_I_1` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_manufactures`;
CREATE TABLE  `medsysmagic`.`mc_manufactures` (
  `manufacture_id` int(11) NOT NULL AUTO_INCREMENT,
  `manufacture_name` varchar(100) DEFAULT NULL,
  `manufacture_desc` varchar(20) NOT NULL,
  `street_address` varchar(50) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  `state_code` varchar(20) DEFAULT NULL,
  `zip_code` varchar(10) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`manufacture_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_order_address`;
CREATE TABLE  `medsysmagic`.`mc_order_address` (
  `order_address_id` int(11) NOT NULL,
  `order_id` int(11) DEFAULT NULL,
  `address_type` varchar(1) DEFAULT NULL,
  `first_name` varchar(20) DEFAULT NULL,
  `middle_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `street_address` varchar(100) DEFAULT NULL,
  `state_code` varchar(20) DEFAULT NULL,
  `zip_code` varchar(20) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `cc_no` varchar(20) DEFAULT NULL,
  `sec_code` varchar(3) DEFAULT NULL,
  `exp_month` varchar(2) DEFAULT NULL,
  `exp_year` varchar(4) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`order_address_id`),
  KEY `fk_order_address_order_id` (`order_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_order_detail`;
CREATE TABLE  `medsysmagic`.`mc_order_detail` (
  `order_detail_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `prod_code` int(11) DEFAULT NULL,
  `prod_sno` varchar(100) DEFAULT NULL,
  `qty` int(11) DEFAULT NULL,
  `rate` int(11) DEFAULT NULL,
  `coupon_code` varchar(20) DEFAULT NULL,
  `coupon_value` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  `admin_comments` varchar(4000) DEFAULT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_detail_id`),
  KEY `fk_order_detail_product_code` (`prod_code`),
  KEY `fk_order_detail_order_id` (`order_id`)
) ENGINE=MyISAM AUTO_INCREMENT=111 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_order_status`;
CREATE TABLE  `medsysmagic`.`mc_order_status` (
  `order_status_id` int(11) NOT NULL,
  `order_desc` varchar(20) DEFAULT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_status_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_order_status_history`;
CREATE TABLE  `medsysmagic`.`mc_order_status_history` (
  `order_status_history_id` int(11) NOT NULL,
  `order_id` int(11) DEFAULT NULL,
  `order_status_id` int(11) DEFAULT NULL,
  `status_date` date DEFAULT NULL,
  `admin_comments` varchar(20) DEFAULT NULL,
  `create_date` date DEFAULT NULL,
  `last_update_date` date DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`order_status_history_id`),
  KEY `fk_order_status_hist_order_id` (`order_id`),
  KEY `fk_order_status_hist_status_id` (`order_status_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_orders`;
CREATE TABLE  `medsysmagic`.`mc_orders` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `order_num` varchar(20) DEFAULT NULL,
  `order_date` datetime DEFAULT NULL,
  `order_sub_total` int(11) DEFAULT NULL,
  `shipment_amount` int(11) DEFAULT NULL,
  `total_tax_amount` int(11) DEFAULT NULL,
  `order_discount_amount` int(11) DEFAULT NULL,
  `order_total_amount` int(11) DEFAULT NULL,
  `customer_comments` varchar(200) DEFAULT NULL,
  `admin_comments` varchar(200) DEFAULT NULL,
  `is_checked_out` int(1) DEFAULT '0',
  PRIMARY KEY (`order_id`),
  KEY `fk_company_gift_card_id` (`customer_id`)
) ENGINE=MyISAM AUTO_INCREMENT=108 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_page_button_images`;
CREATE TABLE  `medsysmagic`.`mc_page_button_images` (
  `button_image_id` int(11) NOT NULL AUTO_INCREMENT,
  `button_image_name` varchar(200) DEFAULT NULL,
  `button_image_path` varchar(200) DEFAULT NULL,
  `button_image_mouseover_path` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`button_image_id`),
  UNIQUE KEY `pk_ec_page_button_images` (`button_image_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_page_group_companies`;
CREATE TABLE  `medsysmagic`.`mc_page_group_companies` (
  `page_name` varchar(200) NOT NULL,
  `group_id` int(11) NOT NULL,
  `company_code` int(11) NOT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`page_name`,`group_id`,`company_code`),
  UNIQUE KEY `pk_ec_page_group_companies` (`page_name`,`group_id`,`company_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_page_special_effects`;
CREATE TABLE  `medsysmagic`.`mc_page_special_effects` (
  `page_special_effect_id` int(11) NOT NULL AUTO_INCREMENT,
  `page_special_effect_name` varchar(40) DEFAULT NULL,
  `page_special_effect_cmd` varchar(4000) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`page_special_effect_id`),
  UNIQUE KEY `pk_ec_page_special_effects` (`page_special_effect_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_pages`;
CREATE TABLE  `medsysmagic`.`mc_pages` (
  `page_id` int(11) NOT NULL AUTO_INCREMENT,
  `button_text` varchar(60) DEFAULT NULL,
  `button_text_color` varchar(60) DEFAULT NULL,
  `button_mouse_over_color` varchar(60) DEFAULT NULL,
  `page_link_path` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`page_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_panel_images`;
CREATE TABLE  `medsysmagic`.`mc_panel_images` (
  `panel_image_id` int(11) NOT NULL AUTO_INCREMENT,
  `panel_id` int(11) DEFAULT NULL,
  `panel_image_text` varchar(4000) DEFAULT NULL,
  `panel_image_path` varchar(200) DEFAULT NULL,
  `panel_link_path` varchar(200) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`panel_image_id`),
  UNIQUE KEY `pk_ec_panel_images` (`panel_image_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_panels`;
CREATE TABLE  `medsysmagic`.`mc_panels` (
  `panel_id` int(11) NOT NULL AUTO_INCREMENT,
  `panel_image_name` varchar(20) DEFAULT NULL,
  `panel_image_path` varchar(200) DEFAULT NULL,
  `panel_image_width` varchar(10) DEFAULT NULL,
  `panel_image_height` varchar(10) DEFAULT NULL,
  `ccs_file_path` varchar(200) DEFAULT NULL,
  `js_file_path` varchar(200) DEFAULT NULL,
  `html_cmd` varchar(4000) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`panel_id`),
  UNIQUE KEY `pk_ec_panels` (`panel_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_payment_detail`;
CREATE TABLE  `medsysmagic`.`mc_payment_detail` (
  `payment_detail_id` int(11) NOT NULL,
  `company_code` int(11) NOT NULL,
  `gift_card_id` int(11) DEFAULT NULL,
  `coupon_id` int(11) DEFAULT NULL,
  `customer_acc_id` int(11) DEFAULT NULL,
  `payment_method_id` int(11) DEFAULT NULL,
  `order_id` int(11) NOT NULL,
  `payment_seq` int(11) DEFAULT NULL,
  `coupon_value_id` int(11) DEFAULT NULL,
  `payment_value` int(11) DEFAULT NULL,
  `remaining_balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`payment_detail_id`),
  KEY `fk_paymnt_detail_pay_method_id` (`payment_method_id`,`company_code`),
  KEY `fk_paymnt_detail_cust_acc_id` (`customer_acc_id`),
  KEY `fk_paymnt_detail_comp_gift_id` (`company_code`,`gift_card_id`),
  KEY `fk_paymnt_detail_comp_coupn_id` (`company_code`,`coupon_id`),
  KEY `fk_payment_detail_coupon_val_id` (`coupon_value_id`),
  KEY `fk_paymnt_detail_comp_order_id` (`company_code`,`order_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_payment_methods`;
CREATE TABLE  `medsysmagic`.`mc_payment_methods` (
  `payment_method_id` int(11) NOT NULL AUTO_INCREMENT,
  `payment_code` varchar(3) DEFAULT NULL,
  `payment_method_name` varchar(50) DEFAULT NULL,
  `payment_method_desc` varchar(20) DEFAULT NULL,
  `api_cmd` varchar(4000) DEFAULT NULL,
  `payment_method_icon_path` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`payment_method_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_prod_treatment`;
CREATE TABLE  `medsysmagic`.`mc_prod_treatment` (
  `prod_treatment_id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_code` int(11) NOT NULL,
  `treatment_id` int(11) NOT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`prod_treatment_id`),
  KEY `prod_treat_treatment_id_fk` (`treatment_id`),
  KEY `prod_treat_prod_id_fk` (`prod_code`),
  CONSTRAINT `mc_prod_treatment_ibfk_1` FOREIGN KEY (`treatment_id`) REFERENCES `mc_treatment` (`treatment_id`),
  CONSTRAINT `mc_prod_treatment_ibfk_2` FOREIGN KEY (`prod_code`) REFERENCES `mc_products` (`prod_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_product_categories`;
CREATE TABLE  `medsysmagic`.`mc_product_categories` (
  `company_code` int(11) NOT NULL,
  `category_code` int(11) NOT NULL,
  `prod_code` int(11) NOT NULL,
  PRIMARY KEY (`company_code`,`prod_code`),
  KEY `ec_product_categories_FI_2` (`category_code`),
  KEY `ec_product_categories_FI_3` (`prod_code`),
  CONSTRAINT `ec_product_categories_FK_1` FOREIGN KEY (`company_code`) REFERENCES `mc_companies` (`company_code`),
  CONSTRAINT `ec_product_categories_FK_2` FOREIGN KEY (`category_code`) REFERENCES `mc_categories` (`category_code`),
  CONSTRAINT `ec_product_categories_FK_3` FOREIGN KEY (`prod_code`) REFERENCES `mc_products` (`prod_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_product_colors`;
CREATE TABLE  `medsysmagic`.`mc_product_colors` (
  `color_id` int(11) NOT NULL AUTO_INCREMENT,
  `color_desc` varchar(50) DEFAULT NULL,
  `color_code` varchar(20) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`color_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_product_details`;
CREATE TABLE  `medsysmagic`.`mc_product_details` (
  `prod_code` int(11) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `prod_description` varchar(4000) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `show_as` char(10) DEFAULT 'B',
  PRIMARY KEY (`id`),
  KEY `ec_product_details_FI_1` (`prod_code`),
  CONSTRAINT `ec_product_details_FK_1` FOREIGN KEY (`prod_code`) REFERENCES `mc_products` (`prod_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_product_distributor`;
CREATE TABLE  `medsysmagic`.`mc_product_distributor` (
  `company_code` int(11) NOT NULL,
  `prod_code` int(11) NOT NULL,
  `distributor_id` int(11) NOT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`company_code`,`prod_code`,`distributor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_product_images`;
CREATE TABLE  `medsysmagic`.`mc_product_images` (
  `prod_image_id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_code` int(11) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `prod_thumbnail_path` varchar(200) DEFAULT NULL,
  `prod_large_image_path` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`prod_image_id`),
  KEY `fk_prod_images_prod_code` (`prod_code`)
) ENGINE=MyISAM AUTO_INCREMENT=34 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_product_manufactures`;
CREATE TABLE  `medsysmagic`.`mc_product_manufactures` (
  `company_code` int(11) NOT NULL,
  `prod_code` int(11) NOT NULL,
  `manufacture_id` int(11) NOT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`company_code`,`prod_code`,`manufacture_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_product_offers`;
CREATE TABLE  `medsysmagic`.`mc_product_offers` (
  `product_offer_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_code` int(11) NOT NULL,
  `prod_code` int(11) NOT NULL,
  `offer_id` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `prod_offer_desc` varchar(4000) DEFAULT NULL,
  `prod_list_price` int(11) DEFAULT NULL,
  `prod_offer_rate` int(11) DEFAULT NULL,
  `is_active` int(11) DEFAULT '0',
  PRIMARY KEY (`product_offer_id`),
  KEY `ec_product_offers_I_1` (`prod_code`),
  KEY `ec_product_offers_FI_2` (`offer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_product_prices`;
CREATE TABLE  `medsysmagic`.`mc_product_prices` (
  `product_price_id` int(11) NOT NULL AUTO_INCREMENT,
  `size_id` int(11) DEFAULT NULL,
  `company_code` int(11) NOT NULL,
  `prod_code` int(11) NOT NULL,
  `quality_id` int(11) DEFAULT NULL,
  `currency_id` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `wholesale_price` int(11) DEFAULT NULL,
  `retail_price` int(11) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `is_active` int(11) DEFAULT '1',
  PRIMARY KEY (`product_price_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_product_quality`;
CREATE TABLE  `medsysmagic`.`mc_product_quality` (
  `product_quality_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_code` int(11) NOT NULL,
  `prod_code` int(11) NOT NULL,
  `quality_id` int(11) NOT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`product_quality_id`,`company_code`,`prod_code`,`quality_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_product_size`;
CREATE TABLE  `medsysmagic`.`mc_product_size` (
  `company_code` int(11) NOT NULL,
  `prod_code` int(11) NOT NULL,
  `size_id` int(11) NOT NULL,
  `prod_sku` varchar(30) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `weight` int(11) DEFAULT NULL,
  `weight_type` varchar(3) DEFAULT NULL,
  `p_length` int(11) DEFAULT NULL,
  `width` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `display_seq` int(11) DEFAULT '0',
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`company_code`,`prod_code`,`size_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_product_type`;
CREATE TABLE  `medsysmagic`.`mc_product_type` (
  `prod_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_type_name` varchar(40) DEFAULT NULL,
  `prod_type_desc` varchar(4000) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`prod_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_products`;
CREATE TABLE  `medsysmagic`.`mc_products` (
  `prod_code` int(11) NOT NULL AUTO_INCREMENT,
  `prod_name` varchar(50) NOT NULL,
  `prod_sku` varchar(30) DEFAULT NULL,
  `prod_short_name` varchar(20) DEFAULT NULL,
  `prod_short_desc` varchar(1000) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `prod_price` int(11) DEFAULT NULL,
  `show_on_front` int(1) unsigned zerofill DEFAULT NULL,
  `is_new_prod` int(11) DEFAULT '1',
  `prod_thumbnail_path` varchar(200) DEFAULT NULL,
  `prod_large_image_path` varchar(200) DEFAULT NULL,
  `current_qty` int(11) DEFAULT NULL,
  `reorder_level` int(11) DEFAULT NULL,
  `special_message` varchar(200) DEFAULT NULL,
  `weight` int(11) DEFAULT NULL,
  `weight_type` varchar(3) DEFAULT NULL,
  `length` int(11) DEFAULT NULL,
  `wide` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `is_backorder_allow` int(1) DEFAULT NULL,
  `is_free_item` int(1) DEFAULT NULL,
  `is_downloadable` int(1) DEFAULT '0',
  `downloadable_link` varchar(200) DEFAULT NULL,
  `is_multi_color` int(1) NOT NULL DEFAULT '0',
  `is_multi_size` int(1) DEFAULT '0',
  `is_multi_price` int(1) DEFAULT '0',
  `is_shippable` int(1) DEFAULT '0',
  `is_taxable` int(1) DEFAULT '0',
  `special_condition` varchar(30) DEFAULT NULL,
  `is_active` int(11) DEFAULT '1',
  PRIMARY KEY (`prod_code`),
  KEY `ec_products_I_1` (`prod_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_quality`;
CREATE TABLE  `medsysmagic`.`mc_quality` (
  `quality_id` int(11) NOT NULL,
  `quality_code` varchar(10) DEFAULT NULL,
  `quality_name` varchar(50) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`quality_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_question_category`;
CREATE TABLE  `medsysmagic`.`mc_question_category` (
  `question_category_id` int(11) NOT NULL AUTO_INCREMENT,
  `question_category_name` varchar(50) DEFAULT NULL,
  `question_category_desc` varchar(4000) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `parent_category_id` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`question_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_questions`;
CREATE TABLE  `medsysmagic`.`mc_questions` (
  `mc_question_id` int(11) NOT NULL AUTO_INCREMENT,
  `question_category_id` int(11) DEFAULT NULL,
  `question_num` varchar(5) DEFAULT NULL,
  `question_desc` varchar(400) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`mc_question_id`),
  KEY `question_quest_cat_id_fk` (`question_category_id`),
  CONSTRAINT `mc_questions_ibfk_1` FOREIGN KEY (`question_category_id`) REFERENCES `mc_question_category` (`question_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_rate_conversion`;
CREATE TABLE  `medsysmagic`.`mc_rate_conversion` (
  `rate_conversion_id` int(11) NOT NULL AUTO_INCREMENT,
  `currency_from` int(11) DEFAULT NULL,
  `conversion_date` date DEFAULT NULL,
  PRIMARY KEY (`rate_conversion_id`),
  KEY `fk_rate_conversion_curr_id` (`currency_from`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_rate_conversion_detail`;
CREATE TABLE  `medsysmagic`.`mc_rate_conversion_detail` (
  `rate_conversion_detail_id` int(11) NOT NULL AUTO_INCREMENT,
  `currency_to` int(11) DEFAULT NULL,
  `rate_conversion_id` int(11) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`rate_conversion_detail_id`),
  KEY `fk_rate_conversion_dtl_curr_id` (`currency_to`),
  KEY `fk_rate_conversion_id` (`rate_conversion_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_relationship`;
CREATE TABLE  `medsysmagic`.`mc_relationship` (
  `relationship_id` int(11) NOT NULL AUTO_INCREMENT,
  `relationship_name` varchar(40) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`relationship_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_scheduler`;
CREATE TABLE  `medsysmagic`.`mc_scheduler` (
  `scheduler_id` int(11) NOT NULL AUTO_INCREMENT,
  `specialist_id` int(11) DEFAULT NULL,
  `facility_id` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `scheduler_date` date DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `admin_comments` varchar(4000) DEFAULT NULL,
  `cust_comments` varchar(4000) DEFAULT NULL,
  `create_date` date DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`scheduler_id`),
  KEY `scheduler_specialist_fk` (`specialist_id`),
  KEY `scheduler_facility_id_fk` (`facility_id`),
  CONSTRAINT `mc_scheduler_ibfk_1` FOREIGN KEY (`specialist_id`) REFERENCES `mc_specialist` (`specialist_id`),
  CONSTRAINT `mc_scheduler_ibfk_2` FOREIGN KEY (`facility_id`) REFERENCES `mc_facility` (`facility_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_search_images`;
CREATE TABLE  `medsysmagic`.`mc_search_images` (
  `search_image_id` int(11) NOT NULL AUTO_INCREMENT,
  `search_image_desc` varchar(20) DEFAULT NULL,
  `search_image_path` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`search_image_id`),
  UNIQUE KEY `pk_ec_search_images` (`search_image_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_section_style`;
CREATE TABLE  `medsysmagic`.`mc_section_style` (
  `section_style_id` int(11) NOT NULL AUTO_INCREMENT,
  `section_style_name` varchar(20) DEFAULT NULL,
  `section_style_abrv` varchar(10) DEFAULT NULL,
  `css_file_path` varchar(200) DEFAULT NULL,
  `js_file_path` varchar(200) DEFAULT NULL,
  `html_cmd` varchar(4000) DEFAULT NULL,
  `amination_delay` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`section_style_id`),
  UNIQUE KEY `pk_ec_section_style` (`section_style_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_setup`;
CREATE TABLE  `medsysmagic`.`mc_setup` (
  `company_code` int(11) NOT NULL,
  `max_product_in_row` int(1) DEFAULT '3',
  `is_product_catelog` int(11) NOT NULL DEFAULT '0',
  `is_product_management` int(11) NOT NULL DEFAULT '0',
  `is_product_search` int(11) NOT NULL DEFAULT '0',
  `is_special_offer` int(11) NOT NULL DEFAULT '0',
  `is_join_list` int(11) NOT NULL DEFAULT '0',
  `is_email_notification` int(11) NOT NULL DEFAULT '0',
  `is_shopping_cart` int(11) NOT NULL DEFAULT '0',
  `is_order_management` int(11) NOT NULL DEFAULT '0',
  `is_order_tracking` int(11) NOT NULL DEFAULT '0',
  `is_customer_management` int(11) NOT NULL DEFAULT '0',
  `is_epayment_gateway` int(11) NOT NULL DEFAULT '0',
  `is_category_image_display` int(11) NOT NULL DEFAULT '0',
  `max_category_level` int(11) NOT NULL DEFAULT '0',
  `max_product_level` int(11) NOT NULL DEFAULT '0',
  `max_product_on_page` int(11) DEFAULT '0',
  `max_menu_button` int(11) NOT NULL DEFAULT '6',
  `is_new_product` int(11) NOT NULL DEFAULT '0',
  `is_product_price` int(1) DEFAULT NULL,
  `is_prod_quality_option` int(1) DEFAULT '0',
  `is_multi_dimension` int(1) DEFAULT '0',
  `image_intervel` int(11) NOT NULL DEFAULT '0',
  `yt_user` varchar(30) NOT NULL,
  `is_additional_prod_info` int(1) DEFAULT '0',
  `is_prod_multi_image` int(1) DEFAULT '0',
  `is_wishlist` int(1) DEFAULT '0',
  `is_multi_lang` int(1) DEFAULT '0',
  `base_currency_id` int(11) DEFAULT NULL,
  `is_mutli_packing` int(1) DEFAULT NULL,
  `is_prod_multi_price` int(1) NOT NULL DEFAULT '0',
  `is_multi_currency` int(1) DEFAULT '0',
  `is_prod_bundling` int(1) DEFAULT NULL,
  `default_comp_images_path` varchar(200) DEFAULT NULL,
  `default_prod_images_path` varchar(200) DEFAULT NULL,
  `default_offer_images_path` varchar(200) DEFAULT NULL,
  `default_banner_images_path` varchar(200) DEFAULT NULL,
  `default_other_images_path` varchar(200) DEFAULT NULL,
  `default_js_file_path` varchar(200) DEFAULT NULL,
  `default_xml_file_path` varchar(200) DEFAULT NULL,
  `default_css_file_path` varchar(200) DEFAULT NULL,
  `is_active` int(11) DEFAULT '1',
  `is_discount_coupons` int(1) DEFAULT NULL,
  `is_gift_card_option` int(1) DEFAULT NULL,
  `is_product_multi_image` int(1) DEFAULT NULL,
  `category_images_diaply` int(1) DEFAULT '0',
  `company_abrv` char(5) DEFAULT NULL,
  PRIMARY KEY (`company_code`),
  CONSTRAINT `ec_setup_FK_1` FOREIGN KEY (`company_code`) REFERENCES `mc_companies` (`company_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_shipping_info`;
CREATE TABLE  `medsysmagic`.`mc_shipping_info` (
  `shipping_info_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `sno` int(11) DEFAULT NULL,
  `title` varchar(4) DEFAULT NULL,
  `first_name` varchar(20) DEFAULT NULL,
  `middle_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `street_address` varchar(50) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  `state_code` varchar(20) DEFAULT NULL,
  `zip_code` varchar(10) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`shipping_info_id`),
  KEY `fk_ec_shipping_info_cust_id` (`customer_id`)
) ENGINE=MyISAM AUTO_INCREMENT=78 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_shipping_methods`;
CREATE TABLE  `medsysmagic`.`mc_shipping_methods` (
  `shipping_method_id` int(11) NOT NULL AUTO_INCREMENT,
  `shipping_method_name` varchar(50) DEFAULT NULL,
  `shipping_method_desc` varchar(100) DEFAULT NULL,
  `shipping_method_icon_path` varchar(200) DEFAULT NULL,
  `shipping_method_api` varchar(20) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`shipping_method_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `medsysmagic`.`mc_sizes`;
CREATE TABLE  `medsysmagic`.`mc_sizes` (
  `size_id` int(11) NOT NULL,
  `size_code` varchar(20) DEFAULT NULL,
  `size_desc` varchar(100) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`size_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_special_offers`;
CREATE TABLE  `medsysmagic`.`mc_special_offers` (
  `offer_id` int(11) NOT NULL AUTO_INCREMENT,
  `offer_name` varchar(30) DEFAULT NULL,
  `offer_desc` varchar(4000) DEFAULT NULL,
  `offer_start_date` varchar(30) DEFAULT NULL,
  `offer_end_date` varchar(30) DEFAULT NULL,
  `offer_link` varchar(200) DEFAULT NULL,
  `offer_image_path` varchar(200) DEFAULT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`offer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_specialist`;
CREATE TABLE  `medsysmagic`.`mc_specialist` (
  `specialist_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) DEFAULT NULL,
  `middle_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  `degree_line1` varchar(100) DEFAULT NULL,
  `degree_line2` varchar(100) DEFAULT NULL,
  `office_phone` varchar(20) DEFAULT NULL,
  `home_phone` varchar(20) DEFAULT NULL,
  `cell_phone` varchar(20) DEFAULT NULL,
  `email_adderess` varchar(100) DEFAULT NULL,
  `specialist_picture_path` varchar(200) DEFAULT NULL,
  `is_picture_display` int(1) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `admin_comments` varchar(400) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`specialist_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_specialist_facility`;
CREATE TABLE  `medsysmagic`.`mc_specialist_facility` (
  `speclialist_facility_id` int(11) NOT NULL AUTO_INCREMENT,
  `specialist_id` int(11) NOT NULL,
  `facility_id` int(11) NOT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`speclialist_facility_id`),
  KEY `spec_facility_specialist_fk` (`specialist_id`),
  KEY `spec_facility_facility_id_fk` (`facility_id`),
  CONSTRAINT `mc_specialist_facility_ibfk_1` FOREIGN KEY (`specialist_id`) REFERENCES `mc_specialist` (`specialist_id`),
  CONSTRAINT `mc_specialist_facility_ibfk_2` FOREIGN KEY (`facility_id`) REFERENCES `mc_facility` (`facility_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_specialist_timing`;
CREATE TABLE  `medsysmagic`.`mc_specialist_timing` (
  `clinic_time_id` int(11) NOT NULL AUTO_INCREMENT,
  `specialist_id` int(11) DEFAULT NULL,
  `facility_id` int(11) DEFAULT NULL,
  `specialist_days` varchar(20) DEFAULT NULL,
  `time_form` varchar(20) DEFAULT NULL,
  `time_to` varchar(20) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `valid_from` date DEFAULT NULL,
  `valid_to` date DEFAULT NULL,
  `comments` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`clinic_time_id`),
  KEY `specialist_tim_facility_fk` (`facility_id`),
  KEY `specialist_tim_specialist_fk` (`specialist_id`),
  CONSTRAINT `mc_specialist_timing_ibfk_1` FOREIGN KEY (`facility_id`) REFERENCES `mc_facility` (`facility_id`),
  CONSTRAINT `mc_specialist_timing_ibfk_2` FOREIGN KEY (`specialist_id`) REFERENCES `mc_specialist` (`specialist_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_specialist_treatment`;
CREATE TABLE  `medsysmagic`.`mc_specialist_treatment` (
  `specialist_treatment_id` int(11) NOT NULL AUTO_INCREMENT,
  `facility_id` int(11) DEFAULT NULL,
  `specialist_id` int(11) DEFAULT NULL,
  `treatment_id` int(11) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `comments` varchar(400) DEFAULT NULL,
  `service_rate` decimal(10,0) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`specialist_treatment_id`),
  KEY `speciallist_treat_faclty_id_fk` (`facility_id`),
  KEY `speciallist_treat_treat_id_fk` (`treatment_id`),
  KEY `speciallist_treat_spec_id_fk` (`specialist_id`),
  CONSTRAINT `mc_specialist_treatment_ibfk_1` FOREIGN KEY (`facility_id`) REFERENCES `mc_facility` (`facility_id`),
  CONSTRAINT `mc_specialist_treatment_ibfk_2` FOREIGN KEY (`facility_id`) REFERENCES `mc_facility` (`facility_id`),
  CONSTRAINT `mc_specialist_treatment_ibfk_3` FOREIGN KEY (`treatment_id`) REFERENCES `mc_treatment` (`treatment_id`),
  CONSTRAINT `mc_specialist_treatment_ibfk_4` FOREIGN KEY (`specialist_id`) REFERENCES `mc_specialist` (`specialist_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_template_category`;
CREATE TABLE  `medsysmagic`.`mc_template_category` (
  `template_category_id` int(11) NOT NULL AUTO_INCREMENT,
  `template_category_name` varchar(40) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`template_category_id`),
  UNIQUE KEY `pk_ec_template_category` (`template_category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_template_languages`;
CREATE TABLE  `medsysmagic`.`mc_template_languages` (
  `web_template_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  PRIMARY KEY (`web_template_id`,`language_id`),
  UNIQUE KEY `pk_ec_template_languages` (`web_template_id`,`language_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_template_search_option_link`;
CREATE TABLE  `medsysmagic`.`mc_template_search_option_link` (
  `search_option_link_id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) DEFAULT NULL,
  `web_template_id` int(11) DEFAULT NULL,
  `search_option_text` varchar(40) DEFAULT NULL,
  `search_option_link` varchar(200) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`search_option_link_id`),
  UNIQUE KEY `pk_ec_template_search_option_l` (`search_option_link_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_treatment`;
CREATE TABLE  `medsysmagic`.`mc_treatment` (
  `treatment_id` int(11) NOT NULL AUTO_INCREMENT,
  `treatment_name` varchar(50) DEFAULT NULL,
  `treatment_desc` varchar(4000) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `service_rate` decimal(10,0) DEFAULT NULL,
  `is_active` int(1) DEFAULT NULL,
  PRIMARY KEY (`treatment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_user_privileges`;
CREATE TABLE  `medsysmagic`.`mc_user_privileges` (
  `company_code` int(11) NOT NULL,
  `login_id` varchar(30) NOT NULL,
  `web_option_id` int(11) NOT NULL,
  `add_alow` int(11) DEFAULT '1',
  `update_alow` int(11) DEFAULT '1',
  `delete_alow` int(11) DEFAULT '1',
  PRIMARY KEY (`company_code`,`login_id`,`web_option_id`),
  KEY `ec_user_privileges_FI_2` (`login_id`),
  KEY `ec_user_privileges_FI_3` (`web_option_id`),
  CONSTRAINT `ec_user_privileges_FK_1` FOREIGN KEY (`company_code`) REFERENCES `mc_companies` (`company_code`),
  CONSTRAINT `ec_user_privileges_FK_2` FOREIGN KEY (`login_id`) REFERENCES `mc_users` (`login_id`),
  CONSTRAINT `ec_user_privileges_FK_3` FOREIGN KEY (`web_option_id`) REFERENCES `mc_web_options` (`web_option_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_users`;
CREATE TABLE  `medsysmagic`.`mc_users` (
  `login_id` varchar(30) NOT NULL,
  `user_password` varchar(30) NOT NULL,
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `email_address` varchar(50) DEFAULT NULL,
  `phone_no` varchar(20) DEFAULT NULL,
  `cell_no` varchar(20) DEFAULT NULL,
  `is_admin` tinyint(1) NOT NULL,
  `is_active` int(11) DEFAULT NULL,
  `forgotten_password` varchar(30) NOT NULL,
  PRIMARY KEY (`login_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_vertical_banners`;
CREATE TABLE  `medsysmagic`.`mc_vertical_banners` (
  `vertical_banner_id` int(11) NOT NULL AUTO_INCREMENT,
  `web_template_id` int(11) DEFAULT NULL,
  `top_bar_image_height` varchar(10) DEFAULT NULL,
  `top_bar_image_width` int(11) DEFAULT NULL,
  `padding_top` int(11) DEFAULT NULL,
  `padding_left` int(11) DEFAULT NULL,
  `top_bar_image_id` int(11) DEFAULT NULL,
  `banner_top_corner_heading` varchar(20) DEFAULT NULL,
  `bottom_bar_image_id` int(11) DEFAULT NULL,
  `bottom_bar_image_height` char(18) DEFAULT NULL,
  `bottom_bar_image_width` int(11) DEFAULT NULL,
  `css_file_path` varchar(200) DEFAULT NULL,
  `js_file_path` varchar(200) DEFAULT NULL,
  `banner_id` int(11) DEFAULT NULL,
  `banner_animation_delay` varchar(10) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`vertical_banner_id`),
  UNIQUE KEY `pk_ec_vertical_banners` (`vertical_banner_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_web_options`;
CREATE TABLE  `medsysmagic`.`mc_web_options` (
  `web_option_id` int(11) NOT NULL AUTO_INCREMENT,
  `web_option_name` varchar(30) DEFAULT NULL,
  `web_thumbnail_path` varchar(200) NOT NULL,
  `web_file_path` varchar(100) NOT NULL,
  `display_seq` int(4) NOT NULL,
  `parent_web_option_id` int(4) NOT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`web_option_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_web_template_button_images`;
CREATE TABLE  `medsysmagic`.`mc_web_template_button_images` (
  `template_section_id` int(11) NOT NULL,
  `button_image_id` int(11) NOT NULL,
  `button_text` varchar(20) DEFAULT NULL,
  `display_seq` int(11) DEFAULT NULL,
  `page_link_path` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`template_section_id`,`button_image_id`),
  UNIQUE KEY `pk_ec_web_template_button_imag` (`template_section_id`,`button_image_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`mc_web_templates`;
CREATE TABLE  `medsysmagic`.`mc_web_templates` (
  `web_template_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_template_id` int(11) DEFAULT NULL,
  `web_template_name` varchar(100) DEFAULT NULL,
  `web_template_abrv` varchar(10) DEFAULT NULL,
  `web_template_width` int(11) DEFAULT NULL,
  `web_template_height` int(11) DEFAULT NULL,
  `css_file_path` varchar(200) DEFAULT NULL,
  `js_file_path` varchar(200) DEFAULT NULL,
  `favicon` varchar(200) DEFAULT NULL,
  `explore_title_message` varchar(200) DEFAULT NULL,
  `explore_footer_message` varchar(200) DEFAULT NULL,
  `template_category_id` int(11) DEFAULT NULL,
  `web_template_type` int(1) DEFAULT NULL,
  `background_image_id` int(11) DEFAULT NULL,
  `background_image_style` varchar(20) DEFAULT NULL,
  `background_color` varchar(20) DEFAULT NULL,
  `bg_music_id` int(11) DEFAULT NULL,
  `bg_music_style` varchar(10) DEFAULT NULL,
  `body_bg_color` varchar(20) DEFAULT NULL,
  `page_special_effect_id` int(11) DEFAULT NULL,
  `catelog_location` varchar(1) DEFAULT NULL,
  `catelog_main_heading` varchar(20) DEFAULT NULL,
  `catelog_sub_heading` varchar(40) DEFAULT NULL,
  `catelog_text_color` varchar(10) DEFAULT NULL,
  `catelog_mouseover_color` varchar(10) DEFAULT NULL,
  `catalog_bullet_id` int(11) DEFAULT NULL,
  `catalog_mouseover_bullet_id` int(11) DEFAULT NULL,
  `vertical_banner_location` varchar(2) DEFAULT NULL,
  `header_border_image_id` int(11) DEFAULT NULL,
  `header_border_height` varchar(10) DEFAULT NULL,
  `header_border_width` varchar(10) DEFAULT NULL,
  `header_border_style` varchar(20) DEFAULT NULL,
  `footer_border_image_id` int(11) DEFAULT NULL,
  `footer_border_heigth` varchar(10) DEFAULT NULL,
  `footer_border_width` varchar(10) DEFAULT NULL,
  `footer_border_style` varchar(20) DEFAULT NULL,
  `left_border_image_id` int(11) DEFAULT NULL,
  `left_border_height` varchar(10) DEFAULT NULL,
  `left_border_width` varchar(10) DEFAULT NULL,
  `left_border_style` varchar(20) DEFAULT NULL,
  `right_border_image_id` int(11) DEFAULT NULL,
  `right_border_height` varchar(10) DEFAULT NULL,
  `right_border_width` varchar(10) DEFAULT NULL,
  `right_border_style` varchar(20) DEFAULT NULL,
  `search_bg_image_id` int(11) DEFAULT NULL,
  `search_bg_image_width` varchar(10) DEFAULT NULL,
  `search_bg_image_height` varchar(10) DEFAULT NULL,
  `search_bg_color` varchar(10) DEFAULT NULL,
  `right_top_bar_heading` varchar(40) DEFAULT NULL,
  `right_top_bar_image_id` int(11) DEFAULT NULL,
  `right_top_bar_image_height` varchar(10) DEFAULT NULL,
  `right_top_bar_image_width` varchar(10) DEFAULT NULL,
  `right_corner_bg_color` varchar(20) DEFAULT NULL,
  `right_bottom_bar_image_id` int(11) DEFAULT NULL,
  `right_bottom_bar_image_height` varchar(10) DEFAULT NULL,
  `right_bottom_bar_image_width` varchar(10) DEFAULT NULL,
  `left_top_bar_heading` varchar(40) DEFAULT NULL,
  `left_top_bar_image_id` int(11) DEFAULT NULL,
  `left_top_bar_height` varchar(10) DEFAULT NULL,
  `left_top_bar_width` varchar(10) DEFAULT NULL,
  `left_corner_bg_color` varchar(20) DEFAULT NULL,
  `left_bottom_bar_image_id` int(11) DEFAULT NULL,
  `left_bottom_bar_image_height` varchar(10) DEFAULT NULL,
  `left_bottom_bar_image_width` varchar(10) DEFAULT NULL,
  `company_welcome_note` varchar(4000) DEFAULT NULL,
  `company_welcome_note_height` varchar(10) DEFAULT NULL,
  `company_welcome_note_width` varchar(10) DEFAULT NULL,
  `home_left_image_path` varchar(200) DEFAULT NULL,
  `home_left_image_redirect_link` varchar(200) DEFAULT NULL,
  `home_right_image_path` varchar(200) DEFAULT NULL,
  `home_right_image_redirect_link` varchar(200) DEFAULT NULL,
  `vedio_link_path` varchar(200) DEFAULT NULL,
  `redirect_link_path` varchar(200) DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  PRIMARY KEY (`web_template_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medsysmagic`.`test_table`;
CREATE TABLE  `medsysmagic`.`test_table` (
  `time_zone` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `id` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DELIMITER $$

DROP PROCEDURE IF EXISTS `medsysmagic`.`dsql_example4`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `medsysmagic`.`dsql_example4`(db_name VARCHAR(64), 
			template VARCHAR(65535))
BEGIN
		DECLARE done INT DEFAULT 0;
		DECLARE tname VARCHAR(64);
		DECLARE c CURSOR FOR
			  SELECT table_name 
			  FROM information_schema.TABLES 
			  WHERE table_schema=db_name;
		DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done= 1;
		
		OPEN c;
		REPEAT
		   FETCH c INTO tname;
		   SET @stmt_text=REPLACE(template, " ?", concat(" ", tname));
		   
		   BEGIN
		       DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
		        SELECT CONCAT("'", @stmt_text, "' command failed") AS "error";
			PREPARE stmt FROM @stmt_text;
			EXECUTE stmt;
		        DEALLOCATE prepare stmt;
	           END;
		UNTIL done END REPEAT;
        END $$

DELIMITER ;

DELIMITER $$

DROP FUNCTION IF EXISTS `medsysmagic`.`fnc_dsql_factory`$$
CREATE DEFINER=`root`@`localhost` FUNCTION  `medsysmagic`.`fnc_dsql_factory`(p_comp_cd int,
			     p_language_id int,
			     p_table_name VARCHAR(30)
			    ) RETURNS varchar(4000) CHARSET latin1
BEGIN

    DECLARE done   INT          DEFAULT 0;
		DECLARE v_col  VARCHAR(30)  DEFAULT ' ';
		DECLARE v_head VARCHAR(100) DEFAULT ' ';
		DECLARE stmt_text VARCHAR(4000) DEFAULT ' ';
		DECLARE no_data_found BOOLEAN;
    DECLARE v_count int DEFAULT 0;
    DECLARE v_total_rec int DEFAULT 0;
    DECLARE v_coma VARCHAR(2);
    DECLARE l_last_row INT DEFAULT 0;
    DECLARE v_select VARCHAR(25) DEFAULT 'SELECT ';
    DECLARE v_from    VARCHAR(15) DEFAULT 'FROM  ';
    DECLARE v_is_display int DEFAULT 1;

		DECLARE c CURSOR FOR
			  SELECT  column_name, column_text,is_display
			  FROM 	ec_apps_tables			a,
				ec_apps_tables_description	b,
				ec_company_apps_tables 		c
			  WHERE a.apps_table_id=b.apps_table_id
			    AND a.table_name=p_table_name
			    AND a.apps_table_id=c.apps_table_id
			    AND a.language_id=p_language_id
			    AND c.company_code=p_comp_cd
			    AND c.is_active=1
          and b.is_display=1
			  ORDER BY b.display_seq;

		DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row=1;
    set v_coma='';
		OPEN c;
    SET v_total_rec = (Select FOUND_ROWS());
	  c_cursor: LOOP
		   FETCH c INTO v_col, v_head,v_is_display;

     IF (l_last_row=1) THEN
          LEAVE c_cursor;
     ELSE

       SET v_count = v_count+1;
        IF(v_count < v_total_rec) and v_is_display=1 THEN

          set v_coma=concat(",",char(10));
        ELSE
          set v_coma="";
        END IF;
          IF v_is_display=1 THEN
            SET stmt_text= concat(stmt_text,v_col, " as ", "'", v_head,"'",v_coma);
          END IF;
     END if;

		END LOOP c_cursor;
    CLOSE c;
		return concat(v_select,char(10),stmt_text,char(10),v_from,p_table_name);
END;

 $$

DELIMITER ;

DELIMITER $$

DROP FUNCTION IF EXISTS `medsysmagic`.`fnc_facility_name`$$
CREATE DEFINER=`root`@`localhost` FUNCTION  `medsysmagic`.`fnc_facility_name`(p_facility_id int) RETURNS varchar(255) CHARSET latin1
BEGIN

DECLARE v_facility_name varchar(255) DEFAULT null;
begin

	select facility_name into v_facility_name
 from mc_facility
 where facility_id=p_facility_id;

  
end;
RETURN v_facility_name;
END;

 $$

DELIMITER ;

DELIMITER $$

DROP FUNCTION IF EXISTS `medsysmagic`.`fnc_getAttributePath`$$
CREATE DEFINER=`root`@`localhost` FUNCTION  `medsysmagic`.`fnc_getAttributePath`(p_comp_cd int, p_attribute_name varchar(255)) RETURNS varchar(255) CHARSET latin1
BEGIN

DECLARE v_attribute_value varchar(255) DEFAULT null;
begin

	select av.attribute_value into v_attribute_value
	from   ec_setup_attributes   sa,
	       ec_attribute_values   av,
	       ec_company_attributes ca
	where  sa.attribute_name=
			(select  sa.stored_location_attribute
			   from  ec_setup_attributes    sa,
			         ec_company_attributes  ca
			  where  sa.attribute_id=ca.attribute_id
			    and  ca.company_code=p_comp_cd
			    and  ca.is_active=1
			    and  sa.is_active=1
			    and  sa.attribute_name=p_attribute_name
			 )
	  and  sa.attribute_id=av.attribute_id
	  and  sa.attribute_id=ca.attribute_id
	  and  ca.company_code=p_comp_cd 
	  and  ca.company_code=av.company_code
	  and  ca.is_active=1
	  and  av.is_active=1
  	  and  sa.is_active=1;
  
end;
RETURN v_attribute_value;
END;

 $$

DELIMITER ;

DELIMITER $$

DROP FUNCTION IF EXISTS `medsysmagic`.`fnc_getAttributeValue`$$
CREATE DEFINER=`root`@`localhost` FUNCTION  `medsysmagic`.`fnc_getAttributeValue`(p_comp_cd int, p_attribute_name varchar(255)) RETURNS varchar(255) CHARSET latin1
BEGIN

DECLARE v_attribute_value varchar(255) DEFAULT null;

begin
	

	select av.attribute_value into v_attribute_value
	from   ec_setup_attributes   sa,
	       ec_attribute_values   av,
	       ec_company_attributes  ca
	where  sa.attribute_name=p_attribute_name
	  and  sa.attribute_id=av.attribute_id
	  and  sa.attribute_id=ca.attribute_id
	  and  (sa.web_template_id=fnc_getCompanyWebTemplateID(p_comp_cd) or sa.web_template_id is null)
	  and  ca.company_code=1
	  and  ca.company_code=av.company_Code
	  and  ca.is_active=1
	  and  av.is_active=1
  	  and  sa.is_active=1;
  
end;
RETURN v_attribute_value ;
END;

 $$

DELIMITER ;

DELIMITER $$

DROP FUNCTION IF EXISTS `medsysmagic`.`fnc_getCompanyWebTemplateID`$$
CREATE DEFINER=`root`@`localhost` FUNCTION  `medsysmagic`.`fnc_getCompanyWebTemplateID`(p_comp_cd int) RETURNS int(11)
BEGIN

DECLARE v_template_id int DEFAULT 0;
begin

	select web_template_id into v_template_id
	from ec_web_templates_events
	where company_code=p_comp_cd
	and CURDATE() between start_Date and end_Date;
end;
RETURN v_template_id;
END;

 $$

DELIMITER ;

DELIMITER $$

DROP FUNCTION IF EXISTS `medsysmagic`.`isnumeric`$$
CREATE DEFINER=`root`@`localhost` FUNCTION  `medsysmagic`.`isnumeric`(s varchar(255)) RETURNS int(11)
begin
   set @match = '^(([0-9+-.$]{1})|([+-]?[$]?[0-9]*(([.]{1}[0-9]*)|([.]?[0-9]+))))$';
   
   return if(s regexp @match, 1, 0);
   end;

 $$

DELIMITER ;

DELIMITER $$

DROP FUNCTION IF EXISTS `medsysmagic`.`next_day`$$
CREATE DEFINER=`root`@`localhost` FUNCTION  `medsysmagic`.`next_day`(p_date datetime,p_day nvarchar (20)) RETURNS date
BEGIN

DECLARE v_date date default DATE_FORMAT(now(), '%Y-%m-%d');
DECLARE v_day varchar(20) ;
DECLARE v_cnt int default 0 ;
begin

    set v_date=DATE_FORMAT(p_date, '%Y-%m-%d');

LOOP1: LOOP
 SET v_date=DATE_ADD(v_date, INTERVAL 1 DAY);

     SET v_day=dayname(v_date);
 SET v_cnt=v_cnt+1;
IF upper(v_day)=upper(p_day) OR v_cnt >7 then
  LEAVE LOOP1;
END IF;

END LOOP;

end;
RETURN v_date ;
END;

 $$

DELIMITER ;

DELIMITER $$

DROP FUNCTION IF EXISTS `medsysmagic`.`nvl2`$$
CREATE DEFINER=`root`@`localhost` FUNCTION  `medsysmagic`.`nvl2`(p_para1 VARCHAR(255), p_para2 VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
BEGIN

DECLARE p_String VARCHAR(255) DEFAULT null;
begin

if p_para1='' or p_para1 is null THEN

  set p_para1 = null;
end if;
 IF p_para1 is not null THEN
  set p_String=p_para1;
 Else
  set p_String=p_para2;
  END IF;
end;
RETURN p_String;
END;

 $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `medsysmagic`.`sp_apps_msg`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `medsysmagic`.`sp_apps_msg`(
  		IN p_comp_cd int,
  		IN p_app_cd VARCHAR(05),
  		IN p_msg_cd  VARCHAR(05),
                IN p_language_id int,
  		OUT p_msg_text text,
  		OUT p_msg_sev  int)
BEGIN
  		SELECT msg_text,msg_sev
  		INTO p_msg_text, p_msg_sev
  		FROM ec_apps_messges a,
  		     ec_company_apps_messages b
  		WHERE a.apps_msg_id=b.apps_msg_id
  		  AND b.company_code=p_comp_cd
                  AND a.language_id=p_language_id
  		  AND a.app_code=p_app_cd
  		  AND a.msg_code=p_msg_cd;
  		  
  		  set p_msg_text=CONCAT(p_comp_cd,'-',p_msg_code,'-',p_msg_text);
  		  
      END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `medsysmagic`.`sp_getImageAttributeValue`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `medsysmagic`.`sp_getImageAttributeValue`(
		IN p_comp_cd         	int,
		IN p_attribute_name 	nvarchar(100),
		OUT p_attribute_value   nvarchar(200),
		OUT p_top_padding       int,
		OUT p_left_padding   	int,
		OUT p_attribute_height  int,
		OUT p_attribute_width   int,
		OUT p_attribute_style   nvarchar(20)
		)
BEGIN
	DECLARE v_template_id int DEFAULT 1;
begin

	select av.attribute_value,av.top_padding,av.left_padding,av.attribute_height,
	       av.attribute_width, av.attribute_style
	into   p_attribute_value,p_top_padding,p_left_padding,p_attribute_height,
	       p_attribute_width, p_attribute_style
	from   ec_setup_attributes   sa,
	       ec_attribute_values   av,
	       ec_company_attributes  ca
	where  sa.attribute_name=p_attribute_name
	  and  sa.attribute_id=av.attribute_id
	  and  sa.attribute_id=ca.attribute_id
	  and  (sa.web_template_id=v_template_id or sa.web_template_id is null)
	  and  ca.company_code=p_comp_cd
	  and  ca.company_code=av.company_Code
	  and  ca.is_active=1
	  and  av.is_active=1
	  and  sa.is_active=1;

end;
END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `medsysmagic`.`sp_l_getListvalue`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `medsysmagic`.`sp_l_getListvalue`(
		in p_comp_cd	       int,
		in p_attribute_name    varchar(200)
		)
BEGIN
select av.attribute_value as ID ,av.attribute_value_desc as V_TEXT
from   ec_setup_attributes   sa,
       ec_attribute_values   av,
       ec_company_attributes  ca
where  sa.attribute_name=p_attribute_name
  and  sa.attribute_id=av.attribute_id
  and  sa.attribute_id=ca.attribute_id
  and  ca.company_code=p_comp_cd
  and  ca.company_code=av.company_Code
  and  ca.is_active=1
  and  av.is_active=1
  and  sa.is_active=1;

END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `medsysmagic`.`sp_l_getTableDescription`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `medsysmagic`.`sp_l_getTableDescription`(IN p_comp_code  VARCHAR(2),
                                                           IN p_table  VARCHAR(30),
                                                           IN p_apps_code VARCHAR(5),
                                                           IN p_is_actice int(1))
BEGIN
SELECT b.*
				FROM  ec_apps_tables a,
				     ec_apps_tables_description b,
				     ec_company_apps_tables c
				WHERE c.company_code=p_comp_code
				  and a.apps_table_id=b.apps_table_id
				  and a.apps_table_id=c.apps_table_id
				  and a.language_id=1
				  and a.apps_code=p_apps_code
				  and a.table_name=p_table
          and b.is_display=1
				  and b.IS_ACTIVE=p_is_actice
        ORDER BY b.display_seq;

END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `medsysmagic`.`sp_maint_mc_company_facility`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `medsysmagic`.`sp_maint_mc_company_facility`(
                INOUT p_company_facility_id  int ,
                IN    p_facility_id          int ,
                IN    p_display_seq          int ,
                IN    p_is_default           int ,
                IN    p_is_active            int ,
                IN    P_created_date         date ,
                IN    p_last_udpated_date    date ,
		IN    p_comp_cd	    int,
		in    p_language_id    int,
		IN    p_user_id	    int,
  		OUT   p_msg_icon nvarchar(200),
  		OUT   p_msg_text text,
  		OUT   p_msg_sev  int)
BEGIN
		DECLARE v_cnt int DEFAULT 0;
	Begin
		IF p_company_facility_id is not null then
			select count(1) into v_cnt 
			from mc_company_facility
	               	where company_facility_id= p_company_facility_id;
		End If;

	End;
		IF v_cnt =0 then

		   Insert into mc_company_facility (
			facility_id         ,
			company_code        ,
			display_seq         ,
			is_default          ,
			is_active           ,
			created_date        ,
			created_by          ,
			last_udpated_date   ,
			last_updated_by     
			)
		   values 
		     (
			p_facility_id       ,
			p_comp_cd           ,
			p_display_seq       ,
			p_is_default        ,
			p_is_active         ,
			CURDATE()           ,
			p_user_id           ,
			CURDATE()   	    ,
			p_user_id
		     );


		    
		    set p_company_facility_id=LAST_INSERT_ID();


		call sp_apps_msg(p_comp_cd,'MCH','00002',p_language_id, p_msg_text,p_msg_sev);

		Else
		     IF v_cnt >0 then

		     update mc_company_facility set
			facility_id	  	 =	p_facility_id	,
			company_code  		=	p_comp_cd  	,
			display_seq       =	p_display_seq   ,
			is_default      	=	p_is_default   	,
			is_active      	 =	p_is_active     ,
			last_updated_date =	CURDATE(),
			last_update_by		=	p_user
		    Where company_facility_id	=	p_company_facility_id;

		    call sp_apps_msg(p_comp_cd,'MCH','00003',p_language_id, p_msg_text,p_msg_sev);

		    Else

		   call sp_apps_msg(p_comp_cd,'MCH','00001',p_language_id, p_msg_text,p_msg_sev);

		    End if;

		End if;

		commit;
      END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `medsysmagic`.`sp_maint_mc_specialist`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `medsysmagic`.`sp_maint_mc_specialist`(
		INOUT p_specialist_id int,
		IN p_first_name	    nvarchar(20),
		IN p_middle_name    nvarchar(20),
		IN p_last_name      nvarchar(20),
		IN p_title          nvarchar(50),
		IN p_degree_line1   nvarchar(100),
		IN p_degree_line2   nvarchar(100),
		IN p_office_phone   nvarchar(20),
		IN p_home_phone     nvarchar(20),
		IN p_cell_phone     nvarchar(20),
		IN p_email_adderess nvarchar(100),
		IN p_specialist_picture_path nvarchar(200),
		IN p_is_picture_display int,
		IN p_display_seq        int,
		IN p_admin_comments nvarchar(400),
		IN p_is_active      int,
		IN p_comp_cd	    int,
		in p_language_id    int,
		IN p_user_id	    int,
  		OUT p_msg_icon nvarchar(200),
  		OUT p_msg_text text,
  		OUT p_msg_sev  int)
BEGIN
		DECLARE v_cnt int DEFAULT 0;
	Begin
		IF p_specialist_id is not null then
			select count(1) into v_cnt 
			from mc_specialist
	               	where specialist_id= p_specialist_id;
		End If;

	End;
		IF v_cnt =0 then

		   Insert into mc_specialist (
			first_name	  	,	
			middle_name  		,
			last_name    		,
			title        		,
			degree_line1 		,
			degree_line2 		,
			office_phone 		,
			home_phone   		,
			cell_phone   		,
			email_adderess 		,
			specialist_picture_path,
			is_picture_display 	,
			display_seq        	,
			admin_comments 		,
			is_active      		,
			created_date,
			created_by,
			last_updated_date,
			last_update_by
			)
		   values 
		     (
			p_first_name,	  		
			p_middle_name,  		
			p_last_name   , 		
			p_title       , 		
			p_degree_line1, 		
			p_degree_line2, 		
			p_office_phone, 		
			p_home_phone  , 		
			p_cell_phone  , 		
			p_email_adderess, 		
			p_specialist_picture_path,
			p_is_picture_display, 	
			p_display_seq     ,   	
			p_admin_comments ,		
			p_is_active      ,		
			p_user_id	 , 
			CURDATE()	,
			p_user_id	 ,
			CURDATE()
		     );
		    
		    set p_specialist_id=LAST_INSERT_ID();


		call sp_apps_msg(p_comp_cd,'MCH','00002',p_language_id, p_msg_text,p_msg_sev);

		Else
		     IF v_cnt >0 then

		     update mc_specialist set
			first_name	  	=	p_first_name	,
			middle_name  		=	p_middle_name  	,
			last_name    		=	p_last_name    	,
			title        		=	p_title        	,
			degree_line1 		=	p_degree_line1 	,
			degree_line2 		=	p_degree_line2 	,
			office_phone 		=	p_office_phone 	,
			home_phone   		=	p_home_phone   	,
			cell_phone   		=	p_cell_phone   	,
			email_adderess 		=	p_email_adderess ,
			specialist_picture_path	=	p_specialist_picture_path,
			is_picture_display 	=	p_is_picture_display 	,
			display_seq        	=	p_display_seq        	,
			admin_comments 		=	p_admin_comments 	,
			is_active      		=	p_is_active      	,
			last_updated_date	=	CURDATE(),
			last_update_by		=	p_user
		    Where specialist_id		=	p_specialist_id;

		    call sp_apps_msg(p_comp_cd,'MCH','00003',p_language_id, p_msg_text,p_msg_sev);

		    Else

		   call sp_apps_msg(p_comp_cd,'MCH','00001',p_language_id, p_msg_text,p_msg_sev);

		    End if;

		End if;

		commit;
      END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `medsysmagic`.`sp_rst_l_mc_facility`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `medsysmagic`.`sp_rst_l_mc_facility`(
       p_company_code         int ,
       p_language_id          int
  )
BEGIN

  select ' ' as ID,'Select..' as id_name ,-1 as ord
  union
  select facility_id as ID ,facility_name as id_name,display_seq as ord
  from mc_facility 
  where company_code=p_company_code
--  and   language_id =p_language_id
  order by ord;

  END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `medsysmagic`.`sp_rst_m_mc_facility`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `medsysmagic`.`sp_rst_m_mc_facility`(
       p_facility_id          int ,
       p_city                 nvarchar(50) ,
       p_state                nvarchar(20) ,
       p_phone                nvarchar(20) ,
       p_company_code         int ,
       p_language_id          int

  )
BEGIN

  DECLARE v_sql_stmt    nvarchar(8000) default ' ';
  DECLARE v_where_clause  nvarchar(4000) default ' ';

  set v_where_clause = concat(char(10),' WHERE ');
--  set v_where_clause = concat(v_where_clause,' facility_id=',nvl2(p_facility_id,0), char(10));
    set v_where_clause = concat(v_where_clause,'facility_id=',nvl2(p_facility_id,'facility_id '),char(10));

  IF IFNULL(p_city,'') != '' THEN
    set v_where_clause = concat(v_where_clause,'AND city like ',nvl2(concat( "'",'%',p_city,'%',"'"),'city_name'), char(10));
  END IF;

  IF IFNULL(p_state,'') != '' THEN
    set v_where_clause = concat(v_where_clause,'AND state like ',nvl2(concat( "'",'%',p_state,'%',"'"),'state'), char(10));
  END IF;

  IF IFNULL(p_phone,'') != '' THEN
    set v_where_clause = concat(v_where_clause,'AND phone01 like ',nvl2(concat( "'",'%',p_phone,'%',"'"),'phone01'), char(10));
  END IF;
  Set v_sql_stmt= fnc_dsql_factory(p_company_code,p_language_id,'mc_facility');


  SET @text_stmt= concat(v_sql_stmt,v_where_clause);
-- select @text_stmt;

   PREPARE stmt FROM @text_stmt;
       EXECUTE stmt;
      DEALLOCATE prepare stmt;


  END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `medsysmagic`.`sp_rst_s_mc_facility`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `medsysmagic`.`sp_rst_s_mc_facility`(
       p_facility_id          int,
       p_company_code int,
       p_language_id int

  )
BEGIN

  DECLARE v_sql_stmt    nvarchar(8000) default ' ';
  DECLARE v_where_clause  nvarchar(4000) default ' ';

  set v_where_clause = concat(char(10),' WHERE ');
  set v_where_clause = concat(v_where_clause,concat('facility_id=',p_facility_id));


  Set v_sql_stmt= fnc_dsql_factory(p_company_code,p_language_id,'mc_facility');


  SET @text_stmt= concat(v_sql_stmt,v_where_clause);

  PREPARE stmt FROM @text_stmt;
      EXECUTE stmt;
      DEALLOCATE prepare stmt;


  END $$

DELIMITER ;