-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Nov 09, 2025 at 05:19 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!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 */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `iot_campus`
--

-- --------------------------------------------------------

--
-- Table structure for table `environment`
--

CREATE TABLE `environment` (
  `id` bigint(20) NOT NULL,
  `iot_id` varchar(50) NOT NULL,
  `zone_id` int(11) DEFAULT NULL,
  `datetime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `temperature` decimal(5,2) DEFAULT NULL,
  `humidity` decimal(5,2) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `environment`
--

INSERT INTO `environment` (`id`, `iot_id`, `zone_id`, `datetime`, `temperature`, `humidity`, `created_at`) VALUES
(3, 'ESP32_002', 7, '2025-11-09 06:22:25', 23.80, 53.00, '2025-11-09 06:22:25'),
(4, 'ESP32_002', 7, '2025-11-09 06:27:25', 23.80, 53.00, '2025-11-09 06:27:25'),
(5, 'ESP32_002', 7, '2025-11-09 07:57:44', 24.50, 69.00, '2025-11-09 07:57:44'),
(6, 'ESP32_002', 7, '2025-11-09 08:02:46', 24.50, 69.00, '2025-11-09 08:02:46'),
(7, 'ESP32_002', 7, '2025-11-09 08:07:47', 31.80, 98.00, '2025-11-09 08:07:47'),
(8, 'ESP32_002', 7, '2025-11-09 08:12:48', 25.30, 65.00, '2025-11-09 08:12:48'),
(9, 'ESP32_004', 8, '2025-11-09 08:12:51', 26.10, 46.00, '2025-11-09 08:12:51'),
(10, 'ESP32_002', 7, '2025-11-09 08:17:50', 25.30, 62.00, '2025-11-09 08:17:50'),
(11, 'ESP32_004', 8, '2025-11-09 08:17:52', 26.00, 45.00, '2025-11-09 08:17:52'),
(12, 'ESP32_002', 7, '2025-11-09 08:22:51', 25.30, 62.00, '2025-11-09 08:22:51'),
(13, 'ESP32_004', 8, '2025-11-09 08:22:53', 26.50, 45.00, '2025-11-09 08:22:53'),
(14, 'ESP32_002', 7, '2025-11-09 08:27:52', 25.30, 62.00, '2025-11-09 08:27:52'),
(15, 'ESP32_004', 8, '2025-11-09 08:27:54', 27.00, 43.00, '2025-11-09 08:27:54'),
(16, 'ESP32_002', 7, '2025-11-09 08:32:53', 25.80, 60.00, '2025-11-09 08:32:53'),
(17, 'ESP32_004', 8, '2025-11-09 08:32:56', 26.80, 43.00, '2025-11-09 08:32:56'),
(18, 'ESP32_002', 7, '2025-11-09 08:37:55', 25.80, 60.00, '2025-11-09 08:37:55'),
(19, 'ESP32_004', 8, '2025-11-09 08:37:57', 27.00, 42.00, '2025-11-09 08:37:57'),
(20, 'ESP32_002', 7, '2025-11-09 08:42:56', 26.20, 59.00, '2025-11-09 08:42:56'),
(21, 'ESP32_004', 8, '2025-11-09 08:42:58', 26.80, 41.00, '2025-11-09 08:42:58'),
(22, 'ESP32_002', 7, '2025-11-09 08:47:58', 25.80, 57.00, '2025-11-09 08:47:58'),
(23, 'ESP32_004', 8, '2025-11-09 08:47:58', 26.60, 40.00, '2025-11-09 08:47:58'),
(24, 'ESP32_002', 7, '2025-11-09 08:52:59', 25.40, 57.00, '2025-11-09 08:52:59'),
(25, 'ESP32_004', 8, '2025-11-09 08:52:59', 26.50, 40.00, '2025-11-09 08:52:59'),
(26, 'ESP32_002', 7, '2025-11-09 08:58:00', 25.30, 57.00, '2025-11-09 08:58:00'),
(27, 'ESP32_004', 8, '2025-11-09 08:58:00', 26.50, 40.00, '2025-11-09 08:58:00'),
(28, 'ESP32_002', 7, '2025-11-09 09:18:59', 25.80, 54.00, '2025-11-09 09:18:59'),
(29, 'ESP32_004', 8, '2025-11-09 09:18:59', 27.00, 39.00, '2025-11-09 09:18:59'),
(30, 'ESP32_002', 7, '2025-11-09 10:34:19', 25.00, 54.00, '2025-11-09 10:34:19'),
(31, 'ESP32_004', 8, '2025-11-09 10:34:23', 26.70, 38.00, '2025-11-09 10:34:23'),
(32, 'ESP32_002', 7, '2025-11-09 10:39:22', 24.80, 54.00, '2025-11-09 10:39:22'),
(33, 'ESP32_004', 8, '2025-11-09 10:39:24', 26.60, 38.00, '2025-11-09 10:39:24');

-- --------------------------------------------------------

--
-- Table structure for table `iot`
--

CREATE TABLE `iot` (
  `iot_id` varchar(50) NOT NULL,
  `zone_id` int(11) DEFAULT NULL,
  `iot_type` enum('environment','power') NOT NULL,
  `status` enum('ONLINE','OFFLINE') DEFAULT 'OFFLINE',
  `last_seen` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `iot`
--

INSERT INTO `iot` (`iot_id`, `zone_id`, `iot_type`, `status`, `last_seen`, `created_at`, `updated_at`) VALUES
('ESP32_001', 7, 'power', 'ONLINE', '2025-11-09 16:39:21', '2025-11-08 08:31:38', '2025-11-09 10:39:21'),
('ESP32_002', 7, 'environment', 'ONLINE', '2025-11-09 16:39:22', '2025-11-08 08:32:11', '2025-11-09 10:39:22'),
('ESP32_003', 8, 'power', 'ONLINE', '2025-11-09 16:39:20', '2025-11-09 08:06:50', '2025-11-09 10:39:20'),
('ESP32_004', 8, 'environment', 'ONLINE', '2025-11-09 16:39:24', '2025-11-09 08:07:43', '2025-11-09 10:39:24');

-- --------------------------------------------------------

--
-- Table structure for table `power_consumption`
--

CREATE TABLE `power_consumption` (
  `id` bigint(20) NOT NULL,
  `iot_id` varchar(50) NOT NULL,
  `zone_id` int(11) DEFAULT NULL,
  `datetime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `voltage` decimal(7,2) DEFAULT NULL,
  `current` decimal(7,2) DEFAULT NULL,
  `power` decimal(7,2) DEFAULT NULL,
  `energy` decimal(10,3) DEFAULT NULL,
  `frequency` decimal(5,2) DEFAULT NULL,
  `power_factor` decimal(4,2) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `power_consumption`
--

INSERT INTO `power_consumption` (`id`, `iot_id`, `zone_id`, `datetime`, `voltage`, `current`, `power`, `energy`, `frequency`, `power_factor`, `created_at`) VALUES
(6, 'ESP32_001', 7, '2025-11-09 07:01:17', 234.60, 0.00, 0.00, 0.010, NULL, NULL, '2025-11-09 07:01:17'),
(7, 'ESP32_001', 7, '2025-11-09 07:06:18', 234.60, 0.00, 0.00, 0.010, NULL, NULL, '2025-11-09 07:06:18'),
(8, 'ESP32_001', 7, '2025-11-09 07:09:40', 235.10, 0.00, 0.00, 0.010, 50.10, 0.00, '2025-11-09 07:09:40'),
(9, 'ESP32_001', 7, '2025-11-09 07:57:41', 238.20, 0.00, 2.30, 0.010, 50.00, 1.00, '2025-11-09 07:57:41'),
(10, 'ESP32_001', 7, '2025-11-09 08:02:42', 235.10, 1.78, 415.00, 0.017, 50.00, 0.99, '2025-11-09 08:02:42'),
(11, 'ESP32_001', 7, '2025-11-09 08:07:43', 236.80, 0.00, 1.90, 0.018, 49.80, 1.00, '2025-11-09 08:07:43'),
(12, 'ESP32_001', 7, '2025-11-09 08:12:45', 237.30, 0.00, 1.90, 0.018, 50.00, 1.00, '2025-11-09 08:12:45'),
(13, 'ESP32_003', 8, '2025-11-09 08:15:04', 236.70, 0.08, 1.20, 0.010, 49.90, 0.07, '2025-11-09 08:15:04'),
(14, 'ESP32_001', 7, '2025-11-09 08:17:46', 237.10, 0.00, 2.00, 0.019, 50.00, 1.00, '2025-11-09 08:17:46'),
(15, 'ESP32_003', 8, '2025-11-09 08:20:05', 238.00, 0.00, 1.90, 0.010, 50.10, 1.00, '2025-11-09 08:20:05'),
(16, 'ESP32_001', 7, '2025-11-09 08:22:48', 238.00, 0.00, 2.00, 0.019, 50.00, 1.00, '2025-11-09 08:22:48'),
(17, 'ESP32_003', 8, '2025-11-09 08:25:06', 238.80, 0.00, 1.90, 0.010, 50.10, 1.00, '2025-11-09 08:25:06'),
(18, 'ESP32_001', 7, '2025-11-09 08:27:49', 238.20, 0.00, 1.90, 0.019, 50.10, 1.00, '2025-11-09 08:27:49'),
(19, 'ESP32_003', 8, '2025-11-09 08:30:07', 238.30, 0.00, 2.00, 0.010, 50.00, 1.00, '2025-11-09 08:30:07'),
(20, 'ESP32_001', 7, '2025-11-09 08:32:51', 238.20, 0.00, 2.00, 0.019, 50.00, 1.00, '2025-11-09 08:32:51'),
(21, 'ESP32_003', 8, '2025-11-09 08:35:09', 238.40, 0.00, 1.80, 0.010, 50.00, 1.00, '2025-11-09 08:35:09'),
(22, 'ESP32_001', 7, '2025-11-09 08:37:51', 238.30, 0.00, 2.00, 0.019, 50.00, 1.00, '2025-11-09 08:37:51'),
(23, 'ESP32_003', 8, '2025-11-09 08:40:11', 238.80, 0.00, 1.90, 0.011, 50.00, 1.00, '2025-11-09 08:40:11'),
(24, 'ESP32_001', 7, '2025-11-09 08:42:53', 237.80, 0.00, 2.10, 0.019, 50.00, 1.00, '2025-11-09 08:42:53'),
(25, 'ESP32_003', 8, '2025-11-09 08:45:13', 238.70, 0.00, 2.30, 0.011, 50.10, 1.00, '2025-11-09 08:45:13'),
(26, 'ESP32_001', 7, '2025-11-09 08:47:54', 238.40, 0.00, 2.00, 0.020, 50.10, 1.00, '2025-11-09 08:47:54'),
(27, 'ESP32_003', 8, '2025-11-09 08:50:14', 238.50, 0.00, 2.10, 0.011, 50.00, 1.00, '2025-11-09 08:50:14'),
(28, 'ESP32_001', 7, '2025-11-09 08:52:55', 237.40, 0.00, 1.90, 0.020, 50.00, 1.00, '2025-11-09 08:52:55'),
(29, 'ESP32_003', 8, '2025-11-09 08:55:15', 237.70, 0.00, 2.00, 0.011, 50.00, 1.00, '2025-11-09 08:55:15'),
(30, 'ESP32_001', 7, '2025-11-09 08:57:57', 237.80, 0.00, 1.90, 0.020, 50.00, 1.00, '2025-11-09 08:57:57'),
(31, 'ESP32_003', 8, '2025-11-09 09:00:15', 238.10, 0.00, 1.80, 0.011, 50.10, 1.00, '2025-11-09 09:00:15'),
(32, 'ESP32_001', 7, '2025-11-09 09:18:57', 234.00, 0.00, 1.90, 0.021, 50.00, 1.00, '2025-11-09 09:18:57'),
(33, 'ESP32_003', 8, '2025-11-09 09:18:59', 234.30, 0.00, 1.90, 0.012, 50.00, 1.00, '2025-11-09 09:18:59'),
(34, 'ESP32_003', 8, '2025-11-09 10:34:17', 231.80, 0.03, 1.90, 0.014, 49.70, 0.26, '2025-11-09 10:34:17'),
(35, 'ESP32_001', 7, '2025-11-09 10:34:19', 231.60, 0.00, 1.90, 0.023, 49.80, 1.00, '2025-11-09 10:34:19'),
(36, 'ESP32_003', 8, '2025-11-09 10:39:20', 231.80, 0.00, 1.80, 0.014, 49.80, 1.00, '2025-11-09 10:39:20'),
(37, 'ESP32_001', 7, '2025-11-09 10:39:21', 231.20, 0.00, 1.80, 0.023, 49.80, 1.00, '2025-11-09 10:39:21');

-- --------------------------------------------------------

--
-- Table structure for table `zone`
--

CREATE TABLE `zone` (
  `zone_id` int(11) NOT NULL,
  `label` varchar(100) NOT NULL,
  `floor_level` int(11) NOT NULL,
  `direction` enum('north','south','east','west') NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `zone`
--

INSERT INTO `zone` (`zone_id`, `label`, `floor_level`, `direction`, `created_at`, `updated_at`) VALUES
(7, '4A', 4, 'north', '2025-11-08 08:11:10', '2025-11-08 08:11:10'),
(8, '4B', 4, 'south', '2025-11-08 08:11:46', '2025-11-08 08:11:46'),
(9, '4C', 4, 'east', '2025-11-08 08:12:01', '2025-11-08 08:12:01'),
(10, '4D', 4, 'west', '2025-11-08 08:12:13', '2025-11-08 08:12:13');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `environment`
--
ALTER TABLE `environment`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_env_zone` (`zone_id`),
  ADD KEY `idx_env_datetime` (`datetime`),
  ADD KEY `idx_env_iot_datetime` (`iot_id`,`datetime`);

--
-- Indexes for table `iot`
--
ALTER TABLE `iot`
  ADD PRIMARY KEY (`iot_id`),
  ADD KEY `fk_iot_zone` (`zone_id`);

--
-- Indexes for table `power_consumption`
--
ALTER TABLE `power_consumption`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_power_zone` (`zone_id`),
  ADD KEY `idx_power_datetime` (`datetime`),
  ADD KEY `idx_power_iot_datetime` (`iot_id`,`datetime`);

--
-- Indexes for table `zone`
--
ALTER TABLE `zone`
  ADD PRIMARY KEY (`zone_id`),
  ADD KEY `idx_zone_floor` (`floor_level`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `environment`
--
ALTER TABLE `environment`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=34;

--
-- AUTO_INCREMENT for table `power_consumption`
--
ALTER TABLE `power_consumption`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38;

--
-- AUTO_INCREMENT for table `zone`
--
ALTER TABLE `zone`
  MODIFY `zone_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `environment`
--
ALTER TABLE `environment`
  ADD CONSTRAINT `fk_env_iot` FOREIGN KEY (`iot_id`) REFERENCES `iot` (`iot_id`),
  ADD CONSTRAINT `fk_env_zone` FOREIGN KEY (`zone_id`) REFERENCES `zone` (`zone_id`) ON DELETE SET NULL;

--
-- Constraints for table `iot`
--
ALTER TABLE `iot`
  ADD CONSTRAINT `fk_iot_zone` FOREIGN KEY (`zone_id`) REFERENCES `zone` (`zone_id`) ON DELETE SET NULL;

--
-- Constraints for table `power_consumption`
--
ALTER TABLE `power_consumption`
  ADD CONSTRAINT `fk_power_iot` FOREIGN KEY (`iot_id`) REFERENCES `iot` (`iot_id`),
  ADD CONSTRAINT `fk_power_zone` FOREIGN KEY (`zone_id`) REFERENCES `zone` (`zone_id`) ON DELETE SET NULL;
COMMIT;

/*!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 */;
