CREATE DATABASE  IF NOT EXISTS `iot_irrigation_controller_dev` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `iot_irrigation_controller_dev`;
--
-- Host: localhost    Database: iot_irrigation_controller_dev
-- ------------------------------------------------------


--
-- Table structure for table `controllers`
--

CREATE TABLE `controllers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `serial_number` varchar(255) NOT NULL,
  `firmware_version` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `serial_number` (`serial_number`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `controllers`
--

INSERT INTO `controllers` VALUES (1,'9002449','v1.0.1','2024-12-23 11:32:26'),(2,'922573','v1.1.0','2024-12-23 11:32:26'),(3,'6746570','v2.0.0','2024-12-23 11:32:26');

--
-- Table structure for table `firmware`
--

CREATE TABLE `firmware` (
  `id` int NOT NULL AUTO_INCREMENT,
  `version` varchar(255) NOT NULL,
  `release_date` date NOT NULL,
  `url` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `version` (`version`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `firmware`
--

INSERT INTO `firmware` VALUES (1,'v1.0.0','2023-01-01','https://example.com/firmware/v1.0.0','2024-12-23 11:33:46'),(2,'v1.1.0','2023-06-01','https://example.com/firmware/v1.1.0','2024-12-23 11:33:46'),(3,'v2.0.0','2023-12-01','https://example.com/firmware/v2.0.0','2024-12-23 11:33:46'),(5,'v2.0.1','2023-12-01','https://example.com/firmware/v1.0.0','2024-12-23 12:32:43');

--
-- Table structure for table `logs`
--

CREATE TABLE `logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `controller_id` int NOT NULL,
  `relay_number` tinyint NOT NULL,
  `action` enum('ON','OFF') NOT NULL,
  `event_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `controller_id` (`controller_id`),
  CONSTRAINT `logs_ibfk_1` FOREIGN KEY (`controller_id`) REFERENCES `controllers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `logs`
--

INSERT INTO `logs` VALUES (1,1,1,'ON','2023-12-01 08:00:00'),(2,1,2,'OFF','2023-12-01 10:00:00'),(3,2,1,'ON','2023-12-01 12:00:00'),(4,1,1,'ON','2023-12-01 08:00:00');

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `product_id` int NOT NULL,
  `order_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` VALUES (1,1,1,'2024-12-23 11:34:12'),(2,1,2,'2024-12-23 11:34:12'),(3,2,3,'2024-12-23 11:34:12');

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `products`
--

INSERT INTO `products` VALUES (1,'Smart Controller','A high-tech controller with advanced features',199.99,'2024-12-23 11:34:05'),(2,'Relay Module','A reliable relay module for automation',49.99,'2024-12-23 11:34:05'),(3,'Sensor Kit','A complete sensor kit for all environments',99.99,'2024-12-23 11:34:05'),(4,'Smart Controller','High-tech controller',199.99,'2024-12-23 12:34:11'),(5,'Smart Controller','High-tech controller',199.99,'2024-12-23 12:34:26');

--
-- Table structure for table `relays`
--

CREATE TABLE `relays` (
  `id` int NOT NULL AUTO_INCREMENT,
  `controller_id` int NOT NULL,
  `relay_number` tinyint NOT NULL,
  `configuration` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `controller_id` (`controller_id`),
  CONSTRAINT `relays_ibfk_1` FOREIGN KEY (`controller_id`) REFERENCES `controllers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `relays`
--

INSERT INTO `relays` VALUES (1,1,1,'{\"mode\": \"auto\"}'),(2,1,2,'{\"mode\": \"manual\"}'),(3,2,1,'{\"mode\": \"auto\"}');

--
-- Table structure for table `schedules`
--

CREATE TABLE `schedules` (
  `id` int NOT NULL AUTO_INCREMENT,
  `relay_id` int NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `frequency` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `relay_id` (`relay_id`),
  CONSTRAINT `schedules_ibfk_1` FOREIGN KEY (`relay_id`) REFERENCES `relays` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `schedules`
--

INSERT INTO `schedules` VALUES (1,1,'08:00:00','10:00:00','Daily'),(2,2,'12:00:00','14:00:00','Weekdays'),(3,3,'18:00:00','20:00:00','Weekends');

--
-- Table structure for table `sensordatalogs`
--

CREATE TABLE `sensordatalogs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sensor_id` int NOT NULL,
  `timestamp` datetime NOT NULL,
  `data` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sensor_id` (`sensor_id`),
  CONSTRAINT `sensordatalogs_ibfk_1` FOREIGN KEY (`sensor_id`) REFERENCES `sensors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `sensordatalogs`
--

INSERT INTO `sensordatalogs` VALUES (1,1,'2023-12-01 08:00:00','{\"temperature\": \"22.5\"}'),(2,2,'2023-12-01 08:10:00','{\"humidity\": \"55\"}'),(3,3,'2023-12-01 08:20:00','{\"pressure\": \"101.3\"}');

--
-- Table structure for table `sensors`
--

CREATE TABLE `sensors` (
  `id` int NOT NULL AUTO_INCREMENT,
  `controller_id` int NOT NULL,
  `type` varchar(255) NOT NULL,
  `serial_number` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `serial_number` (`serial_number`),
  KEY `controller_id` (`controller_id`),
  CONSTRAINT `sensors_ibfk_1` FOREIGN KEY (`controller_id`) REFERENCES `controllers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `sensors`
--

INSERT INTO `sensors` VALUES (1,1,'Temperature','SN123TEMP','Temp Sensor 1','2024-12-23 11:33:20'),(2,2,'Humidity','SN456HUMID','Humidity Sensor 1','2024-12-23 11:33:20'),(3,3,'Pressure','SN789PRESS','Pressure Sensor 1','2024-12-23 11:33:20');

--
-- Table structure for table `usercontrollers`
--

CREATE TABLE `usercontrollers` (
  `user_id` int NOT NULL,
  `controller_id` int NOT NULL,
  `userDefinedControllerName` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`user_id`,`controller_id`),
  KEY `controller_id` (`controller_id`),
  CONSTRAINT `usercontrollers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `usercontrollers_ibfk_2` FOREIGN KEY (`controller_id`) REFERENCES `controllers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `usercontrollers`
--

INSERT INTO `usercontrollers` VALUES (29,1,'BOX Irrigation Controller'),(29,2,'PCB Irrigation Controller Sagar'),(30,3,'New PCB');

--
-- Table structure for table `usersensors`
--

CREATE TABLE `usersensors` (
  `user_id` int NOT NULL,
  `controller_id` int NOT NULL,
  `sensor_id` int NOT NULL,
  PRIMARY KEY (`user_id`,`controller_id`,`sensor_id`),
  KEY `controller_id` (`controller_id`),
  KEY `sensor_id` (`sensor_id`),
  CONSTRAINT `usersensors_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `usersensors_ibfk_2` FOREIGN KEY (`controller_id`) REFERENCES `controllers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `usersensors_ibfk_3` FOREIGN KEY (`sensor_id`) REFERENCES `sensors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `usersensors`
--

INSERT INTO `usersensors` VALUES (1,1,1),(2,2,2);

--
-- Dumping events for database 'iot_irrigation_controller_dev'
--

--
-- Dumping routines for database 'iot_irrigation_controller_dev'
--


