# ************************************************************
# Sequel Ace SQL dump
# Version 20067
#
# https://sequel-ace.com/
# https://github.com/Sequel-Ace/Sequel-Ace
#
# Host: localhost (MySQL 5.7.39)
# Database: rnbanalytics
# Generation Time: 2024-07-23 07:18:12 +0000
# ************************************************************


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SET NAMES utf8mb4;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE='NO_AUTO_VALUE_ON_ZERO', SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


# Dump of table addons
# ------------------------------------------------------------

DROP TABLE IF EXISTS `addons`;

CREATE TABLE `addons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `item_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



# Dump of table banner_clicks
# ------------------------------------------------------------

DROP TABLE IF EXISTS `banner_clicks`;

CREATE TABLE `banner_clicks` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `venue_id` varchar(255) DEFAULT NULL,
  `user_id` varchar(255) DEFAULT NULL,
  `link` varchar(255) NOT NULL,
  `table_number` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `banner_slot` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_venue_id` (`venue_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



# Dump of table button_clicks
# ------------------------------------------------------------

DROP TABLE IF EXISTS `button_clicks`;

CREATE TABLE `button_clicks` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `venue_id` varchar(255) NOT NULL,
  `button_id` varchar(255) NOT NULL,
  `button_name` varchar(255) NOT NULL,
  `table_number` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `user_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`created_at`,`venue_id`,`button_id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_venue_id` (`venue_id`),
  KEY `idx_button_name` (`button_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



# Dump of table devices
# ------------------------------------------------------------

DROP TABLE IF EXISTS `devices`;

CREATE TABLE `devices` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `venue_id` varchar(255) DEFAULT NULL,
  `device_id` varchar(255) DEFAULT NULL,
  `type` varchar(50) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `last_seen` datetime DEFAULT NULL,
  `app_version` varchar(255) DEFAULT NULL,
  `os_type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_device_id` (`device_id`),
  KEY `idx_last_seen` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



# Dump of table employee_actions
# ------------------------------------------------------------

DROP TABLE IF EXISTS `employee_actions`;

CREATE TABLE `employee_actions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) NOT NULL,
  `type` varchar(255) DEFAULT NULL,
  `timestamp` datetime DEFAULT NULL,
  `description` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table employees
# ------------------------------------------------------------

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `position` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `venue_id` varchar(255) NOT NULL,
  `employee_id` varchar(255) NOT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `is_deleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table fb_actions
# ------------------------------------------------------------

DROP TABLE IF EXISTS `fb_actions`;

CREATE TABLE `fb_actions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `fb_smiley_id` bigint(20) DEFAULT NULL,
  `label` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_fb_smiley_id` (`fb_smiley_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table fb_smileys
# ------------------------------------------------------------

DROP TABLE IF EXISTS `fb_smileys`;

CREATE TABLE `fb_smileys` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `feedback_id` bigint(20) DEFAULT NULL,
  `label` varchar(255) DEFAULT NULL,
  `rating` float DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_feedback_id` (`feedback_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table fb_stars
# ------------------------------------------------------------

DROP TABLE IF EXISTS `fb_stars`;

CREATE TABLE `fb_stars` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `feedback_id` bigint(20) DEFAULT NULL,
  `label` varchar(255) DEFAULT NULL,
  `rating` float DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_feedback_id` (`feedback_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table feedbacks
# ------------------------------------------------------------

DROP TABLE IF EXISTS `feedbacks`;

CREATE TABLE `feedbacks` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `venue_id` varchar(255) DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `rating` decimal(10,2) DEFAULT NULL,
  `table_number` varchar(255) DEFAULT NULL,
  `message` text,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `user_id` varchar(255) DEFAULT NULL,
  `isCustomFeedback` tinyint(1) DEFAULT NULL,
  `isSmileyFeedback` tinyint(1) DEFAULT NULL,
  `isFeedbackNegative` tinyint(1) DEFAULT NULL,
  `table_label` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `room_number` varchar(255) DEFAULT NULL,
  `negative_color` varchar(255) DEFAULT NULL,
  `positive_color` varchar(255) DEFAULT NULL,
  `neutral_color` varchar(255) DEFAULT NULL,
  `feedback_id` varchar(255) DEFAULT NULL,
  `acknowledged_at` datetime DEFAULT NULL,
  `acknowledged_by` varchar(255) DEFAULT NULL,
  `escalated_at` datetime DEFAULT NULL,
  `time_to_confirm` int(11) DEFAULT NULL,
  `time_after_escalation` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_venue_id` (`venue_id`),
  KEY `idx_type` (`type`),
  KEY `idx_created_on` (`created_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



# Dump of table general_stats
# ------------------------------------------------------------

DROP TABLE IF EXISTS `general_stats`;

CREATE TABLE `general_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `resto_id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `category` varchar(255) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `link` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `latitude` varchar(255) DEFAULT NULL,
  `longitude` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `wifi` varchar(100) DEFAULT NULL,
  `menu` varchar(100) DEFAULT NULL,
  `avg_time_confirm` time DEFAULT NULL,
  `devices` varchar(100) DEFAULT NULL,
  `buttons_clicks` varchar(100) DEFAULT NULL,
  `date` date NOT NULL,
  `avg_rating` varchar(2) DEFAULT NULL,
  `scans` varchar(100) DEFAULT NULL,
  `service_buttons` varchar(6000) DEFAULT NULL,
  `feedbacks` varchar(100) DEFAULT NULL,
  `orders` varchar(100) DEFAULT NULL,
  `scans_dinein` varchar(100) DEFAULT NULL,
  `scans_delivery` varchar(100) DEFAULT NULL,
  `orders_dinein` varchar(100) DEFAULT NULL,
  `orders_delivery` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_resto_id` (`resto_id`),
  KEY `idx_date_resto_id` (`date`,`resto_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



# Dump of table items
# ------------------------------------------------------------

DROP TABLE IF EXISTS `items`;

CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `venue_id` varchar(255) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `quantity` int(11) NOT NULL,
  `category` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `add_ons` varchar(6000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_venue_id` (`venue_id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



# Dump of table logs
# ------------------------------------------------------------

DROP TABLE IF EXISTS `logs`;

CREATE TABLE `logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `level` varchar(50) NOT NULL,
  `message` text NOT NULL,
  `source` varchar(100) NOT NULL,
  `action` varchar(100) NOT NULL,
  `venue_id` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



# Dump of table order_transactions
# ------------------------------------------------------------

DROP TABLE IF EXISTS `order_transactions`;

CREATE TABLE `order_transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` varchar(255) NOT NULL,
  `reference_id` varchar(255) NOT NULL,
  `amount` decimal(19,9) NOT NULL,
  `payment_mode` varchar(255) DEFAULT NULL,
  `payment_method` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `check_no` varchar(255) DEFAULT NULL,
  `session_key` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table orders
# ------------------------------------------------------------

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `venue_id` varchar(255) NOT NULL,
  `order_id` varchar(255) NOT NULL,
  `user_id` varchar(255) DEFAULT NULL,
  `menu_id` varchar(255) DEFAULT NULL,
  `menu` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `table_number` varchar(255) DEFAULT NULL,
  `price` decimal(19,9) DEFAULT NULL,
  `currency` varchar(50) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `status` varchar(255) NOT NULL,
  `confirmed_by` varchar(255) DEFAULT NULL,
  `done_by` varchar(255) DEFAULT NULL,
  `confirmed_at` datetime DEFAULT NULL,
  `done_at` datetime DEFAULT NULL,
  `table_label` varchar(255) DEFAULT NULL,
  `integration_status` varchar(255) DEFAULT NULL,
  `integration_message` varchar(6000) DEFAULT NULL,
  `escalated_at` datetime DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `tip` decimal(19,9) DEFAULT NULL,
  `delivery_charge` decimal(19,9) DEFAULT NULL,
  `time_to_confirm` int(11) DEFAULT NULL,
  `room_number` varchar(255) DEFAULT NULL,
  `time_to_done` int(11) DEFAULT NULL,
  `time_after_escalation` int(11) DEFAULT NULL,
  `price_in_usd` decimal(19,9) DEFAULT NULL,
  `session_key` varchar(255) DEFAULT NULL,
  `check_no` varchar(255) DEFAULT NULL,
  `is_parent` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`,`venue_id`,`created_at`,`order_id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_created_on` (`created_on`),
  KEY `idx_status` (`status`),
  KEY `idx_venue_id` (`venue_id`),
  KEY `idx_venue_status_created_on` (`venue_id`,`status`,`created_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



# Dump of table requests
# ------------------------------------------------------------

DROP TABLE IF EXISTS `requests`;

CREATE TABLE `requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` varchar(255) NOT NULL,
  `button_id` varchar(255) NOT NULL,
  `table_number` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `done_by` varchar(255) DEFAULT NULL,
  `confirmed_by` varchar(255) DEFAULT NULL,
  `escalated_at` datetime DEFAULT NULL,
  `done_at` datetime DEFAULT NULL,
  `confirmed_at` datetime DEFAULT NULL,
  `status` varchar(255) NOT NULL,
  `venue_id` varchar(255) DEFAULT NULL,
  `button_label` varchar(255) NOT NULL,
  `created_on` datetime DEFAULT NULL,
  `user_id` varchar(255) DEFAULT NULL,
  `table_label` varchar(255) DEFAULT NULL,
  `time_to_confirm` int(11) DEFAULT NULL,
  `time_to_done` int(11) DEFAULT NULL,
  `time_after_escalation` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_request_id` (`request_id`),
  KEY `idx_created_on` (`created_on`),
  KEY `idx_status` (`status`),
  KEY `idx_venue_id` (`venue_id`),
  KEY `idx_venue_status_created_on` (`venue_id`,`status`,`created_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



# Dump of table scans
# ------------------------------------------------------------

DROP TABLE IF EXISTS `scans`;

CREATE TABLE `scans` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `venue_id` varchar(255) NOT NULL,
  `type` varchar(50) NOT NULL,
  `table_number` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `user_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`venue_id`,`created_at`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_venue_id` (`venue_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



# Dump of table tables_info
# ------------------------------------------------------------

DROP TABLE IF EXISTS `tables_info`;

CREATE TABLE `tables_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `restaurant_id` varchar(255) NOT NULL DEFAULT 's0XqQuKCMW9je5nKpD0Z',
  `table_number` varchar(255) NOT NULL,
  `table_label` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `table_id` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_restaurant_id` (`restaurant_id`),
  KEY `idx_table_number` (`table_number`),
  KEY `idx_table_id` (`table_id`),
  KEY `idx_restaurant_table` (`restaurant_id`,`table_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



# Dump of table users
# ------------------------------------------------------------

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password` text NOT NULL,
  `name` varchar(255) NOT NULL,
  `active` tinyint(1) NOT NULL,
  `type` varchar(255) NOT NULL,
  `restaurant_id` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_email` (`email`),
  KEY `idx_type` (`type`),
  KEY `idx_restaurant_id` (`restaurant_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



# Dump of table venues
# ------------------------------------------------------------

DROP TABLE IF EXISTS `venues`;

CREATE TABLE `venues` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `category` varchar(255) NOT NULL DEFAULT 'restaurant',
  `logo` text,
  `link` text NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `website` varchar(255) DEFAULT NULL,
  `branch` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `language` varchar(255) DEFAULT NULL,
  `currency` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `country_code` varchar(255) DEFAULT NULL,
  `timezone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `table_tier` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `subscription_type` varchar(255) DEFAULT NULL,
  `organization` varchar(255) DEFAULT NULL,
  `poc_name` varchar(255) DEFAULT NULL,
  `poc_phone` varchar(255) DEFAULT NULL,
  `poc_email` varchar(255) DEFAULT NULL,
  `join_at` datetime DEFAULT NULL,
  `renewal_at` datetime DEFAULT NULL,
  `contract_price` decimal(10,2) DEFAULT NULL,
  `contract_payment_percentage` decimal(5,2) DEFAULT NULL,
  `delivery_enabled` tinyint(1) DEFAULT NULL,
  `pos_enabled` tinyint(1) DEFAULT NULL,
  `pms_enabled` tinyint(1) DEFAULT NULL,
  `payment_enabled` tinyint(1) DEFAULT NULL,
  `feedback_enabled` tinyint(1) DEFAULT NULL,
  `cart_enabled` tinyint(1) DEFAULT NULL,
  `engage_enabled` tinyint(1) DEFAULT NULL,
  `multimenus_enabled` tinyint(1) DEFAULT NULL,
  `tips_enabled` tinyint(1) DEFAULT NULL,
  `reservation_enabled` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `idx_id` (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_type` (`type`),
  KEY `idx_status` (`status`),
  KEY `idx_name_type_status` (`name`,`type`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;




/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
