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

START TRANSACTION;



--
-- Database: `payroll_system`
--

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

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

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

--
-- 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
(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,
  `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`, `created_at`, `updated_at`) VALUES
(1, '0', 'Big, Boss', 'NCR', 5000.00, 40, 'CEO', '2025-10-20 00:20:04', '2025-10-20 05:51:08');

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

--
-- 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
(5, '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-10-28 05:33:07');

--
-- Indexes for dumped tables
--

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

--
-- 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 `attendance`
--
ALTER TABLE `attendance`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

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

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

--
-- Constraints for dumped tables
--

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

