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

START TRANSACTION;



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

--
-- Table structure for table `emergency_reports`
--

CREATE TABLE `emergency_reports` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `latitude` decimal(10,8) NOT NULL,
  `longitude` decimal(11,8) NOT NULL,
  `priority` enum('low','medium','high','critical') DEFAULT 'medium',
  `status` enum('pending','in_progress','resolved') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `emergency_reports`
--

INSERT INTO `emergency_reports` (`id`, `user_id`, `type`, `description`, `latitude`, `longitude`, `priority`, `status`, `created_at`, `updated_at`) VALUES
(1, 3, 'medical', 'bb', -1.94420000, 30.06650000, 'medium', 'pending', '2025-03-09 00:49:40', '2025-03-09 00:49:40'),
(2, 3, 'medical', 'bbb', -1.92922300, 30.05714200, 'medium', 'pending', '2025-03-09 00:50:22', '2025-03-09 00:50:22'),
(3, 3, 'medical', 'ddd', -1.92922300, 30.05714200, 'medium', 'pending', '2025-03-09 00:50:51', '2025-03-09 00:50:51'),
(4, 3, 'medical', 'nnn', -1.94420000, 30.06650000, 'medium', 'pending', '2025-03-09 00:53:22', '2025-03-09 00:53:22'),
(5, 3, 'medical', 'nnnn', -1.94420000, 30.06650000, 'medium', 'pending', '2025-03-09 00:55:57', '2025-03-09 00:55:57'),
(6, 3, 'medical', 'nnnn', -1.94016828, 30.06220847, 'medium', 'pending', '2025-03-09 00:57:08', '2025-03-09 00:57:08'),
(7, 3, 'medical', 'nnnn', -1.94441445, 30.06761580, 'medium', 'pending', '2025-03-09 00:57:23', '2025-03-09 00:57:23'),
(8, 3, 'medical', 'nnnn', -1.94441445, 30.06761580, 'medium', 'pending', '2025-03-09 00:57:43', '2025-03-09 00:57:43'),
(9, 3, 'medical', 'nnnn', -1.94441445, 30.06761580, 'medium', 'pending', '2025-03-09 00:57:47', '2025-03-09 00:57:47'),
(10, 3, 'medical', 'nnnn', -1.94441445, 30.06761580, 'medium', 'pending', '2025-03-09 00:57:49', '2025-03-09 00:57:49'),
(11, 3, 'medical', 'nnnn', -1.94441445, 30.06761580, 'medium', 'pending', '2025-03-09 00:57:51', '2025-03-09 00:57:51'),
(12, 3, 'medical', 'nnnn', -1.94441445, 30.06761580, 'medium', 'pending', '2025-03-09 00:58:34', '2025-03-09 00:58:34'),
(13, 3, 'security', 'qwertyui', -1.93141044, 30.05825780, 'medium', 'pending', '2025-03-09 01:06:36', '2025-03-09 01:06:36'),
(14, 3, 'security', 'qwertyui', -1.93141044, 30.05825780, 'medium', 'pending', '2025-03-09 01:08:41', '2025-03-09 01:08:41'),
(15, 3, 'security', 'qwertyui', -1.93141044, 30.05825780, 'medium', 'pending', '2025-03-09 01:08:44', '2025-03-09 01:08:44'),
(16, 3, 'accident', 'buy', -1.94304195, 30.07499724, 'high', 'pending', '2025-03-09 01:12:25', '2025-03-09 01:12:25'),
(17, 3, 'Vehicle Breakdown', 'fghjk', -1.29551488, 36.85495079, 'medium', 'pending', '2025-03-09 03:26:34', '2025-03-09 03:26:34');

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

--
-- Table structure for table `fees`
--

CREATE TABLE `fees` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `month_year` date NOT NULL,
  `description` text,
  `due_date` date NOT NULL,
  `status` enum('paid','unpaid') DEFAULT 'unpaid',
  `payment_date` timestamp NULL DEFAULT NULL,
  `payment_method` enum('cash', 'bank_transfer', 'online_payment') NULL,
  `reference_number` varchar(100) NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `fees`
--

INSERT INTO `fees` (`id`, `user_id`, `amount`, `month_year`, `description`, `due_date`, `status`, `payment_date`, `payment_method`, `reference_number`, `notes`, `created_at`) VALUES
(1, 2, 50000.00, '2025-03-10', NULL, '2025-03-10', 'unpaid', NULL, NULL, NULL, NULL, '2025-03-09 12:10:34'),
(2, 2, 50000.00, '2025-03-10', NULL, '2025-03-10', 'unpaid', NULL, NULL, NULL, NULL, '2025-03-09 12:13:23');

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

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

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `type` enum('fee','emergency','trip','system') NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `trips`
--

CREATE TABLE `trips` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `vehicle_plate` varchar(20) NOT NULL,
  `has_passengers` tinyint(1) DEFAULT 0,
  `current_country` varchar(100) NOT NULL,
  `latitude` decimal(10,8) NOT NULL,
  `longitude` decimal(11,8) NOT NULL,
  `start_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `end_date` timestamp NULL DEFAULT NULL,
  `status` enum('active','completed') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `trips`
--

INSERT INTO `trips` (`id`, `user_id`, `vehicle_plate`, `has_passengers`, `current_country`, `latitude`, `longitude`, `start_date`, `end_date`, `status`, `created_at`) VALUES
(1, 3, '4555', 0, 'Rwanda', -1.92922300, 30.05714200, '2025-03-09 02:16:44', '2025-03-09 02:16:44', 'completed', '2025-03-09 02:05:57'),
(2, 3, '12345678', 1, 'Rwanda', -1.94909175, 30.15219155, '2025-03-09 01:09:39', NULL, 'active', '2025-03-09 02:09:39'),
(3, 3, '12345678', 1, 'Rwanda', -1.88415891, 30.06065369, '2025-03-09 01:17:23', NULL, 'active', '2025-03-09 02:17:23'),
(4, 3, '12345678', 1, 'Rwanda', -1.93219744, 29.99988556, '2025-03-09 01:42:32', NULL, 'active', '2025-03-09 02:42:32');

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

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

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

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

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

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

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

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

--
-- Constraints for table `emergency_reports`
--
ALTER TABLE `emergency_reports`
  ADD CONSTRAINT `emergency_reports_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `fees`
--
ALTER TABLE `fees`
  ADD CONSTRAINT `fees_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `trips`
--
ALTER TABLE `trips`
  ADD CONSTRAINT `trips_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
COMMIT;

