CREATE TABLE IF NOT EXISTS `#__sr_experiences` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `state` TINYINT(1) NOT NULL DEFAULT 3,
  `name` VARCHAR(255) NOT NULL,
  `alias` VARCHAR(255) NOT NULL,
  `logo` TEXT NULL,
  `partner_id` INT(11) UNSIGNED NULL,
  `description_short` VARCHAR(255) NULL,
  `description_long` TEXT NULL,
  `code` VARCHAR(255) NULL,
  `tax_id` INT(11) UNSIGNED NULL DEFAULT NULL,
  `base_location` VARCHAR(255) NULL,
  `end_location` VARCHAR(255) NULL,
  `start_time` TIME NOT NULL,
  `duration` DECIMAL (5,2) UNSIGNED NULL,
  `duration_unit` TINYINT(3) UNSIGNED NULL,
  `min_participant` TINYINT(3) UNSIGNED NULL,
  `max_participant` TINYINT(3) UNSIGNED NULL,
  `price_includes` TEXT NULL,
  `price_excludes` TEXT NULL,
  `cancellation_policy` TEXT NULL,
  `languages` VARCHAR(255) NULL,
  `available_dates` TEXT NULL,
  `pricing` TEXT NULL,
  `pricing_base` DECIMAL(12,2) UNSIGNED NULL,
  `tour_size` TINYINT(3) UNSIGNED NULL,
  `country_id` INT(11) UNSIGNED NOT NULL,
  `contact_company_logo` VARCHAR(255) NULL,
  `contact_email` VARCHAR(255) NULL,
  `contact_address_1` VARCHAR(255) NULL,
  `contact_address_2` VARCHAR(255) NULL,
  `contact_city` VARCHAR(255) NULL,
  `contact_phone_number` VARCHAR(45) NULL,
  `contact_company_name` VARCHAR(255) NULL,
  `contact_fax_number` VARCHAR(45) NULL,
  `contact_geo_state_id` INT(11) UNSIGNED NULL DEFAULT NULL,
  `contact_country_id` INT(11) UNSIGNED NULL DEFAULT NULL,
  `disable_registration` TINYINT(1) NOT NULL DEFAULT 1,
  `deposit_required` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  `deposit_is_percentage` TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
  `deposit_amount` DECIMAL(12,4) UNSIGNED NULL,
  `deposit_incl_extra_cost` TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
  `params` TEXT NULL,
  `ordering` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `is_private` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  `language` CHAR(7) NOT NULL DEFAULT '*',
  `created_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `modified_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_by` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `checked_out` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `checked_out_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `meta_title` VARCHAR(255) NOT NULL DEFAULT '',
  `meta_key` VARCHAR(255) NOT NULL DEFAULT '',
  `meta_desc` VARCHAR(255) NOT NULL DEFAULT '',
  `tab_content` LONGTEXT,
  `hits` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `fk_sr_experiences_sr_countries1_idx` (`country_id` ASC),
  INDEX `fk_sr_experiences_sr_geo_states1_idx` (`contact_geo_state_id` ASC),
  INDEX `fk_sr_experiences_sr_taxes1_idx` (`tax_id` ASC),
  CONSTRAINT `fk_sr_experiences_sr_countries1`
    FOREIGN KEY (`country_id`)
    REFERENCES `#__sr_countries` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sr_experiences_sr_geo_states1`
    FOREIGN KEY (`contact_geo_state_id`)
    REFERENCES `#__sr_geo_states` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sr_experiences_sr_taxes1`
    FOREIGN KEY (`tax_id`)
    REFERENCES `#__sr_taxes` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `#__sr_experience_categories` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `alias` VARCHAR(255) NOT NULL,
  `state` TINYINT(3) NOT NULL,
  `image` VARCHAR(500) NOT NULL DEFAULT '',
  `description` VARCHAR(1000) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `#__sr_experience_transportations` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `state` TINYINT(3) NOT NULL DEFAULT 0,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS  `#__sr_experience_category_xref` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `experience_id` INT(11) UNSIGNED NOT NULL,
  `category_id` INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_sr_experience_category_xref_sr_experiences1_idx` (`experience_id` ASC),
  INDEX `fk_sr_experience_category_xref_sr_experience_catego_idx` (`category_id` ASC),
  CONSTRAINT `fk_sr_experience_category_xref_sr_experiences1`
    FOREIGN KEY (`experience_id`)
    REFERENCES `#__sr_experiences` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sr_experience_category_xref_sr_experience_categori1`
    FOREIGN KEY (`category_id`)
    REFERENCES `#__sr_experience_categories` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS  `#__sr_experience_transportation_xref` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `experience_id` INT(11) UNSIGNED NOT NULL,
  `transportation_id` INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_sr_experience_transportation_xref_sr_experiences_idx` (`experience_id` ASC),
  INDEX `fk_sr_experience_transportation_xref_sr_transportat_idx` (`transportation_id` ASC),
  CONSTRAINT `fk_sr_experience_transportation_xref_sr_experiences1`
    FOREIGN KEY (`experience_id`)
    REFERENCES `#__sr_experiences` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sr_experience_transportation_xref_sr_transportatio1`
    FOREIGN KEY (`transportation_id`)
    REFERENCES `#__sr_experience_transportations` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `#__sr_experience_timeslots` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `experience_id` INT(11) UNSIGNED NOT NULL,
  `guide_id` INT(11) UNSIGNED NOT NULL,
  `from` TIME NOT NULL,
  `to` TIME NOT NULL,
  `w_day` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `fk_exp_timeslots_exp1_idx` (`experience_id` ASC),
  CONSTRAINT `fk_exp_timeslots_exps1`
    FOREIGN KEY (`experience_id`)
    REFERENCES `#__sr_experiences` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `#__sr_experience_guides` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `state` TINYINT(3) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `partner_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `#__sr_experience_reservations` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `state` TINYINT(3) NOT NULL DEFAULT 0,
  `code` VARCHAR(255) NOT NULL,
  `user_id` INT(11) UNSIGNED NULL DEFAULT NULL,
  `customer_id` INT(11) UNSIGNED NULL DEFAULT NULL,
  `customer_name` VARCHAR(1000) NULL DEFAULT NULL,
  `book_date` DATE NULL DEFAULT '0000-00-00',
  `start_time` TIME NULL DEFAULT '00:00:00',
  `experience_id` INT(11) UNSIGNED NULL DEFAULT NULL,
  `experience_name` VARCHAR(255) NULL,
  `experience_history` MEDIUMTEXT NULL,
  `participants` TINYINT(3) UNSIGNED NULL DEFAULT 0,
  `summary` TEXT NULL,
  `created_date` DATETIME NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` INT(11) UNSIGNED NULL DEFAULT NULL,
  `modified_date` DATETIME NULL DEFAULT '0000-00-00 00:00:00',
  `modified_by` INT(11) UNSIGNED NULL DEFAULT NULL,
  `payment_method_id` INT(11) UNSIGNED NULL DEFAULT NULL,
  `payment_txn_id` VARCHAR(255) NULL,
  `payment_status` TINYINT(3) NULL DEFAULT 0,
  `payment_data` TEXT NULL,
  `payment_date` DATETIME NULL DEFAULT '0000-00-00 00:00:00',
  `total_price` DECIMAL(12,2) NULL DEFAULT NULL,
  `total_discount` DECIMAL(12,2) NULL DEFAULT NULL,
  `total_paid` DECIMAL(12,2) NULL DEFAULT NULL,
  `tax_amount` DECIMAL(12,2) NULL DEFAULT NULL,
  `currency_id` INT(11) UNSIGNED NULL DEFAULT NULL,
  `currency_code` VARCHAR(10) NULL,
  `token` VARCHAR(40) NULL,
  `deposit_amount` DECIMAL(12,4) UNSIGNED NULL,
  `payment_method_surcharge` DECIMAL(12,2) UNSIGNED NOT NULL DEFAULT 0.00,
  `payment_method_discount` DECIMAL(12,2) UNSIGNED NOT NULL DEFAULT 0.00,
  `coupon_id` INT(11) UNSIGNED NULL DEFAULT NULL,
  `coupon_code` VARCHAR(15) NULL,
  `coupon_amount` DECIMAL(12,2) UNSIGNED NOT NULL DEFAULT 0.00,
  `time_slot_id` INT(11) UNSIGNED NULL,
  `guide_id` INT(11) UNSIGNED NULL,
  `guide_name` VARCHAR(255) NULL,
  `time_slot_from` TIME NULL,
  `time_slot_to` TIME NULL,
  `time_slot_w_day` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  `supplement_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `origin_id` INT(11) UNSIGNED NULL DEFAULT NULL,
  `customer_ua` VARCHAR(255) NOT NULL DEFAULT '',
  `customer_ismobile` TINYINT(1) UNSIGNED NULL DEFAULT NULL,
  `customer_ip` VARCHAR(50) NOT NULL DEFAULT '',
  `customer_coordinates` TEXT,
  `customer_language` VARCHAR(7) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_exp_res_exps1_idx` (`experience_id` ASC),
  INDEX `fk_exp_res_coupons1_idx` (`coupon_id` ASC),
  INDEX `fk_exp_res_exp_timeslots_idx` (`time_slot_id` ASC),
  INDEX `fk_exp_res_exp_guides1_idx` (`guide_id` ASC),
  INDEX `fk_exp_res_origins1_idx` (`origin_id` ASC),
  CONSTRAINT `fk_exp_res_exps1`
    FOREIGN KEY (`experience_id`)
    REFERENCES `#__sr_experiences` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_exp_res_coupons1`
    FOREIGN KEY (`coupon_id`)
    REFERENCES `#__sr_coupons` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_exp_res_exp_timeslots1`
    FOREIGN KEY (`time_slot_id`)
    REFERENCES `#__sr_experience_timeslots` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_exp_res_exp_guides1`
    FOREIGN KEY (`guide_id`)
    REFERENCES `#__sr_experience_guides` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_exp_res_origins1`
    FOREIGN KEY (`origin_id`)
    REFERENCES `#__sr_origins` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `#__sr_experience_reservation_guests` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `reservation_id` INT(11) UNSIGNED NULL,
  `adult` TINYINT(1) UNSIGNED NULL,
  `range_age` VARCHAR(45) NULL,
  `first_name` VARCHAR(45) NULL,
  `middle_name` VARCHAR(45) NULL,
  `last_name` VARCHAR(45) NULL,
  `dob` DATE NULL,
  `gender` TINYINT(2) UNSIGNED NULL,
  `nationality` VARCHAR(45) NULL,
  `single_room` TINYINT(2) UNSIGNED NULL,
  `columns` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_exp_reservation_guests_exp_idx` (`reservation_id` ASC),
  CONSTRAINT `fk_exp_reservation_guests_exp_res1`
    FOREIGN KEY (`reservation_id`)
    REFERENCES `#__sr_experience_reservations` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `#__sr_experience_reservation_extra_xref` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `reservation_id` INT(11) UNSIGNED NOT NULL,
  `extra_id` INT(11) UNSIGNED NULL,
  `extra_name` VARCHAR(255) NULL,
  `extra_quantity` INT(11) NULL,
  `extra_price` DECIMAL(12,2) NULL,
  `extra_taxes` DECIMAL(12,2) NULL,
  `extra_description` TEXT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_sr_exp_res_extra_xref_sr_exp_idx` (`reservation_id` ASC),
  INDEX `fk_sr_exp_res_extra_xref_sr_extras1_idx` (`extra_id` ASC),
  CONSTRAINT `fk_sr_exp_res_extra_xref_sr_exp1`
  FOREIGN KEY (`reservation_id`)
  REFERENCES `#__sr_experience_reservations` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sr_exp_res_extra_xref_sr_extras1`
  FOREIGN KEY (`extra_id`)
  REFERENCES `#__sr_extras` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `#__sr_experience_asset_xref` (
  `experience_id` INT(11) UNSIGNED NOT NULL,
  `reservation_asset_id` INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`experience_id`, `reservation_asset_id`),
  INDEX `fk_exp_asset_xref_assets1_idx` (`reservation_asset_id` ASC),
  CONSTRAINT `fk_exp_asset_xref_experiences1`
    FOREIGN KEY (`experience_id`)
    REFERENCES `#__sr_experiences` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_exp_asset_xref_assets1`
    FOREIGN KEY (`reservation_asset_id`)
    REFERENCES `#__sr_reservation_assets` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `#__sr_experience_date_xref` (
  `experience_id` INT(11) UNSIGNED NOT NULL,
  `date` DATETIME NOT NULL,
  `tariffs` TEXT NULL,
  PRIMARY KEY (`experience_id`, `date`)
)
  ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `#__sr_experience_guide_user_xref` (
  `guide_id` INT(11) UNSIGNED NOT NULL,
  `user_id` INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`guide_id`, `user_id`))
  ENGINE = InnoDB;

INSERT INTO `#__content_types` (`type_id`, `type_title`, `type_alias`, `table`, `rules`, `field_mappings`, `router`, `content_history_options`) VALUES (0, 'Solidres Experience', 'com_solidres.experience', '{"special":{"dbtable":"#__sr_experiences","key":"id","type":"Experience","prefix":"SolidresTable","config":"array()"},"common":{"dbtable":"#__ucm_content","key":"ucm_id","type":"Corecontent","prefix":"JTable","config":"array()"}}', '', '{"common":{"core_content_item_id":"id","core_title":"name","core_state":"state","core_alias":"alias","core_created_time":"created_date","core_modified_time":"modified_date","core_body":"long_description", "core_hits":"null","core_publish_up":"null","core_publish_down":"null","core_access":"null", "core_params":"params", "core_featured":"null", "core_metadata":"null", "core_language":"language", "core_images":"null", "core_urls":"null", "core_version":"null", "core_ordering":"ordering", "core_metakey":"null", "core_metadesc":"null", "core_catid":"null", "core_xreference":"null", "asset_id":"null"}, "special":{}}', 'SRExperienceHelper::getItemRoute', '');