--
-- Host: localhost    Database: hrms
-- ------------------------------------------------------


--
-- Table structure for table `addition_types`
--

CREATE TABLE `addition_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `addition_types`
--

INSERT INTO `addition_types` VALUES (1,'HRA','House Rent Allowance (Usually 40% of basic)','2026-04-10 05:42:50','2026-04-10 05:42:50'),(2,'Special Allowance','Standard special allowance component','2026-04-10 05:42:50','2026-04-10 05:42:50'),(3,'Conveyance','Fixed conveyance allowance','2026-04-10 05:42:50','2026-04-10 05:42:50'),(4,'Medical','Fixed medical allowance','2026-04-10 05:42:50','2026-04-10 05:42:50'),(5,'LTA','Leave Travel Allowance','2026-04-10 05:42:50','2026-04-10 05:42:50'),(6,'Other Allowance','Additional allowance component','2026-04-10 05:42:50','2026-04-10 05:42:50');

--
-- Table structure for table `assets`
--

CREATE TABLE `assets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `asset_id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `serial_number` varchar(255) NOT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `purchase_date` date NOT NULL,
  `warranty_expiry` date DEFAULT NULL,
  `purchase_price` decimal(10,2) DEFAULT NULL,
  `vendor` varchar(255) DEFAULT NULL,
  `status` enum('Available','Assigned','Maintenance','Retired') NOT NULL DEFAULT 'Available',
  `assigned_to` bigint(20) unsigned DEFAULT NULL,
  `assigned_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `assets_asset_id_unique` (`asset_id`),
  UNIQUE KEY `assets_serial_number_unique` (`serial_number`),
  KEY `assets_assigned_to_foreign` (`assigned_to`),
  CONSTRAINT `assets_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `employees` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `assets`
--


--
-- Table structure for table `attendance_requests`
--

CREATE TABLE `attendance_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `date` date NOT NULL,
  `punch_in` time NOT NULL,
  `punch_out` time NOT NULL,
  `reason` text DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `action_by` bigint(20) unsigned DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `attendance_requests_employee_id_foreign` (`employee_id`),
  KEY `attendance_requests_action_by_foreign` (`action_by`),
  CONSTRAINT `attendance_requests_action_by_foreign` FOREIGN KEY (`action_by`) REFERENCES `employees` (`id`),
  CONSTRAINT `attendance_requests_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `attendance_requests`
--

INSERT INTO `attendance_requests` VALUES (1,2,'2026-04-15','09:00:00','18:00:00','missed punch','pending',NULL,NULL,'2026-04-16 06:32:12','2026-04-16 06:32:12');

--
-- Table structure for table `attendances`
--

CREATE TABLE `attendances` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `punch_in` datetime NOT NULL,
  `punch_out` datetime DEFAULT NULL,
  `date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `attendances_employee_id_foreign` (`employee_id`),
  CONSTRAINT `attendances_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `attendances`
--

INSERT INTO `attendances` VALUES (1,2,'2026-04-16 11:44:24','2026-04-16 11:44:40','2026-04-16','2026-04-16 06:14:24','2026-04-16 06:14:40');

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

CREATE TABLE `biometric_devices` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `ip_address` varchar(255) NOT NULL,
  `port` int(11) NOT NULL DEFAULT 4370,
  `serial_number` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `protocol` enum('eSSL','ZK','Other') NOT NULL DEFAULT 'eSSL',
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `last_activity` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `biometric_devices_ip_address_unique` (`ip_address`),
  UNIQUE KEY `biometric_devices_serial_number_unique` (`serial_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `biometric_devices`
--


--
-- Table structure for table `business_settings`
--

CREATE TABLE `business_settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_name` varchar(255) DEFAULT NULL,
  `company_profile` text DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `contact_number` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `company_logo` varchar(255) DEFAULT NULL,
  `currency_symbol` varchar(255) NOT NULL DEFAULT 'Γé╣',
  `minimum_working_hours` int(11) NOT NULL DEFAULT 9,
  `standard_punch_in_time` time NOT NULL DEFAULT '09:30:00',
  `theme_color_primary` varchar(255) NOT NULL DEFAULT '#8B5CF6',
  `theme_color_navbar` varchar(255) NOT NULL DEFAULT '#ffffff',
  `theme_text_color` varchar(255) NOT NULL DEFAULT '#1f2937',
  `theme_font_family` varchar(255) NOT NULL DEFAULT 'Inter',
  `theme_color_sidebar` varchar(255) NOT NULL DEFAULT '#7386D5',
  `theme_color_sidebar_text` varchar(255) NOT NULL DEFAULT '#ffffff',
  `theme_color_navbar_text` varchar(255) NOT NULL DEFAULT '#333333',
  `hr_policy` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `business_settings`
--

INSERT INTO `business_settings` VALUES (1,NULL,NULL,NULL,NULL,NULL,'IN',NULL,'Γé╣',8,'08:00:00','#8B5CF6','#ffffff','#1f2937','Inter','#7386D5','#ffffff','#333333',NULL,'2026-04-16 04:53:23','2026-04-16 04:53:27');

--
-- Table structure for table `deduction_types`
--

CREATE TABLE `deduction_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `deduction_types`
--

INSERT INTO `deduction_types` VALUES (1,'ESIC (Employee)','Employee contribution to ESIC (0.75%)','2026-04-10 05:42:50','2026-04-10 05:42:50'),(2,'Professional Tax','Professional Tax deduction','2026-04-10 05:42:50','2026-04-10 05:42:50'),(3,'Income Tax','Income Tax (TDS) deduction','2026-04-10 05:42:50','2026-04-10 05:42:50'),(4,'Gratuity','Gratuity contribution','2026-04-10 05:42:50','2026-04-10 05:42:50'),(5,'ESIC (Employer)','Employer contribution to ESIC (3.25%)','2026-04-10 05:42:50','2026-04-10 05:42:50');

--
-- Table structure for table `employee_additions`
--

CREATE TABLE `employee_additions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `addition_type_id` bigint(20) unsigned NOT NULL,
  `value_type` varchar(255) NOT NULL DEFAULT 'fixed',
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `percentage` decimal(5,2) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `employee_additions_employee_id_foreign` (`employee_id`),
  KEY `employee_additions_addition_type_id_foreign` (`addition_type_id`),
  CONSTRAINT `employee_additions_addition_type_id_foreign` FOREIGN KEY (`addition_type_id`) REFERENCES `addition_types` (`id`) ON DELETE CASCADE,
  CONSTRAINT `employee_additions_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_additions`
--


--
-- Table structure for table `employee_deductions`
--

CREATE TABLE `employee_deductions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `deduction_type_id` bigint(20) unsigned NOT NULL,
  `value_type` varchar(255) NOT NULL DEFAULT 'percentage',
  `percentage` decimal(5,2) NOT NULL,
  `amount` decimal(12,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `employee_deductions_employee_id_foreign` (`employee_id`),
  KEY `employee_deductions_deduction_type_id_foreign` (`deduction_type_id`),
  CONSTRAINT `employee_deductions_deduction_type_id_foreign` FOREIGN KEY (`deduction_type_id`) REFERENCES `deduction_types` (`id`) ON DELETE CASCADE,
  CONSTRAINT `employee_deductions_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_deductions`
--


--
-- Table structure for table `employee_documents`
--

CREATE TABLE `employee_documents` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'pending',
  `rejection_reason` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `employee_documents_employee_id_foreign` (`employee_id`),
  CONSTRAINT `employee_documents_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_documents`
--


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

CREATE TABLE `employees` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `designation` varchar(255) DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `manager_id` bigint(20) unsigned DEFAULT NULL,
  `base_salary` decimal(15,2) NOT NULL DEFAULT 0.00,
  `joined_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `is_on_probation` tinyint(1) NOT NULL DEFAULT 0,
  `probation_end_date` date DEFAULT NULL,
  `sick_leave_limit` int(11) NOT NULL DEFAULT 12,
  `casual_leave_limit` int(11) NOT NULL DEFAULT 12,
  `bank_name` varchar(255) DEFAULT NULL,
  `account_number` varchar(255) DEFAULT NULL,
  `ifsc_code` varchar(255) DEFAULT NULL,
  `father_name` varchar(255) DEFAULT NULL,
  `mother_name` varchar(255) DEFAULT NULL,
  `total_experience` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employees_email_unique` (`email`),
  KEY `employees_manager_id_foreign` (`manager_id`),
  CONSTRAINT `employees_manager_id_foreign` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employees`
--

INSERT INTO `employees` VALUES (1,'Sibin Thomas','sibin@icsoft.in','9074739601','Developer',NULL,NULL,NULL,25000.00,'2026-04-01','2026-04-16 06:00:08','2026-04-16 06:00:08',0,NULL,12,12,NULL,NULL,NULL,NULL,NULL,NULL),(2,'Ridhul','rd@icsoft.in','9656410149','Python Developer',NULL,NULL,1,30000.00,'2026-04-01','2026-04-16 06:00:56','2026-04-16 06:00:56',0,NULL,12,12,NULL,NULL,NULL,NULL,NULL,NULL);

--
-- Table structure for table `expenses`
--

CREATE TABLE `expenses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `category` varchar(255) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `date` date NOT NULL,
  `proof` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `rejection_reason` text DEFAULT NULL,
  `is_settled` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `expenses_employee_id_foreign` (`employee_id`),
  CONSTRAINT `expenses_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `expenses`
--


--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `failed_jobs`
--


--
-- Table structure for table `holidays`
--

CREATE TABLE `holidays` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `date` date NOT NULL,
  `type` varchar(255) NOT NULL DEFAULT 'common',
  `description` text DEFAULT NULL,
  `country_code` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `holidays`
--


--
-- Table structure for table `job_batches`
--

CREATE TABLE `job_batches` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `total_jobs` int(11) NOT NULL,
  `pending_jobs` int(11) NOT NULL,
  `failed_jobs` int(11) NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` mediumtext DEFAULT NULL,
  `cancelled_at` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `finished_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `job_batches`
--


--
-- Table structure for table `jobs`
--

CREATE TABLE `jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint(3) unsigned NOT NULL,
  `reserved_at` int(10) unsigned DEFAULT NULL,
  `available_at` int(10) unsigned NOT NULL,
  `created_at` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `jobs`
--


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

CREATE TABLE `leave_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `leave_type` varchar(255) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `is_half_day` tinyint(1) NOT NULL DEFAULT 0,
  `half_day_session` enum('first_half','second_half') DEFAULT NULL,
  `reason` text DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `action_by` bigint(20) unsigned DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `is_lop` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `leave_requests_employee_id_foreign` (`employee_id`),
  KEY `leave_requests_action_by_foreign` (`action_by`),
  CONSTRAINT `leave_requests_action_by_foreign` FOREIGN KEY (`action_by`) REFERENCES `employees` (`id`),
  CONSTRAINT `leave_requests_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `leave_requests`
--

INSERT INTO `leave_requests` VALUES (1,2,'Casual Leave','2026-04-17','2026-04-17',0,NULL,'marrage','pending',NULL,NULL,'2026-04-16 06:29:42','2026-04-16 06:29:42',0);

--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` VALUES (1,'0001_01_01_000000_create_users_table',1),(2,'0001_01_01_000001_create_cache_table',1),(3,'0001_01_01_000002_create_jobs_table',1),(4,'2026_01_14_171537_create_employees_table',1),(5,'2026_01_14_171539_create_salaries_table',1),(6,'2026_01_14_171540_create_attendances_table',1),(7,'2026_01_14_172609_create_roles_table',1),(8,'2026_01_14_172610_add_role_and_employee_to_users_table',1),(9,'2026_01_14_173920_create_attendance_requests_table',1),(10,'2026_01_14_173921_create_leave_requests_table',1),(11,'2026_01_14_175739_add_is_lop_to_leave_requests_table',1),(12,'2026_01_14_175739_add_probation_and_leave_limits_to_employees_table',1),(13,'2026_01_15_083225_create_deduction_types_table',1),(14,'2026_01_15_083226_create_employee_deductions_table',1),(15,'2026_01_15_083226_create_salary_deductions_table',1),(16,'2026_01_15_083227_create_business_settings_table',1),(17,'2026_01_15_112241_add_country_and_policy_to_business_settings',1),(18,'2026_01_15_112352_create_holidays_table',1),(19,'2026_01_15_114059_add_extra_info_to_employees_and_create_requests_tables',1),(20,'2026_01_15_145553_add_half_day_to_leave_requests_table',1),(21,'2026_01_15_151628_add_currency_symbol_to_business_settings_table',1),(22,'2026_01_15_152117_create_expenses_table',1),(23,'2026_01_15_181553_add_attendance_settings_to_business_settings_table',1),(24,'2026_01_16_060657_create_assets_table',1),(25,'2026_01_16_073909_create_biometric_devices_table',1),(26,'2026_01_16_090552_add_theme_settings_to_business_settings_table',1),(27,'2026_01_16_091149_add_more_theme_settings_to_business_settings_table',1),(28,'2026_04_10_065414_add_payroll_components_to_salaries_table',2),(29,'2026_04_10_071745_add_special_allowance_2_to_salaries_table',3),(30,'2026_04_10_083421_add_department_to_employees_table',4),(31,'2026_04_10_092700_add_snapshot_fields_to_salaries_table',5),(32,'2026_04_10_103826_create_addition_types_table',6),(33,'2026_04_10_103840_create_employee_additions_table',6),(34,'2026_04_10_104429_add_components_to_salaries_table',7),(35,'2026_04_16_092500_add_permissions_to_roles_table',8),(36,'2026_04_16_104000_update_payroll_components_structure',9);

--
-- Table structure for table `profile_update_requests`
--

CREATE TABLE `profile_update_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `changes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`changes`)),
  `status` varchar(255) NOT NULL DEFAULT 'pending',
  `rejection_reason` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `profile_update_requests_employee_id_foreign` (`employee_id`),
  CONSTRAINT `profile_update_requests_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `profile_update_requests`
--


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

CREATE TABLE `roles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `permissions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`permissions`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_name_unique` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `roles` VALUES (1,'Admin',NULL,'2026-04-09 23:50:41','2026-04-09 23:50:41'),(2,'Staff','[\"dashboard.view\",\"attendance.view\",\"attendance.punch\",\"attendance.manage_requests\",\"leaves.view\",\"leaves.create\",\"leaves.manage\"]','2026-04-09 23:50:41','2026-04-16 06:08:50');

--
-- Table structure for table `salaries`
--

CREATE TABLE `salaries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `designation` varchar(255) DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL,
  `employee_code` varchar(255) DEFAULT NULL,
  `month` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `working_days` int(11) NOT NULL DEFAULT 0,
  `lop_days` int(11) NOT NULL DEFAULT 0,
  `base_salary` decimal(15,2) NOT NULL,
  `hra` decimal(15,2) NOT NULL DEFAULT 0.00,
  `special_allowance` decimal(15,2) NOT NULL DEFAULT 0.00,
  `conveyance` decimal(15,2) NOT NULL DEFAULT 0.00,
  `medical` decimal(15,2) NOT NULL DEFAULT 0.00,
  `lta` decimal(15,2) NOT NULL DEFAULT 0.00,
  `special_allowance_2` decimal(15,2) NOT NULL DEFAULT 0.00,
  `gross_earnings` decimal(15,2) NOT NULL DEFAULT 0.00,
  `esic_employee` decimal(15,2) NOT NULL DEFAULT 0.00,
  `esic_employer` decimal(15,2) NOT NULL DEFAULT 0.00,
  `gratuity` decimal(15,2) NOT NULL DEFAULT 0.00,
  `prof_tax` decimal(15,2) NOT NULL DEFAULT 0.00,
  `income_tax` decimal(15,2) NOT NULL DEFAULT 0.00,
  `gross_deductions` decimal(15,2) NOT NULL DEFAULT 0.00,
  `expense_reimbursements` decimal(15,2) NOT NULL DEFAULT 0.00,
  `net_pay` decimal(15,2) NOT NULL DEFAULT 0.00,
  `components` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`components`)),
  `bonus` decimal(15,2) NOT NULL DEFAULT 0.00,
  `deductions` decimal(15,2) NOT NULL DEFAULT 0.00,
  `net_salary` decimal(15,2) NOT NULL,
  `status` enum('pending','paid') NOT NULL DEFAULT 'pending',
  `payment_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `salaries_employee_id_foreign` (`employee_id`),
  CONSTRAINT `salaries_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `salaries`
--


--
-- Table structure for table `salary_deductions`
--

CREATE TABLE `salary_deductions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `salary_id` bigint(20) unsigned NOT NULL,
  `deduction_type_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `percentage` decimal(5,2) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `salary_deductions_salary_id_foreign` (`salary_id`),
  KEY `salary_deductions_deduction_type_id_foreign` (`deduction_type_id`),
  CONSTRAINT `salary_deductions_deduction_type_id_foreign` FOREIGN KEY (`deduction_type_id`) REFERENCES `deduction_types` (`id`) ON DELETE CASCADE,
  CONSTRAINT `salary_deductions_salary_id_foreign` FOREIGN KEY (`salary_id`) REFERENCES `salaries` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `salary_deductions`
--


--
-- Table structure for table `sessions`
--

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `sessions`
--

INSERT INTO `sessions` VALUES ('l7MOGgDGMl6OvJ643YadPP55iCnkiI3ja44pvOsm',2,'::1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36 Edg/147.0.0.0','YTo2OntzOjY6Il90b2tlbiI7czo0MDoiMTNoUUxFVjhzZTRBbXFOdnJ5NHZQZ1hZOXlYdThGYUVoM3dQSXVQaSI7czozOiJ1cmwiO2E6MDp7fXM6OToiX3ByZXZpb3VzIjthOjI6e3M6MzoidXJsIjtzOjQ4OiJodHRwOi8vbG9jYWxob3N0L2hybXMvcHVibGljL2F0dGVuZGFuY2UtcmVxdWVzdHMiO3M6NToicm91dGUiO3M6MjU6ImF0dGVuZGFuY2UtcmVxdWVzdHMuaW5kZXgiO31zOjY6Il9mbGFzaCI7YToyOntzOjM6Im9sZCI7YTowOnt9czozOiJuZXciO2E6MDp7fX1zOjUwOiJsb2dpbl93ZWJfNTliYTM2YWRkYzJiMmY5NDAxNTgwZjAxNGM3ZjU4ZWE0ZTMwOTg5ZCI7aToyO3M6NDoiYXV0aCI7YToxOntzOjIxOiJwYXNzd29yZF9jb25maXJtZWRfYXQiO2k6MTc3NjMzOTA4MTt9fQ==',1776341210),('TiepgBsJjB1CJZ3VCgEVO0r84vVYt2nXQjVbMcsp',3,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:149.0) Gecko/20100101 Firefox/149.0','YTo1OntzOjY6Il90b2tlbiI7czo0MDoiRGMzbGhmNWJ5TWxSRjRLZFI4QjdYMmVhQm1NeThaamtLc1oyaTdQciI7czo5OiJfcHJldmlvdXMiO2E6Mjp7czozOiJ1cmwiO3M6NDg6Imh0dHA6Ly9sb2NhbGhvc3QvaHJtcy9wdWJsaWMvYXR0ZW5kYW5jZS1yZXF1ZXN0cyI7czo1OiJyb3V0ZSI7czoyNToiYXR0ZW5kYW5jZS1yZXF1ZXN0cy5pbmRleCI7fXM6NjoiX2ZsYXNoIjthOjI6e3M6Mzoib2xkIjthOjA6e31zOjM6Im5ldyI7YTowOnt9fXM6NTA6ImxvZ2luX3dlYl81OWJhMzZhZGRjMmIyZjk0MDE1ODBmMDE0YzdmNThlYTRlMzA5ODlkIjtpOjM7czo0OiJhdXRoIjthOjE6e3M6MjE6InBhc3N3b3JkX2NvbmZpcm1lZF9hdCI7aToxNzc2MzM5NDgzO319',1776343030),('XoqNo8b2UUMWgQcEi8u4czllwzOCuKBnV7PLARb3',NULL,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:149.0) Gecko/20100101 Firefox/149.0','YTo0OntzOjY6Il90b2tlbiI7czo0MDoiWEZVYll0VXJmR1BXMGdZQUQ1NzkwczlkYWEwRDlCUEpjc2g5WVRiNSI7czo2OiJfZmxhc2giO2E6Mjp7czozOiJvbGQiO2E6MDp7fXM6MzoibmV3IjthOjA6e319czozOiJ1cmwiO2E6MTp7czo4OiJpbnRlbmRlZCI7czoyODoiaHR0cDovL2xvY2FsaG9zdC9ocm1zL3B1YmxpYyI7fXM6OToiX3ByZXZpb3VzIjthOjI6e3M6MzoidXJsIjtzOjQzOiJodHRwOi8vbG9jYWxob3N0L2hybXMvcHVibGljL3Bhc3N3b3JkL3Jlc2V0IjtzOjU6InJvdXRlIjtzOjE2OiJwYXNzd29yZC5yZXF1ZXN0Ijt9fQ==',1776343188);


