-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Nov 05, 2026 at 10:39 AM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

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


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `civitrack db`
--

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

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `category_id` int(11) NOT NULL,
  `category_name` varchar(50) NOT NULL,
  `severity_level` int(11) DEFAULT NULL,
  `description` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`category_id`, `category_name`, `severity_level`, `description`) VALUES
(16, 'Road Damage', 4, 'Potholes and broken roads affecting vehicles'),
(17, 'Water Leakage', 4, 'Water pipe leaks causing flooding or water waste'),
(18, 'Street Light Issue', 2, 'Street lights not working or flickering'),
(19, 'Garbage Collection', 3, 'Waste not collected or overflowing bins'),
(20, 'Sewage Problem', 5, 'Blocked drains or sewage overflow posing health risks'),
(21, 'Traffic Signal Fault', 4, 'Broken or malfunctioning traffic lights'),
(22, 'Public Cleanliness', 1, 'Minor cleanliness issues and littering'),
(23, 'Drainage Problem', 4, 'Water accumulation due to poor drainage systems'),
(24, 'Sidewalk Damage', 3, 'Broken or unsafe pedestrian walkways'),
(25, 'Open Manhole', 5, 'Uncovered manholes that can cause serious injury'),
(26, 'Electrical Hazard', 5, 'Exposed wires or dangerous electrical risks'),
(27, 'Illegal Dumping', 3, 'Unauthorized waste disposal in public areas');

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

--
-- Table structure for table `complaints`
--

CREATE TABLE `complaints` (
  `complaint_id` int(11) NOT NULL,
  `submission_date` datetime NOT NULL,
  `description` varchar(50) NOT NULL,
  `priority_level` varchar(10) NOT NULL,
  `risk_score` float NOT NULL,
  `status` varchar(20) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `updated_at` datetime NOT NULL,
  `user_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `assigned_staff_id` int(11) DEFAULT NULL,
  `area_name` varchar(100) DEFAULT NULL,
  `street_name` varchar(100) DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `complaints`
--

INSERT INTO `complaints` (`complaint_id`, `submission_date`, `description`, `priority_level`, `risk_score`, `status`, `image`, `updated_at`, `user_id`, `category_id`, `location_id`, `assigned_staff_id`, `area_name`, `street_name`, `latitude`, `longitude`) VALUES
(22, '2026-05-18 10:30:55', 'Streetlight not working', 'low', 20, 'resolved', NULL, '2026-05-20 10:30:55', 6, 18, 2, 2, 'Muscat - Muttrah', 'Street 5', NULL, NULL),
(23, '2026-05-17 10:30:55', 'Large pothole on main road', 'high', 70, 'in_progress', NULL, '2026-05-20 10:30:55', 7, 16, 35, 3, 'Al Dhahirah - Ibri', 'Main Road', NULL, NULL),
(25, '2026-05-15 10:30:55', 'Broken sidewalk', 'medium', 40, 'resolved', NULL, '2026-05-20 10:30:55', 6, 24, 23, 4, 'Al Batinah North - Sohar', 'Street 8', NULL, NULL),
(26, '2026-05-14 10:30:55', 'Open manhole dangerous', 'high', 95, 'In_Progress', NULL, '2026-05-20 10:30:55', 7, 25, 3, 2, 'Muscat - Seeb', 'Street 20', NULL, NULL),
(27, '2026-05-18 10:30:55', 'Illegal dumping waste', 'high', 75, 'in_progress', NULL, '2026-05-20 10:30:55', 5, 27, 4, 2, 'Muscat - Bawshar', 'Street 3', NULL, NULL),
(28, '2026-05-13 10:30:55', 'Electrical hazard wires exposed', 'high', 90, 'in_progress', NULL, '2026-05-20 10:30:55', 6, 26, 23, 4, 'Al Batinah North - Sohar', 'Street 10', NULL, NULL),
(29, '2026-05-17 10:30:55', 'Small pothole issue', 'medium', 50, 'pending', NULL, '2026-05-20 10:30:55', 7, 16, 35, 3, 'Al Dhahirah - Ibri', 'Street 6', NULL, NULL),
(30, '2026-05-16 10:30:55', 'Garbage not collected', 'medium', 48, 'resolved', NULL, '2026-05-20 10:30:55', 5, 19, 25, 4, 'Al Batinah North - Liwa', 'Street 1', NULL, NULL),
(35, '2026-06-07 10:48:14', 'traffic signal broken, not working!', 'Low', 16, 'pending', NULL, '0000-00-00 00:00:00', 5, 21, 7, NULL, 'salalah', '-', 16.98908580, 54.10644494),
(37, '2026-06-07 12:40:57', 'there is too much garbage ', 'Low', 3, 'pending', '', '2026-06-07 12:42:34', 5, 22, 35, 3, 'al akhdar', '', 23.16617482, 56.51245080);

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

--
-- Table structure for table `locations`
--

CREATE TABLE `locations` (
  `location_id` int(11) NOT NULL,
  `governorate` varchar(50) NOT NULL,
  `wilayat` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `locations`
--

INSERT INTO `locations` (`location_id`, `governorate`, `wilayat`) VALUES
(1, 'Muscat', 'Muscat'),
(2, 'Muscat', 'Muttrah'),
(3, 'Muscat', 'Seeb'),
(4, 'Muscat', 'Bawshar'),
(5, 'Muscat', 'Al Amarat'),
(6, 'Muscat', 'Qurayyat'),
(7, 'Dhofar', 'Salalah'),
(8, 'Dhofar', 'Taqah'),
(9, 'Dhofar', 'Mirbat'),
(10, 'Dhofar', 'Rakhyut'),
(11, 'Dhofar', 'Thumrait'),
(12, 'Dhofar', 'Dalkut'),
(13, 'Dhofar', 'Sadah'),
(14, 'Dhofar', 'Shalim and the Hallaniyat Islands'),
(15, 'Dhofar', 'Muqshin'),
(16, 'Musandam', 'Khasab'),
(17, 'Musandam', 'Dibba'),
(18, 'Musandam', 'Bukha'),
(19, 'Musandam', 'Madha'),
(20, 'Al Buraimi', 'Al Buraimi'),
(21, 'Al Buraimi', 'Mahdah'),
(22, 'Al Buraimi', 'Al Sinainah'),
(23, 'Al Batinah North', 'Sohar'),
(24, 'Al Batinah North', 'Shinas'),
(25, 'Al Batinah North', 'Liwa'),
(26, 'Al Batinah North', 'Saham'),
(27, 'Al Batinah North', 'Al Khaburah'),
(28, 'Al Batinah North', 'Suwaiq'),
(29, 'Al Batinah South', 'Rustaq'),
(30, 'Al Batinah South', 'Al Awabi'),
(31, 'Al Batinah South', 'Nakhal'),
(32, 'Al Batinah South', 'Wadi Al Maawil'),
(33, 'Al Batinah South', 'Barka'),
(34, 'Al Batinah South', 'Musannah'),
(35, 'Al Dhahirah', 'Ibri'),
(36, 'Al Dhahirah', 'Yanqul'),
(37, 'Al Dhahirah', 'Dhank'),
(38, 'Al Dakhiliyah', 'Nizwa'),
(39, 'Al Dakhiliyah', 'Samail'),
(40, 'Al Dakhiliyah', 'Bahla'),
(41, 'Al Dakhiliyah', 'Adam'),
(42, 'Al Dakhiliyah', 'Al Hamra'),
(43, 'Al Dakhiliyah', 'Manah'),
(44, 'Al Dakhiliyah', 'Izki'),
(45, 'Al Dakhiliyah', 'Bidbid'),
(46, 'Al Sharqiyah North', 'Ibra'),
(47, 'Al Sharqiyah North', 'Al Mudhaibi'),
(48, 'Al Sharqiyah North', 'Bidiyah'),
(49, 'Al Sharqiyah North', 'Al Qabil'),
(50, 'Al Sharqiyah North', 'Wadi Bani Khalid'),
(51, 'Al Sharqiyah North', 'Dima Wa Al Tayeen'),
(52, 'Al Sharqiyah South', 'Sur'),
(53, 'Al Sharqiyah South', 'Al Kamil Wa Al Wafi'),
(54, 'Al Sharqiyah South', 'Jalan Bani Bu Ali'),
(55, 'Al Sharqiyah South', 'Jalan Bani Bu Hassan'),
(56, 'Al Sharqiyah South', 'Masirah'),
(57, 'Al Wusta', 'Haima'),
(58, 'Al Wusta', 'Duqm'),
(59, 'Al Wusta', 'Al Jazir'),
(60, 'Al Wusta', 'Mahout');

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

--
-- Table structure for table `managers`
--

CREATE TABLE `managers` (
  `user_id` int(11) NOT NULL,
  `last_login` datetime NOT NULL,
  `assigned_date` datetime DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `managers`
--

INSERT INTO `managers` (`user_id`, `last_login`, `assigned_date`, `status`) VALUES
(1, '0000-00-00 00:00:00', '2026-05-26 23:30:55', 'active');

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

--
-- Table structure for table `staff`
--

CREATE TABLE `staff` (
  `department` varchar(50) NOT NULL,
  `assigned_area` varchar(100) NOT NULL,
  `workload_count` int(11) DEFAULT NULL,
  `user_id` int(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `staff`
--

INSERT INTO `staff` (`department`, `assigned_area`, `workload_count`, `user_id`) VALUES
('Sanitation', 'Muscat - Seeb', 3, 2),
('Water Supply', 'Al Dhahirah - Ibri', 2, 3),
('Road Maintenance', 'Al Batinah North - Sohar', 3, 4),
('Not Assigned', 'Al Dhahirah - Ibri', 0, 7);

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

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

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `contact_info` varchar(100) NOT NULL,
  `account_status` varchar(20) NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `role` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `users` (`user_id`, `name`, `email`, `password`, `contact_info`, `account_status`, `created_at`, `role`) VALUES
(1, 'Manager', 'manager@test.com', '123', '99999999', 'active', '2026-05-20 10:10:36', 'manager'),
(2, 'Ahmed Al-Harthy', 'ahmed@test.com', '123', '91234567', 'active', '2026-05-20 10:10:36', 'staff'),
(3, 'Fatma Al-Balushi', 'fatma@test.com', '123', '92345678', 'active', '2026-05-20 10:10:36', 'staff'),
(4, 'Said Al-Rawahi', 'said1@test.com', '123', '93456785', 'active', '2026-05-20 10:10:36', 'staff'),
(5, 'Ali Al-Salmi', 'ali@test.com', '123', '94567899', 'active', '2026-05-20 10:10:36', 'user'),
(6, 'Mariam Al-Hinai', 'mariam@test.com', '123', '95678901', 'active', '2026-05-20 10:10:36', 'user'),
(7, 'Khalid Al-Maamari', 'khalid@test.com', '123', '96789012', 'active', '2026-05-20 10:10:36', 'staff'),
(22, 'shahd al muqarshi', 'shahdsalim@gmail.com', '123', '99999999', 'blocked', '2026-11-05 07:11:11', 'user'),
(24, 'Reem Salim Al Muqarshi', 'almureemsalim@gmail.com', '$2y$10$jMSBBRs3I.zH9r0GqxHFs.RZ0qT6L0tXl8eIhw63w3ijaulNOziZ2', '', 'active', '2026-11-05 11:05:55', 'user');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`category_id`),
  ADD KEY `category_id` (`category_id`);

--
-- Indexes for table `complaints`
--
ALTER TABLE `complaints`
  ADD PRIMARY KEY (`complaint_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `category_id` (`category_id`),
  ADD KEY `location_id` (`location_id`),
  ADD KEY `assigned_staff_id` (`assigned_staff_id`);

--
-- Indexes for table `locations`
--
ALTER TABLE `locations`
  ADD PRIMARY KEY (`location_id`),
  ADD KEY `location_id` (`location_id`);

--
-- Indexes for table `managers`
--
ALTER TABLE `managers`
  ADD PRIMARY KEY (`user_id`);

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

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `categories`
--
ALTER TABLE `categories`
  MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

--
-- AUTO_INCREMENT for table `complaints`
--
ALTER TABLE `complaints`
  MODIFY `complaint_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38;

--
-- AUTO_INCREMENT for table `locations`
--
ALTER TABLE `locations`
  MODIFY `location_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=115;

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

--
-- AUTO_INCREMENT for table `staff`
--
ALTER TABLE `staff`
  MODIFY `user_id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `complaints`
--
ALTER TABLE `complaints`
  ADD CONSTRAINT `complaints_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
  ADD CONSTRAINT `complaints_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`),
  ADD CONSTRAINT `complaints_ibfk_3` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`),
  ADD CONSTRAINT `complaints_ibfk_4` FOREIGN KEY (`assigned_staff_id`) REFERENCES `staff` (`user_id`);

--
-- Constraints for table `managers`
--
ALTER TABLE `managers`
  ADD CONSTRAINT `managers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `staff`
--
ALTER TABLE `staff`
  ADD CONSTRAINT `staff_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;