-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 05, 2025 at 07:51 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: `e_complaint_db`
--

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

--
-- Table structure for table `tbl_assignment`
--

CREATE TABLE `tbl_assignment` (
  `assignment_id` int(11) NOT NULL,
  `UniqueID` int(11) NOT NULL,
  `assigned_person` int(11) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `status` enum('Pending','Ongoing','Completed','') DEFAULT 'Pending',
  `deadline` date DEFAULT NULL,
  `date_created` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tbl_assignment`
--

INSERT INTO `tbl_assignment` (`assignment_id`, `UniqueID`, `assigned_person`, `department_id`, `status`, `deadline`, `date_created`) VALUES
(3, 16, 5, 2, 'Ongoing', '2025-10-14', '2025-10-02 15:14:09');

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

--
-- Table structure for table `tbl_complaint_types`
--

CREATE TABLE `tbl_complaint_types` (
  `complaint_type_id` int(11) NOT NULL,
  `complain_name` varchar(250) NOT NULL,
  `date_create` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tbl_complaint_types`
--

INSERT INTO `tbl_complaint_types` (`complaint_type_id`, `complain_name`, `date_create`) VALUES
(1, 'Noise Disturbance', '2025-10-01 10:37:57'),
(2, 'Domestic Violence', '2025-10-01 10:37:57'),
(3, 'Trespassing', '2025-10-01 10:37:57'),
(4, 'Public Scandal', '2025-10-01 10:37:57'),
(5, 'Vandalism or Property Damage', '2025-10-01 10:37:57'),
(6, 'Harassment or Threats', '2025-10-01 10:37:57'),
(7, 'Physical Assault', '2025-10-01 10:37:57'),
(8, 'Illegal Parking', '2025-10-01 10:37:57'),
(9, 'Animal Disturbance (Stray or Noisy Animals)', '2025-10-01 10:37:57'),
(10, 'Garbage Disposal / Improper Waste Management', '2025-10-01 10:37:57'),
(11, 'Illegal Construction or Building Encroachment', '2025-10-01 10:37:57'),
(12, 'Business Without Permit', '2025-10-01 10:37:57'),
(13, 'Violation of Curfew', '2025-10-01 10:37:57'),
(14, 'Loud Karaoke or Party Disturbance', '2025-10-01 10:37:57'),
(15, 'Water or Electricity Theft', '2025-10-01 10:37:57'),
(16, 'Neighborhood Disputes', '2025-10-01 10:37:57'),
(17, 'Boundary or Property Disputes', '2025-10-01 10:37:57'),
(18, 'Minor Theft or Petty Crime', '2025-10-01 10:37:57'),
(19, 'Public Intoxication or Drunken Behavior', '2025-10-01 10:37:57'),
(20, 'Child Neglect or Abuse', '2025-10-01 10:37:57'),
(21, 'Burning of Waste / Air Pollution', '2025-10-01 10:37:57'),
(22, 'Blocking Public Road or Pathway', '2025-10-01 10:37:57'),
(23, 'Illegal Gambling', '2025-10-01 10:37:57'),
(24, 'Cyberbullying / Online Harassment', '2025-10-01 10:37:57'),
(25, 'Community Health or Sanitation Complaints', '2025-10-01 10:37:57');

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

--
-- Table structure for table `tbl_department`
--

CREATE TABLE `tbl_department` (
  `department_id` int(11) NOT NULL,
  `department_name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `date_created` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tbl_department`
--

INSERT INTO `tbl_department` (`department_id`, `department_name`, `description`, `date_created`) VALUES
(2, 'DPWH', 'N/A', '2025-09-30 14:54:51'),
(3, 'Barangay Captain\'s Office', 'Handles governance issues, leadership disputes, and policy-related concerns.', '2025-10-01 04:25:58'),
(4, 'Barangay Council (Sangguniang Barangay)', 'Resolves legislative and ordinance-related complaints.', '2025-10-01 04:25:58'),
(5, 'Peace and Order Committee', 'Handles disturbances, fights, noise complaints, and community safety.', '2025-10-01 04:25:58'),
(6, 'Barangay Justice / Lupong Tagapamayapa', 'Mediates disputes between residents and facilitates settlements.', '2025-10-01 04:25:58'),
(7, 'Health and Sanitation Department', 'Addresses health, sanitation, waste disposal, and cleanliness concerns.', '2025-10-01 04:25:58'),
(8, 'Environmental and Agriculture Office', 'Deals with pollution, illegal dumping, and environmental concerns.', '2025-10-01 04:25:58'),
(9, 'Youth and Sports Council (SK)', 'Handles youth-related issues and activities.', '2025-10-01 04:25:58'),
(10, 'Barangay Disaster Risk Reduction and Management (BDRRMC)', 'Manages disaster preparedness and emergency response.', '2025-10-01 04:25:58'),
(11, 'Social Welfare and Community Development', 'Addresses welfare program complaints and vulnerable sector concerns.', '2025-10-01 04:25:58'),
(12, 'Infrastructure and Public Works Office', 'Handles road damage, drainage, and infrastructure complaints.', '2025-10-01 04:25:58'),
(13, 'Business and Permits Department', 'Manages business permit violations and complaints about establishments.', '2025-10-01 04:25:58'),
(14, 'Public Safety and Security Office', 'Handles barangay tanod issues and community security complaints.', '2025-10-01 04:25:58');

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

--
-- Table structure for table `tbl_emergency`
--

CREATE TABLE `tbl_emergency` (
  `emergency_id` int(11) NOT NULL,
  `resident_id` int(11) DEFAULT NULL,
  `description` varchar(250) NOT NULL,
  `lattitude` varchar(250) NOT NULL,
  `longitude` varchar(250) NOT NULL,
  `emergency_status` enum('Pending','Responding','Dispatch','Resolved','Cancelled') NOT NULL,
  `date_created` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tbl_emergency`
--

INSERT INTO `tbl_emergency` (`emergency_id`, `resident_id`, `description`, `lattitude`, `longitude`, `emergency_status`, `date_created`) VALUES
(1, 3, '', '6.7783623', '124.4353452', 'Pending', '2025-10-05 03:00:06'),
(2, 3, 'asdasd', '7.0736134', '125.6110244', 'Pending', '2025-10-05 04:43:10');

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

--
-- Table structure for table `tbl_organizational_structure`
--

CREATE TABLE `tbl_organizational_structure` (
  `organization_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `position` varchar(100) NOT NULL,
  `status` enum('Active','Inactive','','') DEFAULT 'Active',
  `date_created` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tbl_organizational_structure`
--

INSERT INTO `tbl_organizational_structure` (`organization_id`, `user_id`, `position`, `status`, `date_created`) VALUES
(4, 5, 'KAGAWADS', 'Active', '2025-10-01 05:02:31'),
(5, 6, 'KAGAWADS', 'Active', '2025-10-01 05:02:51'),
(6, 7, 'KAGAWADS', 'Active', '2025-10-01 05:02:56'),
(7, 8, 'KAGAWADS', 'Active', '2025-10-01 05:03:03'),
(8, 9, 'KAGAWADS', 'Active', '2025-10-01 05:03:09'),
(9, 10, 'KAGAWADS', 'Active', '2025-10-01 05:03:16'),
(10, 11, 'SECRETARY', 'Active', '2025-10-01 05:03:43'),
(11, 12, 'TREASURER', 'Active', '2025-10-01 05:03:53'),
(12, 13, 'CHAIRPERSON', 'Active', '2025-10-01 05:04:07'),
(13, 4, 'CAPTAIN', 'Active', '2025-10-01 05:45:04');

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

--
-- Table structure for table `tbl_resident`
--

CREATE TABLE `tbl_resident` (
  `resident_id` int(11) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `middle_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(250) NOT NULL,
  `address` varchar(250) NOT NULL,
  `contact` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT NULL,
  `status` varchar(50) DEFAULT 'active',
  `date_created` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tbl_resident`
--

INSERT INTO `tbl_resident` (`resident_id`, `first_name`, `middle_name`, `last_name`, `email`, `password`, `address`, `contact`, `age`, `birthday`, `gender`, `status`, `date_created`) VALUES
(2, 'ARWIN ', 'MORALES', 'MORALES', 'RAGANAS', 'ASDASD', '', '9752842498', 24, '1999-11-15', 'Male', '1', '2025-09-30 11:55:19'),
(3, 'johns', 'villaruel', 'doe', 'johnloyde@gmail.com', '123456', 'isulan', '09356102822', 24, '2025-10-22', 'Male', 'Single', '2025-10-01 12:39:27');

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

--
-- Table structure for table `tbl_transaction`
--

CREATE TABLE `tbl_transaction` (
  `UniqueID` int(11) NOT NULL,
  `resident_id` int(11) DEFAULT NULL,
  `incident_address` varchar(255) NOT NULL,
  `complaint_type_id` int(11) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('Accept','Pending','Declined','Settled','Unsettled') DEFAULT 'Pending',
  `incident_date` date DEFAULT NULL,
  `date_created` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tbl_transaction`
--

INSERT INTO `tbl_transaction` (`UniqueID`, `resident_id`, `incident_address`, `complaint_type_id`, `description`, `status`, `incident_date`, `date_created`) VALUES
(16, 3, 'isulans', 2, 'VIDEOKE 24/7', 'Accept', '2025-10-01', '2025-10-02 06:08:23'),
(18, 3, 'ISULAN NATION HIGH SCHOOL', 23, 'N/A', 'Accept', '2025-10-08', '2025-10-02 15:38:13');

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

--
-- Table structure for table `tbl_user`
--

CREATE TABLE `tbl_user` (
  `user_id` int(11) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `middle_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(250) NOT NULL,
  `address` text DEFAULT NULL,
  `contact` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `role_type` enum('admin','official','staff') NOT NULL,
  `remember_token` varchar(255) DEFAULT NULL,
  `date_created` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tbl_user`
--

INSERT INTO `tbl_user` (`user_id`, `first_name`, `middle_name`, `last_name`, `username`, `password`, `address`, `contact`, `age`, `gender`, `birthday`, `role_type`, `remember_token`, `date_created`) VALUES
(1, 'christ', 'M.', 'john', 'admin', 'admin', 'n/a', '9752842498', 25, 'Male', '2025-09-30', 'admin', 'c6923bb3d15b4100b4f72db4981c8d41b054ebc671f327b79c251e16a509df6b', '2025-09-30 04:39:02'),
(2, 'ESMAEL', 'GAMBOA', 'DAUD', 'CAPITAN', '123456', 'ADDRESS', '09356102822', 25, 'Male', '2025-09-17', 'admin', '1', '2025-09-30 13:49:27'),
(3, 'ARWIN EMMANUEL', 'MORALES', 'RAGANAS', 'ADMIN2', '123456', 'ADDRESS', '9752842498', 24, 'Male', '2025-09-17', 'admin', '1', '2025-09-30 13:57:21'),
(4, 'Juan', 'D.', 'Santos', 'captain_juan', '482c811da5d5b4bc6d497ffa98491e38', 'Purok 1, Barangay Mabuhay', '09171234567', 52, 'Male', '1973-03-15', 'official', NULL, '2025-10-01 04:28:16'),
(5, 'Maria', 'L.', 'Reyes', 'kagawad_maria', '482c811da5d5b4bc6d497ffa98491e38', 'Purok 2, Barangay Mabuhay', '09182345678', 45, 'Female', '1980-07-22', 'official', NULL, '2025-10-01 04:28:16'),
(6, 'Pedro', 'M.', 'Cruz', 'kagawad_pedro', '482c811da5d5b4bc6d497ffa98491e38', 'Purok 3, Barangay Mabuhay', '09193456789', 48, 'Male', '1977-02-10', 'official', NULL, '2025-10-01 04:28:16'),
(7, 'Luisa', 'T.', 'Garcia', 'kagawad_luisa', '482c811da5d5b4bc6d497ffa98491e38', 'Purok 4, Barangay Mabuhay', '09194567890', 50, 'Female', '1975-11-05', 'official', NULL, '2025-10-01 04:28:16'),
(8, 'Jose', 'B.', 'Mendoza', 'kagawad_jose', '482c811da5d5b4bc6d497ffa98491e38', 'Purok 5, Barangay Mabuhay', '09195678901', 43, 'Male', '1982-06-18', 'official', NULL, '2025-10-01 04:28:16'),
(9, 'Cynthia', 'G.', 'Villanueva', 'kagawad_cynthia', '482c811da5d5b4bc6d497ffa98491e38', 'Purok 6, Barangay Mabuhay', '09196789012', 41, 'Female', '1984-01-09', 'official', NULL, '2025-10-01 04:28:16'),
(10, 'Roberto', 'H.', 'Domingo', 'kagawad_roberto', '482c811da5d5b4bc6d497ffa98491e38', 'Purok 7, Barangay Mabuhay', '09197890123', 46, 'Male', '1979-09-27', 'official', NULL, '2025-10-01 04:28:16'),
(11, 'Angela', 'R.', 'Delos Santos', 'secretary_angela', '482c811da5d5b4bc6d497ffa98491e38', 'Purok 1, Barangay Mabuhay', '09198901234', 39, 'Female', '1986-12-13', 'official', NULL, '2025-10-01 04:28:16'),
(12, 'Mark', 'P.', 'Fernandez', 'treasurer_mark', '482c811da5d5b4bc6d497ffa98491e38', 'Purok 2, Barangay Mabuhay', '09199012345', 44, 'Male', '1981-08-30', 'official', NULL, '2025-10-01 04:28:16'),
(13, 'Andrea', 'S.', 'Lopez', 'sk_andrea', '482c811da5d5b4bc6d497ffa98491e38', 'Purok 3, Barangay Mabuhay', '09190123456', 23, 'Female', '2002-05-14', 'official', NULL, '2025-10-01 04:28:16');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_assignment`
--
ALTER TABLE `tbl_assignment`
  ADD PRIMARY KEY (`assignment_id`),
  ADD KEY `fk_assignment_department` (`department_id`),
  ADD KEY `idx_assignment_user` (`assigned_person`),
  ADD KEY `fk_transaction_assignment` (`UniqueID`);

--
-- Indexes for table `tbl_complaint_types`
--
ALTER TABLE `tbl_complaint_types`
  ADD PRIMARY KEY (`complaint_type_id`);

--
-- Indexes for table `tbl_department`
--
ALTER TABLE `tbl_department`
  ADD PRIMARY KEY (`department_id`);

--
-- Indexes for table `tbl_emergency`
--
ALTER TABLE `tbl_emergency`
  ADD PRIMARY KEY (`emergency_id`),
  ADD KEY `idx_emergency_resident` (`resident_id`);

--
-- Indexes for table `tbl_organizational_structure`
--
ALTER TABLE `tbl_organizational_structure`
  ADD PRIMARY KEY (`organization_id`),
  ADD KEY `fk_org_user` (`user_id`);

--
-- Indexes for table `tbl_resident`
--
ALTER TABLE `tbl_resident`
  ADD PRIMARY KEY (`resident_id`);

--
-- Indexes for table `tbl_transaction`
--
ALTER TABLE `tbl_transaction`
  ADD PRIMARY KEY (`UniqueID`),
  ADD KEY `idx_transaction_resident` (`resident_id`),
  ADD KEY `fk_transaction_type_transaction` (`complaint_type_id`);

--
-- Indexes for table `tbl_user`
--
ALTER TABLE `tbl_user`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `username` (`username`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_assignment`
--
ALTER TABLE `tbl_assignment`
  MODIFY `assignment_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `tbl_complaint_types`
--
ALTER TABLE `tbl_complaint_types`
  MODIFY `complaint_type_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26;

--
-- AUTO_INCREMENT for table `tbl_department`
--
ALTER TABLE `tbl_department`
  MODIFY `department_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

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

--
-- AUTO_INCREMENT for table `tbl_organizational_structure`
--
ALTER TABLE `tbl_organizational_structure`
  MODIFY `organization_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;

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

--
-- AUTO_INCREMENT for table `tbl_transaction`
--
ALTER TABLE `tbl_transaction`
  MODIFY `UniqueID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `tbl_assignment`
--
ALTER TABLE `tbl_assignment`
  ADD CONSTRAINT `fk_assignment_department` FOREIGN KEY (`department_id`) REFERENCES `tbl_department` (`department_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_assignment_user` FOREIGN KEY (`assigned_person`) REFERENCES `tbl_user` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_transaction_assignment` FOREIGN KEY (`UniqueID`) REFERENCES `tbl_transaction` (`UniqueID`);

--
-- Constraints for table `tbl_emergency`
--
ALTER TABLE `tbl_emergency`
  ADD CONSTRAINT `fk_emergency_resident` FOREIGN KEY (`resident_id`) REFERENCES `tbl_resident` (`resident_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_organizational_structure`
--
ALTER TABLE `tbl_organizational_structure`
  ADD CONSTRAINT `fk_org_user` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_transaction`
--
ALTER TABLE `tbl_transaction`
  ADD CONSTRAINT `fk_transaction_resident` FOREIGN KEY (`resident_id`) REFERENCES `tbl_resident` (`resident_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_transaction_type_transaction` FOREIGN KEY (`complaint_type_id`) REFERENCES `tbl_complaint_types` (`complaint_type_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 */;
