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

START TRANSACTION;



--
-- Database: `payroll_system`
--

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

--
-- Table structure for table `account_approval_log`
--

CREATE TABLE `account_approval_log` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `action` enum('approved','rejected') NOT NULL,
  `approved_by` int(11) NOT NULL,
  `reason` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `account_approval_log`
--

INSERT INTO `account_approval_log` (`id`, `user_id`, `action`, `approved_by`, `reason`, `created_at`) VALUES
(4, 7, 'approved', 1, NULL, '2025-12-09 07:11:32'),
(12, 7, 'approved', 1, NULL, '2025-12-10 02:20:45');

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

--
-- Table structure for table `attendance`
--

CREATE TABLE `attendance` (
  `id` int(11) NOT NULL,
  `employee_id` varchar(50) NOT NULL,
  `attendance_date` date NOT NULL,
  `time_in` time NOT NULL,
  `time_out` time DEFAULT NULL,
  `hours_worked` decimal(5,2) DEFAULT 0.00,
  `late_minutes` int(11) DEFAULT 0,
  `overtime_hours` decimal(5,2) DEFAULT 0.00,
  `overtime_type` enum('regular','rest_day','special_holiday','regular_holiday','rest_special','rest_regular') DEFAULT 'regular',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `attendance`
--

INSERT INTO `attendance` (`id`, `employee_id`, `attendance_date`, `time_in`, `time_out`, `hours_worked`, `late_minutes`, `overtime_hours`, `overtime_type`, `created_at`) VALUES
(1, '0', '2025-10-20', '07:00:00', '16:00:00', 8.00, 0, 0.00, 'regular', '2025-10-20 00:47:58'),
(2, '0', '2025-10-16', '08:00:00', '16:00:00', 7.00, 60, 0.00, 'regular', '2025-10-20 00:49:15'),
(3, '0', '2025-10-21', '07:00:00', '18:00:00', 10.00, 0, 2.00, 'regular', '2025-10-20 00:51:55'),
(4, '0', '2025-10-22', '07:00:00', '17:00:00', 9.00, 0, 1.00, 'regular', '2025-10-22 08:28:44'),
(5, '0', '2025-10-23', '07:01:00', '16:00:00', 7.98, 1, 0.00, 'regular', '2025-10-23 06:34:06'),
(41, '0', '2025-12-02', '07:00:00', '16:00:00', 8.00, 0, 0.00, 'regular', '2025-12-02 00:40:45'),
(48, '1', '2025-12-09', '07:00:00', '17:00:00', 9.00, 0, 1.00, 'regular', '2025-12-09 00:32:26'),
(62, '1', '2025-10-20', '13:15:00', '00:00:00', 0.00, 0, 0.00, 'regular', '2025-12-09 03:26:35'),
(138, '4', '2025-11-18', '10:05:59', '13:41:29', 2.58, 185, 0.00, 'regular', '2025-12-09 04:06:35'),
(139, '4', '2025-10-20', '14:58:23', '16:04:43', 0.10, 478, 0.00, 'regular', '2025-12-09 04:06:35'),
(140, '4', '2025-10-21', '13:58:12', '15:17:14', 0.32, 418, 0.00, 'regular', '2025-12-09 04:06:35'),
(141, '4', '2025-10-22', '08:49:43', '08:50:56', 0.00, 109, 0.00, 'regular', '2025-12-09 04:06:35'),
(142, '4', '2025-10-30', '10:05:33', '00:00:00', 0.00, 0, 0.00, 'regular', '2025-12-09 04:06:35'),
(143, '4', '2025-11-26', '09:14:48', '00:00:00', 0.00, 0, 0.00, 'regular', '2025-12-09 04:06:35'),
(146, '5', '2025-11-05', '08:45:40', '08:49:54', 0.00, 105, 0.00, 'regular', '2025-12-09 05:05:39'),
(147, '5', '2025-11-18', '10:05:40', '13:44:46', 2.65, 185, 0.00, 'regular', '2025-12-09 05:05:39'),
(148, '5', '2025-10-21', '09:03:29', '15:17:26', 5.22, 123, 0.00, 'regular', '2025-12-09 05:05:39'),
(149, '5', '2025-10-22', '07:57:07', '00:00:00', 0.00, 0, 0.00, 'regular', '2025-12-09 05:05:39'),
(150, '5', '2025-10-23', '09:23:26', '11:02:08', 0.63, 143, 0.00, 'regular', '2025-12-09 05:05:39'),
(151, '5', '2025-11-12', '16:32:08', '00:00:00', 0.00, 0, 0.00, 'regular', '2025-12-09 05:05:39'),
(152, '3', '2025-10-20', '15:11:30', '15:44:37', 0.00, 491, 0.00, 'regular', '2025-12-09 05:37:52');

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

--
-- Table structure for table `attendance_logs`
--

CREATE TABLE `attendance_logs` (
  `id` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `user_id` varchar(50) NOT NULL,
  `timestamp` datetime NOT NULL,
  `status` varchar(5) NOT NULL,
  `is_synced` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `biometric_devices`
--

CREATE TABLE `biometric_devices` (
  `id` int(11) NOT NULL,
  `location` varchar(100) NOT NULL,
  `ip_address` varchar(50) NOT NULL,
  `port` int(11) DEFAULT 4370,
  `comm_key` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `last_sync` datetime DEFAULT NULL,
  `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 `biometric_devices`
--

INSERT INTO `biometric_devices` (`id`, `location`, `ip_address`, `port`, `comm_key`, `status`, `last_sync`, `created_at`, `updated_at`) VALUES
(1, 'Secret Office', '192.168.8.140', 4370, 0, 'active', NULL, '2025-10-20 11:52:39', '2025-12-02 00:42:30'),
(7, 'UF100-S', '192.168.1.201', 4370, 0, 'active', NULL, '2025-10-23 02:54:38', '2025-10-23 02:54:38');

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

--
-- Table structure for table `biometric_sync_logs`
--

CREATE TABLE `biometric_sync_logs` (
  `id` int(11) NOT NULL,
  `device_id` int(11) NOT NULL,
  `sync_time` datetime NOT NULL,
  `records_synced` int(11) DEFAULT 0,
  `status` enum('success','failed','partial') DEFAULT 'success',
  `error_message` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `employees`
--

CREATE TABLE `employees` (
  `id` int(11) NOT NULL,
  `employee_id` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `area` varchar(50) NOT NULL,
  `daily_rate` decimal(10,2) NOT NULL,
  `hours_per_week` int(11) NOT NULL DEFAULT 40,
  `position` varchar(100) NOT NULL,
  `contact_number` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `date_hired` date DEFAULT NULL,
  `sss_number` varchar(50) DEFAULT NULL,
  `philhealth_number` varchar(50) DEFAULT NULL,
  `pagibig_number` varchar(50) DEFAULT NULL,
  `tin_number` varchar(50) DEFAULT NULL,
  `emergency_contact_name` varchar(100) DEFAULT NULL,
  `emergency_contact_number` varchar(20) DEFAULT NULL,
  `employment_status` enum('regular','probationary','contractual','part-time') DEFAULT 'probationary',
  `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 `employees`
--

INSERT INTO `employees` (`id`, `employee_id`, `name`, `area`, `daily_rate`, `hours_per_week`, `position`, `contact_number`, `email`, `address`, `date_of_birth`, `date_hired`, `sss_number`, `philhealth_number`, `pagibig_number`, `tin_number`, `emergency_contact_name`, `emergency_contact_number`, `employment_status`, `created_at`, `updated_at`) VALUES
(1, '0', 'Big, Boss B', 'NCR', 5000.00, 40, 'CEO', '', '', '', '0000-00-00', '0000-00-00', '', '', '', '', '', '', 'regular', '2025-10-20 00:20:04', '2025-12-09 01:46:41'),
(2, '4', 'C, Miguel', 'NCR', 695.00, 40, 'Encoder', '09953550505', 'islend@gmail.com', 'Salitran 4, Dasmariñas City, Cavite', '2004-08-01', '2025-09-08', '', '', '', '', '', '', 'regular', '2025-11-18 05:40:00', '2025-12-09 00:19:31'),
(3, '5', 'P, Aj', 'NCR', 695.00, 40, 'Support', '09983550505', 'aelordjosh@gmail.com', '', '0000-00-00', '0000-00-00', '', '', '', '', '', '', 'regular', '2025-11-18 05:40:18', '2025-12-09 00:27:10'),
(8, '1', 'C, JC', 'NCR', 2000.00, 40, 'Team Leader', '', '', '', '0000-00-00', '0000-00-00', '', '', '', '', '', '', 'regular', '2025-11-26 09:33:22', '2025-12-03 07:41:46'),
(10, '3', 'TEMP-3 (UPDATE REQUIRED)', 'NCR', 695.00, 40, 'PENDING - UPDATE REQUIRED', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'probationary', '2025-12-09 05:37:52', '2025-12-09 05:37:52');

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

--
-- Table structure for table `employee_biometric`
--

CREATE TABLE `employee_biometric` (
  `id` int(11) NOT NULL,
  `employee_id` varchar(50) NOT NULL,
  `device_user_id` varchar(50) NOT NULL COMMENT 'User ID in biometric device',
  `fingerprint_enrolled` tinyint(1) DEFAULT 0,
  `face_enrolled` tinyint(1) DEFAULT 0,
  `enrollment_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `import_history`
--

CREATE TABLE `import_history` (
  `id` int(11) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `total_records` int(11) NOT NULL,
  `imported` int(11) NOT NULL,
  `updated` int(11) NOT NULL,
  `skipped` int(11) NOT NULL,
  `import_date` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Tracks biometric file import history';

--
-- Dumping data for table `import_history`
--

INSERT INTO `import_history` (`id`, `filename`, `total_records`, `imported`, `updated`, `skipped`, `import_date`) VALUES
(1, 'KMY2251200205_attlog.dat', 18, 0, 0, 5, '2025-10-22 05:50:16'),
(2, 'KMY2251200205_attlog.dat', 18, 0, 0, 5, '2025-10-22 05:51:16');

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

--
-- Table structure for table `payroll`
--

CREATE TABLE `payroll` (
  `id` int(11) NOT NULL,
  `employee_id` varchar(50) NOT NULL,
  `payroll_month` varchar(20) NOT NULL,
  `days_present` int(11) NOT NULL,
  `absences` int(11) NOT NULL,
  `late_minutes` int(11) NOT NULL,
  `overtime_hours` decimal(5,2) DEFAULT 0.00,
  `overtime_pay` decimal(10,2) DEFAULT 0.00,
  `gross_income` decimal(10,2) NOT NULL,
  `sss_deduction` decimal(10,2) NOT NULL,
  `philhealth_deduction` decimal(10,2) NOT NULL,
  `pagibig_deduction` decimal(10,2) NOT NULL,
  `tax_deduction` decimal(10,2) NOT NULL,
  `late_deduction` decimal(10,2) NOT NULL,
  `absent_deduction` decimal(10,2) NOT NULL,
  `total_deductions` decimal(10,2) NOT NULL,
  `net_pay` decimal(10,2) NOT NULL,
  `sss_employer` decimal(10,2) DEFAULT 0.00,
  `philhealth_employer` decimal(10,2) DEFAULT 0.00,
  `pagibig_employer` decimal(10,2) DEFAULT 0.00,
  `payment_status` enum('pending','paid') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `payroll`
--

INSERT INTO `payroll` (`id`, `employee_id`, `payroll_month`, `days_present`, `absences`, `late_minutes`, `overtime_hours`, `overtime_pay`, `gross_income`, `sss_deduction`, `philhealth_deduction`, `pagibig_deduction`, `tax_deduction`, `late_deduction`, `absent_deduction`, `total_deductions`, `net_pay`, `sss_employer`, `philhealth_employer`, `pagibig_employer`, `payment_status`, `created_at`) VALUES
(13, '0', '2025-08', 0, 22, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 'pending', '2025-11-18 04:45:04'),
(34, '0', '2025-11', 0, 22, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 'pending', '2025-11-27 02:02:37'),
(35, '4', '2025-11', 2, 20, 185, 0.00, 0.00, 1390.00, 180.00, 250.00, 13.90, 0.00, 267.86, 0.00, 711.76, 678.24, 380.00, 250.00, 27.80, 'pending', '2025-11-27 02:02:37'),
(36, '5', '2025-11', 4, 18, 290, 1.00, 108.59, 2888.59, 180.00, 250.00, 57.77, 0.00, 419.90, 0.00, 907.67, 1980.92, 380.00, 250.00, 57.77, 'pending', '2025-11-27 02:02:37'),
(37, '1', '2025-11', 0, 22, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 'pending', '2025-11-27 02:02:37'),
(79, '0', '2025-10', 5, 17, 61, 3.00, 2343.75, 27343.75, 765.00, 683.59, 100.00, 744.27, 635.42, 0.00, 2928.28, 24415.47, 1555.00, 683.59, 100.00, 'pending', '2025-12-10 02:11:34'),
(80, '4', '2025-10', 4, 18, 1005, 0.00, 0.00, 2780.00, 180.00, 250.00, 55.60, 0.00, 1455.16, 0.00, 1940.76, 839.24, 380.00, 250.00, 55.60, 'pending', '2025-12-10 02:11:34'),
(81, '5', '2025-10', 3, 19, 266, 0.00, 0.00, 2085.00, 180.00, 250.00, 41.70, 0.00, 385.15, 0.00, 856.85, 1228.15, 380.00, 250.00, 41.70, 'pending', '2025-12-10 02:11:34'),
(82, '1', '2025-10', 1, 21, 0, 0.00, 0.00, 2000.00, 180.00, 250.00, 40.00, 0.00, 0.00, 0.00, 470.00, 1530.00, 380.00, 250.00, 40.00, 'pending', '2025-12-10 02:11:34'),
(83, '3', '2025-10', 1, 21, 491, 0.00, 0.00, 695.00, 180.00, 250.00, 6.95, 0.00, 710.93, 0.00, 1147.88, -452.88, 380.00, 250.00, 13.90, 'pending', '2025-12-10 02:11:34');

--
-- Indexes for dumped tables
--

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

--
-- Indexes for table `attendance`
--
ALTER TABLE `attendance`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_attendance` (`employee_id`,`attendance_date`);

--
-- Indexes for table `attendance_logs`
--
ALTER TABLE `attendance_logs`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `biometric_devices`
--
ALTER TABLE `biometric_devices`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `biometric_sync_logs`
--
ALTER TABLE `biometric_sync_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `device_id` (`device_id`);

--
-- Indexes for table `employees`
--
ALTER TABLE `employees`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `employee_id` (`employee_id`);

--
-- Indexes for table `employee_biometric`
--
ALTER TABLE `employee_biometric`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_mapping` (`employee_id`,`device_user_id`);

--
-- Indexes for table `import_history`
--
ALTER TABLE `import_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_import_date` (`import_date`);

--
-- Indexes for table `payroll`
--
ALTER TABLE `payroll`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_payroll` (`employee_id`,`payroll_month`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `account_approval_log`
--
ALTER TABLE `account_approval_log`
  ADD CONSTRAINT `account_approval_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `account_approval_log_ibfk_2` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `attendance`
--
ALTER TABLE `attendance`
  ADD CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE;

--
-- Constraints for table `biometric_sync_logs`
--
ALTER TABLE `biometric_sync_logs`
  ADD CONSTRAINT `biometric_sync_logs_ibfk_1` FOREIGN KEY (`device_id`) REFERENCES `biometric_devices` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `employee_biometric`
--
ALTER TABLE `employee_biometric`
  ADD CONSTRAINT `employee_biometric_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE;

--
-- Constraints for table `payroll`
--
ALTER TABLE `payroll`
  ADD CONSTRAINT `payroll_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE;
COMMIT;

