-- --------------------------------------------------------
-- Host:                         taguard.in
-- Server version:               8.0.31-0ubuntu0.20.04.1 - (Ubuntu)
-- Server OS:                    Linux
-- HeidiSQL Version:             12.0.0.6468
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- Dumping database structure for tempsense
CREATE DATABASE IF NOT EXISTS `tempsense` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `tempsense`;

-- Dumping structure for table tempsense.device_breachlog
CREATE TABLE IF NOT EXISTS `device_breachlog` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` int DEFAULT NULL,
  `device_mac` varchar(50) DEFAULT NULL,
  `device_temperature` varchar(50) DEFAULT NULL,
  `device_humidity` varchar(50) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  `isExpired` int DEFAULT NULL,
  `isRenewed` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.device_log
CREATE TABLE IF NOT EXISTS `device_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_temperature` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_mac` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `device_ID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `ClassName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `battery` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_humidity` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '0',
  `Upload_time` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `post_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `date_arrival` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  FULLTEXT KEY `device_name` (`device_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2269 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.device_mac
CREATE TABLE IF NOT EXISTS `device_mac` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_mac` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_alias` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT 'TAGUARD',
  `device_min_temp` int DEFAULT '0',
  `device_max_temp` int DEFAULT '0',
  `device_min_humidity` int DEFAULT '0',
  `device_max_humidity` int DEFAULT '0',
  `device_userlink` int DEFAULT '0',
  `device_audit_trail` int DEFAULT '0',
  `isActive` int DEFAULT '0' COMMENT '1: active 0: inactive',
  PRIMARY KEY (`id`),
  UNIQUE KEY `device_mac` (`device_mac`)
) ENGINE=InnoDB AUTO_INCREMENT=880 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.device_monitoring
CREATE TABLE IF NOT EXISTS `device_monitoring` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_id` int DEFAULT NULL,
  `device_uid` varchar(50) DEFAULT NULL,
  `device_mac` varchar(50) DEFAULT NULL,
  `device_timestamp` timestamp NULL DEFAULT NULL,
  `device_temperature` varchar(50) DEFAULT NULL,
  `device_humidity` varchar(50) DEFAULT NULL,
  `device_rssi` int DEFAULT NULL,
  `device_x` double DEFAULT NULL,
  `device_y` double DEFAULT NULL,
  `device_z` double DEFAULT NULL,
  `ClassName` varchar(50) DEFAULT NULL,
  `device_status` int DEFAULT NULL,
  `device_isactive` varchar(50) DEFAULT NULL,
  `device_bypassed` varchar(50) DEFAULT NULL,
  `device_isRead` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.marsnfc_log
CREATE TABLE IF NOT EXISTS `marsnfc_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_temperature` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_mac` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `rssi` int DEFAULT NULL,
  `vbatt` varchar(50) DEFAULT NULL,
  `device_ID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `ClassName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `battery` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_humidity` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `Upload_time` datetime DEFAULT NULL,
  `post_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `date_arrival` datetime DEFAULT NULL,
  `latitude` double DEFAULT '0',
  `longitude` double DEFAULT '0',
  `sync` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `Upload_time` (`Upload_time`),
  FULLTEXT KEY `device_name` (`device_name`),
  FULLTEXT KEY `device_mac` (`device_mac`),
  FULLTEXT KEY `device_temperature` (`device_temperature`)
) ENGINE=InnoDB AUTO_INCREMENT=31137276 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.marsnfc_log_bluetooth
CREATE TABLE IF NOT EXISTS `marsnfc_log_bluetooth` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_temperature` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_mac` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `rssi` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `vbatt` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_ID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `ClassName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `battery` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_humidity` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `Upload_time` datetime DEFAULT NULL,
  `post_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `date_arrival` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  FULLTEXT KEY `device_name` (`device_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.Subscriptions
CREATE TABLE IF NOT EXISTS `Subscriptions` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `User` varchar(255) DEFAULT NULL,
  `Endpoint` longtext NOT NULL,
  `PublicKey` varchar(255) NOT NULL,
  `AuthenticationToken` varchar(255) NOT NULL,
  `ContentEncoding` varchar(255) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblAlertLog
CREATE TABLE IF NOT EXISTS `tblAlertLog` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` int DEFAULT NULL,
  `title` mediumtext,
  `message` varchar(50) DEFAULT NULL,
  `priority` int DEFAULT NULL,
  `isChecked` int DEFAULT NULL,
  `isArchived` int DEFAULT NULL,
  `arrival_timestamp` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblAlertType
CREATE TABLE IF NOT EXISTS `tblAlertType` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblAppConfig
CREATE TABLE IF NOT EXISTS `tblAppConfig` (
  `id` int NOT NULL AUTO_INCREMENT,
  `isUpdated` int DEFAULT '0' COMMENT '0: no update 1:update',
  `updateStatus` varchar(50) DEFAULT NULL,
  `updateVersion` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblbatch
CREATE TABLE IF NOT EXISTS `tblbatch` (
  `batchId` int NOT NULL AUTO_INCREMENT,
  `batchName` varchar(50) DEFAULT NULL,
  `userId` int DEFAULT NULL,
  PRIMARY KEY (`batchId`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblCalendarSummary
CREATE TABLE IF NOT EXISTS `tblCalendarSummary` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` int DEFAULT '-1',
  `added_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `is_Visible` int DEFAULT '1',
  `title` text,
  `description` longtext,
  `url` longtext,
  `priority` tinyint DEFAULT '1',
  `value` int DEFAULT '1',
  `tags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `mColor` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `current_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `mAllDay` int DEFAULT '1',
  ` mId` bigint DEFAULT '-1',
  `mName` varchar(50) DEFAULT '-1',
  `mStartTime` datetime DEFAULT NULL,
  `mEndTime` datetime DEFAULT NULL,
  `mLocation` varchar(50) DEFAULT NULL,
  `reminder_interval` varchar(50) DEFAULT NULL,
  `color_code` varchar(50) DEFAULT NULL,
  `isEnabled` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblGeoFence
CREATE TABLE IF NOT EXISTS `tblGeoFence` (
  `id` int NOT NULL AUTO_INCREMENT,
  `geoFenceName` varchar(100) DEFAULT NULL,
  `userId` int DEFAULT '0',
  `createdAtTimeStamp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblGeofenceDataPoints
CREATE TABLE IF NOT EXISTS `tblGeofenceDataPoints` (
  `id` int NOT NULL AUTO_INCREMENT,
  `geofenceId` int DEFAULT '0',
  `shape` varchar(5) DEFAULT 'c' COMMENT 'c: circle p: polygone ( cointains triangle, square and others)',
  `centerLat` float DEFAULT '0' COMMENT 'if shape = ''c''',
  `centerLng` float DEFAULT '0',
  `radius` float DEFAULT '0' COMMENT 'if shape = ''c''',
  `dataPoints` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '0' COMMENT 'if shape = ''p''. Data points seperated by ,',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblGeofenceUserLog
CREATE TABLE IF NOT EXISTS `tblGeofenceUserLog` (
  `id` int NOT NULL AUTO_INCREMENT,
  `geofenceId` int DEFAULT '0',
  `userId` int DEFAULT '0',
  `type` varchar(1) DEFAULT NULL COMMENT 'i : in, o :out',
  `inOutTimeStamp` timestamp NULL DEFAULT NULL,
  `recentLatitude` double DEFAULT '0',
  `recentLongitude` double DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblGroupSensorRelationship
CREATE TABLE IF NOT EXISTS `tblGroupSensorRelationship` (
  `id` int NOT NULL AUTO_INCREMENT,
  `groupId` int DEFAULT '0',
  `sensorId` int DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2586186 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblGroupUserRelationship
CREATE TABLE IF NOT EXISTS `tblGroupUserRelationship` (
  `id` int NOT NULL AUTO_INCREMENT,
  `groupId` int DEFAULT '0',
  `userId` int DEFAULT '0',
  `userAddedBy` int DEFAULT '0' COMMENT '0: means own >0 means someone has added ',
  PRIMARY KEY (`id`),
  UNIQUE KEY `groupId` (`groupId`,`userId`),
  KEY `FK1_user` (`userId`),
  CONSTRAINT `FK1_user` FOREIGN KEY (`userId`) REFERENCES `user_db` (`id`),
  CONSTRAINT `FK2_group` FOREIGN KEY (`groupId`) REFERENCES `tblSensorGroup` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblNewsFeed
CREATE TABLE IF NOT EXISTS `tblNewsFeed` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` int DEFAULT NULL,
  `profile_url` longtext,
  `banner_url` longtext,
  `title` mediumtext,
  `description` longtext,
  `tags` text,
  `add_time` timestamp NULL DEFAULT NULL,
  `is_active` int DEFAULT NULL,
  `is_supressed` int DEFAULT NULL,
  `added_by_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblNewsFeedVideos
CREATE TABLE IF NOT EXISTS `tblNewsFeedVideos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` int DEFAULT '-1',
  `added_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `is_Visible` int DEFAULT '1',
  `title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `url` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `thumbnail` longtext,
  `videoLink` longtext,
  `subtitle` longtext,
  `priority` tinyint DEFAULT '1',
  `value` int DEFAULT '1',
  `tags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `current_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  ` mId` bigint DEFAULT '-1',
  `mName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '-1',
  `mLocation` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `reminder_interval` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `color_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `isEnabled` int DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblProductFeed
CREATE TABLE IF NOT EXISTS `tblProductFeed` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` int DEFAULT '-1',
  `added_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `is_Visible` int DEFAULT '1',
  `title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `url` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `thumbnail` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `videoLink` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `subtitle` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `priority` tinyint DEFAULT '1',
  `value` int DEFAULT '1',
  `tags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `current_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  ` mId` bigint DEFAULT '-1',
  `mName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '-1',
  `mLocation` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '-1',
  `reminder_interval` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `color_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `MOQ` varchar(50) DEFAULT NULL,
  `Tax` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '-1',
  `Price` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '-1',
  `hsn` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '-1',
  `product_origin` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '-1',
  `isEnabled` int DEFAULT '1',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblProductImageUrls
CREATE TABLE IF NOT EXISTS `tblProductImageUrls` (
  `tblProductId` int DEFAULT NULL,
  `tblImageURL` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `tblTitle` varchar(50) DEFAULT NULL,
  `tblDescription` varchar(50) DEFAULT NULL,
  KEY `FK_tblProductImageUrls_tblProductFeed` (`tblProductId`),
  CONSTRAINT `FK_tblProductImageUrls_tblProductFeed` FOREIGN KEY (`tblProductId`) REFERENCES `tblProductFeed` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblProducts
CREATE TABLE IF NOT EXISTS `tblProducts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int DEFAULT NULL,
  `product_title` varchar(50) DEFAULT NULL,
  `product_description` varchar(50) DEFAULT NULL,
  `product_logo` varchar(50) DEFAULT NULL,
  `product_time` varchar(50) DEFAULT NULL,
  `product_added_by` varchar(50) DEFAULT NULL,
  `product_gallery_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_tblProducts_tbpProductGallery` (`product_gallery_id`),
  CONSTRAINT `FK_tblProducts_tbpProductGallery` FOREIGN KEY (`product_gallery_id`) REFERENCES `tbpProductGallery` (`product_gallery_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblSensorGroup
CREATE TABLE IF NOT EXISTS `tblSensorGroup` (
  `id` int NOT NULL AUTO_INCREMENT,
  `groupName` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblServerConfig
CREATE TABLE IF NOT EXISTS `tblServerConfig` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customerId` int DEFAULT NULL,
  `serverIP` varchar(50) DEFAULT NULL,
  `serverPort` varchar(50) DEFAULT NULL,
  `serverUsername` varchar(50) DEFAULT NULL,
  `serverPassword` varchar(50) DEFAULT NULL,
  `talk` varchar(50) DEFAULT NULL,
  `influx_server_url` varchar(50) DEFAULT NULL,
  `influx_token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `influx_bucket` varchar(50) DEFAULT NULL,
  `influx_org` varchar(50) DEFAULT NULL,
  `influx_measurement` varchar(50) DEFAULT NULL,
  `influx_port` int DEFAULT NULL,
  `influx_enabled` int DEFAULT NULL,
  `influx_future` int DEFAULT NULL,
  `ssl_cert` mediumtext,
  `ssl_private` mediumtext,
  `ssl_public` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblSmartAdvertising
CREATE TABLE IF NOT EXISTS `tblSmartAdvertising` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` int DEFAULT NULL,
  `url` mediumtext,
  `added_timestamp` timestamp NULL DEFAULT NULL,
  `added_by` text,
  `device_mac` text,
  `priority` text,
  `fail_url` text,
  `image_url` text,
  `rssi` text,
  `isActive` text,
  `activateTime` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tbltag
CREATE TABLE IF NOT EXISTS `tbltag` (
  `tagId` int NOT NULL AUTO_INCREMENT,
  `tagName` varchar(50) DEFAULT NULL,
  `batchId` int DEFAULT NULL,
  `userId` int DEFAULT NULL,
  PRIMARY KEY (`tagId`),
  FULLTEXT KEY `tagName` (`tagName`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblUserAlertCriteria
CREATE TABLE IF NOT EXISTS `tblUserAlertCriteria` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` int DEFAULT '0',
  `deviceId` varchar(50) DEFAULT NULL,
  `device_mac` varchar(50) DEFAULT NULL,
  `device_alias` varchar(50) DEFAULT 'TAGUARD',
  `maxTemp` double NOT NULL DEFAULT '0',
  `minTemp` double NOT NULL DEFAULT '0',
  `maxHumidity` double NOT NULL DEFAULT '0',
  `minHumidity` double NOT NULL DEFAULT '0',
  `minBattery` double NOT NULL DEFAULT '0',
  `maxBattery` double NOT NULL DEFAULT '0',
  `isEmail` int NOT NULL DEFAULT '0',
  `isSms` int NOT NULL DEFAULT '0',
  `isMQTT` int NOT NULL DEFAULT '0',
  `isEnabled` int NOT NULL DEFAULT '0',
  `snoozeTime` double NOT NULL DEFAULT '0',
  `sleepHours` double NOT NULL DEFAULT '0',
  `ignoreTemperature` double NOT NULL DEFAULT '0',
  `ignoreHumidity` double NOT NULL DEFAULT '0',
  `x` double NOT NULL DEFAULT '0',
  `y` double NOT NULL DEFAULT '0',
  `z` double NOT NULL DEFAULT '0',
  `added_timestamp` varchar(50) DEFAULT NULL,
  `alertInterval` varchar(50) DEFAULT NULL,
  `isAudioOn` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tblUserToken
CREATE TABLE IF NOT EXISTS `tblUserToken` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` int DEFAULT '0',
  `deviceId` varchar(500) DEFAULT NULL,
  `deviceToken` varchar(500) DEFAULT NULL,
  `acivateOn` timestamp NULL DEFAULT NULL,
  `deviceModel` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tbl_device_relationship
CREATE TABLE IF NOT EXISTS `tbl_device_relationship` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sensorID` int NOT NULL DEFAULT '0',
  `ownerId` int NOT NULL DEFAULT '0' COMMENT 'reference to id of user_db',
  `distributorId` int NOT NULL DEFAULT '0' COMMENT 'reference to id of user_db',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tbl_QRCode
CREATE TABLE IF NOT EXISTS `tbl_QRCode` (
  `id` int NOT NULL AUTO_INCREMENT,
  `link` varchar(500) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.tbpProductGallery
CREATE TABLE IF NOT EXISTS `tbpProductGallery` (
  `product_gallery_id` int NOT NULL,
  `weburl` varchar(50) DEFAULT NULL,
  `description` longtext,
  PRIMARY KEY (`product_gallery_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.temperature_log
CREATE TABLE IF NOT EXISTS `temperature_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_temperature` varchar(50) DEFAULT NULL,
  `device_name` varchar(50) DEFAULT NULL,
  `device_mac` varchar(50) DEFAULT NULL,
  `rssi` varchar(50) DEFAULT NULL,
  `device_ID` varchar(50) DEFAULT NULL,
  `ClassName` varchar(50) DEFAULT NULL,
  `battery` varchar(50) DEFAULT NULL,
  `device_humidity` varchar(50) DEFAULT NULL,
  `Upload_time` varchar(50) DEFAULT NULL,
  `post_data` longtext,
  `date_arrival` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `device_name` (`device_name`)
) ENGINE=InnoDB AUTO_INCREMENT=315348 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.temperature_log_copy
CREATE TABLE IF NOT EXISTS `temperature_log_copy` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_temperature` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_mac` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `rssi` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_ID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `ClassName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `battery` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_humidity` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `Upload_time` datetime DEFAULT NULL,
  `post_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `date_arrival` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  FULLTEXT KEY `device_name` (`device_name`)
) ENGINE=InnoDB AUTO_INCREMENT=262419 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.temperature_log_nfc
CREATE TABLE IF NOT EXISTS `temperature_log_nfc` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_temperature` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_mac` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `rssi` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_ID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `ClassName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `battery` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `device_humidity` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `Upload_time` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `date_arrival` datetime DEFAULT NULL,
  `post_data` longtext,
  `2` longtext,
  `3` longtext,
  `4` longtext,
  `5` longtext,
  `6` longtext,
  `7` longtext,
  `8` longtext,
  `9` longtext,
  `10` longtext,
  PRIMARY KEY (`id`) USING BTREE,
  FULLTEXT KEY `device_name` (`device_name`)
) ENGINE=InnoDB AUTO_INCREMENT=404 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.users
CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `api_key` varchar(255) DEFAULT NULL,
  `Upload_time` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.user_db
CREATE TABLE IF NOT EXISTS `user_db` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL,
  `user_pass` varchar(50) DEFAULT NULL,
  `corporateId` varchar(50) DEFAULT NULL,
  `userEmail` varchar(300) DEFAULT NULL,
  `userMobno` varchar(15) DEFAULT NULL,
  `user_role` varchar(50) DEFAULT NULL,
  `token` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `last_activity` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `mac_group_ip` int DEFAULT NULL,
  `isNotify` int DEFAULT '0',
  `isBlocked` int DEFAULT '0' COMMENT '1: yes 0: No ( so that we can send app update',
  `lastSeenTimeStamp` int DEFAULT '0',
  `isActive` int DEFAULT '0' COMMENT '1: active 0: inactive',
  `alertTypes` int DEFAULT NULL COMMENT 'seprated by , ( reference from tblAlertType)',
  `parentId` varchar(50) DEFAULT NULL COMMENT 'all parents seperated by ,',
  `profile_pic` varchar(500) DEFAULT NULL,
  `api_key` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `email` varchar(500) DEFAULT NULL,
  `countrycode` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `mobile` varchar(500) DEFAULT NULL,
  `fk_tableServerConfig` int DEFAULT '1',
  `fk_version` int DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_name` (`user_name`),
  KEY `FK_user_db_tblServerConfig` (`fk_tableServerConfig`),
  KEY `FK_verison_details` (`fk_version`),
  CONSTRAINT `FK_user_db_tblServerConfig` FOREIGN KEY (`fk_tableServerConfig`) REFERENCES `tblServerConfig` (`id`),
  CONSTRAINT `FK_verison_details` FOREIGN KEY (`fk_version`) REFERENCES `version` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for table tempsense.version
CREATE TABLE IF NOT EXISTS `version` (
  `id` int NOT NULL AUTO_INCREMENT,
  `versions` int DEFAULT NULL,
  `app_path` mediumtext,
  `app_extension` mediumtext,
  `app_upload_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_active` int DEFAULT NULL,
  `contact_us_url` mediumtext,
  `privacy_policy_url` mediumtext,
  `help_url` mediumtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.

-- Dumping structure for view tempsense.View1
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `View1` (
	`LOG2(marsnfc_log.device_temperature)` DOUBLE NULL,
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`LOG2(marsnfc_log.device_humidity)` DOUBLE NULL,
	`Upload_time` DATETIME NULL,
	`vbatt` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`rssi` INT(10) NULL,
	`device_name` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`latitude` DOUBLE NULL,
	`longitude` DOUBLE NULL
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_all_mac
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_all_mac` (
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`time` DATETIME NOT NULL
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_ALL_MAC_BC
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_ALL_MAC_BC` (
	`Max(marsnfc_log.Upload_time)` DATETIME NULL,
	`Max(marsnfc_log.id)` INT(10) NULL,
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_all_mac_bc
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_all_mac_bc` 
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_ALL_MAC_LAST_HOUR
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_ALL_MAC_LAST_HOUR` (
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`rssi` INT(10) NULL,
	`device_ID` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`vbatt` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`date_arrival` DATETIME NULL,
	`latitude` DOUBLE NULL,
	`longitude` DOUBLE NULL,
	`device_humidity` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`Upload_time` DATETIME NULL,
	`sync` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_name` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_by_device_12hrs
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_by_device_12hrs` (
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_humidity` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`rssi` INT(10) NULL,
	`vbatt` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`battery` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`Upload_time` DATETIME NULL,
	`latitude` DOUBLE NULL,
	`longitude` DOUBLE NULL,
	`userId` INT(10) NULL,
	`deviceModel` VARCHAR(100) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`id` INT(10) NOT NULL,
	`user_name` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_filter_by_groupid
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_filter_by_groupid` (
	`groupId` INT(10) NULL,
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`id` INT(10) NOT NULL,
	`sensorId` INT(10) NULL,
	`user_name` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`userId` INT(10) NULL,
	`device_alias` VARCHAR(500) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_name` VARCHAR(500) NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_filter_by_mac
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_filter_by_mac` (
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`rssi` INT(10) NULL,
	`vbatt` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_ID` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_name` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_humidity` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`Upload_time` DATETIME NULL,
	`device_alias` VARCHAR(500) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_min_temp` INT(10) NULL,
	`device_max_temp` INT(10) NULL,
	`device_min_humidity` INT(10) NULL,
	`device_max_humidity` INT(10) NULL,
	`device_audit_trail` INT(10) NULL,
	`device_userlink` INT(10) NULL,
	`isActive` INT(10) NULL COMMENT '1: active 0: inactive',
	`user_name` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`sensorId` INT(10) NULL,
	`groupId` INT(10) NULL
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_getMacByUser
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_getMacByUser` (
	`device_mac` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_name` VARCHAR(500) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_alias` VARCHAR(500) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_min_temp` BIGINT(19) NOT NULL,
	`device_max_temp` BIGINT(19) NOT NULL,
	`device_min_humidity` BIGINT(19) NOT NULL,
	`device_max_humidity` BIGINT(19) NOT NULL,
	`device_userlink` BIGINT(19) NOT NULL,
	`device_audit_trail` BIGINT(19) NOT NULL,
	`isActive` BIGINT(19) NOT NULL,
	`groupId` INT(10) NULL,
	`userId` INT(10) NULL
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_ghansoli
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_ghansoli` (
	`id` INT(10) NOT NULL,
	`time` DATETIME NULL,
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_history_with_mac_link
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_history_with_mac_link` (
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`rssi` INT(10) NULL,
	`device_ID` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`vbatt` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`date_arrival` DATETIME NULL,
	`latitude` DOUBLE NULL,
	`longitude` DOUBLE NULL,
	`device_humidity` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`Upload_time` DATETIME NULL,
	`sync` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_name` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_alias` VARCHAR(500) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_min_temp` INT(10) NULL,
	`device_max_temp` INT(10) NULL,
	`device_min_humidity` INT(10) NULL,
	`device_max_humidity` INT(10) NULL
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_interval_24hrs
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_interval_24hrs` (
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_humidity` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`rssi` INT(10) NULL,
	`vbatt` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`battery` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`Upload_time` DATETIME NULL,
	`latitude` DOUBLE NULL,
	`longitude` DOUBLE NULL,
	`userId` INT(10) NULL,
	`deviceModel` VARCHAR(100) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`id` INT(10) NOT NULL
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_interval_30min
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_interval_30min` (
	`id` INT(10) NOT NULL,
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_name` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`rssi` INT(10) NULL,
	`vbatt` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_ID` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`ClassName` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`battery` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_humidity` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`Upload_time` DATETIME NULL,
	`post_data` LONGTEXT NULL COLLATE 'utf8mb4_0900_ai_ci',
	`date_arrival` DATETIME NULL,
	`latitude` DOUBLE NULL,
	`longitude` DOUBLE NULL
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_las100
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_las100` (
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`time` BIGINT(19) NULL,
	`device_humidity` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_last1000
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_last1000` (
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_humidity` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_name` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`rssi` INT(10) NULL,
	`vbatt` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`battery` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`Upload_time` DATETIME NULL,
	`latitude` DOUBLE NULL,
	`longitude` DOUBLE NULL
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_last1000_device_user
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_last1000_device_user` (
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_humidity` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_name` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`rssi` INT(10) NULL,
	`vbatt` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`battery` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`Upload_time` DATETIME NULL,
	`latitude` DOUBLE NULL,
	`longitude` DOUBLE NULL,
	`deviceModel` VARCHAR(100) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`deviceId` VARCHAR(500) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`user_name` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_live_device
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_live_device` (
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`time` BIGINT(19) NULL,
	`device_humidity` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_MarsNFC
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_MarsNFC` (
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`rssi` INT(10) NULL,
	`vbatt` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`battery` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_humidity` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`Upload_time` DATETIME NULL,
	`latitude` DOUBLE NULL,
	`longitude` DOUBLE NULL,
	`sync` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_alias` VARCHAR(500) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_name` VARCHAR(500) NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_max_id_group_mac
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_max_id_group_mac` (
	`Max(marsnfc_log.id)` INT(10) NULL,
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_mondalez
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_mondalez` (
	`id` INT(10) NOT NULL,
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_name` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`rssi` INT(10) NULL,
	`vbatt` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_ID` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`ClassName` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`battery` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_humidity` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`Upload_time` DATETIME NULL,
	`post_data` LONGTEXT NULL COLLATE 'utf8mb4_0900_ai_ci',
	`date_arrival` DATETIME NULL,
	`latitude` DOUBLE NULL,
	`longitude` DOUBLE NULL
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_tempsense_filter
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_tempsense_filter` (
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_humidity` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_mac` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`device_name` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`vbatt` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`rssi` INT(10) NULL,
	`battery` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci',
	`Upload_time` DATETIME NULL,
	`latitude` DOUBLE NULL,
	`longitude` DOUBLE NULL
) ENGINE=MyISAM;

-- Dumping structure for view tempsense.view_Vashi
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `view_Vashi` (
	`id` INT(10) NOT NULL,
	`time` DATETIME NULL,
	`device_temperature` VARCHAR(50) NULL COLLATE 'utf8mb4_0900_ai_ci'
) ENGINE=MyISAM;

-- Dumping structure for procedure tempsense.add_new_mac
DELIMITER //
CREATE PROCEDURE `add_new_mac`()
BEGIN
INSERT INTO device_mac(device_mac) 

SELECT DISTINCT(device_mac)  FROM marsnfc_log WHERE Upload_time > NOW() - INTERVAL 2 DAY AND device_mac LIKE "BC%" 
AND device_mac NOT IN (SELECT device_mac.device_mac FROM device_mac);

END//
DELIMITER ;

-- Dumping structure for procedure tempsense.delete_junk
DELIMITER //
//
DELIMITER ;

-- Dumping structure for procedure tempsense.getData
DELIMITER //
//
DELIMITER ;

-- Dumping structure for procedure tempsense.HelloWorld
DELIMITER //
//
DELIMITER ;

-- Dumping structure for procedure tempsense.refreshGroupID
DELIMITER //
//
DELIMITER ;

-- Dumping structure for procedure tempsense.updateTestUsersMacList
DELIMITER //
CREATE PROCEDURE `updateTestUsersMacList`()
BEGIN
INSERT INTO tblGroupSensorRelationship(tblGroupSensorRelationship.groupId,tblGroupSensorRelationship.sensorId) 
SELECT 10,id FROM device_mac WHERE device_mac.device_mac  IN (SELECT DISTINCT(device_mac)  
FROM marsnfc_log WHERE Upload_time > NOW() - INTERVAL 2 DAY AND device_mac LIKE "BC%" );
END//
DELIMITER ;

-- Dumping structure for view tempsense.View1
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `View1`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `View1` AS select log2(`marsnfc_log`.`device_temperature`) AS `LOG2(marsnfc_log.device_temperature)`,`marsnfc_log`.`device_mac` AS `device_mac`,log2(`marsnfc_log`.`device_humidity`) AS `LOG2(marsnfc_log.device_humidity)`,`marsnfc_log`.`Upload_time` AS `Upload_time`,`marsnfc_log`.`vbatt` AS `vbatt`,`marsnfc_log`.`rssi` AS `rssi`,`marsnfc_log`.`device_name` AS `device_name`,`marsnfc_log`.`latitude` AS `latitude`,`marsnfc_log`.`longitude` AS `longitude` from `marsnfc_log` where ((`marsnfc_log`.`Upload_time` < (now() - interval 60 hour)) and ((minute(`marsnfc_log`.`Upload_time`) % 15) = 0) and (length(`marsnfc_log`.`device_mac`) > 1) and (0 <> `marsnfc_log`.`device_humidity`)) order by `marsnfc_log`.`Upload_time` limit 700;

-- Dumping structure for view tempsense.view_all_mac
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_all_mac`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_all_mac` AS select distinct `marsnfc_log`.`device_mac` AS `device_mac`,now() AS `time` from `marsnfc_log`;

-- Dumping structure for view tempsense.view_ALL_MAC_BC
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_ALL_MAC_BC`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_ALL_MAC_BC` AS select max(`marsnfc_log`.`Upload_time`) AS `Max(marsnfc_log.Upload_time)`,max(`marsnfc_log`.`id`) AS `Max(marsnfc_log.id)`,`marsnfc_log`.`device_mac` AS `device_mac` from `marsnfc_log` where ((`marsnfc_log`.`Upload_time` > (now() - interval 24 hour)) and regexp_like(`marsnfc_log`.`device_mac`,'^BC')) group by `marsnfc_log`.`device_mac`;

-- Dumping structure for view tempsense.view_all_mac_bc
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_all_mac_bc`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_all_mac_bc` AS select replace(`device_mac`.`device_mac`,':','') AS `REPLACE(device_mac.device_mac,":","")` from `device_mac` where (`device_mac`.`device_mac` like 'BC%');

-- Dumping structure for view tempsense.view_ALL_MAC_LAST_HOUR
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_ALL_MAC_LAST_HOUR`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_ALL_MAC_LAST_HOUR` AS select `marsnfc_log`.`device_temperature` AS `device_temperature`,`marsnfc_log`.`device_mac` AS `device_mac`,`marsnfc_log`.`rssi` AS `rssi`,`marsnfc_log`.`device_ID` AS `device_ID`,`marsnfc_log`.`vbatt` AS `vbatt`,`marsnfc_log`.`date_arrival` AS `date_arrival`,`marsnfc_log`.`latitude` AS `latitude`,`marsnfc_log`.`longitude` AS `longitude`,`marsnfc_log`.`device_humidity` AS `device_humidity`,`marsnfc_log`.`Upload_time` AS `Upload_time`,`marsnfc_log`.`sync` AS `sync`,`marsnfc_log`.`device_name` AS `device_name` from (`view_ALL_MAC_BC` left join `marsnfc_log` on((`view_ALL_MAC_BC`.`Max(marsnfc_log.id)` = `marsnfc_log`.`id`)));

-- Dumping structure for view tempsense.view_by_device_12hrs
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_by_device_12hrs`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_by_device_12hrs` AS select `marsnfc_log`.`device_temperature` AS `device_temperature`,`marsnfc_log`.`device_humidity` AS `device_humidity`,`marsnfc_log`.`device_mac` AS `device_mac`,`marsnfc_log`.`rssi` AS `rssi`,`marsnfc_log`.`vbatt` AS `vbatt`,`marsnfc_log`.`battery` AS `battery`,`marsnfc_log`.`Upload_time` AS `Upload_time`,`marsnfc_log`.`latitude` AS `latitude`,`marsnfc_log`.`longitude` AS `longitude`,`tblUserToken`.`userId` AS `userId`,`tblUserToken`.`deviceModel` AS `deviceModel`,`marsnfc_log`.`id` AS `id`,`user_db`.`user_name` AS `user_name` from ((`marsnfc_log` left join `tblUserToken` on((`marsnfc_log`.`device_ID` = `tblUserToken`.`deviceId`))) left join `user_db` on((`tblUserToken`.`userId` = `user_db`.`id`))) where (`marsnfc_log`.`Upload_time` > (now() - interval 12 hour)) order by `marsnfc_log`.`Upload_time` desc;

-- Dumping structure for view tempsense.view_filter_by_groupid
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_filter_by_groupid`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_filter_by_groupid` AS select `tblGroupSensorRelationship`.`groupId` AS `groupId`,replace(`device_mac`.`device_mac`,':','') AS `device_mac`,`user_db`.`id` AS `id`,`tblGroupSensorRelationship`.`sensorId` AS `sensorId`,`user_db`.`user_name` AS `user_name`,`tblGroupUserRelationship`.`userId` AS `userId`,`device_mac`.`device_alias` AS `device_alias`,`device_mac`.`device_name` AS `device_name` from (((`device_mac` left join `tblGroupSensorRelationship` on((`device_mac`.`id` = `tblGroupSensorRelationship`.`sensorId`))) join `tblGroupUserRelationship` on((`tblGroupUserRelationship`.`groupId` = `tblGroupSensorRelationship`.`groupId`))) join `user_db` on((`user_db`.`id` = `tblGroupUserRelationship`.`userId`))) where (`tblGroupUserRelationship`.`userId` = 1);

-- Dumping structure for view tempsense.view_filter_by_mac
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_filter_by_mac`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_filter_by_mac` AS select `marsnfc_log`.`device_temperature` AS `device_temperature`,`marsnfc_log`.`rssi` AS `rssi`,`marsnfc_log`.`vbatt` AS `vbatt`,`marsnfc_log`.`device_ID` AS `device_ID`,ifnull(`marsnfc_log`.`device_name`,'EMPTY') AS `device_name`,`marsnfc_log`.`device_mac` AS `device_mac`,`marsnfc_log`.`device_humidity` AS `device_humidity`,`marsnfc_log`.`Upload_time` AS `Upload_time`,`device_mac`.`device_alias` AS `device_alias`,`device_mac`.`device_min_temp` AS `device_min_temp`,`device_mac`.`device_max_temp` AS `device_max_temp`,`device_mac`.`device_min_humidity` AS `device_min_humidity`,`device_mac`.`device_max_humidity` AS `device_max_humidity`,`device_mac`.`device_audit_trail` AS `device_audit_trail`,`device_mac`.`device_userlink` AS `device_userlink`,`device_mac`.`isActive` AS `isActive`,`user_db`.`user_name` AS `user_name`,`tblGroupSensorRelationship`.`sensorId` AS `sensorId`,`tblGroupUserRelationship`.`groupId` AS `groupId` from ((((`marsnfc_log` left join `device_mac` on((`marsnfc_log`.`device_mac` = `device_mac`.`device_mac`))) join `tblGroupSensorRelationship` on((`tblGroupSensorRelationship`.`sensorId` = `device_mac`.`id`))) join `tblGroupUserRelationship` on((`tblGroupUserRelationship`.`groupId` = `tblGroupSensorRelationship`.`groupId`))) join `user_db` on((`tblGroupUserRelationship`.`userId` = `user_db`.`id`))) where ((`marsnfc_log`.`Upload_time` > (now() - interval 6 hour)) and `marsnfc_log`.`id` in (select `marsnfc_log`.`id` from `marsnfc_log` where (`marsnfc_log`.`device_mac` = 'BC572900CDBE'))) order by `marsnfc_log`.`Upload_time` desc limit 1;

-- Dumping structure for view tempsense.view_getMacByUser
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_getMacByUser`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_getMacByUser` AS select ifnull(`device_mac`.`device_mac`,0) AS `device_mac`,ifnull(`device_mac`.`device_name`,0) AS `device_name`,ifnull(`device_mac`.`device_alias`,0) AS `device_alias`,ifnull(`device_mac`.`device_min_temp`,0) AS `device_min_temp`,ifnull(`device_mac`.`device_max_temp`,0) AS `device_max_temp`,ifnull(`device_mac`.`device_min_humidity`,0) AS `device_min_humidity`,ifnull(`device_mac`.`device_max_humidity`,0) AS `device_max_humidity`,ifnull(`device_mac`.`device_userlink`,0) AS `device_userlink`,ifnull(`device_mac`.`device_audit_trail`,0) AS `device_audit_trail`,ifnull(`device_mac`.`isActive`,0) AS `isActive`,`tblGroupUserRelationship`.`groupId` AS `groupId`,`tblGroupUserRelationship`.`userId` AS `userId` from (`tblGroupUserRelationship` left join (`device_mac` left join `tblGroupSensorRelationship` on((`device_mac`.`id` = `tblGroupSensorRelationship`.`sensorId`))) on((`tblGroupSensorRelationship`.`groupId` = `tblGroupUserRelationship`.`groupId`)));

-- Dumping structure for view tempsense.view_ghansoli
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_ghansoli`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_ghansoli` AS select `marsnfc_log`.`id` AS `id`,`marsnfc_log`.`Upload_time` AS `time`,`marsnfc_log`.`device_temperature` AS `device_temperature` from `marsnfc_log` where (`marsnfc_log`.`device_mac` = 'BC572900CDBE');

-- Dumping structure for view tempsense.view_history_with_mac_link
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_history_with_mac_link`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_history_with_mac_link` AS select `marsnfc_log`.`device_temperature` AS `device_temperature`,`marsnfc_log`.`device_mac` AS `device_mac`,`marsnfc_log`.`rssi` AS `rssi`,`marsnfc_log`.`device_ID` AS `device_ID`,`marsnfc_log`.`vbatt` AS `vbatt`,`marsnfc_log`.`date_arrival` AS `date_arrival`,`marsnfc_log`.`latitude` AS `latitude`,`marsnfc_log`.`longitude` AS `longitude`,`marsnfc_log`.`device_humidity` AS `device_humidity`,`marsnfc_log`.`Upload_time` AS `Upload_time`,`marsnfc_log`.`sync` AS `sync`,`marsnfc_log`.`device_name` AS `device_name`,`device_mac`.`device_alias` AS `device_alias`,`device_mac`.`device_min_temp` AS `device_min_temp`,`device_mac`.`device_max_temp` AS `device_max_temp`,`device_mac`.`device_min_humidity` AS `device_min_humidity`,`device_mac`.`device_max_humidity` AS `device_max_humidity` from ((`view_ALL_MAC_BC` left join `marsnfc_log` on((`view_ALL_MAC_BC`.`Max(marsnfc_log.id)` = `marsnfc_log`.`id`))) join `device_mac` on((`view_ALL_MAC_BC`.`device_mac` = `device_mac`.`device_mac`)));

-- Dumping structure for view tempsense.view_interval_24hrs
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_interval_24hrs`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_interval_24hrs` AS select `marsnfc_log`.`device_temperature` AS `device_temperature`,`marsnfc_log`.`device_humidity` AS `device_humidity`,`marsnfc_log`.`device_mac` AS `device_mac`,`marsnfc_log`.`rssi` AS `rssi`,`marsnfc_log`.`vbatt` AS `vbatt`,`marsnfc_log`.`battery` AS `battery`,`marsnfc_log`.`Upload_time` AS `Upload_time`,`marsnfc_log`.`latitude` AS `latitude`,`marsnfc_log`.`longitude` AS `longitude`,`tblUserToken`.`userId` AS `userId`,`tblUserToken`.`deviceModel` AS `deviceModel`,`marsnfc_log`.`id` AS `id` from (`marsnfc_log` left join `tblUserToken` on((`marsnfc_log`.`device_ID` = `tblUserToken`.`deviceId`))) where (`marsnfc_log`.`Upload_time` > (now() - interval 12 hour)) order by `marsnfc_log`.`Upload_time` desc;

-- Dumping structure for view tempsense.view_interval_30min
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_interval_30min`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_interval_30min` AS select `marsnfc_log`.`id` AS `id`,`marsnfc_log`.`device_temperature` AS `device_temperature`,`marsnfc_log`.`device_name` AS `device_name`,`marsnfc_log`.`device_mac` AS `device_mac`,`marsnfc_log`.`rssi` AS `rssi`,`marsnfc_log`.`vbatt` AS `vbatt`,`marsnfc_log`.`device_ID` AS `device_ID`,`marsnfc_log`.`ClassName` AS `ClassName`,`marsnfc_log`.`battery` AS `battery`,`marsnfc_log`.`device_humidity` AS `device_humidity`,`marsnfc_log`.`Upload_time` AS `Upload_time`,`marsnfc_log`.`post_data` AS `post_data`,`marsnfc_log`.`date_arrival` AS `date_arrival`,`marsnfc_log`.`latitude` AS `latitude`,`marsnfc_log`.`longitude` AS `longitude` from `marsnfc_log` where (((minute(`marsnfc_log`.`Upload_time`) % 30) = 0) and `marsnfc_log`.`device_mac` in (select distinct `marsnfc_log`.`device_mac` from `marsnfc_log` where (`marsnfc_log`.`device_mac` like 'BC%')));

-- Dumping structure for view tempsense.view_las100
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_las100`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_las100` AS select `marsnfc_log`.`device_mac` AS `device_mac`,unix_timestamp(`marsnfc_log`.`Upload_time`) AS `time`,`marsnfc_log`.`device_humidity` AS `device_humidity`,`marsnfc_log`.`device_temperature` AS `device_temperature` from `marsnfc_log` where (`marsnfc_log`.`device_mac` like 'BC572900CDBE') order by `marsnfc_log`.`Upload_time` desc;

-- Dumping structure for view tempsense.view_last1000
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_last1000`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_last1000` AS select `marsnfc_log`.`device_temperature` AS `device_temperature`,`marsnfc_log`.`device_humidity` AS `device_humidity`,`marsnfc_log`.`device_name` AS `device_name`,`marsnfc_log`.`device_mac` AS `device_mac`,`marsnfc_log`.`rssi` AS `rssi`,`marsnfc_log`.`vbatt` AS `vbatt`,`marsnfc_log`.`battery` AS `battery`,`marsnfc_log`.`Upload_time` AS `Upload_time`,`marsnfc_log`.`latitude` AS `latitude`,`marsnfc_log`.`longitude` AS `longitude` from `marsnfc_log` order by `marsnfc_log`.`id` desc limit 1000;

-- Dumping structure for view tempsense.view_last1000_device_user
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_last1000_device_user`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_last1000_device_user` AS select `marsnfc_log`.`device_temperature` AS `device_temperature`,`marsnfc_log`.`device_humidity` AS `device_humidity`,`marsnfc_log`.`device_name` AS `device_name`,`marsnfc_log`.`device_mac` AS `device_mac`,`marsnfc_log`.`rssi` AS `rssi`,`marsnfc_log`.`vbatt` AS `vbatt`,`marsnfc_log`.`battery` AS `battery`,`marsnfc_log`.`Upload_time` AS `Upload_time`,`marsnfc_log`.`latitude` AS `latitude`,`marsnfc_log`.`longitude` AS `longitude`,`tblUserToken`.`deviceModel` AS `deviceModel`,`tblUserToken`.`deviceId` AS `deviceId`,`user_db`.`user_name` AS `user_name` from ((`marsnfc_log` left join `tblUserToken` on((`marsnfc_log`.`device_ID` = `tblUserToken`.`deviceId`))) left join `user_db` on((`tblUserToken`.`userId` = `user_db`.`id`))) order by `marsnfc_log`.`id` desc limit 1000;

-- Dumping structure for view tempsense.view_live_device
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_live_device`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_live_device` AS select `marsnfc_log`.`device_mac` AS `device_mac`,unix_timestamp(`marsnfc_log`.`Upload_time`) AS `time`,`marsnfc_log`.`device_humidity` AS `device_humidity`,`marsnfc_log`.`device_temperature` AS `device_temperature` from `marsnfc_log` where (`marsnfc_log`.`device_mac` like 'BC572900CDBE') order by `marsnfc_log`.`Upload_time` desc limit 5000;

-- Dumping structure for view tempsense.view_MarsNFC
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_MarsNFC`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_MarsNFC` AS select `marsnfc_log`.`device_temperature` AS `device_temperature`,`marsnfc_log`.`device_mac` AS `device_mac`,`marsnfc_log`.`rssi` AS `rssi`,`marsnfc_log`.`vbatt` AS `vbatt`,`marsnfc_log`.`battery` AS `battery`,`marsnfc_log`.`device_humidity` AS `device_humidity`,`marsnfc_log`.`Upload_time` AS `Upload_time`,`marsnfc_log`.`latitude` AS `latitude`,`marsnfc_log`.`longitude` AS `longitude`,`marsnfc_log`.`sync` AS `sync`,`device_mac`.`device_alias` AS `device_alias`,`device_mac`.`device_name` AS `device_name` from (`marsnfc_log` join `device_mac` on((`marsnfc_log`.`device_mac` = `device_mac`.`device_mac`))) where (`marsnfc_log`.`Upload_time` > (now() - interval 1 day));

-- Dumping structure for view tempsense.view_max_id_group_mac
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_max_id_group_mac`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_max_id_group_mac` AS select max(`marsnfc_log`.`id`) AS `Max(marsnfc_log.id)`,`marsnfc_log`.`device_mac` AS `device_mac` from `marsnfc_log` where (`marsnfc_log`.`Upload_time` > (now() - interval 1 hour)) group by `marsnfc_log`.`device_mac`;

-- Dumping structure for view tempsense.view_mondalez
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_mondalez`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_mondalez` AS select `marsnfc_log`.`id` AS `id`,`marsnfc_log`.`device_temperature` AS `device_temperature`,`marsnfc_log`.`device_name` AS `device_name`,`marsnfc_log`.`device_mac` AS `device_mac`,`marsnfc_log`.`rssi` AS `rssi`,`marsnfc_log`.`vbatt` AS `vbatt`,`marsnfc_log`.`device_ID` AS `device_ID`,`marsnfc_log`.`ClassName` AS `ClassName`,`marsnfc_log`.`battery` AS `battery`,`marsnfc_log`.`device_humidity` AS `device_humidity`,`marsnfc_log`.`Upload_time` AS `Upload_time`,`marsnfc_log`.`post_data` AS `post_data`,`marsnfc_log`.`date_arrival` AS `date_arrival`,`marsnfc_log`.`latitude` AS `latitude`,`marsnfc_log`.`longitude` AS `longitude` from `marsnfc_log` where `marsnfc_log`.`id` in (select max(`marsnfc_log`.`id`) from `marsnfc_log` where `marsnfc_log`.`device_mac` in (select distinct `marsnfc_log`.`device_mac` from `marsnfc_log` where (`marsnfc_log`.`device_mac` in ('BC:57:29:00:CD:AA','BC:57:29:00:CD:BE','BC572900CDAA','BC572900CDBE'))) group by `marsnfc_log`.`device_mac`);

-- Dumping structure for view tempsense.view_tempsense_filter
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_tempsense_filter`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_tempsense_filter` AS select `marsnfc_log`.`device_temperature` AS `device_temperature`,`marsnfc_log`.`device_humidity` AS `device_humidity`,`marsnfc_log`.`device_mac` AS `device_mac`,`marsnfc_log`.`device_name` AS `device_name`,`marsnfc_log`.`vbatt` AS `vbatt`,`marsnfc_log`.`rssi` AS `rssi`,`marsnfc_log`.`battery` AS `battery`,`marsnfc_log`.`Upload_time` AS `Upload_time`,`marsnfc_log`.`latitude` AS `latitude`,`marsnfc_log`.`longitude` AS `longitude` from `marsnfc_log`;

-- Dumping structure for view tempsense.view_Vashi
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `view_Vashi`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `view_Vashi` AS select `marsnfc_log`.`id` AS `id`,`marsnfc_log`.`Upload_time` AS `time`,`marsnfc_log`.`device_temperature` AS `device_temperature` from `marsnfc_log` where (`marsnfc_log`.`device_mac` = 'BC572900CDAA');

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;
