--
-- Host: localhost    Database: ReTEd
-- ------------------------------------------------------


--
-- Table structure for table `battery_logs`
--

CREATE TABLE `battery_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `device_id` bigint(20) unsigned NOT NULL,
  `level` double NOT NULL,
  `voltage` double DEFAULT NULL,
  `current` double DEFAULT NULL,
  `temperature` double DEFAULT NULL,
  `cycles` int(11) DEFAULT NULL,
  `charging_status` varchar(255) NOT NULL,
  `recorded_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `battery_logs_device_id_recorded_at_index` (`device_id`,`recorded_at`),
  CONSTRAINT `battery_logs_device_id_foreign` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `battery_logs`
--

COMMIT;

--
-- Dumping data for table `cache`
--

COMMIT;

--
-- Dumping data for table `cache_locks`
--

COMMIT;

--
-- Table structure for table `data_transfer_logs`
--

CREATE TABLE `data_transfer_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `device_id` bigint(20) unsigned NOT NULL,
  `data_size_bytes` int(11) NOT NULL,
  `latency_ms` double NOT NULL,
  `endpoint` varchar(255) NOT NULL,
  `success` tinyint(1) NOT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `sent_at` timestamp NOT NULL,
  `received_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `data_transfer_logs_device_id_sent_at_index` (`device_id`,`sent_at`),
  KEY `data_transfer_logs_success_index` (`success`),
  CONSTRAINT `data_transfer_logs_device_id_foreign` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `data_transfer_logs`
--

COMMIT;

--
-- Table structure for table `device_data`
--

CREATE TABLE `device_data` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `device_id` bigint(20) unsigned NOT NULL,
  `data_type` varchar(255) NOT NULL,
  `value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`value`)),
  `raw_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`raw_data`)),
  `recorded_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `device_data_device_id_data_type_recorded_at_index` (`device_id`,`data_type`,`recorded_at`),
  KEY `device_data_recorded_at_index` (`recorded_at`),
  CONSTRAINT `device_data_device_id_foreign` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `device_data`
--

COMMIT;

--
-- Table structure for table `device_health`
--

CREATE TABLE `device_health` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `device_id` bigint(20) unsigned NOT NULL,
  `battery_level` double DEFAULT NULL,
  `temperature` double DEFAULT NULL,
  `signal_strength` double DEFAULT NULL,
  `status` varchar(255) NOT NULL,
  `alerts` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`alerts`)),
  `uptime_seconds` int(11) DEFAULT NULL,
  `last_data_sent_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `device_health_device_id_status_index` (`device_id`,`status`),
  KEY `device_health_created_at_index` (`created_at`),
  CONSTRAINT `device_health_device_id_foreign` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `device_health`
--

COMMIT;

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

COMMIT;

--
-- Table structure for table `earthquake_events`
--

CREATE TABLE `earthquake_events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `device_id` bigint(20) unsigned NOT NULL,
  `magnitude` decimal(4,2) NOT NULL,
  `latitude` decimal(10,6) NOT NULL,
  `longitude` decimal(10,6) NOT NULL,
  `depth` decimal(8,2) DEFAULT NULL,
  `location_name` varchar(255) NOT NULL,
  `intensity` int(11) NOT NULL,
  `status` varchar(255) NOT NULL,
  `detected_at` timestamp NOT NULL,
  `response_time_ms` double DEFAULT NULL,
  `affected_regions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`affected_regions`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `earthquake_events_device_id_foreign` (`device_id`),
  KEY `earthquake_events_detected_at_magnitude_index` (`detected_at`,`magnitude`),
  KEY `earthquake_events_location_name_index` (`location_name`),
  KEY `earthquake_events_latitude_longitude_index` (`latitude`,`longitude`),
  CONSTRAINT `earthquake_events_device_id_foreign` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `earthquake_events`
--

COMMIT;

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `failed_jobs`
--

COMMIT;

--
-- Table structure for table `gempa`
--

CREATE TABLE `gempa` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `device_id` bigint(20) unsigned NOT NULL,
  `magnitude` decimal(4,2) NOT NULL,
  `intensitas` int(11) NOT NULL,
  `kedalaman` decimal(8,2) DEFAULT NULL,
  `lokasi` varchar(255) NOT NULL,
  `latitude` decimal(10,6) DEFAULT NULL,
  `longitude` decimal(10,6) DEFAULT NULL,
  `status` varchar(255) NOT NULL,
  `keterangan` text DEFAULT NULL,
  `waktu_kejadian` datetime NOT NULL,
  `is_tsunami_potential` tinyint(1) NOT NULL DEFAULT 0,
  `raw_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`raw_data`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `gempa_sensor_id_foreign` (`device_id`),
  KEY `gempa_waktu_kejadian_magnitude_index` (`waktu_kejadian`,`magnitude`),
  KEY `gempa_lokasi_index` (`lokasi`),
  CONSTRAINT `gempa_sensor_id_foreign` FOREIGN KEY (`device_id`) REFERENCES `sensors` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `gempa`
--

COMMIT;

--
-- Table structure for table `job_batches`
--

CREATE TABLE `job_batches` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `total_jobs` int(11) NOT NULL,
  `pending_jobs` int(11) NOT NULL,
  `failed_jobs` int(11) NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` mediumtext DEFAULT NULL,
  `cancelled_at` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `finished_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `job_batches`
--

COMMIT;

--
-- Table structure for table `jobs`
--

CREATE TABLE `jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` smallint(5) unsigned NOT NULL,
  `reserved_at` int(10) unsigned DEFAULT NULL,
  `available_at` int(10) unsigned NOT NULL,
  `created_at` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `jobs`
--

COMMIT;

--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` VALUES
(1,'0001_01_01_000000_create_users_table',1),
(2,'0001_01_01_000001_create_cache_table',1),
(3,'0001_01_01_000002_create_jobs_table',1),
(4,'2026_05_09_044436_create_sensors_table',1),
(5,'2026_05_09_044447_create_gempa_table',1),
(6,'2026_05_09_070836_create_devices_table',1),
(7,'2026_05_09_070846_create_device_data_table',1),
(8,'2026_05_09_072631_add_role_to_users_table',1),
(9,'2026_05_11_021321_add_api_token_to_users_table',1),
(10,'2026_05_12_065235_create_system_metrics_table',1),
(11,'2026_05_12_065251_create_device_health_table',1),
(12,'2026_05_12_065306_create_battery_logs_table',1),
(13,'2026_05_12_065324_create_data_transfer_logs_table',1),
(14,'2026_05_12_071011_update_users_role_enum_add_super_admin',1),
(16,'2026_05_12_071245_create_earthquake_events_table',2),
(17,'2026_05_13_082601_create_notifications_table',3),
(18,'2026_05_14_044515_add_device_id_to_gempa_table',4),
(19,'2026_05_15_085453_add_expiry_to_devices_table',5);
COMMIT;

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `device_id` bigint(20) unsigned DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`data`)),
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `is_sent` tinyint(1) NOT NULL DEFAULT 0,
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `notifications_user_id_is_read_index` (`user_id`,`is_read`),
  KEY `notifications_device_id_type_index` (`device_id`,`type`),
  CONSTRAINT `notifications_device_id_foreign` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `notifications_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `notifications`
--

COMMIT;

--
-- Dumping data for table `password_reset_tokens`
--

COMMIT;

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

CREATE TABLE `sensors` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sensor_id` varchar(255) NOT NULL,
  `nama_lokasi` varchar(255) NOT NULL,
  `latitude` decimal(10,6) NOT NULL,
  `longitude` decimal(10,6) NOT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'active',
  `last_heartbeat` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sensors_sensor_id_unique` (`sensor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `sensors` VALUES
(1,'SENSOR_001','Yogyakarta',-7.795600,110.369500,'active','2026-05-14 04:31:00','2026-05-13 21:31:00','2026-05-13 21:31:00');
COMMIT;

--
-- Table structure for table `sessions`
--

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `sessions`
--

INSERT INTO `sessions` VALUES
('VWiS0aINrJ1WnhSPLM2eMljokUBvQa3GX5DZBcm7',3,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36','eyJfdG9rZW4iOiJRTFF1NEh1NTRpZWd5VzNmdlNPdEE3Nk52eFBoWU9sZFNCdlBlaHFwIiwiX2ZsYXNoIjp7Im9sZCI6W10sIm5ldyI6W119LCJfcHJldmlvdXMiOnsidXJsIjoiaHR0cDpcL1wvMTI3LjAuMC4xOjgwMDBcL3N1cGVyLWFkbWluXC9zZXR0aW5ncyIsInJvdXRlIjoic3VwZXItYWRtaW4uc2V0dGluZ3MifSwibG9naW5fd2ViXzU5YmEzNmFkZGMyYjJmOTQwMTU4MGYwMTRjN2Y1OGVhNGUzMDk4OWQiOjN9',1779157544);
COMMIT;

--
-- Table structure for table `system_metrics`
--

CREATE TABLE `system_metrics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `metric_type` varchar(255) NOT NULL,
  `value` double NOT NULL,
  `unit` varchar(255) DEFAULT NULL,
  `details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`details`)),
  `recorded_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `system_metrics_metric_type_recorded_at_index` (`metric_type`,`recorded_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `system_metrics`
--

COMMIT;

--
-- Dumping data for table `users`
--

COMMIT;


