CREATE TABLE IF NOT EXISTS `#__sr_experiences` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `state` TINYINT(3) NOT NULL DEFAULT 0,
  `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` TINYINT(3) 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_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,
  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
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

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,
  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
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

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_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',
  `coupon_code` VARCHAR(255) NULL,
  `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,
  PRIMARY KEY (`id`),
  INDEX `fk_sr_experience_reservations_sr_experiences1_idx` (`experience_id` ASC),
  CONSTRAINT `fk_sr_experience_reservations_sr_experiences1`
    FOREIGN KEY (`experience_id`)
    REFERENCES `#__sr_experiences` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;