CREATE TABLE continent (
	id INT auto_increment NOT NULL,
	name varchar(100) NULL,
	CONSTRAINT continent_pk PRIMARY KEY (id)
)

ALTER TABLE tour ADD code varchar(100) NOT NULL;


ALTER TABLE country ADD id_continent INT DEFAULT 5 NOT NULL;


CREATE TABLE `user_refresh_tokens` (
	`user_refresh_tokenID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`urf_userID` INT(10) UNSIGNED NOT NULL,
	`urf_token` VARCHAR(1000) NOT NULL,
	`urf_ip` VARCHAR(50) NOT NULL,
	`urf_user_agent` VARCHAR(1000) NOT NULL,
	`urf_created` DATETIME NOT NULL COMMENT 'UTC',
	PRIMARY KEY (`user_refresh_tokenID`)
)
COMMENT='For JWT authentication process';



CREATE TABLE IF NOT EXISTS `booking_payment` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `charge_id` varchar(180) NOT NULL DEFAULT '0',
  `livemode` tinyint NOT NULL DEFAULT (0),
  `tour_booking_id` tinyint NOT NULL DEFAULT (0),
  `created_at` varchar(50) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);


ALTER TABLE tour_availability MODIFY COLUMN `day` int NULL;



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





CREATE TABLE `tour_guide_type` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(150) NOT NULL DEFAULT '',
	`name_eng` VARCHAR(150) NOT NULL DEFAULT '',
	`active` VARCHAR(150) NOT NULL DEFAULT '',
	PRIMARY KEY (`id`)
);


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



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


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


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



CREATE TABLE `tour_extras` (
	`id` INT 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),
	PRIMARY KEY (`id`)
);




CREATE TABLE  `tour_meeting_points` (
  `id` int NOT NULL,
  `lat` varchar(50) NOT NULL DEFAULT '',
  `lon` varchar(50) NOT NULL DEFAULT '',
  `address` varchar(250) NOT NULL DEFAULT '',
  `id_tour_details` int NOT NULL DEFAULT (0),
  `pickup` varchar(50) NOT NULL DEFAULT 'downtown',
  PRIMARY KEY (`id`)
);


ALTER TABLE `tour_booking_config`
	CHANGE COLUMN `on_request` `on_request` INT NULL DEFAULT '0' AFTER `max_pax`;


CREATE TABLE IF NOT EXISTS `statuspost` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

INSERT INTO `statuspost` (`id`, `name`) VALUES ('0', 'Inactive');
INSERT INTO `statuspost` (`id`, `name`) VALUES ('1', 'Draft');
INSERT INTO `statuspost` (`id`, `name`) VALUES ('2', 'Published');


CREATE TABLE IF NOT EXISTS `destination_guides` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id_city` INT NOT NULL,
  `title` VARCHAR(45) NULL,
  `id_statuspost` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_destination_guides_city_idx` (`id_city` ASC),
  INDEX `fk_destination_guides_statuspost1_idx` (`id_statuspost` ASC),
  CONSTRAINT `fk_destination_guides_city`
    FOREIGN KEY (`id_city`)
    REFERENCES `city` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_destination_guides_statuspost1`
    FOREIGN KEY (`id_statuspost`)
    REFERENCES `statuspost` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE `destination_guides_section` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `htmlcontent` text DEFAULT NULL,
  `section` varchar(45) DEFAULT NULL,
  `id_destination_guides` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_destination_guides_section_destination_guides1_idx` (`id_destination_guides`),
  CONSTRAINT `fk_destination_guides_section_destination_guides1` FOREIGN KEY (`id_destination_guides`) REFERENCES `destination_guides` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE IF NOT EXISTS `destination_guides_seo` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `keywords` VARCHAR(250) NULL,
  `description` VARCHAR(150) NULL,
  `canonicalurl` VARCHAR(80) NULL,
  `slug` VARCHAR(100) NULL,
  `id_destination_guides_section` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_destination_guides_seo_destination_guides_section1_idx` (`id_destination_guides_section` ASC),
  CONSTRAINT `fk_destination_guides_seo_destination_guides_section1`
    FOREIGN KEY (`id_destination_guides_section`)
    REFERENCES `destination_guides_section` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE travel_companion (
	ID int PRIMARY KEY auto_increment,
    name varchar(50)
);
INSERT INTO `travel_companion` (`name`) VALUES ('Solo');
INSERT INTO `travel_companion` (`name`) VALUES ('Pareja');
INSERT INTO `travel_companion` (`name`) VALUES ('Familia');
INSERT INTO `travel_companion` (`name`) VALUES ('Amigos');
INSERT INTO `travel_companion` (`name`) VALUES ('Compañeros de trabajo');


	


CREATE TABLE `tour_images` (
	`id` INT 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_tour` INT NOT NULL DEFAULT (0),
	`is_cover` TINYINT NOT NULL DEFAULT (0),
	PRIMARY KEY (`id`)
);




ALTER TABLE `tour_optional`
	CHANGE COLUMN `description_include` `description_include` TEXT NULL AFTER `tour_transfer_note`,
	CHANGE COLUMN `description_not_included` `description_not_included` TEXT NULL AFTER `description_include`;
/* Info: Records: 0  Duplicates: 0  Warnings: 0 */


ALTER TABLE tour_cut_off AUTO_INCREMENT = 0;

ALTER TABLE `tour_cut_off`
	CHANGE COLUMN `value` `value` FLOAT NULL DEFAULT NULL AFTER `name`;


	INSERT INTO tour_cut_off (name, value)
VALUES 
-- Insertar valores de 5 en 5 minutos hasta llegar a 1 hora (1h)
('Instant', 0),
('5 min', 5/60.0),
('10 min', 10/60.0),
('15 min', 15/60.0),
('20 min', 20/60.0),
('25 min', 25/60.0),
('30 min', 30/60.0),
('35 min', 35/60.0),
('40 min', 40/60.0),
('45 min', 45/60.0),
('50 min', 50/60.0),
('55 min', 55/60.0),
('1h', 1),

-- Insertar valores cada hora hasta llegar a 12 horas
('2h', 2),
('3h', 3),
('4h', 4),
('5h', 5),
('6h', 6),
('7h', 7),
('8h', 8),
('9h', 9),
('10h', 10),
('11h', 11),
('12h', 12),

-- Insertar valores para 24h, 48h, 72h, 96h
('24h', 24),
('48h', 48),
('72h', 72),
('96h', 96);



-- Extra Tables

CREATE TABLE `tour_extra_guide` (
	`id` INT 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),
	PRIMARY KEY (`id`)
);