-- Adminer 5.4.1 MariaDB 11.4.10-MariaDB-cll-lve-log dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

CREATE DATABASE `new_timsDB`;
USE `new_timsDB`;

SET NAMES utf8mb4;


CREATE TABLE `airlines` (
  `airline_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `name` varchar(150) NOT NULL,
  `thumbnail_url` varchar(255) DEFAULT NULL,
  `thumbnail_meta` text DEFAULT NULL COMMENT 'myThumbnail JSON object',
  `active` tinyint(1) DEFAULT 1,
  `created_at` int(10) unsigned NOT NULL,
  PRIMARY KEY (`airline_id`),
  UNIQUE KEY `airlines_code` (`code`),
  KEY `airlines_name` (`name`),
  KEY `airlines_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `amenities` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `parent` int(11) DEFAULT NULL,
  `description` varchar(250) DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `icon` varchar(150) DEFAULT NULL,
  `web_text` varchar(250) DEFAULT NULL,
  `active` int(11) NOT NULL DEFAULT 1 COMMENT 'active 1 deleted 0',
  `lastmodified` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `api_group_permissions` (
  `group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `permission_id` int(10) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`group_id`,`permission_id`),
  KEY `permission_id` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `api_history` (
  `history_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `api_user_id` int(10) unsigned DEFAULT NULL,
  `group_id` int(11) unsigned DEFAULT NULL,
  `module` varchar(50) NOT NULL COMMENT 'Class or logical module name',
  `module_action` varchar(50) DEFAULT NULL COMMENT 'Function name e.g. SEARCH_RESORT',
  `ip_address` varchar(45) DEFAULT NULL COMMENT 'Supports IPv4 & IPv6',
  `request_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Sanitized request payload' CHECK (json_valid(`request_data`)),
  `response_code` smallint(5) unsigned NOT NULL COMMENT 'HTTP status code',
  `response_message` varchar(255) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`history_id`),
  KEY `idx_api_user` (`api_user_id`),
  KEY `idx_module` (`module_action`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `api_permissions` (
  `permission_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `permission_code` varchar(100) NOT NULL,
  `permission_description` varchar(255) DEFAULT NULL,
  `active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`permission_id`),
  UNIQUE KEY `permission_code` (`permission_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `api_user` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  `password` varchar(255) NOT NULL,
  `api_key` varchar(64) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=active,0=inactive',
  `group_id` int(10) unsigned DEFAULT NULL,
  `default_currency` varchar(6) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `uq_api_user_username` (`username`),
  UNIQUE KEY `uq_api_user_api_key` (`api_key`),
  KEY `idx_api_user_group` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `bedding` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(25) NOT NULL,
  `description` varchar(250) DEFAULT NULL,
  `adults` int(11) DEFAULT NULL,
  `children` int(11) DEFAULT NULL,
  `is_double` varchar(25) DEFAULT NULL,
  `is_share` varchar(25) DEFAULT NULL,
  `rollaways` int(11) DEFAULT NULL,
  `icon` varchar(200) DEFAULT NULL,
  `web_text` varchar(250) DEFAULT NULL,
  `active` int(11) NOT NULL DEFAULT 1 COMMENT 'active 1 deleted 0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `bookings` (
  `booking_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `prefix` varchar(2) DEFAULT NULL,
  `booking_ref` varchar(50) DEFAULT NULL,
  `lead_id` bigint(20) DEFAULT NULL,
  `lead_from` varchar(255) DEFAULT NULL,
  `customer_name` varchar(255) DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `fax` varchar(50) DEFAULT NULL,
  `mobile` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `address` varchar(500) DEFAULT NULL,
  `source` varchar(100) DEFAULT NULL,
  `branch_id` bigint(20) DEFAULT NULL,
  `referral` varchar(255) DEFAULT NULL,
  `group_id` bigint(20) DEFAULT NULL,
  `consultant_id` int(11) DEFAULT NULL,
  `adult_count` int(11) DEFAULT 0,
  `child_count` int(11) DEFAULT 0,
  `infant_count` int(11) DEFAULT 0,
  `note` varchar(500) DEFAULT NULL,
  `status` enum('draft','confirmed','cancelled') DEFAULT 'draft',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`booking_id`),
  UNIQUE KEY `booking_ref` (`booking_ref`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `booking_items` (
  `item_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `booking_id` bigint(20) DEFAULT NULL,
  `parent_component_id` bigint(20) NOT NULL,
  `component_id` bigint(20) NOT NULL,
  `component_type` varchar(50) DEFAULT NULL,
  `deal_id` bigint(20) DEFAULT NULL,
  `criteria_id` bigint(20) DEFAULT NULL,
  `rate_id` bigint(20) DEFAULT NULL,
  `parent_item_id` bigint(20) DEFAULT NULL,
  `quantity` int(11) DEFAULT 1,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `nights` int(11) DEFAULT NULL,
  `base_price` decimal(12,2) DEFAULT NULL,
  `tax_amount` decimal(12,2) DEFAULT NULL,
  `markup_type` enum('percentage','fixed') DEFAULT NULL,
  `markup_value` decimal(12,2) DEFAULT NULL,
  `markup_amount` decimal(12,2) DEFAULT NULL,
  `commission_type` enum('percentage','fixed') DEFAULT NULL,
  `commission_value` decimal(12,2) DEFAULT NULL,
  `commission_amount` decimal(12,2) DEFAULT NULL,
  `total_price` decimal(12,2) DEFAULT NULL,
  `base_currency` varchar(10) DEFAULT NULL,
  `applied_currency` varchar(10) DEFAULT NULL,
  `conversion_rate` decimal(12,6) DEFAULT NULL,
  `pricing_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`pricing_json`)),
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`item_id`),
  KEY `booking_id` (`booking_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `booking_logs` (
  `log_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `booking_id` bigint(20) DEFAULT NULL,
  `search_id` varchar(64) DEFAULT NULL,
  `component_type` varchar(50) DEFAULT NULL,
  `component_id` bigint(20) DEFAULT NULL,
  `deal_id` varchar(100) DEFAULT NULL,
  `rate_key` varchar(100) DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  `action` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `request_payload` longtext DEFAULT NULL,
  `response_payload` longtext DEFAULT NULL,
  `status` varchar(20) DEFAULT 'success',
  `error_message` text DEFAULT NULL,
  `created_by` bigint(20) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`log_id`),
  KEY `idx_booking_logs_search_id` (`search_id`),
  KEY `idx_booking_logs_booking_id` (`booking_id`),
  KEY `idx_booking_logs_action` (`action`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `booking_pax` (
  `pax_id` int(11) NOT NULL AUTO_INCREMENT,
  `booking_id` bigint(20) DEFAULT NULL,
  `title` varchar(20) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `type` enum('adult','child','infant') DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `mobile` varchar(50) DEFAULT NULL,
  `address` varchar(500) DEFAULT NULL,
  `nationality` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`pax_id`),
  KEY `booking_id` (`booking_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `booking_price_summary` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `booking_id` bigint(20) DEFAULT NULL,
  `total_base` decimal(12,2) DEFAULT NULL,
  `total_tax` decimal(12,2) DEFAULT NULL,
  `total_markup` decimal(12,2) DEFAULT NULL,
  `total_commission` decimal(12,2) DEFAULT NULL,
  `grand_total` decimal(12,2) DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `booking_id` (`booking_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `booking_search` (
  `search_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `booking_id` bigint(20) DEFAULT NULL,
  `component_type` varchar(50) DEFAULT NULL,
  `component_id` int(11) DEFAULT NULL,
  `from_location_id` bigint(20) DEFAULT NULL,
  `to_location_id` bigint(20) DEFAULT NULL,
  `travel_from` date DEFAULT NULL,
  `travel_to` date DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`search_id`),
  KEY `booking_id` (`booking_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `cancellation_policies` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `component_id` bigint(20) unsigned NOT NULL,
  `fee_type` varchar(50) NOT NULL,
  `fee_period` varchar(50) NOT NULL,
  `days` int(11) NOT NULL,
  `occurrence` varchar(20) DEFAULT 'All',
  `amount` decimal(10,2) DEFAULT 0.00,
  `child` decimal(10,2) DEFAULT 0.00,
  `infant` decimal(10,2) DEFAULT 0.00,
  `min_duration` int(11) DEFAULT 0,
  `max_duration` int(11) DEFAULT 0,
  `min_fee` decimal(10,2) DEFAULT 0.00,
  `max_fee` decimal(10,2) DEFAULT 0.00,
  `max_gross` decimal(10,2) DEFAULT 0.00,
  `currency_code` varchar(10) NOT NULL,
  `description` text DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_component_id` (`component_id`),
  KEY `idx_fee_period` (`fee_period`),
  KEY `idx_days` (`days`),
  KEY `idx_status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `category` (
  `category_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) DEFAULT NULL,
  `category_description` longtext DEFAULT NULL,
  `category_icon` varchar(100) DEFAULT NULL,
  `category_icon_format` varchar(50) DEFAULT NULL,
  `active` int(11) NOT NULL DEFAULT 1 COMMENT 'active category 1 deleted category 0',
  `created_at` varchar(20) NOT NULL,
  `updated_at` varchar(20) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `commission` (
  `commission_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int(11) DEFAULT NULL COMMENT 'foreign key',
  `category_id` int(11) DEFAULT NULL COMMENT 'foreign key',
  `effective_date` varchar(20) DEFAULT NULL,
  `expiry_date` varchar(20) DEFAULT NULL,
  `markup` varchar(15) DEFAULT NULL,
  `markup_unit` varchar(20) DEFAULT NULL COMMENT 'percentage or value',
  `commission_tax_basis` enum('BEFORE_TAX','AFTER_TAX') NOT NULL DEFAULT 'AFTER_TAX',
  `round` varchar(10) DEFAULT NULL COMMENT 'up or down',
  `hh_markup` varchar(15) NOT NULL,
  `hh_markup_unit` varchar(20) NOT NULL,
  `hh_round` varchar(10) NOT NULL,
  `active` int(11) NOT NULL DEFAULT 1 COMMENT 'active commission 1 deleted commission 0',
  `created_at` varchar(20) NOT NULL,
  `updated_at` varchar(20) NOT NULL,
  PRIMARY KEY (`commission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `component` (
  `component_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `component_name` varchar(255) NOT NULL,
  `component_type` varchar(255) DEFAULT NULL,
  `origin` varchar(255) DEFAULT NULL,
  `destination` varchar(255) DEFAULT NULL,
  `journey` varchar(255) DEFAULT NULL,
  `via` varchar(255) DEFAULT NULL,
  `parent` int(11) DEFAULT NULL COMMENT 'component parent',
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `fax` varchar(255) NOT NULL,
  `latitude` varchar(255) NOT NULL,
  `longitude` varchar(255) NOT NULL,
  `root_parent` int(11) DEFAULT NULL COMMENT 'component Root parent',
  `category_id` int(11) DEFAULT NULL COMMENT 'foreign key',
  `group_id` bigint(20) NOT NULL,
  `organisation_id` int(11) DEFAULT NULL COMMENT 'foreign key',
  `status` varchar(255) DEFAULT NULL,
  `transmit` varchar(255) DEFAULT NULL,
  `tax` varchar(100) DEFAULT NULL,
  `rating` varchar(255) DEFAULT NULL,
  `no_of_rooms_available` int(10) unsigned DEFAULT NULL,
  `own_times` text DEFAULT NULL COMMENT 'json',
  `bookable` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `commissionable` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `web` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `generic` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `passport_required` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `partial_week` text DEFAULT NULL COMMENT 'json',
  `component_links` text DEFAULT NULL COMMENT 'json',
  `own_pickups` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `own_pickups_location` text DEFAULT NULL COMMENT 'json',
  `own_dropoffs` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `own_dropoffs_location` text DEFAULT NULL COMMENT 'json',
  `amenities_id` varchar(500) DEFAULT NULL,
  `component_connect` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'json' CHECK (json_valid(`component_connect`)),
  `lastmodified` varchar(100) NOT NULL,
  PRIMARY KEY (`component_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `component_allocation` (
  `allocation_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `component_id` int(11) NOT NULL COMMENT 'foreign key',
  `room_id` int(11) NOT NULL COMMENT 'foreign key',
  `allocation_date` date NOT NULL,
  `allocation_status` enum('AVAILABLE','REQUEST','NOT_AVAILABLE','TEMPORARY_OPEN') NOT NULL,
  `href` varchar(255) DEFAULT NULL,
  `updated_at` varchar(20) NOT NULL,
  PRIMARY KEY (`allocation_id`),
  UNIQUE KEY `uniq_component_room_date` (`component_id`,`room_id`,`allocation_date`),
  KEY `idx_component_room_date` (`component_id`,`room_id`,`allocation_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `component_history` (
  `history_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `_module` varchar(45) NOT NULL,
  `_time` varchar(15) NOT NULL,
  `ip` varchar(15) NOT NULL,
  `user_id` varchar(150) NOT NULL,
  `_table` varchar(25) NOT NULL,
  `data_fields` text NOT NULL,
  `field_id` mediumint(8) unsigned NOT NULL,
  `status` text NOT NULL,
  PRIMARY KEY (`history_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `component_media` (
  `media_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `component_id` int(11) NOT NULL COMMENT 'foreign key',
  `has_own_image` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `media_name` varchar(250) DEFAULT NULL,
  `file_name` varchar(250) DEFAULT NULL,
  `primary_image` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `status` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `format` varchar(100) DEFAULT NULL,
  `width` varchar(100) DEFAULT NULL,
  `height` varchar(100) DEFAULT NULL,
  `preserve_aspect_ratio` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `thumbnail_media_name` varchar(150) DEFAULT NULL,
  `thumbnail_file_name` varchar(150) DEFAULT NULL,
  `thumbnail_file_size` varchar(150) DEFAULT NULL,
  `thumbnail_format` varchar(150) DEFAULT NULL,
  `thumbnail_width` varchar(150) DEFAULT NULL,
  `thumbnail_height` varchar(150) DEFAULT NULL,
  `thumbnail_preserve_aspect_ratio` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `media_description` varchar(250) DEFAULT NULL,
  `web` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `default_image` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `has_own_media` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `media_type` varchar(250) DEFAULT NULL,
  `media_url` varchar(250) DEFAULT NULL,
  `media_types` varchar(250) DEFAULT NULL,
  `media_web` varchar(250) DEFAULT NULL,
  `media_company` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `component_notes` (
  `note_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `component_id` int(11) NOT NULL COMMENT 'foreign key',
  `note_subject` varchar(150) DEFAULT NULL,
  `note_text` text DEFAULT NULL,
  `note_notification` varchar(255) DEFAULT NULL,
  `updated_at` varchar(20) NOT NULL,
  PRIMARY KEY (`note_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `component_rule` (
  `component_rule_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `component_id` int(11) DEFAULT NULL COMMENT 'foreign key',
  `has_own_rules` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `max_pax` int(11) DEFAULT NULL,
  `min_pax` int(11) DEFAULT NULL,
  `max_adults` int(11) DEFAULT NULL,
  `min_adults` int(11) DEFAULT NULL,
  `max_children` int(11) DEFAULT NULL,
  `max_share` int(11) DEFAULT NULL,
  `children_allowed` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `share_stranger` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `child_only` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `child_share` int(11) DEFAULT NULL,
  `infants_in_min_max_pax` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `child_free` int(11) DEFAULT NULL,
  `infant_under` int(11) DEFAULT NULL,
  `child_under` int(11) DEFAULT NULL,
  `teen_under` int(11) DEFAULT NULL,
  `senior_age` int(11) DEFAULT NULL,
  `family_adults` int(11) DEFAULT NULL,
  `family_children` int(11) DEFAULT NULL,
  `rollaway_total` int(11) DEFAULT NULL,
  `rollaway_adults` int(11) DEFAULT NULL,
  `rollaway_teens` int(11) DEFAULT NULL,
  `rollaway_children` int(11) DEFAULT NULL,
  `rollaway_cots` int(11) DEFAULT NULL,
  `cots_in_total` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `child_existing_bedding` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `min_dur` int(11) DEFAULT NULL,
  `min_dur_unit` varchar(255) DEFAULT NULL,
  `max_dur` int(11) DEFAULT NULL,
  `max_dur_unit` varchar(255) DEFAULT NULL,
  `min_age` int(11) DEFAULT NULL,
  `max_age` int(11) DEFAULT NULL,
  `all_pax` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `groups_minimum_pax` int(11) DEFAULT NULL,
  `group_minimum_condition` varchar(255) DEFAULT NULL,
  `group_minimum_rooms` int(11) DEFAULT NULL,
  `adavance_purcharse_from_min` int(11) DEFAULT NULL,
  `adavance_purcharse_from_min_unit` varchar(255) DEFAULT NULL,
  `adavance_purcharse_from_max` int(11) DEFAULT NULL,
  `adavance_purcharse_from_max_unit` varchar(255) DEFAULT NULL,
  `travel_completed_by` varchar(255) DEFAULT NULL,
  `second_room_max_pax` int(11) DEFAULT NULL,
  `second_room_max_adults` int(11) DEFAULT NULL,
  `second_room_max_children` int(11) DEFAULT NULL,
  `second_room_rollaway_cots` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `second_room_child_share` int(11) DEFAULT NULL,
  `second_room_dealcode` varchar(255) DEFAULT NULL,
  `bedding` text DEFAULT NULL,
  `single_in_twin` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `child_group_share` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  PRIMARY KEY (`component_rule_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `component_types` (
  `component_type_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` varchar(50) DEFAULT NULL COMMENT 'JSON parentID',
  `code` varchar(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `caption` varchar(100) DEFAULT NULL,
  `root_type` varchar(100) NOT NULL,
  `allocation_caption` varchar(100) DEFAULT NULL,
  `class_caption` varchar(100) DEFAULT NULL,
  `product_name_caption` varchar(100) DEFAULT NULL,
  `origin_caption` varchar(100) DEFAULT NULL,
  `destination_caption` varchar(100) DEFAULT NULL,
  `via_locations_caption` varchar(100) DEFAULT NULL,
  `date_in_caption` varchar(100) DEFAULT NULL,
  `date_out_caption` varchar(100) DEFAULT NULL,
  `time_in_caption` varchar(100) DEFAULT NULL,
  `time_out_caption` varchar(100) DEFAULT NULL,
  `days_caption` varchar(50) DEFAULT NULL,
  `duration_caption` varchar(100) DEFAULT NULL,
  `default_duration` int(11) DEFAULT 0,
  `minimum_duration` int(11) DEFAULT 0,
  `minimum_adults` int(11) DEFAULT 0,
  `is_air` tinyint(1) DEFAULT 0,
  `is_all_package_durations` tinyint(1) DEFAULT 0,
  `is_class_minimum_range` tinyint(1) DEFAULT 0,
  `is_concession_pax` tinyint(1) DEFAULT 0,
  `is_daily_duration` tinyint(1) DEFAULT 0,
  `is_date_in` tinyint(1) DEFAULT 0,
  `is_date_out` tinyint(1) DEFAULT 0,
  `is_time_in` tinyint(1) DEFAULT 0,
  `is_time_out` tinyint(1) DEFAULT 0,
  `is_origin` tinyint(1) DEFAULT 0,
  `is_destination` tinyint(1) DEFAULT 0,
  `is_via_locations` tinyint(1) DEFAULT 0,
  `is_iata_locations` tinyint(1) DEFAULT 0,
  `is_locations_optional` tinyint(1) DEFAULT 0,
  `is_duration` tinyint(1) DEFAULT 0,
  `is_duration_extra_day` tinyint(1) DEFAULT 0,
  `is_flexible_dates` tinyint(1) DEFAULT 0,
  `is_rooming` tinyint(1) DEFAULT 0,
  `is_units` tinyint(1) DEFAULT 0,
  `active` tinyint(1) DEFAULT 1,
  `created_at` int(10) unsigned NOT NULL,
  `updated_at` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`component_type_id`),
  UNIQUE KEY `code` (`code`),
  UNIQUE KEY `component_types_code` (`code`),
  KEY `component_types_root_type` (`root_type`),
  KEY `component_types_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `component_type_airlines` (
  `component_type_airline_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `component_type_id` bigint(20) unsigned NOT NULL,
  `airline_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`component_type_airline_id`),
  UNIQUE KEY `uq_component_type_airline` (`component_type_id`,`airline_id`),
  KEY `fk_cta_airline` (`airline_id`),
  CONSTRAINT `fk_cta_airline` FOREIGN KEY (`airline_id`) REFERENCES `airlines` (`airline_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cta_component_type` FOREIGN KEY (`component_type_id`) REFERENCES `component_types` (`component_type_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `component_type_journey_types` (
  `component_type_journey_type_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `component_type_id` bigint(20) unsigned NOT NULL,
  `name` varchar(50) NOT NULL,
  `is_default` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`component_type_journey_type_id`),
  KEY `ctjt_component_type_id` (`component_type_id`),
  CONSTRAINT `fk_ctjt_component_type` FOREIGN KEY (`component_type_id`) REFERENCES `component_types` (`component_type_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `content` (
  `content_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `component_id` int(11) NOT NULL COMMENT 'foreign key',
  `dates` text DEFAULT NULL,
  `content_type` varchar(250) DEFAULT NULL,
  `profile` varchar(250) DEFAULT NULL,
  `status` varchar(250) DEFAULT NULL,
  `content_description` text DEFAULT NULL,
  `amendment_policy` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `arrival` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `bonus` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `brief_description` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `brochure` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `cancellation` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `cancellation_policy` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `capacity` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `checkin_checkout` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `child_policy` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `children` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `concession` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `confirmation` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `departure` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `deposit_invoice` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `description` varchar(5000) DEFAULT NULL,
  `discount` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `discount_policy` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `extra` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `featured` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `fee_policy` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `group` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `group_range` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `honeymoon` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `invoice` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `itinerary` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `manifest` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `max_duration` int(11) DEFAULT NULL,
  `meta_keyword` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `min_duration` int(11) DEFAULT NULL,
  `non_commissionable` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `open_jaw` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `other` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `component_itinerary` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `quote` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `receipt` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `segment` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `special_deal` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `organisation` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `surcharge` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `tba` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `terms_and_conditions` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `upsell` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `upsell_alternative` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `upsell_extra` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `upsell_siblings` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `voucher` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `web` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `deal_id` varchar(25) DEFAULT NULL,
  `lastmodified` varchar(255) NOT NULL,
  PRIMARY KEY (`content_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `criteria` (
  `criteria_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(500) NOT NULL,
  `date_from` varchar(500) DEFAULT NULL,
  `date_to` varchar(500) DEFAULT NULL,
  `criteria_type` varchar(500) DEFAULT NULL,
  `discount_type` varchar(500) DEFAULT NULL,
  `min_length_stay` varchar(100) DEFAULT NULL,
  `max_length_stay` varchar(100) DEFAULT NULL,
  `discount_value` varchar(100) DEFAULT NULL,
  `deal_id` int(11) DEFAULT NULL,
  `max_pax` varchar(20) DEFAULT NULL,
  `max_adults` varchar(20) DEFAULT NULL,
  `max_children` varchar(20) DEFAULT NULL,
  `min_pax` varchar(20) DEFAULT NULL,
  `min_adults` varchar(20) DEFAULT NULL,
  `is_children_allowed` varchar(20) DEFAULT NULL,
  `child_age` varchar(50) DEFAULT NULL,
  `child_free` varchar(50) DEFAULT NULL,
  `child_share` varchar(50) DEFAULT NULL,
  `infant_age` varchar(50) DEFAULT NULL,
  `working_days` varchar(500) DEFAULT NULL,
  `checkintime` varchar(100) DEFAULT NULL,
  `checkouttime` varchar(100) DEFAULT NULL,
  `earlycheckin` varchar(200) DEFAULT NULL,
  `earlycheckout` varchar(200) DEFAULT NULL,
  `lastmodified` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`criteria_id`),
  KEY `deal_id` (`deal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `currency` (
  `currency_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `currency_from` varchar(5) DEFAULT NULL,
  `currency_to` varchar(5) DEFAULT NULL,
  `exchange_rate` varchar(15) DEFAULT NULL,
  `sell_exchangerate` varchar(15) NOT NULL,
  `group_id` int(11) DEFAULT NULL COMMENT 'foreign key',
  `active` int(11) NOT NULL DEFAULT 1 COMMENT 'active currency 1 deleted currency 0',
  `created_at` varchar(20) NOT NULL,
  `updated_at` varchar(20) NOT NULL,
  PRIMARY KEY (`currency_id`),
  UNIQUE KEY `uq_currency_pair_group` (`currency_from`,`currency_to`,`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `deals` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` varchar(30) NOT NULL,
  `web_display_text` varchar(500) NOT NULL,
  `sys_display_text` varchar(500) DEFAULT NULL,
  `deal` varchar(100) DEFAULT NULL,
  `parent` int(11) NOT NULL DEFAULT 0,
  `travel_start_date` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'json',
  `travel_end_date` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'json',
  `block_start_date` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'json',
  `block_end_date` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'json',
  `surcharge_start_date` varchar(500) NOT NULL,
  `surcharge_end_date` varchar(500) NOT NULL,
  `pax-range` varchar(250) DEFAULT NULL,
  `tax_type` varchar(250) NOT NULL,
  `taxes` varchar(500) NOT NULL,
  `buy_currencies` varchar(250) DEFAULT NULL,
  `sell_currencies` varchar(250) DEFAULT NULL,
  `hot_deal` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `combinable` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `high_season_rate_apply` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `min_duration_stay` varchar(10) DEFAULT NULL,
  `max_duration_stay` varchar(10) DEFAULT NULL,
  `subcomponent_type` varchar(50) DEFAULT NULL,
  `subcomponent_id` int(11) DEFAULT NULL,
  `component_id` int(11) DEFAULT NULL,
  `season_combinable` tinyint(1) DEFAULT 0 COMMENT '1 = can combine seasons',
  `combine_with_deal_ids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Allowed deal IDs',
  `pricing_strategy` enum('INDIVIDUAL','LOWEST','HIGHEST') DEFAULT 'INDIVIDUAL',
  `updated_at` varchar(200) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `component_id` (`component_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `groups` (
  `group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_name` varchar(150) NOT NULL,
  `group_description` text DEFAULT NULL,
  `group_icon` varchar(100) DEFAULT NULL,
  `group_icon_format` varchar(50) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=active,0=deleted',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`group_id`),
  UNIQUE KEY `uq_groups_group_name` (`group_name`),
  KEY `idx_groups_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `hhcustomcurrency` (
  `CurrencyNumber` int(11) NOT NULL AUTO_INCREMENT,
  `CurrencyOf` varchar(5) DEFAULT NULL,
  `CurrencyTo` varchar(5) DEFAULT NULL,
  `ExchangeRate` double DEFAULT NULL,
  PRIMARY KEY (`CurrencyNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;


CREATE TABLE `history` (
  `history_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `_module` varchar(45) NOT NULL,
  `_time` varchar(15) NOT NULL,
  `ip` varchar(15) NOT NULL,
  `user_id` mediumint(8) unsigned NOT NULL,
  `_table` varchar(25) NOT NULL,
  `data_fields` text NOT NULL,
  `field_id` mediumint(8) unsigned NOT NULL,
  `status` text NOT NULL,
  PRIMARY KEY (`history_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `location` (
  `location_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `parent_node` int(11) DEFAULT NULL COMMENT 'root node 0',
  `name` varchar(150) DEFAULT NULL,
  `type` varchar(150) DEFAULT NULL,
  `code` varchar(150) DEFAULT NULL,
  `gmt` varchar(100) DEFAULT NULL,
  `max_transmit` varchar(50) DEFAULT NULL,
  `surcharge` varchar(100) DEFAULT NULL,
  `search` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `tax_boundary` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `manifest` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `bkg_destination` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `sales_region` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `brochure_section` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `web` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `location_icon` varchar(50) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `phone` text DEFAULT NULL,
  `fax` text DEFAULT NULL,
  `mobile` text DEFAULT NULL,
  `email` text DEFAULT NULL,
  `text` text DEFAULT NULL,
  `all_weeks` varchar(10) DEFAULT NULL COMMENT '24 hours 7 days per week',
  `week_days` text DEFAULT NULL,
  `organisations` longtext DEFAULT NULL,
  `calendar_date` varchar(25) DEFAULT NULL,
  `calendar_description` text DEFAULT NULL,
  `public_holiday` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `closed` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `longitude` varchar(50) DEFAULT NULL,
  `latitude` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `location_attributes` (
  `attributes_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `location_id` int(11) DEFAULT NULL COMMENT 'foreign key',
  `name` varchar(255) DEFAULT NULL,
  `value` varchar(255) DEFAULT NULL,
  `updated_at` varchar(20) NOT NULL,
  PRIMARY KEY (`attributes_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `location_calendar` (
  `calendar_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `location_id` int(11) DEFAULT NULL COMMENT 'foreign key',
  `date` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `public_holiday` varchar(255) DEFAULT NULL,
  `closed` varchar(255) DEFAULT NULL,
  `updated_at` varchar(20) NOT NULL,
  PRIMARY KEY (`calendar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `location_coordinates` (
  `coordinates_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `location_id` int(11) DEFAULT NULL COMMENT 'foreign key',
  `distance` varchar(255) DEFAULT NULL,
  `air` varchar(255) DEFAULT NULL,
  `land` varchar(255) DEFAULT NULL,
  `sea` varchar(255) DEFAULT NULL,
  `updated_at` varchar(20) NOT NULL,
  PRIMARY KEY (`coordinates_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `location_host` (
  `host_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `location_id` int(11) DEFAULT NULL COMMENT 'foreign key',
  `host` text DEFAULT NULL,
  `id` varchar(255) DEFAULT NULL,
  `updated_at` varchar(20) NOT NULL,
  PRIMARY KEY (`host_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `location_notes` (
  `note_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `location_id` int(11) DEFAULT NULL COMMENT 'foreign key location id',
  `note_subject` varchar(150) DEFAULT NULL,
  `note_text` text DEFAULT NULL,
  `note_notification` varchar(255) DEFAULT NULL,
  `updated_at` varchar(20) NOT NULL,
  PRIMARY KEY (`note_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `organisation_address` (
  `address_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `address_type` varchar(20) DEFAULT NULL COMMENT 'street or postal',
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(150) DEFAULT NULL,
  `state` varchar(150) DEFAULT NULL,
  `zip` varchar(255) DEFAULT NULL,
  `country` varchar(150) DEFAULT NULL,
  `organisation_no` int(11) NOT NULL COMMENT 'foreign key',
  `location_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `organisation_contacts` (
  `contacts_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `organisation_no` int(11) DEFAULT NULL COMMENT 'foreign key',
  `contacts_type` varchar(50) DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL COMMENT 'active, awaiting-acceptance, inactive',
  `categories` varchar(10) DEFAULT NULL,
  `preferred` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `title` varchar(30) DEFAULT NULL COMMENT 'Mr,Ms',
  `gender` varchar(30) DEFAULT NULL COMMENT 'Male/Female',
  `first_name` varchar(100) DEFAULT NULL,
  `middle_name` varchar(100) DEFAULT NULL,
  `surname` varchar(100) DEFAULT NULL,
  `salutation` varchar(100) DEFAULT NULL,
  `aka_alias` varchar(100) DEFAULT NULL,
  `dob` varchar(50) DEFAULT NULL,
  `contact_icon` varchar(100) DEFAULT NULL,
  `street_address` varchar(255) DEFAULT NULL,
  `street_city` varchar(150) DEFAULT NULL,
  `street_state` varchar(150) DEFAULT NULL,
  `street_zip` varchar(20) DEFAULT NULL,
  `street_country` varchar(150) DEFAULT NULL,
  `postal_address` varchar(255) DEFAULT NULL,
  `postal_city` varchar(150) DEFAULT NULL,
  `postal_state` varchar(150) DEFAULT NULL,
  `postal_zip` varchar(20) DEFAULT NULL,
  `postal_country` varchar(150) DEFAULT NULL,
  `webpage` text DEFAULT NULL,
  `email` text DEFAULT NULL,
  `phone` text DEFAULT NULL,
  `fax` text DEFAULT NULL,
  `mobile` text DEFAULT NULL,
  `other` text DEFAULT NULL,
  `active` int(11) NOT NULL DEFAULT 1 COMMENT 'active 1, deleted 0',
  PRIMARY KEY (`contacts_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `organisation_notes` (
  `note_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `note_subject` varchar(150) DEFAULT NULL,
  `note_text` text DEFAULT NULL,
  `note_notication` varchar(255) DEFAULT NULL,
  `organisation_no` int(11) NOT NULL COMMENT 'foreign key',
  `user_id` mediumint(8) unsigned NOT NULL COMMENT 'foreign key',
  `updated_at` varchar(20) NOT NULL,
  PRIMARY KEY (`note_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `organisation_profile` (
  `organisation_no` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `organisation_name` varchar(255) NOT NULL,
  `treading_as` varchar(255) DEFAULT NULL,
  `short_name` varchar(255) DEFAULT NULL,
  `company_no` varchar(255) DEFAULT NULL,
  `tax_no` varchar(255) DEFAULT NULL,
  `organisation_type` varchar(150) DEFAULT NULL COMMENT 'organisation or person',
  `webpage` text DEFAULT NULL,
  `email` text DEFAULT NULL,
  `phone` text DEFAULT NULL,
  `fax` text DEFAULT NULL,
  `mobile` text DEFAULT NULL,
  `other` text DEFAULT NULL,
  `categories` varchar(100) DEFAULT NULL,
  `organisation_image` varchar(100) DEFAULT NULL,
  `status` varchar(100) DEFAULT NULL COMMENT 'active, awaiting-acceptance, inactive',
  `currencies` varchar(100) DEFAULT NULL,
  `default_currency` varchar(100) DEFAULT NULL,
  `tax_code` varchar(100) DEFAULT NULL,
  `tax_type` varchar(100) DEFAULT NULL,
  `financial` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `invoice_per_component` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `invoice_on_documentation` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `reference_required_on_confirmation` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `bsp` varchar(100) DEFAULT NULL,
  `locale` varchar(100) DEFAULT NULL,
  `payment_method` varchar(100) DEFAULT NULL,
  `bank_name` varchar(150) DEFAULT NULL,
  `branch_or_bsb` varchar(150) DEFAULT NULL,
  `payee` varchar(100) DEFAULT NULL,
  `account_no` varchar(100) DEFAULT NULL,
  `ageing_calendar` varchar(50) DEFAULT NULL,
  `payment_terms` varchar(100) DEFAULT NULL,
  `credit_limit` varchar(50) DEFAULT NULL,
  `own_terms` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `deposit_of` varchar(255) DEFAULT NULL,
  `balance` varchar(255) DEFAULT NULL,
  `no_later_than` varchar(255) DEFAULT NULL,
  `pay_direct` varchar(10) DEFAULT NULL COMMENT 'yes/no',
  `pickup_dropoff_location` varchar(150) DEFAULT NULL,
  `locations` longtext DEFAULT NULL,
  PRIMARY KEY (`organisation_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `permissions` (
  `permission_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `permission_code` varchar(100) NOT NULL,
  `permission_description` varchar(255) DEFAULT NULL,
  `created_at` varchar(20) NOT NULL,
  PRIMARY KEY (`permission_id`),
  UNIQUE KEY `permission_code` (`permission_code`),
  UNIQUE KEY `uq_permission_code` (`permission_code`),
  KEY `idx_permission_code` (`permission_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `rates` (
  `rates_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rates` varchar(5000) NOT NULL,
  `rate_band` enum('NETT','GROSS','RACK') DEFAULT NULL,
  `rate_per` varchar(5000) DEFAULT NULL,
  `deal_id` int(11) DEFAULT NULL,
  `rate_type` varchar(100) DEFAULT NULL,
  `tax_type` enum('INCLUSIVE','EXCLUSIVE') NOT NULL DEFAULT 'INCLUSIVE',
  `component_id` int(11) DEFAULT NULL,
  `subcomponent_type` varchar(50) DEFAULT NULL,
  `subcomponent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`rates_id`),
  KEY `component_id` (`component_id`),
  KEY `deal_id` (`deal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `tax` (
  `tax_id` int(11) NOT NULL AUTO_INCREMENT,
  `tax_name` varchar(250) NOT NULL,
  `tax_code` varchar(100) NOT NULL,
  `tax_location` int(11) NOT NULL,
  `tax_authority` varchar(250) NOT NULL,
  `tax_type` varchar(250) NOT NULL,
  `booked_from` varchar(100) NOT NULL,
  `usage_date` varchar(100) NOT NULL,
  `tax_percentage` decimal(5,2) DEFAULT NULL,
  `tax_amount` int(11) DEFAULT NULL,
  `tax_per` varchar(100) DEFAULT NULL,
  `lastmodified` int(11) DEFAULT NULL,
  PRIMARY KEY (`tax_id`),
  UNIQUE KEY `tax_code` (`tax_code`),
  UNIQUE KEY `uniq_tax_code` (`tax_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `users` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `users_group_id` int(10) unsigned DEFAULT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `username` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `profile_icon` varchar(50) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=active,0=deleted',
  `verification_token` varchar(255) DEFAULT NULL,
  `created_at` varchar(20) NOT NULL,
  `updated_at` varchar(20) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `uq_users_username` (`username`),
  UNIQUE KEY `uq_users_email` (`email`),
  KEY `idx_users_group` (`users_group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `users_group` (
  `users_group_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `group_name` varchar(100) NOT NULL,
  `group_description` text DEFAULT NULL,
  `active` tinyint(1) DEFAULT 1,
  `created_at` bigint(20) DEFAULT NULL,
  `updated_at` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`users_group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `users_group_permissions` (
  `users_group_id` bigint(20) unsigned NOT NULL,
  `permission_id` int(10) unsigned NOT NULL,
  `created_at` varchar(20) NOT NULL,
  PRIMARY KEY (`users_group_id`,`permission_id`),
  KEY `permission_id` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


-- 2026-05-11 07:14:54 UTC