-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `smartaqua`
--

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

--
-- Table structure for table `control_status`
--

CREATE TABLE `control_status` (
  `id` int(11) NOT NULL,
  `device_id` varchar(50) DEFAULT NULL,
  `pump` varchar(10) DEFAULT 'OFF',
  `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 `control_status`
--

INSERT INTO `control_status` (`id`, `device_id`, `pump`, `updated_at`) VALUES
(1, 'DEV1764733466563', 'OFF', '2025-12-11 02:53:01');

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

--
-- Table structure for table `devices`
--

CREATE TABLE `devices` (
  `id` varchar(50) NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `fish_type` varchar(100) NOT NULL,
  `status` enum('online','offline') DEFAULT 'offline',
  `type` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `devices`
--

INSERT INTO `devices` (`id`, `user_id`, `name`, `fish_type`, `status`, `type`, `created_at`) VALUES
('DEV1764733466563', 7, 'kolam 1', 'ikan nila', 'offline', 'fish_pond', '2025-12-03 03:43:49'),
('DEV1764734070752', 7, 'kolam test', 'ikan lele', 'offline', 'fish_pond', '2025-12-03 03:53:53'),
('DEV1764735176895', 7, 'testttt', 'teri', 'offline', 'fish_pond', '2025-12-03 04:12:19'),
('DEV1764746005474', 7, 'testya', 'ikan', 'offline', 'fish_pond', '2025-12-03 07:13:27');

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

--
-- Table structure for table `feeder_start_config`
--

CREATE TABLE `feeder_start_config` (
  `id` int(11) NOT NULL,
  `morning_schedule` varchar(10) NOT NULL DEFAULT '07:00',
  `evening_schedule` varchar(10) NOT NULL DEFAULT '16:00',
  `fish_count` int(11) NOT NULL DEFAULT 100,
  `fish_weight_start` float NOT NULL DEFAULT 0,
  `fish_age_start` int(11) NOT NULL DEFAULT 0,
  `feed_amount_per_schedule` float NOT NULL DEFAULT 500,
  `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 `feeder_start_config`
--

INSERT INTO `feeder_start_config` (`id`, `morning_schedule`, `evening_schedule`, `fish_count`, `fish_weight_start`, `fish_age_start`, `feed_amount_per_schedule`, `updated_at`) VALUES
(1, '06:00', '18:00', 500, 30, 5, 400, '2025-12-10 03:25:47');

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

--
-- Table structure for table `feed_status`
--

CREATE TABLE `feed_status` (
  `id` int(11) NOT NULL,
  `food_remaining` float NOT NULL,
  `raw_reading` float DEFAULT NULL,
  `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 `feed_status`
--

INSERT INTO `feed_status` (`id`, `food_remaining`, `raw_reading`, `updated_at`) VALUES
(1, 10000, 3, '2025-12-05 03:13:58'),
(2, 9000, 2, '2025-12-05 03:15:37');

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

--
-- Table structure for table `fish_data`
--

CREATE TABLE `fish_data` (
  `id` int(11) NOT NULL,
  `fish_age` int(3) NOT NULL,
  `fish_weight` float(10,2) NOT NULL,
  `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 `fish_data`
--

INSERT INTO `fish_data` (`id`, `fish_age`, `fish_weight`, `updated_at`) VALUES
(1, 3, 20.00, '2025-12-05 03:14:46');

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

--
-- Table structure for table `ketinggian_cairan`
--

CREATE TABLE `ketinggian_cairan` (
  `id` int(11) NOT NULL,
  `device_id` varchar(50) NOT NULL,
  `ultrasonik` float NOT NULL,
  `waktu` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `ketinggian_cairan`
--

INSERT INTO `ketinggian_cairan` (`id`, `device_id`, `ultrasonik`, `waktu`) VALUES
(1, 'DEV1764733466563', 5, '2025-12-04 08:13:07'),
(2, 'DEV1764733466563', 10, '2025-12-04 08:26:57'),
(3, 'DEV1764733466563', 30, '2025-12-04 08:31:53'),
(4, 'DEV1764733466563', 5, '2025-12-10 03:24:45');

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

--
-- Table structure for table `sensor_data`
--

CREATE TABLE `sensor_data` (
  `id` int(11) NOT NULL,
  `device_id` varchar(50) NOT NULL,
  `ph` float DEFAULT NULL,
  `turbidity` float DEFAULT NULL,
  `tds` float DEFAULT NULL,
  `temperature` float DEFAULT NULL,
  `do_value` float DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `sensor_data`
--

INSERT INTO `sensor_data` (`id`, `device_id`, `ph`, `turbidity`, `tds`, `temperature`, `do_value`, `created_at`) VALUES
(4, 'DEV1764733466563', 7.2, 11.5, 520, 27.8, 6.3, '2025-12-04 02:30:34'),
(5, 'DEV1764733466563', 7.1, 10.9, 510, 27.6, 6.5, '2025-12-04 02:30:34'),
(6, 'DEV1764733466563', 7.3, 11.2, 530, 28, 6.4, '2025-12-04 02:30:34'),
(7, 'DEV1764733466563', 6.8, 15, 500, 26, 6.2, '2025-12-04 02:55:30'),
(8, 'DEV1764733466563', 6.55, 14, 440, 27, 6.3, '2025-12-10 08:12:57'),
(9, 'GW001', 7.4, 6.2, 226.8, 26.8, 6, '2026-04-10 07:53:06'),
(10, 'GW001', 6.9, 10.2, 188.8, 28.8, 6, '2026-04-10 07:53:46'),
(11, 'GW001', 7.9, 14.2, 240.8, 30.8, 6, '2026-04-10 07:54:26'),
(12, 'GW001', 7.4, 6.2, 202.8, 27.8, 6, '2026-04-10 07:55:06'),
(13, 'GW001', 6.9, 10.2, 254.8, 29.8, 6, '2026-04-10 07:55:46'),
(14, 'GW001', 7.9, 14.2, 216.8, 26.8, 6, '2026-04-10 07:56:26'),
(15, 'GW001', 7.4, 6.2, 268.8, 28.8, 6, '2026-04-10 07:57:06'),
(16, 'GW001', 6.9, 10.2, 230.8, 30.8, 6, '2026-04-10 07:57:46'),
(17, 'GW001', 7.9, 14.2, 192.8, 27.8, 6, '2026-04-10 07:58:26'),
(18, 'GW001', 7.4, 6.2, 244.8, 29.8, 6, '2026-04-10 07:59:07'),
(19, 'GW001', 6.9, 10.2, 206.8, 26.8, 6, '2026-04-10 07:59:46'),
(20, 'GW001', 7.9, 14.2, 258.8, 28.8, 6, '2026-04-10 08:00:26'),
(21, 'GW001', 7.4, 6.2, 220.8, 30.8, 6, '2026-04-10 08:01:06'),
(22, 'GW001', 6.9, 10.2, 182.8, 27.8, 6, '2026-04-10 08:01:46'),
(23, 'GW001', 7.9, 14.2, 234.8, 29.8, 6, '2026-04-10 08:02:26'),
(24, 'GW001', 7.4, 6.2, 196.8, 26.8, 6, '2026-04-10 08:03:06'),
(25, 'GW001', 6.9, 10.2, 248.8, 28.8, 6, '2026-04-10 08:03:46'),
(26, 'GW001', 7.9, 14.2, 210.8, 30.8, 6, '2026-04-10 08:04:26'),
(27, 'GW001', 7.4, 6.2, 262.8, 27.8, 6, '2026-04-10 08:05:06'),
(28, 'GW001', 6.9, 10.2, 224.8, 29.8, 6, '2026-04-10 08:05:46'),
(29, 'GW001', 7.9, 14.2, 186.8, 26.8, 6, '2026-04-10 08:06:26'),
(30, 'GW001', 7.4, 6.2, 238.8, 28.8, 6, '2026-04-10 08:07:06'),
(31, 'GW001', 6.9, 10.2, 200.8, 30.8, 6, '2026-04-10 08:07:46'),
(32, 'GW001', 7, 16.9, 190.1, 29.1, 6.5, '2026-04-10 08:08:48'),
(33, 'DEV1764733466563', 7.4, 6.2, 226.8, 26.8, 6, '2026-05-04 08:07:22'),
(34, 'DEV1764733466563', 6.9, 10.2, 188.8, 28.8, 6, '2026-05-04 08:08:02'),
(35, 'DEV1764733466563', 7.9, 14.2, 240.8, 30.8, 6, '2026-05-04 08:08:42'),
(36, 'DEV1764733466563', 7.4, 6.2, 202.8, 27.8, 6, '2026-05-04 08:09:22'),
(37, 'DEV1764733466563', 6.9, 10.2, 254.8, 29.8, 6, '2026-05-04 08:10:02'),
(38, 'DEV1764733466563', 7.9, 14.2, 216.8, 26.8, 6, '2026-05-04 08:10:42'),
(39, 'DEV1764733466563', 7.4, 6.2, 268.8, 28.8, 6, '2026-05-04 08:11:22'),
(40, 'DEV1764733466563', 6.9, 10.2, 230.8, 30.8, 6, '2026-05-04 08:12:02'),
(41, 'DEV1764733466563', 7.9, 14.2, 192.8, 27.8, 6, '2026-05-04 08:12:42'),
(42, 'DEV1764733466563', 7.4, 6.2, 244.8, 29.8, 6, '2026-05-04 08:13:22'),
(43, 'DEV1764733466563', 6.9, 10.2, 206.8, 26.8, 6, '2026-05-04 08:14:02'),
(44, 'DEV1764733466563', 7.9, 14.2, 258.8, 28.8, 6, '2026-05-04 08:14:42'),
(45, 'DEV1764733466563', 8, 16.4, 222.6, 26.6, 6.5, '2026-05-04 08:38:28'),
(46, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 08:53:09'),
(47, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 08:53:42'),
(48, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 08:54:15'),
(49, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 08:54:48'),
(50, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 08:55:20'),
(51, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 08:55:52'),
(52, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 08:56:32'),
(53, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 08:57:04'),
(54, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 08:57:36'),
(55, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 08:58:08'),
(56, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 08:58:40'),
(57, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 08:59:12'),
(58, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 08:59:44'),
(59, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 09:00:16'),
(60, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 09:00:48'),
(61, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 09:01:20'),
(62, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 09:01:52'),
(63, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 09:02:24'),
(64, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 09:02:56'),
(65, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 09:03:28'),
(66, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 09:04:00'),
(67, 'DEV1764733466563', 0, 0, 0, 0, 0, '2026-05-04 09:04:32'),
(68, 'DEV1764733466563', 11.93, 0, 4, 25.44, 0, '2026-05-04 09:06:00'),
(69, 'DEV1764733466563', 11.82, 0, 3, 25.37, 9.58, '2026-05-04 09:06:32'),
(70, 'DEV1764733466563', 11.88, 0, 2, 25.44, 7.92, '2026-05-04 09:07:04'),
(71, 'DEV1764733466563', 12.08, 0, 3, 25.5, 0, '2026-05-04 09:07:36'),
(72, 'DEV1764733466563', 11.97, 0, 2, 25.5, 0, '2026-05-04 09:08:08'),
(73, 'DEV1764733466563', 11.8, 0, 2, 25.56, 0.99, '2026-05-04 09:08:40'),
(74, 'DEV1764733466563', 11.66, 0, 0, 25.56, 7.98, '2026-05-04 09:09:20'),
(75, 'DEV1764733466563', 11.8, 0, 3, 25.5, 9.14, '2026-05-04 09:09:52'),
(76, 'DEV1764733466563', 11.71, 0, 2, 25.5, 9.58, '2026-05-04 09:10:24'),
(77, 'DEV1764733466563', 11.97, 0, 2, 25.44, 7.5, '2026-05-04 09:10:56'),
(78, 'DEV1764733466563', 11.86, 0, 3, 25.37, 0, '2026-05-04 09:11:28'),
(79, 'DEV1764733466563', 11.73, 0, 3, 25.31, 7.46, '2026-05-04 09:12:00'),
(80, 'DEV1764733466563', 11.88, 0, 2, 25.25, 9.4, '2026-05-04 09:12:32'),
(81, 'DEV1764733466563', 11.92, 0, 3, 25.31, 8.69, '2026-05-04 09:13:04'),
(82, 'DEV1764733466563', 11.86, 0, 3, 25.31, 0, '2026-05-04 09:13:36'),
(83, 'DEV1764733466563', 11.84, 0, 3, 25.31, 3.94, '2026-05-04 09:14:08'),
(84, 'DEV1764733466563', 11.82, 0, 3, 25.31, 9.37, '2026-05-04 09:14:41'),
(85, 'DEV1764733466563', 11.88, 0, 3, 25.37, 0, '2026-05-04 09:15:12'),
(86, 'DEV1764733466563', 11.9, 0, 3, 25.44, 9.27, '2026-05-04 09:15:44'),
(87, 'DEV1764733466563', 11.82, 0, 4, 25.44, 9.39, '2026-05-04 09:16:16'),
(88, 'DEV1764733466563', 11.84, 0, 2, 25.56, 0, '2026-05-04 09:16:48'),
(89, 'DEV1764733466563', 12.27, 21.87, 4, 8.8, 15.24, '2026-05-06 11:28:37'),
(90, 'DEV1764733466563', 12.25, 21.94, 4, 5.57, 16.27, '2026-05-06 11:29:09'),
(91, 'DEV1764733466563', 12.33, 21.94, 4, 0.02, 21.03, '2026-05-06 11:29:41'),
(92, 'DEV1764733466563', 12.29, 22, 3, 8.73, 21.87, '2026-05-06 11:30:13'),
(93, 'DEV1764733466563', 12.23, 22.06, 4, 8.19, 16.94, '2026-05-06 11:30:45');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `control_status`
--
ALTER TABLE `control_status`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `devices`
--
ALTER TABLE `devices`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_user_device` (`user_id`);

--
-- Indexes for table `feeder_start_config`
--
ALTER TABLE `feeder_start_config`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `feed_status`
--
ALTER TABLE `feed_status`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `fish_data`
--
ALTER TABLE `fish_data`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `ketinggian_cairan`
--
ALTER TABLE `ketinggian_cairan`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `sensor_data`
--
ALTER TABLE `sensor_data`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_device_created` (`device_id`,`created_at`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `control_status`
--
ALTER TABLE `control_status`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `feeder_start_config`
--
ALTER TABLE `feeder_start_config`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `feed_status`
--
ALTER TABLE `feed_status`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `fish_data`
--
ALTER TABLE `fish_data`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `ketinggian_cairan`
--
ALTER TABLE `ketinggian_cairan`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `sensor_data`
--
ALTER TABLE `sensor_data`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=94;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `devices`
--
ALTER TABLE `devices`
  ADD CONSTRAINT `fk_user_device` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

