--
-- Host: localhost    Database: cruiseandt_crm
-- ------------------------------------------------------


--
-- Table structure for table `activity_log`
--

CREATE TABLE `activity_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `type` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `activity` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `ip_address` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `details` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `logged_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `component` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `data` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=55799 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `agents`
--

CREATE TABLE `agents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_short_name` varchar(255) DEFAULT NULL,
  `company_full_name` text DEFAULT NULL,
  `company_tax` varchar(20) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `www` varchar(255) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `house_number` varchar(100) DEFAULT NULL,
  `flat_number` varchar(100) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `zip_code` varchar(20) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=utf8mb4;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.agents__ai AFTER INSERT ON `cruiseandt_crm`.agents FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.agents_history SELECT 'insert', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.agents AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.agents__au AFTER UPDATE ON `cruiseandt_crm`.agents FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.agents_history SELECT 'update', NULL, NOW(), d.*

    FROM `cruiseandt_crm`.agents AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.agents__bd BEFORE DELETE ON `cruiseandt_crm`.agents FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.agents_history SELECT 'delete', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.agents AS d WHERE d.id = OLD.id */;;
DELIMITER ;

--
-- Table structure for table `agents_history`
--

CREATE TABLE `agents_history` (
  `action` varchar(8) DEFAULT 'insert',
  `revision` int(6) NOT NULL AUTO_INCREMENT,
  `dt_datetime` datetime NOT NULL DEFAULT current_timestamp(),
  `id` int(11) NOT NULL,
  `company_short_name` varchar(255) DEFAULT NULL,
  `company_full_name` text DEFAULT NULL,
  `company_tax` varchar(20) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `www` varchar(255) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `house_number` varchar(100) DEFAULT NULL,
  `flat_number` varchar(100) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `zip_code` varchar(20) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`,`revision`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `agents_registration`
--

CREATE TABLE `agents_registration` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `office_name` varchar(255) DEFAULT NULL,
  `office_www` varchar(255) DEFAULT NULL,
  `office_street` varchar(255) DEFAULT NULL,
  `office_house_number` varchar(10) DEFAULT NULL,
  `office_flat_number` varchar(10) DEFAULT NULL,
  `office_zip_code` varchar(10) DEFAULT NULL,
  `office_city` varchar(100) DEFAULT NULL,
  `office_country` varchar(100) DEFAULT NULL,
  `office_open` varchar(100) DEFAULT NULL,
  `office_mobile` varchar(20) DEFAULT NULL,
  `office_phone` varchar(20) DEFAULT NULL,
  `office_email` varchar(255) DEFAULT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  `company_owner` varchar(255) DEFAULT NULL,
  `company_street` varchar(255) DEFAULT NULL,
  `company_house_number` varchar(10) DEFAULT NULL,
  `company_flat_number` varchar(10) DEFAULT NULL,
  `company_zip_code` varchar(10) DEFAULT NULL,
  `company_city` varchar(100) DEFAULT NULL,
  `company_country` varchar(100) DEFAULT NULL,
  `company_license` varchar(100) DEFAULT NULL,
  `company_nip` varchar(20) DEFAULT NULL,
  `company_regon` varchar(20) DEFAULT NULL,
  `company_krs` varchar(20) DEFAULT NULL,
  `contact_first_name` varchar(100) DEFAULT NULL,
  `contact_last_name` varchar(100) DEFAULT NULL,
  `contact_position` varchar(100) DEFAULT NULL,
  `contact_mobile` varchar(20) DEFAULT NULL,
  `contact_phone` varchar(20) DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `created_ay` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `brockers`
--

CREATE TABLE `brockers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shipowner_id` int(11) NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `cabin_capacities`
--

CREATE TABLE `cabin_capacities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `capacity` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `cabin_capacities_l18n`
--

CREATE TABLE `cabin_capacities_l18n` (
  `cabin_capacity_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`cabin_capacity_id`,`language_id`),
  CONSTRAINT `cabin_capacities_l18n_ibfk_1` FOREIGN KEY (`cabin_capacity_id`) REFERENCES `cabin_capacities` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `cabin_types`
--

CREATE TABLE `cabin_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `cabin_types_l18n`
--

CREATE TABLE `cabin_types_l18n` (
  `cabin_type_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `short_name` varchar(50) DEFAULT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`cabin_type_id`,`language_id`),
  CONSTRAINT `cabin_types_l18n_ibfk_1` FOREIGN KEY (`cabin_type_id`) REFERENCES `cabin_types` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `calendar`
--

CREATE TABLE `calendar` (
  `date` date NOT NULL,
  `day_type` int(11) NOT NULL,
  `notes` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `client_documents`
--

CREATE TABLE `client_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `document_type_id` int(11) NOT NULL,
  `document_number` varchar(100) DEFAULT NULL,
  `document_valid_from` date DEFAULT NULL,
  `document_valid_to` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=619 DEFAULT CHARSET=utf8mb4;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.client_documents__ai AFTER INSERT ON `cruiseandt_crm`.client_documents FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.client_documents_history SELECT 'insert', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.client_documents AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.client_documents__au AFTER UPDATE ON `cruiseandt_crm`.client_documents FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.client_documents_history SELECT 'update', NULL, NOW(), d.*

    FROM `cruiseandt_crm`.client_documents AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.client_documents__bd BEFORE DELETE ON `cruiseandt_crm`.client_documents FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.client_documents_history SELECT 'delete', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.client_documents AS d WHERE d.id = OLD.id */;;
DELIMITER ;

--
-- Table structure for table `client_documents_history`
--

CREATE TABLE `client_documents_history` (
  `action` varchar(8) DEFAULT 'insert',
  `revision` int(6) NOT NULL AUTO_INCREMENT,
  `dt_datetime` datetime NOT NULL DEFAULT current_timestamp(),
  `id` int(11) NOT NULL,
  `client_id` int(11) NOT NULL,
  `document_type_id` int(11) NOT NULL,
  `document_number` varchar(100) DEFAULT NULL,
  `document_valid_from` date DEFAULT NULL,
  `document_valid_to` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`,`revision`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `clients`
--

CREATE TABLE `clients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_type` tinyint(1) NOT NULL,
  `company_short_name` varchar(255) DEFAULT NULL,
  `company_full_name` text DEFAULT 'NULL',
  `company_tax` varchar(20) DEFAULT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `gender_id` tinyint(4) DEFAULT NULL,
  `citizenship` varchar(255) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `pesel` varchar(11) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `www` varchar(255) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `house_number` varchar(100) DEFAULT NULL,
  `flat_number` varchar(100) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `zip_code` varchar(150) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8133 DEFAULT CHARSET=utf8mb4;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.clients__ai AFTER INSERT ON `cruiseandt_crm`.clients FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.clients_history SELECT 'insert', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.clients AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.clients__au AFTER UPDATE ON `cruiseandt_crm`.clients FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.clients_history SELECT 'update', NULL, NOW(), d.*

    FROM `cruiseandt_crm`.clients AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.clients__bd BEFORE DELETE ON `cruiseandt_crm`.clients FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.clients_history SELECT 'delete', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.clients AS d WHERE d.id = OLD.id */;;
DELIMITER ;

--
-- Table structure for table `clients_history`
--

CREATE TABLE `clients_history` (
  `action` varchar(8) DEFAULT 'insert',
  `revision` int(6) NOT NULL AUTO_INCREMENT,
  `dt_datetime` datetime NOT NULL DEFAULT current_timestamp(),
  `id` int(11) NOT NULL,
  `client_type` tinyint(1) NOT NULL,
  `company_short_name` varchar(255) DEFAULT NULL,
  `company_full_name` text DEFAULT 'NULL',
  `company_tax` varchar(20) DEFAULT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `gender_id` tinyint(4) DEFAULT NULL,
  `citizenship` varchar(255) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `pesel` varchar(11) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `www` varchar(255) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `house_number` varchar(100) DEFAULT NULL,
  `flat_number` varchar(100) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `zip_code` varchar(150) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`,`revision`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

--
-- Temporary view structure for view `clients_main`
--

/*!50001 DROP VIEW IF EXISTS `clients_main`*/;
/*!50001 CREATE VIEW `clients_main` AS SELECT 
 1 AS `id`,
 1 AS `client_type`,
 1 AS `gender_id`,
 1 AS `first_name`,
 1 AS `last_name`,
 1 AS `company_full_name`,
 1 AS `phone`,
 1 AS `email`,
 1 AS `pesel`,
 1 AS `company_tax`,
 1 AS `city`,
 1 AS `country_id`,
 1 AS `country`,
 1 AS `client_name`,
 1 AS `pesel_nip`*/;

--
-- Table structure for table `company_groups`
--

CREATE TABLE `company_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `company_groups_l18n`
--

CREATE TABLE `company_groups_l18n` (
  `company_group_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`company_group_id`,`language_id`),
  CONSTRAINT `company_groups_l18n_ibfk_1` FOREIGN KEY (`company_group_id`) REFERENCES `company_groups` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_advances`
--

CREATE TABLE `contract_advances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `contract_item_id` int(11) DEFAULT NULL,
  `contract_item_type_id` int(11) DEFAULT NULL,
  `price` decimal(11,4) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `term` date DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_contract_advances_contract_item_id` (`contract_item_id`),
  KEY `idx_contract_advances_contract_contract_item` (`contract_id`,`contract_item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10022 DEFAULT CHARSET=utf8mb4;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contract_advances__ai AFTER INSERT ON `cruiseandt_crm`.contract_advances FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contract_advances_history SELECT 'insert', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.contract_advances AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contract_advances__au AFTER UPDATE ON `cruiseandt_crm`.contract_advances FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contract_advances_history SELECT 'update', NULL, NOW(), d.*

    FROM `cruiseandt_crm`.contract_advances AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contract_advances__bd BEFORE DELETE ON `cruiseandt_crm`.contract_advances FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contract_advances_history SELECT 'delete', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.contract_advances AS d WHERE d.id = OLD.id */;;
DELIMITER ;

--
-- Table structure for table `contract_advances_history`
--

CREATE TABLE `contract_advances_history` (
  `action` varchar(8) DEFAULT 'insert',
  `revision` int(6) NOT NULL AUTO_INCREMENT,
  `dt_datetime` datetime NOT NULL DEFAULT current_timestamp(),
  `id` int(11) NOT NULL,
  `contract_id` int(11) NOT NULL,
  `contract_item_id` int(11) DEFAULT NULL,
  `contract_item_type_id` int(11) DEFAULT NULL,
  `price` decimal(11,4) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `term` date DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`,`revision`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_cabins`
--

CREATE TABLE `contract_cabins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `cabin_type_id` int(11) DEFAULT NULL,
  `cabin_capacity_id` int(11) DEFAULT NULL,
  `category` text DEFAULT NULL,
  `number` text DEFAULT NULL,
  `booking_number` text DEFAULT NULL,
  `booking_status` int(11) NOT NULL DEFAULT 0,
  `booking_status_updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `booking_status_updated_by` int(11) NOT NULL DEFAULT 1,
  `description` text DEFAULT NULL,
  `cabin_order` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18953 DEFAULT CHARSET=utf8mb4;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contract_cabins__ai AFTER INSERT ON `cruiseandt_crm`.contract_cabins FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contract_cabins_history SELECT 'insert', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.contract_cabins AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contract_cabins__au AFTER UPDATE ON `cruiseandt_crm`.contract_cabins FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contract_cabins_history SELECT 'update', NULL, NOW(), d.*

    FROM `cruiseandt_crm`.contract_cabins AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contract_cabins__bd BEFORE DELETE ON `cruiseandt_crm`.contract_cabins FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contract_cabins_history SELECT 'delete', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.contract_cabins AS d WHERE d.id = OLD.id */;;
DELIMITER ;

--
-- Table structure for table `contract_cabins_history`
--

CREATE TABLE `contract_cabins_history` (
  `action` varchar(8) DEFAULT 'insert',
  `revision` int(6) NOT NULL AUTO_INCREMENT,
  `dt_datetime` datetime NOT NULL DEFAULT current_timestamp(),
  `id` int(11) NOT NULL,
  `contract_id` int(11) NOT NULL,
  `cabin_type_id` int(11) DEFAULT NULL,
  `cabin_capacity_id` int(11) DEFAULT NULL,
  `category` text DEFAULT NULL,
  `number` text DEFAULT NULL,
  `booking_number` text DEFAULT NULL,
  `booking_status` int(11) NOT NULL DEFAULT 0,
  `booking_status_updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `booking_status_updated_by` int(11) NOT NULL DEFAULT 1,
  `description` text DEFAULT NULL,
  `cabin_order` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`,`revision`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_commissions`
--

CREATE TABLE `contract_commissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `contract_item_type_id` int(11) NOT NULL,
  `commission_base` decimal(11,4) DEFAULT NULL,
  `commission_percentages` decimal(11,4) DEFAULT NULL,
  `commission_value` decimal(11,4) DEFAULT NULL,
  `commission_adjusted_value` decimal(11,4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=987 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_commissions_check`
--

CREATE TABLE `contract_commissions_check` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `checked` tinyint(4) NOT NULL,
  `notice` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_costs`
--

CREATE TABLE `contract_costs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `value` decimal(11,4) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `value_pln` decimal(11,4) DEFAULT NULL,
  `term` date DEFAULT NULL,
  `exchange_rate` decimal(11,4) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_cruise_program`
--

CREATE TABLE `contract_cruise_program` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `point_order` int(11) NOT NULL,
  `travel_day` int(11) NOT NULL,
  `travel_place_name` varchar(255) DEFAULT NULL,
  `country_name` varchar(255) DEFAULT NULL,
  `arrival_time` varchar(16) DEFAULT NULL,
  `departure_time` varchar(16) DEFAULT NULL,
  `point_description` text DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_contract_cruise_program_contract` (`contract_id`),
  KEY `idx_contract_cruise_program_order` (`contract_id`,`point_order`)
) ENGINE=InnoDB AUTO_INCREMENT=116 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_item_types`
--

CREATE TABLE `contract_item_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merge_type` int(11) DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `show_in_contract` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_item_types_l18n`
--

CREATE TABLE `contract_item_types_l18n` (
  `contract_item_type_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `short_name` varchar(100) DEFAULT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`contract_item_type_id`,`language_id`),
  CONSTRAINT `contract_item_types_l18n_ibfk_1` FOREIGN KEY (`contract_item_type_id`) REFERENCES `contract_item_types` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_items`
--

CREATE TABLE `contract_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `contract_item_type_id` int(11) DEFAULT NULL,
  `price` decimal(11,4) DEFAULT NULL,
  `discount` decimal(11,4) DEFAULT NULL,
  `surcharge` decimal(11,4) DEFAULT NULL,
  `nccf_price` decimal(11,4) DEFAULT NULL,
  `commission_base` decimal(11,4) DEFAULT NULL,
  `commission_type` tinyint(4) NOT NULL DEFAULT 0,
  `commission_percentages` decimal(11,4) DEFAULT NULL,
  `commission_value` decimal(11,4) DEFAULT NULL,
  `commission_manual_value` decimal(11,4) DEFAULT NULL,
  `profit` decimal(11,4) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `term` date DEFAULT NULL,
  `description` text DEFAULT NULL,
  `item_order` int(11) DEFAULT NULL,
  `contract_cabin_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_contract_items_contract_cabin` (`contract_id`,`contract_cabin_id`)
) ENGINE=InnoDB AUTO_INCREMENT=47541 DEFAULT CHARSET=utf8mb4;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contract_items__ai AFTER INSERT ON `cruiseandt_crm`.contract_items FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contract_items_history SELECT 'insert', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.contract_items AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contract_items__au AFTER UPDATE ON `cruiseandt_crm`.contract_items FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contract_items_history SELECT 'update', NULL, NOW(), d.*

    FROM `cruiseandt_crm`.contract_items AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contract_items__bd BEFORE DELETE ON `cruiseandt_crm`.contract_items FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contract_items_history SELECT 'delete', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.contract_items AS d WHERE d.id = OLD.id */;;
DELIMITER ;

--
-- Table structure for table `contract_items_history`
--

CREATE TABLE `contract_items_history` (
  `action` varchar(8) DEFAULT 'insert',
  `revision` int(6) NOT NULL AUTO_INCREMENT,
  `dt_datetime` datetime NOT NULL DEFAULT current_timestamp(),
  `id` int(11) NOT NULL,
  `contract_id` int(11) NOT NULL,
  `contract_item_type_id` int(11) DEFAULT NULL,
  `price` decimal(11,4) DEFAULT NULL,
  `discount` decimal(11,4) DEFAULT NULL,
  `surcharge` decimal(11,4) DEFAULT NULL,
  `nccf_price` decimal(11,4) DEFAULT NULL,
  `commission_base` decimal(11,4) DEFAULT NULL,
  `commission_type` tinyint(4) NOT NULL DEFAULT 0,
  `commission_percentages` decimal(11,4) DEFAULT NULL,
  `commission_value` decimal(11,4) DEFAULT NULL,
  `commission_manual_value` decimal(11,4) DEFAULT NULL,
  `profit` decimal(11,4) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `term` date DEFAULT NULL,
  `description` text DEFAULT NULL,
  `item_order` int(11) DEFAULT NULL,
  `contract_cabin_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`,`revision`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_participants`
--

CREATE TABLE `contract_participants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `client_id` int(11) NOT NULL,
  `cabin_id` int(11) DEFAULT NULL,
  `participant_order` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_contract_cabin_id` (`cabin_id`)
) ENGINE=InnoDB AUTO_INCREMENT=46005 DEFAULT CHARSET=utf8mb4;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contract_participants__ai AFTER INSERT ON `cruiseandt_crm`.contract_participants FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contract_participants_history SELECT 'insert', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.contract_participants AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contract_participants__au AFTER UPDATE ON `cruiseandt_crm`.contract_participants FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contract_participants_history SELECT 'update', NULL, NOW(), d.*

    FROM `cruiseandt_crm`.contract_participants AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contract_participants__bd BEFORE DELETE ON `cruiseandt_crm`.contract_participants FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contract_participants_history SELECT 'delete', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.contract_participants AS d WHERE d.id = OLD.id */;;
DELIMITER ;

--
-- Table structure for table `contract_participants_history`
--

CREATE TABLE `contract_participants_history` (
  `action` varchar(8) DEFAULT 'insert',
  `revision` int(6) NOT NULL AUTO_INCREMENT,
  `dt_datetime` datetime NOT NULL DEFAULT current_timestamp(),
  `id` int(11) NOT NULL,
  `contract_id` int(11) NOT NULL,
  `client_id` int(11) NOT NULL,
  `cabin_id` int(11) DEFAULT NULL,
  `participant_order` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`,`revision`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_payments`
--

CREATE TABLE `contract_payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `value` decimal(11,4) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `value_pln` decimal(11,4) DEFAULT NULL,
  `term` date DEFAULT NULL,
  `exchange_rate` decimal(11,4) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_pdfs`
--

CREATE TABLE `contract_pdfs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `token` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `version` int(11) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL,
  `is_deleted` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7358 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_refunds`
--

CREATE TABLE `contract_refunds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `refund_type` int(11) DEFAULT NULL,
  `refund_value` decimal(11,4) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=286 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_settlements`
--

CREATE TABLE `contract_settlements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `deposits_and_withdrawals_id` int(11) NOT NULL,
  `debit_credit` varchar(10) DEFAULT NULL,
  `value` decimal(11,4) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `child` tinyint(4) NOT NULL DEFAULT 0,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16011 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_templates`
--

CREATE TABLE `contract_templates` (
  `id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_templates_l18n`
--

CREATE TABLE `contract_templates_l18n` (
  `contract_template_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`contract_template_id`,`language_id`),
  KEY `language_id` (`language_id`),
  CONSTRAINT `contract_templates_l18n_ibfk_1` FOREIGN KEY (`contract_template_id`) REFERENCES `contract_templates` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_types`
--

CREATE TABLE `contract_types` (
  `id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_types_l18n`
--

CREATE TABLE `contract_types_l18n` (
  `contract_type_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`contract_type_id`,`language_id`),
  KEY `language_id` (`language_id`),
  CONSTRAINT `contract_types_l18n_ibfk_1` FOREIGN KEY (`contract_type_id`) REFERENCES `contract_types` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_uploads`
--

CREATE TABLE `contract_uploads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `type` varchar(200) NOT NULL,
  `file_name` varchar(500) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL,
  `is_deleted` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contract_verification`
--

CREATE TABLE `contract_verification` (
  `contract_id` int(11) NOT NULL,
  `verification_status_id` int(4) NOT NULL,
  `verification_info` text DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_by` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`contract_id`),
  UNIQUE KEY `contract_id` (`contract_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `contracts`
--

CREATE TABLE `contracts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` varchar(50) DEFAULT NULL,
  `recurring_number` int(11) DEFAULT NULL,
  `contract_date` date NOT NULL DEFAULT current_timestamp(),
  `tour_operator_id` int(1) NOT NULL DEFAULT 1,
  `contract_type_id` int(11) NOT NULL DEFAULT 1,
  `portal_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `contract_template_id` int(11) DEFAULT NULL,
  `payer_id` int(11) DEFAULT NULL,
  `by_agent` tinyint(1) NOT NULL DEFAULT 0,
  `agent_id` int(11) DEFAULT NULL,
  `cruise_id` int(11) DEFAULT NULL,
  `cruise_itinerary_id` int(11) DEFAULT NULL,
  `cruise_name` varchar(255) DEFAULT NULL,
  `cruise_begin` date DEFAULT NULL,
  `cruise_end` date DEFAULT NULL,
  `cruise_days` int(11) DEFAULT NULL,
  `ship_id` int(11) DEFAULT NULL,
  `cruise_ship_name` varchar(255) DEFAULT NULL,
  `cruise_shipowner_name` varchar(255) DEFAULT NULL,
  `cruise_shipowner_short` varchar(64) DEFAULT NULL,
  `cruise_shipowner_icon` varchar(255) DEFAULT NULL,
  `shipowner_id` int(11) DEFAULT NULL,
  `brocker_id` int(11) DEFAULT NULL,
  `nccf_price` decimal(16,4) DEFAULT NULL,
  `nccf_currency_id` int(11) DEFAULT NULL,
  `notes_header` text DEFAULT NULL,
  `notes_footer` text DEFAULT NULL,
  `notes_special` text DEFAULT NULL,
  `contract_info` text DEFAULT NULL,
  `cost_information` longtext DEFAULT NULL,
  `price_include` text DEFAULT NULL,
  `price_notinclude` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  `status_id` int(11) NOT NULL DEFAULT 1,
  `cancel_reason_id` int(11) DEFAULT NULL,
  `cancel_decision_id` int(11) DEFAULT NULL,
  `cancel_date` date DEFAULT NULL,
  `cancel_info` text DEFAULT NULL,
  `referral_number` varchar(50) DEFAULT NULL,
  `referral_id` int(11) DEFAULT NULL,
  `status_updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `status_updated_by` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2934 DEFAULT CHARSET=utf8mb4;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contracts__ai AFTER INSERT ON `cruiseandt_crm`.contracts FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contracts_history SELECT 'insert', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.contracts AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contracts__au AFTER UPDATE ON `cruiseandt_crm`.contracts FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contracts_history SELECT 'update', NULL, NOW(), d.*

    FROM `cruiseandt_crm`.contracts AS d WHERE d.id = NEW.id */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 */ /*!50003 TRIGGER `cruiseandt_crm`.contracts__bd BEFORE DELETE ON `cruiseandt_crm`.contracts FOR EACH ROW

    INSERT INTO `cruiseandt_crm`.contracts_history SELECT 'delete', NULL, NOW(), d.* 

    FROM `cruiseandt_crm`.contracts AS d WHERE d.id = OLD.id */;;
DELIMITER ;

--
-- Table structure for table `contracts_history`
--

CREATE TABLE `contracts_history` (
  `action` varchar(8) DEFAULT 'insert',
  `revision` int(6) NOT NULL AUTO_INCREMENT,
  `dt_datetime` datetime NOT NULL DEFAULT current_timestamp(),
  `id` int(11) NOT NULL,
  `number` varchar(50) DEFAULT NULL,
  `recurring_number` int(11) DEFAULT NULL,
  `contract_date` date NOT NULL DEFAULT current_timestamp(),
  `tour_operator_id` int(1) NOT NULL DEFAULT 1,
  `contract_type_id` int(11) NOT NULL DEFAULT 1,
  `portal_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `contract_template_id` int(11) DEFAULT NULL,
  `payer_id` int(11) DEFAULT NULL,
  `by_agent` tinyint(1) NOT NULL DEFAULT 0,
  `agent_id` int(11) DEFAULT NULL,
  `cruise_id` int(11) DEFAULT NULL,
  `cruise_itinerary_id` int(11) DEFAULT NULL,
  `cruise_name` varchar(255) DEFAULT NULL,
  `cruise_begin` date DEFAULT NULL,
  `cruise_end` date DEFAULT NULL,
  `cruise_days` int(11) DEFAULT NULL,
  `ship_id` int(11) DEFAULT NULL,
  `cruise_ship_name` varchar(255) DEFAULT NULL,
  `cruise_shipowner_name` varchar(255) DEFAULT NULL,
  `cruise_shipowner_short` varchar(64) DEFAULT NULL,
  `cruise_shipowner_icon` varchar(255) DEFAULT NULL,
  `shipowner_id` int(11) DEFAULT NULL,
  `brocker_id` int(11) NOT NULL DEFAULT 0,
  `nccf_price` decimal(16,4) DEFAULT NULL,
  `nccf_currency_id` int(11) DEFAULT NULL,
  `notes_header` text DEFAULT NULL,
  `notes_footer` text DEFAULT NULL,
  `notes_special` text DEFAULT NULL,
  `contract_info` text DEFAULT NULL,
  `cost_information` longtext DEFAULT NULL,
  `price_include` text DEFAULT NULL,
  `price_notinclude` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  `status_id` int(11) NOT NULL DEFAULT 1,
  `cancel_reason_id` int(11) DEFAULT NULL,
  `cancel_decision_id` int(11) DEFAULT NULL,
  `cancel_date` date DEFAULT NULL,
  `cancel_info` text DEFAULT NULL,
  `referral_number` varchar(50) DEFAULT NULL,
  `referral_id` int(11) DEFAULT NULL,
  `status_updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `status_updated_by` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`,`revision`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

--
-- Temporary view structure for view `contracts_main`
--

/*!50001 DROP VIEW IF EXISTS `contracts_main`*/;
/*!50001 CREATE VIEW `contracts_main` AS SELECT 
 1 AS `id`,
 1 AS `number`,
 1 AS `number_details`,
 1 AS `recurring_number`,
 1 AS `contract_date`,
 1 AS `contract_date_formatted`,
 1 AS `tour_operator_id`,
 1 AS `contract_type_id`,
 1 AS `portal_id`,
 1 AS `user_id`,
 1 AS `contract_template_id`,
 1 AS `payer_id`,
 1 AS `by_agent`,
 1 AS `agent_id`,
 1 AS `cruise_id`,
 1 AS `cruise_itinerary_id`,
 1 AS `cruise_name`,
 1 AS `cruise_begin`,
 1 AS `cruise_begin_formatted`,
 1 AS `cruise_end`,
 1 AS `cruise_end_formatted`,
 1 AS `cruise_days`,
 1 AS `days_until_cruise_begin`,
 1 AS `cruise_details`,
 1 AS `ship_id`,
 1 AS `shipowner_id`,
 1 AS `brocker_id`,
 1 AS `nccf_price`,
 1 AS `nccf_currency_id`,
 1 AS `created_at`,
 1 AS `created_at_formatted`,
 1 AS `updated_at`,
 1 AS `updated_at_formatted`,
 1 AS `created_by`,
 1 AS `updated_by`,
 1 AS `is_deleted`,
 1 AS `status_id`,
 1 AS `contract_total`,
 1 AS `contract_shipowner_total`,
 1 AS `cancel_reason_id`,
 1 AS `cancel_decision_id`,
 1 AS `cancel_date`,
 1 AS `cancel_info`,
 1 AS `referral_number`,
 1 AS `referral_id`,
 1 AS `status_updated_at`,
 1 AS `status_updated_by`,
 1 AS `client_type`,
 1 AS `gender_id`,
 1 AS `company_full_name`,
 1 AS `payer_details`,
 1 AS `payer`,
 1 AS `first_name`,
 1 AS `last_name`,
 1 AS `phone`,
 1 AS `email`,
 1 AS `portal`,
 1 AS `agent`,
 1 AS `agent_details`,
 1 AS `status_name`,
 1 AS `status_color`,
 1 AS `shipowner_short_name`,
 1 AS `shipowner_name`,
 1 AS `shipowner_icon`,
 1 AS `ship_name`,
 1 AS `owner`,
 1 AS `booking_numbers`,
 1 AS `pax`,
 1 AS `participants`,
 1 AS `referral_status_id`,
 1 AS `verification_status_id`*/;

--
-- Temporary view structure for view `contracts_view`
--

/*!50001 DROP VIEW IF EXISTS `contracts_view`*/;
/*!50001 CREATE VIEW `contracts_view` AS SELECT 
 1 AS `id`,
 1 AS `number`,
 1 AS `number_details`,
 1 AS `recurring_number`,
 1 AS `contract_date`,
 1 AS `tour_operator_id`,
 1 AS `contract_type_id`,
 1 AS `portal_id`,
 1 AS `user_id`,
 1 AS `contract_template_id`,
 1 AS `payer_id`,
 1 AS `by_agent`,
 1 AS `agent_id`,
 1 AS `cruise_id`,
 1 AS `cruise_itinerary_id`,
 1 AS `cruise_name`,
 1 AS `cruise_begin`,
 1 AS `cruise_end`,
 1 AS `cruise_days`,
 1 AS `days_until_cruise_begin`,
 1 AS `cruise_details`,
 1 AS `ship_id`,
 1 AS `shipowner_id`,
 1 AS `brocker_id`,
 1 AS `nccf_price`,
 1 AS `nccf_currency_id`,
 1 AS `notes_header`,
 1 AS `notes_footer`,
 1 AS `contract_details`,
 1 AS `cost_information`,
 1 AS `price_include`,
 1 AS `price_notinclude`,
 1 AS `created_at`,
 1 AS `updated_at`,
 1 AS `created_by`,
 1 AS `updated_by`,
 1 AS `is_deleted`,
 1 AS `status_id`,
 1 AS `contract_total`,
 1 AS `contract_shipowner_total`,
 1 AS `cancel_reason_id`,
 1 AS `cancel_decision_id`,
 1 AS `cancel_date`,
 1 AS `cancel_info`,
 1 AS `referral_number`,
 1 AS `referral_id`,
 1 AS `status_updated_at`,
 1 AS `status_updated_by`,
 1 AS `client_type`,
 1 AS `gender_id`,
 1 AS `company_full_name`,
 1 AS `payer_details`,
 1 AS `payer`,
 1 AS `first_name`,
 1 AS `last_name`,
 1 AS `phone`,
 1 AS `email`,
 1 AS `portal`,
 1 AS `agent`,
 1 AS `agent_details`,
 1 AS `status_name`,
 1 AS `status_color`,
 1 AS `shipowner_short_name`,
 1 AS `shipowner_name`,
 1 AS `shipowner_icon`,
 1 AS `ship_name`,
 1 AS `owner`,
 1 AS `booking_numbers`,
 1 AS `pax`,
 1 AS `participants`,
 1 AS `referral_status_id`,
 1 AS `verification_status_id`*/;

--
-- Table structure for table `countries`
--

CREATE TABLE `countries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=251 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `countries_l18n`
--

CREATE TABLE `countries_l18n` (
  `country_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`country_id`,`language_id`),
  KEY `language_id` (`language_id`),
  CONSTRAINT `countries_l18n_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `countries_l18n_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `currencies`
--

CREATE TABLE `currencies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `symbol` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `symbol_position` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `deposits_and_withdrawals`
--

CREATE TABLE `deposits_and_withdrawals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text DEFAULT NULL,
  `value` decimal(11,4) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `iban` varchar(100) DEFAULT NULL,
  `debit_credit` varchar(20) DEFAULT NULL,
  `posting_date` date DEFAULT NULL,
  `operation_date` date DEFAULT NULL,
  `sender` text DEFAULT NULL,
  `receiver` text DEFAULT NULL,
  `operation_reference_number` varchar(100) DEFAULT NULL,
  `operation_number_per_day` int(11) DEFAULT NULL,
  `sender_account` varchar(255) DEFAULT NULL,
  `receiver_account` varchar(255) DEFAULT NULL,
  `deposits_and_withdrawals_category_id` int(11) DEFAULT NULL,
  `status` int(11) NOT NULL DEFAULT 1,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14205 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `deposits_and_withdrawals_categories`
--

CREATE TABLE `deposits_and_withdrawals_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `for_contracts` tinyint(4) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `deposits_and_withdrawals_categories_l18n`
--

CREATE TABLE `deposits_and_withdrawals_categories_l18n` (
  `deposits_and_withdrawals_category_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`deposits_and_withdrawals_category_id`,`language_id`),
  CONSTRAINT `deposits_and_withdrawals_categories_l18n_ibfk_1` FOREIGN KEY (`deposits_and_withdrawals_category_id`) REFERENCES `deposits_and_withdrawals_categories` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `deposits_and_withdrawals_import`
--

CREATE TABLE `deposits_and_withdrawals_import` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_name` text DEFAULT NULL,
  `iban` varchar(100) DEFAULT NULL,
  `operations_to` datetime DEFAULT NULL,
  `imported_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `imported_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2147 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `document_types`
--

CREATE TABLE `document_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `document_types_l18n`
--

CREATE TABLE `document_types_l18n` (
  `document_type_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`document_type_id`,`language_id`),
  KEY `language_id` (`language_id`),
  CONSTRAINT `document_types_l18n_ibfk_1` FOREIGN KEY (`document_type_id`) REFERENCES `document_types` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `document_types_l18n_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `genders`
--

CREATE TABLE `genders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `genders_l18n`
--

CREATE TABLE `genders_l18n` (
  `gender_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`gender_id`,`language_id`),
  KEY `language_id` (`language_id`),
  CONSTRAINT `genders_l18n_ibfk_1` FOREIGN KEY (`gender_id`) REFERENCES `genders` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `genders_l18n_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `insurance_types`
--

CREATE TABLE `insurance_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `insurance_types_l18n`
--

CREATE TABLE `insurance_types_l18n` (
  `insurance_type_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`insurance_type_id`,`language_id`),
  CONSTRAINT `insurance_types_l18n_ibfk_1` FOREIGN KEY (`insurance_type_id`) REFERENCES `insurance_types` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `invoice_item_types`
--

CREATE TABLE `invoice_item_types` (
  `id` int(11) DEFAULT NULL,
  `code` varchar(100) DEFAULT NULL,
  `type` int(11) DEFAULT NULL,
  `price_net` decimal(11,4) DEFAULT NULL,
  `price_gross` decimal(11,4) DEFAULT NULL,
  `vat_rate_id` int(11) DEFAULT NULL,
  `cn_pkwiu` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `invoice_item_types_l18n`
--

CREATE TABLE `invoice_item_types_l18n` (
  `invoice_item_type_id` int(11) DEFAULT NULL,
  `language_id` int(11) DEFAULT NULL,
  `name` varchar(250) DEFAULT NULL,
  `unit` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `ist_dumps`
--

CREATE TABLE `ist_dumps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(100) DEFAULT NULL,
  `shipowner_id` int(11) DEFAULT NULL,
  `shipowner_name` varchar(255) DEFAULT NULL,
  `ship_id` int(11) DEFAULT NULL,
  `ship_name` varchar(255) DEFAULT NULL,
  `file_name` varchar(255) DEFAULT NULL,
  `creation_date` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=852 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `languages`
--

CREATE TABLE `languages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT 0,
  `is_default` tinyint(4) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `navigation`
--

CREATE TABLE `navigation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `link` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `icon` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `color` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `role_id` int(11) NOT NULL,
  `weight` int(11) NOT NULL DEFAULT 0,
  `status` tinyint(4) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `navigation_l18n`
--

CREATE TABLE `navigation_l18n` (
  `navigation_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`language_id`,`navigation_id`),
  KEY `navigation_id` (`navigation_id`),
  CONSTRAINT `navigation_l18n_ibfk_1` FOREIGN KEY (`navigation_id`) REFERENCES `navigation` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `permissions`
--

CREATE TABLE `permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `parent_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `permissions_l18n`
--

CREATE TABLE `permissions_l18n` (
  `permission_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `display_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`permission_id`,`language_id`),
  CONSTRAINT `permissions_l18n_ibfk_1` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `portals`
--

CREATE TABLE `portals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `role_has_permissions`
--

CREATE TABLE `role_has_permissions` (
  `permission_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  KEY `permission_id` (`permission_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `role_has_permissions_ibfk_1` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `role_has_permissions_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `roles_l18n`
--

CREATE TABLE `roles_l18n` (
  `role_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `display_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`role_id`,`language_id`),
  CONSTRAINT `roles_l18n_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `ordering` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `shipowner_commissions`
--

CREATE TABLE `shipowner_commissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shipowner_id` int(11) NOT NULL,
  `contract_item_type_id` int(11) NOT NULL,
  `broker_id` int(11) DEFAULT 0,
  `value` decimal(11,4) DEFAULT NULL,
  `value_valid_from` decimal(11,4) NOT NULL DEFAULT 0.0000,
  `date_valid_from` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `shipowners`
--

CREATE TABLE `shipowners` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `added` datetime NOT NULL,
  `addedBy` int(11) NOT NULL,
  `edited` datetime DEFAULT NULL,
  `editedBy` int(11) DEFAULT NULL,
  `status` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `shipownerName` varchar(127) DEFAULT NULL,
  `shipOwnerLogo` varchar(255) DEFAULT NULL,
  `shortName` varchar(127) DEFAULT NULL,
  `homepage` varchar(127) DEFAULT NULL,
  `shipownerDescription` text DEFAULT NULL,
  `costInformation` text DEFAULT NULL,
  `contractInfo` text DEFAULT NULL,
  `shipownerIcon` varchar(255) DEFAULT NULL,
  `plSlug` varchar(255) DEFAULT NULL,
  `subpageHeaderImage` varchar(255) DEFAULT NULL,
  `subpageHeaderTitle` varchar(127) DEFAULT NULL,
  `priceInclude` text DEFAULT NULL,
  `priceNotInclude` text DEFAULT NULL,
  `plSeoTitle` varchar(127) DEFAULT NULL,
  `enSeoTitle` varchar(127) DEFAULT NULL,
  `plSeoDescription` varchar(255) DEFAULT NULL,
  `enSeoDescription` varchar(255) DEFAULT NULL,
  `seoImage` varchar(255) DEFAULT NULL,
  `cruiseCategoryId` int(11) DEFAULT NULL,
  `itineraryAvailableOnSitesId` text DEFAULT NULL,
  `multiItineraryCode` varchar(127) DEFAULT NULL,
  `multiItineraryUpdated` datetime DEFAULT NULL,
  `multiItineraryEnabled` tinyint(1) DEFAULT NULL,
  `defaultCruiseId` int(11) DEFAULT NULL,
  `multiItineraryConfig` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8;

--
-- Table structure for table `ships`
--

CREATE TABLE `ships` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `added` datetime NOT NULL,
  `addedBy` int(11) NOT NULL,
  `edited` datetime DEFAULT NULL,
  `editedBy` int(11) DEFAULT NULL,
  `status` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `shipName` varchar(127) DEFAULT NULL,
  `shipCabins` tinyint(4) DEFAULT NULL,
  `shipownerId` int(11) DEFAULT NULL,
  `shipStandard` decimal(10,8) DEFAULT NULL,
  `shipDescription` text DEFAULT NULL,
  `shipPhoto` varchar(255) DEFAULT NULL,
  `shipGallery` int(11) DEFAULT NULL,
  `shipDeckPlan` varchar(255) DEFAULT NULL,
  `plSlug` varchar(255) DEFAULT NULL,
  `shipParamsId` text DEFAULT NULL,
  `shipSecondaryDescription` text DEFAULT NULL,
  `shipDeckPlanLink` varchar(127) DEFAULT NULL,
  `shipActions` int(11) DEFAULT NULL,
  `shipCabinsDescription` text DEFAULT NULL,
  `plSeoTitle` varchar(127) DEFAULT NULL,
  `enSeoTitle` varchar(127) DEFAULT NULL,
  `plSeoDescription` varchar(255) DEFAULT NULL,
  `enSeoDescription` varchar(255) DEFAULT NULL,
  `seoImage` varchar(255) DEFAULT NULL,
  `cruiseCategoryId` int(11) DEFAULT NULL,
  `multiItineraryCode` varchar(127) DEFAULT NULL,
  `multiItineraryUpdated` datetime DEFAULT NULL,
  `multiItineraryEnabled` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=578 DEFAULT CHARSET=utf8;

--
-- Table structure for table `statuses`
--

CREATE TABLE `statuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(100) NOT NULL,
  `color` varchar(100) DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `statuses_l18n`
--

CREATE TABLE `statuses_l18n` (
  `status_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`status_id`,`language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `tour_operator_accounts`
--

CREATE TABLE `tour_operator_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tour_operator_id` int(11) NOT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `account_number` varchar(100) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `swift` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `tour_operators`
--

CREATE TABLE `tour_operators` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(5) DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `weight` int(11) NOT NULL DEFAULT 100,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `legal` varchar(255) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `house_number` varchar(50) DEFAULT NULL,
  `flat_number` varchar(50) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `zip_code` varchar(50) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `nip` varchar(50) DEFAULT NULL,
  `regon` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 1,
  `updated_by` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `user_details`
--

CREATE TABLE `user_details` (
  `user_id` int(11) NOT NULL,
  `date_of_employment` date DEFAULT NULL,
  `date_of_termination` date DEFAULT NULL,
  `sign_attendance_list` tinyint(4) NOT NULL DEFAULT 0,
  `automatic_leave_approve` tinyint(4) NOT NULL DEFAULT 0,
  `birthday` date DEFAULT NULL,
  `birthday_agree` tinyint(4) DEFAULT 0,
  `position_title_en` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `position_title_pl` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `office_id` int(11) DEFAULT NULL,
  `office_location` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_type_id` int(11) DEFAULT NULL,
  `phone_business` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone_personal` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mobile_business` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mobile_personal` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email_business` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email_personal` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `skype` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `linkedin` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `twitter` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `facebook` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `googleplus` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `remote_work` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `user_details_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `user_has_permissions`
--

CREATE TABLE `user_has_permissions` (
  `permission_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`permission_id`,`user_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `user_has_roles`
--

CREATE TABLE `user_has_roles` (
  `role_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`role_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `user_has_roles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password_updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `first_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT 0,
  `language_id` int(11) NOT NULL DEFAULT 2,
  `timezone` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Europe/Warsaw',
  `dateformat` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Y-m-d',
  `timeformat` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'H:i',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `vat_rates`
--

CREATE TABLE `vat_rates` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `calculation_value` decimal(11,4) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping routines for database 'cruiseandt_crm'
--

--
-- Final view structure for view `clients_main`
--

/*!50001 DROP VIEW IF EXISTS `clients_main`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `clients_main` AS select `c`.`id` AS `id`,`c`.`client_type` AS `client_type`,`c`.`gender_id` AS `gender_id`,`c`.`first_name` AS `first_name`,`c`.`last_name` AS `last_name`,`c`.`company_full_name` AS `company_full_name`,`c`.`phone` AS `phone`,`c`.`email` AS `email`,`c`.`pesel` AS `pesel`,`c`.`company_tax` AS `company_tax`,`c`.`city` AS `city`,`c`.`country_id` AS `country_id`,(select `cl`.`name` from `countries_l18n` `cl` where `cl`.`country_id` = `c`.`country_id` and `cl`.`language_id` = 1) AS `country`,case when `c`.`client_type` = 1 then concat(`c`.`first_name`,' ',`c`.`last_name`) else `c`.`company_full_name` end AS `client_name`,case when `c`.`client_type` = 1 then `c`.`pesel` else `c`.`company_tax` end AS `pesel_nip` from `clients` `c` where `c`.`is_deleted` = 0 */;

--
-- Final view structure for view `contracts_main`
--

/*!50001 DROP VIEW IF EXISTS `contracts_main`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `contracts_main` AS select `r`.`id` AS `id`,`r`.`number` AS `number`,`r`.`number` AS `number_details`,`r`.`recurring_number` AS `recurring_number`,`r`.`contract_date` AS `contract_date`,date_format(`r`.`contract_date`,'%d.%m.%Y') AS `contract_date_formatted`,`r`.`tour_operator_id` AS `tour_operator_id`,`r`.`contract_type_id` AS `contract_type_id`,`r`.`portal_id` AS `portal_id`,`r`.`user_id` AS `user_id`,`r`.`contract_template_id` AS `contract_template_id`,`r`.`payer_id` AS `payer_id`,`r`.`by_agent` AS `by_agent`,`r`.`agent_id` AS `agent_id`,`r`.`cruise_id` AS `cruise_id`,`r`.`cruise_itinerary_id` AS `cruise_itinerary_id`,`r`.`cruise_name` AS `cruise_name`,`r`.`cruise_begin` AS `cruise_begin`,date_format(`r`.`cruise_begin`,'%d.%m.%Y') AS `cruise_begin_formatted`,`r`.`cruise_end` AS `cruise_end`,date_format(`r`.`cruise_end`,'%d.%m.%Y') AS `cruise_end_formatted`,`r`.`cruise_days` AS `cruise_days`,to_days(`r`.`cruise_begin`) - to_days(curdate()) AS `days_until_cruise_begin`,concat(`r`.`cruise_name`,' ',date_format(`r`.`cruise_begin`,'%d.%m.%Y'),' ',date_format(`r`.`cruise_end`,'%d.%m.%Y'),' ',`r`.`cruise_days`,' ',`so`.`shortName`,' ',`so`.`shipownerName`,' ',`sh`.`shipName`) AS `cruise_details`,`r`.`ship_id` AS `ship_id`,`r`.`shipowner_id` AS `shipowner_id`,`r`.`brocker_id` AS `brocker_id`,`r`.`nccf_price` AS `nccf_price`,`r`.`nccf_currency_id` AS `nccf_currency_id`,`r`.`created_at` AS `created_at`,date_format(`r`.`created_at`,'%d.%m.%Y %H.%i.%s') AS `created_at_formatted`,`r`.`updated_at` AS `updated_at`,date_format(`r`.`updated_at`,'%d.%m.%Y %H.%i.%s') AS `updated_at_formatted`,`r`.`created_by` AS `created_by`,`r`.`updated_by` AS `updated_by`,`r`.`is_deleted` AS `is_deleted`,`r`.`status_id` AS `status_id`,`r`.`cruise_days` AS `contract_total`,`r`.`cruise_days` AS `contract_shipowner_total`,`r`.`cancel_reason_id` AS `cancel_reason_id`,`r`.`cancel_decision_id` AS `cancel_decision_id`,`r`.`cancel_date` AS `cancel_date`,`r`.`cancel_info` AS `cancel_info`,`r`.`referral_number` AS `referral_number`,`r`.`referral_id` AS `referral_id`,`r`.`status_updated_at` AS `status_updated_at`,`r`.`status_updated_by` AS `status_updated_by`,`c`.`client_type` AS `client_type`,`c`.`gender_id` AS `gender_id`,`c`.`company_full_name` AS `company_full_name`,concat(`c`.`first_name`,' ',`c`.`last_name`,' ',concat(`c`.`first_name`,`c`.`last_name`),' ',replace(`c`.`phone`,' ',''),' ',`c`.`email`) AS `payer_details`,concat(`c`.`first_name`,' ',`c`.`last_name`) AS `payer`,`c`.`first_name` AS `first_name`,`c`.`last_name` AS `last_name`,`c`.`phone` AS `phone`,`c`.`email` AS `email`,`p`.`name` AS `portal`,`a`.`company_short_name` AS `agent`,`a`.`company_short_name` AS `agent_details`,`cs_l18n`.`name` AS `status_name`,`cs`.`color` AS `status_color`,`so`.`shortName` AS `shipowner_short_name`,`so`.`shipownerName` AS `shipowner_name`,`so`.`shipownerIcon` AS `shipowner_icon`,`sh`.`shipName` AS `ship_name`,(select concat(`u`.`first_name`,' ',`u`.`last_name`) from `users` `u` where `r`.`user_id` = `u`.`id`) AS `owner`,(select group_concat(rtrim(`cc`.`booking_number`) separator ', ') from `contract_cabins` `cc` where `cc`.`contract_id` = `r`.`id`) AS `booking_numbers`,(select count(`p`.`client_id`) from `contract_participants` `p` where `r`.`id` = `p`.`contract_id`) AS `pax`,(select group_concat(concat(`cl`.`first_name`,' ',`cl`.`last_name`) separator '<br>') from (`contract_participants` `cp` join `clients` `cl` on(`cp`.`client_id` = `cl`.`id`)) where `r`.`id` = `cp`.`contract_id`) AS `participants`,(select `f`.`status_id` from `contracts` `f` where `f`.`id` = `r`.`referral_id`) AS `referral_status_id`,(select `v`.`verification_status_id` from `contract_verification` `v` where `r`.`id` = `v`.`contract_id`) AS `verification_status_id` from (((((((`contracts` `r` left join `clients` `c` on(`r`.`payer_id` = `c`.`id`)) left join `agents` `a` on(`r`.`agent_id` = `a`.`id`)) left join `portals` `p` on(`r`.`portal_id` = `p`.`id`)) left join `statuses` `cs` on(`r`.`status_id` = `cs`.`id`)) left join `statuses_l18n` `cs_l18n` on(`cs_l18n`.`status_id` = `cs`.`id` and `cs_l18n`.`language_id` = 2)) left join `ships` `sh` on(`r`.`ship_id` = `sh`.`id`)) left join `shipowners` `so` on(`r`.`shipowner_id` = `so`.`id`)) where `cs`.`type` = 'contract' */;

--
-- Final view structure for view `contracts_view`
--

/*!50001 DROP VIEW IF EXISTS `contracts_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `contracts_view` AS select `r`.`id` AS `id`,`r`.`number` AS `number`,`r`.`number` AS `number_details`,`r`.`recurring_number` AS `recurring_number`,`r`.`contract_date` AS `contract_date`,`r`.`tour_operator_id` AS `tour_operator_id`,`r`.`contract_type_id` AS `contract_type_id`,`r`.`portal_id` AS `portal_id`,`r`.`user_id` AS `user_id`,`r`.`contract_template_id` AS `contract_template_id`,`r`.`payer_id` AS `payer_id`,`r`.`by_agent` AS `by_agent`,`r`.`agent_id` AS `agent_id`,`r`.`cruise_id` AS `cruise_id`,`r`.`cruise_itinerary_id` AS `cruise_itinerary_id`,`r`.`cruise_name` AS `cruise_name`,`r`.`cruise_begin` AS `cruise_begin`,`r`.`cruise_end` AS `cruise_end`,`r`.`cruise_days` AS `cruise_days`,to_days(`r`.`cruise_begin`) - to_days(curdate()) AS `days_until_cruise_begin`,concat(`r`.`cruise_name`,' ',`r`.`cruise_begin`,' ',`r`.`cruise_end`,' ',`r`.`cruise_days`,'',`cruiseandt_crm`.`shipowners`.`shortName`,'',`cruiseandt_crm`.`shipowners`.`shipownerName`,'',`cruiseandt_crm`.`ships`.`shipName`) AS `cruise_details`,`r`.`ship_id` AS `ship_id`,`r`.`shipowner_id` AS `shipowner_id`,`r`.`brocker_id` AS `brocker_id`,`r`.`nccf_price` AS `nccf_price`,`r`.`nccf_currency_id` AS `nccf_currency_id`,`r`.`notes_header` AS `notes_header`,`r`.`notes_footer` AS `notes_footer`,`r`.`contract_info` AS `contract_details`,`r`.`cost_information` AS `cost_information`,`r`.`price_include` AS `price_include`,`r`.`price_notinclude` AS `price_notinclude`,`r`.`created_at` AS `created_at`,`r`.`updated_at` AS `updated_at`,`r`.`created_by` AS `created_by`,`r`.`updated_by` AS `updated_by`,`r`.`is_deleted` AS `is_deleted`,`r`.`status_id` AS `status_id`,`r`.`cruise_days` AS `contract_total`,`r`.`cruise_days` AS `contract_shipowner_total`,`r`.`cancel_reason_id` AS `cancel_reason_id`,`r`.`cancel_decision_id` AS `cancel_decision_id`,`r`.`cancel_date` AS `cancel_date`,`r`.`cancel_info` AS `cancel_info`,`r`.`referral_number` AS `referral_number`,`r`.`referral_id` AS `referral_id`,`r`.`status_updated_at` AS `status_updated_at`,`r`.`status_updated_by` AS `status_updated_by`,`c`.`client_type` AS `client_type`,`c`.`gender_id` AS `gender_id`,`c`.`company_full_name` AS `company_full_name`,concat(`c`.`first_name`,' ',`c`.`last_name`,' ',replace(`c`.`phone`,' ',''),' ',`c`.`email`) AS `payer_details`,concat(`c`.`first_name`,' ',`c`.`last_name`) AS `payer`,`c`.`first_name` AS `first_name`,`c`.`last_name` AS `last_name`,`c`.`phone` AS `phone`,`c`.`email` AS `email`,`p`.`name` AS `portal`,`a`.`company_short_name` AS `agent`,`a`.`company_short_name` AS `agent_details`,`s`.`status_name` AS `status_name`,`s`.`status_color` AS `status_color`,`cruiseandt_crm`.`shipowners`.`shortName` AS `shipowner_short_name`,`cruiseandt_crm`.`shipowners`.`shipownerName` AS `shipowner_name`,`cruiseandt_crm`.`shipowners`.`shipownerIcon` AS `shipowner_icon`,`cruiseandt_crm`.`ships`.`shipName` AS `ship_name`,(select concat(`u`.`first_name`,' ',`u`.`last_name`) from `cruiseandt_crm`.`users` `u` where `r`.`user_id` = `u`.`id`) AS `owner`,(select group_concat(rtrim(`cc`.`booking_number`) separator ', ') from `cruiseandt_crm`.`contract_cabins` `cc` where `cc`.`contract_id` = `r`.`id`) AS `booking_numbers`,(select count(`p`.`client_id`) from `cruiseandt_crm`.`contract_participants` `p` where `r`.`id` = `p`.`contract_id`) AS `pax`,(select group_concat(concat(`c`.`first_name`,' ',`c`.`last_name`) separator '<br>') from (`cruiseandt_crm`.`contract_participants` `cp` join `cruiseandt_crm`.`clients` `c` on(`cp`.`client_id` = `c`.`id`)) where `r`.`id` = `cp`.`contract_id`) AS `participants`,(select `f`.`status_id` from `cruiseandt_crm`.`contracts` `f` where `f`.`id` = `r`.`referral_id`) AS `referral_status_id`,(select `v`.`verification_status_id` from `cruiseandt_crm`.`contract_verification` `v` where `r`.`id` = `v`.`contract_id`) AS `verification_status_id` from ((((((`cruiseandt_crm`.`contracts` `r` left join `cruiseandt_crm`.`clients` `c` on(`r`.`payer_id` = `c`.`id`)) left join `cruiseandt_crm`.`agents` `a` on(`r`.`agent_id` = `a`.`id`)) left join `cruiseandt_crm`.`portals` `p` on(`r`.`portal_id` = `p`.`id`)) left join (select `cs`.`id` AS `id`,`cs_l18n`.`name` AS `status_name`,`cs`.`color` AS `status_color` from (`cruiseandt_crm`.`statuses` `cs` join `cruiseandt_crm`.`statuses_l18n` `cs_l18n`) where `cs_l18n`.`status_id` = `cs`.`id` and `cs_l18n`.`language_id` = 2 and `cs`.`type` = 'contract') `s` on(`r`.`status_id` = `s`.`id`)) left join `cruiseandt_crm`.`ships` on(`r`.`ship_id` = `cruiseandt_crm`.`ships`.`id`)) left join `cruiseandt_crm`.`shipowners` on(`r`.`shipowner_id` = `cruiseandt_crm`.`shipowners`.`id`)) */;


