--
-- Host: 127.0.0.1    Database: police_ccms
-- ------------------------------------------------------


--
-- Table structure for table `bail_applications`
--

CREATE TABLE `bail_applications` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `case_id` bigint(20) unsigned DEFAULT NULL,
  `suspect_id` bigint(20) unsigned DEFAULT NULL,
  `application_number` varchar(255) NOT NULL,
  `application_date` date NOT NULL,
  `status` enum('pending','approved','rejected','revoked') NOT NULL DEFAULT 'pending',
  `approval_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `amount` decimal(12,2) DEFAULT NULL,
  `surety_name` varchar(255) DEFAULT NULL,
  `surety_contact` varchar(255) DEFAULT NULL,
  `surety_address` varchar(255) DEFAULT NULL,
  `surety_relationship` varchar(255) DEFAULT NULL,
  `surety_id_type` varchar(255) DEFAULT NULL,
  `surety_id_number` varchar(255) DEFAULT NULL,
  `reason` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `updated_by` bigint(20) unsigned DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bail_applications_application_number_unique` (`application_number`),
  KEY `bail_applications_case_id_foreign` (`case_id`),
  KEY `bail_applications_suspect_id_foreign` (`suspect_id`),
  KEY `bail_applications_approved_by_foreign` (`approved_by`),
  KEY `bail_applications_created_by_foreign` (`created_by`),
  KEY `bail_applications_updated_by_foreign` (`updated_by`),
  CONSTRAINT `bail_applications_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `bail_applications_case_id_foreign` FOREIGN KEY (`case_id`) REFERENCES `cases` (`id`) ON DELETE SET NULL,
  CONSTRAINT `bail_applications_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `bail_applications_suspect_id_foreign` FOREIGN KEY (`suspect_id`) REFERENCES `case_suspects` (`id`) ON DELETE SET NULL,
  CONSTRAINT `bail_applications_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `bail_applications`
--

INSERT INTO `bail_applications` VALUES (1,1,13,'BAIL-2025-238','2025-06-27','revoked',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'The suspect has requested bail on the grounds of being the primary caregiver for dependents',NULL,NULL,1,6,NULL,'2025-06-13 01:52:35','2025-06-13 01:52:35'),(2,1,21,'BAIL-2025-417','2025-06-20','rejected',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'The suspect has requested bail on the grounds of being a first-time offender with no prior criminal record','Additional investigation ongoing. Regular updates required.',NULL,2,6,NULL,'2025-06-13 01:52:35','2025-06-13 01:52:35'),(3,2,10,'BAIL-2025-798','2025-06-17','pending',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'The suspect has requested bail on the grounds of being the primary caregiver for dependents','Additional investigation ongoing. Regular updates required.',NULL,3,1,NULL,'2025-06-13 01:52:35','2025-06-13 01:52:35'),(4,2,2,'BAIL-2025-871','2025-07-04','approved','2025-07-06','2026-01-06',5000.00,'Mohammed Ali','+2208397676','Gunjur Area, The Gambia','Employer','Voter Card','0891FAAA','The suspect has requested bail on the grounds of being the primary caregiver for dependents',NULL,3,7,7,NULL,'2025-06-13 01:52:35','2025-06-13 01:52:35'),(5,2,6,'BAIL-2025-244','2025-06-24','revoked',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'The suspect has requested bail on the grounds of having strong community ties and no flight risk',NULL,NULL,5,4,NULL,'2025-06-13 01:52:35','2025-06-13 01:52:35'),(6,3,11,'BAIL-2025-011','2025-06-30','approved','2025-07-13','2025-08-13',40000.00,'John Doe','+2208516339','Banjul Area, The Gambia','Friend','Passport','66203D5A','The suspect has requested bail on the grounds of being a first-time offender with no prior criminal record',NULL,7,1,4,NULL,'2025-06-13 01:52:35','2025-06-13 01:52:35'),(7,3,27,'BAIL-2025-013','2025-07-05','rejected',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'The suspect has requested bail on the grounds of cooperating fully with the investigation','Additional investigation ongoing. Regular updates required.',NULL,6,4,NULL,'2025-06-13 01:52:35','2025-06-13 01:52:35'),(8,4,4,'BAIL-2025-794','2025-07-11','pending',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'The suspect has requested bail on the grounds of medical reasons requiring specialized treatment','Additional investigation ongoing. Regular updates required.',NULL,6,3,NULL,'2025-06-13 01:52:35','2025-06-13 01:52:35'),(9,4,24,'BAIL-2025-012','2025-06-18','pending',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'The suspect has requested bail on the grounds of having strong community ties and no flight risk',NULL,NULL,1,3,NULL,'2025-06-13 01:52:35','2025-06-13 01:52:35');

--
-- Table structure for table `bail_checkins`
--

CREATE TABLE `bail_checkins` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bail_application_id` bigint(20) unsigned NOT NULL,
  `suspect_id` bigint(20) unsigned DEFAULT NULL,
  `police_station_id` bigint(20) unsigned DEFAULT NULL,
  `checkin_time` datetime NOT NULL,
  `scheduled_time` datetime DEFAULT NULL,
  `is_present` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('scheduled','present','missed','excused') NOT NULL DEFAULT 'scheduled',
  `notes` text DEFAULT NULL,
  `recorded_by` bigint(20) unsigned DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `updated_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bail_checkins_bail_application_id_foreign` (`bail_application_id`),
  KEY `bail_checkins_suspect_id_foreign` (`suspect_id`),
  KEY `bail_checkins_police_station_id_foreign` (`police_station_id`),
  KEY `bail_checkins_recorded_by_foreign` (`recorded_by`),
  KEY `bail_checkins_created_by_foreign` (`created_by`),
  KEY `bail_checkins_updated_by_foreign` (`updated_by`),
  CONSTRAINT `bail_checkins_bail_application_id_foreign` FOREIGN KEY (`bail_application_id`) REFERENCES `bail_applications` (`id`) ON DELETE CASCADE,
  CONSTRAINT `bail_checkins_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `bail_checkins_police_station_id_foreign` FOREIGN KEY (`police_station_id`) REFERENCES `police_stations` (`id`) ON DELETE SET NULL,
  CONSTRAINT `bail_checkins_recorded_by_foreign` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `bail_checkins_suspect_id_foreign` FOREIGN KEY (`suspect_id`) REFERENCES `case_suspects` (`id`) ON DELETE SET NULL,
  CONSTRAINT `bail_checkins_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `bail_checkins`
--

INSERT INTO `bail_checkins` VALUES (1,4,2,6,'2025-03-16 02:40:40','2025-03-15 10:00:40',1,'present',NULL,3,9,3,'2025-03-15 14:33:40','2025-03-15 14:33:40'),(2,4,2,6,'2025-03-17 06:50:40','2025-03-16 10:00:40',0,'missed',NULL,4,2,7,'2025-03-16 14:33:40','2025-03-16 14:33:40'),(3,4,2,6,'2025-03-18 00:07:40','2025-03-17 10:00:40',1,'present',NULL,6,5,6,'2025-03-17 14:33:40','2025-03-17 14:33:40'),(4,4,2,6,'2025-03-19 04:18:40','2025-03-18 10:00:40',1,'present',NULL,3,4,2,'2025-03-18 14:33:40','2025-03-18 14:33:40'),(5,4,2,6,'2025-03-20 00:28:40','2025-03-19 10:00:40',0,'missed',NULL,5,7,3,'2025-03-19 14:33:40','2025-03-19 14:33:40'),(6,4,2,6,'2025-03-21 00:59:40','2025-03-20 10:00:40',1,'present',NULL,4,4,1,'2025-03-20 14:33:40','2025-03-20 14:33:40'),(7,4,2,6,'2025-03-22 02:07:40','2025-03-21 10:00:40',1,'present',NULL,2,5,1,'2025-03-21 14:33:40','2025-03-21 14:33:40'),(8,4,2,6,'2025-03-23 05:12:40','2025-03-22 10:00:40',0,'missed',NULL,9,9,2,'2025-03-22 14:33:40','2025-03-22 14:33:40'),(9,4,2,6,'2025-03-24 00:36:40','2025-03-23 10:00:40',1,'present',NULL,6,5,6,'2025-03-23 14:33:40','2025-03-23 14:33:40'),(10,4,2,6,'2025-03-25 02:20:40','2025-03-24 10:00:40',1,'present',NULL,9,5,2,'2025-03-24 14:33:40','2025-03-24 14:33:40'),(11,4,2,6,'2025-03-26 06:50:40','2025-03-25 10:00:40',1,'present',NULL,5,7,6,'2025-03-25 14:33:40','2025-03-25 14:33:40'),(12,4,2,6,'2025-03-27 01:54:40','2025-03-26 10:00:40',1,'present',NULL,4,9,4,'2025-03-26 14:33:40','2025-03-26 14:33:40'),(13,4,2,6,'2025-03-28 07:22:40','2025-03-27 10:00:40',1,'present',NULL,1,7,4,'2025-03-27 14:33:40','2025-03-27 14:33:40'),(14,4,2,6,'2025-03-29 02:58:40','2025-03-28 10:00:40',1,'present',NULL,3,3,9,'2025-03-28 14:33:40','2025-03-28 14:33:40'),(15,4,2,6,'2025-03-30 01:53:40','2025-03-29 10:00:40',1,'present',NULL,2,9,5,'2025-03-29 14:33:40','2025-03-29 14:33:40'),(16,4,2,6,'2025-03-31 04:26:40','2025-03-30 10:00:40',1,'present',NULL,9,1,4,'2025-03-30 14:33:40','2025-03-30 14:33:40'),(17,4,2,6,'2025-04-01 00:22:40','2025-03-31 10:00:40',1,'present',NULL,2,5,1,'2025-03-31 14:33:40','2025-03-31 14:33:40'),(18,4,2,6,'2025-04-02 03:17:40','2025-04-01 10:00:40',1,'present',NULL,6,9,7,'2025-04-01 14:33:40','2025-04-01 14:33:40'),(19,4,2,6,'2025-04-03 01:25:40','2025-04-02 10:00:40',1,'present',NULL,2,3,3,'2025-04-02 14:33:40','2025-04-02 14:33:40'),(20,4,2,6,'2025-04-04 01:35:40','2025-04-03 10:00:40',1,'present',NULL,2,1,3,'2025-04-03 14:33:40','2025-04-03 14:33:40'),(21,4,2,6,'2025-04-05 03:13:40','2025-04-04 10:00:40',1,'present',NULL,5,7,7,'2025-04-04 14:33:40','2025-04-04 14:33:40'),(22,4,2,6,'2025-04-06 02:59:40','2025-04-05 10:00:40',1,'present',NULL,9,2,2,'2025-04-05 14:33:40','2025-04-05 14:33:40'),(23,4,2,6,'2025-04-07 05:11:40','2025-04-06 10:00:40',0,'excused','Death in immediate family - funeral documentation provided',2,9,1,'2025-04-06 14:33:40','2025-04-06 14:33:40'),(24,4,2,6,'2025-04-08 05:58:40','2025-04-07 10:00:40',1,'present',NULL,1,1,4,'2025-04-07 14:33:40','2025-04-07 14:33:40'),(25,4,2,6,'2025-04-09 02:29:40','2025-04-08 10:00:40',1,'present',NULL,1,9,7,'2025-04-08 14:33:40','2025-04-08 14:33:40'),(26,4,2,6,'2025-04-10 05:00:40','2025-04-09 10:00:40',1,'present',NULL,7,7,3,'2025-04-09 14:33:40','2025-04-09 14:33:40'),(27,4,2,6,'2025-04-11 03:40:40','2025-04-10 10:00:40',0,'missed',NULL,5,5,9,'2025-04-10 14:33:40','2025-04-10 14:33:40'),(28,4,2,6,'2025-04-12 01:09:40','2025-04-11 10:00:40',0,'missed',NULL,5,4,9,'2025-04-11 14:33:40','2025-04-11 14:33:40'),(29,4,2,6,'2025-04-13 07:25:40','2025-04-12 10:00:40',1,'present',NULL,9,2,4,'2025-04-12 14:33:40','2025-04-12 14:33:40'),(30,4,2,6,'2025-04-14 04:25:40','2025-04-13 10:00:40',1,'present',NULL,5,1,6,'2025-04-13 14:33:40','2025-04-13 14:33:40'),(31,4,2,6,'2025-04-15 06:33:40','2025-04-14 10:00:40',1,'present',NULL,7,9,4,'2025-04-14 14:33:40','2025-04-14 14:33:40'),(32,4,2,6,'2025-04-16 03:05:40','2025-04-15 10:00:40',1,'present',NULL,9,3,1,'2025-04-15 14:33:40','2025-04-15 14:33:40'),(33,4,2,6,'2025-04-17 00:44:40','2025-04-16 10:00:40',1,'present',NULL,2,1,4,'2025-04-16 14:33:40','2025-04-16 14:33:40'),(34,4,2,6,'2025-04-18 06:14:40','2025-04-17 10:00:40',1,'present',NULL,1,1,5,'2025-04-17 14:33:40','2025-04-17 14:33:40'),(35,4,2,6,'2025-04-19 02:22:40','2025-04-18 10:00:40',0,'missed',NULL,7,5,3,'2025-04-18 14:33:40','2025-04-18 14:33:40'),(36,4,2,6,'2025-04-20 05:01:40','2025-04-19 10:00:40',1,'present',NULL,1,2,7,'2025-04-19 14:33:40','2025-04-19 14:33:40'),(37,4,2,6,'2025-04-21 02:52:40','2025-04-20 10:00:40',1,'present',NULL,3,4,9,'2025-04-20 14:33:40','2025-04-20 14:33:40'),(38,4,2,6,'2025-04-22 00:17:40','2025-04-21 10:00:40',1,'present',NULL,5,4,6,'2025-04-21 14:33:40','2025-04-21 14:33:40'),(39,4,2,6,'2025-04-23 00:25:40','2025-04-22 10:00:40',1,'present',NULL,9,9,3,'2025-04-22 14:33:40','2025-04-22 14:33:40'),(40,4,2,6,'2025-04-24 03:30:40','2025-04-23 10:00:40',1,'present',NULL,7,9,7,'2025-04-23 14:33:40','2025-04-23 14:33:40'),(41,4,2,6,'2025-04-25 01:59:40','2025-04-24 10:00:40',1,'present',NULL,1,4,2,'2025-04-24 14:33:40','2025-04-24 14:33:40'),(42,4,2,6,'2025-04-26 00:00:40','2025-04-25 10:00:40',1,'present',NULL,9,7,7,'2025-04-25 14:33:40','2025-04-25 14:33:40'),(43,4,2,6,'2025-04-27 00:18:40','2025-04-26 10:00:40',1,'present',NULL,9,4,9,'2025-04-26 14:33:40','2025-04-26 14:33:40'),(44,4,2,6,'2025-04-28 02:28:40','2025-04-27 10:00:40',1,'present',NULL,4,7,1,'2025-04-27 14:33:40','2025-04-27 14:33:40'),(45,4,2,6,'2025-04-29 05:54:40','2025-04-28 10:00:40',1,'present',NULL,3,5,1,'2025-04-28 14:33:40','2025-04-28 14:33:40'),(46,4,2,6,'2025-04-29 23:52:40','2025-04-29 10:00:40',0,'excused','Medical emergency - hospitalized with documentation provided',9,6,2,'2025-04-29 14:33:40','2025-04-29 14:33:40'),(47,4,2,6,'2025-05-01 04:46:40','2025-04-30 10:00:40',1,'present',NULL,7,7,3,'2025-04-30 14:33:40','2025-04-30 14:33:40'),(48,4,2,6,'2025-05-02 02:14:40','2025-05-01 10:00:40',1,'present',NULL,3,2,2,'2025-05-01 14:33:40','2025-05-01 14:33:40'),(49,4,2,6,'2025-05-03 00:12:40','2025-05-02 10:00:40',1,'present',NULL,9,9,6,'2025-05-02 14:33:40','2025-05-02 14:33:40'),(50,4,2,6,'2025-05-04 05:56:40','2025-05-03 10:00:40',1,'present',NULL,7,6,4,'2025-05-03 14:33:40','2025-05-03 14:33:40'),(51,4,2,6,'2025-05-05 04:54:40','2025-05-04 10:00:40',1,'present',NULL,7,2,6,'2025-05-04 14:33:40','2025-05-04 14:33:40'),(52,4,2,6,'2025-05-06 03:39:40','2025-05-05 10:00:40',1,'present',NULL,7,7,5,'2025-05-05 14:33:40','2025-05-05 14:33:40'),(53,4,2,6,'2025-05-07 06:57:40','2025-05-06 10:00:40',0,'missed',NULL,1,2,2,'2025-05-06 14:33:40','2025-05-06 14:33:40'),(54,4,2,6,'2025-05-08 00:11:40','2025-05-07 10:00:40',1,'present',NULL,9,3,3,'2025-05-07 14:33:40','2025-05-07 14:33:40'),(55,4,2,6,'2025-05-09 00:34:40','2025-05-08 10:00:40',1,'present',NULL,5,7,2,'2025-05-08 14:33:40','2025-05-08 14:33:40'),(56,4,2,6,'2025-05-10 03:12:40','2025-05-09 10:00:40',1,'present',NULL,7,5,6,'2025-05-09 14:33:40','2025-05-09 14:33:40'),(57,4,2,6,'2025-05-11 02:07:40','2025-05-10 10:00:40',0,'missed',NULL,6,1,9,'2025-05-10 14:33:40','2025-05-10 14:33:40'),(58,4,2,6,'2025-05-12 07:03:40','2025-05-11 10:00:40',1,'present',NULL,3,3,1,'2025-05-11 14:33:40','2025-05-11 14:33:40'),(59,4,2,6,'2025-05-12 23:35:40','2025-05-12 10:00:40',1,'present',NULL,5,4,2,'2025-05-12 14:33:40','2025-05-12 14:33:40'),(60,4,2,6,'2025-05-14 05:06:40','2025-05-13 10:00:40',0,'missed',NULL,2,7,2,'2025-05-13 14:33:40','2025-05-13 14:33:40'),(61,4,2,6,'2025-05-15 06:44:40','2025-05-14 10:00:40',1,'present',NULL,3,6,4,'2025-05-14 14:33:40','2025-05-14 14:33:40'),(62,4,2,6,'2025-05-16 04:56:40','2025-05-15 10:00:40',0,'missed',NULL,4,5,6,'2025-05-15 14:33:40','2025-05-15 14:33:40'),(63,4,2,6,'2025-05-17 05:14:40','2025-05-16 10:00:40',1,'present',NULL,1,4,6,'2025-05-16 14:33:40','2025-05-16 14:33:40'),(64,4,2,6,'2025-05-18 06:49:40','2025-05-17 10:00:40',1,'present',NULL,1,9,1,'2025-05-17 14:33:40','2025-05-17 14:33:40'),(65,4,2,6,'2025-05-19 02:50:40','2025-05-18 10:00:40',1,'present',NULL,9,9,5,'2025-05-18 14:33:40','2025-05-18 14:33:40'),(66,4,2,6,'2025-05-20 00:37:40','2025-05-19 10:00:40',1,'present',NULL,5,4,4,'2025-05-19 14:33:40','2025-05-19 14:33:40'),(67,4,2,6,'2025-05-21 05:36:40','2025-05-20 10:00:40',0,'excused','Medical emergency - hospitalized with documentation provided',4,3,2,'2025-05-20 14:33:40','2025-05-20 14:33:40'),(68,4,2,6,'2025-05-22 06:04:40','2025-05-21 10:00:40',1,'present',NULL,2,6,6,'2025-05-21 14:33:40','2025-05-21 14:33:40'),(69,4,2,6,'2025-05-23 06:27:40','2025-05-22 10:00:40',1,'present',NULL,3,1,5,'2025-05-22 14:33:40','2025-05-22 14:33:40'),(70,4,2,6,'2025-05-24 05:56:40','2025-05-23 10:00:40',0,'missed',NULL,5,4,5,'2025-05-23 14:33:40','2025-05-23 14:33:40'),(71,4,2,6,'2025-05-25 03:46:40','2025-05-24 10:00:40',0,'missed',NULL,3,7,2,'2025-05-24 14:33:40','2025-05-24 14:33:40'),(72,4,2,6,'2025-05-26 05:14:40','2025-05-25 10:00:40',1,'present',NULL,9,6,3,'2025-05-25 14:33:40','2025-05-25 14:33:40'),(73,4,2,6,'2025-05-27 01:18:40','2025-05-26 10:00:40',1,'present',NULL,7,5,1,'2025-05-26 14:33:40','2025-05-26 14:33:40'),(74,4,2,6,'2025-05-28 05:34:40','2025-05-27 10:00:40',1,'present',NULL,2,9,6,'2025-05-27 14:33:40','2025-05-27 14:33:40'),(75,4,2,6,'2025-05-29 00:26:40','2025-05-28 10:00:40',0,'missed',NULL,2,3,4,'2025-05-28 14:33:40','2025-05-28 14:33:40'),(76,4,2,6,'2025-05-30 03:33:40','2025-05-29 10:00:40',1,'present',NULL,1,6,5,'2025-05-29 14:33:40','2025-05-29 14:33:40'),(77,4,2,6,'2025-05-31 03:37:40','2025-05-30 10:00:40',1,'present',NULL,9,3,2,'2025-05-30 14:33:40','2025-05-30 14:33:40'),(78,4,2,6,'2025-06-01 06:24:40','2025-05-31 10:00:40',1,'present',NULL,5,7,2,'2025-05-31 14:33:40','2025-05-31 14:33:40'),(79,4,2,6,'2025-06-02 03:10:40','2025-06-01 10:00:40',0,'missed',NULL,3,2,2,'2025-06-01 14:33:40','2025-06-01 14:33:40'),(80,4,2,6,'2025-06-03 04:00:40','2025-06-02 10:00:40',1,'present',NULL,1,9,9,'2025-06-02 14:33:40','2025-06-02 14:33:40'),(81,4,2,6,'2025-06-04 01:52:40','2025-06-03 10:00:40',1,'present',NULL,1,6,4,'2025-06-03 14:33:40','2025-06-03 14:33:40'),(82,4,2,6,'2025-06-05 07:17:40','2025-06-04 10:00:40',1,'present',NULL,1,9,1,'2025-06-04 14:33:40','2025-06-04 14:33:40'),(83,4,2,6,'2025-06-06 06:21:40','2025-06-05 10:00:40',1,'present',NULL,6,7,9,'2025-06-05 14:33:40','2025-06-05 14:33:40'),(84,4,2,6,'2025-06-07 00:44:40','2025-06-06 10:00:40',1,'present',NULL,3,4,4,'2025-06-06 14:33:40','2025-06-06 14:33:40'),(85,4,2,6,'2025-06-08 04:50:40','2025-06-07 10:00:40',0,'excused','Child care emergency - verified by social worker',2,7,9,'2025-06-07 14:33:40','2025-06-07 14:33:40'),(86,4,2,6,'2025-06-09 05:20:40','2025-06-08 10:00:40',1,'present',NULL,3,5,9,'2025-06-08 14:33:40','2025-06-08 14:33:40'),(87,4,2,6,'2025-06-10 03:30:40','2025-06-09 10:00:40',1,'present',NULL,9,9,6,'2025-06-09 14:33:40','2025-06-09 14:33:40'),(88,4,2,6,'2025-06-11 04:12:40','2025-06-10 10:00:40',1,'present',NULL,5,3,2,'2025-06-10 14:33:40','2025-06-10 14:33:40'),(89,4,2,6,'2025-06-12 02:43:40','2025-06-11 10:00:40',1,'present',NULL,9,5,9,'2025-06-11 14:33:40','2025-06-11 14:33:40'),(90,4,2,6,'2025-06-13 10:00:00','2025-06-13 10:00:00',0,'missed',NULL,6,3,3,'2025-06-13 00:00:00','2025-06-13 00:00:00'),(91,4,2,6,'2025-06-14 10:00:00','2025-06-14 10:00:00',0,'scheduled',NULL,6,1,2,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(92,4,2,6,'2025-06-15 10:00:00','2025-06-15 10:00:00',0,'scheduled',NULL,1,5,3,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(93,4,2,6,'2025-06-16 10:00:00','2025-06-16 10:00:00',0,'scheduled',NULL,6,9,7,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(94,4,2,6,'2025-06-17 10:00:00','2025-06-17 10:00:00',0,'scheduled',NULL,5,9,7,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(95,4,2,6,'2025-06-18 10:00:00','2025-06-18 10:00:00',0,'scheduled',NULL,4,7,1,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(96,4,2,6,'2025-06-19 10:00:00','2025-06-19 10:00:00',0,'scheduled',NULL,6,1,3,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(97,4,2,6,'2025-06-20 10:00:00','2025-06-20 10:00:00',0,'scheduled',NULL,5,2,9,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(98,4,2,6,'2025-06-21 10:00:00','2025-06-21 10:00:00',0,'scheduled',NULL,3,6,6,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(99,4,2,6,'2025-06-22 10:00:00','2025-06-22 10:00:00',0,'scheduled',NULL,3,4,2,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(100,4,2,6,'2025-06-23 10:00:00','2025-06-23 10:00:00',0,'scheduled',NULL,5,3,7,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(101,4,2,6,'2025-06-24 10:00:00','2025-06-24 10:00:00',0,'scheduled',NULL,2,2,1,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(102,4,2,6,'2025-06-25 10:00:00','2025-06-25 10:00:00',0,'scheduled',NULL,1,6,2,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(103,4,2,6,'2025-06-26 10:00:00','2025-06-26 10:00:00',0,'scheduled',NULL,7,1,1,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(104,4,2,6,'2025-06-27 10:00:00','2025-06-27 10:00:00',0,'scheduled',NULL,9,3,3,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(105,6,11,4,'2025-03-19 04:33:40','2025-03-18 10:00:40',0,'missed',NULL,7,4,6,'2025-03-18 14:33:40','2025-03-18 14:33:40'),(106,6,11,4,'2025-03-26 05:33:40','2025-03-25 10:00:40',1,'present',NULL,6,7,2,'2025-03-25 14:33:40','2025-03-25 14:33:40'),(107,6,11,4,'2025-04-02 05:12:40','2025-04-01 10:00:40',1,'present',NULL,9,6,6,'2025-04-01 14:33:40','2025-04-01 14:33:40'),(108,6,11,4,'2025-04-09 01:30:40','2025-04-08 10:00:40',1,'present',NULL,2,4,1,'2025-04-08 14:33:40','2025-04-08 14:33:40'),(109,6,11,4,'2025-04-16 02:23:40','2025-04-15 10:00:40',0,'excused','Child care emergency - verified by social worker',6,1,5,'2025-04-15 14:33:40','2025-04-15 14:33:40'),(110,6,11,4,'2025-04-23 01:46:40','2025-04-22 10:00:40',1,'present',NULL,9,6,1,'2025-04-22 14:33:40','2025-04-22 14:33:40'),(111,6,11,4,'2025-04-30 00:51:40','2025-04-29 10:00:40',1,'present',NULL,6,7,3,'2025-04-29 14:33:40','2025-04-29 14:33:40'),(112,6,11,4,'2025-05-07 03:21:40','2025-05-06 10:00:40',1,'present',NULL,7,1,6,'2025-05-06 14:33:40','2025-05-06 14:33:40'),(113,6,11,4,'2025-05-14 05:10:40','2025-05-13 10:00:40',1,'present',NULL,2,3,6,'2025-05-13 14:33:40','2025-05-13 14:33:40'),(114,6,11,4,'2025-05-21 05:55:40','2025-05-20 10:00:40',1,'present',NULL,3,5,4,'2025-05-20 14:33:40','2025-05-20 14:33:40'),(115,6,11,4,'2025-05-28 04:12:40','2025-05-27 10:00:40',1,'present',NULL,9,2,2,'2025-05-27 14:33:40','2025-05-27 14:33:40'),(116,6,11,4,'2025-06-04 06:01:40','2025-06-03 10:00:40',1,'present',NULL,9,7,5,'2025-06-03 14:33:40','2025-06-03 14:33:40'),(117,6,11,4,'2025-06-11 02:29:40','2025-06-10 10:00:40',0,'missed',NULL,1,3,9,'2025-06-10 14:33:40','2025-06-10 14:33:40'),(118,6,11,4,'2025-06-17 10:00:00','2025-06-17 10:00:00',0,'scheduled',NULL,4,7,1,'2025-06-13 14:33:40','2025-06-13 14:33:40'),(119,6,11,4,'2025-06-24 10:00:00','2025-06-24 10:00:00',0,'scheduled',NULL,2,9,5,'2025-06-13 14:33:40','2025-06-13 14:33:40');

--
-- Table structure for table `bail_conditions`
--

CREATE TABLE `bail_conditions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bail_application_id` bigint(20) unsigned NOT NULL,
  `condition_type` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `requires_reporting` tinyint(1) NOT NULL DEFAULT 0,
  `police_station_id` bigint(20) unsigned DEFAULT NULL,
  `reporting_frequency` enum('daily','weekly','biweekly','monthly','custom') DEFAULT NULL,
  `reporting_days` varchar(255) DEFAULT NULL,
  `reporting_time_start` time DEFAULT NULL,
  `reporting_time_end` time DEFAULT NULL,
  `reporting_start_date` date DEFAULT NULL,
  `reporting_end_date` date DEFAULT NULL,
  `total_required_checkins` int(11) DEFAULT NULL,
  `completed_checkins` int(11) NOT NULL DEFAULT 0,
  `is_fulfilled` tinyint(1) NOT NULL DEFAULT 0,
  `fulfillment_date` date DEFAULT NULL,
  `fulfillment_notes` text DEFAULT NULL,
  `verified_by` bigint(20) unsigned DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `updated_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bail_conditions_bail_application_id_foreign` (`bail_application_id`),
  KEY `bail_conditions_verified_by_foreign` (`verified_by`),
  KEY `bail_conditions_created_by_foreign` (`created_by`),
  KEY `bail_conditions_updated_by_foreign` (`updated_by`),
  KEY `bail_conditions_police_station_id_foreign` (`police_station_id`),
  CONSTRAINT `bail_conditions_bail_application_id_foreign` FOREIGN KEY (`bail_application_id`) REFERENCES `bail_applications` (`id`) ON DELETE CASCADE,
  CONSTRAINT `bail_conditions_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `bail_conditions_police_station_id_foreign` FOREIGN KEY (`police_station_id`) REFERENCES `police_stations` (`id`) ON DELETE SET NULL,
  CONSTRAINT `bail_conditions_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `bail_conditions_verified_by_foreign` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `bail_conditions`
--

INSERT INTO `bail_conditions` VALUES (1,4,'Travel Restriction','Must not leave the jurisdiction of Gambia without prior court approval. Must surrender passport to court registry.',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,NULL,NULL,3,6,'2025-06-13 14:32:40','2025-06-13 14:32:40'),(2,4,'Substance Testing','Must submit to random drug and alcohol testing when requested by supervising officer.',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,NULL,NULL,7,6,'2025-06-13 14:32:40','2025-06-13 14:32:40'),(3,4,'Surrender of Documents','Must surrender all travel documents including passport and national ID to court registry within 24 hours.',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,NULL,NULL,7,2,'2025-06-13 14:32:40','2025-06-13 14:32:40'),(4,4,'Electronic Monitoring','Must wear electronic monitoring device at all times and maintain the equipment in working order.',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,NULL,NULL,9,4,'2025-06-13 14:32:40','2025-06-13 14:32:40'),(5,4,'Reporting Requirement','Must report to the designated police station as scheduled. Failure to report may result in revocation of bail.',1,6,'daily',NULL,'10:00:00','15:00:00','2025-07-06','2026-01-06',184,7,0,NULL,NULL,NULL,7,4,'2025-06-13 14:32:40','2025-06-13 14:32:40'),(6,6,'Travel Restriction','Must not leave the jurisdiction of Gambia without prior court approval. Must surrender passport to court registry.',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,NULL,NULL,3,4,'2025-06-13 14:32:40','2025-06-13 14:32:40'),(7,6,'Curfew','Must remain at registered address between 8:00 PM and 6:00 AM daily unless authorized by supervising officer.',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,NULL,NULL,7,3,'2025-06-13 14:32:40','2025-06-13 14:32:40'),(8,6,'Surrender of Documents','Must surrender all travel documents including passport and national ID to court registry within 24 hours.',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL,NULL,NULL,6,4,'2025-06-13 14:32:40','2025-06-13 14:32:40'),(9,6,'Reporting Requirement','Must report to the designated police station as scheduled. Failure to report may result in revocation of bail.',1,4,'weekly','\"[\\\"Tuesday\\\"]\"','08:00:00','15:00:00','2025-07-13','2025-08-13',5,1,0,NULL,NULL,NULL,9,3,'2025-06-13 14:32:40','2025-06-13 14:32:40');

--
-- Table structure for table `cache`
--

CREATE TABLE `cache` (
  `key` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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


--
-- Table structure for table `cache_locks`
--

CREATE TABLE `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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


--
-- Table structure for table `case_evidences`
--

CREATE TABLE `case_evidences` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `case_id` bigint(20) unsigned NOT NULL,
  `tag_number` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `evidence_type` enum('physical','digital','documentary','testimonial','forensic','other') NOT NULL,
  `subtype` varchar(255) DEFAULT NULL,
  `collection_datetime` datetime NOT NULL,
  `collection_time` time DEFAULT NULL,
  `collection_location` varchar(255) NOT NULL,
  `collection_details` text DEFAULT NULL,
  `collected_by_officer_id` bigint(20) unsigned NOT NULL,
  `storage_location` varchar(255) DEFAULT NULL,
  `status` enum('collected','in_storage','in_analysis','analyzed','in_court','returned','destroyed') NOT NULL DEFAULT 'collected',
  `condition` enum('excellent','good','fair','poor','damaged') DEFAULT NULL,
  `custodian` varchar(255) DEFAULT NULL,
  `chain_of_custody_status` varchar(255) NOT NULL,
  `chain_of_custody_notes` text DEFAULT NULL,
  `is_sensitive` tinyint(1) NOT NULL DEFAULT 0,
  `file_path` varchar(255) DEFAULT NULL,
  `photo_path` varchar(255) DEFAULT NULL,
  `photos` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`photos`)),
  `documents` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`documents`)),
  `chain_of_custody` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`chain_of_custody`)),
  `analysis_results` text DEFAULT NULL,
  `analyzed_by_officer_id` bigint(20) unsigned DEFAULT NULL,
  `analyzed_at` datetime DEFAULT NULL,
  `is_admissible` tinyint(1) NOT NULL DEFAULT 1,
  `admissibility_notes` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_by_user_id` bigint(20) unsigned DEFAULT NULL,
  `updated_by_user_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `case_evidence_evidence_number_unique` (`tag_number`),
  KEY `case_evidence_case_id_foreign` (`case_id`),
  KEY `case_evidence_collected_by_officer_id_foreign` (`collected_by_officer_id`),
  KEY `case_evidence_analyzed_by_officer_id_foreign` (`analyzed_by_officer_id`),
  KEY `case_evidences_created_by_user_id_foreign` (`created_by_user_id`),
  KEY `case_evidences_updated_by_user_id_foreign` (`updated_by_user_id`),
  CONSTRAINT `case_evidence_analyzed_by_officer_id_foreign` FOREIGN KEY (`analyzed_by_officer_id`) REFERENCES `users` (`id`),
  CONSTRAINT `case_evidence_case_id_foreign` FOREIGN KEY (`case_id`) REFERENCES `cases` (`id`) ON DELETE CASCADE,
  CONSTRAINT `case_evidence_collected_by_officer_id_foreign` FOREIGN KEY (`collected_by_officer_id`) REFERENCES `users` (`id`),
  CONSTRAINT `case_evidences_created_by_user_id_foreign` FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `case_evidences_updated_by_user_id_foreign` FOREIGN KEY (`updated_by_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `case_evidences`
--

INSERT INTO `case_evidences` VALUES (1,1,'EV-2023-001','Evidence EV-2023-001','Replica firearm used in robbery. Black in color, resembling a Glock 19.','physical','weapon','2025-06-07 14:30:00','14:30:00','Kairaba Avenue, Serrekunda','Manual recovery',3,'Evidence Room, Banjul Police Headquarters','collected','good',NULL,'in_storage',NULL,0,NULL,NULL,'\"[]\"','\"[]\"','\"[{\\\"date\\\":\\\"2025-06-07\\\",\\\"time\\\":\\\"14:30:00\\\",\\\"action\\\":\\\"collected\\\",\\\"handler\\\":\\\" \\\",\\\"badge_number\\\":\\\"GPS-1001\\\",\\\"location\\\":\\\"Kairaba Avenue, Serrekunda\\\",\\\"notes\\\":\\\"Initial collection of evidence\\\"},{\\\"date\\\":\\\"2025-06-07\\\",\\\"time\\\":\\\"16:30:00\\\",\\\"action\\\":\\\"stored\\\",\\\"handler\\\":\\\" \\\",\\\"badge_number\\\":\\\"GPS-1001\\\",\\\"location\\\":\\\"Evidence Room, Banjul Police Headquarters\\\",\\\"notes\\\":\\\"Evidence logged and stored in secure evidence room\\\"},{\\\"date\\\":\\\"2025-06-13\\\",\\\"time\\\":\\\"09:30:00\\\",\\\"action\\\":\\\"transferred\\\",\\\"handler\\\":\\\"Fatou Ceesay\\\",\\\"badge_number\\\":\\\"GPS-1002\\\",\\\"location\\\":\\\"Forensic Lab\\\",\\\"notes\\\":\\\"Transferred to forensic department for analysis\\\"},{\\\"date\\\":\\\"2025-06-16\\\",\\\"time\\\":\\\"14:15:00\\\",\\\"action\\\":\\\"transferred\\\",\\\"handler\\\":\\\"Modou Jallow\\\",\\\"badge_number\\\":\\\"GPS-1003\\\",\\\"location\\\":\\\"Evidence Room\\\",\\\"notes\\\":\\\"Analysis complete. Returned to evidence storage\\\"},{\\\"date\\\":\\\"2025-06-23\\\",\\\"time\\\":\\\"08:45:00\\\",\\\"action\\\":\\\"transferred\\\",\\\"handler\\\":\\\"Fatou Jallow\\\",\\\"badge_number\\\":\\\"GPF10004\\\",\\\"location\\\":\\\"Banjul High Court\\\",\\\"notes\\\":\\\"Transferred to court for case hearing\\\"}]\"',NULL,NULL,NULL,1,NULL,'Found discarded in trash bin 100 meters from crime scene. No fingerprints recovered.','2025-06-12 14:05:49','2025-06-12 14:10:23',NULL,3,3),(2,2,'EV-2023-002','Evidence EV-2023-002','Vehicle registration documents for stolen Toyota Corolla, registration number BJL 1234.','documentary','vehicle_document','2025-06-09 10:15:00','10:15:00','Victim\'s residence, Kololi','Provided by victim',4,'Evidence Room, Serrekunda Police Station','collected','good',NULL,'in_storage',NULL,0,NULL,NULL,'\"[]\"','\"[]\"','\"[{\\\"date\\\":\\\"2025-06-09\\\",\\\"time\\\":\\\"10:15:00\\\",\\\"action\\\":\\\"collected\\\",\\\"handler\\\":\\\" \\\",\\\"badge_number\\\":\\\"GPS-1002\\\",\\\"location\\\":\\\"Victim\'s residence, Kololi\\\",\\\"notes\\\":\\\"Initial collection of evidence\\\"},{\\\"date\\\":\\\"2025-06-09\\\",\\\"time\\\":\\\"12:15:00\\\",\\\"action\\\":\\\"stored\\\",\\\"handler\\\":\\\" \\\",\\\"badge_number\\\":\\\"GPS-1002\\\",\\\"location\\\":\\\"Evidence Room, Serrekunda Police Station\\\",\\\"notes\\\":\\\"Evidence logged and stored in secure evidence room\\\"},{\\\"date\\\":\\\"2025-06-13\\\",\\\"time\\\":\\\"09:30:00\\\",\\\"action\\\":\\\"transferred\\\",\\\"handler\\\":\\\"Modou Jallow\\\",\\\"badge_number\\\":\\\"GPS-1003\\\",\\\"location\\\":\\\"Forensic Lab\\\",\\\"notes\\\":\\\"Transferred to forensic department for analysis\\\"},{\\\"date\\\":\\\"2025-06-16\\\",\\\"time\\\":\\\"14:15:00\\\",\\\"action\\\":\\\"transferred\\\",\\\"handler\\\":\\\"Fatou Jallow\\\",\\\"badge_number\\\":\\\"GPF10004\\\",\\\"location\\\":\\\"Evidence Room\\\",\\\"notes\\\":\\\"Analysis complete. Returned to evidence storage\\\"}]\"',NULL,NULL,NULL,1,NULL,'Original vehicle documents provided by the victim for verification purposes.','2025-06-12 14:05:49','2025-06-12 14:10:23',NULL,4,4),(3,3,'EV-2023-003','Evidence EV-2023-003','Blood sample collected from victim of assault at Westfield Junction.','forensic','blood_sample','2025-06-10 16:45:00','16:45:00','Serrekunda General Hospital','Medical collection',5,'Evidence Room, Bakau Police Station','in_analysis','good',NULL,'in_storage',NULL,0,NULL,NULL,'\"[]\"','\"[]\"','\"[{\\\"date\\\":\\\"2025-06-10\\\",\\\"time\\\":\\\"16:45:00\\\",\\\"action\\\":\\\"collected\\\",\\\"handler\\\":\\\" \\\",\\\"badge_number\\\":\\\"GPS-1003\\\",\\\"location\\\":\\\"Serrekunda General Hospital\\\",\\\"notes\\\":\\\"Initial collection of evidence\\\"},{\\\"date\\\":\\\"2025-06-10\\\",\\\"time\\\":\\\"18:45:00\\\",\\\"action\\\":\\\"stored\\\",\\\"handler\\\":\\\" \\\",\\\"badge_number\\\":\\\"GPS-1003\\\",\\\"location\\\":\\\"Evidence Room, Bakau Police Station\\\",\\\"notes\\\":\\\"Evidence logged and stored in secure evidence room\\\"},{\\\"date\\\":\\\"2025-06-13\\\",\\\"time\\\":\\\"09:30:00\\\",\\\"action\\\":\\\"transferred\\\",\\\"handler\\\":\\\"Fatou Jallow\\\",\\\"badge_number\\\":\\\"GPF10004\\\",\\\"location\\\":\\\"Forensic Lab\\\",\\\"notes\\\":\\\"Transferred to forensic department for analysis\\\"}]\"',NULL,NULL,NULL,1,NULL,'Sample collected by medical staff and handed over to investigating officer.','2025-06-12 14:05:49','2025-06-12 14:10:23',NULL,5,5),(4,4,'EV-2023-004','Evidence EV-2023-004','CCTV footage from Fajara Electronics Store showing burglary in progress.','digital','video','2025-06-06 09:30:00','09:30:00','Fajara Electronics Store, Fajara','Digital copy',6,'Evidence Room, Brikama Police Station','analyzed','good',NULL,'in_storage',NULL,0,NULL,NULL,'\"[]\"','\"[]\"','\"[{\\\"date\\\":\\\"2025-06-06\\\",\\\"time\\\":\\\"09:30:00\\\",\\\"action\\\":\\\"collected\\\",\\\"handler\\\":\\\" \\\",\\\"badge_number\\\":\\\"GPF10004\\\",\\\"location\\\":\\\"Fajara Electronics Store, Fajara\\\",\\\"notes\\\":\\\"Initial collection of evidence\\\"},{\\\"date\\\":\\\"2025-06-06\\\",\\\"time\\\":\\\"11:30:00\\\",\\\"action\\\":\\\"stored\\\",\\\"handler\\\":\\\" \\\",\\\"badge_number\\\":\\\"GPF10004\\\",\\\"location\\\":\\\"Evidence Room, Brikama Police Station\\\",\\\"notes\\\":\\\"Evidence logged and stored in secure evidence room\\\"}]\"',NULL,NULL,NULL,1,NULL,'Footage shows two suspects breaking into the store at approximately 02:15 AM.','2025-06-12 14:05:49','2025-06-12 14:05:49',NULL,6,6),(5,5,'EV-2023-005','Evidence EV-2023-005','Suspected cannabis, approximately 50 grams, found in suspect\'s possession.','physical','controlled_substance','2025-06-11 11:20:00','11:20:00','Banjul Ferry Terminal','Seized during search',7,'Evidence Room, Basse Police Station','in_storage','good',NULL,'in_storage',NULL,0,NULL,NULL,'\"[]\"','\"[]\"','\"[{\\\"date\\\":\\\"2025-06-11\\\",\\\"time\\\":\\\"11:20:00\\\",\\\"action\\\":\\\"collected\\\",\\\"handler\\\":\\\" \\\",\\\"badge_number\\\":\\\"GPF10005\\\",\\\"location\\\":\\\"Banjul Ferry Terminal\\\",\\\"notes\\\":\\\"Initial collection of evidence\\\"},{\\\"date\\\":\\\"2025-06-11\\\",\\\"time\\\":\\\"13:20:00\\\",\\\"action\\\":\\\"stored\\\",\\\"handler\\\":\\\" \\\",\\\"badge_number\\\":\\\"GPF10005\\\",\\\"location\\\":\\\"Evidence Room, Basse Police Station\\\",\\\"notes\\\":\\\"Evidence logged and stored in secure evidence room\\\"}]\"',NULL,NULL,NULL,1,NULL,'Substance to be sent to lab for testing and confirmation.','2025-06-12 14:05:49','2025-06-12 14:05:49',NULL,7,7);

--
-- Table structure for table `case_statements`
--

CREATE TABLE `case_statements` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `case_id` bigint(20) unsigned NOT NULL,
  `statement_type` enum('victim','witness','suspect','officer','expert') NOT NULL,
  `given_by_name` varchar(255) NOT NULL,
  `given_by_contact` varchar(255) DEFAULT NULL,
  `given_by_address` varchar(255) DEFAULT NULL,
  `given_by_id_type` varchar(255) DEFAULT NULL,
  `given_by_id_number` varchar(255) DEFAULT NULL,
  `recorded_by_officer_id` bigint(20) unsigned NOT NULL,
  `statement_date` datetime NOT NULL,
  `statement_content` text NOT NULL,
  `location_taken` varchar(255) NOT NULL,
  `is_sworn` tinyint(1) NOT NULL DEFAULT 0,
  `is_signed` tinyint(1) NOT NULL DEFAULT 0,
  `signature_file_path` varchar(255) DEFAULT NULL,
  `audio_recording_path` varchar(255) DEFAULT NULL,
  `officer_remarks` text DEFAULT NULL,
  `is_verified` tinyint(1) NOT NULL DEFAULT 0,
  `verified_by_officer_id` bigint(20) unsigned DEFAULT NULL,
  `verified_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `case_statements_case_id_foreign` (`case_id`),
  KEY `case_statements_recorded_by_officer_id_foreign` (`recorded_by_officer_id`),
  KEY `case_statements_verified_by_officer_id_foreign` (`verified_by_officer_id`),
  CONSTRAINT `case_statements_case_id_foreign` FOREIGN KEY (`case_id`) REFERENCES `cases` (`id`) ON DELETE CASCADE,
  CONSTRAINT `case_statements_recorded_by_officer_id_foreign` FOREIGN KEY (`recorded_by_officer_id`) REFERENCES `users` (`id`),
  CONSTRAINT `case_statements_verified_by_officer_id_foreign` FOREIGN KEY (`verified_by_officer_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `case_statements`
--

INSERT INTO `case_statements` VALUES (1,1,'suspect','Modou Sanneh','+1 (808) 626-0470','6660 Buck Summit\nPort Vanceberg, WI 66785-3714','passport','ID-1265577',7,'2025-06-07 00:00:00','I, Modou Sanneh, provide this statement regarding the allegations against me. Optio est quidem sapiente quia saepe voluptatum. Voluptas molestiae veniam quasi et rerum assumenda.\n\nOn June 6, 2025, I was Animi voluptas magnam quia quis impedit molestiae eligendi rerum corrupti eum dolore pariatur quam.. Aliquam pariatur veniam fugit facilis harum assumenda non. Commodi mollitia consequatur tempore beatae. Occaecati placeat in iste fugit adipisci placeat nam. Alias modi dolores molestiae in nulla. Est rerum voluptates iusto possimus nulla consectetur earum repellat.\n\nI deny any involvement in the alleged robbery and wish to state that Qui accusantium qui et neque cumque iusto iste sunt. Debitis corporis cum illum iure.','Police Station - Lake Wyattmouth',1,1,NULL,NULL,'Corporis ea magnam consequatur a labore voluptatum rem porro soluta est aut.',0,NULL,NULL,'2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(2,1,'victim','Alieu Jallow','+12813033330','55413 Clementine Views Apt. 228\nLake Vanceview, CO 16663','national_id','ID-7114833',4,'2025-06-08 00:00:00','I, Alieu Jallow, was the victim of robbery on June 6, 2025 at approximately 07:04 PM. Enim officiis laborum inventore eaque rerum aut. Iste rerum cupiditate delectus quam. Est magnam non ut.\n\nThe incident occurred at Kairaba Avenue, Serrekunda. Ullam quidem in rem architecto beatae fugiat. Facere blanditiis sequi nisi ullam libero.\n\nI can identify the perpetrator as Ruth Daniel. Dicta blanditiis totam dignissimos mollitia laudantium est. Ducimus ut dolore quisquam molestias rerum iusto. Tenetur consequuntur inventore harum veniam.','Police Station - Lake Reyes',1,1,NULL,NULL,'Ullam laborum asperiores quia minus quam aut sed distinctio id quis.',1,5,'2025-06-09 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(3,1,'witness','Nathaniel Herman','+18385645559','33557 Marjory Locks Suite 622\nNorth Stellafurt, DC 90374','other','ID-6948424',4,'2025-06-10 00:00:00','I, Nathaniel Herman, witnessed an incident on June 6, 2025 at approximately 12:27 PM. Laboriosam velit et dolore. Numquam dicta fugiat omnis suscipit unde debitis nihil excepturi. Ad pariatur qui vel voluptas et unde ipsa.\n\nI was at Kairaba Avenue, Serrekunda when I observed Quasi magnam qui sed nihil iste est consectetur autem blanditiis.. Aspernatur aut architecto dolor et sequi possimus ipsa nemo. Eos dolore consequatur vero aliquam quas alias sequi. Neque eveniet vel exercitationem amet ab.\n\nI can describe the suspect as Aut ut veritatis magnam vel velit et et eius quibusdam quisquam..','Police Station - Hermanport',1,1,NULL,NULL,NULL,1,3,'2025-06-12 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(4,1,'witness','Dr. Amari Gerlach I','+1.806.999.7977','98477 Jast Plaza Apt. 331\nKiraport, UT 87623','voter_id','ID-5757186',4,'2025-06-09 00:00:00','I, Dr. Amari Gerlach I, witnessed an incident on June 6, 2025 at approximately 05:44 PM. Facere optio reiciendis eveniet autem rerum quia aperiam. Quo omnis quisquam repellendus. Magnam impedit eos nisi debitis ut.\n\nI was at Kairaba Avenue, Serrekunda when I observed Inventore voluptatem suscipit dolor ratione consequuntur non.. Ut magni sunt est animi. Quasi deserunt reprehenderit consectetur omnis et unde. Et modi est quod. Ab deleniti quisquam voluptatem ipsum.\n\nI can describe the suspect as Totam voluptas quia mollitia cupiditate magnam est quisquam consequuntur sint..','Police Station - Greggmouth',1,0,NULL,NULL,'Dolorum sunt blanditiis et aliquid tempora sint dolore hic.',1,5,'2025-06-11 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(5,1,'witness','Ludie McCullough','+1-325-422-8591','8550 Larson Ridges\nSouth Audraport, AR 53918','voter_id','ID-6639046',6,'2025-06-10 00:00:00','I, Ludie McCullough, witnessed an incident on June 6, 2025 at approximately 03:33 AM. Ea labore sed earum perferendis. Maiores voluptatum consequatur inventore molestias aut. Dolores provident unde rerum et.\n\nI was at Kairaba Avenue, Serrekunda when I observed Repellendus consequatur asperiores sunt quo id quod cupiditate.. Error voluptas facilis numquam autem ut dolores. Est eius modi et delectus iure sit. Facilis mollitia molestiae architecto recusandae totam.\n\nI can describe the suspect as Reiciendis sed nihil libero omnis pariatur maiores..','Police Station - Emardburgh',0,1,NULL,NULL,'Dicta enim libero non dolor omnis aut nisi et dignissimos eligendi et.',1,5,'2025-06-11 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(6,2,'victim','Omar Sowe','+1.929.336.5061','306 Casandra Dam Suite 506\nBruenmouth, NM 60461','passport','ID-1648096',3,'2025-06-11 00:00:00','I, Omar Sowe, was the victim of theft on June 9, 2025 at approximately 11:00 AM. Quis et fugit aut enim voluptatem qui. Veniam neque quam ratione illo.\n\nThe incident occurred at Senegambia Beach Hotel, Kololi. Sapiente eum ab non saepe quos quia delectus. Id quidem dolor non non quibusdam maxime. Aut amet aspernatur rerum omnis quia ipsa deserunt tempora.\n\nI can identify the perpetrator as Rachelle Nolan. Voluptatem perferendis consequuntur odit et velit. Quaerat nulla in repellendus et laudantium nesciunt. Est autem dolor est nemo in voluptatem.','Police Station - Barrowsmouth',1,1,NULL,NULL,'Dolores vero itaque illum quas illum possimus provident et magnam.',1,7,'2025-06-12 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(7,2,'suspect','Lamin Touray','+1-934-337-4191','252 Herminio Stravenue\nNorth Careychester, KS 15060-3503','passport','ID-3352234',3,'2025-06-20 00:00:00','I, Lamin Touray, provide this statement regarding the allegations against me. Magnam rerum saepe inventore labore aut. Sunt adipisci cupiditate eligendi qui. Enim veniam sint molestiae magnam ut.\n\nOn June 9, 2025, I was Similique ullam dolores sequi sed sed et consequatur enim veniam voluptas in.. Aspernatur quos magnam doloribus tempore adipisci. Laboriosam tempore laboriosam in atque nam cupiditate est. Eveniet nihil consequatur est corrupti dolorem dolorem sequi doloremque.\n\nI deny any involvement in the alleged theft and wish to state that Nobis odit enim consequuntur laboriosam eos corporis. Quas ut eum amet soluta.','Police Station - Samirmouth',1,1,NULL,NULL,'Laudantium maxime cumque rerum nulla ipsum porro.',1,6,'2025-06-21 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(8,2,'witness','Miss Raegan Abernathy IV','+1.901.906.4226','91722 Mills Knoll\nSouth Destinhaven, AZ 31112-1551','voter_id','ID-3276982',4,'2025-06-19 00:00:00','I, Miss Raegan Abernathy IV, witnessed an incident on June 9, 2025 at approximately 04:41 PM. Ut autem suscipit nihil incidunt assumenda corrupti nostrum modi. Saepe rerum et ipsum quasi suscipit.\n\nI was at Senegambia Beach Hotel, Kololi when I observed Est occaecati dolores omnis quia molestias assumenda nisi quas vero aut.. Saepe magnam omnis ad voluptatum. Nulla reiciendis pariatur impedit totam voluptates quod quos. Illum iste recusandae repellendus quibusdam.\n\nI can describe the suspect as Qui veritatis provident omnis deleniti nostrum omnis eligendi aspernatur..','Police Station - Lake Rahsaanmouth',0,1,NULL,NULL,'Quia ratione explicabo provident est assumenda quos velit.',1,3,'2025-06-20 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(9,2,'suspect','Lamin Touray','1-863-464-6146','822 Charity Streets Apt. 814\nPort Zechariah, OK 68653','other','ID-5033760',7,'2025-06-20 00:00:00','I, Lamin Touray, provide this statement regarding the allegations against me. Occaecati eos amet dignissimos veritatis corrupti dignissimos. Temporibus est nihil aut doloremque corporis qui quidem. Aut et voluptatem totam et est facere doloremque.\n\nOn June 9, 2025, I was Perspiciatis qui magni earum id aut odio tenetur culpa officiis quam soluta sed qui.. Et et aut odit unde delectus aliquam. Excepturi molestias qui qui illo. Illo quaerat autem eum vero animi.\n\nI deny any involvement in the alleged theft and wish to state that Ut voluptas sint ullam sequi voluptas. Repellendus odit minus ex sunt quos dolor commodi.','Police Station - Lake Lonny',1,1,NULL,NULL,NULL,1,7,'2025-06-21 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(10,2,'expert','Moshe Leuschke PhD','(820) 396-0187','73312 Mills Rue Apt. 394\nFlatleyshire, TX 12576','passport','ID-6701486',4,'2025-06-13 00:00:00','I, Moshe Leuschke PhD, a qualified expert in Production Planning, provide this professional assessment regarding case #GPCMS-2025-0002. Voluptatem fuga voluptates soluta. Autem veniam ducimus necessitatibus itaque quae possimus. Autem expedita asperiores laboriosam tempora quisquam accusantium inventore.\n\nBased on my examination of the evidence, I conclude that Occaecati nostrum quo neque deleniti aperiam quis tenetur. Accusamus et totam reprehenderit laudantium perspiciatis porro vitae. Sequi earum ab sit et. Et temporibus enim sed suscipit aut cumque ut.\n\nMy professional opinion is that Magni eos occaecati voluptatem illum incidunt fugit. Architecto quod fugiat velit voluptas natus.\n\nThis assessment is based on my 15 years of experience in the field.','Police Station - Bednarfurt',1,0,NULL,NULL,'Nostrum ea occaecati optio error omnis aut accusantium alias iusto.',1,3,'2025-06-15 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(11,3,'witness','Miss Josefa Stehr DDS','1-270-986-5643','652 Ziemann Grove\nHansenberg, ME 88710','passport','ID-5622339',7,'2025-06-23 00:00:00','I, Miss Josefa Stehr DDS, witnessed an incident on June 10, 2025 at approximately 02:47 AM. Itaque ad vel magnam dicta animi officia et modi. Eaque amet laboriosam ipsum.\n\nI was at Westfield Junction, Serrekunda when I observed Dolore praesentium hic dolores nostrum nulla non et quia quo aut.. Quia sint doloribus hic aspernatur numquam voluptatem. Et qui temporibus et nihil sint reiciendis. Voluptatum qui omnis recusandae aut. Rerum laborum voluptatem sint dolorem ut ratione inventore.\n\nI can describe the suspect as Minima non illo exercitationem incidunt amet aut dolorum nostrum quae adipisci..','Police Station - Port Maureenchester',1,1,NULL,NULL,NULL,1,7,'2025-06-26 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(12,3,'expert','Prof. Monroe Kerluke','1-351-892-5946','2326 Casey Drives\nLake Derekborough, OR 47415','other','ID-1939556',4,'2025-06-20 00:00:00','I, Prof. Monroe Kerluke, a qualified expert in Social and Human Service Assistant, provide this professional assessment regarding case #GPCMS-2025-0003. Ducimus et velit dolore. Sit quis ut asperiores hic temporibus et incidunt. Inventore soluta vitae nam excepturi.\n\nBased on my examination of the evidence, I conclude that Error ex omnis modi odit in. Nihil aut voluptatem consequatur minus nostrum. Voluptatum atque rerum consequatur illo ad.\n\nMy professional opinion is that Ab sint sit minima architecto qui. Itaque a quia eius assumenda.\n\nThis assessment is based on my 8 years of experience in the field.','Police Station - Casandramouth',1,1,NULL,NULL,'Nostrum consequuntur sint vitae velit nemo quis placeat repudiandae.',1,7,'2025-06-23 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(13,3,'victim','Isatou Ceesay','910.430.3137','911 Precious Common Apt. 213\nHesselmouth, KS 30837-4924','voter_id','ID-1794561',3,'2025-06-14 00:00:00','I, Isatou Ceesay, was the victim of assault on June 10, 2025 at approximately 07:22 PM. Vitae consequatur blanditiis tempore sunt quis cum beatae. Reiciendis dignissimos aut possimus eos sit possimus nemo est.\n\nThe incident occurred at Westfield Junction, Serrekunda. Vitae deleniti atque velit dolorem. Voluptatem est excepturi dignissimos laborum soluta. Eveniet eveniet iusto illum possimus dolorum reprehenderit.\n\nI can identify the perpetrator as Ms. Leonie Connelly DVM. Doloribus eum et voluptatibus. Consequatur quia eveniet ad deserunt rerum nam quo. Officiis adipisci eius et a et illo sapiente.','Police Station - Lake Stanland',1,1,NULL,NULL,'Reprehenderit soluta cupiditate sed vitae quae error dolorum impedit eos cupiditate velit molestiae molestias.',0,NULL,NULL,'2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(14,3,'victim','Isatou Ceesay','+1-252-851-0932','7012 Flatley Radial Apt. 743\nPort Stefan, NJ 74206-2341','drivers_license','ID-9180334',6,'2025-06-18 00:00:00','I, Isatou Ceesay, was the victim of assault on June 10, 2025 at approximately 01:02 AM. Impedit soluta non et maxime consequatur minima. Sunt molestiae omnis est nostrum.\n\nThe incident occurred at Westfield Junction, Serrekunda. Ea voluptas dignissimos hic rerum et consequatur consequatur illo. Reiciendis voluptatem eius inventore aliquam maiores.\n\nI can identify the perpetrator as an unknown person. Excepturi qui rerum consequatur autem. Dicta aliquam tenetur ut occaecati velit nulla facilis.','Police Station - Henrietteborough',1,1,NULL,NULL,'Voluptatem non veritatis cupiditate aut voluptatem impedit.',0,NULL,NULL,'2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(15,4,'suspect','Ebrima Bah','872-504-5375','5550 Kihn Branch Apt. 295\nPort Rasheed, PA 57078','national_id','ID-6004235',5,'2025-06-10 00:00:00','I, Ebrima Bah, provide this statement regarding the allegations against me. Architecto occaecati blanditiis veniam corporis ut aperiam consequatur. Maxime voluptatem earum sit cum quas.\n\nOn June 4, 2025, I was Suscipit amet perferendis cupiditate nihil illo ea ea dolorum in laudantium in tempora corrupti.. Amet consectetur consequatur deleniti quae ducimus minus. Et voluptas ut voluptate id. Et atque aspernatur nam error iure aut quis.\n\nI deny any involvement in the alleged burglary and wish to state that Aut qui dolor nihil alias.','Police Station - Brennanland',1,1,NULL,NULL,NULL,1,7,'2025-06-11 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(16,4,'officer','Modou Jallow','+1-757-475-4514','183 Jimmy Parkway Suite 574\nLouisastad, MN 84945-4825','passport','ID-9667349',3,'2025-06-11 00:00:00','I, Officer Modou Jallow, badge number 3558, was on duty on June 4, 2025. I was dispatched to Fajara, Bakau at approximately 04:05 PM following a report of burglary. Saepe corrupti consequatur voluptatem dolor. Quod rerum sit voluptates commodi ut velit et. Voluptatibus repudiandae culpa voluptatem consequatur nisi doloremque possimus. Alias consequatur qui ratione magni sit. Deserunt quas voluptatum eaque omnis blanditiis placeat et deserunt.\n\nUpon arrival, I observed Delectus dolores molestiae quaerat eius voluptas. Nisi iusto nihil minus corrupti debitis id. Beatae id odio eius eos fugit.\n\nI proceeded to Quasi quos non unde sit est dolor.. Ducimus soluta non a voluptatum dolores. Quisquam aperiam alias ad aliquam quae ut asperiores autem.','Police Station - North Leilahaven',1,1,NULL,NULL,'Esse non tempore voluptatem consectetur aliquam labore qui quaerat sunt enim quidem dolor.',1,4,'2025-06-13 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(17,4,'expert','Gideon Langworth V','(480) 985-5615','80817 Luettgen Fork Suite 162\nSamarashire, MT 16014-5113','national_id','ID-8993731',6,'2025-06-13 00:00:00','I, Gideon Langworth V, a qualified expert in Drycleaning Machine Operator, provide this professional assessment regarding case #GPCMS-2025-0004. Corporis laboriosam tempora amet. Pariatur culpa quia quis.\n\nBased on my examination of the evidence, I conclude that Eaque exercitationem saepe aut amet assumenda magni. Quibusdam voluptatum amet minima deleniti exercitationem repudiandae. Consequatur nemo labore et debitis non repellendus. Delectus quia corporis pariatur et ut pariatur iste.\n\nMy professional opinion is that Ab voluptates eum odit. Voluptatem illo amet tempora veniam qui. Voluptatem dicta eligendi distinctio nesciunt.\n\nThis assessment is based on my 19 years of experience in the field.','Police Station - Opheliaside',1,0,NULL,NULL,NULL,1,3,'2025-06-14 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(18,4,'victim','James Johnson','+1-435-906-6062','50144 Shields Course Apt. 066\nJimmietown, AK 42765','passport','ID-7513391',3,'2025-06-07 00:00:00','I, James Johnson, was the victim of burglary on June 4, 2025 at approximately 08:36 AM. Neque atque tempore voluptatum nam dolorum. Excepturi qui est ipsa consectetur. Rerum autem vel tempora error. Sed fuga et sit.\n\nThe incident occurred at Fajara, Bakau. Architecto in eligendi quos occaecati quia repellendus quae modi. Quo qui deserunt eius nisi non.\n\nI can identify the perpetrator as Lambert Bauch PhD. Temporibus est ut sunt voluptas. Quidem incidunt voluptatibus officia nobis ab.','Police Station - Fritschtown',1,1,NULL,NULL,'Qui dolorum voluptatibus ut quisquam maiores ratione perspiciatis illum odio.',1,5,'2025-06-09 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(19,4,'expert','Adolphus Quitzon','+1.534.846.0080','937 Bruen Pike\nFreidastad, AR 63499','voter_id','ID-3685562',4,'2025-06-08 00:00:00','I, Adolphus Quitzon, a qualified expert in Urban Planner, provide this professional assessment regarding case #GPCMS-2025-0004. Sed odio asperiores autem alias. Ut harum et architecto error id iste quis.\n\nBased on my examination of the evidence, I conclude that Minima aut suscipit nostrum sapiente similique voluptas impedit. Est omnis assumenda in at rerum maiores. Labore sint consequatur dolor mollitia blanditiis aut.\n\nMy professional opinion is that Non omnis labore voluptates quo omnis. Nam possimus ut quos sed quidem porro suscipit. Voluptas ducimus eum aut eaque deserunt.\n\nThis assessment is based on my 12 years of experience in the field.','Police Station - Lake Johnpaul',1,1,NULL,NULL,'Consequatur eaque rerum doloribus nulla consequatur sit.',1,3,'2025-06-10 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(20,5,'officer','Fatou Ceesay','+1.848.826.6727','84025 Greenholt Brooks\nJohannberg, MS 22697','national_id','ID-6591473',5,'2025-06-20 00:00:00','I, Officer Fatou Ceesay, badge number 7765, was on duty on June 11, 2025. I was dispatched to Banjul Ferry Terminal, Banjul at approximately 03:43 AM following a report of drugs. Quas omnis ut quidem doloribus. Qui vero consectetur qui aut et ut. Est aut perferendis placeat et vel aliquam. Iure ipsam et deleniti magnam.\n\nUpon arrival, I observed Eos temporibus consequuntur a ipsam rerum ut aut. Dolores voluptatem dolorem voluptate.\n\nI proceeded to Sunt vel facilis sit ea temporibus.. Repellat eveniet perspiciatis vel ut. Repellendus assumenda consequatur voluptate aspernatur.','Police Station - Roweport',1,1,NULL,NULL,'Dolorem voluptas numquam accusantium iste voluptatem repudiandae debitis blanditiis impedit modi omnis.',1,7,'2025-06-21 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(21,5,'victim','Fatou Njie','+16205458980','513 Schaden Junctions Suite 842\nNiabury, TN 12866-1115','voter_id','ID-5064478',5,'2025-06-25 00:00:00','I, Fatou Njie, was the victim of drugs on June 11, 2025 at approximately 02:51 PM. Ratione dolores quis fugit voluptatibus assumenda libero. Id asperiores reprehenderit est nihil. Ea impedit beatae esse corrupti. Tempore dolores corrupti delectus ut veritatis dolores reiciendis.\n\nThe incident occurred at Banjul Ferry Terminal, Banjul. Doloremque libero et quia aut eum nisi quia. Quod rem et qui cum.\n\nI can identify the perpetrator as Theodore Schowalter. Officia tempora est nam fugit autem sit. Debitis et omnis culpa doloribus officia vel.','Police Station - South Randal',0,1,NULL,NULL,'Reiciendis illum vel beatae sequi culpa veritatis esse soluta.',0,NULL,NULL,'2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(22,5,'officer','Fatou Jallow','+18209672042','28114 Manuel Junctions\nLake Sherwood, AL 96587','voter_id','ID-8868653',3,'2025-06-12 00:00:00','I, Officer Fatou Jallow, badge number 6750, was on duty on June 11, 2025. I was dispatched to Banjul Ferry Terminal, Banjul at approximately 12:28 PM following a report of drugs. Ea ad voluptates architecto corrupti omnis rerum. Itaque commodi esse provident eius minus beatae.\n\nUpon arrival, I observed Animi nam rem expedita ab corporis. Quis aperiam consequuntur tempore et libero optio.\n\nI proceeded to Recusandae corporis dolore rerum ea molestiae id.. Distinctio et error nisi. Est porro laborum libero ducimus.','Police Station - West Jade',1,0,NULL,NULL,'Vitae beatae sit ducimus dicta magnam sunt enim doloremque doloremque laudantium et.',1,7,'2025-06-14 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL),(23,5,'officer','Fatou Jallow','+1.304.306.4548','425 Beatty Mall\nJasminside, ME 60005','passport','ID-0690443',4,'2025-06-13 00:00:00','I, Officer Fatou Jallow, badge number 7650, was on duty on June 11, 2025. I was dispatched to Banjul Ferry Terminal, Banjul at approximately 09:03 AM following a report of drugs. Rem velit veniam repellat beatae assumenda dolores in. Sed doloremque incidunt alias assumenda quia labore. Ut excepturi totam sed omnis consequatur. Reiciendis asperiores aliquam accusantium ex. Rerum sequi optio qui amet.\n\nUpon arrival, I observed Dolorum distinctio vel et veritatis velit. Nihil nihil dolores provident maxime sit fuga.\n\nI proceeded to Nihil nihil quia culpa libero laudantium asperiores facilis aut.. Cumque officiis quaerat nemo dolore dolores omnis. Quia dolorem officiis amet aut a ut aut qui. Ut aliquam veritatis ipsam error eaque unde.','Police Station - Beerborough',0,1,NULL,NULL,'Quos iusto corrupti aut id dolorum nemo enim maxime.',1,4,'2025-06-15 00:00:00','2025-06-12 14:17:21','2025-06-12 14:17:21',NULL);

--
-- Table structure for table `case_suspects`
--

CREATE TABLE `case_suspects` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `case_id` bigint(20) unsigned NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `alias` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `nationality` varchar(255) DEFAULT NULL,
  `id_type` varchar(255) DEFAULT NULL,
  `id_number` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `physical_description` text DEFAULT NULL,
  `photo_path` varchar(255) DEFAULT NULL,
  `relationship_to_victim` text DEFAULT NULL,
  `alleged_involvement` text DEFAULT NULL,
  `is_arrested` tinyint(1) NOT NULL DEFAULT 0,
  `arrest_date` datetime DEFAULT NULL,
  `arrest_location` varchar(255) DEFAULT NULL,
  `arrested_by_officer_id` bigint(20) unsigned DEFAULT NULL,
  `is_charged` tinyint(1) NOT NULL DEFAULT 0,
  `charges` varchar(255) DEFAULT NULL,
  `charge_date` datetime DEFAULT NULL,
  `is_in_custody` tinyint(1) NOT NULL DEFAULT 0,
  `custody_location` varchar(255) DEFAULT NULL,
  `criminal_history` text DEFAULT NULL,
  `officer_notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `case_suspects_case_id_foreign` (`case_id`),
  KEY `case_suspects_arrested_by_officer_id_foreign` (`arrested_by_officer_id`),
  CONSTRAINT `case_suspects_arrested_by_officer_id_foreign` FOREIGN KEY (`arrested_by_officer_id`) REFERENCES `users` (`id`),
  CONSTRAINT `case_suspects_case_id_foreign` FOREIGN KEY (`case_id`) REFERENCES `cases` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `case_suspects`
--

INSERT INTO `case_suspects` VALUES (1,1,'Modou Sanneh','Mo','male',NULL,28,'Gambian','national_id','GN987654','Bundung, Serrekunda','+2207444555',NULL,'Height: 175cm, Weight: 70kg, Eye color: brown, Hair color: black, Scar on right cheek',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified through CCTV footage. Known to frequent Serrekunda market area.','2025-06-12 13:53:34','2025-06-12 13:53:34',NULL),(2,2,'Lamin Touray','LT','male',NULL,32,'Gambian','national_id','GN456123','Bakau New Town, Bakau','+2207666777',NULL,'Height: 180cm, Weight: 85kg, Eye color: brown, Hair color: black, Tattoo on left arm',NULL,NULL,'arrested',1,'2025-06-11 00:00:00','Banjul Ferry Terminal, Banjul',NULL,0,NULL,NULL,0,NULL,NULL,'Arrested during routine search at ferry terminal. Found with suspected cannabis.','2025-06-12 13:53:34','2025-06-12 13:53:34',NULL),(3,3,'Ousman Jobe','Ous','male',NULL,25,'Gambian','drivers_license','DL123789','Latrikunda, Serrekunda','+2207888999',NULL,'Height: 170cm, Weight: 65kg, Eye color: brown, Hair color: black, None',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified by witnesses at the scene of assault at Westfield Junction.','2025-06-12 13:53:34','2025-06-12 13:53:34',NULL),(4,4,'Ebrima Bah','Ebou','male',NULL,30,'Gambian','national_id','GN789012','Kololi, Kanifing Municipality','+2207222333',NULL,'Height: 178cm, Weight: 75kg, Eye color: brown, Hair color: black, Beard',NULL,NULL,'person_of_interest',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Person of interest in vehicle theft case. Seen near the hotel around the time of the incident.','2025-06-12 13:53:34','2025-06-12 13:53:34',NULL),(5,1,'Modou Sanneh','Mo','male',NULL,28,'Gambian','national_id','GN987654','Bundung, Serrekunda','+2207444555',NULL,'Height: 175cm, Weight: 70kg, Eye color: brown, Hair color: black, Scar on right cheek',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified through CCTV footage. Known to frequent Serrekunda market area.','2025-06-12 13:58:33','2025-06-12 13:58:33',NULL),(6,2,'Lamin Touray','LT','male',NULL,32,'Gambian','national_id','GN456123','Bakau New Town, Bakau','+2207666777',NULL,'Height: 180cm, Weight: 85kg, Eye color: brown, Hair color: black, Tattoo on left arm',NULL,NULL,'arrested',1,'2025-06-11 00:00:00','Banjul Ferry Terminal, Banjul',NULL,0,NULL,NULL,0,NULL,NULL,'Arrested during routine search at ferry terminal. Found with suspected cannabis.','2025-06-12 13:58:33','2025-06-12 13:58:33',NULL),(7,3,'Ousman Jobe','Ous','male',NULL,25,'Gambian','drivers_license','DL123789','Latrikunda, Serrekunda','+2207888999',NULL,'Height: 170cm, Weight: 65kg, Eye color: brown, Hair color: black, None',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified by witnesses at the scene of assault at Westfield Junction.','2025-06-12 13:58:33','2025-06-12 13:58:33',NULL),(8,4,'Ebrima Bah','Ebou','male',NULL,30,'Gambian','national_id','GN789012','Kololi, Kanifing Municipality','+2207222333',NULL,'Height: 178cm, Weight: 75kg, Eye color: brown, Hair color: black, Beard',NULL,NULL,'person_of_interest',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Person of interest in vehicle theft case. Seen near the hotel around the time of the incident.','2025-06-12 13:58:33','2025-06-12 13:58:33',NULL),(9,1,'Modou Sanneh','Mo','male',NULL,28,'Gambian','national_id','GN987654','Bundung, Serrekunda','+2207444555',NULL,'Height: 175cm, Weight: 70kg, Eye color: brown, Hair color: black, Scar on right cheek',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified through CCTV footage. Known to frequent Serrekunda market area.','2025-06-12 13:59:47','2025-06-12 13:59:47',NULL),(10,2,'Lamin Touray','LT','male',NULL,32,'Gambian','national_id','GN456123','Bakau New Town, Bakau','+2207666777',NULL,'Height: 180cm, Weight: 85kg, Eye color: brown, Hair color: black, Tattoo on left arm',NULL,NULL,'arrested',1,'2025-06-11 00:00:00','Banjul Ferry Terminal, Banjul',NULL,0,NULL,NULL,0,NULL,NULL,'Arrested during routine search at ferry terminal. Found with suspected cannabis.','2025-06-12 13:59:47','2025-06-12 13:59:47',NULL),(11,3,'Ousman Jobe','Ous','male',NULL,25,'Gambian','drivers_license','DL123789','Latrikunda, Serrekunda','+2207888999',NULL,'Height: 170cm, Weight: 65kg, Eye color: brown, Hair color: black, None',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified by witnesses at the scene of assault at Westfield Junction.','2025-06-12 13:59:47','2025-06-12 13:59:47',NULL),(12,4,'Ebrima Bah','Ebou','male',NULL,30,'Gambian','national_id','GN789012','Kololi, Kanifing Municipality','+2207222333',NULL,'Height: 178cm, Weight: 75kg, Eye color: brown, Hair color: black, Beard',NULL,NULL,'person_of_interest',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Person of interest in vehicle theft case. Seen near the hotel around the time of the incident.','2025-06-12 13:59:47','2025-06-12 13:59:47',NULL),(13,1,'Modou Sanneh','Mo','male',NULL,28,'Gambian','national_id','GN987654','Bundung, Serrekunda','+2207444555',NULL,'Height: 175cm, Weight: 70kg, Eye color: brown, Hair color: black, Scar on right cheek',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified through CCTV footage. Known to frequent Serrekunda market area.','2025-06-12 14:02:29','2025-06-12 14:02:29',NULL),(14,2,'Lamin Touray','LT','male',NULL,32,'Gambian','national_id','GN456123','Bakau New Town, Bakau','+2207666777',NULL,'Height: 180cm, Weight: 85kg, Eye color: brown, Hair color: black, Tattoo on left arm',NULL,NULL,'arrested',1,'2025-06-11 00:00:00','Banjul Ferry Terminal, Banjul',NULL,0,NULL,NULL,0,NULL,NULL,'Arrested during routine search at ferry terminal. Found with suspected cannabis.','2025-06-12 14:02:29','2025-06-12 14:02:29',NULL),(15,3,'Ousman Jobe','Ous','male',NULL,25,'Gambian','drivers_license','DL123789','Latrikunda, Serrekunda','+2207888999',NULL,'Height: 170cm, Weight: 65kg, Eye color: brown, Hair color: black, None',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified by witnesses at the scene of assault at Westfield Junction.','2025-06-12 14:02:29','2025-06-12 14:02:29',NULL),(16,4,'Ebrima Bah','Ebou','male',NULL,30,'Gambian','national_id','GN789012','Kololi, Kanifing Municipality','+2207222333',NULL,'Height: 178cm, Weight: 75kg, Eye color: brown, Hair color: black, Beard',NULL,NULL,'person_of_interest',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Person of interest in vehicle theft case. Seen near the hotel around the time of the incident.','2025-06-12 14:02:29','2025-06-12 14:02:29',NULL),(17,1,'Modou Sanneh','Mo','male',NULL,28,'Gambian','national_id','GN987654','Bundung, Serrekunda','+2207444555',NULL,'Height: 175cm, Weight: 70kg, Eye color: brown, Hair color: black, Scar on right cheek',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified through CCTV footage. Known to frequent Serrekunda market area.','2025-06-12 14:05:00','2025-06-12 14:05:00',NULL),(18,2,'Lamin Touray','LT','male',NULL,32,'Gambian','national_id','GN456123','Bakau New Town, Bakau','+2207666777',NULL,'Height: 180cm, Weight: 85kg, Eye color: brown, Hair color: black, Tattoo on left arm',NULL,NULL,'arrested',1,'2025-06-11 00:00:00','Banjul Ferry Terminal, Banjul',NULL,0,NULL,NULL,0,NULL,NULL,'Arrested during routine search at ferry terminal. Found with suspected cannabis.','2025-06-12 14:05:00','2025-06-12 14:05:00',NULL),(19,3,'Ousman Jobe','Ous','male',NULL,25,'Gambian','drivers_license','DL123789','Latrikunda, Serrekunda','+2207888999',NULL,'Height: 170cm, Weight: 65kg, Eye color: brown, Hair color: black, None',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified by witnesses at the scene of assault at Westfield Junction.','2025-06-12 14:05:00','2025-06-12 14:05:00',NULL),(20,4,'Ebrima Bah','Ebou','male',NULL,30,'Gambian','national_id','GN789012','Kololi, Kanifing Municipality','+2207222333',NULL,'Height: 178cm, Weight: 75kg, Eye color: brown, Hair color: black, Beard',NULL,NULL,'person_of_interest',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Person of interest in vehicle theft case. Seen near the hotel around the time of the incident.','2025-06-12 14:05:00','2025-06-12 14:05:00',NULL),(21,1,'Modou Sanneh','Mo','male',NULL,28,'Gambian','national_id','GN987654','Bundung, Serrekunda','+2207444555',NULL,'Height: 175cm, Weight: 70kg, Eye color: brown, Hair color: black, Scar on right cheek',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified through CCTV footage. Known to frequent Serrekunda market area.','2025-06-12 14:05:49','2025-06-12 14:05:49',NULL),(22,2,'Lamin Touray','LT','male',NULL,32,'Gambian','national_id','GN456123','Bakau New Town, Bakau','+2207666777',NULL,'Height: 180cm, Weight: 85kg, Eye color: brown, Hair color: black, Tattoo on left arm',NULL,NULL,'arrested',1,'2025-06-11 00:00:00','Banjul Ferry Terminal, Banjul',NULL,0,NULL,NULL,0,NULL,NULL,'Arrested during routine search at ferry terminal. Found with suspected cannabis.','2025-06-12 14:05:49','2025-06-12 14:05:49',NULL),(23,3,'Ousman Jobe','Ous','male',NULL,25,'Gambian','drivers_license','DL123789','Latrikunda, Serrekunda','+2207888999',NULL,'Height: 170cm, Weight: 65kg, Eye color: brown, Hair color: black, None',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified by witnesses at the scene of assault at Westfield Junction.','2025-06-12 14:05:49','2025-06-12 14:05:49',NULL),(24,4,'Ebrima Bah','Ebou','male',NULL,30,'Gambian','national_id','GN789012','Kololi, Kanifing Municipality','+2207222333',NULL,'Height: 178cm, Weight: 75kg, Eye color: brown, Hair color: black, Beard',NULL,NULL,'person_of_interest',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Person of interest in vehicle theft case. Seen near the hotel around the time of the incident.','2025-06-12 14:05:49','2025-06-12 14:05:49',NULL),(25,1,'Modou Sanneh','Mo','male',NULL,28,'Gambian','national_id','GN987654','Bundung, Serrekunda','+2207444555',NULL,'Height: 175cm, Weight: 70kg, Eye color: brown, Hair color: black, Scar on right cheek',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified through CCTV footage. Known to frequent Serrekunda market area.','2025-06-12 14:09:51','2025-06-12 14:09:51',NULL),(26,2,'Lamin Touray','LT','male',NULL,32,'Gambian','national_id','GN456123','Bakau New Town, Bakau','+2207666777',NULL,'Height: 180cm, Weight: 85kg, Eye color: brown, Hair color: black, Tattoo on left arm',NULL,NULL,'arrested',1,'2025-06-11 00:00:00','Banjul Ferry Terminal, Banjul',NULL,0,NULL,NULL,0,NULL,NULL,'Arrested during routine search at ferry terminal. Found with suspected cannabis.','2025-06-12 14:09:51','2025-06-12 14:09:51',NULL),(27,3,'Ousman Jobe','Ous','male',NULL,25,'Gambian','drivers_license','DL123789','Latrikunda, Serrekunda','+2207888999',NULL,'Height: 170cm, Weight: 65kg, Eye color: brown, Hair color: black, None',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified by witnesses at the scene of assault at Westfield Junction.','2025-06-12 14:09:51','2025-06-12 14:09:51',NULL),(28,4,'Ebrima Bah','Ebou','male',NULL,30,'Gambian','national_id','GN789012','Kololi, Kanifing Municipality','+2207222333',NULL,'Height: 178cm, Weight: 75kg, Eye color: brown, Hair color: black, Beard',NULL,NULL,'person_of_interest',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Person of interest in vehicle theft case. Seen near the hotel around the time of the incident.','2025-06-12 14:09:51','2025-06-12 14:09:51',NULL),(29,1,'Modou Sanneh','Mo','male',NULL,28,NULL,'passport','GN987654','Bundung, Serrekunda','+2207444555',NULL,NULL,'suspects/photos/49NRQoB1Enblo0kYoo2V12X5t3kb6TyV3Fz8kLGi.jpg',NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified through CCTV footage. Known to frequent Serrekunda market area.','2025-06-12 14:10:23','2025-06-13 10:00:37',NULL),(30,2,'Lamin Touray','LT','male',NULL,32,'Gambian','national_id','GN456123','Bakau New Town, Bakau','+2207666777',NULL,'Height: 180cm, Weight: 85kg, Eye color: brown, Hair color: black, Tattoo on left arm',NULL,NULL,'arrested',1,'2025-06-11 00:00:00','Banjul Ferry Terminal, Banjul',NULL,0,NULL,NULL,0,NULL,NULL,'Arrested during routine search at ferry terminal. Found with suspected cannabis.','2025-06-12 14:10:23','2025-06-12 14:10:23',NULL),(31,3,'Ousman Jobe','Ous','male',NULL,25,'Gambian','drivers_license','DL123789','Latrikunda, Serrekunda','+2207888999',NULL,'Height: 170cm, Weight: 65kg, Eye color: brown, Hair color: black, None',NULL,NULL,'wanted',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Suspect identified by witnesses at the scene of assault at Westfield Junction.','2025-06-12 14:10:23','2025-06-12 14:10:23',NULL),(32,4,'Ebrima Bah','Ebou','male',NULL,30,'Gambian','national_id','GN789012','Kololi, Kanifing Municipality','+2207222333',NULL,'Height: 178cm, Weight: 75kg, Eye color: brown, Hair color: black, Beard',NULL,NULL,'person_of_interest',0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'Person of interest in vehicle theft case. Seen near the hotel around the time of the incident.','2025-06-12 14:10:23','2025-06-12 14:10:23',NULL);

--
-- Table structure for table `case_updates`
--

CREATE TABLE `case_updates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `case_id` bigint(20) unsigned NOT NULL,
  `update_type` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `officer_id` bigint(20) unsigned NOT NULL,
  `update_date` datetime NOT NULL,
  `location` varchar(255) DEFAULT NULL,
  `action_taken` text DEFAULT NULL,
  `results` text DEFAULT NULL,
  `next_steps` text DEFAULT NULL,
  `is_significant` tinyint(1) NOT NULL DEFAULT 0,
  `requires_followup` tinyint(1) NOT NULL DEFAULT 0,
  `followup_date` datetime DEFAULT NULL,
  `followup_action` varchar(255) DEFAULT NULL,
  `is_internal_only` tinyint(1) NOT NULL DEFAULT 0,
  `attachment_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `case_updates_case_id_foreign` (`case_id`),
  KEY `case_updates_officer_id_foreign` (`officer_id`),
  CONSTRAINT `case_updates_case_id_foreign` FOREIGN KEY (`case_id`) REFERENCES `cases` (`id`) ON DELETE CASCADE,
  CONSTRAINT `case_updates_officer_id_foreign` FOREIGN KEY (`officer_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `case_updates`
--

INSERT INTO `case_updates` VALUES (1,5,'report','Case report generated by super admin',1,'2025-06-12 16:52:35',NULL,NULL,NULL,NULL,0,0,NULL,NULL,0,NULL,'2025-06-12 16:52:35','2025-06-12 16:52:35',NULL),(2,5,'report','Case report generated by super admin',1,'2025-06-12 16:52:42',NULL,NULL,NULL,NULL,0,0,NULL,NULL,0,NULL,'2025-06-12 16:52:42','2025-06-12 16:52:42',NULL),(3,5,'report','Case report generated by super admin',1,'2025-06-12 16:53:27',NULL,NULL,NULL,NULL,0,0,NULL,NULL,0,NULL,'2025-06-12 16:53:27','2025-06-12 16:53:27',NULL),(4,1,'suspect','Modou Sanneh arrest status updated',1,'2025-06-13 02:11:48',NULL,NULL,NULL,NULL,0,0,NULL,NULL,0,NULL,'2025-06-13 02:11:48','2025-06-13 02:11:48',NULL);

--
-- Table structure for table `case_victims`
--

CREATE TABLE `case_victims` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `case_id` bigint(20) unsigned NOT NULL,
  `victim_type` enum('individual','organization') NOT NULL DEFAULT 'individual',
  `full_name` varchar(255) NOT NULL,
  `organization_name` varchar(255) DEFAULT NULL,
  `organization_type` varchar(255) DEFAULT NULL,
  `registration_number` varchar(255) DEFAULT NULL,
  `contact_person_name` varchar(255) DEFAULT NULL,
  `contact_person_role` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `nationality` varchar(255) DEFAULT NULL,
  `id_type` varchar(255) DEFAULT NULL,
  `id_number` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `occupation` varchar(255) DEFAULT NULL,
  `physical_description` text DEFAULT NULL,
  `photo_path` varchar(255) DEFAULT NULL,
  `evidence_photos` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`evidence_photos`)),
  `injuries_sustained` text DEFAULT NULL,
  `injury_status` enum('none','minor','moderate','severe','critical','deceased') NOT NULL DEFAULT 'none',
  `medical_report` text DEFAULT NULL,
  `medical_facility` varchar(255) DEFAULT NULL,
  `relationship_to_suspect` text DEFAULT NULL,
  `is_vulnerable` tinyint(1) NOT NULL DEFAULT 0,
  `vulnerability_details` text DEFAULT NULL,
  `requires_protection` tinyint(1) NOT NULL DEFAULT 0,
  `protection_details` text DEFAULT NULL,
  `incident_date` datetime DEFAULT NULL,
  `incident_location` varchar(255) DEFAULT NULL,
  `incident_description` text DEFAULT NULL,
  `is_deceased` tinyint(1) NOT NULL DEFAULT 0,
  `time_of_death` datetime DEFAULT NULL,
  `cause_of_death` varchar(255) DEFAULT NULL,
  `officer_notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_by_user_id` bigint(20) unsigned DEFAULT NULL,
  `updated_by_user_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `case_victims_case_id_foreign` (`case_id`),
  KEY `case_victims_created_by_user_id_foreign` (`created_by_user_id`),
  KEY `case_victims_updated_by_user_id_foreign` (`updated_by_user_id`),
  CONSTRAINT `case_victims_case_id_foreign` FOREIGN KEY (`case_id`) REFERENCES `cases` (`id`) ON DELETE CASCADE,
  CONSTRAINT `case_victims_created_by_user_id_foreign` FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `case_victims_updated_by_user_id_foreign` FOREIGN KEY (`updated_by_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `case_victims`
--

INSERT INTO `case_victims` VALUES (1,1,'individual','Alieu Jallow',NULL,NULL,NULL,NULL,NULL,'male',NULL,35,'Gambian','national_id','GN123456','Pipeline, Serrekunda','+2207654321','alieu.jallow@example.com','Shopkeeper',NULL,NULL,'\"[]\"',NULL,'minor',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-06 00:00:00','Kairaba Avenue, Serrekunda','Victim was threatened with a firearm during the robbery of his shop.',0,NULL,NULL,NULL,'2025-06-12 13:38:15','2025-06-12 13:38:15',NULL,3,3),(2,2,'individual','Omar Sowe',NULL,NULL,NULL,NULL,NULL,'male',NULL,42,'Gambian','drivers_license','DL789012','Kololi, Kanifing Municipality','+2207123456','omar.sowe@example.com','Business Owner',NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-09 00:00:00','Senegambia Beach Hotel, Kololi','Vehicle stolen from hotel parking lot while victim was at a meeting.',0,NULL,NULL,NULL,'2025-06-12 13:38:15','2025-06-12 13:38:15',NULL,4,4),(3,1,'individual','Alieu Jallow',NULL,NULL,NULL,NULL,NULL,'male',NULL,35,'Gambian','national_id','GN123456','Pipeline, Serrekunda','+2207654321','alieu.jallow@example.com','Shopkeeper',NULL,NULL,'\"[]\"',NULL,'minor',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-06 00:00:00','Kairaba Avenue, Serrekunda','Victim was threatened with a firearm during the robbery of his shop.',0,NULL,NULL,NULL,'2025-06-12 13:51:41','2025-06-12 13:51:41',NULL,3,3),(4,2,'individual','Omar Sowe',NULL,NULL,NULL,NULL,NULL,'male',NULL,42,'Gambian','drivers_license','DL789012','Kololi, Kanifing Municipality','+2207123456','omar.sowe@example.com','Business Owner',NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-09 00:00:00','Senegambia Beach Hotel, Kololi','Vehicle stolen from hotel parking lot while victim was at a meeting.',0,NULL,NULL,NULL,'2025-06-12 13:51:41','2025-06-12 13:51:41',NULL,4,4),(5,3,'individual','Isatou Ceesay',NULL,NULL,NULL,NULL,NULL,'female',NULL,28,'Gambian','national_id','GN456789','Westfield Junction, Serrekunda','+2207888999','isatou.ceesay@example.com','Teacher',NULL,NULL,'\"[]\"',NULL,'severe',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-10 00:00:00','Westfield Junction, Serrekunda','Victim was assaulted while waiting for transport. Sustained head injuries requiring hospitalization.',0,NULL,NULL,NULL,'2025-06-12 13:51:41','2025-06-12 13:51:41',NULL,5,5),(6,1,'individual','Alieu Jallow',NULL,NULL,NULL,NULL,NULL,'male',NULL,35,'Gambian','national_id','GN123456','Pipeline, Serrekunda','+2207654321','alieu.jallow@example.com','Shopkeeper',NULL,NULL,'\"[]\"',NULL,'minor',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-06 00:00:00','Kairaba Avenue, Serrekunda','Victim was threatened with a firearm during the robbery of his shop.',0,NULL,NULL,NULL,'2025-06-12 13:52:23','2025-06-12 13:52:23',NULL,3,3),(7,2,'individual','Omar Sowe',NULL,NULL,NULL,NULL,NULL,'male',NULL,42,'Gambian','drivers_license','DL789012','Kololi, Kanifing Municipality','+2207123456','omar.sowe@example.com','Business Owner',NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-09 00:00:00','Senegambia Beach Hotel, Kololi','Vehicle stolen from hotel parking lot while victim was at a meeting.',0,NULL,NULL,NULL,'2025-06-12 13:52:23','2025-06-12 13:52:23',NULL,4,4),(8,3,'individual','Isatou Ceesay',NULL,NULL,NULL,NULL,NULL,'female',NULL,28,'Gambian','national_id','GN456789','Westfield Junction, Serrekunda','+2207888999','isatou.ceesay@example.com','Teacher',NULL,NULL,'\"[]\"',NULL,'severe',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-10 00:00:00','Westfield Junction, Serrekunda','Victim was assaulted while waiting for transport. Sustained head injuries requiring hospitalization.',0,NULL,NULL,NULL,'2025-06-12 13:52:23','2025-06-12 13:52:23',NULL,5,5),(9,4,'organization','James Johnson','Fajara Electronics Store','business','BRN12345','James Johnson','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Fajara, Bakau','+2207111222','info@fajaraelectronics.com',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-04 00:00:00','Fajara, Bakau','Store was broken into overnight. Multiple electronic items stolen including laptops and mobile phones.',0,NULL,NULL,NULL,'2025-06-12 13:52:23','2025-06-12 13:52:23',NULL,3,3),(10,5,'organization','Fatou Njie','Serrekunda Market Association','organization','ORG78901','Fatou Njie','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Serrekunda Market, Serrekunda','+2207333444','info@serrekundamarket.org',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-11 00:00:00','Serrekunda Market, Serrekunda','Multiple market stalls vandalized overnight. Property damage and theft reported.',0,NULL,NULL,NULL,'2025-06-12 13:52:23','2025-06-12 13:52:23',NULL,4,4),(11,1,'individual','Alieu Jallow',NULL,NULL,NULL,NULL,NULL,'male',NULL,35,'Gambian','national_id','GN123456','Pipeline, Serrekunda','+2207654321','alieu.jallow@example.com','Shopkeeper',NULL,NULL,'\"[]\"',NULL,'minor',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-06 00:00:00','Kairaba Avenue, Serrekunda','Victim was threatened with a firearm during the robbery of his shop.',0,NULL,NULL,NULL,'2025-06-12 13:53:34','2025-06-12 13:53:34',NULL,3,3),(12,2,'individual','Omar Sowe',NULL,NULL,NULL,NULL,NULL,'male',NULL,42,'Gambian','drivers_license','DL789012','Kololi, Kanifing Municipality','+2207123456','omar.sowe@example.com','Business Owner',NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-09 00:00:00','Senegambia Beach Hotel, Kololi','Vehicle stolen from hotel parking lot while victim was at a meeting.',0,NULL,NULL,NULL,'2025-06-12 13:53:34','2025-06-12 13:53:34',NULL,4,4),(13,3,'individual','Isatou Ceesay',NULL,NULL,NULL,NULL,NULL,'female',NULL,28,'Gambian','national_id','GN456789','Westfield Junction, Serrekunda','+2207888999','isatou.ceesay@example.com','Teacher',NULL,NULL,'\"[]\"',NULL,'severe',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-10 00:00:00','Westfield Junction, Serrekunda','Victim was assaulted while waiting for transport. Sustained head injuries requiring hospitalization.',0,NULL,NULL,NULL,'2025-06-12 13:53:34','2025-06-12 13:53:34',NULL,5,5),(14,4,'organization','James Johnson','Fajara Electronics Store','business','BRN12345','James Johnson','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Fajara, Bakau','+2207111222','info@fajaraelectronics.com',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-04 00:00:00','Fajara, Bakau','Store was broken into overnight. Multiple electronic items stolen including laptops and mobile phones.',0,NULL,NULL,NULL,'2025-06-12 13:53:34','2025-06-12 13:53:34',NULL,3,3),(15,5,'organization','Fatou Njie','Serrekunda Market Association','organization','ORG78901','Fatou Njie','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Serrekunda Market, Serrekunda','+2207333444','info@serrekundamarket.org',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-11 00:00:00','Serrekunda Market, Serrekunda','Multiple market stalls vandalized overnight. Property damage and theft reported.',0,NULL,NULL,NULL,'2025-06-12 13:53:34','2025-06-12 13:53:34',NULL,4,4),(16,1,'individual','Alieu Jallow',NULL,NULL,NULL,NULL,NULL,'male',NULL,35,'Gambian','national_id','GN123456','Pipeline, Serrekunda','+2207654321','alieu.jallow@example.com','Shopkeeper',NULL,NULL,'\"[]\"',NULL,'minor',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-06 00:00:00','Kairaba Avenue, Serrekunda','Victim was threatened with a firearm during the robbery of his shop.',0,NULL,NULL,NULL,'2025-06-12 13:58:33','2025-06-12 13:58:33',NULL,3,3),(17,2,'individual','Omar Sowe',NULL,NULL,NULL,NULL,NULL,'male',NULL,42,'Gambian','drivers_license','DL789012','Kololi, Kanifing Municipality','+2207123456','omar.sowe@example.com','Business Owner',NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-09 00:00:00','Senegambia Beach Hotel, Kololi','Vehicle stolen from hotel parking lot while victim was at a meeting.',0,NULL,NULL,NULL,'2025-06-12 13:58:33','2025-06-12 13:58:33',NULL,4,4),(18,3,'individual','Isatou Ceesay',NULL,NULL,NULL,NULL,NULL,'female',NULL,28,'Gambian','national_id','GN456789','Westfield Junction, Serrekunda','+2207888999','isatou.ceesay@example.com','Teacher',NULL,NULL,'\"[]\"',NULL,'severe',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-10 00:00:00','Westfield Junction, Serrekunda','Victim was assaulted while waiting for transport. Sustained head injuries requiring hospitalization.',0,NULL,NULL,NULL,'2025-06-12 13:58:33','2025-06-12 13:58:33',NULL,5,5),(19,4,'organization','James Johnson','Fajara Electronics Store','business','BRN12345','James Johnson','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Fajara, Bakau','+2207111222','info@fajaraelectronics.com',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-04 00:00:00','Fajara, Bakau','Store was broken into overnight. Multiple electronic items stolen including laptops and mobile phones.',0,NULL,NULL,NULL,'2025-06-12 13:58:33','2025-06-12 13:58:33',NULL,3,3),(20,5,'organization','Fatou Njie','Serrekunda Market Association','organization','ORG78901','Fatou Njie','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Serrekunda Market, Serrekunda','+2207333444','info@serrekundamarket.org',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-11 00:00:00','Serrekunda Market, Serrekunda','Multiple market stalls vandalized overnight. Property damage and theft reported.',0,NULL,NULL,NULL,'2025-06-12 13:58:33','2025-06-12 13:58:33',NULL,4,4),(21,1,'individual','Alieu Jallow',NULL,NULL,NULL,NULL,NULL,'male',NULL,35,'Gambian','national_id','GN123456','Pipeline, Serrekunda','+2207654321','alieu.jallow@example.com','Shopkeeper',NULL,NULL,'\"[]\"',NULL,'minor',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-06 00:00:00','Kairaba Avenue, Serrekunda','Victim was threatened with a firearm during the robbery of his shop.',0,NULL,NULL,NULL,'2025-06-12 13:59:47','2025-06-12 13:59:47',NULL,3,3),(22,2,'individual','Omar Sowe',NULL,NULL,NULL,NULL,NULL,'male',NULL,42,'Gambian','drivers_license','DL789012','Kololi, Kanifing Municipality','+2207123456','omar.sowe@example.com','Business Owner',NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-09 00:00:00','Senegambia Beach Hotel, Kololi','Vehicle stolen from hotel parking lot while victim was at a meeting.',0,NULL,NULL,NULL,'2025-06-12 13:59:47','2025-06-12 13:59:47',NULL,4,4),(23,3,'individual','Isatou Ceesay',NULL,NULL,NULL,NULL,NULL,'female',NULL,28,'Gambian','national_id','GN456789','Westfield Junction, Serrekunda','+2207888999','isatou.ceesay@example.com','Teacher',NULL,NULL,'\"[]\"',NULL,'severe',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-10 00:00:00','Westfield Junction, Serrekunda','Victim was assaulted while waiting for transport. Sustained head injuries requiring hospitalization.',0,NULL,NULL,NULL,'2025-06-12 13:59:47','2025-06-12 13:59:47',NULL,5,5),(24,4,'organization','James Johnson','Fajara Electronics Store','business','BRN12345','James Johnson','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Fajara, Bakau','+2207111222','info@fajaraelectronics.com',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-04 00:00:00','Fajara, Bakau','Store was broken into overnight. Multiple electronic items stolen including laptops and mobile phones.',0,NULL,NULL,NULL,'2025-06-12 13:59:47','2025-06-12 13:59:47',NULL,3,3),(25,5,'organization','Fatou Njie','Serrekunda Market Association','organization','ORG78901','Fatou Njie','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Serrekunda Market, Serrekunda','+2207333444','info@serrekundamarket.org',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-11 00:00:00','Serrekunda Market, Serrekunda','Multiple market stalls vandalized overnight. Property damage and theft reported.',0,NULL,NULL,NULL,'2025-06-12 13:59:47','2025-06-12 13:59:47',NULL,4,4),(26,1,'individual','Alieu Jallow',NULL,NULL,NULL,NULL,NULL,'male',NULL,35,'Gambian','national_id','GN123456','Pipeline, Serrekunda','+2207654321','alieu.jallow@example.com','Shopkeeper',NULL,NULL,'\"[]\"',NULL,'minor',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-06 00:00:00','Kairaba Avenue, Serrekunda','Victim was threatened with a firearm during the robbery of his shop.',0,NULL,NULL,NULL,'2025-06-12 14:02:29','2025-06-12 14:02:29',NULL,3,3),(27,2,'individual','Omar Sowe',NULL,NULL,NULL,NULL,NULL,'male',NULL,42,'Gambian','drivers_license','DL789012','Kololi, Kanifing Municipality','+2207123456','omar.sowe@example.com','Business Owner',NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-09 00:00:00','Senegambia Beach Hotel, Kololi','Vehicle stolen from hotel parking lot while victim was at a meeting.',0,NULL,NULL,NULL,'2025-06-12 14:02:29','2025-06-12 14:02:29',NULL,4,4),(28,3,'individual','Isatou Ceesay',NULL,NULL,NULL,NULL,NULL,'female',NULL,28,'Gambian','national_id','GN456789','Westfield Junction, Serrekunda','+2207888999','isatou.ceesay@example.com','Teacher',NULL,NULL,'\"[]\"',NULL,'severe',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-10 00:00:00','Westfield Junction, Serrekunda','Victim was assaulted while waiting for transport. Sustained head injuries requiring hospitalization.',0,NULL,NULL,NULL,'2025-06-12 14:02:29','2025-06-12 14:02:29',NULL,5,5),(29,4,'organization','James Johnson','Fajara Electronics Store','business','BRN12345','James Johnson','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Fajara, Bakau','+2207111222','info@fajaraelectronics.com',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-04 00:00:00','Fajara, Bakau','Store was broken into overnight. Multiple electronic items stolen including laptops and mobile phones.',0,NULL,NULL,NULL,'2025-06-12 14:02:29','2025-06-12 14:02:29',NULL,3,3),(30,5,'organization','Fatou Njie','Serrekunda Market Association','organization','ORG78901','Fatou Njie','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Serrekunda Market, Serrekunda','+2207333444','info@serrekundamarket.org',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-11 00:00:00','Serrekunda Market, Serrekunda','Multiple market stalls vandalized overnight. Property damage and theft reported.',0,NULL,NULL,NULL,'2025-06-12 14:02:29','2025-06-12 14:02:29',NULL,4,4),(31,1,'individual','Alieu Jallow',NULL,NULL,NULL,NULL,NULL,'male',NULL,35,'Gambian','national_id','GN123456','Pipeline, Serrekunda','+2207654321','alieu.jallow@example.com','Shopkeeper',NULL,NULL,'\"[]\"',NULL,'minor',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-06 00:00:00','Kairaba Avenue, Serrekunda','Victim was threatened with a firearm during the robbery of his shop.',0,NULL,NULL,NULL,'2025-06-12 14:05:00','2025-06-12 14:05:00',NULL,3,3),(32,2,'individual','Omar Sowe',NULL,NULL,NULL,NULL,NULL,'male',NULL,42,'Gambian','drivers_license','DL789012','Kololi, Kanifing Municipality','+2207123456','omar.sowe@example.com','Business Owner',NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-09 00:00:00','Senegambia Beach Hotel, Kololi','Vehicle stolen from hotel parking lot while victim was at a meeting.',0,NULL,NULL,NULL,'2025-06-12 14:05:00','2025-06-12 14:05:00',NULL,4,4),(33,3,'individual','Isatou Ceesay',NULL,NULL,NULL,NULL,NULL,'female',NULL,28,'Gambian','national_id','GN456789','Westfield Junction, Serrekunda','+2207888999','isatou.ceesay@example.com','Teacher',NULL,NULL,'\"[]\"',NULL,'severe',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-10 00:00:00','Westfield Junction, Serrekunda','Victim was assaulted while waiting for transport. Sustained head injuries requiring hospitalization.',0,NULL,NULL,NULL,'2025-06-12 14:05:00','2025-06-12 14:05:00',NULL,5,5),(34,4,'organization','James Johnson','Fajara Electronics Store','business','BRN12345','James Johnson','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Fajara, Bakau','+2207111222','info@fajaraelectronics.com',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-04 00:00:00','Fajara, Bakau','Store was broken into overnight. Multiple electronic items stolen including laptops and mobile phones.',0,NULL,NULL,NULL,'2025-06-12 14:05:00','2025-06-12 14:05:00',NULL,3,3),(35,5,'organization','Fatou Njie','Serrekunda Market Association','organization','ORG78901','Fatou Njie','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Serrekunda Market, Serrekunda','+2207333444','info@serrekundamarket.org',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-11 00:00:00','Serrekunda Market, Serrekunda','Multiple market stalls vandalized overnight. Property damage and theft reported.',0,NULL,NULL,NULL,'2025-06-12 14:05:00','2025-06-12 14:05:00',NULL,4,4),(36,1,'individual','Alieu Jallow',NULL,NULL,NULL,NULL,NULL,'male',NULL,35,'Gambian','national_id','GN123456','Pipeline, Serrekunda','+2207654321','alieu.jallow@example.com','Shopkeeper',NULL,NULL,'\"[]\"',NULL,'minor',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-06 00:00:00','Kairaba Avenue, Serrekunda','Victim was threatened with a firearm during the robbery of his shop.',0,NULL,NULL,NULL,'2025-06-12 14:05:49','2025-06-12 14:05:49',NULL,3,3),(37,2,'individual','Omar Sowe',NULL,NULL,NULL,NULL,NULL,'male',NULL,42,'Gambian','drivers_license','DL789012','Kololi, Kanifing Municipality','+2207123456','omar.sowe@example.com','Business Owner',NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-09 00:00:00','Senegambia Beach Hotel, Kololi','Vehicle stolen from hotel parking lot while victim was at a meeting.',0,NULL,NULL,NULL,'2025-06-12 14:05:49','2025-06-12 14:05:49',NULL,4,4),(38,3,'individual','Isatou Ceesay',NULL,NULL,NULL,NULL,NULL,'female',NULL,28,'Gambian','national_id','GN456789','Westfield Junction, Serrekunda','+2207888999','isatou.ceesay@example.com','Teacher',NULL,NULL,'\"[]\"',NULL,'severe',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-10 00:00:00','Westfield Junction, Serrekunda','Victim was assaulted while waiting for transport. Sustained head injuries requiring hospitalization.',0,NULL,NULL,NULL,'2025-06-12 14:05:49','2025-06-12 14:05:49',NULL,5,5),(39,4,'organization','James Johnson','Fajara Electronics Store','business','BRN12345','James Johnson','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Fajara, Bakau','+2207111222','info@fajaraelectronics.com',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-04 00:00:00','Fajara, Bakau','Store was broken into overnight. Multiple electronic items stolen including laptops and mobile phones.',0,NULL,NULL,NULL,'2025-06-12 14:05:49','2025-06-12 14:05:49',NULL,3,3),(40,5,'organization','Fatou Njie','Serrekunda Market Association','organization','ORG78901','Fatou Njie','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Serrekunda Market, Serrekunda','+2207333444','info@serrekundamarket.org',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-11 00:00:00','Serrekunda Market, Serrekunda','Multiple market stalls vandalized overnight. Property damage and theft reported.',0,NULL,NULL,NULL,'2025-06-12 14:05:49','2025-06-12 14:05:49',NULL,4,4),(41,1,'individual','Alieu Jallow',NULL,NULL,NULL,NULL,NULL,'male',NULL,35,'Gambian','national_id','GN123456','Pipeline, Serrekunda','+2207654321','alieu.jallow@example.com','Shopkeeper',NULL,NULL,'\"[]\"',NULL,'minor',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-06 00:00:00','Kairaba Avenue, Serrekunda','Victim was threatened with a firearm during the robbery of his shop.',0,NULL,NULL,NULL,'2025-06-12 14:09:51','2025-06-12 14:09:51',NULL,3,3),(42,2,'individual','Omar Sowe',NULL,NULL,NULL,NULL,NULL,'male',NULL,42,'Gambian','drivers_license','DL789012','Kololi, Kanifing Municipality','+2207123456','omar.sowe@example.com','Business Owner',NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-09 00:00:00','Senegambia Beach Hotel, Kololi','Vehicle stolen from hotel parking lot while victim was at a meeting.',0,NULL,NULL,NULL,'2025-06-12 14:09:51','2025-06-12 14:09:51',NULL,4,4),(43,3,'individual','Isatou Ceesay',NULL,NULL,NULL,NULL,NULL,'female',NULL,28,'Gambian','national_id','GN456789','Westfield Junction, Serrekunda','+2207888999','isatou.ceesay@example.com','Teacher',NULL,NULL,'\"[]\"',NULL,'severe',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-10 00:00:00','Westfield Junction, Serrekunda','Victim was assaulted while waiting for transport. Sustained head injuries requiring hospitalization.',0,NULL,NULL,NULL,'2025-06-12 14:09:51','2025-06-12 14:09:51',NULL,5,5),(44,4,'organization','James Johnson','Fajara Electronics Store','business','BRN12345','James Johnson','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Fajara, Bakau','+2207111222','info@fajaraelectronics.com',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-04 00:00:00','Fajara, Bakau','Store was broken into overnight. Multiple electronic items stolen including laptops and mobile phones.',0,NULL,NULL,NULL,'2025-06-12 14:09:51','2025-06-12 14:09:51',NULL,3,3),(45,5,'organization','Fatou Njie','Serrekunda Market Association','organization','ORG78901','Fatou Njie','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Serrekunda Market, Serrekunda','+2207333444','info@serrekundamarket.org',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-11 00:00:00','Serrekunda Market, Serrekunda','Multiple market stalls vandalized overnight. Property damage and theft reported.',0,NULL,NULL,NULL,'2025-06-12 14:09:51','2025-06-12 14:09:51',NULL,4,4),(46,1,'individual','Alieu Jallow',NULL,NULL,NULL,NULL,NULL,'male',NULL,35,'Gambian','national_id','GN123456','Pipeline, Serrekunda','+2207654321','alieu.jallow@example.com','Shopkeeper',NULL,NULL,'\"[]\"',NULL,'minor',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-06 00:00:00','Kairaba Avenue, Serrekunda','Victim was threatened with a firearm during the robbery of his shop.',0,NULL,NULL,NULL,'2025-06-12 14:10:23','2025-06-12 14:10:23',NULL,3,3),(47,2,'individual','Omar Sowe',NULL,NULL,NULL,NULL,NULL,'male',NULL,42,'Gambian','drivers_license','DL789012','Kololi, Kanifing Municipality','+2207123456','omar.sowe@example.com','Business Owner',NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-09 00:00:00','Senegambia Beach Hotel, Kololi','Vehicle stolen from hotel parking lot while victim was at a meeting.',0,NULL,NULL,NULL,'2025-06-12 14:10:23','2025-06-12 14:10:23',NULL,4,4),(48,3,'individual','Isatou Ceesay',NULL,NULL,NULL,NULL,NULL,'female',NULL,28,'Gambian','national_id','GN456789','Westfield Junction, Serrekunda','+2207888999','isatou.ceesay@example.com','Teacher',NULL,NULL,'\"[]\"',NULL,'severe',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-10 00:00:00','Westfield Junction, Serrekunda','Victim was assaulted while waiting for transport. Sustained head injuries requiring hospitalization.',0,NULL,NULL,NULL,'2025-06-12 14:10:23','2025-06-12 14:10:23',NULL,5,5),(49,4,'organization','James Johnson','Fajara Electronics Store','business','BRN12345','James Johnson','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Fajara, Bakau','+2207111222','info@fajaraelectronics.com',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-04 00:00:00','Fajara, Bakau','Store was broken into overnight. Multiple electronic items stolen including laptops and mobile phones.',0,NULL,NULL,NULL,'2025-06-12 14:10:23','2025-06-12 14:10:23',NULL,3,3),(50,5,'organization','Fatou Njie','Serrekunda Market Association','organization','ORG78901','Fatou Njie','Representative',NULL,NULL,NULL,NULL,NULL,NULL,'Serrekunda Market, Serrekunda','+2207333444','info@serrekundamarket.org',NULL,NULL,NULL,'\"[]\"',NULL,'none',NULL,NULL,NULL,0,NULL,0,NULL,'2025-06-11 00:00:00','Serrekunda Market, Serrekunda','Multiple market stalls vandalized overnight. Property damage and theft reported.',0,NULL,NULL,NULL,'2025-06-12 14:10:23','2025-06-12 14:10:23',NULL,4,4);

--
-- Table structure for table `cases`
--

CREATE TABLE `cases` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `case_number` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `station_id` bigint(20) unsigned NOT NULL,
  `reporting_officer_id` bigint(20) unsigned NOT NULL,
  `lead_investigator_id` bigint(20) unsigned DEFAULT NULL,
  `incident_date` date NOT NULL,
  `incident_time` time DEFAULT NULL,
  `incident_location` varchar(255) NOT NULL,
  `incident_coordinates` varchar(255) DEFAULT NULL,
  `crime_type` varchar(255) NOT NULL,
  `crime_category` varchar(255) NOT NULL,
  `status` enum('open','under_investigation','pending_review','closed','reopened','archived') NOT NULL,
  `priority` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `reported_date` date NOT NULL,
  `action_taken` text DEFAULT NULL,
  `recommendations` text DEFAULT NULL,
  `closed_date` date DEFAULT NULL,
  `closed_by` varchar(255) DEFAULT NULL,
  `closing_remarks` text DEFAULT NULL,
  `is_sensitive` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_by_user_id` bigint(20) unsigned DEFAULT NULL,
  `updated_by_user_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cases_case_number_unique` (`case_number`),
  KEY `cases_station_id_foreign` (`station_id`),
  KEY `cases_reporting_officer_id_foreign` (`reporting_officer_id`),
  KEY `cases_lead_investigator_id_foreign` (`lead_investigator_id`),
  KEY `cases_created_by_user_id_foreign` (`created_by_user_id`),
  KEY `cases_updated_by_user_id_foreign` (`updated_by_user_id`),
  CONSTRAINT `cases_created_by_user_id_foreign` FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `cases_lead_investigator_id_foreign` FOREIGN KEY (`lead_investigator_id`) REFERENCES `users` (`id`),
  CONSTRAINT `cases_reporting_officer_id_foreign` FOREIGN KEY (`reporting_officer_id`) REFERENCES `users` (`id`),
  CONSTRAINT `cases_station_id_foreign` FOREIGN KEY (`station_id`) REFERENCES `police_stations` (`id`),
  CONSTRAINT `cases_updated_by_user_id_foreign` FOREIGN KEY (`updated_by_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `cases`
--

INSERT INTO `cases` VALUES (1,'GPCMS-2025-0001','Armed Robbery at Kairaba Avenue','Armed robbery incident at a supermarket on Kairaba Avenue. Two masked men with firearms stole cash and valuables.',1,3,3,'2025-06-06',NULL,'Kairaba Avenue, Serrekunda',NULL,'robbery','violent','open','high','2025-06-07','Initial report filed. Investigation pending.',NULL,NULL,NULL,NULL,0,'2025-06-12 13:22:47','2025-06-12 13:22:47',NULL,NULL,NULL),(2,'GPCMS-2025-0002','Vehicle Theft at Senegambia','Toyota Corolla stolen from the parking lot of Senegambia Beach Hotel. Vehicle registration number: BJL 1234.',2,4,4,'2025-06-09',NULL,'Senegambia Beach Hotel, Kololi',NULL,'theft','property','under_investigation','medium','2025-06-09','Initial report filed. Investigation pending.',NULL,NULL,NULL,NULL,0,'2025-06-12 13:22:47','2025-06-12 13:22:47',NULL,NULL,NULL),(3,'GPCMS-2025-0003','Assault at Westfield Junction','Physical assault incident at Westfield Junction. Victim sustained injuries and was taken to Serrekunda General Hospital.',3,5,5,'2025-06-10',NULL,'Westfield Junction, Serrekunda',NULL,'assault','violent','open','medium','2025-06-10','Initial report filed. Investigation pending.',NULL,NULL,NULL,NULL,0,'2025-06-12 13:22:47','2025-06-12 13:22:47',NULL,NULL,NULL),(4,'GPCMS-2025-0004','Burglary at Fajara Residence','Residential burglary at Fajara. Electronics and jewelry stolen while homeowners were away.',4,6,6,'2025-06-04',NULL,'Fajara, Bakau',NULL,'burglary','property','under_investigation','medium','2025-06-05','Initial report filed. Investigation pending.',NULL,NULL,NULL,NULL,0,'2025-06-12 13:22:47','2025-06-12 13:22:47',NULL,NULL,NULL),(5,'GPCMS-2025-0005','Drug Possession at Banjul Ferry Terminal','Suspect apprehended with suspected cannabis at the Banjul Ferry Terminal during routine search.',5,7,7,'2025-06-11',NULL,'Banjul Ferry Terminal, Banjul',NULL,'drugs','narcotics','pending_review','medium','2025-06-11','Initial report filed. Investigation pending.',NULL,NULL,NULL,NULL,0,'2025-06-12 13:22:47','2025-06-12 13:22:47',NULL,NULL,NULL),(7,'CASE-2025-001','Test Robbery Case','A test case for evidence management',8,9,9,'2025-06-07',NULL,'Banjul Central',NULL,'robbery','violent','open','high','2025-06-08',NULL,NULL,NULL,NULL,NULL,0,'2025-06-12 17:31:34','2025-06-12 17:31:34',NULL,9,9);

--
-- Table structure for table `evidence`
--

CREATE TABLE `evidence` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tag_number` varchar(255) NOT NULL,
  `evidence_type` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'in_custody',
  `condition` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `storage_location` varchar(255) DEFAULT NULL,
  `collection_date` datetime DEFAULT NULL,
  `collection_location` varchar(255) DEFAULT NULL,
  `collection_notes` text DEFAULT NULL,
  `collected_by_officer_id` bigint(20) unsigned DEFAULT NULL,
  `custodian` bigint(20) unsigned DEFAULT NULL,
  `analyzed_by_officer_id` bigint(20) unsigned DEFAULT NULL,
  `analysis_date` datetime DEFAULT NULL,
  `analysis_results` text DEFAULT NULL,
  `photos` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`photos`)),
  `documents` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`documents`)),
  `chain_of_custody` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`chain_of_custody`)),
  `created_by_user_id` bigint(20) unsigned DEFAULT NULL,
  `updated_by_user_id` bigint(20) unsigned DEFAULT NULL,
  `case_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `evidence_tag_number_unique` (`tag_number`),
  KEY `evidence_collected_by_officer_id_foreign` (`collected_by_officer_id`),
  KEY `evidence_custodian_foreign` (`custodian`),
  KEY `evidence_analyzed_by_officer_id_foreign` (`analyzed_by_officer_id`),
  KEY `evidence_created_by_user_id_foreign` (`created_by_user_id`),
  KEY `evidence_updated_by_user_id_foreign` (`updated_by_user_id`),
  KEY `evidence_case_id_foreign` (`case_id`),
  CONSTRAINT `evidence_analyzed_by_officer_id_foreign` FOREIGN KEY (`analyzed_by_officer_id`) REFERENCES `users` (`id`),
  CONSTRAINT `evidence_case_id_foreign` FOREIGN KEY (`case_id`) REFERENCES `cases` (`id`),
  CONSTRAINT `evidence_collected_by_officer_id_foreign` FOREIGN KEY (`collected_by_officer_id`) REFERENCES `users` (`id`),
  CONSTRAINT `evidence_created_by_user_id_foreign` FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `evidence_custodian_foreign` FOREIGN KEY (`custodian`) REFERENCES `users` (`id`),
  CONSTRAINT `evidence_updated_by_user_id_foreign` FOREIGN KEY (`updated_by_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `evidence`
--

INSERT INTO `evidence` VALUES (1,'EV-2025-001','weapon','Kitchen knife with 8-inch blade','in_custody','Good condition with fingerprints preserved','Evidence Room A','Locker 12, Shelf B','2025-06-08 17:31:00','Victim\'s kitchen, 123 Main St, Banjul','Found on kitchen counter with blood residue',9,9,NULL,NULL,NULL,'[[],{\"path\":\"evidence\\/photos\\/XjNwO6WqpFrjFMrNf8Fi2MR4icmbJPJNaDeb3xSb.jpg\",\"name\":\"IMG_7267.JPG\",\"uploaded_at\":\"2025-06-12 17:36:01\",\"uploaded_by\":1},{\"path\":\"evidence\\/photos\\/CkPzvd4oz9f6D2SA9x78MQPd8K5aSiDE6FURFR0m.jpg\",\"name\":\"WhatsApp Image 2025-06-01 at 22.20.18.jpeg\",\"uploaded_at\":\"2025-06-12 17:53:33\",\"uploaded_by\":1}]','[[],{\"path\":\"evidence\\/documents\\/p24q7OxklAFE0l38rzKPdPiV4Pr4LBG2iFlyXGoR.pdf\",\"name\":\"case_report_GPCMS-2025-0005.pdf\",\"uploaded_at\":\"2025-06-12 17:55:41\",\"uploaded_by\":1},{\"path\":\"evidence\\/documents\\/IudWpot12xTpMatOnAp0XTmmpH1gj24inHxXYFmy.pdf\",\"name\":\"V402641432.pdf\",\"uploaded_at\":\"2025-06-12 17:55:57\",\"uploaded_by\":1}]','\"[{\\\"date\\\":\\\"2025-06-08 17:31:34\\\",\\\"from_officer_id\\\":null,\\\"to_officer_id\\\":9,\\\"action\\\":\\\"collected\\\",\\\"notes\\\":\\\"Initial collection from crime scene\\\"}]\"',9,1,7,'2025-06-12 17:31:34','2025-06-12 17:55:57',NULL),(2,'EV-2025-002','physical','Torn shirt with blood stains','in_analysis','Torn fabric with visible blood stains','Forensic Lab','Lab 3, Cabinet 2','2025-06-08 17:31:00','Victim\'s bedroom, 123 Main St, Banjul','Found on bedroom floor',9,9,9,'2025-06-10 17:31:00','Preliminary tests confirm blood type matches victim','[{\"path\":\"evidence\\/photos\\/J2PPtvM93querSZ7wgjvdLcuJBAKddfi2GSloswI.png\",\"name\":\"uni.png\",\"uploaded_at\":\"2025-06-12 17:48:42\",\"uploaded_by\":1}]','\"[]\"','\"[{\\\"date\\\":\\\"2025-06-08 17:31:34\\\",\\\"from_officer_id\\\":null,\\\"to_officer_id\\\":9,\\\"action\\\":\\\"collected\\\",\\\"notes\\\":\\\"Initial collection from crime scene\\\"},{\\\"date\\\":\\\"2025-06-09 17:31:34\\\",\\\"from_officer_id\\\":9,\\\"to_officer_id\\\":9,\\\"action\\\":\\\"transferred\\\",\\\"notes\\\":\\\"Transferred to forensic lab for analysis\\\"}]\"',9,1,7,'2025-06-12 17:31:34','2025-06-12 17:48:42',NULL),(3,'EV-2025-003','digital','Security camera footage','in_custody','Digital file, 2 hours of footage','Digital Evidence Server','Server 1, Case Folder 2025-001','2025-06-09 17:31:34','Security office at 125 Main St, Banjul','Retrieved with owner\'s permission',9,9,NULL,NULL,NULL,'\"[]\"','\"[]\"','\"[{\\\"date\\\":\\\"2025-06-09 17:31:34\\\",\\\"from_officer_id\\\":null,\\\"to_officer_id\\\":9,\\\"action\\\":\\\"collected\\\",\\\"notes\\\":\\\"Initial collection from security office\\\"}]\"',9,9,7,'2025-06-12 17:31:34','2025-06-12 17:31:34',NULL),(4,'EV-2025-004','documentary','Threatening letter uu','in_custody','Paper document with handwritten text','Evidence Room A','Locker 15, Document Box 3','2025-06-10 17:31:00','Victim\'s mailbox, 123 Main St, Banjul','Found in sealed envelope',9,9,NULL,NULL,NULL,'\"[]\"','\"[]\"','\"[{\\\"date\\\":\\\"2025-06-10 17:31:34\\\",\\\"from_officer_id\\\":null,\\\"to_officer_id\\\":9,\\\"action\\\":\\\"collected\\\",\\\"notes\\\":\\\"Initial collection from victim\'s mailbox\\\"}]\"',9,1,7,'2025-06-12 17:31:34','2025-06-12 17:32:43',NULL),(5,'EV-2025-005','biological','Hair samples','in_analysis','Multiple strands in sealed container','Forensic Lab','Lab 2, Refrigerator 1','2025-06-08 17:31:34','Crime scene bathroom, 123 Main St, Banjul','Found on bathroom floor',9,9,9,'2025-06-11 17:31:34','DNA extraction in progress','\"[]\"','\"[]\"','\"[{\\\"date\\\":\\\"2025-06-08 17:31:34\\\",\\\"from_officer_id\\\":null,\\\"to_officer_id\\\":9,\\\"action\\\":\\\"collected\\\",\\\"notes\\\":\\\"Initial collection from crime scene\\\"},{\\\"date\\\":\\\"2025-06-10 17:31:34\\\",\\\"from_officer_id\\\":9,\\\"to_officer_id\\\":9,\\\"action\\\":\\\"transferred\\\",\\\"notes\\\":\\\"Transferred to forensic lab for analysis\\\"}]\"',9,9,7,'2025-06-12 17:31:34','2025-06-12 17:31:34',NULL);

--
-- Table structure for table `evidence_transfers`
--

CREATE TABLE `evidence_transfers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `evidence_id` bigint(20) unsigned NOT NULL,
  `from_person` varchar(255) NOT NULL,
  `to_person` varchar(255) NOT NULL,
  `transfer_date` date NOT NULL,
  `transfer_time` time NOT NULL,
  `reason` enum('analysis','storage','court','return','disposal','other') NOT NULL DEFAULT 'other',
  `notes` text DEFAULT NULL,
  `recorded_by_user_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `evidence_transfers_evidence_id_foreign` (`evidence_id`),
  KEY `evidence_transfers_recorded_by_user_id_foreign` (`recorded_by_user_id`),
  CONSTRAINT `evidence_transfers_evidence_id_foreign` FOREIGN KEY (`evidence_id`) REFERENCES `case_evidences` (`id`) ON DELETE CASCADE,
  CONSTRAINT `evidence_transfers_recorded_by_user_id_foreign` FOREIGN KEY (`recorded_by_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `evidence_transfers`
--

INSERT INTO `evidence_transfers` VALUES (1,1,'Lamin Bojang (Badge #GPS-1001)','Fatou Ceesay (Badge #GPS-1002)','2025-06-13','09:30:00','analysis','Transferred to forensic department for analysis and processing.',3,'2025-06-12 14:10:23','2025-06-12 14:10:23'),(2,1,'Fatou Ceesay (Badge #GPS-1002)','Modou Jallow (Badge #GPS-1003)','2025-06-16','14:15:00','storage','Analysis complete. Returned to evidence storage.',4,'2025-06-12 14:10:23','2025-06-12 14:10:23'),(3,1,'Modou Jallow (Badge #GPS-1003)','Fatou Jallow (Badge #GPF10004)','2025-06-23','08:45:00','court','Transferred to court for case hearing.',5,'2025-06-12 14:10:23','2025-06-12 14:10:23'),(4,2,'Fatou Ceesay (Badge #GPS-1002)','Modou Jallow (Badge #GPS-1003)','2025-06-13','09:30:00','analysis','Transferred to forensic department for analysis and processing.',4,'2025-06-12 14:10:23','2025-06-12 14:10:23'),(5,2,'Modou Jallow (Badge #GPS-1003)','Fatou Jallow (Badge #GPF10004)','2025-06-16','14:15:00','storage','Analysis complete. Returned to evidence storage.',5,'2025-06-12 14:10:23','2025-06-12 14:10:23'),(6,3,'Modou Jallow (Badge #GPS-1003)','Fatou Jallow (Badge #GPF10004)','2025-06-13','09:30:00','analysis','Transferred to forensic department for analysis and processing.',5,'2025-06-12 14:10:23','2025-06-12 14:10:23');

--
-- 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`
--


--
-- 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`
--


--
-- 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` tinyint(3) 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`
--


--
-- 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=24 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,'2025_06_11_230610_create_police_stations_table',1),(5,'2025_06_11_230903_add_officer_fields_to_users_table',1),(6,'2025_06_11_233931_create_cases_table',1),(7,'2025_06_11_233942_create_case_statements_table',1),(8,'2025_06_11_233953_create_case_evidence_table',1),(9,'2025_06_11_234005_create_case_suspects_table',1),(10,'2025_06_11_234013_create_case_victims_table',1),(11,'2025_06_11_234020_create_case_updates_table',1),(12,'2025_06_12_011800_update_case_victims_table',1),(13,'2025_06_12_011900_update_case_evidence_table',1),(14,'2025_06_12_012000_create_evidence_transfers_table',1),(15,'2025_06_12_152618_add_user_tracking_to_cases_table',2),(16,'2025_06_12_165529_create_evidence_table',3),(17,'2025_06_12_175950_create_bail_applications_table',4),(18,'2025_06_12_180056_create_bail_conditions_table',4),(19,'2025_06_13_020235_create_suspect_status_updates_table',5),(20,'2025_06_13_100829_create_bail_checkins_table',6),(21,'2025_06_13_100907_add_reporting_schedule_to_bail_conditions',6),(22,'2023_06_13_create_settings_table',7),(23,'2025_06_13_212121_create_permission_tables',8);

--
-- Table structure for table `model_has_permissions`
--

CREATE TABLE `model_has_permissions` (
  `permission_id` bigint(20) unsigned NOT NULL,
  `model_type` varchar(255) NOT NULL,
  `model_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`permission_id`,`model_id`,`model_type`),
  KEY `model_has_permissions_model_id_model_type_index` (`model_id`,`model_type`),
  CONSTRAINT `model_has_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `model_has_permissions`
--


--
-- Table structure for table `model_has_roles`
--

CREATE TABLE `model_has_roles` (
  `role_id` bigint(20) unsigned NOT NULL,
  `model_type` varchar(255) NOT NULL,
  `model_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`role_id`,`model_id`,`model_type`),
  KEY `model_has_roles_model_id_model_type_index` (`model_id`,`model_type`),
  CONSTRAINT `model_has_roles_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `model_has_roles`
--


--
-- Table structure for table `password_reset_tokens`
--

CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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


--
-- Table structure for table `permissions`
--

CREATE TABLE `permissions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `guard_name` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permissions_name_guard_name_unique` (`name`,`guard_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `permissions`
--


--
-- Table structure for table `police_stations`
--

CREATE TABLE `police_stations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `region` varchar(255) NOT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `is_headquarters` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `commander_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `police_stations_code_unique` (`code`),
  KEY `police_stations_commander_id_foreign` (`commander_id`),
  CONSTRAINT `police_stations_commander_id_foreign` FOREIGN KEY (`commander_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `police_stations`
--

INSERT INTO `police_stations` VALUES (1,'Banjul Police Headquarters','GPS-HQ','Independence Drive, Banjul','Banjul','Greater Banjul Area','+2207200001','banjul.hq@police.gov.gm','Main headquarters of the Gambia Police Force',1,1,6,'2025-06-12 13:19:45','2025-06-13 20:20:54',NULL),(2,'Serrekunda Police Station','GPS-SRK','Serrekunda Market Road, Serrekunda','Serrekunda','Kanifing Municipality','+2207200002','serrekunda@police.gov.gm','Police station serving the Serrekunda area',0,1,5,'2025-06-12 13:19:45','2025-06-13 14:38:08',NULL),(3,'Bakau Police Station','GPS-BAK','Atlantic Road, Bakau','Bakau','Kanifing Municipality','+2207200003','bakau@police.gov.gm','Police station serving the Bakau area',0,1,7,'2025-06-12 13:19:45','2025-06-13 20:21:03',NULL),(4,'Brikama Police Station','GPS-BRK','Main Road, Brikama','Brikama','West Coast Region','+2207200004','brikama@police.gov.gm','Police station serving the Brikama area',0,1,3,'2025-06-12 13:19:45','2025-06-13 20:21:12',NULL),(5,'Basse Police Station','GPS-BSS','Basse Santa Su, URR','Basse','Upper River Region','+2207200005','basse@police.gov.gm','Police station serving the Basse area',0,1,NULL,'2025-06-12 13:19:45','2025-06-12 13:19:45',NULL),(6,'Farafenni Police Station','GPS-FRF','Trans-Gambia Highway, Farafenni','Farafenni','North Bank Region','+2207200006','farafenni@police.gov.gm','Police station serving the Farafenni area',0,1,NULL,'2025-06-12 13:19:45','2025-06-12 13:19:45',NULL),(7,'Kanifing Police Station','GPS-KAN','Kanifing Estate, Kanifing','Kanifing','Kanifing Municipality','+2207200007','kanifing@police.gov.gm','Police station serving the Kanifing area',0,1,NULL,'2025-06-12 13:19:45','2025-06-12 13:19:45',NULL),(8,'Banjul Police Headquarters','BAN-HQ','123 Police Plaza','Banjul','Greater Banjul','123-456-7890','headquarters@police.gov.gm','Main police headquarters for Banjul',1,1,3,'2025-06-12 17:29:55','2025-06-13 14:37:59',NULL);

--
-- Table structure for table `role_has_permissions`
--

CREATE TABLE `role_has_permissions` (
  `permission_id` bigint(20) unsigned NOT NULL,
  `role_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`permission_id`,`role_id`),
  KEY `role_has_permissions_role_id_foreign` (`role_id`),
  CONSTRAINT `role_has_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `role_has_permissions_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `role_has_permissions`
--


--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `guard_name` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_name_guard_name_unique` (`name`,`guard_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `roles`
--


--
-- 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 ('Fhy77vyLjMZJSmxvCXyU3i7Ss8fPes4p5cHnZ3DI',1,'127.0.0.1','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/137.0.0.0 Safari/537.36','YTo1OntzOjY6Il90b2tlbiI7czo0MDoic2t3ZERVSVY2Q3MySkIxbFlXc0I0ZmdRTWJUM253cUtKaWpXOEdGWiI7czozOiJ1cmwiO2E6MDp7fXM6OToiX3ByZXZpb3VzIjthOjE6e3M6MzoidXJsIjtzOjM3OiJodHRwOi8vMTI3LjAuMC4xOjgwMDAvc2V0dGluZ3MvYmFja3VwIjt9czo2OiJfZmxhc2giO2E6Mjp7czozOiJvbGQiO2E6MDp7fXM6MzoibmV3IjthOjA6e319czo1MDoibG9naW5fd2ViXzU5YmEzNmFkZGMyYjJmOTQwMTU4MGYwMTRjN2Y1OGVhNGUzMDk4OWQiO2k6MTt9',1749852492);

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(255) NOT NULL,
  `value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`value`)),
  `group` varchar(255) NOT NULL DEFAULT 'general',
  `type` varchar(255) NOT NULL DEFAULT 'string',
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `settings_key_unique` (`key`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` VALUES (1,'site_name','\"Police CCMS\"','general','string','Site name displayed in the header and title','2025-06-13 21:10:29','2025-06-13 21:10:29'),(2,'site_description','\"Police Crime Case Management System\"','general','string','Brief description of the site','2025-06-13 21:10:29','2025-06-13 21:10:29'),(3,'contact_email','\"admin@police-ccms.example.com\"','general','string','Primary contact email','2025-06-13 21:10:29','2025-06-13 21:10:29'),(4,'timezone','\"UTC\"','general','string','Default timezone for the application','2025-06-13 21:10:29','2025-06-13 21:10:29'),(5,'date_format','\"Y-m-d\"','general','string','Default date format','2025-06-13 21:10:29','2025-06-13 21:10:29'),(6,'time_format','\"H:i\"','general','string','Default time format','2025-06-13 21:10:29','2025-06-13 21:10:29'),(7,'auto_cleanup_logs','\"0\"','logs','boolean','Enable automatic log cleanup','2025-06-13 21:10:29','2025-06-13 21:10:29'),(8,'log_cleanup_days','\"30\"','logs','integer','Number of days to keep logs','2025-06-13 21:10:29','2025-06-13 21:10:29'),(9,'backup_frequency','\"daily\"','backup','string','How often to run automatic backups','2025-06-13 21:10:29','2025-06-13 21:10:29'),(10,'backup_retention','\"7\"','backup','integer','Number of days to keep backups','2025-06-13 21:10:29','2025-06-13 21:10:29'),(11,'backup_time','\"01:00\"','backup','string','Time to run backups (24-hour format)','2025-06-13 21:10:29','2025-06-13 21:10:29'),(12,'api_enabled','\"1\"','api','boolean','Enable API access','2025-06-13 21:10:29','2025-06-13 21:10:29'),(13,'api_throttle_rate','\"60\"','api','integer','API rate limit per minute','2025-06-13 21:10:29','2025-06-13 21:10:29'),(14,'api_token_expiry_days','\"30\"','api','integer','Number of days before API tokens expire','2025-06-13 21:10:29','2025-06-13 21:10:29'),(15,'api_admin_access','\"1\"','api_access','boolean',NULL,'2025-06-13 21:10:29','2025-06-13 21:14:02'),(16,'api_officer_access','\"1\"','api_access','boolean',NULL,'2025-06-13 21:10:29','2025-06-13 21:14:02'),(17,'api_investigator_access','\"1\"','api_access','boolean',NULL,'2025-06-13 21:10:29','2025-06-13 21:14:02'),(18,'api_clerk_access','\"1\"','api_access','boolean',NULL,'2025-06-13 21:10:29','2025-06-13 21:14:02');

--
-- Table structure for table `suspect_status_updates`
--

CREATE TABLE `suspect_status_updates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `suspect_id` bigint(20) unsigned NOT NULL,
  `officer_id` bigint(20) unsigned DEFAULT NULL,
  `update_type` enum('arrest','charge','custody','other') NOT NULL,
  `new_status` tinyint(1) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `update_date` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `suspect_status_updates_suspect_id_foreign` (`suspect_id`),
  KEY `suspect_status_updates_officer_id_foreign` (`officer_id`),
  CONSTRAINT `suspect_status_updates_officer_id_foreign` FOREIGN KEY (`officer_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `suspect_status_updates_suspect_id_foreign` FOREIGN KEY (`suspect_id`) REFERENCES `case_suspects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `suspect_status_updates`
--

INSERT INTO `suspect_status_updates` VALUES (1,29,1,'arrest',0,'Arrest status updated',NULL,'2025-06-13 02:11:48',NULL,'2025-06-13 02:11:48','2025-06-13 02:11:48');

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `is_officer` tinyint(1) NOT NULL DEFAULT 0,
  `rank` varchar(255) DEFAULT NULL,
  `badge_number` varchar(255) DEFAULT NULL,
  `station_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_station_id_foreign` (`station_id`),
  CONSTRAINT `users_station_id_foreign` FOREIGN KEY (`station_id`) REFERENCES `police_stations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `users` VALUES (1,'super admin','superadmin@headpolice.gm',NULL,'$2y$12$NBdWxMIcVhshahU9du7MseMDTSPbSf0a.Sf7LAQIA/S6DpNWUBKeS',NULL,0,NULL,NULL,NULL,'2025-06-12 09:30:42','2025-06-12 09:30:42'),(2,'Admin User','admin@police.gov.gm',NULL,'$2y$12$334EYG3m5yQ6ezZpw3aVjuMd6xPXQTS4r1Yi.xnp/8FZf9aqw2gD2',NULL,0,NULL,NULL,NULL,'2025-06-12 13:16:56','2025-06-12 13:16:56'),(3,'Lamin Bojang','lbojang@police.gov.gm',NULL,'$2y$12$RDJ.g8oPMxQblMVGxN0IGuYLdhrGYg677jn7IfEdMnPg9SEEy5nye',NULL,1,'Inspector','GPS-1001',1,'2025-06-12 13:16:58','2025-06-13 19:41:23'),(4,'Fatou Ceesay','fceesay@police.gov.gm',NULL,'$2y$12$prZ59o3EHMSnlEHbMw8CZOWDxLfB/bf2O7Lh2SnZ831jOFYBz/ZlW',NULL,1,'Sergeant','GPS-1002',4,'2025-06-12 13:16:58','2025-06-13 19:41:31'),(5,'Modou Jallow','mjallow@police.gov.gm',NULL,'$2y$12$WdCmp4Ladj.VpCP2Bw.IqeFi7tbYEbc5CvsKjmIXe1G1iZcuCwQDa',NULL,1,'Constable','GPS-1003',4,'2025-06-12 13:16:58','2025-06-13 19:41:36'),(6,'Fatou Jallow','fatou.jallow@police.gov.gm',NULL,'$2y$12$H7SQWWRjKbqUY0ZLlWzbR.BqJRIfMkzB6/TuHuPcKFwttxkOuA8GW',NULL,1,'Inspector','GPF10004',5,'2025-06-12 13:16:59','2025-06-13 19:41:42'),(7,'Modou Sowe','modou.sowe@police.gov.gm',NULL,'$2y$12$3FXWqGvvARygiU4IG71/1.jceuYBJlFgVrkcJ2JY7NKWVVDx7yRTC',NULL,1,'Superintendent','GPF10005',7,'2025-06-12 13:16:59','2025-06-13 19:41:47'),(9,'Test Officer','officer@example.com',NULL,'$2y$12$rVlOvXvYSjobs1Xajp.bcePzm0JLXtrpSabhmA5gVYu/01OIn7Q8G',NULL,1,'Inspector','B12345',8,'2025-06-12 17:28:48','2025-06-13 19:41:52');


