-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 16, 2025 at 02:53 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
(6, 26, 4, 5, 'Completed', '2025-10-12', '2025-10-12 04:00:13'),
(7, 29, 4, 4, 'Completed', '2025-10-12', '2025-10-12 06:40:50'),
(8, 31, 8, 5, 'Pending', '2025-10-13', '2025-10-13 02:32:16'),
(9, 31, 4, 5, 'Completed', '2025-10-13', '2025-10-13 02:35:31');

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

--
-- 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
(13, 3, 'may sunog po', '10.3156993', '123.8854', 'Responding', '2025-10-15 14:59:05');

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

--
-- 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` enum('KEEPER','KAGAWADS','CAPTAIN','CHAIRPERSON','SECRETARY','TREASURER') 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
(18, 2, 'CAPTAIN', 'Active', '2025-10-12 03:50:36'),
(19, 3, 'KAGAWADS', 'Active', '2025-10-12 03:50:47'),
(20, 4, 'KAGAWADS', 'Active', '2025-10-12 03:50:53'),
(21, 5, 'KAGAWADS', 'Active', '2025-10-12 03:50:58'),
(22, 6, 'KAGAWADS', 'Active', '2025-10-12 03:51:04'),
(23, 7, 'KAGAWADS', 'Active', '2025-10-12 03:51:10'),
(24, 8, 'KAGAWADS', 'Active', '2025-10-12 03:51:15'),
(25, 9, 'KAGAWADS', 'Active', '2025-10-12 03:51:20'),
(26, 10, 'CHAIRPERSON', 'Active', '2025-10-12 03:51:29'),
(27, 14, 'SECRETARY', 'Active', '2025-10-12 03:51:42'),
(28, 11, 'TREASURER', 'Active', '2025-10-12 03:56:46'),
(29, 12, 'KEEPER', 'Active', '2025-10-12 03:56:58');

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

--
-- Table structure for table `tbl_reports`
--

CREATE TABLE `tbl_reports` (
  `report_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `UniqueID` int(11) NOT NULL,
  `notes` varchar(250) NOT NULL,
  `date_created` date NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tbl_reports`
--

INSERT INTO `tbl_reports` (`report_id`, `user_id`, `UniqueID`, `notes`, `date_created`) VALUES
(11, 4, 31, 'ok na sila', '2025-10-14');

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

--
-- 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, 'JOHN CRIS', 'N/A', 'N/A', 'JAYCEE@GMAIL.COM', 'BARORO', 'PRESIDENT QUIRINOAS', '09072348948', 21, '2004-05-24', 'Male', '1', '2025-09-30 11:55:19'),
(3, 'johns', 'villaruel', 'doe', 'johnloyde@gmail.com', '123456', 'isulan', '9310027582', 24, '2025-10-22', 'Male', '1', '2025-10-01 12:39:27'),
(9, 'JOHN KENNETH', 'BARORO', 'ALBAN', 'KENNETH@GMAIL.COM', '123456', 'PUROK MABUHAY', '09072348930', 16, '2008-10-29', 'Male', '1', '2025-10-12 03:29:06'),
(10, 'GRACE', 'ORTIZ', 'TALASTAS', 'GRACE@GMAIL.COM', '123456', 'PUROK, ROSAS', '09127938158', 16, '2006-10-29', 'Female', '0', '2025-10-12 06:16:25'),
(11, 'GENEVEIVE', 'L.', 'ANTONIO', 'ANTONIO@GMAIL.COM', '123456', 'BRGY MANGILALA', '9310027582', 22, '2002-10-22', 'Female', '0', '2025-10-12 06:34:56'),
(12, 'GRACE', 'NADON', 'APROSTA', 'APROSTAGRACE@GMAIL.COM', '123456', 'PUROK, ROSAS', '09674150614', 18, '2008-10-22', 'Female', '1', '2025-10-13 02:19:52');

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

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

CREATE TABLE `tbl_transaction` (
  `UniqueID` int(11) NOT NULL,
  `resident_id` int(11) DEFAULT NULL,
  `respondent` varchar(100) 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(),
  `supported_evidence` varchar(250) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `tbl_transaction` (`UniqueID`, `resident_id`, `respondent`, `incident_address`, `complaint_type_id`, `description`, `status`, `incident_date`, `date_created`, `supported_evidence`) VALUES
(26, 9, 'John Cris Baroro', 'rosas', 1, 'MAINGAY MASYADO', 'Declined', '2025-10-12', '2025-10-12 03:30:44', '1760239844_lllll.jpg'),
(27, 10, 'antonio', 'mabuhay', 3, 'NAG SADO PULBO', 'Accept', '2025-10-12', '2025-10-12 06:19:53', '1760249993_553732536_780882238264711_7120011362506483835_n.jpg'),
(28, 10, 'john', 'rosas', 8, 'AASWS', 'Accept', '2025-10-12', '2025-10-12 06:35:43', '1760250943_553732536_780882238264711_7120011362506483835_n.jpg'),
(29, 11, 'JC', 'rosas', 3, 'TTATTATTSASAS', 'Declined', '2025-10-12', '2025-10-12 06:38:21', '1760251101_553732536_780882238264711_7120011362506483835_n.jpg'),
(30, 3, 'arnold banate', 'ampatuan', 24, 'HE BULLY ME ON MESSENGER', 'Accept', '2025-10-03', '2025-10-12 14:47:10', '1760280430_background.jpg'),
(31, 12, 'jenny talastas', 'mabuhay', 4, 'LAW AY DAW KO', 'Settled', '2025-10-13', '2025-10-13 02:23:59', '1760322239_553732536_780882238264711_7120011362506483835_n.jpg');

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

--
-- Table structure for table `tbl_transaction_schedule`
--

CREATE TABLE `tbl_transaction_schedule` (
  `schedule_id` int(11) NOT NULL,
  `UniqueID` int(11) NOT NULL,
  `settle_date` date NOT NULL,
  `status` enum('Pending','Closed') NOT NULL DEFAULT 'Pending',
  `date_created` int(11) NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tbl_transaction_schedule`
--

INSERT INTO `tbl_transaction_schedule` (`schedule_id`, `UniqueID`, `settle_date`, `status`, `date_created`) VALUES
(5, 31, '2025-10-23', 'Pending', 20251016);

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

--
-- 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, 'JOHN CRIS', 'N/A', 'BARORO', 'ADMIN', 'admin', 'ROSAS', '09072348948', 21, 'Male', '2025-09-30', 'admin', NULL, '2025-09-30 04:39:02'),
(2, 'ELMER', 'S.', 'JANOLINO', 'CAPITAN', '123456', 'PUROK ANAHAW', '09356102822', 55, 'Male', '2025-09-17', 'official', '1', '2025-09-30 13:49:27'),
(3, 'ROCELL', 'O.', 'COMSOMO', 'KAGAWAD COMSOMO', '123456', 'PAG ASA', '9752842498', 52, 'Male', '2025-09-17', 'official', NULL, '2025-09-30 13:57:21'),
(4, 'ROMIL', 'G.', 'TOMBOC', 'KAGAWAD_ROMEL', '123456', 'MABUHAY', '09171234567', 48, 'Male', '1973-03-15', 'official', '3d8a1ed7720cf27cf22f0a1571b9c94149809c04eb93a15af1d8c0b19bb4463d', '2025-10-01 04:28:16'),
(5, 'LEAN MAE ', 'A.', 'ARADANAS', 'KAGAWAD_MAYMAY', '123456', 'ROSAS', '09182345678', 38, 'Female', '1980-07-22', 'official', NULL, '2025-10-01 04:28:16'),
(6, 'AILYN', 'P.', 'ESTANTE', 'KAGAWAD_ESTANTE', '123456', 'PRK. PAGKAKAISA', '09193456789', 51, 'Female', '1977-02-10', 'official', NULL, '2025-10-01 04:28:16'),
(7, 'ERWIN', 'P.', 'ORO', 'KAGAWAD_ERWIN', '123456', 'PRK. PAG ASA', '09194567890', 50, 'Male', '1975-11-05', 'official', NULL, '2025-10-01 04:28:16'),
(8, 'ARTURO', 'B.', 'RAMOS', 'KAGAWAD_BENG', '123456', 'PUROK 5, BARANGAY MABUHAY', '09195678901', 43, 'Male', '1982-06-18', 'official', NULL, '2025-10-01 04:28:16'),
(9, 'RENATO', 'L.', 'ROMBAOA', 'KAGAWAD_RENE', '123456', ' MABUHAY', '09196789012', 55, 'Male', '1984-01-09', 'official', NULL, '2025-10-01 04:28:16'),
(10, 'CHRIST ROMNEY', 'G.', 'PENIERO', 'SK CHAIRMAN', '123456', 'ANAHAW', '09197890123', 46, 'Male', '1979-09-27', 'official', NULL, '2025-10-01 04:28:16'),
(11, 'ROLLEY', 'D.', 'BOAC', 'API', '123456', 'PAGKAKAISA', '09198901234', 32, 'Male', '1986-12-13', 'official', NULL, '2025-10-01 04:28:16'),
(12, 'ANNALIZA', 'J.', 'APROSTA', 'RECORD KEEPER', '123456', 'ANAHAW', '09199012345', 58, 'Male', '1981-08-30', 'official', NULL, '2025-10-01 04:28:16'),
(13, 'Andrea', 'S.', 'Lopez', 'sk_andrea', '123456', 'Purok 3, Barangay Mabuhay', '09190123456', 23, 'Female', '2002-05-14', 'official', NULL, '2025-10-01 04:28:16'),
(14, 'ANICETA ', 'L.', 'BAYBAYAN', 'SECRETARY_BABET', '123456', 'address', '09127938158', 48, 'Male', '2025-10-12', 'official', NULL, '2025-10-12 03:46:20');

--
-- 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_reports`
--
ALTER TABLE `tbl_reports`
  ADD PRIMARY KEY (`report_id`),
  ADD KEY `fk_user_ibfk5` (`user_id`),
  ADD KEY `UniqueID` (`UniqueID`);

--
-- 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_transaction_schedule`
--
ALTER TABLE `tbl_transaction_schedule`
  ADD PRIMARY KEY (`schedule_id`),
  ADD KEY `UniqueID` (`UniqueID`);

--
-- 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=10;

--
-- 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=14;

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

--
-- AUTO_INCREMENT for table `tbl_reports`
--
ALTER TABLE `tbl_reports`
  MODIFY `report_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

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

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

--
-- AUTO_INCREMENT for table `tbl_transaction_schedule`
--
ALTER TABLE `tbl_transaction_schedule`
  MODIFY `schedule_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

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

--
-- 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_reports`
--
ALTER TABLE `tbl_reports`
  ADD CONSTRAINT `fk_user_ibfk5` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`),
  ADD CONSTRAINT `tbl_reports_ibfk_1` FOREIGN KEY (`UniqueID`) REFERENCES `tbl_transaction` (`UniqueID`);

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

--
-- Constraints for table `tbl_transaction_schedule`
--
ALTER TABLE `tbl_transaction_schedule`
  ADD CONSTRAINT `tbl_transaction_schedule_ibfk_1` FOREIGN KEY (`UniqueID`) REFERENCES `tbl_transaction` (`UniqueID`);
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 */;
