-- CREATE DATABASE `amigocheckout` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

-- CREATE TABLE `users_type` (
--   `id` int NOT NULL AUTO_INCREMENT,
--   `name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
--   PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- CREATE TABLE `company` (
--   `id` int NOT NULL AUTO_INCREMENT,
--   `name` varchar(180) COLLATE utf8mb4_general_ci DEFAULT NULL,
--   `comertial_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
--   `comertial_email` varchar(180) COLLATE utf8mb4_general_ci DEFAULT NULL,
--   `financial_email` varchar(180) COLLATE utf8mb4_general_ci DEFAULT NULL,
--   `booking_email` varchar(180) COLLATE utf8mb4_general_ci DEFAULT NULL,
--   `is_active` int NOT NULL DEFAULT '0',
--   `address` text COLLATE utf8mb4_general_ci NOT NULL,
--   `isDefault` int DEFAULT '0',
--   PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;


-- CREATE TABLE `users` (
--   `id` int NOT NULL AUTO_INCREMENT,
--   `email` varchar(180) COLLATE utf8mb4_general_ci DEFAULT NULL,
--   `password` varchar(280) COLLATE utf8mb4_general_ci DEFAULT NULL,
--   `name` varchar(180) COLLATE utf8mb4_general_ci DEFAULT NULL,
--   `surname` varchar(180) COLLATE utf8mb4_general_ci DEFAULT NULL,
--   `phone` varchar(180) COLLATE utf8mb4_general_ci DEFAULT NULL,
--   `active` tinyint(1) DEFAULT '0' COMMENT 'Activo',
--   `approved` tinyint(1) DEFAULT '0' COMMENT 'Aprovador por un administrador',
--   `id_company` int NOT NULL COMMENT 'Empresa',
--   `id_user_type` int NOT NULL,
--   `id_referred` int DEFAULT NULL,
--   `id_agency` int DEFAULT NULL,
--   `deleted` tinyint(1) DEFAULT '0',
--   PRIMARY KEY (`id`),
--   KEY `users_fk0` (`id_company`),
--   KEY `user_fk_1` (`id_user_type`),
--   CONSTRAINT `user_fk_1` FOREIGN KEY (`id_user_type`) REFERENCES `users_type` (`id`),
--   CONSTRAINT `users_fk0` FOREIGN KEY (`id_company`) REFERENCES `company` (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;



-- CREATE TABLE `user_refresh_tokens` (
--   `user_refresh_tokenID` int unsigned NOT NULL AUTO_INCREMENT,
--   `urf_userID` int unsigned NOT NULL,
--   `urf_token` varchar(1000) COLLATE utf8mb4_general_ci DEFAULT NULL,
--   `urf_ip` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
--   `urf_user_agent` varchar(1000) COLLATE utf8mb4_general_ci DEFAULT NULL,
--   `urf_created` datetime NOT NULL COMMENT 'UTC',
--   PRIMARY KEY (`user_refresh_tokenID`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='For JWT authentication process';

-- ALTER TABLE `orders` 
-- DROP FOREIGN KEY `fk_orders_user_refresh_tokens`;
-- ALTER TABLE `amigocheckout`.`orders` 
-- CHANGE COLUMN `user_refresh_tokenID` `user_refresh_tokenID` INT UNSIGNED NULL ;
-- ALTER TABLE `amigocheckout`.`orders` 
-- ADD CONSTRAINT `fk_orders_user_refresh_tokens`
--   FOREIGN KEY (`user_refresh_tokenID`)
--   REFERENCES `amigocheckout`.`user_refresh_tokens` (`user_refresh_tokenID`)
--   ON DELETE RESTRICT
--   ON UPDATE CASCADE;

-- CREATE TABLE `orders` (
--   `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

--   -- Relación con tu usuario/sesión (como tenías)
--   `user_refresh_tokenID` INT UNSIGNED NOT NULL,

--   -- Datos de contacto y reserva
--   `name`        VARCHAR(180) NOT NULL,
--   `email`       VARCHAR(180) NOT NULL,
--   `phone`       VARCHAR(32)  NULL,
--   `pax`         INT UNSIGNED NOT NULL,                                -- número de personas

--   -- Precios (en centavos)
--   `price_per_person_value` INT UNSIGNED NOT NULL DEFAULT 0,           -- opcional, por trazabilidad
--   `amount_value`           INT UNSIGNED NOT NULL DEFAULT 0,           -- subtotal (pax * price)
--   `tax_value`              INT UNSIGNED NOT NULL DEFAULT 0,
--   `tip_percent`            TINYINT UNSIGNED NOT NULL DEFAULT 0,       -- 0, 10, 15, 18...
--   `tip_value`              INT UNSIGNED NOT NULL DEFAULT 0,
--   `total_value`            INT UNSIGNED NOT NULL,                     -- total a cobrar (amount+tax+tip)

--   -- Moneda y modo de captura
--   `currency`     CHAR(3) NOT NULL,                                    -- p.ej. 'MXN'
--   `capture_mode` ENUM('automatic','manual') NOT NULL DEFAULT 'automatic',

--   -- Trazabilidad de la orden
--   `merchant_order_ext_ref` VARCHAR(100) NOT NULL,                     -- tu referencia interna única
--   `description`            VARCHAR(255) NULL,

--   -- Identificadores de Revolut
--   `checkout_token`   CHAR(36)  NOT NULL,                              -- token devuelto por /api/orders (v2024-09-01)
--   `revolut_order_id` CHAR(36)  NULL,                                  -- id interno de Revolut
--   `revolut_public_id` CHAR(36) NULL,                                  -- public_id de la orden

--   -- Estados
--   `revolut_state` ENUM('PENDING','AUTHORISED','COMPLETED','FAILED','CANCELLED') 
--                   NOT NULL DEFAULT 'PENDING',
--   `status`        ENUM('CREATED','PAID','FAILED','CANCELLED') 
--                   NOT NULL DEFAULT 'CREATED',                          -- estado propio de tu app
--   `webhook_last_event_id` VARCHAR(100) NULL,                           -- id de último evento procesado

--   -- Auditoría
--   `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
--   `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
--   `paid_at`    DATETIME NULL,

--   PRIMARY KEY (`id`),

--   -- Unicidades/Índices
--   UNIQUE KEY `uk_merchant_order_ext_ref` (`merchant_order_ext_ref`),
--   UNIQUE KEY `uk_revolut_public_id`     (`revolut_public_id`),
--   KEY `idx_user_refresh_tokenID` (`user_refresh_tokenID`),
--   KEY `idx_email_created` (`email`, `created_at`),
--   KEY `idx_revolut_state` (`revolut_state`),

--   -- FK a tu tabla de sesiones/usuarios
--   CONSTRAINT `fk_orders_user_refresh_tokens`
--     FOREIGN KEY (`user_refresh_tokenID`)
--     REFERENCES `user_refresh_tokens` (`user_refresh_tokenID`)
--     ON UPDATE CASCADE ON DELETE RESTRICT
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- -- Registro de webhooks para idempotencia + auditoría
-- CREATE TABLE IF NOT EXISTS webhook_events (
--   id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
--   event_id          VARCHAR(100) NOT NULL,    -- ID único del evento enviado por Revolut (si lo provee)
--   event_type        VARCHAR(80)  NOT NULL,    -- p.ej. ORDER_COMPLETED, ORDER_PAYMENT_FAILED
--   revolut_order_id  VARCHAR(64)  NULL,        -- del payload (order.id)
--   revolut_public_id VARCHAR(64)  NULL,        -- del payload (order.public_id)
--   state             VARCHAR(32)  NULL,        -- state reportado (COMPLETED, FAILED, etc.)
--   amount_value      INT          NULL,
--   currency          CHAR(3)      NULL,

--   sig_header        VARCHAR(512) NULL,        -- Revolut-Signature (opcional para auditoría)
--   received_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
--   processed_at      DATETIME NULL,
--   process_status    ENUM('PENDING','PROCESSED','SKIPPED','ERROR') NOT NULL DEFAULT 'PENDING',
--   process_error     VARCHAR(500) NULL,

--   raw_json          LONGTEXT NOT NULL,        -- payload crudo (útil para auditoría y debugging)

--   PRIMARY KEY (id),
--   UNIQUE KEY uk_event_id (event_id),          -- Idempotencia: evita reprocesar un mismo evento
--   KEY idx_revolut_public_id (revolut_public_id),
--   KEY idx_event_type (event_type),
--   KEY idx_received_at (received_at)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE SCHEMA `amigotours`;

CREATE TABLE `amigotours`.`company_branch` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` varchar(180) NOT NULL,
  `id_city` int NOT NULL,
  `id_company` int NOT NULL
);

CREATE TABLE `amigotours`.`tax_types` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT null,
  `rate` DECIMAL(5,2) DEFAULT null,
  `description` TEXT DEFAULT null
);

CREATE TABLE `amigotours`.`continent` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) DEFAULT null,
  `name_en` VARCHAR(100) DEFAULT null,
  `slug` VARCHAR(100) DEFAULT null,
  `slug_en` VARCHAR(100) DEFAULT null
);

CREATE TABLE `amigotours`.`country` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(8) DEFAULT null,
  `name` VARCHAR(45) DEFAULT null,
  `name_en` VARCHAR(45) DEFAULT null,
  `slug` VARCHAR(45) DEFAULT null,
  `slug_en` VARCHAR(50) DEFAULT null,
  `id_continent` INT NOT NULL DEFAULT '5'
);

CREATE TABLE `amigotours`.`region` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) DEFAULT null,
  `name_en` VARCHAR(150) DEFAULT null,
  `abbreviation` VARCHAR(20) DEFAULT null,
  `abbreviation_en` VARCHAR(20) DEFAULT null,
  `id_country` INT NOT NULL COMMENT 'País',
  `slug` VARCHAR(180) DEFAULT null,
  `slug_en` VARCHAR(180) DEFAULT null
);

CREATE TABLE `amigotours`.`city` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(180) DEFAULT null,
  `name_en` VARCHAR(180) DEFAULT null,
  `id_region` INT NOT NULL COMMENT 'Región',
  `slug` VARCHAR(180) DEFAULT null,
  `slug_en` VARCHAR(180) DEFAULT null,
  `image` VARCHAR(250) DEFAULT null,
  `is_zone` INT DEFAULT null
);

CREATE TABLE `amigotours`.`statuspost` (
  `id` INT PRIMARY KEY NOT NULL,
  `name` VARCHAR(45) DEFAULT null,
  `name_en` VARCHAR(45) DEFAULT null
);

CREATE TABLE `amigotours`.`attractions` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_city` INT NOT NULL,
  `title` VARCHAR(150) DEFAULT null,
  `title_en` VARCHAR(150) DEFAULT null,
  `id_statuspost` INT NOT NULL,
  `is_featured` TINYINT DEFAULT '0'
);

CREATE TABLE `amigotours`.`attractions_alternatives` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_attractions` INT NOT NULL,
  `id_city` INT NOT NULL
);

CREATE TABLE `amigotours`.`attractions_images` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `image_date` VARCHAR(150) NOT NULL DEFAULT '0',
  `file_name` VARCHAR(150) NOT NULL DEFAULT '0',
  `guid` VARCHAR(150) NOT NULL DEFAULT '0',
  `caption` VARCHAR(350) NOT NULL DEFAULT '',
  `caption_en` VARCHAR(150) DEFAULT null,
  `id_attractions` INT NOT NULL DEFAULT '0',
  `is_cover` TINYINT NOT NULL DEFAULT '0'
);

CREATE TABLE `amigotours`.`attractions_section` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `htmlcontent` LONGTEXT DEFAULT null,
  `htmlcontent_en` LONGTEXT DEFAULT null,
  `title` VARCHAR(150) DEFAULT null,
  `title_en` VARCHAR(150) DEFAULT null,
  `section_name` VARCHAR(80) DEFAULT null,
  `section_name_en` VARCHAR(80) DEFAULT null,
  `section` VARCHAR(45) DEFAULT null,
  `section_en` VARCHAR(45) DEFAULT null,
  `id_attractions` INT NOT NULL
);

CREATE TABLE `amigotours`.`attractions_seo` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `keywords` VARCHAR(250) DEFAULT null,
  `keywords_en` VARCHAR(250) DEFAULT null,
  `description` VARCHAR(150) DEFAULT null,
  `description_en` VARCHAR(250) DEFAULT null,
  `canonicalurl` VARCHAR(250) DEFAULT null,
  `slug` VARCHAR(200) DEFAULT null,
  `slug_en` VARCHAR(200) DEFAULT null,
  `id_attractions_section` INT NOT NULL
);

CREATE TABLE `amigotours`.`company` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(180) DEFAULT null,
  `comertial_name` VARCHAR(255) DEFAULT null,
  `comertial_email` VARCHAR(180) DEFAULT null,
  `financial_email` VARCHAR(180) DEFAULT null,
  `booking_email` VARCHAR(180) DEFAULT null,
  `is_active` INT NOT NULL DEFAULT '0',
  `address` TEXT NOT NULL,
  `isDefault` INT DEFAULT '0'
);

CREATE TABLE `amigotours`.`bank_settings` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `bank` VARCHAR(180) DEFAULT null,
  `account` VARCHAR(80) DEFAULT null,
  `swift` VARCHAR(50) DEFAULT null,
  `iban` VARCHAR(80) DEFAULT null,
  `country` VARCHAR(180) DEFAULT null,
  `name` VARCHAR(180) DEFAULT null,
  `id_company` INT NOT NULL
);

CREATE TABLE `amigotours`.`booking` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_tour` INT NOT NULL COMMENT 'Tour',
  `date_created` DATETIME NOT NULL COMMENT 'Fecha de creación '
);

CREATE TABLE `amigotours`.`booking_payment` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `charge_id` VARCHAR(180) DEFAULT null,
  `livemode` TINYINT NOT NULL DEFAULT '0',
  `tour_booking_id` TINYINT NOT NULL DEFAULT '0',
  `created_at` VARCHAR(50) DEFAULT null
);

CREATE TABLE `amigotours`.`booking_systems` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) DEFAULT null,
  `config` TEXT DEFAULT null
);

CREATE TABLE `amigotours`.`categories` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) DEFAULT null,
  `name_en` VARCHAR(150) DEFAULT null,
  `slug` VARCHAR(160) DEFAULT null
);

CREATE TABLE `amigotours`.`channel` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(180) DEFAULT null,
  `active` TINYINT(1) NOT NULL DEFAULT '0'
);

CREATE TABLE `amigotours`.`city_summary` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_city` INT NOT NULL,
  `title` VARCHAR(150) DEFAULT null,
  `title_en` VARCHAR(150) DEFAULT null,
  `id_statuspost` INT NOT NULL,
  `short_description` VARCHAR(255) DEFAULT null,
  `short_description_en` VARCHAR(255) DEFAULT null
);

CREATE TABLE `amigotours`.`city_summary_images` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `image_date` VARCHAR(150) NOT NULL DEFAULT '0',
  `file_name` VARCHAR(150) NOT NULL DEFAULT '0',
  `guid` VARCHAR(150) NOT NULL DEFAULT '0',
  `caption` VARCHAR(350) NOT NULL DEFAULT '',
  `caption_en` VARCHAR(150) DEFAULT null,
  `id_city_summary` INT NOT NULL DEFAULT '0',
  `is_cover` TINYINT NOT NULL DEFAULT '0'
);

CREATE TABLE `amigotours`.`city_summary_section` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `htmlcontent` TEXT DEFAULT null,
  `htmlcontent_en` TEXT DEFAULT null,
  `section` VARCHAR(45) DEFAULT null,
  `section_en` VARCHAR(45) DEFAULT null,
  `id_city_summary` INT NOT NULL
);

CREATE TABLE `amigotours`.`city_zone` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_city` INT DEFAULT null,
  `id_city_in_zone` VARCHAR(255) DEFAULT null
);

CREATE TABLE `amigotours`.`users_type` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT null
);

CREATE TABLE `amigotours`.`users_position` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT null
);

CREATE TABLE `amigotours`.`users` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(180) DEFAULT null,
  `password` VARCHAR(280) DEFAULT null,
  `name` VARCHAR(180) DEFAULT null,
  `surname` VARCHAR(180) DEFAULT null,
  `phone` VARCHAR(180) DEFAULT null,
  `active` TINYINT(1) DEFAULT '0' COMMENT 'Activo',
  `approved` TINYINT(1) DEFAULT '0' COMMENT 'Aprovador por un administrador',
  `id_company` INT NOT NULL COMMENT 'Empresa',
  `id_company_branch` INT NOT NULL COMMENT 'Surcursal',
  `id_user_type` INT NOT NULL,
  `id_user_position` INT NOT NULL,
  `position_rank` int DEFAULT null,
  `position_rank_name` int DEFAULT null,
  `deleted` TINYINT(1) DEFAULT '0'
);

CREATE TABLE `amigotours`.`comments` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_booking` INT DEFAULT null COMMENT 'Reserva',
  `date_created` DATETIME NOT NULL COMMENT 'Fecha de creación\\n',
  `date_approved` DATETIME DEFAULT null COMMENT 'Fecha de aprobación\\n',
  `approved` TINYINT(1) DEFAULT '0' COMMENT 'Aprovado',
  `id_user_approved` INT NOT NULL COMMENT 'Usuario que aprovo'
);

CREATE TABLE `amigotours`.`company_contact` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT null,
  `phone` VARCHAR(20) DEFAULT null,
  `cellphone` VARCHAR(30) DEFAULT null,
  `ext` VARCHAR(10) DEFAULT null,
  `email` VARCHAR(255) DEFAULT null,
  `position` VARCHAR(255) DEFAULT null,
  `id_company` INT DEFAULT null,
  `notify_booking` INT DEFAULT null
);

CREATE TABLE `amigotours`.`contract` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(180) DEFAULT null,
  `active` TINYINT(1) NOT NULL DEFAULT '0',
  `id_company` INT NOT NULL,
  `start_on` DATE NOT NULL,
  `end_on` DATE NOT NULL,
  `notes` TEXT DEFAULT null,
  `id_currency` INT NOT NULL,
  `margin` VARCHAR(10) DEFAULT null
);

CREATE TABLE `amigotours`.`country_summary` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_country` INT NOT NULL,
  `title` VARCHAR(150) DEFAULT null,
  `title_en` VARCHAR(150) DEFAULT null,
  `id_statuspost` INT NOT NULL,
  `short_description` VARCHAR(255) DEFAULT null,
  `short_description_en` VARCHAR(255) DEFAULT null
);

CREATE TABLE `amigotours`.`country_summary_images` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `image_date` VARCHAR(150) NOT NULL DEFAULT '0',
  `file_name` VARCHAR(150) NOT NULL DEFAULT '0',
  `guid` VARCHAR(150) NOT NULL DEFAULT '0',
  `caption` VARCHAR(350) NOT NULL DEFAULT '',
  `id_country_summary` INT NOT NULL DEFAULT '0',
  `is_cover` TINYINT NOT NULL DEFAULT '0',
  `caption_en` VARCHAR(350) DEFAULT null
);

CREATE TABLE `amigotours`.`country_summary_section` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `htmlcontent` TEXT DEFAULT null,
  `section` VARCHAR(45) DEFAULT null,
  `section_en` VARCHAR(45) DEFAULT null,
  `id_country_summary` INT NOT NULL,
  `htmlcontent_en` MEDIUMTEXT DEFAULT null
);

CREATE TABLE `amigotours`.`currency` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(180) DEFAULT null,
  `name_en` VARCHAR(180) DEFAULT null,
  `code` VARCHAR(5) DEFAULT null,
  `symbol` VARCHAR(5) DEFAULT null
);

CREATE TABLE `amigotours`.`currency_rate_exchange` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_currency_base` INT NOT NULL,
  `id_currency_to` INT NOT NULL,
  `rate` VARCHAR(50) DEFAULT null,
  `last_update` DATE DEFAULT null
);

CREATE TABLE `amigotours`.`destination_guides` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_city` INT NOT NULL,
  `title` VARCHAR(150) DEFAULT null,
  `title_en` VARCHAR(150) DEFAULT null,
  `id_statuspost` INT NOT NULL,
  `is_featured` TINYINT DEFAULT '0'
);

CREATE TABLE `amigotours`.`destination_guides_images` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `image_date` VARCHAR(150) NOT NULL DEFAULT '0',
  `file_name` VARCHAR(150) NOT NULL DEFAULT '0',
  `guid` VARCHAR(150) NOT NULL DEFAULT '0',
  `caption` VARCHAR(350) NOT NULL DEFAULT '',
  `caption_en` VARCHAR(350) DEFAULT null,
  `id_destination_guides` INT NOT NULL DEFAULT '0',
  `is_cover` TINYINT NOT NULL DEFAULT '0'
);

CREATE TABLE `amigotours`.`destination_guides_section` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `htmlcontent` LONGTEXT DEFAULT null,
  `htmlcontent_en` LONGTEXT DEFAULT null,
  `title` VARCHAR(150) DEFAULT null,
  `title_en` VARCHAR(150) DEFAULT null,
  `section_name` VARCHAR(80) DEFAULT null,
  `section_name_en` VARCHAR(80) DEFAULT null,
  `section` VARCHAR(45) DEFAULT null,
  `section_en` VARCHAR(45) DEFAULT null,
  `id_destination_guides` INT NOT NULL
);

CREATE TABLE `amigotours`.`destination_guides_seo` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `keywords` VARCHAR(500) DEFAULT null,
  `description` VARCHAR(250) DEFAULT null,
  `canonicalurl` VARCHAR(250) DEFAULT null,
  `slug` VARCHAR(250) DEFAULT null,
  `id_destination_guides_section` INT NOT NULL,
  `keywords_en` VARCHAR(250) DEFAULT null,
  `description_en` VARCHAR(250) DEFAULT null,
  `slug_en` VARCHAR(250) DEFAULT null
);

CREATE TABLE `amigotours`.`exchange_rates` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `currency_code` VARCHAR(3) NOT NULL,
  `rate` DECIMAL(15,6) NOT NULL,
  `last_updated` TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE `amigotours`.`fareharbor_account` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(180) DEFAULT null,
  `key` VARCHAR(250) DEFAULT null,
  `active` INT DEFAULT null
);

CREATE TABLE `amigotours`.`front_footer` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(200) NOT NULL,
  `menu_type` VARCHAR(45) NOT NULL,
  `slug` VARCHAR(150) NOT NULL,
  `canonicalurl` VARCHAR(200) DEFAULT null
);

CREATE TABLE `amigotours`.`front_popular_attractions` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_tour` INT DEFAULT null,
  `position` ENUM ('PRIMARY', 'ONE_COLUM_FIRST', 'ONE_COLUM_SECOND', 'SECOND_COLUMN_FIRST', 'SECOND_COLUMN_SECOND') DEFAULT null,
  `raiting` INT DEFAULT null
);

CREATE TABLE `amigotours`.`front_popular_destinations` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_city` INT NOT NULL,
  `image` VARCHAR(180) DEFAULT null,
  `position` ENUM ('LEFT', 'RIGHT', 'TOP_CENTER', 'MIDDLE') DEFAULT null,
  `raiting` INT DEFAULT null
);

CREATE TABLE `amigotours`.`group_type` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT null,
  `min_pax` INT NOT NULL DEFAULT '0',
  `max_pax` INT NOT NULL DEFAULT '0',
  `active` TINYINT DEFAULT null
);

CREATE TABLE `amigotours`.`home_popup` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `image` VARCHAR(255) DEFAULT null,
  `date_from` DATE DEFAULT null,
  `date_to` DATE DEFAULT null,
  `active` INT DEFAULT null
);

CREATE TABLE `amigotours`.`icons` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) DEFAULT null,
  `name_en` VARCHAR(150) DEFAULT null,
  `type` VARCHAR(50) DEFAULT null,
  `type_en` VARCHAR(50) DEFAULT null,
  `icon` MEDIUMTEXT DEFAULT null,
  `active` TINYINT(1) DEFAULT '1'
);

CREATE TABLE `amigotours`.`tour_type` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `description` varchar(180) DEFAULT null
);

CREATE TABLE `amigotours`.`vendor_type` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `description` varchar(180) DEFAULT null
);

CREATE TABLE `amigotours`.`vendors` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` varchar(180) NOT NULL COMMENT 'Proveedor',
  `id_vendor_type` int,
  `active` bit,
  `date_updated` DATETIME NOT NULL COMMENT 'Fecha de actualizacion',
  `id_user_created` INT NOT NULL COMMENT 'Usuario que lo dio de alta',
  `date_created` DATETIME NOT NULL COMMENT 'Fecha de creacion',
  `last_user_updated` INT NOT NULL COMMENT 'Ultimo usuario que actualizo'
);

CREATE TABLE `amigotours`.`tour` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `date_updated` DATETIME NOT NULL COMMENT 'Fecha de actualización',
  `id_user_created` INT NOT NULL COMMENT 'Usuario que lo dio de alta',
  `date_created` DATETIME NOT NULL COMMENT 'Fecha de creación\\n',
  `last_user_updated` INT NOT NULL COMMENT 'Último usuario que actualizo',
  `id_contract` INT NOT NULL,
  `id_company_branch` INT NOT NULL,
  `active` INT NOT NULL DEFAULT '0',
  `id_city` INT NOT NULL,
  `order` INT DEFAULT null,
  `id_tour_type` int DEFAULT 1,
  `id_vendor` int,
  `is_free` INT DEFAULT null,
  `code` VARCHAR(100) DEFAULT null,
  `deleted` INT DEFAULT '0'
);

CREATE TABLE `amigotours`.`images` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `uri` VARCHAR(250) DEFAULT null,
  `caption` VARCHAR(250) DEFAULT null,
  `is_cover` TINYINT(1) DEFAULT null,
  `tour_id` INT NOT NULL COMMENT 'Tour'
);

CREATE TABLE `amigotours`.`languages` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(180) DEFAULT null,
  `name_en` VARCHAR(180) DEFAULT null,
  `code` VARCHAR(10) DEFAULT null
);

CREATE TABLE `amigotours`.`log` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `uri` VARCHAR(250) DEFAULT null,
  `method` VARCHAR(10) DEFAULT null,
  `request` TEXT DEFAULT null,
  `response` TEXT DEFAULT null,
  `key` VARCHAR(180) DEFAULT null,
  `code` INT DEFAULT null,
  `headers` TEXT DEFAULT null,
  `description` VARCHAR(180) DEFAULT null,
  `date_time` DATETIME DEFAULT null
);

CREATE TABLE `amigotours`.`phones` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `phone` VARCHAR(30) DEFAULT null,
  `country` VARCHAR(30) DEFAULT null,
  `clean` VARCHAR(30) DEFAULT null
);

CREATE TABLE `amigotours`.`pickups` (
  `id` VARCHAR(50) PRIMARY KEY NOT NULL,
  `id_en` VARCHAR(50) DEFAULT null,
  `name` VARCHAR(80) NOT NULL,
  `name_en` VARCHAR(80) DEFAULT null,
  `description` VARCHAR(150) DEFAULT null,
  `description_en` VARCHAR(150) DEFAULT null
);

CREATE TABLE `amigotours`.`stripe_payments` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `payment_intent_id` VARCHAR(255) NOT NULL,
  `status` VARCHAR(50) NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `currency` VARCHAR(10) NOT NULL,
  `payment_method` VARCHAR(50) DEFAULT null,
  `card_brand` VARCHAR(50) DEFAULT null,
  `card_last4` VARCHAR(4) DEFAULT null,
  `customer_id` VARCHAR(255) DEFAULT null,
  `description` TEXT DEFAULT null,
  `metadata` LONGTEXT DEFAULT null,
  `created_at` TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE `amigotours`.`subcategories` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NOT NULL,
  `name_en` VARCHAR(150) DEFAULT null,
  `slug` VARCHAR(160) DEFAULT null,
  `active` TINYINT NOT NULL DEFAULT '1',
  `id_category` INT NOT NULL
);

CREATE TABLE `amigotours`.`third_party_credentials` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_company` INT NOT NULL,
  `provider_name` VARCHAR(50) NOT NULL,
  `key_name` VARCHAR(50) NOT NULL,
  `key_value` TEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE `amigotours`.`tour_optional` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(180) DEFAULT null,
  `description` VARCHAR(180) DEFAULT null,
  `id_tour` INT NOT NULL,
  `is_private` TINYINT NOT NULL DEFAULT '0',
  `max_pax` INT NOT NULL DEFAULT '0',
  `optional_margin` INT DEFAULT null,
  `min_pax` INT NOT NULL DEFAULT '1',
  `duration` VARCHAR(11) DEFAULT null,
  `id_tour_duration_type` INT DEFAULT null,
  `active` INT DEFAULT '0',
  `id_tour_transfer_type` INT DEFAULT null,
  `tour_transfer_note` VARCHAR(180) DEFAULT null,
  `description_include` TEXT DEFAULT null,
  `description_not_included` TEXT DEFAULT null,
  `is_external` INT DEFAULT null,
  `rates_external` INT DEFAULT null,
  `date_updated` DATETIME NOT NULL COMMENT 'Fecha de actualizacion',
  `id_user_created` INT NOT NULL COMMENT 'Usuario que lo dio de alta',
  `date_created` DATETIME NOT NULL COMMENT 'Fecha de creacion',
  `last_user_updated` INT NOT NULL COMMENT 'Ultimo usuario que actualizo'
);

CREATE TABLE `amigotours`.`tour_availability` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_tour_optional` INT NOT NULL,
  `id_season` INT DEFAULT null,
  `day` INT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_availability_hours` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_season` INT NOT NULL,
  `hour` VARCHAR(11) DEFAULT null
);

CREATE TABLE `amigotours`.`tour_booking_status` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT null
);

CREATE TABLE `amigotours`.`tour_booking` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `date_created` DATETIME DEFAULT null,
  `email_sent` TINYINT(1) DEFAULT null,
  `id_tour_booking_status` INT DEFAULT null,
  `travel_date` DATE DEFAULT null,
  `id_optional` INT DEFAULT null,
  `country` VARCHAR(90) DEFAULT null,
  `key` VARCHAR(180) DEFAULT null,
  `confirmation` VARCHAR(30) DEFAULT null,
  `payment_status` VARCHAR(150) DEFAULT null,
  `stripe_id` VARCHAR(150) DEFAULT null
);

CREATE TABLE `amigotours`.`tour_cut_off` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT null,
  `value` FLOAT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_config` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_tour_cut_off` INT NOT NULL,
  `max_pax` INT NOT NULL DEFAULT '1',
  `on_request` INT DEFAULT '0',
  `is_private` TINYINT(1) DEFAULT null,
  `id_tour` INT NOT NULL,
  `free_cancelation` INT DEFAULT null,
  `skip_line_type` INT NOT NULL,
  `skip_line` INT NOT NULL,
  `use_system` INT DEFAULT '0',
  `date_updated` DATETIME NOT NULL COMMENT 'Fecha de actualizacion',
  `id_user_created` INT NOT NULL COMMENT 'Usuario que lo dio de alta',
  `date_created` DATETIME NOT NULL COMMENT 'Fecha de creacion',
  `last_user_updated` INT NOT NULL COMMENT 'Ultimo usuario que actualizo'
);

CREATE TABLE `amigotours`.`tour_pax_type` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) DEFAULT null,
  `name_en` VARCHAR(100) DEFAULT null,
  `id_language` INT DEFAULT null,
  `is_active` TINYINT(1) NOT NULL DEFAULT '1',
  `name_plural` VARCHAR(100) DEFAULT null,
  `name_plural_en` VARCHAR(100) DEFAULT null,
  `icon` VARCHAR(50) DEFAULT null
);

CREATE TABLE `amigotours`.`tour_config_pax` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `min_age` INT NOT NULL,
  `max_age` INT NOT NULL,
  `id_tour_config` INT NOT NULL,
  `id_pax_type` INT NOT NULL
);

CREATE TABLE `amigotours`.`tour_booking_extra` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `extra` VARCHAR(360) NOT NULL DEFAULT '',
  `pax` VARCHAR(360) NOT NULL DEFAULT '',
  `price` VARCHAR(100) NOT NULL DEFAULT '0',
  `type` VARCHAR(50) NOT NULL DEFAULT '',
  `id_tour_booking` INT NOT NULL DEFAULT '0',
  `id_tour` INT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_booking_lead` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `full_name` VARCHAR(180) DEFAULT null,
  `email` VARCHAR(180) DEFAULT null,
  `phone` VARCHAR(180) DEFAULT null,
  `id_tour_booking` INT NOT NULL,
  `name` VARCHAR(180) DEFAULT null,
  `last_name` VARCHAR(180) DEFAULT null
);

CREATE TABLE `amigotours`.`tour_booking_rates` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_pax_type` INT NOT NULL,
  `price` VARCHAR(180) DEFAULT null,
  `margin` INT NOT NULL,
  `currency` VARCHAR(20) DEFAULT null,
  `rate_exchange` VARCHAR(180) DEFAULT null,
  `id_tour_booking` INT NOT NULL,
  `pax_total` INT NOT NULL,
  `id_tour_season` INT DEFAULT null,
  `coupon` VARCHAR(20) DEFAULT null,
  `discount` INT DEFAULT null,
  `unpaid` VARCHAR(180) DEFAULT null
);

CREATE TABLE `amigotours`.`tour_category` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_tour` INT NOT NULL,
  `id_subcategory` INT NOT NULL
);

CREATE TABLE `amigotours`.`tour_close_dates` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_tour` INT NOT NULL,
  `close_date` DATE NOT NULL,
  `id_optional` INT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_coupons` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `coupon` VARCHAR(20) DEFAULT null,
  `start_date` DATE DEFAULT null,
  `end_date` DATE DEFAULT null,
  `discount` INT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_detail_languages` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_tour` INT DEFAULT null,
  `id_language` INT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_details` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(180) DEFAULT null,
  `name_en` VARCHAR(180) DEFAULT null,
  `short_description` TEXT DEFAULT null,
  `short_description_en` TEXT DEFAULT null,
  `description` TEXT DEFAULT null,
  `description_en` TEXT DEFAULT null,
  `FH_name` VARCHAR(180) DEFAULT null,
  `FH_code` VARCHAR(180) DEFAULT null,
  `itinerary` TEXT DEFAULT null,
  `itinerary_en` TEXT DEFAULT null,
  `include` TEXT DEFAULT null,
  `include_en` TEXT DEFAULT null,
  `policy_cancellation` TEXT DEFAULT null,
  `policy_cancellation_en` TEXT DEFAULT null,
  `restrictions` TEXT DEFAULT null,
  `restrictions_en` TEXT DEFAULT null,
  `tags` VARCHAR(250) DEFAULT null,
  `keywords` VARCHAR(250) DEFAULT null,
  `keywords_en` VARCHAR(250) DEFAULT null,
  `requirement` TEXT DEFAULT null,
  `requirement_en` TEXT DEFAULT null,
  `not_included` TEXT DEFAULT null,
  `not_included_en` TEXT DEFAULT null,
  `what_you_need` TEXT DEFAULT null,
  `what_you_need_en` TEXT DEFAULT null,
  `on_demand` TINYINT(1) DEFAULT null,
  `published` TINYINT(1) DEFAULT '0' COMMENT 'Publicado',
  `guide_included` TINYINT(1) DEFAULT null,
  `is_accessible` TINYINT(1) DEFAULT null,
  `id_language` INT DEFAULT null COMMENT 'Idioma',
  `id_tour` INT NOT NULL COMMENT 'Tour',
  `slug` VARCHAR(180) DEFAULT null,
  `slug_en` VARCHAR(180) DEFAULT null,
  `ribbon` VARCHAR(50) DEFAULT null,
  `reduction_rate` VARCHAR(50) DEFAULT null,
  `meeting_points` VARCHAR(50) DEFAULT null,
  `is_private` TINYINT(1) DEFAULT null,
  `instructions` TEXT DEFAULT null,
  `instructions_en` TEXT DEFAULT null,
  `additional_information` TEXT DEFAULT null,
  `additional_information_en` TEXT DEFAULT null,
  `free_cancellation` INT DEFAULT null,
  `free_cancellation_en` TEXT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_vendor_details` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_tour` int NOT NULL,
  `id_tour_optional` int NOT NULL,
  `tour_name_es` varchar(180) COMMENT 'nombre de tour en español como lo maneja ese proveedor',
  `tour_name_en` varchar(180) COMMENT 'nombre de tour en ingles como lo maneja ese proveedor',
  `tour_code` varchar(180)
);

CREATE TABLE `amigotours`.`tour_duration_type` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) DEFAULT null,
  `name_en` VARCHAR(150) DEFAULT null,
  `singular` VARCHAR(150) DEFAULT null,
  `singular_en` VARCHAR(150) DEFAULT null,
  `factor` INT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_external` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `item_id` VARCHAR(280) NOT NULL DEFAULT '0',
  `image` TEXT NOT NULL,
  `name` TEXT NOT NULL,
  `description` TEXT NOT NULL,
  `id_tour` INT NOT NULL DEFAULT '0',
  `channel` VARCHAR(50) DEFAULT null,
  `id_availability` INT DEFAULT null,
  `currency` VARCHAR(50) DEFAULT null,
  `company_name` VARCHAR(250) NOT NULL DEFAULT '',
  `company` VARCHAR(250) NOT NULL DEFAULT ''
);

CREATE TABLE `amigotours`.`tour_external_minimal` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `item_id` VARCHAR(50) DEFAULT null,
  `price` VARCHAR(50) DEFAULT null,
  `tour_id` INT DEFAULT null,
  `last_update` DATETIME DEFAULT null,
  `currency` VARCHAR(50) DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_attractions` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `attraction_name` VARCHAR(150) NOT NULL DEFAULT '0',
  `attraction_name_en` VARCHAR(150) DEFAULT null,
  `description` VARCHAR(250) NOT NULL DEFAULT '0',
  `description_en` VARCHAR(250) DEFAULT null,
  `pax_type` VARCHAR(250) NOT NULL DEFAULT '0',
  `extra_attraction` VARCHAR(250) NOT NULL DEFAULT '0',
  `extra_pax_type` VARCHAR(250) NOT NULL DEFAULT '0',
  `margin` INT DEFAULT '0',
  `extra_margin` VARCHAR(250) DEFAULT '0',
  `id_optional` INT NOT NULL DEFAULT '0',
  `id_season` INT DEFAULT null,
  `duration` VARCHAR(11) DEFAULT null,
  `id_tour_duration_type` INT DEFAULT null,
  `id_icon` INT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_attractions_exceptions` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_extra_attractions` INT DEFAULT null,
  `exception_date` DATE DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_attractions_tickets` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `ticket_type` VARCHAR(80) NOT NULL,
  `ticket_type_en` VARCHAR(80) DEFAULT null,
  `pax_type` INT NOT NULL,
  `extra_pax_type` INT NOT NULL DEFAULT '0',
  `id_tour_extra_attractions` INT NOT NULL DEFAULT '0',
  `id_currency` INT DEFAULT null,
  `id_icon` INT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_custom` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(250) NOT NULL DEFAULT '0',
  `name_en` VARCHAR(250) DEFAULT null,
  `extra` VARCHAR(50) NOT NULL DEFAULT '0',
  `min_pax` INT NOT NULL DEFAULT '0',
  `max_pax` INT NOT NULL DEFAULT '0',
  `id_optional` INT NOT NULL DEFAULT '0',
  `id_season` INT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_custom_exceptions` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_extra_custom` INT DEFAULT null,
  `exception_date` DATE DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_duration` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_duration` INT NOT NULL DEFAULT '0',
  `id_optional` INT NOT NULL DEFAULT '0',
  `id_season` INT DEFAULT null,
  `extra` VARCHAR(50) NOT NULL DEFAULT '0'
);

CREATE TABLE `amigotours`.`tour_extra_duration_exceptions` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_extra_duration` INT DEFAULT null,
  `exception_date` DATE DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_equipment` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_optional` INT NOT NULL DEFAULT '0',
  `id_season` INT DEFAULT null,
  `item_name` VARCHAR(250) NOT NULL DEFAULT '0',
  `item_name_en` VARCHAR(250) DEFAULT null,
  `quantity` INT NOT NULL DEFAULT '0',
  `extra` VARCHAR(250) NOT NULL DEFAULT '0',
  `is_shared` INT NOT NULL DEFAULT '0',
  `max_shared` INT NOT NULL DEFAULT '0'
);

CREATE TABLE `amigotours`.`tour_extra_equipment_exceptions` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_extra_equipment` INT DEFAULT null,
  `exception_date` DATE DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_food` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_extra` INT DEFAULT '0',
  `num_pax` INT DEFAULT '0',
  `extra` VARCHAR(50) DEFAULT null,
  `id_optional` INT DEFAULT null,
  `id_season` INT DEFAULT null,
  `description` VARCHAR(350) DEFAULT null,
  `description_en` VARCHAR(350) DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_food_exceptions` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_extra_food` INT DEFAULT null,
  `exception_date` DATE DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_guide` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_type_guide` INT NOT NULL DEFAULT '0',
  `language` INT NOT NULL DEFAULT '0',
  `extra` VARCHAR(50) NOT NULL DEFAULT '0',
  `id_optional` INT NOT NULL DEFAULT '0',
  `id_season` INT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_guide_exceptions` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_extra_guide` INT DEFAULT null,
  `exception_date` DATE DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_language` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_language` INT NOT NULL DEFAULT '0',
  `extra` VARCHAR(50) NOT NULL DEFAULT '0',
  `id_optional` INT NOT NULL DEFAULT '0',
  `id_season` INT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_language_exceptions` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_extra_language` INT DEFAULT null,
  `exception_date` DATE DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_meeting` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `address` TEXT NOT NULL,
  `extra` VARCHAR(50) NOT NULL DEFAULT '0',
  `pickup` VARCHAR(350) NOT NULL DEFAULT '0',
  `extra_pickup` VARCHAR(350) NOT NULL DEFAULT '0',
  `latitud` VARCHAR(150) NOT NULL DEFAULT '0',
  `longitude` VARCHAR(150) NOT NULL DEFAULT '0',
  `id_optional` INT DEFAULT null,
  `id_season` INT DEFAULT null,
  `pickup_note` VARCHAR(250) DEFAULT null,
  `pickup_note_en` VARCHAR(250) DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_meeting_exceptions` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_extra_meeting` INT DEFAULT null,
  `exception_date` DATE DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extra_vehicle` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_vehicle_type` INT NOT NULL DEFAULT '0',
  `extra` VARCHAR(50) NOT NULL DEFAULT '0',
  `id_vehicle_option` INT NOT NULL DEFAULT '0',
  `id_optional` INT NOT NULL DEFAULT '0',
  `id_season` INT DEFAULT null,
  `extra_vehicle_option` VARCHAR(50) NOT NULL DEFAULT '0'
);

CREATE TABLE `amigotours`.`tour_extra_vehicle_exceptions` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_extra_vehicle` INT DEFAULT null,
  `exception_date` DATE DEFAULT null
);

CREATE TABLE `amigotours`.`tour_extras` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NOT NULL DEFAULT '',
  `name_eng` VARCHAR(150) NOT NULL DEFAULT '',
  `type` VARCHAR(150) NOT NULL DEFAULT '',
  `active` TINYINT NOT NULL DEFAULT '0'
);

CREATE TABLE `amigotours`.`tour_images` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `image_date` VARCHAR(150) NOT NULL DEFAULT '0',
  `file_name` VARCHAR(150) NOT NULL DEFAULT '0',
  `guid` VARCHAR(150) NOT NULL DEFAULT '0',
  `caption` VARCHAR(350) NOT NULL DEFAULT '',
  `caption_en` VARCHAR(350) DEFAULT null,
  `id_tour` INT NOT NULL DEFAULT '0',
  `is_cover` TINYINT NOT NULL DEFAULT '0',
  `order` INT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_itinerary` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `stop_name` VARCHAR(280) NOT NULL DEFAULT '0',
  `stop_name_en` VARCHAR(280) DEFAULT null,
  `description` VARCHAR(512) DEFAULT null,
  `description_en` VARCHAR(512) DEFAULT null,
  `tags` TEXT DEFAULT null,
  `address` TEXT DEFAULT null,
  `duration` VARCHAR(50) NOT NULL DEFAULT '',
  `id_tour_duration_type` INT DEFAULT null,
  `latitude` VARCHAR(50) NOT NULL DEFAULT '',
  `longitude` VARCHAR(50) NOT NULL DEFAULT '',
  `order` INT NOT NULL DEFAULT '0',
  `id_tour` INT NOT NULL,
  `date_updated` DATETIME NOT NULL COMMENT 'Fecha de actualizacion',
  `id_user_created` INT NOT NULL COMMENT 'Usuario que lo dio de alta',
  `date_created` DATETIME NOT NULL COMMENT 'Fecha de creacion',
  `last_user_updated` INT NOT NULL COMMENT 'Ultimo usuario que actualizo'
);

CREATE TABLE `amigotours`.`tour_meeting_points` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `lat` VARCHAR(50) NOT NULL DEFAULT '',
  `lon` VARCHAR(50) NOT NULL DEFAULT '',
  `address` VARCHAR(250) NOT NULL DEFAULT '',
  `id_tour` INT NOT NULL DEFAULT '0',
  `pickup` VARCHAR(50) NOT NULL DEFAULT '0',
  `pickup_note` VARCHAR(250) DEFAULT null,
  `pickup_note_en` VARCHAR(250) DEFAULT null
);

CREATE TABLE `amigotours`.`tour_metadata` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_tour` INT DEFAULT null,
  `min_booking` INT DEFAULT null,
  `max_booking` INT DEFAULT null
);

CREATE TABLE `amigotours`.`tour_optional_extra` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(180) NOT NULL DEFAULT '',
  `name_eng` VARCHAR(180) NOT NULL DEFAULT '',
  `active` TINYINT NOT NULL DEFAULT '1',
  `name_key` VARCHAR(180) NOT NULL DEFAULT ''
);

CREATE TABLE `amigotours`.`tour_rates` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_optional` INT NOT NULL,
  `id_pax_type` INT NOT NULL,
  `price` VARCHAR(50) DEFAULT null,
  `id_tour_season` INT NOT NULL,
  `id_currency` INT NOT NULL
);

CREATE TABLE `amigotours`.`tour_rates_group` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_tour_rates` INT NOT NULL,
  `num_paxes` INT NOT NULL,
  `price` VARCHAR(50) DEFAULT null,
  `id_currency` INT NOT NULL
);

CREATE TABLE `amigotours`.`tour_review` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_tour` INT NOT NULL,
  `raiting` INT NOT NULL,
  `comment` VARCHAR(250) DEFAULT null,
  `client_name` VARCHAR(255) DEFAULT null,
  `country` VARCHAR(11) DEFAULT null,
  `approved` INT DEFAULT null,
  `confirmation` VARCHAR(80) DEFAULT null,
  `date_created` DATE DEFAULT null
);

CREATE TABLE `amigotours`.`tour_season` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(180) DEFAULT null,
  `name_en` VARCHAR(180) DEFAULT null,
  `start_on` DATE NOT NULL,
  `end_on` DATE NOT NULL,
  `id_tour_optional` INT NOT NULL,
  `active` TINYINT DEFAULT '1',
  `deleted` TINYINT DEFAULT '0'
);

CREATE TABLE `amigotours`.`travel_companion` (
  `ID` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT null
);

CREATE TABLE `amigotours`.`user_refresh_tokens` (
  `user_refresh_tokenID` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `urf_userID` INT NOT NULL,
  `urf_token` VARCHAR(1000) DEFAULT null,
  `urf_ip` VARCHAR(50) DEFAULT null,
  `urf_user_agent` VARCHAR(1000) DEFAULT null,
  `urf_created` DATETIME NOT NULL COMMENT 'UTC'
);

CREATE TABLE `amigotours`.`widgets` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) DEFAULT null,
  `description` VARCHAR(150) DEFAULT null,
  `script` TEXT DEFAULT null,
  `active` TINYINT DEFAULT '1',
  `id_company` INT DEFAULT null
);

CREATE TABLE `amigotours`.`widget_config` (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `id_widget` INT NOT NULL,
  `parameter` VARCHAR(100) DEFAULT null,
  `value` VARCHAR(255) DEFAULT null,
  `type` VARCHAR(45) DEFAULT null
);

CREATE TABLE `amigotours`.`orders` (
  `id` BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(180) NOT NULL,
  `email` VARCHAR(180) NOT NULL,
  `phone` VARCHAR(32) DEFAULT null,
  `pax` INT NOT NULL,
  `price_per_person_value` INT NOT NULL DEFAULT '0',
  `amount_value` INT NOT NULL DEFAULT '0',
  `tax_value` INT NOT NULL DEFAULT '0',
  `tip_percent` TINYINT NOT NULL DEFAULT '0',
  `tip_value` INT NOT NULL DEFAULT '0',
  `total_value` INT NOT NULL,
  `currency` CHAR(3) NOT NULL,
  `booking_id` VARCHAR(30) DEFAULT null,
  `capture_mode` ENUM ('automatic', 'manual') NOT NULL DEFAULT 'automatic',
  `merchant_order_ext_ref` VARCHAR(100) NOT NULL,
  `description` VARCHAR(255) DEFAULT null,
  `checkout_token` CHAR(36) NOT NULL,
  `revolut_order_id` CHAR(36) DEFAULT null,
  `revolut_public_id` CHAR(36) DEFAULT null,
  `user_refresh_tokenID` INT NOT NULL,
  `revolut_state` ENUM ('PENDING', 'AUTHORISED', 'COMPLETED', 'FAILED', 'CANCELLED') NOT NULL DEFAULT 'PENDING',
  `status` ENUM ('CREATED', 'PAID', 'FAILED', 'CANCELLED') NOT NULL DEFAULT 'CREATED',
  `webhook_last_event_id` BIGINT DEFAULT null,
  `created_at` DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `updated_at` DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `paid_at` DATETIME DEFAULT null
);

CREATE TABLE `amigotours`.`webhook_events` (
  `id` BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `event_id` VARCHAR(100) NOT NULL,
  `event_type` VARCHAR(80) NOT NULL,
  `revolut_order_id` VARCHAR(64) DEFAULT null,
  `revolut_public_id` VARCHAR(64) DEFAULT null,
  `state` VARCHAR(32) DEFAULT null,
  `amount_value` INT DEFAULT null,
  `currency` CHAR(3) DEFAULT null,
  `sig_header` VARCHAR(512) DEFAULT null,
  `received_at` DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  `processed_at` DATETIME DEFAULT null,
  `process_status` ENUM ('PENDING', 'PROCESSED', 'SKIPPED', 'ERROR') NOT NULL DEFAULT 'PENDING',
  `process_error` VARCHAR(500) DEFAULT null,
  `raw_json` LONGTEXT NOT NULL
);

CREATE TABLE `amigotours`.`taxes` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  `description` varchar(180),
  `value` varchar(20),
  `id_tax_type` int
);

CREATE TABLE `amigotours`.`tour_optional_taxes` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  `description` varchar(180),
  `id_tour_optional` int,
  `id_tax` int
);

CREATE INDEX `country_continent_FK` ON `amigotours`.`country` (`id_continent`);

CREATE INDEX `fk_region_1_idx` ON `amigotours`.`region` (`id_country`);

CREATE INDEX `fk_city_1_idx` ON `amigotours`.`city` (`id_region`);

CREATE INDEX `fk_attractions_city_idx` ON `amigotours`.`attractions` (`id_city`);

CREATE INDEX `fk_attractions_statuspost1_idx` ON `amigotours`.`attractions` (`id_statuspost`);

CREATE INDEX `attractions_alternatives_idx_1` ON `amigotours`.`attractions_alternatives` (`id_attractions`);

CREATE INDEX `attractions_alternatives_idx_2` ON `amigotours`.`attractions_alternatives` (`id_city`);

CREATE INDEX `fk_attractions_section_attractions1_idx` ON `amigotours`.`attractions_section` (`id_attractions`);

CREATE INDEX `fk_attractions_seo_attractions_section1_idx` ON `amigotours`.`attractions_seo` (`id_attractions_section`);

CREATE INDEX `fk_b_c_0` ON `amigotours`.`bank_settings` (`id_company`) USING BTREE;

CREATE INDEX `fk_city_summary_city_idx` ON `amigotours`.`city_summary` (`id_city`);

CREATE INDEX `fk_city_summary_statuspost1_idx` ON `amigotours`.`city_summary` (`id_statuspost`);

CREATE INDEX `fk_city_summary_section_city_summary1_idx` ON `amigotours`.`city_summary_section` (`id_city_summary`);

CREATE INDEX `users_fk0` ON `amigotours`.`users` (`id_company`);

CREATE INDEX `users_fk3` ON `amigotours`.`users` (`id_company_branch`);

CREATE INDEX `user_fk_1` ON `amigotours`.`users` (`id_user_type`);

CREATE INDEX `user_fk_2` ON `amigotours`.`users` (`id_user_position`);

CREATE INDEX `fk_c_1_idx` ON `amigotours`.`comments` (`id_booking`);

CREATE INDEX `fc_c_2_idx` ON `amigotours`.`comments` (`id_user_approved`);

CREATE INDEX `fk_c_c_0` ON `amigotours`.`company_contact` (`id_company`) USING BTREE;

CREATE INDEX `fk_c_0` ON `amigotours`.`contract` (`id_company`);

CREATE INDEX `fk_country_summary_country_idx` ON `amigotours`.`country_summary` (`id_country`);

CREATE INDEX `fk_country_summary_statuspost1_idx` ON `amigotours`.`country_summary` (`id_statuspost`);

CREATE INDEX `fk_country_summary_section_country_summary1_idx` ON `amigotours`.`country_summary_section` (`id_country_summary`);

CREATE INDEX `fk_e_0` ON `amigotours`.`currency_rate_exchange` (`id_currency_base`) USING BTREE;

CREATE INDEX `fk_e_1` ON `amigotours`.`currency_rate_exchange` (`id_currency_to`) USING BTREE;

CREATE INDEX `fk_destination_guides_city_idx` ON `amigotours`.`destination_guides` (`id_city`);

CREATE INDEX `fk_destination_guides_statuspost1_idx` ON `amigotours`.`destination_guides` (`id_statuspost`);

CREATE INDEX `fk_destination_guides_section_destination_guides1_idx` ON `amigotours`.`destination_guides_section` (`id_destination_guides`);

CREATE INDEX `fk_destination_guides_seo_destination_guides_section1_idx` ON `amigotours`.`destination_guides_seo` (`id_destination_guides_section`);

CREATE UNIQUE INDEX `currency_code` ON `amigotours`.`exchange_rates` (`currency_code`);

CREATE INDEX `fk_f_c_0` ON `amigotours`.`front_popular_destinations` (`id_city`) USING BTREE;

CREATE INDEX `vendors_fk0` ON `amigotours`.`vendors` (`id_vendor_type`);

CREATE INDEX `tour_fk0` ON `amigotours`.`tour` (`id_user_created`);

CREATE INDEX `tour_fk1` ON `amigotours`.`tour` (`last_user_updated`);

CREATE INDEX `tour_fk2` ON `amigotours`.`tour` (`id_contract`) USING BTREE;

CREATE INDEX `tour_fk3` ON `amigotours`.`tour` (`id_city`) USING BTREE;

CREATE INDEX `tour_fk4` ON `amigotours`.`tour` (`id_company_branch`) USING BTREE;

CREATE INDEX `tour_fk5` ON `amigotours`.`tour` (`id_vendor`) USING BTREE;

CREATE INDEX `tour_fk6` ON `amigotours`.`tour` (`id_tour_type`) USING BTREE;

CREATE INDEX `images_fk0` ON `amigotours`.`images` (`tour_id`);

CREATE INDEX `fk_scat_1` ON `amigotours`.`subcategories` (`id_category`);

CREATE UNIQUE INDEX `id_company` ON `amigotours`.`third_party_credentials` (`id_company`, `provider_name`, `key_name`);

CREATE INDEX `fk_to_0` ON `amigotours`.`tour_optional` (`id_tour`);

CREATE INDEX `fk_a_o_0` ON `amigotours`.`tour_availability` (`id_tour_optional`);

CREATE INDEX `fk_bs_0` ON `amigotours`.`tour_booking` (`id_tour_booking_status`) USING BTREE;

CREATE INDEX `fk_bs_2` ON `amigotours`.`tour_booking` (`id_optional`) USING BTREE;

CREATE INDEX `fk_cu_0` ON `amigotours`.`tour_config` (`id_tour_cut_off`);

CREATE INDEX `fk_bc_t_1` ON `amigotours`.`tour_config` (`id_tour`);

CREATE INDEX `fk_l_0` ON `amigotours`.`tour_pax_type` (`id_language`);

CREATE INDEX `fk_pt_0` ON `amigotours`.`tour_config_pax` (`id_tour_config`);

CREATE INDEX `fk_pt_1` ON `amigotours`.`tour_config_pax` (`id_pax_type`);

CREATE INDEX `fk_bl_0` ON `amigotours`.`tour_booking_lead` (`id_tour_booking`) USING BTREE;

CREATE INDEX `fk_tbr_0` ON `amigotours`.`tour_booking_rates` (`id_pax_type`) USING BTREE;

CREATE INDEX `fk_tbr_2` ON `amigotours`.`tour_booking_rates` (`id_tour_booking`) USING BTREE;

CREATE INDEX `fk_tcat_0` ON `amigotours`.`tour_category` (`id_tour`);

CREATE INDEX `fk_tcat_1_idx` ON `amigotours`.`tour_category` (`id_subcategory`);

CREATE INDEX `fk_cd_0` ON `amigotours`.`tour_close_dates` (`id_tour`) USING BTREE;

CREATE INDEX `fk_cd_1` ON `amigotours`.`tour_close_dates` (`id_optional`) USING BTREE;

CREATE INDEX `fk_tdl_0` ON `amigotours`.`tour_detail_languages` (`id_tour`);

CREATE INDEX `fk_tdl_1` ON `amigotours`.`tour_detail_languages` (`id_language`);

CREATE INDEX `fk_tour_details_2_idx` ON `amigotours`.`tour_details` (`id_language`);

CREATE INDEX `fk_tour_details_1_idx` ON `amigotours`.`tour_details` (`id_tour`);

CREATE INDEX `tour_vendor_details_idx1` ON `amigotours`.`tour_vendor_details` (`id_tour`);

CREATE INDEX `fk_tea1` ON `amigotours`.`tour_extra_attractions` (`id_icon`);

CREATE INDEX `fk_teat_0` ON `amigotours`.`tour_extra_attractions_tickets` (`pax_type`);

CREATE INDEX `fk_teat_1` ON `amigotours`.`tour_extra_attractions_tickets` (`id_currency`);

CREATE INDEX `fk_teat1` ON `amigotours`.`tour_extra_attractions_tickets` (`id_icon`);

CREATE INDEX `tour_extra_language_i_idx1` ON `amigotours`.`tour_extra_language` (`id_language`);

CREATE INDEX `idx_tel1` ON `amigotours`.`tour_extra_language_exceptions` (`id_extra_language`);

CREATE INDEX `tour_i_fk1` ON `amigotours`.`tour_itinerary` (`id_tour_duration_type`);

CREATE INDEX `tour_i_fk2` ON `amigotours`.`tour_itinerary` (`id_tour`);

CREATE INDEX `fk_trg_0` ON `amigotours`.`tour_rates_group` (`id_tour_rates`);

CREATE INDEX `fk_tr_0` ON `amigotours`.`tour_review` (`id_tour`) USING BTREE;

CREATE INDEX `fk_s_to_0` ON `amigotours`.`tour_season` (`id_tour_optional`);

CREATE INDEX `idx_widget` ON `amigotours`.`widget_config` (`id_widget`);

CREATE UNIQUE INDEX `uk_merchant_order_ext_ref` ON `amigotours`.`orders` (`merchant_order_ext_ref`);

CREATE UNIQUE INDEX `uk_revolut_public_id` ON `amigotours`.`orders` (`revolut_public_id`);

CREATE INDEX `idx_email_created` ON `amigotours`.`orders` (`email`, `created_at`);

CREATE INDEX `idx_revolut_state` ON `amigotours`.`orders` (`revolut_state`);

CREATE INDEX `idx_user_refresh_tokenID` ON `amigotours`.`orders` (`user_refresh_tokenID`);

CREATE UNIQUE INDEX `uk_event_id` ON `amigotours`.`webhook_events` (`event_id`);

CREATE INDEX `idx_revolut_public_id` ON `amigotours`.`webhook_events` (`revolut_public_id`);

CREATE INDEX `idx_event_type` ON `amigotours`.`webhook_events` (`event_type`);

CREATE INDEX `idx_received_at` ON `amigotours`.`webhook_events` (`received_at`);

CREATE INDEX `taxes_fk1` ON `amigotours`.`taxes` (`id_tax_type`);

CREATE INDEX `tour_optional_taxes_fk1` ON `amigotours`.`tour_optional_taxes` (`id_tour_optional`);

CREATE INDEX `tour_optional_taxes_fk2` ON `amigotours`.`tour_optional_taxes` (`id_tax`);

ALTER TABLE `amigotours`.`user_refresh_tokens` COMMENT = 'For JWT authentication process';

ALTER TABLE `amigotours`.`country` ADD CONSTRAINT `country_continent_FK` FOREIGN KEY (`id_continent`) REFERENCES `amigotours`.`continent` (`id`);

ALTER TABLE `amigotours`.`region` ADD CONSTRAINT `FK1_rc_id` FOREIGN KEY (`id_country`) REFERENCES `amigotours`.`country` (`id`);

ALTER TABLE `amigotours`.`city` ADD CONSTRAINT `FK1_region_id` FOREIGN KEY (`id_region`) REFERENCES `amigotours`.`region` (`id`);

ALTER TABLE `amigotours`.`attractions` ADD CONSTRAINT `fk_attractions_city` FOREIGN KEY (`id_city`) REFERENCES `amigotours`.`city` (`id`);

ALTER TABLE `amigotours`.`attractions` ADD CONSTRAINT `fk_attractions_statuspost1` FOREIGN KEY (`id_statuspost`) REFERENCES `amigotours`.`statuspost` (`id`);

ALTER TABLE `amigotours`.`attractions_alternatives` ADD CONSTRAINT `attractions_alternatives_fk_1` FOREIGN KEY (`id_attractions`) REFERENCES `amigotours`.`attractions` (`id`);

ALTER TABLE `amigotours`.`attractions_alternatives` ADD CONSTRAINT `attractions_alternatives_fk_2` FOREIGN KEY (`id_city`) REFERENCES `amigotours`.`city` (`id`);

ALTER TABLE `amigotours`.`attractions_section` ADD CONSTRAINT `fk_attractions_section_attractions1` FOREIGN KEY (`id_attractions`) REFERENCES `amigotours`.`attractions` (`id`);

ALTER TABLE `amigotours`.`attractions_seo` ADD CONSTRAINT `fk_attractions_seo_attractions_section1` FOREIGN KEY (`id_attractions_section`) REFERENCES `amigotours`.`attractions_section` (`id`);

ALTER TABLE `amigotours`.`bank_settings` ADD CONSTRAINT `fk_b_c_0` FOREIGN KEY (`id_company`) REFERENCES `amigotours`.`company` (`id`);

ALTER TABLE `amigotours`.`city_summary` ADD CONSTRAINT `fk_city_summary_city` FOREIGN KEY (`id_city`) REFERENCES `amigotours`.`city` (`id`);

ALTER TABLE `amigotours`.`city_summary` ADD CONSTRAINT `fk_city_summary_statuspost1` FOREIGN KEY (`id_statuspost`) REFERENCES `amigotours`.`statuspost` (`id`);

ALTER TABLE `amigotours`.`city_summary_section` ADD CONSTRAINT `fk_city_summary_section_city_summary1` FOREIGN KEY (`id_city_summary`) REFERENCES `amigotours`.`city_summary` (`id`);

ALTER TABLE `amigotours`.`users` ADD CONSTRAINT `user_fk_1` FOREIGN KEY (`id_user_type`) REFERENCES `amigotours`.`users_type` (`id`);

ALTER TABLE `amigotours`.`users` ADD CONSTRAINT `users_fk0` FOREIGN KEY (`id_company`) REFERENCES `amigotours`.`company` (`id`);

ALTER TABLE `amigotours`.`comments` ADD CONSTRAINT `fc_c_2` FOREIGN KEY (`id_user_approved`) REFERENCES `amigotours`.`users` (`id`);

ALTER TABLE `amigotours`.`comments` ADD CONSTRAINT `fk_c_1` FOREIGN KEY (`id_booking`) REFERENCES `amigotours`.`booking` (`id`);

ALTER TABLE `amigotours`.`company_contact` ADD CONSTRAINT `fk_c_c_0` FOREIGN KEY (`id_company`) REFERENCES `amigotours`.`company` (`id`);

ALTER TABLE `amigotours`.`contract` ADD CONSTRAINT `fk_c_0` FOREIGN KEY (`id_company`) REFERENCES `amigotours`.`company` (`id`);

ALTER TABLE `amigotours`.`country_summary` ADD CONSTRAINT `fk_country_summary_country` FOREIGN KEY (`id_country`) REFERENCES `amigotours`.`country` (`id`);

ALTER TABLE `amigotours`.`country_summary` ADD CONSTRAINT `fk_country_summary_statuspost1` FOREIGN KEY (`id_statuspost`) REFERENCES `amigotours`.`statuspost` (`id`);

ALTER TABLE `amigotours`.`country_summary_section` ADD CONSTRAINT `fk_country_summary_section_country_summary1` FOREIGN KEY (`id_country_summary`) REFERENCES `amigotours`.`country_summary` (`id`);

ALTER TABLE `amigotours`.`currency_rate_exchange` ADD CONSTRAINT `fk_e_0` FOREIGN KEY (`id_currency_base`) REFERENCES `amigotours`.`currency` (`id`);

ALTER TABLE `amigotours`.`currency_rate_exchange` ADD CONSTRAINT `fk_e_1` FOREIGN KEY (`id_currency_to`) REFERENCES `amigotours`.`currency` (`id`);

ALTER TABLE `amigotours`.`destination_guides` ADD CONSTRAINT `fk_destination_guides_city` FOREIGN KEY (`id_city`) REFERENCES `amigotours`.`city` (`id`);

ALTER TABLE `amigotours`.`destination_guides` ADD CONSTRAINT `fk_destination_guides_statuspost1` FOREIGN KEY (`id_statuspost`) REFERENCES `amigotours`.`statuspost` (`id`);

ALTER TABLE `amigotours`.`destination_guides_section` ADD CONSTRAINT `fk_destination_guides_section_destination_guides1` FOREIGN KEY (`id_destination_guides`) REFERENCES `amigotours`.`destination_guides` (`id`);

ALTER TABLE `amigotours`.`destination_guides_seo` ADD CONSTRAINT `fk_destination_guides_seo_destination_guides_section1` FOREIGN KEY (`id_destination_guides_section`) REFERENCES `amigotours`.`destination_guides_section` (`id`);

ALTER TABLE `amigotours`.`tour` ADD CONSTRAINT `tour_fk0` FOREIGN KEY (`id_user_created`) REFERENCES `amigotours`.`users` (`id`);

ALTER TABLE `amigotours`.`tour` ADD CONSTRAINT `tour_fk1` FOREIGN KEY (`last_user_updated`) REFERENCES `amigotours`.`users` (`id`);

ALTER TABLE `amigotours`.`tour` ADD CONSTRAINT `tour_fk2` FOREIGN KEY (`id_contract`) REFERENCES `amigotours`.`contract` (`id`);

ALTER TABLE `amigotours`.`images` ADD CONSTRAINT `images_fk0` FOREIGN KEY (`tour_id`) REFERENCES `amigotours`.`tour` (`id`);

ALTER TABLE `amigotours`.`subcategories` ADD CONSTRAINT `fk_scat_1` FOREIGN KEY (`id_category`) REFERENCES `amigotours`.`categories` (`id`);

ALTER TABLE `amigotours`.`tour_optional` ADD CONSTRAINT `fk_to_0` FOREIGN KEY (`id_tour`) REFERENCES `amigotours`.`tour` (`id`);

ALTER TABLE `amigotours`.`tour_availability` ADD CONSTRAINT `fk_a_o_0` FOREIGN KEY (`id_tour_optional`) REFERENCES `amigotours`.`tour_optional` (`id`);

ALTER TABLE `amigotours`.`tour_booking` ADD CONSTRAINT `fk_bs_0` FOREIGN KEY (`id_tour_booking_status`) REFERENCES `amigotours`.`tour_booking_status` (`id`);

ALTER TABLE `amigotours`.`tour_booking` ADD CONSTRAINT `fk_bs_2` FOREIGN KEY (`id_optional`) REFERENCES `amigotours`.`tour_optional` (`id`);

ALTER TABLE `amigotours`.`tour_config` ADD CONSTRAINT `fk_bc_t_1` FOREIGN KEY (`id_tour`) REFERENCES `amigotours`.`tour` (`id`);

ALTER TABLE `amigotours`.`tour_config` ADD CONSTRAINT `fk_cu_0` FOREIGN KEY (`id_tour_cut_off`) REFERENCES `amigotours`.`tour_cut_off` (`id`);

ALTER TABLE `amigotours`.`tour_pax_type` ADD CONSTRAINT `fk_l_0` FOREIGN KEY (`id_language`) REFERENCES `amigotours`.`languages` (`id`);

ALTER TABLE `amigotours`.`tour_config_pax` ADD CONSTRAINT `fk_pt_0` FOREIGN KEY (`id_tour_config`) REFERENCES `amigotours`.`tour_config` (`id`);

ALTER TABLE `amigotours`.`tour_config_pax` ADD CONSTRAINT `fk_pt_1` FOREIGN KEY (`id_pax_type`) REFERENCES `amigotours`.`tour_pax_type` (`id`);

ALTER TABLE `amigotours`.`tour_booking_lead` ADD CONSTRAINT `fk_bl_0` FOREIGN KEY (`id_tour_booking`) REFERENCES `amigotours`.`tour_booking` (`id`);

ALTER TABLE `amigotours`.`tour_booking_rates` ADD CONSTRAINT `fk_tbr_0` FOREIGN KEY (`id_pax_type`) REFERENCES `amigotours`.`tour_pax_type` (`id`);

ALTER TABLE `amigotours`.`tour_booking_rates` ADD CONSTRAINT `fk_tbr_2` FOREIGN KEY (`id_tour_booking`) REFERENCES `amigotours`.`tour_booking` (`id`);

ALTER TABLE `amigotours`.`tour_category` ADD CONSTRAINT `fk_tcat_0` FOREIGN KEY (`id_tour`) REFERENCES `amigotours`.`tour` (`id`);

ALTER TABLE `amigotours`.`tour_category` ADD CONSTRAINT `fk_tcat_1` FOREIGN KEY (`id_subcategory`) REFERENCES `amigotours`.`subcategories` (`id`);

ALTER TABLE `amigotours`.`tour_close_dates` ADD CONSTRAINT `fk_cd_0` FOREIGN KEY (`id_tour`) REFERENCES `amigotours`.`tour` (`id`);

ALTER TABLE `amigotours`.`tour_close_dates` ADD CONSTRAINT `fk_cd_1` FOREIGN KEY (`id_optional`) REFERENCES `amigotours`.`tour_optional` (`id`);

ALTER TABLE `amigotours`.`tour_detail_languages` ADD CONSTRAINT `fk_tdl_0` FOREIGN KEY (`id_tour`) REFERENCES `amigotours`.`tour` (`id`);

ALTER TABLE `amigotours`.`tour_detail_languages` ADD CONSTRAINT `fk_tdl_1` FOREIGN KEY (`id_language`) REFERENCES `amigotours`.`languages` (`id`);

ALTER TABLE `amigotours`.`tour_details` ADD CONSTRAINT `fk_tour_details_1` FOREIGN KEY (`id_tour`) REFERENCES `amigotours`.`tour` (`id`);

ALTER TABLE `amigotours`.`tour_details` ADD CONSTRAINT `fk_tour_details_2` FOREIGN KEY (`id_language`) REFERENCES `amigotours`.`languages` (`id`);

ALTER TABLE `amigotours`.`tour_extra_attractions` ADD CONSTRAINT `fk_tea1` FOREIGN KEY (`id_icon`) REFERENCES `amigotours`.`icons` (`id`);

ALTER TABLE `amigotours`.`tour_extra_attractions_tickets` ADD CONSTRAINT `fk_teat1` FOREIGN KEY (`id_icon`) REFERENCES `amigotours`.`icons` (`id`);

ALTER TABLE `amigotours`.`tour_extra_attractions_tickets` ADD CONSTRAINT `fk_teat_0` FOREIGN KEY (`pax_type`) REFERENCES `amigotours`.`tour_pax_type` (`id`);

ALTER TABLE `amigotours`.`tour_extra_attractions_tickets` ADD CONSTRAINT `fk_teat_1` FOREIGN KEY (`id_currency`) REFERENCES `amigotours`.`currency` (`id`);

ALTER TABLE `amigotours`.`tour_extra_language` ADD CONSTRAINT `tour_extra_language_i_fk1` FOREIGN KEY (`id_language`) REFERENCES `amigotours`.`languages` (`id`);

ALTER TABLE `amigotours`.`tour_extra_language_exceptions` ADD CONSTRAINT `fk_tel1` FOREIGN KEY (`id_extra_language`) REFERENCES `amigotours`.`tour_extra_language` (`id`);

ALTER TABLE `amigotours`.`tour_itinerary` ADD CONSTRAINT `tour_i_fk1` FOREIGN KEY (`id_tour_duration_type`) REFERENCES `amigotours`.`tour_duration_type` (`id`);

ALTER TABLE `amigotours`.`tour_rates_group` ADD CONSTRAINT `fk_trg_0` FOREIGN KEY (`id_tour_rates`) REFERENCES `amigotours`.`tour_rates` (`id`);

ALTER TABLE `amigotours`.`tour_review` ADD CONSTRAINT `fk_tr_0` FOREIGN KEY (`id_tour`) REFERENCES `amigotours`.`tour` (`id`);

ALTER TABLE `amigotours`.`tour_season` ADD CONSTRAINT `fk_s_to_0` FOREIGN KEY (`id_tour_optional`) REFERENCES `amigotours`.`tour_optional` (`id`);

ALTER TABLE `amigotours`.`widget_config` ADD CONSTRAINT `fk_widget` FOREIGN KEY (`id_widget`) REFERENCES `amigotours`.`widgets` (`id`);

ALTER TABLE `amigotours`.`company_branch` ADD FOREIGN KEY (`id_city`) REFERENCES `amigotours`.`city` (`id`);

ALTER TABLE `amigotours`.`tour` ADD FOREIGN KEY (`id_city`) REFERENCES `amigotours`.`city` (`id`);

ALTER TABLE `amigotours`.`tour` ADD FOREIGN KEY (`id_company_branch`) REFERENCES `amigotours`.`company_branch` (`id`);

ALTER TABLE `amigotours`.`users` ADD FOREIGN KEY (`id_company_branch`) REFERENCES `amigotours`.`company_branch` (`id`);

ALTER TABLE `amigotours`.`tour` ADD FOREIGN KEY (`id_tour_type`) REFERENCES `amigotours`.`tour_type` (`id`);

ALTER TABLE `amigotours`.`vendors` ADD FOREIGN KEY (`id_vendor_type`) REFERENCES `amigotours`.`vendor_type` (`id`);

ALTER TABLE `amigotours`.`tour` ADD FOREIGN KEY (`id_vendor`) REFERENCES `amigotours`.`vendors` (`id`);

ALTER TABLE `amigotours`.`orders` ADD FOREIGN KEY (`user_refresh_tokenID`) REFERENCES `amigotours`.`user_refresh_tokens` (`user_refresh_tokenID`);

ALTER TABLE `amigotours`.`orders` ADD FOREIGN KEY (`webhook_last_event_id`) REFERENCES `amigotours`.`webhook_events` (`id`);

ALTER TABLE `amigotours`.`destination_guides_images` ADD FOREIGN KEY (`id_destination_guides`) REFERENCES `amigotours`.`destination_guides` (`id`);

ALTER TABLE `amigotours`.`users` ADD FOREIGN KEY (`id_user_position`) REFERENCES `amigotours`.`users_position` (`id`);

ALTER TABLE `amigotours`.`country_summary_images` ADD FOREIGN KEY (`id_country_summary`) REFERENCES `amigotours`.`country_summary` (`id`);

ALTER TABLE `amigotours`.`tour_itinerary` ADD FOREIGN KEY (`id_tour`) REFERENCES `amigotours`.`tour` (`id`);

ALTER TABLE `amigotours`.`taxes` ADD FOREIGN KEY (`id_tax_type`) REFERENCES `amigotours`.`tax_types` (`id`);

ALTER TABLE `amigotours`.`tour_optional_taxes` ADD FOREIGN KEY (`id_tax`) REFERENCES `amigotours`.`taxes` (`id`);

ALTER TABLE `amigotours`.`tour_optional_taxes` ADD FOREIGN KEY (`id_tour_optional`) REFERENCES `amigotours`.`tour_optional` (`id`);

ALTER TABLE `amigotours`.`company_branch` ADD FOREIGN KEY (`id_company`) REFERENCES `amigotours`.`company` (`id`);

ALTER TABLE `amigotours`.`tour_vendor_details` ADD FOREIGN KEY (`id_tour`) REFERENCES `amigotours`.`tour` (`id`);

ALTER TABLE `amigotours`.`tour_vendor_details` ADD FOREIGN KEY (`id_tour_optional`) REFERENCES `amigotours`.`tour_optional` (`id`);




ALTER TABLE `amigotours`.`tour_extra_attractions` 
ADD COLUMN `is_from_vendor` TINYINT(1) NULL,
ADD COLUMN `for_checkout` TINYINT(1) NULL,
ADD COLUMN `id_vendor` INT NULL AFTER `is_from_vendor`,
ADD INDEX `idx_tea2` (`id_vendor` ASC) VISIBLE;
;
ALTER TABLE `amigotours`.`tour_extra_attractions` 
ADD CONSTRAINT `fk_tea_2`
  FOREIGN KEY (`id_vendor`)
  REFERENCES `amigotours`.`vendors` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


ALTER TABLE `amigotours`.`tour_extra_meeting`
ADD COLUMN `is_from_vendor` TINYINT(1) NULL,
ADD COLUMN `for_checkout` TINYINT(1) NULL,
ADD COLUMN `id_vendor` INT NULL AFTER `is_from_vendor`,
ADD INDEX `idx_tem2` (`id_vendor` ASC) VISIBLE;
;
ALTER TABLE `amigotours`.`tour_extra_meeting` 
ADD CONSTRAINT `fk_tem_2`
  FOREIGN KEY (`id_vendor`)
  REFERENCES `amigotours`.`vendors` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `amigotours`.`tour_extra_vehicle`
ADD COLUMN `is_from_vendor` TINYINT(1) NULL,
ADD COLUMN `for_checkout` TINYINT(1) NULL,
ADD COLUMN `id_vendor` INT NULL AFTER `is_from_vendor`,
ADD INDEX `idx_tev2` (`id_vendor` ASC) VISIBLE;
;
ALTER TABLE `amigotours`.`tour_extra_vehicle` 
ADD CONSTRAINT `fk_tev_2`
  FOREIGN KEY (`id_vendor`)
  REFERENCES `amigotours`.`vendors` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `amigotours`.`tour_extra_language`
ADD COLUMN `is_from_vendor` TINYINT(1) NULL,
ADD COLUMN `for_checkout` TINYINT(1) NULL,
ADD COLUMN `id_vendor` INT NULL AFTER `is_from_vendor`,
ADD INDEX `idx_tel2` (`id_vendor` ASC) VISIBLE;
;
ALTER TABLE `amigotours`.`tour_extra_language`
ADD CONSTRAINT `fk_tel_2`
  FOREIGN KEY (`id_vendor`)
  REFERENCES `amigotours`.`vendors` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `amigotours`.`tour_extra_duration`
ADD COLUMN `is_from_vendor` TINYINT(1) NULL,
ADD COLUMN `for_checkout` TINYINT(1) NULL,
ADD COLUMN `id_vendor` INT NULL AFTER `is_from_vendor`,
ADD INDEX `idx_ted2` (`id_vendor` ASC) VISIBLE;
;
ALTER TABLE `amigotours`.`tour_extra_duration`
ADD CONSTRAINT `fk_ted_2`
  FOREIGN KEY (`id_vendor`)
  REFERENCES `amigotours`.`vendors` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `amigotours`.`tour_extra_food`
ADD COLUMN `is_from_vendor` TINYINT(1) NULL,
ADD COLUMN `for_checkout` TINYINT(1) NULL,
ADD COLUMN `id_vendor` INT NULL AFTER `is_from_vendor`,
ADD INDEX `idx_tef2` (`id_vendor` ASC) VISIBLE;
;
ALTER TABLE `amigotours`.`tour_extra_food`
ADD CONSTRAINT `fk_tef_2`
  FOREIGN KEY (`id_vendor`)
  REFERENCES `amigotours`.`vendors` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


ALTER TABLE `amigotours`.`tour_extra_equipment`
ADD COLUMN `is_from_vendor` TINYINT(1) NULL,
ADD COLUMN `for_checkout` TINYINT(1) NULL,
ADD COLUMN `id_vendor` INT NULL AFTER `is_from_vendor`,
ADD INDEX `idx_tee2` (`id_vendor` ASC) VISIBLE;
;
ALTER TABLE `amigotours`.`tour_extra_equipment` 
ADD CONSTRAINT `fk_tee_2`
  FOREIGN KEY (`id_vendor`)
  REFERENCES `amigotours`.`vendors` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


ALTER TABLE `amigotours`.`tour_extra_food`
ADD COLUMN `is_from_vendor` TINYINT(1) NULL,
ADD COLUMN `for_checkout` TINYINT(1) NULL,
ADD COLUMN `id_vendor` INT NULL AFTER `is_from_vendor`,
ADD INDEX `idx_tef2` (`id_vendor` ASC) VISIBLE;
;
ALTER TABLE `amigotours`.`tour_extra_food` 
ADD CONSTRAINT `fk_tef_2`
  FOREIGN KEY (`id_vendor`)
  REFERENCES `amigotours`.`vendors` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


ALTER TABLE `amigotours`.`tour_extra_guide`
ADD COLUMN `is_from_vendor` TINYINT(1) NULL,
ADD COLUMN `for_checkout` TINYINT(1) NULL,
ADD COLUMN `id_vendor` INT NULL AFTER `is_from_vendor`,
ADD INDEX `idx_teg2` (`id_vendor` ASC) VISIBLE;
;
ALTER TABLE `amigotours`.`tour_extra_guide`
ADD CONSTRAINT `fk_teg_2`
  FOREIGN KEY (`id_vendor`)
  REFERENCES `amigotours`.`vendors` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;




SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS `orders_payments` (
  -- Clave primaria = payment_id de Revolut (UUID)
  `id`                      VARCHAR(64) NOT NULL,              -- payment_id (Revolut)

  -- Enlaces a la orden
  `id_order`          BIGINT NULL,              -- FK opcional a orders.id (tu tabla)
  `revolut_order_id`        VARCHAR(64) NOT NULL,              -- order_id de Revolut

  -- Estado y tiempos (UTC con microsegundos)
  `state`                   VARCHAR(32) NOT NULL,              -- 'completed','pending', etc.
  `created_at_utc`          DATETIME(6) NOT NULL,
  `updated_at_utc`          DATETIME(6) NOT NULL,

  -- Montos (minor units)
  `amount_minor`            INT NOT NULL,
  `currency`                CHAR(3) NOT NULL,
  `settled_amount_minor`    INT NULL,
  `settled_currency`        CHAR(3) NULL,

  -- Billing address
  `billing_city`            VARCHAR(100) NULL,
  `billing_country`         CHAR(2) NULL,
  `billing_postcode`        VARCHAR(100) NULL,

  -- Riesgo
  `risk_level`              VARCHAR(16) NULL,

  -- Método de pago (genérico) + detalles de tarjeta (si aplica)
  `payment_method_type`     VARCHAR(32) NULL,                  -- 'card','apple_pay','google_pay','revolut_pay', etc.
  `card_brand`              VARCHAR(32) NULL,
  `card_funding`            VARCHAR(16) NULL,                  -- 'debit','credit','prepaid'
  `card_country_code`       CHAR(2) NULL,
  `card_bin`                VARCHAR(16) NULL,
  `card_last_four`          CHAR(4) NULL,
  `card_expiry`             VARCHAR(7) NULL,                   -- 'MM/YY' o 'MM/YYYY'
  `cardholder_name`         VARCHAR(255) NULL,
  `network_transaction_id`  VARCHAR(255) NULL,

  -- Checks (un solo objeto de three_ds)
  `three_ds_eci`            VARCHAR(8) NULL,                   -- p.ej. '05','07'
  `three_ds_state`          VARCHAR(32) NULL,                  -- 'verified','failed', etc.
  `three_ds_version`        TINYINT NULL,                      -- 1 ó 2

  -- Fee (un solo elemento)
  `fee_type`                VARCHAR(32) NULL,                  -- 'acquiring','fx', etc.
  `fee_amount_minor`        INT NULL,
  `fee_currency`            CHAR(3) NULL,

  -- Guardar crudo por auditoría / cambios de schema
  `raw_json`                JSON NULL,

  -- Metadatos locales
  `created_at`              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  KEY `idx_rev_order` (`revolut_order_id`),
  KEY `idx_state` (`state`),
  KEY `idx_created_at_utc` (`created_at_utc`),

  CONSTRAINT `fk_orders_payments_order_local`
    FOREIGN KEY (`id_order`) REFERENCES `orders`(`id`)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;