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

START TRANSACTION;



--
-- Database: `real-time-signin`
--

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

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

CREATE TABLE IF NOT EXISTS `attendance` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` int UNSIGNED NOT NULL,
  `attendance_date` date NOT NULL,
  `sign_in_time` time DEFAULT NULL,
  `sign_out_time` time DEFAULT NULL,
  `sign_in_method` enum('QR Code','Facial Recognition','Manual','Biometric') COLLATE utf8mb4_general_ci DEFAULT NULL,
  `sign_out_method` enum('QR Code','Facial Recognition','Manual','Biometric') COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status` enum('present','absent','late','half-day','on-leave') COLLATE utf8mb4_general_ci DEFAULT 'present',
  `is_late` tinyint(1) DEFAULT '0',
  `late_minutes` int DEFAULT '0',
  `working_hours` decimal(5,2) DEFAULT NULL,
  `overtime_hours` decimal(5,2) DEFAULT '0.00',
  `notes` text COLLATE utf8mb4_general_ci,
  `location_id` int UNSIGNED DEFAULT NULL,
  `created_by` int UNSIGNED DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_date` (`employee_id`,`attendance_date`),
  KEY `employee_id` (`employee_id`),
  KEY `attendance_date` (`attendance_date`),
  KEY `status` (`status`),
  KEY `location_id` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `attendance_correction_requests`
--

CREATE TABLE IF NOT EXISTS `attendance_correction_requests` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` int UNSIGNED NOT NULL,
  `attendance_date` date NOT NULL,
  `original_sign_in` time DEFAULT NULL,
  `original_sign_out` time DEFAULT NULL,
  `requested_sign_in` time DEFAULT NULL,
  `requested_sign_out` time DEFAULT NULL,
  `reason` text COLLATE utf8mb4_general_ci NOT NULL,
  `status` enum('pending','approved','rejected') COLLATE utf8mb4_general_ci DEFAULT 'pending',
  `reviewed_by` int UNSIGNED DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `review_notes` text COLLATE utf8mb4_general_ci,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `employee_id` (`employee_id`),
  KEY `status` (`status`),
  KEY `reviewed_by` (`reviewed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `audit_logs`
--

CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int UNSIGNED DEFAULT NULL,
  `action` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `module` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `record_id` int UNSIGNED DEFAULT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `old_values` text COLLATE utf8mb4_general_ci,
  `new_values` text COLLATE utf8mb4_general_ci,
  `ip_address` varchar(45) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_general_ci,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `module` (`module`),
  KEY `action` (`action`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `audit_logs`
--

INSERT INTO `audit_logs` (`id`, `user_id`, `action`, `module`, `record_id`, `description`, `old_values`, `new_values`, `ip_address`, `user_agent`, `created_at`) VALUES
(1, 1, 'Login', 'Authentication', NULL, 'User logged in successfully', NULL, NULL, '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/143.0.0.0 Safari/537.36', NULL);

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

--
-- Table structure for table `departments`
--

CREATE TABLE IF NOT EXISTS `departments` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `code` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `head_employee_id` int UNSIGNED DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `departments`
--

INSERT INTO `departments` (`id`, `name`, `code`, `description`, `head_employee_id`, `is_active`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'Human Resources', 'HR', 'Human resources and recruitment', NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(2, 'Information Technology', 'IT', 'IT support and development', NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(3, 'Finance', 'FIN', 'Financial management and accounting', NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(4, 'Operations', 'OPS', 'Daily operations management', NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(5, 'Sales', 'SALES', 'Sales and business development', NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(6, 'Marketing', 'MKTG', 'Marketing and communications', NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(7, 'Customer Service', 'CS', 'Customer support and relations', NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(8, 'Logistics', 'LOG', 'Logistics and supply chain', NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(9, 'Quality Assurance', 'QA', 'Quality control and assurance', NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(10, 'Administration', 'ADMIN', 'General administration', NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL);

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

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

CREATE TABLE IF NOT EXISTS `employees` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_number` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `first_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `middle_name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `last_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` enum('Male','Female','Other') COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_general_ci,
  `city` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `province` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `zip_code` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `department_id` int UNSIGNED DEFAULT NULL,
  `position` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `location_id` int UNSIGNED NOT NULL,
  `hire_date` date NOT NULL,
  `employment_status` enum('Regular','Probationary','Contractual','Part-time') COLLATE utf8mb4_general_ci DEFAULT 'Probationary',
  `status` enum('active','inactive','resigned','terminated') COLLATE utf8mb4_general_ci DEFAULT 'active',
  `qr_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `facial_recognition_data` text COLLATE utf8mb4_general_ci,
  `photo` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_number` (`employee_number`),
  UNIQUE KEY `qr_code` (`qr_code`),
  KEY `department_id` (`department_id`),
  KEY `location_id` (`location_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `leave_balances`
--

CREATE TABLE IF NOT EXISTS `leave_balances` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` int UNSIGNED NOT NULL,
  `leave_type_id` int UNSIGNED NOT NULL,
  `year` int NOT NULL,
  `total_days` decimal(5,2) NOT NULL,
  `used_days` decimal(5,2) DEFAULT '0.00',
  `remaining_days` decimal(5,2) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_leave_year` (`employee_id`,`leave_type_id`,`year`),
  KEY `employee_id` (`employee_id`),
  KEY `leave_type_id` (`leave_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `leave_requests`
--

CREATE TABLE IF NOT EXISTS `leave_requests` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` int UNSIGNED NOT NULL,
  `leave_type_id` int UNSIGNED NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `total_days` decimal(5,2) NOT NULL,
  `reason` text COLLATE utf8mb4_general_ci NOT NULL,
  `attachment` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status` enum('pending','approved','rejected','cancelled') COLLATE utf8mb4_general_ci DEFAULT 'pending',
  `approved_by` int UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `rejection_reason` text COLLATE utf8mb4_general_ci,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `employee_id` (`employee_id`),
  KEY `leave_type_id` (`leave_type_id`),
  KEY `status` (`status`),
  KEY `approved_by` (`approved_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `leave_types`
--

CREATE TABLE IF NOT EXISTS `leave_types` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `code` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `days_per_year` int DEFAULT NULL,
  `is_paid` tinyint(1) DEFAULT '1',
  `requires_document` tinyint(1) DEFAULT '0',
  `max_consecutive_days` int DEFAULT NULL,
  `color` varchar(7) COLLATE utf8mb4_general_ci DEFAULT '#3498db',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `leave_types`
--

INSERT INTO `leave_types` (`id`, `name`, `code`, `description`, `days_per_year`, `is_paid`, `requires_document`, `max_consecutive_days`, `color`, `is_active`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'Sick Leave', 'SL', 'For medical reasons', 15, 1, 1, 5, '#e74c3c', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(2, 'Personal Leave', 'PL', 'For personal matters', 10, 1, 0, 3, '#3498db', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(3, 'Vacation Leave', 'VL', 'Paid vacation time', 20, 1, 0, 15, '#2ecc71', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(4, 'Maternity Leave', 'ML', 'For expecting mothers', 105, 1, 1, 105, '#e91e63', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(5, 'Paternity Leave', 'PTL', 'For new fathers', 7, 1, 1, 7, '#9b59b6', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(6, 'Emergency Leave', 'EL', 'For emergencies', 5, 1, 0, 2, '#f39c12', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(7, 'Bereavement Leave', 'BL', 'For mourning', 5, 1, 1, 5, '#34495e', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(8, 'Unpaid Leave', 'UL', 'Without pay', NULL, 0, 0, NULL, '#95a5a6', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL);

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

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

CREATE TABLE IF NOT EXISTS `locations` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `code` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `address` text COLLATE utf8mb4_general_ci,
  `city` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `province` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `contact_number` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `timezone` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'Asia/Manila',
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `is_active` (`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `locations` (`id`, `name`, `code`, `address`, `city`, `province`, `contact_number`, `email`, `timezone`, `latitude`, `longitude`, `is_active`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'LBDI Manila Head Office', 'MNL-HQ', '123 Ayala Avenue, Makati City', 'Makati', 'Metro Manila', '+63 2 8123 4567', 'manila@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(2, 'LBDI Cebu Branch', 'CEB-01', '456 Osmeña Boulevard, Cebu City', 'Cebu City', 'Cebu', '+63 32 234 5678', 'cebu@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(3, 'LBDI Davao Branch', 'DAV-01', '789 J.P. Laurel Avenue, Davao City', 'Davao City', 'Davao del Sur', '+63 82 345 6789', 'davao@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(4, 'LBDI Baguio Branch', 'BAG-01', '321 Session Road, Baguio City', 'Baguio', 'Benguet', '+63 74 456 7890', 'baguio@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(5, 'LBDI Iloilo Branch', 'ILO-01', '654 Iznart Street, Iloilo City', 'Iloilo City', 'Iloilo', '+63 33 567 8901', 'iloilo@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(6, 'LBDI Cagayan de Oro Branch', 'CDO-01', '987 Corrales Avenue, Cagayan de Oro', 'Cagayan de Oro', 'Misamis Oriental', '+63 88 678 9012', 'cdo@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(7, 'LBDI Bacolod Branch', 'BAC-01', '147 Lacson Street, Bacolod City', 'Bacolod', 'Negros Occidental', '+63 34 789 0123', 'bacolod@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(8, 'LBDI Zamboanga Branch', 'ZAM-01', '258 Valderosa Street, Zamboanga City', 'Zamboanga', 'Zamboanga del Sur', '+63 62 890 1234', 'zamboanga@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(9, 'LBDI Tacloban Branch', 'TAC-01', '369 Real Street, Tacloban City', 'Tacloban', 'Leyte', '+63 53 901 2345', 'tacloban@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(10, 'LBDI General Santos Branch', 'GEN-01', '741 Pioneer Avenue, General Santos City', 'General Santos', 'South Cotabato', '+63 83 012 3456', 'gensan@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(11, 'LBDI Dumaguete Branch', 'DUM-01', '852 Rizal Boulevard, Dumaguete City', 'Dumaguete', 'Negros Oriental', '+63 35 123 4567', 'dumaguete@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(12, 'LBDI Butuan Branch', 'BUT-01', '963 Montilla Boulevard, Butuan City', 'Butuan', 'Agusan del Norte', '+63 85 234 5678', 'butuan@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(13, 'LBDI Legazpi Branch', 'LEG-01', '159 Peñaranda Street, Legazpi City', 'Legazpi', 'Albay', '+63 52 345 6789', 'legazpi@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(14, 'LBDI Naga Branch', 'NAG-01', '357 Elias Angeles Street, Naga City', 'Naga', 'Camarines Sur', '+63 54 456 7890', 'naga@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(15, 'LBDI Puerto Princesa Branch', 'PPS-01', '468 Rizal Avenue, Puerto Princesa', 'Puerto Princesa', 'Palawan', '+63 48 567 8901', 'palawan@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(16, 'LBDI Iligan Branch', 'ILI-01', '579 Quezon Avenue, Iligan City', 'Iligan', 'Lanao del Norte', '+63 63 678 9012', 'iligan@lbdi.com', 'Asia/Manila', NULL, NULL, 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL);

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

--
-- Table structure for table `permissions`
--

CREATE TABLE IF NOT EXISTS `permissions` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `slug` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `module` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `module` (`module`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



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

--
-- Table structure for table `roles`
--

CREATE TABLE IF NOT EXISTS `roles` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `slug` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` (`id`, `name`, `slug`, `description`, `is_active`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'Super Admin', 'super-admin', 'Full system access with all permissions', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(2, 'HR Manager', 'hr-manager', 'Manage employees, attendance, and leave requests', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(3, 'Branch Manager', 'branch-manager', 'Manage specific branch operations', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(4, 'Department Head', 'department-head', 'Manage department-level operations', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL),
(5, 'Viewer', 'viewer', 'Read-only access to reports and data', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43', NULL);

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

--
-- Table structure for table `role_permissions`
--

CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` int UNSIGNED NOT NULL,
  `permission_id` int UNSIGNED NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_permission` (`role_id`,`permission_id`),
  KEY `role_id` (`role_id`),
  KEY `permission_id` (`permission_id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `role_permissions`
--

INSERT INTO `role_permissions` (`id`, `role_id`, `permission_id`, `created_at`) VALUES
(1, 1, 7, '2026-01-10 01:29:43'),
(2, 1, 8, '2026-01-10 01:29:43'),
(3, 1, 9, '2026-01-10 01:29:43'),
(4, 1, 10, '2026-01-10 01:29:43'),
(5, 1, 28, '2026-01-10 01:29:43'),
(6, 1, 1, '2026-01-10 01:29:43'),
(7, 1, 30, '2026-01-10 01:29:43'),
(8, 1, 2, '2026-01-10 01:29:43'),
(9, 1, 3, '2026-01-10 01:29:43'),
(10, 1, 4, '2026-01-10 01:29:43'),
(11, 1, 5, '2026-01-10 01:29:43'),
(12, 1, 6, '2026-01-10 01:29:43'),
(13, 1, 11, '2026-01-10 01:29:43'),
(14, 1, 12, '2026-01-10 01:29:43'),
(15, 1, 13, '2026-01-10 01:29:43'),
(16, 1, 14, '2026-01-10 01:29:43'),
(17, 1, 29, '2026-01-10 01:29:43'),
(18, 1, 15, '2026-01-10 01:29:43'),
(19, 1, 16, '2026-01-10 01:29:43'),
(20, 1, 17, '2026-01-10 01:29:43'),
(21, 1, 18, '2026-01-10 01:29:43'),
(22, 1, 23, '2026-01-10 01:29:43'),
(23, 1, 24, '2026-01-10 01:29:43'),
(24, 1, 25, '2026-01-10 01:29:43'),
(25, 1, 26, '2026-01-10 01:29:43'),
(26, 1, 27, '2026-01-10 01:29:43'),
(27, 1, 31, '2026-01-10 01:29:43'),
(28, 1, 19, '2026-01-10 01:29:43'),
(29, 1, 20, '2026-01-10 01:29:43'),
(30, 1, 21, '2026-01-10 01:29:43'),
(31, 1, 22, '2026-01-10 01:29:43'),
(32, 2, 1, '2026-01-10 01:29:43'),
(33, 2, 2, '2026-01-10 01:29:43'),
(34, 2, 3, '2026-01-10 01:29:43'),
(35, 2, 4, '2026-01-10 01:29:43'),
(36, 2, 5, '2026-01-10 01:29:43'),
(37, 2, 6, '2026-01-10 01:29:43'),
(38, 2, 7, '2026-01-10 01:29:43'),
(39, 2, 8, '2026-01-10 01:29:43'),
(40, 2, 9, '2026-01-10 01:29:43'),
(41, 2, 10, '2026-01-10 01:29:43'),
(42, 2, 11, '2026-01-10 01:29:43'),
(43, 2, 12, '2026-01-10 01:29:43'),
(44, 2, 13, '2026-01-10 01:29:43'),
(45, 2, 14, '2026-01-10 01:29:43'),
(46, 2, 15, '2026-01-10 01:29:43'),
(47, 2, 16, '2026-01-10 01:29:43'),
(48, 2, 17, '2026-01-10 01:29:43'),
(49, 3, 1, '2026-01-10 01:29:43'),
(50, 3, 2, '2026-01-10 01:29:43'),
(51, 3, 7, '2026-01-10 01:29:43'),
(52, 3, 8, '2026-01-10 01:29:43'),
(53, 3, 11, '2026-01-10 01:29:43'),
(54, 3, 13, '2026-01-10 01:29:43'),
(55, 3, 15, '2026-01-10 01:29:43');

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

--
-- Table structure for table `scheduled_reports`
--

CREATE TABLE IF NOT EXISTS `scheduled_reports` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `report_type` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `frequency` enum('daily','weekly','monthly','yearly') COLLATE utf8mb4_general_ci NOT NULL,
  `schedule_time` time DEFAULT NULL,
  `schedule_day` int DEFAULT NULL,
  `recipients` text COLLATE utf8mb4_general_ci NOT NULL,
  `format` enum('PDF','Excel','CSV') COLLATE utf8mb4_general_ci DEFAULT 'PDF',
  `filters` text COLLATE utf8mb4_general_ci,
  `is_active` tinyint(1) DEFAULT '1',
  `last_run` datetime DEFAULT NULL,
  `next_run` datetime DEFAULT NULL,
  `created_by` int UNSIGNED DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `sync_logs`
--

CREATE TABLE IF NOT EXISTS `sync_logs` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `location_id` int UNSIGNED NOT NULL,
  `sync_type` enum('attendance','employee','manual') COLLATE utf8mb4_general_ci DEFAULT 'attendance',
  `records_synced` int DEFAULT '0',
  `status` enum('pending','in_progress','completed','failed') COLLATE utf8mb4_general_ci DEFAULT 'pending',
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `error_message` text COLLATE utf8mb4_general_ci,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `location_id` (`location_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `system_settings`
--

CREATE TABLE IF NOT EXISTS `system_settings` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `key` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `value` text COLLATE utf8mb4_general_ci,
  `type` enum('string','number','boolean','json') COLLATE utf8mb4_general_ci DEFAULT 'string',
  `description` text COLLATE utf8mb4_general_ci,
  `is_public` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key` (`key`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `system_settings`
--

INSERT INTO `system_settings` (`id`, `key`, `value`, `type`, `description`, `is_public`, `created_at`, `updated_at`) VALUES
(1, 'app_name', 'LBDI Attendance Management System', 'string', 'Application name', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43'),
(2, 'company_name', 'LBDI Corporation', 'string', 'Company name', 1, '2026-01-10 01:29:43', '2026-01-10 01:29:43'),
(3, 'timezone', 'Asia/Manila', 'string', 'System timezone', 0, '2026-01-10 01:29:43', '2026-01-10 01:29:43'),
(4, 'working_hours_start', '08:00', 'string', 'Standard work start time', 0, '2026-01-10 01:29:43', '2026-01-10 01:29:43'),
(5, 'working_hours_end', '17:00', 'string', 'Standard work end time', 0, '2026-01-10 01:29:43', '2026-01-10 01:29:43'),
(6, 'late_threshold_minutes', '15', 'number', 'Minutes before marked as late', 0, '2026-01-10 01:29:43', '2026-01-10 01:29:43'),
(7, 'attendance_auto_absent', 'true', 'boolean', 'Auto mark absent if no sign-in', 0, '2026-01-10 01:29:43', '2026-01-10 01:29:43'),
(8, 'leave_approval_required', 'true', 'boolean', 'Require approval for leave', 0, '2026-01-10 01:29:43', '2026-01-10 01:29:43'),
(9, 'max_file_upload_size', '5', 'number', 'Max file size in MB', 0, '2026-01-10 01:29:43', '2026-01-10 01:29:43'),
(10, 'date_format', 'Y-m-d', 'string', 'System date format', 0, '2026-01-10 01:29:43', '2026-01-10 01:29:43'),
(11, 'time_format', 'H:i', 'string', 'System time format', 0, '2026-01-10 01:29:43', '2026-01-10 01:29:43'),
(12, 'records_per_page', '25', 'number', 'Pagination records per page', 0, '2026-01-10 01:29:43', '2026-01-10 01:29:43');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `attendance`
--
ALTER TABLE `attendance`
  ADD CONSTRAINT `attendance_employee_fk` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `attendance_location_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`);

--
-- Constraints for table `attendance_correction_requests`
--
ALTER TABLE `attendance_correction_requests`
  ADD CONSTRAINT `correction_requests_employee_fk` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `correction_requests_reviewer_fk` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `audit_logs`
--
ALTER TABLE `audit_logs`
  ADD CONSTRAINT `audit_logs_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `employees`
--
ALTER TABLE `employees`
  ADD CONSTRAINT `employees_department_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`),
  ADD CONSTRAINT `employees_location_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`);

--
-- Constraints for table `leave_balances`
--
ALTER TABLE `leave_balances`
  ADD CONSTRAINT `leave_balances_employee_fk` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `leave_balances_leave_type_fk` FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `leave_requests`
--
ALTER TABLE `leave_requests`
  ADD CONSTRAINT `leave_requests_approver_fk` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `leave_requests_employee_fk` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `leave_requests_leave_type_fk` FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types` (`id`);

--
-- Constraints for table `role_permissions`
--
ALTER TABLE `role_permissions`
  ADD CONSTRAINT `role_permissions_permission_fk` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `role_permissions_role_fk` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `scheduled_reports`
--
ALTER TABLE `scheduled_reports`
  ADD CONSTRAINT `scheduled_reports_user_fk` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `sync_logs`
--
ALTER TABLE `sync_logs`
  ADD CONSTRAINT `sync_logs_location_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE CASCADE;
COMMIT;

