

CREATE TABLE IF NOT EXISTS `svt_categories` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_rooms_alt` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `id_room` bigint(20) unsigned NOT NULL,
   `panorama_image` varchar(100) DEFAULT NULL,
   `multires_status` tinyint(1) NOT NULL DEFAULT '0',
   `view_tooltip` varchar(100) NOT NULL DEFAULT '',
   PRIMARY KEY (`id`),
   KEY `id_room` (`id_room`),
   CONSTRAINT `svt_rooms_alt_ibfk_1` FOREIGN KEY (`id_room`) REFERENCES `svt_rooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_markers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_room` bigint(20) unsigned DEFAULT NULL,
  `pitch` float DEFAULT NULL,
  `yaw` float DEFAULT NULL,
  `rotateX` int(11) NOT NULL DEFAULT '0',
  `rotateZ` int(11) NOT NULL DEFAULT '0',
  `id_room_target` bigint(20) unsigned DEFAULT NULL,
  `pitch_room_target` int(11) DEFAULT NULL,
  `yaw_room_target` int(11) DEFAULT NULL,
  `icon` varchar(50) NOT NULL DEFAULT 'fas fa-chevron-circle-up',
  `id_icon_library` bigint(20) unsigned NOT NULL DEFAULT '0',
  `background` varchar(25) NOT NULL DEFAULT 'rgba(255,255,255,0.7)',
  `color` varchar(25) NOT NULL DEFAULT '#000000',
  `tooltip_text` varchar(100) DEFAULT NULL,
  `tooltip_type` enum('none','text','preview','preview_square','preview_rect','room_name') NOT NULL DEFAULT 'none',
  `size_scale` float NOT NULL DEFAULT '1',
  `show_room` tinyint(1) NOT NULL DEFAULT '1',
  `css_class` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `id_room` (`id_room`),
  KEY `id_room_target` (`id_room_target`),
  CONSTRAINT `svt_markers_ibfk_1` FOREIGN KEY (`id_room`) REFERENCES `svt_rooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `svt_markers_ibfk_2` FOREIGN KEY (`id_room_target`) REFERENCES `svt_rooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_pois` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_room` bigint(20) unsigned DEFAULT NULL,
  `pitch` float DEFAULT NULL,
  `yaw` float DEFAULT NULL,
  `rotateX` int(11) NOT NULL DEFAULT '0',
  `rotateZ` int(11) NOT NULL DEFAULT '0',
  `type` enum('image','video','link','link_ext','html','html_sc','download','form','video360','audio','gallery') DEFAULT NULL,
  `style` tinyint(1) NOT NULL DEFAULT '0',
  `icon` varchar(50) DEFAULT NULL,
  `label` varchar(100) DEFAULT NULL,
  `id_icon_library` bigint(20) unsigned NOT NULL DEFAULT '0',
  `background` varchar(25) NOT NULL DEFAULT 'rgba(255,255,255,0.7)',
  `color` varchar(25) NOT NULL DEFAULT '#000000',
  `tooltip_text` text,
  `tooltip_type` enum('none','text') NOT NULL DEFAULT 'none',
  `size_scale` float NOT NULL DEFAULT '1',
  `title` varchar(100) DEFAULT NULL,
  `description` text,
  `content` longtext,
  `target` enum('_blank','_self') DEFAULT NULL,
  `access_count` bigint(20) NOT NULL DEFAULT '0',
  `schedule` varchar(250) DEFAULT NULL,
  `css_class` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `id_room` (`id_room`),
  CONSTRAINT `svt_pois_ibfk_1` FOREIGN KEY (`id_room`) REFERENCES `svt_rooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_access_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_virtualtour` bigint(20) unsigned DEFAULT NULL,
  `date_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_virtualtour` (`id_virtualtour`),
  CONSTRAINT `svt_access_log_ibfk_1` FOREIGN KEY (`id_virtualtour`) REFERENCES `svt_virtualtours` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_presentations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_virtualtour` bigint(20) unsigned DEFAULT NULL,
  `id_room` bigint(20) unsigned DEFAULT NULL,
  `action` varchar(50) DEFAULT NULL,
  `params` text,
  `sleep` int(11) NOT NULL DEFAULT '0',
  `priority_1` int(11) DEFAULT NULL,
  `priority_2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_virtual_tour` (`id_virtualtour`),
  KEY `id_room` (`id_room`),
  CONSTRAINT `svt_presentations_ibfk_1` FOREIGN KEY (`id_virtualtour`) REFERENCES `svt_virtualtours` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `svt_presentations_ibfk_2` FOREIGN KEY (`id_room`) REFERENCES `svt_rooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_gallery` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_virtualtour` bigint(20) unsigned DEFAULT NULL,
  `image` varchar(50) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `description` text,
  `priority` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `id_virtualtour` (`id_virtualtour`),
  CONSTRAINT `svt_gallery_ibfk_1` FOREIGN KEY (`id_virtualtour`) REFERENCES `svt_virtualtours` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_maps` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_virtualtour` bigint(20) unsigned NOT NULL,
  `map_type` enum('floorplan','map') DEFAULT 'floorplan',
  `map` varchar(200) DEFAULT NULL,
  `point_color` varchar(25) NOT NULL DEFAULT '#005eff',
  `name` varchar(200) DEFAULT NULL,
  `point_size` int(11) NOT NULL DEFAULT '20',
  `north_degree` int(11) NOT NULL DEFAULT '0',
  `zoom_level` int(11) NOT NULL DEFAULT '16',
  `zoom_to_point` tinyint(1) NOT NULL DEFAULT '0',
  `priority` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `id_virtualtour` (`id_virtualtour`),
  CONSTRAINT `svt_maps_ibfk_1` FOREIGN KEY (`id_virtualtour`) REFERENCES `svt_virtualtours` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_voice_commands` (
  `id` int(11) NOT NULL DEFAULT '0',
  `language` varchar(10) NOT NULL DEFAULT 'en-US',
  `initial_msg` varchar(200) NOT NULL DEFAULT 'Listening ... Say HELP for command list',
  `listening_msg` varchar(200) NOT NULL DEFAULT 'Listening ...',
  `next_cmd` varchar(200) NOT NULL DEFAULT 'next',
  `next_msg` varchar(200) NOT NULL DEFAULT 'Ok, going to next room',
  `prev_cmd` varchar(200) NOT NULL DEFAULT 'prev',
  `prev_msg` varchar(200) NOT NULL DEFAULT 'Ok, going to previous room',
  `left_cmd` varchar(200) NOT NULL DEFAULT 'left',
  `left_msg` varchar(200) NOT NULL DEFAULT 'Ok, looking left',
  `right_cmd` varchar(200) NOT NULL DEFAULT 'right',
  `right_msg` varchar(200) NOT NULL DEFAULT 'Ok, looking right',
  `up_cmd` varchar(200) NOT NULL DEFAULT 'up',
  `up_msg` varchar(200) NOT NULL DEFAULT 'Ok, looking up',
  `down_cmd` varchar(200) NOT NULL DEFAULT 'down',
  `down_msg` varchar(200) NOT NULL DEFAULT 'Ok, looking down',
  `help_cmd` varchar(200) NOT NULL DEFAULT 'help',
  `help_msg_1` varchar(200) NOT NULL DEFAULT 'Say NEXT / PREVIOUS to navigate between rooms',
  `help_msg_2` varchar(200) NOT NULL DEFAULT 'Say LEFT / RIGHT / UP / DOWN to look around',
  `error_msg` varchar(200) NOT NULL DEFAULT 'I do not understand, repeat please...',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT IGNORE INTO `svt_voice_commands` (`id`) VALUES(1);

CREATE TABLE IF NOT EXISTS `svt_icons` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_virtualtour` bigint(20) unsigned DEFAULT NULL,
  `image` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_virtualtour` (`id_virtualtour`),
  CONSTRAINT `svt_icon_ibfk_1` FOREIGN KEY (`id_virtualtour`) REFERENCES `svt_virtualtours` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_rooms_access_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_room` bigint(20) unsigned DEFAULT NULL,
  `time` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_room` (`id_room`),
  CONSTRAINT `svt_rooms_access_log_ibfk_1` FOREIGN KEY (`id_room`) REFERENCES `svt_rooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_forms_data` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_virtualtour` bigint(20) unsigned NOT NULL,
  `id_room` bigint(20) unsigned DEFAULT NULL,
  `title` varchar(250) DEFAULT NULL,
  `field1` text,
  `field2` text,
  `field3` text,
  `field4` text,
  `field5` text,
  PRIMARY KEY (`id`),
  KEY `id_virtualtour` (`id_virtualtour`),
  CONSTRAINT `svt_forms_data_ibfk_1` FOREIGN KEY (`id_virtualtour`) REFERENCES `svt_virtualtours` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_poi_gallery` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_poi` bigint(20) unsigned DEFAULT NULL,
  `image` varchar(50) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `description` text,
  `priority` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `id_poi` (`id_poi`),
  CONSTRAINT `svt_poi_gallery_ibfk_1` FOREIGN KEY (`id_poi`) REFERENCES `svt_pois` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_assign_virtualtours` (
  `id_user` int(11) unsigned DEFAULT NULL,
  `id_virtualtour` bigint(20) unsigned DEFAULT NULL,
  UNIQUE KEY `id_user` (`id_user`,`id_virtualtour`),
  KEY `id_virtualtour` (`id_virtualtour`),
  CONSTRAINT `svt_assign_virtualtours_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `svt_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `svt_assign_virtualtours_ibfk_2` FOREIGN KEY (`id_virtualtour`) REFERENCES `svt_virtualtours` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_leads` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id_virtualtour` bigint(20) unsigned NOT NULL,
  `name` varchar(250) DEFAULT NULL,
  `email` varchar(250) DEFAULT NULL,
  `phone` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_virtualtour` (`id_virtualtour`),
  CONSTRAINT `svt_leads_ibfk_1` FOREIGN KEY (`id_virtualtour`) REFERENCES `svt_virtualtours` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_showcases` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_user` int(11) unsigned DEFAULT NULL,
  `code` varchar(100) DEFAULT NULL,
  `name` varchar(250) DEFAULT NULL,
  `friendly_url` varchar(100) DEFAULT NULL,
  `banner` varchar(100) DEFAULT NULL,
  `logo` varchar(100) DEFAULT NULL,
  `bg_color` varchar(10) NOT NULL DEFAULT '#EEEEEE',
  `header_html` longtext DEFAULT NULL,
  `footer_html` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_user` (`id_user`),
  CONSTRAINT `svt_showcases_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `svt_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_showcase_list` (
  `id_showcase` bigint(20) unsigned DEFAULT NULL,
  `id_virtualtour` bigint(20) unsigned DEFAULT NULL,
  `type_viewer` enum('viewer','landing') NOT NULL DEFAULT 'viewer',
  KEY `id_showcase` (`id_showcase`),
  KEY `id_virtualtour` (`id_virtualtour`),
  CONSTRAINT `svt_showcase_list_ibfk_1` FOREIGN KEY (`id_showcase`) REFERENCES `svt_showcases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `svt_showcase_list_ibfk_2` FOREIGN KEY (`id_virtualtour`) REFERENCES `svt_virtualtours` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_advertisements` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `image` varchar(50) DEFAULT NULL,
  `link` varchar(250) DEFAULT NULL,
  `countdown` int(11) NOT NULL DEFAULT 0,
  `id_plans` varchar(100) DEFAULT NULL,
  `auto_assign` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `svt_assign_advertisements` (
  `id_advertisement` int(11) unsigned NOT NULL,
  `id_virtualtour` bigint(20) unsigned NOT NULL,
  KEY `id_advertisement` (`id_advertisement`),
  KEY `id_virtualtour` (`id_virtualtour`),
  CONSTRAINT `svt_assign_advertisements_ibfk_1` FOREIGN KEY (`id_advertisement`) REFERENCES `svt_advertisements` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `svt_assign_advertisements_ibfk_2` FOREIGN KEY (`id_virtualtour`) REFERENCES `svt_virtualtours` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;